From c47123a101c99b35c3c7b1be5b003b773ae00e06 Mon Sep 17 00:00:00 2001 From: ivan Date: Fri, 29 Aug 2008 01:09:09 +0000 Subject: [PATCH] add CDR batch TFTP feature, RT#3113 --- FS/FS/Schema.pm | 4 +- FS/FS/cdr.pm | 7 +- bin/cdr.sftp_and_import | 114 ++++++++++++++++++++++++++++ httemplate/elements/select-cdrbatch.html | 38 ++++++++++ httemplate/elements/tr-select-cdrbatch.html | 32 ++++++++ httemplate/search/cdr.html | 14 ++++ httemplate/search/report_cdr.html | 2 + 7 files changed, 209 insertions(+), 2 deletions(-) create mode 100755 bin/cdr.sftp_and_import create mode 100644 httemplate/elements/select-cdrbatch.html create mode 100644 httemplate/elements/tr-select-cdrbatch.html 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'} }) ) { + + + +% } else { + + + +% } + +<%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; + +} + + + 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'} }) ) { + + + +% } else { + + + <% $opt{'cdrbatch'} || 'CDR Batch: ' %> + + <% include( '/elements/select-cdrbatch.html', 'curr_value' => $selected_cdrbatch, %opt ) %> + + + +% } +<%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; + +} + + + 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' ) %> +
-- 2.11.0