RT# 80488 WA tax tables maintained with Cron

* Some re-work for WA tax tables when using tax classes
* Relocated functions from wa_tax_rate_update utility script
  into FS::Cron::wa_tax_rate_update library
* Cron downloads tax database monthly instead of daily
* Cron populates entire WA tax table, instead of piecemeal
  for existing customer locations
* Cron will attempt to classify cust_location in WA without
  a determined district, and generate system log errors
  upon failure

diff --git a/FS/FS/Cron/tax_rate_update.pm b/FS/FS/Cron/tax_rate_update.pm
index fec696f..b0745e4 100755 (executable)
--- a/FS/FS/Cron/tax_rate_update.pm
+++ b/FS/FS/Cron/tax_rate_update.pm
@@ -9,106 +9,593 @@ FS::Cron::tax_rate_update
 Cron routine to update city/district sales tax rates in I<cust_main_county>.
 Currently supports sales tax in the state of Washington.
 
 Cron routine to update city/district sales tax rates in I<cust_main_county>.
 Currently supports sales tax in the state of Washington.
 

+=head2 wa_sales
+
+=item Tax Rate Download
+
+Once each month, update the tax tables from the WA DOR website.
+
+=item Customer Address Rate Classification
+
+Find cust_location rows in WA with no tax district.  Try to determine
+a tax district.  Otherwise, generate a log error that address needs
+to be correctd.
+

 =cut
 
 use strict;
 use warnings;
 =cut
 
 use strict;
 use warnings;

-use FS::Conf;
-use FS::Record qw(qsearch qsearchs dbh);
-use FS::cust_main_county;
-use FS::part_pkg_taxclass;
+use feature 'state';
+
+use Exporter;
+our @EXPORT_OK = qw(
+  tax_rate_update
+  wa_sales_update_tax_table
+  wa_sales_log_customer_without_tax_district
+);
+
+use Carp qw(croak);

 use DateTime;
 use DateTime;

-use LWP::UserAgent;

 use File::Temp 'tempdir';
 use File::Slurp qw(read_file write_file);
 use File::Temp 'tempdir';
 use File::Slurp qw(read_file write_file);

+use LWP::UserAgent;
+use Spreadsheet::XLSX;

 use Text::CSV;
 use Text::CSV;

-use Exporter;

 
 

-our @EXPORT_OK = qw(tax_rate_update);
+use FS::Conf;
+use FS::cust_main;
+use FS::cust_main_county;
+use FS::geocode_Mixin;
+use FS::Log;
+use FS::part_pkg_taxclass;
+use FS::Record qw(qsearch qsearchs dbh);
+use FS::upgrade_journal;
+

 our $DEBUG = 0;
 
 our $DEBUG = 0;
 

