function LevelNetting(&$db, $part, $eoq, $PanSize, $ShrinkFactor, $LeadTime)
{
// Create an array of mrprequirements and an array of mrpsupplies, then read through
// them seeing if all requirements are covered by supplies. Create a planned order
// for any unmet requirements. Change dates if necessary for the supplies.
//echo '<br />Part is ' . "$part" . '<br />';
// Get decimal places from stockmaster for rounding of shrinkage factor
$sql = "SELECT decimalplaces FROM stockmaster WHERE stockid = '" . $part . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
$DecimalPlaces = $myrow[0];
// Load mrprequirements into $Requirements array
$sql = "SELECT * FROM mrprequirements WHERE part = '" . $part . "' ORDER BY daterequired";
$result = DB_query($sql, $db);
$Requirements = array();
$i = 0;
while ($myrow = DB_fetch_array($result)) {
array_push($Requirements, $myrow);
$i++;
}
//end of while loop
// Load mrpsupplies into $Supplies array
$sql = "SELECT * FROM mrpsupplies WHERE part = '" . $part . "' ORDER BY duedate";
$result = DB_query($sql, $db);
$Supplies = array();
$i = 0;
while ($myrow = DB_fetch_array($result)) {
array_push($Supplies, $myrow);
$i++;
}
//end of while loop
// Go through all requirements and check if have supplies to cover them
$RequirementCount = count($Requirements);
$SupplyCount = count($Supplies);
$reqi = 0;
//Index for requirements
$supi = 0;
// index for supplies
$TotalRequirement = 0;
$TotalSupply = 0;
if ($RequirementCount > 0 && $SupplyCount > 0) {
$TotalRequirement += $Requirements[$reqi]['quantity'];
$TotalSupply += $Supplies[$supi]['supplyquantity'];
while ($TotalRequirement > 0 && $TotalSupply > 0) {
$Supplies[$supi]['updateflag'] = 1;
// ******** Put leeway calculation in here ********
$DueDate = ConvertSQLDate($Supplies[$supi]['duedate']);
$ReqDate = ConvertSQLDate($Requirements[$reqi]['daterequired']);
$DateDiff = DateDiff($DueDate, $ReqDate, 'd');
//if ($Supplies[$supi]['duedate'] > $Requirements[$reqi]['daterequired']) {
if ($DateDiff > abs(filter_number_format($_POST['Leeway']))) {
$sql = "UPDATE mrpsupplies SET mrpdate = '" . $Requirements[$reqi]['daterequired'] . "' WHERE id = '" . $Supplies[$supi]['id'] . "' AND duedate = mrpdate";
$result = DB_query($sql, $db);
}
if ($TotalRequirement > $TotalSupply) {
$TotalRequirement -= $TotalSupply;
$Requirements[$reqi]['quantity'] -= $TotalSupply;
$TotalSupply = 0;
$Supplies[$supi]['supplyquantity'] = 0;
$supi++;
if ($SupplyCount > $supi) {
$TotalSupply += $Supplies[$supi]['supplyquantity'];
}
} elseif ($TotalRequirement < $TotalSupply) {
$TotalSupply -= $TotalRequirement;
$Supplies[$supi]['supplyquantity'] -= $TotalRequirement;
$TotalRequirement = 0;
$Requirements[$reqi]['quantity'] = 0;
$reqi++;
if ($RequirementCount > $reqi) {
$TotalRequirement += $Requirements[$reqi]['quantity'];
}
} else {
$TotalSupply -= $TotalRequirement;
$Supplies[$supi]['supplyquantity'] -= $TotalRequirement;
$TotalRequirement = 0;
$Requirements[$reqi]['quantity'] = 0;
$reqi++;
if ($RequirementCount > $reqi) {
$TotalRequirement += $Requirements[$reqi]['quantity'];
}
$TotalRequirement -= $TotalSupply;
$Requirements[$reqi]['quantity'] -= $TotalSupply;
$TotalSupply = 0;
$Supplies[$supi]['supplyquantity'] = 0;
$supi++;
if ($SupplyCount > $supi) {
$TotalSupply += $Supplies[$supi]['supplyquantity'];
}
}
// End of if $TotalRequirement > $TotalSupply
}
// End of while
}
// End of if
// When get to this part of code, have gone through all requirements, If there is any
// unmet requirements, create an mrpplannedorder to cover it. Also call the
// CreateLowerLevelRequirement() function to create gross requirements for lower level parts.
// There is an excess quantity if the eoq is higher than the actual required amount.
// If there is a subsuquent requirement, the excess quantity is subtracted from that
// quantity. For instance, if the first requirement was for 2 and the eoq was 5, there
// would be an excess of 3; if there was another requirement for 3 or less, the excess
// would cover it, so no planned order would have to be created for the second requirement.
$ExcessQty = 0;
foreach ($Requirements as $key => $row) {
$DateRequired[$key] = $row['daterequired'];
}
if (count($Requirements)) {
array_multisort($DateRequired, SORT_ASC, $Requirements);
}
foreach ($Requirements as $Requirement) {
// First, inflate requirement if there is a shrinkage factor
// Should the quantity be rounded?
if ($_POST['ShrinkageFlag'] == 'y' and $ShrinkFactor > 0) {
$Requirement['quantity'] = $Requirement['quantity'] * 100 / (100 - $ShrinkFactor);
$Requirement['quantity'] = round($Requirement['quantity'], $DecimalPlaces);
}
if ($ExcessQty >= $Requirement['quantity']) {
$PlannedQty = 0;
$ExcessQty -= $Requirement['quantity'];
} else {
$PlannedQty = $Requirement['quantity'] - $ExcessQty;
$ExcessQty = 0;
}
if ($PlannedQty > 0) {
if ($_POST['EOQFlag'] == 'y' and $eoq > $PlannedQty) {
$ExcessQty = $eoq - $PlannedQty;
$PlannedQty = $eoq;
}
// Pansize calculation here
// if $PlannedQty not evenly divisible by $PanSize, calculate as $PlannedQty
// divided by $PanSize and rounded up to the next highest integer and then
// multiplied by the pansize. For instance, with a planned qty of 17 with a pansize
// of 5, divide 17 by 5 to get 3 with a remainder of 2, which is rounded up to 4
// and then multiplied by 5 - the pansize - to get 20
if ($_POST['PanSizeFlag'] == 'y' and $PanSize != 0 and $PlannedQty != 0) {
$PlannedQty = ceil($PlannedQty / $PanSize) * $PanSize;
}
// Calculate required date by subtracting leadtime from top part's required date
$PartRequiredDate = $Requirement['daterequired'];
if ((int) $LeadTime > 0) {
$CalendarSQL = "SELECT COUNT(*),cal2.calendardate\n\t\t\t\t\t\t FROM mrpcalendar\n\t\t\t\t\t\t\tLEFT JOIN mrpcalendar as cal2\n\t\t\t\t\t\t\t ON (mrpcalendar.daynumber - '" . $LeadTime . "') = cal2.daynumber\n\t\t\t\t\t\t WHERE mrpcalendar.calendardate = '" . $PartRequiredDate . "'\n\t\t\t\t\t\t\tAND cal2.manufacturingflag='1'\n\t\t\t\t\t\t\tGROUP BY cal2.calendardate";
$ResultDate = DB_query($CalendarSQL, $db);
$myrowdate = DB_fetch_array($ResultDate);
$NewDate = $myrowdate[1];
// If can't find date based on manufacturing calendar, use $PartRequiredDate
} else {
// Convert $PartRequiredDate from mysql format to system date format, use that to subtract leadtime
// from it using DateAdd, convert that date back to mysql format
$ConvertDate = ConvertSQLDate($PartRequiredDate);
$DateAdd = DateAdd($ConvertDate, 'd', $LeadTime * -1);
$NewDate = FormatDateForSQL($DateAdd);
}
$sql = "INSERT INTO mrpplannedorders (id,\n\t\t\t\t\t\t\t\t\t\t\t\tpart,\n\t\t\t\t\t\t\t\t\t\t\t\tduedate,\n\t\t\t\t\t\t\t\t\t\t\t\tsupplyquantity,\n\t\t\t\t\t\t\t\t\t\t\t\tordertype,\n\t\t\t\t\t\t\t\t\t\t\t\torderno,\n\t\t\t\t\t\t\t\t\t\t\t\tmrpdate,\n\t\t\t\t\t\t\t\t\t\t\t\tupdateflag)\n\t\t\t\t\t\t\t\t\t\t\tVALUES (NULL,\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Requirement['part'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $NewDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $PlannedQty . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Requirement['mrpdemandtype'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Requirement['orderno'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $NewDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'0')";
$result = DB_query($sql, $db);
// If part has lower level components, create requirements for them
$sql = "SELECT COUNT(*) FROM bom\n\t\t\t\t\t WHERE parent ='" . $Requirement['part'] . "'\n\t\t\t\t\t GROUP BY parent";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
if ($myrow[0] > 0) {
CreateLowerLevelRequirement($db, $Requirement['part'], $Requirement['daterequired'], $PlannedQty, $Requirement['mrpdemandtype'], $Requirement['orderno'], $Requirement['whererequired']);
}
}
// End of if $PlannedQty > 0
}
// End of foreach $Requirements
// If there are any supplies not used and updateflag is zero, those supplies are not
// necessary, so change date
foreach ($Supplies as $supply) {
if ($supply['supplyquantity'] > 0 && $supply['updateflag'] == 0) {
$id = $supply['id'];
$sql = "UPDATE mrpsupplies SET mrpdate ='2050-12-31' WHERE id = '" . $id . "'\n\t\t\t\t\t AND ordertype <> 'QOH'";
$result = DB_query($sql, $db);
}
}
}
function LevelNetting(&$db, $part, $eoq, $pansize, $shrinkfactor)
{
// Create an array of mrprequirements and an array of mrpsupplies, then read through
// them seeing if all requirements are covered by supplies. Create a planned order
// for any unmet requirements. Change dates if necessary for the supplies.
//echo '</br>Part is ' . "$part" . '</br>';
// Get decimal places from stockmaster for rounding of shrinkage factor
$sql = "SELECT decimalplaces FROM stockmaster WHERE stockid = '" . $part . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
$decimalplaces = $myrow[0];
// Load mrprequirements into $requirements array
$sql = "SELECT * FROM mrprequirements WHERE part = '" . "{$part}" . "' ORDER BY daterequired";
$result = DB_query($sql, $db);
$requirements = array();
$i = 0;
while ($myrow = DB_fetch_array($result)) {
array_push($requirements, $myrow);
$i++;
}
//end of while loop
// Load mrpsupplies into $supplies array
$sql = "SELECT * FROM mrpsupplies WHERE part = '" . "{$part}" . "' ORDER BY duedate";
$result = DB_query($sql, $db);
$supplies = array();
$i = 0;
while ($myrow = DB_fetch_array($result)) {
array_push($supplies, $myrow);
$i++;
}
//end of while loop
// Go through all requirements and check if have supplies to cover them
$requirementcount = count($requirements);
$supplycount = count($supplies);
$reqi = 0;
//Index for requirements
$supi = 0;
// index for supplies
$totalrequirement = 0;
$totalsupply = 0;
if ($requirementcount > 0 && $supplycount > 0) {
$totalrequirement += $requirements[$reqi]['quantity'];
$totalsupply += $supplies[$supi]['supplyquantity'];
while ($totalrequirement > 0 && $totalsupply > 0) {
$supplies[$supi]['updateflag'] = 1;
// ******** Put leeway calculation in here ********
$duedate = ConvertSQLDate($supplies[$supi]['duedate']);
$reqdate = ConvertSQLDate($requirements[$reqi]['daterequired']);
$datediff = DateDiff($duedate, $reqdate, 'd');
//if ($supplies[$supi]['duedate'] > $requirements[$reqi]['daterequired']) {
if ($datediff > abs($_POST['Leeway'])) {
$sql = "UPDATE mrpsupplies SET mrpdate = '" . $requirements[$reqi]['daterequired'] . "' WHERE id = '" . $supplies[$supi]['id'] . "' AND duedate = mrpdate";
$result = DB_query($sql, $db);
}
if ($totalrequirement > $totalsupply) {
$totalrequirement -= $totalsupply;
$requirements[$reqi]['quantity'] -= $totalsupply;
$totalsupply = 0;
$supplies[$supi]['supplyquantity'] = 0;
$supi++;
if ($supplycount > $supi) {
$totalsupply += $supplies[$supi]['supplyquantity'];
}
} else {
$totalsupply -= $totalrequirement;
$supplies[$supi]['supplyquantity'] -= $totalrequirement;
$totalrequirement = 0;
$requirements[$reqi]['quantity'] = 0;
$reqi++;
if ($requirementcount > $reqi) {
$totalrequirement += $requirements[$reqi]['quantity'];
}
}
// End of if $totalrequirement > $totalsupply
}
// End of while
}
// End of if
// When get to this part of code, have gone through all requirements, If there is any
// unmet requirements, create an mrpplannedorder to cover it. Also call the
// CreateLowerLevelRequirement() function to create gross requirements for lower level parts.
// There is an excess quantity if the eoq is higher than the actual required amount.
// If there is a subsuquent requirement, the excess quantity is subtracted from that
// quantity. For instance, if the first requirement was for 2 and the eoq was 5, there
// would be an excess of 3; if there was another requirement for 3 or less, the excess
// would cover it, so no planned order would have to be created for the second requirement.
$excessqty = 0;
foreach ($requirements as $key => $row) {
$daterequired[$key] = $row['daterequired'];
}
if (count($requirements)) {
array_multisort($daterequired, SORT_ASC, $requirements);
}
foreach ($requirements as $requirement) {
// First, inflate requirement if there is a shrinkage factor
// Should the quantity be rounded?
if ($_POST['shrinkageflag'] == 'y' and $shrinkfactor > 0) {
$requirement['quantity'] = $requirement['quantity'] * 100 / (100 - $shrinkfactor);
$requirement['quantity'] = round($requirement['quantity'], $decimalplaces);
}
if ($excessqty >= $requirement['quantity']) {
$plannedqty = 0;
$excessqty -= $requirement['quantity'];
} else {
$plannedqty = $requirement['quantity'] - $excessqty;
$excessqty = 0;
}
if ($plannedqty > 0) {
if ($_POST['eoqflag'] == 'y' and $eoq > $plannedqty) {
$excessqty = $eoq - $plannedqty;
$plannedqty = $eoq;
}
// Pansize calculation here
// if $plannedqty not evenly divisible by $pansize, calculate as $plannedqty
// divided by $pansize and rounded up to the next highest integer and then
// multiplied by the pansize. For instance, with a planned qty of 17 with a pansize
// of 5, divide 17 by 5 to get 3 with a remainder of 2, which is rounded up to 4
// and then multiplied by 5 - the pansize - to get 20
if ($_POST['pansizeflag'] == 'y' and $pansize != 0 and $plannedqty % $pansize != 0) {
$plannedqty = ceil($plannedqty / $pansize) * $pansize;
}
$sql = "INSERT INTO mrpplannedorders (id,\n\t\t\t\t\t\t\t\tpart,\n\t\t\t\t\t\t\t\tduedate,\n\t\t\t\t\t\t\t\tsupplyquantity,\n\t\t\t\t\t\t\t\tordertype,\n\t\t\t\t\t\t\t\torderno,\n\t\t\t\t\t\t\t\tmrpdate,\n\t\t\t\t\t\t\t\tupdateflag)\n\t\t\t\t\t\t\tVALUES ('NULL',\n\t\t\t\t\t\t\t\t'" . $requirement['part'] . "',\n\t\t\t\t\t\t\t\t'" . $requirement['daterequired'] . "',\n\t\t\t\t\t\t\t\t'" . $plannedqty . "',\n\t\t\t\t\t\t\t\t'" . $requirement['mrpdemandtype'] . "',\n\t\t\t\t\t\t\t\t'" . $requirement['orderno'] . "',\n\t\t\t\t\t\t\t\t'" . $requirement['daterequired'] . "',\n\t\t\t\t\t\t\t\t'0')";
$result = DB_query($sql, $db);
// If part has lower level components, create requirements for them
$sql = "SELECT COUNT(*) FROM bom WHERE parent ='" . $requirement['part'] . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
if ($myrow[0] > 0) {
CreateLowerLevelRequirement($db, $requirement['part'], $requirement['daterequired'], $plannedqty, $requirement['mrpdemandtype'], $requirement['orderno'], $requirement['whererequired']);
}
}
// End of if $plannedqty > 0
}
// End of foreach $requirements
// If there are any supplies not used and updateflag is zero, those supplies are not
// necessary, so change date
foreach ($supplies as $supply) {
if ($supply['supplyquantity'] > 0 && $supply['updateflag'] == 0) {
$id = $supply['id'];
$sql = "UPDATE mrpsupplies SET mrpdate ='2050-12-31' WHERE id = '{$id}'\n\t\t\t AND ordertype <> 'QOH'";
$result = DB_query($sql, $db);
}
}
}
