#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: insert_nic_saa_records
#
# This perl script verifies that the input environment variables are OK, then
# it generates a set of queries to insert nic_saa_dark and nic_saa_link records.
#
# All the records are generated using data within the replan time range of the
# SMS that is found in file_times.
#
# During processing the following temporary tables are created and dropped 
# soon after their last usage.
# Temp tables created on OPUS_DB but sometimes from SPSS_DB queries.
#               #obslist          all nicmos observations except dumps
#               #nicsaa           get nic saa data from obslist and nic_saa_exit
#               #nicsaadark       identifies nic saa darks
#
#
# Interactive Usage:
#	insert_nic_saa_records.pl  [pod_name [max_delta]]
#
#       if pod_name is missing the ENV variable OSF_DATASET is used.
#       if max_delta is missing the ENV variable NIC_SAA_MAX_DELTA is used.
#       if NIC_SAA_MAX_DELTA and max_delta are both missing, 3000 is used.
#
#	Note that the following environment variables are also 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.
#
#  Return status: 0 = success, 1 = failure 
#
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 04/20/00 40279    Baum        Initial version.
# 11/21/00 44880    Baum        Create standalone Unix version that can be
#                               passed two arguments.
# 04/29/02 45738    Baum        Use external subroutines.
# 02/11/09 61920    MSwam       Split some multi-step queries after EBF 15959
#                               seems to have altered their behavior
# 03/24/10 64274    MSwam       Replace ST_DBlib with DBI
# 06/30/10 64432    MSwam       Use single quotes for SQLServer
# 07/28/10 64432    MSwam       More single quotes (in the INSERT)
# 07/28/10 64432    MSwam       More single quotes (in the INSERT)
# 07/28/10 64432    MSwam       More single quotes (in the INSERT)
# 07/28/10 64432    MSwam       More single quotes (in the INSERT)
# 09/18/12 72255    Sherbert    keep DSQUERY in code to emphasize the sharing 
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'do_dbi_pkg.pl';         # run query returning only record count
require 'sogs_time_pkg.pl';  # manipulate time in SOGS format(yyy.ddd:hh:mm:ss) 

#specify exit status values
    $PROCESS_FAILURE = 1; # exit status for XPOLL
    $PROCESS_SUCCESS = 0; # exit status for XPOLL

#check for arguments
    $num_arg = scalar @ARGV;

    if ($num_arg > 0 && substr($ARGV[0],0,1) ne "-") {
       $POD_FILE = $ARGV[0];
       if ($num_arg > 1) {
          $NIC_SAA_MAX_DELTA = $ARGV[1];
       } else {
          $NIC_SAA_MAX_DELTA = $ENV{"NIC_SAA_MAX_DELTA"};
       }
    } else {
        $POD_FILE = $ENV{"OSF_DATASET"};
        $NIC_SAA_MAX_DELTA = $ENV{"NIC_SAA_MAX_DELTA"};
    }
    if (!defined($NIC_SAA_MAX_DELTA)) {$NIC_SAA_MAX_DELTA = 3000;}

    if ( !defined($POD_FILE)) {
       PrintMsg ("E","Missing POD name in args or environment");
       exit( $PROCESS_FAILURE);
    } 
# get the external variables
    $DSQUERY=           $ENV{"OPUS_SERVER"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});
    $SPSS_DB=           lc($ENV{"SPSS_DB"});

# begin processing
    PrintMsg ("I","--- start --- Insert NIC_SAA Table Records ----------");
    PrintMsg ("I","--- POD: $POD_FILE");
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);

    $pod_name = uc( $POD_FILE); #uppercased pod name needed for query
#---------------------------------------------------------------------
#  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.
#---------------------------------------------------------------------

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

    $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);  # EXIT_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 #observ for NICMOS observations in time range of SMS.
# Exclude dumps having blank control_id.
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT o.program_id,o.obset_id,o.ob_number,l.proposal_id,l.alignment_id,
   l.exposure_id,l.version_num
INTO #obslist
FROM  $SPSS_DB..qobservation o, $SPSS_DB..qolink l
WHERE o.pred_strt_tm>'$sms_start'
  and o.pred_strt_tm<'$sms_stop'
  and o.si_id = 'NIC'
  and o.control_id!=' '
  and l.program_id = o.program_id
  and l.obset_id = o.obset_id 
  and l.ob_number = o.ob_number
EOQ
    $count = DoDBI( $db, $query );
    PrintMsg ("I","$count NICMOS observations put into #obslist.");
#---------------------------------------------------------------------
#  Create temporary table #nicsaa for NIC SAA exit data. This is the last query
#  using spss_db tables.
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT l.program_id,l.obset_id,l.ob_number,n.saa_exit,e.config,e.targname
INTO #nicsaa
FROM #obslist l, $SPSS_DB..nic_saa_exit n, $SPSS_DB..qelogsheet e
WHERE l.program_id=n.program_id
  and l.obset_id=n.obset_id 
  and l.ob_number=n.ob_number
  and n.delta_time<$NIC_SAA_MAX_DELTA 
  and l.proposal_id=e.proposal_id 
  and l.obset_id=e.obset_id
  and l.alignment_id=e.alignment_id 
  and l.exposure_id=e.exposure_id 
  and l.version_num=e.version_num 
ORDER BY l.program_id,l.obset_id,l.ob_number
EOQ

    $nicsaa_count = DoDBI( $db, $query );
    PrintMsg("I","$nicsaa_count records for #nicsaa.");
    #---------------------------------------------------------------------
    #  Create temporary table for NIC SAA dark associations.
    #  If $nicsaa_count is zero this table will also have zero records.
    #  We create it anyway to simplify dropping.
    #---------------------------------------------------------------------