+=head1 FUNCTIONS
+
+=head2 tax_rate_update
+
+Cron routine for freeside_daily.
+
+Run one of the available cron functions based on conf value tax_district_method
+
+=cut
+

 sub tax_rate_update {
 sub tax_rate_update {

-  my %opt = @_;

 
 

-  my $oldAutoCommit = $FS::UID::AutoCommit;
-  local $FS::UID::AutoCommit = 0;
-  my $dbh = dbh;
-
-  my $conf = FS::Conf->new;
-  my $method = $conf->config('tax_district_method');
-  return if !$method;
-
-  my $taxname = $conf->config('tax_district_taxname') || '';
-
-  FS::cust_main_county->lock_table;
-  if ($method eq 'wa_sales') {
-    # download the update file
-    my $now = DateTime->now;
-    my $yr = $now->year;
-    my $qt = $now->quarter;
-    my $file = "Rates${yr}Q${qt}.zip";
-    my $url = 'http://dor.wa.gov/downloads/Add_Data/'.$file;
-    my $dir = tempdir();
-    chdir($dir);
-    my $ua = LWP::UserAgent->new;
-    warn "Downloading $url...\n" if $DEBUG;
-    my $response = $ua->get($url);
-    if ( ! $response->is_success ) {
-      die $response->status_line;
-    }
-    write_file($file, $response->decoded_content);
+  # Currently only wa_sales is supported
+  my $tax_district_method = conf_tax_district_method();
+
+  return unless $tax_district_method;
+
+  if ( exists &{$tax_district_method} ) {
+    my $func = \&{$tax_district_method};
+    $func->();
+  } else {
+    my $log = FS::Log->new('tax_rate_update');
+    $log->error( "Unhandled tax_district_method($tax_district_method)" );
+  }
+
+}
+
+=head2 wa_sales
+
+Monthly:   Update the complete WA state tax tables
+Every Run: Log errors for cust_location records without a district
+
+=cut
+
+sub wa_sales {
+
+  return
+    unless conf_tax_district_method()
+        && conf_tax_district_method() eq 'wa_sales';
+
+  my $dt_now  = DateTime->now;
+  my $year    = $dt_now->year;
+  my $quarter = $dt_now->quarter;
+
+  my $journal_label =
+    sprintf 'wa_sales_update_tax_table_%sQ%s', $year, $quarter;
+
+  unless ( FS::upgrade_journal->is_done( $journal_label ) ) {
+    local $@;
+
+    eval{ wa_sales_update_tax_table(); };
+    log_error_and_die( "Error updating tax tables: $@" )
+      if $@;
+    FS::upgrade_journal->set_done( $journal_label );
+  }
+
+  wa_sales_log_customer_without_tax_district();
+
+  '';
+
+}

 
 

-    # parse it
-    system('unzip', $file);
-    $file =~ s/\.zip$/.csv/;
-    if (! -f $file) {
-      die "$file not found in zip archive.\n";
+=head2 wa_sales_log_customer_without_tax_district
+
+For any active customers with cust_location records in WA state,
+if a cust_location record has no tax district, find the correct
+district using WA DOR API, or if not possible, generate an error
+message into system log so address can be corrected
+
+=cut
+
+sub wa_sales_log_customer_without_tax_district {
+
+  return
+    unless conf_tax_district_method()
+        && conf_tax_district_method() eq 'wa_sales';
+
+  my %qsearch_cust_location = (
+    table => 'cust_location',
+    select => '
+      cust_location.locationnum,
+      cust_location.custnum,
+      cust_location.address1,
+      cust_location.city,
+      cust_location.state,
+      cust_location.zip
+    ',
+    hashref => {
+      state    => 'WA',
+      district => undef,
+    },
+    addl_from => 'LEFT JOIN cust_main USING (custnum)',
+    extra_sql => sprintf 'AND ( %s ) ', FS::cust_main->active_sql,
+  );
+
+  for my $cust_location ( qsearch( \%qsearch_cust_location )) {
+    local $@;
+    eval {
+      FS::geocode_Mixin::process_district_update(
+        'FS::cust_location',
+        $cust_location->locationnum
+      );
+    };
+
+    if ( $@ ) {
+      log_error_and_warn(
+        sprintf "Failed to classify district for cust_location(%s): %s",
+          $cust_location->locationnum,
+          $@
+      );
+    } else {
+      log_info_and_warn(
+        sprintf "Classified district for cust_location(%s)",
+          $cust_location->locationnum
+      );

     }
     }

-    open my $fh, '<', $file
-      or die "couldn't open $file: $!\n";
-    my $csv = Text::CSV->new;
-    my $header = $csv->getline($fh);
-    $csv->column_names(@$header);
-    # columns we care about are headed 'Code' and 'Rate'
-
-    my $total_changed = 0;
-    my $total_skipped = 0;
-    while ( !$csv->eof ) {
-      my $line = $csv->getline_hr($fh);
-      my $district = $line->{Code} or next;
-      $district = sprintf('%04d', $district);
-      my $tax = sprintf('%.1f', $line->{Rate} * 100);
-      my $changed = 0;
-      my $skipped = 0;
-      # find rate(s) in this country+state+district+taxclass that have the
-      # wa_sales flag and the configured taxname, and haven't been disabled.
-      my @rates = qsearch('cust_main_county', {
-          country   => 'US',
-          state     => 'WA', # this is specific to WA
-          district  => $district,
-          taxname   => $taxname,
-          source    => 'wa_sales',
-          tax       => { op => '>', value => '0' },
-      });
-      foreach my $rate (@rates) {
-        if ( $rate->tax == $tax ) {
-          $skipped++;
-        } else {
-          $rate->set('tax', $tax);
-          my $error = $rate->replace;
-          die "error updating district $district: $error\n" if $error;
-          $changed++;
+
+    sleep 1; # Be polite to WA DOR API
+  }
+
+  for my $cust_location ( qsearch( \%qsearch_cust_location )) {
+    log_error_and_warn(
+      sprintf
+        "Customer address in WA lacking tax district classification. ".
+        "custnum(%s) ".
+        "locationnum(%s) ".
+        "address(%s, %s %s, %s) ".
+        "[https://webgis.dor.wa.gov/taxratelookup/SalesTax.aspx]",
+          map { $cust_location->$_ }
+          qw( custnum locationnum address1 city state zip )
+    );
+  }
+
+}
+
+
+=head2 wa_sales_update_tax_table \%args
+
+Update city/district sales tax rates in L<FS::cust_main_county> from the
+Washington State Department of Revenue published data files.
+
+Creates, or updates, a L<FS::cust_main_county> row for every tax district
+in Washington state. Some cities have different tax rates based on the
+address, within the city.  Because of this, some cities have multiple
+districts.
+
+If tax classes are enabled, a row is created in every tax class for
+every district.
+
+Customer addresses aren't classified into districts here.  Instead,
+when a Washington state address is inserted or changed in L<FS::cust_location>,
+a job is queued for FS::geocode_Mixin::process_district_update, to ask the
+Washington state API which tax district to use for this address.
+
+All arguments are optional:
+
+  filename: Skip file download, and process the specified filename instead
+
+  taxname:  Updated or created records will be set to the given tax name.
+            If not specified, conf value 'tax_district_taxname' is used
+
+  year:     Specify year for tax table download.  Defaults to current year
+
+  quarter:  Specify quarter for tax table download.  Defaults to current quarter
+
+=head3 Washington State Department of Revenue Resources
+
+The state of Washington makes data files available via their public website.
+It's possible the availability or format of these files may change.  As of now,
+the only data file that contains both city and county names is published in
+XLSX format.
+
+=over 4
+
+=item WA Dept of Revenue
+
+https://dor.wa.gov
+
+=item Data file downloads
+
+https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
+
+=item XLSX file example
+
+https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
+
+=item CSV file example
+
+https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
+
+
+=item Address lookup API tool
+
+http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
+
+=back
+
+=cut
+
+sub wa_sales_update_tax_table {
+  my $args = shift;
+
+  croak 'wa_sales_update_tax_table requires \$args hashref'
+    if $args && !ref $args;
+
+  return
+    unless conf_tax_district_method()
+        && conf_tax_district_method() eq 'wa_sales';
+
+  $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname');
+  $args->{year}    ||= DateTime->now->year;
+  $args->{quarter} ||= DateTime->now->quarter;
+
+  log_info_and_warn(
+    "Begin wa_sales_update_tax_table() ".
+    join ', ' => (
+      map{ "$_ => ". ( $args->{$_} || 'undef' ) }
+      sort keys %$args
+    )
+  );
+
+  $args->{temp_dir} ||= tempdir();
+
+  $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
+
+  $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
+
+  wa_sales_update_cust_main_county( $args );
+
+  log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
+}
+
+=head2 wa_sales_update_cust_main_county \%args
+
+Create or update the L<FS::cust_main_county> records with new data
+
+
+
+=cut
+
+sub wa_sales_update_cust_main_county {
+  my $args = shift;
+
+  return
+    unless conf_tax_district_method()
+        && conf_tax_district_method() eq 'wa_sales';
+
+  croak 'wa_sales_update_cust_main_county requires $args hashref'
+    unless ref $args
+        && ref $args->{tax_districts};
+
+  my $insert_count = 0;
+  my $update_count = 0;
+  my $same_count   = 0;
+
+  # Work within a SQL transaction
+  local $FS::UID::AutoCommit = 0;
+
+  for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
+    $taxclass ||= undef; # trap empty string when taxclasses are disabled
+
+    my %cust_main_county =
+      map { $_->district => $_ }
+      qsearch(
+        cust_main_county => {
+          district => { op => '!=', value => undef },
+          state    => 'WA',
+          country  => 'US',
+          source   => 'wa_sales',
+          taxclass => $taxclass,

         }
         }

-      } # foreach $taxclass
-      print "$district: updated $changed, skipped $skipped\n"
-        if $DEBUG and ($changed or $skipped);
-      $total_changed += $changed;
-      $total_skipped += $skipped;
+      );
+
+    for my $district ( @{ $args->{tax_districts} } ) {
+      if ( my $row = $cust_main_county{ $district->{district} } ) {
+
+        # District already exists in this taxclass, update if necessary
+        #
+        # If admin updates value of conf tax_district_taxname, instead of
+        # creating an entire separate set of tax rows with
+        # the new taxname, update the taxname on existing records
+
+        if (
+          $row->tax == ( $district->{tax_combined} * 100 )
+          && $row->taxname eq $args->{taxname}
+        ) {
+          $same_count++;
+          next;
+        }
+
+        $row->city( uc $district->{city} );
+        $row->county( uc $district->{county} );
+        $row->taxclass( $taxclass );
+        $row->taxname( $args->{taxname} || undef );
+        $row->tax( $district->{tax_combined} * 100 );
+
+        if ( my $error = $row->replace ) {
+          dbh->rollback;
+          local $FS::UID::AutoCommit = 1;
+          log_error_and_die(
+            sprintf
+              "Error updating cust_main_county row %s for district %s: %s",
+              $row->taxnum,
+              $district->{district},
+              $error
+          );
+        }
+
+        $update_count++;
+
+      } else {
+
+        # District doesn't exist, create row
+
+        my $row = FS::cust_main_county->new({
+          district => $district->{district},
+          city     => uc $district->{city},
+          county   => uc $district->{county},
+          state    => 'WA',
+          country  => 'US',
+          taxclass => $taxclass,
+          taxname  => $args->{taxname} || undef,
+          tax      => $district->{tax_combined} * 100,
+          source   => 'wa_sales',
+        });
+
+        if ( my $error = $row->insert ) {
+          dbh->rollback;
+          local $FS::UID::AutoCommit = 1;
+          log_error_and_die(
+            sprintf
+              "Error inserting cust_main_county row for district %s: %s",
+              $district->{district},
+              $error
+          );
+        }
+
+        $cust_main_county{ $district->{district} } = $row;
+        $insert_count++;
+      }
+
+    } # /foreach $district
+  } # /foreach $taxclass
+
+  dbh->commit;
+
+  local $FS::UID::AutoCommit = 1;
+  log_info_and_warn(
+    sprintf
+      "WA tax table update completed. ".
+      "Inserted %s rows, updated %s rows, identical %s rows",
+      $insert_count,
+      $update_count,
+      $same_count
+  );
+
+}
+
+=head2 wa_sales_parse_xlsx_file \%args
+
+Parse given XLSX file for tax district information
+Return an arrayref of district information hashrefs
+
+=cut
+
+sub wa_sales_parse_xlsx_file {
+  my $args = shift;
+
+  croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
+    unless ref $args
+        && $args->{filename};
+
+  # About the file format:
+  #
+  # The current spreadsheet contains the following @columns.
+  # Rows 1 and 2 are a marquee header
+  # Row 3 is the column labels.  We will test these to detect
+  #   changes in the data format
+  # Rows 4+ are the tax district data
+  #
+  # The "city" column is being parsed from "Location"
+
+  my @columns = qw( city county district tax_local tax_state tax_combined );
+
+  log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
+    unless -r $args->{filename};
+
+  my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
+    or log_error_and_die( "Error parsing XLSX file: $!" );
+
+  my $sheet = $xls_parser->{Worksheet}->[0]
+    or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
+
+  my $cells = $sheet->{Cells}
+    or log_error_and_die( "Unable to read cells in XLSX file" );
+
+  # Read the column labels and verify
+  my %labels =
+    map{ $columns[$_] => $cells->[2][$_]->{Val} }
+    0 .. scalar(@columns)-1;
+
+  my %expected_labels = (
+    city         => 'Location',
+    county       => 'County',
+    district     => 'Location Code',
+    tax_local    => 'Local Rate',
+    tax_state    => 'State Rate',
+    tax_combined => 'Combined Sales Tax',
+  );
+
+  if (
+    my @error_labels =
+      grep { lc $labels{$_} ne lc $expected_labels{$_} }
+      @columns
+  ) {
+    my $error = "Error parsing XLS file - ".
+                "Data format may have been updated with WA DOR! ";
+    $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
+      for @error_labels;
+    log_error_and_die( $error );
+  }
+
+  # Parse the rows into an array of hashes
+  my @districts;
+  for my $row ( 3..$sheet->{MaxRow} ) {
+    my %district = (
+      map { $columns[$_] => $cells->[$row][$_]->{Val} }
+      0 .. scalar(@columns)-1
+    );
+
+    if (
+         $district{city}
+      && $district{county}
+      && $district{district}     =~ /^\d+$/
+      && $district{tax_local}    =~ /^\d?\.\d+$/
+      && $district{tax_state}    =~ /^\d?\.\d+$/
+      && $district{tax_combined} =~ /^\d?\.\d+$/
+    ) {
+
+      # For some reason, city may contain line breaks!
+      $district{city} =~ s/[\r\n]//g;
+
+      push @districts, \%district;
+    } else {
+      log_warn_and_warn(
+        "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
+      );

     }
     }

-    print "Updated $total_changed tax rates.\nSkipped $total_skipped unchanged rates.\n" if $DEBUG;
-    dbh->commit;
-  } # else $method isn't wa_sales, no other methods exist yet
-  '';
+
+  }
+
+  log_error_and_die( "No \@districts found in data file!" )
+    unless @districts;
+
+  log_info_and_warn(
+    sprintf "Parsed %s districts from data file", scalar @districts
+  );
+
+  \@districts;
+
+}
+
+=head2 wa_sales_fetch_xlsx_file \%args
+
+Download data file from WA state DOR to temporary storage,
+return filename
+
+=cut
+
+sub wa_sales_fetch_xlsx_file {
+  my $args = shift;
+
+  return
+    unless conf_tax_district_method()
+        && conf_tax_district_method() eq 'wa_sales';
+
+  croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
+    unless ref $args
+        && $args->{temp_dir};
+
+  my $url_base = 'https://dor.wa.gov'.
+                 '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
+
+  my $year    = $args->{year}    || DateTime->now->year;
+  my $quarter = $args->{quarter} || DateTime->now->quarter;
+  $year = substr( $year, 2, 2 ) if $year >= 1000;
+
+  my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
+  my $url = "$url_base/$fn";
+
+  my $write_fn = "$args->{temp_dir}/$fn";
+
+  log_info_and_warn( "Begin download from url: $url" );
+
+  my $ua = LWP::UserAgent->new;
+  my $res = $ua->get( $url );
+
+  log_error_and_die( "Download error: ".$res->status_line )
+    unless $res->is_success;
+
+  local $@;
+  eval { write_file( $write_fn, $res->decoded_content ); };
+  log_error_and_die( "Problem writing download to disk: $@" )
+    if $@;
+
+  log_info_and_warn( "Temporary file: $write_fn" );
+  $write_fn;
+
+}
+
+sub log {
+  state $log = FS::Log->new('tax_rate_update');
+  $log;

 }
 
 }
 

