From f4a0aa6fc746ba02dd021ece8f46b2ca4e001cc4 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Sat, 8 Dec 2018 08:55:07 -0500 Subject: RT# 77532 - search count is now correct --- FS/FS/cust_main/Search.pm | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 3e77704e6..26f6f0394 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -1086,8 +1086,6 @@ sub search { # (maybe we should be using FS::UI::Web::join_cust_main instead?) $addl_from .= ' LEFT JOIN (select refnum, referral from part_referral) AS part_referral_x ON (cust_main.refnum = part_referral_x.refnum) '; - my $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql"; - my @select = ( 'cust_main.custnum', 'cust_main.salesnum', @@ -1140,6 +1138,8 @@ sub search { } + my $count_query = "SELECT COUNT(DISTINCT cust_main.custnum) FROM cust_main $addl_from $extra_sql"; + if ($params->{'flattened_pkgs'}) { #my $pkg_join = ''; -- cgit v1.2.1 From 5f6b143801578ebcc30b97fba1b047f80fa232fe Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 9 Dec 2018 17:53:20 -0500 Subject: RT# 81706 Fix for transparent customer menu tabs --- httemplate/elements/dropdown-menu.html | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/httemplate/elements/dropdown-menu.html b/httemplate/elements/dropdown-menu.html index 54447a23f..3c0f40f75 100644 --- a/httemplate/elements/dropdown-menu.html +++ b/httemplate/elements/dropdown-menu.html @@ -5,12 +5,17 @@ border: none; } +% if ( $opt{id} !~ /customer_/ ) { +% # Fix for changes to how jQuery UI applies state classes + #<% $opt{id} %> .ui-state-active { color: inherit; background-color: transparent; border-color: transparent; } +% } + #<% $opt{id} %> li { float: left; padding: .25em; -- cgit v1.2.1 From 1f0d70b0b4e3149ac515f20fbfa6da2a3254e07a Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 9 Dec 2018 18:37:56 -0500 Subject: RT# 79916 Makefile copies latex .sty files --- Makefile | 1 + 1 file changed, 1 insertion(+) diff --git a/Makefile b/Makefile index 3486b7967..864fee6b9 100644 --- a/Makefile +++ b/Makefile @@ -271,6 +271,7 @@ dev-perl-modules: perl-modules ln -sf ${FREESIDE_PATH}/FS/blib/lib/FS ${PERL_INC_DEV_KLUDGE}/FS install-texmf: + mkdir -p /usr/local/share/texmf/tex/latex install -D -o freeside -m 444 etc/*.sty \ /usr/local/share/texmf/tex/latex/ texhash /usr/local/share/texmf -- cgit v1.2.1 From aa0c6cc1247b802d58b4c890339bcacf4d3f567a Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 9 Dec 2018 20:26:34 -0500 Subject: RT# 81701 Add system log lines for EFT Canada upload/download --- FS/bin/freeside-eftca-download | 73 ++++++++++++++++++++++++++++++------------ FS/bin/freeside-eftca-upload | 47 ++++++++++++++++++++------- 2 files changed, 89 insertions(+), 31 deletions(-) diff --git a/FS/bin/freeside-eftca-download b/FS/bin/freeside-eftca-download index 1b7653cb3..caf9e0e70 100755 --- a/FS/bin/freeside-eftca-download +++ b/FS/bin/freeside-eftca-download @@ -11,6 +11,7 @@ use FS::Record qw(qsearch qsearchs); use FS::pay_batch; use FS::cust_pay_batch; use FS::Conf; +use FS::Log; use vars qw( $opt_v $opt_a ); getopts('va:'); @@ -38,11 +39,15 @@ my @fields = ( my $user = shift or die &HELP_MESSAGE; adminsuidsetup $user; +my $log = FS::Log->new('freeside-eftca-download'); +log_info( "EFT Canada download started\n" ); + if ( $opt_a ) { - die "no such directory: $opt_a\n" + log_error_and_die( "no such directory: $opt_a\n" ) unless -d $opt_a; - die "archive directory $opt_a is not writable by the freeside user\n" - unless -w $opt_a; + log_error_and_die( + "archive directory $opt_a is not writable by the freeside user\n" + ) unless -w $opt_a; } #my $tmpdir = File::Temp->newdir(); @@ -63,51 +68,58 @@ foreach my $agent (@agents) { if ( $conf->exists('batch-spoolagent') ) { @batchconf = $conf->config('batchconfig-eft_canada', $agent->agentnum, 1); if ( !length($batchconf[0]) ) { - warn "agent '".$agent->agent."' has no batchconfig-eft_canada setting; skipped.\n"; + log_info( + "agent '".$agent->agent. + "' has no batchconfig-eft_canada setting; skipped.\n" + ); next; } } else { @batchconf = $conf->config('batchconfig-eft_canada'); } # user, password, transaction code, delay days - my $user = $batchconf[0] or die "no EFT Canada batch username configured\n"; - my $pass = $batchconf[1] or die "no EFT Canada batch password configured\n"; + my $user = $batchconf[0] + or log_error_and_die( "no EFT Canada batch username configured\n" ); + my $pass = $batchconf[1] + or log_error_and_die( "no EFT Canada batch password configured\n" ); my $host = 'ftp.eftcanada.com'; - print STDERR "Connecting to $user\@$host...\n" if $opt_v; + log_info( "Connecting to $user\@$host...\n" ); my $sftp = Net::SFTP::Foreign->new( host => $host, user => $user, password => $pass, timeout => 30, ); - die "failed to connect to '$user\@$host'\n(".$sftp->error.")\n" if $sftp->error; + log_error_and_die("failed to connect to '$user\@$host'\n(".$sftp->error.")\n") + if $sftp->error; $sftp->setcwd('/Returns'); my $files = $sftp->ls('.', wanted => qr/\.txt$/, names_only => 1); - die "no response files found\n" if !@$files; + log_info_and_die( "Finished: No response files found\n" ) + if !@$files; FILE: foreach my $filename (@$files) { - print STDERR "Retrieving $filename\n" if $opt_v; + log_info( "Retrieving $filename\n" ); $sftp->get("$filename", "$tmpdir/$filename"); if($sftp->error) { - warn "failed to download $filename\n"; + log_info( "failed to download $filename\n" ); next FILE; } #move to server archive dir $sftp->rename("$filename", "Archive/$filename"); if($sftp->error) { - warn "failed to archive $filename on server\n"; + log_info( "failed to archive $filename on server\n" ); } # process it anyway though #copy to local archive dir if ( $opt_a ) { - print STDERR "Copying $tmpdir/$filename to archive dir $opt_a\n" - if $opt_v; + log_info( "Copying $tmpdir/$filename to archive dir $opt_a\n" ); system 'cp', "$tmpdir/$filename", $opt_a; - warn "failed to copy $tmpdir/$filename to $opt_a: $@" if $@; + log_info( "failed to copy $tmpdir/$filename to $opt_a: $@" ) + if $@; } open my $fh, "<$tmpdir/$filename"; @@ -118,20 +130,23 @@ foreach my $agent (@agents) { while (my $line = <$fh>) { next if $line =~ /^\s*$/; $csv->parse($line) or do { - warn "can't parse $filename: ".$csv->error_input."\n"; + log_info( "can't parse $filename: ".$csv->error_input."\n" ); next FILE; #parsing errors = reading the wrong kind of file }; @hash{@fields} = $csv->fields(); - print STDERR "voiding paybatchnum#$hash{paybatchnum}\n" if $opt_v; + log_info( "voiding paybatchnum#$hash{paybatchnum}\n" ); my $cpb = qsearchs('cust_pay_batch', { paybatchnum => $hash{'paybatchnum'} }); if ( !$cpb ) { - warn "can't find paybatchnum #$hash{paybatchnum} ($hash{first} $hash{last}, $hash{paid})\n"; + log_info( + "can't find paybatchnum #$hash{paybatchnum} ". + "($hash{first} $hash{last}, $hash{paid})\n" + ); next; } my $error = $cpb->decline("Returned payment ($hash{returncode})"); if ( $error ) { - warn "can't void paybatchnum #$hash{paybatchnum}: $error\n"; + log_info( "can't void paybatchnum #$hash{paybatchnum}: $error\n" ); } } close $fh; @@ -139,7 +154,25 @@ foreach my $agent (@agents) { } -print STDERR "Finished!\n" if $opt_v; +log_info( "Finished!\n" ); + +sub log_info { + my $log_message = shift; + $log->info( $log_message ); + print STDERR $log_message if $opt_v; +} + +sub log_info_and_die { + my $log_message = shift; + $log->info( $log_message ); + die $log_message; +} + +sub log_error_and_die { + my $log_message = shift; + $log->error( $log_message ); + die $log_message; +} =head1 NAME diff --git a/FS/bin/freeside-eftca-upload b/FS/bin/freeside-eftca-upload index afe60afd9..9818cbdb5 100755 --- a/FS/bin/freeside-eftca-upload +++ b/FS/bin/freeside-eftca-upload @@ -9,6 +9,7 @@ use FS::UID qw(adminsuidsetup dbh); use FS::Record qw(qsearch qsearchs); use FS::pay_batch; use FS::Conf; +use FS::Log; use vars qw( $opt_a $opt_v ); getopts('av'); @@ -24,17 +25,20 @@ sub HELP_MESSAGE { " my $user = shift or die &HELP_MESSAGE; adminsuidsetup $user; +my $log = FS::Log->new('freeside-eftca-upload'); +log_info( "EFT Canada upload started\n" ); + my @batches; if($opt_a) { @batches = qsearch('pay_batch', { 'status' => 'O', 'payby' => 'CHEK' }) - or die "No open batches found.\n"; + or log_info_and_die( "Finished: No open batches found.\n" ); } else { my $batchnum = shift; die &HELP_MESSAGE if !$batchnum; @batches = qsearchs('pay_batch', { batchnum => $batchnum } ); - die "Can't find payment batch '$batchnum'\n" if !@batches; + log_error_and_die( "Can't find payment batch '$batchnum'\n" ) if !@batches; } my $conf = new FS::Conf; @@ -45,10 +49,10 @@ foreach my $pay_batch (@batches) { my $batchnum = $pay_batch->batchnum; my $filename = time2str('%Y%m%d', time) . '-' . sprintf('%06d.csv',$batchnum); - print STDERR "Exporting batch $batchnum to $filename...\n" if $opt_v; + log_info( "Exporting batch $batchnum to $filename...\n" ); my $text = $pay_batch->export_batch(format => 'eft_canada'); unless ($text) { - print STDERR "Batch is empty, resolving..." if $opt_v; + log_info( "Batch is empty, resolving..." ); next; } open OUT, ">$tmpdir/$filename"; @@ -56,22 +60,24 @@ foreach my $pay_batch (@batches) { close OUT; my @batchconf = $conf->config('batchconfig-eft_canada', $pay_batch->agentnum); - my $user = $batchconf[0] or die "no EFT Canada batch username configured\n"; - my $pass = $batchconf[1] or die "no EFT Canada batch password configured\n"; + my $user = $batchconf[0] + or log_error_and_die( "no EFT Canada batch username configured\n" ); + my $pass = $batchconf[1] + or log_error_and_die( "no EFT Canada batch password configured\n" ); my $host = 'ftp.eftcanada.com'; - print STDERR "Connecting to $user\@$host...\n" if $opt_v; + log_info( "Connecting to $user\@$host...\n" ); my $sftp = Net::SFTP::Foreign->new( host => $host, user => $user, password => $pass, timeout => 30, ); - die "failed to connect to '$user\@$host'\n(".$sftp->error.")\n" + log_error_and_die("failed to connect to '$user\@$host'\n(".$sftp->error.")\n") if $sftp->error; $sftp->put("$tmpdir/$filename", "$filename") - or die "failed to upload file (".$sftp->error.")\n"; + or log_error_and_die( "failed to upload file (".$sftp->error.")\n" ); undef $sftp; #$sftp->disconnect; @@ -84,10 +90,29 @@ foreach my $pay_batch (@batches) { last if $error; } $error ||= $pay_batch->set_status('R'); - die "error closing batch $batchnum: $error\n\n" if $error; + log_error_and_die( "error closing batch $batchnum: $error\n\n" ) + if $error; +} + +log_info( "Finished!\n" ); + +sub log_info { + my $log_message = shift; + $log->info( $log_message ); + print STDERR $log_message if $opt_v; } -print STDERR "Finished!\n" if $opt_v; +sub log_info_and_die { + my $log_message = shift; + $log->info( $log_message ); + die $log_message; +} + +sub log_error_and_die { + my $log_message = shift; + $log->error( $log_message ); + die $log_message; +} =head1 NAME -- cgit v1.2.1 From 006f36a6d2ee10664c3207b47ff046de447ea8bb Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Tue, 11 Dec 2018 18:01:30 -0500 Subject: RT# 74693 - Added Bulk edit of rates only --- httemplate/browse/cust_main_county.cgi | 59 +++++++++++++++++++++- httemplate/edit/bulk-cust_main_county.html | 30 +++++++---- httemplate/edit/process/bulk-cust_main_county.html | 11 +++- 3 files changed, 87 insertions(+), 13 deletions(-) diff --git a/httemplate/browse/cust_main_county.cgi b/httemplate/browse/cust_main_county.cgi index 552327836..722c699a2 100755 --- a/httemplate/browse/cust_main_county.cgi +++ b/httemplate/browse/cust_main_county.cgi @@ -260,6 +260,21 @@ if ( $country && $state && } $cgi->delete('county'); +my $city = ''; +if ( $country && $state && $county && + $cgi->param('city') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]]+)$/ + ) +{ + $city = $1; + if ( $city eq '__NONE__' ) { + $title = "No city, $title"; + } else { + $title = "$city city, $title"; + } +} +$cgi->delete('city'); + $title = " for $title" if $title; my $taxclass = ''; @@ -279,12 +294,18 @@ my $filter_change = "window.location = '". $cgi->self_url. ";country=' + encodeURIComponent( document.getElementById('country').options[document.getElementById('country').selectedIndex].value ) + ". "';state=' + encodeURIComponent( document.getElementById('state').options[document.getElementById('state').selectedIndex].value ) +". - "';county=' + encodeURIComponent( document.getElementById('county').options[document.getElementById('county').selectedIndex].value );"; + "';county=' + encodeURIComponent( document.getElementById('county').options[document.getElementById('county').selectedIndex].value )"; + +$filter_change .= " +';city=' + encodeURIComponent( document.getElementById('city').options[document.getElementById('city').selectedIndex].value )" + if $conf->exists('enable_taxclasses'); + +$filter_change .= ";"; #restore this so pagination works $cgi->param('country', $country) if $country; $cgi->param('state', $state ) if $state; $cgi->param('county', $county ) if $county; +$cgi->param('city', $city ) if $city; $cgi->param('taxclass', $county ) if $taxclass; my $html_posttotal = @@ -338,6 +359,31 @@ if ( scalar(@counties) > 1 ) { ''; } +if ( $conf->exists('enable_taxclasses') ) { + my @cities = ( $country && $state && $county ) ? cities($county, $state, $country) : (); + if ( scalar(@cities) > 1 ) { + $html_posttotal .= + ' show city: '. + include('/elements/select-city.html', + 'country' => $country, + 'state' => $state, + 'county' => $county, + 'city' => $city, + 'onchange' => $filter_change, + 'empty_label' => '(all)', + 'empty_data_label' => '(none)', + 'empty_data_value' => '__NONE__', + 'disable_empty' => 0, + 'disable_cityupdate' => 1, + ); + } else { + $html_posttotal .= + ''; + } +} + $html_posttotal .= ' )'; my $bulk_popup_link = @@ -412,6 +458,8 @@ my $html_foot = <Bulk edit selected | +Bulk edit rate only selected +| bulk edit with excel file END @@ -434,6 +482,15 @@ if ( $county ) { $count_query .= ' AND county = '. dbh->quote($county); } } +if ( $city ) { + if ( $city eq '__NONE__' ) { + $hashref->{'city'} = ''; + $count_query .= " AND ( city = '' OR city IS NULL ) "; + } else { + $hashref->{'city'} = $city; + $count_query .= ' AND city = '. dbh->quote($city); + } +} if ( $taxclass ) { $hashref->{'taxclass'} = $taxclass; $count_query .= ( $count_query =~ /WHERE/i ? ' AND ' : ' WHERE ' ). diff --git a/httemplate/edit/bulk-cust_main_county.html b/httemplate/edit/bulk-cust_main_county.html index 8b1234825..650fa7857 100644 --- a/httemplate/edit/bulk-cust_main_county.html +++ b/httemplate/edit/bulk-cust_main_county.html @@ -3,6 +3,7 @@
" METHOD="POST"> + @@ -45,11 +46,13 @@ % } -<% include('/elements/tr-input-text.html', +% unless ($rate_only) { + <% include('/elements/tr-input-text.html', 'field' => 'taxname', 'label' => 'Tax name' ) -%> + %> +% } <% include('/elements/tr-input-percentage.html', 'field' => 'tax', @@ -57,27 +60,29 @@ ) %> -<% include('/elements/tablebreak-tr-title.html', value=>'Exemptions' ) %> +% unless ($rate_only) { + <% include('/elements/tablebreak-tr-title.html', value=>'Exemptions' ) %> -<% include('/elements/tr-checkbox.html', + <% include('/elements/tr-checkbox.html', 'field' => 'setuptax', 'value' => 'Y', 'label' => 'This tax not applicable to setup fees', ) -%> + %> -<% include('/elements/tr-checkbox.html', + <% include('/elements/tr-checkbox.html', 'field' => 'recurtax', 'value' => 'Y', 'label' => 'This tax not applicable to recurring fees', ) -%> + %> -<% include('/elements/tr-input-money.html', + <% include('/elements/tr-input-money.html', 'field' => 'exempt_amount', 'label' => 'Monthly exemption per customer ($25 "Texas tax")', ) -%> + %> +% }
@@ -97,8 +102,13 @@ $cgi->param('taxnum') =~ /^([\d,]+)$/ or $m->comp('/elements/errorpage-popup.html', $cgi->param('error') || 'Nothing selected'); my @taxnum = split(',', $1); -$cgi->param('action') =~ /^(add|edit)$/ or die "unknown action"; +$cgi->param('action') =~ /^(add|edit|edit_rate_only)$/ or die "unknown action"; my $action = $1; +my $rate_only; +if ($action eq "edit_rate_only") { + $action = "edit"; + $rate_only = 1; +} my $title = "Bulk $action tax rate"; my @cust_main_county = diff --git a/httemplate/edit/process/bulk-cust_main_county.html b/httemplate/edit/process/bulk-cust_main_county.html index b5a0258b1..55832e9c5 100644 --- a/httemplate/edit/process/bulk-cust_main_county.html +++ b/httemplate/edit/process/bulk-cust_main_county.html @@ -27,6 +27,8 @@ my @taxnum = split(',', $1); $cgi->param('action') =~ /^(add|edit)$/ or die "unknown action"; my $action = $1; +my $rate_only = $cgi->param('rate_only') if $cgi->param('rate_only'); + my $error = ''; foreach my $taxnum ( @taxnum ) { @@ -35,8 +37,13 @@ foreach my $taxnum ( @taxnum ) { if ( $action eq 'edit' || $cust_main_county->tax == 0 ) { #let's replace - foreach (qw( taxname tax exempt_amount setuptax recurtax )) { - $cust_main_county->set( $_ => scalar($cgi->param($_)) ) + if ($rate_only) { + $cust_main_county->set( tax => scalar($cgi->param('tax')) ); + } + else { + foreach (qw( taxname tax exempt_amount setuptax recurtax )) { + $cust_main_county->set( $_ => scalar($cgi->param($_)) ) + } } $error = $cust_main_county->replace and last; -- cgit v1.2.1 From 639c645c0f00c082a735b40f97a4f830c5e84949 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Tue, 11 Dec 2018 18:08:18 -0500 Subject: Revert "RT# 74693 - Added ability to bulk edit rates with excel" This reverts commit 1d9fd3b93be720823656cd23db79ff74e2e7a829. --- FS/FS/cust_main_county.pm | 298 +--------------------------- httemplate/browse/cust_main_county.cgi | 2 - httemplate/misc/process/tax_edit_excel.html | 9 - httemplate/misc/tax_edit_excel.html | 70 ------- 4 files changed, 1 insertion(+), 378 deletions(-) delete mode 100644 httemplate/misc/process/tax_edit_excel.html delete 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 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 -- cgit v1.2.1 From 88678677483b169d035d623cc1a07606dff6b046 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Tue, 11 Dec 2018 18:45:36 -0500 Subject: RT# 74693 - Added city select when using tax classes --- httemplate/elements/select-city.html | 176 +++++++++++++++++++++++++++++++++++ 1 file changed, 176 insertions(+) create mode 100644 httemplate/elements/select-city.html diff --git a/httemplate/elements/select-city.html b/httemplate/elements/select-city.html new file mode 100644 index 000000000..09e28dd48 --- /dev/null +++ b/httemplate/elements/select-city.html @@ -0,0 +1,176 @@ +<%doc> + +Example: + + <& /elements/select-city.html, + #recommended + country => $current_country, + state => $current_state, + county => $current_county, + city => $current_city, + + #optional + prefix => $optional_unique_prefix, + onchange => $javascript, + disabled => 0, #bool + disable_empty => 1, #defaults to 1, set to 0 to disable the empty option + empty_label => 'all', #label for empty option + style => [ 'attribute:value', 'another:value' ], + &> + + +% if ( $cityflag ) { + + <% include('/elements/xmlhttp.html', + 'url' => $p.'misc/cities.cgi', + 'subs' => [ $pre. 'get_cities' ], + ) + %> + + + + + +% } else { + + + + + +% } + +<%init> + +my %opt = @_; +foreach my $opt (qw( city county state country prefix onchange disabled + empty_value )) { + $opt{$opt} = '' unless exists($opt{$opt}) && defined($opt{$opt}); +} + +$opt{'disable_empty'} = 1 unless exists($opt{'disable_empty'}); + +my $pre = $opt{'prefix'}; + +my $onchange = $opt{'onchange'}; + +my $city_style = $opt{'style'} ? [ @{ $opt{'style'} } ] : []; + +my @cities = (); +if ( $cityflag ) { + + @cities = map { length($_) ? $_ : $opt{'empty_data_value'} } + cities( $opt{'county'}, $opt{'state'}, $opt{'country'} ); + + push @$city_style, 'display:none' + unless scalar(@cities) > 1; + +} + +my $style = + scalar(@$city_style) + ? 'STYLE="'. join(';', @$city_style). '"' + : ''; + + +<%once> + +my $sql = "SELECT COUNT(*) FROM cust_main_county". + " WHERE city IS NOT NULL AND city != ''"; +my $sth = dbh->prepare($sql) or die dbh->errstr; +$sth->execute or die $sth->errstr; +my $cityflag = $sth->fetchrow_arrayref->[0]; + + \ No newline at end of file -- cgit v1.2.1 From f743de125f5daf7a7243aa4f2c72e5a87a770456 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Tue, 18 Dec 2018 03:20:20 -0500 Subject: RT# 80488 Utility to populate WA tax district table --- bin/wa_tax_rate_update | 425 ++++++++++++++++++++++++++++++++++++++----------- 1 file changed, 331 insertions(+), 94 deletions(-) diff --git a/bin/wa_tax_rate_update b/bin/wa_tax_rate_update index d4a4b52e4..c50a77771 100644 --- a/bin/wa_tax_rate_update +++ b/bin/wa_tax_rate_update @@ -9,118 +9,355 @@ wa_tax_rate_update Tool to update city/district sales tax rates in I from the Washington State Department of Revenue website. -This does not handle address standardization or geocoding addresses to -Washington tax district codes. That logic is still in FS::Misc::Geo, -and relies on a heinous screen-scraping of the interactive search tool. -This script just updates the cust_main_county records that already exist -with the latest quarterly tax rates. +Creates, or updates, a L row for every tax district +in Washington state. Some cities have different tax rates based on the +address, within the city. Because of this, some cities may have multiple +rows defined. + +When a Washington state address is inserted or changed in L, +a job is queued for FS::geocode_Mixin::process_district_update, to ask the +Washington state API which tax district to use for this address. Options: --c : operate only on records with the named tax class. If not -specified, this operates on records with null tax class. +-f : Skip downloading, and process the given excel file + +-c : Updated or create records within given tax class, + If not specified, taxclass will be set as NULL + +-t : Updated or created records will be set to the given tax name. + If not specified, conf value 'tax_district_taxname' will be used + +-y : Specify year for tax table - defaults to current year + +-q : Specify quarter for tax table - defaults to current quarter + +=head1 Washington State Department of Revenue Resources + +The state of Washington makes data files available via their public website. +It's possible the availability or format of these files may change. As of now, +the only data file that contains both city and county names is published in +XLSX format. + +=item WA Dept of Revenue + +https://dor.wa.gov + +=item Data file downloads + +https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database + +=item XLSX file example --t : operate only on records with that tax name. If not specified, -it operates on records where the tax name is either null or 'Tax'. +https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx + +=item CSV file example + +https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip + + +=item Address lookup API tool + +http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100 =cut -use FS::Record qw(qsearch qsearchs dbh); -use FS::cust_main_county; -use FS::UID qw(adminsuidsetup); +use strict; +use warnings; + +our $VERSION = '0.02'; # Make Getopt:Std happy + +use Data::Dumper; use DateTime; -use LWP::UserAgent; use File::Temp 'tempdir'; use File::Slurp qw(read_file write_file); -use Text::CSV; use Getopt::Std; +use LWP::UserAgent; +use Spreadsheet::XLSX; -getopts('c:t:'); -my $user = shift or die usage(); - -# download the update file -my $now = DateTime->now; -my $yr = $now->year; -my $qt = $now->quarter; -my $file = "Rates${yr}Q${qt}.zip"; -my $url = 'http://dor.wa.gov/downloads/Add_Data/'.$file; -my $dir = tempdir(); -chdir($dir); -my $ua = LWP::UserAgent->new; -warn "Downloading $url...\n"; -my $response = $ua->get($url); -if ( ! $response->is_success ) { - die $response->status_line; -} -write_file($file, $response->decoded_content); +use FS::cust_main_county; +use FS::Log; +use FS::Record qw(qsearch qsearchs dbh); +use FS::UID qw(adminsuidsetup); + +my %opts; +getopts( 'c:t:y:q:f:', \%opts ); +my $user = shift + or die HELP_MESSAGE(); + +adminsuidsetup( $user ) + or die "bad username '$user'\n"; + +my $temp_dir = tempdir(); +my $log = FS::Log->new('wa_tax_rate_update'); + +$opts{t} ||= FS::Conf->new->config('tax_district_taxname'); + +log_info_and_warn( "Set taxclass=$opts{c}" ) if $opts{c}; +log_info_and_warn( "Set taxname=$opts{t}" ) if $opts{t}; + +my $xls_fn = $opts{f} || fetch_xlsx_file(); +my $tax_districts = parse_xlsx_file( $xls_fn ); + +update_cust_main_county( $tax_districts ); + +# print Dumper( \%opts ); +# for ( 0..5 ) { +# print Dumper( $tax_districts->[$_] ); +# } + +log_info_and_warn( "Finished" ); +exit; + + +sub update_cust_main_county { + my $tax_districts = shift; + + # + # Working from an assumption tax tables may be loaded multiple times, + # each with a different tax class, + # re: comments on FS::geocode_Mixin::process_district_update + # + + my %cust_main_county = + map { $_->district => $_ } + qsearch( + cust_main_county => { + district => { op => '!=', value => undef }, + state => 'WA', + source => 'wa_sales', + taxclass => $opts{c}, + } + ); + + # Work within a SQL transaction + local $FS::UID::AutoCommit = 0; + + my $insert_count = 0; + my $update_count = 0; + + for my $district ( @$tax_districts ) { + if ( my $row = $cust_main_county{ $district->{district} } ) { + + $row->city( uc $district->{city} ); + $row->county( uc $district->{county} ); + $row->taxclass( $opts{c} || undef ); + $row->taxname( $opts{t} || undef ); + $row->tax( $district->{tax_combined} * 100 ); + + if ( my $error = $row->replace ) { + dbh->rollback; + local $FS::UID::AutoCommit = 1; + log_error_and_die( + sprintf + "Error updating cust_main_county row %s for district %s: %s", + $row->taxnum, + $district->{district}, + $error + ); + } + + $update_count++; + + } else { + my $row = FS::cust_main_county->new({ + district => $district->{district}, + city => uc $district->{city}, + county => uc $district->{county}, + state => 'WA', + country => 'US', + taxclass => $opts{c} || undef, + taxname => $opts{t} || undef, + tax => $district->{tax_combined} * 100, + source => 'wa_sales', + }); + + if ( my $error = $row->insert ) { + dbh->rollback; + local $FS::UID::AutoCommit = 1; + log_error_and_die( + sprintf + "Error inserting cust_main_county row for district %s: %s", + $district->{district}, + $error + ); + } + + $cust_main_county{ $district->{district} } = $row; + $insert_count++; + } + } + + dbh->commit; + + local $FS::UID::AutoCommit = 1; + log_info_and_warn( + sprintf + "WA tax table update completed. Inserted %s rows, updated %s rows", + $insert_count, + $update_count + ); -# parse it -system('unzip', $file); -$file =~ s/\.zip$/.csv/; -if (! -f $file) { - die "$file not found in zip archive.\n"; } -open my $fh, '<', $file - or die "couldn't open $file: $!\n"; -my $csv = Text::CSV->new; -my $header = $csv->getline($fh); -$csv->column_names(@$header); -# columns we care about are headed 'Code' and 'Rate' - -# connect to the DB -adminsuidsetup($user) or die "bad username '$user'\n"; -$FS::UID::AutoCommit = 0; - -$opt_c ||= ''; # taxclass -$opt_t ||= ''; # taxname -my $total_changed = 0; -my $total_skipped = 0; -while ( !$csv->eof ) { - my $line = $csv->getline_hr($fh); - my $district = $line->{Code} or next; - $district = sprintf('%04d', $district); - my $tax = sprintf('%.1f', $line->{Rate} * 100); - my $changed = 0; - my $skipped = 0; - # find all rates in WA - my @rates = qsearch('cust_main_county', { - country => 'US', - state => 'WA', # this is specific to WA - district => $district, - taxclass => $opt_c, - taxname => $opt_t, - tax => { op => '>', value => '0' }, - }); - if ($opt_t eq '') { - push @rates, qsearch('cust_main_county', { - country => 'US', - state => 'WA', # this is specific to WA - district => $district, - taxclass => $opt_c, - taxname => 'Tax', - tax => { op => '>', value => '0' }, - }); + +sub parse_xlsx_file { + my $parse_fn = shift; + + # About the file format: + # + # The current spreadsheet contains the following @columns. + # Rows 1 and 2 are a marquee header + # Row 3 is the column labels. We will test these to detect + # changes in the data format + # Rows 4+ are the tax district data + # + # The "city" column is being parsed from "Location" + + my @columns = qw( city county district tax_local tax_state tax_combined ); + + log_error_and_die( "Unable to access XLSX file: $parse_fn" ) + unless -r $parse_fn; + + my $xls_parser = Spreadsheet::XLSX->new( $parse_fn ) + or log_error_and_die( "Error parsing XLSX file: $!" ); + + my $sheet = $xls_parser->{Worksheet}->[0] + or log_error_and_die(" Unable to access worksheet 1 in XLSX file" ); + + my $cells = $sheet->{Cells} + or log_error_and_die( "Unable to read cells in XLSX file" ); + + # Read the column labels and verify + my %labels = + map{ $columns[$_] => $cells->[2][$_]->{Val} } + 0 .. scalar(@columns)-1; + + my %expected_labels = ( + city => 'Location', + county => 'County', + district => 'Location Code', + tax_local => 'Local Rate', + tax_state => 'State Rate', + tax_combined => 'Combined Sales Tax', + ); + + if ( + my @error_labels = + grep { lc $labels{$_} ne lc $expected_labels{$_} } + @columns + ) { + my $error = "Error parsing XLS file - ". + "Data format may have been updated with WA DOR! "; + $error .= "Expected column $expected_labels{$_}, found $labels{$_}! " + for @error_labels; + log_error_and_die( $error ); } - foreach my $rate (@rates) { - if ( $rate->tax == $tax ) { - $skipped++; + + # Parse the rows into an array of hashes + my @districts; + for my $row ( 3..$sheet->{MaxRow} ) { + my %district = ( + map { $columns[$_] => $cells->[$row][$_]->{Val} } + 0 .. scalar(@columns)-1 + ); + + if ( + $district{city} + && $district{county} + && $district{district} =~ /^\d+$/ + && $district{tax_local} =~ /^\d?\.\d+$/ + && $district{tax_state} =~ /^\d?\.\d+$/ + && $district{tax_combined} =~ /^\d?\.\d+$/ + ) { + + # For some reason, city may contain line breaks! + $district{city} =~ s/[\r\n]//g; + + push @districts, \%district; } else { - $rate->set('tax', $tax); - my $error = $rate->replace; - die "error updating district $district: $error\n" if $error; - $changed++; + log_warn_and_warn( + "Non-usable row found in spreadsheet:\n" . Dumper( \%district ) + ); } + } - print "$district: updated $changed, skipped $skipped\n" - if $changed or $skipped; - $total_changed += $changed; - $total_skipped += $skipped; + + log_error_and_die( "No \@districts found in data file!" ) + unless @districts; + + log_info_and_warn( + sprintf "Parsed %s districts from data file", scalar @districts + ); + + \@districts; +} + +sub fetch_xlsx_file { + # Download file to temporary storage, return filename + + my $url_base = 'https://dor.wa.gov'. + '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx'; + + my $year = $opts{y} || DateTime->now->year; + my $quarter = $opts{q} || DateTime->now->quarter; + $year = substr( $year, 2, 2 ) if $year >= 1000; + my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter ); + my $url = "$url_base/$fn"; + + my $write_fn = "$temp_dir/$fn"; + + log_info_and_warn( "Begin download from url: $url" ); + + my $ua = LWP::UserAgent->new; + my $res = $ua->get( $url ); + + log_error_and_die( "Download error: ".$res->status_line ) + unless $res->is_success; + + local $@; + eval { write_file( $write_fn, $res->decoded_content ); }; + log_error_and_die( "Problem writing download to disk: $@" ) + if $@; + + log_info_and_warn( "Temporary file: $write_fn" ); + $write_fn; +} + + +sub HELP_MESSAGE { + print " + Tool to update city/district sales tax rates in I from + the Washington State Department of Revenue website. + + Usage: [-f filename] [-c taxclass] [-t taxname] [-y year] [-q quarter] + + Optional Options: + -f filename Skip download, and process the specified filename + -c taxclass Update or create records within this taxclass + defaults as NULL + -t taxname Apply tax name value to created or updated records + defaults as conf value 'tax_district_taxname' + -y year Year for data file download + -q quarter Quarter of data file to download + + "; + exit; +} + +sub log_info_and_warn { + my $log_message = shift; + warn "$log_message\n"; + $log->info( $log_message ); +} + +sub log_warn_and_warn { + my $log_message = shift; + warn "$log_message\n"; + $log->warn( $log_message ); } -print "Updated $total_changed tax rates.\nSkipped $total_skipped unchanged rates.\n"; -dbh->commit; -sub usage { - "usage: - wa_tax_rate_update [ -c taxclass ] [ -t taxname ] user -"; +sub log_error_and_die { + my $log_message = shift; + $log->error( $log_message ); + die( "$log_message\n" ); } -- cgit v1.2.1 From 4ee944740d805eb46be09f45a213f0c279c56d8c Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Tue, 18 Dec 2018 17:45:18 -0500 Subject: RT# 80488 Find tax districts for WA addresses using official API --- FS/FS/Misc/Geo.pm | 222 +++++++++++++++++++++++++++++++++--------------------- 1 file changed, 138 insertions(+), 84 deletions(-) diff --git a/FS/FS/Misc/Geo.pm b/FS/FS/Misc/Geo.pm index 6b3d6ca71..96ce0764b 100644 --- a/FS/FS/Misc/Geo.pm +++ b/FS/FS/Misc/Geo.pm @@ -14,6 +14,7 @@ use Data::Dumper; use FS::Conf; use FS::Log; use Locale::Country; +use XML::LibXML; FS::UID->install_callback( sub { $conf = new FS::Conf; @@ -141,102 +142,155 @@ sub get_district { &$method($location); } -sub wa_sales { - my $location = shift; - my $error = ''; - return '' if $location->{state} ne 'WA'; - my $return = { %$location }; - $return->{'exempt_amount'} = 0.00; +=head2 wa_sales location_hash - my $url = 'http://webgis2.dor.wa.gov/TaxRateLookup_AGS/TaxReport.aspx'; - my $ua = new LWP::UserAgent; +Expects output of location_hash() as parameter + +Dies on error, or if tax rate cannot be found using given address + +Query the WA State Dept of Revenue API with an address, and return +tax district information for that address. + +Documentation for the API can be found here: + +L + +This API does not return consistent usable county names, as the county +name may include appreviations or labels referring to PTBA (public transport +benefit area) or CEZ (community empowerment zone). It's recommended to use +the tool wa_tax_rate_update to fully populate the city/county/districts for +WA state every financial quarter. + +Returns a hashref with the following keys: - my $delim = '<|>'; # yes, <|> - my $year = (localtime)[5] + 1900; - my $month = (localtime)[4] + 1; - my @zip = split('-', $location->{zip}); - - my @args = ( - 'TaxType=S', #sales; 'P' = property - 'Src=0', #does something complicated - 'TAXABLE=', - 'Addr='.uri_escape($location->{address1}), - 'City='.uri_escape($location->{city}), - 'Zip='.$zip[0], - 'Zip1='.($zip[1] || ''), #optional - 'Year='.$year, - 'SYear='.$year, - 'Month='.$month, - 'EMon='.$month, + - district the wa state tax district id + - tax the combined total tax rate, as a percentage + - city the API rate name + - county The API address PTBA + - state WA + - country US + - exempt_amount 0 + +=cut + +sub wa_sales { + my $location_hash = shift; + + # Return without die() when called with pointless context + return + unless $location_hash + && ref $location_hash + && $location_hash->{state} eq 'WA' + && $location_hash->{address1} + && $location_hash->{zip} + && $location_hash->{city}; + + my $log = FS::Log->new('wa_sales'); + + warn "wa_sales() called with location_hash:\n".Dumper( $location_hash)."\n" + if $DEBUG; + + my $api_url = 'http://webgis.dor.wa.gov/webapi/AddressRates.aspx'; + my @api_response_codes = ( + 'The address was found', + 'The address was not found, but the ZIP+4 was located.', + 'The address was updated and found, the user should validate the address record', + 'The address was updated and Zip+4 located, the user should validate the address record', + 'The address was corrected and found, the user should validate the address record', + 'Neither the address or ZIP+4 was found, but the 5-digit ZIP was located.', + 'The address, ZIP+4, and ZIP could not be found.', + 'Invalid Latitude/Longitude', + 'Internal error' ); - - my $query_string = join($delim, @args ); - $url .= "?$query_string"; - warn "\nrequest: $url\n\n" if $DEBUG > 1; - my $res = $ua->request( GET( "$url?$query_string" ) ); + my %get_query = ( + output => 'xml', + addr => $location_hash->{address1}, + city => $location_hash->{city}, + zip => substr( $location_hash->{zip}, 0, 5 ), + ); + my $get_string = join '&' => ( + map{ sprintf "%s=%s", $_, uri_escape( $get_query{$_} ) } + keys %get_query + ); - warn $res->as_string - if $DEBUG > 2; + my $prepared_url = "${api_url}?$get_string"; - if ($res->code ne '200') { - $error = $res->message; - } + warn "API call to URL: $prepared_url\n" + if $DEBUG; - my $content = $res->content; - my $p = new HTML::TokeParser \$content; - my $js = ''; - while ( my $t = $p->get_tag('script') ) { - my $u = $p->get_token; #either enclosed text or the tag - if ( $u->[0] eq 'T' and $u->[1] =~ /tblSales/ ) { - $js = $u->[1]; - last; - } + my $dom; + local $@; + eval { $dom = XML::LibXML->load_xml( location => $prepared_url ); }; + if ( $@ ) { + my $error = + sprintf "Problem parsing XML from API URL(%s): %s", + $prepared_url, $@; + $log->error( $error ); + die $error; } - if ( $js ) { #found it - # strip down to the quoted string, which contains escaped single quotes. - $js =~ s/.*\('tblSales'\);c.innerHTML='//s; - $js =~ s/(? 2; - - $p = new HTML::TokeParser \$js; - TD: while ( my $td = $p->get_tag('td') ) { - while ( my $u = $p->get_token ) { - next TD if $u->[0] eq 'E' and $u->[1] eq 'td'; - next if $u->[0] ne 'T'; # skip non-text - my $text = $u->[1]; - - if ( lc($text) eq 'location code' ) { - $p->get_tag('td'); # skip to the next column - undef $u; - $u = $p->get_token until ($u->[0] || '') eq 'T'; # and then skip non-text - $return->{'district'} = $u->[1]; - } - elsif ( lc($text) eq 'total tax rate' ) { - $p->get_tag('td'); - undef $u; - $u = $p->get_token until ($u->[0] || '') eq 'T'; - $return->{'tax'} = $u->[1]; - } - } # get_token - } # TD - - # just to make sure - if ( $return->{'district'} =~ /^\d+$/ and $return->{'tax'} =~ /^.\d+$/ ) { - $return->{'tax'} *= 100; #percentage - warn Dumper($return) if $DEBUG > 1; - return $return; - } - else { - $error = 'district code/tax rate not found'; - } + + my ($res_root) = $dom->findnodes('/response'); + my ($res_addressline) = $dom->findnodes('/response/addressline'); + my ($res_rate) = $dom->findnodes('/response/rate'); + + my $res_code = $res_root->getAttribute('code') + if $res_root; + + unless ( + ref $res_root + && ref $res_addressline + && ref $res_rate + && $res_code <= 5 + && $res_root->getAttribute('rate') > 0 + ) { + my $error = + sprintf + "Problem querying WA DOR tax district - " . + "code( %s %s ) " . + "address( %s ) " . + "url( %s )", + $res_code || 'n/a', + $res_code ? $api_response_codes[$res_code] : 'n/a', + $location_hash->{address1}, + $prepared_url; + $log->error( $error ); + die "$error\n"; } - else { - $error = "failed to parse document"; + + my %response = ( + exempt_amount => 0, + state => 'WA', + country => 'US', + district => $res_root->getAttribute('loccode'), + tax => $res_root->getAttribute('rate') * 100, + county => uc $res_addressline->getAttribute('ptba'), + city => uc $res_rate->getAttribute('name') + ); + + $response{county} =~ s/ PTBA//i; + + if ( $DEBUG ) { + warn "XML document: $dom\n"; + warn "API parsed response: ".Dumper( \%response )."\n"; } - die "WA tax district lookup error: $error"; + my $info_message = + sprintf + "Tax district(%s) selected for address(%s %s %s %s)", + $response{district}, + $location_hash->{address1}, + $location_hash->{city}, + $location_hash->{state}, + $location_hash->{zip}; + + $log->info( $info_message ); + warn "$info_message\n" + if $DEBUG; + + \%response; + } ###### USPS Standardization ###### -- cgit v1.2.1 From 4d9e2d0980eb542aec39997b7bd92130b3a60676 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Tue, 18 Dec 2018 17:47:57 -0500 Subject: RT# 80488 Allow city select for tax_district_method=wa_sales --- httemplate/elements/city.html | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) diff --git a/httemplate/elements/city.html b/httemplate/elements/city.html index 05250fef5..3c5e91782 100644 --- a/httemplate/elements/city.html +++ b/httemplate/elements/city.html @@ -153,9 +153,7 @@ my %opt = @_; my $pre = $opt{'prefix'}; my $conf = new FS::Conf; -# Using tax_district_method implies that there's not a preloaded city/county -# tax district table. -my $disable_select = 1 if $conf->config('tax_district_method'); +my $disable_select = 0; $opt{'disable_empty'} = 1 unless exists($opt{'disable_empty'}); -- cgit v1.2.1 From c213fdbabc178985a93996aac33a907465b31007 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Fri, 21 Dec 2018 14:06:15 -0500 Subject: RT# 81596 - fixed freeside-upgrade to not drop custom fields --- FS/bin/freeside-upgrade | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/FS/bin/freeside-upgrade b/FS/bin/freeside-upgrade index c5df06dc3..0df388411 100755 --- a/FS/bin/freeside-upgrade +++ b/FS/bin/freeside-upgrade @@ -120,7 +120,7 @@ while ( $cf = $cfsth->fetchrow_hashref ) { my $name = $cf->{'name'}; $name = lc($name) unless driver_name =~ /^mysql/i; - @statements = grep { $_ !~ /^\s*ALTER\s+TABLE\s+(h_|)$tbl\s+DROP\s+COLUMN\s+cf_$name\s*$/i } + @statements = grep { $_ !~ /^\s*ALTER\s+TABLE\s+(h_|)$tbl DROP\s+COLUMN\s+cf_$name/i } @statements; push @statements, "ALTER TABLE $tbl ADD COLUMN cf_$name varchar(".$cf->{'length'}.")" -- cgit v1.2.1 From 83a6052bc16ed5cff28e32613f20dc4b1156bac6 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sat, 22 Dec 2018 03:27:22 -0500 Subject: RT# 80488 WA tax tables maintained with Cron * Some re-work for WA tax tables when using tax classes * Relocated functions from wa_tax_rate_update utility script into FS::Cron::wa_tax_rate_update library * Cron downloads tax database monthly instead of daily * Cron populates entire WA tax table, instead of piecemeal for existing customer locations * Cron will attempt to classify cust_location in WA without a determined district, and generate system log errors upon failure --- FS/FS/Cron/tax_rate_update.pm | 653 ++++++++++++++++++++++++++++++++++++------ bin/wa_tax_rate_update | 314 ++++---------------- 2 files changed, 619 insertions(+), 348 deletions(-) diff --git a/FS/FS/Cron/tax_rate_update.pm b/FS/FS/Cron/tax_rate_update.pm index fec696fbb..b0745e409 100755 --- a/FS/FS/Cron/tax_rate_update.pm +++ b/FS/FS/Cron/tax_rate_update.pm @@ -9,106 +9,593 @@ FS::Cron::tax_rate_update Cron routine to update city/district sales tax rates in I. Currently supports sales tax in the state of Washington. +=head2 wa_sales + +=item Tax Rate Download + +Once each month, update the tax tables from the WA DOR website. + +=item Customer Address Rate Classification + +Find cust_location rows in WA with no tax district. Try to determine +a tax district. Otherwise, generate a log error that address needs +to be correctd. + =cut use strict; use warnings; -use FS::Conf; -use FS::Record qw(qsearch qsearchs dbh); -use FS::cust_main_county; -use FS::part_pkg_taxclass; +use feature 'state'; + +use Exporter; +our @EXPORT_OK = qw( + tax_rate_update + wa_sales_update_tax_table + wa_sales_log_customer_without_tax_district +); + +use Carp qw(croak); use DateTime; -use LWP::UserAgent; use File::Temp 'tempdir'; use File::Slurp qw(read_file write_file); +use LWP::UserAgent; +use Spreadsheet::XLSX; use Text::CSV; -use Exporter; -our @EXPORT_OK = qw(tax_rate_update); +use FS::Conf; +use FS::cust_main; +use FS::cust_main_county; +use FS::geocode_Mixin; +use FS::Log; +use FS::part_pkg_taxclass; +use FS::Record qw(qsearch qsearchs dbh); +use FS::upgrade_journal; + our $DEBUG = 0; +=head1 FUNCTIONS + +=head2 tax_rate_update + +Cron routine for freeside_daily. + +Run one of the available cron functions based on conf value tax_district_method + +=cut + sub tax_rate_update { - my %opt = @_; - my $oldAutoCommit = $FS::UID::AutoCommit; - local $FS::UID::AutoCommit = 0; - my $dbh = dbh; - - my $conf = FS::Conf->new; - my $method = $conf->config('tax_district_method'); - return if !$method; - - my $taxname = $conf->config('tax_district_taxname') || ''; - - FS::cust_main_county->lock_table; - if ($method eq 'wa_sales') { - # download the update file - my $now = DateTime->now; - my $yr = $now->year; - my $qt = $now->quarter; - my $file = "Rates${yr}Q${qt}.zip"; - my $url = 'http://dor.wa.gov/downloads/Add_Data/'.$file; - my $dir = tempdir(); - chdir($dir); - my $ua = LWP::UserAgent->new; - warn "Downloading $url...\n" if $DEBUG; - my $response = $ua->get($url); - if ( ! $response->is_success ) { - die $response->status_line; - } - write_file($file, $response->decoded_content); + # Currently only wa_sales is supported + my $tax_district_method = conf_tax_district_method(); + + return unless $tax_district_method; + + if ( exists &{$tax_district_method} ) { + my $func = \&{$tax_district_method}; + $func->(); + } else { + my $log = FS::Log->new('tax_rate_update'); + $log->error( "Unhandled tax_district_method($tax_district_method)" ); + } + +} + +=head2 wa_sales + +Monthly: Update the complete WA state tax tables +Every Run: Log errors for cust_location records without a district + +=cut + +sub wa_sales { + + return + unless conf_tax_district_method() + && conf_tax_district_method() eq 'wa_sales'; + + my $dt_now = DateTime->now; + my $year = $dt_now->year; + my $quarter = $dt_now->quarter; + + my $journal_label = + sprintf 'wa_sales_update_tax_table_%sQ%s', $year, $quarter; + + unless ( FS::upgrade_journal->is_done( $journal_label ) ) { + local $@; + + eval{ wa_sales_update_tax_table(); }; + log_error_and_die( "Error updating tax tables: $@" ) + if $@; + FS::upgrade_journal->set_done( $journal_label ); + } + + wa_sales_log_customer_without_tax_district(); + + ''; + +} - # parse it - system('unzip', $file); - $file =~ s/\.zip$/.csv/; - if (! -f $file) { - die "$file not found in zip archive.\n"; +=head2 wa_sales_log_customer_without_tax_district + +For any active customers with cust_location records in WA state, +if a cust_location record has no tax district, find the correct +district using WA DOR API, or if not possible, generate an error +message into system log so address can be corrected + +=cut + +sub wa_sales_log_customer_without_tax_district { + + return + unless conf_tax_district_method() + && conf_tax_district_method() eq 'wa_sales'; + + my %qsearch_cust_location = ( + table => 'cust_location', + select => ' + cust_location.locationnum, + cust_location.custnum, + cust_location.address1, + cust_location.city, + cust_location.state, + cust_location.zip + ', + hashref => { + state => 'WA', + district => undef, + }, + addl_from => 'LEFT JOIN cust_main USING (custnum)', + extra_sql => sprintf 'AND ( %s ) ', FS::cust_main->active_sql, + ); + + for my $cust_location ( qsearch( \%qsearch_cust_location )) { + local $@; + eval { + FS::geocode_Mixin::process_district_update( + 'FS::cust_location', + $cust_location->locationnum + ); + }; + + if ( $@ ) { + log_error_and_warn( + sprintf "Failed to classify district for cust_location(%s): %s", + $cust_location->locationnum, + $@ + ); + } else { + log_info_and_warn( + sprintf "Classified district for cust_location(%s)", + $cust_location->locationnum + ); } - open my $fh, '<', $file - or die "couldn't open $file: $!\n"; - my $csv = Text::CSV->new; - my $header = $csv->getline($fh); - $csv->column_names(@$header); - # columns we care about are headed 'Code' and 'Rate' - - my $total_changed = 0; - my $total_skipped = 0; - while ( !$csv->eof ) { - my $line = $csv->getline_hr($fh); - my $district = $line->{Code} or next; - $district = sprintf('%04d', $district); - my $tax = sprintf('%.1f', $line->{Rate} * 100); - my $changed = 0; - my $skipped = 0; - # find rate(s) in this country+state+district+taxclass that have the - # wa_sales flag and the configured taxname, and haven't been disabled. - my @rates = qsearch('cust_main_county', { - country => 'US', - state => 'WA', # this is specific to WA - district => $district, - taxname => $taxname, - source => 'wa_sales', - tax => { op => '>', value => '0' }, - }); - foreach my $rate (@rates) { - if ( $rate->tax == $tax ) { - $skipped++; - } else { - $rate->set('tax', $tax); - my $error = $rate->replace; - die "error updating district $district: $error\n" if $error; - $changed++; + + sleep 1; # Be polite to WA DOR API + } + + for my $cust_location ( qsearch( \%qsearch_cust_location )) { + log_error_and_warn( + sprintf + "Customer address in WA lacking tax district classification. ". + "custnum(%s) ". + "locationnum(%s) ". + "address(%s, %s %s, %s) ". + "[https://webgis.dor.wa.gov/taxratelookup/SalesTax.aspx]", + map { $cust_location->$_ } + qw( custnum locationnum address1 city state zip ) + ); + } + +} + + +=head2 wa_sales_update_tax_table \%args + +Update city/district sales tax rates in L from the +Washington State Department of Revenue published data files. + +Creates, or updates, a L row for every tax district +in Washington state. Some cities have different tax rates based on the +address, within the city. Because of this, some cities have multiple +districts. + +If tax classes are enabled, a row is created in every tax class for +every district. + +Customer addresses aren't classified into districts here. Instead, +when a Washington state address is inserted or changed in L, +a job is queued for FS::geocode_Mixin::process_district_update, to ask the +Washington state API which tax district to use for this address. + +All arguments are optional: + + filename: Skip file download, and process the specified filename instead + + taxname: Updated or created records will be set to the given tax name. + If not specified, conf value 'tax_district_taxname' is used + + year: Specify year for tax table download. Defaults to current year + + quarter: Specify quarter for tax table download. Defaults to current quarter + +=head3 Washington State Department of Revenue Resources + +The state of Washington makes data files available via their public website. +It's possible the availability or format of these files may change. As of now, +the only data file that contains both city and county names is published in +XLSX format. + +=over 4 + +=item WA Dept of Revenue + +https://dor.wa.gov + +=item Data file downloads + +https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database + +=item XLSX file example + +https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx + +=item CSV file example + +https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip + + +=item Address lookup API tool + +http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100 + +=back + +=cut + +sub wa_sales_update_tax_table { + my $args = shift; + + croak 'wa_sales_update_tax_table requires \$args hashref' + if $args && !ref $args; + + return + unless conf_tax_district_method() + && conf_tax_district_method() eq 'wa_sales'; + + $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname'); + $args->{year} ||= DateTime->now->year; + $args->{quarter} ||= DateTime->now->quarter; + + log_info_and_warn( + "Begin wa_sales_update_tax_table() ". + join ', ' => ( + map{ "$_ => ". ( $args->{$_} || 'undef' ) } + sort keys %$args + ) + ); + + $args->{temp_dir} ||= tempdir(); + + $args->{filename} ||= wa_sales_fetch_xlsx_file( $args ); + + $args->{tax_districts} = wa_sales_parse_xlsx_file( $args ); + + wa_sales_update_cust_main_county( $args ); + + log_info_and_warn( 'Finished wa_sales_update_tax_table()' ); +} + +=head2 wa_sales_update_cust_main_county \%args + +Create or update the L records with new data + + + +=cut + +sub wa_sales_update_cust_main_county { + my $args = shift; + + return + unless conf_tax_district_method() + && conf_tax_district_method() eq 'wa_sales'; + + croak 'wa_sales_update_cust_main_county requires $args hashref' + unless ref $args + && ref $args->{tax_districts}; + + my $insert_count = 0; + my $update_count = 0; + my $same_count = 0; + + # Work within a SQL transaction + local $FS::UID::AutoCommit = 0; + + for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) { + $taxclass ||= undef; # trap empty string when taxclasses are disabled + + my %cust_main_county = + map { $_->district => $_ } + qsearch( + cust_main_county => { + district => { op => '!=', value => undef }, + state => 'WA', + country => 'US', + source => 'wa_sales', + taxclass => $taxclass, } - } # foreach $taxclass - print "$district: updated $changed, skipped $skipped\n" - if $DEBUG and ($changed or $skipped); - $total_changed += $changed; - $total_skipped += $skipped; + ); + + for my $district ( @{ $args->{tax_districts} } ) { + if ( my $row = $cust_main_county{ $district->{district} } ) { + + # District already exists in this taxclass, update if necessary + # + # If admin updates value of conf tax_district_taxname, instead of + # creating an entire separate set of tax rows with + # the new taxname, update the taxname on existing records + + if ( + $row->tax == ( $district->{tax_combined} * 100 ) + && $row->taxname eq $args->{taxname} + ) { + $same_count++; + next; + } + + $row->city( uc $district->{city} ); + $row->county( uc $district->{county} ); + $row->taxclass( $taxclass ); + $row->taxname( $args->{taxname} || undef ); + $row->tax( $district->{tax_combined} * 100 ); + + if ( my $error = $row->replace ) { + dbh->rollback; + local $FS::UID::AutoCommit = 1; + log_error_and_die( + sprintf + "Error updating cust_main_county row %s for district %s: %s", + $row->taxnum, + $district->{district}, + $error + ); + } + + $update_count++; + + } else { + + # District doesn't exist, create row + + my $row = FS::cust_main_county->new({ + district => $district->{district}, + city => uc $district->{city}, + county => uc $district->{county}, + state => 'WA', + country => 'US', + taxclass => $taxclass, + taxname => $args->{taxname} || undef, + tax => $district->{tax_combined} * 100, + source => 'wa_sales', + }); + + if ( my $error = $row->insert ) { + dbh->rollback; + local $FS::UID::AutoCommit = 1; + log_error_and_die( + sprintf + "Error inserting cust_main_county row for district %s: %s", + $district->{district}, + $error + ); + } + + $cust_main_county{ $district->{district} } = $row; + $insert_count++; + } + + } # /foreach $district + } # /foreach $taxclass + + dbh->commit; + + local $FS::UID::AutoCommit = 1; + log_info_and_warn( + sprintf + "WA tax table update completed. ". + "Inserted %s rows, updated %s rows, identical %s rows", + $insert_count, + $update_count, + $same_count + ); + +} + +=head2 wa_sales_parse_xlsx_file \%args + +Parse given XLSX file for tax district information +Return an arrayref of district information hashrefs + +=cut + +sub wa_sales_parse_xlsx_file { + my $args = shift; + + croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename' + unless ref $args + && $args->{filename}; + + # About the file format: + # + # The current spreadsheet contains the following @columns. + # Rows 1 and 2 are a marquee header + # Row 3 is the column labels. We will test these to detect + # changes in the data format + # Rows 4+ are the tax district data + # + # The "city" column is being parsed from "Location" + + my @columns = qw( city county district tax_local tax_state tax_combined ); + + log_error_and_die( "Unable to access XLSX file: $args->{filename}" ) + unless -r $args->{filename}; + + my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} ) + or log_error_and_die( "Error parsing XLSX file: $!" ); + + my $sheet = $xls_parser->{Worksheet}->[0] + or log_error_and_die(" Unable to access worksheet 1 in XLSX file" ); + + my $cells = $sheet->{Cells} + or log_error_and_die( "Unable to read cells in XLSX file" ); + + # Read the column labels and verify + my %labels = + map{ $columns[$_] => $cells->[2][$_]->{Val} } + 0 .. scalar(@columns)-1; + + my %expected_labels = ( + city => 'Location', + county => 'County', + district => 'Location Code', + tax_local => 'Local Rate', + tax_state => 'State Rate', + tax_combined => 'Combined Sales Tax', + ); + + if ( + my @error_labels = + grep { lc $labels{$_} ne lc $expected_labels{$_} } + @columns + ) { + my $error = "Error parsing XLS file - ". + "Data format may have been updated with WA DOR! "; + $error .= "Expected column $expected_labels{$_}, found $labels{$_}! " + for @error_labels; + log_error_and_die( $error ); + } + + # Parse the rows into an array of hashes + my @districts; + for my $row ( 3..$sheet->{MaxRow} ) { + my %district = ( + map { $columns[$_] => $cells->[$row][$_]->{Val} } + 0 .. scalar(@columns)-1 + ); + + if ( + $district{city} + && $district{county} + && $district{district} =~ /^\d+$/ + && $district{tax_local} =~ /^\d?\.\d+$/ + && $district{tax_state} =~ /^\d?\.\d+$/ + && $district{tax_combined} =~ /^\d?\.\d+$/ + ) { + + # For some reason, city may contain line breaks! + $district{city} =~ s/[\r\n]//g; + + push @districts, \%district; + } else { + log_warn_and_warn( + "Non-usable row found in spreadsheet:\n" . Dumper( \%district ) + ); } - print "Updated $total_changed tax rates.\nSkipped $total_skipped unchanged rates.\n" if $DEBUG; - dbh->commit; - } # else $method isn't wa_sales, no other methods exist yet - ''; + + } + + log_error_and_die( "No \@districts found in data file!" ) + unless @districts; + + log_info_and_warn( + sprintf "Parsed %s districts from data file", scalar @districts + ); + + \@districts; + +} + +=head2 wa_sales_fetch_xlsx_file \%args + +Download data file from WA state DOR to temporary storage, +return filename + +=cut + +sub wa_sales_fetch_xlsx_file { + my $args = shift; + + return + unless conf_tax_district_method() + && conf_tax_district_method() eq 'wa_sales'; + + croak 'wa_sales_fetch_xlsx_file requires \$args hashref' + unless ref $args + && $args->{temp_dir}; + + my $url_base = 'https://dor.wa.gov'. + '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx'; + + my $year = $args->{year} || DateTime->now->year; + my $quarter = $args->{quarter} || DateTime->now->quarter; + $year = substr( $year, 2, 2 ) if $year >= 1000; + + my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter ); + my $url = "$url_base/$fn"; + + my $write_fn = "$args->{temp_dir}/$fn"; + + log_info_and_warn( "Begin download from url: $url" ); + + my $ua = LWP::UserAgent->new; + my $res = $ua->get( $url ); + + log_error_and_die( "Download error: ".$res->status_line ) + unless $res->is_success; + + local $@; + eval { write_file( $write_fn, $res->decoded_content ); }; + log_error_and_die( "Problem writing download to disk: $@" ) + if $@; + + log_info_and_warn( "Temporary file: $write_fn" ); + $write_fn; + +} + +sub log { + state $log = FS::Log->new('tax_rate_update'); + $log; } +sub log_info_and_warn { + my $log_message = shift; + warn "$log_message\n"; + &log()->info( $log_message ); +} + +sub log_warn_and_warn { + my $log_message = shift; + warn "$log_message\n"; + &log()->warn( $log_message ); +} + +sub log_error_and_die { + my $log_message = shift; + &log()->error( $log_message ); + die( "$log_message\n" ); +} + +sub log_error_and_warn { + my $log_message = shift; + warn "$log_message\n"; + &log()->error( $log_message ); +} + +sub conf_tax_district_method { + state $tax_district_method = FS::Conf->new->config('tax_district_method'); + $tax_district_method; +} + + 1; diff --git a/bin/wa_tax_rate_update b/bin/wa_tax_rate_update index c50a77771..fef126d34 100644 --- a/bin/wa_tax_rate_update +++ b/bin/wa_tax_rate_update @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl =head1 NAME @@ -10,27 +10,32 @@ Tool to update city/district sales tax rates in I from the Washington State Department of Revenue website. Creates, or updates, a L row for every tax district -in Washington state. Some cities have different tax rates based on the -address, within the city. Because of this, some cities may have multiple -rows defined. +in Washington state. Some cities have different tax rates based on the +address, within the city. Because of this, some cities have +district. -When a Washington state address is inserted or changed in L, +If tax classes are enabled, a row is created in every tax class for +every district. + +Customer addresses aren't classified into districts here. Instead, +when a Washington state address is inserted or changed in L, a job is queued for FS::geocode_Mixin::process_district_update, to ask the Washington state API which tax district to use for this address. Options: --f : Skip downloading, and process the given excel file + -f : Skip downloading, and process the given excel file --c : Updated or create records within given tax class, - If not specified, taxclass will be set as NULL + -t : Updated or created records will be set to the given tax name. + If not specified, conf value 'tax_district_taxname' will be used --t : Updated or created records will be set to the given tax name. - If not specified, conf value 'tax_district_taxname' will be used + -y : Specify year for tax table - defaults to current year --y : Specify year for tax table - defaults to current year + -q : Specify quarter for tax table - defaults to current quarter --q : Specify quarter for tax table - defaults to current quarter + -l : Attempt to look up the tax district classification for + unclassified cust_location records in Washington. Will + notify of records that cannot be classified =head1 Washington State Department of Revenue Resources @@ -67,297 +72,76 @@ use warnings; our $VERSION = '0.02'; # Make Getopt:Std happy -use Data::Dumper; -use DateTime; -use File::Temp 'tempdir'; -use File::Slurp qw(read_file write_file); use Getopt::Std; -use LWP::UserAgent; -use Spreadsheet::XLSX; -use FS::cust_main_county; +use FS::Cron::tax_rate_update qw( + wa_sales_update_tax_table + wa_sales_log_customer_without_tax_district +); use FS::Log; -use FS::Record qw(qsearch qsearchs dbh); use FS::UID qw(adminsuidsetup); my %opts; -getopts( 'c:t:y:q:f:', \%opts ); +getopts( 't:y:q:f:l', \%opts ); + my $user = shift or die HELP_MESSAGE(); adminsuidsetup( $user ) or die "bad username '$user'\n"; -my $temp_dir = tempdir(); my $log = FS::Log->new('wa_tax_rate_update'); -$opts{t} ||= FS::Conf->new->config('tax_district_taxname'); - -log_info_and_warn( "Set taxclass=$opts{c}" ) if $opts{c}; -log_info_and_warn( "Set taxname=$opts{t}" ) if $opts{t}; - -my $xls_fn = $opts{f} || fetch_xlsx_file(); -my $tax_districts = parse_xlsx_file( $xls_fn ); - -update_cust_main_county( $tax_districts ); - -# print Dumper( \%opts ); -# for ( 0..5 ) { -# print Dumper( $tax_districts->[$_] ); -# } - -log_info_and_warn( "Finished" ); -exit; - - -sub update_cust_main_county { - my $tax_districts = shift; - - # - # Working from an assumption tax tables may be loaded multiple times, - # each with a different tax class, - # re: comments on FS::geocode_Mixin::process_district_update - # - - my %cust_main_county = - map { $_->district => $_ } - qsearch( - cust_main_county => { - district => { op => '!=', value => undef }, - state => 'WA', - source => 'wa_sales', - taxclass => $opts{c}, - } - ); - - # Work within a SQL transaction - local $FS::UID::AutoCommit = 0; - - my $insert_count = 0; - my $update_count = 0; - - for my $district ( @$tax_districts ) { - if ( my $row = $cust_main_county{ $district->{district} } ) { - - $row->city( uc $district->{city} ); - $row->county( uc $district->{county} ); - $row->taxclass( $opts{c} || undef ); - $row->taxname( $opts{t} || undef ); - $row->tax( $district->{tax_combined} * 100 ); - - if ( my $error = $row->replace ) { - dbh->rollback; - local $FS::UID::AutoCommit = 1; - log_error_and_die( - sprintf - "Error updating cust_main_county row %s for district %s: %s", - $row->taxnum, - $district->{district}, - $error - ); - } - - $update_count++; - - } else { - my $row = FS::cust_main_county->new({ - district => $district->{district}, - city => uc $district->{city}, - county => uc $district->{county}, - state => 'WA', - country => 'US', - taxclass => $opts{c} || undef, - taxname => $opts{t} || undef, - tax => $district->{tax_combined} * 100, - source => 'wa_sales', - }); - - if ( my $error = $row->insert ) { - dbh->rollback; - local $FS::UID::AutoCommit = 1; - log_error_and_die( - sprintf - "Error inserting cust_main_county row for district %s: %s", - $district->{district}, - $error - ); - } - - $cust_main_county{ $district->{district} } = $row; - $insert_count++; - } - } - - dbh->commit; - - local $FS::UID::AutoCommit = 1; - log_info_and_warn( - sprintf - "WA tax table update completed. Inserted %s rows, updated %s rows", - $insert_count, - $update_count - ); - -} - -sub parse_xlsx_file { - my $parse_fn = shift; - - # About the file format: - # - # The current spreadsheet contains the following @columns. - # Rows 1 and 2 are a marquee header - # Row 3 is the column labels. We will test these to detect - # changes in the data format - # Rows 4+ are the tax district data - # - # The "city" column is being parsed from "Location" - - my @columns = qw( city county district tax_local tax_state tax_combined ); - - log_error_and_die( "Unable to access XLSX file: $parse_fn" ) - unless -r $parse_fn; - - my $xls_parser = Spreadsheet::XLSX->new( $parse_fn ) - or log_error_and_die( "Error parsing XLSX file: $!" ); - - my $sheet = $xls_parser->{Worksheet}->[0] - or log_error_and_die(" Unable to access worksheet 1 in XLSX file" ); - - my $cells = $sheet->{Cells} - or log_error_and_die( "Unable to read cells in XLSX file" ); - - # Read the column labels and verify - my %labels = - map{ $columns[$_] => $cells->[2][$_]->{Val} } - 0 .. scalar(@columns)-1; - - my %expected_labels = ( - city => 'Location', - county => 'County', - district => 'Location Code', - tax_local => 'Local Rate', - tax_state => 'State Rate', - tax_combined => 'Combined Sales Tax', - ); - - if ( - my @error_labels = - grep { lc $labels{$_} ne lc $expected_labels{$_} } - @columns - ) { - my $error = "Error parsing XLS file - ". - "Data format may have been updated with WA DOR! "; - $error .= "Expected column $expected_labels{$_}, found $labels{$_}! " - for @error_labels; - log_error_and_die( $error ); - } - - # Parse the rows into an array of hashes - my @districts; - for my $row ( 3..$sheet->{MaxRow} ) { - my %district = ( - map { $columns[$_] => $cells->[$row][$_]->{Val} } - 0 .. scalar(@columns)-1 - ); - - if ( - $district{city} - && $district{county} - && $district{district} =~ /^\d+$/ - && $district{tax_local} =~ /^\d?\.\d+$/ - && $district{tax_state} =~ /^\d?\.\d+$/ - && $district{tax_combined} =~ /^\d?\.\d+$/ - ) { - - # For some reason, city may contain line breaks! - $district{city} =~ s/[\r\n]//g; - - push @districts, \%district; - } else { - log_warn_and_warn( - "Non-usable row found in spreadsheet:\n" . Dumper( \%district ) - ); - } +$log->info('Begin wa_tax_rate_update'); +{ + local $@; + eval { + wa_sales_update_tax_table({ + $opts{f} ? ( filename => $opts{f} ) : (), + $opts{t} ? ( taxname => $opts{t} ) : (), + $opts{y} ? ( year => $opts{y} ) : (), + $opts{q} ? ( quarter => $opts{q} ) : (), + }); + }; + + if ( $@ ) { + $log->error( "Error: $@" ); + warn "Error: $@\n"; + } else { + $log->info( 'Finished wa_tax_rate_update' ); + warn "Finished wa_tax_rate_update\n"; } - - log_error_and_die( "No \@districts found in data file!" ) - unless @districts; - - log_info_and_warn( - sprintf "Parsed %s districts from data file", scalar @districts - ); - - \@districts; } -sub fetch_xlsx_file { - # Download file to temporary storage, return filename - - my $url_base = 'https://dor.wa.gov'. - '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx'; - - my $year = $opts{y} || DateTime->now->year; - my $quarter = $opts{q} || DateTime->now->quarter; - $year = substr( $year, 2, 2 ) if $year >= 1000; - my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter ); - my $url = "$url_base/$fn"; - - my $write_fn = "$temp_dir/$fn"; - - log_info_and_warn( "Begin download from url: $url" ); - my $ua = LWP::UserAgent->new; - my $res = $ua->get( $url ); +if ( $opts{l} ) { + $log->info( 'Begin wa_sales_log_customer_without_tax_district' ); - log_error_and_die( "Download error: ".$res->status_line ) - unless $res->is_success; + wa_sales_log_customer_without_tax_district(); - local $@; - eval { write_file( $write_fn, $res->decoded_content ); }; - log_error_and_die( "Problem writing download to disk: $@" ) - if $@; - - log_info_and_warn( "Temporary file: $write_fn" ); - $write_fn; + $log->info( 'Finished wa_sales_log_customer_without_tax_district' ); + warn "Finished wa_sales_log_customer_without_tax_district\n"; } +exit; sub HELP_MESSAGE { print " Tool to update city/district sales tax rates in I from the Washington State Department of Revenue website. - Usage: [-f filename] [-c taxclass] [-t taxname] [-y year] [-q quarter] + Usage: [-f filename] [-t taxname] [-y year] [-q quarter] [-l] freeside_username Optional Options: -f filename Skip download, and process the specified filename - -c taxclass Update or create records within this taxclass - defaults as NULL -t taxname Apply tax name value to created or updated records defaults as conf value 'tax_district_taxname' -y year Year for data file download -q quarter Quarter of data file to download + -t lookup Try to fix cust_location records without a district "; exit; } -sub log_info_and_warn { - my $log_message = shift; - warn "$log_message\n"; - $log->info( $log_message ); -} - -sub log_warn_and_warn { - my $log_message = shift; - warn "$log_message\n"; - $log->warn( $log_message ); -} - -sub log_error_and_die { - my $log_message = shift; - $log->error( $log_message ); - die( "$log_message\n" ); -} -- cgit v1.2.1 From ad2c21d213088e731b33264e9bf3f868bb4689dd Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 23 Dec 2018 15:36:51 -0500 Subject: RT# 80488 Prevent rollback of system log messages --- FS/FS/Cron/tax_rate_update.pm | 50 ++++++++++++++++++++++++++++++------------- FS/FS/Misc/Geo.pm | 23 ++++++++++++++++---- bin/wa_tax_rate_update | 2 +- 3 files changed, 55 insertions(+), 20 deletions(-) mode change 100644 => 100755 bin/wa_tax_rate_update diff --git a/FS/FS/Cron/tax_rate_update.pm b/FS/FS/Cron/tax_rate_update.pm index b0745e409..72ca14535 100755 --- a/FS/FS/Cron/tax_rate_update.pm +++ b/FS/FS/Cron/tax_rate_update.pm @@ -117,10 +117,14 @@ sub wa_sales { =head2 wa_sales_log_customer_without_tax_district -For any active customers with cust_location records in WA state, -if a cust_location record has no tax district, find the correct -district using WA DOR API, or if not possible, generate an error -message into system log so address can be corrected +For any cust_location records +* In WA state +* Attached to non cancelled packages +* With no tax district + +Classify the tax district for the record using the WA State Dept of +Revenue API. If this fails, generate an error into system log so +address can be corrected =cut @@ -144,12 +148,31 @@ sub wa_sales_log_customer_without_tax_district { state => 'WA', district => undef, }, - addl_from => 'LEFT JOIN cust_main USING (custnum)', - extra_sql => sprintf 'AND ( %s ) ', FS::cust_main->active_sql, + addl_from => ' + LEFT JOIN cust_main USING (custnum) + LEFT JOIN cust_pkg ON cust_location.locationnum = cust_pkg.locationnum + ', + extra_sql => sprintf( + ' + AND cust_pkg.pkgnum IS NOT NULL + AND ( + cust_pkg.cancel > %s + OR cust_pkg.cancel IS NULL + ) + ', time() + ), ); for my $cust_location ( qsearch( \%qsearch_cust_location )) { local $@; + log_info_and_warn( + sprintf + 'Attempting to classify district for cust_location ' . + 'locationnum(%s) address(%s)', + $cust_location->locationnum, + $cust_location->address1, + ); + eval { FS::geocode_Mixin::process_district_update( 'FS::cust_location', @@ -158,16 +181,13 @@ sub wa_sales_log_customer_without_tax_district { }; if ( $@ ) { + # Error indicates a crash, not an error looking up district + # process_district_udpate will generate log messages for those errors log_error_and_warn( - sprintf "Failed to classify district for cust_location(%s): %s", + sprintf "Classify district error for cust_location(%s): %s", $cust_location->locationnum, $@ ); - } else { - log_info_and_warn( - sprintf "Classified district for cust_location(%s)", - $cust_location->locationnum - ); } sleep 1; # Be polite to WA DOR API @@ -289,8 +309,6 @@ sub wa_sales_update_tax_table { Create or update the L records with new data - - =cut sub wa_sales_update_cust_main_county { @@ -337,7 +355,9 @@ sub wa_sales_update_cust_main_county { if ( $row->tax == ( $district->{tax_combined} * 100 ) - && $row->taxname eq $args->{taxname} + && $row->taxname eq $args->{taxname} + && uc $row->county eq uc $district->{county} + && uc $row->city eq uc $district->{city} ) { $same_count++; next; diff --git a/FS/FS/Misc/Geo.pm b/FS/FS/Misc/Geo.pm index 96ce0764b..2e44364f2 100644 --- a/FS/FS/Misc/Geo.pm +++ b/FS/FS/Misc/Geo.pm @@ -147,7 +147,7 @@ sub get_district { Expects output of location_hash() as parameter -Dies on error, or if tax rate cannot be found using given address +Returns undef on error, or if tax rate cannot be found using given address Query the WA State Dept of Revenue API with an address, and return tax district information for that address. @@ -172,12 +172,23 @@ Returns a hashref with the following keys: - country US - exempt_amount 0 +If api returns no district for address, generates system log error +and returns undef + =cut sub wa_sales { + + # + # no die(): + # freeside-queued will issue dbh->rollback on die() ... this will + # also roll back system log messages about errors :/ freeside-queued + # doesn't propgate die messages into the system log. + # + my $location_hash = shift; - # Return without die() when called with pointless context + # Return when called with pointless context return unless $location_hash && ref $location_hash @@ -227,8 +238,10 @@ sub wa_sales { my $error = sprintf "Problem parsing XML from API URL(%s): %s", $prepared_url, $@; + $log->error( $error ); - die $error; + warn $error; + return; } my ($res_root) = $dom->findnodes('/response'); @@ -255,8 +268,10 @@ sub wa_sales { $res_code ? $api_response_codes[$res_code] : 'n/a', $location_hash->{address1}, $prepared_url; + $log->error( $error ); - die "$error\n"; + warn "$error\n"; + return; } my %response = ( diff --git a/bin/wa_tax_rate_update b/bin/wa_tax_rate_update old mode 100644 new mode 100755 index fef126d34..cb5814537 --- a/bin/wa_tax_rate_update +++ b/bin/wa_tax_rate_update @@ -131,7 +131,7 @@ sub HELP_MESSAGE { Tool to update city/district sales tax rates in I from the Washington State Department of Revenue website. - Usage: [-f filename] [-t taxname] [-y year] [-q quarter] [-l] freeside_username + Usage: wa_tax_rate_update [-f filename] [-t taxname] [-y year] [-q quarter] [-l] freeside_username Optional Options: -f filename Skip download, and process the specified filename -- cgit v1.2.1 From e37d7fcb7a13646234aca1542cbce0fc79e94a88 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 23 Dec 2018 16:37:20 -0500 Subject: RT# 80488 Add dependency libspreadsheet-parsexlsx-perl --- debian/control | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/debian/control b/debian/control index 2ec36892f..69577ba87 100644 --- a/debian/control +++ b/debian/control @@ -101,7 +101,8 @@ Depends: aspell-en,gnupg,ghostscript,gsfonts,gzip,latex-xcolor, libmap-splat-perl, libdatetime-format-ical-perl, librest-client-perl, libgeo-streetaddress-us-perl, libbusiness-onlinepayment-perl, libnet-vitelity-perl (>= 0.05), libnet-sslglue-perl, libexpect-perl, - libspreadsheet-parsexlsx-perl, libunicode-truncate-perl (>= 0.303-1) + libspreadsheet-parsexlsx-perl, libunicode-truncate-perl (>= 0.303-1), + libspreadsheet-xlsx-perl Conflicts: libparams-classify-perl (>= 0.013-6) Replaces: freeside (<<4) Breaks: freeside (<<4) -- cgit v1.2.1 From c8736d73613fa8a764eb7990dbd217d5071e3ea1 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 23 Dec 2018 16:40:26 -0500 Subject: RT# 80488 Correct typo --- bin/wa_tax_rate_update | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/bin/wa_tax_rate_update b/bin/wa_tax_rate_update index cb5814537..ad14687c9 100755 --- a/bin/wa_tax_rate_update +++ b/bin/wa_tax_rate_update @@ -139,7 +139,7 @@ sub HELP_MESSAGE { defaults as conf value 'tax_district_taxname' -y year Year for data file download -q quarter Quarter of data file to download - -t lookup Try to fix cust_location records without a district + -l lookup Try to fix cust_location records without a district "; exit; -- cgit v1.2.1 From 6cd7a3aef125a43fa9b5753062b5a7c4929ffdd3 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 23 Dec 2018 21:11:25 -0500 Subject: RT# 80488 Suppress warnings, disambiguate columns --- FS/FS/Cron/tax_rate_update.pm | 35 ++++++++++++++++++++--------------- 1 file changed, 20 insertions(+), 15 deletions(-) diff --git a/FS/FS/Cron/tax_rate_update.pm b/FS/FS/Cron/tax_rate_update.pm index 72ca14535..ef529c4a5 100755 --- a/FS/FS/Cron/tax_rate_update.pm +++ b/FS/FS/Cron/tax_rate_update.pm @@ -144,22 +144,22 @@ sub wa_sales_log_customer_without_tax_district { cust_location.state, cust_location.zip ', - hashref => { - state => 'WA', - district => undef, - }, addl_from => ' LEFT JOIN cust_main USING (custnum) LEFT JOIN cust_pkg ON cust_location.locationnum = cust_pkg.locationnum ', - extra_sql => sprintf( - ' + extra_sql => sprintf(q{ + WHERE cust_location.state = 'WA' + AND ( + cust_location.district IS NULL + or cust_location.district = '' + ) AND cust_pkg.pkgnum IS NOT NULL AND ( cust_pkg.cancel > %s OR cust_pkg.cancel IS NULL ) - ', time() + }, time() ), ); @@ -353,14 +353,19 @@ sub wa_sales_update_cust_main_county { # creating an entire separate set of tax rows with # the new taxname, update the taxname on existing records - if ( - $row->tax == ( $district->{tax_combined} * 100 ) - && $row->taxname eq $args->{taxname} - && uc $row->county eq uc $district->{county} - && uc $row->city eq uc $district->{city} - ) { - $same_count++; - next; + { + # Supress warning on taxname comparison, when taxname is undef + no warnings 'uninitialized'; + + if ( + $row->tax == ( $district->{tax_combined} * 100 ) + && $row->taxname eq $args->{taxname} + && uc $row->county eq uc $district->{county} + && uc $row->city eq uc $district->{city} + ) { + $same_count++; + next; + } } $row->city( uc $district->{city} ); -- cgit v1.2.1 From 363891726c5d37b4d002b6b7cdf8dcf6d7851380 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Wed, 26 Dec 2018 14:09:46 -0500 Subject: RT# - added Holidays thru 2025 --- FS/FS/pay_batch/eft_canada.pm | 56 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 56 insertions(+) diff --git a/FS/FS/pay_batch/eft_canada.pm b/FS/FS/pay_batch/eft_canada.pm index 3995ac3d2..4726f8888 100644 --- a/FS/FS/pay_batch/eft_canada.pm +++ b/FS/FS/pay_batch/eft_canada.pm @@ -51,6 +51,62 @@ my %holiday = ( 9 => { map {$_=>1} 3 }, #labour day 10 => { map {$_=>1} 8 }, #thanksgiving }, + 2019 => { 2 => { map {$_=>1} 18 }, #family day + 4 => { map {$_=>1} 19 }, #good friday + 4 => { map {$_=>1} 22 }, #easter monday + 5 => { map {$_=>1} 20 }, #victoria day + 8 => { map {$_=>1} 5 }, #First Monday of August Civic Holiday + 9 => { map {$_=>1} 2 }, #labour day + 10 => { map {$_=>1} 14 }, #thanksgiving + }, + 2020 => { 2 => { map {$_=>1} 17 }, #family day + 4 => { map {$_=>1} 10 }, #good friday + 4 => { map {$_=>1} 13 }, #easter monday + 5 => { map {$_=>1} 18 }, #victoria day + 8 => { map {$_=>1} 3 }, #First Monday of August Civic Holiday + 9 => { map {$_=>1} 7 }, #labour day + 10 => { map {$_=>1} 12 }, #thanksgiving + }, + 2021 => { 2 => { map {$_=>1} 15 }, #family day + 4 => { map {$_=>1} 2 }, #good friday + 4 => { map {$_=>1} 5 }, #easter monday + 5 => { map {$_=>1} 24 }, #victoria day + 8 => { map {$_=>1} 2 }, #First Monday of August Civic Holiday + 9 => { map {$_=>1} 6 }, #labour day + 10 => { map {$_=>1} 11 }, #thanksgiving + }, + 2022 => { 2 => { map {$_=>1} 21 }, #family day + 4 => { map {$_=>1} 15 }, #good friday + 4 => { map {$_=>1} 18 }, #easter monday + 5 => { map {$_=>1} 23 }, #victoria day + 8 => { map {$_=>1} 1 }, #First Monday of August Civic Holiday + 9 => { map {$_=>1} 5 }, #labour day + 10 => { map {$_=>1} 10 }, #thanksgiving + }, + 2023 => { 2 => { map {$_=>1} 20 }, #family day + 4 => { map {$_=>1} 7 }, #good friday + 4 => { map {$_=>1} 10 }, #easter monday + 5 => { map {$_=>1} 22 }, #victoria day + 8 => { map {$_=>1} 7 }, #First Monday of August Civic Holiday + 9 => { map {$_=>1} 4 }, #labour day + 10 => { map {$_=>1} 9 }, #thanksgiving + }, + 2024 => { 2 => { map {$_=>1} 19 }, #family day + 3 => { map {$_=>1} 29 }, #good friday + 4 => { map {$_=>1} 1 }, #easter monday + 5 => { map {$_=>1} 20 }, #victoria day + 8 => { map {$_=>1} 5 }, #First Monday of August Civic Holiday + 9 => { map {$_=>1} 2 }, #labour day + 10 => { map {$_=>1} 14 }, #thanksgiving + }, + 2025 => { 2 => { map {$_=>1} 17 }, #family day + 4 => { map {$_=>1} 18 }, #good friday + 4 => { map {$_=>1} 21 }, #easter monday + 5 => { map {$_=>1} 19 }, #victoria day + 8 => { map {$_=>1} 4 }, #First Monday of August Civic Holiday + 9 => { map {$_=>1} 1 }, #labour day + 10 => { map {$_=>1} 13 }, #thanksgiving + }, ); sub is_holiday { -- cgit v1.2.1 From a7cd032eb8714905311070b4dd25a21ee42102ee Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Wed, 26 Dec 2018 20:37:48 -0500 Subject: RT# 40206 Add logging to freeside-cdr-mysql --- FS/bin/freeside-cdr-asterisk_sql | 84 +++++++++++++++++++++++++++++++++------- 1 file changed, 69 insertions(+), 15 deletions(-) diff --git a/FS/bin/freeside-cdr-asterisk_sql b/FS/bin/freeside-cdr-asterisk_sql index 529ec9bb9..e32ccfe82 100755 --- a/FS/bin/freeside-cdr-asterisk_sql +++ b/FS/bin/freeside-cdr-asterisk_sql @@ -5,6 +5,7 @@ use vars qw( $DEBUG ); use Date::Parse 'str2time'; use Date::Format 'time2str'; use FS::UID qw(adminsuidsetup dbh); +use FS::Log; use FS::cdr; use DBI; use Getopt::Std; @@ -21,11 +22,22 @@ my $dsn = "dbi:$engine"; $dsn .= ":database=$opt{D}"; # if $opt{D}; $dsn .= ";host=$opt{H}" if $opt{H}; -my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) - or die $DBI::errstr; - adminsuidsetup $user; +my $log = FS::Log->new( 'freeside-cdr-asterisk_sql' ); + +my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) ; + +if ( $dbi ) { + log_msg( info => "Established connection to CDR database at dsn($dsn)" ); +} else { + log_and_die( error => + sprintf 'Fatal error connecting to CDR database at dsn(%s): %s', + $dsn, + $DBI::errstr + ); +} + my $fsdbh = FS::UID::dbh; my $table = $opt{T} || 'cdr'; @@ -34,11 +46,11 @@ my $table = $opt{T} || 'cdr'; if ( $engine =~ /^mysql/ ) { my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'"); if( ! @$status ) { - warn "Adding freesidestatus column...\n" if $DEBUG; + log_msg( warn => "Adding freesidestatus column" ); $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)") - or die $dbi->errstr; + or log_and_die( error => $dbi->errstr ); } else { - warn "freesidestatus column present\n" if $DEBUG; + log_msg( info => "freesidestatus column present" ); } } @@ -68,14 +80,24 @@ if ( $engine =~ /^mysql/ ) { my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL"; my $sth = $dbi->prepare($sql); -$sth->execute; -warn "Importing ".$sth->rows." records...\n" if $DEBUG; +$sth->execute + or log_and_die( error => $sth->errstr ); + +log_msg( info => sprintf 'Importing %s records', $sth->rows ); my $cdr_batch = new FS::cdr_batch({ 'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time), }); -my $error = $cdr_batch->insert; -die $error if $error; +if ( my $error = $cdr_batch->insert ) { + log_and_die( error => $error ); +} else { + log_msg( info => + sprintf 'cdrbatch %s %s', + $cdr_batch->cdrbatch, + $cdr_batch->cdrbatchnum + ); +} + my $cdrbatchnum = $cdr_batch->cdrbatchnum; my $imports = 0; @@ -97,9 +119,13 @@ while ( my $row = $sth->fetchrow_hashref ) { $cdr->cdrbatchnum($cdrbatchnum); - my $error = $cdr->insert; - if ($error) { - warn "failed import: $error\n"; + if ( my $error = $cdr->insert ) { + log_msg( error => + sprintf 'Non-fatal failure to import acctid(%s) from table(%s): %s', + $row->acctid, + $table, + $error + ); } else { $imports++; @@ -117,16 +143,44 @@ while ( my $row = $sth->fetchrow_hashref ) { if ( $dbi->do($usql, @args) ) { $updates++; } else { - warn "failed to set status: ".$dbi->errstr."\n"; + log_msg( error => + sprintf 'Non-fatal failure set status(done) acctid(%s) table(%s): %s', + $row->acctid, + $table, + $dbi->errstr + ); } } } -warn "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n"; +log_and_warn( + info => "Done.\nImported $imports CDRs, marked $updates CDRs as done" +); + $dbi->disconnect; +sub log_and_die { + my ( $level, $message ) = @_; + $log->$level( $message ); + die "[$level] $message\n"; +} + +sub log_msg { + my ( $level, $message ) = @_; + $log->$level( $message ); + warn "[$level] $message\n" + if $opt{v}; +} + +sub log_and_warn { + my ( $level, $message ) = @_; + $log->$level( $message ); + warn "$message\n"; +} + + sub usage { "Usage: \n freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]n\t-D database\n\t[ -T table ]\n\t[ -V asterisk_version]\n\t-U user\n\t-P password\n\tfreesideuser\n"; } -- cgit v1.2.1 From c8d7489992fc09eb8903f8071de77ae79e343e02 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Thu, 27 Dec 2018 14:39:04 -0500 Subject: RT# 74537 - fixes error where paytype is blank in V3 --- fs_selfservice/FS-SelfService/cgi/change_pay.html | 4 ++-- fs_selfservice/FS-SelfService/cgi/check.html | 6 ++++++ 2 files changed, 8 insertions(+), 2 deletions(-) diff --git a/fs_selfservice/FS-SelfService/cgi/change_pay.html b/fs_selfservice/FS-SelfService/cgi/change_pay.html index e272669dc..2b3142f4a 100644 --- a/fs_selfservice/FS-SelfService/cgi/change_pay.html +++ b/fs_selfservice/FS-SelfService/cgi/change_pay.html @@ -39,8 +39,8 @@ ); ## Don't show CHEK or DCHK option if ACH is read only - delete( $payby_index{'CHEK'} ) unless !$ach_read_only; - delete( $payby_index{'DCHK'} ) unless !$ach_read_only; + delete( $payby_index{'CHEK'} ) if ($ach_read_only && $payby ne "CHEK"); + delete( $payby_index{'DCHK'} ) if ($ach_read_only && $payby ne "DCHK"); tie my %options, 'Tie::IxHash', (); foreach my $payby_option ( grep { exists( $payby_index{$_} ) } @paybys ) { diff --git a/fs_selfservice/FS-SelfService/cgi/check.html b/fs_selfservice/FS-SelfService/cgi/check.html index 17635c306..b6fead12a 100644 --- a/fs_selfservice/FS-SelfService/cgi/check.html +++ b/fs_selfservice/FS-SelfService/cgi/check.html @@ -1,3 +1,9 @@ +<%= + $OUT = ''; + if ($ach_read_only) { + $OUT .= qq!You only have read only access!; + } +%> Account type > -- cgit v1.2.1 From 0ec0b91b7c383d626dd75b0c2b2d3969e5bdcbc7 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Fri, 28 Dec 2018 15:52:02 -0500 Subject: RT# 32917 - Added ability for taxes to be charged prior to applying the discount --- FS/FS/Schema.pm | 1 + FS/FS/TaxEngine/internal.pm | 9 +++++++++ httemplate/edit/cust_main_county.html | 4 ++++ 3 files changed, 14 insertions(+) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 7cc84a9f0..290c89daf 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -2318,6 +2318,7 @@ sub tables_hashref { 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt 'source', 'varchar', 'NULL', $char_d, '', '', + 'charge_prediscount', 'char', 'NULL', 1, '', '', # Y = charge this tax pre discount ], 'primary_key' => 'taxnum', 'unique' => [], diff --git a/FS/FS/TaxEngine/internal.pm b/FS/FS/TaxEngine/internal.pm index 5f5d2295a..6fb1ca756 100644 --- a/FS/FS/TaxEngine/internal.pm +++ b/FS/FS/TaxEngine/internal.pm @@ -105,6 +105,15 @@ sub taxline { my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur or next; # don't create zero-amount exemptions + ## re-add the discounted amount if the tax needs to be charged pre discount + if ($tax_object->charge_prediscount) { + my $discount_amount = 0; + foreach my $discount (@{$cust_bill_pkg->discounts}) { + $discount_amount += $discount->amount; + } + $taxable_charged += $discount_amount; + } + # XXX the following procedure should probably be in cust_bill_pkg if ( $exempt_cust ) { diff --git a/httemplate/edit/cust_main_county.html b/httemplate/edit/cust_main_county.html index 9cc5131a2..b0823098f 100644 --- a/httemplate/edit/cust_main_county.html +++ b/httemplate/edit/cust_main_county.html @@ -14,6 +14,7 @@ 'setuptax' => 'This tax not applicable to setup fees', 'recurtax' => 'This tax not applicable to recurring fees', 'exempt_amount' => 'Monthly exemption per customer ($25 "Texas tax")', + 'charge_prediscount' => 'Charge this tax prior to any discounts', }, 'fields' => \@fields, ) @@ -60,6 +61,9 @@ push @fields, 'taxname', { field=>'tax', type=>'percentage', }, + { type=>'tablebreak-tr-title', value=>'Charging options' }, + { field=>'charge_prediscount', type=>'checkbox', value=>'Y', }, + { type=>'tablebreak-tr-title', value=>'Exemptions' }, { field=>'setuptax', type=>'checkbox', value=>'Y', }, { field=>'recurtax', type=>'checkbox', value=>'Y', }, -- cgit v1.2.1