#!/usr/bin/perl 
#
# $Id: cdr_import.pl,v 1.2 2007/01/15 21:54:02 nugget Exp $
#
# http://macnugget.org/projects/asterisk for more information about this script.
#
# Master.csv => "${CDR(clid)}","${CDR(src)}","${CDR(dst)}","${CDR(dcontext)}","${CDR(channel)}","${CDR(dstchannel)}","${CDR(lastapp)}","${CDR(lastdata)}","${CDR(start)}","${CDR(answer)}","${CDR(end)}","${CDR(duration)}","${CDR(billsec)}","${CDR(disposition)}","${CDR(amaflags)}","${CDR(accountcode)}","${CDR(uniqueid)}","${CDR(userfield)}"
#

my $in = '/var/log/old_asterisk/cdr-custom/Master.csv';
my $out = 'master.sql';

open IN, "$in" or die "cannot open input";
open OUT, ">$out" or die "cannot open output";

while(<IN>) {
  $buf = $_; chomp $buf;
  my ($clid, $src, $dst, $dcontext, $channel, $dstchannel, $lastapp, $lastdata, $start, $answer, $end, $duration, $billsec, $disposition, $amaflags, $accountcode, $uniqueid, $userfield) = split /,/, $buf;
  
  $clid = cleanup($clid);
  $src = cleanup($src);
  $dst = cleanup($dst);
  $dcontext = cleanup($dcontext);
  $channel = cleanup($channel);
  $dstchannel = cleanup($dstchannel);
  $lastapp = cleanup($lastapp);
  $lastdata = cleanup($lastdata);
  $start = cleanup($start);
  $answer = cleanup($answer);
  $end = cleanup($end);
  $duration = cleanup($duration);
  $billsec = cleanup($billsec);
  $disposition = cleanup($disposition);
  $amaflags = cleanup($amaflags);
  $accountcode = cleanup($accountcode);
  $uniqueid = cleanup($uniqueid);
  $userfield = cleanup($userfield);

  print OUT "INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES (";
  print OUT timestamp($start);
  print OUT varchar($clid);
  print OUT varchar($src);
  print OUT varchar($dst);
  print OUT varchar($dcontext);
  print OUT varchar($channel);
  print OUT varchar($dstchannel);
  print OUT varchar($lastapp);
  print OUT varchar($lastdata);
  print OUT numeric($duration);
  print OUT numeric($billsec);
  print OUT varchar($disposition);
#  print OUT numeric($amaflags);
  print OUT "3,";
  print OUT varchar($accountcode);
  print OUT varchar($uniqueid);
  print OUT "'" . $userfield . "'";
  print OUT ");\n";
}

# print OUT "update cdr set uniqueid = extract(epoch from calldate) || '.' || round(random()*25)*2;\n";

close OUT;
close IN;


sub cleanup {
  my ($buf) = @_;

  $buf =~ s/^"//;
  $buf =~ s/"$//;

  $buf =~ s/""/"/g;
  return $buf;
}

sub numeric {
  my ($buf) = @_;

  if($buf eq '') {
    return "NULL,";
  } else {
    return $buf . ',';
  }
}

sub varchar {
  my ($buf) = @_;
  $buf =~ s/'/\\'/g;
  return "'" . $buf . "',";
}

sub timestamp {
  my ($buf) = @_;
  $buf =~ s/'/\\'/g;
  return "'" . $buf . "',";
}