#!/usr/bin/env perl 
#-------------------------------------------------------------------------------
#
# Name: insert_support_records
#
# (This script is based on the old VMS version called insert_ss_records.pl
#  which required a supporting shel script. This script is designed to be
#  used directly as the resource command.)
#
# This perl script verifies that the input environment variables are OK, then
# it generates a set of queries to insert support schedule (SS) records into
# the following tables --
#
#                qolink_sms
#                asn_members
#                asn_association
#                asn_product_link
#                executed
#
# All these tables are generated using data within the replan time range of the
# SMS and their construction depends on the same temporary table #obslist.
#
# During processing the following temporary tables are created and dropped
# soon after their last usage.
#               #missing          missing obsets due to replan
#               #inst_id          converts si_id to file prefix code
#               #observ           all observations including dumps
#               #obslist          all observations except dumps
#               #datasets         add association data to obslist
#               #new_asn_list     gets new association records from #datasets
#               #old_asn_list     gets old association records from #datasets
#               #nic_acs_prod     identifies nic and acs products
#               #acs_dither       identifies acs dither products
#               #stis_obs         associated stis observations
#               #prod_times       start and stop times for asn products
#
# Interactive Usage:
#   insert_support_records.pl   [pod_name [sms_id]]
#
#   Note that the following environment variables (logicals) are
#       always required: OPUS_SERVER, SPSS_DB and OPUS_DB.
#       These queries require multiple _DBs on SAME server: 
#       keeping DSQUERY as a variable to emphasize this point.
#
#       If ENV variable LOG_DELETED_OBSETS is defined and set to Y, then
#       the obsets of deleted qolink_sms records are written.
#
#       If pod_name is missing the ENV variable OSF_DATASET is used.
#
#       If sms_id is missing the ENV variable MSC_DIR is used to locate the
#       actual POD file. The sms_id is used for a shortcut during testing when
#       the POD file may not be present.
#
# History:
# Date     OPR   Who        Reason
# -------- ----- ---------- ----------------------------------------------------
# 09/01/99 39638 Baum       Initial code - based on build 10.2 version of
#                           the ISQL commands in update_qodata plus
#                           additional queries for 39638 requirements for
#                           new executed table.
# 10/08/99 39638.1  Baum    Add new SPSS_DB logical.
# 04/20/00 40279 Baum       Change interface to use POD_FILE instead of date
#                           range. Use POD_FILE for file_times table to get
#                           replan date range. Cleanup qolink_sms for replan.
#                           Renamed procedure. If SMS_ID is NODEF, then use
#                           MSC_DIR to construct the POD file spec to get
#                           the calendar name from the MSC header. Verify
#                           executed table before proceeding.
# 11/21/00 43958 Baum       Changed name of script. Create standalone Unix
#                           version that can be passed two arguments. Set
#                           start_time, end_time and time_type in qolink_sms
#                           for exposures and products. Set new
#                           product_status in asn_members. Add codes for COS
#                           and WFP3. Remove some checking that is done in
#                           REPLAN process.
# 04/26/02 45738 Baum       Use external subroutines.
# 05/21/02 45888 Baum       Improve STIS ACQ end_time estimate.
# 11/01/02 46785 Baum       Improve replan processing - avoid deleting
#                           executed or qolink_sms records - do not update
#                           qolink_sms times having time_type="A".
# 02/13/03 47570 Baum       Change exit status values
# 04/11/03 48338 Baum       Do not change original SMS_ID for missing exps,
#                           and mark qolink_sms to N for missing observations
#                           that are in existing obsets.
# 05/02/03 46525 Baum       Delete qolink_sms records instead of setting
#                           status to N for missing observations in replan.
# 05/16/03 48642 Baum       Delete executed records of replans that have
#                           not yet been executed. Change some messages.
#                           Change location to set $replan.
# 09/02/03 49289 Baum       Update existing executed records to get new
#                           coord_id, control_id or si_id. Add control_id
#                           in test for missing exposures.
# 07/19/07 55484 MSwam      Changes for COS association handling
# 10/10/07 57745 Sherbert   Improved, logical indentation
#                           Verbosity will print ALL queries and save all temp
#                           tables to an output file in subdir of current dir.  
# 03/04/08 xxxxx MSwam      replace ST_DBlib with DBI
# 06/30/10 64432 MSwam      Use single quotes for SQLServer
# 07/29/10 64432 Garder/MSwam   Replace *= with LEFT OUTER JOIN for SQLserver
# 09/18/12 72255 Sherbert   keep DSQUERY in code to emphasize the sharing 
# 
#-------------------------------------------------------------------------------
# set up external routines
unshift @INC, ( split /:/, $ENV{PATH} );
require 'insert_support_records.pm';    # contains the subroutines
require 'printmsg.pl';          # prints a formatted status message
require 'do_dbi_pkg.pl';        # run queries that return records
require 'parse_sms.pl';         # parse SMS id from msc file header
require 'sogs_time_pkg.pl';     # manipulate time in SOGS format(yyy.ddd:hh:mm:ss)

#-------------------------------------------------------------------------------
# specify exit status values
#-------------------------------------------------------------------------------
$PROCESS_FAILURE = 7;           # exit status for XPOLL
$PROCESS_SUCCESS = 9;           # exit status for XPOLL

#-------------------------------------------------------------------------------
# check for arguments
#-------------------------------------------------------------------------------
$num_arg = scalar @ARGV;

if ( $num_arg > 0 && substr( $ARGV[0], 0, 1 ) ne "-" ) {
    $PIPELINE = 0;
    if ( $num_arg > 1 ) {
        $SMS_ID = uc( $ARGV[1] );    # uppercase it
    }
} else {
    $PIPELINE = 1;
}
if ( !defined($SMS_ID) ) {
    $MSC_DIR = $ENV{"MSC_DIR"};
    if ( !defined($MSC_DIR) ) {
        PrintMsg( "E", "Missing MSC_DIR in environment" );
        exit($PROCESS_FAILURE);
    }
}
if ($PIPELINE) {
    $POD_FILE = $ENV{"OSF_DATASET"};
} else {
    $POD_FILE = $ARGV[0];
}
if ( !defined($POD_FILE) ) {
    PrintMsg( "E", "Missing POD name in args or environment" );
    exit($PROCESS_FAILURE);
}

