Jump to content

Archived

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

kidsngarden

WA state destination based sales tax

Recommended Posts

Sorry for the double post, the quick edit feature doesn't seem to be working.

 

I managed to call the right file, now I get the following error message instead of the upload interface:

 

WA State Destination-based sales tax databases

1146 - Table 'shoppingfko.TABLE_WA_ZIP4' doesn't exist

 

select period from TABLE_WA_ZIP4 limit 1

 

[TEP STOP]

 

I logged back into phpmyadmin and tried to re-upload the sql, it spit out that it was already in existance so that verifies it's in place and in tact. The other cart I did some slight revision to prior, nothing major, but I also did install the fedex freight shipping module on it, which may have something to do with this actually functioning due to how it revised it all in the first place if this is any help.

 

Those that are new to using phpmyadmin, what you do is log in typically through your control panel, select the database you used when you installed the cart, click the SQL tab and a form field will show up, I'd add that step as being necessary within the original read me file along with the instructions I'm adding here to get more people on board. From there you are copying the sql text supplied with the original download of this contribution and paste it into the box. Click the Go button and you have just ran it. In this case, both were given on the same file, I just copied and pasted them both seperately and ran two different sql's. We need as many trying this out as possible to work out all of the bugs. This is something not even the largest players in this industry along the expensive plug and play shopping cart arena, this contribution and it's development is showing what a huge advantage we have working with this awsome open source shopping cart.

 

Also, one more thing that the new people will find helpful, get textpad or some other non windows based editor, you'll see it has huge advantages over the notepad or wordpad, it also keeps the file in tact better because windows adds hidden characters to your code and source files which can cause them to have issues or become completely non functional which does account for many headaches typically dealt with in this scene.

Share this post


Link to post
Share on other sites
Sorry for the double post, the quick edit feature doesn't seem to be working.

 

I managed to call the right file, now I get the following error message instead of the upload interface:

 

WA State Destination-based sales tax databases

1146 - Table 'shoppingfko.TABLE_WA_ZIP4' doesn't exist

 

select period from TABLE_WA_ZIP4 limit 1

 

[TEP STOP]

 

I logged back into phpmyadmin and tried to re-upload the sql, it spit out that it was already in existance so that verifies it's in place and in tact. The other cart I did some slight revision to prior, nothing major, but I also did install the fedex freight shipping module on it, which may have something to do with this actually functioning due to how it revised it all in the first place if this is any help.

 

Those that are new to using phpmyadmin, what you do is log in typically through your control panel, select the database you used when you installed the cart, click the SQL tab and a form field will show up, I'd add that step as being necessary within the original read me file along with the instructions I'm adding here to get more people on board. From there you are copying the sql text supplied with the original download of this contribution and paste it into the box. Click the Go button and you have just ran it. In this case, both were given on the same file, I just copied and pasted them both seperately and ran two different sql's. We need as many trying this out as possible to work out all of the bugs. This is something not even the largest players in this industry along the expensive plug and play shopping cart arena, this contribution and it's development is showing what a huge advantage we have working with this awsome open source shopping cart.

 

Also, one more thing that the new people will find helpful, get textpad or some other non windows based editor, you'll see it has huge advantages over the notepad or wordpad, it also keeps the file in tact better because windows adds hidden characters to your code and source files which can cause them to have issues or become completely non functional which does account for many headaches typically dealt with in this scene.

 

 

Thanks BrianMillar12!

 

It is great to see someone is trying to test it. Too bad you wern't properly informed to try it on a non-production server first! Sorry about the disconnect there. I will make efforts to make that a more pronounced warning for the next one.

 

As far as the SQL statement import, I think you did it fine by imorting it in two parts. I would like to make it work as one though. If you can, let me know how to make the sql file better for phpMyAdmin importing. It could be a version thing too.

 

On to the issue with the "...TABLE_WA_ZIP4' doesn't exist" issue. I think you still need to edit the file:

admin/includes/database_tables.php

 

And add the lines:

define('TABLE_WA_ZIP4', 'wa_zip4');
define('TABLE_WA_RATES', 'wa_rates');

 

If you add this, I think the admin section will begin to work for you.

 

 

