<?php
session_start();
$PageSecurity = 1;
include('includes/session.inc');
include('config.php');
include('includes/ConnectDB.inc');
$title = _('BOM Demand on Stock');
if(!isset($_REQUEST['Printable']) || $_REQUEST['Printable']!='Y')
include('includes/header.inc');
//global vars
$G_RowCount = 0;
$G_RowsPerPage = 18;
function PrintHeader(){
echo "<tr>
<td align='center'>Stock ID</td>
<td align='center'>Description</td>
<td align='center'>Quantity Desired</td>
<td align='center'>Quantity On Hand</td>
<td align='center'>Sales Demand</td>
<td align='center'>Work Order Demand</td>
<td align='center'>On Order</td>
<td align='center'>On Work Order</td>
<td align='center'>Suggested Order</td>
</tr>";
} // function PrintHeader()
function NewPage(){
echo '</table>';
echo '<div style="page-break-after:always;"> </div> ';
echo '<table width="800" cellspacing="1" border="1">';
PrintHeader();
} // function NewPage()
function ShowDemand($db, $id, $quantity, $parentQuan, $level)
{
//upper recurse limit
if($level > 20)
return;
$purchasedBOM = false;
$manufPart = false;
$preferredSupplier = false;
$supplierId = 0;
$isaBOM = false;
global $G_RowCount;
global $G_RowsPerPage;
//first, get the description & quantity on hand for this part
$sql = "SELECT
stockmaster.categoryid,
stockmaster.description,
stockmaster.mbflag,
stockcategory.categorydescription,
locstock.stockid,
SUM(locstock.quantity) AS qoh
FROM
locstock,
stockmaster,
stockcategory
INNER JOIN
locations
ON
locations.loccode = locstock.loccode
WHERE
locstock.stockid=stockmaster.stockid
AND
stockmaster.stockid = '" . $id . "'
AND
locations.ignorestock = 0
GROUP BY
stockmaster.categoryid,
stockmaster.description,
stockcategory.categorydescription,
locstock.stockid,
stockmaster.stockid
ORDER BY
stockmaster.categoryid,
stockmaster.stockid";
$result = DB_query($sql,$db,'','',false,false);
if(!$result)
die( 'sql error:' . $sql);
if (DB_num_rows($result)==0){
prnMsg(_('No data was found for this stock item'),'warn');
echo $sql;
} else {
$list = DB_fetch_array($result,$db);
$stockId = $list['stockid'];
$description = $list['description'];
$qoh = $list['qoh'];
//see if this is a manufactured part
if($list['mbflag']=='M')
$manufPart = true;
else
$manufPart = false;
}
//find supplier info
$sql = "SELECT
purchdata.preferred,
purchdata.supplierno
FROM
purchdata
WHERE
purchdata.stockid = '" . $id . "'";
$supplierRes = DB_query($sql,$db,'','',false,false);
if(!$supplierRes)
die( 'sql error:' . $sql);
if (DB_num_rows($supplierRes)>0){
while($list = DB_fetch_array($supplierRes,$db)){
if($list['preferred']>0){
$preferredSupplier = true;
$supplierId = $list['supplierno'];
}
}
}
//Dont display BOM for a part with a preferred supplier, even if it is a manufactured
//part.
if($manufPart && $purchasedBOM)
$purchasedBOM = true;
//See if this part is a BOM
$sql = "select *
from bom
where parent = '" . $id . "'";
//we use this resultset later to loop through all components and recurse
$BOMchildren = DB_query($sql,$db,'','',false,false);
if(DB_num_rows($BOMchildren)>0)
$isaBOM = true;
//demand - find out the demand on part
$sql = "SELECT
SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem
FROM
salesorderdetails
INNER JOIN
stockmaster ON salesorderdetails.stkcode = stockmaster.stockid
INNER JOIN
salesorders ON salesorderdetails.orderno = salesorders.orderno
WHERE
stockmaster.stockid='" . id . "'
AND
salesorders.quotation=0
AND
salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
AND
salesorderdetails.completed=0";
$result = DB_query($sql,$db,'','',false,false);
if(!$result)
die( 'sql error:' . $sql);
$list = DB_fetch_array($result,$db);
$demand = 0 + $list['dem'];
//find work order demand on part- make sure work order is still open: could have
//been closed before completed, and we don't want to count those.
$sql = "SELECT
SUM(quantityneeded)-SUM(quantitymoved) AS workorderdemand
FROM
workorderdetails
INNER JOIN
workorders
ON
workorderdetails.orderno = workorders.orderno
WHERE
workorderdetails.stkcode = '" . $id . "'
AND
workorderdetails.ioflag = 'I'
AND
closeddate = '0000-00-00'
AND
completeddate = '0000-00-00'";
$result = DB_query($sql,$db,'','',false,false);
if(!$result)
die( 'sql error:' . $sql);
$list = DB_fetch_array($result,$db);
$workOrderDemand = 0 + $list['workorderdemand'];
//Assembly BOM demand - if this part belongs to a parent assembly BOM, find out the demand on parent
$sql = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
FROM
salesorderdetails
INNER JOIN
stockmaster ON stockmaster.stockid=bom.parent
INNER JOIN
bom ON salesorderdetails.stkcode=bom.parent
INNER JOIN
salesorders ON salesorderdetails.orderno = salesorders.orderno
WHERE
salesorders.quotation=0
AND
salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
AND
bom.component='" . $id . "'
AND
stockmaster.mbflag='A'
AND
salesorderdetails.completed=0";
$result = DB_query($sql,$db,'','',false,false);
if(!$result)
die( 'sql error:' . $sql);
$list = DB_fetch_array($result,$db);
$demand += $list['dem'];
//quantity on order..
$sql = "SELECT
SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) AS qtyonorder
FROM
purchorderdetails
WHERE
purchorderdetails.itemcode = '" . $id . "'
AND
purchorderdetails.completed = 0";
$result = DB_query($sql,$db,'','',false,false);
if(!$result)
die( 'sql error:' . $sql);
$list = DB_fetch_array($result,$db);
$onOrder = 0 + $list['qtyonorder'];
//quantity pending on work orders - make sure work order is still open: could have
//been closed before completed, and we don't want to count those.
$sql = "SELECT
SUM(quantityneeded)-SUM(quantitymoved) AS workorderquantity
FROM
workorderdetails
INNER JOIN
workorders
ON
workorderdetails.orderno = workorders.orderno
WHERE
workorderdetails.stkcode = '" . $id . "'
AND
workorderdetails.ioflag = 'O'
AND
closeddate = '0000-00-00'
AND
completeddate = '0000-00-00'";
$result = DB_query($sql,$db,'','',false,false);
if(!$result)
die( 'sql error:' . $sql);
$list = DB_fetch_array($result,$db);
$workOrderQuantity = 0 + $list['workorderquantity'];
//display stuff
for($count = 1; $count < $level; $count++)
$str .= "----|";
$BOMcolor = 'BLACK';
$rowcolor = '#EEEEEE';
$suggested = '0';
$color = 'GREEN';
if($isaBOM ){
$BOMColor = 'BLUE';
}
//multiply quantity needed to make one * the number desired
$quantity *= $parentQuan;
if(($quantity+$demand+$workOrderDemand) > ($qoh+$onOrder+$workOrderQuantity)){
$color = 'RED';
$suggested = ($quantity+$demand+$workOrderDemand) - ($qoh+$onOrder+$workOrderQuantity);
}
if(($quantity+$demand+$workOrderDemand) == ($qoh+$onOrder+$workOrderQuantity))
$color = 'YELLOW';
//print it all out
echo "<tr BGCOLOR='$rowcolor'>
<td align='left' NOWRAP><FONT COLOR='$BOMColor'>$str $stockId</FONT></td>
<td align='left'>$description</td>
<td align='center'><FONT COLOR='$color'>$quantity</FONT></td>
<td align='center'>$qoh</td>
<td align='center'>$demand</td>
<td align='center'>$workOrderDemand</td>
<td align='center'>$onOrder</td>
<td align='center'>$workOrderQuantity</td>
<td align='center'><B>$suggested</B></td>
</tr>";
$G_RowCount++;
if($G_RowCount >= $G_RowsPerPage){
NewPage();
$G_RowCount = 0;
}
//get all BOM records with this parent
//if there are BOM recs; this is another BOM; recurse.
$sql = "select component, quantity from bom where parent = '" . $id . "'";
$BOMchildren = DB_query($sql,$db,'','',false,false);
//dont recurse if the part is purchased, has a preferred supplier, or
//if there is sufficient quantity in stock
if(DB_num_rows($BOMchildren)>0 && !($purchasedBOM || $preferredSupplier )){
While ($BOMList = DB_fetch_array($BOMchildren,$db) )
{
//passing down the suggested order quantity, not the requested number
ShowDemand($db, $BOMList['component'], $BOMList['quantity'], $suggested, $level+1);
}
}
}// function ShowDemand($db, $id, $quantity, $parentQuan, $level)
$BOMid = $_REQUEST["id"];
if(!isset($_SESSION['DatabaseName'])){
echo "$" . "SESSION['DatabaseName'] not set!";
}else if(!$db){
echo "$" . "db not set!";
}else if(!isset($_REQUEST['id'])){
//set up form
?>
<html>
<body>
<center>
<form ACTION='BOMDemandList.php' METHOD='POST'>
<table width='400'>
<tr>
<td align='center'>Enter part number:</td>
</tr>
<tr>
<td align='center'><input type='TEXT' SIZE="20" NAME='id'></td>
</tr>
<tr>
<td align='center'>Enter quantity desired:</td>
</tr>
<tr>
<td align='center'><input type='TEXT' SIZE=5 NAME='quantity' value="1"></td>
</tr>
<tr>
<td align='center'><input type='SUBMIT' VALUE='Search'></td>
</tr>
</table>
</form>
</center>
</body>
</html>
<?
}else{
//add search again button to top of page;
//show BOM
echo '<A HREF="'
. $rootpath
. '/BOMDemandList.php?'
. SID . '&id='
. $BOMid
. '&quantity='
. $_REQUEST['quantity']
. '&Printable=Y" TARGET="_BLANK">Printable Version</A><BR>';
?>
<center>
<form ACTION='BOMDemandList.php' METHOD='GET'>
<table width='400'>
<tr>
<td align='center'><input type='SUBMIT' VALUE='Search Again'></td>
</tr>
</table>
</form>
<form ACTION='PO_Header.php' METHOD='POST'>
<input type='HIDDEN' NAME='Select' VALUE=''>
<table width=800 cellspacing="1" border="1">
<? PrintHeader(); ?>
</form>
<?
$rowcolor = '#CCCCCC';
if(isset($_REQUEST['quantity'])&& $_REQUEST['quantity']!='' && is_numeric($_REQUEST['quantity']))
ShowDemand($db, $BOMid, $_REQUEST['quantity'],1, 1 );
else
ShowDemand($db, $BOMid, 1, 1, 1 );
?>
</table>
</center>
<?
}
if(!isset($_REQUEST['Printable']) || $_REQUEST['Printable']!='Y')
include('includes/footer.inc');
?>