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 cust_location records
 121 * In WA state
 122 * Attached to non cancelled packages
 123 * With no tax district
 124
 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
 128
 129 =cut
 130
 131 sub wa_sales_log_customer_without_tax_district {
 132
 133   return
 134     unless conf_tax_district_method()
 135         && conf_tax_district_method() eq 'wa_sales';
 136
 137   my %qsearch_cust_location = (
 138     table => 'cust_location',
 139     select => '
 140       cust_location.locationnum,
 141       cust_location.custnum,
 142       cust_location.address1,
 143       cust_location.city,
 144       cust_location.state,
 145       cust_location.zip
 146     ',
 147     hashref => {
 148       state    => 'WA',
 149       district => undef,
 150     },
 151     addl_from => '
 152       LEFT JOIN cust_main USING (custnum)
 153       LEFT JOIN cust_pkg ON cust_location.locationnum = cust_pkg.locationnum
 154     ',
 155     extra_sql => sprintf(
 156       '
 157         AND cust_pkg.pkgnum IS NOT NULL
 158         AND (
 159              cust_pkg.cancel > %s
 160           OR cust_pkg.cancel IS NULL
 161         )
 162       ', time()
 163     ),
 164   );
 165
 166   for my $cust_location ( qsearch( \%qsearch_cust_location )) {
 167     local $@;
 168     log_info_and_warn(
 169       sprintf
 170         'Attempting to classify district for cust_location ' .
 171         'locationnum(%s) address(%s)',
 172           $cust_location->locationnum,
 173           $cust_location->address1,
 174     );
 175
 176     eval {
 177       FS::geocode_Mixin::process_district_update(
 178         'FS::cust_location',
 179         $cust_location->locationnum
 180       );
 181     };
 182
 183     if ( $@ ) {
 184       # Error indicates a crash, not an error looking up district
 185       # process_district_udpate will generate log messages for those errors
 186       log_error_and_warn(
 187         sprintf "Classify district error for cust_location(%s): %s",
 188           $cust_location->locationnum,
 189           $@
 190       );
 191     }
 192
 193     sleep 1; # Be polite to WA DOR API
 194   }
 195
 196   for my $cust_location ( qsearch( \%qsearch_cust_location )) {
 197     log_error_and_warn(
 198       sprintf
 199         "Customer address in WA lacking tax district classification. ".
 200         "custnum(%s) ".
 201         "locationnum(%s) ".
 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 )
 206     );
 207   }
 208
 209 }
 210
 211
 212 =head2 wa_sales_update_tax_table \%args
 213
 214 Update city/district sales tax rates in L<FS::cust_main_county> from the
 215 Washington State Department of Revenue published data files.
 216
 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
 220 districts.
 221
 222 If tax classes are enabled, a row is created in every tax class for
 223 every district.
 224
 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.
 229
 230 All arguments are optional:
 231
 232   filename: Skip file download, and process the specified filename instead
 233
 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
 236
 237   year:     Specify year for tax table download.  Defaults to current year
 238
 239   quarter:  Specify quarter for tax table download.  Defaults to current quarter
 240
 241 =head3 Washington State Department of Revenue Resources
 242
 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
 246 XLSX format.
 247
 248 =over 4
 249
 250 =item WA Dept of Revenue
 251
 252 https://dor.wa.gov
 253
 254 =item Data file downloads
 255
 256 https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
 257
 258 =item XLSX file example
 259
 260 https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
 261
 262 =item CSV file example
 263
 264 https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
 265
 266
 267 =item Address lookup API tool
 268
 269 http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
 270
 271 =back
 272
 273 =cut
 274
 275 sub wa_sales_update_tax_table {
 276   my $args = shift;
 277
 278   croak 'wa_sales_update_tax_table requires \$args hashref'
 279     if $args && !ref $args;
 280
 281   return
 282     unless conf_tax_district_method()
 283         && conf_tax_district_method() eq 'wa_sales';
 284
 285   $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname');
 286   $args->{year}    ||= DateTime->now->year;
 287   $args->{quarter} ||= DateTime->now->quarter;
 288
 289   log_info_and_warn(
 290     "Begin wa_sales_update_tax_table() ".
 291     join ', ' => (
 292       map{ "$_ => ". ( $args->{$_} || 'undef' ) }
 293       sort keys %$args
 294     )
 295   );
 296
 297   $args->{temp_dir} ||= tempdir();
 298
 299   $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
 300
 301   $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
 302
 303   wa_sales_update_cust_main_county( $args );
 304
 305   log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
 306 }
 307
 308 =head2 wa_sales_update_cust_main_county \%args
 309
 310 Create or update the L<FS::cust_main_county> records with new data
 311
 312 =cut
 313
 314 sub wa_sales_update_cust_main_county {
 315   my $args = shift;
 316
 317   return
 318     unless conf_tax_district_method()
 319         && conf_tax_district_method() eq 'wa_sales';
 320
 321   croak 'wa_sales_update_cust_main_county requires $args hashref'
 322     unless ref $args
 323         && ref $args->{tax_districts};
 324
 325   my $insert_count = 0;
 326   my $update_count = 0;
 327   my $same_count   = 0;
 328
 329   # Work within a SQL transaction
 330   local $FS::UID::AutoCommit = 0;
 331
 332   for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
 333     $taxclass ||= undef; # trap empty string when taxclasses are disabled
 334
 335     my %cust_main_county =
 336       map { $_->district => $_ }
 337       qsearch(
 338         cust_main_county => {
 339           district => { op => '!=', value => undef },
 340           state    => 'WA',
 341           country  => 'US',
 342           source   => 'wa_sales',
 343           taxclass => $taxclass,
 344         }
 345       );
 346
 347     for my $district ( @{ $args->{tax_districts} } ) {
 348       if ( my $row = $cust_main_county{ $district->{district} } ) {
 349
 350         # District already exists in this taxclass, update if necessary
 351         #
 352         # If admin updates value of conf tax_district_taxname, instead of
 353         # creating an entire separate set of tax rows with
 354         # the new taxname, update the taxname on existing records
 355
 356         if (
 357           $row->tax == ( $district->{tax_combined} * 100 )
 358           &&    $row->taxname eq    $args->{taxname}
 359           && uc $row->county  eq uc $district->{county}
 360           && uc $row->city    eq uc $district->{city}
 361         ) {
 362           $same_count++;
 363           next;
 364         }
 365
 366         $row->city( uc $district->{city} );
 367         $row->county( uc $district->{county} );
 368         $row->taxclass( $taxclass );
 369         $row->taxname( $args->{taxname} || undef );
 370         $row->tax( $district->{tax_combined} * 100 );
 371
 372         if ( my $error = $row->replace ) {
 373           dbh->rollback;
 374           local $FS::UID::AutoCommit = 1;
 375           log_error_and_die(
 376             sprintf
 377               "Error updating cust_main_county row %s for district %s: %s",
 378               $row->taxnum,
 379               $district->{district},
 380               $error
 381           );
 382         }
 383
 384         $update_count++;
 385
 386       } else {
 387
 388         # District doesn't exist, create row
 389
 390         my $row = FS::cust_main_county->new({
 391           district => $district->{district},
 392           city     => uc $district->{city},
 393           county   => uc $district->{county},
 394           state    => 'WA',
 395           country  => 'US',
 396           taxclass => $taxclass,
 397           taxname  => $args->{taxname} || undef,
 398           tax      => $district->{tax_combined} * 100,
 399           source   => 'wa_sales',
 400         });
 401
 402         if ( my $error = $row->insert ) {
 403           dbh->rollback;
 404           local $FS::UID::AutoCommit = 1;
 405           log_error_and_die(
 406             sprintf
 407               "Error inserting cust_main_county row for district %s: %s",
 408               $district->{district},
 409               $error
 410           );
 411         }
 412
 413         $cust_main_county{ $district->{district} } = $row;
 414         $insert_count++;
 415       }
 416
 417     } # /foreach $district
 418   } # /foreach $taxclass
 419
 420   dbh->commit;
 421
 422   local $FS::UID::AutoCommit = 1;
 423   log_info_and_warn(
 424     sprintf
 425       "WA tax table update completed. ".
 426       "Inserted %s rows, updated %s rows, identical %s rows",
 427       $insert_count,
 428       $update_count,
 429       $same_count
 430   );
 431
 432 }
 433
 434 =head2 wa_sales_parse_xlsx_file \%args
 435
 436 Parse given XLSX file for tax district information
 437 Return an arrayref of district information hashrefs
 438
 439 =cut
 440
 441 sub wa_sales_parse_xlsx_file {
 442   my $args = shift;
 443
 444   croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
 445     unless ref $args
 446         && $args->{filename};
 447
 448   # About the file format:
 449   #
 450   # The current spreadsheet contains the following @columns.
 451   # Rows 1 and 2 are a marquee header
 452   # Row 3 is the column labels.  We will test these to detect
 453   #   changes in the data format
 454   # Rows 4+ are the tax district data
 455   #
 456   # The "city" column is being parsed from "Location"
 457
 458   my @columns = qw( city county district tax_local tax_state tax_combined );
 459
 460   log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
 461     unless -r $args->{filename};
 462
 463   my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
 464     or log_error_and_die( "Error parsing XLSX file: $!" );
 465
 466   my $sheet = $xls_parser->{Worksheet}->[0]
 467     or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
 468
 469   my $cells = $sheet->{Cells}
 470     or log_error_and_die( "Unable to read cells in XLSX file" );
 471
 472   # Read the column labels and verify
 473   my %labels =
 474     map{ $columns[$_] => $cells->[2][$_]->{Val} }
 475     0 .. scalar(@columns)-1;
 476
 477   my %expected_labels = (
 478     city         => 'Location',
 479     county       => 'County',
 480     district     => 'Location Code',
 481     tax_local    => 'Local Rate',
 482     tax_state    => 'State Rate',
 483     tax_combined => 'Combined Sales Tax',
 484   );
 485
 486   if (
 487     my @error_labels =
 488       grep { lc $labels{$_} ne lc $expected_labels{$_} }
 489       @columns
 490   ) {
 491     my $error = "Error parsing XLS file - ".
 492                 "Data format may have been updated with WA DOR! ";
 493     $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
 494       for @error_labels;
 495     log_error_and_die( $error );
 496   }
 497
 498   # Parse the rows into an array of hashes
 499   my @districts;
 500   for my $row ( 3..$sheet->{MaxRow} ) {
 501     my %district = (
 502       map { $columns[$_] => $cells->[$row][$_]->{Val} }
 503       0 .. scalar(@columns)-1
 504     );
 505
 506     if (
 507          $district{city}
 508       && $district{county}
 509       && $district{district}     =~ /^\d+$/
 510       && $district{tax_local}    =~ /^\d?\.\d+$/
 511       && $district{tax_state}    =~ /^\d?\.\d+$/
 512       && $district{tax_combined} =~ /^\d?\.\d+$/
 513     ) {
 514
 515       # For some reason, city may contain line breaks!
 516       $district{city} =~ s/[\r\n]//g;
 517
 518       push @districts, \%district;
 519     } else {
 520       log_warn_and_warn(
 521         "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
 522       );
 523     }
 524
 525   }
 526
 527   log_error_and_die( "No \@districts found in data file!" )
 528     unless @districts;
 529
 530   log_info_and_warn(
 531     sprintf "Parsed %s districts from data file", scalar @districts
 532   );
 533
 534   \@districts;
 535
 536 }
 537
 538 =head2 wa_sales_fetch_xlsx_file \%args
 539
 540 Download data file from WA state DOR to temporary storage,
 541 return filename
 542
 543 =cut
 544
 545 sub wa_sales_fetch_xlsx_file {
 546   my $args = shift;
 547
 548   return
 549     unless conf_tax_district_method()
 550         && conf_tax_district_method() eq 'wa_sales';
 551
 552   croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
 553     unless ref $args
 554         && $args->{temp_dir};
 555
 556   my $url_base = 'https://dor.wa.gov'.
 557                  '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
 558
 559   my $year    = $args->{year}    || DateTime->now->year;
 560   my $quarter = $args->{quarter} || DateTime->now->quarter;
 561   $year = substr( $year, 2, 2 ) if $year >= 1000;
 562
 563   my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
 564   my $url = "$url_base/$fn";
 565
 566   my $write_fn = "$args->{temp_dir}/$fn";
 567
 568   log_info_and_warn( "Begin download from url: $url" );
 569
 570   my $ua = LWP::UserAgent->new;
 571   my $res = $ua->get( $url );
 572
 573   log_error_and_die( "Download error: ".$res->status_line )
 574     unless $res->is_success;
 575
 576   local $@;
 577   eval { write_file( $write_fn, $res->decoded_content ); };
 578   log_error_and_die( "Problem writing download to disk: $@" )
 579     if $@;
 580
 581   log_info_and_warn( "Temporary file: $write_fn" );
 582   $write_fn;
 583
 584 }
 585
 586 sub log {
 587   state $log = FS::Log->new('tax_rate_update');
 588   $log;
 589 }
 590
 591 sub log_info_and_warn {
 592   my $log_message = shift;
 593   warn "$log_message\n";
 594   &log()->info( $log_message );
 595 }
 596
 597 sub log_warn_and_warn {
 598   my $log_message = shift;
 599   warn "$log_message\n";
 600   &log()->warn( $log_message );
 601 }
 602
 603 sub log_error_and_die {
 604   my $log_message = shift;
 605   &log()->error( $log_message );
 606   die( "$log_message\n" );
 607 }
 608
 609 sub log_error_and_warn {
 610   my $log_message = shift;
 611   warn "$log_message\n";
 612   &log()->error( $log_message );
 613 }
 614
 615 sub conf_tax_district_method {
 616   state $tax_district_method = FS::Conf->new->config('tax_district_method');
 617   $tax_district_method;
 618 }
 619
 620
 621 1;