# 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 );
=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;
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 = ();
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} );
}
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
Import a CSV file containing customer charges.
<BR><BR>
-<FORM ACTION="process/cust_main-import_charges.cgi" METHOD="post" ENCTYPE="multipart/form-data">
+<& /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) %>
)
%>
+<INPUT TYPE="hidden" NAME="custbatch" VALUE="<% $custbatch %>"%>
+
<TR>
<TH ALIGN="right">Format</TH>
<TD>
<SELECT NAME="format">
<OPTION VALUE="simple">Simple
+ <OPTION VALUE="ooma">Ooma
<!-- <OPTION VALUE="extended" SELECTED>Extended -->
</SELECT>
</TD>
</TR>
+ <% include( '/elements/file-upload.html',
+ 'field' => 'file',
+ 'label' => 'Filename',
+ )
+ %>
+
<TR>
- <TH ALIGN="right">CSV filename</TH>
- <TD><INPUT TYPE="file" NAME="csvfile"></TD>
+ <TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px">
+ <INPUT TYPE = "submit"
+ NAME = "submitButton"
+ ID = "submitButton"
+ VALUE = "Import file"
+ >
+ </TD>
</TR>
-<TR><TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px"><INPUT TYPE="submit" VALUE="Import CSV file"></TD></TR>
-
</TABLE>
</FORM>
<BR>
+<b>Ooma</b> format has the following field order: <i>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</i>
+<BR><BR>
+
+
<% include('/elements/footer.html') %>
+
+<%once>
+ my $req = qq!<font color="#ff0000">*</font>!;
+</%once>
+
<%init>
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Import');
+ my $custbatch = time2str('webimport-%Y/%m/%d-%T'. "-$$-". rand() * 2**32, time);
+
</%init>
projects / freeside.git / commitdiff