script to update Washington sales tax rates, #26265

1 files changed, 108 insertions, 0 deletions

diff --git a/bin/wa_tax_rate_update b/bin/wa_tax_rate_update
new file mode 100755
index 000000000..27d152775
--- /dev/null
+++ b/bin/wa_tax_rate_update
@@ -0,0 +1,108 @@
+#!/usr/bin/perl
+
+=head1 NAME
+
+wa_tax_rate_update
+
+=head1 DESCRIPTION
+
+Tool to update city/district sales tax rates in I<cust_main_county> from 
+the Washington State Department of Revenue website.
+
+This does not handle address standardization or geocoding addresses to 
+Washington tax district codes.  That logic is still in FS::Misc::Geo,
+and relies on a heinous screen-scraping of the interactive search tool.
+This script just updates the cust_main_county records that already exist
+with the latest quarterly tax rates.
+
+The only option it accepts is "-c" to operate on a specific tax class 
+(named after the -c).  If this isn't included it will operate on records
+with null tax class.
+
+=cut
+
+use FS::Record qw(qsearch qsearchs dbh);
+use FS::cust_main_county;
+use FS::UID qw(adminsuidsetup);
+use DateTime;
+use LWP::UserAgent;
+use File::Temp 'tempdir';
+use File::Slurp qw(read_file write_file);
+use Text::CSV;
+use Getopt::Std;
+
+getopts('c:');
+my $user = shift or die usage();
+
+# download the update file
+my $now = DateTime->now;
+my $yr = $now->year;
+my $qt = $now->quarter;
+my $file = "Rates${yr}Q${qt}.zip";
+my $url = 'http://dor.wa.gov/downloads/Add_Data/'.$file;
+my $dir = tempdir();
+chdir($dir);
+my $ua = LWP::UserAgent->new;
+warn "Downloading $url...\n";
+my $response = $ua->get($url);
+if ( ! $response->is_success ) {
+  die $response->status_line;
+}
+write_file($file, $response->decoded_content);
+
+# parse it
+system('unzip', $file);
+$file =~ s/\.zip$/.csv/;
+if (! -f $file) {
+  die "$file not found in zip archive.\n";
+}
+open my $fh, '<', $file
+  or die "couldn't open $file: $!\n";
+my $csv = Text::CSV->new;
+my $header = $csv->getline($fh);
+$csv->column_names(@$header);
+# columns we care about are headed 'Code' and 'Rate'
+
+# connect to the DB
+adminsuidsetup($user) or die "bad username '$user'\n";
+$FS::UID::AutoCommit = 0;
+
+$opt_c ||= ''; # taxclass
+my $total_changed = 0;
+my $total_skipped = 0;
+while ( !$csv->eof ) {
+  my $line = $csv->getline_hr($fh);
+  my $district = $line->{Code} or next;
+  my $tax = sprintf('%.1f', $line->{Rate} * 100);
+  my $changed = 0;
+  my $skipped = 0;
+  # find all rates in WA
+  my @rates = qsearch('cust_main_county', {
+      country   => 'US',
+      state     => 'WA', # this is specific to WA
+      district  => $district,
+      taxclass  => $opt_c,
+  });
+  foreach my $rate (@rates) {
+    if ( $rate->tax == $tax ) {
+      $skipped++;
+    } else {
+      $rate->set('tax', $tax);
+      my $error = $rate->replace;
+      die "error updating district $district: $error\n" if $error;
+      $changed++;
+    }
+  }
+  print "$district: updated $changed, skipped $skipped\n"
+    if $changed or $skipped;
+  $total_changed += $changed;
+  $total_skipped += $skipped;
+}
+print "Updated $total_changed tax rates.\nSkipped $total_skipped unchanged rates.\n";
+dbh->commit;
+
+sub usage {
+  "usage:
+  wa_tax_rate_update [ -c taxclass ] user
+";
+}