CODEWITHSUNDEEP
phpmysql
Md. Shamvil Hossain
Md. Shamvil Hossain

Reputation: 424

Availability Calculating logic in php

enter image description here

This is a mysql query result. Now I have to calculate the available quantity in php based on per challan Id. The expected result will be:

ChallanId |  Avaiable Qty  |  unitCostPrice  | totalPrice     
  11      |    7           |     2           |   14         
  12      |    10          |     1           |   10

How it can be done in php? useing foreach or any other tricks.

Upvotes: 0

Views: 91

Answers (3)

BeetleJuice
BeetleJuice

Reputation: 40886

I would do the calculation in SQL by having a smarter query.

select 
    ChallanId,
    UnitCostPrice,
    sum(`ItemIn`)-sum(`ItemOut`) as AvailableQty,
    (sum(`ItemIn`)-sum(`ItemOut`))*UnitCostPrice as totalPrice
from tbl
group by ChallanId

Live demo

Upvotes: 2

scrowler
scrowler

Reputation: 24406

This is psuedo-code kind of, since you didn't share your implementation with PHP.

Firstly, you can assume the Narration from whether ItemIn or ItemOut is not zero, in fact it is probably better to do that since you could then introduce items in as well as items out with the same line. But that's not the question.

$output = array();
foreach ($dbResults as $row) {
    // Create an output entry
    if (!array_key_exists($row['ChallanID'], $output)) {
        $output[$row['ChallanID']] = array('Qty' => 0, 'CostPrice' => 0, 'Total' => 0);
    }

    // Add to totals
    $output[$row['ChallanID']]['Qty'] += ($row['ItemIn'] - $row['ItemOut']);
    $output[$row['ChallanID']]['CostPrice'] = $row['UnitCostPrice']; // you may want to average this instead?
    $output[$row['ChallanID']]['Total'] += $output[$row['ChallanID']]['Qty'] * $output[$row['ChallanID']]['CostPrice'];
}

$output should now contain your desired output. Untested.

Upvotes: 0

Blank
Blank

Reputation: 12378

Though you want to achieve this for PHP solution, but here I think SQL query also can do that:

select
    ChallanId,
    sum(case when Narration = 'in' then ItemIn when Narration = 'out' then  0 - ItemOut end) as Avaiable_Qty,
    unitCostPrice,
    sum(case when Narration = 'in' then ItemIn when Narration = 'out' then  0 - ItemOut end) * unitCostPrice as totalPrice
from (your query here...)
having Avaiable_Qty > 0
group by ChallanId

Upvotes: 3

Related Questions