Post by creative1 » Tue Jul 21, 2009 12:44 am

Hello All,

I've been reading on some of the threads in this forum regarding this issue, and I believe I'm getting close to a "hack" to get this to work. Since there is no way to narrow a zone down to zip code, the only way to set up this functionality is to bypass the built-in taxing function entirely.

The following guide is assuming you are willing to bypass the zone functionality, and every item in your store will be taxed the same way.

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

To get the system ready, set it up to not add tax to the displayed prices of products, and set up a dummy tax item which you'll apply to every product (could be any zone and rate). We won't actually be using this to apply tax, it's just there so the system will know we need to tax these items.

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

The first step is to make zip code a required field in the customer account setup:

in catalog/controller/account/create.php
around line 96, add the following to display the error message if field fails check:

Code: Select all

$this->data['error_postcode'] = @$this->error['postcode'];
around line 192, add to check the contents of the field:

Code: Select all

if ((strlen(utf8_decode($this->request->post['postcode'])) < 5) || (strlen(utf8_decode($this->request->post['postcode'])) > 5)) {
      		$this->error['postcode'] = $this->language->get('error_postcode');
    	}
in catalog/language/english/account/create.php
Under the error section, add the following to display a message:

Code: Select all

$_['error_postcode']       = 'Post code must be 5 digits!';
----------------------

So, that takes care of the zip code field. We know we'll get that when they sign up. Now we need a database. You'll have to find one that works for your state. For my case, I needed NY, and found a great one (ironically) under the addon site for oscommerce:

http://addons.oscommerce.com/info/2792

create a separate database with your zip, rate, (and county if you want) fields.

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

Now we're checking for the zip code, we have tax rates for the zip code, now we just need to apply them at checkout. The way I see it, there's no need to reinvent the tax system. It's too easy in PHP to lookup rates on our own database, and add them to the total. Only question is the best place to do that. I think I've found that. It's the file that's called on the confirmation screen that totals up the tax rates and updates the total:

catalog/model/total/tax.php

Code: Select all

<?php
class ModelTotalTax extends Model {
	public function getTotal(&$total_data, &$total, &$taxes) {
		if ($this->config->get('tax_status')) { 		
			foreach ($taxes as $key => $value) {
				if ($value > 0) {
	    	   		$total_data[] = array(
	    				'title'      => $this->tax->getDescription($key) . ':', 
	    				'text'       => $this->currency->format($value),
	    				'value'      => $value,
						'sort_order' => $this->config->get('tax_sort_order')
	    			);
			
					$total += $value;
				}
			}
		}
	}
}
?>
In this file, if I modify $value at the end, I can add whatever I want to the total. I can also change the description of the tax amount (for example to include a county from my own database), and I can change the tax amount listed. I have a script that will simply lookup a zip code and pull the new rate:

Code: Select all

$xdbhost = 'localhost';
$xdbuser = 'USER';
$xdbpass = 'PASS';
$xconn = mysql_connect($xdbhost, $xdbuser, $xdbpass) or die
('Error connecting to mysql');

$xdbname = 'DATABASE';
mysql_select_db($xdbname);

$zipcode="11530";

$xresult=mysql_query("SELECT * FROM ziptax WHERE zip_code=$zipcode");
$xrow=mysql_fetch_array($xresult);
$xtaxrate=$xrow['zip_tax_rate'];

mysql_close($conn);
Now, I've got two problems before this is solved:

1. I need to get the zip code from the current order/customer in order to pull the right rate. Right now I'm just hard coding it (11530) How can I do that?

2. I need to get the total quantity of items in the cart, so I can display the correct tax on the order, and add the proper amount to the total price.

Once I have these two things figured out, I believe some simple modifications to this script will allow for taxing by zip code. Similar edits could be done to the order history and/or invoice pages.

Any thoughts / ideas?

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by Qphoria » Tue Jul 21, 2009 1:56 am

I think it'd be less work just added zip-code granularity

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by creative1 » Tue Jul 21, 2009 2:11 am

