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     addl_from => '
 148       LEFT JOIN cust_main USING (custnum)
 149       LEFT JOIN cust_pkg ON cust_location.locationnum = cust_pkg.locationnum
 150     ',
 151     extra_sql => sprintf(q{
 152         WHERE cust_location.state = 'WA'
 153         AND (
 154              cust_location.district IS NULL
 155           or cust_location.district = ''
 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   unless ( wa_sales_update_tax_table_sanity_check() ) {
 298     log_error_and_die(
 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. '
 302     );
 303   }
 304
 305   $args->{temp_dir} ||= tempdir();
 306
 307   $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
 308
 309   $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
 310
 311   wa_sales_update_cust_main_county( $args );
 312
 313   log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
 314 }
 315
 316 =head2 wa_sales_update_cust_main_county \%args
 317
 318 Create or update the L<FS::cust_main_county> records with new data
 319
 320 =cut
 321
 322 sub wa_sales_update_cust_main_county {
 323   my $args = shift;
 324
 325   return
 326     unless conf_tax_district_method()
 327         && conf_tax_district_method() eq 'wa_sales';
 328
 329   croak 'wa_sales_update_cust_main_county requires $args hashref'
 330     unless ref $args
 331         && ref $args->{tax_districts};
 332
 333   my $insert_count = 0;
 334   my $update_count = 0;
 335   my $same_count   = 0;
 336
 337   $args->{taxname} ||= 'State Sales Tax';
 338
 339   # Work within a SQL transaction
 340   local $FS::UID::AutoCommit = 0;
 341
 342   for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
 343     $taxclass ||= undef; # trap empty string when taxclasses are disabled
 344
 345     # Dupe detection/remediation:
 346     #
 347     # Previous code for washington state tax district was creating
 348     # duplicate entries for tax districts.  This could lead to customers
 349     # being double-taxed
 350     #
 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
 354     # data
 355
 356     my %cust_main_county;
 357     my %cust_main_county_dupe;
 358
 359     for my $row (
 360       qsearch(
 361         cust_main_county => {
 362           source    => 'wa_sales',
 363           district  => { op => '!=', value => undef },
 364           taxclass => $taxclass,
 365         }
 366       )
 367     ) {
 368       my $district = $row->district;
 369
 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;
 373         next;
 374       }
 375
 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},
 380           $row
 381         ];
 382         next;
 383       }
 384
 385       # Row is the first seen with this district
 386       $cust_main_county{$district} = $row;
 387     }
 388
 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(
 397     #       $row_to_keep,
 398     #       { identical_record_check => 0 },
 399     #     );
 400     #   }
 401     #   $cust_main_county{$row_to_keep->district} = $row_to_keep;
 402     # }
 403
 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 )
 412       ));
 413     }
 414
 415     DIST: for my $district ( @{ $args->{tax_districts} } ) {
 416       if ( my $row = $cust_main_county{ $district->{district} } ) {
 417
 418         # Strip whitespace from input
 419         $district->{$_} =~ s/(^\s+|\s+$)//g for keys %$district;
 420
 421         # District already exists in this taxclass, update if necessary
 422         #
 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
 426
 427         {
 428           # Supress warning on taxname comparison, when taxname is undef
 429           no warnings 'uninitialized';
 430
 431           if (
 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}
 436           ) {
 437             $same_count++;
 438             next DIST;
 439           }
 440         }
 441
 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 );
 447
 448         if ( my $error = $row->replace ) {
 449           dbh->rollback;
 450           local $FS::UID::AutoCommit = 1;
 451           log_error_and_die(
 452             sprintf
 453               "Error updating cust_main_county row %s for district %s: %s",
 454               $row->taxnum,
 455               $district->{district},
 456               $error
 457           );
 458         }
 459
 460         $update_count++;
 461
 462       } else {
 463
 464         # District doesn't exist, create row
 465
 466         my $row = FS::cust_main_county->new({
 467           district => $district->{district},
 468           city     => uc $district->{city},
 469           county   => uc $district->{county},
 470           state    => 'WA',
 471           country  => 'US',
 472           taxclass => $taxclass,
 473           taxname  => $args->{taxname} || undef,
 474           tax      => $district->{tax_combined} * 100,
 475           source   => 'wa_sales',
 476         });
 477
 478         if ( my $error = $row->insert ) {
 479           dbh->rollback;
 480           local $FS::UID::AutoCommit = 1;
 481           log_error_and_die(
 482             sprintf
 483               "Error inserting cust_main_county row for district %s: %s",
 484               $district->{district},
 485               $error
 486           );
 487         }
 488
 489         $cust_main_county{ $district->{district} } = $row;
 490         $insert_count++;
 491       }
 492
 493       update_non_sales_tax_rows( $taxclass, $district );
 494
 495     } # /foreach $district
 496   } # /foreach $taxclass
 497
 498   dbh->commit;
 499
 500   local $FS::UID::AutoCommit = 1;
 501   log_info_and_warn(
 502     sprintf
 503       "WA tax table update completed. ".
 504       "Inserted %s rows, updated %s rows, identical %s rows",
 505       $insert_count,
 506       $update_count,
 507       $same_count
 508   );
 509
 510 }
 511
 512 =head2 update_non_sales_tax_rows tax_class, $district_href
 513
 514 The customer may have created additional taxes, such as Universal Service Fund.
 515
 516 Ensure the columns for city and county are consistant between
 517 the user-created tax rows and the wa-sales-managed tax rows.
 518
 519 =cut
 520
 521 sub update_non_sales_tax_rows {
 522   my ( $taxclass, $district ) = @_;
 523
 524   return unless ref $district && $district->{district};
 525
 526   my @rows = qsearch( cust_main_county => {
 527     taxclass => $taxclass,
 528     district => $district->{district},
 529     state    => 'WA',
 530     country  => 'US',
 531     source   => { op => '!=', value => 'wa_sales' },
 532   });
 533
 534   for my $row ( @rows ) {
 535     $row->city( uc $district->{city} );
 536     $row->county( uc $district->{county} );
 537
 538     if ( my $error = $row->replace ) {
 539       dbh->rollback;
 540       local $FS::UID::AutoCommit = 1;
 541       log_error_and_die(
 542         sprintf
 543           "Error updating cust_main_county row %s for district %s: %s",
 544           $row->taxnum,
 545           $district->{district},
 546           $error
 547       );
 548     }
 549   }
 550
 551 }
 552
 553 =head2 wa_sales_parse_xlsx_file \%args
 554
 555 Parse given XLSX file for tax district information
 556 Return an arrayref of district information hashrefs
 557
 558 =cut
 559
 560 sub wa_sales_parse_xlsx_file {
 561   my $args = shift;
 562
 563   croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
 564     unless ref $args
 565         && $args->{filename};
 566
 567   # About the file format:
 568   #
 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
 574   #
 575   # The "city" column is being parsed from "Location"
 576
 577   my @columns = qw( city county district tax_local tax_state tax_combined );
 578
 579   log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
 580     unless -r $args->{filename};
 581
 582   my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
 583     or log_error_and_die( "Error parsing XLSX file: $!" );
 584
 585   my $sheet = $xls_parser->{Worksheet}->[0]
 586     or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
 587
 588   my $cells = $sheet->{Cells}
 589     or log_error_and_die( "Unable to read cells in XLSX file" );
 590
 591   # Read the column labels and verify
 592   my %labels =
 593     map{ $columns[$_] => $cells->[2][$_]->{Val} }
 594     0 .. scalar(@columns)-1;
 595
 596   my %expected_labels = (
 597     city         => 'Location',
 598     county       => 'County',
 599     district     => 'Location Code',
 600     tax_local    => 'Local Rate',
 601     tax_state    => 'State Rate',
 602     tax_combined => 'Combined Sales Tax',
 603   );
 604
 605   if (
 606     my @error_labels =
 607       grep { lc $labels{$_} ne lc $expected_labels{$_} }
 608       @columns
 609   ) {
 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{$_}! "
 613       for @error_labels;
 614     log_error_and_die( $error );
 615   }
 616
 617   # Parse the rows into an array of hashes
 618   my @districts;
 619   for my $row ( 3..$sheet->{MaxRow} ) {
 620     my %district = (
 621       map { $columns[$_] => $cells->[$row][$_]->{Val} }
 622       0 .. scalar(@columns)-1
 623     );
 624
 625     if (
 626          $district{city}
 627       && $district{county}
 628       && $district{district}     =~ /^\d+$/
 629       && $district{tax_local}    =~ /^\d?\.\d+$/
 630       && $district{tax_state}    =~ /^\d?\.\d+$/
 631       && $district{tax_combined} =~ /^\d?\.\d+$/
 632     ) {
 633
 634       # For some reason, city may contain line breaks!
 635       $district{city} =~ s/[\r\n]//g;
 636
 637       push @districts, \%district;
 638     } else {
 639       log_warn_and_warn(
 640         "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
 641       );
 642     }
 643
 644   }
 645
 646   log_error_and_die( "No \@districts found in data file!" )
 647     unless @districts;
 648
 649   log_info_and_warn(
 650     sprintf "Parsed %s districts from data file", scalar @districts
 651   );
 652
 653   \@districts;
 654
 655 }
 656
 657 =head2 wa_sales_fetch_xlsx_file \%args
 658
 659 Download data file from WA state DOR to temporary storage,
 660 return filename
 661
 662 =cut
 663
 664 sub wa_sales_fetch_xlsx_file {
 665   my $args = shift;
 666
 667   return
 668     unless conf_tax_district_method()
 669         && conf_tax_district_method() eq 'wa_sales';
 670
 671   croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
 672     unless ref $args
 673         && $args->{temp_dir};
 674
 675   my $url_base = 'https://dor.wa.gov'.
 676                  '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
 677
 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;
 681
 682   my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
 683   my $url = "$url_base/$fn";
 684
 685   my $write_fn = "$args->{temp_dir}/$fn";
 686
 687   log_info_and_warn( "Begin download from url: $url" );
 688
 689   my $ua = LWP::UserAgent->new;
 690   my $res = $ua->get( $url );
 691
 692   log_error_and_die( "Download error: ".$res->status_line )
 693     unless $res->is_success;
 694
 695   local $@;
 696   eval { write_file( $write_fn, $res->decoded_content ); };
 697   log_error_and_die( "Problem writing download to disk: $@" )
 698     if $@;
 699
 700   log_info_and_warn( "Temporary file: $write_fn" );
 701   $write_fn;
 702
 703 }
 704
 705 =head2 wa_sales_update_tax_table_sanity_check
 706
 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.
 709
 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.
 713
 714 If these dupes exist, the sysadmin must solve the problem by hand
 715 with the freeeside-wa-tax-table-resolve script
 716
 717 Returns 1 unless problem sales tax entries are detected
 718
 719 =cut
 720
 721 sub wa_sales_update_tax_table_sanity_check {
 722   FS::cust_main_county->find_wa_tax_dupes ? 0 : 1;
 723 }
 724
 725 sub log {
 726   state $log = FS::Log->new('tax_rate_update');
 727   $log;
 728 }
 729
 730 sub log_info_and_warn {
 731   my $log_message = shift;
 732   warn "$log_message\n";
 733   &log()->info( $log_message );
 734 }
 735
 736 sub log_warn_and_warn {
 737   my $log_message = shift;
 738   warn "$log_message\n";
 739   &log()->warn( $log_message );
 740 }
 741
 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" );
 747 }
 748
 749 sub log_error_and_warn {
 750   my $log_message = shift;
 751   warn "$log_message\n";
 752   &log()->error( $log_message );
 753 }
 754
 755 sub conf_tax_district_method {
 756   state $tax_district_method = FS::Conf->new->config('tax_district_method');
 757   $tax_district_method;
 758 }
 759
 760
 761 1;