<?php $PageSecurity = 2;
include('includes/DefineSuppTransClass.php');
include('includes/DefinePOClass.php');
include('includes/SQL_CommonFunctions.inc');
include('includes/session.inc');
include('includes/function_lib.php');
include('includes/fun-HRClass.php');
/* Get opening balance */

$function_filter[]=$_REQUEST['site1'];
$function_filter[]=$_REQUEST['store'];
$data_date=explode('/',$_REQUEST['date']);
					     $date55=$data_date[2].'-'.$data_date[1].'-'.$data_date[0];
					      $date22= strtotime(date("Y-m-d", strtotime($date55)) . " +0 day");
                            $date1= date("Y-m-d",$date22);

$function_filter[]= $date1;
							
							function openingBalance($function_filter,$item)
							{
								global $db;
								
								$site=$function_filter[0];
								$store=$function_filter[1];
								$from=$function_filter[2];								
								if($site!='')
								$filter[]="site = '".$site."'";
								if($store!='')
								$filter[]="store = '".$store."'";
								if($from!='--' && $to!='--')
								$filter[]="reveive_date <= '".$from."'";
								if($item!='')
								$filter[]="itemcode = '".$item."'";
								
								$filter=implode(' and ',$filter);
								$sql1="Select stock, rate from opening_stockitemwise where $filter";
								$result1=DB_query($sql1,$db);
								$myrow1=DB_fetch_array($result1);
								return $myrow1['stock'];
							}
                      
					   

						
                   function receivedMRN($function_filter,$item)
						{
							global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							if($site!='')
							$filter[]="addmrn.site = '".$site."'";
							if($store!='')
							$filter[]="addmrn.StoreCode = '".$store."'";
							if($item!='')
							$filter[]="addmrnqty.mst_itemid = '".$item."'";
							if($from!='')
							$filter[]="addmrn.MRNDate <= '".$from."'";
							
							$filter=implode(' and ',$filter);
							  $sql1="Select SUM(addmrnqty.reciveqty),sum(addmrnqty.item_total_rate) as total
							  from addmrn left join addmrnqty 
							       on(addmrn.id=addmrnqty.issue_id) where Cancel_status='N' and $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							  $return_array[]=$myrow1['SUM(addmrnqty.reciveqty)'];
							  if($myrow1['total']!='' && $myrow1['SUM(addmrnqty.reciveqty)']!=0 )
							  $rate1=$myrow1['total']/$myrow1['SUM(addmrnqty.reciveqty)'];
							  else
							  $rate1=0;
							   $rate=round($rate1,2);
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
							  return $return_array;
						}
						

							
			               function issueSlip($function_filter,$item)
							{
								global $db;
								$site=$function_filter[0];
								$store=$function_filter[1];
								$from=$function_filter[2];
								if($site!='')
								$filter[]="addissue.site = '".$site."'";
								if($store!='')
								$filter[]="addissue.mst_storeid = '".$store."'";
								if($item!='')
								$filter[]="issueqty.mst_itemid = '".$item."'";
								
								
								$filter=implode(' and ',$filter);
							
								 $sql1="Select SUM(issueqty.issueqty) from addissue left join 
								 issueqty on(addissue.id=issueqty.issue_id) where $filter";
								$result1=DB_query($sql1,$db);
								$myrow1=DB_fetch_array($result1);
								
								return $myrow1['SUM(issueqty.issueqty)'];
							}




				function receivedReturn($function_filter,$item)
				{
					global $db;
					$site=$function_filter[0];
					$store=$function_filter[1];
					$from=$function_filter[2];
					$to=$function_filter[3];
					
					if($site!='')
					$filter[]="material_return.site = '".$site."'";
					if($store!='')
					$filter[]="material_return.store = '".$store."'";
					if($from!='--' && $to!='--')
					$filter[]="material_return.returndate <='".$from."' ";
					if($item!='')
					$filter[]="material_return_details.itemid = '".$item."'";
					
					$filter=implode(' and ',$filter);
					   $sql1="Select SUM(material_return_details.receiveqty) 
					  from material_return left join material_return_details    
					  on(material_return.id=material_return_details.parentId) where $filter";
				 
					
					$result1=DB_query($sql1,$db);
					$myrow1=DB_fetch_array($result1);
					return $myrow1['SUM(material_return_details.receiveqty)'];
				}



				function receivedSiteToSite($function_filter,$item)
				{
					global $db;
					$site=$function_filter[0];
					$store=$function_filter[1];
					$from=$function_filter[2];
					$to=$function_filter[3];
					
					if($site!='')
					$filter[]="transfer_receipt.Received_to = '".$site."'";
					if($store!='')
					$filter[]="transfer_receipt.Received_store = '".$store."'";
					if($from!='--' && $to!='--')
					$filter[]="transfer_receipt.MRNDate <='".$from."' ";
					if($item!='')
					$filter[]="transfer_receipt_details.itemid = '".$item."'";
					
					$filter=implode(' and ',$filter);
					
					 $sql1="Select SUM(transfer_receipt_details.reciveqty)
					  from transfer_receipt left join transfer_receipt_details 
					  on(transfer_receipt.id=transfer_receipt_details.transfer_receipt_id) where $filter";
					$result1=DB_query($sql1,$db);
					$myrow1=DB_fetch_array($result1);
					return $myrow1['SUM(transfer_receipt_details.reciveqty)'];
				}

				function issueSiteToSite($function_filter,$item)
				{
					global $db;
				 
					$site=$function_filter[0];
					$store=$function_filter[1];
					$from=$function_filter[2];
					$to=$function_filter[3];
					
					if($site!='')
					$filter[]="transfer_issue.sitecode = '".$site."'";
					if($store!='')
					$filter[]="transfer_issue.fromstore = '".$store."'";
					if($from!='--' && $to!='--')
					$filter[]="transfer_issue.issuedate <='".$from."' ";
					if($item!='')
					$filter[]="transfer_issue_details.itemid = '".$item."'";
					
					$filter=implode(' and ',$filter);
						  $sql1="Select SUM(transfer_issue_details.issueqty)  from
						  transfer_issue left join transfer_issue_details  
						  on(transfer_issue.id=transfer_issue_details.transfer_issue_id) where $filter";
					$result1=DB_query($sql1,$db);
					$myrow1=DB_fetch_array($result1);
					$return_array=$myrow1['SUM(transfer_issue_details.issueqty)'];
					//$avg_rate=$myrow1['SUM(transfer_issue_details.rate)'];
					//$return_array[]=$avg_rate;
					return $return_array;
					
				}
					function issueMatarialReturn($function_filter,$item)
					{
						global $db;
						$site=$function_filter[0];
						$store=$function_filter[1];
						$from=$function_filter[2];
						$to=$function_filter[3];						
						if($site!='')
						$filter[]="inv_material_return.sitecode = '".$site."'";
						if($store!='')
						$filter[]="inv_material_return.storecode = '".$store."'";
						if($from!='--' && $to!='--')
						$filter[]="inv_material_return.MRNDate <='".$from."' ";
						if($item!='')
						$filter[]="inv_material_return_details.itemid = '".$item."'";
						
						$filter=implode(' and ',$filter);
						
					  $sql1="Select SUM(inv_material_return_details.ReturnQty) 
					  from inv_material_return left join inv_material_return_details   
					   on(inv_material_return.id=inv_material_return_details.parentId) where $filter";
						$result1=DB_query($sql1,$db);
						$myrow1=DB_fetch_array($result1);
						return $myrow1['SUM(inv_material_return_details.ReturnQty)'];
					}

	 function Rgpchallan($function_filter,$item)
					  {
					  
					      global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[2];
							
							if($site!='')
							$filter[]="rgp_tab.branch = '".$site."'";
							if($store!='')
							$filter[]="rgp_tab.store = '".$store."'";
							if($from!='--' && $to!='--')
							$filter[]="rgp_tab.date <= '".$from."'";
							if($item!='')
							$filter[]="rgp_tab_details.mst_itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							  $sql1="Select SUM(rgp_tab_details.qty) 
							  from rgp_tab left join rgp_tab_details 
							  on(rgp_tab.id=rgp_tab_details.parent_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							return $myrow1['SUM(rgp_tab_details.qty)'];
					  
					  
					  		
					  }	
					   function Rgpreturn($function_filter,$item)
					    {
					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[2];
							
							if($site!='')
							$filter[]="rgp_return.branch = '".$site."'";
							if($store!='')
							$filter[]="rgp_return.store = '".$store."'";
							if($from!='--' && $to!='--')
							$filter[]="rgp_return.return_date <= '".$from."' ";
							if($item!='')
							$filter[]="rgp_return_details.item_id = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							  $sql1="Select SUM(rgp_return_details.qty) 
							  from rgp_return left join rgp_return_details 
							  on(rgp_return.id=rgp_return_details.parent_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							return $myrow1['SUM(rgp_return_details.qty)'];
					  
					  
					  		
					  }	
					  
					  function Nrgp($function_filter,$item)
					    {					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[2];
							
							if($site!='')
							$filter[]="nrgp_challan.branch = '".$site."'";
							if($store!='')
							$filter[]="nrgp_challan.store = '".$store."'";
							if($from!='--' && $to!='--')
							$filter[]="nrgp_challan.date <= '".$from."' ";
							if($item!='')
							$filter[]="nrgp_challan_details.item_code = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							  $sql1="Select SUM(nrgp_challan_details.qty) 
							  from nrgp_challan left join nrgp_challan_details 
							  on(nrgp_challan.id=nrgp_challan_details.parant_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							return $myrow1['SUM(nrgp_challan_details.qty)'];
					  
					  
					  		
					  }	

  ?> 

	<div align="center">
  <div style="overflow:auto; width:600px; height:250px">
  <p align="right" style="padding-left:0px; padding-bottom:0px; margin:0px;"><a href="#" onclick="hidedata();" style="font-size:18px; font-weight:bold;border:#000000 solid 2px; padding:2px; text-decoration:none; background-color:#FFFFFF; color:#000000;" title="Close">X</a></p>
  <table width="100%" border="1" cellspacing="0" cellpadding="3" style="border-collapse:collapse;">
 <tr>
        <th><strong>Select</strong></th>
        <th><strong>Item Code</strong></th>
        <th><strong>Item Name </strong></th>
		<th><strong>UOM </strong></th>		
		<th><strong>Stock Balance  </strong></th>
		</tr> 
		<?php   
	    $i=0;	 
	    $store_sql = "SELECT id,StockID,item_name,mst_unitid FROM item_master  where item_name like '%".$_REQUEST['site']."%'";
        $Result=DB_query($store_sql,$db);
	  while($myrow =DB_fetch_array($Result))
	  {  	 		
		 $item_name= $s->GetFieldValue('item_master'," StockID='".$myrow['StockID']."'" ,'item_name');
		 $parameter=$myrow['id'];
		 $unitname= $s->GetFieldValue('unitsofmeasure',"unitid='".$myrow['mst_unitid']."'",'unitname');
         
		
		
		  
		    $op=openingBalance($function_filter,$myrow['id']);
            //  echo "openingBalance"."<br>";
 		      $in_1=receivedMRN($function_filter,$myrow['id']);
          //echo "receivedMRN"."<br>";
 		    $out_1=issueSlip($function_filter,$myrow['id']);
           // echo "issueSlip"."<br>";
		   $out_2=issueSiteToSite($function_filter,$myrow['id']);		
          // echo "issuesitetosite"."<br>";
		   $in_2=receivedSiteToSite($function_filter,$myrow['id']);
		// echo " receivesitetosite"."<br>";
		  $out_3=issueMatarialReturn($function_filter,$myrow['id']);
		 
		   $out_4=Rgpchallan($function_filter,$myrow['id']);
		   $out_5=Nrgp($function_filter,$myrow['id']);
		  $in_3=receivedReturn($function_filter,$myrow['id']);		 
		  //$in_4=return_rejection($function_filter,$myrow['id']);
		  $in_4=Rgpreturn($function_filter,$myrow['id']);
		  $invalue=($op+$in_1[0]+$in_2+$in_3+$in_4);
		   $outvalue=($out_1+$out_2+$out_3+$out_4+$out_5);
		  $tot=trim($invalue)-trim($outvalue);
		
		
		 $total_current=$tot;
		
		
		$outward_rate=$in_1[1];
		 
		
 	  ?> 	  
	   <tr bgcolor="#FFFFFF">
        <td ><?php //echo $myrow['id'];?>
       <a href="javascript:getItemsList1('<?php echo $parameter;?>','<?php echo $myrow['StockID'];?>','<?php echo  $myrow['item_name'];?>','<?php echo $unitname;?>','<?php echo $total_current;?>','<?php echo $outward_rate; ?>','<?php echo $_REQUEST['data'];?>')"><?php echo $parameter;?></a>	
		<!--<input type="checkbox" name="item_issue[<?php echo $i;?>]" id="row_id_<?php echo $i;?>"  value="<?php echo $parameter;?>" onclick="getItemsList('<?php echo $parameter;?>','<?php echo $myrow['StockID'];?>','<?php echo  $myrow['item_name'];?>','<?php echo $myrow['unit'];?>','<?php echo $unitname;?>','<?php echo $stock;?>','<?php echo $i;?>')"/>--> </td>
        <td > <?php echo $myrow['StockID'];?> </td>
        <td ><?php echo $item_name;?></td>		
		<td ><?php  echo $s->GetFieldValue('unitsofmeasure',"unitid='".$myrow['unit']."'",'unitname');
		?></td>
	     <td> <?php  echo $total_current;
		 ?>	
	    		</tr>
	  <?php $i++; } ?>	
		
	</table>
	</div></div>
	