It is not mentioned in the install file but for this new tax computation to work properly, you will need to make a tax zone/class/rate in the admin section. I just took all the Florida stuff and changed it to Washington and then made the rate 0.0%. If you add a rate here it will be combined with the rate coming from the destination based tax rate thereby charging about double the tax.

 

There are a few issues with my mod that I have discovered since I posted it. First, it has problems with certain addresses for strange reasons. I have fixed a few but I still need to work on making it better and still fast. Remember, this query is seasrching over 700,000 records each time. We don't want to have to do it more than once per sale. I tried to code it so that would be the case but the query gets pretty big. There is a happy median (that we will discover).

 

Another issue is my address parser. I had originally coded it to parse addresses in Thurston county. I am noticing there are some more strange addresses out there. For example, can someone explain to me what the street type AVCT is? Avenue-court? Hmmm.... Anyway, there may need to be some more abbreviations like that added to the parser so that it can do a better job of parsing all the addresses. The reason I included the parser is because you don't know what the customer is going to type. For example, if I lived at '1234 Main St W' I could enter "1234 West Main Street". If that was searched in the zip4 database, there would be no results because that address would be stored as "MAIN ST W". So, I parse what the customer types to extract the pieces of the address and then just use the street name to do a lookup and ignore the crud they may have supplied me. I could also use the extra crud, later, to choose a best match if I retreive more than one tax rate using the given streetname/zipcode.

 

Other problems exist with the address database itself. There are going to be addresses that just aren't in there. I will have to code in some catch to use a default rate or something (maybe it can be specified in the admin). In that (rare?) case, the order can be marked for manual processing.

 

Hopefully you all can test this with many addresses. I happen to have millions of valid addresses that I can use to feed into it for testing. I suppose others may only have small lists or none at all. Random addresses are hit and miss, mostly miss.

 

I can supply a tool that is seperate from the oscommerce system (uses the same address/rate DB tables). It just runs the query with an address and returns what it finds. If anyone wants this, let me know and I will post it somewhere. It uses the address parser and the oscommerce DB. It makes testing addresses a bit faster. I may try to automate my address list into it and see what kind of performance I can get from it as well as what percentage of not-found's there are. Hmmm...

 

This hack is only meant to help us all in the mean time until there is a better solution (i.e. the state offers an automated look up tool). They have the tool, just no automated interface is available yet. They probably need to do a seriously huge server upgrade to make that a possibility <_< (my guess...) I was just thinking... they should call it "iRate". :lol:

 

I really think that we can make this a pretty good solution if we continue to work together. I am available to help to some degree. I will try to make the install more clear in the next releases. Do keep in mind that I am doing thisbasically as a personal challange and for the warm fuzzy feeling of helping others. There is a little money in it for me but not enough to grab my entire focus. ;-)

 

Please continue to check this forum periodically, I will be adding some betterments soon. And test! test! test! ;-)

Share this post


Link to post
Share on other sites

My last post gave me an idea (did you notice?).

 

I just pumped 10,000 addresses (some may be duplicates) into this thing to see how well it works. Here are the results:

 

Found: 9203 (92.03%)

Multiple Found: 414 (4.49%)

Not Found: 797

 

This is pretty promising! I was surprised to see it was higher than 90%.

 

Out of the 10,000 addresses only 797 were not found. out of the records found, there were 4.49% of those where there were multiple tax rates that would need to be further processed to find the correct one.

 

In a nutshell, this means that most of the time, it is going to be fast and find the proper rate.

Share this post


Link to post
Share on other sites
My last post gave me an idea (did you notice?).

 

I just pumped 10,000 addresses (some may be duplicates) into this thing to see how well it works. Here are the results:

 

Found: 9203 (92.03%)

Multiple Found: 414 (4.49%)

Not Found: 797

 

This is pretty promising! I was surprised to see it was higher than 90%.

 

Out of the 10,000 addresses only 797 were not found. out of the records found, there were 4.49% of those where there were multiple tax rates that would need to be further processed to find the correct one.

 

In a nutshell, this means that most of the time, it is going to be fast and find the proper rate.

 

