} 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">';