+
+ # 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;
+ }
+
+ 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;
+ }
+ }
+
+ $row->city( uc $district->{city} );
+ $row->county( uc $district->{county} );
+ $row->taxclass( $taxclass );
+ $row->taxname( $args->{taxname} || undef );
+ $row->tax( $district->{tax_combined} * 100 );
+
+ 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
+ );
+ }
+
+ $update_count++;
+
+ } else {
+
+ # District doesn't exist, create row
+
+ my $row = FS::cust_main_county->new({
+ district => $district->{district},
+ city => uc $district->{city},
+ county => uc $district->{county},
+ state => 'WA',
+ country => 'US',
+ taxclass => $taxclass,
+ taxname => $args->{taxname} || undef,
+ tax => $district->{tax_combined} * 100,
+ source => 'wa_sales',
+ });
+
+ if ( my $error = $row->insert ) {
+ dbh->rollback;
+ local $FS::UID::AutoCommit = 1;
+ log_error_and_die(
+ sprintf
+ "Error inserting cust_main_county row for district %s: %s",
+ $district->{district},
+ $error
+ );
+ }
+
+ $cust_main_county{ $district->{district} } = $row;
+ $insert_count++;
+ }
+
+ } # /foreach $district
+ } # /foreach $taxclass
+
+ dbh->commit;
+
+ local $FS::UID::AutoCommit = 1;
+ log_info_and_warn(
+ sprintf
+ "WA tax table update completed. ".
+ "Inserted %s rows, updated %s rows, identical %s rows",
+ $insert_count,
+ $update_count,
+ $same_count
+ );
+
+}
+
+=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;
+