FS/FS/Cron/tax_rate_update.pm

   1 #!/usr/bin/perl
   2
   3 =head1 NAME
   4
   5 FS::Cron::tax_rate_update
   6
   7 =head1 DESCRIPTION
   8
   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.
  11
  12 =head2 wa_sales
  13
  14 =item Tax Rate Download
  15
  16 Once each month, update the tax tables from the WA DOR website.
  17
  18 =item Customer Address Rate Classification
  19
  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
  22 to be correctd.
  23
  24 =cut
  25
  26 use strict;
  27 use warnings;
  28 use feature 'state';
  29
  30 use Exporter;
  31 our @EXPORT_OK = qw(
  32   tax_rate_update
  33   wa_sales_update_tax_table
  34   wa_sales_log_customer_without_tax_district
  35 );
  36
  37 use Carp qw(croak);
  38 use DateTime;
  39 use File::Temp 'tempdir';
  40 use File::Slurp qw(read_file write_file);
  41 use LWP::UserAgent;
  42 use Spreadsheet::XLSX;
  43 use Text::CSV;
  44
  45 use FS::Conf;
  46 use FS::cust_main;
  47 use FS::cust_main_county;
  48 use FS::geocode_Mixin;
  49 use FS::Log;
  50 use FS::part_pkg_taxclass;
  51 use FS::Record qw(qsearch qsearchs dbh);
  52 use FS::upgrade_journal;
  53
  54 our $DEBUG = 0;
  55
  56 =head1 FUNCTIONS
  57
  58 =head2 tax_rate_update
  59
  60 Cron routine for freeside_daily.
  61
  62 Run one of the available cron functions based on conf value tax_district_method
  63
  64 =cut
  65
  66 sub tax_rate_update {
  67
  68   # Currently only wa_sales is supported
  69   my $tax_district_method = conf_tax_district_method();
  70
  71   return unless $tax_district_method;
  72
  73   if ( exists &{$tax_district_method} ) {
  74     my $func = \&{$tax_district_method};
  75     $func->();
  76   } else {
  77     my $log = FS::Log->new('tax_rate_update');
  78     $log->error( "Unhandled tax_district_method($tax_district_method)" );
  79   }
  80
  81 }
  82
  83 =head2 wa_sales
  84
  85 Monthly:   Update the complete WA state tax tables
  86 Every Run: Log errors for cust_location records without a district
  87
  88 =cut
  89
  90 sub wa_sales {
  91
  92   return
  93     unless conf_tax_district_method()
  94         && conf_tax_district_method() eq 'wa_sales';
  95
  96   my $dt_now  = DateTime->now;
  97   my $year    = $dt_now->year;
  98   my $quarter = $dt_now->quarter;
  99
 100   my $journal_label =
 101     sprintf 'wa_sales_update_tax_table_%sQ%s', $year, $quarter;
 102
 103   unless ( FS::upgrade_journal->is_done( $journal_label ) ) {
 104     local $@;
 105
 106     eval{ wa_sales_update_tax_table(); };
 107     log_error_and_die( "Error updating tax tables: $@" )
 108       if $@;
 109     FS::upgrade_journal->set_done( $journal_label );
 110   }
 111
 112   wa_sales_log_customer_without_tax_district();
 113
 114   '';
 115
 116 }
 117
 118 =head2 wa_sales_log_customer_without_tax_district
 119
 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
 124
 125 =cut
 126
 127 sub wa_sales_log_customer_without_tax_district {
 128
 129   return
 130     unless conf_tax_district_method()
 131         && conf_tax_district_method() eq 'wa_sales';
 132
 133   my %qsearch_cust_location = (
 134     table => 'cust_location',
 135     select => '
 136       cust_location.locationnum,
 137       cust_location.custnum,
 138       cust_location.address1,
 139       cust_location.city,
 140       cust_location.state,
 141       cust_location.zip
 142     ',
 143     hashref => {
 144       state    => 'WA',
 145       district => undef,
 146     },
 147     addl_from => 'LEFT JOIN cust_main USING (custnum)',
 148     extra_sql => sprintf 'AND ( %s ) ', FS::cust_main->active_sql,
 149   );
 150
 151   for my $cust_location ( qsearch( \%qsearch_cust_location )) {
 152     local $@;
 153     eval {
 154       FS::geocode_Mixin::process_district_update(
 155         'FS::cust_location',
 156         $cust_location->locationnum
 157       );
 158     };
 159
 160     if ( $@ ) {
 161       log_error_and_warn(
 162         sprintf "Failed to classify district for cust_location(%s): %s",
 163           $cust_location->locationnum,
 164           $@
 165       );
 166     } else {
 167       log_info_and_warn(
 168         sprintf "Classified district for cust_location(%s)",
 169           $cust_location->locationnum
 170       );
 171     }
 172
 173     sleep 1; # Be polite to WA DOR API
 174   }
 175
 176   for my $cust_location ( qsearch( \%qsearch_cust_location )) {
 177     log_error_and_warn(
 178       sprintf
 179         "Customer address in WA lacking tax district classification. ".
 180         "custnum(%s) ".
 181         "locationnum(%s) ".
 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 )
 186     );
 187   }
 188
 189 }
 190
 191
 192 =head2 wa_sales_update_tax_table \%args
 193
 194 Update city/district sales tax rates in L<FS::cust_main_county> from the
 195 Washington State Department of Revenue published data files.
 196
 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
 200 districts.
 201
 202 If tax classes are enabled, a row is created in every tax class for
 203 every district.
 204
 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.
 209
 210 All arguments are optional:
 211
 212   filename: Skip file download, and process the specified filename instead
 213
 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
 216
 217   year:     Specify year for tax table download.  Defaults to current year
 218
 219   quarter:  Specify quarter for tax table download.  Defaults to current quarter
 220
 221 =head3 Washington State Department of Revenue Resources
 222
 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
 226 XLSX format.
 227
 228 =over 4
 229
 230 =item WA Dept of Revenue
 231
 232 https://dor.wa.gov
 233
 234 =item Data file downloads
 235
 236 https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
 237
 238 =item XLSX file example
 239
 240 https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
 241
 242 =item CSV file example
 243
 244 https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
 245
 246
 247 =item Address lookup API tool
 248
 249 http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
 250
 251 =back
 252
 253 =cut
 254
 255 sub wa_sales_update_tax_table {
 256   my $args = shift;
 257
 258   croak 'wa_sales_update_tax_table requires \$args hashref'
 259     if $args && !ref $args;
 260
 261   return
 262     unless conf_tax_district_method()
 263         && conf_tax_district_method() eq 'wa_sales';
 264
 265   $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname');
 266   $args->{year}    ||= DateTime->now->year;
 267   $args->{quarter} ||= DateTime->now->quarter;
 268
 269   log_info_and_warn(
 270     "Begin wa_sales_update_tax_table() ".
 271     join ', ' => (
 272       map{ "$_ => ". ( $args->{$_} || 'undef' ) }
 273       sort keys %$args
 274     )
 275   );
 276
 277   $args->{temp_dir} ||= tempdir();
 278
 279   $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
 280
 281   $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
 282
 283   wa_sales_update_cust_main_county( $args );
 284
 285   log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
 286 }
 287
 288 =head2 wa_sales_update_cust_main_county \%args
 289
 290 Create or update the L<FS::cust_main_county> records with new data
 291
 292
 293
 294 =cut
 295
 296 sub wa_sales_update_cust_main_county {
 297   my $args = shift;
 298
 299   return
 300     unless conf_tax_district_method()
 301         && conf_tax_district_method() eq 'wa_sales';
 302
 303   croak 'wa_sales_update_cust_main_county requires $args hashref'
 304     unless ref $args
 305         && ref $args->{tax_districts};
 306
 307   my $insert_count = 0;
 308   my $update_count = 0;
 309   my $same_count   = 0;
 310
 311   # Work within a SQL transaction
 312   local $FS::UID::AutoCommit = 0;
 313
 314   for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
 315     $taxclass ||= undef; # trap empty string when taxclasses are disabled
 316
 317     my %cust_main_county =
 318       map { $_->district => $_ }
 319       qsearch(
 320         cust_main_county => {
 321           district => { op => '!=', value => undef },
 322           state    => 'WA',
 323           country  => 'US',
 324           source   => 'wa_sales',
 325           taxclass => $taxclass,
 326         }
 327       );
 328
 329     for my $district ( @{ $args->{tax_districts} } ) {
 330       if ( my $row = $cust_main_county{ $district->{district} } ) {
 331
 332         # District already exists in this taxclass, update if necessary
 333         #
 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
 337
 338         if (
 339           $row->tax == ( $district->{tax_combined} * 100 )
 340           && $row->taxname eq $args->{taxname}
 341         ) {
 342           $same_count++;
 343           next;
 344         }
 345
 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 );
 351
 352         if ( my $error = $row->replace ) {
 353           dbh->rollback;
 354           local $FS::UID::AutoCommit = 1;
 355           log_error_and_die(
 356             sprintf
 357               "Error updating cust_main_county row %s for district %s: %s",
 358               $row->taxnum,
 359               $district->{district},
 360               $error
 361           );
 362         }
 363
 364         $update_count++;
 365
 366       } else {
 367
 368         # District doesn't exist, create row
 369
 370         my $row = FS::cust_main_county->new({
 371           district => $district->{district},
 372           city     => uc $district->{city},
 373           county   => uc $district->{county},
 374           state    => 'WA',
 375           country  => 'US',
 376           taxclass => $taxclass,
 377           taxname  => $args->{taxname} || undef,
 378           tax      => $district->{tax_combined} * 100,
 379           source   => 'wa_sales',
 380         });
 381
 382         if ( my $error = $row->insert ) {
 383           dbh->rollback;
 384           local $FS::UID::AutoCommit = 1;
 385           log_error_and_die(
 386             sprintf
 387               "Error inserting cust_main_county row for district %s: %s",
 388               $district->{district},
 389               $error
 390           );
 391         }
 392
 393         $cust_main_county{ $district->{district} } = $row;
 394         $insert_count++;
 395       }
 396
 397     } # /foreach $district
 398   } # /foreach $taxclass
 399
 400   dbh->commit;
 401
 402   local $FS::UID::AutoCommit = 1;
 403   log_info_and_warn(
 404     sprintf
 405       "WA tax table update completed. ".
 406       "Inserted %s rows, updated %s rows, identical %s rows",
 407       $insert_count,
 408       $update_count,
 409       $same_count
 410   );
 411
 412 }
 413
 414 =head2 wa_sales_parse_xlsx_file \%args
 415
 416 Parse given XLSX file for tax district information
 417 Return an arrayref of district information hashrefs
 418
 419 =cut
 420
 421 sub wa_sales_parse_xlsx_file {
 422   my $args = shift;
 423
 424   croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
 425     unless ref $args
 426         && $args->{filename};
 427
 428   # About the file format:
 429   #
 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
 435   #
 436   # The "city" column is being parsed from "Location"
 437
 438   my @columns = qw( city county district tax_local tax_state tax_combined );
 439
 440   log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
 441     unless -r $args->{filename};
 442
 443   my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
 444     or log_error_and_die( "Error parsing XLSX file: $!" );
 445
 446   my $sheet = $xls_parser->{Worksheet}->[0]
 447     or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
 448
 449   my $cells = $sheet->{Cells}
 450     or log_error_and_die( "Unable to read cells in XLSX file" );
 451
 452   # Read the column labels and verify
 453   my %labels =
 454     map{ $columns[$_] => $cells->[2][$_]->{Val} }
 455     0 .. scalar(@columns)-1;
 456
 457   my %expected_labels = (
 458     city         => 'Location',
 459     county       => 'County',
 460     district     => 'Location Code',
 461     tax_local    => 'Local Rate',
 462     tax_state    => 'State Rate',
 463     tax_combined => 'Combined Sales Tax',
 464   );
 465
 466   if (
 467     my @error_labels =
 468       grep { lc $labels{$_} ne lc $expected_labels{$_} }
 469       @columns
 470   ) {
 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{$_}! "
 474       for @error_labels;
 475     log_error_and_die( $error );
 476   }
 477
 478   # Parse the rows into an array of hashes
 479   my @districts;
 480   for my $row ( 3..$sheet->{MaxRow} ) {
 481     my %district = (
 482       map { $columns[$_] => $cells->[$row][$_]->{Val} }
 483       0 .. scalar(@columns)-1
 484     );
 485
 486     if (
 487          $district{city}
 488       && $district{county}
 489       && $district{district}     =~ /^\d+$/
 490       && $district{tax_local}    =~ /^\d?\.\d+$/
 491       && $district{tax_state}    =~ /^\d?\.\d+$/
 492       && $district{tax_combined} =~ /^\d?\.\d+$/
 493     ) {
 494
 495       # For some reason, city may contain line breaks!
 496       $district{city} =~ s/[\r\n]//g;
 497
 498       push @districts, \%district;
 499     } else {
 500       log_warn_and_warn(
 501         "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
 502       );
 503     }
 504
 505   }
 506
 507   log_error_and_die( "No \@districts found in data file!" )
 508     unless @districts;
 509
 510   log_info_and_warn(
 511     sprintf "Parsed %s districts from data file", scalar @districts
 512   );
 513
 514   \@districts;
 515
 516 }
 517
 518 =head2 wa_sales_fetch_xlsx_file \%args
 519
 520 Download data file from WA state DOR to temporary storage,
 521 return filename
 522
 523 =cut
 524
 525 sub wa_sales_fetch_xlsx_file {
 526   my $args = shift;
 527
 528   return
 529     unless conf_tax_district_method()
 530         && conf_tax_district_method() eq 'wa_sales';
 531
 532   croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
 533     unless ref $args
 534         && $args->{temp_dir};
 535
 536   my $url_base = 'https://dor.wa.gov'.
 537                  '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
 538
 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;
 542
 543   my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
 544   my $url = "$url_base/$fn";
 545
 546   my $write_fn = "$args->{temp_dir}/$fn";
 547
 548   log_info_and_warn( "Begin download from url: $url" );
 549
 550   my $ua = LWP::UserAgent->new;
 551   my $res = $ua->get( $url );
 552
 553   log_error_and_die( "Download error: ".$res->status_line )
 554     unless $res->is_success;
 555
 556   local $@;
 557   eval { write_file( $write_fn, $res->decoded_content ); };
 558   log_error_and_die( "Problem writing download to disk: $@" )
 559     if $@;
 560
 561   log_info_and_warn( "Temporary file: $write_fn" );
 562   $write_fn;
 563
 564 }
 565
 566 sub log {
 567   state $log = FS::Log->new('tax_rate_update');
 568   $log;
 569 }
 570
 571 sub log_info_and_warn {
 572   my $log_message = shift;
 573   warn "$log_message\n";
 574   &log()->info( $log_message );
 575 }
 576
 577 sub log_warn_and_warn {
 578   my $log_message = shift;
 579   warn "$log_message\n";
 580   &log()->warn( $log_message );
 581 }
 582
 583 sub log_error_and_die {
 584   my $log_message = shift;
 585   &log()->error( $log_message );
 586   die( "$log_message\n" );
 587 }
 588
 589 sub log_error_and_warn {
 590   my $log_message = shift;
 591   warn "$log_message\n";
 592   &log()->error( $log_message );
 593 }
 594
 595 sub conf_tax_district_method {
 596   state $tax_district_method = FS::Conf->new->config('tax_district_method');
 597   $tax_district_method;
 598 }
 599
 600
 601 1;