#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: msc_rto_report.pl
#
# This perl script is used to generate a single report that merges all RTO
# data in time order from the msc_events and msc_slew_slot tables. The output
# is written to the default directory with the name msc_rto_report.txt.
#
# The environmental variables OPUS_SERVER and OPUS_DB must be set properly. 
# All tables are in OPUS_DB.
#
# Interactive Usage:  (one required arguments)
#	msc_rto_report.pl  start_time [stop_time]
#
#       e.g. msc_rto_report 2001.200     (or)
#            msc_rto_report 2001.200 2001.324
#
#  Return status: 0 = success, 1 = failure 
#
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 09/10/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
# 09/18/12 72255    Sherbert    get rid of DSQUERY
#----------------------------------------------------------------------------
# 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; 
    $PROCESS_SUCCESS = 0; 

# get the external variables
    $OPUS_SERVER=          $ENV{"OPUS_SERVER"};
    $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_rto_report.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 MSC RTO Records ----------");
    PrintMsg ("I","  Start:$start_time End:$stop_time");
    PrintMsg ("I","  Using server: $OPUS_SERVER database: $OPUS_DB");
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = DoDBIopen( $OPUS_SERVER, $OPUS_DB, $PROCESS_FAILURE);
    $db2 = DoDBIopen( $OPUS_SERVER, $OPUS_DB, $PROCESS_FAILURE);

#---------------------------------------------------------------------
# open report and write header lines
#---------------------------------------------------------------------
    open(REPORT,">msc_rto_report.txt");
    print REPORT " RTO Slot Usage Report from $start_time to $stop_time\n\n";
    print REPORT "|     event time     |slot| type| prg| ob|",
#                 |yyyy.ddd:hh:mm:ss.00| nn |clear| prg| ob| 
      "  load by time |max|   spss id   |\n",
#       yyyy.ddd:hh:mm|nnn| xxxxxxxxxxxx| 
                 "|--------------------|----|-----|----|---|", 
      "---------------|---|-------------|\n";
    $report_format = "|%20s| %2s |%5s| %3s| %2s| %14s|%3s|%13s|\n";                

#---------------------------------------------------------------------
#  Process all RTO events
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT event_time, event_class, event_name
FROM msc_events
WHERE event_time>'$start_time' and event_time<'$stop_time' and
  event_type='RTO'
EOQ
    $err_msg = "Cannot access first record from msc_events.";
    $err_msg2 = "Failed getting next record from msc_events.";

    $sth = DoDBIexecute( $db, $query);
    while ( ( @in_record ) = DoDBIfetch( $db, $query, $sth) ) {
       $ctime = $in_record[0]; 
       $class = $in_record[1]; 
       $name  = $in_record[2]; 
       $last_underscore = rindex($name,"_");
       $slot = substr($name,2,($last_underscore-2));
       $program_id = substr($name,($last_underscore+1),3);
       $obset_id = substr($name,($last_underscore+4),2);
#---------------------------------------------------------------------
#      write clear and set events with blank fields
#---------------------------------------------------------------------

       if ($class eq "SET" or $class eq "CLEAR") {
          printf REPORT $report_format,$ctime,$slot,$class,$program_id,
	     $obset_id," "," "," ";
       } elsif ($class eq "GEN") {  
#---------------------------------------------------------------------
#         For GEN event, query msc_slew_slot for slew data 
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT load_by, max_slew, offset_id 
FROM msc_slew_slot
WHERE event_time = '$ctime' and program_id = '$program_id' and
  obset_id = '$obset_id' and slot = '$slot' 
EOQ
          $err_msg3 = "Cannot access msc_slew_slot at $ctime"; 

          $sth2 = DoDBIexecute( $db2, $query);
          @in_record2 = DoDBIfetch( $db2, $query, $sth2);
          if ((scalar @in_record2) == 0)  {
            PrintMsg ("E","Cannot find msc_slew_slot data for $ctime");
	    exit( $PROCESS_FAILURE);
          }
          $load_by = $in_record2[0];
          $max_slew = $in_record2[1];
          $offset_id = $in_record2[2];
#---------------------------------------------------------------------
#         write GEN event with msc_slew_slot data
#---------------------------------------------------------------------
          printf REPORT $report_format,$ctime,$slot,$class,$program_id,
	     $obset_id,$load_by,$max_slew,$offset_id;
       } else {       
#---------------------------------------------------------------------
#         For REUSE event, query msc_slew_slot for last slot usage 
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT load_by, max_slew, offset_id 
FROM msc_slew_slot
WHERE event_time = (SELECT MAX(event_time) 
                    FROM msc_slew_slot
		    WHERE event_time < '$ctime' and slot = '$slot') 
EOQ
          $err_msg4 = "Cannot access msc_slew_slot for slot $slot"; 

          $sth2 = DoDBIexecute( $db2, $query);
          @in_record2 = DoDBIfetch( $db2, $query, $sth2);
          if ((scalar @in_record2) == 0)  {
            PrintMsg ("E","Cannot find msc_slew_slot data for slot $slot");
	    exit( $PROCESS_FAILURE);
          }
          $load_by = $in_record2[0];
          $max_slew = $in_record2[1];
          $offset_id = $in_record2[2];
#---------------------------------------------------------------------
#         write REUSE event with msc_slew_slot data from query
#---------------------------------------------------------------------
          printf REPORT $report_format,$ctime,$slot,$class,$program_id,
	     $obset_id,$load_by,$max_slew,$offset_id;
       }	
#---------------------------------------------------------------------
#      loop back to get next msc_events record
#---------------------------------------------------------------------
    }	
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    close REPORT;
    DoDBIclose($db);
    DoDBIclose($db2);
    PrintMsg ("I","---  end  --- Report MSC RTO Records ----------");
    exit( $PROCESS_SUCCESS);  # EXIT_SUCCESS