Location: PHPKode > projects > InfoERP for Outdoor Caterers > BOMDemandList.php
<?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;">&nbsp;</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');

?>
Return current item: InfoERP for Outdoor Caterers