Need Help in getting only data which can suffix the total Qty

newphpcoder · May 15, 2012

Hi...

I have a

table: bom

fields:

Comp = P28

ReqQty = 31968

and I have table

table: dipping

fields needed to get data

LOT_CODE

OUTPUT_QTY

DATE_ENTRY

now I only want to get LOT_CODE and OUTPUT_QTY where can suffix the ReqQty ORDER BY DATE_ENTRY

I tried this query:

SELECT SUBSTR(LOT_CODE, 9,4) AS Comp, LOT_CODE, OUTPUT_QTY, DATE_ENTRY FROM dipping WHERE SUBSTR(LOT_CODE, 9,4) = 'P28'   ORDER BY DATE_ENTRY ASC;

I attach the result of this query.

I want only query or get is the LOT_CODE where the OUTPUT can suffix the ReqQty.

Simple Example:

ReqQty = 100

Comp = P28

LOT_CODE--OUTPUT_QTY---DATE_ENTRY

00001P28--- 50------------2012-05-15

00002P28----25------------2012-05-16

00003P28----50------------2012-05-17

00004P28----25-----------2012-05-18

in query I need to get only the

00001P28--- 50------------2012-05-15

00002P28----25------------2012-05-16

00003P28----50------------2012-05-17

because it suffix my ReqQty as you can see in this LOT_CODE

00003P28----50------------2012-05-17 i only need is 25 to suffix my Req

Qty so I need to display only:

00001P28--- 50------------2012-05-15

00002P28----25------------2012-05-16

00003P28----25------------2012-05-17

Feel free to ask me if theirs any question for better understanding of my problem

Thank you

18360_.zip

Illusion · May 15, 2012

I am afraid we can't put all that intelligence in single SQL query so you have to get the records which have LOT_CODE and apply quantity logic after fetching the results

pseudo code

requiredQty=x;
foreach (result in resultsSet){
code=result.LOT_CODE;
avilableQty=result.OUTPUT_QTY;
date=result.DATE_ENTRY
if (avilableQty <  requiredQty){
print code+ "----"+ avilableQty+"----"+date;
requiredQty=requiredQty-avilableQty;

}
else {
print code+ "----"+ requiredQty+"----"+date;
break;
}

}

Barand · May 15, 2012

Needs more experimentation to get 25 in the final row's qty but this gets you records you need

mysql> set @t=0;


mysql> SELECT SUBSTR(lot_code,6) as code, output_qty,
    -> date_entry, @t := @t + d.output_qty
    -> FROM dipping d
    -> WHERE SUBSTR(lot_code,6)='P28' AND @t < 100;
+------+------------+------------+-------------------------+
| code | output_qty | date_entry | @t := @t + d.output_qty |
+------+------------+------------+-------------------------+
| P28  |         50 | 2012-05-15 |                      50 |
| P28  |         25 | 2012-05-16 |                      75 |
| P28  |         50 | 2012-05-17 |                     125 |
+------+------------+------------+-------------------------+

Illusion · May 15, 2012

if you can afford CPU time and memory to have two more columns in your query ...following query will the fetch the data you need .

Extension to what Barand had suggested

set @rqty=130;

set @tqty=0;

set @cqty=0;



select SUBSTR(code,6) as code,updatedate,@cqty:=@tqty beforeAccumlation, case when @tqty+d.qty  < @rqty then  @tqty := @tqty + d.qty else  @tqty :=  @tqty +(d.qty-((d.qty +@tqty)-@rqty))  end as accumlation,@tqty-@cqty as contribution 

from testtable d where SUBSTR(code,6)='P28' AND @tqty < @rqty


+ --------- + --------------- + ---------------------- + ---------------- + ----------------- +
| code      | updatedate      | beforeAccumlation      | accumlation      | contribution      |
+ --------- + --------------- + ---------------------- + ---------------- + ----------------- +
| P28       | 2012-05-15      | 0                      | 50               | 50                |
| P28       | 2012-05-16      | 50                     | 75               | 25                |
| P28       | 2012-05-17      | 75                     | 125              | 50                |
| P28       | 2012-05-18      | 125                    | 130              | 5                 |
+ --------- + --------------- + ---------------------- + ---------------- + ----------------- +
4 rows

