RT# 76398 - Added auto status field or status table creation

Conflicts:
	FS/FS/cdr/Import.pm
	FS/bin/freeside-cdr-freeswitch

2 files changed, 175 insertions, 34 deletions

diff --git a/FS/FS/cdr/Import.pm b/FS/FS/cdr/Import.pm
index 39a7772a3..6f5062d9e 100644
--- a/FS/FS/cdr/Import.pm
+++ b/FS/FS/cdr/Import.pm
@@ -19,7 +19,7 @@ FS::cdr::Import - CDR importing
   use FS::cdr::Import;
 
   FS::cdr::Import->dbi_import(
-    'dbd'         => 'mysql', #Pg, Sybase, etc.
+    'dbd'         => 'Pg', #mysql, Sybase, etc.
     'table'       => 'TABLE_NAME',
     'primary_key' => 'BILLING_ID',
     'column_map'  => { #freeside => remote_db
@@ -43,42 +43,61 @@ sub dbi_import {
   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};
+  getopts('H:U:P:D:T:c:L:S:', \%opt);
 
-  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 $user = shift(@ARGV) or die $class->cli_usage;
+  my $database = $opt{D} || $args{database};
+  my $table = $opt{T} || $args{table};
+  my $pkey = $args{primary_key};
+  my $pkey_info = $args{primary_key_info} ? $args{primary_key_info} : 'BIGINT';
+  my $status_table = $opt{S} || $args{status_table};
+  my $dbd_type = $args{'dbd'} ? $args{'dbd'} : 'Pg';
+  my $status_column = $args{status_column} ? $args{status_column} : 'freesidestatus';
+  my $status_column_info = $args{status_column_info} ? $args{status_column} : 'VARCHAR(32)';
+
+  my $queries = get_queries({
+    'dbd'                 => $dbd_type,
+    'table'               => $table,
+    'status_column'       => $status_column,
+    'status_column_info'  => $status_column_info,
+    'status_table'        => $status_table,
+    'primary_key'         => $pkey,
+    'primary_key_info'    => $pkey_info,
+  });
+
+  my $dsn = 'dbi:'. $dbd_type;
+  $dsn .= $queries->{connect_type} . "=$opt{H}";
+  $dsn .= ";database=$database" if $database;
 
   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};
+  ## check for status table if using. if not there create it.
+  if ($status_table) {
+    my $status = $dbi->selectall_arrayref( $queries->{check_statustable} );
+    if( ! @$status ) {
+      print "Adding status table $status_table ...\n";
+      $dbi->do( $queries->{create_statustable} )
+        or die $dbi->errstr;
+    }
+  }
+  ## check for column freeside status if not using status table and create it if not there.
+  else {
+    my $status = $dbi->selectall_arrayref( $queries->{check_statuscolumn} );
+    if( ! @$status ) {
+      print "Adding $status_column column...\n";
+      $dbi->do( $queries->{create_statuscolumn} )
+        or die $dbi->errstr;
+    }
+  }
 
-  #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";
-  #  }
-  # or if using a status_table:
-  #      CREATE TABLE FREESIDE_BILLING (
-  #        BILLING_ID BIGINT,
-  #        FREESIDESTATUS VARCHAR(32)
-  #      )
+  #my @cols = values %{ $args{column_map} };
+  my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
+  $sql .=  "LEFT JOIN $status_table ON ( $table.$pkey = $status_table.$pkey ) "
+    if $status_table;
+  $sql .= "WHERE  $status_column IS NULL ";
 
   #my @cols = values %{ $args{column_map} };
   my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
@@ -90,7 +109,6 @@ sub dbi_import {
   #$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),
@@ -113,6 +131,7 @@ sub dbi_import {
       }
       $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs
     }
+
     my $cdr = FS::cdr->new(\%hash);
 
     $cdr->cdrtypenum($opt{c}) if $opt{c};
@@ -135,12 +154,12 @@ sub dbi_import {
       if ( $args{status_table} ) {
 
         $st_sql = 
-          'INSERT INTO '. $args{status_table}. " ( $pkey, freesidestatus ) ".
+          'INSERT INTO '. $status_table. " ( $pkey, $status_column ) ".
             " VALUES ( ?, 'done' )";
 
       } else {
 
-        $st_sql = "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?";
+        $st_sql = "UPDATE $table SET $status_column = 'done' WHERE $pkey = ?";
 
       }
 
@@ -169,10 +188,58 @@ sub cli_usage {
   "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";
 }
 
+sub get_queries {
+  #my ($dbd, $table, $column, $column_create_info, $status_table, $primary_key, $primary_key_info) = @_;
+  my $info = shift;
+
+  #do we want to add more types? or add as we go?
+  my %dbi_connect_types = (
+    'Sybase'  => ':server',
+    'Pg'      => ':host',
+  );
+
+  #Check for freeside status table Sybase has not been tested
+  my %dbi_check_statustable = (
+    'Sybase'  => "SELECT systables.name FROM sysobjects
+                  JOIN systables ON sysobjects.id = systables.id
+                  WHERE sysobjects.name LIKE '$info->{table}' AND systables.name = $info->{status_table}",
+    'Pg'      => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{status_table}' AND column_name = '$info->{status_column}'",
+  );
+
+  #Check for freeside status table Sybase has not been tested
+  my %dbi_create_statustable = (
+    'Sybase'  => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )",
+    'Pg'      => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )",
+  );
+
+  #Check for freeside status column Sybase has not been tested
+  my %dbi_check_statuscolumn = (
+    'Sybase'  => "SELECT syscolumns.name FROM sysobjects
+                  JOIN syscolumns ON sysobjects.id = syscolumns.id
+                  WHERE sysobjects.name LIKE '$info->{table}' AND syscolumns.name = $info->{status_column}",
+    'Pg'      => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{table}' AND column_name = '$info->{status_column}' ",
+  );
+
+    #Check for freeside status column Sybase has not been tested
+  my %dbi_create_statuscolumn = (
+    'Sybase'  => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}",
+    'Pg'      => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}",
+  );
+
+  my $queries = {
+    'connect_type'         =>  $dbi_connect_types{$info->{dbd}},
+    'check_statustable'    =>  $dbi_check_statustable{$info->{dbd}},
+    'create_statustable'   =>  $dbi_create_statustable{$info->{dbd}},
+    'check_statuscolumn'   =>  $dbi_check_statuscolumn{$info->{dbd}},
+    'create_statuscolumn'  =>  $dbi_create_statuscolumn{$info->{dbd}},
+  };
+
+  return $queries;
+}
+
 =head1 BUGS
 
