#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: ast_keyword.pl
#
# This perl script is used for the ASTKWD process. In the pipeline it is
# invoked by an XPOLL command when the AST-class OSF is triggered.
#
# Interactive Usage:
#	>ast_keyword.pl <ast_rootname>
#
#       where ast_rootname is FpppssooM.
#
# Pipeline Usage:
#       command:ast_keyword.pl 
#      
#       The ENV OUTPATH must be defined in the resourse file
#
#	Note that the following environment variables (logicals) are 
#       always required in both modes: OPUS_SERVER, SPSS_DB and OPUS_DB.
#       These queries require multiple _DBs on SAME server: 
#       keeping DSQUERY as a variable to emphasize this point.
#
#
# History:
# Date     OPR      Who        Reason
# -------- -------- ---------- ---------------------------------------------
# 06/26/01 43961    Baum       Initial code
# 04/29/02 45738    Baum       Use external subroutines. Use lowercase rootname
# 06/18/02 46012    Baum       Get TARGETID from $SPSS_DB..qobservation
# 03/25/10 64274    MSwam      Replace ST_DBlib with DBI
# 06/30/10 64432    MSwam      Use single quotes for SQLServer
# 09/18/12 72255    Sherbert   keep DSQUERY in code to emphasize the sharing 
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'do_dbi_pkg.pl'; # run query selecting records 
require 'sogs_time_pkg.pl';  # get current date in SOGS format

#specify exit status values

    $OSF_SUCCESS =      11;   # exit status for XPOLL
    $OSF_HOLD    =      55;   # exit status for XPOLL
    $OSF_FAILURE =      99;   # exit status for XPOLL

#check for arguments

    $num_arg = scalar @ARGV;

    if ($num_arg > 0 && substr($ARGV[0],0,1) ne "-") {
      $interactive = 1;
    } else {
      $interactive = 0;
    }
    $outpath = $ENV{"OUTPATH"};
    if ($interactive) {
      $ast_root = lc($ARGV[0]);
      # Verify IPPPSSOOT format for AST rootname
      if (!($ast_root=~/^\D\w\w\w\w\w\w\w\D$/)){
        PrintMsg("E","First arg must be AST rootname: FpppssooM");
        exit( $OSF_FAILURE);                      
      }
      if (!defined($outpath)) {
        $outpath = "";
      }
    } else {
      # Verify ENV variables
      $ast_root = $ENV{"OSF_DATASET"};
      if (!defined($ast_root) || !defined($outpath))
      {
        PrintMsg("E","Missing ENV variable in resource file.");
        PrintMsg("E","Need: OUTPATH");
        exit( $OSF_FAILURE);
      }
    }
    $DSQUERY=           $ENV{"OPUS_SERVER"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});
    $SPSS_DB=           lc($ENV{"SPSS_DB"});
    $uc_root=           uc($ast_root);
    $process_ext=       ".agx_proc";
    $final_ext=         ".agx";
    $err_ext=           ".agx_bad";

# begin processing

    PrintMsg ("I","--- start --- Create $ast_root AST keyword file -------");
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = DoDBIopen( $DSQUERY, $OPUS_DB, $OSF_FAILURE);

#---------------------------------------------------------------------
# Verify ast_jitter_kw data created by JITTER process or FGS pipeline
#---------------------------------------------------------------------
    $query = <<"EOQ";