Barand · May 15, 2012

This was what I had in mind when I mentioned more experiments

mysql> set @t = 0;


mysql> SELECT lot_code as code,
    -> date_entry, @t := @t + d.output_qty as cumulative,
    -> CASE WHEN @t+output_qty > 100 THEN @t - 100 ELSE output_qty END as qty
    -> FROM dipping d
    -> WHERE SUBSTR(lot_code,6)='P28' AND @t < 100;

+----------+------------+------------+------+
| code     | date_entry | cumulative | qty  |
+----------+------------+------------+------+
| 00001P28 | 2012-05-15 |         50 |   50 |
| 00002P28 | 2012-05-16 |         75 |   25 |
| 00003P28 | 2012-05-17 |        125 |   25 |
+----------+------------+------------+------+

newphpcoder · May 16, 2012

Thank you for your code..

Now i tried to add ORDER BY DATE_ENTRY ASC but it happened is all P28 was displayed

here is my code:

set @t = 0;
SELECT LOT_CODE as code,  DATE_ENTRY, @t := @t + d.OUTPUT_QTY as cumulative,  CASE WHEN @t+OUTPUT_QTY > 31680 THEN @t - 31680 ELSE OUTPUT_QTY END as qty   FROM dipping d  WHERE SUBSTR(LOT_CODE, 9,4)='P28' AND (@t < 31680) ORDER BY DATE_ENTRY ASC;

Thank you

newphpcoder · May 16, 2012

Hi

Thank you for your query i tried it:

set @t = 0;
SELECT LOT_CODE as code,  DATE_ENTRY, @t := @t + d.OUTPUT_QTY as cumulative,  CASE WHEN @t+OUTPUT_QTY > 31680 THEN @t - 31680 ELSE OUTPUT_QTY END as qty   FROM dipping d  WHERE SUBSTR(LOT_CODE, 9,4)='P28' AND (@t < 31680) ;

and I just want to know what is the use of cumulative and qty?

I attach the output of this query:

and when I add the cumulative and qty the output is:

cumulative :252700

qty: 26004

both of them did not match on 31680

Thank you

newphpcoder · May 16, 2012

Hi

For better understand i create a sampe documents for better understanding of my post thread

I hope it could help

Thank you

18368_.zip

Illusion · May 16, 2012

try this



set @t = 0;

set @rqty=31680;

SELECT 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)='P28' AND (@t < @rqty;

qty is the column you are interested in and shows quantity fetched from each record to get the required quantity ... SUM of this column data gives you 31680

newphpcoder · May 16, 2012

I attach the updated logic.zip

Thank you

18369_.zip

newphpcoder · May 16, 2012

I tried this:

set @t = 0;
set @rqty=31680; 
SELECT 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)='P28' AND (@t < @rqty;

and I got this:

(0 row(s) affected)

(0 ms taken)

(0 row(s) affected)

(0 ms taken)

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

(0 ms taken)

Thank you

newphpcoder · May 16, 2012

I attach the SUM output total

Thank you

Illusion · May 16, 2012

This is output from the first query , right ?

I am using different column names and variable names for testing so there is syntax error while updating ...

There is an extra parenthesis in my previous query at the end "AND ( @t < @rqty" change it to " AND @t < @rqty" . It should work fine.

newphpcoder · May 16, 2012

This code was work:

set @t = 0;
set @rqty=31680; 
SELECT 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) = 'P28' AND (@t < @rqty);

can you explain it to me?

Thank you

newphpcoder · May 16, 2012

Hi..

From mysql query or syntax i import it in php code:

$ReqQty = $Qty * $POReq;

$t = 0;

$sql = "SELECT LOT_CODE as code, DATE_ENTRY,

CASE WHEN $t+OUTPUT_QTY > $ReqQty

THEN $ReqQty -$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 < $ReqQty)";

I echo the query:

SELECT LOT_CODE as code, DATE_ENTRY, CASE WHEN 0+OUTPUT_QTY > 6660 THEN 6660 -0 ELSE OUTPUT_QTY END as qty, 0 := 0 + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = 'P28' AND (0 < 6660)

