I have a solution to the California Sales Tax Conundrum

(1/3) > >>

lcwakeman:
 ;D And this might work for other states, countries as well.
First, I went to the California State Sales Tax web site and found a download of an excel spreadsheet of sales tax by city and county.  Then I searched for a list of zip codes by city for California.  No spreadsheet for this but I copied the table out of the web site and using a smart text editor (it has macro capability) created a csv file.  I imported these into an Access database, created some queries and ended up with a table that looked strangly like #__{vm}_tax_rate. One problem here, there are two Brentwoods in California and several cities that cross county lines.  You have to realize that the sales tax is a "cooperative" effort in that the state levies a sales tax, each county and city can also add to the tax.  Also, public agencies like the Bay Area Rapid Transit can add their peice to many counties tax rate.  I wrote a quick exporter that created a sql file.  I use phpMyAdmin to empty the tax rate table and reloaded it using the sql file.

I made the following changes to ps_product.php (marked with my initials - LCW):
Code:

/**
* Get the tax rate...
* @author soeren
* @return int The tax rate found
*/
function get_taxrate() {
global $page;
$ps_vendor_id = $_SESSION["ps_vendor_id"];
$auth = $_SESSION['auth'];

if( !defined('_PSHOP_ADMIN' ) || $page == 'product.product_list') {

$db = new ps_DB;

if ($auth["show_price_including_tax"] == 1) {

require_once( CLASSPATH . 'ps_checkout.php' );
if (! ps_checkout::tax_based_on_vendor_address ()) {
if( $auth["user_id"] > 0 ) {

// $q = "SELECT state, country FROM #__{vm}_user_info WHERE user_id='". $auth["user_id"] . "'"; // LCW - look up sales tax by zip
$q = "SELECT state, country, zip FROM #__{vm}_user_info WHERE user_id='". $auth["user_id"] . "'"; // LCW - look up sales tax by zip
$db->query($q);

$db->next_record();
$state = $db->f("state");
$zip = $db->f("zip"); // LCW - look up sales tax by zip
$country = $db->f("country");

$q = "SELECT tax_rate FROM #__{vm}_tax_rate WHERE tax_country='$country'\n";
// if( !empty($state)) { // LCW - look up sales tax by zip
// $q .= "AND (tax_state='$state' OR tax_state=' $state ')"; // LCW - look up sales tax by zip
if( !empty($zip)) { // LCW - look up sales tax by zip
$q .= "AND (tax_state='$zip' OR tax_state=' $zip ')"; // LCW - look up sales tax by zip
}
$db->query($q);
if ($db->next_record()) {
$_SESSION['taxrate'][$ps_vendor_id] = $db->f("tax_rate");
}
else {
$_SESSION['taxrate'][$ps_vendor_id] = 0;
}
}
else {
$_SESSION['taxrate'][$ps_vendor_id] = 0;
}

}
else {

and ps_checkout.php:
Code:

/**
* Calculate the tax charges for the current order.
* You can switch the way, taxes are calculated:
* either based on the VENDOR address,
* or based on the ship-to address.
* ! Creates the global $order_tax_details
*
* @param float $order_taxable
* @param array $d
* @return float
*/
function calc_order_tax($order_taxable, $d) {
global $order_tax_details, $discount_factor;
$auth = $_SESSION['auth'];
$ps_vendor_id = $_SESSION["ps_vendor_id"];
$db = new ps_DB;
$ship_to_info_id = vmGet( $_REQUEST, 'ship_to_info_id');


require_once(CLASSPATH.'ps_tax.php');
$ps_tax = new ps_tax;

$discount_factor = 1;

// Shipping address based TAX
if ( !ps_checkout::tax_based_on_vendor_address () ) {
// $q = "SELECT state, country FROM #__{vm}_user_info "; // LCW - look up sales tax by zip
$q = "SELECT state, country, zip FROM #__{vm}_user_info "; // LCW - look up sales tax by zip
$q .= "WHERE user_info_id='".$ship_to_info_id. "'";
$db->query($q);
$db->next_record();
$state = $db->f("state");
$country = $db->f("country");
$zip = $db->f("zip"); // LCW - look up sales tax by zip
$q = "SELECT * FROM #__{vm}_tax_rate WHERE tax_country='$country' ";
// if( !empty($state)) { // LCW - look up sales tax by zip
// $q .= "AND (tax_state='$state' OR tax_state=' $state ')"; // LCW - look up sales tax by zip
if( !empty($zip)) { // LCW - look up sales tax by zip
$q .= "AND (tax_state='$zip' OR tax_state=' $zip ')"; // LCW - look up sales tax by zip
}
$db->query($q);
if ($db->next_record()) {
$rate = $order_taxable * floatval( $db->f("tax_rate") );
if (empty($rate)) {
$order_tax = 0.0;
}
else {
$order_tax = $rate;
}
}
else {
$order_tax = 0.0;
}
$order_tax_details[$db->f('tax_rate')] = $order_tax;
}
// Store Owner Address based TAX
else {

Note that I commented out the original lines and put my changes below them.

Larry

If you want a copy of the sql script or the access (2007) database, drop me a line.

oushinhogin:
Great post Icwakeman.  I took a similar, but slightly different approach.  I decided to use ranges of zip code to determine the tax rate.  My modification also has the ability to match any number of tax rate entries and sums them up.  This is helpful if the customer's zip falls within a range that represents the state and the city.  First off, I added two columns to the tax rate table, zip_start and zip_end.  I then modified the query to grab all tax rates that contained the customer's zip between the zip_start and zip_end values.  Finally, I sum them up the returned tax rates and multiply the result with the taxable amount.  I haven't modified the tax rate administration area, and am managing the tax rate table manually on the command line.  Below is modified section of my ps_checkout.php file.  I did not perform the modification on the product class as I am not displaying tax anywhere but the checkout.  However, the modification would be nearly identical.
Code:

function calc_order_tax($order_taxable, $d) {
global $order_tax_details, $discount_factor;
$auth = $_SESSION['auth'];
$ps_vendor_id = $_SESSION["ps_vendor_id"];
$db = new ps_DB;
$ship_to_info_id = vmGet( $_REQUEST, 'ship_to_info_id');


require_once(CLASSPATH.'ps_tax.php');
$ps_tax = new ps_tax;

$discount_factor = 1;

// Shipping address based TAX
if ( !ps_checkout::tax_based_on_vendor_address () ) {
$q = "SELECT state, country, zip FROM #__{vm}_user_info "; //add zip to the the query
$q .= "WHERE user_info_id='".$ship_to_info_id. "'";
$db->query($q);
$db->next_record();
$state = $db->f("state");
$country = $db->f("country");
$zip = $db->f("zip"); //capture zip into variable
$q = "SELECT tax_rate FROM #__{vm}_tax_rate WHERE tax_country='$country' "; //we are only concerned with the tax rate
//if( !empty($state)) {
// $q .= "AND (tax_state='$state' OR tax_state=' $state ')";
if(!empty($zip)) { //check for presence of zip, rather than state
$q .= "AND zip_start <= $zip AND zip_end >= $zip"; //add entries that contain the zip to the query
}
$db->setQuery($q);  //set the query instead of executing it
/*if ($db->next_record()) {
$rate = $order_taxable * floatval( $db->f("tax_rate") );
if (empty($rate)) {
$order_tax = 0.0;
}
else {
$order_tax = $rate;
}
}
else {
$order_tax = 0.0;
}*/
$rates = $db->loadResultArray(); //execute query any load the results into an array
$order_tax = $order_taxable * array_sum($rates); //sum up all tax rates that apply and multiply by the taxable amount
$order_tax_details[$db->f('tax_rate')] = $order_tax;
}

MituAku:
Hello lcwakeman,

Awesomw work. May you please explain the Sql part more clearly. What column fields did you create?

Please help! I am in need to implement the tax based on the zip code.

Any help will be really appreciated.

Thank You!

Pabodie:
lcwakeman,

Thanks for your post!

Could you share your Access file (the one with the ZIP codes included)?

Best,
Pbd

jaylemmons:
Here is another (simple) fix for the California Sales Tax isse which looks up (in a MySQL table built from a .CSV included here) and if a matching zipcode is found the tax is adjusted (to a value in the table for each zipcode listed).  If there is no match, everything behaves normally.

Create the table and inport the .CSV attached, replace checkout.php with the attached one and you are good to go.

I will send up the next tax table when it changes mid-year. 

Also, this hack of checkout.php has taxing of shipping disabled as that is not done in California.

J

Navigation

[0] Message Index

[#] Next page