#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: check_replan_msc
#
# This perl script verifies that the input environment variables are OK, then
# it generates a set of queries to check for a replan SMS and to perform other
# database validations. If a replan is detected certain control tables are
# cleaned of invalid records.
#
# During processing the following temporary tables are created.
#               #obsets           all observations from old MSC
#               #missing          obsets missing from qolink
#               #asn              missing associations
#
# Interactive Usage:
#	check_replan_msc.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 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
# -------- -------- ----------  ---------------------------------------------
# 06/22/01 43956    Baum        Initial code
# 04/29/02 45738    Baum        Use external subroutines.
# 06/14/02 46012    Baum        Fix info message for last delete query
# 02/13/03 47570    Baum        Change exit status values
# 04/11/03 48320    Baum        Add check for missing association records
# 04/11/03 48338    Baum        Mark missing exposures within extant obset
# 04/17/03 48352    Baum        Check for H&S SMS when no observations found
# 05/01/03 48515    Baum        Add sleep timer
# 08/26/03 49289    Baum        Remove records for replan exposures that have
#                               new si_id. Add control_id to missing_exp check
# 01/13/05 48735    Baum        Add verification of observation list.
# 03/25/10 64274    MSwam       Replace ST_DBlib with DBI
# 06/30/10 64432    MSwam       Use single quotes for SQLServer
# 02/17/11 67574    MSwam       Adjust a query for MSSQL complaint
# 06/14/11 68456    LThompson   handle \r (CR) in SPSS ob list files
# 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 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
    $PROCESS_ABORT   = 101; # causes XPOLL process to go absent

#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;
    }
    $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=           $ENV{"OPUS_SERVER"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});
    $SPSS_DB=           lc($ENV{"SPSS_DB"});

# get optional sleep time - check if valid number
    $SLEEP_TIME=        $ENV{"SLEEP_TIME"};
    if (!defined($SLEEP_TIME)) {
       $sleep_time=0;
    } else {
       # check if SLEEP_TIME starts with a digit
       if ($SLEEP_TIME =~/^\d/) {
         $sleep_time = 0 + $SLEEP_TIME; # change to integer
       } else {
         $sleep_time = 0;
       }
    }

# get optional max retries - check if valid number
    $MAX_RETRIES=        $ENV{"MAX_RETRIES"};
    if (!defined($MAX_RETRIES)) {
       $max_retries=0;
    } else {
       # check if MAX_RETRIES starts with a digit
       if ($MAX_RETRIES =~/^\d/) {
         $max_retries = 0 + $MAX_RETRIES; # change to integer
       } else {
         $max_retries = 0;
       }
    }

# begin processing
    PrintMsg ("I","--- start --- Check Replan MSC file ----------");
    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);

#---------------------------------------------------------------------
# select db info to verify processing is valid
#---------------------------------------------------------------------
    $query = <<"EOQ";
SELECT sms_send_stt, link_sms_id 
FROM $SPSS_DB..sms_catalog 
WHERE sms_id='$SMS_ID'
EOQ

    @in_record = DoDBIselect( $db, $query);
    $sms_status = $in_record[0];
    $link_id    = $in_record[1];

    if (!defined($sms_status) || $sms_status eq ' ' || $sms_status eq '') {
      PrintMsg ("E","This sms_id $SMS_ID has no SMS baselined in sms_catalog.");
      DoDBIclose($db);
      exit( $PROCESS_FAILURE);  
    }
    if ($sms_status eq 'R') {
      PrintMsg ("E","This sms_id $SMS_ID is not the name of the");
      PrintMsg ("E","   current SMS baselined in sms_catalog table.");
      DoDBIclose($db);
      exit( $PROCESS_FAILURE);  
    }
    $cold_start = $link_id eq "default";
    $pod_name = uc( $POD_FILE );  # use uppercased name for query

