Jump to content

Archived

This topic is now archived and is closed to further replies.

kidsngarden

WA state destination based sales tax

Recommended Posts

I am getting close to having this ready for production. i don't know where the original creators of this contrib went, but would like to thank them for the great work they did and all the time they must have spent getting it as far as they did.

 

lildog

 

Todd,

 

I am the original developer of this solution. I had to take a short hiatus to handle a deadline with some clients. I have been slammed lately!

 

I am grateful you are having such success and are contributing more code modifications and documentation. I got the ball rolling, you have kept it rolling in my absence. Kudos to you! Thanks!

 

I have some updated queries that seem to work better and some updates to the address parser that I would like to submit. PM me if you want me to send anything to you regarding this. It would be great if you can take my mods and implement them while I am away.

 

The most prominent addition to the address parser code is it now can now parse more addresses properly. It had not previously been able to get apartment numbers right and could not parse things like 'OLD HIGHWAY 99". Now the highway's work and apartments numbers are better (BTW, we don't need apt numbers here but it can do it :lol:).

 

I will try your modifications out and see how I can help get this thing ready soon.

 

I have dropped off the map, but I will be back soon! Next week I will be able to offer some more time on this. I am doing this mainly in my "spare time" which I suddenly ran out of this week. Stay tuned, I will be back soon!

 

Thanks again to all who have helped out so far! This open source stuff really does work! hehehe

Share this post


Link to post
Share on other sites

I think its too much of a pain in the ass to even think about dealing with. Why dont you just set it for one tax rate - a median rate and charge everybody that. Then when you process the order (on quick books or whatever) then just look up the correct tax and apply it then for your books - then you front the difference. The state just wants the money, they dont care who its from. It will probably be negligible and make it a lot easier then having to enter ALL those diff rates.

 

 

Hello,

I use oscommerce as my cart and am based in WA state. Effective July 1, 2008 I will have to charge sales tax based on where my products are being sent too, not my cities tax rate. So If I was sending to Seattle, I would have to charge the Seattle tax rate, not my own. Obviously this sets up a whole lot of problems for me as the oscommerce cart is not set up to handle 100's of tax rates within one state.

 

Read more about destination based sales tax here.

 

Aside from the fact that I think this is nuts and going to be a whole lot of work, I was wondering if oscommerce is doing anything to be able to accomodate this?

 

Thanks,

Bethany

Capella's Garden Soap Co.

www.capellasgarden.com

Share this post


Link to post
Share on other sites

keith03, I didn't mean to take this from you...I would be glad to implement your new code I have been trying to solve my own problems with this. I am on a shared server and uploading is timing out, now the file is too big for my alloted memory. It is still mostly your code, I am just adding my own to try help others that may be in the same position as me. Please send me what changes you have and I will implement them. Again, thanks for the original code, I actually forgot about this until I saw your contrib come up. There has been suprisingly little attention given to this contrib, I thought people would be scrambling to meet the tuesday deadline.

 

I agree for small businesses it is probably going to be negligable, but I have a computerized system and it is capable of doing the calculations without anyone thinking about it. Just side stepping the tax law is not really a viable alternative for me. I don't think you can legaly charge a median tax rate, some would be overcharged for tax.

Share this post


Link to post
Share on other sites
keith03, I didn't mean to take this from you...I would be glad to implement your new code I have been trying to solve my own problems with this. I am on a shared server and uploading is timing out, now the file is too big for my alloted memory. It is still mostly your code, I am just adding my own to try help others that may be in the same position as me. Please send me what changes you have and I will implement them. Again, thanks for the original code, I actually forgot about this until I saw your contrib come up. There has been suprisingly little attention given to this contrib, I thought people would be scrambling to meet the tuesday deadline.

 

I agree for small businesses it is probably going to be negligable, but I have a computerized system and it is capable of doing the calculations without anyone thinking about it. Just side stepping the tax law is not really a viable alternative for me. I don't think you can legaly charge a median tax rate, some would be overcharged for tax.

 

Todd,

 

You haven't taken anything from me. I have given this to everyone! :-) It belongs to you as well, go nuts! hehee

 

I am really grateful that you have taken time to try to make it better. I will have some time this week to look at some of the issues with this.

 

I knew when I was making the database upload part that it may be troublesome (to say the least) for people on shared hosting with limited resources. The problem is the 6 MB file expands to 42+ MB before it can be put into the database.

 

The caveat(s) summary:

If your hosting plan does not allow uploads, it won't work. If your hosting plan only allows uploads smaller than 6 MB, it won't work. If your hosting disk space is not big enough to hold the upload after unzipping, it won't work. If the PHP configuration has a short timeout, you guessed it, it won't work. And there are more.

 

I had hoped someone could find a better solution. Perhaps a client-side helper? Hmmm... Maybe a cron job could be set up to process it is smaller chunks.

 

I will keep you updated on anything I come up with. I will have more time this week.

Share this post


Link to post
Share on other sites

Yeah, I got around the time out by downloading directly from DOR to my server. That worked well. I also added a solution to upload it straight from an ftp client to the tmp folder. Now I have hit a wall, my memory limit is 20M and you need 45+ for the large file. if it were client side it would almost be the equivalent of a 45meg upload wouldn't it? My option at this point is to upload the decompressed 45meg file either in pieces or whole. Either one is not really optimal I don't think. I am still investigating. Let me know what changes need to go into the address parser...

 

 

lildog

Share this post


Link to post
Share on other sites

I have put a call into DOR to see if an API is on the horizon. They are going to call me back......

 

 

lildog

Share this post


Link to post
Share on other sites

I just came accross this forum by accident looking up some info on the WA Destination Based Sales Tax.

 

I wanted to reply that I have successfully integrated the DOR's XML taxrate lookup into an (*shudder) asp.net shopping cart system, and it works quite well. I know this isn't super pertinent to osCommerce, but wanted to say it does work. The only thing you will need to watch out for is if a customer types in a zip code that doesn't exist, then you need to handle the error of no response. Even if they type in an incorrect address with the correct zip code, it will at least give you a rate.

 

In speaking with a few different people at the WA DOR, there will be a letter available for businesses who are having trouble implementing anything into their shopping cart systems, which will allow you up to a one year transition period to comply with this law.

 

I hope this alleviates at least a little anxiety for some of you out there.

Share this post


Link to post
Share on other sites

In truth - I did not realize that this was coming until the WA DOR quarterly newsletter arrived on Saturday the 28th. However, like many I am scrambling to comply and like many will ultimately be FAR FAR behind the $$$ curve on implementation.

 

Before posing my question(s) thanks to both Kieth and Brian for their work on this endeavor and everyone who has contributed their time and efforts to this modification/hack.

 

Curiosity - in reviewing the various versions and the enclosed readme_install.txt there seems to be inconsitencies with the applicable versions of php and MySql. All of the readme_install.txt files say:

 

PHP Version: 5.2.3 (Zend: 2.2.0)

MySQL Version: 5.0.37

 

Yet the uploads/downloads starting with WA_Dest_tax_v1.2 seem to indicate pre php 5

 

Realizing they ALL are apparently still BETA can someone (anyone) please identify what versions of php and MySQL are necessary for the most current existing mod WA_Dest_tax_v1.5 to work?

 

Spending the time to incorporate this would be much more palatable knowing it was going to work with what I have currently installed and working.

Share this post


Link to post
Share on other sites

Chadduck, Keith we just found out about a URI interface to the DOR database of tax rates. Wait just a while, a day or two for a NEW version of this contrib. I am scrambling also and am working on it.

 

lildog

Share this post


Link to post
Share on other sites

hopefully someone can help me here. I think I have it almost worked out except every attempt to open the xml fails.....

 

I have used fopen, file_get_contents, and stream_get_contents all failed to open the xml with either BAD REQUEST or errno=2 - No such file or directory.

 

All help would be great.

 

lildog

Share this post


Link to post
Share on other sites
Chadduck, Keith we just found out about a URI interface to the DOR database of tax rates. Wait just a while, a day or two for a NEW version of this contrib. I am scrambling also and am working on it.

 

lildog

 

I wrote a class of PHP functions that you can copy and paste into a text file here:

 

http://www.destinationtax.com/washington-s...-tax-lookup.htm

 

You simply enter a street address, a city, and a 5-digit zip code, and the class will contact the server, parse the XML result, and returns an array of data for the tax lookup. Sample code is included for making the function calls (there are only 3 or 4 lines you need), such as:

 

include('classes/WAStateRateParser.inc.php');
$rate_look = new WAXMLParser('522 W 1st Avenue','Spokane','99201');
$rate_look->setSimpleXML();
$my_lookup = $rate_look->getParsedData();

 

Should be a good tool for you guys who are developing a solution for OS Commerce. Enjoy.

 

Edit: lildog, I just read your last post. You need to use cURL to retrieve the XML response, and then parse it out. dor.wa.gov does offer a plain text response, but without nearly the information you get via XML. Check out the code linked to above - it uses cURL to retrieve the XML data, and then processes it using the simpleXML class available with PHP.

 

Jerry

Share this post


Link to post
Share on other sites
hopefully someone can help me here. I think I have it almost worked out except every attempt to open the xml fails.....

 

I have used fopen, file_get_contents, and stream_get_contents all failed to open the xml with either BAD REQUEST or errno=2 - No such file or directory.

 

All help would be great.

 

lildog

 

 

lildog,

 

fopen should work. are you putting the http:// in the filename? I know this is silly but I will just post it anyway:

http://us2.php.net/manual/en/function.fopen.php

 

Hope this helps. I will be on tomorrow. Let me know if you are still stuck.

 

Keith

Share this post


Link to post
Share on other sites

hi,

you have right in kansas are the most ridiculous law, there it's legal to smoke de cigarette from www.euro-cigs.com everywhere :).

what is the wsdl ? how i integrate the wsdl into a cart?

Share this post


Link to post
Share on other sites

Hi... I was wondering if the wa module was ready for use?.. or is it still in the test phase?..thanks so much

Share this post


Link to post
Share on other sites

As of right now it is still beta, DOR put up an API on the 1st and that is the best soulution for us all. We are working on a production version. I had server problems this morning so no progress was made.

 

lildog

Share this post


Link to post
Share on other sites

Thanks Jerry, I am going to give it a whirl now. Yeah Late yesterday I was working on it ans saw it was a response not a xml file, so I was going about it all wrong! Curl was what I had figured was going to work.

 

lildog

Share this post


Link to post
Share on other sites

Hi, all -

 

I have a solution working (needs a little testing - any volunteers? ;) ) using the tool provided by Dept. of Revenue. I had a little glitch, and ended up phoning their customer service line and I got to talk to the developer of the tool. Very cool - they really are offering great customer service!

 