and when I run this query to sql for checking I got an error:

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0 + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = '' at line 1

(0 ms taken)

Thank you

Illusion · May 16, 2012

you couldn't see a problem ? $t is supposed to be a mysql variable ...so you need to set @t value thru another query before executing this query.

You need to refresh your php and mysql basics

newphpcoder · May 16, 2012

what do you mean for this:

$t is supposed to be a mysql variable ...so you need to set @t value thru another query before executing this query.

you mean:

$t = @t = 0;????

Thank you

Illusion · May 16, 2012

first query

$mysql_variable="@t";
$first_query="set $mysql_variable=0";

second query



$second_query="SELECT LOT_CODE as code,  DATE_ENTRY,   
CASE WHEN $mysql_variable+OUTPUT_QTY > $ReqQty
THEN $ReqQty  -$mysql_variable 
ELSE OUTPUT_QTY
END as qty,
$mysql_variable := $mysql_variable + d.OUTPUT_QTY as cumulative   
FROM dipping d  WHERE SUBSTR(LOT_CODE, 9,4) = '$Comp' AND ($mysql_variable < $ReqQty)";

newphpcoder · May 16, 2012

I resolved it using this code:

$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'];
}

Thank you

newphpcoder · May 17, 2012

Hi...

I tought it was resolve yet i notice that theirs a problem when I approve with same pcode.

here is my scenario.

1. I need to click Approve Button to create JO Number and compute Req Qty.

2. get lot code that sum of output will equal to Req Qty.

I notice that when I approve another data I notice that the lotcode from the first i approve was also get or displayed.

here is my whole code:

<?php
    error_reporting(0);
  date_default_timezone_set("Asia/Singapore"); //set the time zone  
$con = mysql_connect('localhost', 'root','');

if (!$con) {
    echo 'failed';
    die();
}

mysql_select_db("mes", $con);
$Date_Shelve =date('Y-m-d H:i:s');  


?>
<html>
<head>
<title>Job Order</title> 
<link rel="stylesheet" type="text/css" href="kanban.css" />
<style type="text/css">
#SR_date{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .8em;
    margin-left: 10px;
    margin-right: 770px;
    width: auto;
    height: auto;
    float: left;
    top : 10px;
}

#disp_btn{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .8em;
    margin-left: 10px;
    top: 10px;
}


#disp{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .8em;
    margin-left: 10px;
    top: 10px;
}

table {          
    margin: 12px;
    margin-left: 3px;
    font-family: Arial, Helvetica, sans-serif;  
    font-size: .7em;
    border: 1px solid #DDD;
}

th {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .9em;
    background: #694;
    color: #FFF;
    padding: 2px 6px;
    border-collapse: separate;
    border: 1px solid #000; 
}    

td {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    border: 1px solid #DDD;
    text-align: left;
}