+sub log_info_and_warn {
+  my $log_message = shift;
+  warn "$log_message\n";
+  &log()->info( $log_message );
+}
+
+sub log_warn_and_warn {
+  my $log_message = shift;
+  warn "$log_message\n";
+  &log()->warn( $log_message );
+}
+
+sub log_error_and_die {
+  my $log_message = shift;
+  &log()->error( $log_message );
+  die( "$log_message\n" );
+}
+
+sub log_error_and_warn {
+  my $log_message = shift;
+  warn "$log_message\n";
+  &log()->error( $log_message );
+}
+
+sub conf_tax_district_method {
+  state $tax_district_method = FS::Conf->new->config('tax_district_method');
+  $tax_district_method;
+}
+
+

 1;
 1;

diff --git a/bin/wa_tax_rate_update b/bin/wa_tax_rate_update
index c50a777..fef126d 100644 (file)
--- a/bin/wa_tax_rate_update
+++ b/bin/wa_tax_rate_update
@@ -1,4 +1,4 @@
-#!/usr/bin/perl
+#!/usr/bin/env perl

 
 =head1 NAME
 
 
 =head1 NAME
 


@@ -10,27 +10,32 @@ Tool to update city/district sales tax rates in I<cust_main_county> from
 the Washington State Department of Revenue website.
 
 Creates, or updates, a L<FS::cust_main_county> row for every tax district
 the Washington State Department of Revenue website.
 
 Creates, or updates, a L<FS::cust_main_county> row for every tax district

