#!/usr/local/bin/perl -w

#Set the packages to use
#(DBD::ORACLE and DBI need to be downloaded from Active State/CPAN)
use DBI;
#
# Installation Specific Variables
#
my $oratab       = '/var/opt/oracle/oratab';
my $user_id      = 'system';
my $password_exe = '/u01/oracle/scripts/shell/getorapw';
my $dba_notify_list  = '';
my $user_notify_list = '';
#
#Set variables used in this script
#
my $sid;
my $oracle_home;
my $nls = 'AMERICAN';
my $user_pwd;
my $ctlfile;
my $logfile;
my $errors;
my $oracle_base="/u01/oracle";
my $openFile = "$oracle_base/scripts/sqlldr/licagt.data.bad";
my $printfile = "$oracle_base/scripts/sqlldr/licagt.data.bad.csv";
my $ftpdir = "/ftp/ftponly";
my $ftparchive = "/ftp/ftponly/archive";
#
#Usage statement
#
if (scalar @ARGV != 4)
{
        warn "Usage: licagt_sqlldr.pl {ORACLE_SID} {CTL_FILE} {LOG_FIL
+E} {ERRORS}\n";
        print "\t{ORACLE_SID} is the database you wish to connect to.\
+n";
        print "\t{CTL_FILE} is the name of the SQL*Loader control file
+ for this load.\n";
        print "\t{LOG_FILE} is the name of the log file for this load.
+\n";
        print "\t{ERRORS} is the number of errors you wish to allow fo
+r this load.\n";
        exit();
}
$sid = $ARGV[0];
$ctlfile = $ARGV[1];
$logfile = $ARGV[2];
$errors = $ARGV[3];
#
# Set the Oracle environment
#
$oracle_home=qx(cat $oratab |grep $sid |cut -d: -f2);
chomp($oracle_home);

$ENV{NLS_LANG} = $nls;

if (! $oracle_home) {
  print $sid . " not defined in " . $oratab . "\n";
  exit();
} else {
  $ENV{ORACLE_HOME} = $oracle_home;
}

$user_pwd=qx($password_exe $sid $user_id);
chomp($user_pwd);

if (! $user_pwd) {
  print "Unable to obtain password for user " . $user_id . "\n";
  exit();
}
#
#Set and run the SQL*Loader command and email results
#

system("$oracle_home/bin/sqlldr $user_id/$user_pwd@$sid control=$oracl
+e_base/scripts/sqlldr/$ctlfile errors=$errors log=$oracle_base/script
+s/sqlldr/$logfile >/dev/null 2>&1");

my $results = qx(tail -20 $oracle_base/scripts/sqlldr/$logfile);
my @dba_to_email = split ';', $dba_notify_list;


for(my $cnt=0;($cnt <= ((scalar @dba_to_email)-1));$cnt++)
{
    my $email = $dba_to_email[$cnt];
    my $mail_command ="/usr/bin/mailx -s\"Agent_License SQL*Loader Loa
+d Status\" $email << EOF
$results
EOF";
    system($mail_command);    
}

my @user_to_email = split ';', $user_notify_list;

if(-e "$oracle_base/scripts/sqlldr/licagt.data.bad")
{
    open (FILE,"$openFile") || die "Unable to open $openFile: $!\n";
    open (WFILE,">$printfile") || die "Unable to open $printfile: $!\n
+";
    
    print WFILE "agt-company|agtprod|agtsprod|agtseq|agtname|agtaddres
+s1|agtaddress2|agtcity|".
    "agtstate|agtzip|agtcounty|agtlicstate|agttermdate|agtssn|agtlicnu
+mb|".
    "agtdob|agtphone|agtcmpapptdate|agtcmptermdate|agtnationalprodnum|
+agtfiller\n";
    
    while (<FILE>)
    {   
        if(/\z/)
        {
            if(length($_)==241)
            {
                print WFILE substr($_,0,2)."|".substr($_,2,6)."|".subs
+tr($_,8,6)."|".substr($_,14,3)."|".substr($_,17,32)."|".
                      substr($_,49,32)."|".substr($_,81,32)."|".substr
+($_,113,14)."|".substr($_,127,2)."|".substr($_,129,11)."|".
                      substr($_,140,15)."|".substr($_,155,2)."|".subst
+r($_,157,8)."|".substr($_,165,9)."|".substr($_,174,12)."|".
                      substr($_,186,8)."|".substr($_,194,10)."|".subst
+r($_,204,8)."|".substr($_,212,8)."|".substr($_,220,10)."|".
                      substr($_,230,10)."\n";
            }
            else
            {
                print WFILE "Not a valid line\n";
            }
        }
    }

    close(FILE) || warn "Unable to close $openFile: $!\n";
    close(WFILE) || warn "Unable to close $printfile: $!\n";
    
   for(my $cnt=0;($cnt <= ((scalar @user_to_email)-1));$cnt++)
    {
      my $email = $user_to_email[$cnt];
      my $mail_command2 ="cat $oracle_base/scripts/sqlldr/licagt.data.
+bad.csv | uuencode licagt.data.bad.csv | /usr/bin/mailx -s\"Agent_Lic
+ense SQL*Loader Load Bad Data\" $email";
      system($mail_command2);
    }
}

#
#Zip and archive the original datafile
#

if(-e "$ftpdir/licagt.data")
{
    @f = (localtime)[3..5];

    $month = $f[1] +1;
    $day = $f[0];
    $year = $f[2] + 1900;

    rename("$ftpdir/licagt.data", "$ftparchive/licagt.data.$month$day$
+year");
    system("gzip $ftparchive/licagt.data.$month$day$year");
}

#
#Clean up un-needed files
#

if(-e "$ftpdir/licagt.data"){unlink ("$ftpdir/licagt.data")};
if(-e "$oracle_base/scripts/sqlldr/licagt.data.bad"){unlink("$oracle_b
+ase/scripts/sqlldr/licagt.data.bad")};
if(-e "$oracle_base/scripts/sqlldr/licagt.data.bad.csv"){unlink("$orac
+le_base/scripts/sqlldr/licagt.data.bad.csv")};
if(-e "$oracle_base/scripts/sqlldr/licagt.log"){unlink("$oracle_base/s
+cripts/sqlldr/licagt.log")};
[download]