Page 1 of 1

Tax adding repeatedly :(

Posted: Thu Mar 16, 2006 4:05 pm
by LiveFree
Hey Guys,

I have a system developed where the script checks the Product CAT Id against a DB ... if the Cat is returned as 2, then it adds tax (17.5%)... but when I select a tax-able item and a non-tax-able item... it adds it up for both..

Code:

Code: Select all

foreach ($_SESSION['purchase_prods'] AS $arr){
 
 $tot+=$arr[1] * $arr[2];
  $qry=mysql_query("SELECT cat.catid AS catid FROM Products AS p, categories AS cat WHERE p.Product_ID='$arr[0]' AND p.Product_Cat_ID=cat.catid AND cat.catid='2'") OR DIE (mysql_error());
 $result=mysql_num_rows($qry);
 
switch ($result){
  
  case '1':
  $vat=(.175 * $tot);
  $hax=$sub_total+$vat;
   $vat=number_format($vat,2,'.','');
   $sub_total=$hax; 
   break;
   
  default:
  $vat='0.00';
  break;
  
}


}
$sub_total=number_format($sub_total, 2, '.', "");
Thanks for any help! :)

Posted: Thu Mar 16, 2006 4:20 pm
by josh
You'll have to ask it for each item separately and add tax to only those that need it. If you have an array it should be fairly easy to sum up the array values at the end to get the total.

Posted: Thu Mar 16, 2006 4:22 pm
by LiveFree
How is that possible if I need to check EVERY product selected (ID) for the SQL match?

Posted: Thu Mar 16, 2006 4:46 pm
by onion2k
Personally, I prefer to do all calculations on the cost of items in the SQL .. including adding VAT .. ideally in a stored procedure, but just in the raw SQL is ok if you're not running MySQL 5 yet.

Code: Select all

define ("VAT_RATE",1.175);
$sql  = "SELECT *, price as price_no_tax, ROUND(IF(taxable=1, price*".VAT_RATE.", price),2) as price_inc_tax FROM products WHERE id = '1';";
$result = mysql_query($sql);
$record = mysql_fetch_object($result);
echo "Price without tax: ".$record->price_no_tax;
echo "Price with tax: ".$record->price_inc_tax;
You'd need to tweak that code to work with your database structure, natch.

Posted: Thu Mar 16, 2006 6:38 pm
by LiveFree
Hey Thanks onion :), I'll look at that in my next job

But I think jspro's idea will work .. I just need some help implementing it :D

Posted: Thu Mar 16, 2006 9:28 pm
by josh
Select every product in the order, loop over it applying tax if it needs it, then adding it to a temp variable let's call it $total

so the psuedo code looks like

Code: Select all

while(list($productID,$needsTAX,$price)=mysql_fetch_row($result)) {
  $total+=$price;
  $total+=$needsTAX ? $price * TAX_AMNT : 0;
}

Posted: Fri Mar 17, 2006 3:30 pm
by LiveFree
Thanks js!

But ... the VAT is still added for both products (even when 1 doesnt need it) and the subtotal does not include the VAT generated...

Here is my modified code:

Code: Select all

foreach ($_SESSION['purchase_prods'] AS $arr){
 
 $price=$arr[1] * $arr[2];
  $sql=mysql_query("SELECT cat.catid AS catid FROM Products AS p, categories AS cat WHERE p.Product_ID='$arr[0]' AND p.Product_Cat_ID=cat.catid");
 $result=mysql_fetch_array(mysql_query("SELECT cat.catid AS catid FROM Products AS p, categories AS cat WHERE p.Product_ID='$arr[0]' AND p.Product_Cat_ID=cat.catid")) OR DIE (mysql_error());
 
 if ($result['catid'] == 2){
   
$needsTAX=TRUE;
}
define('TAX_AMNT',.175);
while(list($needsTAX,$price)=mysql_fetch_row($sql)) {
  $sub_total+=$price;
  $vat=(.175 * $sub_total);
  $sub_total+=$needsTAX ? $price * TAX_AMNT : '0.00';
}

$vat=number_format($vat,2,'.',''); 
  
}
Again, thanks :)