Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2010
    Posts
    136

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

    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:

    Code:
    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
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    haven't got a clue what you mean
    its not clear form the description
    its not clear from the example
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by healdem View Post
    haven't got a clue what you mean
    its not clear form the description
    its not clear from the example
    I need to get only the lot code where can I minus in my Req Qty.

    Thank you

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So in effect what you are asking is how to extract the requird quantity from whatever batches that exist

    I'd grab the data that exiusts for that product code in your front end the PHP stuff, work out which batches are now consumed then update those records (either delete OR update depednign on whether the batch is consumed fully or not, or set an amount used as appropriate)

    but then again I wouldn't be using the horrible method you have of modelling the lot code. yo'd be far better having two separate values the product code and the sequence byt declaring a composite primary key.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  6. #6
    Join Date
    Dec 2010
    Posts
    136
    Hi..

    I have updated query:

    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) ;
    and I attach sample output where i got an issue.

    Also I attach my sample documents for better understanding of my issue

    Thank you
    Attached Thumbnails Attached Thumbnails query data.JPG  
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    I resolved it using this code:

    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

  9. #9
    Join Date
    Dec 2010
    Posts
    136
    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 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" />
    </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'], 26);
           
    $currentSRNum substr($row['jo_number'], 83);
           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>&nbsp;</td>";
        echo 
    "<td>&nbsp;</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>&nbsp;</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>&nbsp;</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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •