#!/usr/bin/perl
# Written by Garth van Sittert
# October 2015
# Version 2.0.1
# Copyright 2015 BitCo
################################################################################

use DBI;

#   echo 'select domain_name from v_xml_cdr;' | PGPASSWORD=khbYjlU0wDyYErvvzUHC4zDht7U psql fusionpbx -U fusionpbx -h localhost | sed 's/vpbx-//' | sed 's/.bitcovoice.com//'
$tmpfile = "/tmp/bathfusion-lastcdr";
$dstchannel="SIP/bitco";
$disposition="ANSWERED";
$domain="vpbx-bathroomfusion-jhb.bitco.co.za";
require "/etc/bitco/tms.conf" if (-e "/etc/bitco/tms.conf");

exit(1) if not defined($remoteuser);
exit(1) if not defined($remotepass);

$clientcode = "bathfusion";
if (-e $tmpfile)
{
    $lastart_stamp = `cat $tmpfile`;
#    chomp($lastxml_cdr_uuid);
    #  $query = "SELECT calldate, clid, src, channel, dst, dstchannel, duration, billsec, disposition, accountcode, uniqueid FROM cdr WHERE uniqueid >= '$lastuniqueid' ORDER BY calldate";
    $query = "SELECT start_stamp as calldate,caller_id_number as clid,caller_id_name as src,destination_number as dst,duration,billsec,pin_number as accountcode FROM v_xml_cdr WHERE start_stamp >= '$lastart_stamp' and billsec > 3 and direction = 'outbound' and domain_name = '$domain' ORDER BY start_stamp;"
}
else
{
#       $query = "SELECT calldate, clid, src, channel, dst, dstchannel, duration, billsec, disposition, accountcode, uniqueid FROM cdr ORDER BY calldate";
}

$dsnlocal = "DBI:Pg:dbname=fusionpbx;host=102.223.99.60";
$dbhlocal->disconnect() if $dbhlocal;
$dbhlocal = DBI->connect($dsnlocal, fusionpbx, khbYjlU0wDyYErvvzUHC4zDht7U) || die "Cannot connect to local MySQL server";

$dsnremote = "DBI:mysql:database=billing;host=tms.bitco.co.za";
$dbhremote->disconnect() if $dbhremote;
$dbhremote = DBI->connect($dsnremote, $remoteuser, $remotepass) || die "Cannot to remote MySQL server";


$sthlocal = $dbhlocal->prepare($query);
$sthlocal->execute();
$sthlocal->bind_columns(\$calldate, \$clid, \$src, \$dst, \$duration, \$billsec, \$accountcode);

while($sthlocal->fetch())
{
    $clid =~ s/\"//g;
    ($clid) = split(/</,$clid);
#    ($channel) = split(/-/,$channel);
    ($dstchannel) = split(/-/,$dstchannel);
    $dbhremote->do("INSERT INTO cdr (calldate, clientcode, clid, src, channel, dst, dstchannel, duration, billsec, disposition, accountcode, uniqueid) VALUES('$calldate', '$clientcode', '$clid', '$src', 'SIP/$src', '$dst', '$dstchannel', '$duration', '$billsec', '$disposition', '$accountcode', '$clientcode')");
    `echo $calldate > $tmpfile`;
}

$sthlocal->finish();
$dbhlocal->disconnect();
$dbhremote->disconnect();

################################################################################

exit(0);