SELECT COUNT(*) FROM ast_jitter_kw a, dataset_link l
WHERE l.dataset_rootname='$uc_root' and l.dataset_type='AST' and
a.program_id = l.program_id and a.obset_id = l.obset_id and
a.ob_number = l.ob_number 
EOQ

    $msg = "Cannot access ast_jitter_kw table for $uc_root";
    @count = DoDBIselect( $db, $query);

    if ($count[0] == 0) {
      PrintMsg("I","Waiting for records in ast_jitter_kw table.");
      $exit_status = $OSF_HOLD;
    } else {

#---------------------------------------------------------------------
# open output AGX file with processing extension
#---------------------------------------------------------------------
      $file_spec = $outpath.$ast_root.$process_ext;
      if (!open (AGX_FILE,">".$file_spec)) {
        PrintMsg("E","Failed to open $file_spec for output");
        exit( $OSF_FAILURE); 
      }
#---------------------------------------------------------------------
# get target_id from qobservation
#---------------------------------------------------------------------
$query = <<"EOQ";
SELECT target_id
FROM $SPSS_DB..qobservation o, dataset_link l
WHERE l.dataset_rootname = '$uc_root' and l.dataset_type='AST' and
o.program_id = l.program_id and
o.obset_id = l.obset_id and
o.ob_number = l.ob_number
EOQ

    $msg = "Cannot access qobservation table for $uc_root";
    @target_id = DoDBIselect( $db, $query);
     
    if (!defined(@target_id)) {
      PrintMsg("E","Failed to get target_id from $SPSS_DB..qobservation table");
      exit( $OSF_FAILURE); 
    } 
#---------------------------------------------------------------------
# compute processing date and start loop over frames
#---------------------------------------------------------------------
      $cur_date = CurDate();
      $total_count = 0;
      for ($frame=1;$frame<4;$frame++) {

#---------------------------------------------------------------------
# write frame header
#---------------------------------------------------------------------
        print AGX_FILE ("-------------- file_type AGX, frame ",$frame,
          "\n-------------- ",$uc_root,"  generated ",$cur_date,"\n");

#---------------------------------------------------------------------
# query and write general and FGS specific msc_ast_obset keywords
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT param_name, param_value
FROM msc_ast_obset a, dataset_link l
WHERE l.dataset_rootname = '$uc_root' and
a.program_id = l.program_id and
a.obset_id = l.obset_id and
(a.fgs='0' or a.fgs='$frame')
EOQ

        $total_count += QueryWrite( $query, "msc_ast_obset");
#---------------------------------------------------------------------
# query and write ast_jitter keywords
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT keyword_name, keyword_value
FROM ast_jitter_kw a, dataset_link l
WHERE l.dataset_rootname = '$uc_root' and
a.program_id = l.program_id and
a.obset_id = l.obset_id and
a.ob_number = l.ob_number
EOQ

        $total_count += QueryWrite( $query, "ast_jitter_kw");
#---------------------------------------------------------------------
# write target_id keyword
#---------------------------------------------------------------------
        WriteKW("TARGETID",$target_id[0]);
	$total_count += 1;
#---------------------------------------------------------------------
# query and write general and FGS specific msc_ast_observe keywords
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT param_name, param_value
FROM msc_ast_observe a, dataset_link l
WHERE l.dataset_rootname = '$uc_root' and
a.program_id = l.program_id and
a.obset_id = l.obset_id and
a.ob_number = l.ob_number and
(a.fgs='0' or a.fgs='$frame')
EOQ

        $total_count += QueryWrite( $query, "msc_ast_observe");
#---------------------------------------------------------------------
# end of frames, log total and check count
#---------------------------------------------------------------------
      }  # next frame
      PrintMsg ("I","$total_count total keywords written to AGX file.");
      if ($total_count < 60) {
        PrintMsg ("E","Too few keyword records found.");
        $exit_status = $OSF_FAILURE;
      } else {
        $exit_status = $OSF_SUCCESS;
      }
#---------------------------------------------------------------------
# close and rename AGX file
#---------------------------------------------------------------------
      close AGX_FILE;
      if ($exit_status == $OSF_SUCCESS) {
        $new_name = $outpath.$ast_root.$final_ext;
      } else {
        $new_name = $outpath.$ast_root.$err_ext;
        PrintMsg ("I","Invalid AGX renamed to $new_name.");
      }
      if (!(rename $file_spec, $new_name)) {
        PrintMsg ("E","Cannot rename $file_spec");
        PrintMsg ("E","to new name: $new_name.");
        $exit_status = $OSF_FAILURE;
      }
    }
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    DoDBIclose($db);

    PrintMsg ("I","---  end  --- Create $ast_root AST keyword file -------");
    exit( $exit_status);  

#---------------------------------------------------------------------
# end of main procedure -- subroutines follow
#---------------------------------------------------------------------
sub QueryWrite
{
    my ($query,$table) = @_;
    my $kw_count=0;
    my $err_msg = "Cannot query first $table record.";
    my $err_msg2 = "Cannot query next $table record.";

    $sth = DoDBIexecute( $db, $query);
    while ( ( $keyword, $value) = DoDBIfetch( $db, $query, $sth) ) {
      WriteKW( $keyword, $value);
      $kw_count++;
    }
    $kw_count;
}
#---------------------------------------------------------------------
sub WriteKW
{
    my ($keyword,$value) = @_;
    my $line = "        =                      / AGX";
    my $len_kwd = length $keyword;
    my $len_val = length $value;
    if ($len_val > 20) {$len_val = 20;}
    substr($line,0,$len_kwd,$keyword);
    substr($line,10,$len_val,$value);
    print AGX_FILE ($line,"\n");
}