#-------------------------------------------------------------------------------
# get the required external variables
#-------------------------------------------------------------------------------
$DSQUERY = uc( $ENV{"OPUS_SERVER"} );
$OPUS_DB = lc( $ENV{"OPUS_DB"} );
$SPSS_DB = lc( $ENV{"SPSS_DB"} );

#-------------------------------------------------------------------------------
# get the optional external variables
#-------------------------------------------------------------------------------
my $MSG_REPORT_LEVEL = "MSG_INFO";
if ( exists( $ENV{"MSG_REPORT_LEVEL"} ) ) {
    $MSG_REPORT_LEVEL = $ENV{"MSG_REPORT_LEVEL"} ;
}
my $verbose = 0;
if ( $MSG_REPORT_LEVEL =~ "MSG_DIAG" ||
     $MSG_REPORT_LEVEL =~ "MSG_ALL" ) { 
    $verbose = 1;
}

#-------------------------------------------------------------------------------
# begin processing
#-------------------------------------------------------------------------------
PrintMsg( "I", "--- start --- Insert Support Table Records ----------" );
PrintMsg( "I", "--- POD: $POD_FILE" );

#-------------------------------------------------------------------------------
# extract $SMS_ID from POD file if it is not yet defined
#-------------------------------------------------------------------------------
if ( !defined($SMS_ID) ) {
    $msc_file = $MSC_DIR . $POD_FILE . '.pod';
    if ( !( ($SMS_ID) = Parse_SMS($msc_file) ) ) {
        exit($PROCESS_FAILURE);
    }
}
PrintMsg( "I", "--- SMS: $SMS_ID" );

#-------------------------------------------------------------------------------
# open database for queries
#-------------------------------------------------------------------------------
$db = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);

#-------------------------------------------------------------------------------
#  Use file_times table to get true time range of SMS. Always use the
#  replan time and report an info message if the replan time is not
#  equal to the start time.
#-------------------------------------------------------------------------------
$pod_name = uc($POD_FILE);    # use uppercased name for query

$query = <<"EOQ";
SELECT window_start, window_stop, replan_time 
  FROM file_times
 WHERE dataset_name = '$pod_name' AND archclass = 'MSC'
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$err_msg = "Cannot access file_times for MSC file $pod_name.";
@in_record = DoDBIselect( $db, $query );
if ( ( scalar @in_record ) == 0 ) {
    PrintMsg( "E", "Missing file_times record for MSC $pod_name." );
    exit($PROCESS_FAILURE);
}
$sms_start = DecompressTime( $in_record[2] );
$sms_stop  = DecompressTime( $in_record[1] );
$old_start = DecompressTime( $in_record[0] );
if ( $sms_start ne $old_start ) {
    PrintMsg( "I", "Replan SMS found. Original start is $old_start." );
}
PrintMsg( "I", "Start: $sms_start  End: $sms_stop." );

#-------------------------------------------------------------------------------
# Create temp table #missing from qolink_sms and qolink
#-------------------------------------------------------------------------------
$query = <<"EOQ";
SELECT DISTINCT program_id, obset_id
  INTO #missing
  FROM qolink_sms s 
 WHERE s.start_time > '$sms_start' 
   AND s.start_time < '$sms_stop'
   AND NOT EXISTS (SELECT * FROM $SPSS_DB..qolink qol 
                    WHERE program_id = s.program_id 
                      AND   obset_id = s.obset_id)
EOQ

$num_missing = DoDBI( $db, $query );
DumpTempTable( "missing", $db, $SMS_ID, $query, $verbose );
if ( $num_missing == 0 ) {
    PrintMsg( "I", "All replan obsets are found in qolink." );
} else {
    PrintMsg( "I", "The following obsets were not found in qolink." );

    #---------------------------------------------------------------------
    # Report missing obsets
    #---------------------------------------------------------------------
    $query = <<"EOQ";
SELECT * FROM #missing
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }
    $err_msg  = "Cannot query first #missing record.";
    $err_msg2 = "Cannot query next #missing record.";
    $sth = DoDBIexecute( $db, $query);
    while ( ( $program_id, $obset_id ) = DoDBIfetch( $db, $query, $sth) ) {
        PrintMsg( "I", "Missing obset $program_id$obset_id." );
    }

    #---------------------------------------------------------------------
    # Delete qolink_sms records for missing obsets
    #---------------------------------------------------------------------
    $query = <<"EOQ";
DELETE qolink_sms
  FROM qolink_sms l, #missing m
 WHERE l.program_id = m.program_id
   AND l.obset_id   = m.obset_id    
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }
    $count = DoDBI( $db, $query );
    PrintMsg( "I", "$count qolink_sms records deleted for missing obsets." );

    #---------------------------------------------------------------------
    # Delete executed records for missing obsets
    #---------------------------------------------------------------------
    $query = <<"EOQ";
DELETE executed
  FROM executed e, #missing m
 WHERE e.program_id = m.program_id
   AND e.obset_id   = m.obset_id    
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }
    $count = DoDBI( $db, $query );
    PrintMsg( "I", "$count executed records deleted for missing obsets." );
}

#-------------------------------------------------------------------------------
# Create temp table #missing_exp from qolink_sms, qolink and qobservation.
# Avoid products by checking length of ob_number field. Verify both
# qolink and qobservation records are present.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
SELECT DISTINCT s.program_id, s.obset_id, s.ob_number
  INTO #missing_exp
  FROM qolink_sms s, executed e 
 WHERE s.start_time > '$sms_start' 
   AND s.start_time < '$sms_stop'
   AND datalength(s.ob_number) = 2 
   AND e.program_id = s.program_id
   AND e.obset_id   = s.obset_id 
   AND e.ob_number  = s.ob_number
   AND NOT EXISTS (
       SELECT * FROM $SPSS_DB..qolink qol, $SPSS_DB..qobservation qob
        WHERE qol.program_id = s.program_id 
          AND qol.obset_id   = s.obset_id 
          AND qol.ob_number  = s.ob_number 
          AND qob.program_id = s.program_id 
          AND qob.obset_id   = s.obset_id 
          AND qob.ob_number  = s.ob_number 
          AND (qob.control_id != ' ' OR  qob.si_id in ('1','2','3'))
   )
