FIFO method for stock valuation

[justsomeguy]

http://www.php.net//manual/en/mysqli-result.fetch-object.php

[davej]

I guess there are more differences than I expected...

 

Try replacing...

while ($row = mysqli_stmt_fetch_object($result) && $totqty<$cqty) {

...with...

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $totqty<$cqty) {

...that may also force us to change all of the $row->column values to $row['column']

 

I'm looking at example #2 here...

http://www.php.net/manual/en/mysqli-stmt.get-result.php

[funbinod]

thats fine. ok! now its showing just the error msg that u set-

error: recorded qty purchased is insufficient 

now i think there is not error in the script and now i should understand the logic u used here. please help me understand the overall process - how is this set to work?

[davej]

All I'm doing is processing a single query that returns all of the rows for a particular $sid that do not have a remain column value of 0. If the remain column is 0 then that row has already been processed in earlier inventory cycles and has no remaining in-stock items to process. A non-zero remain value means that the row should be processed. For each row I accumulate the quantity and the value of that quantity until I have accumulated a quantity equal to cqty, which is the quantity sold since the last time the inventory was processed.

[funbinod]

ok! i'm getting it somewhat. still a bit confusion..

can u explain what is this line conditioning here....

$row['remain']+$totqty > $cqty

& here

elseif ($row['qty']+$totqty > $cqty){

& here

if ($cqty != $totqty) {

actually i'm not understanding the use of $totqty

Edited June 23, 2014 by funbinod

[davej]

Those are the conditions that would tell you if the current row contains more items than are needed to reach a total count of cqty.

[funbinod]

then i thing on my side also there is all fine.

 

its still showing

error: recorded qty purchased is insufficient

[davej]

Well, maybe there is a logic error in there somewhere. It might be a good idea to echo out the values every time it goes through the loop. The $totqty and $totamt values should start at zero and then increase with each processed line until $totqty == $cqty.

 

Also change...

$sql = "select qty, amt, remain from purchase where sid=? and remain>0 order by date desc";

...back to...

$sql = "select qty, amt, remain from purchase where sid=? and remain!=0 order by date desc";

I forgot that I was using != because I wanted to exclude NULL values.

[funbinod]

remain>0 & remain!=0 are, i think, round same conditions. even changing that didn't work...

[davej]

Is this the current code?

$connect = mysqli_connect("url", "user", "password", "database");if (!$connect){    $error = mysqli_connect_error();    $errno = mysqli_connect_errno();    echo "$errno: $errorn";    exit();}$sid = 1;  // this is the stock identifier$cqty = 5; // this is the qty sold$totqty = 0;$totamt = 0;$sql = "select qty, amt, remain from purchase where sid=? and remain!=0 order by date desc";$stmt = mysqli_prepare($connect, $sql);if (mysqli_stmt_bind_param($stmt, 'i', $sid)){  mysqli_stmt_execute($stmt) or die("Error: " . mysqli_error($connect));  $result = mysqli_stmt_get_result($stmt);  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $totqty<$cqty) {        if ( !is_null($row['remain']) ){        if ($row['remain']>0 && $row['remain']+$totqty > $cqty){            $diff = $cqty - $totqty;            $totqty = $cqty;            $totamt += $diff*$row['amt']/$row['qty'];            //            //            //            // now you must record the remain count = $row['remain']-$diff            //            //            //        }else{            $totqty += $row['remain'];            $totamt += $row['remain']*$row['amt']/$row['qty'];            //            //            //            // now you must record the remain=0            //            //            //        }    }elseif ($row['qty']+$totqty > $cqty){        $diff = $cqty-$totqty;        $totqty = $cqty;        $totamt += $diff*$row['amt']/$row['qty'];        //        //        //        // now you must record the remain count = $row->qty-$diff        //        //        //    }else{        $totqty += $row['qty'];        $totamt += $row['amt'];        //        //        //        // now you must record the remain=0        //        //        //    }        echo "qty={$row['qty']} amt={$row['amt']} remain={$row['remain']} cqty={$cqty} totqty={$totqty} totamt={$totamt} <br/>";  }//end while  if ($cqty != $totqty){      echo "error: recorded totqty={$totqty} purchased is insufficient to match cqty={$cqty}";  }}else{  echo 'error: could not bind';}mysqli_stmt_close($stmt);mysqli_close($connect);

[funbinod]

yes its the same--

<link rel="stylesheet" type="text/css" href="style.css" /><?phprequire_once('functions.php');if (!$connect){    $error = mysqli_connect_error();    $errno = mysqli_connect_errno();    echo "$errno: $errorn";    exit();}$sid = 1;  // this is the stock identifier$cqty = 7; // this is the qty sold$totqty = 0;$totamt = 0;$sql = "select qty, amt, remain from fifo where sid=? and remain!=0 order by date desc";$stmt = mysqli_prepare($connect, $sql);if (mysqli_stmt_bind_param($stmt, 'i', $sid)){	mysqli_stmt_execute($stmt) or die("Error: " . mysqli_error($connect));	$result = mysqli_stmt_get_result($stmt);	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $totqty<$cqty) {		if ( !is_null($row['remain']) ){			if ($row['remain']>0 && $row['remain']+$totqty > $cqty){				$diff = $cqty - $totqty;				$totqty = $cqty;				$totamt += $diff*$row['amt']/$row['qty'];			// now you must record the remain count = $row->remain-$diff			} else {				$totqty += $row['remain'];				$totamt += $row['remain']*$row['amt']/$row['qty'];			// now you must record the remain=0			}		} else if ($row['qty']+$totqty > $cqty){			$diff = $cqty-$totqty;			$totqty = $cqty;			$totamt += $diff*$row['amt']/$row['qty'];		// now you must record the remain count = $row->qty-$diff		} else {			$totqty += $row['qty'];			$totamt += $row['amt'];		// now you must record the remain=0		}	    echo "qty={$row['qty']} amt={$row['amt']} remain={$row['remain']} cqty={$cqty} totqty={$totqty} totamt={$totamt} <br/>";	}	if ($cqty != $totqty) {		echo "error: recorded totqty={$totqty} purchased is insufficient to match cqty={$cqty}";	} else { echo $totamt; }} else {	echo 'error: could not bind';}mysqli_stmt_close($stmt);mysqli_close($connect);?>

[davej]

What is the echo output from the loop? Also what is the primary key of this table?

[funbinod]

wait wait!!

changing

	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $totqty<$cqty) {

to

        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

looks like it worked. now it returned...

 

 

qty=1 amt=45000 remain=1 cqty=7 totqty=1 totamt=45000qty=5 amt=230000 remain=5 cqty=7 totqty=6 totamt=275000qty=3 amt=114000 remain=1 cqty=7 totqty=7 totamt=313000313000

 

[funbinod]

and one thing more--

i removed all conditions relating 'remain' (columns, where clause) and i got the same result. i think i dont need this extra remain column. here is the code i changed--

<?phprequire_once('functions.php');if (!$connect){    $error = mysqli_connect_error();    $errno = mysqli_connect_errno();    echo "$errno: $errorn";    exit();}$sid = 1;  // this is the stock identifier$cqty = 5; // this is the qty sold$totqty = 0;$totamt = 0;$sql = "select qty, amt from fifo where sid=? order by date desc";$stmt = mysqli_prepare($connect, $sql);if (mysqli_stmt_bind_param($stmt, 'i', $sid)){	mysqli_stmt_execute($stmt) or die("Error: " . mysqli_error($connect));	$result = mysqli_stmt_get_result($stmt);	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {		if ( !is_null($row['qty']) ){			if ($row['qty']>0 && $row['qty']+$totqty > $cqty){				$diff = $cqty - $totqty;				$totqty = $cqty;				$totamt += $diff*$row['amt']/$row['qty'];			// now you must record the remain count = $row->remain-$diff			} else {				$totqty += $row['qty'];				$totamt += $row['qty']*$row['amt']/$row['qty'];			// now you must record the remain=0			}		} else if ($row['qty']+$totqty > $cqty){			$diff = $cqty-$totqty;			$totqty = $cqty;			$totamt += $diff*$row['amt']/$row['qty'];		// now you must record the remain count = $row->qty-$diff		} else {			$totqty += $row['qty'];			$totamt += $row['amt'];		// now you must record the remain=0		}	    echo "qty={$row['qty']} amt={$row['amt']} cqty={$cqty} totqty={$totqty} totamt={$totamt} <br/>";	}	if ($cqty != $totqty) {		echo "error: recorded totqty={$totqty} purchased is insufficient to match cqty={$cqty}";	} else { echo $totamt; }} else {	echo 'error: could not bind';}mysqli_stmt_close($stmt);mysqli_close($connect);?>

it returned the same

 

 

qty=1 amt=45000 cqty=5 totqty=1 totamt=45000qty=5 amt=230000 cqty=5 totqty=5 totamt=229000qty=3 amt=114000 cqty=5 totqty=5 totamt=229000229000

 

and after this i'll need another guidance about summing all the $totamt for all the $sid from one category

[davej]

well, it looks like it may have worked. It should end with totqty equal to cqty.

[funbinod]

yes! it did the same. and gave the perfect result. thank u

 

now i got one more confusion.

i've many $sid there in the table. how can i get $totamt for all....?

 

i can get $cqty for all items ($sid) with the following. but getting $camt( or $ totamt) for all of them is quite difficult

 

i tried something rubbish on line 65,66 but that is selecting only from $sid=1...

<?phprequire_once('functions.php');$user = new User;if (!$user->isLoggedIn) {	die(header("location: login.php"));}echo "<h2>ALL Items</h2><hr noshade='noshade' size='2px' color='#000000' /><br />";//echo "Total Closing Stock: Rs." .  number_format(($camtsum), 2, '.', ',') . "<br />";$sql = mysqli_query($connect, "SELECT null date, item, oamt, oqty, sum(samt)samt, sum(sqty)sqty, sum(pamt)pamt, sum(pqty)pqty, sum(cnamt)cnamt, sum(cnqty)cnqty, sum(damt)damt, sum(dqty)dqty, tr_type, sid, catFROM (SELECT null date, item, oamt, oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, null damt, null dqty, null tr_type, st.sid, cat from stock st	JOIN category cat on cat.catid=st.catid GROUP BY st.sidunion allSELECT date, item, null oamt, null oqty, sum(amt)samt, sum(qty)sqty, NULL AS pamt, NULL AS pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, s.sid, cat FROM sales s	JOIN stock st on st.sid=s.sid	JOIN category cat on cat.catid=st.catid GROUP BY s.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, sum(amt)pamt, sum(qty)pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, p.sid, cat FROM purchase p	JOIN stock st on st.sid=p.sid	JOIN category cat on cat.catid=st.catid GROUP BY p.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, sum(amt)cnamt, sum(qty)cnqty, null damt, null dqty, tr_type, c.sid, cat FROM cnote c	JOIN stock st on st.sid=c.sid	JOIN category cat on cat.catid=st.catid GROUP BY c.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, sum(amt)damt, sum(qty)dqty, tr_type, d.sid, cat FROM dnote d	JOIN stock st on st.sid=d.sid	JOIN category cat on cat.catid=st.catid GROUP BY d.sid ORDER BY item) AS test	GROUP BY sid") or die("Error:" . mysqli_error($connect));echo "<table border='1' cellpadding='6' style='font-size:14px' cellspacing='0' width='95%' align='center'>	<tr bgcolor='#009933' align='center' style='font-weight:bold;'>		<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>";while ($row = mysqli_fetch_array($sql)) {	$inqty = $row['pqty']+$row['cnqty'];	$outqty = $row['sqty']+$row['dqty'];	$cqty = $row['oqty']+$inqty-$outqty;	echo "	<tr bgcolor='#FFCC99' align='center'>		<th><a href='stockreport.php?sid=$row[sid]' class='index'>".$row['item']."</a></th>		<th>".$row['cat']."</th>		<th>";		if ($row['oqty'] == NULL) {			echo '0';		} else {			echo $row['oqty'];		}	echo "</th>		<th>".$inqty."</th>		<th>".$outqty."</th>		<th>".$cqty."</th>";	$sid = $row['sid'];	require_once('classStockReport.php'); // this is the code that u suggested....	echo "<th>".$totamt."</th>	</tr>";}echo "</table>";mysqli_close($connect);?>

Edited June 24, 2014 by funbinod

[davej]

I'm not sure I understand the purchase table. We need a column such as "remain" or "instock" or it should not work because there would be no way to record or process partially sold purchase-lines. Also we need to record this processing line-by-line in an array so that we can make a second pass and update that "remain" or "instock" column. To do this we need to also save the primary key of each line. What is the primary key? Also where do you obtain the $cqty value for each $sid? Is this from another table?

[davej]

 

wait wait!!

changing

	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $totqty<$cqty) {

to

        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

 

 

Oh, I think we need that. Maybe it needs added parens such as this...

while (($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) && $totqty<$cqty) {

[davej]

 

i've many $sid there in the table. how can i get $totamt for all....?

 

i can get $cqty for all items ($sid) with the following. but getting $camt( or $ totamt) for all of them is quite difficult

 

We probably need to do a query to obtain the $cqty for each $sid. That query would probably use GROUP BY sid. As I mentioned above I'm not clear on where you are obtaining the $cqty value.

[funbinod]

if u look at my previous reply (#41) u'll understand it, i hope.

there are different tables to record purchase and sales and debit note and credit note. will making transactions i simultaneously insert opening stock, purchase and credit note to their respective tables and also to another table named 'fifo'. $cqty (closing or current quantity) is calculated from 'stock'(that has opening stock), 'purchase', 'sales', 'cnote' and 'dnote' tables like this--

$sql = mysqli_query($connect, "SELECT null date, item, oamt, oqty, sum(samt)samt, sum(sqty)sqty, sum(pamt)pamt, sum(pqty)pqty, sum(cnamt)cnamt, sum(cnqty)cnqty, sum(damt)damt, sum(dqty)dqty, tr_type, sid, catFROM (SELECT null date, item, oamt, oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, null damt, null dqty, null tr_type, st.sid, cat from stock st	JOIN category cat on cat.catid=st.catid GROUP BY st.sidunion allSELECT date, item, null oamt, null oqty, sum(amt)samt, sum(qty)sqty, NULL AS pamt, NULL AS pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, s.sid, cat FROM sales s	JOIN stock st on st.sid=s.sid	JOIN category cat on cat.catid=st.catid GROUP BY s.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, sum(amt)pamt, sum(qty)pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, p.sid, cat FROM purchase p	JOIN stock st on st.sid=p.sid	JOIN category cat on cat.catid=st.catid GROUP BY p.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, sum(amt)cnamt, sum(qty)cnqty, null damt, null dqty, tr_type, c.sid, cat FROM cnote c	JOIN stock st on st.sid=c.sid	JOIN category cat on cat.catid=st.catid GROUP BY c.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, sum(amt)damt, sum(qty)dqty, tr_type, d.sid, cat FROM dnote d	JOIN stock st on st.sid=d.sid	JOIN category cat on cat.catid=st.catid GROUP BY d.sid ORDER BY item) AS test	GROUP BY sid") or die("Error:" . mysqli_error($connect));while ($row = mysqli_fetch_array($sql)) {	$inqty = $row['pqty']+$row['cnqty'];	$outqty = $row['sqty']+$row['dqty'];	$cqty = $row['oqty']+$inqty-$outqty;

and after having $cqty for single $sid i calculated $totamt (closing amount for last inward items (purchase & debit note)) i use the method u suggested from 'fifo' table.

 

if u need here is the schema for the tables..

http://sqlfiddle.com/#!2/da4187

[davej]

Ok, so you do have a GROUP BY sid query that gives you the $cqty for each sid.

[funbinod]

yes! so i need now is to get $totamt for all $sid and SUM them

Edited June 25, 2014 by funbinod

[davej]

OK, that looks good if it works. The other stuff we have been working earlier on would just go inside this while loop.

[funbinod]

OK, that looks good if it works. The other stuff we have been working earlier on would just go inside this while loop.

 

sorry i couldn't understand

[davej]

See the while loop at the bottom of your message #46 above? We'll just use that.