1. #!/usr/bin/perl
2. # Written by Garth van Sittert
3. # October 2015
4. # Version 2.0.1
5. # Copyright 2015 BitCo
6. ################################################################################
7.  
8. use DBI;
9.  
10. #   echo 'select domain_name from v_xml_cdr;' | PGPASSWORD=khbYjlU0wDyYErvvzUHC4zDht7U psql fusionpbx -U fusionpbx -h localhost | sed 's/vpbx-//' | sed 's/.bitcovoice.com//'
11. $tmpfile = "/tmp/bathfusion-lastcdr";
12. $dstchannel="SIP/bitco";
13. $disposition="ANSWERED";
14. $domain="vpbx-bathroomfusion-jhb.bitco.co.za";
15. require "/etc/bitco/tms.conf" if (-e "/etc/bitco/tms.conf");
16.  
17. exit(1) if not defined($remoteuser);
18. exit(1) if not defined($remotepass);
19.  
20. $clientcode = "bathfusion";
21. if (-e $tmpfile)
22. {
23.     $lastart_stamp = `cat $tmpfile`;
24. #    chomp($lastxml_cdr_uuid);
25.     #  $query = "SELECT calldate, clid, src, channel, dst, dstchannel, duration, billsec, disposition, accountcode, uniqueid FROM cdr WHERE uniqueid >= '$lastuniqueid' ORDER BY calldate";
26.     $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;"
27. }
28. else
29. {
30. #       $query = "SELECT calldate, clid, src, channel, dst, dstchannel, duration, billsec, disposition, accountcode, uniqueid FROM cdr ORDER BY calldate";
31. }
32.  
33. $dsnlocal = "DBI:Pg:dbname=fusionpbx;host=102.223.99.60";
34. $dbhlocal->disconnect() if $dbhlocal;
35. $dbhlocal = DBI->connect($dsnlocal, fusionpbx, khbYjlU0wDyYErvvzUHC4zDht7U) || die "Cannot connect to local MySQL server";
36.  
37. $dsnremote = "DBI:mysql:database=billing;host=tms.bitco.co.za";
38. $dbhremote->disconnect() if $dbhremote;
39. $dbhremote = DBI->connect($dsnremote, $remoteuser, $remotepass) || die "Cannot to remote MySQL server";
40.  
41.  
42. $sthlocal = $dbhlocal->prepare($query);
43. $sthlocal->execute();
44. $sthlocal->bind_columns(\$calldate, \$clid, \$src, \$dst, \$duration, \$billsec, \$accountcode);
45.  
46. while($sthlocal->fetch())
47. {
48.     $clid =~ s/\"//g;
49.     ($clid) = split(/</,$clid);
50. #    ($channel) = split(/-/,$channel);
51.     ($dstchannel) = split(/-/,$dstchannel);
52.     $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')");
53.     `echo $calldate > $tmpfile`;
54. }
55.  
56. $sthlocal->finish();
57. $dbhlocal->disconnect();
58. $dbhremote->disconnect();
59.  
60. ################################################################################
61.  
62. exit(0);
63.