cdrsqltocsv

SQL CDR to CDR CSV

userpagelesnet

I had a situation where Asterisk stopped logging to the text file cdr-csv/Master.csv (It happened twice in 48 hours)

Luckily, I was also logging to Postgresql CDR.

I wrote this PHP script that query’s the SQL CDR and generates a nearly perfect Master.csv output file.

So what I did (to be safe) was copy my inconsistent Master.csv to Master-fubar.csv

Then I ran ./dump.php >Master.csv to re-generate my Master.csv from the SQL CDR.

#!/usr/bin/php -q
<?
$pgdb = pg_connect("dbname=asterisk_cdr");
$qry = pg_query($pgdb,"select accountcode,src,dst,dcontext,clid,channel,dstchannel,lastapp,lastdata,calldate,".
"timestamp with time zone ‘epoch’ + (date_part(‘epoch’,calldate)+duration-billsec) * interval ‘1 second’,".
"timestamp with time zone ‘epoch’ + (date_part(‘epoch’,calldate)+duration) * interval ‘1 second’,".
"duration,billsec,disposition,amaflags from cdr order by calldate");
for($x=0;$x<pg_num_rows($qry);$x++) {
$data = pg_fetch_row($qry);
$z = sizeof($data);
$data[$z-1] = "DOCUMENTATION";
if ($data[14] == "NO ANSWER") {
$data[10] = "";
}
for($y=0;$y<sizeof($data);$y++) {
$data[$y] = ereg_replace("-05$","",$data[$y]);
$data[$y] = str_replace(‘"’,’""’,$data[$y]);
if ($y) { echo ","; }
if (!(($y >= ($z-4)) && ($y <= ($z-3)))) {
$data[$y] = ‘"’.$data[$y].’"’;
}
if (($y==10) && ($data[$y] == ‘""’)) {
$data[$y] = ”;
}
echo $data[$y];
}
echo "\n";
}
?>