#!/usr/local/bin/perl -w
# -----------------------------------------------------------------------------
# Name:        post_psc_auto.pl
# Description: Create csv and zip files in PSC 041 format to post to 
#              /usr/ftp/pub/data/
#
#              Called through cron by a driver program that reads Oracle table
#              load_dates_041 then individually submits datasets to a que via 
#              this program to produce output data files of the days validated 
#              data 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, File Type, Agency, Year, Out File
#	ex.    $RMPCDB_BIN/post_psc_auto.pl cpro rl ADFG none rl_ADFG_none
#              
#*******************************************************************************
# -------------------------------------------------------------------
# Program History:
# INT  DATE      Comments
# ---  --------  --------
# DLW  20140609  Copied from post_psc.pl removed specific acct_id param and
#                replaced with path to /usr/ftp/pub/data/
#---------------  2015 Oracle to Postgres Database Conversion  -------------
#
# DLW  20150910  Modifications for Oracle to Postgres Database Conversion
#                include changes to environment variable, database references,
#                SQL functions & some possible logic flow where necessary.
# DLW  20170922  Added elsif 'dd' to match code and capability added in mcdev3
#                test environment on Feb 2nd 2017.  This script can be run
#                in production to extract DD fies on demand like other types
# DLW  20180208  Update example to show new filename convention               
#****************************************************************************

# -------------------------------------------------------------------
$| = 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 != 4) {
    printf("usage: $0 db_name, file_type, agency, year, out_file\n");
    printf("ex:    $0 rpro        cs       ADFG   1999  CS041_ADFG_1999\n");
    exit(1);
  }

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

  $file_type     = lc($file_type);
  $agency        = uc($agency);
  $out_file_path = "/usr/ftp/pub/data/"; 
#  $out_file_path = "/home/mcdev3/tst/data/"; #uncomment for TEST 
  $out_file_name = $out_file_path . $out_file_txt;

# Open ERRLOG Log File
  if ($file_type =~ /^(rl|lc|dd)$/) {
    $log_file_name = "post_psc_auto_" . $file_type . "_" . $agency . "_FULLSET";
  } else {
    $log_file_name = "post_psc_auto_" . $file_type . "_" . $agency . "_" . $year;
  }
  $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
  $process = "Y";
  if ($file_type =~ /^(rl|lc|dd)$/) {
    $year = lc($year);
    if ($year ne "none") {
      printf ERRLOG "year parameter must be 'none' for this file_type [%s][%s]\n", $year, $file_type;
      $process = "N";
    }
  } elsif ($file_type =~ /^(rc|cs|ce)$/) {
    if ($year !~ /^\d{4}$/) {
      printf ERRLOG "valid year required for this file_type [%s][%s]\n", $year, $file_type;
      $process = "N";
    }
  } else {
      printf ERRLOG "file_type not recognized [%s]\n", $file_type;
      $process = "N";
  }

  if ($agency !~ /^[ABCDEFGHIJKLMNOPQRSTUVWXYZ]{3,4}$/) {
    printf ERRLOG "invalid agency [%s]\n", $agency;
    $process = "N";
  }

# Print ERRLOG header record
  print ERRLOG "Database       : $db_name\n";
  print ERRLOG "File Type      : $file_type\n";
  print ERRLOG "Agency         : $agency\n";
  print ERRLOG "Year           : $year\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  = "";
  if ($file_type eq "rl") {
    $from = "query_psc_rl_041";
    if ($agency ne "ALL") {
      $where = " WHERE reporting_agency = " . "'" . $agency . "'";
    }
  } elsif ($file_type eq "lc") {
    $from = "query_psc_lc_041";
    if ($agency ne "ALL") {
     $where = " WHERE reporting_agency = " . "'" . $agency . "'";
    }
  } elsif ($file_type eq "dd") {
    $from = "query_psc_dd_041";
    if ($agency ne "ALL") {
     $where = " WHERE reporting_agency = " . "'" . $agency . "'";
    }
  } elsif ($file_type eq "rc") {
    $from = "query_psc_rc_041";
     $where = " WHERE reporting_agency = " . "'" . $agency . "'" . " AND run_year = " . $year;
  } elsif ($file_type eq "ce") {
    $from = "query_psc_ce_041";
     $where = " WHERE reporting_agency = " . "'" . $agency . "'" . " AND catch_year = " . $year;
  } elsif ($file_type eq "cs") {
    $from = "query_psc_cs_041";
     $where = " WHERE reporting_agency = " . "'" . $agency . "'" . " AND catch_year = " . $year;
  }
  $select = "SELECT *\n";
  $select = $select . "FROM $from\n"; 
  if ($where ne "") {
    $select = $select . $where;
  }

# 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};
}