9 use FS::cust_main_county;
11 use FS::Record qw( qsearch qsearchs );
12 use FS::UID qw( adminsuidsetup );
17 local $FS::UID::AutoCommit = 0;
30 'check' => \$opt_check,
31 'fix-usf' => \$opt_fix_usf,
32 'merge=s' => \@opt_merge,
33 'merge-all' => \$opt_merge_all,
34 'set-source-null=s' => \@opt_set_source_null,
36 @opt_merge = split(',',join(',',@opt_merge));
37 @opt_set_source_null = split(',',join(',',@opt_set_source_null));
41 # check => $opt_check,
42 # merge => \@opt_merge,
43 # set_source_numm => \@opt_set_source_null,
48 $dbh = adminsuidsetup( $freeside_user )
49 or die "Bad username: $freeside_user\n";
51 my $log = FS::Log->new('freeside-wa-tax-table-resolve');
55 } elsif ( @opt_merge ) {
57 } elsif ( @opt_set_source_null ) {
59 } elsif ( $opt_merge_all ) {
61 } elsif ( $opt_fix_usf ) {
64 error_and_help('No options selected');
69 local $FS::UID::AutoCommit = 1;
76 for my $taxnum ( @opt_set_source_null ) {
77 my $row = qsearchs( cust_main_county => { taxnum => $taxnum } );
79 push @cust_main_county, $row;
81 error_and_help("Invalid taxnum specified: $taxnum");
85 say "=== Specified tax rows ===";
86 print_taxnum($_) for @cust_main_county;
90 The source column will be set to NULL for each of the
91 tax rows listed. The tax row will no longer be managed
92 by the washington state sales tax table update utilities.
94 The listed taxes should be manually created taxes, that
95 were never intended to be managed by the auto updater.
99 for my $row ( @cust_main_county ) {
101 $row->setfield( source => undef );
102 my $error = $row->replace;
107 my $message = sprintf 'Error setting source=null taxnum %s: %s',
108 $row->taxnum, $error;
110 $log->error( $message );
116 my $message = sprintf 'Source column set to null for taxnum %s',
119 $log->warn( $message );
125 my $source = qsearchs( cust_main_county => { taxnum => $opt_merge[0] });
126 my $target = qsearchs( cust_main_county => { taxnum => $opt_merge[1] });
128 error_and_help("Invalid source taxnum: $opt_merge[0]")
130 error_and_help("Invalid target taxnum: $opt_merge[1]")
133 local $| = 1; # disable output buffering
135 say '==== source row ====';
136 print_taxnum( $source );
138 say '==== target row ====';
139 print_taxnum( $target );
141 confirm_to_continue("
143 The source tax will be merged into the target tax.
144 All references to the source tax on customer invoices
145 will be replaced with references to the target tax.
146 The source tax will be removed from the tax tables.
150 merge_into( $source, $target );
154 my ( $source, $target ) = @_;
157 eval { $source->_merge_into( $target, { identical_record_check => 0 } ) };
161 my $message = sprintf 'Failed to merge wa sales tax %s into %s: %s',
162 $source->taxnum, $target->taxnum, $@;
165 $log->error( $message );
168 my $message = sprintf 'Merged wa sales tax %s into %s for district %s',
169 $source->taxnum, $target->taxnum, $source->district;
172 $log->warn( $message );
177 my @dupes = FS::cust_main_county->find_wa_tax_dupes;
180 say 'No duplicate tax rows detected for WA sales tax districts';
184 confirm_to_continue(sprintf "
186 %s blocking duplicate rows detected
188 Duplicate rows will be merged using FS::cust_main_county::_merge_into()
190 Rows are considered duplicates when they:
191 - Share the same tax class
192 - Share the same district
193 - Contain 'wa_sales' in the source column
197 # Sort dupes into buckets to be merged, by taxclass and district
198 # $to_merge{taxclass}->{district} = [ @rows_to_merge ]
200 for my $row ( @dupes ) {
201 my $taxclass = $row->taxclass || 'none';
202 $to_merge{$taxclass} ||= {};
203 $to_merge{$taxclass}->{$row->district} ||= [];
204 push @{ $to_merge{$taxclass}->{$row->district} }, $row;
207 # Merge the duplicates
208 for my $taxclass ( keys %to_merge ) {
209 for my $district ( keys %{ $to_merge{$taxclass} }) {
211 # Keep the first row in the list as the target.
212 # Merge the remaining rows into the target
213 my $rows = $to_merge{$taxclass}->{$district};
214 my $target = shift @$rows;
217 merge_into( shift(@$rows), $target );
224 Merge operations completed
226 Please run freeside-wa-tax-table-update. This will update
227 the merged district rows with correct county and city names
234 confirm_to_continue("
236 Search for duplicate districts within the tax tables with
237 - duplicate district column values
239 - district = NOT NULL
243 Merge these rows into a single USF row for each tax district
247 my @rows = qsearch( cust_main_county => {
252 tax => { op => '>', value => 17 },
253 district => { op => '!=', value => undef },
257 for my $row (@rows) {
258 $to_merge{$row->district} ||= [];
259 push @{ $to_merge{$row->district} }, $row;
262 for my $dist_rows ( values %to_merge ) {
263 my $target = shift @$dist_rows;
264 while ( @$dist_rows ) {
265 merge_into( shift(@$dist_rows), $target );
271 USF clean up completed
273 Please run freeside-wa-tax-table-update. This will update
274 the merged district rows with correct county and city names
281 $freeside_user = shift @ARGV
282 or error_and_help('freeside_user parameter required');
285 error_and_help(( '--merge requires a comma separated list of two taxnums'))
286 unless scalar(@opt_merge) == 2
287 && $opt_merge[0] =~ /^\d+$/
288 && $opt_merge[1] =~ /^\d+$/;
291 for my $taxnum ( @opt_set_source_null ) {
292 if ( $taxnum =~ /\D/ ) {
293 error_and_help( "Invalid taxnum ($taxnum)" );
299 my @dupes = FS::cust_main_county->find_wa_tax_dupes;
302 say 'No duplicate tax rows detected for WA sales tax districts';
306 say sprintf '=== Detected %s duplicate tax rows ===', scalar @dupes;
308 print_taxnum($_) for sort { $a->district <=> $b->district } @dupes;
311 sprintf 'Detected %s duplicate wa sales tax rows: %s',
313 join( ',', map{ $_->taxnum } @dupes )
318 Rows are considered duplicates when they:
319 - Share the same tax class
320 - Share the same district
321 - Contain 'wa_sales' in the source column
328 die unless ref $taxnum;
330 say 'taxnum: '.$taxnum->taxnum;
332 map { sprintf(' %s:%s', $_, $taxnum->$_ ) }
333 qw/district city county state tax taxname taxclass source/
338 sub confirm_to_continue {
340 print "Confirm: [y/N]: ";
343 if ( lc $yn ne 'y' ) {
351 -message => sprintf( "\n\nError:\n\t%s\n\n", shift ),
362 freeside-wa-tax-table-resolve
366 freeside-wa-tax-table-resolve --help
367 freeside-wa-tax-table-resolve --check [freeside_user]
368 freeside-wa-tax-table-resolve --merge 123,234 [freeside_user]
369 freeside-wa-tax-table-resolve --set-source-null 1337,6553 [freeside_user]
370 freeside-wa-tax-table-resolve --merge-all [freeside_user]
371 freeside-wa-tax-table-resolve --fix-usf [freeside_user]
379 Display help and exit
383 Display info on any taxnums considered blocking duplicates
385 =item B<--merge> [source-taxnum],[target-taxnum]
387 Update all records referring to [source-taxnum], so they now
388 refer to [target-taxnum]. [source-taxnum] is deleted.
390 Used to merge duplicate taxnums
392 =item B<--set-source-null> [taxnum],[taxnum],...
394 Update all records for the given taxnums, by setting the
395 I<source> column to NULL.
397 Used for manually entered tax entries, incorrectly labelled
398 as created and managed for Washington State Sales Taxes
402 Automatically merge all blocking duplicate taxnums.
404 If after reviewing all blocking duplicate taxnum rows with --check,
405 if all duplicate rows are safe to merge, this option will merge them all.
409 Fix routine for a particular USF issue
411 Search for duplicate districts within the tax tables with
413 - duplicate district column values
415 - district = NOT NULL
419 Merge these rows into a single USF row for each tax district
425 Tool to resolve tax table issues for customer using Washington state
428 If Freeside detects duplicate rows within the wa sales tax tables,
429 tax table updates are blocked, and a log message directs the
430 sysadmin to this tool.
432 Duplicate rows may be manually entered taxes, not related
433 to WA sales tax. Or duplicate rows may have been manually entered
434 into freeside for other tax purposes.
436 Use --check to display which tax entries were detected as dupes.
438 For each tax entry, decide if it is a duplicate wa sales tax entry,
439 or some other manually entered tax.
441 if the row is a duplicate, merge the duplicates with the --merge
442 option of this script
444 If the row is a manually entered tax, not for WA state sales taxes,
445 keep the tax but remove the flag incorrectly labeling it as WA state
446 sales taxes with the --set-source-null option of this script
448 Once --check no longer returns problematic tax entries, the
449 wa state tax tables will be able to complete their automatic