#!/usr/bin/env perl 
#----------------------------------------------------------------------------
#
# Name: otfr_stats_po.pl
#
# This perl script fills part of a database record in the "otfr_request_stats"
# relation using information gleaned from an OTFC request file.
# This script performs the initial insert of the database record, loading:
#    data_set_name
#    otfr_id
#    dads_tracking_id
#    instrume
#    program_id
#    obset_id
#    obsnum
#    req_recv (time at which OTFR began processing the request)
#
# Usage:
#	% otfr_stats_po.pl otfr_request_filespec
#
#	Note that the following environment variables (logicals) are required:
#       ARCH_SERVER and ARCH_DB
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 09/09/00 43165    MSwam       first version
# 10/11/01 44597    MSwam       turn off db_req_date for remote sites
# 04/01/03 43915    WMiller     DADS 10.0 support
# 08/21/03 48370    Sherbert    Add missing /bin/ qualifier to egrep
# 09/22/04 51406    J.Baum      Change field-name ors_dads_reqnum to
#                               ors_dads_tracking_id. Change comments
#                               and variables to use tracking_id instead 
#                               of reqnum.
# 09/25/06 55370    MSwam       Add COS,WFC3
# 03/25/10 64274    MSwam       Replace ST_DBlib with DBI
# 07/30/10 64432    MSwam       Use single quotes for SQLserver
#
#----------------------------------------------------------------------------
# adjust the Perl include dir using PATH
$PATH=      $ENV{"PATH"};
@parts = split /:/, $PATH;
while (defined($val = pop(@parts))) {
   if (substr($val,0,1) eq "/") { unshift(@INC, $val)}
}

# get the external variables
$ARCH_SERVER = $ENV{"ARCH_SERVER"};
$STSCI_SITE  = $ENV{"STSCI_SITE"};
$ARCH_DB     = lc($ENV{"ARCH_DB"});

$usage = "USAGE: otfr_stats_po.pl otfr_request_filespec";

require 'printmsg.pl';	# PrintMsg routine
require 'do_dbi_pkg.pl';	# RunSql routine
require 'db_req_date.pl';  # reads DADS request date from DADS db

    #
    # verify argument 1 is present and an existent file
    # (#argv=0 when there is exactly one argument)
    #
    if ($#ARGV != 0) {  
        die "$usage\n";
    }
    $filespec = $ARGV[0];
    if (! -e $filespec) {
        die "ERROR: File $filespec does not exist.\n";
    }

    #
    # initialize database
    PrintMsg ("I","--- start --- insert to otfr_request_stats ----------");
	 
    my $EXIT_FAILURE = 1;
    $db = DoDBIopen( $ARCH_SERVER, $ARCH_DB, $EXIT_FAILURE);

    #
    # get file access time and convert to input date format
    ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime) = stat($filespec);
    ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($atime);
    use POSIX;
    # this format produces Month DD YYYY HH:MM:SS [AM|PM]
    $req_recv = POSIX::strftime("%b %d %Y %r",$sec,$min,$hour,$mday,$mon,$year);

    #
    # parse the DADS tracking id from the request file
    @line = `/bin/egrep "DADS_REQ" $filespec`;
    chomp $line[0];
    $dads_tracking_id = substr($line[0], 9);

    #
    # parse the filespec to fill database fields
    # (the file extension is considered the first dot followed by anything)
    #
    use File::Basename;
    $file_extension_match = "\.[0-z]*";
    ($name, $path, $suffix) = fileparse($filespec, $file_extension_match);

    #
    # split the request name into the id and the ipppssoot
    @parts = split /_/, $name;
    $otfr_id = $parts[0];
    # convert dataset name to UPPERCASE
    $data_set_name = uc($parts[1]);

    #
    # parse fields from the ipppssoot
    $program_id = substr($data_set_name, 1, 3);
    $obset_id = substr($data_set_name, 4, 2);
    if ($data_set_name =~ /[0-9]$/) {
       $obsnum = substr($data_set_name, 6, 3);
    } else {
       $obsnum = substr($data_set_name, 6, 2);
    }

    #
    # assign instrument
    if    ($data_set_name =~ /^O/) { $instrume = "STIS"; }
    elsif ($data_set_name =~ /^U/) { $instrume = "WFPC2";}
    elsif ($data_set_name =~ /^N/) { $instrume = "NICMOS";}
    elsif ($data_set_name =~ /^J/) { $instrume = "ACS";}
    elsif ($data_set_name =~ /^X/) { $instrume = "FOC";}
    elsif ($data_set_name =~ /^I/) { $instrume = "WFC3";}
    elsif ($data_set_name =~ /^L/) { $instrume = "COS";}
    else                              { $instrume = "UNKNOWN";}

    # query DADS db to get the dads request date (if at STSCI)
    if ($STSCI_SITE =~ /^[Tt]/) {
        $dads_request_date = DB_req_date($dads_tracking_id);
    }
    else {
        $dads_request_date = "";
    }

    #
    # try update first to detect existing db record
    $query = <<"EOQ";
UPDATE otfr_request_stats
SET
   ors_cal_success = 'F',
   ors_out_data_set_size = 0.0,
   ors_out_file_count = 0,
   ors_trouble_flag = 'F'
WHERE ors_otfr_request_id = \'$otfr_id\'
AND ors_data_set_name = \'$data_set_name\'
EOQ

    $count = DoDBI( $db, $query );
    if ($count == 1) {
       PrintMsg("I","$count record updated in otfr_request_stats");
    }
    else {
       #
       # insert the otfr_request_stats db record
       $query = <<"EOQ";
INSERT INTO otfr_request_stats
               ( ors_data_set_name,
                 ors_program_id,
                 ors_obset_id,
                 ors_obsnum,
                 ors_cal_success,
                 ors_dads_tracking_id,
                 ors_dads_request_date,
                 ors_instrume,
                 ors_out_data_set_size,
                 ors_out_file_count,
                 ors_otfr_request_id,
                 ors_req_receipt_time,
                 ors_trouble_flag )
VALUES
               ( '$data_set_name',
                 '$program_id',
                 '$obset_id',
                 '$obsnum',
                 'F',
                 '$dads_tracking_id',
                 '$dads_request_date',
                 '$instrume',
                 0.0,
                 0,
                 '$otfr_id',
                 '$req_recv',
                 'F' )              
EOQ

       $count = DoDBI( $db, $query );
       if ($count == 1) {
          PrintMsg("I","$count record inserted into otfr_request_stats");
       }
       else {
          PrintMsg("E","$count records inserted into otfr_request_stats\n");
       }
    }

#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    DoDBIclose($db);
    PrintMsg ("I","---  end  --- insert to otfr_request_stats ----------");
    exit( 0);  # EXIT_SUCCESS