#!/usr/bin/env perl
#===========================================================================
# NOTE:  As of 3/24/2010, it appears this code is NO LONGER USED.
#        To help prove that, leave it with ST_DBlib calls, and it
#        will fail under Linux if it is ever used (and we would know).
#===========================================================================
#----------------------------------------------------------------------------
#
# Name:  update_product_times
#
#    Parses the header records of the MSC POD file to get the SMS_ID, start and
#    end time. Uses the SMS_ID in a set of queries to update all the
#    qolink_sms start and end times for every association product in the SMS.
#    It only uses qolink_sms and asn_product_link tables.
#
# Usage:
#	$perl oms_exe:update_product_times.pl     (for VMS)
#
#	Note that the following environment variables are required:
#       INPATH, OSF_DATASET, DSQUERY and OPUS_DB.
#
# History:
# Date     OPR      Who      Reason
# -------- ------ ---------- ---------------------------------------------
# 09/27/99 39655  Baum       Initial code
# 03/14/00 39655  Baum       Repair regular expression for MSC parsing
# 08/08/00 42207  Baum       Allow no exposures in health and safety SMS
# 07/20/06 55869  Sherbert   Place subroutine prototypes before calls
# 03/25/10 64274    MSwam     Replace ST_DBlib with DBI
#
#----------------------------------------------------------------------------

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

# subroutine prototypes
sub Parse_SMS_Times($);

# set exit status
$EXIT_SUCCESS=    11;
$EXIT_FAILURE=    99;

# declarations
my $ms_start;
my $ms_end;
my $calendar;
my $msc_file;
my $msc_type;

# begin

    PrintMsg ("I","--- start --- Update qolink_sms product times -------");
    if (lc($inpath) eq "-s") {
      $calendar = uc($pod_root);
      PrintMsg("I","SMS $calendar");
    } else {
      $msc_file = $inpath.$pod_root.'.pod';
      if ( !(($calendar, $ms_start, $ms_end) = Parse_SMS_Times( $msc_file))) {
        exit( $EXIT_FAILURE);
      }
      PrintMsg("I","SMS $calendar: $ms_start to $ms_end");
    }
    use ST_DBlib;
    $db = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db == -1;

#----------------------------------------------------------------------------
# create temporary table containing minimum start and maximum end times over
# all exposures for each product for all associations in the time range.
#----------------------------------------------------------------------------

$query = <<"EOQ";
SELECT p.program_id,p.asn_obset_id,p.member_num,prod_start=MIN(le.start_time), 
      prod_end=MAX(le.end_time)
INTO #prod_times 
FROM qolink_sms lp, asn_product_link p, qolink_sms le
WHERE lp.sms_id = '$calendar' and
      p.program_id = lp.program_id and
      p.asn_obset_id = lp.obset_id and
      p.member_num = lp.ob_number and
      le.program_id = p.program_id and
      le.obset_id = p.obset_id and
      le.ob_number = p.ob_number and
      le.start_time!=' '
GROUP BY p.program_id,p.asn_obset_id,p.member_num
EOQ

    $count = RunSql( $query );
    PrintMsg("I", "$count records put into table #prod_times.");

    if ($count==0) { 
#      OPR.42207 - Probably health and safety SMS - generate warning and quit
       PrintMsg("W", "No exposures in SMS - presumed H & S SMS.");
       $db->dbclose;
       PrintMsg ("I","---  end  --- Update qolink_sms product times -------");
       exit( $EXIT_SUCCESS);
    }

#----------------------------------------------------------------------------
# update product records in qolink_sms 
#----------------------------------------------------------------------------

$query = <<"EOQ";
UPDATE qolink_sms
SET start_time=#prod_times.prod_start, end_time=#prod_times.prod_end
FROM qolink_sms, #prod_times
WHERE
      qolink_sms.program_id=#prod_times.program_id and
      qolink_sms.obset_id=#prod_times.asn_obset_id and
      qolink_sms.ob_number=#prod_times.member_num
EOQ

    $count = RunSql( $query );
    PrintMsg("I", "$count qolink_sms product records updated.");

#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------

    $db->dbclose;
    PrintMsg ("I","---  end  --- Update qolink_sms product times -------");
    exit( $EXIT_SUCCESS);

#---------------------------------------------------------------------
# end of main -- subroutines follow
#---------------------------------------------------------------------
sub PrintMsg
{
    my ($type,$msg) = @_;
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);
    my $date;

    ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
    $year   += 1900;
    $date = sprintf("%4d%03d%02d%02d%02d",$year,$yday,$hour,$min,$sec);
    print "$date-$type-$msg\n";
}
#---------------------------------------------------------------------
#---  used only to delete or insert records -- only record count is returned
sub RunSql
{
    my ( $query ) = @_;
    my $rec_count;
    my $count =0;

    die if $db->dbcmd($query) == Sybase::DBlib::FAIL;
    die if $db->dbsqlexec     == Sybase::DBlib::FAIL;
    while ($db->dbresults    != Sybase::DBlib::NO_MORE_RESULTS ) {
        $rec_count = $db->DBCOUNT;
        if ($rec_count>0){$count += $rec_count};
    }
    $count;
}
#---------------------------------------------------------------------
#---  open MSC file and parse header lines for SMS name and times
sub Parse_SMS_Times($)
{
  my ($msc_file) = @_;

  if (!open( MSC_FILE, "<".$msc_file)) {
    PrintMsg("F","Cannot open $msc_file.");
    return;    # no return arguments indicates error
  }
  my $ms_start;
  my $ms_end;
  my $calendar;
  my $inline;
  my $count=0;

  while (<MSC_FILE>) 
  {
    $inline = $_;
    $count++;
    last if ($count>50);
    if ($inline=~/^MS RUN NAME+/) {
      ($ms_start,$ms_end)=($inline=~m/START=(.+?),\s+END=(.+?)\s*\n/);
    } elsif ($inline=~/CALENDAR\(/) {
      ($calendar) = ($inline=~m/CALENDAR\((.+?)\)/);
      last;
    }
  }
  close MSC_FILE;

  if ($calendar eq "") { 
    PrintMsg("E","Cannot parse header lines of SMS.");
    return;
  } 
  return ($calendar, $ms_start, $ms_end);  
}