$query = <<"EOQ";
SELECT l.program_id,obset_id=l.asn_obset_id,l.member_num,n.config,
saa_exit=min(n.saa_exit)
INTO #nicsaadark
FROM #nicsaa n, asn_product_link l
WHERE n.targname='POST-SAA-DARK'
  and l.program_id=n.program_id
  and l.obset_id=n.obset_id 
  and l.ob_number=n.ob_number
GROUP BY l.program_id,l.asn_obset_id,l.member_num,n.config
EOQ

    $count = DoDBI( $db, $query );
    PrintMsg("I","$count records for #nicsaadark.");
#---------------------------------------------------------------------
#  Start conditional processing for nic saa darks.
#---------------------------------------------------------------------
    if ($nicsaa_count>0) {
        #---------------------------------------------------------------------
        #  delete old data from nic_saa_dark by association ids
        #---------------------------------------------------------------------

$query = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX #saadark_1 
ON #nicsaadark (program_id,obset_id,member_num)
EOQ
        $count = DoDBI( $db, $query );

$query = <<"EOQ";
DELETE nic_saa_dark 
FROM nic_saa_dark,#nicsaadark 
WHERE
  nic_saa_dark.program_id=#nicsaadark.program_id and
  nic_saa_dark.obset_id=#nicsaadark.obset_id and
  nic_saa_dark.member_num=#nicsaadark.member_num
EOQ

        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count records deleted by id from nic_saa_dark.");
        #---------------------------------------------------------------------
        #  Open second DB session for data to insert nic_saa_dark records
        #---------------------------------------------------------------------
        $db2 = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);

        #---------------------------------------------------------------------
        #  Select all records from #nicsaadark
        #---------------------------------------------------------------------

$query = <<"EOQ";
SELECT * FROM #nicsaadark 
EOQ

        $err_msg = "Cannot read first record from #nicsaadark";
        $err_msg2 = "Failure while reading record from #nicsaadark";
        $del_count = 0;
        $count = 0;

        $sth = DoDBIexecute( $db, $query);
        while ( ( @in_record ) = DoDBIfetch( $db, $query, $sth) ) {
          ($program_id,$obset_id,$member_num, $config, $saa_exit) = @in_record;
          #---------------------------------------------------------------------
          #  Insert records into nic_saa_dark after computing adjusted saa_hour.
          #  For each #nicsaadark record, one or two nic_saa_dark records will
          #  be generated.
          #---------------------------------------------------------------------
          $saa_hour1 = substr( AdjustTime($saa_exit, -2), 0, 11);
          $saa_hour = $saa_hour1;
          for ($i=0; $i<2; $i++) {
            if ($i==0 || $saa_hour ne $saa_hour1) {
               #----------------------------------------------------------------
               #  Delete old record from nic_saa_dark having the same hour.
               #----------------------------------------------------------------

$query = <<"EOQ";
DELETE FROM nic_saa_dark 
WHERE saa_exit_hour = '$saa_hour' 
  and config = '$config'
EOQ

               $del_count+=DoDBI( $db2, $query);
               #----------------------------------------------------------------
               #  Insert new record into nic_saa_dark.
               #----------------------------------------------------------------

$query = <<"EOQ";
INSERT INTO nic_saa_dark VALUES 
('$saa_hour','$config','$program_id','$obset_id','$member_num')
EOQ
               DoDBI( $db2, $query);
               $count++;
            }
            $saa_hour = substr( AdjustTime($saa_exit, 2), 0, 11);
          }
        }
        PrintMsg ("I","$del_count records deleted by time from nic_saa_dark.");
        PrintMsg ("I","$count records inserted into nic_saa_dark.");
        #---------------------------------------------------------------------
        #  Close second DB session
        #---------------------------------------------------------------------

        DoDBIclose($db2);
        #---------------------------------------------------------------------
        #  Delete old data from nic_saa_link
        #---------------------------------------------------------------------

$query = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX #nicsaa_1 
ON #nicsaa (program_id,obset_id,ob_number)
EOQ
        $count = DoDBI( $db, $query );

$query = <<"EOQ";
DELETE nic_saa_link 
FROM nic_saa_link,#nicsaa
WHERE
  nic_saa_link.program_id=#nicsaa.program_id and
  nic_saa_link.obset_id=#nicsaa.obset_id and
  nic_saa_link.ob_number=#nicsaa.ob_number
EOQ

        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count old records deleted from nic_saa_link.");
        #---------------------------------------------------------------------
        # Use updated nic_saa_dark table to create nic_saa_link records.
        #---------------------------------------------------------------------

$query = <<"EOQ";
INSERT INTO nic_saa_link
SELECT  s.program_id, s.obset_id, s.ob_number, d.program_id, d.obset_id,
 d.member_num, ('N'+d.program_id+d.obset_id+d.member_num)
FROM #nicsaa s, nic_saa_dark d
WHERE s.targname != 'POST-SAA-DARK' and
      d.saa_exit_hour = SUBSTRING(s.saa_exit,1,11) and
      d.config = s.config
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg("I","$count records inserted into nic_saa_link.");
        #---------------------------------------------------------------------
        # End of conditional processing for nic_saa_darks
        #---------------------------------------------------------------------
    }
#---------------------------------------------------------------------
#  We are done with temporary tables
#---------------------------------------------------------------------

$query = <<"EOQ";
DROP TABLE #obslist
DROP TABLE #nicsaa
DROP TABLE #nicsaadark
EOQ

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