#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: msc_obset_events.pl
#
# This perl script is used to generate a single report that provides event
# timing data for obsets that include target acquisition, slew by offset slot,
# or COMCON support. For each obset selected with a given time range, additional
# queries are made to get the time of slew, next_slew, gsacq, reacq and fhst. 
#
# The environmental variables DSQUERY and OPUS_DB must be set properly. All
# tables are in OPUS_DB.
#
# Interactive Usage:  (one required arguments)
#	msc_obset_events.pl  start_time [stop_time]
#
#       e.g. msc_obset_events 2001.200     (or)
#            msc_obset_events 2001.200 2001.324
#
#  Return status: 0 = success, 1 = failure 
#
#        Definitions from PR.38202:
#
#        SLEW_START_TIME/SLEW_NEXT_TIME - Each slew time pair is composed
#        of a slew_start_time+duration and slew_next_time type-2 slew.  The
#        next slew time pair begins with the slew_next_time+duration of the
#        privious slew time pair.
#
#        BOACQ_START_TIME/REACQ_START_TIME: Each acquisition time pair is
#        composed of a boacq_start_time and reacq_start_time.  Should a
#        obset occur prior to the first acquisition in the mission schedule
#        the BOF time is used for the boacq_start_time.  When there is no
#        reacquisitions the reacq_start_time is the boacq_start_time.  When
#        there are one or more reacquistions the reacq_start_time is the
#        time of the last reacquisition found.  When the last aquisition
#        is a reacq the EOF time is used for reacq_start_time.
#
#        FHST_START_TIME: Each fhst time is the first occurence of the FHST3
#        AXIS event.  Should a obset occur prior to the first fhst in the
#        mission schedule the BOF time is used for the fhst_start_time.
#
#        Changes in definition for this tool:
#
#        SLEW_START_TIME/SLEW_NEXT_TIME will use end-of-slew time instead of
#        calculating slew_start_time+duration.
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 10/30/02 46559    Baum        Initial version.
# 03/24/10 64274    MSwam       Replace ST_DBlib with DBI
# 06/30/10 64432    MSwam       Use single quotes for SQLServer
#----------------------------------------------------------------------------
# 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

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

# get the external variables
    $DSQUERY=           $ENV{"DSQUERY"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});

#---------------------------------------------------------------------
# get arguments (set default argument)
#---------------------------------------------------------------------

    $num_arg = scalar @ARGV;

    if ($num_arg > 0) {
       $start_time = $ARGV[0];
       if ($num_arg > 1) {
          $stop_time = $ARGV[1];
       } else {
          $stop_time = "9999.999";
       }
    } else {
       PrintMsg("E","Missing start time argument.");
       PrintMsg("E","Usage: supply inline arguments start_time [stop_time]");
       PrintMsg("E"," e.g. msc_obset_events.pl 2002.123 2002.234"); 
       exit( $PROCESS_FAILURE);
    }
    if ($stop_time le $start_time) {
       PrintMsg("E"," stop time not greater than start time"); 
       exit( $PROCESS_FAILURE);
    }

# begin processing
    PrintMsg ("I","--- start --- Report Obset Events ----------");
    PrintMsg ("I","  Start:$start_time End:$stop_time");
    PrintMsg ("I","  Using server: $DSQUERY database: $OPUS_DB");
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);
    $db2 = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);

#---------------------------------------------------------------------
#  check for valid msc_events
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT count(*) FROM msc_events
WHERE  event_time > '$start_time' and event_time < '$stop_time' 
  and event_type='FGS' and event_class='BOA'
EOQ
    @num_acqs = DoDBIselect( $db, $query);    
    if (!defined(@num_acqs) or $num_acqs[0] == 0) {
      PrintMsg("E", "No BOA msc_event records found in time range - no report");
      DoDBIclose($db);
      DoDBIclose($db2);
      PrintMsg ("I","---  end  --- Report Obset Events ----------");
      exit( $PROCESS_SUCCESS);  # EXIT_SUCCESS
    }
#---------------------------------------------------------------------
# open report and write header lines
#---------------------------------------------------------------------
    open(REPORT,">msc_obset_events.txt");
    print REPORT " Obset Event Times Report from $start_time to $stop_time\n\n";
    print REPORT "|prg|ob| slew start time |  next slew time ",
#                  --- -- ----------------- ----------------- 
    "|   begin GS acq  |  last GS reacq  |    fhst start   |\n",
#     ----------------- ----------------- -----------------    
                 "|---|--|-----------------|-----------------", 
    "|-----------------|-----------------|-----------------|\n";
    $report_format = "|%3s|%2s|%17s|%17s|%17s|%17s|%17s|\n";                