EOQ

$num_miss_exp = DoDBI( $db, $query );
DumpTempTable( "missing_exp", $db, $SMS_ID, $query, $verbose );
if ( $num_miss_exp == 0 ) {
    PrintMsg( "I", "All other replan observations are found in qolink." );
} else {
    PrintMsg( "I", "The following observations were not found in qolink," );
    PrintMsg( "I", "or the corresponding qobservation record is gone." );

    #---------------------------------------------------------------------
    # Report missing exposures
    #---------------------------------------------------------------------
    $query = <<"EOQ";
SELECT * FROM #missing_exp
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }
    $err_msg  = "Cannot query first #missing_exp record.";
    $err_msg2 = "Cannot query next #missing_exp record.";
    $sth = DoDBIexecute( $db, $query);
    while ( ( $program_id, $obset_id, $ob_number ) = DoDBIfetch( $db, $query, $sth)) 
    {
        PrintMsg( "I", "Missing observation $program_id$obset_id$ob_number." );
    }

    #---------------------------------------------------------------------
    # Delete qolink_sms records for missing observations
    #---------------------------------------------------------------------
    $query = <<"EOQ";
DELETE qolink_sms
  FROM qolink_sms l, #missing_exp m
 WHERE l.program_id = m.program_id
   AND l.obset_id   = m.obset_id 
   AND l.ob_number  = m.ob_number   
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }
    $count = DoDBI( $db, $query );
    PrintMsg( "I", 
      "$count qolink_sms records deleted for missing observations." );

    #---------------------------------------------------------------------
    # Delete executed records for missing observations
    #---------------------------------------------------------------------
    $query = <<"EOQ";
DELETE executed
  FROM executed e, #missing_exp m
 WHERE e.program_id = m.program_id
   AND e.obset_id   = m.obset_id
   AND e.ob_number  = m.ob_number
   AND e.executed_flg = ' '    
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }
    $count = DoDBI( $db, $query );
    PrintMsg( "I", "$count executed records deleted for missing observations." );
}

#-------------------------------------------------------------------------------
# Delete any executed records with the SMS time that are not yet
# executed. Set replan flag if executed records were found.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DELETE executed
  FROM  $SPSS_DB..qobservation o, executed e
 WHERE o.pred_strt_tm > '$sms_start'
   AND o.pred_strt_tm < '$sms_stop'
   AND o.program_id = e.program_id
   AND o.obset_id   = e.obset_id
   AND o.ob_number  = e.ob_number
   AND e.executed_flg = ' '
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
if ( $count > 0 ) {
    PrintMsg( "I", "Extra queries will be performed for the replan." );
    PrintMsg( "I", "$count old executed records deleted." );
    $replan = 1;
} else {
    $replan = 0;
    PrintMsg( "I", "No replan, therefore no old executed records deleted." );
}

#-------------------------------------------------------------------------------
#  Update sms_id for any existing qolink_sms records that are not marked
#  with an N status.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
UPDATE qolink_sms SET sms_id = '$SMS_ID' 
 WHERE start_time > '$sms_start'
   AND start_time < '$sms_stop'
   AND status != 'N'
EOQ

$old_count = DoDBI( $db, $query );

if ( $verbose ) { print "\n------------\n", $query; }
if ( $old_count > 0 ) {
    PrintMsg( "I", "Found old qolink_sms records within this SMS time range." );
    PrintMsg( "I", "This count excludes records with status N." );
    PrintMsg( "I", "Updated sms_id for $old_count old qolink_sms records." );
} else {
    PrintMsg( "I", "Good, $old_count old qolink_sms records needed updating." );
}

#-------------------------------------------------------------------------------
#  Delete any existing qolink_sms records that are not marked
#  with an N status or have not been updated.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DELETE qolink_sms
 WHERE start_time > '$sms_start' 
   AND start_time < '$sms_stop'
   AND status != 'N' 
   AND time_type = 'P'
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
if ( $count > 0 ) {
    PrintMsg( "I", "Deleted $count old, but not updated, qolink_sms records." );
} else {
    PrintMsg( "I", "Good, found $count old, but not updated, qolink_sms records." );
}

#-------------------------------------------------------------------------------
#  Create temporary table #inst_id to provide lookup table to convert si_id to
#  single character code for the instrument. FGS use integer values to
#  distinguish the astrometry FGS number.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
CREATE TABLE #inst_id (inst varchar(1), si_id varchar(4))
CREATE UNIQUE CLUSTERED INDEX #inst_id_1 on #inst_id (si_id)
INSERT INTO #inst_id VALUES ('1','1') 
INSERT INTO #inst_id VALUES ('2','2') 
INSERT INTO #inst_id VALUES ('3','3') 
INSERT INTO #inst_id VALUES ('I','WFC3') 
INSERT INTO #inst_id VALUES ('J','ACS') 
INSERT INTO #inst_id VALUES ('L','COS') 
INSERT INTO #inst_id VALUES ('N','NIC') 
INSERT INTO #inst_id VALUES ('O','STIS') 
INSERT INTO #inst_id VALUES ('U','WFII') 
INSERT INTO #inst_id VALUES ('V','HSP') 
INSERT INTO #inst_id VALUES ('W','WFPC') 
INSERT INTO #inst_id VALUES ('X','FOC') 
INSERT INTO #inst_id VALUES ('Y','FOS') 
INSERT INTO #inst_id VALUES ('Z','HRS') 
EOQ

$count = DoDBI( $db, $query );
DumpTempTable( "inst_id", $db, $SMS_ID, $query, $verbose );
#
# Since DBI only returns the count of the last insert in this set, confirm
# that, but also do a confirmation "select count" to verify all the inserts
#
if ($count != 1) {
  PrintMsg( "W", "Problem inserting into #inst_id ?");
}
$query = <<"EOQ";
SELECT COUNT(*) FROM #inst_id
EOQ

@count = DoDBIselect( $db, $query );
PrintMsg( "I", "@count[0] records put into table #inst_id." );

