Hi…
I have code in getting the lotcodes and output_qty from dipping table until the req_qty will be equal on the total output_qty from lotcodes.
this codes work correctly on the first:
$cmd = "set @t = 0";
$result = mysql_query($cmd);
$cmd2 = "set @rqty=$ReqQty";
$res = mysql_query($cmd2);
$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, LOT_CODE as code, DATE_ENTRY, CASE WHEN @t+OUTPUT_QTY > @rqty THEN @rqty - @t ELSE OUTPUT_QTY END as qty,@t := @t + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = '$Comp' AND (@t < @rqty)";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
$pcode = $row['pcode'];
$LOT_CODE = $row['code'];
$DATE_ENTRY = $row['DATE_ENTRY'];
$qty = $row['qty'];
$cumulative = $row['cumulative'];
$sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$qty', '$cumulative', '$DATE_ENTRY')";
$res_in = mysql_query($sql, $con);
}
but when I approve or create another jo number, I notice that the list of lotcode from the first jo displayed also on the second jo.
The correct is if on the last lotcode has a balance it will be the first lotcode displayed on the second jo.
here is my code now:
/----check if jo_dump has data-----
$sql = "SELECT * FROM jo_dump";
$res_jodump = mysql_query($sql, $con);
$row_jodump = mysql_num_rows($res_jodump);
//----if jo_dump is <=0, get the lotcode, qty , etc from dipping table until the sum of total output will equal to req qty.
if ($row_jodump <= 0){
$cmd = "set @t = 0";
$result = mysql_query($cmd);
$cmd2 = "set @rqty=$ReqQty";
$res = mysql_query($cmd2);
$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, LOT_CODE as code, DATE_ENTRY, CASE WHEN @t+OUTPUT_QTY > @rqty THEN @rqty - @t ELSE OUTPUT_QTY END as qty,@t := @t + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = '$Comp' AND (@t < @rqty)";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
$pcode = $row['pcode'];
$LOT_CODE = $row['code'];
$DATE_ENTRY = $row['DATE_ENTRY'];
$qty = $row['qty'];
$cumulative = $row['cumulative'];
$sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$qty', '$cumulative', '$DATE_ENTRY')";
$res_in = mysql_query($sql, $con);
}
}
//---else select the last lotcode, qty, etc if the last lotcode has a balance output so it would be the first lotcode on the second jo, here I got a problem in logic and coding to get the last lotcode and display it on the second approve jo number.
else{
$sql = "SELECT jo_number, pcode, lotcode, qty, cumulative, date_entry FROM jo_dump ORDER BY date_entry DESC LIMIT 0,1";
$res_jp = mysql_query($sql, $con);
$row_jp = mysql_fetch_assoc($res_jp);
$lotcode = $row_jp['lotcode'];
$qty = $row_jp['qty'];
$cmd = "set @t = 0";
$result = mysql_query($cmd);
$cmd2 = "set @rqty=$ReqQty";
$res = mysql_query($cmd2);
$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, LOT_CODE as code, DATE_ENTRY, CASE WHEN @t+OUTPUT_QTY > @rqty THEN @rqty - @t ELSE OUTPUT_QTY END as qty,@t := @t + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = '$Comp' AND (@t < @rqty)";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
$pcode = $row['pcode'];
$LOT_CODE = $row['code'];
$DATE_ENTRY = $row['DATE_ENTRY'];
$qty = $row['qty'];
$cumulative = $row['cumulative'];
$sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$qty', '$cumulative', '$DATE_ENTRY')";
$res_in = mysql_query($sql, $con);
}
}
I attach my sample documents for better understanding
Thank you so much.