#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name:  mscxtr_prep
#
#    Deletes old msc table records within the replan time range of MSC file.
#
# Usage:
#	>mscxtr_prep.pl pod_name
#          where pod_name must match a MSC dataset_name in file_times table. 
#
#	Note that the following environment variables are required:
#       DSQUERY and OPUS_DB.
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 05/24/01 43959    Baum      Initial code
# 04/29/02 45738    Baum        Use external subroutines.
# 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

# set exit status
$EXIT_SUCCESS=    0;
$EXIT_FAILURE=    1;

#check for arguments
    $num_arg = scalar @ARGV;

    if ($num_arg > 0) {
        $POD_FILE = uc($ARGV[0]);
    } else {
       PrintMsg ("E","Missing POD name in first argument.");
       exit( $EXIT_FAILURE);
    } 

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

#---------------------------------------------------------------------
# Log start, check input, open database
#---------------------------------------------------------------------

    PrintMsg ("I","--- start --- MSCXTR database preparation -------");

    if (defined( $DSQUERY) && defined( $OPUS_DB) ) {
      $db = DoDBIopen( $DSQUERY, $OPUS_DB, $EXIT_FAILURE);
    } else {
      PrintMsg ("F","ENV variables DSQUERY and OPUS_DB must be defined.");
      exit( $EXIT_FAILURE);
    }
#---------------------------------------------------------------------
# get MSC replan time range from file_times table
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT replan_time, window_stop 
FROM file_times
WHERE dataset_name = '$POD_FILE' and archclass = 'MSC'
EOQ

    $err_msg = "Cannot access file_times for MSC file $POD_FILE.";
    @in_record = DoDBIselect( $db, $query);
    if ((scalar @in_record)==0) {
      PrintMsg ("E","Missing file_times record for MSC $POD_FILE.");
      DoDBIclose($db);
      exit (1);  # EXIT_FAILURE
    }
    $msc_start = $in_record[0];
    # convert yyyydddhhmmss to yyyy.ddd:hh:mm:ss.00
    $msc_start =~s/^(....)(...)(..)(..)(..)/$1\.$2:$3:$4:$5\.00/;
    $msc_end   = $in_record[1];
    # convert yyyydddhhmmss to yyyy.ddd:hh:mm:ss.00
    $msc_end   =~s/^(....)(...)(..)(..)(..)/$1\.$2:$3:$4:$5\.00/;
    PrintMsg ("I","For MSC $POD_FILE: $msc_start to $msc_end.");

#---------------------------------------------------------------------
# delete msc table records within time range
#---------------------------------------------------------------------

    @tables = 
      qw/msc_events msc_gs_acq msc_ast_obset msc_ast_observe msc_slew_slot/;
    foreach $msc_table (@tables) {

#---------------------------------------------------------------------
# use query to delete records from $msc_table
#---------------------------------------------------------------------
$query = <<"EOQ";
DELETE FROM $msc_table
WHERE event_time>='$msc_start' and event_time<='$msc_end'
EOQ

      $count = DoDBI( $db, $query );
      PrintMsg("I", "$count records deleted from $msc_table.");
    }
    PrintMsg ("I","---  end  --- MSCXTR database preparation -------");
    DoDBIclose($db);
    exit( $EXIT_SUCCESS);