package org.mbari.seepsearch.db;

import java.sql.*;

public class SimpleVarsQuery {

    public SimpleVarsQuery() throws ClassNotFoundException,
     SQLException, IllegalAccessException, InstantiationException  {
        this.vc = new VimsConnection();
    }

    public ResultSet execute(String lookUpName) throws ClassNotFoundException,
     SQLException, IllegalAccessException, InstantiationException  {

        // executeQuery returns a ResultSet
        String query = "SELECT DISTINCT RecordedEpoch, InSequence , " +
         "ISNULL(RTRIM(CONVERT(VARCHAR(12),Latitude)),' ') AS Latitude1, " +
         "ISNULL(RTRIM(CONVERT(VARCHAR(13),Longitude)),' ') AS Longitude1, " +
         "ISNULL(RTRIM(CONVERT(VARCHAR(10),Depth)),' ') AS Depth1, " +
         "ISNULL(CONVERT(CHAR(4),ShipName),' ') AS ShipName1, " +
         "ISNULL(CONVERT(CHAR(4),PlatformName),' ') AS PlatformName1, " +
         "ISNULL(RTRIM(CONVERT(VARCHAR(6),SeqNumber)),' ') AS DiveNumber1 " +
         "FROM BasicAnnotations " +
         "JOIN AncillaryData  " +
         "ON (AncillaryDataID = Annotation_AncillaryDataID) " +
         "LEFT OUTER JOIN PlatformUsage  " +
         "ON (PlatformUsage_TapeSetID = TapeSetID) " +
         "AND (UsageStartDTG <= RecordedDtg) " +
         "AND (UsageEndDTG >= RecordedDtg) " +
         "WHERE (TapeSetID > 0) " +
         "AND AnnotationID " +
         "IN (" +
            "SELECT AnnoObserv_AnnotationID " +
            "FROM AnnoObserv " +
            "WHERE (LookupName = '" +
            lookUpName +
            "') ) ORDER BY RecordedEpoch";

        return vc.executeQuery(query);


    }



    private VimsConnection vc;

}