X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcdr%2FImport.pm;h=dd9fc26d6dc4bf59fb2e6e24c2488dee189cca00;hb=fcce61390d750f98bd82e84d4664ae9c03202be2;hp=6f5062d9e689dcf95cd3d8056fc4a1c37aeca7f0;hpb=6815fb35400b1f36abbbb7f392f8be348fef14b9;p=freeside.git

diff --git a/FS/FS/cdr/Import.pm b/FS/FS/cdr/Import.pm
index 6f5062d9e..dd9fc26d6 100644
--- a/FS/FS/cdr/Import.pm
+++ b/FS/FS/cdr/Import.pm
@@ -4,7 +4,7 @@ use strict;
 use Date::Format 'time2str';
 use FS::UID qw(adminsuidsetup dbh);
 use FS::cdr;
-use DBI;
+use FS::DBI;
 use Getopt::Std;
 
 use vars qw( $DEBUG );
@@ -19,13 +19,19 @@ FS::cdr::Import - CDR importing
   use FS::cdr::Import;
 
   FS::cdr::Import->dbi_import(
-    'dbd'         => 'Pg', #mysql, 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}; },
+    'dbd'                => 'Pg', #mysql, Sybase, etc.
+    'database'           => 'DATABASE_NAME',
+    'table'              => 'TABLE_NAME',,
+    'status_table'       => 'STATUS_TABLE_NAME', # if using a table rather than field in main table
+    'primary_key'        => 'BILLING_ID',
+    'primary_key_info'   => 'BIGINT', # defaults to bigint
+    'status_column'      => 'STATUS_COLUMN_NAME', # defaults to freesidestatus
+    'status_column_info' => 'varchar(32)', # defaults to varchar(32)
+    'column_map'         => { #freeside => remote_db
+      'freeside_column'    => 'remote_db_column',
+      'freeside_column'    => sub { my $row = shift; $row->{remote_db_column}; },
     },
+    'batch_name'         => 'batch_name', # cdr_batch name -import-date gets appended.
   );
 
 =head1 DESCRIPTION
@@ -54,9 +60,12 @@ sub dbi_import {
   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 $st_sql;
+  my $batch_name = $args{batch_name} ? $args{batch_name} : 'CDR_DB';
 
   my $queries = get_queries({
     'dbd'                 => $dbd_type,
+    'host'                => $opt{H},
     'table'               => $table,
     'status_column'       => $status_column,
     'status_column_info'  => $status_column_info,
@@ -65,12 +74,11 @@ sub dbi_import {
     'primary_key_info'    => $pkey_info,
   });
 
-  my $dsn = 'dbi:'. $dbd_type;
-  $dsn .= $queries->{connect_type} . "=$opt{H}";
+  my $dsn = 'dbi:'. $dbd_type . $queries->{connect_type};
   $dsn .= ";database=$database" if $database;
 
-  my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) 
-    or die $DBI::errstr;
+  my $dbi = FS::DBI->connect($dsn, $opt{U}, $opt{P})
+    or die $FS::DBI::errstr;
 
   adminsuidsetup $user;
 
@@ -82,6 +90,7 @@ sub dbi_import {
       $dbi->do( $queries->{create_statustable} )
         or die $dbi->errstr;
     }
+    $st_sql = "INSERT INTO $status_table ( $pkey, $status_column ) VALUES ( ?, 'done' )";
   }
   ## check for column freeside status if not using status table and create it if not there.
   else {
@@ -91,6 +100,7 @@ sub dbi_import {
       $dbi->do( $queries->{create_statuscolumn} )
         or die $dbi->errstr;
     }
+    $st_sql = "UPDATE $table SET $status_column = 'done' WHERE $pkey = ?";
   }
 
   #my @cols = values %{ $args{column_map} };
@@ -99,19 +109,12 @@ sub dbi_import {
     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 ".
-  $sql .=  'LEFT JOIN '. $args{status_table}.
-           " ON ( $table.$pkey = ". $args{status_table}. ".$pkey )"
-    if $args{status_table};
-  $sql .= ' WHERE freesidestatus IS NULL ';
-
   #$sql .= ' LIMIT '. $opt{L} if $opt{L};
   my $sth = $dbi->prepare($sql);
   $sth->execute or die $sth->errstr. " executing $sql";
 
   my $cdr_batch = new FS::cdr_batch({ 
-      'cdrbatch' => 'IVR-import-'. time2str('%Y/%m/%d-%T',time),
+      'cdrbatch' => $batch_name . '-import-'. time2str('%Y/%m/%d-%T',time),
     });
   my $error = $cdr_batch->insert;
   die $error if $error;
@@ -150,19 +153,6 @@ sub dbi_import {
 
       $imported++;
 
-      my $st_sql;
-      if ( $args{status_table} ) {
-
-        $st_sql = 
-          'INSERT INTO '. $status_table. " ( $pkey, $status_column ) ".
-            " VALUES ( ?, 'done' )";
-
-      } else {
-
-        $st_sql = "UPDATE $table SET $status_column = 'done' WHERE $pkey = ?";
-
-      }
-
       my $updated = $dbi->do($st_sql, undef, $pkey_value );
       #$updates += $updated;
       die "failed to set status: ".$dbi->errstr."\n" unless $updated;
@@ -183,46 +173,46 @@ sub dbi_import {
 }
 
 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";
+  "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\t[ -T table ]\n\t[ -S status table ]\n\tfreesideuser\n";
 }
 
 sub get_queries {
-  #my ($dbd, $table, $column, $column_create_info, $status_table, $primary_key, $primary_key_info) = @_;
+  #my ($dbd, $host, $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?
+  #get host and port information.
+  my ($host, $port) = split /:/, $info->{host};
+  $host ||= 'localhost';
+  $port ||= '5000'; # check for pg default 5000 is sybase.
+
   my %dbi_connect_types = (
-    'Sybase'  => ':server',
-    'Pg'      => ':host',
+    'Sybase'  => ':server='.$host.';port='.$port,
+    'Pg'      => ':host='.$info->{host},
   );
 
-  #Check for freeside status table Sybase has not been tested
+  #Check for freeside status table
   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}",
+    'Sybase'  => "SELECT * FROM sysobjects WHERE 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
+  #Create freeside status table
   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
+  #Check for freeside status column
   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}",
+                  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
+    #Create freeside status column
   my %dbi_create_statuscolumn = (
-    'Sybase'  => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}",
+    'Sybase'  => "ALTER TABLE $info->{table} ADD $info->{status_column} $info->{status_column_info} NULL",
     'Pg'      => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}",
   );
 
@@ -239,7 +229,7 @@ sub get_queries {
 
 =head1 BUGS
 
-This has only been test with Pg -> postgresql databases
+currently works with Pg(Postgresql) and Sybase(Sybase AES)
 
 Sparse documentation.