row by row value

[Bin_2]

hi all!

it might look similar to previous question but actually different..

i'm trying to calculate stock value. for this i want to use "average last purchase value" of item. like

if closing stock is 14 and purchase detail is like this

2014/2/2    15pc @ 38000
2014/2/15   5pc  @ 36000
2014/3/1    5pc  @ 35000

now i want to calculate it from the last row moving up till we reach the number of closing stock

value of 14pcs is (5 + 5 + 4)@(35000, 36000, 38000)

(i might have failed to explain it more clearly. if so, please ask ur confusion i'll reply back. perhaps that will help clarify it)

thanks in advance.....

[Kyle Wiering]

Hello,

I do not see anything particularily tied to PHP in this question. This looks like a general problem that you are looking to have solved. I would suggest approaching the problem and asking for assistance on particular points.

[Bin_2]

u r right @kwiering! its because i couldn't reach anywhere. i cannot make any logic for that. nothing is striking in my mind. os i need help. i dont want any particular code help. i just need how could it been done. just show me the path from which i can rech to my goal...

[Bin_2]

to add more=>
i have retrived last purchase value using

$sql2 = "SELECT * FROM purchase WHERE sid=$sid ORDER BY date DESC LIMIT 1";
$result2 = mysqli_query($connect, $sql2) or die(mysqli_error($connect));
$row2 = mysqli_fetch_array($result2);
echo $row2['qty'] . " @ " . $row2['rate'];

but that is not enough. i've to move upwards from last row and retrive different rates for the different purchases until it reachs to current quantiy

to count current quantity(cqty) i've written =>

// select opening quantity
$sql = "SELECT * FROM stock where sid = '$sid' ";
$result = mysqli_query($connect, $sql);
$rs = mysqli_fetch_object($result);
$oqty = $rs->oqty;

// select purchase quantity
$sql0 = "SELECT *, sum(qty) FROM purchase WHERE sid=$sid";
$result0 = mysqli_query($connect, $sql0) or die(mysqli_error($connect));
$row0 = mysqli_fetch_array($result0)
$pqty = $row0['sum(qty)'];

// select sales quantity
$sql1 = "SELECT *, sum(qty) FROM sales where sid=$sid";
$result1 = mysqli_query($connect, $sql1) or die(mysqli_error($connect));
$row1 = mysqli_fetch_array($result1);
$sqty = $row1['sum(qty)'];

$cqty = $oqty + $pqty - $sqty;

[diafol]

This is a bit confusing. What about this?

SELECT sid, AVG(pc*rate) AS average FROM table WHERE sid= $sid GROUP BY sid

[Bin_2]

thatdoesn't work for me. i dont need average value, i need First In First Out method to calculate stock valuation. like there might be many purchases for single item with different prices. but while selling the first purchased value should be subtracted and remaining stock with their purchase value should be calculated.
to know detail about what i'm trying to do, (if u wish) u can visit this link --
http://accountingexplained.com/financial/inventories/fifo-method

[diafol]

Sorry but I'm not the sharpest tool in the box, but you mentioned...

or this i want to use "average last purchase value" of item

so I assumed you wanted an average value for all the specific items

If you could give us some sample rows of each table, that would be helpful

[Bin_2]

thank u for ur kind reply.
as i explained above, it is called the FIFO method. the attached picture can explain u more clearly. the calculation in the shaded area (BALANCE) is my requirement. please have a look.

--------------------------------------------

[diafol]

I get the method - I looked at the link. What we need is an idea of your data in your relevant tables.

[Bin_2]

ok!
let me submit the exact dump for my testing mysql tables

table=> stock

CREATE TABLE IF NOT EXISTS `stock` (
  `sid` int(3) NOT NULL AUTO_INCREMENT COMMENT 'Stock ID',
  `icode` char(20) NOT NULL COMMENT 'Itrm Code',
  `item` char(25) NOT NULL COMMENT 'Item Name',
  `icat` char(25) NOT NULL COMMENT 'Category',
  `iunit` char(10) NOT NULL,
  `pprice` int(10) NOT NULL COMMENT 'Purchase Price',
  `sprice` int(10) NOT NULL COMMENT 'Selling Price',
  `oqty` int(10) NOT NULL COMMENT 'Opening Quantity',
  `oprice` char(20) NOT NULL COMMENT 'Opening Price',
  `oamt` int(10) NOT NULL COMMENT 'Opening Amt',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `stock`
--

INSERT INTO `stock` (`sid`, `icode`, `item`, `icat`, `iunit`, `pprice`, `sprice`, `oqty`, `oprice`, `oamt`) VALUES
(1, '1234567890', 'ASUS x451CA', 'Laptop', 'pcs', 46000, 57900, 3, '38000', 114000),
(2, '2345678901', 'ASUS X440L', 'Laptops', 'pcs', 63000, 76900, 1, '63000', 63000),
(3, '123', 'MTS Business PC - P43G', 'Desktops', 'set', 32000, 36000, 5, '32000', 160000),
(4, '12345', 'ASUS VS191', 'Moniter', 'pcs', 10500, 11000, 5, '95000', 475000);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

table=> sales

CREATE TABLE IF NOT EXISTS `sales` (
  `svn` int(100) NOT NULL COMMENT 'Sales V. No.',
  `sn` int(10) NOT NULL COMMENT 'S.N.',
  `date` varchar(100) NOT NULL,
  `cid` int(11) NOT NULL,
  `sid` int(10) NOT NULL COMMENT 'Stock ID',
  `qty` int(100) NOT NULL COMMENT 'Quantity',
  `rate` int(11) NOT NULL COMMENT 'Rate',
  `less` int(3) NOT NULL COMMENT 'Discount',
  `amt` int(11) NOT NULL,
  `rvn` varchar(100) NOT NULL,
  `rref` varchar(100) NOT NULL,
  `ramt` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `sales`
--

INSERT INTO `sales` (`svn`, `sn`, `date`, `cid`, `sid`, `qty`, `rate`, `less`, `amt`, `rvn`, `rref`, `ramt`) VALUES
(1, 1, '2014/04/28', 1, 1, 1, 45000, 5, 45000, '0', '', ''),
(1, 2, '2014/04/28', 1, 2, 1, 76900, 5, 73055, '0', '', ''),
(1, 3, '2014/04/28', 1, 3, 1, 40000, 10, 36000, '0', '', ''),
(2, 1, '2014/04/29', 1, 3, 3, 40000, 12, 105600, '0', '', ''),
(2, 2, '2014/04/29', 1, 2, 1, 76900, 10, 69210, '0', '', ''),
(3, 1, '2014/04/29', 1, 1, 2, 45000, 10, 81000, '0', '', ''),
(3, 2, '2014/04/29', 1, 2, 3, 76900, 12, 203016, '0', '', ''),
(4, 1, '2014/04/01', 2, 2, 1, 76900, 5, 73055, '0', '', ''),
(4, 2, '2014/04/01', 2, 3, 1, 40000, 5, 38000, '0', '', ''),
(5, 1, '2014/04/09', 2, 1, 1, 45000, 10, 40500, '0', '', ''),
(6, 1, '2014/04/15', 2, 3, 1, 40000, 5, 38000, '0', '', ''),
(7, 1, '2014/04/16', 2, 2, 1, 76900, 0, 76900, '0', '', ''),
(7, 2, '2014/04/16', 2, 3, 2, 40000, 0, 80000, '0', '', ''),
(8, 1, '2014/04/23', 1, 2, 1, 45000, 0, 45000, '0', '', ''),
(9, 1, '2014/04/30', 2, 3, 3, 40000, 5, 114000, '0', '', ''),
(9, 2, '2014/04/30', 2, 2, 2, 45000, 5, 85500, '0', '', ''),
(0, 0, '2014/04/10', 2, 0, 0, 0, 0, 0, '1', '123', '35000'),
(0, 0, '2014/04/16', 2, 0, 0, 0, 0, 0, '2', '124', '30000'),
(0, 0, '2014/04/17', 2, 0, 0, 0, 0, 0, '3', '125', '25000'),
(0, 0, '2014/04/18', 2, 0, 0, 0, 0, 0, '4', '126', '15000'),
(0, 0, '2014/04/18', 2, 0, 0, 0, 0, 0, '5', '127', '100000'),
(0, 0, '2014/04/22', 2, 0, 0, 0, 0, 0, '6', '128', '25000'),
(0, 0, '2014/04/16', 3, 0, 0, 0, 0, 0, '7', '568786', '138450'),
(0, 0, '2014/05/28', 2, 0, 0, 0, 0, 0, '8', '1111', '5000'),
(0, 0, '2014/05/29', 2, 0, 0, 0, 0, 0, '9', '1112', '55955'),
(0, 0, '2014/05/30', 2, 0, 0, 0, 0, 0, '10', '1113', '125000'),
(0, 0, '2014/05/30', 2, 0, 0, 0, 0, 0, '11', '1115', '25000'),
(10, 1, '2014/05/01', 2, 1, 1, 45000, 0, 45000, '', '', ''),
(10, 2, '2014/05/01', 2, 3, 1, 40000, 0, 40000, '', '', ''),
(11, 1, '2014/05/01', 1, 1, 1, 45000, 0, 45000, '', '', '');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

table=> purchase

CREATE TABLE IF NOT EXISTS `purchase` (
  `pvn` int(100) NOT NULL COMMENT 'Pur V. No.',
  `sn` int(5) NOT NULL COMMENT 'S.N.',
  `date` char(100) NOT NULL,
  `vid` int(11) NOT NULL,
  `sid` int(3) NOT NULL COMMENT 'Stock ID',
  `qty` int(10) NOT NULL COMMENT 'Quantity',
  `rate` int(11) NOT NULL COMMENT 'Rate',
  `less` char(11) NOT NULL COMMENT 'Discount',
  `amt` int(11) NOT NULL,
  `payvn` int(10) NOT NULL,
  `payref` varchar(100) NOT NULL,
  `payamt` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `purchase`
--

INSERT INTO `purchase` (`pvn`, `sn`, `date`, `vid`, `sid`, `qty`, `rate`, `less`, `amt`, `payvn`, `payref`, `payamt`) VALUES
(1, 1, '2014/04/29', 1, 1, 5, 38000, '0', 190000, 0, '', ''),
(1, 2, '2014/04/29', 1, 2, 5, 63000, '0', 315000, 0, '', ''),
(2, 1, '2014/05/13', 1, 1, 20, 38000, '0', 760000, 0, '', ''),
(2, 2, '2014/05/13', 1, 2, 20, 63000, '0', 1260000, 0, '', ''),
(2, 3, '2014/05/13', 1, 3, 20, 32000, '0', 640000, 0, '', ''),
(2, 4, '2014/05/13', 1, 4, 20, 10500, '0', 210000, 0, '', ''),
(3, 1, '2014/05/19', 1, 1, 1, 38000, '0', 38000, 0, '', ''),
(0, 0, '2014/04/16', 1, 0, 0, 0, '', 0, 1, '1234', '500000'),
(0, 0, '2014/04/16', 1, 0, 0, 0, '', 0, 2, '1234', '500000'),
(0, 0, '2014/04/23', 1, 0, 0, 0, '', 0, 3, '1235', '50000'),
(0, 0, '2014/04/24', 1, 0, 0, 0, '', 0, 4, '1236', '100000'),
(0, 0, '2014/04/27', 1, 0, 0, 0, '', 0, 5, '1237', '150000'),
(4, 1, '2014/05/04', 1, 1, 1, 35000, '0', 35000, 0, '', '');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[veedeoo]

If we want FIFO, then we can sort by the date in DESC order. It will be easier if the date is in unix timestamp. Just reverse the order for the LIFO order.

[diafol]

Do you need each sid separately listed in the report or will you just be looking at a specific sid? Or improbably, the total value of all stock?

[Bin_2]

thankz again for the replies...
and i'm sorry i couldn't explain it properly that u couldn't understand it properly.
'sid' are recurring for every 'svn'. only sorting(@veedeoo) and listing(@diafol) is not enough for this. i can list &/or sort in every way. i can get total purchase and sales value and closing quantity for each value. my purpose is to retrive the "value of closing stock". what i need is to subtract every sold product from purchase that is purchased first.
or it can be reverse. calculate value from last purchased item. if remaining(closing) quantity is 14 and last three purchases are
5pc@500, 4pc@600,10pc@650
he closing stock value should be calculated as
5pc@500 = 2500, 4pc@600 = 2400, 5pc@650 = 3250. TOTAL = 8150.
i want to know how can it be moved upwards from the last row of the 'purchase' table until it reaches to the row where it equals to the remaining quantiy.

[Kyle Wiering]

First, assuming your starting input data is the data in your stock table. For a base case, I will stick to one SID (sid 1 is a good start).

#   sid,    pprice, sprice, oqty,   oprice, oamt
    1,      46000,  57900,  3,      38000,  114000

Now, let's take a look at the transactions. I am assuming that time is relevant, and that both sales and purchases affect whatever is going on with the stock price - so we really need to see both together.

To get a nice look, I used this:

select st.sid, st.pprice, st.sprice, st.oqty, oprice, oamt, null as svn, pvn, date, null as cid, vid, qty, rate, less, amt from stock st
join purchase p on st.sid = p.sid where st.sid = 1
union all
select st.sid, st.pprice, st.sprice, st.oqty, oprice, oamt, svn, null as pvn, date, cid, null as vid, qty, rate, less, amt from stock st
join sales s on st.sid = s.sid where st.sid = 1
order by date;

Which resulted in:

-- svn 3 and pvn 1 might not be ordered correctly as the timestamp is not unique.
svn,    pvn,    date,       cid,    vid,    qty,    rate,   less,   amt
5               2014/04/09  2               1       45000   10      40500
1               2014/04/28  1               1       45000   5       45000
        1       2014/04/29          1       5       38000   0       190000
3               2014/04/29  1               2       45000   10      81000
11              2014/05/01  1               1       45000   0       45000
10              2014/05/01  2               1       45000   0       45000
        4       2014/05/04          1       1       35000   0       35000
        2       2014/05/13          1       20      38000   0       760000
        3       2014/05/19          1       1       38000   0       38000

Now, going through the sales and purchases is where I'm unclear. I would expect the oqty to be as follows, the others I'm not certain about the calculation.

#   sid,    pprice, sprice, oqty,   oprice, oamt
    1,      ?,      ?,      2,      ?,      ?
    1,      ?,      ?,      1,      ?,      ?
    1,      ?,      ?,      6,      ?,      ?       
    1,      ?,      ?,      4,      ?,      ?       
    1,      ?,      ?,      3,      ?,      ?       
    1,      ?,      ?,      2,      ?,      ?       
    1,      ?,      ?,      3,      ?,      ?
    1,      ?,      ?,      23,     ?,      ?
    1,      ?,      ?,      24,     ?,      ?

[Bin_2]

thanks for this reply @kwiering! this is now making some sound.
let me clear u some more.
the 'pprice'(purchase price), 'sprice'(selling price) are just for reference to make the user easier while making SELL or PURCHASE. 'oqty' (opening quantity) 'oprice' (price for opening quantity) & 'oamt' (total amount of opening stock) are for further reports. they are used to calculate closing stock valuation. u r coming near to my problem. now let me expalin with ur result.

in ur first query select only " sid, oqty, oprice & oamt " and add the result at the top. and then the main question comes. lets see in ur result ==>

svn,    pvn,    date,       cid,    vid,    qty,    rate,   less,   amt
             opening stock                  3       38000           114000       
5               2014/04/09  2               1       45000   10      40500
1               2014/04/28  1               1       45000   5       45000
        1       2014/04/29          1       5       38000   0       190000
3               2014/04/29  1               2       45000   10      81000
11              2014/05/01  1               1       45000   0       45000
10              2014/05/01  2               1       45000   0       45000
        4       2014/05/04          1       1       35000   0       35000
        2       2014/05/13          1       20      38000   0       760000
        3       2014/05/19          1       1       38000   0       38000
-------------------------------------------------------------------------
             closing stock                  24         ?              ?

how will u calculate this closing stock? what will be the rate if there are different rates while purchasing and they still are in the godown? so for this while selling u have to first sell from opening stock. if opening stock is sold out then sell ur first purchase and contineously come below.

or we can calculate it from below. here we can move upwatds from buttom to calulate price from "pvn-3=> 1pc=>@38000", "pvn-2=>20pcs=>@38000", "pvn-4=>1pc=>@35000" & "pvn-1=>2pc=>@38000". moving upwards untill we reach 24pcs. and now the calculation is {(138000 + 2038000 + 135000 +238000) / 24 = 37875}. i want to know how could this calculation be done in php from mysql table! :(

[Kyle Wiering]

This might be off course, but it was kind of interesting... For the 'test' data, I think this works:

select st.sid, (st.oamt + sum(something.totalv)) / (st.oqty+sum(totalqty)) as newRate, (st.oamt + sum(something.totalv)) as newAmount, (st.oqty+sum(totalqty)) as newQty from (   
select st.sid, sum(qty * rate) as totalv, sum(qty) as totalqty from stock st
join purchase p on st.sid = p.sid group by st.sid  -- where st.sid = 1
union all
select st.sid,  -sum(qty * rate) as totalv,  -sum(qty) as totalqty from stock st
join sales s on st.sid = s.sid group by st.sid -- where st.sid = 1;
) as something
join stock as st on st.sid = something.sid group by st.sid;

I get these results:

sid     New Rate    New Amount  New Qty
1       36125.0000  867000      24
2       60293.7500  964700      16
3       24615.3846  320000      13
4       27400.0000  685000      25

This 'works' because it isn't real time data. Meaning, the transactions have already been completed.

[Bin_2]

thanks. its being really interesting.
but the result is not exact what should be. the result should be deducted (according to my requirement) as '37875' for sid=1.
can u please explain what have u done so that we can discuss upon it coz i've not tried this before....

[Bin_2]

for this i think we have to move upwards 'sum'ming the purchase quantity column until we get the greater than or equal to closing quantity,i.e sum(p.qty)>=$cqty (p.qty is purchase quantity and $cqty is closing balance).. find some idea to loop until we get sum(p.qty)>=$cqty and stop looping at that point...

[Kyle Wiering]

I had taken the total amount for purchase and the original amount added these together and then subtracted the total amount of sales. I divided the resulting number by the final total qty

(totalPurchaseAmount + originalAmount - totalSalesAmount) / (totalPurchaseQty + OriginalQty - totalSalesQty)

How did you get 2@38000 from PVN 1?

Without the original:
((1 * 38000 + 20 * 38000 + 1 * 35000 +5 * 38000) - (1*45000 + 1 * 45000+2 * 45000+1 * 45000+1 * 45000)) / 21 = 35857

With the Original row included
((3*38000) + (1*38000 + 20*38000 + 1*35000 +5*38000) - (1*45000 + 1*45000+2*45000+1*45000+1*45000)) / (3+21) = 36125

[Bin_2]

no! that's not the calculation i requested. the calculation i requested is--
start from the last row

pvn-3    1*38000    38000
pvn-2    20*38000   760000
pvn-4    1*35000    35000 //here the sum is less than closing qty so neeed to add one more row
pvn-1    5*38000    190000 //here the sum meets closing qty. no matter it exceeds. but here it should stop counting and shouldn't go up for another pvn or opening qty.
total    sum(pqty)=27    sum(amt)=1023000
closing amount = sum(amt) / sum(pqty) = 37888.89

(actual result needed is => 909000 / 24 = 37875. but this above result will be ok)...

[Kyle Wiering]

sid 1: 37875
sid 2: 63000
sid 3: 32000
sid 4: 27400

Have you made any attempt to calculate the values yourself?

(if my site is up)View the results here
Here's my final function to calculate the cost of the current level of stock:

function CostOfGoodsForSale($dataSet, $countOfGoodsForSale)
{
    $totalPurchaseValue = 0;
    $totalPurchaseQty = 0;
    $done = false;
    foreach($dataSet as $row)
    {
        // skip sales rows
        if ($row['qty'] < 0)
        {
            continue;
        }

        for ($i=0; $i<$row['qty']; $i++ )
        {
            $totalPurchaseQty++;
            $totalPurchaseValue += $row['rate'];

            if ($totalPurchaseQty == $countOfGoodsForSale)
            {
                return $totalPurchaseValue;
            }
        }
    }

    return $totalPurchaseValue;
}

[Kyle Wiering]

(final query I used as well)

select date, qty, rate, amt from stock st
join purchase p on st.sid = p.sid where st.sid = 1
union all
select date, -qty, rate, amt from stock st
join sales s on st.sid = s.sid where st.sid = 1
union all
select null, oqty, oprice, oamt from stock where sid = 1
order by date desc;

[Bin_2]

wow! thanks! u r great! the result on the link u provided is great and exactly what i expected.

i applied the final query as u stated on my page and it is working fine and returning everything perfectly. but i couldn't apply the CostOfGoodsForSale function correctly. it is returning nothing for me. (i think, perhaps, its bcoz there is no sid selection).
bcoz i'm a bit unfamilier with functions, can u please explain it so that i can understand it more clearly!

[Kyle Wiering]

This can all likely be optimized. I wrote the CostOfGoodsForSale for a single SID.

You'll need to calculate the $countofgoodsforsale - good practice with PHP.
Then for the data table, it is the results from the query built into an array.

$sid = 1;
$query = <<<QUERY
select date, qty, rate, amt from stock st
join purchase p on st.sid = p.sid where st.sid = {$sid}
union all
select date, -qty, rate, amt from stock st
join sales s on st.sid = s.sid where st.sid = {$sid}
union all
select null, oqty, oprice, oamt from stock where sid = {$sid}
order by date desc;
QUERY;

$dataSet = array();
$result = mysql_query($query, $connection);
while(($row = mysql_fetch_array($result, 1) )!== false)
{
    $dataSet[] = $row;
}

// Determine the $countOfGoodsForSale

$finalResult = CostOfGoodsForSale($dataSet, $countOfGoodsForSale) / $countOfGoodsForSale;

If this answers your question, could you be so kind as to mark the question as resolved?

[Bin_2]

thanks. it's been a great support for me.
and sorry for the contineous questions for support. and thank u for the contineous answers without any hassel.

the question is solved now. but still one last question and i'm done. i couldn't loop through each item at once that are in the database. how can i select this CostOfGoodsForSale for each item with a single line like we do with while loop.

i'd done the following procedure before adding ur code to it and it returned all data correctly.

<?php
    $query1 = "SELECT * FROM stock";
    $result1 = mysqli_query($connect, $query1) or die("Error: " . mysqli_error($connect));
echo "
<table border='1' cellpadding='6' style='font-size:14px' cellspacing='0'>
    <tr bgcolor='#009933' align='center' style='font-weight:bold;'>
        <td>SID</td>
        <td>Item Code</td>
        <td>Item</td>
        <td>Category</td>
        <td>Opening Quantity</td>
        <td>Item Inward</td>
        <td>Item Outward</td>
        <td>Current Stock</td>
        <td>Current Value</td>
    </tr>";
    while ($row = mysqli_fetch_array($result1)) {
        $oqty = $row['oqty'];
        $sid = $row['sid'];
        $icode = $row['icode'];
        $item = $row['item'];
        $iunit = $row['iunit'];
        $icat = $row['icat'];
//calculate outward sum
$out = mysqli_query($connect, "SELECT sum(qty) FROM sales WHERE sid='$sid'") or die(mysqli_error($connect));
$ttlout = mysqli_fetch_array($out);
$outqty = $ttlout['sum(qty)'];
//calculate inward sum
$in = mysqli_query($connect, "SELECT sum(qty) FROM purchase WHERE sid='$sid'") or die(mysqli_error($connect));
$ttlin = mysqli_fetch_array($in);
$inqty = $ttlin['sum(qty)'];
//calculate closing stock
$cqty = $oqty + $inqty - $outqty;
echo "
    <tr bgcolor='#009966' align='center'>
        <td>" . $sid . "</td>
        <td>" . $icode . "</td>
        <td><a href=\"stockreport.php?sid=$row[sid]\" class=slink>" . $item . "</a></td>
        <td>" . $icat . "<br />
        <td>" . $oqty . " " . $iunit . "</td>
        <td>" . $inqty . " " . $iunit . "</td>
        <td>" . $outqty . " " . $iunit . "</td>
        <td>" . $cqty . " " . $iunit . "</te>
        <td>&nbsp;</td>";
    }
?>
    </tr>
</table>
<?php

mysqli_close($connect);
?>

but adding ur code to it is not looping through all items. please help me solve this and i'm done with this question and will mark this SOLVED and close this discussion.

thanks for ur support so far....

[Kyle Wiering]

$stockData = QueryForStock($connect);
echo BuildStockDataHtml($stockData, $connect);

/**
 * 
 * @param link $connect
 * @return array
 */
function QueryForStock($connect)
{
    $results = array();
    $query  = "SELECT sid, oqty, icode, item, iunit, icat FROM stock";

    try
    {
        $result = mysqli_query($connect, $query);

        while ($row = mysqli_fetch_array($result)) 
        {
            $results[] = $row;
        }

        mysqli_free_result($result);
    }
    catch(Exception $e)
    {
        error_log($e . __FILE__.' '.__LINE__);
        $results = array();
    }
    return $results;
}

function QueryForItemInward($sid, $connect)
{
    $results = array();

    $statement = mysqli_prepare($connect, "SELECT sum(qty) FROM purchase WHERE sid=?");
    mysqli_stmt_bind_param($statement,'i', $sid);
    mysqli_stmt_execute($statement);
    $statement->bind_result($total);
    $statement->fetch();
    $statement->close();

    return (int)$total;
}

function QueryForItemOutward($sid, $connect)
{
    $results = array();

    $statement = mysqli_prepare($connect, "SELECT sum(qty) FROM sales WHERE sid=?");
    mysqli_stmt_bind_param($statement,'i', $sid);
    mysqli_stmt_execute($statement);
    $statement->bind_result($total);
    $statement->fetch();
    $statement->close();

    return (int)$total;
}

function QueryDataForCurrentValue($sid, $connect)
{
    $query = <<<QUERY
select date, qty, rate, amt from stock st
join purchase p on st.sid = p.sid where st.sid = ?
union all
select date, -qty, rate, amt from stock st
join sales s on st.sid = s.sid where st.sid = ?
union all
select null, oqty, oprice, oamt from stock where sid = ?
order by date desc;
QUERY;


    $statement = mysqli_prepare($connect, $query);
    mysqli_stmt_bind_param($statement,'iii', $sid, $sid, $sid);
    mysqli_stmt_execute($statement);
    $statement->bind_result( $date, $qty, $rate, $amt);

    while($statement->fetch())
    {
        $results[] = [ 'date' => $date, 'qty'=> $qty, 'rate' => $rate, 'amt' => $amt];
    }

    $statement->close();

    return $results;
}

function BuildStockDataHtml(array $stockData, $connect)
{

    $tableRows = '';
    foreach($stockData as $stock)
    {
        $inQuantity         = QueryForItemInward($stock['sid'], $connect);
        $outQuantity        = QueryForItemOutward($stock['sid'], $connect);
        $currentQuantity    = $stock['oqty'] + $inQuantity - $outQuantity;
        $costOfGoodsForSale = CostOfGoodsForSale($stock['sid'], $connect, $currentQuantity);

         $averageCostOfGoodsForSale = $costOfGoodsForSale / $currentQuantity;
        $itemCode =  $stock['icode'];
        $item    =  $stock['item'];
        $category = $stock['icat'];

        $tableRows .= '<tr><td>'.$stock['sid'].'</td><td>'.$itemCode.'</td><td>'.$item.'</td><td>'.$category.'</td><td>'.$stock['oqty'].'</td><td>'.$inQuantity.'</td><td>'.$outQuantity.'</td><td>'.$currentQuantity.'</td><td>'.$averageCostOfGoodsForSale.'</td></tr>';
    }


    $stockTable = <<<STOCKTABLE
<table border='1' cellpadding='6' style='font-size:14px' cellspacing='0'>
    <tr bgcolor='#009933' align='center'>
        <th>SID</th><th>Item Code</th><th>Item</th><th>Category</th><th>Opening Quantity</th><th>Item Inward</th><th>Item Outward</th><th>Current Stock</th><th>Current Value</th>
    </tr>
    {$tableRows}
</table>    
STOCKTABLE;
    return $stockTable;    
}

function CostOfGoodsForSale($sid, $connect, $countOfGoodsForSale)
{
    $dataSet = QueryDataForCurrentValue($sid, $connect);

    $totalPurchaseValue = 0;
    $totalPurchaseQty = 0;
    $done = false;
    foreach($dataSet as $row)
    {
        // skip sales rows
        if ($row['qty'] < 0)
        {
            continue;
        }

        for ($i=0; $i<$row['qty']; $i++ )
        {
        $totalPurchaseQty++;
        $totalPurchaseValue += $row['rate'];

        if ($totalPurchaseQty == $countOfGoodsForSale)
            {
                return $totalPurchaseValue;
            }
        }
    }

    return $totalPurchaseValue;
}

[Bin_2]

thanks! its great. u solved my problem. i hope for ur future helps.

[Bin_2]

sorry! m back again. i got difficulty at once place. can u guide me how can i add all the $camt values of all $sid ?