Hallo Team
I can perform the task manually but i would like to automate this process. ok here goes. I have a perl script which runs every Wednesday every week and the name of the script is check_19.pl
This is how the script looks like :
#!/usr/bin/perl -w
#use strict;
use DBI;
# connect to the databases
my $dbh = DBI->connect ( 'DBI:Oracle:PAXLEY.WORLD', 'PaxReporter', 'L3xLuThvoaJi', {
PrintError => 0
} )
or die "Database connection could not be made: $DBI::errstr";
$dbh->{RowCacheSize} = 16;
my $SEL = "
select /*+parallel(v,4)*/
unique a.accountname, v.billing_period_segment_id,
accountid, callid, calldate, inserttime, ani, destination, direction
from voipcdr v, accounts a
where v.billing_period_segment_id in
(select current_usage_segment_id from billing_periods
where todate = to_date('2015-10-19', 'yyyy-mm-dd')
and fromdate = to_date('2015-09-20', 'yyyy-mm-dd' ))
and acctchargeflag = 'Y'
and v.usercode <> 'ZZZZZZ'
and a.id = v.accountid
";
my @resultSet;
my $sth = $dbh->prepare($SEL);
$sth->execute();
while ( my @record = $sth->fetchrow_array() )
{
push @resultSet, [ @record ];
}
$sth->finish;
### Email only if results were returned
if ( scalar @resultSet > 0 )
{
open (OUTPUT,">check_19.csv") || die "Cant open the file";
print OUTPUT "Accountname,BillingPeriod,AccountID,CallID,Calldate,Inserttime,Ani,Destination,Direction\n";
foreach ( @resultSet )
{
my ( $Accountname,$BillingPeriod,$AccountID,$CallID,$Calldate,$Inserttime,$Ani,$Destination,$Direction ) = @{$_};
print OUTPUT "$Accountname,$BillingPeriod,$AccountID,$CallID,$Calldate,$Inserttime,$Ani,$Destination,$Direction\n";
}
close(OUTPUT);
}
END {
$dbh->disconnect if defined($dbh);
}
Now this is how i want things to happen.
On the 20th of of each month i would like the following to happen
todate = to_date('2015-10-19', 'yyyy-mm-dd')
=> This should be the 19th of the Current Month
fromdate = to_date('2015-09-20', 'yyyy-mm-dd' )
=> This should be the 20th of the previous month.
I hope its clear. i thank you in advance.
Kind regards
Pax