#---------------------------------------------------------------------
#  create #obsetlist and get target acq obsets
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT distinct program_id, obset_id 
INTO #obsetlist
FROM qolink_sms
WHERE start_time>'$start_time' and start_time<'$stop_time' and taq = 'Y'
EOQ
    $err_msg = "Cannot access qolink_sms.";
    $count = DoDBI( $db, $query);    
    PrintMsg("I", "$count target acq obsets added to #obsetlist");

#---------------------------------------------------------------------
#  create index for #obsetlist
#---------------------------------------------------------------------
$query = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX #obsetlist_1 
ON #obsetlist (program_id,obset_id)
EOQ

    $err_msg = "Cannot create index for #obsetlist.";
    DoDBI( $db, $query);    

#---------------------------------------------------------------------
#  add RTO obsets to #obsetlist 
#---------------------------------------------------------------------
$query = <<"EOQ";
INSERT INTO #obsetlist SELECT DISTINCT s.program_id, s.obset_id 
FROM msc_slew_slot s
WHERE s.event_time > '$start_time' and s.event_time < '$stop_time'
  and NOT EXISTS (SELECT * FROM #obsetlist o
                  WHERE o.program_id = s.program_id and o.obset_id=s.obset_id) 
EOQ

    $err_msg = "Cannot insert msc_slew_slot obsets into #obsetlist.";
    $count = DoDBI( $db, $query);    
    PrintMsg("I", "$count msc_slew_slot obsets added to #obsetlist");

#---------------------------------------------------------------------
#  add COMCON obsets to #obsetlist 
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT event_name
FROM msc_events
WHERE event_time>'$start_time' and event_time<'$stop_time' and
  event_type='TDR' and event_class='BOC'
EOQ
    $err_msg = "Cannot access first record from msc_events.";
    $err_msg2 = "Failed getting next record from msc_events.";
    $err_msg3 = "Cannot insert msc_slew_slot obsets into #obsetlist.";

    $tot_count = 0;
    $sth2 = DoDBIexecute( $db2, $query);
    while ( ( @in_record) = DoDBIfetch( $db2, $query, $sth2) ) {
       $name = $in_record[0]; 
       $program_id = substr($name,0,3);
       $obset_id = substr($name,3,2);

$query = <<"EOQ";
INSERT INTO #obsetlist 
SELECT '$program_id','$obset_id'
WHERE NOT EXISTS (SELECT * FROM #obsetlist 
                  WHERE program_id='$program_id'
		    and obset_id='$obset_id')
EOQ

       $tot_count += DoDBI( $db, $query);    
#---------------------------------------------------------------------
#      loop back to get next msc_events record
#---------------------------------------------------------------------
    }	
    PrintMsg("I", "$tot_count COMCON obsets added to #obsetlist");
#---------------------------------------------------------------------
#      generate report
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT l.program_id, l.obset_id, min(s.start_time)
FROM #obsetlist l, qolink_sms s
WHERE s.program_id=l.program_id and s.obset_id=l.obset_id
GROUP BY l.program_id, l.obset_id
ORDER BY min(s.start_time)
EOQ
    $err_msg = "Cannot access first record from #obsetlist.";
    $err_msg2 = "Failed getting next record from #obsetlist.";

    $sth = DoDBIexecute( $db, $query);
    while ( ( @in_record ) = DoDBIfetch( $db, $query, $sth) ) {

       if ((scalar @in_record)==0 or not defined($in_record[2]) or 
            $in_record[2] eq " ") { last; }

       $program_id = $in_record[0]; 
       $obset_id = $in_record[1];
       $obset_start = $in_record[2];
       ($slew_start, $slew_next, $gsacq, $gsreacq, $fhst) = 
           Get_Obset_Times($program_id,$obset_id,$obset_start);
       printf REPORT ($report_format,$program_id,$obset_id,$slew_start,
         $slew_next, $gsacq, $gsreacq, $fhst);
#---------------------------------------------------------------------
#      loop back to get next #obssetlist record
#---------------------------------------------------------------------
    }	
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    close REPORT;
    DoDBIclose($db);
    DoDBIclose($db2);
    PrintMsg ("I","---  end  --- Report Obset Events ----------");
    exit( $PROCESS_SUCCESS);  # EXIT_SUCCESS