#-------------------------------------------------------------------------------
# Create temp table #observ from qobservation for time range of SMS and
# set the e_present or q_present field to "/" if no executed or qolink_sms
# record is present.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
SELECT o.proposal_id,
       o.program_id,
       o.obset_id,
       o.ob_number,
       o.si_id,
       o.pred_strt_tm, 
       o.target_acqmd, 
       o.coord_id, 
       o.control_id,
       isnull(e.ob_number, '/') e_present, 
       isnull(q.ob_number,'/') q_present
  INTO #observ
  FROM  $SPSS_DB..qobservation o
  LEFT OUTER JOIN executed e
       ON o.program_id = e.program_id
       AND o.obset_id = e.obset_id
       AND o.ob_number = e.ob_number
  LEFT OUTER JOIN qolink_sms q
       ON o.program_id = q.program_id
       AND o.obset_id = q.obset_id
       AND o.ob_number = q.ob_number
  WHERE o.pred_strt_tm > '$sms_start'
   AND o.pred_strt_tm < '$sms_stop'
EOQ

$all_count = DoDBI( $db, $query );

#-------------------------------------------------------------------------------
#  Build index for efficiency
#-------------------------------------------------------------------------------
$iquery = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX #observ_1 
ON #observ (program_id,obset_id,ob_number)
EOQ

if ( $verbose ) { print "\n------------\n", $iquery; }
$dont_care = DoDBI( $db, $iquery );

## Dump after indexing safe because index query is now iquery
DumpTempTable( "observ", $db, $SMS_ID, $query, $verbose );  ## Just added 10:57am 24 Sep

