Oracle DBA by Example. Code samples, Scripts, Reference.: Code Example. Format file in and mail with blat. (perl, bacth)

========================================
General
========================================
Generate HTML mail, via perl and sqlplus

========================================
Files List
========================================
Code
send_report_main.bat
send_report_main.pl
format_db_report.pl
send_report.ini

Mail files
sendMail.bat
mail_header.txt
mail_footer.txt
mail_header_template.txt
optional_reciepients.txt

Output File
daily_refreh_report.html

====================
Files
====================
send_report_main.bat
ECHO OFF
set PATH=c:\strawberry-perl\ver_5.18.2.1\perl\site\bin;c:\strawberry-perl\ver_5.18.2.1\perl\bin;%PATH%
perl send_report_main.pl

send_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='send_report.ini';
my $LOG_FILE_BASE_NAME='Gates_Refresh_Log';
my $display_delimiter="========================================\n";
my %hashTable;

my $MAIN_HOME_WIN='';
my $MAIN_REPORT_FILE='';
my $FILE_TO_SEND = '';
my $BAT_CMD = '';
my $MAIL_CMD= '';
my $MAIL_HEADER='';
my $MAIL_HEADER_TEMPLATE='';
my $MAIL_FOOTER='';
my $MAIL_PATH='';
my $MAP_DRIVE_LOCAL = '';
my $FORMAT_TYPE='';

my $mailCmd='';
my $mailHeaderTemplateFile='';
my $mailHeaderFile='';
my $mailFooterFile='';
my $reportFileName='';
my $fileToSend = '';
my $result='';
#==================================
# subs
#==================================

sub fileFullPathNames{

$fileToSend=$MAIN_HOME_WIN."\\".$FILE_TO_SEND;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderTemplateFile=$MAIL_PATH."\\".$MAIL_HEADER_TEMPLATE;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
}

sub readIniFile{
my $i_ini_file=shift;

my $line='';
my $key='';
my $value='';

#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: ".$fileToSend;
@mailArg=($mailCmd,$fileToSend);
system(@mailArg);
}

sub printMultipath{
my $str = shift;
my $log_handler = shift;
if (defined $log_handler){
print $log_handler ($str);
}
print ($str);
}

sub setGeneralParams{
#from ini file
$MAIN_HOME_WIN = $hashTable{"MAIN_HOME_WIN"};
$MAIN_REPORT_FILE = $hashTable{"MAIN_REPORT_FILE"};
$FILE_TO_SEND = $hashTable{"FILE_TO_SEND"};
$BAT_CMD = $hashTable{"BAT_CMD"};
$MAIL_CMD = $hashTable{"MAIL_CMD"};
$MAIL_HEADER = $hashTable{"MAIL_HEADER"};
$MAIL_HEADER_TEMPLATE = $hashTable{"MAIL_HEADER_TEMPLATE"};
$MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};
$MAP_DRIVE_LOCAL = $hashTable{"MAP_DRIVE_LOCAL"};
$FORMAT_TYPE = $hashTable{"FORMAT_TYPE"};

#concatenations
$MAIL_PATH=$MAIN_HOME_WIN."\\"."Mail";

#report
print $display_delimiter;
print "Running With General Parameters:"."\n";
print "MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n";
print "MAIN_REPORT_FILE: ".$MAIN_REPORT_FILE."\n";
print "FORMAT_TYPE: ".$FORMAT_TYPE."\n";
print $display_delimiter;
}

sub formatReportFile{
my $log_handler = shift;
my $sourceFile = shift;
my $targetFile = shift;
my $header=shift;
my $footer=shift;
print "Will use source_file: ".$sourceFile." and target file: ".$targetFile."\n";
$result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $header,$footer,$FORMAT_TYPE);
$result;
}

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

sub getReportNameToSend{
my $i_line='';
open IN_FILE,$MAIN_REPORT_FILE or die $!;
while (my $i_line = <IN_FILE>){
$reportFileName = $i_line;
}
my $strLenght = length $reportFileName;
$reportFileName = $MAP_DRIVE_LOCAL.substr $reportFileName, 1 ,($strLenght-1);
print "Will fetch File ".$reportFileName."\n";
close IN_FILE or die $!;
}