Anyhow, the way I did it involves using the http_client.php class methods to do the http request/response stuff. I put the code inside the function tep_get_tax_rate (in /catalog/includes/functions/general.php). I perform a number of 'checks' (is this customer in Washington State? do we have customer's street address? were we able to access dor site? did we get a valid tax rate back?) to make sure that the rate is usable, and if it doesn't pass the tests, the code just reverts to the regular 'origin-based' code, which will return the tax rate for merchant's address for washington, as a default.

 

I'm pretty pleased with it, but I didn't want to just foist it on anyone. So, if you want more info, just let me know.

Share this post


Link to post
Share on other sites
I would love to see the code I am having a heck of a time getting the response from the url. I would really like to get a contrib back up that works well....

 

lildog

 

 

Hi, all -

 

I have a solution working (needs a little testing - any volunteers? ;) ) using the tool provided by Dept. of Revenue. I had a little glitch, and ended up phoning their customer service line and I got to talk to the developer of the tool. Very cool - they really are offering great customer service!

 

Anyhow, the way I did it involves using the http_client.php class methods to do the http request/response stuff. I put the code inside the function tep_get_tax_rate (in /catalog/includes/functions/general.php). I perform a number of 'checks' (is this customer in Washington State? do we have customer's street address? were we able to access dor site? did we get a valid tax rate back?) to make sure that the rate is usable, and if it doesn't pass the tests, the code just reverts to the regular 'origin-based' code, which will return the tax rate for merchant's address for washington, as a default.

 

I'm pretty pleased with it, but I didn't want to just foist it on anyone. So, if you want more info, just let me know.

Share this post


Link to post
Share on other sites

Well... can you say "spoke too soon"? That is, it DOES work, but it caused an issue with usps, and potentially ups shipping contribs. You said you're having response-time issues? I had that too! 2+ minutes - I measured. Read on...

 

In /catalog/includes/functions/general.php...

REPLACE line 315

global $customer_zone_id, $customer_country_id, $billto, $sendto, $cart, $customer_id;

WITH:

 global $customer_zone_id, $customer_country_id, $billto, $sendto, $cart, $customer_id;

 

 

JUST AFTER line 326, INSERT:

	//DBK: WA State Sales Tax Modification
//Lookup WA State Sales tax from WA Department of Revenue website

if (tep_get_zone_code($customer_country_id, $customer_zone_id, "") == "WA") {
	//this customer is from Washington State,
	//so attempt to get their tax rate based on order destination

	//check to see if customer's address is available
	$tax_address_query = tep_db_query("select ab.entry_street_address, ab.entry_city, ab.entry_postcode from " . TABLE_ADDRESS_BOOK . " ab left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) where ab.customers_id = '" . (int)$customer_id . "' and ab.address_book_id = '" . (int)(($cart->content_type == 'virtual' ) ? $billto : $sendto) . "'"); 

	if (tep_db_num_rows($tax_address_query)) {
		//we have some address information for this customer, so find their tax rate
		$tax_address = tep_db_fetch_array($tax_address_query);
		$url = "http://dor.wa.gov/AddressRates.aspx?output=text";
		$url .= "&addr=".urlencode($tax_address['entry_street_address']);
		$url .= "&city=".urlencode($tax_address['entry_city']);
		$url .= "&zip=".urlencode($tax_address['entry_postcode']);

		//this code uses httpRequest object, so include class
		require_once("includes/classes/http_client.php");

		$headers = array('host'=>'dor.wa.gov');

		$http = new httpClient();
		$http->Connect("dor.wa.gov", 80) or die("Connect problem");
		$http->setHeaders($headers);
		$status = $http->Get($url);
		if ($status == 200) $resultString =  $http->getBody();
		$http->Disconnect();

		if (!empty($resultString)) {
			$results = explode(' ', $resultString);
			foreach ($results as $key=>$value) {
				$breakPosition = strpos($value,"=");
				$newKey = substr($value,0,$breakPosition);
				$newValue = substr($value,$breakPosition+1);
				$results[$newKey] = $newValue;
			}
			if ((int)$results['ResultCode'] < 3) return $results['Rate']*100;
		}
	}
}
//end Washington State Tax Lookup Modification

 

In /catalog/includes/classes/http_client.php...

COMMENT OUT Lines ~423 - 426

 $buffer = fread($this->socket, 128);
	  $counter++;
	  usleep(2);

 

INSERT just after line 426 (usleep(2)), above

 $counter = 10;

 

 

This works, BUT commenting out those lines in http_client.php breaks the ups/usps modules which also use http_client. I'm not sure how to proceed. I commented those out because I was getting huge time delays on response. When I output the values of relevant variables, I thought these lines were causing a read beyond the end of the file. That is, I was getting unread_bytes = 0, so why perfom another fread? If you can shed any light on this, I'm interested. Will post more if I get something.

Share this post


Link to post
Share on other sites
Is the http_client.php class part of PEAR installation?

 

No... the http_client.php is part of the osCommerce classes. Came with the standard osC download, and it's located in /catalog/includes/classes/

Share this post


Link to post
Share on other sites

Jerry, I am working with your code and can get the text response back but not the xml response. here is what I have, any ideas:

 

 

$address='6500_Linderson way';

$city='';

$zip='98501';

$request_url = "http://dor.wa.gov/AddressRates.aspx?output=xml&addr=".urlencode($address)."&city=".urlencode($city)."&zip=".urlencode($zip);

 

 

// Start a cURL session and send the XML data

//$data = trim($data);

if($handle = curl_init($request_url)){

echo 'curl init successful';

curl_setopt($handle,CURLOPT_RETURNTRANSFER,1);

curl_setopt($handle, CURLOPT_HEADER, 0);

curl_setopt($handle, CURLOPT_FORBID_REUSE, 1);

curl_setopt($handle, CURLOPT_FRESH_CONNECT, 1);

if($rate_data = curl_exec($handle)){

curl_close($handle);

//return TRUE;

}else{

// exit('Error executing cURL session. <a href="java script:history.go(-1)">Go Back</a>');

echo 'trouble';

}

}else{

// exit('Error initializing cURL session. <a href="java script:history.go(-1)">Go Back</a>');

echo 'more trouble';

}

 

echo 'rate_data:'.$rate_data;

Share this post


Link to post
Share on other sites

kehlers,

I got the response working quickly using curl, I will also look at your code and see what I can figure.

 

This is very preliminary code BUT it does return the rate fairly quickly, a couple of seconds.

 

$address='6500_Linderson way';

$city='';

$zip='98501';

$request_url = "http://dor.wa.gov/AddressRates.aspx?output=text&addr=".urlencode($address)."&city=".urlencode($city)."&zip=".urlencode($zip);

 

 

// Start a cURL session and send the XML data

//$data = trim($data);

if($handle = curl_init($request_url)){

// echo 'curl init successful';

curl_setopt($handle,CURLOPT_RETURNTRANSFER,1);

curl_setopt($handle, CURLOPT_HEADER, 0);

curl_setopt($handle, CURLOPT_FORBID_REUSE, 1);

curl_setopt($handle, CURLOPT_FRESH_CONNECT, 1);

if($rate_data = curl_exec($handle)){

curl_close($handle);

//return TRUE;

}else{

// exit('Error executing cURL session. <a href="java script:history.go(-1)">Go Back</a>');

echo 'trouble';

}

}else{

// exit('Error initializing cURL session. <a href="java script:history.go(-1)">Go Back</a>');

echo 'more trouble';

}

 

$pieces = explode(" ", $rate_data);

// each run over each of those blocks

foreach($pieces as $block){

// break the block into three lines, using the new line as a break point

$temp_array = explode("=", $block);

// use the first of those lines as a "key" for easy access, then put the rest in an array

$rate_array[$temp_array[0]] = array($temp_array[1], $temp_array[2]);

}

 

// to display the rate

echo $block_array[Rate][0];

 

 

I will probably have the new contrib uploaded tomorrow, but would like to know any alternatives and I will try to implement them into the contrib, so hopefully everyone can use it.

 

lildog

 

 

 

Well... can you say "spoke too soon"? That is, it DOES work, but it caused an issue with usps, and potentially ups shipping contribs. You said you're having response-time issues? I had that too! 2+ minutes - I measured. Read on...

 

In /catalog/includes/functions/general.php...

REPLACE line 315

global $customer_zone_id, $customer_country_id, $billto, $sendto, $cart, $customer_id;

WITH:

 global $customer_zone_id, $customer_country_id, $billto, $sendto, $cart, $customer_id;

 

 

JUST AFTER line 326, INSERT:

	//DBK: WA State Sales Tax Modification
//Lookup WA State Sales tax from WA Department of Revenue website

if (tep_get_zone_code($customer_country_id, $customer_zone_id, "") == "WA") {
	//this customer is from Washington State,
	//so attempt to get their tax rate based on order destination

	//check to see if customer's address is available
	$tax_address_query = tep_db_query("select ab.entry_street_address, ab.entry_city, ab.entry_postcode from " . TABLE_ADDRESS_BOOK . " ab left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) where ab.customers_id = '" . (int)$customer_id . "' and ab.address_book_id = '" . (int)(($cart->content_type == 'virtual' ) ? $billto : $sendto) . "'"); 

	if (tep_db_num_rows($tax_address_query)) {
		//we have some address information for this customer, so find their tax rate
		$tax_address = tep_db_fetch_array($tax_address_query);
		$url = "http://dor.wa.gov/AddressRates.aspx?output=text";
		$url .= "&addr=".urlencode($tax_address['entry_street_address']);
		$url .= "&city=".urlencode($tax_address['entry_city']);
		$url .= "&zip=".urlencode($tax_address['entry_postcode']);

		//this code uses httpRequest object, so include class
		require_once("includes/classes/http_client.php");

		$headers = array('host'=>'dor.wa.gov');

		$http = new httpClient();
		$http->Connect("dor.wa.gov", 80) or die("Connect problem");
		$http->setHeaders($headers);
		$status = $http->Get($url);
		if ($status == 200) $resultString =  $http->getBody();
		$http->Disconnect();

		if (!empty($resultString)) {
			$results = explode(' ', $resultString);
			foreach ($results as $key=>$value) {
				$breakPosition = strpos($value,"=");
				$newKey = substr($value,0,$breakPosition);
				$newValue = substr($value,$breakPosition+1);
				$results[$newKey] = $newValue;
			}
			if ((int)$results['ResultCode'] < 3) return $results['Rate']*100;
		}
	}
}
//end Washington State Tax Lookup Modification

 

In /catalog/includes/classes/http_client.php...

COMMENT OUT Lines ~423 - 426

 $buffer = fread($this->socket, 128);
	  $counter++;
	  usleep(2);

 

INSERT just after line 426 (usleep(2)), above

 $counter = 10;

 

 

This works, BUT commenting out those lines in http_client.php breaks the ups/usps modules which also use http_client. I'm not sure how to proceed. I commented those out because I was getting huge time delays on response. When I output the values of relevant variables, I thought these lines were causing a read beyond the end of the file. That is, I was getting unread_bytes = 0, so why perfom another fread? If you can shed any light on this, I'm interested. Will post more if I get something.

Share this post


Link to post
Share on other sites

×