table.mytable { margin: 12px;
    margin-left: 3px;
    font-family: Arial, Helvetica, sans-serif;  
    font-size: .7em;
    border: 1px solid #678b3f;
} 
table.mytable th { font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    background: #694;
    color: #FFF;
    padding: 2px 6px;
    border-collapse: separate;
    border: 1px solid #000; } 
table.mytable td { font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
   /* border: .1em solid #DDD;*/
   border:none;
    text-align: left; }  

</style>    
<script type="text/javascript">
function disp(){
  window.location='JobOrderDisplay.php';
}

function confirm(){
    var JO_No_ = document.getElementById('JO_No_').value;
    var pcode = document.getElementById('pcode').value;
    var lotcode = document.getElementById('lotcode').value;
    var qty = document.getElementById('qty').value;
    
    document.jo_confirm.action="JobOrderConfirm.php?JO_No_="+JO_No_+"&pcode="+pcode+"&lotcode="+lotcode+"&qty="+qty;
    document.jo_confirm.submit();
    alert('Job Order Confirm');
    window.location='JobOrderDisplayConfirm.php';       
}

</script>  
</head>
<body>


<?php
//----approve button-----//
$Approved = isset($_POST['priority']);
if ($Approved)
{
    $PO_No = $_POST['PO_No'];  
    $ETD = $_POST['ETD'];
    $SKUCode = $_POST['SKUCode'];
    $Description = $_POST['Description'];
    $POReq = $_POST['POReq'];   
    $priority = $_POST['priority'];
    $line = $_POST['line'];
    
   $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
   $result = mysql_query($sql, $con);
        
   if (!$result)
   {
       echo mysql_error();
       die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
       $currentSR = "JO".date("ymd")."001";
   }
   else
   {
            // Stock Number iteration....
       $currentYMD = date("ymd");
       $row = mysql_fetch_assoc($result);
  
       $currentSRYMD = substr($row['jo_number'], 2, 6);
       $currentSRNum = substr($row['jo_number'], 8, 3);
       if ($currentYMD > $currentSRYMD) 
       {  
           $currentSRNum = 1;
           $currentYMD = $currentSRYMD;
       }
           else 
           {  
           $currentSRNum++;
           }
       $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);
   }      

   
     $sql = "SELECT SKUCode, Materials, Comp, Qty 
     FROM bom  WHERE SKUCode = '$SKUCode'";
     $res = mysql_query($sql, $con);

    ($row = mysql_fetch_assoc($res));
     $Materials = $row['Materials'];
     $Qty = $row['Qty'];
     $Comp = $row['Comp'];
//-----Compute Req Qty and Save to table---//     

     $ReqQty = $Qty * $POReq;


     
$sql = "UPDATE bom SET ReqQty = '$ReqQty' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);


//------Insert to Job Order table---//
$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);
}
$sql = "INSERT INTO job_order (jo_number, jo_date, ETD, PO_No, SKUCode, Description, POQty, Priority, Status, line)
VALUES ('$currentSR', '$Date_Shelve', '$ETD', '$PO_No', '$SKUCode', '$Description', '$POReq', '$priority', '$Status', '$line')"; 
$res = mysql_query($sql, $con);
   echo "<meta http-equiv='refresh' content='0; url=JobOrder.php'>";    
}
else{
    
}


$sql = "SELECT jo_number, ETD, PO_No, SKUCode, Description, POQty, Priority, Status, line
FROM job_order ORDER BY jo_number, ETD";
$res_jo = mysql_query($sql,$con);
?>
<div style="position: fixed;width: 1012px;">
<table>

<th style="width:72px;position: fixed;"> JO No.</th>
<th style="width:72px;position: fixed;"> ETD </th>
<th style="width:195px;position: fixed;"> PO No. </th>
<th style="width:108px;position: fixed;"> SKU Code </th>
<th style="width:220px;position: fixed;"> Description </th>
<th style="width:72px;"> Qty Req </th>
<th style="width:55px;"> Lines </th>
<th style="width:80px;"> Status</th>
</table>
</div>
<div id="kanban_table" style="overflow:auto; height:200px;">
<table>
<?php
$sql = "SELECT FromMonth , ToMonth FROM so_month";
$res = mysql_query($sql, $con);

$row = mysql_fetch_assoc($res);

$FromMonth = $row['FromMonth'];
$ToMonth = $row['ToMonth'];

$sql = "SELECT ETD, PO_No, SKUCode, Description, POReq 
FROM sales_order  WHERE NOT EXISTS (SELECT PO_No FROM job_order WHERE job_order.PO_No = sales_order.PO_No) AND MONTH(ETD) BETWEEN '$FromMonth' AND '$ToMonth' ORDER BY ETD ASC LIMIT 10 ";
$res_so = mysql_query($sql, $con);
while($row = mysql_fetch_assoc($res_so)){ 
echo "<form name='joborder_form' action='' method='post'>";   
$PO_No = $row['PO_No']; 

echo "<tr>
<td><input type='text' value='' style='border:none;' size='10' readonly='readonly'></td>
<td><input type='text' name='ETD' id='ETD' value='$row[ETD]' style='border:none;' size='10' readonly='readonly'></td> 
<td><input type='text' name='PO_No' id='PO_No' value='$row[PO_No]' style='border:none;' size='30' readonly='readonly'></td>
<td><input type='text' name='SKUCode' id='SKUCode' value='$row[sKUCode]' style='border:none;' size='15' readonly='readonly'></td>
<td><input type='text' name='Description' id='Description' value='$row[Description]' style='border:none;' size='35' readonly='readonly'></td>
<td><input type='text' name='POReq' id='POReq' value='$row[POReq]' style='border:none;' size='10' readonly='readonly'></td> 
<td>
<select name='line' id='line'>
<option name='Select'>Select</option>
<option value='Line1' if($_POST[line] == 'Line1') echo 'selected='selected'';>Line 1</option>
  <option value='Line2' if($_POST[line] == 'Line2') echo 'selected='selected'';>Line 2</option>
<option value='Line3' if($_POST[line] == 'Line3') echo 'selected='selected'';>Line 3</option>
</select>
</td>
<td><input type='submit' name='priority' value='Approved' id='priority'></td>
</tr>";


echo "</form>";

}
echo "</table>";
echo "</div>"; 
?>

