Oracle DBA by Example. Code samples, Scripts, Reference.: Perl by Example. Get input file, format to html, and mail.

The flow:
Get an input script, which is an extract from database.
Read the input file, and generate output file in html format.
Mail the generated file, using blat free mailing utility.

Input file:
refresh_log_report_20140423_0400000.rep

Configuration File:
send_report.ini

Code:
send_report_main.bat
format_db_report.pl
send_report_main.pl

Mailing files:
sendMail.bat
mail_header.txt
mail_footer.txt

refresh_log_report_20140423_0400000.rep
ENTRY_NAME LOG_ENTRIES
------------------------- -------------------
ENTRY_1 90009
ENTRY_2 1
ENTRY_3 89999
ENTRY_4 90036
ENTRY_5 1
ENTRY_6 3
ENTRY_7 3
ENTRY_8 128509
ENTRY_9 132225
ENTRY_10 90442

send_report.ini
MAIN_HOME_WIN=D:\REFRESH\refresh_report\send_report
MAIN_REPORT_FILE=J:\refresh_report\last_db_report.txt
#MAIN_REPORT_FILE=J:\code\Development\Managed Services\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_TEMPLATE=mail_header_template.txt
MAIL_HEADER=mail_header.txt
MAIL_FOOTER=mail_footer.txt
MAP_DRIVE_LOCAL=J
FORMAT_TYPE=HTML

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

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><b><p>".$db_name."</p></b></TD><TD><b><p>".$enrties."</p></b></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 "<TR><TD><p>".$db_name."</p></TD><TD align=\"right\"><p>".$enrties."</p></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_main.pl

#! /usr/bin/perl

#mail the input file.

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_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_TEMPLATE = $hashTable{"MAIL_HEADER_TEMPLATE"};

$MAIL_HEADER = $hashTable{"MAIL_HEADER"};

$MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};

$MAP_DRIVE_LOCAL = $hashTable{"MAP_DRIVE_LOCAL"};

$FORMAT_TYPE = $hashTable{"FORMAT_TYPE"};

#concatinations

$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='';

unlink $updateHeaderFileName;
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 $!;

}

#==================================

# 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();

$result;

}

#==================================

# The code starts here

#==================================

$result=main();

exit $result;

#====================================================
# Code End Here
#====================================================

sendMail.bat
blat %1 -to userA@someCompany.com,userB@someCompany.com -i fromItDepartment@someCompany.com -subject "Refresh Log 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>
<p>
Hi,
<br>
   Please see results of the Refresh Log Report.
</p>
<H2>
Refresh Log Report

</H2>

mail_footer.txt
<br>
<font color="green">
Regards,<BR>
IT Department<BR>
SomeCompany<BR>
</font>
</body>

</html>

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

Pages

Wednesday, April 23, 2014

Perl by Example. Get input file, format to html, and mail.

No comments:

Post a Comment