track taxes created by Washington sales tax lookup so they can be updated in place, #40645

4 files changed, 50 insertions, 9 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index bfd3131..738dc53 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -2272,6 +2272,7 @@ sub tables_hashref {
         'taxname',  'varchar', 'NULL', $char_d, '', '', 
         'setuptax',    'char', 'NULL',       1, '', '', # Y = setup tax exempt
         'recurtax',    'char', 'NULL',       1, '', '', # Y = recur tax exempt
+        'source',   'varchar', 'NULL', $char_d, '', '',
       ],
       'primary_key' => 'taxnum',
       'unique' => [],
diff --git a/FS/FS/Upgrade.pm b/FS/FS/Upgrade.pm
index f3f0798..6cec4f8 100644
--- a/FS/FS/Upgrade.pm
+++ b/FS/FS/Upgrade.pm
@@ -458,6 +458,9 @@ sub upgrade_data {
 
     #populate tax statuses
     'tax_status' => [],
+
+    #mark certain taxes as system-maintained
+    'cust_main_county' => [],
   ;
 
   \%hash;
diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm
index 652ff33..3c355e8 100644
--- a/FS/FS/cust_main_county.pm
+++ b/FS/FS/cust_main_county.pm
@@ -12,6 +12,7 @@ use FS::cust_pkg;
 use FS::part_pkg;
 use FS::cust_tax_exempt;
 use FS::cust_tax_exempt_pkg;
+use FS::upgrade_journal;
 
 @EXPORT_OK = qw( regionselector );
 
@@ -78,6 +79,9 @@ currently supported:
 
 =item recurtax - if 'Y', this tax does not apply to recurring fees
 
+=item source - the tax lookup method that created this tax record. For records
+created manually, this will be null.
+
 =back
 
 =head1 METHODS
@@ -132,6 +136,7 @@ sub check {
     || $self->ut_textn('taxname')
     || $self->ut_enum('setuptax', [ '', 'Y' ] )
     || $self->ut_enum('recurtax', [ '', 'Y' ] )
+    || $self->ut_textn('source')
     || $self->SUPER::check
     ;
 
@@ -674,6 +679,31 @@ END
 
 }
 
+sub _upgrade_data {
+  my $class = shift;
+  # assume taxes in Washington with district numbers, and null name, or 
+  # named 'sales tax', are looked up via the wa_sales method. mark them.
+  my $journal = 'cust_main_county__source_wa_sales';
+  if (!FS::upgrade_journal->is_done($journal)) {
+    my @taxes = qsearch({
+        'table'     => 'cust_main_county',
+        'extra_sql' => " WHERE tax > 0 AND country = 'US' AND state = 'WA'".
+                       " AND district IS NOT NULL AND ( taxname IS NULL OR ".
+                       " taxname ~* 'sales tax' )",
+    });
+    if ( @taxes ) {
+      warn "Flagging Washington state sales taxes: ".scalar(@taxes)." records.\n";
+      foreach (@taxes) {
+        $_->set('source', 'wa_sales');
+        my $error = $_->replace;
+        die $error if $error;
+      }
+    }
+    FS::upgrade_journal->set_done($journal);
+  }
+  '';
+}
+
 =back
 
 =head1 BUGS
diff --git a/FS/FS/geocode_Mixin.pm b/FS/FS/geocode_Mixin.pm
index bc8c118..8d0c7ea 100644
--- a/FS/FS/geocode_Mixin.pm
+++ b/FS/FS/geocode_Mixin.pm
@@ -248,6 +248,8 @@ Queueable function to update the tax district code using the selected method
 
 =cut
 
+# this is run from the job queue so I'm not transactionizing it.
+
 sub process_district_update {
   my $class = shift;
   my $id = shift;
@@ -273,15 +275,20 @@ sub process_district_update {
 
     my %hash = map { $_ => $tax_info->{$_} } 
       qw( district city county state country );
-    $hash{'taxname'} = '';
-
-    my $old = qsearchs('cust_main_county', \%hash);
-    if ( $old ) {
-      my $new = new FS::cust_main_county { $old->hash, %$tax_info };
-      warn "updating tax rate for district ".$tax_info->{'district'} if $DEBUG;
-      $error = $new->replace($old);
-    }
-    else {
+    $hash{'source'} = $method; # apply the update only to taxes we maintain
+
+    my @old = qsearch('cust_main_county', \%hash);
+    if ( @old ) {
+      foreach my $cust_main_county (@old) {
+        warn "updating tax rate #".$cust_main_county->taxnum.
+          " for district ".$tax_info->{'district'} if $DEBUG;
+        # update the tax rate only
+        $cust_main_county->set('tax', $tax_info->{'tax'});
+        $error ||= $cust_main_county->replace;
+      }
+    } else {
+      # make a new tax record, and mark it so we can find it later
+      $tax_info->{'source'} = $method;
       my $new = new FS::cust_main_county $tax_info;
       warn "creating tax rate for district ".$tax_info->{'district'} if $DEBUG;
       $error = $new->insert;