#---------------------------------------------------------------------
#  Use observation list file transfered from SPSS to verify the
#  the replicated qobservation records in SPSS_DB are complete.
#---------------------------------------------------------------------
    $list_file = $MSC_DIR.$POD_FILE.'.lis';
    
    if (-e $list_file) {
        CheckObserveList( $SMS_ID, $list_file, $sleep_time, $max_retries) ;
    } else {
        PrintMsg ("W","No observation list: $list_file.");
        PrintMsg ("I","Cannot verify replication of SPSS support schedule.");
    }
#---------------------------------------------------------------------
#  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);
    $window_start = $in_record[0];
    $window_stop  = $in_record[1];
    $replan_time  = $in_record[2];
    if (!defined($window_start)) {
      PrintMsg ("E","Missing file_times record for MSC $pod_name.");
      DoDBIclose($db);
      exit ( $PROCESS_FAILURE);  
    }

#---------------------------------------------------------------------
#  Validate times from file_times
#---------------------------------------------------------------------
    ValidateTime("window_start",$window_start);
    ValidateTime("window_stop", $window_stop);
    ValidateTime("replan_time", $replan_time);

#---------------------------------------------------------------------
#  Decompress and report times from file_times
#---------------------------------------------------------------------
    $sms_start = DecompressTime($replan_time);
    $sms_stop  = DecompressTime($window_stop);
    $old_start = DecompressTime($window_start);
    if ($sms_start ne $old_start) {
      PrintMsg ("I","Replan SMS found. Original start is $old_start.");
    }
    PrintMsg ("I","Start: $sms_start  End: $sms_stop.");

#---------------------------------------------------------------------
#  Report last planned observation
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT MAX(ob_start_tim) 
FROM $SPSS_DB..qolink
EOQ
    @last_plan_time = DoDBIselect( $db, $query);
    if (!defined(@last_plan_time)) {
      PrintMsg ("E","Failed to find MAX(ob_start_tim) in qolink");
      DoDBIclose($db);
      exit ( $PROCESS_FAILURE);  
    }
    PrintMsg ("I","Last planned observation at $last_plan_time[0].");

#---------------------------------------------------------------------
#  Report type of delivery - normal or late
#---------------------------------------------------------------------
    $cur_date = CurDate();
    if ($sms_start gt $cur_date) {
      PrintMsg ("I","SMS delivery processing time is normal.");
    } else {
      PrintMsg ("W","SMS delivery processing time is after SMS start.");
    }

#---------------------------------------------------------------------
#  Report last executed observation
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT MAX(start_time) 
FROM qolink_sms l, executed ex
WHERE ex.program_id = l.program_id and
      ex.obset_id = l.obset_id and
      ex.ob_number = l.ob_number and
      ex.executed_flg !=' '  
EOQ

    @last_exec_time = DoDBIselect( $db, $query);
    if (!defined(@last_exec_time)) {
      PrintMsg ("E", "Cannot get last executed time using qolink_sms and executed.");
      DoDBIclose($db);
      exit ( $PROCESS_FAILURE);  
    }
    PrintMsg ("I","Last executed observation at $last_exec_time[0].");
    if ($last_exec_time[0] gt $sms_start) {
      PrintMsg ("W","Executed observation found after SMS start time.");
    }

#---------------------------------------------------------------------
#  Stop if MCS file has no observations and is not a cold_start SMS
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT COUNT(*) 
FROM $SPSS_DB..qobservation
where  pred_strt_tm > '$sms_start' and pred_strt_tm< '$sms_stop'
EOQ

    $err_msg = "Cannot access spss_db..qobservation table within SMS times.";
    @count = DoDBIselect( $db, $query);

    if ($count[0] == 0) {
      if ($cold_start) {
        PrintMsg ("I","No qobservation records in cold_start SMS");
	PrintMsg ("I","Assumming this is a H&S SMS - continuing");
      } else {
        PrintMsg ("E","No qobservations records for SMS time range.");
        PrintMsg ("I","If this is a valid SMS, just archive the POD file."); 
        DoDBIclose($db);
        exit( $PROCESS_FAILURE);
      }
    }

