Author Topic: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)  (Read 62962 times)

crackdaddy

  • Full Member
  • ***
  • Posts: 263
this mod is for creating a dual tax mod, most places in the us require you to collect city/county/state taxes
here in alabama we must collect state tax for all alabama, and also collect tax on anythign in the county(this is doen by zip code lookup) here is a link to get all your zip codes n such
http://www.getzips.com/county.htm

i have to get my site up and live asap, so i didnt create lookup tables or any thing fancy in the databases, nor a back end to admin this-
someone else can do it, since this is fine for me for the moment, i will do it late...much later, if no one else does it by then...


ok here is how you use it:
im my case AL state tax is 4%
county tax is 5%
so if you live in the county, you get 9% and some where else in the state(and not in county by zip code lookup)
you get charged 4%
out of state= no tax


1.set the state tax rate to =4% in the backend of standard tax module
set the value of county plus state(ie 9%  to = .09) in my code
 find the line below and replace this value with yours and you are done - oh also change your zips duoh
//set full tax rate
else {
$order_tax = $order_taxable * floatval( '.09');
}


anyway it works just fine.. here is the code:
in page: ps_checkout.php
around line 1222 on my page
Code: [Select]
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;

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

$discount_factor = 1;

// Shipping address based TAX
//<-------------------------mod crackdaddy #342u328947u-------------------------------->

if (TAX_MODE == '0') {
$zips[] = '36618';
$zips[] = '36615';
$zips[] = '36612';
$zips[] = '36616';
$zips[] = '36617';
$zips[] = '36622';
$zips[] = '36621';
$zips[] = '36625';
$zips[] = '36613';
$zips[] = '36619';
$zips[] = '36628';
$zips[] = '36689';
$zips[] = '36660';
$zips[] = '36652';
$zips[] = '36690';
$zips[] = '36695';
$zips[] = '36693';
$zips[] = '36691';
$zips[] = '36630';
$zips[] = '36688';
$zips[] = '36663';
$zips[] = '36633';
$zips[] = '36644';
$zips[] = '36641';
$zips[] = '36640';
$zips[] = '36685';
$zips[] = '36512';
$zips[] = '36571';
$zips[] = '36528';
$zips[] = '36575';
$zips[] = '36582';
$zips[] = '36525';
$zips[] = '36572';
$zips[] = '36675';
$zips[] = '36544';
$zips[] = '36568';
$zips[] = '36505';
$zips[] = '36560';
$zips[] = '36670';
$zips[] = '36671';
$zips[] = '36509';
$zips[] = '36541';
$zips[] = '36608';
$zips[] = '36609';
$zips[] = '36606';
$zips[] = '36607';
$zips[] = '36611';
$zips[] = '36522';
$zips[] = '36521';
$zips[] = '36610';
$zips[] = '36601';
$zips[] = '36602';
$zips[] = '36587';
$zips[] = '36590';
$zips[] = '36523';
$zips[] = '36604';
$zips[] = '36605';
$zips[] = '36603';

$q = "SELECT state, zip, country FROM #__{vm}_user_info ";
$q .= "WHERE user_info_id='".@$_REQUEST["ship_to_info_id"] . "'";
$db->query($q);
$db->next_record();
$zip = $db->f("zip");
$state = $db->f("state");
$country = $db->f("country");
            if ( !($state=='AL' && in_array($zip, $zips)) ) {

$q = "SELECT * FROM #__{vm}_tax_rate WHERE tax_country='$country' ";
if( $state ) {
$q .= "AND tax_state='$state'";

}


$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;
}
            }
else {
//set your tax rate here
$order_tax = $order_taxable * floatval( '.09');
}

$order_tax_details[$db->f('tax_rate')] = $order_tax;
}

//<---------------------od crackdaddy #342u328947u------------------------>

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes
« Reply #1 on: November 06, 2006, 06:36:15 am »
Crackdaddy,

