#!/usr/bin/perl

use DBI;
use Date::Calc;
use Getopt::Std;


my %options=();
getopts('md', \%options);

#----- Start Config -----

# your database username
my $user='';

# your database password
my $pass='';

# by default this is the database for FreePBX, if you changed it, you can change it here too.
# it's safe bet to leave it like this.
my $db='asteriskcdrdb';

#----- End Config ----

my $dbh = DBI->connect( "dbi:mysql:$db", $user, $pass, {'PrintError' => 1, 'RaiseError' => 1 } );


use Date::Calc qw(:all);

#Date Calculations
($year,$month,$day,$hour,$min,$sec) = Today_and_Now();
my $today = "$year-$month-$day $hour:$min:$sec";
($year,$month,$day,$hour,$min,$sec) = Add_Delta_DHMS($year,$month,$day, $hour,$min,$sec,-30,0,0,0);
my $onemonthago = "$year-$month-$day $hour:$min:$sec";
($year,$month,$day,$hour,$min,$sec) = Add_Delta_DHMS($year,$month,$day, $hour,$min,$sec,29,0,0,0);
my $yesterday = "$year-$month-$day $hour:$min:$sec";



#print "$options{m} -- $options{d} -- $today  -  $onemonthago  - $yesterday \n\n\n";

my $subject = "Call Reports between $today and ";
my $sql ="select *,SEC_TO_TIME(duration) from cdr WHERE disposition = 'ANSWERED' AND calldate < '$today' ";

if ($options{m}) {
          $sql = $sql . "AND calldate > '$onemonthago'";
          $subject = $subject . "$onemonthago";
        }
if ($options{d}) {
          $sql = $sql . "AND calldate > '$yesterday'";
          $subject = $subject . "$yesterday";
        }

$sql = $sql . " AND duration > 20 ORDER BY calldate DESC";

#print $sql ."\n";
my $sql_handle=$dbh->prepare($sql);
$sql_handle->execute();

print $subject . "\n\n";

my $totalduration = 0;
my @data;
while (@data=$sql_handle->fetchrow_array()) {

        $calldate = $data[0];
        $clid = $data[1];
        $src = $data[2];
        $dst = $data[3];
        $dcontext = $data[4];
        $channel = $data[5];
        $dstchannel = $data[6];
        $lastapp = $data[7];
        $lastdata = $data[8];
        $duration = $data[9];
        $billsec = $data[10];
        $disposition = $data[11];
        $amaflags = $data[12];
        $accountcode = $data[13];
        $uniqueid = $data[14];
        $userfield = $data[15];
        $durminutes = $data[16];

        $totalduration = $totalduration + $duration;

if ($src == "") {$src = $channel};
print "$calldate/$durminutes: $src -> $dst \n";

}

@parts = gmtime($totalduration);

print "Total Call duration: " . $parts[2] . ":" . $parts[1] . ":" . $parts[0] ."\n";

$dbh->disconnect();