Qphoria wrote:I think it'd be less work just added zip-code granularity
Could you explain? I'm open to anything...

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by creative1 » Tue Jul 21, 2009 5:16 am

OK, figured out the answer to one of my questions, but not quite the way I thought. Forget editing the file that prints the totals to the cart, a better idea is to edit the file that calculates the tax rates in the first place. This way, I don't need to worry about the quantity of products in the cart, since the proper files will calculate that using my new tax rates. The file to edit is here:

system/library/tax.php

Look for:

Code: Select all

public function getRate($tax_class_id) {
    	return (isset($this->taxes[$tax_class_id]) ? $this->taxes[$tax_class_id]['rate'] : NULL);
  	}
  
  	public function getDescription($tax_class_id) {
  	return (isset($this->taxes[$tax_class_id]) ? $this->taxes[$tax_class_id]['description'] : NULL);
  	}
and replace with:

Code: Select all

public function getRate($tax_class_id) {
  	
  		// TAX OVERRIDE
		
		$zipcode="11530";
		
		$xresult=mysql_query("SELECT * FROM ziptax WHERE zip_code=$zipcode");
		$xrow=mysql_fetch_array($xresult);
		$xtaxrate=$xrow['zip_tax_rate'];
		
		// TAX OVERRIDE
  	
    	//return (isset($this->taxes[$tax_class_id]) ? $this->taxes[$tax_class_id]['rate'] : NULL);
    	return $xtaxrate;
  	}
  
  	public function getDescription($tax_class_id) {
  	
  		// TAX OVERRIDE
		
		$zipcode="11530";
		
		$xresult=mysql_query("SELECT * FROM ziptax WHERE zip_code=$zipcode");
		$xrow=mysql_fetch_array($xresult);
		$xcounty=$xrow['zipcounty'];
		
		// TAX OVERRIDE
  	
		return (isset($this->taxes[$tax_class_id]) ? $this->taxes[$tax_class_id]['description']." (".$xcounty.")" : NULL);
  	}
This is assuming that you have imported your zip code table within the existing open cart database.

The first function sets the rate, so I'm bypassing all the built in math and lookups, and performing my own lookup in my own database, based on the customer zip code (I'm currently hard coding that zip code in). Instead of the previous return, I'm returning my new tax rate.

The second function isn't necessary, but it's a nice touch. Since my database has the county names, I'm adding the matching county to the tax description.

For some reason (most likely linked to my average level understanding of PHP), I can't call any variables set outside of those functions within them, hence the duplicate database lookups within the functions. Perhaps someone can solve that issue as well to streamline the coding.

