5 FS::Cron::tax_rate_update
9 Cron routine to update city/district sales tax rates in I<cust_main_county>.
10 Currently supports sales tax in the state of Washington.
14 =item Tax Rate Download
16 Once each month, update the tax tables from the WA DOR website.
18 =item Customer Address Rate Classification
20 Find cust_location rows in WA with no tax district. Try to determine
21 a tax district. Otherwise, generate a log error that address needs
33 wa_sales_update_tax_table
34 wa_sales_log_customer_without_tax_district
39 use File::Temp 'tempdir';
40 use File::Slurp qw(read_file write_file);
42 use Spreadsheet::XLSX;
47 use FS::cust_main_county;
48 use FS::geocode_Mixin;
50 use FS::part_pkg_taxclass;
51 use FS::Record qw(qsearch qsearchs dbh);
52 use FS::upgrade_journal;
58 =head2 tax_rate_update
60 Cron routine for freeside_daily.
62 Run one of the available cron functions based on conf value tax_district_method
68 # Currently only wa_sales is supported
69 my $tax_district_method = conf_tax_district_method();
71 return unless $tax_district_method;
73 if ( exists &{$tax_district_method} ) {
74 my $func = \&{$tax_district_method};
77 my $log = FS::Log->new('tax_rate_update');
78 $log->error( "Unhandled tax_district_method($tax_district_method)" );
85 Monthly: Update the complete WA state tax tables
86 Every Run: Log errors for cust_location records without a district
93 unless conf_tax_district_method()
94 && conf_tax_district_method() eq 'wa_sales';
96 my $dt_now = DateTime->now;
97 my $year = $dt_now->year;
98 my $quarter = $dt_now->quarter;
101 sprintf 'wa_sales_update_tax_table_%sQ%s', $year, $quarter;
103 unless ( FS::upgrade_journal->is_done( $journal_label ) ) {
106 eval{ wa_sales_update_tax_table(); };
107 log_error_and_die( "Error updating tax tables: $@" )
109 FS::upgrade_journal->set_done( $journal_label );
112 wa_sales_log_customer_without_tax_district();
118 =head2 wa_sales_log_customer_without_tax_district
120 For any cust_location records
122 * Attached to non cancelled packages
123 * With no tax district
125 Classify the tax district for the record using the WA State Dept of
126 Revenue API. If this fails, generate an error into system log so
127 address can be corrected
131 sub wa_sales_log_customer_without_tax_district {
134 unless conf_tax_district_method()
135 && conf_tax_district_method() eq 'wa_sales';
137 my %qsearch_cust_location = (
138 table => 'cust_location',
140 cust_location.locationnum,
141 cust_location.custnum,
142 cust_location.address1,
148 LEFT JOIN cust_main USING (custnum)
149 LEFT JOIN cust_pkg ON cust_location.locationnum = cust_pkg.locationnum
151 extra_sql => sprintf(q{
152 WHERE cust_location.state = 'WA'
154 cust_location.district IS NULL
155 or cust_location.district = ''
157 AND cust_pkg.pkgnum IS NOT NULL
160 OR cust_pkg.cancel IS NULL
166 for my $cust_location ( qsearch( \%qsearch_cust_location )) {
170 'Attempting to classify district for cust_location ' .
171 'locationnum(%s) address(%s)',
172 $cust_location->locationnum,
173 $cust_location->address1,
177 FS::geocode_Mixin::process_district_update(
179 $cust_location->locationnum
184 # Error indicates a crash, not an error looking up district
185 # process_district_udpate will generate log messages for those errors
187 sprintf "Classify district error for cust_location(%s): %s",
188 $cust_location->locationnum,
193 sleep 1; # Be polite to WA DOR API
196 for my $cust_location ( qsearch( \%qsearch_cust_location )) {
199 "Customer address in WA lacking tax district classification. ".
202 "address(%s, %s %s, %s) ".
203 "[https://webgis.dor.wa.gov/taxratelookup/SalesTax.aspx]",
204 map { $cust_location->$_ }
205 qw( custnum locationnum address1 city state zip )
212 =head2 wa_sales_update_tax_table \%args
214 Update city/district sales tax rates in L<FS::cust_main_county> from the
215 Washington State Department of Revenue published data files.
217 Creates, or updates, a L<FS::cust_main_county> row for every tax district
218 in Washington state. Some cities have different tax rates based on the
219 address, within the city. Because of this, some cities have multiple
222 If tax classes are enabled, a row is created in every tax class for
225 Customer addresses aren't classified into districts here. Instead,
226 when a Washington state address is inserted or changed in L<FS::cust_location>,
227 a job is queued for FS::geocode_Mixin::process_district_update, to ask the
228 Washington state API which tax district to use for this address.
230 All arguments are optional:
232 filename: Skip file download, and process the specified filename instead
234 taxname: Updated or created records will be set to the given tax name.
235 If not specified, conf value 'tax_district_taxname' is used
237 year: Specify year for tax table download. Defaults to current year
239 quarter: Specify quarter for tax table download. Defaults to current quarter
241 =head3 Washington State Department of Revenue Resources
243 The state of Washington makes data files available via their public website.
244 It's possible the availability or format of these files may change. As of now,
245 the only data file that contains both city and county names is published in
250 =item WA Dept of Revenue
254 =item Data file downloads
256 https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
258 =item XLSX file example
260 https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
262 =item CSV file example
264 https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
267 =item Address lookup API tool
269 http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
275 sub wa_sales_update_tax_table {
278 croak 'wa_sales_update_tax_table requires \$args hashref'
279 if $args && !ref $args;
282 unless conf_tax_district_method()
283 && conf_tax_district_method() eq 'wa_sales';
285 $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname');
286 $args->{year} ||= DateTime->now->year;
287 $args->{quarter} ||= DateTime->now->quarter;
290 "Begin wa_sales_update_tax_table() ".
292 map{ "$_ => ". ( $args->{$_} || 'undef' ) }
297 unless ( wa_sales_update_tax_table_sanity_check() ) {
299 'Duplicate district rows exist in the Washington state sales tax table. '.
300 'These must be resolved before updating the tax tables. '.
301 'See "freeside-wa-tax-table-resolve --check" to repair the tax tables. '
305 $args->{temp_dir} ||= tempdir();
307 $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
309 $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
311 wa_sales_update_cust_main_county( $args );
313 log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
316 =head2 wa_sales_update_cust_main_county \%args
318 Create or update the L<FS::cust_main_county> records with new data
322 sub wa_sales_update_cust_main_county {
326 unless conf_tax_district_method()
327 && conf_tax_district_method() eq 'wa_sales';
329 croak 'wa_sales_update_cust_main_county requires $args hashref'
331 && ref $args->{tax_districts};
333 my $insert_count = 0;
334 my $update_count = 0;
337 $args->{taxname} ||= 'State Sales Tax';
339 # Work within a SQL transaction
340 local $FS::UID::AutoCommit = 0;
342 for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
343 $taxclass ||= undef; # trap empty string when taxclasses are disabled
345 # Dupe detection/remediation:
347 # Previous code for washington state tax district was creating
348 # duplicate entries for tax districts. This could lead to customers
351 # The following code detects and eliminates duplicates that
352 # were created by wa_sales district code (source=wa_sales)
353 # before updating the tax table with the newly downloaded
356 my %cust_main_county;
357 my %cust_main_county_dupe;
361 cust_main_county => {
362 source => 'wa_sales',
363 district => { op => '!=', value => undef },
364 taxclass => $taxclass,
368 my $district = $row->district;
370 # Row belongs to a known dupe group of districts
371 if ( $cust_main_county_dupe{$district} ) {
372 push @{ $cust_main_county_dupe{$district} }, $row;
376 # Row is the first seen dupe for the given district
377 if ( $cust_main_county{$district} ) {
378 $cust_main_county_dupe{$district} = [
379 delete $cust_main_county{$district},
385 # Row is the first seen with this district
386 $cust_main_county{$district} = $row;
389 # # Merge any dupes, place resulting non-dupe row in %cust_main_county
390 # # Merge, even if one of the dupes has a $0 tax, or some other
391 # # variation on tax row data. Data for this row will get corrected
392 # # during the following tax import
393 # for my $dupe_district_aref ( values %cust_main_county_dupe ) {
394 # my $row_to_keep = shift @$dupe_district_aref;
395 # while ( my $row_to_merge = shift @$dupe_district_aref ) {
396 # $row_to_merge->_merge_into(
398 # { identical_record_check => 0 },
401 # $cust_main_county{$row_to_keep->district} = $row_to_keep;
404 # If there are duplicate rows, it may be unsafe to auto-resolve them
405 if ( %cust_main_county_dupe ) {
406 warn "Unable to continue!";
407 log_error_and_die( sprintf(
408 'Tax district duplicate rows detected(%s) - '.
409 'WA Sales tax tables cannot be updated without resolving duplicates - '.
410 'Please use tool freeside-wa-tax-table-resolve for tax table repair',
411 join( ',', keys %cust_main_county_dupe )
415 DIST: for my $district ( @{ $args->{tax_districts} } ) {
416 if ( my $row = $cust_main_county{ $district->{district} } ) {
418 # Strip whitespace from input
419 $district->{$_} =~ s/(^\s+|\s+$)//g for keys %$district;
421 # District already exists in this taxclass, update if necessary
423 # If admin updates value of conf tax_district_taxname, instead of
424 # creating an entire separate set of tax rows with
425 # the new taxname, update the taxname on existing records
428 # Supress warning on taxname comparison, when taxname is undef
429 no warnings 'uninitialized';
432 sprintf('%.4f',$row->tax) == sprintf('%.4f',($district->{tax_combined} * 100))
433 && $row->taxname eq $args->{taxname}
434 && uc $row->county eq uc $district->{county}
435 && uc $row->city eq uc $district->{city}
442 $row->city( uc $district->{city} );
443 $row->county( uc $district->{county} );
444 $row->taxclass( $taxclass );
445 $row->taxname( $args->{taxname} );
446 $row->tax( $district->{tax_combined} * 100 );
448 if ( my $error = $row->replace ) {
450 local $FS::UID::AutoCommit = 1;
453 "Error updating cust_main_county row %s for district %s: %s",
455 $district->{district},
464 # District doesn't exist, create row
466 my $row = FS::cust_main_county->new({
467 district => $district->{district},
468 city => uc $district->{city},
469 county => uc $district->{county},
472 taxclass => $taxclass,
473 taxname => $args->{taxname} || undef,
474 tax => $district->{tax_combined} * 100,
475 source => 'wa_sales',
478 if ( my $error = $row->insert ) {
480 local $FS::UID::AutoCommit = 1;
483 "Error inserting cust_main_county row for district %s: %s",
484 $district->{district},
489 $cust_main_county{ $district->{district} } = $row;
493 update_non_sales_tax_rows( $taxclass, $district );
495 } # /foreach $district
496 } # /foreach $taxclass
500 local $FS::UID::AutoCommit = 1;
503 "WA tax table update completed. ".
504 "Inserted %s rows, updated %s rows, identical %s rows",
512 =head2 update_non_sales_tax_rows tax_class, $district_href
514 The customer may have created additional taxes, such as Universal Service Fund.
516 Ensure the columns for city and county are consistant between
517 the user-created tax rows and the wa-sales-managed tax rows.
521 sub update_non_sales_tax_rows {
522 my ( $taxclass, $district ) = @_;
524 return unless ref $district && $district->{district};
526 my @rows = qsearch( cust_main_county => {
527 taxclass => $taxclass,
528 district => $district->{district},
531 source => { op => '!=', value => 'wa_sales' },
534 for my $row ( @rows ) {
535 $row->city( uc $district->{city} );
536 $row->county( uc $district->{county} );
538 if ( my $error = $row->replace ) {
540 local $FS::UID::AutoCommit = 1;
543 "Error updating cust_main_county row %s for district %s: %s",
545 $district->{district},
553 =head2 wa_sales_parse_xlsx_file \%args
555 Parse given XLSX file for tax district information
556 Return an arrayref of district information hashrefs
560 sub wa_sales_parse_xlsx_file {
563 croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
565 && $args->{filename};
567 # About the file format:
569 # The current spreadsheet contains the following @columns.
570 # Rows 1 and 2 are a marquee header
571 # Row 3 is the column labels. We will test these to detect
572 # changes in the data format
573 # Rows 4+ are the tax district data
575 # The "city" column is being parsed from "Location"
577 my @columns = qw( city county district tax_local tax_state tax_combined );
579 log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
580 unless -r $args->{filename};
582 my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
583 or log_error_and_die( "Error parsing XLSX file: $!" );
585 my $sheet = $xls_parser->{Worksheet}->[0]
586 or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
588 my $cells = $sheet->{Cells}
589 or log_error_and_die( "Unable to read cells in XLSX file" );
591 # Read the column labels and verify
593 map{ $columns[$_] => $cells->[2][$_]->{Val} }
594 0 .. scalar(@columns)-1;
596 my %expected_labels = (
599 district => 'Location Code',
600 tax_local => 'Local Rate',
601 tax_state => 'State Rate',
602 tax_combined => 'Combined Sales Tax',
607 grep { lc $labels{$_} ne lc $expected_labels{$_} }
610 my $error = "Error parsing XLS file - ".
611 "Data format may have been updated with WA DOR! ";
612 $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
614 log_error_and_die( $error );
617 # Parse the rows into an array of hashes
619 for my $row ( 3..$sheet->{MaxRow} ) {
621 map { $columns[$_] => $cells->[$row][$_]->{Val} }
622 0 .. scalar(@columns)-1
628 && $district{district} =~ /^\d+$/
629 && $district{tax_local} =~ /^\d?\.\d+$/
630 && $district{tax_state} =~ /^\d?\.\d+$/
631 && $district{tax_combined} =~ /^\d?\.\d+$/
634 # For some reason, city may contain line breaks!
635 $district{city} =~ s/[\r\n]//g;
637 push @districts, \%district;
640 "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
646 log_error_and_die( "No \@districts found in data file!" )
650 sprintf "Parsed %s districts from data file", scalar @districts
657 =head2 wa_sales_fetch_xlsx_file \%args
659 Download data file from WA state DOR to temporary storage,
664 sub wa_sales_fetch_xlsx_file {
668 unless conf_tax_district_method()
669 && conf_tax_district_method() eq 'wa_sales';
671 croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
673 && $args->{temp_dir};
675 my $url_base = 'https://dor.wa.gov'.
676 '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
678 my $year = $args->{year} || DateTime->now->year;
679 my $quarter = $args->{quarter} || DateTime->now->quarter;
680 $year = substr( $year, 2, 2 ) if $year >= 1000;
682 my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
683 my $url = "$url_base/$fn";
685 my $write_fn = "$args->{temp_dir}/$fn";
687 log_info_and_warn( "Begin download from url: $url" );
689 my $ua = LWP::UserAgent->new;
690 my $res = $ua->get( $url );
692 log_error_and_die( "Download error: ".$res->status_line )
693 unless $res->is_success;
696 eval { write_file( $write_fn, $res->decoded_content ); };
697 log_error_and_die( "Problem writing download to disk: $@" )
700 log_info_and_warn( "Temporary file: $write_fn" );
705 =head2 wa_sales_update_tax_table_sanity_check
707 There should be no duplicate tax table entries in the tax table,
708 with the same district value, within a tax class, where source=wa_sales.
710 If there are, custome taxes may have been user-entered in the
711 freeside UI, and incorrectly labelled as source=wa_sales. Or, the
712 dupe record may have been created by issues with older wa_sales code.
714 If these dupes exist, the sysadmin must solve the problem by hand
715 with the freeeside-wa-tax-table-resolve script
717 Returns 1 unless problem sales tax entries are detected
721 sub wa_sales_update_tax_table_sanity_check {
722 FS::cust_main_county->find_wa_tax_dupes ? 0 : 1;
726 state $log = FS::Log->new('tax_rate_update');
730 sub log_info_and_warn {
731 my $log_message = shift;
732 warn "$log_message\n";
733 &log()->info( $log_message );
736 sub log_warn_and_warn {
737 my $log_message = shift;
738 warn "$log_message\n";
739 &log()->warn( $log_message );
742 sub log_error_and_die {
743 my $log_message = shift;
744 &log()->error( $log_message );
745 warn( "$log_message\n" );
746 die( "$log_message\n" );
749 sub log_error_and_warn {
750 my $log_message = shift;
751 warn "$log_message\n";
752 &log()->error( $log_message );
755 sub conf_tax_district_method {
756 state $tax_district_method = FS::Conf->new->config('tax_district_method');
757 $tax_district_method;