Zip based taxes Myql Query help?

[bastille]

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?

[bastille]

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??

[bastille]

......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??

[Jan Zonjee]

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;
}

[bastille]

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;
}

<{POST_SNAPBACK}>

 

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...

[Jan Zonjee]

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.

[bastille]

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 November 23, 2004 by bastille

[Jan Zonjee]

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.

[mvigil514]

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....

<{POST_SNAPBACK}>

 

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.

[bastille]

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.

<{POST_SNAPBACK}>

 

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 :) )