#---------------------------------------------------------------------
#  Stop if missing qolink or qobservation records are detected for
#  associations.
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT DISTINCT association_id
INTO #asn_list
FROM $SPSS_DB..qobservation o, $SPSS_DB..qolink l, $SPSS_DB..qeassociation a
WHERE  o.pred_strt_tm > '$sms_start' and o.pred_strt_tm< '$sms_stop'
  and l.program_id = o.program_id and l.obset_id = o.obset_id 
  and l.ob_number = o.ob_number
  and a.proposal_id = l.proposal_id and a.obset_id = l.obset_id
  and a.alignment_id = l.alignment_id and a.exposure_id = l.exposure_id
  and a.version_num = l.version_num and a.collect='Y'
EOQ

      $asn_count = DoDBI( $db, $query );
      if ($asn_count == 0) {
         PrintMsg ("I","No associations found in time range");
      } else {
         PrintMsg ("I","Checking $asn_count associations for missing records");

$query = <<"EOQ";
SELECT DISTINCT proposal_id,obset_id,alignment_id,exposure_id,version_num
INTO #asn_exp
FROM #asn_list l, $SPSS_DB..qeassociation a
WHERE  a.association_id = l.association_id and a.collect='Y' and 
       version_num = '01' 
EOQ

         $full_count = DoDBI( $db, $query);
         PrintMsg ("I","Found $full_count qeassociation exposure records");

$query = <<"EOQ";
SELECT count(*)
FROM #asn_exp asn, $SPSS_DB..qolink qol, $SPSS_DB..qobservation qob
WHERE qol.proposal_id=asn.proposal_id and qol.obset_id=asn.obset_id
   and qol.alignment_id=asn.alignment_id and qol.exposure_id=asn.exposure_id
   and qol.version_num = asn.version_num
   and qob.program_id = qol.program_id and qob.obset_id=qol.obset_id
   and qob.ob_number = qol.ob_number 
EOQ
 
         $err_msg = "Cannot join #asn_exp and qobservation.";
         @test_count = DoDBIselect( $db, $query );
         PrintMsg ("I","Found $test_count[0] related qobservation records");

         if ($test_count[0] != $full_count) {
	   $num_missing = $full_count - $test_count[0];
           PrintMsg ("E",
	     "Missing $num_missing records from qolink or qobservation.");
	     
#---------------------------------------------------------------------
# Report missing asn records
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT proposal_id,obset_id,alignment_id,exposure_id 
FROM #asn_exp asn
WHERE NOT EXISTS (SELECT * FROM $SPSS_DB..qolink qol, $SPSS_DB..qobservation qob
                  WHERE qol.proposal_id=asn.proposal_id 
		    and qol.obset_id=asn.obset_id
                    and qol.alignment_id=asn.alignment_id 
		    and qol.exposure_id=asn.exposure_id
                    and qol.version_num = asn.version_num
                    and qob.program_id = qol.program_id 
		    and qob.obset_id=qol.obset_id
                    and qob.ob_number = qol.ob_number)
EOQ

           $err_msg = "Cannot query first missing #asn_exp record.";
           $err_msg2 = "Cannot query next missing #asn_exp record.";

           $sth = DoDBIexecute( $db, $query);
           while ( ($prop_id, $obs_id, $align_id, $exp_id) = DoDBIfetch( $db, $query, $sth) ) {
               PrintMsg ("I",
                 "Missing SPSS exposure $prop_id $obs_id $align_id $exp_id.");
	   }
           DoDBIclose($db);
           exit( $PROCESS_FAILURE);
	 }
      }

