1 files changed, 153 insertions, 0 deletions
diff --git a/bin/cdr-voipnow.import b/bin/cdr-voipnow.import
new file mode 100755
index 000000000..c16b00d7c
--- /dev/null
+++ b/bin/cdr-voipnow.import
@@ -0,0 +1,153 @@
+#!/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::Record qw(qsearchs);
+use FS::cdr;
+use DBI;
+use Getopt::Std;
+
+my %opt;
+getopts('H:U:P:D:T:vs: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;
+
+adminsuidsetup $user;
+$FS::UID::AutoCommit = 0;
+my $fsdbh = FS::UID::dbh;
+
+# don't use freesidestatus
+
+my $start_id;
+if ( $opt{s} ) {
+  $start_id = $opt{s};
+}
+else {
+  my $last_cdr = qsearchs({
+      'table'   => 'cdr',
+      'hashref' => {},
+      'extra_sql' => 'ORDER BY cdrid DESC LIMIT 1',
+    });
+  $start_id = $last_cdr ? $last_cdr->cdrid + 1: 1;
+}
+my $end_id = $opt{e};
+print "Selecting CDRs from $start_id to ".($end_id || 'end')."...\n";
+
+my $table = $opt{T} || 'call_history';
+# spelled "disposion" in the table
+my @cols = ( qw( 
+  id extension_number flow channel partyid start answer duration disposion did
+  client_client_id ) );
+my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE id >= $start_id";
+$sql .= " AND id <= $end_id" if $end_id;
+$sql .= " ORDER BY id";
+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 $imported = 0;
+my $skipped = 0;
+
+my $row;
+my ($first, $last);
+while ( $row = $sth->fetchrow_hashref ) {
+  if ( $opt{s} # skip this check if the range doesn't overlap
+      and qsearchs('cdr', { cdrid => $row->{id} } ) ) {
+    $skipped++;
+    print $row->{id} ." (skipped)\n" if $opt{v};
+    next;
+  }
+  my $cdr = FS::cdr->new({
+      cdrid         => $row->{id},
+      channel       => $row->{channel},
+      duration      => $row->{duration},
+      billsec       => $row->{duration},
+      disposition   => $row->{disposion},
+      startdate     => str2time($row->{start}),
+      answerdate    => str2time($row->{answer}),
+      cdrbatchnum   => $cdrbatchnum,
+      accountcode   => $row->{client_client_id},
+    }
+  );
+  print $row->{id},"\n" if $opt{v};
+  if ( $row->{flow} eq 'out' ) {
+    $cdr->src($row->{'extension_number'});
+    $cdr->dst($row->{'partyid'});
+  }
+  elsif ( $row->{flow} eq 'in' ) {
+    $cdr->dst($row->{'did'});
+    $cdr->src($row->{'partyid'});
+  }
+  else {
+    $fsdbh->rollback;
+    die $row->{id} .": invalid flow value: '".$row->{flow}."'\n";
+  }
+  my $error = $cdr->insert;
+  if($error) {
+    $fsdbh->rollback;
+    die $row->{id} . ": failed import: $error\n";
+  }
+  $first ||= $row->{id};
+  $last = $row->{id};
+  $imported++;
+}
+$fsdbh->commit or die $fsdbh->errstr;
+print "Done.\n";
+print "Imported $imported CDRs ($first - $last).\n" if $imported;
+print "Skipped $skipped duplicates.\n" if $skipped;
+$mysql->disconnect;
+
+sub usage {
+  "Usage: \n  cdr-voipnow.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\t[ -v ] [ -s start ] [ -e end ]\n\tfreesideuser\n";
+}
+
+=head1 NAME
+
+cdr-voipnow.import - Import call data records (CDRs) from a 4psa VoipNow system
+
+=head1 SYNOPSIS
+
+  cdr-voipnow.import [ -H host ] -D database -U user -P password 
+    [ -v ] [ -s start ] [ -e end ] freesideuser
+
+=head1 DESCRIPTION
+
+Connects to a MySQL database and downloads CDRs from the "call_history" table.
+The "id" field maps to "cdrid" in Freeside.  Other than that, the following
+fields are imported: channel, duration, billsec, startdate, answerdate,
+disposition, src, dst.  src and dst are inferred from the "extension_number"
+and "partyid" fields, with the value of the "flow" field (in or out) deciding
+which is the source number and which is the destination.
+
+Any import errors (except duplicates) will abort and roll back the 
+transaction.
+
+=head1 OPTIONS
+
+-H, -D, -U, -P: parameters to connect to the database: host, database name
+user, password.  Required, except -H, which defaults to localhost.
+
+-s: set the lowest CDR id to import.  By default, the script will find 
+the highest existing cdrid and import all CDRs with ids greater than that.
+-s overrides this and turns on duplicate checking.
+
+-e: set the highest CDR id to import.  By default, this is unlimited.
+
+-v: report all CDR ids as they are imported.
+
+=cut