The only critical aspect left is to figure out how to get the customer zip code, so I can pass that through my query instead of a hard coded example. If I figure that out (and I'm determined), I'll update this thread.

Edit: I'd love to edit the original post with this information, but for some reason I can't edit it (no button).
Last edited by creative1 on Tue Jul 21, 2009 5:29 am, edited 1 time in total.

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by Qphoria » Tue Jul 21, 2009 5:19 am

Why not use the existing db connection?

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by creative1 » Tue Jul 21, 2009 5:27 am

Qphoria wrote:Why not use the existing db connection?
For testing, I had my zips in a separate database. I imported the table into the existing open cart database and that cleaned things up quite a bit. I've edited the above post. Thanks!

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by Qphoria » Tue Jul 21, 2009 5:31 am

creative1 wrote:
Qphoria wrote:Why not use the existing db connection?
For testing, I had my zips in a separate database. I imported the table into the existing open cart database and that cleaned things up quite a bit. I've edited the above post. Thanks!
for getting the actual customer zip, try this in place of the hard coded zip:

Code: Select all

$shipping_address = $this->customer->getAddress(@$this->session->data['payment_address_id']);
$zip_code = @$shipping_address['postcode'];

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by creative1 » Tue Jul 21, 2009 8:51 pm

Qphoria wrote:
creative1 wrote:
Qphoria wrote:Why not use the existing db connection?
For testing, I had my zips in a separate database. I imported the table into the existing open cart database and that cleaned things up quite a bit. I've edited the above post. Thanks!
for getting the actual customer zip, try this in place of the hard coded zip:

Code: Select all

$shipping_address = $this->customer->getAddress(@$this->session->data['payment_address_id']);
$zip_code = @$shipping_address['postcode'];
It looks like the "$this->customer->getAddress" part is killing PHP when I add that code. I'm trying some variations. If I just try the following:

Code: Select all

$shipping_address = $this->session->data['payment_address_id'];
then "$shipping_address returns "11". Not sure if this helps.

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by Qphoria » Tue Jul 21, 2009 9:18 pm

well that is the id which gets passed to the getAddress() function.
You'd need to put a reference to the customer class in the file that the function is called from, but i forget which file you added your code to.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by creative1 » Tue Jul 21, 2009 9:21 pm

Qphoria wrote:well that is the which gets passed to the getAddress() function.
You'd need to put a reference to the customer class in the file that the function is called from, but i forget which file you added your code to.
I'm in system/library/tax.php

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by creative1 » Tue Jul 21, 2009 9:47 pm

Figured it out. Not sure if it's the most efficient way, but it works. Higher up in that system/library/tax.php document, we're doing a database query to find out the customer's country and zone information from the "address" table:

Code: Select all

$tax_class_query = $this->db->query("SELECT country_id, zone_id FROM " . DB_PREFIX . "address WHERE address_id = '" . (int)@$this->session->data['shipping_address_id'] . "' AND customer_id = '" . (int)@$this->session->data['customer_id'] . "'");
I checked out that table, and it also stores the postal code. I can just repeat the query, this time geting the postcode field. So, here's the new substitution for the system/library/tax.php document, that pulls the customer's zip code and substitutes the tax rate for the custom rate defined in the separate local tax table:

Code: Select all

public function getRate($tax_class_id) {
  	
  		// TAX OVERRIDE
		
		$pullcustomerzip = $this->db->query("SELECT postcode FROM " . DB_PREFIX . "address WHERE address_id = '" . (int)@$this->session->data['shipping_address_id'] . "' AND customer_id = '" . (int)@$this->session->data['customer_id'] . "'");
		$zipcode=$pullcustomerzip->row['postcode'];
		
		$getnewtaxrate=$this->db->query("SELECT * FROM ziptax WHERE zip_code=$zipcode");
		$newtaxrate=$getnewtaxrate->row['zip_tax_rate'];
		
		// TAX OVERRIDE
  	
    	//return (isset($this->taxes[$tax_class_id]) ? $this->taxes[$tax_class_id]['rate'] : NULL);
    	return $newtaxrate;
  	}
  
  	public function getDescription($tax_class_id) {
  	
  		// TAX OVERRIDE
		
		$pullcustomerzip = $this->db->query("SELECT postcode FROM " . DB_PREFIX . "address WHERE address_id = '" . (int)@$this->session->data['shipping_address_id'] . "' AND customer_id = '" . (int)@$this->session->data['customer_id'] . "'");
		$zipcode=$pullcustomerzip->row['postcode'];
		
		$getcustomercounty=$this->db->query("SELECT * FROM ziptax WHERE zip_code=$zipcode");
		$customercounty=$getcustomercounty->row['zipcounty'];
		
		// TAX OVERRIDE
  	
		return (isset($this->taxes[$tax_class_id]) ? $this->taxes[$tax_class_id]['description']." (".$customercounty.")" : NULL);
  	}
I'd like to share this with the community (assuming you can't think of a way to streamline my changes any more). What is the best place to do that?

Thanks for your help!

edit: changes query code to match exiting methods to streamline code.
Last edited by creative1 on Tue Jul 21, 2009 9:52 pm, edited 1 time in total.

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by Qphoria » Tue Jul 21, 2009 9:48 pm

zip all the files together, preserving their directory depth and put it up on the contrib page.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by creative1 » Tue Jul 21, 2009 11:16 pm