sub getRunDate{
my $file = shift;
my $file_length=length($file);
my $timestamp_length=length('YYYYMMDD_hhmmss.rep');
my $run_date_time=substr $file,(-$timestamp_length-1);
my $run_date=substr $run_date_time,0,length('YYYYMMDD');
my $run_date_str=(substr $run_date,0,4)."-".(substr $run_date,4,2)."-".(substr $run_date,6,2);
$run_date_str;
}

sub formatMailHeader{
my $inputFileName=$reportFileName;
my $updateHeaderFileName=$mailHeaderFile;
my $inputHeaderFileName=$mailHeaderTemplateFile;
my $runDate=getRunDate($inputFileName);
my $i_line='';

print "About to delete file ".$updateHeaderFileName."\n";
unlink $updateHeaderFileName;

print "Opening file for Input ".$inputHeaderFileName."\n";
open IN_FILE,$inputHeaderFileName or die $!;
open OUT_FILE,">",$updateHeaderFileName or die $!;

while ($i_line=<IN_FILE>){
$i_line =~ s/YYYY-MM-DD/$runDate/;
print OUT_FILE sprintf($i_line);
}
close IN_FILE or die $!;
close OUT_FILE or die $!;
print "Done working on MailHeader"."\n";
}

#==================================
# The code starts here
#==================================

$result=main();
exit $result;

#==================================
# sub main
#==================================
sub main{
no warnings 'uninitialized';

readIniFile("$INI_FILE");
setGeneralParams();
fileFullPathNames();
getReportNameToSend();
formatMailHeader();
$result=formatReportFile(undef,$reportFileName,$fileToSend,$mailHeaderFile,$mailFooterFile);
handleStatus($result, "formatReportFile",undef);
sendMail();
}

format_db_report.pl
#====================================================
# Declaration
#====================================================
my $pl_script_name='';
my $sourceFile='';
my $targetFile='';
my $headerFile = '';
my $footerFile = '';
my $reportStyle = '';
my $status='0';

my $header="GATE_NAME REFRESH_LOG_ENTRIES";
my $hliner="-------------------------------------------------- -------------------";
my $style_html = 'HTML';
#====================================================
# Sub routines Start Here
#====================================================

sub reportInputParams{
print "Input Parameters:"."\n";
print "sourceFile = ".$sourceFile."\n";
print "targetFile = ".$targetFile."\n";
print "reportStyle = ".$reportStyle."\n";
}
sub handleTextStyle{
my $sourceFile = shift;
my $targetFile = shift;

my $line_counter=0;
open IN_FILE,$sourceFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;

print OUT_FILE $header."\n";
print OUT_FILE $hliner."\n";

while ($i_line = <IN_FILE>){
$line_counter+=1;
@words=split(" ",$i_line);
$db_name=@words[0];
$enrties=@words[1];
if ($enrties > 0){
print OUT_FILE sprintf("%-50s %-10s\n",$db_name,$enrties);
}
}
close IN_FILE or die $!;
close OUT_FILE or die $!;
}

sub handleHtmlStyle{
my $sourceFile = shift;
my $targetFile = shift;

my $line_counter=0;

open IN_FILE,$sourceFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;

print OUT_FILE "<TABLE border=\"1\">";

while ($i_line = <IN_FILE>){

$line_counter+=1;
@words=split(" ",$i_line);
$db_name=@words[0];
$enrties=@words[1];
print $i_line."\n";
if ($line_counter == 1){
#This is a header Line
print OUT_FILE "<THEAD>";
print OUT_FILE "<TR><TD>".$db_name."</TD><TD>".$enrties."</TD></TR>";
print OUT_FILE "</THEAD>";
#print OUT_FILE sprintf("%-50s %-10s\n",$db_name,$enrties);
}

if ($line_counter == 2){
#This is a underline Line
print OUT_FILE "<TBODY>";
}

if ($line_counter > 2){
if ($enrties > 0){
#print OUT_FILE "Z";
print OUT_FILE "<TR><TD>".$db_name."</TD><TD align=\"right\">".$enrties."</TD></TR>";
}
}
}
print OUT_FILE "</TBODY>";
print OUT_FILE "</TABLE>";

close IN_FILE or die $!;
close OUT_FILE or die $!;

}
sub readAndWrite{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $reportStyle = shift;

my $i_line='';
my $o_line='';

my $header_ind='Y';
my @words;

my $db_name='';
my $enrties='';

#=================================
#Add Header
#=================================
open IN_FILE,$headerFile or die $!;
open OUT_FILE,">",$targetFile or die $!;

print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
print OUT_FILE $i_line;
}
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";

