#!/usr/local/bin/perl -w
# -----------------------------------------------------------------------------
# Name:        post_psc_auto_lookup_auto_submit.pl
# Description: Reads Database psc_lookup_types table for lookup_type and lookup_table
#              and calls post_psc_lookup_auto.pl to Create csv and zip files
#              per each each lookup_type to /usr/ftp/pub/data/
#
#              $post_ variables are used to generate parameters for the post
#              program post_psc_auto.pl and to format the file names of files
#              posted to /usr/ftp/pub/data/
#
#              Lookup Types using 'psc_lookup' table require the lookup_type parameter 
#              to restrict output to one file per lookup_type
#
#              For Testing uncomment lines with TEST
#
#*******************************************************************************
# -------------------------------------------------------------------
# Program History:
# INT  DATE      Comments
# ---  --------  --------
# DLW  20171114  Created
#****************************************************************************
#
$| = 1;              #flushes the log buffer so output can be seen quicker

use lib $ENV{RMPCDB_BIN};
use DBI;
&Setup_Environment();

# Open ERRLOG Log File
  $log_file_name = "post_psc_auto_lookup_submit";
  $logfile       = $logpath . "/" . $log_file_name . ".log";
  if (! open (ERRLOG, ">> " . $logfile)) {
    die "open ERRLOG for output failed $!\n";
  }
  print ERRLOG "Start Time : " . scalar(localtime) . "\n";
  print ERRLOG "  PG_DATABASE = $db_name \n";

# Build SQL statment
  $select =           "SELECT lookup_type, lookup_table\n";
  $select = $select . " FROM psc_lookup_types\n";
  $select = $select . " ORDER BY lookup_type\n";

# Connect to the database
printf(scalar(localtime) . ": Connecting to $db_name database\n");
$dbh = DBI->connect("dbi:Pg:dbname=".$db_name ,$user ,$passwd, { RaiseError => 1, AutoCommit => 0 })
  or die "Can't open $db_name database: $DBI::errstr";
$dbh->commit();

# Uncomment next line to show SELECT statment in ERRLOG for debugging or TEST
  #print ERRLOG "     $select\n";
  print ERRLOG "  Creating Post jobs\n";

# Open the $sth and execute the query
my $sth = $dbh->prepare($select) || die "cannot prepare SQL statement";
   $sth ->execute || die "cannot execute SQL statement";

my $rowcount = 0;
while (my @row = $sth->fetchrow_array()) {
  $rowcount++;
  # Declare post variables 
  my ($lookup_type, $lookup_table) = @row;
  my $post_cmd;
  my $post_file = $lookup_type ;

  # End of record processing - Post file and write News & Data Status entry
  # Uncomment next line to show News & Data Status in ERRLOG for debugging or TEST
  print ERRLOG "    $lookup_type $lookup_table\n";
  $post_cmd = "echo " . $binpath . "/post_psc_auto_lookup.pl " . $db_name . " " . $lookup_type . " " . $lookup_table . " " . $post_file . " | /usr/local/bin/qsub";
  print "$post_cmd\n";
  system "$post_cmd >> $logfile 2>&1";

}

# Close the $sth 
  $sth->finish;
  
  print ERRLOG "  $rowcount Datafiles Processed\n";

  printf(scalar(localtime) . ": Disconnect from $db_name database\n");

# Close Error Log 
  print ERRLOG "End Time   : " . scalar(localtime) . "\n\n";
  close (ERRLOG);

# Setup_Environment
# -----------------------------------------------------------------------------
sub Setup_Environment  {

  $db_name         = $ENV{PG_DATABASE};
#  $db_name         = "rdev3"; #Uncomment for TEST
  $user            = $ENV{RMPCDB_USER};
  $passwd          = $ENV{RMPCDB_PASS};
  $binpath         = $ENV{RMPCDB_BIN};
  $logpath         = $ENV{CWT_LOG};
#  $logpath         = "/home/mcdev3/tst/log"; #REDIRECT for TEST;
  $acctpath        = $ENV{CWT_ACCT};
}