#!/usr/local/bin/perl -w
# -----------------------------------------------------------------------------
# Name:        post_psc_auto_lookup.pl
# Description: Create csv and zip files of PSC Lookup Values to post to 
#              /usr/ftp/pub/data/
#
#              Called through cron by a driver program that reads Database table
#              psc_lookup_types then individually submits datasets to a que via 
#              this program to produce output data files in the /usr/ftp/pub/data/ 
#              directory
#
#              This program can be executed directly from the command line with 
#              the requred parameters listed below.
#
# Parameters:  Data Base Name, lookup_type, lookup_table, Out File
#	ex.    $RMPCDB_BIN/post_psc_auto_lookup.pl rpro release_strategy psc_lookup 
#	       release_strategy
#              
#*******************************************************************************
# -------------------------------------------------------------------
# Program History:
# INT  DATE      Comments
# ---  --------  --------
# DLW  20171116  Created
# DLW  20171121  Added "Order By 1" to select statement to sort output
# DLW  20180212  Replace zip with 7za to retain filename inside compressed file
# DLW  20180213  Update log file name.  Add Underscore & remove table name
#****************************************************************************

# -------------------------------------------------------------------
$| = 1;              #flushes the log buffer so output can be seen quicker

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

# Validating number of parameters
  if ($#ARGV != 3) {
    printf("usage: $0 db_name, lookup_type,      lookup_table, out_file\n");
    printf("ex:    $0 rpro     release_strategy, psc_lookup,   release_strategy \n");
    exit(1);
  }

# Assign global variables from parameter list @ARGV
  #$db_name       = $ARGV[0]  . ".psmfc.org";
  $db_name       = $ARGV[0];
  $lookup_type   = $ARGV[1];
  $lookup_table  = $ARGV[2];
  $out_file      = $ARGV[3]; 
#  $out_file_txt  = $out_file . ".txt";
  $out_file_txt  = $out_file . ".csv";
  $out_file_zip  = $out_file . ".zip";

  $lookup_type     = lc($lookup_type);
  $lookup_table    = lc($lookup_table);
  $out_file_path = "/usr/ftp/pub/data/"; 
#  $out_file_path = "/home/mcdev3/tst/data/"; 
  $out_file_name = $out_file_path . $out_file_txt;

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

# Open TO_FILE File
  print ERRLOG "out_file_path  : " . $out_file_name . "\n";
  $outfile = $out_file_name;
  if (! open (TO_FILE, "> " . $outfile)) {
    die "open TO_FILE for output failed $!\n";
  }

# Validate Parameters

# Print ERRLOG header record
  print ERRLOG "Database       : $db_name\n";
  print ERRLOG "lookup_type    : $lookup_type\n";
  print ERRLOG "lookup_table   : $lookup_table\n";
  print ERRLOG "Log Dir        : $logpath\n";
  print ERRLOG "Out Dir        : $out_file_path\n";
  print ERRLOG "Text File (txt): $out_file_txt\n";
  print ERRLOG "Out File  (zip): $out_file_zip\n";

# Build SQL statment
  $select = "";
  $from   = "";
  $where  = "";

  $from = $lookup_table;
  if ($lookup_table eq "psc_lookup") {
    $where = " WHERE lookup_type = " . "'" . $lookup_type . "'";
  } else {
    $where = "";
  }
  $select = "SELECT *\n";
  $select = $select . "FROM $from\n"; 
  if ($where ne "") {
    $select = $select . $where;
  }
  $select = $select . "ORDER BY 1\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();

  print ERRLOG "Creating CSV file for SQL statement:\n$select\n";

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

  @column_name = @{$cursor->{NAME}};
  foreach my $column_name (@column_name) {
    $header_line .= $column_name . ", ";
  }
    chop $header_line;
    chop $header_line;
    print TO_FILE "$header_line\r\n";

# Read from the cursor
  $rowcount = 0;
  while (@a = $cursor->fetchrow_array) {
   $rowcount++;
   $buffer = "";
   foreach $i (0 .. $#a) {
     if ($i > 0) {
       $buffer .= ",";  # Add a comma before each CSV field except first one
     }
     if (defined $a[$i] and $a[$i] ne "") {
       $a[$i] =~ s/"/""/g;  # Escape double-quote characters inside CSV fields
       $a[$i] =~ s/^\s+//;  # Get rid of leading spaces
       $a[$i] =~ s/\s+$//;  # Get rid of trailing spaces
       $buffer .= "\"" . $a[$i] . "\"";  # Add double-quotes around CSV fields
     }
   }
   print TO_FILE "$buffer\r\n";
  }

# Close the cursor
  $cursor->finish;
  
  print ERRLOG "$rowcount data records written to $outfile\n";

# Close Output File
  close (TO_FILE);

# Create Zip file
  system "cd $out_file_path ; rm $out_file_zip ; /usr/bin/7za a $out_file_zip $out_file_txt";

# 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};
  $logpath         = $ENV{CWT_LOG};
#  $acctpath        = $ENV{CWT_ACCT};
}