1 files changed, 150 insertions, 0 deletions
diff --git a/bin/cdr-opensips.import b/bin/cdr-opensips.import
new file mode 100755
index 000000000..54f06824c
--- /dev/null
+++ b/bin/cdr-opensips.import
@@ -0,0 +1,150 @@
+#!/usr/bin/perl
+
+use strict;
+use vars qw( $DEBUG );
+use Date::Parse 'str2time';
+use Date::Format 'time2str';
+use FS::UID qw(adminsuidsetup dbh);
+use FS::cdr;
+use DBI;
+use Getopt::Std;
+
+my %opt;
+getopts('H:U:P:D:T:s:e:', \%opt);
+my $user = shift or die &usage;
+
+my $dsn = 'dbi:mysql';
+$dsn .= ":database=$opt{D}" if $opt{D};
+$dsn .= ":host=$opt{H}" if $opt{H};
+
+my $mysql = DBI->connect($dsn, $opt{U}, $opt{P}) 
+  or die $DBI::errstr;
+
+my ($start, $end) = ('', '');
+if ( $opt{s} ) {
+  $start = str2time($opt{s}) or die "can't parse start date $opt{s}\n";
+  $start = time2str('%Y-%m-%d', $start);
+}
+if ( $opt{e} ) {
+  $end = str2time($opt{e}) or die "can't parse end date $opt{e}\n";
+  $end = time2str('%Y-%m-%d', $end);
+}
+
+adminsuidsetup $user;
+
+my $fsdbh = FS::UID::dbh;
+
+# check for existence of freesidestatus
+my $table = $opt{T} || 'acc';
+my $status = $mysql->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
+if( ! @$status ) {
+  print "Adding freesidestatus column...\n";
+  $mysql->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
+    or die $mysql->errstr;
+}
+else {
+  print "freesidestatus column present\n";
+}
+
+my @cols = ( qw( 
+  id caller_id callee_id method from_tag to_tag callid sip_code sip_reason 
+  time )
+);
+
+my $sql = 'SELECT '.join(',', @cols). " FROM $table".
+  ' WHERE freesidestatus IS NULL' .
+  ' AND sip_code = 200 ' . # only want successful calls
+  ($start && " AND time >= '$start'") .
+  ($end   && " AND time <  '$end'") .
+  ' ORDER BY time'; # should ensure INVITE/ACK/BYE order
+my $sth = $mysql->prepare($sql);
+$sth->execute;
+print "Importing ".$sth->rows." records...\n";
+
+my $cdr_batch = new FS::cdr_batch({ 
+    'cdrbatch' => 'mysql-import-'. time2str('%Y/%m/%d-%T',time),
+  });
+my $error = $cdr_batch->insert;
+die $error if $error;
+my $cdrbatchnum = $cdr_batch->cdrbatchnum;
+my $imports = 0;
+my $updates = 0;
+
+my %cdrs;
+my $row;
+while ( $row = $sth->fetchrow_hashref ) {
+  my ($callid) = $row->{'callid'};
+  $callid =~ s/@.*//;
+  if ( !$callid ) {
+    warn $row->{'time'} . ": no callid, skipped.\n";
+    next;
+  }
+  my ($src) = $row->{'caller_id'} =~ /^sip:(\d+)@/;
+  my ($dst) = $row->{'callee_id'} =~ /^sip:(\d+)@/;
+
+  my $cdr = $cdrs{$callid};
+  if ( !$cdr ) {
+    $cdr = $cdrs{$callid} = FS::cdr->new ({
+      uniqueid    => $callid,
+      cdrbatchnum => $cdrbatchnum,
+    });
+  }
+  my $date = str2time($row->{'time'});
+  if ( $row->{'method'} eq 'INVITE' ) {
+    $cdr->startdate($date);
+    $cdr->src($src);
+    $cdr->dst($dst);
+  }
+  elsif ( $row->{'method'} eq 'ACK' ) {
+    $cdr->answerdate($date);
+    next if !check_cdr($cdr, $src, $dst);
+  }
+  elsif ( $row->{'method'} eq 'BYE' ) {
+    $cdr->enddate($date);
+    next if !check_cdr($cdr, $src, $dst);
+  }
+  if ( $cdr->startdate and $cdr->answerdate and $cdr->enddate ) {
+    $cdr->duration($cdr->enddate - $cdr->startdate);
+    $cdr->billsec($cdr->enddate - $cdr->answerdate);
+    my $error = $cdr->insert;
+    if($error) {
+      print "failed import: $error\n";
+    }
+    else {
+      $imports++;
+      if( $updates += $mysql->do("UPDATE $table SET freesidestatus = 'done' 
+          WHERE sip_code = 200 AND callid = ?",
+          undef,
+          $row->{'callid'}
+        ) ) { #nothing
+      }
+      else {
+        print "failed to set status: ".$mysql->errstr."\n";
+      }
+      delete $cdrs{$callid};
+    }
+  }
+}
+print "Done.\nImported $imports CDRs, marked $updates accounting events as done.\n";
+if ( keys(%cdrs) ) {
+  print "Skipped ".scalar(keys(%cdrs))." incomplete calls.\n";
+}
+$mysql->disconnect;
+
+sub usage {
+  "Usage: \n  cdr-opensips.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\t[ -s start ] [ -e end ]\n\tfreesideuser\n";
+}
+
+sub check_cdr {
+  # Verify that these records belong to the same call.
+  # BYE records sometimes have the caller/callee fields swapped.
+  # We allow empty src/dst so as not to make noise about incomplete calls. If 
+  # this check fails, something is wrong with the source data.
+  my ($cdr, $a, $b) = @_;
+  if ( ( $cdr->src and $cdr->src ne $a and $cdr->src ne $b )
+    or ( $cdr->dst and $cdr->dst ne $a and $cdr->dst ne $b ) ) {
+    warn $cdr->uniqueid . ": src/dst mismatch, skipped.\n";
+    return 0;
+  }
+  return 1;
+}