Washington State introduced some interesting tax rules back in 2008. Where previously sales tax was calculated at the point of sale, it was changed to be point of delivery. This was a huge distinction for companies that make deliveries. In addition, the state requires tax reporting to be broken down by district. So prior to 2008, all you needed to report was tax paid. Since then, you have to know what tax districts your customer reside in and charge tax based on the district. At the end of the reporting period, you have to compile a list of taxes paid per district and report that to the state.
What I had to deal with was gathering the district and rate information on customers before sales could be made. At one point, that was a tedious process of validating the customer’s address in order to get the ZIP+4, then using that, looking up the district on the state’s DOR page. I initially got around the first part of that by writing scripts to validate addresses for me, upon submission of a setup request form. That cut out one step, but the DOR website was still clunky, and the form fields didn’t even play nice with the TAB key.
Poking around the site one day, I found something interesting. Turns out the state offers zip file downloads of tax district tables. I went with the Short option, which has all 5-digit ZIP codes and the +4s in high-low ranges. The query was a bit harder to write that way, but the file is significantly smaller – just over 137,000 records, versus hundreds of thousands in the Long file, which is every ZIP+4 in the state.
I first created a MySQL table name ‘tax_rate’, consisting of the following fields:
zip5 varchar(5)
zip4_low varchar(4)
zip4_high varchar(4)
district varchar(4)
state_tax float
local_tax float
total_tax float
start_date varchar(8)
end_date varchar(8)
I don’t need the local or state tax, really, since I only care about the total tax for my purposes. However, this way I don’t need to mess with the file. I don’t do much beyond unzip it and rezip it with gzip. gzip compresses better and leaves the file named correctly for PHPMyAdmin.
From there, it’s very simple. The query I typically use looks like
$taxQry = "SELECT district, total_tax FROM tax_rate WHERE '$zip5' = zip5 AND '$zip4' >= zip4_low AND '$zip4' <= zip4_high";
$Qry = mysqli_query($connect, $taxQry);
if ($Qry === 'false') {
echo mysqli_error($connect) . "
";
}
$taxD = mysqli_fetch_array($Qry);
$region = $taxD[0];
$district = number_format(($taxD[1] * 100), 2, '.', '');
unset($taxD);
} else {
$region = '';
$district = '';
}
I typically include the start and end dates in the query, as well. On the forms that use this query, I’ll change the text color on the date range depending on if the current date is within range of the dates returned from the table. I’ve been a day or so behind on some quarterly updates, and it’s a good visual reminder to not only check the results with invalid dates, but to get in and update the table.
Of course, this can also be set within a loop. I’ve used it many a time in loops that return a validated address with the tax district and rate. Most often I’m writing them into Excel documents using PHPExcel, but that’s a post for another time.
Recent Comments