RE: Local, City, County Sales Tax and Taxes

This message was sent from: Features
http://forums.oscommerce.com/viewtopic.php?p=162632#162632
----------------------------------------------------------------

Oh wow... it worked!!!

 :D  :D  :D 

Ocularmagic, you [i]are[/i] magic.

It needed some editing of course, but it was the start.  I never would've figured this out myself.  

Ok, so I copied the entire [i]If[/i] statement 2 more times and changed them to [i]elseif[/i] instead for a running [i]if[/i] statement, and assigned each to 'Denver', 'denver', and 'DENVER' respectively to cover all case situations.  Then I input '7.3000' for each 'return', since this was the format that the tax_rate table in the database listed for our state tax_rate.  Our city tax is actually 7.3%.

Next, I took the same code and copied it into the next section for returning the tax rate description.  After modifying them each a little bit, and defining a new TEXT_CUSTOM_TAX_RATE field in the english.php file, I was able to get it to also return the description "Denver, CO combined City & State Tax of 7.3%."

We tested it with a customer account in Denver, and one in an outlying suburb of Erie.  The Denver customer returned the proper city/state tax rate, and the Erie one only returned the State tax.

Now I don't have to dump the cart!  I immediately called the business guys and they were ecstatic - we were all starting to get a little bummed out 'cause we thought we would have to start over.  

The only problem is if someone from a city called Denver outside of Colorado places an order, then they will be charged the tax as well.  But the chances of that happening are worth the risk... as one of the business guys said, "That's like preparing for lightning."

I could see modifying the code here to check for both the City name and State name, and am going to look at the code a little closer to see how that would work.  But otherwise, I'm very happy right now.

I could see easily modifying this to work for multiple city listings by adding addtional '[i]elseif[/i]' statements, and possibly for multiple Zip Code listings as well by replacing each appearance of the 'city' term below with 'postcode' instead.  This won't work for counties since there isn't an 'entry_county' column in the address_book database table for members.  But... 

If one were to enable 'suburbs' in their customers' accounts and change all of the language files where the word 'suburb' appeared to say 'county' instead, and then replaced all of the 'city' terms below with 'suburb'... one could easily modify this for use with a county or two.  This could be a hardcoded temp solution for this with carts in California.

Here is the code, and it needs to be put in place of lines 268 - 315 in the includes/functions/general.php file (both the 'return tax rate' and 'return tax description' sections).  Then you'll want to replace all instances of 'Denver' with your city, and input your custom tax rate as well.  

[code]
////
// Returns the tax rate for a zone / class
// TABLES: tax_rates, zones_to_geo_zones
  function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) {
    global $customer_zone_id, $customer_country_id, $customer_id;

	$customer_city_query = tep_db_query("select entry_city from " . TABLE_ADDRESS_BOOK . " where customers_id =  '" . $customer_id . "'"); 

	$customer_city = tep_db_fetch_array($customer_city_query); 

// Must take $customer_city['entry_city'] and make it either all caps or all 
// lower case because some customers capitalize and some don't. 
// I don't know the function off the top of my head. 

	if ($customer_city['entry_city'] == 'Denver') { 
  		return 7.3000; // Input your custom tax rate here	
	} 
	
	elseif ($customer_city['entry_city'] == 'denver') { 
  		return 7.3000; // Input your custom tax rate here 
	} 
	
	elseif ($customer_city['entry_city'] == 'DENVER') { 
  		return 7.3000; // Input your custome tax rate here 
	}
	
	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;
      }
    }

    $tax_query = tep_db_query("select SUM(tax_rate) as tax_rate from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za ON tr.tax_zone_id = za.geo_zone_id left join " . TABLE_GEO_ZONES . " tz ON tz.geo_zone_id = tr.tax_zone_id WHERE (za.zone_country_id IS NULL OR za.zone_country_id = '0' OR za.zone_country_id = '" . $country_id . "') AND (za.zone_id IS NULL OR za.zone_id = '0' OR za.zone_id = '" . $zone_id . "') AND tr.tax_class_id = '" . $class_id . "' GROUP BY tr.tax_priority");
    if (tep_db_num_rows($tax_query)) {
      $tax_multiplier = 0;
      while ($tax = tep_db_fetch_array($tax_query)) {
        $tax_multiplier += $tax['tax_rate'];
      }
      return $tax_multiplier;
    } else {
      return 0;
    }
  }

////
// Return the tax description for a zone / class
// TABLES: tax_rates;
  function tep_get_tax_description($class_id, $country_id, $zone_id) {
    global $customer_id;
	
	$customer_city_query = tep_db_query("select entry_city from " . TABLE_ADDRESS_BOOK . " where customers_id =  '" . $customer_id . "'"); 

	$customer_city = tep_db_fetch_array($customer_city_query);
	
    $tax_query = tep_db_query("select tax_description from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za ON tr.tax_zone_id = za.geo_zone_id left join " . TABLE_GEO_ZONES . " tz ON tz.geo_zone_id = tr.tax_zone_id WHERE (za.zone_country_id IS NULL OR za.zone_country_id = '0' OR za.zone_country_id = '" . $country_id . "') AND (za.zone_id IS NULL OR za.zone_id = '0' OR za.zone_id = '" . $zone_id . "') AND tr.tax_class_id = '" . $class_id . "' order by tr.tax_priority");
	
	if ($customer_city['entry_city'] == 'Denver') { 
  		return TEXT_CUSTOM_TAX_RATE; // Input your custom tax description here 
	}	
	elseif ($customer_city['entry_city'] == 'denver') { 
  		return TEXT_CUSTOM_TAX_RATE; // Input your custom tax description here 
	}	
	elseif ($customer_city['entry_city'] == 'DENVER') { 
  		return TEXT_CUSTOM_TAX_RATE; // Input your custom tax description here 
	}	
	
    elseif (tep_db_num_rows($tax_query)) {
      $tax_description = '';
      while ($tax = tep_db_fetch_array($tax_query)) {
        $tax_description .= $tax['tax_description'] . ' + ';
      }
      $tax_description = substr($tax_description, 0, -3);

      return $tax_description;
    } 
	
	else {
      return TEXT_UNKNOWN_TAX_RATE;
    }
  }
[/code]

Next, you'll want to open the includes/languages/english.php file and around line 316 add this text...

define('TEXT_CUSTOM_TAX_RATE', 'Denver, CO combined City & State Tax of 7.3%');

Of course you'll want to put your own text in place of 'Denver, CO...' for whatever your tax rate and location.

And again... if one were to replace all instances of 'city' in the code above with 'postcode' or 'suburb' (ie., '$customer_city' to '$customer_postcode', and 'entry_city' to 'entry_postcode', etc.), then they could apply this to Zip Codes and suburbs as well.  If one were to enable the suburb option in customer accounts, and then replace all instances of the word 'suburb' in the various language files with 'county', then one could conceivably do this on a county basis as well.

*Note - this is a hack to a core file, and is not accessible or editable from the Administrative Menu.  All of the changes take place in the general.php file.  When upgrading to newer versions of this file, the code will have to be added again; and there is no guarantee that future releases of OsCommerce will allow for this hack to work.

What OsCommerce really needs is to create a 4-way matrix for Tax Zones, instead of a simple 2-way matrix - Country, State, County/Parish, and City.

Anyway... Yeah!!!! It worked!!!

 :wink:

[b]Ocularmagic[/b]... send me a PM with a mailing address and I'll send you either one of my CDs or a discount coupon at our online store.