bin/wa_tax_rate_update

   1 #!/usr/bin/perl
   2
   3 =head1 NAME
   4
   5 wa_tax_rate_update
   6
   7 =head1 DESCRIPTION
   8
   9 Tool to update city/district sales tax rates in I<cust_main_county> from
  10 the Washington State Department of Revenue website.
  11
  12 Creates, or updates, a L<FS::cust_main_county> row for every tax district
  13 in Washington state.  Some cities have different tax rates based on the
  14 address, within the city.  Because of this, some cities may have multiple
  15 rows defined.
  16
  17 When a Washington state address is inserted or changed in L<FS::cust_location>,
  18 a job is queued for FS::geocode_Mixin::process_district_update, to ask the
  19 Washington state API which tax district to use for this address.
  20
  21 Options:
  22
  23 -f <filename>: Skip downloading, and process the given excel file
  24
  25 -c <taxclass>: Updated or create records within given tax class,
  26    If not specified, taxclass will be set as NULL
  27
  28 -t <taxname>: Updated or created records will be set to the given tax name.
  29   If not specified, conf value 'tax_district_taxname' will be used
  30
  31 -y <year>: Specify year for tax table - defaults to current year
  32
  33 -q <quarter>: Specify quarter for tax table - defaults to current quarter
  34
  35 =head1 Washington State Department of Revenue Resources
  36
  37 The state of Washington makes data files available via their public website.
  38 It's possible the availability or format of these files may change.  As of now,
  39 the only data file that contains both city and county names is published in
  40 XLSX format.
  41
  42 =item WA Dept of Revenue
  43
  44 https://dor.wa.gov
  45
  46 =item Data file downloads
  47
  48 https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
  49
  50 =item XLSX file example
  51
  52 https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
  53
  54 =item CSV file example
  55
  56 https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
  57
  58
  59 =item Address lookup API tool
  60
  61 http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
  62
  63 =cut
  64
  65 use strict;
  66 use warnings;
  67
  68 our $VERSION = '0.02'; # Make Getopt:Std happy
  69
  70 use Data::Dumper;
  71 use DateTime;
  72 use File::Temp 'tempdir';
  73 use File::Slurp qw(read_file write_file);
  74 use Getopt::Std;
  75 use LWP::UserAgent;
  76 use Spreadsheet::XLSX;
  77
  78 use FS::cust_main_county;
  79 use FS::Log;
  80 use FS::Record qw(qsearch qsearchs dbh);
  81 use FS::UID qw(adminsuidsetup);
  82
  83 my %opts;
  84 getopts( 'c:t:y:q:f:', \%opts );
  85 my $user = shift
  86   or die HELP_MESSAGE();
  87
  88 adminsuidsetup( $user )
  89   or die "bad username '$user'\n";
  90
  91 my $temp_dir = tempdir();
  92 my $log = FS::Log->new('wa_tax_rate_update');
  93
  94 $opts{t} ||= FS::Conf->new->config('tax_district_taxname');
  95
  96 log_info_and_warn( "Set taxclass=$opts{c}" ) if $opts{c};
  97 log_info_and_warn( "Set taxname=$opts{t}"  ) if $opts{t};
  98
  99 my $xls_fn = $opts{f} || fetch_xlsx_file();
 100 my $tax_districts = parse_xlsx_file( $xls_fn );
 101
 102 update_cust_main_county( $tax_districts );
 103
 104 # print Dumper( \%opts );
 105 # for ( 0..5 ) {
 106 #   print Dumper( $tax_districts->[$_] );
 107 # }
 108
 109 log_info_and_warn( "Finished" );
 110 exit;
 111
 112
 113 sub update_cust_main_county {
 114   my $tax_districts = shift;
 115
 116   #
 117   # Working from an assumption tax tables may be loaded multiple times,
 118   # each with a different tax class,
 119   #  re: comments on FS::geocode_Mixin::process_district_update
 120   #
 121
 122   my %cust_main_county =
 123     map { $_->district => $_ }
 124     qsearch(
 125       cust_main_county => {
 126         district => { op => '!=', value => undef },
 127         state    => 'WA',
 128         source   => 'wa_sales',
 129         taxclass => $opts{c},
 130       }
 131     );
 132
 133   # Work within a SQL transaction
 134   local $FS::UID::AutoCommit = 0;
 135
 136   my $insert_count = 0;
 137   my $update_count = 0;
 138
 139   for my $district ( @$tax_districts ) {
 140     if ( my $row = $cust_main_county{ $district->{district} } ) {
 141
 142       $row->city( uc $district->{city} );
 143       $row->county( uc $district->{county} );
 144       $row->taxclass( $opts{c} || undef );
 145       $row->taxname( $opts{t} || undef );
 146       $row->tax( $district->{tax_combined} * 100 );
 147
 148       if ( my $error = $row->replace ) {
 149         dbh->rollback;
 150         local $FS::UID::AutoCommit = 1;
 151         log_error_and_die(
 152           sprintf
 153             "Error updating cust_main_county row %s for district %s: %s",
 154             $row->taxnum,
 155             $district->{district},
 156             $error
 157         );
 158       }
 159
 160       $update_count++;
 161
 162     } else {
 163       my $row = FS::cust_main_county->new({
 164         district => $district->{district},
 165         city     => uc $district->{city},
 166         county   => uc $district->{county},
 167         state    => 'WA',
 168         country  => 'US',
 169         taxclass => $opts{c} || undef,
 170         taxname  => $opts{t} || undef,
 171         tax      => $district->{tax_combined} * 100,
 172         source   => 'wa_sales',
 173       });
 174
 175       if ( my $error = $row->insert ) {
 176         dbh->rollback;
 177         local $FS::UID::AutoCommit = 1;
 178         log_error_and_die(
 179           sprintf
 180             "Error inserting cust_main_county row for district %s: %s",
 181             $district->{district},
 182             $error
 183         );
 184       }
 185
 186       $cust_main_county{ $district->{district} } = $row;
 187       $insert_count++;
 188     }
 189   }
 190
 191   dbh->commit;
 192
 193   local $FS::UID::AutoCommit = 1;
 194   log_info_and_warn(
 195     sprintf
 196       "WA tax table update completed. Inserted %s rows, updated %s rows",
 197       $insert_count,
 198       $update_count
 199   );
 200
 201 }
 202
 203 sub parse_xlsx_file {
 204   my $parse_fn = shift;
 205
 206   # About the file format:
 207   #
 208   # The current spreadsheet contains the following @columns.
 209   # Rows 1 and 2 are a marquee header
 210   # Row 3 is the column labels.  We will test these to detect
 211   #   changes in the data format
 212   # Rows 4+ are the tax district data
 213   #
 214   # The "city" column is being parsed from "Location"
 215
 216   my @columns = qw( city county district tax_local tax_state tax_combined );
 217
 218   log_error_and_die( "Unable to access XLSX file: $parse_fn" )
 219     unless -r $parse_fn;
 220
 221   my $xls_parser = Spreadsheet::XLSX->new( $parse_fn )
 222     or log_error_and_die( "Error parsing XLSX file: $!" );
 223
 224   my $sheet = $xls_parser->{Worksheet}->[0]
 225     or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
 226
 227   my $cells = $sheet->{Cells}
 228     or log_error_and_die( "Unable to read cells in XLSX file" );
 229
 230   # Read the column labels and verify
 231   my %labels =
 232     map{ $columns[$_] => $cells->[2][$_]->{Val} }
 233     0 .. scalar(@columns)-1;
 234
 235   my %expected_labels = (
 236     city         => 'Location',
 237     county       => 'County',
 238     district     => 'Location Code',
 239     tax_local    => 'Local Rate',
 240     tax_state    => 'State Rate',
 241     tax_combined => 'Combined Sales Tax',
 242   );
 243
 244   if (
 245     my @error_labels =
 246       grep { lc $labels{$_} ne lc $expected_labels{$_} }
 247       @columns
 248   ) {
 249     my $error = "Error parsing XLS file - ".
 250                 "Data format may have been updated with WA DOR! ";
 251     $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
 252       for @error_labels;
 253     log_error_and_die( $error );
 254   }
 255
 256   # Parse the rows into an array of hashes
 257   my @districts;
 258   for my $row ( 3..$sheet->{MaxRow} ) {
 259     my %district = (
 260       map { $columns[$_] => $cells->[$row][$_]->{Val} }
 261       0 .. scalar(@columns)-1
 262     );
 263
 264     if (
 265          $district{city}
 266       && $district{county}
 267       && $district{district}     =~ /^\d+$/
 268       && $district{tax_local}    =~ /^\d?\.\d+$/
 269       && $district{tax_state}    =~ /^\d?\.\d+$/
 270       && $district{tax_combined} =~ /^\d?\.\d+$/
 271     ) {
 272
 273       # For some reason, city may contain line breaks!
 274       $district{city} =~ s/[\r\n]//g;
 275
 276       push @districts, \%district;
 277     } else {
 278       log_warn_and_warn(
 279         "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
 280       );
 281     }
 282
 283   }
 284
 285   log_error_and_die( "No \@districts found in data file!" )
 286     unless @districts;
 287
 288   log_info_and_warn(
 289     sprintf "Parsed %s districts from data file", scalar @districts
 290   );
 291
 292   \@districts;
 293 }
 294
 295 sub fetch_xlsx_file {
 296   # Download file to temporary storage, return filename
 297
 298   my $url_base = 'https://dor.wa.gov'.
 299                  '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
 300
 301   my $year = $opts{y}    || DateTime->now->year;
 302   my $quarter = $opts{q} || DateTime->now->quarter;
 303   $year = substr( $year, 2, 2 ) if $year >= 1000;
 304   my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
 305   my $url = "$url_base/$fn";
 306
 307   my $write_fn = "$temp_dir/$fn";
 308
 309   log_info_and_warn( "Begin download from url: $url" );
 310
 311   my $ua = LWP::UserAgent->new;
 312   my $res = $ua->get( $url );
 313
 314   log_error_and_die( "Download error: ".$res->status_line )
 315     unless $res->is_success;
 316
 317   local $@;
 318   eval { write_file( $write_fn, $res->decoded_content ); };
 319   log_error_and_die( "Problem writing download to disk: $@" )
 320     if $@;
 321
 322   log_info_and_warn( "Temporary file: $write_fn" );
 323   $write_fn;
 324 }
 325
 326
 327 sub HELP_MESSAGE {
 328   print "
 329     Tool to update city/district sales tax rates in I<cust_main_county> from
 330     the Washington State Department of Revenue website.
 331
 332     Usage: [-f filename] [-c taxclass] [-t taxname] [-y year] [-q quarter]
 333
 334     Optional Options:
 335       -f filename   Skip download, and process the specified filename
 336       -c taxclass   Update or create records within this taxclass
 337                     defaults as NULL
 338       -t taxname    Apply tax name value to created or updated records
 339                     defaults as conf value 'tax_district_taxname'
 340       -y year       Year for data file download
 341       -q quarter    Quarter of data file to download
 342
 343   ";
 344   exit;
 345 }
 346
 347 sub log_info_and_warn {
 348   my $log_message = shift;
 349   warn "$log_message\n";
 350   $log->info( $log_message );
 351 }
 352
 353 sub log_warn_and_warn {
 354   my $log_message = shift;
 355   warn "$log_message\n";
 356   $log->warn( $log_message );
 357 }
 358
 359 sub log_error_and_die {
 360   my $log_message = shift;
 361   $log->error( $log_message );
 362   die( "$log_message\n" );
 363 }