RT# 80488 Improve WA tax table update utility

2 files changed, 58 insertions, 4 deletions

diff --git a/FS/FS/Cron/tax_rate_update.pm b/FS/FS/Cron/tax_rate_update.pm
index 5111ef4..fd291af 100755
--- a/FS/FS/Cron/tax_rate_update.pm
+++ b/FS/FS/Cron/tax_rate_update.pm
@@ -334,6 +334,8 @@ sub wa_sales_update_cust_main_county {
   my $update_count = 0;
   my $same_count   = 0;
 
+  $args->{taxname} ||= 'State Sales Tax';
+
   # Work within a SQL transaction
   local $FS::UID::AutoCommit = 0;
 
@@ -410,9 +412,12 @@ sub wa_sales_update_cust_main_county {
       ));
     }
 
-    for my $district ( @{ $args->{tax_districts} } ) {
+    DIST: for my $district ( @{ $args->{tax_districts} } ) {
       if ( my $row = $cust_main_county{ $district->{district} } ) {
 
+        # Strip whitespace from input
+        $district->{$_} =~ s/(^\s+|\s+$)//g for keys %$district;
+
         # District already exists in this taxclass, update if necessary
         #
         # If admin updates value of conf tax_district_taxname, instead of
@@ -424,20 +429,20 @@ sub wa_sales_update_cust_main_county {
           no warnings 'uninitialized';
 
           if (
-            $row->tax == ( $district->{tax_combined} * 100 )
+            sprintf('%.4f',$row->tax) == sprintf('%.4f',($district->{tax_combined} * 100))
             &&    $row->taxname eq    $args->{taxname}
             && uc $row->county  eq uc $district->{county}
             && uc $row->city    eq uc $district->{city}
           ) {
             $same_count++;
-            next;
+            next DIST;
           }
         }
 
         $row->city( uc $district->{city} );
         $row->county( uc $district->{county} );
         $row->taxclass( $taxclass );
-        $row->taxname( $args->{taxname} || undef );
+        $row->taxname( $args->{taxname} );
         $row->tax( $district->{tax_combined} * 100 );
 
         if ( my $error = $row->replace ) {
@@ -485,6 +490,8 @@ sub wa_sales_update_cust_main_county {
         $insert_count++;
       }
 
+      update_non_sales_tax_rows( $taxclass, $district );
+
     } # /foreach $district
   } # /foreach $taxclass
 
@@ -502,6 +509,47 @@ sub wa_sales_update_cust_main_county {
 
 }
 
+=head2 update_non_sales_tax_rows tax_class, $district_href
+
+The customer may have created additional taxes, such as Universal Service Fund.
+
+Ensure the columns for city and county are consistant between
+the user-created tax rows and the wa-sales-managed tax rows.
+
+=cut
+
+sub update_non_sales_tax_rows {
+  my ( $taxclass, $district ) = @_;
+
+  return unless ref $district && $district->{district};
+
+  my @rows = qsearch( cust_main_county => {
+    taxclass => $taxclass,
+    district => $district->{district},
+    state    => 'WA',
+    country  => 'US',
+    source   => { op => '!=', value => 'wa_sales' },
+  });
+
+  for my $row ( @rows ) {
+    $row->city( uc $district->{city} );
+    $row->county( uc $district->{county} );
+
+    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
+      );
+    }
+  }
+
+}
+
 =head2 wa_sales_parse_xlsx_file \%args
 
 Parse given XLSX file for tax district information
diff --git a/FS/bin/freeside-wa-tax-table-update b/FS/bin/freeside-wa-tax-table-update
index b197ac8..53c7324 100755
--- a/FS/bin/freeside-wa-tax-table-update
+++ b/FS/bin/freeside-wa-tax-table-update
@@ -60,6 +60,12 @@ https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/Exce
 
 https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
 
+=item Other district tax rows
+
+When this tool updates the tax tables, any additional tax table rows with
+a district set, where the 'source' column is not 'wa_sales', will have the
+country, state, county, and city values kept updated to match the data
+provided in the state tax tables
 
 =item Address lookup API tool