#!/usr/bin/perl

# look through Asterisk cdr database or cdr file and determine max usage for given time period
#
# Copyright 2006 - John Lange <john.lange@open-it.ca>
# Dec 15, 2006
#
# This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.

$|++;
use strict;
use DBI; #comment this line if you don't need database access and don't have the module
use Date::Format;
use Date::Parse;
use Text::CSV_XS; #comment this line if you don't need file access and don't have the module
#use Text::CSV; #The older name for this module. Try this if CSV_XS doesn't work for you.

my $calls = 0;
my $totalduration = 0;
my $longest = 0;
my @longestdetail;
my %peak;
my $startstamp;
my %opt;
my $verbose;
my $db;
my $host;
my $user;
my $pass;
my $dbtable;
my $infile;
my $delim;

# Command line options processing
sub init() {
	use Getopt::Std;
	my $opt_string = 'vs:f:h:u:p:d:t:';
	getopts( "$opt_string", \%opt ) or usage();
}

# Message about this program and how to use it
sub usage()
{
	print STDERR << "EOF";
usage: $0 [-v [-s <delim>]] [-f logfile] [-h dbhost] [-u dbuser] [-p dbpass] [-d database] [-t table] <"startdate"> <"enddate">
    -f	: input file - asterisk csv logfile (default none)
    -h	: mysql database host (default localhost)
    -u	: databaes user (default 'root')
    -p	: database password (default '')
    -d	: database (default 'asteriskcdrdb')
    -t	: database table (default 'cdr')
    -v	: verbose - display every record to stdout (default off)
    -s	: deliminator to use between fields during verbose output (default is tab)
    startdate : Must be in mysql compatible date format (YYYY-MM-DD HH:MM:SS)
    enddate   : Same format as startdate.

example: $0 "2006-12-01" "2006-12-31 23:59:59"
   Note: remember that mysql interprets a date with no time as midnight thus enddate should be the next day or include the time as in the example.

EOF
	exit;
}

init();

sub process {
	my @calldetail = @_;
	(my $calldate, my $src, my $dst, my $duration, my $accountcode) = @calldetail;
	if($calls == 0) {
		$startstamp = $calldate;
	}
	$calls++;
	$totalduration += $duration;
	if($duration > $longest) {
		$longest = $duration;
		@longestdetail = @calldetail;
	}
	for(my $x=0; $x < $duration; $x++) {
		$peak{($x + ($calldate - $startstamp))}++;
	}
}

if($opt{v}) {
	$verbose = 1;
} else {
	$verbose = 0;
}

if($opt{h}) {
	$host = $opt{h};
} else {
	$host = "localhost";
}
if($opt{u}) {
	$user = $opt{u};
} else {
	$user = "root";
}
if($opt{p}) {
	$pass = $opt{p};
} else {
	$pass = "";
}
if($opt{d}) {
	$db = $opt{d};
} else {
	$db = "asteriskcdrdb";
}
if($opt{t}) {
	$dbtable = $opt{t};
} else {
	$dbtable = "cdr";
}
if($opt{f}) {
	$infile = $opt{f};
}
if($opt{s}) {
	$delim = $opt{s};
} else {
	$delim = "\t";
}

my $startdate = $ARGV[0];
my $enddate = $ARGV[1];

if(!$startdate) { usage(); }
if(!$enddate) { usage(); }

if($verbose) {
	print("calldate\tsrc\tdst\tduration\taccountcode\n");
}
if(!$opt{f}) {
	my $dbh= DBI->connect("DBI:mysql:$db:host=$host", $user, $pass);
	my $sql = "SELECT UNIX_TIMESTAMP(calldate) as calldate, src, dst, duration, accountcode
              FROM $dbtable
             WHERE calldate > '$startdate'
               AND calldate < '$enddate'
          ORDER BY calldate";
	my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
	my $rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
	while (my @row = $sth->fetchrow_array()) {
		process(@row);
		if($verbose) {
			printf("%s\n", join ("$delim", @row));
		}
	}
} else {
	my $csv = Text::CSV_XS->new();
    open (CSV, "<", $infile) or die $!;
	while (<CSV>) {
		if ($csv->parse($_)) {
			(my $accountcode,my $src,my $dst,my $dcontext,my $clid,my $channel,my $dstchannel,my $lastapp,my $lastdata,my $start,my $answer,my $end,my $duration,my $billsec,my $disposition,my $amaflags) = $csv->fields();
			my $timestamp = str2time($start);
			if($verbose) {
				print("$start\t$src\t$dst\t$duration\t$accountcode\n");
			}
			if($timestamp > str2time($startdate) && $timestamp < str2time($enddate)) {
				my @row = ($timestamp, $src, $dst, $duration, $accountcode);
				process(@row);
			}
		} else {
			my $err = $csv->error_input;
			print "Failed to parse line: $err";
		}
	}
	close CSV;
}

my @peaks = sort {$peak{$b} <=> $peak{$a}} keys %peak; 	# asc by value
(my $calldate, my $src, my $dst, my $duration, my $accountcode) = @longestdetail;
my @parts = gmtime($longest); # Convert seconds to days, hours, minutes, seconds

printf("Report from: %s - %s\n", time2str("%a %b %e %T %Y", str2time($startdate)), time2str("%a %b %e %T %Y", str2time($enddate)));
printf("Total calls: %d (%d sec - %d min)\n", $calls, $totalduration, ($totalduration / 60));
printf("    Longest: %01d days %01d:%02d:%02d (%4d sec) Source: %s Date: %s - %s\n",@parts[7,2,1,0],$longest, $src, time2str("%a %b %e %T %Y", $calldate), time2str("%a %b %e %T %Y", $calldate + $duration));
printf("       Peak: %01d calls on %s\n", $peak{$peaks[0]}, time2str("%a %b %e %T %Y", $peaks[0] + $startstamp));