Jump to content

newphpcoder

Members
  • Posts

    254
  • Joined

  • Last visited

Everything posted by newphpcoder

  1. I thought dipping table get updated with new quantities ... to zero if completely utilized or to a new number if it partially used. If you are not updating the table.. it is very tricky to correlate with table where you inserting the data and display the data for next JO. try this.. I don't have access to mysql server now so I didn't test the code . 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,date_entry from jo_dump where output_qty > qty and SUBSTR(LOT_CODE, 9,4) = '$Comp' order by date_entry desc limit 1) UNION (select LOT_CODE,output_qty as op_qty ,date_entry from dipping where date_entry >(select ifnull(date_entry, '0000-00-00') from jo_dump where output_qty > qty and SUBSTR(LOT_CODE, 9,4) = '$Comp' order by date_entry desc limit 1))) d WHERE (@t < @rqty); Looking at my query I can tell you that...tables design is wrong. I tried this no error but no output display. Thank you
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. Hi.. I have while loop inside while loop and I need to add confirm button on the last loop data. here is my 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> </head> <body> <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 style='overflow:auto; height:200px;'> <table class="mytable"> <?php $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); while($row_job = mysql_fetch_assoc($res_jo)){ // echo "<tbody>"; 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' value='$row_dump[lotcode]' style='border:none;width:auto;' size='10' readonly='readonly'></td>"; echo "<td><input type='text' name='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' value='$row_dump[qty]' style='border:none;width:auto;' size='10' readonly='readonly'></td>"; echo "</tr>"; } } echo "</table>"; echo "</div>"; ?> </body> </html> and I attach the image of output of this code. Thank you
  8. 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
  9. I attach the sample logic documents for better understanding of my problem Thank you 18374_.zip
  10. The attach files is the data list I need to get, and I get it using the mysql query but I got an error when I apply it in my php code Thank you
  11. I'm trying to do is to get the value from dipping, which I get it using mysql query but when I input it in php it did not work Thank you...
  12. 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
  13. Hi.. I create mysql syntax for query testing before i input to my php code here is my mysql code: set @t = 0; set @rqty=31968; 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 attach the sample output of the above query. Now that query test is work i will input that code to my php codes. $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']; //P28 //-----Compute Req Qty and Save to table---// $ReqQty = $Qty * $POReq; // 31968 $sql = "UPDATE bom SET ReqQty = '$ReqQty' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'"; $resReqQty = mysql_query($sql, $con); $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)"; when I echo the query: I got this: SELECT LOT_CODE as code, DATE_ENTRY, CASE WHEN 0+OUTPUT_QTY > 31968 THEN 31968 -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 < 31968) then I run it to the sql and 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) Any help is highly appreciated Thank you so much
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.