Jump to content
Sign in to follow this  
bastille

Zip based taxes Myql Query help?

Recommended Posts

Hi all

I am trying to alter a modifcation so that it returns a tax rate based on zip code.

(new york state has different rates based on counties)

I have created a table, ziptax, and in it have a column with the zip and the corresponding tax rate.

 

I want to modify this contribution that returns a hard -coded rate to return a rate based on matching the customer's zip with the table above.. thus, I modified the code

 

$customer_zip_query = tep_db_query("select entry_postcode, entry_zone_id from " . TABLE_ADDRESS_BOOK . " where customers_id =  '" . $customer_id . "'");

 

$address_query = tep_db_fetch_array($customer_zip_query);

$cust_zip_code = $address_query['entry_postcode'];

if ($cust_zip_code == '35542' || $cust_zip_code == '36551' || $cust_zip_code == '36555' || $cust_zip_code == '36559' || $cust_zip_code == '36561' || $cust_zip_code == '36562' || $cust_zip_code == '36564' || $cust_zip_code == '36567' || $cust_zip_code == '36577' || $cust_zip_code == '36580' || $cust_zip_code == '36526' || $cust_zip_code == '36532' || $cust_zip_code == '36533' || $cust_zip_code == '36535' || $cust_zip_code == '36536' || $cust_zip_code == '36547' || $cust_zip_code == '36549') {

  $zip_in_county = true;

        } else {

  $zip_in_county = false;

  }

 

if (($zip_in_county == 'true') && ($address_query['entry_zone_id'] == '1')) {

    return 6.0000;

}

elseif (($zip_in_county == 'false') && ($address_query['entry_zone_id'] == '1')) {

    return 4.0000;

}

 

to this::

 

 

$taxquery = tep_db_query("select zip_tax_rate from  " . TABLE_ZIPTAX . " where zip_code =  '" . $address_query['entry_postcode'] . "'");

$tax_query = tep_db_fetch_array($taxquery);

$taxrate=$tax_query['zip_tax_rate'];

if (($zip_in_county == 'true') && ($address_query['entry_zone_id'] == '43')) {

 

    //return 10.0000;

  return $taxrate;

 

}

 

