#!/usr/bin/perl 
use strict;
use warnings; 
use DBI; 
use DBD::ODBC;


# Global Variables 
my @statement;    # Array of sql statements that will be used for Sql 
my @data;        # Array of table rows read from Sybase 
my @message;    # Array of error messages used for 
debugging Sql <p>

my $destination2 =   "/processlog.txt"; 
my $errcounter = 0; 
my $succounter = 0; 
my $runtime = localtime; 
my( $sec, $min, $hour, $mday, $mon, $year ) = gmtime; 
  my $datestamp = sprintf "%02d%02d%04d", 
     $mon+1, $mday,$year+1900; 
	my $destination =   "\/BookErrorlog$datestamp.err"; 
	my $lineno = 0; 
	
# SQL Connection            

my $dbh = DBI->connect( "dbi:ODBC:RETQC_SQL", "", "", 
  {RaiseError => 1, PrintError => 1, AutoCommit => 0} ) or 
die "Unable to connect: ";#. $DBI::errstr . "\n";



# Sybase Connection 
my $dbh2 = DBI->connect
( "dbi:ODBC:BPSA", "", "",
  {RaiseError => 1, PrintError => 1, AutoCommit => 0} ) or 
die "Unable to connect: ";#. $DBI::errstr . "\n";

open( OUTFILE, ">> $destination" ) or die "Couldn't open file for reading.$!\n";

open( PROSFILE, ">> $destination2" ) or die "Couldn't open file for reading.$!\n";



# #Create the sql statement for Sybase and execute it 
my $sth = $dbh2->prepare("SELECT (dbo.tbookkeeping_trans.branch_cd + dbo.tbookkeeping_trans.account_cd)As AccountNo,
'TRI'as TransferFlag,'NCT' as TransferType, 'C' as RejectType, dbo.tbookkeeping_trans.security_adp_nbr  as AdpNumber,
dbo.tbookkeeping_trans.share_trans_qty  as Shares,dbo.tbookkeeping_trans.processing_dt
FROM dbo.tbookkeeping_trans   
WHERE (dbo.tbookkeeping_trans.client_nbr='0030' AND dbo.tbookkeeping_trans.entry_cd IN ('JNL', 'JRL', 'REC') AND
dbo.tbookkeeping_trans.branch_cd >'248') ORDER BY  dbo.tbookkeeping_trans.branch_cd , dbo.tbookkeeping_trans.account_cd,
dbo.tbookkeeping_trans.security_adp_nbr,dbo.tbookkeeping_trans.share_trans_qty,dbo.tbookkeeping_trans.processing_dt");


$sth->execute;
#print "sth=$sth/n";


#
my $insh=$dbh->prepare("INSERT INTO ClearTransferDataTemp(AccountNo, TransferFlag, TransferType, RejectType, AdpNumber, Shares, ProcessingDate,KeyId) VALUES ( ?, ?, ?, ?, ?, ?, ? ,?)");<p>
print OUTFILE "|--------------------------------------------------------------|\n";  
print OUTFILE "| BOOKKEEPING Process Date $runtime:|\n"; 
print OUTFILE "|--------------------------------------------------------------|\n";
eval { 
  while((@data) = $sth->fetchrow_array)
  {
$lineno++;<p>

my $keyId = ("$data[0]".$datestamp.$lineno);
#print "$keyId\n";
push (@data,$keyId);
#print "$keyId\n";
my $sql=("Select Count(*) From ClearTransferDataTemp	Where KeyId = '".$keyId."'");


my ($ncount) = $dbh->selectrow_array($sql);

 #print "$ncount\n";
  if ($ncount > 0)
    {
	 $errcounter++;
    print OUTFILE "@data\n";
     }
	  else
	{
  $insh->execute(@data);
  $succounter++;
   }
  }
};
if($@)
{
  print STDERR "The insert died: $@";
  print OUTFILE "The insert died: $@\n";    
}<p>

print PROSFILE "|--------------------------------------------------------------|\n";  
print PROSFILE "| BOOKKEEPING Process Date $runtime:|\n"; 
print PROSFILE "|--------------------------------------------------------------|\n";
print PROSFILE "|Records inserted  $succounter |\n";             
print PROSFILE "|Records written to error log  $errcounter |\n";
print PROSFILE "|--------------------------------------------------------------|\n"; 
print OUTFILE "|Records written to error log  $errcounter |\n";
print OUTFILE "|--------------------------------------------------------------|\n";
  
$dbh2->commit();
$dbh->commit();

 #Close the sql DB Connection 
$dbh->disconnect;

 #Close the Sybase DB connection
$sth->finish;
$dbh2->disconnect;