Thank you for your tremendous help on this one Keith, "when" I need a programmer to help out, you will be the first person I get in touch with and I will make it worth your time fiscally. I feel bad with the run around I and my boss were given by the shopping cart vendors over this and some other matters, ie. promising the world, yet in the end, finding out they could not deliver so we are back to modifying os commerce.

 

Your efforts are very much apprieciated, I'll do what I can to help on this contribution just so you know that you are not alone on this matter.

 

I made the revisions you mentioned, the menue item is still not showing up in the admin interface, I can now call it directly, which is good enough, it's showing the upload interface correctly now.

 

I think that's a good idea for when the address is not listed, for it to default back to the state's flat rate setting, that should help temporarily address the issue, I look forward to testing out that revision from here.

 

Thanks again Keith, you rule!!!

Share this post


Link to post
Share on other sites
Thank you for your tremendous help on this one Keith, "when" I need a programmer to help out, you will be the first person I get in touch with and I will make it worth your time fiscally. I feel bad with the run around I and my boss were given by the shopping cart vendors over this and some other matters, ie. promising the world, yet in the end, finding out they could not deliver so we are back to modifying os commerce.

 

Your efforts are very much apprieciated, I'll do what I can to help on this contribution just so you know that you are not alone on this matter.

 

I made the revisions you mentioned, the menue item is still not showing up in the admin interface, I can now call it directly, which is good enough, it's showing the upload interface correctly now.

 

I think that's a good idea for when the address is not listed, for it to default back to the state's flat rate setting, that should help temporarily address the issue, I look forward to testing out that revision from here.

 

Thanks again Keith, you rule!!!

 

Thanks for your kind words. I am happy to help.

 

About your menu issue, I would check the syntax of the contents of the file:

admin/includes/boxes/taxes.php

 

Make sure you have properly applied the code I supplied. It should look like this at the bottom:

