X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=bin%2Fwa_tax_rate_update;fp=bin%2Fwa_tax_rate_update;h=fef126d347bdedb9405ee9fb5d1f7db2b5d0f675;hp=c50a77771351bae8568f77afd88ffde6af37b7d2;hb=83a6052bc16ed5cff28e32613f20dc4b1156bac6;hpb=c213fdbabc178985a93996aac33a907465b31007 diff --git a/bin/wa_tax_rate_update b/bin/wa_tax_rate_update index c50a77771..fef126d34 100644 --- a/bin/wa_tax_rate_update +++ b/bin/wa_tax_rate_update @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl =head1 NAME @@ -10,27 +10,32 @@ Tool to update city/district sales tax rates in I from the Washington State Department of Revenue website. Creates, or updates, a L 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 may have multiple -rows defined. +in Washington state. Some cities have different tax rates based on the +address, within the city. Because of this, some cities have +district. -When a Washington state address is inserted or changed in L, +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, 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. Options: --f : Skip downloading, and process the given excel file + -f : Skip downloading, and process the given excel file --c : Updated or create records within given tax class, - If not specified, taxclass will be set as NULL + -t : Updated or created records will be set to the given tax name. + If not specified, conf value 'tax_district_taxname' will be used --t : Updated or created records will be set to the given tax name. - If not specified, conf value 'tax_district_taxname' will be used + -y : Specify year for tax table - defaults to current year --y : Specify year for tax table - defaults to current year + -q : Specify quarter for tax table - defaults to current quarter --q : Specify quarter for tax table - defaults to current quarter + -l : Attempt to look up the tax district classification for + unclassified cust_location records in Washington. Will + notify of records that cannot be classified =head1 Washington State Department of Revenue Resources @@ -67,297 +72,76 @@ use warnings; our $VERSION = '0.02'; # Make Getopt:Std happy -use Data::Dumper; -use DateTime; -use File::Temp 'tempdir'; -use File::Slurp qw(read_file write_file); use Getopt::Std; -use LWP::UserAgent; -use Spreadsheet::XLSX; -use FS::cust_main_county; +use FS::Cron::tax_rate_update qw( + wa_sales_update_tax_table + wa_sales_log_customer_without_tax_district +); use FS::Log; -use FS::Record qw(qsearch qsearchs dbh); use FS::UID qw(adminsuidsetup); my %opts; -getopts( 'c:t:y:q:f:', \%opts ); +getopts( 't:y:q:f:l', \%opts ); + my $user = shift or die HELP_MESSAGE(); adminsuidsetup( $user ) or die "bad username '$user'\n"; -my $temp_dir = tempdir(); my $log = FS::Log->new('wa_tax_rate_update'); -$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 ) - ); - } +$log->info('Begin wa_tax_rate_update'); +{ + local $@; + eval { + wa_sales_update_tax_table({ + $opts{f} ? ( filename => $opts{f} ) : (), + $opts{t} ? ( taxname => $opts{t} ) : (), + $opts{y} ? ( year => $opts{y} ) : (), + $opts{q} ? ( quarter => $opts{q} ) : (), + }); + }; + + if ( $@ ) { + $log->error( "Error: $@" ); + warn "Error: $@\n"; + } else { + $log->info( 'Finished wa_tax_rate_update' ); + warn "Finished wa_tax_rate_update\n"; } - - 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; } -sub fetch_xlsx_file { - # Download file to temporary storage, return filename - - my $url_base = 'https://dor.wa.gov'. - '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx'; - - my $year = $opts{y} || DateTime->now->year; - my $quarter = $opts{q} || 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 = "$temp_dir/$fn"; - - log_info_and_warn( "Begin download from url: $url" ); - my $ua = LWP::UserAgent->new; - my $res = $ua->get( $url ); +if ( $opts{l} ) { + $log->info( 'Begin wa_sales_log_customer_without_tax_district' ); - log_error_and_die( "Download error: ".$res->status_line ) - unless $res->is_success; + wa_sales_log_customer_without_tax_district(); - 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; + $log->info( 'Finished wa_sales_log_customer_without_tax_district' ); + warn "Finished wa_sales_log_customer_without_tax_district\n"; } +exit; sub HELP_MESSAGE { print " Tool to update city/district sales tax rates in I from the Washington State Department of Revenue website. - Usage: [-f filename] [-c taxclass] [-t taxname] [-y year] [-q quarter] + Usage: [-f filename] [-t taxname] [-y year] [-q quarter] [-l] freeside_username Optional Options: -f filename Skip download, and process the specified filename - -c taxclass Update or create records within this taxclass - defaults as NULL -t taxname Apply tax name value to created or updated records defaults as conf value 'tax_district_taxname' -y year Year for data file download -q quarter Quarter of data file to download + -t lookup Try to fix cust_location records without a district "; exit; } -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 ); - die( "$log_message\n" ); -}