Merge branch 'FREESIDE_3_BRANCH' of git.freeside.biz:/home/git/freeside into FREESIDE_3_BRANCH

6 files changed, 387 insertions, 26 deletions

diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index 4cf079837..d0a530e9a 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -131,7 +131,7 @@ if ( -e $addl_handler_use_file ) {
   use FS::UID qw( getotaker dbh datasrc driver_name );
   use FS::Record qw( qsearch qsearchs fields dbdef
                     str2time_sql str2time_sql_closing
-                    midnight_sql
+                    midnight_sql regexp_sql
                    );
   use FS::Conf;
   use FS::CGI qw(header menubar table itable ntable idiot
diff --git a/FS/FS/Misc/Geo.pm b/FS/FS/Misc/Geo.pm
index 7ab6d280d..168e4e959 100644
--- a/FS/FS/Misc/Geo.pm
+++ b/FS/FS/Misc/Geo.pm
@@ -334,8 +334,12 @@ sub standardize_uscensus {
         censustract => $result->censustract,
       };
     } else {
-      die "can't parse address '".$result->address."'";
+      die "Geocoding returned '".$result->address."', which does not seem to be a valid address.\n";
     }
+  } elsif ( $result->match_level eq 'Tie' ) {
+    die "Geocoding was not able to identify a unique matching address.\n";
+  } elsif ( $result->match_level ) {
+    die "Geocoding did not find a matching address.\n";
   } else {
     warn Dumper($result) if $DEBUG;
     die $result->error_message;
diff --git a/FS/FS/cdr/enswitch.pm b/FS/FS/cdr/enswitch.pm
index c1966b404..26d27821e 100644
--- a/FS/FS/cdr/enswitch.pm
+++ b/FS/FS/cdr/enswitch.pm
@@ -16,10 +16,10 @@ use FS::cdr_type;
     'startdate',    #Start, already a unix timestamp
     skip(2),        #Start date, Start time
     'enddate',      #End
-    skip(6),        #End date, End time
+    skip(4),        #End date, End time
                     #Calling customer, Calling type
     'src',          #Calling number     
-    skip(1),        #Called type
+    'dcontext',     #Called type
     'dst',          #Called number
     skip(26),       #Destination customer, Destination type
                     #Destination number
@@ -38,8 +38,8 @@ use FS::cdr_type;
     'duration',     #Total seconds
     skip(1),        #Ring seconds
     'billsec',      #Billable seconds
-    'upstream_price', #Cost
-    skip(1),        #Cost including taxes
+    skip(2),        #Cost
+    	            #Cost including taxes
     'accountcode',  #Billing customer
     skip(3),        #Billing customer name, Billing type, Billing reference
   ],
diff --git a/FS/FS/cdr/ispphone.pm b/FS/FS/cdr/ispphone.pm
index 49d1b07f4..2817d53c1 100644
--- a/FS/FS/cdr/ispphone.pm
+++ b/FS/FS/cdr/ispphone.pm
@@ -3,7 +3,7 @@ package FS::cdr::ispphone;
 use strict;
 use vars qw( @ISA %info $tmp_mon $tmp_mday $tmp_year );
 use Time::Local;
-use FS::cdr;
+use FS::cdr qw ( _cdr_date_parser_maker );
 use Date::Parse;
 
 @ISA = qw(FS::cdr);
