From b282a5536ad5145f956ef27704e2267a124aacf5 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Wed, 1 Nov 2017 12:56:48 -0400 Subject: [PATCH] RT# 75095 - Added Ooma integration to one time charges --- FS/FS/cust_main/Import_Charges.pm | 171 +++++++++++++++++---- httemplate/misc/cust_main-import_charges.cgi | 41 ++++- .../misc/process/cust_main-import_charges.cgi | 20 +-- 3 files changed, 178 insertions(+), 54 deletions(-) diff --git a/FS/FS/cust_main/Import_Charges.pm b/FS/FS/cust_main/Import_Charges.pm index 312a6060b..64fcb5ccf 100644 --- a/FS/FS/cust_main/Import_Charges.pm +++ b/FS/FS/cust_main/Import_Charges.pm @@ -4,7 +4,6 @@ package FS::cust_main::Import_Charges; # subroutine for misc/process/cust_main-import_charges.cgi use strict; -use Text::CSV_XS; use FS::UID qw( dbh ); use FS::CurrentUser; use FS::Record qw( qsearchs ); @@ -39,24 +38,76 @@ Batch customer charging. =cut sub batch_charge { + my $job = shift; my $param = shift; #warn join('-',keys %$param); - my $fh = $param->{filehandle}; my $agentnum = $param->{agentnum}; my $format = $param->{format}; + my $files = $param->{'uploaded_files'} + or die "No files provided.\n"; + + my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files; + + my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/'; + my $filename = $dir. $files{'file'}; + + my $type; + if ( $filename =~ /\.(\w+)$/i ) { + $type = lc($1); + } else { + #or error out??? + warn "can't parse file type from filename $filename; defaulting to CSV"; + $type = 'csv'; + } + my $extra_sql = ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql; my @fields; + my %charges; if ( $format eq 'simple' ) { @fields = qw( custnum agent_custid amount pkg ); + } elsif ( $format eq 'ooma' ) { + @fields = ( 'userfield1', 'userfield2', 'userfield3', 'userfield4', 'userfield5', 'userfield6', 'userfield7', 'userfield8', 'userfield9', 'userfield10', 'amount', 'userfield12', 'userfield13', 'userfield14', 'userfield15', 'userfield16', 'userfield17', 'userfield18', 'pkg', 'userfield20', 'custnum', 'userfield22', 'userfield23', 'userfield24', 'userfield25', ); + ##should charges to charge be a config option? + %charges = ( + 'DISABILITY ACCESS/ENHANCED 911 SERVICES SURCHARGE' => '1', + 'FEDERAL TRS FUND' => '1', + 'FEDERAL UNIVERSAL SERVICE FUND' => '1', + 'STATE SALES TAX' => '1', + ); } else { die "unknown format $format"; } - my $csv = new Text::CSV_XS; - #warn $csv; - #warn $fh; + my $count; + my $parser; + my @buffer = (); + + if ( $type eq 'csv' ) { + + eval "use Text::CSV_XS;"; + eval "use File::Slurp qw( slurp );"; + die $@ if $@; + + $parser = new Text::CSV_XS; + + @buffer = split(/\r?\n/, slurp($filename) ); + $count = scalar(@buffer); + + } elsif ( $type eq 'xls' ) { + eval "use Spreadsheet::ParseExcel;"; + die $@ if $@; + + my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($filename); + $parser = $excel->{Worksheet}[0]; #first sheet + + $count = $parser->{MaxRow} || $parser->{MinRow}; + $count++; + + } else { + die "Unknown file type $type\n"; + } my $imported = 0; #my $columns; @@ -71,17 +122,36 @@ sub batch_charge { my $oldAutoCommit = $FS::UID::AutoCommit; local $FS::UID::AutoCommit = 0; my $dbh = dbh; - - #while ( $columns = $csv->getline($fh) ) { + my $line; - while ( defined($line=<$fh>) ) { + my $row = 0; + my %data = (); + my( $last, $min_sec ) = ( time, 5 ); #progressbar foo + while (1) { + my @columns = (); - $csv->parse($line) or do { - $dbh->rollback if $oldAutoCommit; - return "can't parse: ". $csv->error_input(); - }; + if ( $type eq 'csv' ) { + + last unless scalar(@buffer); + $line = shift(@buffer); + + $parser->parse($line) or do { + $dbh->rollback if $oldAutoCommit; + return "can't parse: ". $parser->error_input(); + }; + @columns = $parser->fields(); + + } elsif ( $type eq 'xls' ) { + last if $row > ($parser->{MaxRow} || $parser->{MinRow}) + || ! $parser->{Cells}[$row]; + + my @row = @{ $parser->{Cells}[$row] }; + @columns = map $_->{Val}, @row; + + } else { + die "Unknown file type $type\n"; + } - my @columns = $csv->fields(); #warn join('-',@columns); my %row = (); @@ -94,14 +164,26 @@ sub batch_charge { return "can't specify custnum with agent_custid $row{agent_custid}"; } + my $id; my %hash = (); + if ( $row{agent_custid} && $agentnum ) { + $id = $row{agent_custid}; + $data{$id}{cust} = ( + 'agent_custid' => $row{agent_custid}, + 'agentnum' => $agentnum, + ); %hash = ( 'agent_custid' => $row{agent_custid}, 'agentnum' => $agentnum, ); } if ( $row{custnum} ) { + $id = $row{custnum}; + $data{$id}{cust} = { + 'custnum' => $row{custnum}, + 'testnum' => 'test', + }; %hash = ( 'custnum' => $row{custnum} ); } @@ -110,40 +192,65 @@ sub batch_charge { return "can't find customer without custnum or agent_custid and agentnum"; } - my $cust_main = qsearchs('cust_main', { %hash } ); + ## add new pkg data or upate existing by adding new amount for custnum + $data{$id}{pkg}{$row{pkg}} = $data{$id}{pkg}{$row{pkg}} ? $data{$id}{pkg}{$row{pkg}} + $row{'amount'} : $row{'amount'}; + + $row++; + + if ( $job && time - $min_sec > $last ) { #progress bar + $job->update_statustext( int(100 * $row / $count) ); + $last = time; + } + + } + + ### run through data hash to post all charges. + foreach my $k (keys %data) { + my %pkg_hash = %{$data{$k}{pkg}}; + my %cust_hash = %{$data{$k}{cust}}; + + my $cust_main = qsearchs('cust_main', { %cust_hash } ); unless ( $cust_main ) { $dbh->rollback if $oldAutoCommit; - my $custnum = $row{custnum} || $row{agent_custid}; + my $custnum = $cust_hash{custnum} || $cust_hash{agent_custid}; return "unknown custnum $custnum"; } - if ( $row{'amount'} > 0 ) { - my $error = $cust_main->charge($row{'amount'}, $row{'pkg'}); - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return $error; - } - $imported++; - } elsif ( $row{'amount'} < 0 ) { - my $error = $cust_main->credit( sprintf( "%.2f", 0-$row{'amount'} ), - $row{'pkg'} ); - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return $error; - } - $imported++; - } else { + foreach my $pkg_key (keys %pkg_hash) { + my $pkg = $pkg_key; + my $amount = $pkg_hash{$pkg_key}; + + if (%charges) { next unless $charges{$pkg}; } + + if ( $amount > 0 ) { + my $error = $cust_main->charge($amount, $pkg); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + $imported++; + } elsif ( $amount < 0 ) { + my $error = $cust_main->credit( sprintf( "%.2f", 0-$amount ), $pkg ); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + $imported++; + } else { #hmm? + } } } $dbh->commit or die $dbh->errstr if $oldAutoCommit; + unlink $filename; + return "Empty file!" unless $imported; ''; #no error } -1; +1; \ No newline at end of file diff --git a/httemplate/misc/cust_main-import_charges.cgi b/httemplate/misc/cust_main-import_charges.cgi index 065506369..4eacce13a 100644 --- a/httemplate/misc/cust_main-import_charges.cgi +++ b/httemplate/misc/cust_main-import_charges.cgi @@ -3,7 +3,15 @@ Import a CSV file containing customer charges.