#---------------------------------------------------------------------
# Check for overlapping time in file_times due to replan or
# redeliveries. Additional cleanup required if true.
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT COUNT(*) 
FROM file_times
where archclass='MSC' and window_start<='$replan_time' and 
window_stop>'$replan_time'
EOQ

    $err_msg = "Cannot access file_times table for overlap.";
    @sms_count = DoDBIselect( $db, $query);
    if ($sms_count[0] < 2 ) {
      PrintMsg ("I","No overlapping POD files in file_times. Done.");
    } else {
      PrintMsg ("I","Found multiple POD files in file_times at $replan_time.");

#---------------------------------------------------------------------
# Create #badexp table for all exposures in executed that map to
# qobservation data for different instrument
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT e.program_id,e.obset_id,e.ob_number
INTO #badexp
FROM executed e, $SPSS_DB..qobservation o
WHERE  o.pred_strt_tm > '$sms_start' and o.pred_strt_tm< '$sms_stop'
  and e.program_id = o.program_id and e.obset_id = o.obset_id 
  and e.ob_number = o.ob_number
  and e.si_id != o.si_id
EOQ
      $bad_count = DoDBI( $db, $query );
      if ($bad_count == 0) {
         PrintMsg ("I","No instrument reassignments of replanned exposures.");
      } else {
          PrintMsg ("I","The following replanned exposures have new si_id.");

#---------------------------------------------------------------------
# Report bad observations
#---------------------------------------------------------------------

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

          $err_msg = "Cannot query first #badexp record.";
          $err_msg2 = "Cannot query next #badexp record.";

          $sth = DoDBIexecute( $db, $query);
          while ( ($program_id, $obset_id, $ob_number) = DoDBIfetch( $db, $query, $sth) ) {
            PrintMsg ("I","Purging observation $program_id$obset_id$ob_number.");
          }
          PrintMsg ("I","Found $bad_count exposures with reassigned si_id.");
#---------------------------------------------------------------------
#  Delete product_status records for bad exposures for any product_type
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE product_status
FROM product_status s, dataset_link l, #badexp b
WHERE s.product_rootname = l.dataset_rootname and
  l.program_id = b.program_id and
  l.obset_id = b.obset_id and
  l.ob_number = b.ob_number
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count product_status records deleted for bad si_id.");

#---------------------------------------------------------------------
#  Delete product_eng_map records for bad exposures for any
#  product_type
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE product_eng_map
FROM product_eng_map e, dataset_link l, #badexp b
WHERE e.product_rootname = l.dataset_rootname and
  l.program_id = b.program_id and
  l.obset_id = b.obset_id and
  l.ob_number = b.ob_number
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count product_eng_map records deleted for bad si_id.");

#---------------------------------------------------------------------
#  Delete jitter_evt_map records for bad exposures
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE jitter_evt_map
FROM jitter_evt_map j, #badexp b
WHERE 
  j.program_id = b.program_id and
  j.obset_id = b.obset_id and
  j.ob_number = b.ob_number
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count jitter_evt_map records deleted for bad si_id.");

#---------------------------------------------------------------------
#  Delete dataset_link records for bad exposures
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE dataset_link
FROM dataset_link l, #badexp b
WHERE 
  l.program_id = b.program_id and
  l.obset_id = b.obset_id and
  l.ob_number = b.ob_number
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count dataset_link records deleted for bad si_id.");

#---------------------------------------------------------------------
#  Delete asn_members records for bad exposures
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE asn_members
FROM asn_members m, #badexp b
WHERE 
  m.program_id = b.program_id and
  m.obset_id = b.obset_id and
  m.member_num = b.ob_number
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count asn_members deleted for bad si_id.");

#---------------------------------------------------------------------
#  Delete executed records for bad exposures
#---------------------------------------------------------------------
$query = <<"EOQ";
DELETE executed
FROM executed e, #badexp b
WHERE 
  e.program_id = b.program_id and
  e.obset_id = b.obset_id and
  e.ob_number = b.ob_number and
  e.executed_flg = ' '
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count executed records deleted for bad si_id.");

#---------------------------------------------------------------------
#  Delete qolink_sms records for bad exposures
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE qolink_sms
FROM qolink_sms q, #badexp b
WHERE 
  q.program_id = b.program_id and
  q.obset_id = b.obset_id and
  q.ob_number = b.ob_number and
  q.status NOT IN ('E','N')
EOQ
        $count = DoDBI( $db, $query );
        PrintMsg ("I","$count qolink_sms records deleted for bad si_id.");

      }  # end of badexp processing
#---------------------------------------------------------------------
# Create temp table #obsets from product_eng_map
#---------------------------------------------------------------------

      $adjusted_start = AdjustTime( $sms_start, -60);
      $adjusted_start=~s/^(....)\.(...):(..).+$/T$1$2$3/;
      $adjusted_start=$adjusted_start."00";
      $adjusted_stop = AdjustTime( $sms_stop, 60);
      $adjusted_stop =~s/^(....)\.(...):(..).+$/T$1$2$3/;
      $adjusted_stop =$adjusted_stop."00";
      $num_missing = 0;  # initialize
      $num_miss_exp = 0; # initialize
      
$query = <<"EOQ";
SELECT DISTINCT l.program_id,l.obset_id
INTO #obsets
FROM dataset_link l, product_eng_map m
WHERE m.eng_rootname >= '$adjusted_start' and m.eng_rootname <='$adjusted_stop'
and l.dataset_rootname = m.product_rootname
EOQ
      $obs_count = DoDBI( $db, $query );
      if ($obs_count == 0) {
         PrintMsg ("I","No old obsets found in control tables");
      } else {
         PrintMsg ("I","Checking $obs_count old obsets found in control tables");
#---------------------------------------------------------------------
# Create temp table #missing from #obsets and qolink
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT program_id,obset_id
INTO #missing
FROM #obsets 
WHERE NOT EXISTS (SELECT * FROM $SPSS_DB..qolink
                  WHERE program_id = #obsets.program_id and
                        obset_id = #obsets.obset_id)
EOQ

        $num_missing = DoDBI( $db, $query );
        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

          $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","Purging obset $program_id$obset_id.");
          }

#---------------------------------------------------------------------
# Create #asn for missing associations
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT DISTINCT a.association_id
INTO #asn
FROM #missing m, asn_members a
WHERE a.program_id = m.program_id
  and a.obset_id = m.obset_id 
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count invalid associations.");
#---------------------------------------------------------------------
# Delete invalid associations
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE asn_association
FROM asn_association a, #asn m
WHERE a.association_id = m.association_id
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count asn_association records deleted.");

#---------------------------------------------------------------------
# Delete invalid asn_members
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE asn_members
FROM asn_members a, #asn m
WHERE a.association_id = m.association_id
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count asn_members records deleted.");
#---------------------------------------------------------------------
# Delete invalid asn_product_link and drop #asn table
#---------------------------------------------------------------------

$query = <<"EOQ";
DROP TABLE #asn
DELETE asn_product_link
FROM asn_product_link a, #missing m
WHERE a.program_id = m.program_id
  and a.obset_id = m.obset_id
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count asn_product_link records deleted.");

#---------------------------------------------------------------------
# Delete invalid jitter_evt_map records
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE jitter_evt_map
FROM jitter_evt_map j, #missing m
WHERE j.program_id = m.program_id
  and j.obset_id = m.obset_id
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count jitter_evt_map records deleted.");

#---------------------------------------------------------------------
# Delete invalid product_status records
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE product_status
FROM product_status p, dataset_link l, #missing m
WHERE l.program_id = m.program_id
  and l.obset_id = m.obset_id
  and p.product_rootname = l.dataset_rootname
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count product_status records deleted.");

#---------------------------------------------------------------------
# Delete invalid dataset_link records
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE dataset_link
FROM dataset_link l, #missing m
WHERE l.program_id = m.program_id
  and l.obset_id = m.obset_id
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count dataset_link records deleted.");
        }  # end if num_missing=0
		  
#---------------------------------------------------------------------
# Create temp table #missing_exp from #obsets, dataset_link, qolink
# and qobservation and add control_id check
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT l.program_id,l.obset_id,l.ob_number
INTO #missing_exp
FROM #obsets o, dataset_link l
WHERE l.program_id = o.program_id and l.obset_id=o.obset_id and
   l.dataset_type = 'FGS' and NOT EXISTS (
      SELECT * FROM $SPSS_DB..qolink qol,$SPSS_DB..qobservation qob
      WHERE qol.program_id = l.program_id and
            qol.obset_id = l.obset_id and
	    qol.ob_number = l.ob_number and
            qob.program_id = l.program_id and
            qob.obset_id = l.obset_id and
	    qob.ob_number = l.ob_number and
	    (qob.control_id != ' ' or qob.si_id in ('1','2','3'))
   )
EOQ

        $num_miss_exp = DoDBI( $db, $query );
        if ($num_miss_exp  == 0) {
          PrintMsg ("I","All remaining replan observations are found in qolink.");
        } else {
          PrintMsg ("I","The following observations were not found in qolink.");

#---------------------------------------------------------------------
# Report missing observations
#---------------------------------------------------------------------

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

          $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","Purging observation $program_id$obset_id$ob_number.");
          }

#---------------------------------------------------------------------
#  Delete invalid product_eng_map records for observations
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE product_eng_map
FROM product_eng_map p, dataset_link 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
  and p.product_rootname = l.dataset_rootname
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count observation product_eng_map records deleted.");

#---------------------------------------------------------------------
#  Delete invalid product_status records for observations
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE product_status
FROM product_status p, dataset_link 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
  and p.product_rootname = l.dataset_rootname
EOQ

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count observation product_status records deleted.");
#---------------------------------------------------------------------
#  Delete invalid dataset_link records for observations
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE dataset_link
FROM dataset_link 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

          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count observation dataset_link records deleted.");
        }  # end if num_miss_exp=0

