<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<div style="width:800px; margin-left:auto; margin-right:auto;">

	<fieldset style="background-color:#f3f7fb">
		<h1 style="width:455px; float:left; background-color:#003399; padding-left:10px; padding-top:5px; padding-bottom:5px;font-size:12px; color:#FFFFFF; font-weight:bold" align="left">PR v/s PO and Balance Qty Report</h1>

<form action="" method="post">
			
<table width="60%" border="0" cellspacing="0" cellpadding="0" align="left">
  <tr>
    <td width="38%"><strong>Project</strong> <span style="color:#FF0000">*</span></td>
    <td width="62%"><select name="site" style="width:350px; background-color:#dbe9f7; padding-top:3px; padding-bottom:3px;">
					<option value=""> Select Site </option>
			<?php  $site_sql=DB_query("Select loccode,locationname from locations",$db);
	 			 while($site_row=DB_fetch_array($site_sql)) 
				  {
	  				$s='';
					if($_REQUEST['site']==$site_row['loccode'])
					$s="selected";
	 		 ?>
        <option value="<?php echo $site_row['loccode'];?>" <?php echo $s;?>><?php echo $site_row['locationname'];?></option>
        <?php } ?>
	</select>
	
	</td>
  </tr>
  <tr>
    <td><strong>Vendor</strong></td>
    <td width="62%"><select name="vendor" style="width:350px; background-color:#dbe9f7; padding-top:3px; padding-bottom:3px;">
	<option value=""> Select Vendor </option>
			<?php 
	 		 $v_sql=DB_query("Select id,vendor_code,vendor_name from suppliers",$db);
	 		 while($v_row=DB_fetch_array($v_sql)) 
	 		 {
	  			$s='';
				if($_REQUEST['vendor']==$v_row['id'])
				$s="selected";
	 		 ?>
	 		 <option value="<?php echo $v_row['id'];?>" <?php echo $s;?>><?php echo $v_row['vendor_name'];?></option>
	 	 <?php } ?>
	
	</select>
	
	</td>
  </tr>
  <tr>
    <td><strong>Meterial Category</strong></td>
    <td width="62%">
	
	<select name="meterial_category" style="width:350px; background-color:#dbe9f7; padding-top:3px; padding-bottom:3px;">
			<option value=""> Select Meterial Category </option>
			<?php 
	 		 $stock_sql=DB_query("Select * from stockmaincategory",$db);
	 		 while($stock_row=DB_fetch_array($stock_sql)) 
	 		 {
	  			$s='';
				if($_REQUEST['meterial_category']==$stock_row['id'])
				$s="selected";
	 		 ?>
	 		 <option value="<?php echo $stock_row['id'];?>" <?php echo $s;?>><?php echo $stock_row['stockmaincategoryname'];?></option>
	 	 <?php } ?>
	
	</select>
	
	</td>
  </tr>
  <tr>
    <td><strong>Meterial Group</strong></td>
	<td width="62%"><select name="sub_meterial_category" style="width:350px; background-color:#dbe9f7; padding-top:3px; padding-bottom:3px;">
			<option value="">Select Meterial Group </option>
			<?php 
	 		 $stock_sql=DB_query("Select * from stockcategory",$db);
	 		 while($stock_row=DB_fetch_array($stock_sql)) 
	 		 {
	  			$s='';
				if($_REQUEST['sub_meterial_category']==$stock_row['id'])
				$s="selected";
	 		 ?>
	 		 <option value="<?php echo $stock_row['id'];?>" <?php echo $s;?>><?php echo $stock_row['categorydescription'];?></option>
	 	 <?php } ?>
	</select>
	
	</td>
  </tr>
  <tr>
    <td><strong>Meterial Name</strong></td>
    <td width="62%"><select name="item_code" style="width:350px; background-color:#dbe9f7; padding-top:3px; padding-bottom:3px;">
			<option value="">Select Meterial </option>
			<?php 
	 		 $stock_sql=DB_query("Select * from item_master",$db);
	 		 while($stock_row=DB_fetch_array($stock_sql)) 
	 		 {
	  			$s='';
				if($_REQUEST['item_code']==$stock_row['StockID'])
				$s="selected";
	 		 ?>
	 		 <option value="<?php echo $stock_row['StockID'];?>" <?php echo $s;?>><?php echo $stock_row['item_name'];?></option>
	 	 <?php } ?>
	
	</select>
	
	</td>
  </tr>
  <tr>
    <td><strong>Block</strong></td>
    <td width="62%"><select style="width:350px; background-color:#dbe9f7; padding-top:3px; padding-bottom:3px;">
			<option id="0">Select Your Projects </option>
			<option id="1">Select Your Projects </option>
			<option id="2">Select Your Projects </option>
			<option id="3">Select Your Projects </option>
	
	</select>
	
	</td>
  </tr>

  <tr>
    <td><strong>PR No.</strong></td>
    <td width="62%"><select style="width:350px; background-color:#dbe9f7; padding-top:3px; padding-bottom:3px;" name="pr_no">
			<option value="">Select PR No </option>
			<?php 
	 		 $stock_sql=DB_query("Select * from materialrequisitions",$db);
	 		 while($stock_row=DB_fetch_array($stock_sql)) 
	 		 {
	  			$s='';
				if($_REQUEST['pr_no']==$stock_row['requisitionno'])
				$s="selected";
	 		 ?>
	 		 <option value="<?php echo $stock_row['requisitionno'];?>" <?php echo $s;?>><?php echo $stock_row['Reqno'];?></option>
	 	 <?php } ?>
	</select>
	
	</td>
  </tr>
  <tr>
  	<td><strong>PR From Date</strong></td>
  
    <td>
			<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="0%"></td>
    <td width="40%"><input type="text" name="fromdate" style="width:140px; background-color:#dbe9f7"  value="<?php  echo $_REQUEST['fromdate']?>" class="datepicker" />
	
	</td>
    <td width="23%"><strong>PR To Date</strong></td>
    <td width="37%"><input type="text" name="todate" style="width:140px; background-color:#dbe9f7" value="<?php  echo $_REQUEST['todate']?>" class="datepicker"  />
	
	</td>
  </tr>