-in Washington state.  Some cities have different tax rates based on the
-address, within the city.  Because of this, some cities may have multiple
-rows defined.
+in Washington state. Some cities have different tax rates based on the
+address, within the city.  Because of this, some cities have
+district.

 
 

-When a Washington state address is inserted or changed in L<FS::cust_location>,
+If tax classes are enabled, a row is created in every tax class for
+every district.
+
+Customer addresses aren't classified into districts here.  Instead,
+when a Washington state address is inserted or changed in L<FS::cust_location>,

 a job is queued for FS::geocode_Mixin::process_district_update, to ask the
 Washington state API which tax district to use for this address.
 
 Options:
 
 a job is queued for FS::geocode_Mixin::process_district_update, to ask the
 Washington state API which tax district to use for this address.
 
 Options:
 

--f <filename>: Skip downloading, and process the given excel file
+  -f <filename>: Skip downloading, and process the given excel file

 
 

--c <taxclass>: Updated or create records within given tax class,
-   If not specified, taxclass will be set as NULL
+  -t <taxname>:  Updated or created records will be set to the given tax name.
+                 If not specified, conf value 'tax_district_taxname' will be used

 
 

--t <taxname>: Updated or created records will be set to the given tax name.
-  If not specified, conf value 'tax_district_taxname' will be used
+  -y <year>:     Specify year for tax table - defaults to current year

 
 