@@ -11,29 +11,15 @@ use Date::Parse;
 %info = (
   'name'          => 'ISPPhone',
   'weight'        => 123,
-  'header'        => 2,
+  'header'        => 1,
   'import_fields' => [
 
+                 'accountcode',  # Accountcode
 	                 'src',	 # Form
 		         'dst',  # To
-     'upstream_dst_regionname',  # Country
-                    'dcontext',  # Description
-              	 
-			sub { my ($cdr, $calldate) = @_;
-                        	$cdr->set('calldate', $calldate);
-
-			my $tmp_date;
-
- 	                      if ($calldate =~ /^(\d{2})\/(\d{2})\/(\d{2})\s*(\d{1,2}):(\d{2})$/){
-
-                	        $tmp_date = "$2/$1/$3 $4:$5:$6";
-        	                        
-			      } else { $tmp_date = $calldate; }
-	
-				$tmp_date = str2time($tmp_date);
-                        	$cdr->set('startdate', $tmp_date);
-
-                 	},       #DateTime
+		       skip(1),  # Country
+     'upstream_dst_regionname',  # Description
+_cdr_date_parser_maker('startdate'),  #DateTime
 
 	                sub { my ($cdr, $duration) = @_;
 				my ($min,$sec) = split(/:/, $duration);
@@ -47,5 +33,7 @@ use Date::Parse;
 
 );
 
+sub skip { map {''} (1..$_[0]) }
+
 1;
 
diff --git a/FS/bin/freeside-aradial-sftp_and_import b/FS/bin/freeside-aradial-sftp_and_import
new file mode 100644
index 000000000..668ec49ba
--- /dev/null
+++ b/FS/bin/freeside-aradial-sftp_and_import
@@ -0,0 +1,327 @@
+#!/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 qw( :sql_types );
+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',
+  'NASID' => 'NASIPAddress',
+  'AcctSessionId' => 'AcctSessionId',
+  'Port' => 'NasPortId',
+  #'Status-Type' => 'Acct-Status-Type',
+  #'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' => '', #XXX handle lots of data
+  'Input-Octets' => 'AcctInputOctets',
+  #'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:d:P:v:');
+
+my %options = ();
+
+my $user = shift or die &usage;
+#adminsuidsetup $user;
+
+# %%%FREESIDE_CACHE%%% & hardcoded datasrc
+#my $cachedir = '%%%FREESIDE_CACHE%%%/cache.'. datasrc. '/cdrs';
+my $cachedir = '/usr/local/etc/freeside/cache.DBI:Pg:dbname=freeside/cdrs';
+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 $csv = Text::CSV_XS->new;
+
+###
+# get the file list
+###
+
+warn "Retrieving directory listing\n" if $opt_v;
+
+$opt_m = 'sftp' if !defined($opt_m);
+$opt_m = lc($opt_m);
+
+my $ls;
+
+if($opt_m eq 'ftp') {
+  $options{'Port'}    = $opt_P if $opt_P;
+  $options{'Debug'}   = $opt_v if $opt_v;
+  $options{'Passive'} = $opt_a if $opt_a;
+
+  my $ls_ftp = ftp();
+
+  $ls = [ grep { /^.*$/i } $ls_ftp->ls ];
+}
+elsif($opt_m eq 'sftp') {
+  $options{'port'}    = $opt_P if $opt_P;
+  $options{'debug'}   = $opt_v if $opt_v;
+
+  my $ls_sftp = sftp();
+
+  $ls_sftp->setcwd($opt_r) or die "can't chdir to $opt_r\n"
+    if $opt_r;
+
+  $ls = $ls_sftp->ls('.', no_wanted  => qr/^\.+$/,
+                          names_only => 1 );
+}
+else {
+  die "Method '$opt_m' not supported; must be ftp or sftp\n";
+}
+
+###
+# import each file
+###
+
+foreach my $filename ( @$ls ) {
+
+  next if $opt_d && $filename eq $opt_d;
+
+  warn "Downloading $filename\n" if $opt_v;
+
+  #get the file
+  if($opt_m eq 'ftp') {
+    my $ftp = ftp();
+    $ftp->get($filename, "$cachedir/$filename")
+      or die "Can't get $filename: ". $ftp->message . "\n";
+  }
+  else {
+    my $sftp = sftp();
+    $sftp->get($filename, "$cachedir/$filename")
+      or die "Can't get $filename: ". $sftp->error . "\n";
+  }
+
+  warn "Processing $filename\n" if $opt_v;
+ 
+  open my $fh, "$cachedir/$filename" or die "$cachedir/$filename: $!";
+  my $header = $csv->getline($fh);
+
+  while ( my $row = $csv->getline($fh) ) {
+
+    my $i = 0;
+    my %hash = map { $_ => $row->[$i++] } @$header;
+
+    my %dbhash = map { $aradial2db{$_} => $hash{$_} }
+                   grep $aradial2db{$_},
+                     keys %hash;
+
+    my @keys = keys %dbhash;
+
+    #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' ) {
+
+      push @keys, 'AcctStartTime';
+      $dbhash{'AcctStartTime'} = $date;
+
+      $sql = 'INSERT INTO radacct ( '. join(',', @keys).
+             ' ) VALUES ( '. join(',', map ' ? ', @keys ). ' )';
+
+    } elsif ( $hash{'Status-Type'} eq 'Stop' ) {
+
+      my $AcctSessionId = delete($dbhash{AcctSessionId});
+
+      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
+      # normally super-long, extending across month boundaries, or we need
+      # 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 ) {
+    my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time);
+    if ( $opt_m eq 'ftp') {
+      my $ftp = ftp();
+      $ftp->rename($filename, "$opt_d/$file_timestamp")
+        or do {
+          unlink "$cachedir/$filename";
+          die "Can't move $filename to $opt_d: ".$ftp->message . "\n";
+        };
+    } else {
+      my $sftp = sftp();
+      $sftp->rename($filename, "$opt_d/$file_timestamp")
+        or do {
+          unlink "$cachedir/$filename";
+          die "can't move $filename to $opt_d: ". $sftp->error . "\n";
+        };
+    }
+  }
+
+  unlink "$cachedir/$filename";
+
+}
+
+###
+# subs
+###
+
+sub usage {
+  "Usage:
+  aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
+    [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
+    user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass
+  ";
+}
+
+use vars qw( $sftp $ftp );
+
+sub ftp {
+  return $ftp if $ftp && $ftp->pwd;
+  
+  my ($hostname, $userpass) = reverse split('@', $servername);
+  my ($ftp_user, $ftp_pass) = split(':', $userpass);
+
+  my $ftp = Net::FTP->new($hostname, %options) 
+    or die "FTP connection to '$hostname' failed.";
+  $ftp->login($ftp_user, $ftp_pass) or die "FTP login failed: ".$ftp->message;
+  $ftp->cwd($opt_r) or die "can't chdir to $opt_r\n" if $opt_r;
+  $ftp->binary or die "can't set BINARY mode: ". $ftp->message if $opt_b;
+  return $ftp;
+}
+
+sub sftp {
+
+  #reuse connections
+  return $sftp if $sftp && $sftp->cwd;
+
+  my %sftp = ( host => $servername );
+
+  $sftp = Net::SFTP::Foreign->new(%sftp);
+  $sftp->error and die "SFTP connection failed: ". $sftp->error;
+
+  $sftp;
+}
+
+=head1 NAME
+
+freeside-aradial-sftp_and_import - Download Aradial "CDR" (really RADIUS detail) files from a remote server via SFTP
+
+=head1 SYNOPSIS
+
+  aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
+    [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
+    user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass
+
+=head1 DESCRIPTION
+
+Command line tool to download CDR files from a remote server via SFTP 
+or FTP and then import them into the database.
+
+-m: transfer method (sftp or ftp), defaults to sftp
+
+-a: use ftp passive mode
+
+-b: use ftp binary mode
+
+-r: if specified, changes into this remote folder before starting
+
+-d: if specified, moves files to the specified folder when done
+
+-P: if specified, sets the port to use
+
+-v: set verbosity level; this script only has one level, but it will 
+    be passed as the 'debug' argument to the transport method
+
+user: freeside username
+
+[sftpuser@]servername: remote server
+(or ftpuser:ftppass@servername)
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::cdr>
+
+=cut
+
+1;
+
diff --git a/FS/bin/freeside-backup b/FS/bin/freeside-backup
new file mode 100644
index 000000000..25e74a451
--- /dev/null
+++ b/FS/bin/freeside-backup
@@ -0,0 +1,42 @@
+#!/usr/bin/perl -w
+
+use strict;
+use Getopt::Std;
+use FS::UID qw(adminsuidsetup);
+use FS::Conf;
+
+my $user = shift or die &usage;
+adminsuidsetup $user;
+
+#you can skip this just by not having the config
+use FS::Cron::backup qw(backup);
+backup();
+
+sub usage {
+  die "Usage:\n\n  freeside-backup user\n";
+}
+
+###
+# documentation
+###
+
+=head1 NAME
+
+freeside-backup - Runs a backup
+
+=head1 SYNOPSIS
+
+  freeside-backup user
+
+=head1 DESCRIPTION
+
+Runs a backup.  See the dump-scpdest and dump-localdest configuration options.
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+=cut
+
+1;
+