In your code you use an && statement. 
Quote
if ( !($state=='AL' && in_array($zip, $zips)) ) {
If I'm correct this has the effect of saying:
Quote
If the user's state is not Alabama AND the user's zip is not in these arrays, then do the following...

Seems like it wouldn't necessarily need to be an AND statement but would be better used as an OR statement.  Am I missing something?

Thanks for your code!

(BTW: Roll Tide! - Born and raised in Hueytown, AL - Now living in Wisconsin, brrrrrrr!)

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes
« Reply #2 on: November 06, 2006, 09:51:14 am »
Ok,
I have been successful in creating a field in the tax table that can hold zip codes.  I have modified VM to allow multiple state entries but not multiple zip code entries per state.  This is working and shows up in my admin interface.

Now I'm trying to modify the ps_checkout.php file so that it will get the tax_rate for the corresponding zip code.  This is as far as I have gotten.  I have tried it several different ways but I'm just not getting anywhere.  I am not a PHP programmer (background in LISP, C, C++, VB, etc. but all of that was 8+ years ago!).

The following code shows that I'm attempting to get the zip code tax.  If there is no specific zip code then it looks for an entry "ALL" for the default tax rate.

If I could look inside the variable at run time I might be better able to figure this out.  Anyone able to point me in the right direction?  I will certainly post all my work back to this forum if we can get it working.  I think this would be a lot easier for my clients to work with than the existing coding Crackdaddy has done.

ONCE AGAIN, thanks for any help you can give!
Quote
      // Shipping address based TAX
      if (TAX_MODE == '0') {
         $q = "SELECT state, zip, country FROM #__{vm}_user_info ";
         $q .= "WHERE user_info_id='".@$_REQUEST["ship_to_info_id"] . "'";
         $db->query($q);
         $db->next_record();
         $zip = $db->f("zip");
         $state = $db->f("state");
         $country = $db->f("country");
         $q = "SELECT * FROM #__{vm}_tax_rate WHERE tax_country='$country' ";
         if( $state ) {
            $q .= "AND tax_state='$state'";
         }
         $db->query($q);
         /* Set $zip_rate to null then check to see if it is changed. */
         $zip_rate = NULL;
         foreach ($db->next_record()) {
            if ($db->f("tax_zip")==$zip){
               $zip_rate=$db->f("tax_rate");
            }
         }
         /* if $zip_rate has not changed then check to see if there is a default rate for the state. */
         if (empty($zip_rate)) {
            $q = "SELECT * FROM #__{vm}_tax_rate WHERE tax_country='$country' AND tax_zip=ALL";
            $db->query($q);
            if ($db->next_record()) {
               $zip_rate=$db->f("tax_rate");
            }
         }
         /* at this point, if $zip_rate has been set, then calculate the tax. */
         if (!empty($zip_rate)) {
               $rate = $order_taxable * floatval($zip_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;
      }

Feel free to clean my code up or change it all together!  Thanks!

crackdaddy

  • Full Member
  • ***
  • Posts: 263
Re: [How to] Dual Tax Mod, City county State Taxes
« Reply #3 on: November 06, 2006, 16:43:26 pm »
Crackdaddy,

In your code you use an && statement. 
Quote
if ( !($state=='AL' && in_array($zip, $zips)) ) {
If I'm correct this has the effect of saying:
Quote
If the user's state is not Alabama AND the user's zip is not in these arrays, then do the following...

Seems like it wouldn't necessarily need to be an AND statement but would be better used as an OR statement.  Am I missing something?

Thanks for your code!

(BTW: Roll Tide! - Born and raised in Hueytown, AL - Now living in Wisconsin, brrrrrrr!)

First off,...WAR EAGLE!! HEY
now with that out of the way..
my code is 100% correct, you are not understanding how it functions is the issue
Quote

this 100% correct
if ( !($state=='AL' && in_array($zip, $zips)) )

father down you with find an "else statement "
which is the equavalent to saying
if ( $state=='AL' && in_array($zip, $zips) )
the reason we check the state as well as the zip, this gives us extra protectiona gainst foreign addresses and such that might somehow have the same zip...or they accidently enter the wrong zip... it basically is a failsafe check


1>like i mentioned..i hard coded this for the fact i was done with it in like 2 seconds.
...i have to go live asap..
so i hvae no time right now to build it with db tables and back end crap
...one day i will come back to it....
2>ok let me reexplain how and why my mod works
in mobile, AL we have to collect 2 tax rates amounts
a>county tax for people in the county of mobile(5%)the county is defined by the zip code lookup
b>state tax for the people in the state of Al(4%)the state is defined by the state code lookup(AL)
............so if the order is placed by someone who has the state code AL but not also a zipcode in my list then they are charged 4% tax
.......if the have both the state code of Al and one of the zip codes in my list they are charged the full 9%(county and state taxes)

ok heres how it works...
you asked about this code
Quote
     if ( !($state=='AL' && in_array($zip, $zips)) ) {
this statement check to see if the order needs both taxes(county and state for my example)

>>>>>the statement reads - if not equal to both the state and county then...
( it will apply the taxes as normal... you should set the state level taxes as normal in the back end
since it will work as normal(and in the back end i have select the taxable state as AL and the amount to 4%)
it will assign 1 level of taxes as normal at the state level(all people in Al will get 4% tax and those not in Al get no tax)

>>>>>>
here is where you set the total amount if the state above was true
(both the state is AL and the zip is one from the list)
all you do is change the ( .09 =9%) to whatever you dual tax rate adds up to
Quote
else {   
         //set your tax rate here
            $order_tax = $order_taxable * floatval( '.09');
         }



----------------------------
this is rather simple and rather effective way to get it working!!

if you wanted to create db looktables and a backend, thats cool, you could just insert your variables for the queries into my code easliy, or redo the whole thing.....i honestestly havent even read your code yet...i have no time right now...i just wanted to explain how mine works since you didnt seem to understand
-i hope this clears it up how it functions, let me know if oyu need to know more or this is unclear
-this mod works 100% fine as is

-crackdaddy

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #4 on: November 06, 2006, 17:37:45 pm »
Thanks for the response.  No, I did understand how it works.  I just didn't see the need to redundantly check the state.  That's just a difference of opinion on the failsafe needs. 

Actually, the way you have it setup does not report an error if the customer put in a state and zip that did not match.  Instead it just charges 0.0 tax.  Surely if this is a failsafe measure you would want it to report an error.

I already have the back end functions completed and will post them if we can get the calculation functions to work.  In this case, I'm seeking to allow the client to input a specific rate for individual zip codes.  In Wisconsin we have the same county tax issues you bring up but we also have some counties that charge a stadium tax.  All in all, there are 4 different combinations of state and county tax that we have to accommodate.  Your solution is just not flexible enough.  If we allow a different rate for each zip, then we can meet every possible zip code tax issue.

(BTW: I took one semester of college at Auburn then transferred to UAB.  Looks like Auburn is going to have a good season this year - enjoy it while it lasts! :) )

crackdaddy

  • Full Member
  • ***
  • Posts: 263
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #5 on: November 06, 2006, 18:21:35 pm »
Actually, the way you have it setup does not report an error if the customer put in a state and zip that did not match.  Instead it just charges 0.0 tax.  Surely if this is a failsafe measure you would want it to report an error.
the 0.0 tax is part of the origninal code and is nothing to do with an error, it means they get charged 0 dollars in tax, when they are not matching any taxable areas, this is correct!! as you dont want to charge tax for those who shouldnt be taxed.
-not sure why you mention an error check? kinda lost with that part, there no way to check nor need to for an "error"

--------
yeah sounds good.. what are working on... i just needed 2 (dual taxes)... the taxes should be done from the db side and be done as zones, and zones should be able to be defined by and combination of things you want..

as a side note, im not sure if there something yet, it may be good to create a tax free check for schools/churches etc...
this would have to be done at the user level to indicate tax free user, prob with admin approval/verify easy enough to add to user account... but then integrate into the tax check....
-just a thought if your gonna totally mod out the tax stuff

-crackdaddy

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #6 on: November 06, 2006, 18:59:59 pm »
Thanks. 

On the Error Reporting, I'm thinking that if a customer puts in a state that doesn't match a zip code, they would have made a mistake and might get charged no tax when in reality, they should be charged.  I would think you would want to trap that and have them correct it before you proceed.  I understand the 0.0 is coming from the existing code, but I don't think it would be appropriate in this scenario.  (just my 2cents worth).

Tax-exempt is another issue that I'm trying to think through.  We do need a real solution for this and my current client will need it sooner rather than later (90% of their business is with tax-exempt entities).  Thanks for the thoughts.  I'm hoping to find that someone else has already made some headway.  I'm already getting tired of being back in the programming trenches.  I think I'm going to have to bite the bullet and really get a handle on php.

Thanks again!

crackdaddy

  • Full Member
  • ***
  • Posts: 263
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #7 on: November 06, 2006, 20:26:35 pm »
the best way to check for erros as you describe would be avs check .. i didnt do it in my case cause i would have to hard code all possible zip fo rhte state and thats not worth the effort for what i need.

-the tax exempt should be the easiest thing to create... you could get fancy with it, but the simplestway to make it work would not take more than 60 min prob to code.

----
thats for the input
-crackdaddy

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #8 on: November 09, 2006, 04:48:28 am »
I have worked for days now on attempting to get the calc_order_tax() to work with individual zip codes.  Seemed that nothing I did made a difference.  Finally, I intentionally broke it and still had no change.  Then I finally realized: "This must not be the actual function being called on my system."  I then began the process of working my way through he Ps_checkout code to see what functions were called when.  Turns out that taxes are being calculated in several places.  I'm not sure why this is so complicated but I trust there is good reason for it and I just haven't discovered why yet.

Now I'm wondering if anyone knows or has a clear explanation of the checkout / taxing process?  It looks like the very first place taxes are being processed is in ps_product in a function called get_taxrate().  I'm going to start making changes and seeing how the checkout process is effected.  From there I will attempt to better understand the process.  Since Crackdaddy had his zips working, I assumed  that this would be the same file and location for me to make changes.  Unfortunately, that just doesn't seem to be the case.

Anyone have a clearer understanding of this whole process?

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #9 on: November 09, 2006, 08:48:48 am »
Ok.  I have a working system.  It might need to be tested more thoroughly but I'm satisfied with how it is working thus far.

I still haven't figured out all of the TAX nuances.  I made similar changes to both the ps_checkout and the ps_product files and have everthing working at this time.

The front end code is as follows:

In ps_products.php around line 1490 change the code to match what follows:
Code: [Select]
if( $auth["user_id"] > 0 ) {

$q = "SELECT state, zip, country FROM #__{vm}_user_info ";
$q .= "WHERE user_info_id='".@$_REQUEST["ship_to_info_id"] . "'";
$db->query($q);

$db->next_record();
$state = $db->f("state");
$country = $db->f("country");
$zip = $db->f("zip");

$q = "SELECT tax_rate FROM #__{vm}_tax_rate WHERE tax_country='$country'";
if( $zip ) {
$q .= " AND tax_zip='$zip'";
}

$db->query($q);
/* Set $zip_rate to null then check to see if it is changed. */
$zip_rate = NULL;
if ($db->next_record()){
$zip_rate=$db->f("tax_rate");
}
/* else check to see if there is a default rate for the state. */
else {
$q = "SELECT tax_rate FROM #__{vm}_tax_rate WHERE tax_country='$country' AND tax_state='$state' AND tax_zip='ALL'";
$db->query($q);
if ($db->next_record()) {
$zip_rate=$db->f("tax_rate");

}
}
/* at this point, if $zip_rate has been set, then calculate the tax. */
   if (empty($zip_rate)) {
$_SESSION['taxrate'][$ps_vendor_id] = 0;
   }
   else {
$_SESSION['taxrate'][$ps_vendor_id] = $zip_rate;
}
}

In ps_checkout.php around liine 1194 change to match the following:
Code: [Select]
// Shipping address based TAX
if ( !$this->tax_based_on_vendor_address () ) {
$q = "SELECT state, zip, country FROM #__{vm}_user_info ";
$q .= "WHERE user_info_id='".@$_REQUEST["ship_to_info_id"] . "'";
$db->query($q);
$db->next_record();
$zip = $db->f("zip");
$state = $db->f("state");
$country = $db->f("country");
$q = "SELECT * FROM #__{vm}_tax_rate WHERE tax_country='$country'";
if( $zip ) {
$q .= " AND tax_zip='$zip'";
}
$db->query($q);
/* Set $zip_rate to null then check to see if it is changed. */
$zip_rate = NULL;
if ($db->next_record()){
$zip_rate=$db->f("tax_rate");
}
/* else check to see if there is a default rate for the state. */
else {
$q = "SELECT * FROM #__{vm}_tax_rate WHERE tax_country='$country' AND tax_state='$state' AND tax_zip='ALL'";
$db->query($q);
if ($db->next_record()) {
$zip_rate=$db->f("tax_rate");
}
}
   /* at this point, if $zip_rate has been set, then calculate the tax. */
   if (empty($zip_rate)) {
$order_tax = 0.0;
   }
   else {
$rate = $order_taxable * floatval($zip_rate);
if (empty($rate)) {
$order_tax = 0.0;
}
else {
$order_tax = $rate;
}
   $order_tax_details[$db->f('tax_rate')] = $order_tax;
   }
}

Then for the Back End you will need to add a field named tax_zip to the jos_vm_tax_rate table in your MySQL db.  I made it a varchar field of 5 characters.

Also, for the default State tax rates to work, you will need to turn on the option to allow customers to specify state (in Virtuemar go to Admin->Configuration).

I am attaching a zip file that has the changes that are needed for the English language file and for the HTML files.



[attachment cleanup by admin]

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #10 on: November 09, 2006, 08:54:49 am »
Does anyone know how secure the code I have added is?  I have several SQL statements being created from inputs, so I wonder how susceptible they are to SQL injection.  Am I using VM code that is checking for this.  I wouldn't want to be responsible for someone's site becoming hacked.

N6REJ

  • Full Member
  • ***
  • Posts: 295
  • Beauty By Design
    • PapaBears Pens
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #11 on: December 04, 2006, 00:57:55 am »
thomas you have this set for if by "vendor id", in my case I tax based on customers address.  So where does it go then?
Troy
http://www.treeturner.com
Beauty by Design

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #12 on: December 04, 2006, 04:13:12 am »
n6rej,

The following statement is what I believe you are referring to:
Code: [Select]
// Shipping address based TAX
if ( !$this->tax_based_on_vendor_address () ) {

Notice that the $this has a ! in front of it.  The exclamation point is a shortcut for "not true".  This means that the following code will be processed if it is NOT based on vendor address.  If it is based on vendor address, then it drops down to the "else" statement which I did not change.

Does that help?  It has been working fine for me and I also need to charge based on the customer's shipping address if it is an in state address (it should not be based on their billing address nor our vendor address.)

Have a great day!


N6REJ

  • Full Member
  • ***
  • Posts: 295
  • Beauty By Design
    • PapaBears Pens
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #13 on: December 04, 2006, 20:25:07 pm »
actually I was referring to ps_product.php.  In my version 1.0.7 it looks different then yours... I've included mine so you can see

[attachment cleanup by admin]
Troy
http://www.treeturner.com
Beauty by Design

thomas_freeman

  • Contributing Developer
  • Full Member
  • *
  • Posts: 450
    • Kustom Services
Re: [How to] Dual Tax Mod, City county State Taxes (TESTED & WORKING)
« Reply #14 on: December 05, 2006, 18:00:52 pm »
Your ps_product.php contains the following statement right before the code I'm asking you to change:
Code: [Select]
if (! ps_checkout::tax_based_on_vendor_address ()) {

Again, note the !.  This means that it is checking to make sure that it is NOT based on vendor address.

I'm wondering if the reason you are having problems is because you may have skipped this statement from my previous post:
Quote
Also, for the default State tax rates to work, you will need to turn on the option to allow customers to specify state (in Virtuemar go to Admin->Configuration).

The customer does need to specify what state they are in for my code to work.  This could be changed.  I considered doing so since the zip code should be enough information.  In my previous discussion with Crackdaddy, it seemed he felt that this was a good secondary check before charging the tax.  I'm not sure I agree but I'm also not sure I think it really matters if the configuration is setup properly so I kept it.

Thanks,
Thomas