--y <year>: Specify year for tax table - defaults to current year
+  -q <quarter>:  Specify quarter for tax table - defaults to current quarter

 
 

--q <quarter>: Specify quarter for tax table - defaults to current quarter
+  -l <lookup>:   Attempt to look up the tax district classification for
+                 unclassified cust_location records in Washington.  Will
+                 notify of records that cannot be classified

 
 =head1 Washington State Department of Revenue Resources
 
 
 =head1 Washington State Department of Revenue Resources
 


@@ -67,297 +72,76 @@ use warnings;
 
 our $VERSION = '0.02'; # Make Getopt:Std happy
 
 
 our $VERSION = '0.02'; # Make Getopt:Std happy
 

-use Data::Dumper;
-use DateTime;
-use File::Temp 'tempdir';
-use File::Slurp qw(read_file write_file);

 use Getopt::Std;
 use Getopt::Std;

-use LWP::UserAgent;
-use Spreadsheet::XLSX;

 
 

-use FS::cust_main_county;
+use FS::Cron::tax_rate_update qw(
+  wa_sales_update_tax_table
+  wa_sales_log_customer_without_tax_district
+);

 use FS::Log;
 use FS::Log;

-use FS::Record qw(qsearch qsearchs dbh);

 use FS::UID qw(adminsuidsetup);
 
 my %opts;
 use FS::UID qw(adminsuidsetup);
 
 my %opts;

