========================
General
========================
Send files by ftp with perl.
Scheduled Task is activating bat file.
bat file is calling perl program.
perl program is setting customer speific details and calling a generic program.
ftp parameters - are read from database
files to send - all files under source directory.
In case of an success - move file to backup directory.
In case of an error - send an email.
========================
File List
========================
main_send_cdrs.bat
main_send_cdrs.pl
gen_send_cdrs.pl
sendMail.bat
mailBody.txt
========================
File Code
========================
main_send_cdrs.bat
perl main_send_cdrs.pl
main_send_cdrs.pl
#! /usr/bin/perl
use DBI;
use Time::gmtime;
use File::Copy;
use Net::FTP;
use strict;
use warnings;
#-------------------------------------------------------------------
# Global Variables - these variables need to be known in routines
#-------------------------------------------------------------------
my $dbh;
############################################################################
#####A subroutine to get the current date ##################################
############################################################################
sub getDate
{
use Time::gmtime;
my $tm=gmtime;
my $day;
my $month;
my $year;
($day,$month,$year) = (gmtime) [3,4,5];
my $locDate=sprintf("%04d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday);
return $locDate;
}
############################################################################
#####A subroutine to get the current time ##################################
############################################################################
sub getTime
{
use Time::gmtime;
my $lt=gmtime;
my $hour;
my $minute;
my $seconds;
($hour,$minute,$seconds)=(gmtime) [1,2];
my $LocTime = sprintf ("%02d%02d%02d",$lt->hour,$lt->min,$lt->sec);
return $LocTime
}
#---------------------------------------
# Open Database Connection
#---------------------------------------
sub OpenDatabaseConnection
{
my $db_user=shift;
my $db_pass=shift;
my $db_connect_str=shift;
print "----I---- Opening Database Connection ".$db_user."/".$db_pass."@".$db_connect_str."\n";
$dbh=DBI->connect("DBI:Oracle:".$db_connect_str,$db_user,$db_pass,{RaiseError =>1,AutoCommit=>0})||die "$DBI::errstr";
print "----I---- Database Connection Opened"."\n";
}
sub CloseDatabaseConnection
{
$dbh->disconnect;
}
#--------------------------------------------------------
sub GetFtpData
{
my $gate_id=shift;
my $IPAddress;
my $FTPUser;
my $FtpPass;
my $ftpDir;
my $sql;
my $sth;
$sql = qq{SELECT server_ip_address, ftp_user, ftp_password, remote_cdr_dir FROM XFER_FTP_PARAMS WHERE customer_id=?};
$sth=$dbh->prepare($sql);
$sth->execute($gate_id);
$sth->bind_columns(undef,\$IPAddress,\$FTPUser,\$FtpPass,\$ftpDir);
$sth->fetch();
$sth->finish();
return ($IPAddress,$FTPUser,$FtpPass,$ftpDir);
}
#--------------------------------------------------------
#===================================================
#sub routines End
#===================================================
#==========================================
# main()
#==========================================
my $myGate = "291";
my $IPAddress= "444.333.222.111";
my $FTPUsr="ftpuser";
my $FTPPass="ftppass";
my $RemoteDir="/Starhome/";
#-------------------------------------------------------------------------
# Local
#-------------------------------------------------------------------------
my $rootDir="D:\\Data\\CdrXfer";
my $customerRootDir="EMTEL_Inbound";
my $localFilesDir=$rootDir."\\".$customerRootDir."\\"."Files";
my $oldFilesDir=$rootDir."\\".$customerRootDir."\\"."Oldfiles";
#-------------------------------------------------------------------------
# CGW Database Connection
#-------------------------------------------------------------------------
my $db_user='db_user';
my $db_pass='db_pass';
my $connection_str='orainst';
#-------------------------------------------------------------------------
# Other
#-------------------------------------------------------------------------
my $primaryExtension="dat";
my $oldExtension="old";
my $myScript='main_send_cdrs.pl';
my $runMode;
my $retCode;
my $runDate;
my $runTime;
my $mailScript='';
my $mailBatScript='';
my $oldName;
my $logFile;
#-------------------------------------------------------------------------
# Start execution
#-------------------------------------------------------------------------
$runMode='PROD';
#-------------------------------------------------------------------------
# For TEST mode - override the PROD settings
#-------------------------------------------------------------------------
#$runMode='TEST';
if ($runMode eq 'TEST'){
$db_user='db_user_test';
$db_pass='db_pass_test';
$connection_str='orainst';
$myGate = "999";
$IPAddress= "666.555.444.333";
$FTPUsr="ftp_user_test";
$FTPPass="ftp_pass_test";
$RemoteDir="/starhome/iu/XFER_FILES/TEST/";
$customerRootDir="TEST";
$localFilesDir=$rootDir."\\".$customerRootDir."\\"."Files";
$oldFilesDir=$rootDir."\\".$customerRootDir."\\"."Oldfiles";
}
#-------------------------------------------------------------------------
# Set parameters before calling gen_send_cdrs.pl
#-------------------------------------------------------------------------
$runDate=getDate;
$runTime=getTime;
$logFile=$rootDir."\\".$customerRootDir."\\Logs\\runLog_".$runDate."_".$runTime.".txt";
$mailBatScript='sendMail.bat';
$mailScript=$rootDir."\\".$customerRootDir."\\".$mailBatScript;
print "----I---- ---------------------------------"."\n";
OpenDatabaseConnection($db_user,$db_pass,$connection_str);
($IPAddress,$FTPUsr,$FTPPass,$RemoteDir)=GetFtpData($myGate);
print "----I---- ---------------------------------"."\n";
print "----I---- FTP Details"."\n";
print "----I---- Customer Remote Server Details. Server: ".$IPAddress." User: ".$FTPUsr." Remote Dir: ".$RemoteDir."\n";
print "----I---- ---------------------------------"."\n";
CloseDatabaseConnection();
#-------------------------------------------------------------------------
# Call gen_send_cdrs.pl
#-------------------------------------------------------------------------
#my $result=system($^X,"gen_send_cdrs.pl",
my $result=system($^X,"D:\\CDR_XFER\\GENERIC_CODE\\gen_send_cdrs.pl",
#00
$myGate,
$IPAddress,
$FTPUsr,
$FTPPass,
$RemoteDir,
#05
$rootDir,
$customerRootDir,
$localFilesDir,
$oldFilesDir,
$primaryExtension,
#10
$oldExtension,
$logFile,
$runDate,
$runTime,
$mailScript,
#15
$db_user,
$db_pass,
$connection_str
#20
);
print $myScript." Finished with Status: ".$result."\n";
exit;
gen_send_cdrs.pl
#! /usr/bin/perl
use DBI;
use Net::FTP;
use Time::gmtime;
use File::Copy;
use strict;
use warnings;
#================================
# Input Parameters
#================================
#00
my $myGate=shift;
my $IPAddress=shift;
my $FTPUsr=shift;
my $FTPPass=shift;
my $RemoteDir=shift;
#05
my $rootDir=shift;
my $customerRootDir=shift;
my $localFilesDir=shift;
my $oldFilesDir=shift;
my $primaryExtension=shift;
#10
my $oldExtension=shift;
my $logFile=shift;
my $runDate=shift;
my $runTime=shift;
my $mailScript=shift;
#15
my $db_user=shift;
my $db_pass=shift;
my $connection_str=shift;
#20
#================================
# Global Parameters
#================================
my $retCode;
my $dbh;
#===================================================
#sub routines Start
#===================================================
sub FTPFile
{
my $ipAddress=shift;
my $FTPuser=shift;
my $FTPPass=shift;
my $FTPdir=shift;
my $localFilesDir=shift;
my $fileName=shift;
my $newerr=0;
my $ftp;
$ftp=Net::FTP->new($ipAddress,Passive => 1,Timeout=>240) or $newerr=1 ;
if ($newerr==1){
print MyLog "----E---- Cannot connect to server: ".$ipAddress."\n";
#$ftp->quit;
return 0;
}
print MyLog "----I---- Connected to Remote server: ".$ipAddress."\n";
$ftp->login($FTPuser,$FTPPass) or $newerr=1 ;
if ($newerr==1){
print MyLog "----E---- Cannot login with credentials: ".$FTPuser."/".$FTPPass."\n";
#$ftp->quit;
return 0;
}
print MyLog "----I---- Logged with user: ".$FTPuser."\n";
$ftp->cwd($FTPdir) or $newerr=1 ;
if ($newerr==1){
print MyLog "----E---- Cannot Change Directory to ".$FTPdir."\n";
#$ftp->quit;
return 0;
}
print MyLog "----I---- Directory changed to ".$FTPdir."\n";
$ftp->binary;
$ftp->put($localFilesDir."/".$fileName) or $newerr=1 ;
if ($newerr==1){
print MyLog "----W---- Cannot put file ".$fileName." from local Directory: ".$localFilesDir."\n";
sleep 5;
$newerr=0;
print MyLog "----I---- Going to sleep 5 seconds before second attempt";
$ftp->put($localFilesDir."/".$fileName) or $newerr=1 ;
if ($newerr==1){
print MyLog "----W---- Cannot put file ".$fileName." from local Directory: ".$localFilesDir."\n";
#$ftp->quit;
return 0;
}
}
print MyLog "----I---- Successfully sent file ".$fileName." to remote Directory: ".$FTPdir."\n";
$ftp-> quit;
return 1;
}
sub updateXFERFilesTable
{
my $p_db_user=shift;
my $p_db_pass=shift;
my $p_connection_str=shift;
my $p_sent_ind=shift;
my $p_customer_id=shift;
my $p_file_name=shift;
my $newerr;
my $ora_conn_str='dbi:Oracle:'.$p_connection_str;
#---------------------------------------
# Open Database Connection
#---------------------------------------
print MyLog "----I---- Opening Database Connection ".$ora_conn_str."\n";
$dbh=DBI->connect($ora_conn_str,$p_db_user,$p_db_pass,{RaiseError =>1,AutoCommit=>0})||die "$DBI::errstr";
print MyLog "----I---- Database Connection Opened"."\n";
print MyLog "----I---- Running SQL: UPDATE XFER_FILES SET sent_ind=".$p_sent_ind." WHERE customer_id=".$p_customer_id." AND file_name=".$p_file_name."\n";
my $sql = qq{UPDATE XFER_FILES SET sent_ind=? WHERE customer_id=? AND file_name=?};
my $sth=$dbh->prepare($sql);
$sth->execute($p_sent_ind,$p_customer_id,$p_file_name);
$dbh->commit;
$sth->finish();
$dbh->disconnect;
print MyLog "----I---- SQL Completed Successfully."."\n";
return 1;
}
sub sendMail{
my $sendMailbat=shift;
my $logFile=shift;
my @mailArg;
@mailArg=($sendMailbat,$logFile);
sleep 1;
system(@mailArg);
}
#===================================================
#sub routines End
#===================================================
#==========================================
# main()
#==========================================
print "using log file: ".$logFile."\n";
unlink $logFile;
open (MyLog,">>".$logFile);
print MyLog "### Strating CDR XFER process for date: ".$runDate." ".$runTime." ###"."\n";
print MyLog "----I---- Sending files to server: ".$IPAddress." with login user: ".$FTPUsr." Remote Folder: ".$RemoteDir."\n";
$retCode=1;
my $f;
my $oldName;
opendir(IMD,$localFilesDir)||die ("cannot open dir".$localFilesDir);
print MyLog "----I---- Processing files under Local Directory ".$localFilesDir."\n";
while ($f=readdir(IMD))
{
if ($f=~/\.$primaryExtension$/)
{
print MyLog "\n";
print MyLog "----I---- Currently processing file: ".$f."\n";
# Remove extension from file name
$oldName=$f;
$oldName=~s/\.$primaryExtension$/\.$oldExtension/;
$retCode=FTPFile($IPAddress,$FTPUsr,$FTPPass,$RemoteDir,$localFilesDir,$f);
if ($retCode !=1 )
{
print MyLog "----E---- FATAL ERROR. Cannot FTP ".$f." to Customer."."\n";
close(MyLog);
sendMail($mailScript,$logFile);
die;
}
else
{
## Move the file into the oldFile directory
move($localFilesDir."/".$f,$oldFilesDir."/".$oldName);
# Mark the file as SENT_IND=1 in the XFER_FILES table
$retCode=0;
$retCode=updateXFERFilesTable($db_user, $db_pass,$connection_str,"1",$myGate,$f);
if ($retCode!=1)
{
print "----E---- FATAL ERROR. Cannot update XFER_FILES table"."\n";
print MyLog "----E---- FATAL ERROR. Cannot update XFER_FILES table"."\n";
close(MyLog);
sendMail($mailScript,$logFile);
$dbh->disconnect;
die;
}
else
{
print MyLog "File ".$f." in XFER_FILES table was successfully updated."."\n";
}
}
}
}
print MyLog "----I---- CDR XFER process has finished successfully"."\n";
print MyLog "### End CDR XFER process ###"."\n";
close(MyLog);
sendMail.bat
blat mailBody.txt -to alec.kaplan@starhomemach.com -i mis@starhome.com -subject "CDR XFER Failure for EMTEL" -attacht %1
mailBody.txt
Hi All,
A Fatal Error Occurred during Files Transfer.
Thanks,
MIS Department
StarhomeMACH
Showing posts with label Perl. Show all posts
Showing posts with label Perl. Show all posts
Monday, June 27, 2016
Sunday, November 15, 2015
Perl, Batch, sqlplus code example. Generate extract from several DB.
====================
General
====================
Connect to DB listed in ini file.
And append output to one HTML type output.
====================
Files List
====================
refresh_report_main.bat
refresh_report_main.pl
main_from_perl.bat
run_sql.bat
refresh_log_sql.sql
refresh_report.ini
db_list.ini
last_db_report.txt
gates_refresh_log_stats.txt
====================
Files
====================
refresh_report_main.bat
perl refresh_report_main.pl
refresh_report_main.pl
#! /usr/bin/perl
#Daily process for reading from refresh_log table on gates
use DateTime;
use strict;
use warnings;
#===================
#Global Parameters
#===================
my $INI_FILE='refresh_report.ini';
my $LOG_FILE_BASE_NAME='Gates_Refresh_Log';
my $display_delimiter="========================================\n";
my %hashTable;
my $MAIN_HOME_WIN='';
my $BAT_CMD = '';
my $MAIL_CMD= '';
my $MAIL_HEADER='';
my $MAIL_FOOTER='';
my $MAIL_PATH='';
my $INI_FILE_FOR_DB;
my $DB_LOG='';
my $LOG_FILE_PATH='';
my $LOG_FILE_NAME='';
my $REPORT_FILE_PATH='';
my $REPORT_FILE='';
my $LAST_DB_REPORT_FILE='';
my $timestamp='';
my $logFile='';
my $dbLogFile='';
my $reportFile='';
my $mailCmd='';
my $mailHeaderFile='';
my $mailFooterFile='';
my $lastReportFile;
my $run_date='';
my $result='';
#==================================
# subs
#==================================
sub setRundate{
my $dateTime=DateTime->now;
my $month=$dateTime->month;
my $year=$dateTime->year;
if ($month < 10){ $month="0".$month ; }
my $day=$dateTime->day;
if ($day < 10){ $day="0".$day ; }
my $hh=$dateTime->hour;
if ($hh < 10){ $hh="0".$hh ; }
my $mm=$dateTime->minute;
if ($mm < 10){ $mm="0".$mm ; }
my $ss=$dateTime->second;
if ($ss < 10){ $ss="0".$ss ; }
$run_date=$dateTime->year.$month.$day;
$timestamp=$run_date."_".$hh.$mm.$ss;
print "Running time: ".$timestamp."\n";
}
sub fileFullPathNames{
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
}
sub setLogFileName{
$logFile=$LOG_FILE_PATH."\\".$LOG_FILE_NAME."_".$timestamp.".log";
my $status=0;
#print "Will try to create now new folder: ".$LOG_FILE_PATH;
if (!-d "$LOG_FILE_PATH" ){
my $cmd = "mkdir "."\"".$LOG_FILE_PATH."\"";
$status=system($cmd);
}
if ($status == 0 ){
print "\n";
print $display_delimiter;
print "\n";
print "Log File: ".$logFile."\n";
print "\n";
print $display_delimiter;
print "\n";
}
$status;
}
sub readIniFile{
my $i_ini_file=shift;
my $line='';
my $key='';
my $value='';
my $match_str = "CUSTOMER_[0-9][0-9]";
#read from ini file
open(FILE_HANDLER, "$i_ini_file") or die ("Could not open ini file: "."$i_ini_file"."\n");
my $count=0;
foreach $line (<FILE_HANDLER>){
chomp($line);
if ($line =~ /^$/) {
#nothing. Ignore empty lines
}elsif($line =~ /^#/) {
#nothing. Ignore # comment lines lines
}else{
($key, $value)=split(/=/,$line);
$hashTable{$key} = $value;
}
}
close (FILE_HANDLER);
}
sub sendMail{
my @mailArg = "";
my $sendMailInd=0;
print "Running command: ".$mailCmd." to send contents of file: ".$reportFile;
@mailArg=($mailCmd,$reportFile);
system(@mailArg);
}
sub printMultipath{
my $str = shift;
my $log_handler = shift;
print $log_handler ($str);
print ($str);
}
sub setGeneralParams{
#from ini file
$MAIN_HOME_WIN = $hashTable{"MAIN_HOME_WIN"};
$INI_FILE_FOR_DB=$hashTable{"INI_FILE_FOR_DB"};
$BAT_CMD = $hashTable{"BAT_CMD"};
$DB_LOG=$hashTable{"DB_LOG"};
$REPORT_FILE=$hashTable{"REPORT_FILE"};
$MAIL_CMD = $hashTable{"MAIL_CMD"};
$MAIL_HEADER = $hashTable{"MAIL_HEADER"};
$MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};
$LAST_DB_REPORT_FILE= $hashTable{"LAST_DB_REPORT_FILE"};
#concatinations
$LOG_FILE_PATH=$MAIN_HOME_WIN."\\"."Logs";
$REPORT_FILE_PATH=$MAIN_HOME_WIN."\\"."Reports";
$LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
$MAIL_PATH=$MAIN_HOME_WIN."\\"."Mail";
$lastReportFile=$MAIN_HOME_WIN."\\".$LAST_DB_REPORT_FILE;
#report
print $display_delimiter;
print "Running With General Parameters:"."\n";
print "MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n";
print "BAT_CMD: ".$BAT_CMD."\n";
print "INI_FILE_FOR_DB: ".$INI_FILE_FOR_DB."\n";
#print "MAIL_CMD: ".$MAIL_CMD."\n";
print "DB_LOG: ".$DB_LOG."\n";
print "Report: ".$REPORT_FILE."\n";
print $display_delimiter;
}
sub runOsCommand{
my $log_handler = shift;
my $cmd = shift;
my $ini_file = shift;
print "Running Command: ".$cmd."\n";
#$result=system($^X,$cmd);
my @SendArg=($cmd,$ini_file, $dbLogFile);
$result=system(@SendArg);
#print "Exit point after call to ".$cmd." Exist status is: ".$result."\n";
$result;
}
sub updateLastDbReport{
my $lastReportFile=shift;
my $reportFile=shift;
open OUT_FILE,">",$lastReportFile or die $!;
print OUT_FILE $reportFile."\n";
close OUT_FILE or die $!;
}
sub formatReportFile{
my $log_handler = shift;
my $sourceFile = shift;
my $targetFile = shift;
my $header=shift;
my $footer=shift;
$result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $header,$footer);
$result;
}
sub reportGeneralParameters{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Input Parameters: \n",$log_handler);
printMultipath ("Run Date:".$run_date."\n",$log_handler);
printMultipath ("Log File: ".$logFile."\n",$log_handler);
printMultipath ("MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n",$log_handler);
printMultipath ("BAT_CMD: ".$BAT_CMD."\n",$log_handler);
printMultipath ("MAIL_CMD: ".$MAIL_CMD."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportStartOfProcess{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Refresh Log On Gates Checkup Process Started at: ".$timestamp."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportEndOfProcess{
my $log_handler = shift;
my $status = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
if ($status == 0){
printMultipath ("Process has Finished Successfuly"."\n",$log_handler);
}else{
printMultipath ("Error!!!! Process has Terminated with Error."."\n",$log_handler);
}
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
$status;
}
sub handleStatus{
my $status = shift;
my $method = shift;
my $log_handler = shift;
if ($status != 0){
if (defined $log_handler){
printMultipath ("Error in Routine: ".$method." Status: ".$status."\n",$log_handler);
}else{
print ("Error in Routine: ".$method." Status: ".$status."\n");
}
return $status;
}
$status;
}
#==================================
# The code starts here
#==================================
$result=main();
exit $result;
#==================================
# sub main
#==================================
sub main{
no warnings 'uninitialized';
setRundate();
readIniFile("$INI_FILE");
setGeneralParams();
$result=setLogFileName();
handleStatus($result, "setLogFileName",undef);
open my $LOG_HANDLER, '>', $logFile or die ("Could not open log file: ".$logFile);
fileFullPathNames();
reportStartOfProcess($LOG_HANDLER);
reportGeneralParameters($LOG_HANDLER);
my $cmd='"'.$MAIN_HOME_WIN."\\".$BAT_CMD.'"';
$result=runOsCommand($LOG_HANDLER,$cmd,$INI_FILE_FOR_DB);
handleStatus($result, $cmd,undef);
#$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$mailHeaderFile,$mailFooterFile);
$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,"NULL","NULL");
handleStatus($result, "formatReportFile",undef);
updateLastDbReport($lastReportFile,$reportFile);
reportEndOfProcess($LOG_HANDLER, $result);
close $LOG_HANDLER;
#sendMail(); - mail is send from a server with access to mail server
}
main_from_perl.bat
ECHO OFF
SET inifile=%1%
SET REFRESH_LOG=%2%
SET TEMP_REFRESH_LOG=temp_gates_refresh_log_stats.txt
ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%REFRESH_LOG%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
ECHO handling %%i
ECHO. 2>%TEMP_REFRESH_LOG%
call run_sql.bat %TEMP_REFRESH_LOG% %%i
TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO.
)
ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================
run_sql.bat
SET spool_to_file=%1
SET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO refresh_log_sql %db_name%
sqlplus -l -s %db_name% @refresh_log_sql %spool_to_file% %db_name%
refresh_log_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON
SPOOL &&1
SELECT '&&2' AS GATE_NAME, COUNT(*) AS REFRESH_LOG_ENTRIES FROM REFRESH_LOG;
SPOOL OFF
EXIT;
/
General
====================
Connect to DB listed in ini file.
And append output to one HTML type output.
====================
Files List
====================
refresh_report_main.bat
refresh_report_main.pl
main_from_perl.bat
run_sql.bat
refresh_log_sql.sql
refresh_report.ini
db_list.ini
last_db_report.txt
gates_refresh_log_stats.txt
====================
Files
====================
refresh_report_main.bat
perl refresh_report_main.pl
refresh_report_main.pl
#! /usr/bin/perl
#Daily process for reading from refresh_log table on gates
use DateTime;
use strict;
use warnings;
#===================
#Global Parameters
#===================
my $INI_FILE='refresh_report.ini';
my $LOG_FILE_BASE_NAME='Gates_Refresh_Log';
my $display_delimiter="========================================\n";
my %hashTable;
my $MAIN_HOME_WIN='';
my $BAT_CMD = '';
my $MAIL_CMD= '';
my $MAIL_HEADER='';
my $MAIL_FOOTER='';
my $MAIL_PATH='';
my $INI_FILE_FOR_DB;
my $DB_LOG='';
my $LOG_FILE_PATH='';
my $LOG_FILE_NAME='';
my $REPORT_FILE_PATH='';
my $REPORT_FILE='';
my $LAST_DB_REPORT_FILE='';
my $timestamp='';
my $logFile='';
my $dbLogFile='';
my $reportFile='';
my $mailCmd='';
my $mailHeaderFile='';
my $mailFooterFile='';
my $lastReportFile;
my $run_date='';
my $result='';
#==================================
# subs
#==================================
sub setRundate{
my $dateTime=DateTime->now;
my $month=$dateTime->month;
my $year=$dateTime->year;
if ($month < 10){ $month="0".$month ; }
my $day=$dateTime->day;
if ($day < 10){ $day="0".$day ; }
my $hh=$dateTime->hour;
if ($hh < 10){ $hh="0".$hh ; }
my $mm=$dateTime->minute;
if ($mm < 10){ $mm="0".$mm ; }
my $ss=$dateTime->second;
if ($ss < 10){ $ss="0".$ss ; }
$run_date=$dateTime->year.$month.$day;
$timestamp=$run_date."_".$hh.$mm.$ss;
print "Running time: ".$timestamp."\n";
}
sub fileFullPathNames{
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
}
sub setLogFileName{
$logFile=$LOG_FILE_PATH."\\".$LOG_FILE_NAME."_".$timestamp.".log";
my $status=0;
#print "Will try to create now new folder: ".$LOG_FILE_PATH;
if (!-d "$LOG_FILE_PATH" ){
my $cmd = "mkdir "."\"".$LOG_FILE_PATH."\"";
$status=system($cmd);
}
if ($status == 0 ){
print "\n";
print $display_delimiter;
print "\n";
print "Log File: ".$logFile."\n";
print "\n";
print $display_delimiter;
print "\n";
}
$status;
}
sub readIniFile{
my $i_ini_file=shift;
my $line='';
my $key='';
my $value='';
my $match_str = "CUSTOMER_[0-9][0-9]";
#read from ini file
open(FILE_HANDLER, "$i_ini_file") or die ("Could not open ini file: "."$i_ini_file"."\n");
my $count=0;
foreach $line (<FILE_HANDLER>){
chomp($line);
if ($line =~ /^$/) {
#nothing. Ignore empty lines
}elsif($line =~ /^#/) {
#nothing. Ignore # comment lines lines
}else{
($key, $value)=split(/=/,$line);
$hashTable{$key} = $value;
}
}
close (FILE_HANDLER);
}
sub sendMail{
my @mailArg = "";
my $sendMailInd=0;
print "Running command: ".$mailCmd." to send contents of file: ".$reportFile;
@mailArg=($mailCmd,$reportFile);
system(@mailArg);
}
sub printMultipath{
my $str = shift;
my $log_handler = shift;
print $log_handler ($str);
print ($str);
}
sub setGeneralParams{
#from ini file
$MAIN_HOME_WIN = $hashTable{"MAIN_HOME_WIN"};
$INI_FILE_FOR_DB=$hashTable{"INI_FILE_FOR_DB"};
$BAT_CMD = $hashTable{"BAT_CMD"};
$DB_LOG=$hashTable{"DB_LOG"};
$REPORT_FILE=$hashTable{"REPORT_FILE"};
$MAIL_CMD = $hashTable{"MAIL_CMD"};
$MAIL_HEADER = $hashTable{"MAIL_HEADER"};
$MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};
$LAST_DB_REPORT_FILE= $hashTable{"LAST_DB_REPORT_FILE"};
#concatinations
$LOG_FILE_PATH=$MAIN_HOME_WIN."\\"."Logs";
$REPORT_FILE_PATH=$MAIN_HOME_WIN."\\"."Reports";
$LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
$MAIL_PATH=$MAIN_HOME_WIN."\\"."Mail";
$lastReportFile=$MAIN_HOME_WIN."\\".$LAST_DB_REPORT_FILE;
#report
print $display_delimiter;
print "Running With General Parameters:"."\n";
print "MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n";
print "BAT_CMD: ".$BAT_CMD."\n";
print "INI_FILE_FOR_DB: ".$INI_FILE_FOR_DB."\n";
#print "MAIL_CMD: ".$MAIL_CMD."\n";
print "DB_LOG: ".$DB_LOG."\n";
print "Report: ".$REPORT_FILE."\n";
print $display_delimiter;
}
sub runOsCommand{
my $log_handler = shift;
my $cmd = shift;
my $ini_file = shift;
print "Running Command: ".$cmd."\n";
#$result=system($^X,$cmd);
my @SendArg=($cmd,$ini_file, $dbLogFile);
$result=system(@SendArg);
#print "Exit point after call to ".$cmd." Exist status is: ".$result."\n";
$result;
}
sub updateLastDbReport{
my $lastReportFile=shift;
my $reportFile=shift;
open OUT_FILE,">",$lastReportFile or die $!;
print OUT_FILE $reportFile."\n";
close OUT_FILE or die $!;
}
sub formatReportFile{
my $log_handler = shift;
my $sourceFile = shift;
my $targetFile = shift;
my $header=shift;
my $footer=shift;
$result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $header,$footer);
$result;
}
sub reportGeneralParameters{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Input Parameters: \n",$log_handler);
printMultipath ("Run Date:".$run_date."\n",$log_handler);
printMultipath ("Log File: ".$logFile."\n",$log_handler);
printMultipath ("MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n",$log_handler);
printMultipath ("BAT_CMD: ".$BAT_CMD."\n",$log_handler);
printMultipath ("MAIL_CMD: ".$MAIL_CMD."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportStartOfProcess{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Refresh Log On Gates Checkup Process Started at: ".$timestamp."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportEndOfProcess{
my $log_handler = shift;
my $status = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
if ($status == 0){
printMultipath ("Process has Finished Successfuly"."\n",$log_handler);
}else{
printMultipath ("Error!!!! Process has Terminated with Error."."\n",$log_handler);
}
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
$status;
}
sub handleStatus{
my $status = shift;
my $method = shift;
my $log_handler = shift;
if ($status != 0){
if (defined $log_handler){
printMultipath ("Error in Routine: ".$method." Status: ".$status."\n",$log_handler);
}else{
print ("Error in Routine: ".$method." Status: ".$status."\n");
}
return $status;
}
$status;
}
#==================================
# The code starts here
#==================================
$result=main();
exit $result;
#==================================
# sub main
#==================================
sub main{
no warnings 'uninitialized';
setRundate();
readIniFile("$INI_FILE");
setGeneralParams();
$result=setLogFileName();
handleStatus($result, "setLogFileName",undef);
open my $LOG_HANDLER, '>', $logFile or die ("Could not open log file: ".$logFile);
fileFullPathNames();
reportStartOfProcess($LOG_HANDLER);
reportGeneralParameters($LOG_HANDLER);
my $cmd='"'.$MAIN_HOME_WIN."\\".$BAT_CMD.'"';
$result=runOsCommand($LOG_HANDLER,$cmd,$INI_FILE_FOR_DB);
handleStatus($result, $cmd,undef);
#$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$mailHeaderFile,$mailFooterFile);
$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,"NULL","NULL");
handleStatus($result, "formatReportFile",undef);
updateLastDbReport($lastReportFile,$reportFile);
reportEndOfProcess($LOG_HANDLER, $result);
close $LOG_HANDLER;
#sendMail(); - mail is send from a server with access to mail server
}
main_from_perl.bat
ECHO OFF
SET inifile=%1%
SET REFRESH_LOG=%2%
SET TEMP_REFRESH_LOG=temp_gates_refresh_log_stats.txt
ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%REFRESH_LOG%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
ECHO handling %%i
ECHO. 2>%TEMP_REFRESH_LOG%
call run_sql.bat %TEMP_REFRESH_LOG% %%i
TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO.
)
ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================
run_sql.bat
SET spool_to_file=%1
SET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO refresh_log_sql %db_name%
sqlplus -l -s %db_name% @refresh_log_sql %spool_to_file% %db_name%
refresh_log_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON
SPOOL &&1
SELECT '&&2' AS GATE_NAME, COUNT(*) AS REFRESH_LOG_ENTRIES FROM REFRESH_LOG;
SPOOL OFF
EXIT;
/
refresh_report.ini
MAIN_HOME_WIN=L:\refresh_report
INI_FILE_FOR_DB=db_list.ini
BAT_CMD=main_from_perl.bat
DB_LOG=gates_refresh.log
REPORT_FILE=refresh_log_report
MAIL_CMD=sendMail.bat
MAIL_HEADER=mail_header.txt
MAIL_FOOTER=mail_footer.txt
LAST_DB_REPORT_FILE=last_db_report.txt
db_list.ini
vip700/vip700@AGO-MOVIC-01_VNODE
vip700/vip700@ALB-VODAF-01_VNODE
vip700/vip700@ARG-PERSO-01_VNODE
vip700/vip700@AUS-VODAF-01_VNODE
vip700/vip700@AUT-CONNE-01_VNODE
vip700/vip700@AZE-AZERF-01-VNODE
vip700/vip700@BGR-MOBIL-01_VNODE
vip700/vip700@BOL-TELEC-01_VNODE
vip700/vip700@BRA-TIMRN-01_VNODE
vip700/vip700@CAN-ROGWI-01_DNODE
====================
Output Files
====================
Output Files
====================
last_db_report.txt
L:\refresh_report\Reports\refresh_log_report_20151108_140031.rep
gates_refresh_log_stats.txt
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
vip700/vip700@PRT-TELEC-01_VNODE 17
GATE_NAME REFRESH_LOG_ENTRIES
----------------------- -------------------
vip700/vip700@BGR-MOBIL 89539
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
vip700/vip700@PRT-TELEC-01_VNODE 17
GATE_NAME REFRESH_LOG_ENTRIES
----------------------- -------------------
vip700/vip700@BGR-MOBIL 89539
Subscribe to:
Posts (Atom)