close IN_FILE or die $!;
close OUT_FILE or die $!;
#=================================
#Add Contents
#=================================


if ($reportStyle eq $style_html){
handleHtmlStyle($sourceFile,$targetFile);
}


#=================================
#Add Footer
#=================================
open IN_FILE,$footerFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
print OUT_FILE $i_line;
}
print OUT_FILE "\n";

close IN_FILE or die $!;
close OUT_FILE or die $!;

$status;
}

#====================================================
# Code Start Here
#====================================================
$pl_script_name="format_db_report.pl";
$sourceFile = shift;
$targetFile = shift;
$headerFile = shift;
$footerFile = shift;
$reportStyle= shift;

#print "Inside ".$pl_script_name."\n";
reportInputParams();
$status=readAndWrite($sourceFile, $targetFile, $headerFile, $footerFile,$reportStyle);
exit $status;
#====================================================
# Code End Here
#====================================================

send_report.ini
MAIN_HOME_WIN=D:\Reports\refresh_report\send_report
MAIN_REPORT_FILE=L:\refresh_report\last_db_report.txt
FILE_TO_SEND=daily_refreh_report.html
BAT_CMD=main_from_perl.bat
MAIL_CMD=sendMail.bat
MAIL_HEADER=mail_header.txt
MAIL_HEADER_TEMPLATE=mail_header_template.txt
MAIL_FOOTER=mail_footer.txt
MAP_DRIVE_LOCAL=L
FORMAT_TYPE=HTML

sendMail.bat
blat %1 -to alexandra.takchuk@starhomemach.com -cc userA@starhomemach.com,UserB@starhomemach.com,userC@starhomemach.com,alec.kaplan@starhomemach.com -i FromUser@starhome.com -subject "Refresh Log On Gates Report." -html

mail_header.txt
<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office:word"
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1255">
<meta name=Generator content="Microsoft Word 14 (filtered medium)">
<style>
H1 {color:red;}
H2 {color:red;}
p {color:blue;}
</style>
</head>
<body>

Hi,
 
   Please see results of the Refresh Log on Gates Report.

<H2>
Refresh Log Report for 2015-11-08
</H2>

mail_footer.txt
 

Regards, 
MIS Department 
StarhomeMach 


</body>
</html>

mail_header_template.txt
<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office:word"
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1255">
<meta name=Generator content="Microsoft Word 14 (filtered medium)">
<style>
H1 {color:red;}
H2 {color:red;}
p {color:blue;}
</style>
</head>
<body>

Hi,
 
   Please see results of the Refresh Log on Gates Report.

<H2>
Refresh Log Report for YYYY-MM-DD
</H2>

optional_reciepients.txt
UserD@starhomemach.com,

daily_refreh_report.html

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office:word"
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1255">
<meta name=Generator content="Microsoft Word 14 (filtered medium)">
<style>
H1 {color:red;}
H2 {color:red;}
p {color:blue;}
</style>
</head>
<body>

Hi,
 
   Please see results of the Refresh Log on Gates Report.

<H2>
Refresh Log Report for 2015-11-08
</H2>