<div style='overflow:auto; height:200px;'>
<table class="mytable">
<form name='jo_confirm' method="post">
<?php
while($row_job = mysql_fetch_assoc($res_jo)){
    echo "<tr>
    <td><input type='text' name='JO_No_' id='JO_No_' value='$row_job[jo_number]' style='border:none;width:auto;' size='10' readonly='readonly'></td>
    <td><input type='text' name='ETD_' id='ETD_' value='$row_job[ETD]' style='border:none;width:auto;' size='10' readonly='readonly'></td> 
    <td><input type='text' name='PO_No_' id='PO_No_' value='$row_job[PO_No]' style='border:none;' size='32' readonly='readonly'></td>
    <td><input type='text' name='SKUCode_' id='SKUCode_' value='$row_job[sKUCode]' style='border:none;' size='15' readonly='readonly'></td>
    <td><input type='text' name='Description_' id='Description_' value='$row_job[Description]' style='border:none;' size='35' readonly='readonly'></td>
    <td><input type='text' name='POReq_' id='POReq_' value='$row_job[POQty]' style='border:none;width:auto;' size='10' readonly='readonly'></td> 
    <td><input type='text' name='line' id='line' value='$row_job[line]' style='border:none;width:auto;' size='7' readonly='readonly'></td> 
    <td><input type='text' name='priority_' value='$row_job[Priority]' id='priority_' style='border:none;width:auto;' size='11' readonly='readonly'></td>
    </tr>" ;
    
$sql = "SELECT pcode, lotcode, qty FROM jo_dump WHERE jo_number = '$row_job[jo_number]' ORDER BY date_entry ASC";
$res_dump = mysql_query($sql, $con);

while($row_dump = mysql_fetch_assoc($res_dump)){
    echo "<tr>";
    echo "<td> </td>";
    echo "<td> </td>";
    echo "<td><input type='text' name='lotcode' id='lotcode' value='$row_dump[lotcode]' style='border:none;width:auto;' size='10' readonly='readonly'></td>";
    echo "<td><input type='text' name='pcode' id='pcode' value='$row_dump[pcode]' style='border:none;width:auto;' size='15' readonly='readonly'></td>";
    echo "<td> </td>";
    echo "<td><input type='text' name='qty' id='qty' value='$row_dump[qty]' style='border:none;width:auto;' size='10' readonly='readonly'></td>";
}
    echo "<td> </td>";
    echo "<td><input type='button' name='confirm' value='confirm' onclick='confirm()'></td>";

    echo "</tr>";
}

echo "</table>";
echo "</form>";
echo "</div>"  ;
?>
<div id="disp_btn">
<input type="button" name="print" value="display" onclick="disp()">
</div>
</body>
</html>

I will attach the sample scenario .

Thank you

18388_.zip

newphpcoder · May 17, 2012

Hi...

I tought it was resolve yet i notice that theirs a problem when I approve with same pcode.

here is my scenario.

1. I need to click Approve Button to create JO Number and compute Req Qty.

2. get lot code that sum of output will equal to Req Qty.

I notice that when I approve another data I notice that the lotcode from the first i approve was also get or displayed.

here is my whole code:

<?php
    error_reporting(0);
  date_default_timezone_set("Asia/Singapore"); //set the time zone  
$con = mysql_connect('localhost', 'root','');

if (!$con) {
    echo 'failed';
    die();
}

mysql_select_db("mes", $con);
$Date_Shelve =date('Y-m-d H:i:s');  


?>
<html>
<head>
<title>Job Order</title> 
<link rel="stylesheet" type="text/css" href="kanban.css" />
<style type="text/css">
#SR_date{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .8em;
    margin-left: 10px;
    margin-right: 770px;
    width: auto;
    height: auto;
    float: left;
    top : 10px;
}

#disp_btn{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .8em;
    margin-left: 10px;
    top: 10px;
}


#disp{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .8em;
    margin-left: 10px;
    top: 10px;
}

table {          
    margin: 12px;
    margin-left: 3px;
    font-family: Arial, Helvetica, sans-serif;  
    font-size: .7em;
    border: 1px solid #DDD;
}

th {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .9em;
    background: #694;
    color: #FFF;
    padding: 2px 6px;
    border-collapse: separate;
    border: 1px solid #000; 
}    

td {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    border: 1px solid #DDD;
    text-align: left;
}

table.mytable { margin: 12px;
    margin-left: 3px;
    font-family: Arial, Helvetica, sans-serif;  
    font-size: .7em;
    border: 1px solid #678b3f;
} 
table.mytable th { font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    background: #694;
    color: #FFF;
    padding: 2px 6px;
    border-collapse: separate;
    border: 1px solid #000; } 
table.mytable td { font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
   /* border: .1em solid #DDD;*/
   border:none;
    text-align: left; }  

</style>    
<script type="text/javascript">
function disp(){
  window.location='JobOrderDisplay.php';
}

function confirm(){
    var JO_No_ = document.getElementById('JO_No_').value;
    var pcode = document.getElementById('pcode').value;
    var lotcode = document.getElementById('lotcode').value;
    var qty = document.getElementById('qty').value;
    
    document.jo_confirm.action="JobOrderConfirm.php?JO_No_="+JO_No_+"&pcode="+pcode+"&lotcode="+lotcode+"&qty="+qty;
    document.jo_confirm.submit();
    alert('Job Order Confirm');
    window.location='JobOrderDisplayConfirm.php';       
}

</script>  
</head>
<body>


<?php
//----approve button-----//
$Approved = isset($_POST['priority']);
if ($Approved)
{
    $PO_No = $_POST['PO_No'];  
    $ETD = $_POST['ETD'];
    $SKUCode = $_POST['SKUCode'];
    $Description = $_POST['Description'];
    $POReq = $_POST['POReq'];   
    $priority = $_POST['priority'];
    $line = $_POST['line'];
    
   $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
   $result = mysql_query($sql, $con);
        
   if (!$result)
   {
       echo mysql_error();
       die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
       $currentSR = "JO".date("ymd")."001";
   }
   else
   {
            // Stock Number iteration....
       $currentYMD = date("ymd");
       $row = mysql_fetch_assoc($result);
  
       $currentSRYMD = substr($row['jo_number'], 2, 6);
       $currentSRNum = substr($row['jo_number'], 8, 3);
       if ($currentYMD > $currentSRYMD) 
       {  
           $currentSRNum = 1;
           $currentYMD = $currentSRYMD;
       }
           else 
           {  
           $currentSRNum++;
           }
       $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);
   }      

   
     $sql = "SELECT SKUCode, Materials, Comp, Qty 
     FROM bom  WHERE SKUCode = '$SKUCode'";
     $res = mysql_query($sql, $con);

    ($row = mysql_fetch_assoc($res));
     $Materials = $row['Materials'];
     $Qty = $row['Qty'];
     $Comp = $row['Comp'];
//-----Compute Req Qty and Save to table---//     

     $ReqQty = $Qty * $POReq;


     
$sql = "UPDATE bom SET ReqQty = '$ReqQty' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);


//------Insert to Job Order table---//
$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);
}
$sql = "INSERT INTO job_order (jo_number, jo_date, ETD, PO_No, SKUCode, Description, POQty, Priority, Status, line)
VALUES ('$currentSR', '$Date_Shelve', '$ETD', '$PO_No', '$SKUCode', '$Description', '$POReq', '$priority', '$Status', '$line')"; 
$res = mysql_query($sql, $con);
   echo "<meta http-equiv='refresh' content='0; url=JobOrder.php'>";    
}
else{
    
}


