Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Problem w/ NY State zip tax contrib


milauskas

Recommended Posts

I've tried a couple times to use the NY State zip code tax contribution, however, here's what happens.

 

I followed the install instructions (making a backup of files that I was replacing), then started PHPMyAdmin and added a new table by browsing to the ziptax.sql file which is part of the contrib. It appears to execute successfully, but when I go to the catalog I see that almost everything has disappeared from the index page except for the header and some left column items (Categories, Manufacturers, What's New?).

 

The following code is visible just below the What's New heading:

1146 - Table 'leepharm2.table_ziptax' doesn't exist

select zip_tax_rate from TABLE_ZIPTAX where zip_code = ''

[TEP STOP]

 

I am assuming it's telling me the table I just created doesn't exist. But it does. Can anyone tell me what may be happening or may have happened and what I can do to fix it. I REALLY need an easy way to charge tax by zip code and if I can get this to work it will save me DAYS of headache. Thanks!

Link to comment
Share on other sites

Here's an additional installation step which I didn't see mentioned in the instructions.

 

In the file catalog/includes/database_tables.php, insert the following:

 

define('TABLE_ZIPTAX', 'ziptax');

 

Try that and see if it fixes your error.

 

I've tried a couple times to use the NY State zip code tax contribution, however, here's what happens.

 

I followed the install instructions (making a backup of files that I was replacing), then started PHPMyAdmin and added a new table by browsing to the ziptax.sql file which is part of the contrib. It appears to execute successfully, but when I go to the catalog I see that almost everything has disappeared from the index page except for the header and some left column items (Categories, Manufacturers, What's New?).

 

The following code is visible just below the What's New heading:

1146 - Table 'leepharm2.table_ziptax' doesn't exist

select zip_tax_rate from TABLE_ZIPTAX where zip_code = ''

[TEP STOP]

 

I am assuming it's telling me the table I just created doesn't exist. But it does. Can anyone tell me what may be happening or may have happened and what I can do to fix it. I REALLY need an easy way to charge tax by zip code and if I can get this to work it will save me DAYS of headache. Thanks!

Link to comment
Share on other sites

Yes, it did. Thanks very much! This is the first time I've tried to do anything with the database tables (such as adding one) so I wasn't aware of this. Should I alert the author of the contrib?

 

Also, I tested out a transaction and I'm still not getting the tax added for the specific zip code(s). Here's the sql statement I ran in PHPMyAdmin:

 

 

-- phpMyAdmin SQL Dump

-- version 2.6.0-pl2

-- http://www.phpmyadmin.net

-- 

--

-- --------------------------------------------------------



-- 

-- Table structure for table `ziptax`

-- 



CREATE TABLE `ziptax` (
 `zip_id` int(11) NOT NULL auto_increment,
 `zip_code` varchar(32) NOT NULL default '',
 `zip_tax_rate` decimal(7,4) NOT NULL default '0.0000',
 `zipcounty` varchar(32) NOT NULL default '',
 `zipstate` varchar(32) NOT NULL default '',
 PRIMARY KEY  (`zip_id`)
) TYPE=MyISAM AUTO_INCREMENT=2790;



-- 

-- Dumping data for table `ziptax`

-- 



INSERT INTO `ziptax` VALUES (1, '94105', 8.7500, 'San Francisco', 'CA');

INSERT INTO `ziptax` VALUES (2, '92262', 7.7500, 'Palm Springs', 'CA');

 

Can you tell just at a glance if this is correct? I just altered the default NY values in the orginal sql file. I'm assuming osC should see the zip code a customer enters and calculate tax accordingly. Right now, I'm just getting charged basic CA state tax (7.25%). So, I'm wondering if there's a problem somewhere (the sql file?)

 

The only other things I've done are to replace 4 files with the 4 that came in the contrib.

Link to comment
Share on other sites

I just checked the original files for this contrib vs. the updated SQL file with the new NY sales tax rates. In the orig contrib files, the table created is called 'ziptax'. In the new SQL file the name of the table is now TABLE_ZIPTAX

I've tried a couple times to use the NY State zip code tax contribution, however, here's what happens.

 

I followed the install instructions (making a backup of files that I was replacing), then started PHPMyAdmin and added a new table by browsing to the ziptax.sql file which is part of the contrib. It appears to execute successfully, but when I go to the catalog I see that almost everything has disappeared from the index page except for the header and some left column items (Categories, Manufacturers, What's New?).

 

The following code is visible just below the What's New heading:

1146 - Table 'leepharm2.table_ziptax' doesn't exist

select zip_tax_rate from TABLE_ZIPTAX where zip_code = ''

[TEP STOP]

 

I am assuming it's telling me the table I just created doesn't exist. But it does. Can anyone tell me what may be happening or may have happened and what I can do to fix it. I REALLY need an easy way to charge tax by zip code and if I can get this to work it will save me DAYS of headache. Thanks!

Link to comment
Share on other sites

I just checked the original files for this contrib vs. the updated SQL file with the new NY sales tax rates. In the orig contrib files, the table created is called 'ziptax'. In the new SQL file the name of the table is now TABLE_ZIPTAX for some reason.

 

I bet if you rename the TABLE_ZIPTAX table in MySQL to 'ziptax' you will be all set. If that works then I can update that newest SQL file with the proper table name so this doesn't happen to anyone else.

 

Thanks!

Nathan

 

 

I've tried a couple times to use the NY State zip code tax contribution, however, here's what happens.

 

I followed the install instructions (making a backup of files that I was replacing), then started PHPMyAdmin and added a new table by browsing to the ziptax.sql file which is part of the contrib. It appears to execute successfully, but when I go to the catalog I see that almost everything has disappeared from the index page except for the header and some left column items (Categories, Manufacturers, What's New?).

 

The following code is visible just below the What's New heading:

1146 - Table 'leepharm2.table_ziptax' doesn't exist

select zip_tax_rate from TABLE_ZIPTAX where zip_code = ''

[TEP STOP]

 

I am assuming it's telling me the table I just created doesn't exist. But it does. Can anyone tell me what may be happening or may have happened and what I can do to fix it. I REALLY need an easy way to charge tax by zip code and if I can get this to work it will save me DAYS of headache. Thanks!

Link to comment
Share on other sites

There are a few more steps needed to get this mod working which may not be obvious. After several days of playing around with this, it was just last night that I figured out that I needed to add that additional line to database_tables.php.

 

Your SQL looks alright. What you need to do now is set up OSC to recognize your state as a tax zone or edit your existing tax zone to work properly with this mod. Note that the following steps describe what I had to do to get it working for my shop. Your set up may be a little different.

 

In OSC Admin, under Locations/Taxes -> Tax Zones, click the 'insert' button to create a new tax zone. Name it appropriately and give it a description like 'XX State Sales Tax'. Next, click on the folder icon next to the name of the tax zone you just created, then click on the 'insert' button. From the Country drop-down, select United States and then your state from the Zone drop-down, and then hit the 'insert' button.

 

Under Locations/Taxes -> Tax Rates, click the 'new tax rate' button to tie a tax rate to the tax zone for your state, or edit your existing tax rate if it's already there. Select a Tax Class Title of 'Taxable Goods'; set the Zone to the tax zone you just created in the previous step; set the Tax Rate to 0.00%; set the Description to 'XX State Sales Tax' or something similar, and set the Priority to 1. The trick here is that the Tax Rate has to be zero since the zip-code based tax will be added on top of this.

 

Since shipping is taxable in my state, there's an additional step that I had to do. Under Modules -> Shipping Modules, edit whichever module(s) you use to make the Tax Class 'Taxable Goods'. If shipping is not taxed in your state, you would select 'None'.

 

That got it working for me. Note that this mod is not perfect. In my state, South Carolina, some zip codes span across counties and can have multiple tax rates. This mod will select the first tax rate in the database table which matches the 'ship-to' zip code.

 

Good luck.

 

Yes, it did. Thanks very much! This is the first time I've tried to do anything with the database tables (such as adding one) so I wasn't aware of this. Should I alert the author of the contrib?

 

Also, I tested out a transaction and I'm still not getting the tax added for the specific zip code(s). Here's the sql statement I ran in PHPMyAdmin:

-- phpMyAdmin SQL Dump

-- version 2.6.0-pl2

-- http://www.phpmyadmin.net

-- 

--

-- --------------------------------------------------------
-- 

-- Table structure for table `ziptax`

-- 
CREATE TABLE `ziptax` (
 `zip_id` int(11) NOT NULL auto_increment,
 `zip_code` varchar(32) NOT NULL default '',
 `zip_tax_rate` decimal(7,4) NOT NULL default '0.0000',
 `zipcounty` varchar(32) NOT NULL default '',
 `zipstate` varchar(32) NOT NULL default '',
 PRIMARY KEY  (`zip_id`)
) TYPE=MyISAM AUTO_INCREMENT=2790;
-- 

-- Dumping data for table `ziptax`

-- 
INSERT INTO `ziptax` VALUES (1, '94105', 8.7500, 'San Francisco', 'CA');

INSERT INTO `ziptax` VALUES (2, '92262', 7.7500, 'Palm Springs', 'CA');

 

Can you tell just at a glance if this is correct? I just altered the default NY values in the orginal sql file. I'm assuming osC should see the zip code a customer enters and calculate tax accordingly. Right now, I'm just getting charged basic CA state tax (7.25%). So, I'm wondering if there's a problem somewhere (the sql file?)

 

The only other things I've done are to replace 4 files with the 4 that came in the contrib.

Link to comment
Share on other sites

Even though I got this working, I don't think I will 'go live' with it because of the problem of zip codes which overlap more than one tax jurisdiction. You run the risk of either undercharging on tax, or angering customers if you default to the highest rate for that zip.

 

Perhaps an even bigger problem is that if a customer inputs a zip code which is not in the 'ziptax' table (but which may be valid nonetheless due to changes that the USPS regularly makes) he or she will be charged 0% tax even though the zip falls within your state. I guess the way around that would be to set the minimum baseline tax rate for your state in 'Locations/Taxes -> Tax Rates -> (your state tax rate)' and then populate your ziptax table with the incremental amount above that baseline rate for each zip code. However, when it comes time to settle things with the tax man, you'll have to figure out exactly how much you should have charged that customer and perhaps make up the difference. Not a good situation to be in.

 

There's a detailed discussion about another way to do it here. It looks like it calculates taxes more correctly. A bit more work for the customer, but I guess you can blame that on complicated tax laws and those who enact them.

 

There are a few more steps needed to get this mod working which may not be obvious. After several days of playing around with this, it was just last night that I figured out that I needed to add that additional line to database_tables.php.

 

Your SQL looks alright. What you need to do now is set up OSC to recognize your state as a tax zone or edit your existing tax zone to work properly with this mod. Note that the following steps describe what I had to do to get it working for my shop. Your set up may be a little different.

 

In OSC Admin, under Locations/Taxes -> Tax Zones, click the 'insert' button to create a new tax zone. Name it appropriately and give it a description like 'XX State Sales Tax'. Next, click on the folder icon next to the name of the tax zone you just created, then click on the 'insert' button. From the Country drop-down, select United States and then your state from the Zone drop-down, and then hit the 'insert' button.

 

Under Locations/Taxes -> Tax Rates, click the 'new tax rate' button to tie a tax rate to the tax zone for your state, or edit your existing tax rate if it's already there. Select a Tax Class Title of 'Taxable Goods'; set the Zone to the tax zone you just created in the previous step; set the Tax Rate to 0.00%; set the Description to 'XX State Sales Tax' or something similar, and set the Priority to 1. The trick here is that the Tax Rate has to be zero since the zip-code based tax will be added on top of this.

 

Since shipping is taxable in my state, there's an additional step that I had to do. Under Modules -> Shipping Modules, edit whichever module(s) you use to make the Tax Class 'Taxable Goods'. If shipping is not taxed in your state, you would select 'None'.

 

That got it working for me. Note that this mod is not perfect. In my state, South Carolina, some zip codes span across counties and can have multiple tax rates. This mod will select the first tax rate in the database table which matches the 'ship-to' zip code.

 

Good luck.

Link to comment
Share on other sites

Well, I've gone through all the steps you detailed. I'm still not successful. Setting the CA tax to 0% only resulted in no tax being charged. I set it back to 7.25% and again was only charged that rate (no city tax was added). I will attempt to redo everything from the beginning to see if I missed a step, but I don't think so.

 

I backed up each file that I was going to replace with those from the contrib. I then copied each new contib file into their respective locations. I ran the sql statement to create the new table and altered database_tables.php to define the new table.

 

Have I left something out? I then modified the tax zones/rates as you described, but still no luck. It's really frustrating.

 

As far as the tax rates themselves, I'm using a document from the CA state board of equalization that gives all tax rates by zip code. I think this is fine; I just want to get the contrib working.

 

I'll continue my research, but if you can tell if I've overlooked something, let me know.

 

And thanks for so much assistance. I really appreciate it!

 

Mike

Link to comment
Share on other sites

I can only think of one more thing to check. Are all your products entered as 'Taxable Goods'? You can check that in the Catalog -> Categories/Products edit page for each item.

 

Well, I've gone through all the steps you detailed. I'm still not successful. Setting the CA tax to 0% only resulted in no tax being charged. I set it back to 7.25% and again was only charged that rate (no city tax was added). I will attempt to redo everything from the beginning to see if I missed a step, but I don't think so.

 

I backed up each file that I was going to replace with those from the contrib. I then copied each new contib file into their respective locations. I ran the sql statement to create the new table and altered database_tables.php to define the new table.

 

Have I left something out? I then modified the tax zones/rates as you described, but still no luck. It's really frustrating.

 

As far as the tax rates themselves, I'm using a document from the CA state board of equalization that gives all tax rates by zip code. I think this is fine; I just want to get the contrib working.

 

I'll continue my research, but if you can tell if I've overlooked something, let me know.

 

And thanks for so much assistance. I really appreciate it!

 

Mike

Link to comment
Share on other sites

I can only think of one more thing to check. Are all your products entered as 'Taxable Goods'? You can check that in the Catalog -> Categories/Products edit page for each item.

 

 

THANKS!!! that was my prob... oh my god!!! I have to put each item of my store with tax!!! oh my god... I have hmmm :blink: alot of items!!!! :o

 

Thanks :thumbsup: its pass midnight and you resolve my prob :D

Link to comment
Share on other sites

If you use phpMyAdmin or have some other way of manipulating the data in your mySQL database directly, you should go into the table 'products' and change 'products_tax_class_id' to 1 for each of your taxable items.

Another question please... Do we have a option to delete the NONE tax... so AUTOMATICLY everything have taxs??

 

That will help me so much!!!

Thanks in advance for you help!

Link to comment
Share on other sites

Here's something else you might try. OSC seems to be picky about the order in which shipping, taxes, etc are added onto the order total. Sometimes you get unexpected results when you change the order of things. I didn't have trouble with it this time, but I remember having to experiment quite a bit with Order Total to get another contrib to work properly. Sometimes things that should have been added into the total would drop out completely if they were not in the expected order.

 

Under Modules -> Order Total, I have the Sort Order values set in the following sequence: Sub-Total, followed by Shipping, then Discount Coupons (you may or may not have this), then Tax, then Total. Try changing the sequence of your Order Total modules and see if it effects the results.

 

I can only think of one more thing to check. Are all your products entered as 'Taxable Goods'? You can check that in the Catalog -> Categories/Products edit page for each item.
Link to comment
Share on other sites

If you use phpMyAdmin or have some other way of manipulating the data in your mySQL database directly, you should go into the table 'products' and change 'products_tax_class_id' to 1 for each of your taxable items.

 

 

Thanks for your help... I do have a phpmyadmin...

so I went in... I clicked on PRODUCTS and 'products_tax_class_id'

but I don't know where to put 1 to each of my items?

 

there is a print screen...

 

http://www.passionscrapbook.com/photosdiverses/php1.bmp

 

can you help me? Am I a the wrong place?

Link to comment
Share on other sites

Here's the easy way to do it with phpMyAdmin. When you first start phpMyAdmin, select your database (looks like 'passionscrapbook_com_01' from the screenshot you sent), click on the 'SQL' tab at top, and paste the following SQL statement into the box:

 UPDATE products SET products_tax_class_id = 1;

Click on the 'Go' button and that should do it.

 

Thanks for your help... I do have a phpmyadmin...

so I went in... I clicked on PRODUCTS and 'products_tax_class_id'

but I don't know where to put 1 to each of my items?

 

there is a print screen...

 

http://www.passionscrapbook.com/photosdiverses/php1.bmp

 

can you help me? Am I a the wrong place?

Link to comment
Share on other sites

Here's a new problem. Looking at your suggestion to change the sort order in Order Total, I see the following errors:

 

Warning: main(/Users/milauskas/Sites/lee2/catalog/includes/languages/english/modules/order_total/ot_shipping orig.php) [function.main]: failed to open stream: No such file or directory in /Users/milauskas/Sites/lee2/catalog/admin/modules.php on line 128

Warning: main() [function.include]: Failed opening '/Users/milauskas/Sites/lee2/catalog/includes/languages/english/modules/order_total/ot_shipping orig.php' for inclusion (include_path='.:/usr/local/php5/lib/php') in /Users/milauskas/Sites/lee2/catalog/admin/modules.php on line 128

Fatal error: Cannot redeclare class ot_shipping in /Users/milauskas/Sites/lee2/catalog/includes/modules/order_total/ot_shipping.php on line 13

 

Now, I suspect this may have something to do with my renaming the original files before copying the new ones from the cotrib to their locations. In the first two warnings above there are references to "ot_shipping orig.php." This is, of course, what I named the original ot_shipping file. Since I was placing a copy of ot_shipping.php in the same folder I thought there would be no problem. DO you think it may be because I'm working in Dreamweaver? Could renaming the original files have made a change in the pathnames used by osC? I guess I need to do some of my own research, but I'm asking anyway in case you have some insight.

Link to comment
Share on other sites

I removed the original files to another folder outside osC before copying the new contrib ones. THis solved my MySQL errors. I then tried to rrearrange the sort order for order totals, but still no luck. I'm at a loss. :'(

Link to comment
Share on other sites

Sorry, but I don't have any experience with Dreamweaver. Can't tell if that would make any difference.

 

Do you have any other files within your OSC setup that have spaces in the filenames like 'ot_shipping orig.php'? I don't know for certain, but the presence of those spaces may mess up the logic in either OSC or the contrib.

I removed the original files to another folder outside osC before copying the new contrib ones. THis solved my MySQL errors. I then tried to rrearrange the sort order for order totals, but still no luck. I'm at a loss. :'(
Link to comment
Share on other sites

Here's the easy way to do it with phpMyAdmin. When you first start phpMyAdmin, select your database (looks like 'passionscrapbook_com_01' from the screenshot you sent), click on the 'SQL' tab at top, and paste the following SQL statement into the box:

 UPDATE products SET products_tax_class_id = 1;

Click on the 'Go' button and that should do it.

 

 

THANKS!!! :thumbsup:

 

now I have another prob...

 

when i'm in catalog... I try to create a new file... I write a name and take a pic from my computer and there is what I get... so I have to go back... my file is there!! but no pic.. so I done EDIT.. try to put a pic again... and...

 

Warning: move_uploaded_file(/home/www/www.passionscrapbook.com/osboutique/catalog/images/logofichierjauneboutique.jpg): failed to open stream: Permission denied in /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/classes/upload.php on line 94

 

Warning: move_uploaded_file(): Unable to move '/share/tmp/phpQfymNu' to '/home/www/www.passionscrapbook.com/osboutique/catalog/images/logofichierjauneboutique.jpg' in /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/classes/upload.php on line 94

 

Warning: Cannot modify header information - headers already sent by (output started at /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/classes/upload.php:94) in /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/functions/general.php on line 18

Link to comment
Share on other sites

Please take a look at this thread:

 

Permissions when uploading images to catalog

 

and see if it helps.

 

THANKS!!! :thumbsup:

 

now I have another prob...

 

when i'm in catalog... I try to create a new file... I write a name and take a pic from my computer and there is what I get... so I have to go back... my file is there!! but no pic.. so I done EDIT.. try to put a pic again... and...

 

Warning: move_uploaded_file(/home/www/www.passionscrapbook.com/osboutique/catalog/images/logofichierjauneboutique.jpg): failed to open stream: Permission denied in /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/classes/upload.php on line 94

 

Warning: move_uploaded_file(): Unable to move '/share/tmp/phpQfymNu' to '/home/www/www.passionscrapbook.com/osboutique/catalog/images/logofichierjauneboutique.jpg' in /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/classes/upload.php on line 94

 

Warning: Cannot modify header information - headers already sent by (output started at /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/classes/upload.php:94) in /home/www/passionscrapbook.com/osboutique/catalog/admin/includes/functions/general.php on line 18

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...