Date-Manipulation-1

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

Save the following Perl package as Period.pm inside the directory where you have your script.

#!/usr/bin/perl

package Period;
use strict;
use warnings;

use Exporter qw(import);
our @EXPORT_OK = qw(period);
use POSIX qw(strftime);

sub last_month {
    my @work_month = @_;
    --$work_month[4];
    if ($work_month[4] == -1) {
        $work_month[4] = 11;
        --$work_month[5];
    }
    return @work_month;
}

sub period {
    my @now = localtime;
    my @month_ago = last_month(@now);
    my $end_period = strftime("%Y-%m-19", @now);
    my $start_period = strftime("%Y-%m-20", @month_ago);
    return ($start_period, $end_period);
}
1;

Add the following at the top of your script:

use strict;
use warnings;
use Period qw(period);

Modify and add the following to your script:

my ($start, $end) = period;
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($end, 'yyyy-mm-dd') 
  and fromdate = to_date($start, 'yyyy-mm-dd' ))
  and acctchargeflag = 'Y'
  and v.usercode <> 'ZZZZZZ'
  and a.id = v.accountid
";