#---------------------------------------------------------------------
# End of process - start subroutines here
#---------------------------------------------------------------------
sub Get_Obset_Times {
my ($program_id, $obset_id, $obset_start) = @_;
my $slew_start;
my $slew_next;
my $gsacq;
my $gsreacq;
my $fhst;
my $last_time;
my $last_class;
my $err_msg;
my @in_record;
my $query;

#---------------------------------------------------------------------
#  Query gsacq time which will be used in a later query.
#  The gsacq is start of acquisition (BOA GSACQ1) or MSC start (BOM), prior to 
#  obset start time.
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT max(event_time) 
FROM msc_events
WHERE event_time < '$obset_start' and (event_class = 'BOM' or
  (event_class = 'BOA' and event_name = 'GSACQ1')) 
EOQ
    @in_record = DoDBIselect( $db2, $query);
    if ((scalar @in_record) == 0 or !defined($in_record[0])) {
        PrintMsg("E","Cannot obtain BOM or GSACQ1 times before $obset_start");
	exit( $PROCESS_FAILURE);
    } 
    $gsacq = $in_record[0];

#---------------------------------------------------------------------
#  Query next gsacq1 time or EOM and get event class so we can later
#  check whether the time is from the EOM. Normally an EOM and BOM 
#  appears at the same time for weekly MSC files, but replans can
#  eliminate an EOM so we test for both EOM and BOM events.
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT event_time, event_class 
FROM msc_events
WHERE event_class IN ('BOM','EOM','BOA') and event_time = 
  (SELECT min(event_time) 
   FROM msc_events 
   WHERE event_time > '$gsacq' and event_class IN ('BOM','EOM','BOA') and
    (event_name = 'GSACQ1' or event_class !='BOA'))
EOQ
    @in_record = DoDBIselect( $db2, $query);
    if ((scalar @in_record) == 0) {
        PrintMsg("E","Cannot obtain EOM or GSACQ1 times after $gsacq");
	exit( $PROCESS_FAILURE);
    } 
    $last_time = $in_record[0];
    $last_class = $in_record[1];

#---------------------------------------------------------------------
#  Query last gsreacq time before next GSACQ1 or EOM.
#  The gsreacq is set to gsacq if no more BOA events occur between GSACQ1
#  events, else use time of last BOA (which must be a REACQ) before the next
#  GSACQ1, or use EOM after last GSACQ1. Avoid picking up any GSACQ2 events.
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT max(event_time)
FROM msc_events
WHERE event_time >= '$gsacq' and event_time < '$last_time' and 
    event_class IN ('BOM','EOM','BOA') and event_name != 'GSACQ2'
EOQ
    @in_record = DoDBIselect( $db2, $query);
    if ((scalar @in_record) == 0) {
        PrintMsg("E","Cannot obtain last REACQ time after $gsacq");
	exit( $PROCESS_FAILURE);
    } 
    $gsreacq = $in_record[0];
    if (($gsreacq ne $gsacq) && ($last_class ne "BOA") ) {
        $gsreacq = $last_time; # continue reacq time to EOM
    }

#---------------------------------------------------------------------
#  Query end of slew time before obset.
#  The slew start is end time of type 2 slew (EOS2) or MSC start time (BOM),
#  prior to obset.
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT max(event_time)
FROM msc_events
WHERE event_class IN ('BOM','EOS2') and event_time < '$obset_start'
EOQ
    @in_record = DoDBIselect( $db2, $query);
    if ((scalar @in_record) == 0) {
        PrintMsg("E","Cannot obtain BOM or EOS2 time before $obset_start");
	exit( $PROCESS_FAILURE);
    } 
    $slew_start = $in_record[0];

#---------------------------------------------------------------------
#  Query next slew time after obset.
#  The slew next is start time of next type 2 slew (BOS2) or MSC end (EOM), 
#  after obset.
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT min(event_time)
FROM msc_events
WHERE event_class IN ('EOM','BOM','BOS2') and event_time > '$obset_start'
EOQ
    @in_record = DoDBIselect( $db2, $query);
    if ((scalar @in_record) == 0) {
        PrintMsg("E","Cannot obtain EOM or BOS2 time after $obset_start");
	exit( $PROCESS_FAILURE);
    } 
    $slew_next = $in_record[0];

#---------------------------------------------------------------------
#  Query FHST3 before obset.
#  The fhst is FHST3 time or BOM time, prior to obset.
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT max(event_time)
FROM msc_events
WHERE event_class IN ('BOM','FHST3') and event_time < '$obset_start'
EOQ
    @in_record = DoDBIselect( $db2, $query);
    if ((scalar @in_record) == 0) {
        PrintMsg("E","Cannot obtain BOM or FHST3 time before $obset_start");
	exit( $PROCESS_FAILURE);
    } 
    $fhst = $in_record[0];

#---------------------------------------------------------------------
#  Return array of five time values
#---------------------------------------------------------------------
  my @return_array = (substr($slew_start,0,17),substr($slew_next,0,17),
     substr($gsacq,0,17),substr($gsreacq,0,17),substr($fhst,0,17));
}