- open my $fh, '<', $file
- or die "couldn't open $file: $!\n";
- my $csv = Text::CSV->new;
- my $header = $csv->getline($fh);
- $csv->column_names(@$header);
- # columns we care about are headed 'Code' and 'Rate'
-
- my $total_changed = 0;
- my $total_skipped = 0;
- while ( !$csv->eof ) {
- my $line = $csv->getline_hr($fh);
- my $district = $line->{Code} or next;
- $district = sprintf('%04d', $district);
- my $tax = sprintf('%.1f', $line->{Rate} * 100);
- my $changed = 0;
- my $skipped = 0;
- # find rate(s) in this country+state+district+taxclass that have the
- # wa_sales flag and the configured taxname, and haven't been disabled.
- my @rates = qsearch('cust_main_county', {
- country => 'US',
- state => 'WA', # this is specific to WA
- district => $district,
- taxname => $taxname,
- source => 'wa_sales',
- tax => { op => '>', value => '0' },
- });
- foreach my $rate (@rates) {
- if ( $rate->tax == $tax ) {
- $skipped++;
- } else {
- $rate->set('tax', $tax);
- my $error = $rate->replace;
- die "error updating district $district: $error\n" if $error;
- $changed++;
+
+ sleep 1; # Be polite to WA DOR API
+ }
+
+ for my $cust_location ( qsearch( \%qsearch_cust_location )) {
+ log_error_and_warn(
+ sprintf
+ "Customer address in WA lacking tax district classification. ".
+ "custnum(%s) ".
+ "locationnum(%s) ".
+ "address(%s, %s %s, %s) ".
+ "[https://webgis.dor.wa.gov/taxratelookup/SalesTax.aspx]",
+ map { $cust_location->$_ }
+ qw( custnum locationnum address1 city state zip )
+ );
+ }
+
+}
+
+
+=head2 wa_sales_update_tax_table \%args
+
+Update city/district sales tax rates in L<FS::cust_main_county> from the
+Washington State Department of Revenue published data files.
+
+Creates, or updates, a L<FS::cust_main_county> row for every tax district
+in Washington state. Some cities have different tax rates based on the
+address, within the city. Because of this, some cities have multiple
+districts.
+
+If tax classes are enabled, a row is created in every tax class for
+every district.
+
+Customer addresses aren't classified into districts here. Instead,
+when a Washington state address is inserted or changed in L<FS::cust_location>,
+a job is queued for FS::geocode_Mixin::process_district_update, to ask the
+Washington state API which tax district to use for this address.
+
+All arguments are optional:
+
+ filename: Skip file download, and process the specified filename instead
+
+ taxname: Updated or created records will be set to the given tax name.
+ If not specified, conf value 'tax_district_taxname' is used
+
+ year: Specify year for tax table download. Defaults to current year
+
+ quarter: Specify quarter for tax table download. Defaults to current quarter
+
+=head3 Washington State Department of Revenue Resources
+
+The state of Washington makes data files available via their public website.
+It's possible the availability or format of these files may change. As of now,
+the only data file that contains both city and county names is published in
+XLSX format.
+
+=over 4
+
+=item WA Dept of Revenue
+
+https://dor.wa.gov
+
+=item Data file downloads
+
+https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
+
+=item XLSX file example
+
+https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
+
+=item CSV file example
+
+https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
+
+
+=item Address lookup API tool
+
+http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
+
+=back
+
+=cut
+
+sub wa_sales_update_tax_table {
+ my $args = shift;
+
+ croak 'wa_sales_update_tax_table requires \$args hashref'
+ if $args && !ref $args;
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname');
+ $args->{year} ||= DateTime->now->year;
+ $args->{quarter} ||= DateTime->now->quarter;
+
+ log_info_and_warn(
+ "Begin wa_sales_update_tax_table() ".
+ join ', ' => (
+ map{ "$_ => ". ( $args->{$_} || 'undef' ) }
+ sort keys %$args
+ )
+ );
+
+ $args->{temp_dir} ||= tempdir();
+
+ $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
+
+ $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
+
+ wa_sales_update_cust_main_county( $args );
+
+ log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
+}
+
+=head2 wa_sales_update_cust_main_county \%args
+
+Create or update the L<FS::cust_main_county> records with new data
+
+=cut
+
+sub wa_sales_update_cust_main_county {
+ my $args = shift;
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ croak 'wa_sales_update_cust_main_county requires $args hashref'
+ unless ref $args
+ && ref $args->{tax_districts};
+
+ my $insert_count = 0;
+ my $update_count = 0;
+ my $same_count = 0;
+
+ # 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 => $_ }
+ qsearch(
+ cust_main_county => {
+ district => { op => '!=', value => undef },
+ state => 'WA',
+ country => 'US',
+ source => 'wa_sales',
+ taxclass => $taxclass,
+ }
+ );
+
+ for my $district ( @{ $args->{tax_districts} } ) {
+ if ( my $row = $cust_main_county{ $district->{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
+
+ {
+ # Supress warning on taxname comparison, when taxname is undef
+ no warnings 'uninitialized';
+
+ if (
+ $row->tax == ( $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;
+ }