I don’t know how can I create test site so you can seev it.
here is my joborder.php
<?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: .9em;
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;
}
table {
margin: 12px;
margin-left: 3px;
font-family: Arial, Helvetica, sans-serif;
font-size: .9em;
border: 1px solid #DDD;
}
th {
font-family: Arial, Helvetica, sans-serif;
font-size: .7em;
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;
}
</style>
<script type="text/javascript">
function disp(){
window.location='JobOrderDisplay.php';
}
</script>
</head>
<body>
<div id="ddcolortabs">
<ul>
<li id="current"> <a href="SalesOrder.php" title="Sales Order"><span>Order Management</span></a></li>
<li> <a href="ParameterSettings.php" title="Parameter Settings"><span>Parameter Settings</span></a></li>
<li style="margin-left: 1px"><a href="kanban_report.php" title="WIP Report"><span>WIP Report</span></a></li>
<li><a href="fsn.php" title="Finished Stock Note"><span>WMS FG</span></a></li>
<li><a href="ReceivingMaterials.php" title="WMS RM"><span>WMS RM</span></a></li>
<li><a href="calendar_days.php" title="Calendar Days"><span>Calendar Days</span></a></li>
</ul>
</div>
<div id="ddcolortabs1">
<ul>
<li><a href="SalesOrder.php" title="Sales Order"><span>Sales Order</span></a></li>
<li id="current"><a href="JobOrder.php" title="Job Order"><span>Job Order</span></a></li>
</ul>
</div>
<div id="SR_date">
<input type="hidden" name="Date_Shelve" id="Date_Shelve" value="<?php echo $Date_Shelve; ?>" size="16" readonly="readonly" style="border: none;">
</div>
<?php
//----approve button-----//
function convertToDozen ($total, $cmpdType) {
switch ($cmpdType) {
case "P28" :
$convTotal = (($total * 1000) / 22.00) / 12;
break;
case "P30" :
$convTotal = (($total * 1000) / 25.10) / 12;
break;
case "P32" :
$convTotal = (($total * 1000) / 22.50) / 12;
break;
case "P33" :
$convTotal = (($total * 1000) / 25.00) / 12;
break;
case "P35" :
$convTotal = (($total * 1000) / 25.25) / 12;
break;
case "P35M" :
$convTotal = (($total * 1000) / 22.60) / 12;
break;
case "P35W" :
$convTotal = (($total * 1000) / 22.50) / 12;
break;
case "P38" :
$convTotal = (($total * 1000) / 26.70) / 12;
break;
case "P41" :
$convTotal = (($total * 1000) / 24.80) / 12;
break;
case "P42" :
$convTotal = (($total * 1000) / 18.90) / 12;
break;
case "P43" :
$convTotal = (($total * 1000) / 25.00) / 12;
break;
case "P46" :
$convTotal = (($total * 1000) / 27.00) / 12;
break;
case "P47" :
$convTotal = (($total * 1000) / 27.60) / 12;
break;
}
return round($convTotal, 2);
}
$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'];
//echo $SKUCode;
// exit;
//echo "<meta http-equiv='refresh' content='1; url=JobORderApproved.php'>";
/*$sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
$result = mysql_query($sql, $con);
if (!$result) {
echo 'failed';
die();
}
$total = mysql_num_rows($result);
if ($total <= 0) {
$currentSRNum = 1;
$currentYear = (int)(date('y'));
$currentMonth = (int)(date('m'));
$currentDay = (int)(date('d'));
$currentSRYMD = substr($row['jo_number'], 0, 6);
$currentYMD = date("ymd");
if ($currentYMD > $currentSRYMD)
{
$currentSRNum = 1;
}
else
{
$currentSRNum += 1;
}
}
else {
//------------------------------------------------------------------------------------------------------------------
// Stock Number iteration....
$row = mysql_fetch_assoc($result);
//$row['jo_number'] = 'JO' . $row['jo_number'];
// $row['jo_number'] = 'JO' . $row['jo_number'];
// $currentSRNum = (int)(substr($row['jo_number'],2,5));
// $row['jo_number'] = 'JO' . $row['jo_number'];
$currentSRNum = (int)(substr($row['jo_number'],0,3));
//$currentSRNum = int(substr( 'JO' . $row['jo_number'],0,3));
$currentSRYear = (int)(substr($row['jo_number'],2,2));
$currentSRMonth = (int)(substr($row['jo_number'],0,2));
$currentSRNum = (int)(substr($row['jo_number'],6,4));
// $currentSRNum = (substr($row['jo_number'],6,4));
$currentYear = (int)(date('y'));
$currentMonth = (int)(date('m'));
$currentDay = (int)(date('d'));
$currentSRYMD = substr($row['jo_number'], 0, 6);
$currentYMD = date("ymd");
if ($currentYMD > $currentSRYMD)
{
$currentSRNum = 1;
}
else
{
$currentSRNum += 1;
}
}
//------------------------------------------------------------------------------------------------------------------
$yearMonth = date('ymd');
$currentSR = $currentYMD . sprintf("%03d", $currentSRNum);*/
//$currentSR = $row['jo_number'] . sprintf("%03d", $currentSRNum);
$sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC";
$result = mysql_query($sql, $con);
if (!$result)
{
echo mysql_error();
die();
}
$total = mysql_num_rows($result);
if ($total <= 0)
{
$currentSR = "JO".date("ymd")."001";
echo "$currentSR<br>";
}
else
{
// Stock Number iteration....
$currentYMD = date("ymd");
while($row = mysql_fetch_assoc($result))
{
$currentSRYMD = substr($row['jo_number'], 0, 6);
if ($currentYMD > $currentSRYMD)
{
$currentSRNum = 1;
$currentYMD = $currentSRYMD;
}
else
{
$currentSRNum++;
}
$currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);
echo "$currentSR<br>";
}
}
$sql = "SELECT SKUCode, Materials, Comp, Qty
FROM bom WHERE SKUCode = '$SKUCode'";
$res = mysql_query($sql, $con);
//while ($row = mysql_fetch_assoc($res)){
($row = mysql_fetch_assoc($res));
$Materials = $row['Materials'];
$Qty = $row['Qty'];
$Comp = $row['Comp'];
$ReqQty = $Qty * $POReq;
$sql = "UPDATE bom SET ReqQty = '$ReqQty' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);
// }
//----------kanban----//
$sql = "DELETE FROM MES_REPORT_MAIN";
mysql_query($sql, $con);
$sql = "INSERT INTO MES_REPORT_MAIN
(REFNUM,
LOT_CODE,
WEIGHING_OUTPUT,
COMPOUNDING_OUTPUT,
EXTRUSION_OUTPUT,
FORMING_OUTPUT,
DIPPING_OUTPUT,
MOULDING_OUTPUT,
DATE_ENTRY_WEIGHING,
DATE_ENTRY_COMPOUNDING,
DATE_ENTRY_EXTRUSION,
DATE_ENTRY_FORMING,
DATE_ENTRY_DIPPING,
DATE_ENTRY_MOULDING)
SELECT
REFNUM,
LOT_CODE,
WEIGHT,
NULL,
NULL,
NULL,
NULL,
NULL,
DATE_ENTRY,
NULL,
NULL,
NULL,
NULL,
NULL
FROM WEIGHING";
mysql_query($sql,$con);
$sql = "INSERT INTO MES_REPORT_MAIN
(REFNUM,
LOT_CODE,
WEIGHING_OUTPUT,
COMPOUNDING_OUTPUT,
EXTRUSION_OUTPUT,
FORMING_OUTPUT,
DIPPING_OUTPUT,
MOULDING_OUTPUT,
DATE_ENTRY_WEIGHING,
DATE_ENTRY_COMPOUNDING,
DATE_ENTRY_EXTRUSION,
DATE_ENTRY_FORMING,
DATE_ENTRY_DIPPING,
DATE_ENTRY_MOULDING)
SELECT
REFNUM,
LOT_CODE,
NULL,
WEIGHT,
NULL,
NULL,
NULL,
NULL,
NULL,
DATE_ENTRY,
NULL,
NULL,
NULL,
NULL
FROM COMPOUNDING";
mysql_query($sql,$con);
$sql = "INSERT INTO MES_REPORT_MAIN
(REFNUM,
LOT_CODE,
WEIGHING_OUTPUT,
COMPOUNDING_OUTPUT,
EXTRUSION_OUTPUT,
FORMING_OUTPUT,
DIPPING_OUTPUT,
MOULDING_OUTPUT,
DATE_ENTRY_WEIGHING,
DATE_ENTRY_COMPOUNDING,
DATE_ENTRY_EXTRUSION,
DATE_ENTRY_FORMING,
DATE_ENTRY_DIPPING,
DATE_ENTRY_MOULDING)
SELECT
REFNUM,
LOT_CODE,
NULL,
NULL,
WEIGHT,
NULL,
NULL,
NULL,
NULL,
NULL,
DATE_ENTRY,
NULL,
NULL,
NULL
FROM EXTRUSION";
mysql_query($sql,$con);
$sql = "INSERT INTO MES_REPORT_MAIN
(REFNUM,
LOT_CODE,
WEIGHING_OUTPUT,
COMPOUNDING_OUTPUT,
EXTRUSION_OUTPUT,
FORMING_OUTPUT,
DIPPING_OUTPUT,
MOULDING_OUTPUT,
DATE_ENTRY_WEIGHING,
DATE_ENTRY_COMPOUNDING,
DATE_ENTRY_EXTRUSION,
DATE_ENTRY_FORMING,
DATE_ENTRY_DIPPING,
DATE_ENTRY_MOULDING)
SELECT
REFNUM,
LOT_CODE,
NULL,
NULL,
NULL,
OUTPUT_QTY,
NULL,
NULL,
NULL,
NULL,
NULL,
DATE_ENTRY,
NULL,
NULL,
FROM FORMING";
mysql_query($sql,$con);
$sql = "INSERT INTO MES_REPORT_MAIN
(REFNUM,
LOT_CODE,
WEIGHING_OUTPUT,
COMPOUNDING_OUTPUT,
EXTRUSION_OUTPUT,
FORMING_OUTPUT,
DIPPING_OUTPUT,
MOULDING_OUTPUT,
DATE_ENTRY_WEIGHING,
DATE_ENTRY_COMPOUNDING,
DATE_ENTRY_EXTRUSION,
DATE_ENTRY_FORMING,
DATE_ENTRY_DIPPING,
DATE_ENTRY_MOULDING)
SELECT
REFNUM,
LOT_CODE,
NULL,
NULL,
NULL,
NULL,
OUTPUT_QTY,
NULL,
NULL,
NULL,
NULL,
NULL,
DATE_ENTRY,
NULL
FROM DIPPING";
mysql_query($sql,$con);
$sql = "INSERT INTO MES_REPORT_MAIN
(REFNUM,
LOT_CODE,
WEIGHING_OUTPUT,
COMPOUNDING_OUTPUT,
EXTRUSION_OUTPUT,
FORMING_OUTPUT,
DIPPING_OUTPUT,
MOULDING_OUTPUT,
DATE_ENTRY_WEIGHING,
DATE_ENTRY_COMPOUNDING,
DATE_ENTRY_EXTRUSION,
DATE_ENTRY_FORMING,
DATE_ENTRY_DIPPING,
DATE_ENTRY_MOULDING
)
SELECT
REFNUM,
LOT_CODE,
NULL,
NULL,
NULL,
NULL,
NULL,
OUTPUT_QTY,
NULL,
NULL,
NULL,
NULL,
NULL,
DATE_ENTRY,
FROM MOULDING";
mysql_query($sql,$con);
$sql = "DELETE FROM KANBAN_REPORT_OPEN";
mysql_query($sql,$con);
$sql = "INSERT INTO KANBAN_REPORT_OPEN";
$sql = $sql . " " . "SELECT REFNUM, LOT_CODE,
SUM(WEIGHING_OUTPUT) AS WEIGHING,
SUM(COMPOUNDING_OUTPUT) AS COMPOUNDING,
SUM(EXTRUSION_OUTPUT) AS EXTRUSION,
SUM(FORMING_OUTPUT) AS FORMING,
SUM(DIPPING_OUTPUT)AS DIPPING,
SUM(MOULDING_OUTPUT) AS MOULDING,
IF(NOT ISNULL(SUM(COMPOUNDING_OUTPUT)), 0, SUM(WEIGHING_OUTPUT)) AS WIP_1,
IF(NOT ISNULL(SUM(EXTRUSION_OUTPUT)), 0, SUM(COMPOUNDING_OUTPUT)) AS WIP_2,
IF(NOT ISNULL(SUM(FORMING_OUTPUT)), 0, SUM(EXTRUSION_OUTPUT)) AS WIP_3,
IF(NOT ISNULL(SUM(DIPPING_OUTPUT)), 0, SUM(FORMING_OUTPUT)) AS WIP_4,
IF(NOT ISNULL(SUM(MOULDING_OUTPUT)), 0, SUM(DIPPING_OUTPUT)) AS WIP_5,
SUM(MOULDING_OUTPUT) AS WIP_6
FROM MES_REPORT_MAIN GROUP BY REFNUM HAVING ISNULL(SUM(MOULDING_OUTPUT))";
$result = mysql_query($sql,$con);
$sql = "SELECT SUM(WIP_1) AS WEIGHING_TOTAL, ";
$sql .= "SUM(WIP_2) AS COMPOUNDING_TOTAL, ";
$sql .= "SUM(WIP_3) AS EXTRUSION_TOTAL, ";
$sql .= "SUM(WIP_4) AS FORMING_TOTAL, ";
$sql .= "SUM(WIP_5) AS DIPPING_TOTAL, ";
$sql .= "SUM(WIP_6) AS MOULDING_TOTAL ";
$sql .= "FROM KANBAN_REPORT_OPEN WHERE SUBSTRING(LOT_CODE, 9) = '$Comp'";
$result = mysql_query($sql, $con);
$weighingTotal = mysql_result($result, 0, "WEIGHING_TOTAL");
$weighingTotal = convertToDozen($weighingTotal, $Comp);
$weighingAccum = $weighingAccum + $weighingTotal;
$compoundingTotal = mysql_result($result, 0, "COMPOUNDING_TOTAL");
$compoundingTotal = convertToDozen($compoundingTotal, $Comp);
$compoundingAccum = $compoundingAccum + $compoundingTotal;
$extrusionTotal = mysql_result($result, 0, "EXTRUSION_TOTAL");
$extrusionTotal = convertToDozen($extrusionTotal, $Comp);
$extrusionAccum = $extrusionAccum + $extrusionTotal;
$formingTotal = mysql_result($result, 0, "FORMING_TOTAL");
$formingTotal = convertToDozen($formingTotal, $Comp);
$formingAccum = $formingAccum = $formingTotal;
$dippingTotal = mysql_result($result, 0, "DIPPING_TOTAL");
$dippingTotal = convertToDozen($dippingTotal, $Comp);
$dippingAccum = $dippingAccum + $dippingTotal;
$totalY = $weighingTotal + $compoundingTotal + $extrusionTotal + $formingTotal + $dippingTotal;
//------Total Kanban Doz - ReqQty----//
/*$sql = "SELECT SKUCode, Materials, Comp, Qty, ReqQty
FROM bom WHERE SKUCode = '$SKUCode'";
$res = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($res)){
$Materials = $row['Materials'];
$Comp = $row['Comp'];
$ReqQty = $row['ReqQty'];*/
if($totalY >= $ReqQty){
$ReqQty_Update = "0";
$ReqQty_Issued = $ReqQty;
//$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials' AND Comp = '$Comp' AND ReqQty = '$ReqQty'";
$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);
}
elseif($totalY < $ReqQty){
$ReqQty_Update = $ReqQty - $totalY;
$ReqQty_Issued = $totalY - $ReqQty;
//$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials' AND Comp = '$Comp' AND ReqQty = '$ReqQty'";
$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);
}
// }
if($ReqQty_Update == '0.00'){
$Status = 'Close';
}
else{
$Status = 'Open';
}
$sql = "INSERT INTO job_order (jo_number, jo_date, ETD, PO_No, SKUCode, Description, POQty, Priority, Status)
VALUES ('$currentSR', '$Date_Shelve', '$ETD', '$PO_No', '$SKUCode', '$Description', '$POReq', '$priority', '$Status')";
$res = mysql_query($sql, $con);
$sql = "SELECT compound, req_total FROM jo_total";
$result = mysql_query($sql, $con);
$num = mysql_num_rows($result);
if ($num >= 1){
$sql = "UPDATE jo_total SET compound = (SELECT DISTINCT Comp FROM bom WHERE Comp = compound), req_total = (SELECT SUM(ReqQty_Issued) AS req_total FROM bom WHERE Comp = compound GROUP BY Comp)";
$res_up = mysql_query($sql, $con);
}
else{
$sql = "INSERT INTO jo_total (compound, req_total) SELECT Comp, SUM(ReqQty_Issued) AS ReqQty_Issued FROM bom
GROUP BY Comp";
$res_in = 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
FROM job_order ORDER BY jo_number, ETD";
$res_jo = mysql_query($sql,$con);
?>
<table>
<th style="width: 72px;"> JO No.</th>
<th style="width: 72px;"> ETD </th>
<th style="width: 192px;"> PO No. </th>
<th style="width: 101px;"> SKU Code </th>
<th style="width: 222px;"> Description </th>
<th style="width: 72px;"> PO Req </th>
<th style="width: 105px;"> Status</th>
</table>
<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);
//$rowCounter = 1;
while($row = mysql_fetch_assoc($res_so)){
echo "<form name='joborder_form' action='' method='post'>";
$PO_No = $row['PO_No'];
//echo "<tbody>";
echo "<tr>
<td><input type='text' value='' style='border:none;' size='10'></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><input type='submit' name='priority' value='Approved' id='priority'></td>
</tr>";
//$rowCounter = $rowCounter + 1;
echo "</form>";
}
echo "</table>";
echo "</div>";
?>
<div style='overflow:auto; height:200px;'>
<table>
<?php
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='30' 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='priority_' value='$row_job[Priority] | $row_job[Status]' id='priority_' style='border:none;width:auto;' size='15' readonly='readonly'></td>
</tr>" ;
}
echo "</table>";
echo "</div>" ;
echo "</div>";
?>
<div id="disp_btn">
<input type="button" name="print" value="display" onclick="disp()">
</div>
</body>
</html>