From 1d9fd3b93be720823656cd23db79ff74e2e7a829 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Wed, 14 Nov 2018 12:55:13 -0500 Subject: [PATCH] RT# 74693 - Added ability to bulk edit rates with excel --- FS/FS/cust_main_county.pm | 298 +++++++++++++++++++++++++++- httemplate/browse/cust_main_county.cgi | 3 +- httemplate/misc/process/tax_edit_excel.html | 9 + httemplate/misc/tax_edit_excel.html | 70 +++++++ 4 files changed, 378 insertions(+), 2 deletions(-) create mode 100644 httemplate/misc/process/tax_edit_excel.html create mode 100644 httemplate/misc/tax_edit_excel.html diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm index 5325fa562..a8aaeef77 100644 --- a/FS/FS/cust_main_county.pm +++ b/FS/FS/cust_main_county.pm @@ -3,7 +3,7 @@ use base qw( FS::Record ); use strict; use vars qw( @EXPORT_OK $conf - @cust_main_county %cust_main_county $countyflag ); # $cityflag ); + @cust_main_county %cust_main_county $countyflag $DEBUG $me); # $cityflag ); use Exporter; use FS::Record qw( qsearch qsearchs dbh ); use FS::cust_bill_pkg; @@ -14,6 +14,9 @@ use FS::cust_tax_exempt; use FS::cust_tax_exempt_pkg; use FS::upgrade_journal; +$DEBUG = 0; +$me = '[FS::cust_main_county]'; + @EXPORT_OK = qw( regionselector ); @cust_main_county = (); @@ -713,6 +716,299 @@ sub _merge_into { } } +=item process_edit_import + +=cut + +use Data::Dumper; +sub process_edit_import { + my $job = shift; + + my $opt = { 'table' => 'cust_main_county', + 'params' => [], #required, apparantly + 'formats' => { 'default' => [ + 'country', + 'state', + 'county', + 'city', + '', #tax class + 'taxname', + 'tax', + 'old_tax', #old tax + ] }, + 'format_headers' => { 'default' => 1, }, + 'format_types' => { 'default' => 'xls' }, + }; + + #false laziness w/ + #FS::Record::process_batch_import( $job, $opt, @_ ); + + my $table = $opt->{table}; + my @pass_params = @{ $opt->{params} }; + my %formats = %{ $opt->{formats} }; + + my $param = shift; + warn Dumper($param) if $DEBUG; + + 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 $file = $dir. $files{'file'}; + + my $error = + #false laziness w/ + #FS::Record::batch_import( { + FS::cust_main_county::edit_import( { + #class-static + table => $table, + formats => \%formats, + format_types => $opt->{format_types}, + format_headers => $opt->{format_headers}, + format_sep_chars => $opt->{format_sep_chars}, + format_fixedlength_formats => $opt->{format_fixedlength_formats}, + #per-import + job => $job, + file => $file, + #type => $type, + format => $param->{format}, + params => { map { $_ => $param->{$_} } @pass_params }, + #? + default_csv => $opt->{default_csv}, + } ); + + unlink $file; + + die "$error\n" if $error; + +} + +=item edit_import + +=cut + +#false laziness w/ #FS::Record::batch_import, grep "edit_import" for differences +#could be turned into callbacks or something +use Text::CSV_XS; +sub edit_import { + my $param = shift; + + warn "$me edit_import call with params: \n". Dumper($param) + if $DEBUG; + + my $table = $param->{table}; + my $formats = $param->{formats}; + + my $job = $param->{job}; + my $file = $param->{file}; + my $format = $param->{'format'}; + my $params = $param->{params} || {}; + + die "unknown format $format" unless exists $formats->{ $format }; + + my $type = $param->{'format_types'} + ? $param->{'format_types'}{ $format } + : $param->{type} || 'csv'; + + unless ( $type ) { + if ( $file =~ /\.(\w+)$/i ) { + $type = lc($1); + } else { + #or error out??? + warn "can't parse file type from filename $file; defaulting to CSV"; + $type = 'csv'; + } + $type = 'csv' + if $param->{'default_csv'} && $type ne 'xls'; + } + + my $header = $param->{'format_headers'} + ? $param->{'format_headers'}{ $param->{'format'} } + : 0; + + my $sep_char = $param->{'format_sep_chars'} + ? $param->{'format_sep_chars'}{ $param->{'format'} } + : ','; + + my $fixedlength_format = + $param->{'format_fixedlength_formats'} + ? $param->{'format_fixedlength_formats'}{ $param->{'format'} } + : ''; + + my @fields = @{ $formats->{ $format } }; + + my $row = 0; + my $count; + my $parser; + my @buffer = (); + my @header = (); #edit_import + if ( $type eq 'csv' || $type eq 'fixedlength' ) { + + if ( $type eq 'csv' ) { + + my %attr = (); + $attr{sep_char} = $sep_char if $sep_char; + $parser = new Text::CSV_XS \%attr; + + } elsif ( $type eq 'fixedlength' ) { + + eval "use Parse::FixedLength;"; + die $@ if $@; + $parser = new Parse::FixedLength $fixedlength_format; + + } else { + die "Unknown file type $type\n"; + } + + @buffer = split(/\r?\n/, slurp($file) ); + splice(@buffer, 0, ($header || 0) ); + $count = scalar(@buffer); + + } elsif ( $type eq 'xls' ) { + + eval "use Spreadsheet::ParseExcel;"; + die $@ if $@; + + eval "use DateTime::Format::Excel;"; + #for now, just let the error be thrown if it is used, since only CDR + # formats bill_west and troop use it, not other excel-parsing things + #die $@ if $@; + + my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($file); + + $parser = $excel->{Worksheet}[0]; #first sheet + + $count = $parser->{MaxRow} || $parser->{MinRow}; + $count++; + + $row = $header || 0; + + #edit_import - need some magic to parse the header + if ( $header ) { + my @header_row = @{ $parser->{Cells}[$0] }; + @header = map $_->{Val}, @header_row; + } + + } else { + die "Unknown file type $type\n"; + } + + #my $columns; + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $line; + my $imported = 0; + my( $last, $min_sec ) = ( time, 5 ); #progressbar foo + while (1) { + + my @columns = (); + 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 'fixedlength' ) { + + @columns = $parser->parse($line); + + } elsif ( $type eq 'xls' ) { + + last if $row > ($parser->{MaxRow} || $parser->{MinRow}) + || ! $parser->{Cells}[$row]; + + my @row = @{ $parser->{Cells}[$row] }; + @columns = map $_->{Val}, @row; + + #my $z = 'A'; + #warn $z++. ": $_\n" for @columns; + + } else { + die "Unknown file type $type\n"; + } + + #edit_import loop + + my %hash = %$params; + my @later; + + foreach my $field ( @fields ) { + + my $value = shift @columns; + + if ( ref($field) eq 'CODE' ) { + #&{$field}(\%hash, $value); + push @later, $field, $value; + } elsif ($field) { #edit_import + $hash{$field} = $value if defined($value) && length($value); + } + + } + + my $class = "FS::$table"; + + my $record = $class->new( \%hash ); + + while ( scalar(@later) ) { + my $sub = shift @later; + my $data = shift @later; + &{$sub}($record, $data); #edit_import - don't have $conf + } + + #edit_import update or insert, not just insert + my $old = qsearchs({ + 'table' => $table, + 'hashref' => { map { $_ => $record->$_() } qw(country state county city taxname) }, + }); + + my $error; + if ( $old ) { + $record->taxnum($old->taxnum); + $error = $record->replace($old) + } else { + $record->insert; + } + + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "can't insert record". ( $line ? " for $line" : '' ). ": $error"; + } + + $row++; + $imported++; + + if ( $job && time - $min_sec > $last ) { #progress bar + $job->update_statustext( int(100 * $imported / $count) ); + $last = time; + } + + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit;; + + return "Empty file!" unless $imported || $param->{empty_ok}; + + ''; #no error + +} + sub _upgrade_data { my $class = shift; # assume taxes in Washington with district numbers, and null name, or diff --git a/httemplate/browse/cust_main_county.cgi b/httemplate/browse/cust_main_county.cgi index 26a3e21b9..552327836 100755 --- a/httemplate/browse/cust_main_county.cgi +++ b/httemplate/browse/cust_main_county.cgi @@ -411,7 +411,8 @@ my $html_foot = <Add new tax to selected | Bulk edit selected - +| +bulk edit with excel file END my $hashref = {}; diff --git a/httemplate/misc/process/tax_edit_excel.html b/httemplate/misc/process/tax_edit_excel.html new file mode 100644 index 000000000..a9928f902 --- /dev/null +++ b/httemplate/misc/process/tax_edit_excel.html @@ -0,0 +1,9 @@ +<% $server->process %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +my $server = new FS::UI::Web::JSRPC 'FS::cust_main_county::process_edit_import', $cgi; + + \ No newline at end of file diff --git a/httemplate/misc/tax_edit_excel.html b/httemplate/misc/tax_edit_excel.html new file mode 100644 index 000000000..1546393d9 --- /dev/null +++ b/httemplate/misc/tax_edit_excel.html @@ -0,0 +1,70 @@ +<% include('/elements/header.html', 'Edit tax rates with Excel' ) %> + +% # 'name' => 'RateImportForm', + +<& /elements/form-file_upload.html, + 'name' => 'TaxEditForm', + 'action' => 'process/tax_edit_excel.html', + 'num_files' => 1, + 'fields' => [ 'format' ], + 'message' => 'Tax Rate edit successful', + 'url' => $p."browse/cust_main_county.cgi", + 'onsubmit' => "document.TaxEditForm.submitButton.disabled=true;" +&> + +<% &ntable("#cccccc", 2) %> + + + File format should be as follows:
+ + + + + + + +
Country as standard two letter code
State as standard two letter code
County name
City name
Tax name
Tax rate

+ * first row should be blank or contain headers
+ * Tax rate should be formated as a number not percentage. +

+ + + + Upload tax rates with Excel (or other .XLS-compatible application) + + + + <% include( '/elements/file-upload.html', + 'field' => 'file', + 'label' => '', + 'label_align' => 'left', + ) + %> + + + + + + + + + + + + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +my $sth = dbh->prepare('SELECT COUNT(*) FROM rate_detail WHERE conn_charge > 0 OR conn_sec > 0 LIMIT 1') + or die dbh->errstr; +$sth->execute or die $sth->errstr; +my $have_conn = $sth->fetchrow_arrayref->[0]; + + \ No newline at end of file -- 2.11.0