diff options
Diffstat (limited to 'FS/bin/freeside-wa-tax-table-resolve')
| -rwxr-xr-x | FS/bin/freeside-wa-tax-table-resolve | 452 |
1 files changed, 452 insertions, 0 deletions
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 |