RT# 83402 CLI tool to repair wa state tax tables

3 files changed, 364 insertions, 0 deletions

diff --git a/FS/FS/Cron/tax_rate_update.pm b/FS/FS/Cron/tax_rate_update.pm
index bb9d4d13d..4383bc501 100755
--- a/FS/FS/Cron/tax_rate_update.pm
+++ b/FS/FS/Cron/tax_rate_update.pm
@@ -294,6 +294,11 @@ sub wa_sales_update_tax_table {
     )
   );
 
+  # The checks themselves will fully log details about the problem,
+  # so simple error message is sufficient here
+  log_error_and_die('abort tax table update, sanity checks failed')
+    unless wa_sales_update_tax_table_sanity_check();
+
   $args->{temp_dir} ||= tempdir();
 
   $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
@@ -635,6 +640,26 @@ sub wa_sales_fetch_xlsx_file {
 
 }
 
+=head2 wa_sales_update_tax_table_sanity_check
+
+There should be no duplicate tax table entries in the tax table,
+with the same district value, within a tax class, where source=wa_sales.
+
+If there are, custome taxes may have been user-entered in the
+freeside UI, and incorrectly labelled as source=wa_sales.  Or, the
+dupe record may have been created by issues with older wa_sales code.
+
+If these dupes exist, the sysadmin must solve the problem by hand
+with the freeeside-wa-tax-table-resolve script
+
+Returns 1 unless problem sales tax entries are detected
+
+=cut
+
+sub wa_sales_update_tax_table_sanity_check {
+  FS::cust_main_county->find_wa_tax_dupes ? 0 : 1;
+}
+
 sub log {
   state $log = FS::Log->new('tax_rate_update');
   $log;
@@ -655,6 +680,7 @@ sub log_warn_and_warn {
 sub log_error_and_die {
   my $log_message = shift;
   &log()->error( $log_message );
+  warn( "$log_message\n" );
   die( "$log_message\n" );
 }
 
diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm
index 2bd7342ca..958233440 100644
--- a/FS/FS/cust_main_county.pm
+++ b/FS/FS/cust_main_county.pm
@@ -562,6 +562,40 @@ sub taxline {
   return $tax_item;
 }
 
+=head1 find_wa_tax_dupes
+
+Return a list of cust_main_county Record objects that are detected
+as duplicate washington state sales tax rows (source=wa_state)
+within their respective tax classes
+
+=cut
+
+sub find_wa_tax_dupes {
+  my %cust_main_county;
+  my @dupes;
+
+  for my $row ( qsearch( cust_main_county => { source => 'wa_sales' } ) ) {
+    my $taxclass = $row->taxclass || 'none';
+    $cust_main_county{$taxclass} ||= {};
+
+    my $district = $row->district || 'none';
+    $cust_main_county{$taxclass}->{$district} ||= [];
+
+    push @{ $cust_main_county{$taxclass}->{$district} }, $row;
+  }
+
+  for my $taxclass ( keys %cust_main_county ) {
+    for my $district ( keys %{ $cust_main_county{$taxclass} } ) {
+      my $tax_rows = $cust_main_county{$taxclass}->{$district};
+      if ( scalar @$tax_rows > 1 ) {
+        push @dupes, @$tax_rows;
+      }
+    }
+  }
+
+  @dupes;
+}
+
 =back
 
 =head1 SUBROUTINES
diff --git a/FS/bin/freeside-wa-tax-table-resolve b/FS/bin/freeside-wa-tax-table-resolve
new file mode 100755
index 000000000..fa6db3e39
--- /dev/null
+++ b/FS/bin/freeside-wa-tax-table-resolve
@@ -0,0 +1,304 @@
+#!/usr/bin/env perl
+use v5.10;
+use strict;
+use warnings;
+
+our $VERSION = '1.0';
+
+use Data::Dumper;
+use FS::cust_main_county;
+use FS::Log;
+use FS::Record qw( qsearch qsearchs );
+use FS::UID qw( adminsuidsetup );
+use Getopt::Long;
+use Pod::Usage;
+
+# Begin transaction
+local $FS::UID::AutoCommit = 0;
+
+my(
+  $dbh,
+  $freeside_user,
+  $opt_check,
+  @opt_merge,
+  @opt_set_source_null,
+);
+
+GetOptions(
+  'check'             => \$opt_check,
+  'merge=s'           => \@opt_merge,
+  'set-source-null=s' => \@opt_set_source_null,
+);
+@opt_merge = split(',',join(',',@opt_merge));
+@opt_set_source_null = split(',',join(',',@opt_set_source_null));
+
+
+# say Dumper({
+#   check => $opt_check,
+#   merge => \@opt_merge,
+#   set_source_numm => \@opt_set_source_null,
+# });
+
+validate_opts();
+
+$dbh = adminsuidsetup( $freeside_user )
+  or die "Bad  username: $freeside_user\n";
+
+my $log = FS::Log->new('freeside-wa-tax-table-resolve');
+
+if ( $opt_check ) {
+  check();
+} elsif ( @opt_merge ) {
+  merge();
+} elsif ( @opt_set_source_null ) {
+  set_source_null();
+} else {
+  error_and_help('No options selected');
+}
+
+# Commit transaction
+$dbh->commit;
+local $FS::UID::AutoCommit = 1;
+
+exit;
+
+
+sub set_source_null {
+  my @cust_main_county;
+  for my $taxnum ( @opt_set_source_null ) {
+    my $row = qsearchs( cust_main_county => { taxnum => $taxnum } );
+    if ( $row ) {
+      push @cust_main_county, $row;
+    } else {
+      error_and_help("Invalid taxnum specified: $taxnum");
+    }
+  }
+
+  say "=== Specified tax rows ===";
+  print_taxnum($_) for @cust_main_county;
+
+  confirm_to_continue("
+
+    The source column will be set to NULL for each of the
+    tax rows listed.  The tax row will no longer be managed
+    by the washington state sales tax table update utilities.
+
+    The listed taxes should be manually created taxes, that
+    were never intended to be managed by the auto updater.
+
+  ");
+
+  for my $row ( @cust_main_county ) {
+
+    $row->setfield( source => undef );
+    my $error = $row->replace;
+
+    if ( $error ) {
+      $dbh->rollback;
+
+      my $message = sprintf 'Error setting source=null taxnum %s: %s',
+          $row->taxnum, $error;
+
+      $log->error( $message );
+      say $message;
+
+      return;
+    }
+
+    my $message = sprintf 'Source column set to null for taxnum %s',
+      $row->taxnum;
+
+    $log->warn( $message );
+    say $message;
+  }
+}
+
+sub merge {
+  my $source = qsearchs( cust_main_county => { taxnum => $opt_merge[0] });
+  my $target = qsearchs( cust_main_county => { taxnum => $opt_merge[1] });
+
+  error_and_help("Invalid source taxnum: $opt_merge[0]")
+    unless $source;
+  error_and_help("Invalid target taxnum: $opt_merge[1]")
+    unless $target;
+
+  local $| = 1; # disable output buffering
+
+  say '==== source row ====';
+  print_taxnum( $source );
+
+  say '==== target row ====';
+  print_taxnum( $target );
+
+  confirm_to_continue("
+  
+    The source tax will be merged into the target tax.
+    All references to the source tax on customer invoices
+    will be replaced with references to the target tax.
+    The source tax will be removed from the tax tables.
+
+  ");
+
+  local $@;
+  eval { $source->_merge_into( $target, { identical_record_check => 0 } ) };
+  if ( $@ ) {
+    $dbh->rollback;
+  
+    my $message = sprintf 'Failed to merge wa sales tax %s into %s: %s',
+        $source->taxnum, $target->taxnum, $@;
+
+    say $message;
+    $log->error( $message );
+
+  } else {
+    my $message = sprintf 'Merged wa sales tax %s into %s - success',
+        $source->taxnum, $target->taxnum;
+
+    say $message;
+    $log->warn( $message );
+  }
+}
+
+sub validate_opts {
+
+  $freeside_user = shift @ARGV
+    or error_and_help('freeside_user parameter required');
+
+  if ( @opt_merge ) {
+    error_and_help(( '--merge requires a comma separated list of two taxnums'))
+      unless scalar(@opt_merge) == 2
+          && $opt_merge[0] =~ /^\d+$/
+          && $opt_merge[1] =~ /^\d+$/;
+  }
+
+  for my $taxnum ( @opt_set_source_null ) {
+    if ( $taxnum =~ /\D/ ) {
+      error_and_help( "Invalid taxnum ($taxnum)" );
+    }
+  }
+}
+
+sub check {
+  my @dupes = FS::cust_main_county->find_wa_tax_dupes;
+
+  unless ( @dupes ) {
+    say 'No duplicate tax rows detected for WA sales tax districts';
+    return;
+  }
+
+  say sprintf '=== Detected %s duplicate tax rows ===', scalar @dupes;
+
+  print_taxnum($_) for @dupes;
+
+  $log->error(
+    sprintf 'Detected %s duplicate wa sales tax rows: %s',
+      scalar( @dupes ),
+      join( ',', map{ $_->taxnum } @dupes )
+  );
+
+}
+
+sub print_taxnum {
+  my $taxnum = shift;
+  die unless ref $taxnum;
+
+  say 'taxnum: '.$taxnum->taxnum;
+  say join "\n" => (
+    map { sprintf('  %s:%s', $_, $taxnum->$_ ) }
+    qw/district city county state tax taxname taxclass source/
+  );
+  print "\n";
+}
+
+sub confirm_to_continue {
+  say shift;
+  print "Confirm: [y/N]: ";
+  my $yn = <STDIN>;
+  chomp $yn;
+  if ( lc $yn ne 'y' ) {
+    say "\nAborted\n";
+    exit;
+  }
+}
+
+sub error_and_help {
+  pod2usage({
+    -message => sprintf( "\n\nError:\n\t%s\n\n", shift ),
+    -exitval => 2,
+    verbose => 1,
+  });
+  exit;
+}
+
+__END__
+
+=head1 name
+
+freeside-wa-tax-table-resolve
+
+=head1 SYNOPSIS
+
+freeside-issue-credit-for-taxnums --help
+freeside-issue-credit-for-taxnums --check [freeside_user]
+freeside-issue-credit-for-taxnums --merge 123,234 [freeside_user]
+freeside-issue-credit-for-taxnums --set-source-null 1337,6553 [freeside_user]
+
+=head1 OPTIONS
+
+=over 4
+
+=item B<--help>
+
+Display help and exit
+
+=item B<--check>
+
+Display info on any taxnums considered blocking duplicates
+
+=item B<--merge> [source-taxnum],[target-taxnum]
+
+Update all records referring to [source-taxnum], so they now
+refer to [target-taxnum].  [source-taxnum] is deleted.
+
+Used to merge duplicate taxnums
+
+=item B<--set-source-null> [taxnum],[taxnum],...
+
+Update all records for the given taxnums, by setting the
+I<source> column to NULL.
+
+Used for manually entered tax entries, incorrectly labelled
+as created and managed for Washington State Sales Taxes
+
+=back
+
+=head1 DESCRIPTION
+
+Tool to resolve tax table issues for customer using Washington state
+sales tax districts.
+
+If Freeside detects duplicate rows within the wa sales tax tables,
+tax table updates are blocked, and a log message directs the
+sysadmin to this tool.
+
+Duplicate rows may be manually entered taxes, not related
+to WA sales tax.  Or duplicate rows may have been manually entered
+into freeside for other tax purposes.
+
+Use --check to display which tax entries were detected as dupes.
+
+For each tax entry, decide if it is a duplicate wa sales tax entry,
+or some other manually entered tax.
+
+if the row is a duplicate, merge the duplicates with the --merge
+option of this script
+
+If the row is a manually entered tax, not for WA state sales taxes,
+keep the tax but remove the flag incorrectly labeling it as WA state
+sales taxes with the --set-source-null option of this script
+
+Once --check no longer returns problematic tax entries, the
+wa state tax tables will be able to complete their automatic
+tax rate updates
+
+=cut