+=head2 wa_sales_parse_xlsx_file \%args
+
+Parse given XLSX file for tax district information
+Return an arrayref of district information hashrefs
+
+=cut
+
+sub wa_sales_parse_xlsx_file {
+ my $args = shift;
+
+ croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
+ unless ref $args
+ && $args->{filename};
+
+ # About the file format:
+ #
+ # The current spreadsheet contains the following @columns.
+ # Rows 1 and 2 are a marquee header
+ # Row 3 is the column labels. We will test these to detect
+ # changes in the data format
+ # Rows 4+ are the tax district data
+ #
+ # The "city" column is being parsed from "Location"
+
+ my @columns = qw( city county district tax_local tax_state tax_combined );
+
+ log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
+ unless -r $args->{filename};
+
+ my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
+ or log_error_and_die( "Error parsing XLSX file: $!" );
+
+ my $sheet = $xls_parser->{Worksheet}->[0]
+ or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
+
+ my $cells = $sheet->{Cells}
+ or log_error_and_die( "Unable to read cells in XLSX file" );
+
+ # Read the column labels and verify
+ my %labels =
+ map{ $columns[$_] => $cells->[2][$_]->{Val} }
+ 0 .. scalar(@columns)-1;
+
+ my %expected_labels = (
+ city => 'Location',
+ county => 'County',
+ district => 'Location Code',
+ tax_local => 'Local Rate',
+ tax_state => 'State Rate',
+ tax_combined => 'Combined Sales Tax',
+ );
+
+ if (
+ my @error_labels =
+ grep { lc $labels{$_} ne lc $expected_labels{$_} }
+ @columns
+ ) {
+ my $error = "Error parsing XLS file - ".
+ "Data format may have been updated with WA DOR! ";
+ $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
+ for @error_labels;
+ log_error_and_die( $error );
+ }
+
+ # Parse the rows into an array of hashes
+ my @districts;
+ for my $row ( 3..$sheet->{MaxRow} ) {
+ my %district = (
+ map { $columns[$_] => $cells->[$row][$_]->{Val} }
+ 0 .. scalar(@columns)-1
+ );
+
+ if (
+ $district{city}
+ && $district{county}
+ && $district{district} =~ /^\d+$/
+ && $district{tax_local} =~ /^\d?\.\d+$/
+ && $district{tax_state} =~ /^\d?\.\d+$/
+ && $district{tax_combined} =~ /^\d?\.\d+$/
+ ) {
+
+ # For some reason, city may contain line breaks!
+ $district{city} =~ s/[\r\n]//g;
+
+ push @districts, \%district;
+ } else {
+ log_warn_and_warn(
+ "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
+ );
+ }
+
+ }
+
+ log_error_and_die( "No \@districts found in data file!" )
+ unless @districts;
+
+ log_info_and_warn(
+ sprintf "Parsed %s districts from data file", scalar @districts
+ );
+
+ \@districts;
+
+}
+
+=head2 wa_sales_fetch_xlsx_file \%args
+
+Download data file from WA state DOR to temporary storage,
+return filename
+
+=cut
+
+sub wa_sales_fetch_xlsx_file {
+ my $args = shift;
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
+ unless ref $args
+ && $args->{temp_dir};
+
+ my $url_base = 'https://dor.wa.gov'.
+ '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
+
+ my $year = $args->{year} || DateTime->now->year;
+ my $quarter = $args->{quarter} || DateTime->now->quarter;
+ $year = substr( $year, 2, 2 ) if $year >= 1000;
+
+ my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
+ my $url = "$url_base/$fn";
+
+ my $write_fn = "$args->{temp_dir}/$fn";
+
+ log_info_and_warn( "Begin download from url: $url" );
+
+ my $ua = LWP::UserAgent->new;
+ my $res = $ua->get( $url );
+
+ log_error_and_die( "Download error: ".$res->status_line )
+ unless $res->is_success;
+
+ local $@;
+ eval { write_file( $write_fn, $res->decoded_content ); };
+ log_error_and_die( "Problem writing download to disk: $@" )
+ if $@;
+
+ log_info_and_warn( "Temporary file: $write_fn" );
+ $write_fn;
+
+}
+
+=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_info_and_warn {
+ my $log_message = shift;
+ warn "$log_message\n";
+ &log()->info( $log_message );
+}
+
+sub log_warn_and_warn {
+ my $log_message = shift;
+ warn "$log_message\n";
+ &log()->warn( $log_message );
+}
+
+sub log_error_and_die {
+ my $log_message = shift;
+ &log()->error( $log_message );
+ warn( "$log_message\n" );
+ die( "$log_message\n" );
+}
+
+sub log_error_and_warn {
+ my $log_message = shift;
+ warn "$log_message\n";
+ &log()->error( $log_message );
+}
+
+sub conf_tax_district_method {
+ state $tax_district_method = FS::Conf->new->config('tax_district_method');
+ $tax_district_method;
+}
+
+