planet telecom rate import, RT#83146

[freeside.git] / bin / aradial-sftp_and_import

diff --git a/bin/aradial-sftp_and_import b/bin/aradial-sftp_and_import
index d48e218..e763963 100755 (executable)
--- a/bin/aradial-sftp_and_import
+++ b/bin/aradial-sftp_and_import
@@ -4,9 +4,11 @@
 
 use strict;
 use Getopt::Std;
+use Date::Parse;
 use Date::Format;
 use Text::CSV_XS;
-use DBI;
+use DBI qw( :sql_types );
+use FS::DBI;
 use Net::SFTP::Foreign;
 #use FS::UID qw( adminsuidsetup datasrc );
 
@@ -35,6 +37,14 @@ our %aradial2db = (
   'Acct-Terminate-Cause' => 'AcctTerminateCause',
 );
 
+our %bind_type = (
+  'AcctInputOctets'  => SQL_INTEGER,
+  'AcctOutputOctets' => SQL_INTEGER,
+  'AcctSessionTime'  => SQL_INTEGER,
+  'AcctStartDelay'   => SQL_INTEGER,
+  'AcctStopDelay'    => SQL_INTEGER,
+);
+
 #http://www.iana.org/assignments/radius-types/radius-types.xhtml#radius-types-10
 our %status_type = (
    1 => 'Start',
@@ -57,7 +67,7 @@ our %status_type = (
 ###
 
 use vars qw( $opt_m $opt_a $opt_b $opt_r $opt_d $opt_v $opt_P );
-getopts('m:abr:dP:v:');
+getopts('m:abr:d:P:v:');
 
 my %options = ();
 
@@ -72,8 +82,8 @@ mkdir $cachedir unless -d $cachedir;
 my $servername = shift or die &usage;
 
 my( $datasrc, $db_user, $db_pass ) = ( shift, shift, shift );
-my $dbh = DBI->connect( $datasrc, $db_user, $db_pass)
-  or die "can't connect: $DBI::errstr\n";
+my $dbh = FS::DBI->connect( $datasrc, $db_user, $db_pass)
+  or die "can't connect: $FS::DBI::errstr\n";
 
 my $csv = Text::CSV_XS->new;
 
@@ -119,6 +129,8 @@ else {
 
 foreach my $filename ( @$ls ) {
 
+  next if $opt_d && $filename eq $opt_d;
+
   warn "Downloading $filename\n" if $opt_v;
 
   #get the file
@@ -148,29 +160,36 @@ foreach my $filename ( @$ls ) {
                      keys %hash;
 
     my @keys = keys %dbhash;
-    my @values = map $dbhash{$_}, @keys;
+
+    #skip blank records
+    next unless grep defined($_), values %dbhash;
+
+    my $date = time2str( '%Y-%m-%d %X', str2time( $hash{'Date'} ) );
 
     $hash{'Status-Type'} = $status_type{ $hash{'Status-Type'} }
       if exists $status_type{ $hash{'Status-Type'} };
 
+    my $sql;
+    my @extra_values = ();
     if ( $hash{'Status-Type'} eq 'Start' ) {
 
-      $dbhash{'AcctStartTime'} = $hash{'Date'};
+      push @keys, 'AcctStartTime';
+      $dbhash{'AcctStartTime'} = $date;
 
-      my $sql = 'INSERT INTO radacct ( ', join(',', @keys).
-                ' ) VALUES ( '. map( ' ? ', @values ). ' )';
-      my $sth = $dbh->prepare($sql) or die $dbh->errstr;
-      $sth->execute(@values) or die $sth->errstr;
+      $sql = 'INSERT INTO radacct ( '. join(',', @keys).
+             ' ) VALUES ( '. join(',', map ' ? ', @keys ). ' )';
 
     } elsif ( $hash{'Status-Type'} eq 'Stop' ) {
 
       my $AcctSessionId = delete($dbhash{AcctSessionId});
-      $dbhash{'AcctStopTime'} = $hash{'Date'};
 
-      my $sql = 'UPDATE radacct '. join(' , ', map "SET $_ = ?", @keys ).
-                ' WHERE AcctSessionId = ? ';
-      my $sth = $dbh->prepare($sql) or die $dbh->errstr;
-      $sth->execute(@values, $AcctSessionId) or die $sth->errstr;
+      push @keys, 'AcctStopTime';
+      $dbhash{'AcctStopTime'} = $date;
+
+      push @extra_values, $AcctSessionId;
+
+      $sql = 'UPDATE radacct SET '. join(',', map "$_ = ?", @keys ).
+             ' WHERE AcctSessionId = ? ';
 
     } elsif ( $hash{'Status-Type'} eq 'Interim' ) {
       #not handled, but stop should capture the usage.  unless session are
@@ -178,8 +197,24 @@ foreach my $filename ( @$ls ) {
       # real-time-ish data usage detail, it isn't a big deal
     } else {
       warn 'Unknown Status-Type '. $hash{'Status-Type'}. "; skipping\n";
+      next;
+    }
+
+    my $sth = $dbh->prepare($sql) or die $dbh->errstr;
+
+    my $p_num = 1;
+    foreach my $value ( map $dbhash{$_}, @keys ) {
+      my $key = shift @keys;
+      my $type = exists($bind_type{$key}) ? $bind_type{$key} : SQL_VARCHAR;
+      $value ||= 0 if $type == SQL_INTEGER;
+      $sth->bind_param($p_num++, $value, $type);
+    }
+    foreach my $value ( @extra_values ) {
+      $sth->bind_param($p_num++, $value);
     }
 
+    $sth->execute or die $sth->errstr;
+
   }
   
   if ( $opt_d ) {