add CDR batch TFTP feature, RT#3113

7 files changed, 209 insertions, 2 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 2043b45ec..94a56248b 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -1921,10 +1921,12 @@ sub tables_hashref {
         #NULL, done (or something)
         'freesidestatus', 'varchar',   'NULL',     32,   '', '', 
 
+        'cdrbatch', 'varchar', 'NULL', $char_d, '', '',
+
       ],
       'primary_key' => 'acctid',
       'unique' => [],
-      'index' => [ [ 'calldate' ], [ 'dst' ], [ 'accountcode' ], [ 'freesidestatus' ] ],
+      'index' => [ [ 'calldate' ], [ 'dst' ], [ 'accountcode' ], [ 'freesidestatus' ], [ 'cdrbatch' ], ],
     },
 
     'cdr_calltype' => {
diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm
index 439d5ae69..8b09e4ec4 100644
--- a/FS/FS/cdr.pm
+++ b/FS/FS/cdr.pm
@@ -129,6 +129,8 @@ following fields are currently supported:
 
 =item freesidestatus - NULL, done (or something)
 
+=item cdrbatch
+
 =back
 
 =head1 METHODS
@@ -617,6 +619,7 @@ sub batch_import {
 
   my $fh = $param->{filehandle};
   my $format = $param->{format};
+  my $cdrbatch = $param->{cdrbatch};
 
   return "Unknown format $format"
     unless exists( $cdr_info{$format} )
@@ -699,6 +702,8 @@ sub batch_import {
       }
       @{ $info->{'import_fields'} }
     ;
+ 
+    $cdr{cdrbatch} = $cdrbatch;
 
     my $cdr = new FS::cdr ( \%cdr );
 
@@ -732,7 +737,7 @@ sub batch_import {
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 
   #might want to disable this if we skip records for any reason...
-  return "Empty file!" unless $imported;
+  return "Empty file!" unless $imported || $param->{empty_ok};
 
   '';
 
diff --git a/bin/cdr.sftp_and_import b/bin/cdr.sftp_and_import
new file mode 100755
index 000000000..f68d70c76
--- /dev/null
+++ b/bin/cdr.sftp_and_import
@@ -0,0 +1,114 @@
+#!/usr/bin/perl
+#
+# Usage:
+#  cdr.sftp_and_import [ -e extension ] [ -d donefolder ] [ -v ] user format [sftpuser@]servername
+#
+# -e: file extension, defaults to .csv
+# -d: if specified, moves files to the specified folder when done
+
+use strict;
+use Getopt::Std;
+use Net::SFTP::Foreign;
+use FS::UID qw(adminsuidsetup datasrc);
+use FS::cdr;
+
+###
+# parse command line
+###
+
+use vars qw( $opt_e $opt_d $opt_u $opt_v );
+getopts('e:d:u:v');
+
+$opt_e ||= 'csv';
+#$opt_e = ".$opt_e" unless $opt_e =~ /^\./;
+$opt_e =~ s/^\.//;
+
+my $user = shift or die &usage;
+adminsuidsetup $user;
+
+# %%%FREESIDE_CACHE%%%
+my $cachedir = '/usr/local/etc/freeside/cache.'. datasrc. '/cdrs';
+mkdir $cachedir unless -d $cachedir;
+
+my $format = shift or die &usage;
+
+use vars qw( $servername );
+$servername = shift or die &usage;
+
+###
+# get the file list
+###
+
+warn "Retreiving directory listing\n" if $opt_v;
+
+my $ls_sftp = sftp();
+
+my $ls = $ls_sftp->ls('.', wanted => qr/\.*$opt_e$/i );
+
+###
+# import each file
+###
+
+foreach my $file ( @$ls ) {
+
+  my $filename = $file->{filename};
+  warn "Downloading $filename\n" if $opt_v;
+
+  #get the file
+  my $get_sftp = sftp();
+  $get_sftp->get($filename, "$cachedir/$filename")
+    or die "Can't get $filename: ". $get_sftp->error;
+
+  warn "Processing $filename\n" if $opt_v;
+
+  open(FILE, "$cachedir/$filename") or die "can't read $cachedir/$filename: $!";
+
+  my $error = FS::cdr::batch_import( {
+    'filehandle' => *FILE{IO},
+    'format'     => $format,
+    'cdrbatch'   => $filename,
+    'empty_ok'   => 1,
+  } );
+  die $error if $error;
+
+  close FILE;
+
+  if ( $opt_d ) {
+    my $mv_sftp = sftp();
+    $mv_sftp->rename($filename, "$opt_d/$filename")
+      or die "can't move $filename to $opt_d: ". $mv_sftp->error;
+  }
+
+  unlink "$cachedir/$filename";
+
+}
+
+1;
+
+###
+# sub
+###
+
+sub usage {
+  "Usage: \n  cdr.import user format servername\n";
+}
+
+use vars qw( $sftp );
+
+sub sftp {
+
+  #reuse connections
+  return $sftp if $sftp && $sftp->cwd;
+
+  my %sftp = ( host => $servername );
+
+  #XXX remove these
+  $sftp{port} = 10022;
+  #$sftp{more} = '-v';
+
+  $sftp = Net::SFTP::Foreign->new(%sftp);
+  $sftp->error and die "SFTP connection failed: ". $sftp->error;
+
+  $sftp;
+}
+
diff --git a/httemplate/elements/select-cdrbatch.html b/httemplate/elements/select-cdrbatch.html
new file mode 100644
index 000000000..866ba2516
--- /dev/null
+++ b/httemplate/elements/select-cdrbatch.html
@@ -0,0 +1,38 @@
+% if ( scalar(@{ $opt{'cdrbatches'} }) ) {
+
+    <SELECT NAME="<% $opt{'name'} || 'cdrbatch' %>">
+
+    <OPTION VALUE="__ALL__">All
+    <OPTION VALUE="">(blank)
+
+%   foreach my $cdrbatch ( @{ $opt{'cdrbatches'} } ) { 
+        <OPTION VALUE="<% $cdrbatch %>"<% $cdrbatch eq $selected_cdrbatch ? ' SELECTED' : '' %>><% $cdrbatch %>
+%   } 
+
+    </SELECT>
+
+% } else {
+
+  <INPUT TYPE="hidden" NAME="cdrbatch" VALUE="__ALL__">
+
+% }
+
+<%init>
+
+my %opt = @_;
+my $selected_cdrbatch = $opt{'curr_value'}; # || $opt{'value'} necessary?
+
+my $conf = new FS::Conf;
+
+unless ( $opt{'cdrbatches'} ) {
+
+  my $sth = dbh->prepare('SELECT DISTINCT cdrbatch FROM cdr')
+     or die dbh->errstr;
+  $sth->execute or die $sth->errstr;
+  my %cdrbatches = map { $_->[0] => 1 } @{$sth->fetchall_arrayref};
+  @{ $opt{'cdrbatches'} } = grep $_, keys %cdrbatches;
+
+}
+
+</%init>
+
diff --git a/httemplate/elements/tr-select-cdrbatch.html b/httemplate/elements/tr-select-cdrbatch.html
new file mode 100644
index 000000000..21cd00462
--- /dev/null
+++ b/httemplate/elements/tr-select-cdrbatch.html
@@ -0,0 +1,32 @@
+% if ( ! scalar(@{ $opt{'cdrbatches'} }) ) { 
+
+  <INPUT TYPE="hidden" NAME="<% $opt{'element_name'} || $opt{'field'} || 'cdrbatch' %>" VALUE="<% $selected_cdrbatch %>">
+
+% } else { 
+
+  <TR>
+    <TD ALIGN="right"><% $opt{'cdrbatch'} || 'CDR Batch: ' %></TD>
+    <TD>
+      <% include( '/elements/select-cdrbatch.html', 'curr_value' => $selected_cdrbatch, %opt ) %>
+    </TD>
+  </TR>
+
+% } 
+<%init>
+
+my( %opt ) = @_;
+my $conf = new FS::Conf;
+my $selected_cdrbatch = $opt{'curr_value'}; # || $opt{'value'} necessary?
+
+unless ( $opt{'cdrbatches'} ) {
+
+  my $sth = dbh->prepare('SELECT cdrbatch FROM cdr')
+    or die dbh->errstr;
+  $sth->execute or die $sth->errstr;
+  my %cdrbatches = map { $_->[0] => 1 } @{$sth->fetchall_arrayref};
+  @{ $opt{'cdrbatches'} } = grep $_, keys %cdrbatches;
+
+}
+
+</%init>
+
diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html
index 4bac8c1f9..0fa64c00c 100644
--- a/httemplate/search/cdr.html
+++ b/httemplate/search/cdr.html
@@ -89,6 +89,20 @@ if ( $cgi->param('charged_party') =~ /^\s*([\d\-\+\ ]+)\s*$/ ) {
                     OR charged_party = '1$charged_party' ) ";
 }
 
+###
+# cdrbatch
+###
+
+if ( $cgi->param('cdrbatch') ne '__ALL__' ) {
+  if ( $cgi->param('cdrbatch') eq '' ) {
+    my $search = "( cdrbatch IS NULL OR cdrbatch = '' )";
+    push @qsearch, $search;
+    push @search,  $search;
+  } else {
+    $hashref->{cdrbatch} = $cgi->param('cdrbatch');
+    push @search, 'cdrbatch = '. dbh->quote($cgi->param('cdrbatch'));
+  }
+}
 
 ###
 # finish it up
diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html
index 1c191506f..28516313b 100644
--- a/httemplate/search/report_cdr.html
+++ b/httemplate/search/report_cdr.html
@@ -42,6 +42,8 @@
             )
   %>
 
+  <% include( '/elements/tr-select-cdrbatch.html' ) %>
+
 </TABLE>
 
 <BR>