Problem in condition and logic to get the last row data and displayed

[newphpcoder]

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.

18389_.zip

[Barand]

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.

 

Of course they will, the data hasn't changed.

[Barand]

Note to others reading this thread

 

If you want to know what he's talking about you need to read one of his other threads on this subject

http://www.phpfreaks.com/forums/index.php?topic=359334.0

[Illusion]

You should also store output_qty in jo_dump and columns output_qty,qty shouldn't be varchar type.

 

  
$cmd = "set @t = 0";
$result = mysql_query($cmd);
$cmd2 = "set @rqty=$ReqQty";
$res = mysql_query($cmd2); 
$cmd3="set @lastrequest_date='0000-00-00 00:00:00'";
$res = mysql_query($cmd3); 

$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, LOT_CODE as code, op_qty,  DATE_ENTRY,   CASE WHEN @t+op_qty > @rqty THEN @rqty  - @t  ELSE op_qty END as qty,@t := @t + d.op_qty as cumulative  from 

((select LOT_CODE,output_qty -qty as op_qty,@lastrequest_date:=DATE_ENTRY as DATE_ENTRY from jo_dump where output_qty > qty and pcode = '$Comp' order by DATE_ENTRY desc limit 1) UNION

(select LOT_CODE,output_qty as op_qty ,DATE_ENTRY from dipping  where DATE_ENTRY >@lastrequest_date and SUBSTR(LOT_CODE, 9,4)='$Comp' )  )d  WHERE (@t < @rqty)";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
$pcode = $row['pcode'];   
$LOT_CODE = $row['code'];
$DATE_ENTRY = $row['DATE_ENTRY'];
$output_qty=$row['op_qty'];
$qty = $row['qty'];
$cumulative = $row['cumulative'];


$sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, qty,output_qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', $qty,$output_$qty,$cumulative, '$DATE_ENTRY')";
$res_in = mysql_query($sql, $con);

[Illusion]

The above query fails when JO's  last record OUTPUT_QTY and QTY are same in JO_DUMP table

 

 

with fix

 

$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, LOT_CODE as code, op_qty,  DATE_ENTRY,   CASE WHEN @t+op_qty > @rqty THEN @rqty  - @t  ELSE op_qty END as qty,@t := @t + d.op_qty as cumulative  from 

((select LOT_CODE,output_qty -qty as op_qty,@lastrequest_date:=DATE_ENTRY as DATE_ENTRY from jo_dump where  pcode = '$Comp' order by DATE_ENTRY desc limit 1) UNION

(select LOT_CODE,output_qty as op_qty ,DATE_ENTRY from dipping  where DATE_ENTRY >@lastrequest_date and SUBSTR(LOT_CODE, 9,4)='$Comp' )  )d  WHERE (@t < @rqty) having op_qty >0 ";

[fenway]

@newphpcoder -- last warning -- stop posting new topics for the same issue.