From: Christopher Burger Date: Tue, 11 Dec 2018 23:08:18 +0000 (-0500) Subject: Revert "RT# 74693 - Added ability to bulk edit rates with excel" X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=639c645c0f00c082a735b40f97a4f830c5e84949;hp=006f36a6d2ee10664c3207b47ff046de447ea8bb Revert "RT# 74693 - Added ability to bulk edit rates with excel" This reverts commit 1d9fd3b93be720823656cd23db79ff74e2e7a829. --- diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm index a8aaeef77..5325fa562 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 $DEBUG $me); # $cityflag ); + @cust_main_county %cust_main_county $countyflag ); # $cityflag ); use Exporter; use FS::Record qw( qsearch qsearchs dbh ); use FS::cust_bill_pkg; @@ -14,9 +14,6 @@ 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 = (); @@ -716,299 +713,6 @@ 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 722c699a2..9df8fed0b 100755 --- a/httemplate/browse/cust_main_county.cgi +++ b/httemplate/browse/cust_main_county.cgi @@ -459,8 +459,6 @@ my $html_foot = <Bulk edit selected | Bulk edit rate only 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 deleted file mode 100644 index a9928f902..000000000 --- a/httemplate/misc/process/tax_edit_excel.html +++ /dev/null @@ -1,9 +0,0 @@ -<% $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 deleted file mode 100644 index 1546393d9..000000000 --- a/httemplate/misc/tax_edit_excel.html +++ /dev/null @@ -1,70 +0,0 @@ -<% 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