#---------------------------------------------------------------------
# This must be last delete query. Delete invalid product_eng_map obsets
#---------------------------------------------------------------------
        if ($num_missing > 0) {
	      
$query = <<"EOQ";
DELETE product_eng_map
FROM product_eng_map p, dataset_link l, #missing m
WHERE l.program_id = m.program_id
  and l.obset_id = m.obset_id
  and p.product_rootname = l.dataset_rootname
EOQ
          $count = DoDBI( $db, $query );
          PrintMsg ("I","$count obset product_eng_map records deleted.");
        }  # end if num_missing > 0	  
      }  # end if obs_count = 0	  
#---------------------------------------------------------------------
# drop #missing_exp table
#---------------------------------------------------------------------
      if ($num_miss_exp>0) {
	
$query = <<"EOQ";
DROP TABLE #missing_exp
EOQ
        DoDBI( $db, $query );
      }

#---------------------------------------------------------------------
# drop #missing table
#---------------------------------------------------------------------
      if ($num_missing>0) {

$query = <<"EOQ";
DROP TABLE #missing
EOQ
        DoDBI( $db, $query );
      }

#---------------------------------------------------------------------
# drop #obsets table
#---------------------------------------------------------------------
      if ($obs_count>0) {
      
$query = <<"EOQ";
DROP TABLE #obsets
EOQ
        DoDBI( $db, $query );
      }

