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 active customers with cust_location records in WA state,
121 if a cust_location record has no tax district, find the correct
122 district using WA DOR API, or if not possible, generate an error
123 message into system log so address can be corrected
127 sub wa_sales_log_customer_without_tax_district {
130 unless conf_tax_district_method()
131 && conf_tax_district_method() eq 'wa_sales';
133 my %qsearch_cust_location = (
134 table => 'cust_location',
136 cust_location.locationnum,
137 cust_location.custnum,
138 cust_location.address1,
147 addl_from => 'LEFT JOIN cust_main USING (custnum)',
148 extra_sql => sprintf 'AND ( %s ) ', FS::cust_main->active_sql,
151 for my $cust_location ( qsearch( \%qsearch_cust_location )) {
154 FS::geocode_Mixin::process_district_update(
156 $cust_location->locationnum
162 sprintf "Failed to classify district for cust_location(%s): %s",
163 $cust_location->locationnum,
168 sprintf "Classified district for cust_location(%s)",
169 $cust_location->locationnum
173 sleep 1; # Be polite to WA DOR API
176 for my $cust_location ( qsearch( \%qsearch_cust_location )) {
179 "Customer address in WA lacking tax district classification. ".
182 "address(%s, %s %s, %s) ".
183 "[https://webgis.dor.wa.gov/taxratelookup/SalesTax.aspx]",
184 map { $cust_location->$_ }
185 qw( custnum locationnum address1 city state zip )
192 =head2 wa_sales_update_tax_table \%args
194 Update city/district sales tax rates in L<FS::cust_main_county> from the
195 Washington State Department of Revenue published data files.
197 Creates, or updates, a L<FS::cust_main_county> row for every tax district
198 in Washington state. Some cities have different tax rates based on the
199 address, within the city. Because of this, some cities have multiple
202 If tax classes are enabled, a row is created in every tax class for
205 Customer addresses aren't classified into districts here. Instead,
206 when a Washington state address is inserted or changed in L<FS::cust_location>,
207 a job is queued for FS::geocode_Mixin::process_district_update, to ask the
208 Washington state API which tax district to use for this address.
210 All arguments are optional:
212 filename: Skip file download, and process the specified filename instead
214 taxname: Updated or created records will be set to the given tax name.
215 If not specified, conf value 'tax_district_taxname' is used
217 year: Specify year for tax table download. Defaults to current year
219 quarter: Specify quarter for tax table download. Defaults to current quarter
221 =head3 Washington State Department of Revenue Resources
223 The state of Washington makes data files available via their public website.
224 It's possible the availability or format of these files may change. As of now,
225 the only data file that contains both city and county names is published in
230 =item WA Dept of Revenue
234 =item Data file downloads
236 https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
238 =item XLSX file example
240 https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
242 =item CSV file example
244 https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
247 =item Address lookup API tool
249 http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
255 sub wa_sales_update_tax_table {
258 croak 'wa_sales_update_tax_table requires \$args hashref'
259 if $args && !ref $args;
262 unless conf_tax_district_method()
263 && conf_tax_district_method() eq 'wa_sales';
265 $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname');
266 $args->{year} ||= DateTime->now->year;
267 $args->{quarter} ||= DateTime->now->quarter;
270 "Begin wa_sales_update_tax_table() ".
272 map{ "$_ => ". ( $args->{$_} || 'undef' ) }
277 $args->{temp_dir} ||= tempdir();
279 $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
281 $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
283 wa_sales_update_cust_main_county( $args );
285 log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
288 =head2 wa_sales_update_cust_main_county \%args
290 Create or update the L<FS::cust_main_county> records with new data
296 sub wa_sales_update_cust_main_county {
300 unless conf_tax_district_method()
301 && conf_tax_district_method() eq 'wa_sales';
303 croak 'wa_sales_update_cust_main_county requires $args hashref'
305 && ref $args->{tax_districts};
307 my $insert_count = 0;
308 my $update_count = 0;
311 # Work within a SQL transaction
312 local $FS::UID::AutoCommit = 0;
314 for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
315 $taxclass ||= undef; # trap empty string when taxclasses are disabled
317 my %cust_main_county =
318 map { $_->district => $_ }
320 cust_main_county => {
321 district => { op => '!=', value => undef },
324 source => 'wa_sales',
325 taxclass => $taxclass,
329 for my $district ( @{ $args->{tax_districts} } ) {
330 if ( my $row = $cust_main_county{ $district->{district} } ) {
332 # District already exists in this taxclass, update if necessary
334 # If admin updates value of conf tax_district_taxname, instead of
335 # creating an entire separate set of tax rows with
336 # the new taxname, update the taxname on existing records
339 $row->tax == ( $district->{tax_combined} * 100 )
340 && $row->taxname eq $args->{taxname}
346 $row->city( uc $district->{city} );
347 $row->county( uc $district->{county} );
348 $row->taxclass( $taxclass );
349 $row->taxname( $args->{taxname} || undef );
350 $row->tax( $district->{tax_combined} * 100 );
352 if ( my $error = $row->replace ) {
354 local $FS::UID::AutoCommit = 1;
357 "Error updating cust_main_county row %s for district %s: %s",
359 $district->{district},
368 # District doesn't exist, create row
370 my $row = FS::cust_main_county->new({
371 district => $district->{district},
372 city => uc $district->{city},
373 county => uc $district->{county},
376 taxclass => $taxclass,
377 taxname => $args->{taxname} || undef,
378 tax => $district->{tax_combined} * 100,
379 source => 'wa_sales',
382 if ( my $error = $row->insert ) {
384 local $FS::UID::AutoCommit = 1;
387 "Error inserting cust_main_county row for district %s: %s",
388 $district->{district},
393 $cust_main_county{ $district->{district} } = $row;
397 } # /foreach $district
398 } # /foreach $taxclass
402 local $FS::UID::AutoCommit = 1;
405 "WA tax table update completed. ".
406 "Inserted %s rows, updated %s rows, identical %s rows",
414 =head2 wa_sales_parse_xlsx_file \%args
416 Parse given XLSX file for tax district information
417 Return an arrayref of district information hashrefs
421 sub wa_sales_parse_xlsx_file {
424 croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
426 && $args->{filename};
428 # About the file format:
430 # The current spreadsheet contains the following @columns.
431 # Rows 1 and 2 are a marquee header
432 # Row 3 is the column labels. We will test these to detect
433 # changes in the data format
434 # Rows 4+ are the tax district data
436 # The "city" column is being parsed from "Location"
438 my @columns = qw( city county district tax_local tax_state tax_combined );
440 log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
441 unless -r $args->{filename};
443 my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
444 or log_error_and_die( "Error parsing XLSX file: $!" );
446 my $sheet = $xls_parser->{Worksheet}->[0]
447 or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
449 my $cells = $sheet->{Cells}
450 or log_error_and_die( "Unable to read cells in XLSX file" );
452 # Read the column labels and verify
454 map{ $columns[$_] => $cells->[2][$_]->{Val} }
455 0 .. scalar(@columns)-1;
457 my %expected_labels = (
460 district => 'Location Code',
461 tax_local => 'Local Rate',
462 tax_state => 'State Rate',
463 tax_combined => 'Combined Sales Tax',
468 grep { lc $labels{$_} ne lc $expected_labels{$_} }
471 my $error = "Error parsing XLS file - ".
472 "Data format may have been updated with WA DOR! ";
473 $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
475 log_error_and_die( $error );
478 # Parse the rows into an array of hashes
480 for my $row ( 3..$sheet->{MaxRow} ) {
482 map { $columns[$_] => $cells->[$row][$_]->{Val} }
483 0 .. scalar(@columns)-1
489 && $district{district} =~ /^\d+$/
490 && $district{tax_local} =~ /^\d?\.\d+$/
491 && $district{tax_state} =~ /^\d?\.\d+$/
492 && $district{tax_combined} =~ /^\d?\.\d+$/
495 # For some reason, city may contain line breaks!
496 $district{city} =~ s/[\r\n]//g;
498 push @districts, \%district;
501 "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
507 log_error_and_die( "No \@districts found in data file!" )
511 sprintf "Parsed %s districts from data file", scalar @districts
518 =head2 wa_sales_fetch_xlsx_file \%args
520 Download data file from WA state DOR to temporary storage,
525 sub wa_sales_fetch_xlsx_file {
529 unless conf_tax_district_method()
530 && conf_tax_district_method() eq 'wa_sales';
532 croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
534 && $args->{temp_dir};
536 my $url_base = 'https://dor.wa.gov'.
537 '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
539 my $year = $args->{year} || DateTime->now->year;
540 my $quarter = $args->{quarter} || DateTime->now->quarter;
541 $year = substr( $year, 2, 2 ) if $year >= 1000;
543 my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
544 my $url = "$url_base/$fn";
546 my $write_fn = "$args->{temp_dir}/$fn";
548 log_info_and_warn( "Begin download from url: $url" );
550 my $ua = LWP::UserAgent->new;
551 my $res = $ua->get( $url );
553 log_error_and_die( "Download error: ".$res->status_line )
554 unless $res->is_success;
557 eval { write_file( $write_fn, $res->decoded_content ); };
558 log_error_and_die( "Problem writing download to disk: $@" )
561 log_info_and_warn( "Temporary file: $write_fn" );
567 state $log = FS::Log->new('tax_rate_update');
571 sub log_info_and_warn {
572 my $log_message = shift;
573 warn "$log_message\n";
574 &log()->info( $log_message );
577 sub log_warn_and_warn {
578 my $log_message = shift;
579 warn "$log_message\n";
580 &log()->warn( $log_message );
583 sub log_error_and_die {
584 my $log_message = shift;
585 &log()->error( $log_message );
586 die( "$log_message\n" );
589 sub log_error_and_warn {
590 my $log_message = shift;
591 warn "$log_message\n";
592 &log()->error( $log_message );
595 sub conf_tax_district_method {
596 state $tax_district_method = FS::Conf->new->config('tax_district_method');
597 $tax_district_method;