Super California Sales Tax Add-on

To all my fellow Californians:
I've modified two add-ons: one from mzimmers (zc forum poster) and the Order History Export mod (found in the official add-ons section) to make it easier to apply and record CA sales tax. These are the current rates found on the BOE website effective Oct 2010. I haven't included a db change file for the tax rates change effective April 1 yet. If there is enough of a response, I'll post the file later.

The required files in this add-on apply sales tax according to the billing city and on the invoices/order details, the tax is labeled according to county (since the BOE needs to know which counties to send the money to). Your bookeeper will love you for this. Example: Order from Granada Hills, CA --> Los Angeles County CA 9.75% Tax: $xx.xx on the tax line in the order.

The optional files are to export the following information into an excel spreadsheet: order #, date, customer name, address, county and rate, subtotal, tax, shipping and total. The possibilities for this are endless. You could easily sort the lines by alphabetizing the "County" column then use a function to total up how much tax was paid. Again, a bookeeper's heaven.

There are no core file modifications, but a new table will be added to your database.

KNOWN BUGS

1. In Super Orders 3.0, the tax does not get listed in the horizontal column of order details/invoice, however it is still in the vertical column with subtotal and shipping charges.
2. The same issue is found during checkout and in the customer’s account details. However, this is fixed by taking out a few lines of code. If this is an issue for you (ie you only tax in CA), let me know and I'll post the solution here.
3. The Export History page does not mark which orders have previously been downloaded; for the moment, simply take note of which order you left off with on the last download. This is my highest priority to fix.
4. The Export History exports all orders, not just CA. Not sure if this is a good thing or just annoying.

I hope to have time to work on the bugs soon, but since it does work and does the job just fine, I thought I'd post it here in case someone else wanted it in the meantime.

This has only been tested with ZC 1.3.9h. I have no reason to believe that it won't work in older versions, but if there are issues, post them here and I'll try to help. Y'all can be the guinea pigs :)
This also works with the Tax Exempt Add-on.

Help yourself and let me know about issues!:clap:

I installed the CA Tax Rate table and at checkout process, the local CA tax rate was pickup correctly, however, the total order amount does not equal to the amount each line add up. Below is an example:
Sub-Total: $39.60
San Bernardino County CA 8.75% Tax: $3.47
United Parcel Service (Ground): $12.07
Total: $55.48

Obviously, the total sales amount should be $55.14 instead of $55.48. Do you know which part of the calculation went wrong?

Please post your module to the Free Addons section.

hliang,
Well, since the tax is calculated properly, I'm not sure. Can you tell me what all the settings are in your Order Totals? (Admin -> Modules -> Order Total).

Wendy

I have found several issues with this module.

1. When the low order fee module is Enabled. The cart total did not add the low order fee, example:
   Sub-Total: $35.00
   Low Order Fee : $3.00
   Los Angeles County CA 9.75% Tax: $3.41
   United Parcel Service (Ground): $10.21
   Total: $48.62 (incorrect, suppose to be $51.62)

2. I have also implemented discount coupons. When this "Super CA sales tax" installed, the coupon will not be added into the total amount either. Example:

Sub-Total: $276.20
Discount Coupon: 58e54e5c16 : -$13.81
Los Angeles County CA 9.75% Tax: $26.93
United Parcel Service (Ground): $13.37
Total: $316.50 (incorrect, suppose to be $302.69)

I have tried to change the sort order under order total into many different ways, no luck.:(

If I set customer to tax exempt ALL (under edit Customers), means customer is a wholesaler, this calculation problem go away.

Finally, I have removed this MOD, the lower order fee and coupon discount are all working properly. I think this MOD is either not structured correctly, or it is conflict with "Customer Tax Exempt" module. They cannot be coexist.

I am going to install "Local Sales Tax Mod" to see if I can get better luck.

No, there is no conflict with the Tax Exempt Mod; it works fine for me.

I will try to re-create your issues tonight and see if I can find a solution!

Just out of curiosity, do you have the standard zen-cart tax module enabled or disabled?

Thanks for helping me researching out the problem. Yes, i have the standard zen-cart tax module enabled. I put the Super CA Sales Tax Add-on sort order smaller than Zen standard tax module.

I have tried to install the "Local Sales Tax Mod" as indicated in my last message. I got the same exact problem in calculating coupon and low order fee. So I had to remove it as well.

By removing both module and leave the standard tax on, the total all added up correctly. I just couldn't get either local tax mod to work with them. :no:

hliang,
I know it is frustrating, but I suppose the good thing is that I am able to recreate the problem :wacko:

I am working on finding a solution! I know what the problem is...it's just getting a good and proper fix for it. My site goes live on Monday, so I do have a lot of motivation to get this working.

If you'll bear with me for a few days longer, I will post what I find.

wendyandmilo, great to hear from you so quickly. Thanks in advance in posting the solution. Also, begin July 1, 2011, California will have a new tax rate table. Will you have time to post the new datasheet also?

hliang,
Yup, I have the new tax table all ready; I just have to package it up as an upgrade. It will be ready before July 1st. But first things first...

Still working on the fix :blush:

hliang,
ok, try this for the coupon fix. Just upload the two files to your cart.

Order_total modules:
ot_city_tax: 250
ot_coupon: 280 (false, false, standard)
ot_shipping: 400
ot_subtotal: 100
ot_tax: 300
ot_total: 999

Let me know if that works for you! If it does, I'll see about the low order fee and whatnot.

Wendy

Just a note about the mysql files provided with this addon. There is a syntax error in the 1st file:

This line has a comma at the end

INSERT INTO local_cities (city, rate, county) VALUES ('Base Line', 8.75, 'San Bernardino County'),

Should be:

INSERT INTO local_cities (city, rate, county) VALUES ('Base Line', 8.75, 'San Bernardino County');

with a semi colon. Otherwise, it will not import.

FYI

Ack, so sorry about that!

I finally finished the tax updates - I know, they're a little late.

The attached file should work fine :lookaroun

I get this error when trying to check out:

1146 Table 'she1117409494446.local_cities' doesn't exist
in:
[SELECT rate FROM local_cities WHERE city='Placentia']

I checked the DB with phpMyAdmin and Placentia is there.

SQL query:
SELECT*
FROMzen_local_cities
WHEREcityLIKE CONVERT( _utf8 'Placentia'
USING latin1 )
COLLATE latin1_swedish_ci
LIMIT 0 , 30

Oh, by the way , I had to get rid of a double entry issue in the 2011 tax update for Baker and Visalia but it was no big beal.

I'll uninstall untill I hear from you.

Nadia

Ok.
"zen_local_cities" isn't the same as "local_cities", so try renaming the table to just "local_cities". There shouldn't be any prefix on it.

Double check your Visalia entry. The original one had a misspelling. The update has an INSERT entry with the correct spelling and...it looks like I forgot to DELETE the misspelled entry. Sigh. I don't know what happened with Baker though.

Working 70+ hrs/a week and updating 1600 line items apparently doesn't mix well :/

Ahh, Works Awsome now!!! Thanks!

Hi. Thanks for this module.

I always feel rusty when I approach the modules installations in Zen Cart. I didn't comprehend the meaning of posts #12 and #13 until I received the error message, so I thought I would post it for those who might not understand that they need to download the attachment in #13 and use it in place of the file that still resides in the module as of my date of download in July:

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 zc_local_cities (city, rate, county) VALUES ('Bass Lake', 8.75, 'Mad' at line 1
in:
[INSERT INTO zc_local_cities (city, rate, county) VALUES ('Base Line', 8.75, 'San Bernardino County'), INSERT INTO zc_local_cities (city, rate, county) VALUES ('Bass Lake', 8.75, 'Madera County');]
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.

Since the download still had the 5/11 date I thought perhaps it was okay to use as is, but that is erroneous.

I now have a question based on my timidity in trying to upload: I am assuming if I go again to Admin->Tools->Install SQL Patches and upload the new file from #13 I will possibly get duplicates. So I must try to summon the courage to insert the corrected line into PHPMyAdmin directly. I am always so rusty it takes a great deal of adrenoline and courage for me to do such a thing.

I welcome any comments on the best approach--re-download whole file or whatever one does to insert in PHPMyAdmin (I will be trying to figure this out shortly), but I probably will have either messed up my database or somehow survived the insert process by the time anyone comments.

I just wanted to post this to help someone else who doesn't understand the importance of posts #12 and #13 to bypass this error or in the alternative, be able to read someone's instructions on what, exactly, to do when the error occurs.

But perhaps the error will have been corrected in the download by the time the next person installs the module.

Notageek

Looking at the "Local_Cities" table, I see that there are only 4 pages; it uploaded everything until the city, "Base Line," and then stopped the upload from Admin->Tools->Install SQL Patches.

So, I suppose I could just delete all the insertions up to that point, add the semi-colon mentioned in post #12, and try the Admin->Tools->Install SQL Patches again for the file #1.

I am uncertain of instructions for inserting the whole file directly into PHPMyAdmin.

I'm posting this in case it helps someone else get farther than I have done so far without repeating these problems.

Notageek

It worked to delete the insertions up to the city "Base Line" and then save and insert the modified file via Admin->Tools->Install SQL Patch.

I did keep the SQL instructions at the top of the file to create the table if it didn't exist, so I did receive the error message that the table already existed.

Thanks again for the module and for sharing it.

Notageek

Hi. My zone for California has a red button which means it is not configured. Is that correct, or should it have a green button? I think the green button is there if a zone is configured for a single tax rate, so it would make sense that this module would bypass the need to set the tax rate and show itself to be not configured.

Thanks for any explanation.

Notageek