9 Tool to update city/district sales tax rates in I<cust_main_county> from
10 the Washington State Department of Revenue website.
12 This does not handle address standardization or geocoding addresses to
13 Washington tax district codes. That logic is still in FS::Misc::Geo,
14 and relies on a heinous screen-scraping of the interactive search tool.
15 This script just updates the cust_main_county records that already exist
16 with the latest quarterly tax rates.
18 The only option it accepts is "-c" to operate on a specific tax class
19 (named after the -c). If this isn't included it will operate on records
24 use FS::Record qw(qsearch qsearchs dbh);
25 use FS::cust_main_county;
26 use FS::UID qw(adminsuidsetup);
29 use File::Temp 'tempdir';
30 use File::Slurp qw(read_file write_file);
35 my $user = shift or die usage();
37 # download the update file
38 my $now = DateTime->now;
40 my $qt = $now->quarter;
41 my $file = "Rates${yr}Q${qt}.zip";
42 my $url = 'http://dor.wa.gov/downloads/Add_Data/'.$file;
45 my $ua = LWP::UserAgent->new;
46 warn "Downloading $url...\n";
47 my $response = $ua->get($url);
48 if ( ! $response->is_success ) {
49 die $response->status_line;
51 write_file($file, $response->decoded_content);
54 system('unzip', $file);
55 $file =~ s/\.zip$/.csv/;
57 die "$file not found in zip archive.\n";
59 open my $fh, '<', $file
60 or die "couldn't open $file: $!\n";
61 my $csv = Text::CSV->new;
62 my $header = $csv->getline($fh);
63 $csv->column_names(@$header);
64 # columns we care about are headed 'Code' and 'Rate'
67 adminsuidsetup($user) or die "bad username '$user'\n";
68 $FS::UID::AutoCommit = 0;
70 $opt_c ||= ''; # taxclass
71 my $total_changed = 0;
72 my $total_skipped = 0;
73 while ( !$csv->eof ) {
74 my $line = $csv->getline_hr($fh);
75 my $district = $line->{Code} or next;
76 my $tax = sprintf('%.1f', $line->{Rate} * 100);
79 # find all rates in WA
80 my @rates = qsearch('cust_main_county', {
82 state => 'WA', # this is specific to WA
83 district => $district,
86 foreach my $rate (@rates) {
87 if ( $rate->tax == $tax ) {
90 $rate->set('tax', $tax);
91 my $error = $rate->replace;
92 die "error updating district $district: $error\n" if $error;
96 print "$district: updated $changed, skipped $skipped\n"
97 if $changed or $skipped;
98 $total_changed += $changed;
99 $total_skipped += $skipped;
101 print "Updated $total_changed tax rates.\nSkipped $total_skipped unchanged rates.\n";
106 wa_tax_rate_update [ -c taxclass ] user