<TABLE border="1"><THEAD><TR><TD>GATE_NAME</TD><TD>REFRESH_LOG_ENTRIES</TD></TR></THEAD><TBODY><TR><TD>vip700/vip700@AGO-MOVIC-01_VNODE</TD><TD align="right">1</TD></TR><TR><TD>vip700/vip700@ARG-PERSO-01_VNODE</TD><TD align="right">2037</TD></TR><TR><TD>vip700/vip700@AUS-VODAF-01_VNODE</TD><TD align="right">820107</TD></TR><TR><TD>vip700/vip700@AZE-AZERF-01-VNODE</TD><TD align="right">10</TD></TR><TR><TD>vip700/vip700@BGR-MOBIL-01_VNODE</TD><TD align="right">559834</TD></TR><TR><TD>vip700/vip700@BOL-TELEC-01_VNODE</TD><TD align="right">803490</TD></TR><TR><TD>vip700/vip700@BRA-TIMRN-01_VNODE</TD><TD align="right">1031</TD></TR><TR><TD>vip700/vip700@DOM-CLARO-VHE</TD><TD align="right">10455</TD></TR><TR><TD>vip700/vip700@ECU-CONEC-01_VNODE</TD><TD align="right">23</TD></TR><TR><TD>vip700/vip700@FRA-BYTEL-01_VNODE_NEW</TD><TD align="right">1</TD></TR><TR><TD>vip700/vip700@FRA-SFRQQ-01_VNODE</TD><TD align="right">1031</TD></TR><TR><TD>vip700/vip700@GBR-VODAF-03_VHE</TD><TD align="right">1031</TD></TR><TR><TD>GTM_PCSDI_VHEQQ/GTM_PCSDI_VHEQQ@GTM_PCSDI_VHEQQ</TD><TD align="right">1</TD></TR><TR><TD>vip700/vip700@HKG-PEOPLE-01_VNODE</TD><TD align="right">28</TD></TR><TR><TD>vip700/vip700@HKG-SMART-01</TD><TD align="right">27</TD></TR><TR><TD>vip700/vip700@IDN-PTEXC-01_VNODE</TD><TD align="right">1</TD></TR><TR><TD>vip700/vip700@IDN-TELKOMCELL-VHE</TD><TD align="right">849095</TD></TR><TR><TD>vip700/vip700@IRL-EIRCE-01_VNODE</TD><TD align="right">496</TD></TR><TR><TD>vip700/vip700@KWT-MTCQQ</TD><TD align="right">17111</TD></TR><TR><TD>vip700/vip700@MDG-TELMA-01_VNODE</TD><TD align="right">4</TD></TR><TR><TD>vip700/vip700@MEX-TELCE-01_VNODE</TD><TD align="right">1</TD></TR><TR><TD>MKD_NOVIO_VHEQQ/MKD_NOVIO_VHEQQ@MKD_NOVIO_VHEQQ</TD><TD align="right">252971</TD></TR><TR><TD>vip700/vip700@MLT-VODAF-01_VNODE</TD><TD align="right">1057</TD></TR><TR><TD>vip700/vip700@MWI-TELEC-01_VNODE</TD><TD align="right">67</TD></TR><TR><TD>vip700/vip700@MYS-CELCO-01_VNODE</TD><TD align="right">318</TD></TR><TR><TD>vip700/vip700@NGA-GLOMO-01_VNODE</TD><TD align="right">1015</TD></TR><TR><TD>NIC_PCSDI_VHEQQ/NIC_PCSDI_VHEQQ@NIC_PCSDI_VHEQQ</TD><TD align="right">708935</TD></TR><TR><TD>VIP700/VIP700@PHL-DIGIT-01_VNODE</TD><TD align="right">2012</TD></TR><TR><TD>vip700/vip700@PRU-TIMPR_VNODE</TD><TD align="right">623321</TD></TR><TR><TD>vip700/vip700@SGP-MOBIL-01_VNODE</TD><TD align="right">1</TD></TR><TR><TD>SLV_CTETE_VHEQQ/SLV_CTETE_VHEQQ@SLV_CTETE_VHEQQ</TD><TD align="right">254607</TD></TR><TR><TD>VIP700/VIP700@ATN-TELCE-01_VNODE</TD><TD align="right">1031</TD></TR><TR><TD>vip700/vip700@SYC-CABWI-01_VNODE</TD><TD align="right">1</TD></TR><TR><TD>ZWE_PTCOR_VHEQQ/ZWE_PTCOR_VHEQQ@ZWE_PTCOR_VHEQQ</TD><TD align="right">38</TD></TR></TBODY></TABLE>

 

Regards, 
MIS Department 
StarhomeMach 


</body>
</html>

And as html file:

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Sunday, November 15, 2015

Code Example. Format file in and mail with blat. (perl, bacth)

No comments:

Post a Comment