DB_free_result($OldResult);
$OldCost = $_POST['OldMaterialCost'] + $_POST['OldLabourCost'] + $_POST['OldOverheadCost'];
$NewCost = filter_number_format($_POST['MaterialCost']) + filter_number_format($_POST['LabourCost']) + filter_number_format($_POST['OverheadCost']);
$result = DB_query("SELECT * FROM stockmaster WHERE stockid='" . $StockID . "'");
$myrow = DB_fetch_row($result);
if (DB_num_rows($result) == 0) {
prnMsg(_('The entered item code does not exist'), 'error', _('Non-existent Item'));
} elseif ($OldCost != $NewCost) {
$Result = DB_Txn_Begin();
ItemCostUpdateGL($db, $StockID, $NewCost, $OldCost, $_POST['QOH']);
$SQL = "UPDATE stockmaster SET\tmaterialcost='" . filter_number_format($_POST['MaterialCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlabourcost='" . filter_number_format($_POST['LabourCost']) . "',\n\t\t\t\t\t\t\t\t\t\toverheadcost='" . filter_number_format($_POST['OverheadCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlastcost='" . $OldCost . "',\n\t\t\t\t\t\t\t\t\t\tlastcostupdate ='" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\t\tWHERE stockid='" . $StockID . "'";
$ErrMsg = _('The cost details for the stock item could not be updated because');
$DbgMsg = _('The SQL that failed was');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
$Result = DB_Txn_Commit();
UpdateCost($db, $StockID);
//Update any affected BOMs
}
}
$ErrMsg = _('The cost details for the stock item could not be retrieved because');
$DbgMsg = _('The SQL that failed was');
$result = DB_query("SELECT description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype,\n\t\t\t\t\t\t\tlastcostupdate,\n\t\t\t\t\t\t\tsum(quantity) as totalqoh\n\t\t\t\t\t\tFROM stockmaster INNER JOIN locstock\n\t\t\t\t\t\t\tON stockmaster.stockid=locstock.stockid\n\t\t\t\t\t\t\tINNER JOIN stockcategory\n\t\t\t\t\t\t\tON stockmaster.categoryid = stockcategory.categoryid\n\t\t\t\t\t\tWHERE stockmaster.stockid='" . $StockID . "'\n\t\t\t\t\t\tGROUP BY description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype", $ErrMsg, $DbgMsg);
$myrow = DB_fetch_array($result);
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">
<div>
<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
<table cellpadding="2" class="selection">
<tr>
<th colspan="2">' . _('Item Code') . ':<input type="text" name="StockID" value="' . $StockID . '" maxlength="20" /><input type="submit" name="Show" value="' . _('Show Cost Details') . '" /></th>
</tr>
<tr>
include 'includes/header.inc';
include 'includes/SQL_CommonFunctions.inc';
if (isset($_GET['Run'])) {
$Run = $_GET['Run'];
} elseif (isset($_POST['Run'])) {
$Run = $_POST['Run'];
}
if (isset($Run)) {
//start bom processing
// Get all bottom level components
$sql = "SELECT DISTINCT b1.component\n\t\t\tFROM bom as b1\n\t\t\tleft join bom as b2 on b2.parent=b1.component\n\t\t\tWHERE b2.parent is null;";
$ErrMsg = _('An error occurred selecting all bottom level components');
$DbgMsg = _('The SQL that was used to select bottom level components and failed in the process was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
while ($item = DB_fetch_array($result)) {
$inputerror = UpdateCost($db, $item['component']);
if ($inputerror == 0) {
prnMsg(_('Component') . ' ' . $item['component'] . ' ' . _('has been processed'), 'success');
} else {
break;
}
}
if ($inputerror == 1) {
//exited loop with errors so rollback
prnMsg(_('Failed on item') . ' ' . $item['component'] . ' ' . _('Cost update has been rolled back'), 'error');
DB_Txn_Rollback($db);
} else {
//all good so commit data transaction
DB_Txn_Commit($db);
prnMsg(_('All cost updates committed to the database.'), 'success');
}
$result = DB_query($sql, $db);
} else {
$sql = "SELECT lastcurcostdate, actualcost, lastcost, materialcost, labourcost, overheadcost, lowestlevel\n FROM stockmaster WHERE stockid='" . $stkid . "';";
$result = DB_query($sql, $db);
$row = DB_fetch_row($result);
$sql = "update stockmaster set\n lastcurcostdate = '" . $row[0] . "',\n actualcost = " . $row[1] . ",\n lastcost = " . $row[2] . ",\n materialcost = " . $row[3] . ",\n labourcost = " . $row[4] . ",\n overheadcost = " . $row[5] . ",\n lowestlevel = " . $row[6] . "\n where stockid='" . $newstkid . "';";
$result = DB_query($sql, $db);
}
$sql = "insert into bom\n\t\t\t\tselect '" . $newstkid . "' as parent,\n\t\t\t\t\t\tcomponent,\n\t\t\t\t\t\tworkcentreadded,\n\t\t\t\t\t\tloccode,\n\t\t\t\t\t\teffectiveafter,\n\t\t\t\t\t\teffectiveto,\n\t\t\t\t\t\tquantity,\n\t\t\t\t\t\tautoissue\n\t\t\t\tfrom bom\n\t\t\t\twhere parent='" . $stkid . "';";
$result = DB_query($sql, $db);
if ($type == 'N') {
$sql = "insert into locstock\n\t select loccode, '" . $newstkid . "' as stockid,0 as quantity,\n\t reorderlevel\n\t from locstock\n\t where stockid='" . $stkid . "';";
$result = DB_query($sql, $db);
}
$result = DB_query('commit', $db);
UpdateCost($db, $newstkid);
header('Location: BOMs.php?Select=' . $newstkid);
} else {
$title = _('UTILITY PAGE To Copy a BOM');
include 'includes/header.inc';
echo "<form method=\"post\" action=\"Z_CopyBOM.php\">";
$sql = "SELECT stockid, description FROM stockmaster WHERE stockid IN (SELECT DISTINCT parent FROM bom) AND mbflag IN ('M', 'A', 'K');";
$result = DB_query($sql, $db);
echo "<p>" . _("From Stock ID");
echo ": <select name=\"stkid\">";
while ($row = DB_fetch_row($result)) {
echo "<option value=\"{$row['0']}\">" . $row[0] . " -- " . $row[1] . "</option>";
}
echo "</select><br/><input type=\"radio\" name=\"type\" value=\"N\" checked=\"\"/>" . _(" To New Stock ID");
echo ": <input type=\"text\" maxlength=\"20\" name=\"tostkid\"/>";
$sql = "SELECT stockid, description FROM stockmaster WHERE stockid NOT IN (SELECT DISTINCT parent FROM bom) AND mbflag IN ('M', 'A', 'K');";
//end of if its not a recursive BOM
}
//end of if no input errors
if ($msg != '') {
prnMsg($msg, 'success');
}
} elseif (isset($_GET['delete']) and isset($SelectedComponent) and isset($SelectedParent)) {
//the link to delete a selected record was clicked instead of the Submit button
$sql = "DELETE FROM bom\n\t\t\t\tWHERE parent='" . $SelectedParent . "'\n\t\t\t\tAND component='" . $SelectedComponent . "'\n\t\t\t\tAND loccode='" . $Location . "'\n\t\t\t\tAND workcentreadded='" . $WorkCentre . "'";
$ErrMsg = _('Could not delete this BOM components because');
$DbgMsg = _('The SQL used to delete the BOM was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
$ComponentSQL = "SELECT component\n\t\t\t\t\t\t\tFROM bom\n\t\t\t\t\t\t\tWHERE parent='" . $SelectedParent . "'";
$ComponentResult = DB_query($ComponentSQL, $db);
$ComponentArray = DB_fetch_row($ComponentResult);
UpdateCost($db, $ComponentArray[0]);
prnMsg(_('The component part') . ' - ' . $SelectedComponent . ' - ' . _('has been deleted from this BOM'), 'success');
// Now reset to enable New Component Details to display after delete
unset($_GET['SelectedComponent']);
} elseif (isset($SelectedParent) and !isset($SelectedComponent) and !isset($_POST['submit'])) {
/* It could still be the second time the page has been run and a record has been selected for modification - SelectedParent will exist because it was sent with the new call. if its the first time the page has been displayed with no parameters then none of the above are true and the list of components will be displayed with links to delete or edit each. These will call the same page again and allow update/input or deletion of the records*/
//DisplayBOMItems($SelectedParent, $db);
}
//BOM editing/insertion ifs
if (isset($_GET['ReSelect'])) {
$SelectedParent = $_GET['ReSelect'];
}
//DisplayBOMItems($SelectedParent, $db);
$sql = "SELECT stockmaster.description,\n\t\t\t\t\tstockmaster.mbflag\n\t\t\tFROM stockmaster\n\t\t\tWHERE stockmaster.stockid='" . $SelectedParent . "'";
$ErrMsg = _('Could not retrieve the description of the parent part because');
$DbgMsg = _('The SQL used to retrieve description of the parent part was');
$result = DB_query($sql, $db);
} else {
$sql = "SELECT lastcostupdate,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tlowestlevel\n\t\t\t\t\t\tFROM stockmaster\n\t\t\t\t\t\tWHERE stockid='" . $StockID . "';";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
$sql = "UPDATE stockmaster set\n\t\t\t\t\tlastcostupdate = " . $myrow[0] . ",\n\t\t\t\t\tactualcost = " . $myrow[1] . ",\n\t\t\t\t\tlastcost = " . $myrow[2] . ",\n\t\t\t\t\tmaterialcost = " . $myrow[3] . ",\n\t\t\t\t\tlabourcost = " . $myrow[4] . ",\n\t\t\t\t\toverheadcost = " . $myrow[5] . ",\n\t\t\t\t\tlowestlevel = " . $myrow[6] . "\n\t\t\t\t\tWHERE stockid='" . $NewStockID . "';";
$result = DB_query($sql, $db);
}
$sql = "INSERT INTO bom\n\t\t\t\t\tSELECT '" . $NewStockID . "' AS parent,\n\t\t\t\t\t\t\tcomponent,\n\t\t\t\t\t\t\tworkcentreadded,\n\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\teffectiveafter,\n\t\t\t\t\t\t\teffectiveto,\n\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\tautoissue\n\t\t\t\t\tFROM bom\n\t\t\t\t\tWHERE parent='" . $StockID . "';";
$result = DB_query($sql, $db);
if ($NewOrExisting == 'N') {
$sql = "INSERT INTO locstock\n\t\t SELECT loccode,\n\t\t\t\t\t'" . $NewStockID . "' AS stockid,\n\t\t\t\t\t0 AS quantity,\n\t\t\t\t\treorderlevel\n\t\t\t\tFROM locstock\n\t\t\t\tWHERE stockid='" . $StockID . "'";
$result = DB_query($sql, $db);
}
$result = DB_Txn_Commit($db);
UpdateCost($db, $NewStockID);
header('Location: BOMs.php?Select=' . $NewStockID);
ob_end_flush();
}
//end if there is no input error
} else {
echo '<p class="page_title_text"><img src="' . $rootpath . '/css/' . $theme . '/images/inventory.png" title="' . _('Contract') . '" alt="" />' . ' ' . $title . '</p>';
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
$sql = "SELECT stockid,\n\t\t\t\t\tdescription\n\t\t\t\tFROM stockmaster\n\t\t\t\tWHERE stockid IN (SELECT DISTINCT parent FROM bom)\n\t\t\t\tAND mbflag IN ('M', 'A', 'K', 'G');";
$result = DB_query($sql, $db);
echo '<table class="selection">
<tr>
<td>' . _('From Stock ID') . '</td>';
echo '<td><select name="StockID">';
MoveSubCamp($EditId, $MoveCampTo);
}
if (ValidId($EditId) && isset($EditArr) && is_array($EditArr)) {
UpdateSubCampaign($EditId, $EditArr);
}
if (isset($EditId) && $EditId == "new" && isset($EditArr) && ValidId($GrpId)) {
CreateNewSubCampaign($GrpId, $EditArr);
}
if (ValidId($DeleteId)) {
DeleteSubCampaign($CpId, $DeleteId);
}
if (isset($GenCode) && $GenCode == 1) {
GenLink();
}
if (ValidId($EditCostId) && ValidArr($EditCost)) {
UpdateCost($EditCostId, $EditCost);
}
if (ValidVar($EditCostId) == "new" && ValidArr($EditCost)) {
CreateCost($EditCost);
}
if (ValidVar($UpdateSum) == 1) {
UpdateSumThis($SumThis);
}
if (ValidId($DelCostId)) {
DeleteCost($DelCostId);
}
}
/////////////////////////////////////////////
///////// display section here
if (!ValidArr($EditCost)) {
$EditCost = array();