Local Sales Tax Mod (with admin!) for testing

Yes but you wont be overwriting the folders you will simply be copying the new content into the folders. The sales tax mod does not overwrite any files.

LadyHLG

Hello and thank you so much for the mod! If we can get it working right, it'll be such a help. We're located in California, so obviously something like this is definitely appreciated.

So, I'm having a problem getting it integrated and hopefully you can help. We've installed the mod into our database and I modified an sql file with all the California zip codes (all 2606 of them!) to fit the format of your own 'LocalSalesTax.sql' file, specifically this line:

INSERT INTO tax_rates_local (zone_id,local_fieldmatch,local_datamatch,local_tax_rate,local_tax_label) values (12,'postcode','90001',8.2500,'LA Zip Tax');

Everything seemed to work fine, but now there's the following message in the newly created 'local sales taxes' section of the 'Locations/Taxes' module:

"1054 Unknown column 'st.local_tax_shipping' in 'field list'
in:
[SELECT st.local_tax_id, st.zone_id, st.local_fieldmatch, st.local_datamatch, st.local_tax_rate, st.local_tax_label, st.local_tax_shipping, z.zone_name, z.zone_country_id FROM tax_rates_local st LEFT JOIN zones z ON st.zone_id = z.zone_id limit 0, 20]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields."

Any idea of why we'd be getting this error and how to fix it? Thanks in advance.

It looks like you are missing one of the table fields called "local_tax_shipping'".
What version of the mod did you install?

LadyHLG

Hello Lady and thanks for the response. My partner had used the earliest version of the mod and has now upgraded to version 2.1. The error message is no longer an issue.

Now, we have the proper installation for adding zip codes and tax rates to the 'Local Sales Taxes' section of the 'Locations/Taxes' menu. How would you suggest populating that section with all of the info we have? There are soooo many zip codes and tax rates in California, and we would really rather not have to insert them all manually.

I have an sql file that has all of the zips and rates in it already, but would need to reformat it to work with your mod, I'm thinking. Can you let me know what the formatting should be so that they import seamlessly?

Thank you!

These are all the fields for the Local Tax Mod

Field Type

**local_tax_id int(11) **
Auto number dont need to do anything

**zone_id int(11) **
*Zone ID # for California if you dont know the number you will have to find it in the "zone" table
example: 12
*

*local_fieldmatch varchar(100) **
this will be the same for for all recs since you are matching by zip
example: postcode
*
**
local_datamatch varchar(100)
*The actual zip code or zip code ranges
example: 90012
*

local_tax_rate decimal(7,4)
*Tax rate to 4 decimal places
example: 8.250
*

local_tax_label varchar(100)
The label you want to appear next to each tax on the checkout page
example: Los Angles Sales Tax

local_tax_shipping varchar(5)
*Are you applying sales tax to shipping true or false
example: true
*

local_tax_class_id int(1)
*ID for the tax class usually for Taxable goods
example: 1
*

I hope that helps, you will need each of those field completed for each zip code record.

LadyHLG

Hello again! I've created my SQL file with the various zip codes and tax rates. I attempted to upload the file using the 'Install SQL Patches' tool in the Zen Cart Admin.

I receive an error stating: "1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Ôtax_rates_localÕ (zone_id,local_fieldmatch,local_datamatch,local_ta' at line 1"

Can you take a look at the attached SQL file to see if you can figure out where the error lies?

The mod works fine for me when I enter single zip in the search data. How do I enter more than one zip code in the search data?

I enter "94505;94508" using semi-colon as specified in the Upgrade2.1 readme. But it does not pick up for 94508.

Also, will "94500-to-94505;94508" work?

artcoder and everyone else,

The way the delimited list option was coded into the mod does not allow for mixed entries like that however, as it appears everyone wants to put as much data as possible into each record I have reworked the code to allow for both single zip codes and zip code ranges mixed into a single record.

This should allow for zip code entries like:
53528;53711-to-53720;53524;54302-54310

Which mixes both the ranges and the single entries in one comma delimited list.

I am posting it here for people to test out before I post this to the downloads. I have done as much testing as I have time for now and would appreciate some help from everyone to be sure its working correctly.

Please give it a try and if you run into problems be sure to keep track of specific problems so I can tackle them.

The attached file should be unzipped and uploaded to *includes\modules\order_total* and will overwrite the current file. BE SURE TO BACK UP before you overwrite anything so you can rollback to the previous version if there is a problem.

One more thing, as before, the list in any zone should not overlap with any ranges, single zip code, or any lists in any other zones, or the tax will be calculated twice!!!

LadyHLG

LadyHLG:

This should allow for zip code entries like:
53528;53711-to-53720;53524;54302-54310

Thanks LadyHLG! Works great. I have tried various case scenarios and was not able to find any bugs with it.

