summaryrefslogtreecommitdiff
path: root/bin/wa_tax_rate_update
blob: d4a4b52e48ff49bff2b0e0669d39915b3480757b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
#!/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.

Options:

-c <taxclass>: operate only on records with the named tax class.  If not 
specified, this operates on records with null tax class.

-t <taxname>: operate only on records with that tax name.  If not specified,
it operates on records where the tax name is either null or 'Tax'.

=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:t:');
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
$opt_t ||= ''; # taxname
my $total_changed = 0;
my $total_skipped = 0;
while ( !$csv->eof ) {
  my $line = $csv->getline_hr($fh);
  my $district = $line->{Code} or next;
  $district = sprintf('%04d', $district);
  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,
      taxname   => $opt_t,
      tax       => { op => '>', value => '0' },
  });
  if ($opt_t eq '') {
    push @rates, qsearch('cust_main_county', {
      country   => 'US',
      state     => 'WA', # this is specific to WA
      district  => $district,
      taxclass  => $opt_c,
      taxname   => 'Tax',
      tax       => { op => '>', value => '0' },
    });
  }
  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 ] [ -t taxname ] user
";
}