-
+<& /elements/form-file_upload.html, + 'name' => 'OneTimeChargeImportForm', + 'action' => 'process/cust_main-import_charges.cgi', + 'num_files' => 1, + 'fields' => [ 'agentnum', 'custbatch', 'format' ], + 'message' => 'One time charge batch import successful', + 'url' => $p."misc/cust_main-import_charges.cgi", + 'onsubmit' => "document.OneTimeChargeImportForm.submitButton.disabled=true;", +&> <% &ntable("#cccccc", 2) %> @@ -14,23 +22,35 @@ Import a CSV file containing customer charges. ) %> + + Format + <% include( '/elements/file-upload.html', + 'field' => 'file', + 'label' => 'Filename', + ) + %> + - CSV filename - + + + - -
@@ -59,11 +79,22 @@ Field information:
+Ooma format has the following field order: Description, Description2, Record Type, Customer Number, Billing Phone Number or Zip Code, Bus/Res Indicator, Invoice Date, Invoice Number, Group, Item, Revenue<%$req%>, LineCount, Exempt, ExemptList, State, City, Zipcode, OfferingPK, Offering name<%$req%>, Quantity, AccountNo<%$req%>, Status, Cust Created, PartnerID +

+ + <% include('/elements/footer.html') %> + +<%once> + my $req = qq!*!; + + <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Import'); + my $custbatch = time2str('webimport-%Y/%m/%d-%T'. "-$$-". rand() * 2**32, time); + diff --git a/httemplate/misc/process/cust_main-import_charges.cgi b/httemplate/misc/process/cust_main-import_charges.cgi index d877ad17d..42ca82592 100644 --- a/httemplate/misc/process/cust_main-import_charges.cgi +++ b/httemplate/misc/process/cust_main-import_charges.cgi @@ -1,24 +1,10 @@ -% if ( $error ) { -% errorpage($error); -% } else { - <% include('/elements/header.html','Import successful') %> - <% include('/elements/footer.html') %> -% } +<% $server->process %> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Import'); -my $fh = $cgi->upload('csvfile'); -#warn $cgi; -#warn $fh; - -my $error = defined($fh) - ? FS::cust_main::Import_Charges::batch_charge( { - filehandle => $fh, - 'agentnum' => scalar($cgi->param('agentnum')), - 'format' => scalar($cgi->param('format')), - } ) - : 'No file'; +my $server = + new FS::UI::Web::JSRPC 'FS::cust_main::Import_Charges::batch_charge', $cgi; -- 2.11.0