diff options
| -rwxr-xr-x | FS/FS/Cron/tax_rate_update.pm | 653 | ||||
| -rw-r--r-- | 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 fec696f..b0745e4 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<cust_main_county>. 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<FS::cust_main_county> from the +Washington State Department of Revenue published data files. + +Creates, or updates, a L<FS::cust_main_county> 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<FS::cust_location>, +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<FS::cust_main_county> 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 c50a777..fef126d 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<cust_main_county> from the Washington State Department of Revenue website. Creates, or updates, a L<FS::cust_main_county> 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<FS::cust_location>, +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<FS::cust_location>, 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 <filename>: Skip downloading, and process the given excel file + -f <filename>: Skip downloading, and process the given excel file --c <taxclass>: Updated or create records within given tax class, - If not specified, taxclass will be set as NULL + -t <taxname>: Updated or created records will be set to the given tax name. + If not specified, conf value 'tax_district_taxname' will be used --t <taxname>: Updated or created records will be set to the given tax name. - If not specified, conf value 'tax_district_taxname' will be used + -y <year>: Specify year for tax table - defaults to current year --y <year>: Specify year for tax table - defaults to current year + -q <quarter>: Specify quarter for tax table - defaults to current quarter --q <quarter>: Specify quarter for tax table - defaults to current quarter + -l <lookup>: 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<cust_main_county> 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" ); -} |