Merge branch 'master' of git.freeside.biz:/home/git/freeside

4 files changed, 472 insertions, 14 deletions

diff --git a/FS/bin/freeside-issue-credit-for-taxnums b/FS/bin/freeside-issue-credit-for-taxnums
index 8b7a12252..016c14ed6 100755
--- a/FS/bin/freeside-issue-credit-for-taxnums
+++ b/FS/bin/freeside-issue-credit-for-taxnums
@@ -293,18 +293,18 @@ sub validate_opts {
 
   error_and_help( '--csv_dir is required' )
     unless $csv_dir;
-  error_and_help( '--start_date is required' )
+  error_and_help( '--start-date is required' )
     unless $start_date;
   error_and_help( '--end-date is required' )
     unless $end_date;
   error_and_help( '--taxnums is required' )
     unless @taxnums;
-  error_and_help( '--credit-reasonnum is required with --apply-credits' )
+  error_and_help( '--credit-reasonnum is required with --insert-credits' )
     if $insert_credits && !$credit_reasonnum;
-  error_and_help( '--credit-addlinfo is required with --apply-credits' )
+  error_and_help( '--credit-addlinfo is required with --insert-credits' )
     if $insert_credits && !$credit_addlinfo;
 
-  error_and_help( "csv dir ($csv_dir) is not a writable directoryu" )
+  error_and_help( "csv dir ($csv_dir) is not a writable directory" )
     unless -d $csv_dir && -r $csv_dir;
 
   error_and_help( "start_date($start_date) is not a valid date string")
diff --git a/FS/bin/freeside-svcbroadband_update_speeds b/FS/bin/freeside-svcbroadband_update_speeds
index 3a7759286..8a6f33f7d 100755
--- a/FS/bin/freeside-svcbroadband_update_speeds
+++ b/FS/bin/freeside-svcbroadband_update_speeds
@@ -19,7 +19,7 @@ adminsuidsetup $user;
 
 sub usage { "
   Usage:
-      svc_broadband_update_speeds: [ -h help] [ -v verbose] [ -n only update services with a null up/down speed] [ -e export service ] [ -a update tower_sector_num ] [ -s service_part_num (required) ] [ -c sibling service_part_num ] [ -r (speed rate in KB 'up,down') ] [ -p (get speed from package fcc rate) ] [ -t tower_sector_num ] [ -d directory for exception file (required) ] user (required)\n
+      freeside-svcbroadband_update_speeds: [ -h help] [ -v verbose] [ -n only update services with a null up/down speed] [ -e export service ] [ -a update tower_sector_num ] [ -s service_part_num (required) ] [ -c sibling service_part_num ] [ -r (speed rate in KB 'up,down') ] [ -p (get speed from package fcc rate) ] [ -t tower_sector_num ] [ -d directory for exception file (required) ] user (required)\n
       A directory for the exception file, freeside user name and a service to update is required.\n
       Must set one or more of options p, c, or r. \n
       Also must run this report as user freeside.\n
@@ -47,12 +47,12 @@ my @services = qsearch({
 });
 
 ### get list of all unprovisioned services
-my $ups_extra_sql = "where cust_pkg.cancel is null and pkg_svc.quantity > 0 and  pkg_svc.quantity > (select count(1) from cust_svc where  cust_svc.pkgnum = cust_pkg.pkgnum and  cust_svc.svcpart = pkg_svc.svcpart)  and pkg_svc.svcpart = $opt_s";
-my @unprovisioned_services = qsearchs({
-    'table'     => 'cust_pkg',
-    'addl_from' => 'JOIN pkg_svc using (pkgpart)',
-    'extra_sql' => $ups_extra_sql,
-});
+#my $ups_extra_sql = "where cust_pkg.cancel is null and pkg_svc.quantity > 0 and  pkg_svc.quantity > (select count(1) from cust_svc where  cust_svc.pkgnum = cust_pkg.pkgnum and  cust_svc.svcpart = pkg_svc.svcpart)  and pkg_svc.svcpart = $opt_s";
+#my @unprovisioned_services = qsearch({
+#    'table'     => 'cust_pkg',
+#    'addl_from' => 'JOIN pkg_svc using (pkgpart)',
+#    'extra_sql' => $ups_extra_sql,
+#});
 
 my $speed;
 $speed = 'package' if $opt_p;
@@ -144,7 +144,7 @@ sub _update_service {
 
 exit;
 
-=head2 svc_broadband_update_speeds
+=head2 freeside-svcbroadband_update_speeds
 
 This script allows for the mas update of up and down speeds for a svc_broadband service.
 
@@ -159,7 +159,7 @@ Script must be run as user freeside.
 Options -s, -d and freeside user are required.
 
 example:
-sudo -u freeside ./svc_broadband_update_speeds -v -s 4 -c 2 -r 148000,248000 -p -d /home/freeside/ freesideuser
+sudo -u freeside ./freeside-svcbroadband_update_speeds -v -s 4 -c 2 -r 148000,248000 -p -d /home/freeside/ freesideuser
 
 available options:
 [ -h help]
diff --git a/FS/bin/freeside-wa-tax-table-resolve b/FS/bin/freeside-wa-tax-table-resolve
new file mode 100755
index 000000000..790dce20b
--- /dev/null
+++ b/FS/bin/freeside-wa-tax-table-resolve
@@ -0,0 +1,452 @@
+#!/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_fix_usf,
+  @opt_merge,
+  $opt_merge_all,
+  @opt_set_source_null,
+);
+
+GetOptions(
+  'check'             => \$opt_check,
+  'fix-usf'           => \$opt_fix_usf,
+  'merge=s'           => \@opt_merge,
+  'merge-all'         => \$opt_merge_all,
+  '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();
+} elsif ( $opt_merge_all ) {
+  merge_all();
+} elsif ( $opt_fix_usf ) {
+  fix_usf();
+} 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.
+
+  ");
+
+  merge_into( $source, $target );
+}
+
+sub merge_into {
+  my ( $source, $target ) = @_;
+
+  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 for district %s',
+        $source->taxnum, $target->taxnum, $source->district;
+
+    say $message;
+    $log->warn( $message );
+  }
+}
+
+sub merge_all {
+  my @dupes = FS::cust_main_county->find_wa_tax_dupes;
+
+  unless ( @dupes ) {
+    say 'No duplicate tax rows detected for WA sales tax districts';
+    return;
+  }
+
+  confirm_to_continue(sprintf "
+
+    %s blocking duplicate rows detected
+
+    Duplicate rows will be merged using FS::cust_main_county::_merge_into()
+
+    Rows are considered duplicates when they:
+    - Share the same tax class
+    - Share the same district
+    - Contain 'wa_sales' in the source column
+
+  ", scalar @dupes);
+
+  # Sort dupes into buckets to be merged, by taxclass and district
+  # $to_merge{taxclass}->{district} = [ @rows_to_merge ]
+  my %to_merge;
+  for my $row ( @dupes ) {
+    my $taxclass = $row->taxclass || 'none';
+    $to_merge{$taxclass} ||= {};
+    $to_merge{$taxclass}->{$row->district} ||= [];
+    push @{ $to_merge{$taxclass}->{$row->district} }, $row;
+  }
+
+  # Merge the duplicates
+  for my $taxclass ( keys %to_merge ) {
+    for my $district ( keys %{ $to_merge{$taxclass} }) {
+
+      # Keep the first row in the list as the target.
+      # Merge the remaining rows into the target
+      my $rows = $to_merge{$taxclass}->{$district};
+      my $target = shift @$rows;
+
+      while ( @$rows ) {
+        merge_into( shift(@$rows), $target );
+      }
+    }
+  }
+
+  say "
+
+    Merge operations completed
+
+    Please run freeside-wa-tax-table-update.  This will update
+    the merged district rows with correct county and city names
+
+  ";
+
+}
+
+sub fix_usf {
+  confirm_to_continue("
+
+    Search for duplicate districts within the tax tables with
+    - duplicate district column values
+    - source = NULL
+    - district = NOT NULL
+    - taxclass = USF
+    - tax > 17
+
+    Merge these rows into a single USF row for each tax district
+
+  ");
+
+  my @rows = qsearch( cust_main_county => {
+    taxclass => 'USF',
+    source   => undef,
+    state    => 'WA',
+    country  => 'US',
+    tax      => { op => '>',  value => 17 },
+    district => { op => '!=', value => undef },
+  });
+
+  my %to_merge;
+  for my $row (@rows) {
+    $to_merge{$row->district} ||= [];
+    push @{ $to_merge{$row->district} }, $row;
+  }
+
+  for my $dist_rows ( values %to_merge ) {
+    my $target = shift @$dist_rows;
+    while ( @$dist_rows ) {
+      merge_into( shift(@$dist_rows), $target );
+    }
+  }
+
+  say "
+
+    USF clean up completed
+
+    Please run freeside-wa-tax-table-update.  This will update
+    the merged district rows with correct county and city names
+
+  ";
+}
+
+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 sort { $a->district <=> $b->district } @dupes;
+
+  $log->error(
+    sprintf 'Detected %s duplicate wa sales tax rows: %s',
+      scalar( @dupes ),
+      join( ',', map{ $_->taxnum } @dupes )
+  );
+
+  say "
+
+    Rows are considered duplicates when they:
+    - Share the same tax class
+    - Share the same district
+    - Contain 'wa_sales' in the source column
+
+  ";
+}
+
+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-wa-tax-table-resolve --help
+  freeside-wa-tax-table-resolve --check [freeside_user]
+  freeside-wa-tax-table-resolve --merge 123,234 [freeside_user]
+  freeside-wa-tax-table-resolve --set-source-null 1337,6553 [freeside_user]
+  freeside-wa-tax-table-resolve --merge-all [freeside_user]
+  freeside-wa-tax-table-resolve --fix-usf [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
+
+=item B<--merge-all>
+
+Automatically merge all blocking duplicate taxnums.
+
+If after reviewing all blocking duplicate taxnum rows with --check,
+if all duplicate rows are safe to merge, this option will merge them all.
+
+=item B<--fix-usf>
+
+Fix routine for a particular USF issue
+
+Search for duplicate districts within the tax tables with
+
+  - duplicate district column values
+  - source = NULL
+  - district = NOT NULL
+  - taxclass = USF
+  - tax > 17
+
+Merge these rows into a single USF row for each tax district
+
+=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
diff --git a/FS/bin/freeside-wa-tax-table-update b/FS/bin/freeside-wa-tax-table-update
index ad14687c9..53c7324d7 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
 
@@ -106,8 +112,8 @@ $log->info('Begin wa_tax_rate_update');
   };
 
   if ( $@ ) {
-    $log->error( "Error: $@" );
     warn "Error: $@\n";
+    $log->error( "Error: $@" );
   } else {
     $log->info( 'Finished wa_tax_rate_update' );
     warn "Finished wa_tax_rate_update\n";