-Not everything has been refactored out of the various bin/cdr-*.import scripts,
-let alone other places.
+This has only been test with Pg -> postgresql databases
 
 Sparse documentation.
 
diff --git a/FS/bin/freeside-cdr-freeswitch b/FS/bin/freeside-cdr-freeswitch
new file mode 100644
index 000000000..51f360ca7
--- /dev/null
+++ b/FS/bin/freeside-cdr-freeswitch
@@ -0,0 +1,74 @@
+#!/usr/bin/perl
+
+use strict;
+use Date::Parse 'str2time';
+use FS::cdr::Import;
+
+FS::cdr::Import->dbi_import(
+  'dbd'                 => 'Pg',
+  'database'            => 'fusionpbx',
+  'table'               => 'v_xml_cdr',
+  'status_column'       => 'freesidestatus',
+  'primary_key'         => 'uuid',:q
+  'column_map'          => { #freeside => fusionpbx
+    #'cdrid'             => 'uuid', #Primary key
+    #'' => 'CALL_SESSION_ID', # Call Session Id (unique per call session – GUID)
+    'uniqueid'          => 'uuid', #
+    #'' => 'ENTRY_TYPE', #
+    'accountcode'       => 'accountcode', #
+    #'' => 'ACCOUNT', #
+    #'' => 'ACCOUNT_GROUP', #
+    'startdate'         => sub { str2time(shift->{'start_stamp'}); },
+    'answerdate'        => sub { str2time(shift->{'answer_stamp'}); },
+    'enddate'           => sub { str2time(shift->{'end_stamp'}); },
+    #'' => '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 ?
+    #'' => 'PER_CALL_CHARGE', #
+    #'' => 'PER_MINUTE_CHARGE', #
+    #'' => 'PER_CALL_SURCHARGE', #
+    #'' => 'PER_MINUTE_SURCHARGE', #
+    'duration'          => 'duration', #
+    'billsec'           => sub { int( shift->{'billsec'} * 60 + .49 ); }, #
+    #'upstream_price'    => 'AMOUNT', #
+    #'' => 'PACKAGED_BALANCE_INDEX', #
+    #'upstream_currency' => 'CURRENCY', #
+    #'' => 'CONVERSION_RATE', #
+    'lastapp'           => 'last_app', #
+    #'src'               => 'ANI', # 'clid' => 'ANI', #
+    'dst'               => sub { $_[0]->{'COUNTRY_CODE'} !~ /^1/
+                                   ? "011". $_[0]->{'DETAIL'}
+                                   : $_[0]->{'DETAIL'};
+                               },
+    #'' => '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
+  },
+  'batch_name' => 'fusionpbx',
+);
+
+1;
\ No newline at end of file