X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2Fbin%2Ffreeside-cdr-asterisk_sql;h=529ec9bb977d69bde290086df7a22ec06ef6e1f9;hb=11bbf29de447fe39e9d7155fe280a0df70fa8c3c;hp=5c90ab48b72dfe1a2ae03df5a88997f5740c9e7f;hpb=5259890066c6a6dc90048b209cfb86550d5336a4;p=freeside.git

diff --git a/FS/bin/freeside-cdr-asterisk_sql b/FS/bin/freeside-cdr-asterisk_sql
index 5c90ab48b..529ec9bb9 100755
--- a/FS/bin/freeside-cdr-asterisk_sql
+++ b/FS/bin/freeside-cdr-asterisk_sql
@@ -9,8 +9,10 @@ use FS::cdr;
 use DBI;
 use Getopt::Std;
 
+$DEBUG = 0;
+
 my %opt;
-getopts('e:H:U:P:D:T:', \%opt);
+getopts('e:H:U:P:D:T:V:', \%opt);
 my $user = shift or die &usage;
 
 my $engine = $opt{e} || 'mysql';
@@ -32,21 +34,42 @@ my $table = $opt{T} || 'cdr';
 if ( $engine =~ /^mysql/ ) {
   my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
   if( ! @$status ) {
-    print "Adding freesidestatus column...\n";
+    warn "Adding freesidestatus column...\n" if $DEBUG;
     $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
       or die $dbi->errstr;
   } else {
-    print "freesidestatus column present\n";
+    warn "freesidestatus column present\n" if $DEBUG;
+  }
+}
+
+my @cols = ();
+if ( $opt{V} >= 12 ) {
+  push @cols, qw( id start answer end );
+} else {
+  push @cols, qw( calldate );
+}
+
+push @cols,  qw( 
+  clid src dst dcontext channel lastapp lastdata duration 
+  billsec disposition amaflags accountcode userfield
+);
+
+# check for existence of uniqueid
+if ( $engine =~ /^mysql/ ) {
+  my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'uniqueid'");
+  if( @$status ) {
+    push @cols, 'uniqueid';
   }
+} else {
+  #assume there's a uniqueid
+  push @cols, 'uniqueid';
 }
 
-my @cols = ( qw( 
-calldate clid src dst dcontext channel lastapp lastdata duration 
-    billsec disposition amaflags accountcode uniqueid userfield) );
-my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
-my $sth = $sql->prepare($sql);
+my $sql =
+  'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
+my $sth = $dbi->prepare($sql);
 $sth->execute;
-print "Importing ".$sth->rows." records...\n";
+warn "Importing ".$sth->rows." records...\n" if $DEBUG;
 
 my $cdr_batch = new FS::cdr_batch({ 
     'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time),
@@ -54,40 +77,58 @@ my $cdr_batch = new FS::cdr_batch({
 my $error = $cdr_batch->insert;
 die $error if $error;
 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
+
 my $imports = 0;
 my $updates = 0;
 
-my $row;
-while ( $row = $sth->fetchrow_hashref ) {
+while ( my $row = $sth->fetchrow_hashref ) {
+
   my $cdr = FS::cdr->new($row);
-  $cdr->startdate(str2time($cdr->calldate));
+
+  if ( $opt{V} >= 12 ) {
+    $cdr->calldate($cdr->start);
+    $cdr->startdate(str2time($cdr->start));
+    $cdr->answerdate(str2time($cdr->answer));
+    $cdr->enddate(str2time($cdr->end));
+    $cdr->cdrid($cdr->id);
+  } else {
+    $cdr->startdate(str2time($cdr->calldate));
+  }
+
   $cdr->cdrbatchnum($cdrbatchnum);
+
   my $error = $cdr->insert;
-  if($error) {
-    print "failed import: $error\n";
-  }
-  else {
+  if ($error) {
+    warn "failed import: $error\n";
+  } else {
+
     $imports++;
-    if( $dbi->do("UPDATE cdr SET freesidestatus = 'done' 
-        WHERE calldate = ? AND src = ? AND dst = ?",
-                undef,
-                $row->{'calldate'},
-                $row->{'src'},
-                $row->{'dst'},
-
-      ) ) {
-        $updates++;
+
+    my $usql = "UPDATE $table SET freesidestatus = 'done' WHERE ";
+    my @args = ();
+    if ( $opt{V} >= 12 ) {
+      $usql .= ' id = '. $row->{'id'};
+      @args = ( undef );
+    } else {
+      $usql .= ' calldate = ? AND src = ? AND dst = ?';
+      @args = ( undef, $row->{'calldate'}, $row->{'src'}, $row->{'dst'}, );
     }
-    else {
-      print "failed to set status: ".$dbi->errstr."\n";
+
+    if ( $dbi->do($usql, @args) ) {
+      $updates++;
+    } else {
+      warn "failed to set status: ".$dbi->errstr."\n";
     }
+
   }
+
 }
-print "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
+
+warn "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
 $dbi->disconnect;
 
 sub usage {
-  "Usage: \n  freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
+  "Usage: \n  freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]n\t-D database\n\t[ -T table ]\n\t[ -V asterisk_version]\n\t-U user\n\t-P password\n\tfreesideuser\n";
 }
 
 =head1 NAME
@@ -96,12 +137,26 @@ freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database
 
 =head1 SYNOPSIS
 
-  freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database -U user -P password freesideuser
+  freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database [ -T table ][ -V asterisk_version ] -U user -P password freesideuser
 
 =head1 DESCRIPTION
 
 Imports CDR records from an Asterisk SQL database.
 
+-H: hostname
+
+-e: Database engine (default mysql)
+
+-D: database name
+
+-T: table name (default cdr)
+
+-U: username
+
+-P: password
+
+-V: Asterisk version.  Values of 12 or higher have a new database schema which no longer includes calldate.  Currently defaults to 11, but may default to 12+ or be required in a future, so best to always specify the version.
+
 =cut
 
 1;