-getopts( 'c:t:y:q:f:', \%opts );
+getopts( 't:y:q:f:l', \%opts );
+

 my $user = shift
   or die HELP_MESSAGE();
 
 adminsuidsetup( $user )
   or die "bad username '$user'\n";
 
 my $user = shift
   or die HELP_MESSAGE();
 
 adminsuidsetup( $user )
   or die "bad username '$user'\n";
 

-my $temp_dir = tempdir();

 my $log = FS::Log->new('wa_tax_rate_update');
 
 my $log = FS::Log->new('wa_tax_rate_update');
 

-$opts{t} ||= FS::Conf->new->config('tax_district_taxname');
-
-log_info_and_warn( "Set taxclass=$opts{c}" ) if $opts{c};
-log_info_and_warn( "Set taxname=$opts{t}"  ) if $opts{t};
-
-my $xls_fn = $opts{f} || fetch_xlsx_file();
-my $tax_districts = parse_xlsx_file( $xls_fn );
-
-update_cust_main_county( $tax_districts );
-
-# print Dumper( \%opts );
-# for ( 0..5 ) {
-#   print Dumper( $tax_districts->[$_] );
-# }
-
-log_info_and_warn( "Finished" );
-exit;
-
-
-sub update_cust_main_county {
-  my $tax_districts = shift;
-
-  #
-  # Working from an assumption tax tables may be loaded multiple times,
-  # each with a different tax class,
-  #  re: comments on FS::geocode_Mixin::process_district_update
-  #
-
-  my %cust_main_county =
-    map { $_->district => $_ }
-    qsearch(
-      cust_main_county => {
-        district => { op => '!=', value => undef },
-        state    => 'WA',
-        source   => 'wa_sales',
-        taxclass => $opts{c},
-      }
-    );
-
-  # Work within a SQL transaction
-  local $FS::UID::AutoCommit = 0;
-
-  my $insert_count = 0;
-  my $update_count = 0;
-
-  for my $district ( @$tax_districts ) {
-    if ( my $row = $cust_main_county{ $district->{district} } ) {
-
-      $row->city( uc $district->{city} );
-      $row->county( uc $district->{county} );
-      $row->taxclass( $opts{c} || undef );
-      $row->taxname( $opts{t} || undef );
-      $row->tax( $district->{tax_combined} * 100 );
-
-      if ( my $error = $row->replace ) {
-        dbh->rollback;
-        local $FS::UID::AutoCommit = 1;
-        log_error_and_die(
-          sprintf
-            "Error updating cust_main_county row %s for district %s: %s",
-            $row->taxnum,
-            $district->{district},
-            $error
-        );
-      }
-
-      $update_count++;
-
-    } else {
-      my $row = FS::cust_main_county->new({
-        district => $district->{district},
-        city     => uc $district->{city},
-        county   => uc $district->{county},
-        state    => 'WA',
-        country  => 'US',
-        taxclass => $opts{c} || undef,
-        taxname  => $opts{t} || undef,
-        tax      => $district->{tax_combined} * 100,
-        source   => 'wa_sales',
-      });
-
-      if ( my $error = $row->insert ) {
-        dbh->rollback;
-        local $FS::UID::AutoCommit = 1;
-        log_error_and_die(
-          sprintf
-            "Error inserting cust_main_county row for district %s: %s",
-            $district->{district},
-            $error
-        );
-      }
-
-      $cust_main_county{ $district->{district} } = $row;
-      $insert_count++;
-    }
-  }
-
-  dbh->commit;
-
-  local $FS::UID::AutoCommit = 1;
-  log_info_and_warn(
-    sprintf
-      "WA tax table update completed. Inserted %s rows, updated %s rows",
-      $insert_count,
-      $update_count
-  );
-
-}
-
-sub parse_xlsx_file {
-  my $parse_fn = shift;
-
-  # About the file format:
-  #
-  # The current spreadsheet contains the following @columns.
-  # Rows 1 and 2 are a marquee header
-  # Row 3 is the column labels.  We will test these to detect
-  #   changes in the data format
-  # Rows 4+ are the tax district data
-  #
-  # The "city" column is being parsed from "Location"
-
-  my @columns = qw( city county district tax_local tax_state tax_combined );
-
-  log_error_and_die( "Unable to access XLSX file: $parse_fn" )
-    unless -r $parse_fn;
-
-  my $xls_parser = Spreadsheet::XLSX->new( $parse_fn )
-    or log_error_and_die( "Error parsing XLSX file: $!" );
-
-  my $sheet = $xls_parser->{Worksheet}->[0]
-    or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
-
-  my $cells = $sheet->{Cells}
-    or log_error_and_die( "Unable to read cells in XLSX file" );
-
-  # Read the column labels and verify
-  my %labels =
-    map{ $columns[$_] => $cells->[2][$_]->{Val} }
-    0 .. scalar(@columns)-1;
-
-  my %expected_labels = (
-    city         => 'Location',
-    county       => 'County',
-    district     => 'Location Code',
-    tax_local    => 'Local Rate',
-    tax_state    => 'State Rate',
-    tax_combined => 'Combined Sales Tax',
-  );
-
-  if (
-    my @error_labels =
-      grep { lc $labels{$_} ne lc $expected_labels{$_} }
-      @columns
-  ) {
-    my $error = "Error parsing XLS file - ".
-                "Data format may have been updated with WA DOR! ";
-    $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
-      for @error_labels;
-    log_error_and_die( $error );
-  }
-
-  # Parse the rows into an array of hashes
-  my @districts;
-  for my $row ( 3..$sheet->{MaxRow} ) {
-    my %district = (
-      map { $columns[$_] => $cells->[$row][$_]->{Val} }
-      0 .. scalar(@columns)-1
-    );
-
-    if (
-         $district{city}
-      && $district{county}
-      && $district{district}     =~ /^\d+$/
-      && $district{tax_local}    =~ /^\d?\.\d+$/
-      && $district{tax_state}    =~ /^\d?\.\d+$/
-      && $district{tax_combined} =~ /^\d?\.\d+$/
-    ) {
-
-      # For some reason, city may contain line breaks!
-      $district{city} =~ s/[\r\n]//g;
-
-      push @districts, \%district;
-    } else {
-      log_warn_and_warn(
-        "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
-      );
-    }
+$log->info('Begin wa_tax_rate_update');

 
 

+{
+  local $@;
+  eval {
+    wa_sales_update_tax_table({
+      $opts{f} ? ( filename => $opts{f} ) : (),
+      $opts{t} ? ( taxname  => $opts{t} ) : (),
+      $opts{y} ? ( year     => $opts{y} ) : (),
+      $opts{q} ? ( quarter  => $opts{q} ) : (),
+    });
+  };
+
+  if ( $@ ) {
+    $log->error( "Error: $@" );
+    warn "Error: $@\n";
+  } else {
+    $log->info( 'Finished wa_tax_rate_update' );
+    warn "Finished wa_tax_rate_update\n";

   }
   }

-
-  log_error_and_die( "No \@districts found in data file!" )
-    unless @districts;
-
-  log_info_and_warn(
-    sprintf "Parsed %s districts from data file", scalar @districts
-  );
-
-  \@districts;

 }
 
 }
 