Although I must warn users to make sure they do not construct overly-long search strings. Otherwise it will get truncated and not do as you expect. Always check after submitting the search string that it displays out the full search string without getting truncated.

artcoder,
Thanks for the update and the tip! Because the mod wasn't origionally designed to hold so much data in the field where the zip is entered its truncating it. People can always change the database field to avoid the problem.
Personally I think cramming so much data into one field will make updating later a nighmare, but, as they say, to each his own!
I will leave this posted here for a few more days before adding it to the downloads incase anyone else runs into issues.

Have a great weekend!

LadyHLG

LadyHLG:

I'm sorry the mod doesn't do quite what you were looking for, we all seem to be struggling for different solutions to the same problem.

The simple truth is there simply is no way for the cart to know what county a person is in based only on the zip code. Some people choose to tax by zip anyway since its the best they can do under the circumstances, and for some its not a problem.

In Wisconsin I set up each county as a separate zone with separate taxes rather than a single WI zone, and that has worked just fine.

I think the best we can hope for is that the developers at some point will add "county" as one of the permanent address options. Until then we are left with mods and work arounds.

LadyHLG

Hello All,

I recently discovered a bug in the core files that has now been corrected. In Admin, the customer file said 'Suburb', externally the SIgn-Up area said 'Address 2'. I changed the labels on both of those to state "County" so that when a customer is filling in the form, that line description is described as"County - Calif Only". That gives me the field to be able to pull from. I am generating an Excel matrix for State/County/City(Zip) taxes. Once completed, it should be importable as a CSV, the County can be pulled from the redefined 'data field' suburb/address 2 ?

Hello Lady HLG,

I have a new issue.. I needed to redo a scratch build reload of all files due to a failing mod. When I got back to yours, I ran-up the SQL patch and it saif that it 'Failed' ? Since I did not redo the exisiting databses through MyPHPadmin, I fugured it was complaining about it already being there ? I went in, deleted the xxx_tax_rates_local table, reran the SQL and it failed ? I am perplexes as to what to clean-up to complete the reinstall of your mod ? Help please ?

Try opening the sql file and copying the sql statement and pasting it into the text field on the Install Sql Patches page rather than uploading the file. For some reason some people have issues with the sql file and some don't.
Let me know if that works.

LadyHLG

Hi all,

Thanks for this mod. I think it may be my salvation.

I swear I had it working last night but I must have changed something that broke it. Go figure.

Am I correct in the following?

• Local sales tax follows cart setting for whether tax is charged on billing or shipping address (I use shipping).

• Zone tax and local tax can include tax on shipping service. (I do)

• Local sales tax (.5%) should be compounded to zone tax? (5%) for a total tax of 5.5%

My zone tax appears as zero dollars but my local tax is correct. Have you run into this before? My zones are set up correctly. What would you look at to troubleshoot this?

Thanks
Amy

Nevermind. Problem was in the Zones Definition, as expected. I recreated it and it works now.

Thanks again
Amy

I was just about to suggest checking over the zones. Glad you have it sorted out.

LadyHLG

LadyHLG:

Try opening the sql file and copying the sql statement and pasting it into the text field on the Install Sql Patches page rather than uploading the file. For some reason some people have issues with the sql file and some don't.
Let me know if that works.

LadyHLG

Hello Lady HLG,

I tried the drop and pray, but my prayers weren't answered. :(

I rebuilt the table from scratch and all seems to be in order ? I do have some enlightening news for California stores.. I have compiled, from the latest Tax Tables, from the State Board of Equalization -AND- the US Post Office, a CSV that has the following; City, County, Zipcode and specific Tax Rate for each city/town(ship). Hopefully, you will be able to use it to do a final integration that will P/U on the Zipcode for the shipping address, and charge the tax accordingly ? There is 1545 towns in the CSV, some having only a single zip, many having multiple zip codes, still on a single line. I may need to cleanup the delimiters a little bit due to the fact that, by habit, I always put a space after a comma ? Let me know ? I feel that this will be a contribution to the cause, once you, or another contrib member, work out the fine details ? I also added an extra data line to your table called entry_suburb which matches the data field in the address_book, which seems to be where the 'Sign Up' customer information gets stored ? I will be your guinea pig, online or off. California is cracking down and the penalties are getting worse.

I'm not sure I understand the point of the extra field for subburb. What are you using it for?

LadyHLG

LadyHLG:

I'm not sure I understand the point of the extra field for subburb. What are you using it for?

LadyHLG

Initially when I started creating the Data file, I wasn't sure that I could get a tax rate for EACH city or township, so I was thinking that I could use the fallback Suburb field for the County tax rates that the township sat within to get closer to the true rate vs. the state base rate. Make sense ?

Have you considered setting up zones for each county instead? That way you can tax by county and one other sub-division such as zip or city for those zips that span multiple counties.

Just another way to tackle the issue.

LadyHLG