Qphoria wrote:zip all the files together, preserving their directory depth and put it up on the contrib page.
This is all set up. I posted the files I modified, as well as instructions for making the modification manually:

http://www.opencart.com/index.php?route ... egory_id=8

Under the "Other" Category.

Newbie

Posts

Joined
Tue Jul 21, 2009 12:07 am

Post by vang » Sun Sep 05, 2010 2:49 am

The link to this mod is broken, and doesn't seem to exist in the mod section anymore.

Is this code still valid?
I personally see great value in this code, especially for Florida merchants.
We have to charge sales tax based on the customers - ship to - address (postcode).
Since OpenCart doesn't base sales taxes on [postcode] it makes it a bit difficult to adhere to State Tax Laws.

I tried to follow this thread and make my own modifications, but I seem to be missing something.
Can "Creative1" or "Q" elaborate on this thread to help those of us unfortunate ones who have to use difficult sales tax billings due to crazy State tax laws?

Any help is greatly appreciated. ;)

http://www.aboveclothing.com


New member

Posts

Joined
Mon May 11, 2009 8:22 am


User avatar
Active Member

Posts

Joined
Mon Jul 20, 2009 8:25 am

Post by ebrooks » Fri Jan 14, 2011 7:19 am

You may be able to get away without it if you are an individual or a small company but for a company our size with auditors it's an absolute necessity.

Newbie

Posts

Joined
Thu Mar 25, 2010 7:31 am

Post by fido-x » Fri Jan 14, 2011 3:24 pm

I'm not sure if this will work the way you want it to, since the IP address is not related to the zip-code in any way, shape or form, except as an exit point for the routed network you a on.

Example: I live just outside a little town called Longley in the south of Tasmania (postcode 7150). According to IP locator websites, I'm either in Devonport (over 400km away) with a postcode of 7310, or in Newstead (a suburb of Launceston - over 120km away) which has a postcode of 7250. All of these are in different municipalities (ie., counties). So, if each municipality (county) had their own (different) tax rate, which would I pay? Obviously, the correct tax won't be applied until I enter my address.

And you should also remember, that the tax payable is based on the payment (residential) address of the customer making the purchase, and not the shipping address.

Image
Modules for OpenCart 2.3.0.2
Homepage Module [Free - since OpenCart 0.7.7]
Multistore Extensions
Store Manager Multi-Vendor/Multi-Store management tool

If you're not living on the edge ... you're taking up too much space!


User avatar
Expert Member

Posts

Joined
Sat Jun 28, 2008 1:09 am
Location - Tasmania, Australia

Post by finedesignz » Thu Feb 24, 2011 3:09 am

Hi,

We still need the solution to this issue. Does anyone have access to the code or module that will resolve this?

If so, please make it available to us.

New member

Posts

Joined
Tue Feb 08, 2011 3:57 am

Post by SXGuy » Thu Feb 24, 2011 5:10 am

seems to me it would be alot simpler to clone the way zones work, but instead have a database filled with zip codes and when the customer submits his billing details, the system checks the first part of the zip code to dertermine the rate to set.

I dont know how your zip code system works, but in the UK, our postcode system can determine where someone lives based on the first letter or/and next two numbers, the last bit is for the street, which for tax rate terms isnt gonna be needed.

therefore what i would do is create a new table called zips, and have a geozone id field and a zip code field.

Populate the table with the first few digits of a zip code, use the geozone id field to match them together.

then, edit the tax function, to compare the new table with the first few digits of the zip code submitted, and return the right geozone id.

That geozone id could then be used to set the correct tax rate providing you specify a tax rate for that geozone.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by JAY6390 » Thu Feb 24, 2011 5:45 am

I did recently create this as a fix for a company. It's not free but if you want to get a price for it then feel free to PM me. This is for 1.4.9.3

Image


User avatar
Guru Member

Posts

Joined
Wed May 26, 2010 11:47 pm
Location - United Kingdom
Who is online

Users browsing this forum: No registered users and 29 guests