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";
       }
?>