but its coming up empty :( Any ideas?

Share this post


Link to post
Share on other sites

Ok i got this to work

 

But its getting the zip informatoin from the customer's default address rather than the shipping address (which is what i need to calcualte tax) does anyone have any idea which field i would use in a query for that??

Share this post


Link to post
Share on other sites

......so when i change the user's default address to NOT be ny, i get this message:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

/home/www/***********/catalog/includes/functions/database.php on line 10

 

 

on this page:

/catalog/checkout_confirmation.php?

 

 

....any ideas??

Share this post


Link to post
Share on other sites

IMHO you could things more simple. Now you get errors if the zip code is not in the table: $taxquery is empty (you could code for that too of course) so $tax_query = tep_db_fetch_array($taxquery) is not valid.

 

Can't you extend the code of the original contribution not to something like the following?

 

$address_query = tep_db_fetch_array($customer_zip_query);

$ny_county_1 = array('35542', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $address_query['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($address_query['entry_zone_id'] == '43')) { 
? ? return 10.0000; 
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($address_query['entry_zone_id'] == '43')) { 
? ? return 10.5000; 
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($address_query['entry_zone_id'] == '43')) {
? ? return 8.5200;
} else {
? ? return 0;
}

Share this post


Link to post
Share on other sites
IMHO you could things more simple. Now you get errors if the zip code is not in the table: $taxquery is empty (you could code for that too of course) so $tax_query = tep_db_fetch_array($taxquery) is not valid.

 

Can't you extend the code of the original contribution not to something like the following?

 

$address_query = tep_db_fetch_array($customer_zip_query);

$ny_county_1 = array('35542', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $address_query['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($address_query['entry_zone_id'] == '43')) { 
   return 10.0000; 
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($address_query['entry_zone_id'] == '43')) { 
   return 10.5000; 
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($address_query['entry_zone_id'] == '43')) {
   return 8.5200;
} else {
   return 0;
}

 

hi, thanks for responding. yeah i do get that error if the zip isn't there, or if the state is not ny.

 

my thinking runs along these lines :

its easier to manage the zips and tax rates in a mysql table as opposed to hard coded since new york is forvere changing and raising taxes (several counties are set to increase in less than a month's time.

 

it looks like your solution would definately work however - but i think the original contrib that i am modifiying goes off the customer's default zip (post) code as opposed to the shipping - which is what should be used to calc. taxes. but i can't seem to pin down what field that is...

Share this post


Link to post
Share on other sites
its easier to manage the zips and tax rates in a mysql table as opposed to hard coded since new york is forvere changing and raising taxes (several counties are set to increase in less than a month's time.
Exactly why you don't want a mysql table where you have to edit each and every taxrate for each and every zipcode... In the "hard coded variant" you could even hard code dates and then if the date changes to the date on which the new tax rate is introduced you can relax because 1 minute after 12 the new tax rate is applied.
but i can't seem to pin down what field that is...
In checkout_confirmation.php the class $order is called, and from the code it looks $order->delivery['delivery_postcode'] might be the one you are looking for. Give it a try I would say.

Share this post


Link to post
Share on other sites

I tried switching it to that query method an still getting that error :'(

 

 

$tax_address_query = tep_db_query("select ab.entry_country_id, ab.entry_zone_id, ab.entry_postcode from " . TABLE_ADDRESS_BOOK . " ab left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) where ab.customers_id = '" . (int)$customer_id . "' and ab.address_book_id = '" . (int)($this->content_type == 'virtual' ? $billto :$sendto) . "'");

$tax_address = tep_db_fetch_array($tax_address_query);

$ny_county_1 = array('10011', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $tax_address['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($tax_address['entry_zone_id'] == '43')) {
   return 10.0000;
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($tax_address['entry_zone_id'] == '43')) {
   return 10.5000;
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($tax_address['entry_zone_id'] == '43')) {
   return 8.5200;
}

elseif (($zip_in_county == 'false') && ($address_query['entry_zone_id'] == '43')) {

 return 0;

}

elseif ( ($country_id == -1) && ($zone_id == -1) ) {

     if (!tep_session_is_registered('customer_id')) {

       $country_id = STORE_COUNTRY;

       $zone_id = STORE_ZONE;

     } else {

       $country_id = $customer_country_id;

       $zone_id = $customer_zone_id;

     }

   }

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

 

I assume this query isn't working because I put the zip code of the test user (10011) in it.

 

I am baffled. I believe i have the tax address query correct- i copied out of orders....

Edited by bastille

Share this post


Link to post
Share on other sites

Try inserting between $tax_address_query and $tax_address = tep_db_fetch_array($tax_address_query);

echo $tax_address_query;
exit;

That will show the actual query and makes debugging usually a lot easier.

Share this post


Link to post
Share on other sites
I tried switching it to that query method an still getting that error  :'(

$tax_address_query = tep_db_query("select ab.entry_country_id, ab.entry_zone_id, ab.entry_postcode from " . TABLE_ADDRESS_BOOK . " ab left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) where ab.customers_id = '" . (int)$customer_id . "' and ab.address_book_id = '" . (int)($this->content_type == 'virtual' ? $billto :$sendto) . "'");

$tax_address = tep_db_fetch_array($tax_address_query);

$ny_county_1 = array('10011', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $tax_address['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($tax_address['entry_zone_id'] == '43')) {
? ?return 10.0000;
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($tax_address['entry_zone_id'] == '43')) {
? ?return 10.5000;
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($tax_address['entry_zone_id'] == '43')) {
? ?return 8.5200;
}

elseif (($zip_in_county == 'false') && ($address_query['entry_zone_id'] == '43')) {

?return 0;

}

elseif ( ($country_id == -1) && ($zone_id == -1) ) {

? ? ?if (!tep_session_is_registered('customer_id')) {

? ? ? ?$country_id = STORE_COUNTRY;

? ? ? ?$zone_id = STORE_ZONE;

? ? ?} else {

? ? ? ?$country_id = $customer_country_id;

? ? ? ?$zone_id = $customer_zone_id;

? ? ?}

? ?}

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

 

I assume this query isn't working because I put the zip code of the test user (10011) in it.

 

I am baffled. I believe i have the tax address query correct- i copied out of orders....

 

What coding did you decide to go with this. I have no programming experience and want to use the zip code method also. I would appreciate being able to use the code you develop on my site. Thank You.

Share this post


Link to post
Share on other sites
What coding did you decide to go with this.  I have no programming experience and want to use the zip code method also.  I would appreciate being able to use the code you develop on my site.  Thank You.

 

Hello

with the help of some very kind OScommerce posters I have been putting something together as still have a couple of problems as soon as they are fixed I will post here or as a contribution.

 

For now, I just have the counties as states (awkward but it works :) )

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×