$sql = "SELECT jo_number, ETD, PO_No, SKUCode, Description, POQty, Priority, Status, line
FROM job_order ORDER BY jo_number, ETD";
$res_jo = mysql_query($sql,$con);
?>
<div style="position: fixed;width: 1012px;">
<table>

<th style="width:72px;position: fixed;"> JO No.</th>
<th style="width:72px;position: fixed;"> ETD </th>
<th style="width:195px;position: fixed;"> PO No. </th>
<th style="width:108px;position: fixed;"> SKU Code </th>
<th style="width:220px;position: fixed;"> Description </th>
<th style="width:72px;"> Qty Req </th>
<th style="width:55px;"> Lines </th>
<th style="width:80px;"> Status</th>
</table>
</div>
<div id="kanban_table" style="overflow:auto; height:200px;">
<table>
<?php
$sql = "SELECT FromMonth , ToMonth FROM so_month";
$res = mysql_query($sql, $con);

$row = mysql_fetch_assoc($res);

$FromMonth = $row['FromMonth'];
$ToMonth = $row['ToMonth'];

$sql = "SELECT ETD, PO_No, SKUCode, Description, POReq 
FROM sales_order  WHERE NOT EXISTS (SELECT PO_No FROM job_order WHERE job_order.PO_No = sales_order.PO_No) AND MONTH(ETD) BETWEEN '$FromMonth' AND '$ToMonth' ORDER BY ETD ASC LIMIT 10 ";
$res_so = mysql_query($sql, $con);
while($row = mysql_fetch_assoc($res_so)){ 
echo "<form name='joborder_form' action='' method='post'>";   
$PO_No = $row['PO_No']; 

echo "<tr>
<td><input type='text' value='' style='border:none;' size='10' readonly='readonly'></td>
<td><input type='text' name='ETD' id='ETD' value='$row[ETD]' style='border:none;' size='10' readonly='readonly'></td> 
<td><input type='text' name='PO_No' id='PO_No' value='$row[PO_No]' style='border:none;' size='30' readonly='readonly'></td>
<td><input type='text' name='SKUCode' id='SKUCode' value='$row[sKUCode]' style='border:none;' size='15' readonly='readonly'></td>
<td><input type='text' name='Description' id='Description' value='$row[Description]' style='border:none;' size='35' readonly='readonly'></td>
<td><input type='text' name='POReq' id='POReq' value='$row[POReq]' style='border:none;' size='10' readonly='readonly'></td> 
<td>
<select name='line' id='line'>
<option name='Select'>Select</option>
<option value='Line1' if($_POST[line] == 'Line1') echo 'selected='selected'';>Line 1</option>
  <option value='Line2' if($_POST[line] == 'Line2') echo 'selected='selected'';>Line 2</option>
<option value='Line3' if($_POST[line] == 'Line3') echo 'selected='selected'';>Line 3</option>
</select>
</td>
<td><input type='submit' name='priority' value='Approved' id='priority'></td>
</tr>";


echo "</form>";

}
echo "</table>";
echo "</div>"; 
?>