-sub fetch_xlsx_file {
-  # Download file to temporary storage, return filename
-
-  my $url_base = 'https://dor.wa.gov'.
-                 '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
-
-  my $year = $opts{y}    || DateTime->now->year;
-  my $quarter = $opts{q} || DateTime->now->quarter;
-  $year = substr( $year, 2, 2 ) if $year >= 1000;
-  my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
-  my $url = "$url_base/$fn";
-
-  my $write_fn = "$temp_dir/$fn";
-
-  log_info_and_warn( "Begin download from url: $url" );

 
 

-  my $ua = LWP::UserAgent->new;
-  my $res = $ua->get( $url );
+if ( $opts{l} ) {
+  $log->info( 'Begin wa_sales_log_customer_without_tax_district' );

 
 

-  log_error_and_die( "Download error: ".$res->status_line )
-    unless $res->is_success;
+  wa_sales_log_customer_without_tax_district();

 
 

-  local $@;
-  eval { write_file( $write_fn, $res->decoded_content ); };
-  log_error_and_die( "Problem writing download to disk: $@" )
-    if $@;
-
-  log_info_and_warn( "Temporary file: $write_fn" );
-  $write_fn;
+  $log->info( 'Finished wa_sales_log_customer_without_tax_district' );
+  warn "Finished wa_sales_log_customer_without_tax_district\n";

 }
 
 }
 

+exit;

 
 sub HELP_MESSAGE {
   print "
     Tool to update city/district sales tax rates in I<cust_main_county> from
     the Washington State Department of Revenue website.
 
 
 sub HELP_MESSAGE {
   print "
     Tool to update city/district sales tax rates in I<cust_main_county> from
     the Washington State Department of Revenue website.
 

-    Usage: [-f filename] [-c taxclass] [-t taxname] [-y year] [-q quarter]
+    Usage: [-f filename] [-t taxname] [-y year] [-q quarter] [-l] freeside_username

 
     Optional Options:
       -f filename   Skip download, and process the specified filename
 
     Optional Options:
       -f filename   Skip download, and process the specified filename

-      -c taxclass   Update or create records within this taxclass
-                    defaults as NULL

       -t taxname    Apply tax name value to created or updated records
                     defaults as conf value 'tax_district_taxname'
       -y year       Year for data file download
       -q quarter    Quarter of data file to download
       -t taxname    Apply tax name value to created or updated records
                     defaults as conf value 'tax_district_taxname'
       -y year       Year for data file download
       -q quarter    Quarter of data file to download

+      -t lookup     Try to fix cust_location records without a district

 
   ";
   exit;
 }
 
 
   ";
   exit;
 }
 

-sub log_info_and_warn {
-  my $log_message = shift;
-  warn "$log_message\n";
-  $log->info( $log_message );
-}
-
-sub log_warn_and_warn {
-  my $log_message = shift;
-  warn "$log_message\n";
-  $log->warn( $log_message );
-}
-
-sub log_error_and_die {
-  my $log_message = shift;
-  $log->error( $log_message );
-  die( "$log_message\n" );
-}