IVR CDR import, RT#29055

3 files changed, 233 insertions, 2 deletions

diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm
index 3af776b..0771a7b 100644
--- a/FS/FS/cdr.pm
+++ b/FS/FS/cdr.pm
@@ -1519,8 +1519,8 @@ as keys (for use with batch_import) and "pretty" format names as values.
 
 my %cdr_info;
 foreach my $INC ( @INC ) {
-  warn "globbing $INC/FS/cdr/*.pm\n" if $DEBUG;
-  foreach my $file ( glob("$INC/FS/cdr/*.pm") ) {
+  warn "globbing $INC/FS/cdr/[a-z]*.pm\n" if $DEBUG;
+  foreach my $file ( glob("$INC/FS/cdr/[a-z]*.pm") ) {
     warn "attempting to load CDR format info from $file\n" if $DEBUG;
     $file =~ /\/(\w+)\.pm$/ or do {
       warn "unrecognized file in $INC/FS/cdr/: $file\n";
diff --git a/FS/FS/cdr/Import.pm b/FS/FS/cdr/Import.pm
new file mode 100644
index 0000000..e13c682
--- /dev/null
+++ b/FS/FS/cdr/Import.pm
@@ -0,0 +1,161 @@
+package FS::cdr::Import;
+
+use strict;
+use Date::Format 'time2str';
+use FS::UID qw(adminsuidsetup dbh);
+use FS::cdr;
+use DBI;
+use Getopt::Std;
+
+use vars qw( $DEBUG );
+$DEBUG = 0;
+
+=head1 NAME
+
+FS::cdr::Import - CDR importing
+
+=head1 SYNOPSIS
+
+  use FS::cdr::Import;
+
+  FS::cdr::Import->dbi_import(
+    'dbd'         => 'mysql', #Pg, Sybase, etc.
+    'table'       => 'TABLE_NAME',
+    'primary_key' => 'BILLING_ID',
+    'column_map'  => { #freeside => remote_db
+      'freeside_column' => 'remote_db_column',
+      'freeside_column' => sub { my $row = shift; $row->{remote_db_column}; },
+    },
+  );
+
+=head1 DESCRIPTION
+
+CDR importing
+
+=head1 CLASS METHODS
+
+=item do_cli_import
+
+=cut
+
+sub dbi_import {
+  my $class = shift;
+  my %args = @_; #args are specifed by the script using this sub
+
+  my %opt; #opt is specified for each install / run of the script
+  getopts('H:U:P:D:T:c:L:', \%opt);
+  my $user = shift(@ARGV) or die $class->cli_usage;
+
+  $opt{D} ||= $args{database};
+
+  my $dsn = 'dbi:'. $args{dbd};
+  #$dsn .= ":host=$opt{H}"; #if $opt{H};
+  $dsn .= ":server=$opt{H}"; #if $opt{H};
+  $dsn .= ";database=$opt{D}" if $opt{D};
+
+  my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) 
+    or die $DBI::errstr;
+
+  adminsuidsetup $user;
+
+  #my $fsdbh = FS::UID::dbh;
+
+  my $table = $opt{T} || $args{table};
+  my $pkey = $args{primary_key};
+
+  #just doing this manually with IVR MSSQL databases for now
+  #  # check for existence of freesidestatus
+  #  my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
+  #  if( ! @$status ) {
+  #    print "Adding freesidestatus column...\n";
+  #    $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
+  #      or die $dbi->errstr;
+  #  }
+  #  else {
+  #    print "freesidestatus column present\n";
+  #  }
+
+  #my @cols = values %{ $args{column_map} };
+  my $sql = "SELECT * FROM $table ". # join(',', @cols). " FROM $table ".
+
+            ' WHERE freesidestatus IS NULL ';
+  #$sql .= ' LIMIT '. $opt{L} if $opt{L};
+  my $sth = $dbi->prepare($sql);
+  $sth->execute or die $sth->errstr. " executing $sql";
+  #MySQL-specific print "Importing ".$sth->rows." records...\n";
+
+  my $cdr_batch = new FS::cdr_batch({ 
+      'cdrbatch' => 'IVR-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 $row;
+  while ( $row = $sth->fetchrow_hashref ) {
+
+    my %hash = ( 'cdrbatchnum' => $cdrbatchnum );
+    foreach my $field ( keys %{ $args{column_map} } ) {
+      my $col_or_coderef = $args{column_map}->{$field};
+      if ( ref($col_or_coderef) eq 'CODE' ) {
+        $hash{$field} = &{ $col_or_coderef }( $row );
+      } else {
+        $hash{$field} = $row->{ $col_or_coderef };
+      }
+      $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs
+    }
+    my $cdr = FS::cdr->new(\%hash);
+
+    $cdr->cdrtypenum($opt{c}) if $opt{c};
+
+    #print $row->{$pkey},"\n" if $opt{v};
+    my $error = $cdr->insert;
+    if ($error) {
+      #die $row->{$pkey} . ": failed import: $error\n";
+      print $row->{$pkey} . ": failed import: $error\n";
+    } else {
+      $imported++;
+
+      my $updated = $dbi->do(
+        "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?",
+        undef,
+        $row->{'$pkey'}
+      );
+      #$updates += $updated;
+      die "failed to set status: ".$dbi->errstr."\n" unless $updated;
+    }
+
+    if ( $opt{L} && $imported >= $opt{L} ) {
+      $sth->finish;
+      last;
+    }
+
+  }
+  print "Done.\n";
+  print "Imported $imported CDRs.\n" if $imported;
+
+  $dbi->disconnect;
+
+}
+
+sub cli_usage {
+  #"Usage: \n  $0\n\t[ -H hostname ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
+  #"Usage: \n  $0\n\t-H hostname\n\t-D database\n\t-U user\n\t-P password\n\t[ -c cdrtypenum ]\n\tfreesideuser\n";
+  "Usage: \n  $0\n\t-H hostname\n\t[ -D database ]\n\t-U user\n\t-P password\n\t[ -c cdrtypenum ]\n\t[ -L num_cdrs_limit ]\n\tfreesideuser\n";
+}
+
+=head1 BUGS
+
+Not everything has been refactored out of the various bin/cdr-*.import scripts,
+let alone other places.
+
+Sparse documentation.
+
+=head1 SEE ALSO
+
+L<FS::cdr>
+
+=cut
+
+1;
diff --git a/bin/cdr-ivr.import b/bin/cdr-ivr.import
new file mode 100755
index 0000000..07c180b
--- /dev/null
+++ b/bin/cdr-ivr.import
@@ -0,0 +1,70 @@
+#!/usr/bin/perl
+
+use strict;
+use Date::Parse 'str2time';
+use FS::cdr::Import;
+
+FS::cdr::Import->dbi_import(
+  'dbd'         => 'Sybase',
+  'database'    => 'TEL_DATA',
+  'table'       => 'BILLING',
+  'primary_key' => 'BILLING_ID',
+  'column_map'  => { #freeside => IVR
+    'cdrid'             => 'BILLING_ID', #Primary key
+    #'' => 'CALL_SESSION_ID', # Call Session Id (unique per call session – GUID)
+    'uniqueid'          => 'CALL_ID', #
+    #'' => 'ENTRY_TYPE', #
+    'accountcode'       => 'ACCOUNT_ID', #
+    #'' => 'ACCOUNT', #
+    #'' => 'ACCOUNT_GROUP', #
+    'startdate'         => sub { str2time(shift->{'START_DATE_TIME'}); },
+    'answerdate'        => sub { str2time(shift->{'CONNECT_DATE_TIME'}); },
+    'enddate'           => sub { str2time(shift->{'DISCONNECT_DATE_TIME'}); },
+    #'' => 'PARENT_ACCOUNT_ID', #
+    #'' => 'PARENT_ACCOUNT', #
+    #'' => 'LOGIN_NAME', #
+    #varchars not ints 'upstream_rateid' => 'RATE_SCHEDULE', #
+    #varchars not ints 'upstream_rateplanid' => 'RATE_PLAN', #
+    'channel' => 'NODE', #
+    #'' => 'NODE_TYPE', #
+    #'' => 'ORIGIN', #
+    #'dst'               => sub { $_[0]->{COUNTRY_CODE}. $_[0]->{NPA}.
+    #                             $_[0]->{NXX}. $_[0]->{LOCAL_NUMBER};
+    #                           },
+    'description'       => 'DESCRIPTION', # or upstream_dst_regionname ?
+    #'' => 'DETAIL', #
+    #'' => 'PER_CALL_CHARGE', #
+    #'' => 'PER_MINUTE_CHARGE', #
+    #'' => 'PER_CALL_SURCHARGE', #
+    #'' => 'PER_MINUTE_SURCHARGE', #
+    'duration'          => 'ACTUAL_DURATION', #
+    'billsec'           => sub { int( shift->{'QUANTITY'} * 60 + .49 ); }, #
+    'upstream_price'    => 'AMOUNT', #
+    #'' => 'PACKAGED_BALANCE_INDEX', #
+    'upstream_currency' => 'CURRENCY', #
+    #'' => 'CONVERSION_RATE', #
+    'lastapp'           => 'MODULE_NAME', #
+    'src'               => 'ANI', # 'clid' => 'ANI', #
+    'dst'               => 'DNIS',
+    #'' => 'SALES_GROUP', #
+    #'' => 'TAX_GROUP', #
+    'userfield'         => 'USER_1', #
+    #'' => 'USER_2', #
+    #'' => 'USER_3', #
+    #'' => 'USER_4', #
+    #'' => 'USER_5', #
+    #'' => 'USER_6', #
+    #'' => 'USER_7', #
+    #'' => 'USER_8', #
+    #'' => 'USER_9', #
+    #'' => 'USER_10', #
+    #'' => 'INFO_DIGITS', # VARCHAR(3) Info digits from the inbound leg, if applicable
+    #'' => 'RATE_INTERVAL', # TINYINT Rate interval used to bill the call (default: 60 seconds) ... create a new "upstream_granularity" field if we need this
+    #'' => 'DISCONNECT_CHARGE', # DECIMAL(15,4) The disconnect charge billed for the call
+    #'' => 'BILLING_DELAY', # SMALLINT Billing delay associated with the call
+    #'' => 'GRACE_PERIOD', # SMALLINT Grace period associated with the call
+    #'' => 'ACCOUNT_TYPE', # Account type from ACCOUNT_TYPES table
+  },
+);
+
+1;