Description
====================
The is a input file, in csv format, OutCounters.txt which is loaded via perl and INSERT statements into DB.
Files List
====================
Code
load_counters.bat
o2ukosms_load_counters.pl
Counter file
OutCounters.txt_20151109063400
====================
Files
====================
load_counters.bat
perl load_counters.pl
load_counters.pl
#! /usr/bin/perl
use DBI;
use Net::FTP;
use Time::gmtime;
use File::Copy;
############################################################################
#####A subroutine to get the current date ##################################
############################################################################
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;
}
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;
}
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:\\SOURCE";
my $newerr=0;
my $logDate= getDate;
my $logTime= getTime;
my $CounterFile;
#for debug messages- set to 1
my $debugInd=0;
#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 = "100.200.300.400";
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="055";
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=0;
#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:orainst','db_user','db_pass',{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)
{
if ($debugInd==1){print MyLoadCountersLogFile "Starting Loading line ".$Dline."\n";}
chomp($Dline);
# ($field0,$field1,$field2,$field3,$field4,$field5,$field6,$field7,$field8,$field9,$field10,$field11,$field12,$field13)=split(/\,/,$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;
}
# if ($field11=="NA")
# {
# $field11=0;
# }
# if ($field12=="NA")
# {
# $field12=0;
# }
# if ($field13=="NA")
# {
# $field13=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,"TTL_NUM_LU",$IntervalID,0,$field4,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading Counter #2002. IntervalID: ".$IntervalID." counter_delta: ".$field4."\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,"NUM_LU_OK",$IntervalID,0,$field5,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading NUM_LU_OK 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,"NUM_LU_ERR",$IntervalID,0,$field6,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading NUM_LU_ERR 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,"NUM_SUB_TTL_LU",$IntervalID,0,$field7,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading NUM_SUB_TTL_LU 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,"TTL_NUM_SENT_MSG",$IntervalID,0,$field8,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading TTL_NUM_SENT_MSG 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,"NUM_SENT_SUB_SMS",$IntervalID,0,$field9,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading NUM_SENT_SUB_SMS 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,"NUM_SUB_SMS_OK",$IntervalID,0,$field10,$field0);
if ($debugInd==1){print MyLoadCountersLogFile "Loading NUM_SUB_SMS_OK 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";
OutCounters.txt20151109063400
20151109,06,15,Total,23730,23699,0,23699,552,552,874
No comments:
Post a Comment