0

I have following structure of mysql: Record looks like that:

 id invoice_id position_name qty price_without_tax tax currency
  1     12      Processor I7   2     120.00        23%       €
  2     12      Processor I5   3     80.00         15%       €
  3     12      Processor AMD  2     60.00         15%       €
  4     12      Processor I3   1     60.00          0%       €

And I have to sum tax value by the key to get something like that:

According to VAT rate | Net    | Vat Amount | Gross

 23%                  | 240.00 | 55.20      | 295.20

 15%                  | 360.00 | 54.00      | 414.00

 0%                   | 60.00  | 0.00       | 60.00

TOTAL                 | 660.00 | 109.20     | 769.20

I thought to download records by:

$position=mysql_query("SELECT * FROM position where invoice_id = '$id'");
while($row=mysql_fetch_array($position))
    {
Sum there
    }

But really don't know how to sum values of VAT. Could you help me with that?

Improve this question
5
  • vat rate or vat amount ? Commented Jun 2, 2013 at 15:39
  • Vat amount per vat rate Commented Jun 2, 2013 at 15:53
  • you have already made sum 109.20 or you dont know how to echo it ? Commented Jun 2, 2013 at 16:13
  • I don't know how to echo Vat amount according to vat rate, and how to sum that. Commented Jun 2, 2013 at 16:31
  • Vat amount is (tax/100 * (price_without_tax * qty)) i.e. 0.23 * (120 * 2) = 55.20 Commented Jun 2, 2013 at 16:36

1 Answer 1

Reset to default
1

First of all it's not good to use mysql_fetch_array. Since this extension is deprecated as of PHP 5.5.0. More info: mysql_fetch_array

About your question (using your code)

$position=mysql_query("SELECT * FROM position where invoice_id = '$id'");

$products_by_vat = array();

while($row=mysql_fetch_array($position))
{
    $row["tax"] = str_replace("%", "", $row["tax"]); // as far as I can see tax is a string not a number and has % at the end
    if(!isset($products_by_vat[$row["tax"]])) {
        $products_by_vat[$row["tax"]]["price"] = $products_by_vat[$row["tax"]]["vat_amount"] = 0.0;
    }
    $tmp_price = $row["qty"] * $row["price_without_tax"];
    $products_by_vat[$row["tax"]]["price"] += $tmp_price;
    $products_by_vat[$row["tax"]]["vat_amount"] += $tmp_price * ($row["tax"]/100);
}

echo "VAT % | PRICE | VAT AMOUNT | PRICE WITH VAT \n";

$total_price = $total_vat_amount = 0.0;
foreach($products_by_vat as $vat_percent => $vat_info) {
    $total_price += $vat_info["price"];
    $total_vat_amount += $vat_info["vat_amount"];

    $price_with_vat = $vat_info["price"] + $vat_info["vat_amount"];
    echo "{$vat_percent} | {$vat_info["price"]} | {$vat_info["vat_amount"]} | {$price_with_vat} \n"; 
}
$total_price_with_vat  = $total_price + $total_vat_amount;
echo "TOTAL | {$total_price} | {$total_vat_amount} | {$total_price_with_vat} \n";

Considering that price_without_tax is a price per 1 product.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

It's sum amount all Tax's. I would like to sum amount according to vat rate.
What do you mean with "rates of Tax"?
I want to calculate amount of each Vat rates. Like in example: 23% | 240.00 | 55.20 | 295.20
On the code above just echo $tmp_sum * ($row["tax"]/100)); it will give you 55.20
That's true, but if I get 2 same tax, your code will don't sum them.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.