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
";
}
|
