<!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>
<?php 
session_name('CAKEPHP'); session_start();
if($_REQUEST['Cancel']!='')
{unset($_REQUEST);}
?>
<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 
			if($_SESSION['AssignedSites']!='0')
	        { 
			$site_sql=DB_query("Select loccode,locationname from locations where loccode  in (".$_SESSION['AssignedSites'].")",$db); 
			}
			else
			{
			 $site_sql=DB_query("Select loccode,locationname from locations",$db);
			 }
	 			 while($site_row=DB_fetch_array($site_sql)) 
				  {
	  				$p='';
					if($_REQUEST['site']==$site_row['loccode'])
					$p="selected";
	 		 ?>
        <option value="<?php echo $site_row['loccode'];?>" <?php echo $p;?>><?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)) 
	 		 {
	  			$p='';
				if($_REQUEST['vendor']==$v_row['id'])
				$p="selected";
	 		 ?>
	 		 <option value="<?php echo $v_row['id'];?>" <?php echo $p;?>><?php echo str_replace(array("M/s ","M/S"),'',$v_row['vendor_name']);?></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)) 
	 		 {
	  			$p='';
				if($_REQUEST['item_code']==$stock_row['id'])
				$p="selected";
	 		 ?>
	 		 <option value="<?php echo $stock_row['id'];?>" <?php echo $p;?>><?php echo $stock_row['item_name'];?></option>
	 	 <?php } ?>
	
	</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)) 
	 		 {
	  			$p='';
				if($_REQUEST['pr_no']==$stock_row['requisitionno'])
				$p="selected";
	 		 ?>
	 		 <option value="<?php echo $stock_row['requisitionno'];?>" <?php echo $p;?>><?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>
  <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="submit" name="Cancel" value="Cancel" style="background-color:#003399;padding:3px; color:#FFFFFF; margin:0px;" /></td>
  </tr>
  
  
  
</table>
</form></fieldset>

</div>
<div style="clear:both;"></div>
<br />
<?php 
if($_REQUEST['view']!='')
{
	$fromdate=$_REQUEST['fromdate'];
	$todate=$_REQUEST['todate'];
	$fromdate_brk=explode('/',$fromdate);
	$from=$fromdate_brk[2]."-".$fromdate_brk[1]."-".$fromdate_brk[0];
	$todate_brk=explode('/',$todate);
	$to=$todate_brk[2]."-".$todate_brk[1]."-".$todate_brk[0];
	
	$site=$_REQUEST['site'];
	$vendor=$_REQUEST['vendor'];
	$pr_no=$_REQUEST['pr_no'];
	$item_code=$_REQUEST['item_code'];
	
	$filter='';
	if($site!='')
	$filter[]="materialrequisitions.sitecode = '".$site."'";
	if($vendor!='')
	$filter[]="purchorders.vendor_id = '".$vendor."'";
	if($pr_no!='')
	$filter[]="materialrequisitions.requisitionno = '".$pr_no."'";
	if($fromdate!='' && $todate!='')
	$filter[]="materialrequisitions.prdate BETWEEN '".$from."' and '".$to."'";
	if($item_code!='')
	$filter[]="requisitionorderdetails.mst_itemid ='".$item_code."'";
	
	if($filter!=''){$filter=implode(' and ',$filter);}
	
	if($filter!='')
	{
	 $sql1="Select *,materialrequisitions.requisitionno as main_id, materialrequisitions.sitecode as site,requisitionorderdetails.mst_itemid , requisitionorderdetails.product_id from materialrequisitions left join purchorders on (materialrequisitions.requisitionno=purchorders.mst_reqino) left join requisitionorderdetails on (materialrequisitions.requisitionno=requisitionorderdetails.requsitionno) where $filter group by materialrequisitions.requisitionno order by prdate desc ";
	 }
	 else
	 {
	  if($_SESSION['AssignedSites']!='0')
	  {
	 $sql1="Select *,materialrequisitions.requisitionno as main_id, materialrequisitions.sitecode as site,requisitionorderdetails.mst_itemid , requisitionorderdetails.product_id from materialrequisitions left join purchorders on (materialrequisitions.requisitionno=purchorders.mst_reqino) left join requisitionorderdetails on (materialrequisitions.requisitionno=requisitionorderdetails.requsitionno) where materialrequisitions.sitecode in (".$_SESSION['AssignedSites'].")  group by materialrequisitions.requisitionno order by prdate desc ";
	 }
	 else
	 {
	 	 $sql1="Select *,materialrequisitions.requisitionno as main_id, materialrequisitions.sitecode as site,requisitionorderdetails.mst_itemid , requisitionorderdetails.product_id from materialrequisitions left join purchorders on (materialrequisitions.requisitionno=purchorders.mst_reqino) left join requisitionorderdetails on (materialrequisitions.requisitionno=requisitionorderdetails.requsitionno)  group by materialrequisitions.requisitionno order by prdate desc ";
	 }
	 }
	 
}
/*else 
{
	$sql1="Select *,materialrequisitions.requisitionno as main_id, materialrequisitions.sitecode as site from materialrequisitions order by prdate desc";
}*/

//echo $sql1;
  $_SESSION['prpo_report']=$sql1;
?>
<p align="right"><a href="PO_PR_Qty_Report_print.php?site=<?php echo $_REQUEST['site'];?>&pr_no=<?php echo $_REQUEST['pr_no']?>&fromdate=<?php echo $_REQUEST['fromdate']?>&todate=<?php echo $_REQUEST['todate']?>&vendor_id=<?php echo $_REQUEST['vendor']?>&itemCode=<?php echo $_REQUEST['item_code']?>" target="_blank">Print</a>&nbsp;&nbsp;
<!--<a href="tcpdf/Pdf_material_receipt_report_print.php?site=<?php echo $_REQUEST['site'];?>&store_code=<?php echo $_REQUEST['store1']?>&fromdate=<?php echo $_REQUEST['fromdate']?>&todate=<?php echo $_REQUEST['todate']?>&vendor_id=<?php echo $_REQUEST['vendor']?>&itemname=<?php echo $_REQUEST['itemdata']?>" target="_blank">Pdf</a>-->
</p>
<?php 
if($_REQUEST['view']!='')
{ 
?>
<div style="width:810px; height:auto; overflow:auto; margin-left:auto; margin-right:auto;">

<table width="1000px;" cellpadding="0" cellspacing="0" border="1" style="border:solid 1px #666666;">
	<tr style="background-color:#CCCCCC; height: 30px">
	<td style="width: 120px;" class="jjrs_tablehead"><strong>SITE</strong></td>
     <td style="width: 150px;" class="jjrs_tablehead"><strong>PR NO.</strong></td>
     <td style="width: 191px;" class="jjrs_tablehead"><strong>ITEM</strong></td>
	 <td style="width: 70px;" class="jjrs_tablehead"><strong>PR QTY</strong></td>
     <td style="width: 55px;" class="jjrs_tablehead"><strong>PO DATE</strong></td>
     <td style="width: 93px;" class="jjrs_tablehead"><strong>VENDOR</strong></td>
     <td style="width: 105px;" class="jjrs_tablehead"><strong>PO NO.</strong></td>
     <td style="width: 73px;" class="jjrs_tablehead"><strong>PO QTY</strong></td>
	 <td style="" class="jjrs_tablehead"><strong>BAL. QTY</strong></td>
  </tr>
	<?php 
	//echo $sql1;
	$result1=DB_query($sql1,$db);
	while($row1=DB_fetch_array($result1)){
	?> 
<tr bgcolor="#FFFFFF">
	<td><?php echo $s->getField('locations','loccode',$row1['site'],'locationname'); //echo $row1['main_id'];?></td>
	<td><?php echo $row1['Reqno']; ?></td>
	<td colspan="7" style="background-color:#e7e7e7;">
		<table width="730" border="0" >
			<?php 
			if($item_code!=''){$sql2="Select mst_itemid, product_id, product_description,App_quantity from requisitionorderdetails where requsitionno='".$row1['main_id']."' and mst_itemid='".$item_code."'";}else {
			 $sql2="Select mst_itemid, product_id, product_description,App_quantity from requisitionorderdetails where requsitionno='".$row1['main_id']."'"; }
			$result2=DB_query($sql2,$db);
			while($row2=DB_fetch_array($result2)){
			?>
			<tr>
				<td style="width:190px; border-bottom: solid 1px #666666" class="jjrs_tabledata1"  bgcolor="99CCCC"><?php echo $row2['product_description'];?></td>
				<td  style="width:70px;  border-bottom: solid 1px #666666" class="jjrs_tabledata1"  bgcolor="99CCCC"><strong><?php echo $row2['App_quantity'];?></strong></td>
				<td colspan="4" valign="bottom" >
					<table width="100%" border="1">		
						<?php 
						if($vendor!=''){$sql3="SELECT A.*, B.* FROM purchorders A, purchorderdetails B WHERE A.orderno=B.orderno AND B.mst_reqino='".$row1['main_id']."' AND B.mst_itemid='".$row2['mst_itemid']."' AND A.vendor_id='".$vendor."' AND B.rate!='0.00 ";}else{
					 	$sql3="SELECT A.*, B.* FROM purchorders A, purchorderdetails B WHERE A.orderno=B.orderno AND B.mst_reqino='".$row1['main_id']."' AND B.mst_itemid='".$row2['mst_itemid']."' and B.rate!='0.00' "; }
						$result3=DB_query($sql3,$db);
						if(DB_num_rows($result3)>0){
						$po_qty=array();
						while($row3=DB_fetch_array($result3)){
			?>				
						<tr bgcolor="D6C5CC">
							<td style="width:50px;" class="jjrs_tabledata1"><?php echo ConvertSQLDate($row3['orddate']);?></td>
							<td style="width:130px;" class="jjrs_tabledata1"><?php echo $row3['supplierno'];?></td>
							<td style="width:130px;" class="jjrs_tabledata1"><?php echo $row3['PO_Orderno'];?></td>
							<td style="width:100px;" class="jjrs_tabledata1"><?php echo $row3['quatity']; $po_qty[]=$row3['quatity']; ?></td>
							<td style="width:100px;" class="jjrs_tabledata1"><?php echo $row2['App_quantity']-array_sum($po_qty);?></td>
						</tr>	
						<?php } ?>	
							<?php }?>			
					</table>
				</td>
			</tr>
			<?php } ?>
		</table>
	</td>	</tr>
	<?php } ?>	
</table>

</div>
<?php } ?>
	
</body>
</html>
