#!/usr/bin/perl -Tw
#
# Asterisk -- A telephony toolkit for Linux
#
# Verify that all CSV records are indeed stored in a MySQL database table
#
# Copyright (C) Joseph W. Benden <joe@thrallingpenguin.com>
#
# This program is free software, distributed under the terms of
# the GNU General Public License.
#

use DBI;
use Text::CSV;

################### BEGIN OF CONFIGURATION ####################

# the name of the cdr table
$table_name = "cdr";
# the path to the Master.csv file
$csv_master = "/var/log/asterisk/cdr-csv/Master.csv";
# the name of the box the MySQL database is running on
$hostname = "localhost";
# the name of the database our tables are kept
$database = "asterisk";
# username to connect to the database
$username = "";
# password to connect to the database
$password = "";
# debugging mode?
$debug = 0;
################### END OF CONFIGURATION #######################

$dbh = DBI->connect("dbi:mysql:dbname=$database;host=$hostname", "$username", "$password");

sub verify
{
	my (@field) = @_;
	my $count = 0;

	$statement = "SELECT COUNT(*) FROM $table_name WHERE src=".$dbh->quote($field[1])." and dst=".$dbh->quote($field[2])." and calldate=".$dbh->quote($field[9])." and channel=".$dbh->quote($field[5]);
	if ($debug) {
		print $statement, "\n";
	}
	$sth = $dbh->prepare($statement);
	$sth->execute;
	my @result = $sth->fetchrow_array;
	$count = $result[0]; 
	$sth->finish;
	return $count;
}

sub insert_cdr
{
	my (@field) = @_;
	
	$statement = "INSERT INTO $table_name (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,userfield) VALUES (".$dbh->quote($field[9]).",".$dbh->quote($field[4]).",".$dbh->quote($field[1]).",".$dbh->quote($field[2]).",".$dbh->quote($field[3]).",".$dbh->quote($field[5]).",".$dbh->quote($field[6]).",".$dbh->quote($field[7]).",".$dbh->quote($field[8]).",".$dbh->quote($field[12]).",".$dbh->quote($field[13]).",".$dbh->quote($field[14]).",".$dbh->quote($field[15]).",".$dbh->quote($field[0]).",".$dbh->quote($field[17]).")";
	if ($debug) {
		print $statement, "\n";
	}
	$dbh->do($statement);
}

my $csv = Text::CSV->new;

open CSV, "<$csv_master" or die "Cannot open master csv file: $csv_master\n";
while ( ($line = <CSV>) ) {
	chomp $line;
	if ($csv->parse($line)) {
		my @field = $csv->fields;
		if (!verify(@field)) {
			insert_cdr(@field);
		}
	} else {
		my $err = $csv->error_input;
		print "parse() failed on argument: ", $err, "\n";
	}
}
close CSV;
exit 0;

