Shell Programming and Scripting

#!/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);
}

#!/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;

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

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
";