</table>
	</td>
  </tr>
  <tr>
  <td>&nbsp;</td>
    <td><input name="include" type="checkbox" value="include" />&nbsp;&nbsp;&nbsp;<strong>Include Zero Balance Qty</strong></td>
  </tr>
  <tr>

  <tr>
  <td colspan="2" style="height:20px;"></td>
  </tr>
  <tr>
  
  
  <td>&nbsp;</td>
    <td><input type="submit" value="View Report" style="background-color:#003399;padding:3px; color:#FFFFFF; margin:0px;" name="view" />&nbsp;&nbsp;&nbsp;&nbsp;<input type="reset" value="Cancel" style="background-color:#003399;padding:3px; color:#FFFFFF; margin:0px;" /></td>
  </tr>
  
  
  
</table>
</form></fieldset>
<div style="clear:both;"></div>
<br />
<div style="width:800px;">
	<p align="right"><a href="po_pr_report_exp.php?site=<?php echo $_REQUEST['site'];?>&vendor=<?php echo $_REQUEST['vendor'];?>&meterial_category=<?php echo $_REQUEST['meterial_category'];?>&sub_meterial_category=<?php echo $_REQUEST['sub_meterial_category'];?>&item_code=<?php echo $_REQUEST['item_code'];?>&pr_no=<?php echo $_REQUEST['pr_no'];?>&fromdate<?php echo $_REQUEST['fromdate'];?>&todate<?php echo $_REQUEST['todate'];?>&include=<?php echo $_REQUEST['include'];?>">Export</a>&nbsp;<a href="po_pr_print.php?site=<?php echo $_REQUEST['site'];?>&vendor=<?php echo $_REQUEST['vendor'];?>&meterial_category=<?php echo $_REQUEST['meterial_category'];?>&sub_meterial_category=<?php echo $_REQUEST['sub_meterial_category'];?>&item_code=<?php echo $_REQUEST['item_code'];?>&pr_no=<?php echo $_REQUEST['pr_no'];?>&fromdate<?php echo $_REQUEST['fromdate'];?>&todate<?php echo $_REQUEST['todate'];?>&include=<?php echo $_REQUEST['include'];?>">Print</a></p>
<table style="width:100%" cellpadding="3" cellspacing="1" border="0" align="center">
<tr>

<th><strong>Project</strong></th>
<th><strong>PR No.</strong></th>
<th><strong>Vendor</strong></th>
<th><strong>Item Description</strong></th>

<th><strong>PR Qty.</strong></th>

<th><strong>PR Date</strong></th>
<th><strong>PO Date</strong></th>
<th><strong>PO No.</strong></th>
<th><strong>PO Qty</strong></th>
<th><strong>Balance Qty</strong></th>
</tr>
<?php
$totalQty1=array();
$totalQty2=array();
$BalanceTotal=array();
 
		if($_POST['view'])
		{
		
		
		$fordate = $_POST['fromdate'];
	$todate = $_POST['todate'];
	$fromdate = explode("/", $fordate);
	$daydate = $fromdate['0'];
	$Monthdate = $fromdate['1'];
	$yeardate = $fromdate['2'];
	$fdate = $yeardate."-".$Monthdate."-".$daydate;
					   
	$tredate   = explode("/", $todate);
	$dayda     = $tredate['0'];
	$Monthda   = $tredate['1'];
	$yearda    = $tredate['2'];
	$toyrdate  = $yearda."-".$Monthda."-".$dayda;
	
			$filter = array();
	
			if(isset($_REQUEST['site']) && $_REQUEST['site'] != '')
			
			$filter[]="materialrequisitions.sitecode='".$_POST['site']."'";
			
			if(isset($_REQUEST['pr_no']) && $_REQUEST['pr_no'] != '')
				
			$filter[]="materialrequisitions.requisitionno='".$_REQUEST['pr_no']."'";
		
			
			
			if(isset($_POST['fromdate']) && $_POST['fromdate'] != '' && isset($_POST['todate']) && $_POST['todate'] != '')
 	$filter[]="materialrequisitions.prdate between '".$fdate."' and '".$toyrdate."' ";
			
			
			/*************************** find main and sub category item *****************************************/
				if(isset($_REQUEST['meterial_category']) && $_REQUEST['meterial_category'] != '')
			{
		
		$itemidquery=DB_query("select id from item_master where CategoryID='".$_REQUEST['meterial_category']."'",$db);
		
		while($itemid=DB_fetch_array($itemidquery))
		{
			$itemArray[]=$itemid['id'];
			//print_r($itemArray);
	
		}
	}
	//print_r($itemArray);
	if(!empty($itemArray))
	{
		$itemString="'".implode("','",$itemArray)."'";	
	//echo "<pre>";
	//print_r($itemString);
	
	
	$filter[]="requisitionorderdetails.mst_itemid in (".$itemString.")";
	
	}
			
			
	if(isset($_REQUEST['sub_meterial_category']) && $_REQUEST['sub_meterial_category'] != '')
	{
		
		$itemidquery=DB_query("select id from item_master where SubCategoryID='".$_REQUEST['sub_meterial_category']."'",$db);
		
		while($itemid=DB_fetch_array($itemidquery))
		{
			$itemArray1[]=$itemid['id'];
			//print_r($itemArray1);
	
		}
	}
	//print_r($itemArray1);
	if(!empty($itemArray1))
	{
	$itemString1="'".implode("','",$itemArray1)."'";	
	//echo "<pre>";
	//print_r($itemString1);
	
	
	$filter[]="requisitionorderdetails.mst_itemid in (".$itemString1.")";
	
	}
			
	/*************************** find main and sub category item *****************************************/		
			
			
			if(isset($_REQUEST['item_code']) && $_REQUEST['item_code'] != '')
			 $filter[]="requisitionorderdetails.product_id='".$_POST['item_code']."'";
			
			  $filter1 = implode("and ",$filter); 

	if($filter1!='')
	{
		
					if($_REQUEST['vendor'] != '')
					{
					
								 
					  $sql="SELECT materialrequisitions.Reqno as Reqno,materialrequisitions.*,requisitionorderdetails.* ,purchorders.*,purchorderdetails.* FROM materialrequisitions
					INNER JOIN requisitionorderdetails ON materialrequisitions.requisitionno=requisitionorderdetails.requsitionno 
					INNER JOIN purchorders ON materialrequisitions.requisitionno=purchorders.mst_reqino
					INNER JOIN purchorderdetails ON purchorders.orderno=purchorderdetails.orderno WHERE $filter1 and purchorders.vendor_id='".$_REQUEST['vendor']."' GROUP BY purchorderdetails.id";

					}
					else
					{
					 	    $sql="SELECT materialrequisitions.Reqno as Reqno, materialrequisitions.*,requisitionorderdetails.*,purchorders.*,purchorderdetails.* FROM materialrequisitions
					INNER JOIN requisitionorderdetails ON materialrequisitions.requisitionno=requisitionorderdetails.requsitionno 
					INNER JOIN purchorders ON materialrequisitions.requisitionno=purchorders.mst_reqino
					INNER JOIN purchorderdetails ON purchorders.orderno=purchorderdetails.orderno WHERE $filter1 GROUP BY purchorderdetails.id";
					}
 $_SESSION['po']=$sql;

//echo $sql;
?>


<?php 
$flag='';	
$flag1='';	
$flag2='';	
$flag3='';	
$flag4='';
$flag5='';
$flag6='';
$flagTest='';
$secondtime='';
		$i=0;
		$query=DB_query($sql,$db);
		while($query_row=DB_fetch_array($query))
		{
		
		$i++;
		
		$balance=$query_row['App_quantity'] - $query_row['quatity'];
		if($balance>0)
		{
		
		if($flag!=$query_row['vendor_id'])
				{
				$flag=$query_row['vendor_id'];
				$count=1;	
				}
              else
              {
              	 $count=0;
              }
			  
			  if($flag1!=$query_row['sitecode'])
				{
				$flag1=$query_row['sitecode'];
				$count1=1;	
				}
              else
              {
              	 $count1=0;
              }
		 if($flag2!=$query_row['Reqno'])
				{
				$flag2=$query_row['Reqno'];
				$count2=1;	
				}
              else
              {
              	 $count2=0;
              }
			   if($flag3!=$query_row['mst_itemid'])
				{
				$flag3=$query_row['mst_itemid'];
				$count3=1;	
				}
              else
              {
              	 $count3=0;
              }
			  
			   if($flag4!=$query_row['orddate'])
				{
				$flag4=$query_row['orddate'];
				$count4=1;	
				}
              else
              {
              	 $count4=0;
              }
		 if($flag5!=$query_row['prdate'])
				{
				$flag5=$query_row['prdate'];
				$count5=1;	
				}
              else
              {
              	 $count5=0;
              }
			   if($flag6!=$query_row['PO_Orderno'])
				{
				$flag6=$query_row['PO_Orderno'];
				$count6=1;	
				}
              else
              {
              	 $count6=0;
              }
			   if($flagTest!=$query_row['quatity'])
				{
				$flagTest=$query_row['quatity'];
				$countTest=1;	
				}
              else
              {
              	 $countTest=0;
              }
			 
			  
		
			  
		
		
		
		//echo "<pre>";
		//print_r($query_row);
		//echo "<br>";
?>
<tr>
<td>
 <?php if($flag1==$query_row['sitecode'] && $count1!=0) { ?>

<?php $locations=DB_query("select locationname from locations where loccode='".$query_row['sitecode']."'",$db);
	$locationsname=DB_fetch_array($locations); echo $locationsname['locationname']; ?>
	 <?php } ?>	</td>
<td>
<?php if($flag2==$query_row['Reqno'] and $count2!=0) {

  
 ?>
  
<?php echo $query_row['Reqno']; ?>
<?php }



?></td>
<td>
 <?php if($flag==$query_row['vendor_id'] and $count!=0) { ?>
<?php 
	$vendor=DB_query("select * from suppliers where id='".$query_row['vendor_id']."'",$db);
	$vendor_name=DB_fetch_array($vendor); echo $vendor_name['vendor_name']; 
//echo $suppliers=$s->getField('suppliers','id',$query_row['vendor_id'],'vendor_name');  ?>
 <?php } ?></td>
<td>
<?php if($flag3==$query_row['mst_itemid'] and $count3!=0) { ?>
<?php
		$itemquery=DB_query("select item_name from item_master where id='".$query_row['mst_itemid']."'",$db);
	$item_name=DB_fetch_array($itemquery); echo $item_name['item_name'];
 ?>
 <?php }?></td>
<td><?php 

$totalQty1[]=$query_row['App_quantity'];
echo $query_row['App_quantity']; ?></td>
<td>
 <?php if($flag4==$query_row['orddate'] and $count4!=0) { ?>
<?php echo $query_row['orddate']; ?>

<?php }?></td>
<td>
<?php if($flag5==$query_row['prdate'] and $count5!=0) { ?>
<?php echo $query_row['prdate']; ?>
<?php }?></td>


<td>
<?php if($flag6==$query_row['PO_Orderno'] and $count6!=0) { ?>
<?php echo $query_row['PO_Orderno']; ?>
<?php }?></td>
<td><?php 

$totalQty2[]=$query_row['quatity'];


echo $query_row['quatity']; ?></td>
<td><?php 

$BalanceTotal[]= $balance;

 //echo $balance1;
 if($flag2==$query_row['Reqno'] and $count2!=0) {
 
  
  //echo $query_row['quatity'];
  $balance=$query_row['App_quantity'] - $query_row['quatity'];
  $secondtime= $balance;

	//if($balance!=0 || $balance>0) { 
	echo  $balance;
	}
	else
	{
	//$t[]=$query_row['quatity'];
	echo  $t=$secondtime-$query_row['quatity'];
	
	//echo  $balance;
//echo 	$balance=$query_row['App_quantity'] - $query_row['quatity']; 
	//echo $ghcfh+=$query_row['quatity'];
	//echo $query_row['quatity'];
	 //echo $balance2 = $balance-$query_row['quatity'];
	//echo $balance2 ; 
	}
	//}
	?></td>
</tr>
<?php  } }  ?>




<?php
$queryreq=DB_query("SELECT * FROM comprative_detail WHERE status_active ='1'",$db);
		
		$req=array();
		while($row1=DB_fetch_array($queryreq))
		{
		
		$req[]=$row1['RequisitionNo'];
		
	
		}
		
		
		$reqno="'".implode("','",$req)."'";	
		//echo $reqno;
				
		 /*  $sql="SELECT materialrequisitions.*,requisitionorderdetails.* FROM materialrequisitions
				INNER JOIN requisitionorderdetails ON materialrequisitions.requisitionno=requisitionorderdetails.requsitionno 
				WHERE requisitionno NOT IN(".$reqno.") and $filter1 GROUP BY requisitionorderdetails.id";*/
			$_SESSION['compare']=$sql;
		
		
/*$sqldetails_row=DB_query($sql,$db);*/
		
		/*
		while($query_row1=DB_fetch_array($sqldetails_row))
		{
		$i++;*/
		
		/*if($balance>0)
		{*/
		
		//echo "<pre>";
		//print_r($query_row);
		//echo "<br>";
?>
<!--<tr>
<td>
<?php /*if($flag1==$query_row1['sitecode'] and $count1!=0) {*/ ?>
<?php /*$locations=DB_query("select locationname from locations where loccode='".$query_row1['sitecode']."'",$db);
	$locationsname=DB_fetch_array($locations); echo $locationsname['locationname'];*/ ?>
	<?php //} ?>	</td>
<td>
<?php /*if($flag2==$query_row1['Reqno'] and $count2!=0) {*/ ?>
<?php //echo $query_row1['Reqno']; ?>
<?php // }?></td>
<td><?php 
	/*$vendor=DB_query("select * from suppliers where id='".$query_row1['vendor_id']."'",$db);
	$vendor_name=DB_fetch_array($vendor); echo $vendor_name['vendor_name']; */
//echo $suppliers=$s->getField('suppliers','id',$query_row['vendor_id'],'vendor_name');  ?></td>
<td>
<?php /*if($flag3==$query_row1['mst_itemid'] and $count3!=0) { */?>
<?php
		/*$itemquery=DB_query("select item_name from item_master where id='".$query_row1['mst_itemid']."'",$db);
	$item_name=DB_fetch_array($itemquery); echo $item_name['item_name'];*/
 ?>
 
 <?php //}?> </td>
	
<td><?php
/*$totalQty1[]=$query_row1['App_quantity'];

 echo $query_row1['App_quantity']; */?></td>

<td>
 <?php /*if($flag4==$query_row1['orddate'] and $count4!=0) { */?>
<?php //echo $query_row1['orddate']; ?>
<?php // }?></td>
<td>
<?php /*if($flag5==$query_row1['prdate'] and $count5!=0) { */?>
<?php //echo $query_row1['prdate']; ?>
<?php //}?></td>

<?php /*if($flag6==$query_row1['PO_Orderno'] and $count6!=0) {*/ ?>
<td><?php //echo $query_row1['PO_Orderno']; ?>

<?php //}?></td>
<td><?php
/*$totalQty2[]=$query_row1['quatity'];
 echo $query_row1['quatity'];*/ ?></td>
<td><?php //echo   $balance=$query_row1['App_quantity'] - $query_row1['quatity']; 
	 ?></td>
</tr>-->
<?php //} 
//} 
}  ?>

<?php } ?>
<tr>
<td><strong>Total</strong></td>
<td></td>
<td></td>
<td ></td>
<td><strong><?php echo number_format(array_sum($totalQty1),2); ?></strong></td>
<td></td>
<td></td>
<td></td>
<td><strong><?php echo number_format(array_sum($totalQty2),2); ?></strong></td>
<td><strong><?php echo number_format(array_sum($BalanceTotal),2);?></strong></td>
</tr>
</table>
</div>



	
</body>
</html>