#---------------------------------------------------------------------
#  Stop in error if executed records within this time range have been updated.
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT COUNT(*) 
FROM qolink_sms l, executed ex
where l.start_time > '$sms_start' and l.start_time < '$sms_stop' and
ex.program_id = l.program_id and
ex.obset_id = l.obset_id and
ex.ob_number = l.ob_number and
ex.executed_flg != ' '
EOQ

      @count = DoDBIselect( $db, $query);
      if ($count[0] > 0) {
        PrintMsg ("E","Found non-blank executed_flg values within SMS range -");
        PrintMsg ("E","if this condition is acceptable, procede w. UPDATR.");
        DoDBIclose($db);
        exit( $PROCESS_FAILURE);
      }

#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    } # end if sms_count<2
    DoDBIclose($db);
    PrintMsg ("I","---  end  --- Check Replan MSC file ----------");
    exit( $PROCESS_SUCCESS);  
#---------------------------------------------------------------------
# end of main procedure -- subroutines follow
#---------------------------------------------------------------------
#---  check obesrvation list against SPSS_DB database
#     Retry up to $max_retries with arithmetreically increasing sleep time
#     until list matches database. If list never matches database exit 
sub CheckObserveList
{
    my ($sms_id, $list_file, $initial_sleep_time, $max_retries) = @_;

    # get and save entire list from file
    open(LISTFILE,"<$list_file");
    my @observations = <LISTFILE>;
    close(LISTFILE);
    my $sleep_time;
    my $retry_count = 0;
    my @db_list;
    my $db_observe;
    my $list_observe;
    my $valid;
    
    # query qobservation and qolink to verify that both tables have the 
    # observation data for the time range in sms_catalog for the SMS_ID.
    # (had to add individual field retrievals of program_id, obset_id, and
    #  ob_number to the SELECT, otherwise it would fail under MSSQL.  The
    #  values are just ignored in the query results.)
    #
    my $query =<<EOQ;
select distinct o.program_id+o.obset_id+o.ob_number,
o.program_id,o.obset_id,o.ob_number
from $SPSS_DB..qobservation o, $SPSS_DB..sms_catalog s, $SPSS_DB..qolink l
where s.sms_id='$sms_id' and o.pred_strt_tm > s.start_time and
      o.pred_strt_tm < s.end_time and l.program_id = o.program_id and
      l.obset_id = o.obset_id and l.ob_number = o.ob_number
order by  o.program_id, o.obset_id, o.ob_number
EOQ
    
    my $err_msg1 = "Cannot query first qobservation record.";
    my $err_msg2 = "Cannot query next qobervationrecord.";
    $old_flush_mode = $|;
    $| = 1; # turn on autoflush mode for default file handle
      
    while (1) {  # do until last command is used
        PrintMsg ("I","--- start verification of SPSS observation list ");
        # get entire list from database
        @db_list = ();  # removes any old data
        
        # query all records from database into @db_list array
        $sth = DoDBIexecute( $db, $query);
        while ( ( $observation,$dontcare1,$dc2,$dc3) = DoDBIfetch( $db, $query, $sth) ) {
           push @db_list, $observation;
        }
        #verify @observations against @db_list
        $valid = 1;  # assume valid until a mismatch is found

        foreach $list_observe (@observations) {
           # remove trailing CR and newline characters from end of observation ID
           #  (works for all combos of CR and newline except \n\r, which would 
           #   require a loop, hasn't been seen, and is unlikely to occur)
           chomp $list_observe;  # remove \n
           if( $list_observe =~ /\r$/ ) {
              chop $list_observe ;  # remove CR also
           }

           $db_observe = shift @db_list;
           if (!defined( $db_observe) ) {
              PrintMsg ("I",
                 "Mismatch: query ends at SPSS observation: $list_observe ");
              $valid = 0;
              last;
           } 
           if ($list_observe ne $db_observe) {
              PrintMsg ("I",
                 "Mismatch: DB value: $db_observe, SPSS value: $list_observe ");
              $valid = 0;
              last;
           }
        }
        if ($valid) {last;}
        
        # observation list not valid
        if ($initial_sleep_time == 0) {
            PrintMsg ("I",
               "SLEEP_TIME is zero or not defined in resource file.");
            PrintMsg ("I", "No retries are allowed when SLEEP_TIME is zero.");
            PrintMsg ("F","Cannot verify observation list.");
           exit( $PROCESS_ABORT)
        }
        $retry_count++;
        if ($retry_count > $max_retries) {last;}
        $sleep_time = $initial_sleep_time * $retry_count;
        PrintMsg("I", "Sleeping for $sleep_time seconds before next retry.");
        sleep $sleep_time;
    }
    $| = $old_flush_mode; # restore autoflush mode for default file handle
    
    if (!$valid) {
       PrintMsg ("F","Cannot verify observation list after maximum retries.");
       exit( $PROCESS_ABORT)
    }
    PrintMsg ("I","--- resuming processing after verifying observation list.");
}
#---------------------------------------------------------------------
#---  validate time yyyydddhhmmss
sub ValidateTime
{
    my ( $time_name, $compressed_time ) = @_;
    if (!($compressed_time=~/^\d\d\d\d\d\d\d\d\d\d\d\d\d$/)) {
      PrintMsg("E",
        "In file_times, found invalid $time_name <$compressed_time>.");
      exit( $PROCESS_FAILURE);
    }
}