-$opts{t} ||= FS::Conf->new->config('tax_district_taxname');
-
-log_info_and_warn( "Set taxclass=$opts{c}" ) if $opts{c};
-log_info_and_warn( "Set taxname=$opts{t}" ) if $opts{t};
-
-my $xls_fn = $opts{f} || fetch_xlsx_file();
-my $tax_districts = parse_xlsx_file( $xls_fn );
-
-update_cust_main_county( $tax_districts );
-
-# print Dumper( \%opts );
-# for ( 0..5 ) {
-# print Dumper( $tax_districts->[$_] );
-# }
-
-log_info_and_warn( "Finished" );
-exit;
-
-
-sub update_cust_main_county {
- my $tax_districts = shift;
-
- #
- # Working from an assumption tax tables may be loaded multiple times,
- # each with a different tax class,
- # re: comments on FS::geocode_Mixin::process_district_update
- #
-
- my %cust_main_county =
- map { $_->district => $_ }
- qsearch(
- cust_main_county => {
- district => { op => '!=', value => undef },
- state => 'WA',
- source => 'wa_sales',
- taxclass => $opts{c},
- }
- );
-
- # Work within a SQL transaction
- local $FS::UID::AutoCommit = 0;
-
- my $insert_count = 0;
- my $update_count = 0;
-
- for my $district ( @$tax_districts ) {
- if ( my $row = $cust_main_county{ $district->{district} } ) {
-
- $row->city( uc $district->{city} );
- $row->county( uc $district->{county} );
- $row->taxclass( $opts{c} || undef );
- $row->taxname( $opts{t} || 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 {
- my $row = FS::cust_main_county->new({
- district => $district->{district},
- city => uc $district->{city},
- county => uc $district->{county},
- state => 'WA',
- country => 'US',
- taxclass => $opts{c} || undef,
- taxname => $opts{t} || 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++;
- }
- }
-
- dbh->commit;
-
- local $FS::UID::AutoCommit = 1;
- log_info_and_warn(
- sprintf
- "WA tax table update completed. Inserted %s rows, updated %s rows",
- $insert_count,
- $update_count
- );
-
-}
-
-sub parse_xlsx_file {
- my $parse_fn = shift;
-
- # 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: $parse_fn" )
- unless -r $parse_fn;
-
- my $xls_parser = Spreadsheet::XLSX->new( $parse_fn )
- 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 )
- );
- }