#**************************************************************************************************
#WHAT:Perl script for launching SQLPLUS and calling a stored procedure
#WHERE: Runs in test environment on Genesys server 
#WHEN: A control-M job calls this perl script at 1:00 am every Wednesday
#WHO: Written by Scott MacDonald
#WHY: To delete records older than 30 days in the GENLOG instance on databases DB1 and DB2
#
#EDITS:
# 2014-12-03 - Todd McCall - Add comments to script 
#***************************************************************************************************

#************************************
#*******SET USER VARIABLES **********
#************************************
my $DBname = 'dbname';
my $account = 'genesys';
my $pass = 'password';
my $daysToKeep = 67;
my $timelimit = 480;

use MIME::Lite;

my $outputdata;
my @notify=('user1@emailserver.com', 'user2@emailserver.com');
my $from = 'Todd.McCall@emailserver.com';

my $dir = $0;# get the path and program name of this script
$dir =~ s/\\[^\\]*$//i;# remove the script name so we just have the path
my $ScriptName = $0;# get the path and program name of this script
$ScriptName =~ s/^.+\\//i;# remove the path name so we just have the script

chdir "$dir" or &CritErrorHandler("Cant Change to $dir\n");# change directory to where the code is.

my $command = "exit | sqlplus $account/$pass\@$DBname \@sql.txt";
my $SQL = "set serveroutput on\nexecute GENLOG_APP.G_LOG_CLEANUP ($daysToKeep, $timelimit)";

# Open file sql.txt to store data for email
open(SQL, ">$dir/sql.txt");

# Write the SQL command line to the file and then close it
print SQL $SQL;
close SQL;

# Launch SQL Plus
$outputdata = `$command`;
print $outputdata;


&email(shift(@notify),"$ScriptName output", $outputdata) if($outputdata);
exit 0;



sub CritErrorHandler
{
      my $CritErrors = shift;
      $CritErrors =~ s/\s*$//;
      while ($#notify>-1)
      {
            &email(shift(@notify),"$ScriptName errors", $CritErrors) if($CritErrors);
      }
      print $CritErrors . "\n";
      exit 1;
}

sub email
{
      my $from_address = $from;
      my $to_address = shift;
      my $subject = shift;

      my $mail_host = 'mailhost.pacificorp.com';
      my $message_body = shift;
      
      ### Create the multipart container
      $msg = MIME::Lite->new (
            From => $from_address,
            To => $to_address,
            #Bcc => $Bcc,
            Subject => $subject,
            Type =>'multipart/mixed'
      ) or &CritErrorHandler("Error creating multipart container: $!\n");
      
      ### Add the text message part
      $msg->attach (
            Type => 'TEXT',
            Data => $message_body
      ) or &CritErrorHandler("Error adding the text message part: $!\n");
      
      MIME::Lite->send('smtp', $mail_host, Timeout=>60);
      $msg->send;
}