#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: msc_backfill.pl
#
# This perl script is used one time to populate msc_events and msc_slew_slot 
# tables with RTO records that describe slew slot usage. This old data is 
# required for some slot usage analysis.
#
# The environmental variables DSQUERY and OPUS_DB must be set properly. All
# tables are in OPUS_DB.
#
# Interactive Usage:  (no arguments)
#	msc_backfill.pl  
#
#  Return status: 0 = success, 1 = failure 
#
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 09/09/02 46559    Baum        Initial version.
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'runsql.pl';         # run query returning only record count
require 'sql_select_pkg.pl'; # run queries that return records
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
    $DSQUERY=           $ENV{"DSQUERY"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});

# begin processing
    use ST_DBlib;
    PrintMsg ("I","--- start --- Backfill MSC RTO Records ----------");
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db == -1;

    $db2 = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db2 == -1;

#---------------------------------------------------------------------
#  Find last time of RTO data
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT max(t.ctime) table_time, max(h.ctime) hist_time 
FROM oms_rto_table t, oms_rto_history h
EOQ

    $err_msg = "Cannot access max times for oms_rto tables.";
    @in_record = FirstRecordSql( $db, $query, $err_msg);
    if ((scalar @in_record)==0) {
      PrintMsg ("E","Missing rto data");
      exit ($PROCESS_FAILURE);  # EXIT_FAILURE
    }
    if ($in_record[0] gt $in_record[1] ) {
       $last_rto_time = $in_record[0];
    } else {
       $last_rto_time = $in_record[1];
    }   
    PrintMsg ("I","Deleting MSC RTO records before $last_rto_time.");
#---------------------------------------------------------------------
#  delete MSC RTO data prior to last_rto_time
#---------------------------------------------------------------------

$query = <<"EOQ";
DELETE FROM msc_events
WHERE event_type = "RTO" and event_time < "$last_rto_time"
EOQ
    $count = RunSql( $db, $query );
    PrintMsg ("I","Deleted $count RTO records from msc_evemts.");


$query = <<"EOQ";
DELETE FROM msc_slew_slot
WHERE event_time < "$last_rto_time"
EOQ
    $count = RunSql( $db, $query );
    PrintMsg ("I","Deleted $count records from msc_slew_slot.");

#---------------------------------------------------------------------
#  process all oms_rto_table records
#---------------------------------------------------------------------
    $gen_count = 0;
    $reu_count = 0;

$query = <<"EOQ";
SELECT * FROM  oms_rto_table
EOQ

    $err_msg = "Cannot access first record from oms_rto_table.";
    $err_msg2 = "Failed getting next record from oms_rto_table.";
    @in_record = FirstRecordSql( $db, $query, $err_msg);
    while ((scalar @in_record)!=0) {
       $ctime = $in_record[0]; 
#      $jtime = $in_record[1];   # -- not used
       $slot = $in_record[2]; 
       $type = $in_record[3]; 
       $program_id = $in_record[4]; 
       $obset_id = $in_record[5]; 
       $load_by = $in_record[6]; 
       $max_slew = $in_record[7]; 
       $offset_id = $in_record[8];
       if ($type eq "GEN-SLEW") {
          $event_class = "GEN";
	  $gen_count++;
       } else {
          $event_class = "REUSE";
	  $reu_count++;
       }
       $event_name = "S_".$slot."_".$program_id.$obset_id; 
#---------------------------------------------------------------------
#      insert msc_events record for GEN and REUSE 
#---------------------------------------------------------------------

$query = <<"EOQ";
INSERT INTO msc_events VALUES ( "$ctime","RTO","$event_class","$event_name")
EOQ

       $count = RunSql( $db2, $query );
       if (!$count) {
          PrintMsg ("E","record: $ctime RTO $event_class $event_name");
          PrintMsg ("E","Failed to insert record into msc_events.");
	  exit( $PROCESS_FAILURE);
       }
       if ($event_class eq "GEN") {
#---------------------------------------------------------------------
#         insert msc_slew_slot record  
#---------------------------------------------------------------------

$query = <<"EOQ";
INSERT INTO msc_slew_slot VALUES ( "$ctime","$program_id","$obset_id",
   "$slot","$load_by","$max_slew","$offset_id")
EOQ

          $count = RunSql( $db2, $query );
          if (!$count) {
             PrintMsg ("E","record: $ctime $program_id $obset_id $slot");
             PrintMsg ("E","Failed to insert record into msc_slew_slot.");
	     exit( $PROCESS_FAILURE);
          }
       } 
#---------------------------------------------------------------------
#      get next oms_rto_table record
#---------------------------------------------------------------------
       @in_record = NextRecordSql( $db, $err_msg2);
    }
    PrintMsg ("I","Inserted $gen_count records into msc_slew_slot.");
    PrintMsg ("I","Inserted $gen_count RTO GEN records into msc_events.");
    PrintMsg ("I","Inserted $reu_count RTO REUSE records into msc_events.");

#---------------------------------------------------------------------
#  process all oms_rto_table records
#---------------------------------------------------------------------
    $clr_count = 0;
    $set_count = 0;

$query = <<"EOQ";
SELECT * FROM  oms_rto_history
EOQ

    $err_msg = "Cannot access first record from oms_rto_history.";
    $err_msg2 = "Failed getting next record from oms_rto_history.";
    @in_record = FirstRecordSql( $db, $query, $err_msg);
    while ((scalar @in_record)!=0) {
       $ctime = $in_record[0]; 
#      $jtime = $in_record[1];   # -- not used
       $slot = $in_record[2]; 
       $type = $in_record[3]; 
       $program_id = $in_record[4]; 
       $obset_id = $in_record[5]; 
       if ($type eq "GEN-CLR") {
          $event_class = "CLEAR";
	  $clr_count++;
       } else {
          $event_class = "SET";
	  $set_count++;
       }
       $event_name = "S_".$slot."_".$program_id.$obset_id; 
#---------------------------------------------------------------------
#      insert msc_events record for CLEAR and SET 
#---------------------------------------------------------------------

$query = <<"EOQ";
INSERT INTO msc_events VALUES ( "$ctime","RTO","$event_class","$event_name")
EOQ

       $count = RunSql( $db2, $query );
       if (!$count) {
          PrintMsg ("E","record: $ctime RTO $event_class $event_name");
          PrintMsg ("E","Failed to insert record into msc_events.");
	  exit( $PROCESS_FAILURE);
       } 
#---------------------------------------------------------------------
#      get next oms_rto_history record
#---------------------------------------------------------------------
       @in_record = NextRecordSql( $db, $err_msg2);
    }
    PrintMsg ("I","Inserted $clr_count RTO CLEAR records into msc_events.");
    PrintMsg ("I","Inserted $set_count RTO SET records into msc_events.");
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    $db2->dbclose;
    PrintMsg ("I","---  end  --- Backfill MSC RTO Records ----------");
    exit( $PROCESS_SUCCESS);  # EXIT_SUCCESS