=head2 wa_sales_log_customer_without_tax_district
-For any active customers with cust_location records in WA state,
-if a cust_location record has no tax district, find the correct
-district using WA DOR API, or if not possible, generate an error
-message into system log so address can be corrected
+For any cust_location records
+* In WA state
+* Attached to non cancelled packages
+* With no tax district
+
+Classify the tax district for the record using the WA State Dept of
+Revenue API. If this fails, generate an error into system log so
+address can be corrected
=cut
cust_location.state,
cust_location.zip
',
- hashref => {
- state => 'WA',
- district => undef,
- },
- addl_from => 'LEFT JOIN cust_main USING (custnum)',
- extra_sql => sprintf 'AND ( %s ) ', FS::cust_main->active_sql,
+ addl_from => '
+ LEFT JOIN cust_main USING (custnum)
+ LEFT JOIN cust_pkg ON cust_location.locationnum = cust_pkg.locationnum
+ ',
+ extra_sql => sprintf(q{
+ WHERE cust_location.state = 'WA'
+ AND (
+ cust_location.district IS NULL
+ or cust_location.district = ''
+ )
+ AND cust_pkg.pkgnum IS NOT NULL
+ AND (
+ cust_pkg.cancel > %s
+ OR cust_pkg.cancel IS NULL
+ )
+ }, time()
+ ),
);
for my $cust_location ( qsearch( \%qsearch_cust_location )) {
local $@;
+ log_info_and_warn(
+ sprintf
+ 'Attempting to classify district for cust_location ' .
+ 'locationnum(%s) address(%s)',
+ $cust_location->locationnum,
+ $cust_location->address1,
+ );
+
eval {
FS::geocode_Mixin::process_district_update(
'FS::cust_location',
};
if ( $@ ) {
+ # Error indicates a crash, not an error looking up district
+ # process_district_udpate will generate log messages for those errors
log_error_and_warn(
- sprintf "Failed to classify district for cust_location(%s): %s",
+ sprintf "Classify district error for cust_location(%s): %s",
$cust_location->locationnum,
$@
);
- } else {
- log_info_and_warn(
- sprintf "Classified district for cust_location(%s)",
- $cust_location->locationnum
- );
}
sleep 1; # Be polite to WA DOR API
)
);
+ unless ( wa_sales_update_tax_table_sanity_check() ) {
+ log_error_and_die(
+ 'Duplicate district rows exist in the Washington state sales tax table. '.
+ 'These must be resolved before updating the tax tables. '.
+ 'See "freeside-wa-tax-table-resolve --check" to repair the tax tables. '
+ );
+ }
+
$args->{temp_dir} ||= tempdir();
$args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
Create or update the L<FS::cust_main_county> records with new data
-
-
=cut
sub wa_sales_update_cust_main_county {
my $update_count = 0;
my $same_count = 0;
+ $args->{taxname} ||= 'State Sales Tax';
+
# Work within a SQL transaction
local $FS::UID::AutoCommit = 0;
for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
$taxclass ||= undef; # trap empty string when taxclasses are disabled
- my %cust_main_county =
- map { $_->district => $_ }
+ # Dupe detection/remediation:
+ #
+ # Previous code for washington state tax district was creating
+ # duplicate entries for tax districts. This could lead to customers
+ # being double-taxed
+ #
+ # The following code detects and eliminates duplicates that
+ # were created by wa_sales district code (source=wa_sales)
+ # before updating the tax table with the newly downloaded
+ # data
+
+ my %cust_main_county;
+ my %cust_main_county_dupe;
+
+ for my $row (
qsearch(
cust_main_county => {
- district => { op => '!=', value => undef },
- state => 'WA',
- country => 'US',
- source => 'wa_sales',
+ source => 'wa_sales',
+ district => { op => '!=', value => undef },
taxclass => $taxclass,
}
- );
+ )
+ ) {
+ my $district = $row->district;
+
+ # Row belongs to a known dupe group of districts
+ if ( $cust_main_county_dupe{$district} ) {
+ push @{ $cust_main_county_dupe{$district} }, $row;
+ next;
+ }
+
+ # Row is the first seen dupe for the given district
+ if ( $cust_main_county{$district} ) {
+ $cust_main_county_dupe{$district} = [
+ delete $cust_main_county{$district},
+ $row
+ ];
+ next;
+ }
+
+ # Row is the first seen with this district
+ $cust_main_county{$district} = $row;
+ }
- for my $district ( @{ $args->{tax_districts} } ) {
+ # # Merge any dupes, place resulting non-dupe row in %cust_main_county
+ # # Merge, even if one of the dupes has a $0 tax, or some other
+ # # variation on tax row data. Data for this row will get corrected
+ # # during the following tax import
+ # for my $dupe_district_aref ( values %cust_main_county_dupe ) {
+ # my $row_to_keep = shift @$dupe_district_aref;
+ # while ( my $row_to_merge = shift @$dupe_district_aref ) {
+ # $row_to_merge->_merge_into(
+ # $row_to_keep,
+ # { identical_record_check => 0 },
+ # );
+ # }
+ # $cust_main_county{$row_to_keep->district} = $row_to_keep;
+ # }
+
+ # If there are duplicate rows, it may be unsafe to auto-resolve them
+ if ( %cust_main_county_dupe ) {
+ warn "Unable to continue!";
+ log_error_and_die( sprintf(
+ 'Tax district duplicate rows detected(%s) - '.
+ 'WA Sales tax tables cannot be updated without resolving duplicates - '.
+ 'Please use tool freeside-wa-tax-table-resolve for tax table repair',
+ join( ',', keys %cust_main_county_dupe )
+ ));
+ }
+
+ DIST: for my $district ( @{ $args->{tax_districts} } ) {
if ( my $row = $cust_main_county{ $district->{district} } ) {
+ # Strip whitespace from input
+ $district->{$_} =~ s/(^\s+|\s+$)//g for keys %$district;
+
# District already exists in this taxclass, update if necessary
#
# If admin updates value of conf tax_district_taxname, instead of
# creating an entire separate set of tax rows with
# the new taxname, update the taxname on existing records
- if (
- $row->tax == ( $district->{tax_combined} * 100 )
- && $row->taxname eq $args->{taxname}
- ) {
- $same_count++;
- next;
+ {
+ # Supress warning on taxname comparison, when taxname is undef
+ no warnings 'uninitialized';
+
+ if (
+ sprintf('%.4f',$row->tax) == sprintf('%.4f',($district->{tax_combined} * 100))
+ && $row->taxname eq $args->{taxname}
+ && uc $row->county eq uc $district->{county}
+ && uc $row->city eq uc $district->{city}
+ ) {
+ $same_count++;
+ next DIST;
+ }
}
$row->city( uc $district->{city} );
$row->county( uc $district->{county} );
$row->taxclass( $taxclass );
- $row->taxname( $args->{taxname} || undef );
+ $row->taxname( $args->{taxname} );
$row->tax( $district->{tax_combined} * 100 );
if ( my $error = $row->replace ) {
$insert_count++;
}
+ update_non_sales_tax_rows( $taxclass, $district );
+
} # /foreach $district
} # /foreach $taxclass
}
+=head2 update_non_sales_tax_rows tax_class, $district_href
+
+The customer may have created additional taxes, such as Universal Service Fund.
+
+Ensure the columns for city and county are consistant between
+the user-created tax rows and the wa-sales-managed tax rows.
+
+=cut
+
+sub update_non_sales_tax_rows {
+ my ( $taxclass, $district ) = @_;
+
+ return unless ref $district && $district->{district};
+
+ my @rows = qsearch( cust_main_county => {
+ taxclass => $taxclass,
+ district => $district->{district},
+ state => 'WA',
+ country => 'US',
+ source => { op => '!=', value => 'wa_sales' },
+ });
+
+ for my $row ( @rows ) {
+ $row->city( uc $district->{city} );
+ $row->county( uc $district->{county} );
+
+ if ( my $error = $row->replace ) {
+ dbh->rollback;
+ local $FS::UID::AutoCommit = 1;
+ log_error_and_die(
+ sprintf
+ "Error updating cust_main_county row %s for district %s: %s",
+ $row->taxnum,
+ $district->{district},
+ $error
+ );
+ }
+ }
+
+}
+
=head2 wa_sales_parse_xlsx_file \%args
Parse given XLSX file for tax district information
}
+=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;
sub log_error_and_die {
my $log_message = shift;
&log()->error( $log_message );
+ warn( "$log_message\n" );
die( "$log_message\n" );
}
projects / freeside.git / blobdiff