PrintMsg( "I", "$all_count observations put into #observ from qobservation (& executed & qolink_sms)." );
if ( $count > 0 ) {
    my $tbl = '#observ' ;
    my $err_msg = "Cannot count number of records in $tbl for $si[1] ";

    my @si = ( 'J', 'ACS' );
    my $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'N', 'NIC' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'O', 'STIS' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    ## Want WF2 in the output, but must search on WFII
    @si = ( 'WF2', 'WFII' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[0] records now in $tbl" );

    @si = ( 'L', 'COS' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    ## Want WF3 in the output, but must search on WFC3
    @si = ( 'WF3', 'WFC3' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[0] records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert all new exposure and dump records into executed table.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
INSERT INTO executed 
SELECT program_id, 
       obset_id, 
       ob_number, 
       proposal_id, 
       si_id, 
       control_id,
       coord_id,  
       ' '
  FROM #observ
 WHERE #observ.e_present = '/'
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count records inserted into executed table from #observ." );

#-------------------------------------------------------------------------------
#  Update all old exposure and dump records for executed table.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
UPDATE executed
   SET si_id = o.si_id, 
       control_id = o.control_id, 
       coord_id   = o.coord_id
  FROM executed e, #observ o  
 WHERE o.program_id = e.program_id 
   AND o.obset_id   = e.obset_id 
   AND o.e_present  = e.ob_number 
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "Updated $count old executed records." );

#-------------------------------------------------------------------------------
# Create temporary table #obslist from #observ.
# Exclude science instrument dumps having blank control_id and si_id not
# "1","2" or "3" (i.e. not astrometry).
#-------------------------------------------------------------------------------
$query = <<"EOQ";
SELECT o.proposal_id,
       o.program_id,
       o.obset_id,
       o.ob_number,
       l.alignment_id,
       l.exposure_id,
       l.version_num,
       o.si_id,
       o.pred_strt_tm,
       o.target_acqmd,
       i.inst,
       o.q_present present
  INTO #obslist
  FROM #observ o, $SPSS_DB..qolink l, #inst_id i
 WHERE NOT (    o.si_id != '1' 
            AND o.si_id != '2' 
            AND o.si_id != '3' 
            AND o.control_id = ' '
       )
   AND l.proposal_id = o.proposal_id 
   AND l.program_id  = o.program_id 
   AND l.obset_id    = o.obset_id
   AND l.ob_number   = o.ob_number 
   AND i.si_id       = o.si_id 
ORDER BY o.program_id, o.obset_id, o.ob_number
EOQ

$count = DoDBI( $db, $query );
DumpTempTable( "obslist", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count non-dump observations joined into #obslist." );

if ( $count > 0 ) {
    my $tbl = '#obslist' ;
    my $err_msg = "Cannot count number of records in $tbl for $si[1] ";

    my @si = ( 'J', 'ACS' );
    my $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'N', 'NIC' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'O', 'STIS' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    ## Want WF2 in the output, but must search on WFII
    @si = ( 'WF2', 'WFII' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'L', 'COS' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    ## Want WF3 in the output, but must search on WFC3
    @si = ( 'WF3', 'WFC3' );
    $query = count_si_in_tbl_QueryS( $si[1], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert new qolink_sms records using new sms_id for all observations
#  and set status to U. We will reset status of STIS and COS
#  member exposures later.
#  First we open another DB session. Then, we query data for each record.
#  After start and end times are computed, the qolink_sms record is
#  inserted, or updated if the time_type is not A. If existing record
#  is updated, its status is reset to U, since the N status was for the
#  previous SMS..
#

#  Open second DB session for data to insert qolink_sms records
#-------------------------------------------------------------------------------
PrintMsg( "I", "Computing times for both old and new observations." );
$db2 = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);

#-------------------------------------------------------------------------------
#  Query first array of fields needed by qolink_sms
#-------------------------------------------------------------------------------
$query = <<"EOQ";
SELECT l.program_id,
       l.obset_id,
       l.ob_number,
       l.inst,
       l.pred_strt_tm,
       q.opmode,
       convert(int, q.exptime),
       l.present
  FROM #obslist l, $SPSS_DB..qelogsheet q
 WHERE l.proposal_id  = q.proposal_id 
   AND l.obset_id     = q.obset_id
   AND l.alignment_id = q.alignment_id 
   AND l.exposure_id  = q.exposure_id 
   AND l.version_num  = q.version_num 
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$err_msg   = "Cannot query qelogsheet.exptime.";
$err_msg2  = "Cannot query next qelogsheet.exptime.";
$sth = DoDBIexecute( $db, $query);

#-------------------------------------------------------------------------------
#  Insert or update qolink_sms records after calculating start and end
#  times. Do not update records having time_type=A.
#-------------------------------------------------------------------------------
$upd_count = 0;
$ins_count = 0;
while ( @in_record = DoDBIfetch( $db, $query, $sth) ) {
    $program_id = $in_record[0];
    $obset_id   = $in_record[1];
    $ob_number  = $in_record[2];
    $inst       = $in_record[3];
    $pred_start = $in_record[4];
    $opmode     = $in_record[5];
    $exptime    = $in_record[6];
    $present    = $in_record[7];
    ( $start_time, $end_time ) =
      ComputeTimes( $inst, $pred_start, $opmode, $exptime );

    if ( $present eq "/" ) {
        $query = <<"EOQ";
INSERT INTO qolink_sms 
VALUES ('$program_id', '$obset_id', '$ob_number', '$SMS_ID', 'U', '$inst', 
   ' ',' ',' ',' ',' ',' ',' ',' ', '$start_time', '$end_time', 'P')
EOQ

        if ( $verbose ) { print "\n------------\n", $query; }
        $count = DoDBI( $db2, $query );
        if ( $count == 0 ) {
            PrintMsg( "W", "Failed to insert qolink_sms for $program_id $obset_id $ob_number." );
        }
        $ins_count += $count;
    } else {
        $query = <<"EOQ";
SELECT COUNT(*) FROM qolink_sms 
WHERE program_id = '$program_id' 
  AND obset_id   = '$obset_id' 
  AND ob_number  = '$ob_number' 
  AND time_type != 'A' 
EOQ
        if ( $verbose ) { print "\n------------\n", $query; }
        @any_found = DoDBIselect( $db2, $query );
        if (@any_found[0] != 0) {
           $query = <<"EOQ";
UPDATE qolink_sms 
SET start_time = '$start_time', 
      end_time = '$end_time',
        sms_id = '$SMS_ID', 
        status = 'U'
WHERE program_id = '$program_id' 
  AND obset_id   = '$obset_id' 
  AND ob_number  = '$ob_number' 
  ANd time_type != 'A' 
EOQ

           if ( $verbose ) { print "\n------------\n", $query; }
           $count = DoDBI( $db2, $query );
           if ( $count == 0 ) {
               PrintMsg( "W", "Failed to update qolink_sms for $program_id $obset_id $ob_number." );
               PrintMsg( "W", "Update failure may be due to time_type = A which is OK." );
           }
           $upd_count += $count;
        }
    }
}
PrintMsg( "I", "$ins_count new exposure records inserted into qolink_sms from #obslist,qelogsheet." );
PrintMsg( "I", "$upd_count qolink_sms records updated for planned time from #obslist,qelogsheet?" );

#-------------------------------------------------------------------------------
#  Close second DB session
#-------------------------------------------------------------------------------
DoDBIclose($db2);

#-------------------------------------------------------------------------------
#  Update qolink_sms.taq and .ocx_expected for target_acqmd="01". This
# is only needed for new records.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
UPDATE qolink_sms 
   SET taq = 'Y', ocx_expected = 'Y'
  FROM qolink_sms, #obslist
 WHERE #obslist.target_acqmd = '01' 
   AND #obslist.present = '/' 
   AND qolink_sms.program_id = #obslist.program_id 
   AND qolink_sms.obset_id   = #obslist.obset_id 
   AND qolink_sms.ob_number  = #obslist.ob_number 
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count new mode 01 target acquisition exposures in SMS accd'g to qolink_sms,#obslist." );

#-------------------------------------------------------------------------------
#  Update qolink_sms.taq only for target_acqmd="02". This is only needed
#  for new records.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
UPDATE qolink_sms SET taq = 'Y'
  FROM qolink_sms, #obslist
 WHERE #obslist.target_acqmd = '02' 
   AND #obslist.present = '/' 
   AND qolink_sms.program_id = #obslist.program_id 
   AND qolink_sms.obset_id   = #obslist.obset_id 
   AND qolink_sms.ob_number  = #obslist.ob_number 
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count new mode 02 target acquisition exposures in SMS accd'g to qolink_sms,#obslist." );

#-------------------------------------------------------------------------------
#  Create temporary table #datasets with an outerjoin of #obslist and
#  qeassociation. Values from qeassociation for exposures that are not in
#  qeassociation will be null.
#-------------------------------------------------------------------------------

$query = <<"EOQ";
SELECT l.program_id,
       l.obset_id,
       l.ob_number,
       a.association_id,
       a.si_name,
       a.collect,
       a.exp_type,
       start_time = l.pred_strt_tm,
       l.inst,
       l.present
  INTO #datasets
  FROM #obslist l
  LEFT OUTER JOIN $SPSS_DB..qeassociation a
       ON l.proposal_id  = a.proposal_id
       AND l.obset_id = a.obset_id
       AND l.alignment_id = a.alignment_id
       AND l.exposure_id = a.exposure_id
       AND l.version_num = a.version_num
  ORDER BY l.program_id, l.obset_id, l.ob_number 
EOQ

$count = DoDBI( $db, $query );
DumpTempTable( "datasets", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count outerjoin records for #obslist,qeassociation to create #datasets." );

#-------------------------------------------------------------------------------
#  Drop unneeded temporary tables
#  Create the temporary table #new_asn_list with a record for every unique
#  association having a new collected member in this SMS.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DROP TABLE #observ
DROP TABLE #obslist
DROP TABLE #inst_id
SELECT program_id, 
       association_id, 
       si_name, 
       last_time = MAX(start_time) 
  INTO #new_asn_list
  FROM #datasets
 WHERE collect = 'Y' AND present = '/'
GROUP BY program_id, association_id, si_name
EOQ

$count = DoDBI( $db, $query );

#-------------------------------------------------------------------------------
#  Create an index for this table
#-------------------------------------------------------------------------------
$iquery = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX #new_asn_1 
ON #new_asn_list (program_id, association_id, si_name)
EOQ

if ( $verbose ) { print "\n------------\n", $iquery; }
$dont_care = DoDBI( $db, $iquery );

## Dump after indexing safe because index query is now iquery
DumpTempTable( "new_asn_list", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count new associations in this SMS put into #new_asn_list (from #datasets)." );

#-------------------------------------------------------------------------------
#  if this is a replan get list of old associations
#-------------------------------------------------------------------------------
if ($replan) {
    $query = <<"EOQ";
SELECT program_id,  
       association_id,  
       si_name,  
       last_time = MAX(start_time) 
  INTO #old_asn_list
  FROM #datasets d 
 WHERE collect = 'Y' AND present != '/' 
   AND NOT EXISTS ( SELECT * 
                      FROM #new_asn_list n
                     WHERE n.program_id = d.program_id 
                       AND n.association_id = d.association_id
       )
GROUP BY program_id, association_id, si_name
EOQ

    $count = DoDBI( $db, $query );
    DumpTempTable( "old_asn_list", $db, $SMS_ID, $query, $verbose );
    PrintMsg( "I", "$count old associations in this replan SMS put into #old_asn_list (from #datasets)." );
}

#-------------------------------------------------------------------------------
#  Delete new asn_association table records
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DELETE asn_association
  FROM asn_association a, #new_asn_list l
 WHERE a.association_id = l.association_id
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count old records deleted from asn_association (if in #new_asn_list)." );

#-------------------------------------------------------------------------------
#  Use #new_asn_list to add asn_association records
#-------------------------------------------------------------------------------
$query = <<"EOQ";
INSERT INTO asn_association 
      (association_id, si_name, last_exp_date, collect_date)
SELECT association_id, si_name, last_time, ' '
  FROM #new_asn_list
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count new records inserted into asn_association from #new_asn_list." );

#-------------------------------------------------------------------------------
#  Delete new asn_member table records
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DELETE asn_members
  FROM asn_members m, #new_asn_list l
 WHERE m.association_id = l.association_id
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count old records deleted from asn_members (if in #new_asn_list)." );

#-------------------------------------------------------------------------------
#  Delete new asn_member_product table records
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DELETE asn_product_link
  FROM asn_product_link p, #new_asn_list l
 WHERE p.program_id = SUBSTRING(l.association_id, 2, 3) 
   AND p.asn_obset_id = SUBSTRING(l.association_id, 5, 2)
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count old records deleted from asn_product_link (if in #new_asn_list)." );

#-------------------------------------------------------------------------------
#  Insert into the asn_members all the new collect=Y exposure members
#  setting status = 'U'.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
INSERT INTO asn_members (
  association_id, 
  program_id, 
  obset_id, 
  member_num,
  member_type, 
  member_status, 
  product_status
)
SELECT d.association_id, 
       d.program_id, 
       d.obset_id, 
       d.ob_number, 
       d.exp_type, 
       'U', 
       'E'
  FROM #datasets d, #new_asn_list l
 WHERE d.collect = 'Y' 
   AND d.association_id = l.association_id
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count exposure records inserted into asn_members (from #datasets join w/#new_asn_list)" );

#-------------------------------------------------------------------------------
#  Create a temporary table containing NICMOS products, ACS subproducts, and
#  WFC3 products.  #nic_acs_prod contains program_id,obset_id,ob_number 
#  (3-char for product), inst
#-------------------------------------------------------------------------------
$query = <<"EOQ";
SELECT d.program_id,
       obset_id  = SUBSTRING(d.association_id, 5, 2),
       ob_number = SUBSTRING(d.association_id, 7, 2) + p.product_id,
       d.inst, 
       member_type = ('PROD-'+SUBSTRING(d.exp_type, 5, 8)), 
       d.exp_type,
       d.association_id 
  INTO #nic_acs_prod
  FROM #datasets d, product_code p, #new_asn_list l
 WHERE d.inst IN ( 'N', 'J', 'I' ) 
   AND d.collect = 'Y' 
   AND d.association_id = l.association_id 
   AND p.si_name  = d.si_name 
   AND p.exp_type = d.exp_type
GROUP BY d.program_id, d.association_id, d.inst, p.product_id, d.exp_type
EOQ

$count = DoDBI( $db, $query );
DumpTempTable( "nic_acs_prod", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count new products in #nic_acs_prod." );

if ( $count > 0 ) {
    my $tbl = '#nic_acs_prod' ;
    my $err_msg = "Cannot count number of records in $tbl for $si[1] ";

    my @si = ( 'J', 'ACS' );
    my $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si  = ( 'N', 'NIC' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'I', 'WF3' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Create a temporary table containing ACS and WFC3 dither products.
#  #acs_dither contains program_id,obset_id, and ob_number (ending in 0).
#  ACS dither products occur when there is only one exp_type
#  called EXP-DTH which is already taken care of in the product_code table,
#  or when there is more than one different exp_type such as EXP-CR1 and
#  EXP-CR2. This table contains the associations having multiple products.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
SELECT n.program_id, 
       n.obset_id, 
       ob_number = SUBSTRING(n.ob_number, 1, 2) + '0', 
       n.inst, 
       n.association_id 
  INTO #acs_dither
  FROM #nic_acs_prod n
 WHERE n.inst IN ( 'J', 'I' )
GROUP BY n.program_id,
         n.obset_id,
         SUBSTRING(n.ob_number, 1, 2),
         n.inst,
         n.association_id
HAVING COUNT(*) > 1
EOQ
$count = DoDBI( $db, $query );
DumpTempTable( "acs_dither", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count dither products put into #acs_dither from #nic_acs_prod." );

if ( $count > 0 ) {
    my $tbl = '#acs_dither' ;
    my $err_msg = "Cannot count number of records in $tbl for $si[1] ";

    my @si  = ( 'J', 'ACS' );
    my $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose ); 
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'I', 'WF3' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose ); 
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert ACS or WF3 dither products in #nic_acs_prod table
#-------------------------------------------------------------------------------
$query = <<"EOQ";
INSERT INTO #nic_acs_prod (
  program_id, 
  obset_id, 
  ob_number, 
  inst, 
  member_type, 
  exp_type, 
  association_id
) 
SELECT program_id, 
       obset_id, 
       ob_number, 
       inst, 
       'PROD-DTH', 
       '*', 
       association_id
  FROM #acs_dither
EOQ

$count = DoDBI( $db, $query );
DumpTempTable( "nic_acs_prod", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count new dither products added to #nic_acs_prod." );

if ( $count > 0 ) {
    my $tbl = '#nic_acs_prod' ;
    my $err_msg = "Cannot count number of records in $tbl for $si[1] ";

    my @si = ( 'J', 'ACS' );
    my $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'I', 'WF3' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  delete from qolink_sms records old NICMOS, WFC3, or ACS product records
#  precautionary - expecting no deletions - no report line
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DELETE qolink_sms
  FROM qolink_sms, #nic_acs_prod 
 WHERE qolink_sms.program_id = #nic_acs_prod.program_id 
   AND qolink_sms.obset_id   = #nic_acs_prod.obset_id 
   AND qolink_sms.ob_number  = #nic_acs_prod.ob_number
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$dont_care = DoDBI( $db, $query );

#-------------------------------------------------------------------------------
#  Insert the NICMOS, ACS, and WFC3 product records into qolink_sms
#-------------------------------------------------------------------------------
## Zero out the counts
my $count  = 0;
my $ncount = 0;
my $jcount = 0;
my $icount = 0;

$query = create_qls_InsertQuery( 'J', 'ACS', $SMS_ID, $verbose ); 
$jcount = DoDBI( $db, $query );

$query = create_qls_InsertQuery( 'N', 'NIC', $SMS_ID, $verbose ); 
$ncount = DoDBI( $db, $query );

$query = create_qls_InsertQuery( 'I', 'WF3', $SMS_ID, $verbose ); 
$icount = DoDBI( $db, $query );

$count = $ncount + $jcount + $icount ;
PrintMsg( "I", "$count new subproducts inserted into qolink_sms from #nic_acs_prod." );
if ( $count > 0 ) {
    my $tbl = "qolink_sms";
    PrintMsg( "I", "... $jcount ACS subproducts now in $tbl" );
    PrintMsg( "I", "... $ncount NIC products now in $tbl" );
    PrintMsg( "I", "... $icount WF3 subproducts now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert the NICMOS, ACS, and WFC3 product records into asn_members
#-------------------------------------------------------------------------------
## Zero out the counts
$count  = 0;
$ncount = 0;
$jcount = 0;
$icount = 0;

$query = create_asm_InsertQuery( 'J', 'ACS', '', $verbose ) ;
$jcount = DoDBI( $db, $query );

$query = create_asm_InsertQuery( 'N', 'NIC', '', $verbose ) ;
$ncount = DoDBI( $db, $query );

$query = create_asm_InsertQuery( 'I', 'WF3', '', $verbose ) ;
$icount = DoDBI( $db, $query );

$count = $ncount + $jcount + $icount ;
PrintMsg( "I", "$count new products inserted into asn_members from #nic_acs_prod." );
if ( $count > 0 ) {
    my $tbl = "asn_members";
    PrintMsg( "I", "... $jcount ACS subproducts now in $tbl" );
    PrintMsg( "I", "... $ncount NIC products now in $tbl" );
    PrintMsg( "I", "... $icount WF3 subproducts now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert all NICMOS, WFC3, and subproduct ACS records into asn_product_link
#-------------------------------------------------------------------------------
## Zero out the counts
$count  = 0;
$ncount = 0;
$jcount = 0;
$icount = 0;

$query = create_apl_InsertQuery( 'J', 'ACS', 'N', $verbose ); 
$jcount = DoDBI( $db, $query );

$query = create_apl_InsertQuery( 'N', 'NIC', 'N', $verbose ); 
$ncount = DoDBI( $db, $query );

$query = create_apl_InsertQuery( 'I', 'WF3', 'N', $verbose ); 
$icount = DoDBI( $db, $query );

$count = $ncount + $jcount + $icount ;
PrintMsg( "I", "$count new records inserted into asn_product_link from #nic_acs_prod jn w/ asn_members." );
if ( $count > 0 ) {
    my $tbl = "asn_product_link";
    PrintMsg( "I", "... $jcount ACS records now in $tbl" );
    PrintMsg( "I", "... $ncount NIC records now in $tbl" );
    PrintMsg( "I", "... $icount WF3 records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert ACS and WFC3 dither product records into asn_product_link
#-------------------------------------------------------------------------------
## Zero out the counts
$count  = 0;
$ncount = 0;
$jcount = 0;
$icount = 0;

$query = create_apl_InsertQuery( 'J', 'ACS', 'Y', $verbose ); 
$jcount = DoDBI( $db, $query );

$query = create_apl_InsertQuery( 'I', 'WF3', 'Y', $verbose ); 
$icount = DoDBI( $db, $query );

$count = $jcount + $icount ;
PrintMsg( "I", "$count dither records inserted into asn_product_link (#acs_dither jn w/ asn_members)." );
if ( $count > 0 ) {
    my $tbl = "asn_product_link";
    PrintMsg( "I", "... $jcount ACS records now in $tbl" );
    PrintMsg( "I", "... $icount WF3 records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  drop unneeded temporary tables
#  delete from qolink_sms records old STIS and COS product records
#  precautionary only - no report line (remove this and next comment and block)
#  (in fact, if decide on NO report line, then ok to combine two queries)
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DROP TABLE #acs_dither
DROP TABLE #nic_acs_prod
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$dont_care = DoDBI( $db, $query );

$query = create_qls_DeleteQuery( 'STIS', $verbose ); 
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count old STIS product records dropped from qolink_sms.  Expect 0." );

$query = create_qls_DeleteQuery( 'COS', $verbose ); 
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count old COS  product records dropped from qolink_sms.  Expect 0." );

#-------------------------------------------------------------------------------
#  Insert the STIS and COS product records into qolink_sms
#-------------------------------------------------------------------------------
## Zero out the counts
$count  = 0;
my $ocount = 0;
my $lcount = 0;

$query = create_qls_InsertQuery( 'O', 'STIS', $SMS_ID, $verbose );
$ocount = DoDBI( $db, $query );

$query = create_qls_InsertQuery( 'L', 'COS', $SMS_ID, $verbose );
$lcount = DoDBI( $db, $query );

$count = $ocount + $lcount ;
PrintMsg( "I", "$count additional product records inserted into qolink_sms from #new_asn_list." );
if ( $count > 0 ) {
    my $tbl = "qolink_sms";
    PrintMsg( "I", "... $ocount STIS records now in $tbl" );
    PrintMsg( "I", "... $lcount COS  records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert the STIS product records into asn_members
#-------------------------------------------------------------------------------
## Zero out the counts
$count  = 0;
$ocount = 0;
$lcount = 0;
my $pcount = 0;

$query = create_asm_InsertQuery( 'O', 'STIS', 'PRODUCT', $verbose );
$ocount = DoDBI( $db, $query );

#-------------------------------------------------------------------------------
#  Insert the COS product records into asn_members (2 types)
#-------------------------------------------------------------------------------
$query = create_asm_InsertQuery( 'L', 'COS', 'PROD-FP', $verbose );
$lcount = DoDBI( $db, $query );

$query = create_asm_InsertQuery( 'L', 'COS', 'PROD-RPT', $verbose );
$pcount = DoDBI( $db, $query );

$count = $ocount + $lcount + $pcount ;
PrintMsg( "I", "$count additional product records inserted into asn_member from #new_asn_lists." );
if ( $count > 0 ) {
    my $tbl = "asn_member";
    PrintMsg( "I", "... $ocount STIS    products now in $tbl" );
    PrintMsg( "I", "... $lcount COS FP  products now in $tbl" );
    PrintMsg( "I", "... $lcount COS RPT products now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Insert the STIS and COS records into asn_product_link
#-------------------------------------------------------------------------------
## Zero out the counts
$count  = 0;
$ocount = 0;
$lcount = 0;

$query = create_apl_InsertQuery( 'O', 'STIS', 'N', $verbose  ); 
$ocount = DoDBI( $db, $query );

$query = create_apl_InsertQuery( 'L', 'COS', 'N', $verbose ); 
$lcount = DoDBI( $db, $query );

$count = $ocount + $lcount ;
PrintMsg( "I", "$count additional records inserted into asn_product_link (#new_asn_list jn w/ asn_members)." );
if ( $count > 0 ) {
    my $tbl = "asn_product_link";
    PrintMsg( "I", "... $ocount STIS records now in $tbl" );
    PrintMsg( "I", "... $lcount COS  records now in $tbl" );
}

#-------------------------------------------------------------------------------
# Update times for association products using asn_product_link.
#
# Create temporary table containing minimum start and maximum end times over
# all exposures for each product for all associations in both new_asn_list
# and old_asn_list
#
# Query will differ depending on if this is a replan or not.
#-------------------------------------------------------------------------------
$query = create_prt_UpdateQuery( $replan, $verbose ); 
$count = DoDBI( $db, $query );
DumpTempTable( "prod_times", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count records put into table #prod_times (#new_asn_list jn w/asn_product_link, qolink_sms)." );

#-------------------------------------------------------------------------------
# drop unneeded temporary tables
#-------------------------------------------------------------------------------
if ($replan) {
    $query = <<"EOQ";
DROP TABLE #new_asn_list
DROP TABLE #old_asn_list
EOQ
} else {
    $query = <<"EOQ";
DROP TABLE #new_asn_list
EOQ
}
if ( $verbose ) { print "\n------------\n", $query; }
$dont_care = DoDBI( $db, $query );

#-------------------------------------------------------------------------------
# update product records in qolink_sms if time_type not A
#-------------------------------------------------------------------------------
if ( $count > 0 ) {
    $query = <<"EOQ";
UPDATE qolink_sms
   SET start_time = #prod_times.prod_start, 
       end_time   = #prod_times.prod_end,
       time_type  = 'P'
  FROM qolink_sms, #prod_times
 WHERE qolink_sms.program_id = #prod_times.program_id 
   AND qolink_sms.obset_id   = #prod_times.asn_obset_id 
   AND qolink_sms.ob_number  = #prod_times.member_num 
   AND qolink_sms.time_type != 'A'
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }
    $count = DoDBI( $db, $query );
}
PrintMsg( "I", "$count qolink_sms product records updated for times from #prod_times." );

if ( $count > 0 ) {
    my $tbl = 'qolink_sms' ;
    my $err_msg = "Cannot count total number of records in $tbl for $si[1] ";

    my @si = ( 'J', 'ACS' );
    my $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'N', 'NIC' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'O', 'STIS' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'U', 'WF2' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'L', 'COS' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );

    @si = ( 'I', 'WF3' );
    $query = count_si_in_tbl_Query( $si[0], $tbl, $verbose );
    my @count = DoDBIselect( $db, $query );
    PrintMsg( "I", "... $count[0] $si[1] records now in $tbl" );
}

#-------------------------------------------------------------------------------
#  Drop unneeded temporary table.
#  Create temporary table for STIS members in associations.
#  STIS calibrates collected data; members are NOT files.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
DROP TABLE #prod_times
SELECT program_id, obset_id, ob_number, max_collect = MAX(collect)
  INTO #stis_obs
  FROM #datasets
 WHERE inst = 'O' AND present = '/'
GROUP BY program_id, obset_id, ob_number
EOQ

$count = DoDBI( $db, $query );
DumpTempTable( "stis_obs", $db, $SMS_ID, $query, $verbose );
PrintMsg( "I", "$count new STIS exposures in qeassociation (create #stis_obs from #datasets)." );

#-------------------------------------------------------------------------------
#  Update qolink_sms records STIS observations that are collected in
#  qeassociation and set status to M. Drop #stis_obs after use.
#  STIS calibrates collected data; members are NOT files.
#-------------------------------------------------------------------------------
$query = <<"EOQ";
UPDATE qolink_sms 
   SET status = 'M'  
  FROM #stis_obs, qolink_sms
 WHERE #stis_obs.max_collect = 'Y' 
   AND qolink_sms.program_id = #stis_obs.program_id 
   AND qolink_sms.obset_id   = #stis_obs.obset_id 
   AND qolink_sms.ob_number  = #stis_obs.ob_number
EOQ

if ( $verbose ) { print "\n------------\n", $query; }
$count = DoDBI( $db, $query );
PrintMsg( "I", "$count STIS association members marked M in qolink_sms (updated from jn w/#stis_obs)." );

#  Drop unneeded tables
$query = <<"EOQ";
DROP TABLE #datasets
DROP TABLE #stis_obs
EOQ
$count = DoDBI( $db, $query );

#-------------------------------------------------------------------------------
# end of all queries
#-------------------------------------------------------------------------------
DoDBIclose($db);
PrintMsg( "I", "---  end  --- Insert Support Table Records ----------" );
exit($PROCESS_SUCCESS);

#-------------------------------------------------------------------------------
# end of main procedure -- subroutines follow
#-------------------------------------------------------------------------------