The flow of events is:
- Get file by FTP.
- Read the file line by line
- Parse each line into fields
- Load content, line by line, into DB
The files:
launch_load_counters.bat
load_counters.pl
launch_load_counters.bat
perl load_counters.pl
load_counters.pl
#! /usr/bin/perl
use DBI;
use Net::FTP;
use Time::gmtime;
use File::Copy;
#-----------------------------
# get 15 minutes interval ID
#-----------------------------
sub CalcInterval{
local($hour,$minute)=($_[0],$_[1]);
my $intid=(($hour*3600)+($minute*60))/900;
if ($intid == 0)
{
$intid=95
}
else
{
$intid=$intid-1;
}
return $intid;
}
#-----------------------------
# get Current Date
#-----------------------------
sub getDate{
use Time::gmtime;
$tm=gmtime;
($day,$month,$year) = (gmtime) [3,4,5];
my $locDate=sprintf("%04d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday);
return $locDate;
}
#-----------------------------
# get Current Time
#-----------------------------
sub getTime{
use Time::gmtime;
$lt=gmtime;
($hour,$minute,$seconds)=(gmtime) [1,2];
my $LocTime = sprintf ("%02d%02d%02d",$lt->hour,$lt->min,$lt->sec);
return $LocTime
}
#----------------------------------
sub GetCounterFile
#----------------------------------
{
my $home_dir="d:\\COUNTERS";
my $newerr=0;
my $logDate= getDate;
my $logTime= getTime;
my $CounterFile;
#for debug messages- set to 1
my $debugInd=1;
#for using test file set to 1
my $loadTestData=0;
if ($debugInd==1){
my $log_file = $home_dir."\\"."counters\\Get_Counter_File".$logDate."_".$logTime.".log";
open MyFtpLogFile,">>".$log_file;
print MyFtpLogFile "start MyFtpLogFile for :".$logDate."_".$logTime."\n";
}
if ($loadTestData==1){
$CounterFile = "test_counters.txt";
}else{
$CounterFile = "OutCounters.txt.fin";
}
if ($debugInd==1){print MyFtpLogFile "Loading from file ".$CounterFile."\n";}
my $OrigCounterFile = "OutCounters.txt";
my $LocCounterDir=$home_dir."\\"."counters";
my $ScriptDir =$home_dir."\\"."scripts\\";
my $CounterBckDir = $home_dir."\\"."counters\\old_counters_files\\";
my $address = "000.000.000.000";
my $username="user";
my $pass="pass";
my $rem_dir="counters/";
my $PDate=getDate;
my $PTime=getTime;
if ($debugInd==1){print MyFtpLogFile "Before chdir(".$LocCounterDir.")"."\n";}
chdir($LocCounterDir);
if ($debugInd==1){print MyFtpLogFile "After chdir(".$LocCounterDir.")"."\n";}
$ftp=Net::FTP->new($address,Timeout=>240) or die "Cant FTP to host" ;
if ($debugInd==1){print MyFtpLogFile "After opening ftp connection"."\n";}
$ftp->login($username,$pass) or die "Cant login to host";
if ($debugInd==1){print MyFtpLogFile "After ftp login"."\n";}
$ftp->cwd($rem_dir) or die "Cant cd to remote directory";
$ftp->pwd or die "Cant pwd";
if ($debugInd==1){print MyFtpLogFile "After cd to remote directory ".$rem_dir."\n";}
$ftp->binary;
if ($debugInd==1){print MyFtpLogFile "After set ftp mode to binary"."\n";}
if ($debugInd==1){print MyFtpLogFile "Before ftp get ".$CounterFile."\n";}
$ftp->get($CounterFile,$CounterFile) or $newerr=1;
if ($debugInd==1){
print MyFtpLogFile "Got file from server: ".$CounterFile."\n";
print MyFtpLogFile "FTP return status=".$newerr."\n";
if ($newerr==1){
print MyFtpLogFile "----E---- "."Cannot get file ".$CounterFile." from remote Directory: ".$rem_dir."\n";
$ftp->quit;
return 0;
}
}
$ftp->rename($CounterFile,$CounterFile.$PDate.$PTime);
if ($debugInd==1){print MyFtpLogFile "After ftp rename from ".$CounterFile." to ".$CounterFile.$PDate.$PTime."\n";}
$ftp-> quit;
move($CounterFile,$OrigCounterFile);
if ($debugInd==1){
print MyFtpLogFile "After local rename from ".$CounterFile." to ".$OrigCounterFile."\n";
print MyFtpLogFile $OrigCounterFile." file created under folder: ".$LocCounterDir."\n";
close(MyFtpLogFile);
}
chdir($ScriptDir);
return 1;
}
sub LoadCounterFile
{
#declare variables
my $home_dir="d:\\COUNTERS";
my $customer_id="001";
my $DateOfCall;
my $DayOfCall;
my $processName="";
my $IntervalID;
my $LocCounterDir=$home_dir."\\"."counters\\";
my $OrigCounterFile= "OutCounters.txt";
my $logDate= getDate;
my $logTime= getTime;
#for debug set debugInd to 1
my $debugInd=1;
#to skip load to db set debugSkipLoadToDbInd to 1
my $debugSkipLoadToDbInd=0;
if ($debugInd==1){
my $log_file = $home_dir."\\"."counters\\Load_Counters".$logDate."_".$logTime.".log";
open MyLoadCountersLogFile,">>".$log_file;
print MyLoadCountersLogFile "Start Loading Counters for :".$logDate."_".$logTime."\n";
print MyLoadCountersLogFile "Before opening DB connection"."\n";
}
##Connect to the db
my $dbh=DBI->connect('dbi:Oracle:sid','user','passwd',{RaiseError =>1,AutoCommit=>0})||die "$DBI::errstr";
print $DBI::errstr;
if ($debugInd==1){
print MyLoadCountersLogFile "The DBI:err: ".$DBI::errstr."\n";
print MyLoadCountersLogFile "After opening DB connection"."\n";
print MyLoadCountersLogFile "Before opening Counters File ".$LocCounterDir.$OrigCounterFile."\n";
print MyLoadCountersLogFile "Running in mode debugSkipLoadToDbInd=".$debugSkipLoadToDbInd."\n";
}
##read the file and prepare the insets
open(MyCountFile,$LocCounterDir.$OrigCounterFile) or die("Cannot open file ".$LocCounterDir.$OrigCounterFile);
@DataLine = <MyCountFile>;
close(MyCountFile);
if ($debugInd==1){print MyLoadCountersLogFile "After opening MyCountFile ".$LocCounterDir.$OrigCounterFile."\n";}
##use the data
foreach $Dline (@DataLine)
{
print MyLoadCountersLogFile "Starting Loading line ".$Dline."\n";
chomp($Dline);
($field0,$field1,$field2,$field3,$field4,$field5,$field6,$field7,$field8,$field9,$field10)=split(/\,/,$Dline);
$IntervalID=CalcInterval($field1,$field2);
$DayOfCall=substr($field0,6,2);
$nowDate=getDate;
$nowTime=getTime;
$lastUpdateTime=$field0.$field1.$field2;
if ($field4=="NA"){$field4=0; }
if ($field5=="NA"){$field5=0; }
if ($field6=="NA"){$field6=0; }
if ($field7=="NA"){$field7=0; }
if ($field8=="NA"){$field8=0; }
if ($field9=="NA"){$field9=0; }
if ($field10=="NA"){$field10=0; }
my $sql = qq{insert into counter_data (customer_id,day_of_call,counter_id,counter_name,interval_id,counter_sum,counter_delta,date_of_call) values(?,?,?,?,?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute ($customer_id,$DayOfCall,2002,"COUNTER_1",$IntervalID,0,$field4,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading COUNTER_1 line "."\n";}
if ($debugSkipLoadToDbInd==1){
$dbh->rollback;
}else{
$dbh->commit;
}
$sth->finish();
my $sql = qq{insert into counter_data (customer_id,day_of_call,counter_id,counter_name,interval_id,counter_sum,counter_delta,date_of_call) values(?,?,?,?,?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute ($customer_id,$DayOfCall,2003,"COUNTER_2",$IntervalID,0,$field5,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading COUNTER_2 line "."\n";}
if ($debugSkipLoadToDbInd==1){
$dbh->rollback;
}else{
$dbh->commit;
}
$sth->finish();
my $sql = qq{insert into counter_data (customer_id,day_of_call,counter_id,counter_name,interval_id,counter_sum,counter_delta,date_of_call) values(?,?,?,?,?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute ($customer_id,$DayOfCall,2004,"COUNTER_3",$IntervalID,0,$field6,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading COUNTER_3 line "."\n";}
if ($debugSkipLoadToDbInd==1){
$dbh->rollback;
}else{
$dbh->commit;
}
$sth->finish();
my $sql = qq{insert into counter_data (customer_id,day_of_call,counter_id,counter_name,interval_id,counter_sum,counter_delta,date_of_call) values(?,?,?,?,?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute ($customer_id,$DayOfCall,2009,"COUNTER_4",$IntervalID,0,$field7,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading COUNTER_4 line "."\n";}
if ($debugSkipLoadToDbInd==1){
$dbh->rollback;
}else{
$dbh->commit;
}
$sth->finish();
my $sql = qq{insert into counter_data(customer_id,day_of_call,counter_id,counter_name,interval_id,counter_sum,counter_delta,date_of_call) values(?,?,?,?,?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute ($customer_id,$DayOfCall,3001,"COUNTER_5",$IntervalID,0,$field8,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading COUNTER_5 line "."\n";}
if ($debugSkipLoadToDbInd==1){
$dbh->rollback;
}else{
$dbh->commit;
}
$sth->finish();
my $sql = qq{insert into counter_data (customer_id,day_of_call,counter_id,counter_name,interval_id,counter_sum,counter_delta,date_of_call) values(?,?,?,?,?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute($customer_id, $DayOfCall, 3003, "COUNTER_6", $IntervalID,0,$field9,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading COUNTER_6 line "."\n";}
if ($debugSkipLoadToDbInd==1){
$dbh->rollback;
}else{
$dbh->commit;
}
$sth->finish();
$field10=~ s/\D+//g;
my $sql = qq{insert into counter_data (customer_id,day_of_call,counter_id,counter_name,interval_id,counter_sum,counter_delta,date_of_call) values(?,?,?,?,?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute ($customer_id,$DayOfCall,3006,"COUNTER_7",$IntervalID,0,$field10,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading COUNTER_6 line "."\n";}
if ($debugSkipLoadToDbInd==1){
$dbh->rollback;
}else{
$dbh->commit;
}
$sth->finish();
if ($debugInd==1){
print MyLoadCountersLogFile "Finished Loading line ".$Dline."\n";
}
}
if ($debugInd==1){
print MyLoadCountersLogFile "Load Counters to DB Completed"."\n";
close(MyLoadCountersLogFile);
}
return 1;
}
#---------------
#main()
#---------------
#declare variables
my $home_dir="d:\\COUNTERS";
my $log_file = $home_dir."\\"."counters\\ProcessLog.txt";
my $logDate= getDate;
my $logTime= getTime;
open MyLogFile,">>".$log_file;
print MyLogFile "Start Process for: ".$logDate."_".$logTime."\n";
#get the counter file by ftp
GetCounterFile;
print MyLogFile "After FTP get counters....";
#Load the counters into the db
LoadCounterFile;
print MyLogFile "After LoadCounters..."."\n";
my $targetFile = $home_dir."\\"."counters\\old_counters_files\\OutCounters.txt".$logDate.$logTime;
move($home_dir."\\"."counters\\OutCounters.txt",$targetFile);
print MyLogFile "New File Created: ".$targetFile."\n";
print MyLogFile "End Process for: ".$logDate."_".$logTime."\n\n";
exit 0;
