next »

[lcwakeman]

  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

[cbrimmer]

I see this as a greatly needed feature to the product and I'm looking to add it into our store. If I understand this correctly, I replace the checkout.php with this file. Then I create a mySQL database and import the csv file into it.

If this is the correct procedure, then my questions are:

- Do I need to name the database a specific name?
- What are the field names I should use?

I appreciate the help.

[jaylemmons]

cbrimmer

Here is a bit simpler way to import.

Attached you will find a zipped sql file that will generate the table (not database) needed and import the data.  Use phpMyAdmin (should find it in the hosting cPanel if your hosting company uses cPanel) to do the import. 

After that table is built and checked rename the existing checkout.php (for safety) and upload the modified one.

This way you don't need to hand build the table and know those details.

Just remember, back up first and be careful, I am not offering a warranty!

That should do California taxes properly.

J

[eldorado2768]

I appreciate your work on this issue. I contacted the Board of Equalization today and was told that this is the way it is. Isn't that just typical of some government agencies? I went ahead and sent an email through their contact form requesting a CSV file with tax by zip code. I will post an additional comment regarding what response, if any I get back from them. The other issue with their file is the presence of asterisks "*" and names of counties in parenthesis after the city name. That certainly wasn't very helpful. I got rid of that crap stuff using Excel search and replace so that part wasn't too hard to solve. However I ran into unmatched records in MS Access setting a relationship of city<>city between the BOE data and my own zip code file. That left around 33 records I had to resolve manually.

In any case, attached are the 2010 tax rates by zip code if anyone wants to use them.

[boone76]

Thanks for the sql file, Mr. JayLemmons.
I have a question though, where is the checkout.php file you referred to?
I found one file named "ps.checkout.php" in ...zipCodeHack.zip, but I was not sure which file does this replace because there is no ps.checkout in my Virtue Mart.
I checked all the files in root/ folder and I found 7 different php files with checkout_xxxxx.php, but I could not find the one which resembles "ps.checkout.php" you provided here. Can you help?

Thanks in advance

[boone76]

My bad... Please ignore my post above, I just found where the file is.
Sorry, I mistyped ps.checkout.php (should be ps_checkout.php) in my windows search.

Thanks Jay, I tried the module, it looks like it works fine, at least on a number of zip codes that I tried. I will let you know if I found something otherwise.

Thanks again... and sorry for the post above.

[boone76]

I have a minor problem here, eve though the tax $ amount is correct, for some reason the tax rate (x.xx%) in the check-out page always shows as 0%, even though your code had defined $rate = rate *100
Do you have same problem? If not, any idea why mine is doing so?

[lewistactical]

Thanks JayLemmons.  I have been looking for a solution to this tax problem for a couple of weeks now.  Your hack and table worked great.

Tim

[EmBe]

Hi - I have been successfully using the solution provided by jaylemmons...  and it's absolutely wonderful...  In July 2011 the CA Tax Rate is changing... 

Anyone else using this solution and know how to update to the new tax rate?

Thank you thank you!!!