<div style='overflow:auto; height:200px;'>
<table class="mytable">
<form name='jo_confirm' method="post">
<?php
while($row_job = mysql_fetch_assoc($res_jo)){
    echo "<tr>
    <td><input type='text' name='JO_No_' id='JO_No_' value='$row_job[jo_number]' style='border:none;width:auto;' size='10' readonly='readonly'></td>
    <td><input type='text' name='ETD_' id='ETD_' value='$row_job[ETD]' style='border:none;width:auto;' size='10' readonly='readonly'></td> 
    <td><input type='text' name='PO_No_' id='PO_No_' value='$row_job[PO_No]' style='border:none;' size='32' readonly='readonly'></td>
    <td><input type='text' name='SKUCode_' id='SKUCode_' value='$row_job[sKUCode]' style='border:none;' size='15' readonly='readonly'></td>
    <td><input type='text' name='Description_' id='Description_' value='$row_job[Description]' style='border:none;' size='35' readonly='readonly'></td>
    <td><input type='text' name='POReq_' id='POReq_' value='$row_job[POQty]' style='border:none;width:auto;' size='10' readonly='readonly'></td> 
    <td><input type='text' name='line' id='line' value='$row_job[line]' style='border:none;width:auto;' size='7' readonly='readonly'></td> 
    <td><input type='text' name='priority_' value='$row_job[Priority]' id='priority_' style='border:none;width:auto;' size='11' readonly='readonly'></td>
    </tr>" ;
    
$sql = "SELECT pcode, lotcode, qty FROM jo_dump WHERE jo_number = '$row_job[jo_number]' ORDER BY date_entry ASC";
$res_dump = mysql_query($sql, $con);

while($row_dump = mysql_fetch_assoc($res_dump)){
    echo "<tr>";
    echo "<td> </td>";
    echo "<td> </td>";
    echo "<td><input type='text' name='lotcode' id='lotcode' value='$row_dump[lotcode]' style='border:none;width:auto;' size='10' readonly='readonly'></td>";
    echo "<td><input type='text' name='pcode' id='pcode' value='$row_dump[pcode]' style='border:none;width:auto;' size='15' readonly='readonly'></td>";
    echo "<td> </td>";
    echo "<td><input type='text' name='qty' id='qty' value='$row_dump[qty]' style='border:none;width:auto;' size='10' readonly='readonly'></td>";
}
    echo "<td> </td>";
    echo "<td><input type='button' name='confirm' value='confirm' onclick='confirm()'></td>";

    echo "</tr>";
}

echo "</table>";
echo "</form>";
echo "</div>"  ;
?>
<div id="disp_btn">
<input type="button" name="print" value="display" onclick="disp()">
</div>
</body>
</html>

I will attach the sample scenario .

Thank you

Illusion · May 17, 2012

Wooff...

obviously your select query doesn't alter your table data. You need to update the records based on results returned from select query .

You also need to make sure that data is not fetched from table for other users before updating the records as there is possibility that already approved records can be displayed other users.

Don't expect us write the udpate query also....you try it first , if you couldn't make it we will help you.

newphpcoder · May 17, 2012

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.

18391_.zip

newphpcoder · May 17, 2012

Wooff...

obviously your select query doesn't alter your table data. You need to update the records based on results returned from select query .

You also need to make sure that data is not fetched from table for other users before updating the records as there is possibility that already approved records can be displayed other users.

Don't expect us write the udpate query also....you try it first , if you couldn't make it we will help you.

What do you I did not alter the data?What table or should i alter?

And also what table or data should I update?

Actuallyt the data that I get from the select query I insert it to another table.

What do you mean on this:

You also need to make sure that data is not fetched from table for other users before updating the records as there is possibility that already approved records can be displayed other users.

Thank you so much for your help... I tries it to solved using if else condition and yet I dont know how can I solve it

Thank you

newphpcoder · May 17, 2012

Hi..

I add the actual output qty in my jo_dump where I save the data from my select query.

I tried to resolve my problem:

here is my new code I add in where clause the condition that LOT_CODE = '$lotcode' to get data from the last lotcode that was save in jo_dump .

But it happened is all lotcode display on the second approve is the last lot code. I have no idea on how can does start selecting data from '$lotcode' not on the first data.\

//----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, OUTPUT_QTY,  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'];
$OUTPUT_QTY = $row['OUTPUT_QTY'];
$DATE_ENTRY = $row['DATE_ENTRY'];
$qty = $row['qty'];
$cumulative = $row['cumulative'];


$sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, output_qty, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$OUTPUT_QTY', '$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) AND LOT_CODE = '$lotcode'";
$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, output_qty, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$OUTPUT_QTY', '$qty', '$cumulative', '$DATE_ENTRY')";
$res_in = mysql_query($sql, $con);
}
}

Thank you

Sign In

Need Help in getting only data which can suffix the total Qty

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information