if ($selected_box == 'taxes') {
  $contents[] = array('text'  => '<a href="' . tep_href_link(FILENAME_COUNTRIES, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_TAXES_COUNTRIES . '</a><br>' .
	'<a href="' . tep_href_link(FILENAME_ZONES, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_TAXES_ZONES . '</a><br>' .
	'<a href="' . tep_href_link(FILENAME_GEO_ZONES, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_TAXES_GEO_ZONES . '</a><br>' .
	 '<a href="' . tep_href_link(FILENAME_TAX_CLASSES, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_TAXES_TAX_CLASSES . '</a><br>' .
	 '<a href="' . tep_href_link(FILENAME_TAX_RATES, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_TAXES_TAX_RATES . '</a><br>' .
	 '<a href="' . tep_href_link(FILENAME_WA_TAXES, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_TAXES_WA_TAXES . '</a>');
}

 

In the middle of the file. Make sure the dots are in the right places and everything.

 

But no biggie if you can get it to work directly, great. Have you had success uploading the database? This could be the tricky part as the State###.zip file size will likely exceed the allowable upload size of a default PHP install. You may need to modify your php.ini file (or add a directive to .htaccess?). The two settings that may need to be tweaked in the php.ini file are:

 

post_max_size = ###

upload_max_filesize = ###

 

If these two parameters are smaller than the 6MB+ State###.zip file size, then the upload may fail. BTW, after changing these settings in the php.ini file, the webserver will need a restart for the settings to take effect.

 

Also, because the file is unzipped and processed on the server-side, another PHP setting may need to be upped to allow a longer run time. The parameter in the php.ini file for this is:

max_execution_time = ###

 

I have mine set to 300 seconds (5 mins) but it depends on the speed and load of your server.

 

 

Let me know if this helps. I really want people to be able to test it. You can't test it if it can't be installed properly. I will check this forum periodically.

 

BTW, I too have had problems with the quick edit feature of this forum.

Share this post


Link to post
Share on other sites

Thanks for the additional help Keith. I'm focused upon the drop shipping module for today, but will follow up here with the tax module and impliment your help on it for better functionality tonight if I don't reach the burn out stage.

 

I was also thinking a simple direction to go would be to split up the large files into smaller units, just as I have it set up with the ezpopulate system I'm using on it as well due to the time out elements, I'll try your fix on that one as well so it might add more functionality to that element streamlining the process.

Share this post


Link to post
Share on other sites

I am new to this forum, but I am not new to PHP or e-commerce. I have been reading everyone's comments regarding destination-based sales tax, and wanted to offer you a solution. I have written roughly 5-6000 thousand lines of (working!) PHP code to implement the destination based sales tax system, and yes, it is extremely complicated and a pain, but it is manageable. Let me tell you a little bit about how it works and clear up a couple of items:

 

(1) For destination based sales tax to work, the user (ie your customer) needs to enter a valid street address and 5-digit zip code. Based on these two pieces of information, the DT code needs to look up the 4-digit zip code, return that code, and then look up the tax rates based on the full 9-digit zip code. Here are some of the issues with that process:

 

(a) What if our customers don't enter the 'right' street address, ie a street address that isn't found in our database?

Address standardization is really the only way to handle this issue. Address standardization is probably the hardest part of this

whole process since it has to apply 'fuzzy' logic to get the job done.

 

The USPS does address validation and zip+4 lookup. However, they will only let you use the service for free if you are using the lookup

information to ship a package via the USPS. Otherwise it is rather expensive for access to the database.

 

(B) Once you get the address validation done, you are really only halfway there. You then need to look up the appropriate codes in the taxing

'boundary' area, and once you have the codes, do a lookup in the rates table depending upon whether the person is being charged general tax

rates or food and drug tax rates. After you have the rates, you apply them to your order just like regular sales tax. But how do you keep of

track of what tax was charged where?

 

© You need a recording system on your own website that tracks and organizes this information by zip code(s), order date, etc for your sales

tax reporting.

 

(d) I read that one user here was using a file gleaned from the washington state website with something like 730000 street addresses; I used

that to build the address validation for Washington state. Don't forget about the boundary table and rates table. Washington state has one

of the larger boundary tables with over 1.4 million records. Fortunately the rates table is somewhat smaller.

 

With the process I have built, the lookup, recording, and application of this information takes 5-6 seconds, which includes hooking into two different databases, retrieving the information, applying it to the customer's order, and recording the information.

 

Currently, we have a test website at themakeupstudio.net that implements the zip+4 lookup and address standardization. To see how it works, put something in the shopping cart on the site, go the checkout, and play around with entering the address and zip codes. Obviously you don't actually have to order anything, but it will let you see the system in action. Note the site won't actually charge DT tax until after July 01, 2008.

 

I am also working on the site destinationtax.com, which will provide information and calculators about the whole process including address validation, zip4 lookup, and a destination tax calculator. We will also be showcasing a method of sending XML to the destination tax servers and getting an XML response in return so that this whole process can be cross-platform, and not just limited to PHP-based websites.

 

If you have questions about what my scripts can or can't do, feel free to email me at jeromastheking@yahoo.com.

 

Cheers,

Jerry

Share this post


Link to post
Share on other sites

I am a bit late but ready to help out. I am a moderate PHP programmer in Wa state. Until I saw you guys working on this I had resolver to charge the lowest rate and make up the difference by raising prices slightly. I know of at least 1 large company going that route. But that is not really the way I do things, all or nothing! Now that I am done pouting and swearing about this ridiculous law I will offer whatever I can to this project..... On a side note issuing frequent updates to the add-ons site MAY draw more people in as the due date draws nearer.

 

lildog

Share this post


Link to post
Share on other sites

New York has had destination-based sales tax for years, and specifically says DO NOT USE ZIP CODE TO DETERMINE TAX RATE! A given ZIP code (Post Office) can serve up to three counties, each with a different rate, or can span a city boundary (with two different rates). A few towns are served by a Post Office across the border in another state (and vice-versa)!

 

The state publishes a bunch of tables (PDF files) that I've been trying to beat into a usable database. They used to publish tables of address ranges for selected cities, to show you where the city's tax rate ended and the county's began, but they've withdrawn those tables! Now they have an online system where you type in the street address and the ZIP code, and it tells you the general sales tax rate. Unfortunately, there does not appear to be an API where you could call it from your shopping cart! Then they keep wondering why businesses flee New York state!

 

Anyway, without address range mapping for almost every town and village in the state, it's impossible to accurately tell what the sales tax rate should be. It can't even be done manually, as the information is not published. New York has yet to enter the age of mail order, much less telephone, fax, or the Web! Does anyone have an acceptable solution (I don't think they'll accept ZIP code to rate matching)? Failing that, is there an easy way to tell osCommerce to hold orders bound in-state and not collect payment, until I can manually figure out the sales tax and tell the buyer the rate?

Share this post


Link to post
Share on other sites

One of our clients (an online retailer) informed us of this a week ago. Unfortunately there was not enough time to implement this for the very reasons expressed in this thread. Their site uses 5-digit zip codes. Fortunately for them, very few of their sales are in-state taxable items. In the interim, we're using the business location (rule #5) to determine the tax rate as suggested in the link below. It's a stretch but there really is nothing we can do in such a short amount of time.

 

http://dor.wa.gov/Content/FindTaxesAndRate...rcingRules.aspx

Share this post


Link to post
Share on other sites

In catalog/includes/classes/shopping_cart.php:

global $customer_id, $sendto, $ship_zip, $ship_addy;

 

I think should also have:

global $currencies;

 

I only have a copy of oscommerce-2.2rc2a. The original has it.

 

 

I may be wrong......

 

lildog

Share this post


Link to post
Share on other sites

Also in rc2.2rc2a

 

the $tax_address_query in catalog/includes//classes/order.php is different now. I am working on updated instructions.

 

lildog

Share this post


Link to post
Share on other sites

Is anyone working on an update? If not I have an update with new instructions. The code is the same just updated instructions.

 

lildog

Share this post


Link to post
Share on other sites

It seems that pre PHP 5 doesn't support the public variable keyword... For those in the know what does the public keywrod do exactly?

 

lildog

Share this post


Link to post
Share on other sites

Brian,

you need to add to catalog/admin/includes/laguages/english.php

 

////BOF Washington State Tax Modification

define('BOX_TAXES_WA_TAXES','WA Taxes');

////EOF Washington State Tax Modification

 

 

this should fix the display menu problem.

Share this post


Link to post
Share on other sites

there is no DIR_FS_TMP to upload to. It needs to be defined somewhere.

Share this post


Link to post
Share on other sites

in admin/wa_tax.php

AFTER:

require('includes/application_top.php');

 

ADD:

define('DIR_FS_CATALOG', '/catalog/tmp/');

 

Then be sure you have a catalog/tmp folder...

Share this post


Link to post
Share on other sites

now...

PHP Fatal error: Cannot instantiate non-existent class: ziparchive in /catalog/admin/wa_taxes.php on line 92. My phpinfo says I have zlib zip enabled. This is probably the problem....so I guess I will try to write a simple class to do this for me.

Share this post


Link to post
Share on other sites

This appears to be a php 5 issue. But I do have zlib enabled. I will try to retrofit.

 

lildog

 

now...

PHP Fatal error: Cannot instantiate non-existent class: ziparchive in /catalog/admin/wa_taxes.php on line 92. My phpinfo says I have zlib zip enabled. This is probably the problem....so I guess I will try to write a simple class to do this for me.

Share this post


Link to post
Share on other sites

for those without ziparchive class...I have almost got an alternative working.

Share this post


Link to post
Share on other sites

Just uploaded a newer version....servers with pre php5 can now load rates into database.

Share this post


Link to post
Share on other sites

Ok so I am getting time out issues with the zlib version. I have found a script I am going to modify that downloads the script directly from the DOR site. Any hint tips or ideas would be great.

Share this post


Link to post
Share on other sites

What my company is going to do, until a reliable tax module is ready, is charge the base 6.5% state tax, and add a 3% "processing fee" for Washington state orders ... orders to WA will have to be hand-processed at tax time, and that will require extra employee hours, and therefore cost us more.

 

It's not too different than raising prices to cover the new tax rates, but it's limited to only WA orders, and they can't legally prevent a business from charging whatever fees they want.

 

Also, WA Department of Revenue has a survey online, where internet retailers can tell the DOR what they think of the new system:

 

https://fortress.wa.gov/dor/efile/SecureFor...px?surveyid=114

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×