X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=bin%2Faradial-sftp_and_import;h=e7639633a0f83381a0d3771024956bb92a8279a3;hp=923c674bacb6d61388942187e4e9411fa8f6fc0c;hb=5f0388ca15d79d964b1c6197d0841d8f5c708d15;hpb=bc6393f0c5d8d3c797a22ed0280f18fe3ff32210

diff --git a/bin/aradial-sftp_and_import b/bin/aradial-sftp_and_import
index 923c674ba..e7639633a 100755
--- a/bin/aradial-sftp_and_import
+++ b/bin/aradial-sftp_and_import
@@ -1,42 +1,73 @@
-#!/usr/bin/perl
+#!/usr/bin/perl -w
 
 #i'm kinda like freeside-cdr-sftp_and_import... some parts should be libraried
 
 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 );
 
+#adjusted these for what we're actually seeing in the real log files
 our %aradial2db = (
   #'Date' => '',
-  'NASIP' => 'NASIPAddress',
+  #'NASIP' => 'NASIPAddress',
+  'NASID' => 'NASIPAddress',
   'AcctSessionId' => 'AcctSessionId',
   'Port' => 'NasPortId',
   #'Status-Type' => 'Acct-Status-Type',
-  'UserID' => 'UserName',
-  'Authentic' => 'AccdtAuthentic',
+  #'UserID' => 'UserName',
+  'User ID' => 'UserName',
+  'Authentic' => 'AcctAuthentic',
   'Service-Type' => 'ServiceType',
   'FramedProtocol' => 'FramedProtocol',
   #'FramedCompression' => '', #not handled, needed?  unlikely
   'FramedAddress' => 'FramedIPAddress',
   'Acct-Delay-Time' => 'AcctStartDelay', #?
   'Session-Time' => 'AcctSessionTime',
-  #'Input-Gigawords' => '',
+  #'Input-Gigawords' => '', #XXX handle lots of data
   'Input-Octets' => 'AcctInputOctets',
-  #'Output-Gigawords' => '',
+  #'Output-Gigawords' => '', #XXX handle lots of data
   'Output-Octets' => 'AcctOutputOctets',
   'NAS-Port-Type' => 'NASPortType',
   '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',
+   2 => 'Stop',
+   3 => 'Interim-Update',
+  #4-6,'Unassigned',
+   7 => 'Accounting-On',
+   8 => 'Accounting-Off',
+   9 => 'Tunnel-Start',
+  10 => 'Tunnel-Stop',
+  11 => 'Tunnel-Reject',
+  12 => 'Tunnel-Link-Start',
+  13 => 'Tunnel-Link-Stop',
+  14 => 'Tunnel-Link-Reject',
+  15 => 'Failed',
+);
+
 ###
 # parse command line
 ###
 
 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 = ();
 
@@ -51,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;
 
@@ -98,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
@@ -114,8 +147,6 @@ foreach my $filename ( @$ls ) {
 
   warn "Processing $filename\n" if $opt_v;
  
-  my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time);
-
   open my $fh, "$cachedir/$filename" or die "$cachedir/$filename: $!";
   my $header = $csv->getline($fh);
 
@@ -129,38 +160,65 @@ foreach my $filename ( @$ls ) {
                      keys %hash;
 
     my @keys = keys %dbhash;
-    my @values = map $dbhash{$_}, @keys;
 
-    if ( $hash{'Acct-Status-Type'} eq 'Start' ) {
+    #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'} };
 
-      $dbhash{'AcctStartTime'} = $hash{'Date'};
+    my $sql;
+    my @extra_values = ();
+    if ( $hash{'Status-Type'} eq 'Start' ) {
 
-      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;
+      push @keys, 'AcctStartTime';
+      $dbhash{'AcctStartTime'} = $date;
 
-    } elsif ( $hash{'Acct-Status-Type'} eq 'Stop' ) {
+      $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;
 
-    } elsif ( $hash{'Acct-Status-Type'} eq 'Interim' ) {
+      $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
       # normally super-long, extending across month boundaries, or we need
       # real-time-ish data usage detail, it isn't a big deal
     } else {
-      die 'Unknown Acct-Status-Type '. $hash{'Acct-Status-Type'}. "\n";
+      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 ) {
+    my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time);
     if ( $opt_m eq 'ftp') {
       my $ftp = ftp();
       $ftp->rename($filename, "$opt_d/$file_timestamp")