} else {
$_POST['OldLabourCost'] = 0;
$_POST['OldOverheadCost'] = 0;
$_POST['LabourCost'] = 0;
$_POST['OverheadCost'] = 0;
}
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>
//start database transaction
DB_Txn_Begin();
//loop through file rows
$LineNumber = 1;
while (($myrow = fgetcsv($FileHandle, 10000, ',')) !== FALSE) {
$StockID = mb_strtoupper($myrow[0]);
$NewCost = (double) $myrow[1] + (double) $myrow[2] + (double) $myrow[3];
$sql = "SELECT mbflag,\n\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\tsum(quantity) as totalqoh\n\t\t\t\tFROM stockmaster INNER JOIN locstock\n\t\t\t\tON stockmaster.stockid=locstock.stockid\n\t\t\t\tWHERE stockmaster.stockid='" . $StockID . "'\n\t\t\t\tGROUP BY materialcost,\n\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\toverheadcost";
$ErrMsg = _('The selected item code does not exist');
$OldResult = DB_query($sql, $ErrMsg);
$OldRow = DB_fetch_array($OldResult);
$QOH = $OldRow['totalqoh'];
$OldCost = $OldRow['materialcost'] + $OldRow['labourcost'] + $OldRow['overheadcost'];
//dont update costs for assembly or kit-sets or ghost items!!
if ($OldCost != $NewCost and $OldRow['mbflag'] != 'K' and $OldRow['mbflag'] != 'A' and $OldRow['mbflag'] != 'G') {
ItemCostUpdateGL($db, $StockID, $NewCost, $OldCost, $QOH);
$SQL = "UPDATE stockmaster SET\tmaterialcost='" . (double) $myrow[1] . "',\n\t\t\t\t\t\t\t\t\t\t\tlabourcost='" . (double) $myrow[2] . "',\n\t\t\t\t\t\t\t\t\t\t\toverheadcost='" . (double) $myrow[3] . "',\n\t\t\t\t\t\t\t\t\t\t\tlastcost='" . $OldCost . "',\n\t\t\t\t\t\t\t\t\t\t\tlastcostupdate ='" . Date('Y-m-d') . "'\n\t\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);
UpdateCost($db, $StockID);
//Update any affected BOMs
}
$LineNumber++;
}
DB_Txn_Commit();
prnMsg(_('Batch Update of costs') . ' ' . $FileName . ' ' . _('has been completed. All transactions committed to the database.'), 'success');
fclose($FileHandle);
} else {
//show file upload form
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" enctype="multipart/form-data">';
