/* Calculates the material cost of a bill of materials, given parent code*/
function BomMaterialCost($Parent, $db) {
$SQL = "SELECT labourcost+materialcost+overheadcost FROM stockmaster WHERE stockid='" . $Parent . "'";
$result1 = DB_query($SQL);
$MyRow1 = DB_fetch_row($result1);
$OldCost = $MyRow1[0];
$SQL = "SELECT sum(quantity) AS qoh from locstock where stockid='" . $Parent . "'";
$result1 = DB_query($SQL);
$MyRow1 = DB_fetch_row($result1);
$QOH = $MyRow1[0];
$SQL = "SELECT Sum(stockmaster.labourcost*bom.quantity) AS SumOfLaborCost,
Sum(stockmaster.materialcost*bom.quantity) AS SumOfMaterialCost,
Sum(stockmaster.overheadcost*bom.quantity) AS SumOfOverheadCost
FROM bom LEFT JOIN stockmaster
ON bom.component = stockmaster.stockid
WHERE bom.parent='". $Parent . "'
AND bom.effectiveafter <= '" . date('Y-m-d') . "'
AND bom.effectiveto > '" . date('Y-m-d') . "'";
$result = DB_query($SQL);
$MyRow = DB_fetch_row($result);
$LabourCost = $MyRow[0];
$MaterialCost = $MyRow[1];
$OverheadCost = $MyRow[2];
$CombinedCost=$LabourCost+$MaterialCost+$OverheadCost;
if (abs($QOH*($CombinedCost-$OldCost))>0) {
ItemCostUpdateGL($Parent, $CombinedCost, $OldCost, $QOH);
}
return array($OldCost, $LabourCost, $MaterialCost, $OverheadCost);
}
/*Iterates through the levels of the bom, recalculating each bom it meets*/
function UpdateCost($db, $Item) {
//update the items cost itself first;
BomMaterialCost(strtoupper($Item), $db);
$SQL = "SELECT parent FROM bom where component = '" . $Item . "'";
$Result = DB_query($SQL);
while ($MyRow=DB_fetch_array($Result)){
$NewParent = $MyRow['parent'];
$TotalCost = BomMaterialCost($NewParent, $db);
$LastCost = $TotalCost[0];
$LabourCost = $TotalCost[1];
$MaterialCost = $TotalCost[2];
$OverheadCost = $TotalCost[3];
$SQL = "UPDATE stockmaster SET
labourcost=" . $LabourCost . ",
materialcost=" . $MaterialCost . ",
overheadcost=" . $OverheadCost . ",
lastcost=" . $LastCost . "
WHERE stockid='" . $NewParent . "'";
$result1 = DB_query($SQL);
if (DB_error_no()!=0) {
return 1;
}
UpdateCost($db, $NewParent);
}
return 0;
}