<?php
error_reporting(0);
?>
<script type="text/javascript">
function checkval(){
	var idsite = document.getElementById("dealer").value;
	var idstore = document.getElementById("dealerstore").value;
	var fromdate = document.getElementById("from_date").value;
	var todate = document.getElementById("to_date").value;
	var maincat = document.getElementById("maincat").value;
	if(idsite=='0'){
		alert("Select Dealer Name First.");
		return false;
	}
		
	else if(fromdate=='' && todate=='')
	{
		alert("Select From - To date.");
		return false;
	}
	else if(maincat=='0' && idstore=='0')
	{
	alert("Select Main category First.");
		return false;
	}
	return true;
}
</script>
<link href="css/purchses.css" rel="stylesheet" type="text/css" />
<h3 style="font-size:18px;">Stock Summary</h3>
<form method="post" action="">

<table width="90%" border="1" cellspacing="5" cellpadding="3" style="border-collapse:collapse">
	<tr>
    	<th class="tabledata1">Dealer<span style="color:#FF0000">*</span></th>
        <td>
		<select name="dealer" id="dealer" onchange="showsitename160(this.value)">
   	<?php
if(!empty($_SESSION['dealerid']))
	{	  	
    $ss1="Select * from dbl_dealer  where company='".$_SESSION['ACGCompanyId']."'
        and id='".$_SESSION['dealerid']."'";
    $site_sql1=DB_query($ss1,$db);
    }
   else
   {
   ?>
    <option value="0">Select Site</option>
   <?php 
   $ss1="Select * from dbl_dealer  where company='".$_SESSION['ACGCompanyId']."'";
   $site_sql1=DB_query($ss1,$db);
   }
   ?>
	
     <?php 	while($site_row = DB_fetch_array($site_sql1))
	  {	$p='';
    if($site_row['id'] == $_POST['dealer'])	
	$p="selected";	?>
 <option value="<?php echo $site_row['id']; ?>" <?php echo $p;?>><?php echo $site_row['dealer_name']; ?></option><?php } ?>
  </select>
		</td>
        <th class="tabledata1">Store<span style="color:#FF0000">*</span></th>
        <td>
			<div id="storedata7">
		     	<select name="dealerstore" id="dealerstore" >
				<?php 
		  if(!empty($_SESSION['dealerid']))
	       {
		    $store_sql1 = "SELECT * FROM dbl_dealer_store where dealer = '".$_SESSION['dealerid']."'";
		   }
		   else
		   {
		    $store_sql1 = "SELECT * FROM dbl_dealer_store where dealer = '".$_POST['dealer']."'";
		   }	              
	 		
	 			$store_result1 = DB_query($store_sql1,$db);
	 			while($store_row1 = DB_fetch_array($store_result1))
				{
				
				$p1='';
				if($store_row1['id']==$_REQUEST['dealerstore'])
				$p1='selected="selected"';
	 			?>
         <option value="<?php echo $store_row1['id'] ?>"<?php echo $p1; ?>><?php echo $store_row1['dealer_store_name']; ?></option>
                <?php } ?>
                </select></div> 
		</td>
		<th class="tabledata1">Enter Part Name Of Item</th>
		<td>
			<input type="text" name="itemName" value="" onkeyup="showItems(this.value)"   style="width:100%" /><br /><br />
		</td>
	</tr>
	<tr><th class="tabledata1">Main Category</th>
	<td><select name="maincat" id="maincat" onchange="showsubcat(this.value)">
	                <?php 
	 			$store_sql = "SELECT * FROM stockmaincategory  where CompanyId='".$_SESSION['ACGCompanyId']."'";
	 			$store_result = DB_query($store_sql,$db);
	 			while($store_row = DB_fetch_array($store_result))
				{
					$p='';
					if($store_row['id'] == $_REQUEST['maincat'] || $store_row['id']==3)
						$p="selected";
	 			?>
                <option value="<?php echo $store_row['id'] ?>"<?php echo $p; ?>><?php echo $store_row['stockmaincategoryname']?></option>
                <?php } ?>
                </select></td>
	<th class="tabledata1">Sub Category</th>
	<td><div id="subcatdata">
	<?php 	  
	 $store_sql = "SELECT * FROM stockcategory  where stockmaincategory = '12'";
	 ?>
	<select name="subcat"  style="width:107px;" id="subcat" onchange="showdata(this.value)">
	
			<?php
 			$store_result = DB_query($store_sql,$db);
			while($store_row = DB_fetch_array($store_result))
			{
				;
				$p='';
				if($store_row['id'] == $_REQUEST['subcat'])
				$p="selected";
			?>
			
	<option value="<?php echo $store_row['id'] ; ?>" <?php echo $p ; ?>><?php echo $store_row['categorydescription']; ?></option>
			 <?php }?>
			
		
	
              
                </select></div>
	
	</td>
	</tr>
	
	<tr>
        <th class="tabledata1">Date from :</th>
        <td><input type="text" name="from_date" id="from_date" value="<?php echo $_REQUEST['from_date'];?>"  class="datepicker"></td>
        <th class="tabledata1">Date to : </th>
        <td><input type="text" name="to_date" id="to_date" value="<?php echo $_POST['to_date'];?>"  class="datepicker"></td>
		<td colspan="2" class="tabledata1">
			<strong>Select Item</strong><br />
			<div id="itemid">
                <div style="overflow:auto; height:100px; width:auto; border:1px solid;" >
                  <?php 
		$item_sql=DB_query("select id,StockID,item_name from item_master where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
					$c3=0;
					while($row3=DB_fetch_array($item_sql))
					{ 
						$c='';
						if($_REQUEST['item'][$c3]==$row3['id'])
							{$c="checked";}
					?>
                  <input type="checkbox" name="item[<?php echo $c3;?>]" id="item_<?php echo $c3;?>" value="<?php echo $row3['id'];?>" <?php echo $c;?>/>
                  <?php echo $row3['StockID']." - ".$row3['item_name'];?><br />
                  <?php 
		
					$c3++;
					}
					if($_REQUEST['view'] && $_REQUEST['item']){
						$itemString[]=$_REQUEST['item'];
						$a=implode(",", $_REQUEST['item']); 
					}	 
		
				?>
                </div>
              </div>
		</td>
    </tr>
</table>
		 
      
	    <p align="center">
          <input type="submit" name="view" value="View" onclick="return checkval()">
          
        </p>
		
</form>


<?php  if($_REQUEST['view']!=''){?>
<p align="right">
<!--<a href="#" onclick="javascript:popup('stock_summary_print.php?site=<?php //echo $_REQUEST['dealer'];?>&store_code=<?php //echo $_REQUEST['dealerstorer']?>&fromdate=<?php //echo $_REQUEST['from_date']?>&todate=<?php //echo $_REQUEST['to_date']?>',1000,1000)"><input type="button" name="print" value="Print" ></a>--></p>
<div style="clear:both;"></div>
<?php } ?>

 <?php
     if(isset($_POST['view']))
       {
	 	            
					 $from_date=$_REQUEST['from_date'];
					$to_date=$_REQUEST['to_date'];	
					$from_date_brk=explode('/',$from_date);
					$from=$from_date_brk[2]."-".$from_date_brk[1]."-".$from_date_brk[0];
				
					$to_date_brk=explode('/',$to_date);
					$to=$to_date_brk[2]."-".$to_date_brk[1]."-".$to_date_brk[0];
				
					$site=$_REQUEST['dealer'];
					
					if($_REQUEST['dealerstore']=='0')
					{
					 $store12=array();
					 $store_sql = "SELECT id FROM dbl_dealer_store where dealer = '".$_REQUEST['dealer']."'";
					 $store_sql1=DB_query($store_sql,$db);
					 while($st_rows=DB_fetch_array($store_sql1))
					 {
					 $store12[]=$st_rows['id'];
					 }
					 $store=implode("','",$store12);
					 }
					else
					$store=$_REQUEST['dealerstore'];
					if($_REQUEST['maincat']!='0')
					$filter2[]="CategoryID='".$_REQUEST['maincat']."'";
					if($_REQUEST['subcat']!='0')
					$filter2[]="SubCategoryID='".$_REQUEST['subcat']."'";
					
					 $item=$_REQUEST['item'];	
					 //print_r($item);				
					if($item!=''){
					$item_string=implode("','",$item);
					$filter2[]="id in('".$item_string."')";
					
					 }
					 if($filter2!='')
					 {
					
					 $filter4=implode(' and ',$filter2);
					 }
					 $filter4;
					$function_filter[]=$site;
					$function_filter[]=$store;	
					$function_filter[]=$from;
					$function_filter[]=$to;
					
					
	               
	
	function privious_opening($function_filter,$item)
	{
	 global $db;
	 $receive=array();
	 $total=array();
	  $total1=array();
	 $issueqty=array();	
	 $site=$function_filter[0];
	 $store=$function_filter[1];
	 $fdate1= strtotime(date("Y-m-d", strtotime($function_filter[2])) . " -1 day");
	   $fdate= date("Y-m-d",$fdate1);
	/**************************************  MRN *********************************************/
	
	
	$sqlmrn=DB_query("select sum(reciveqty) as reciveqty,sum(item_total_rate) as total from dbl_addmrnqty 
	inner join dbl_addmrn on dbl_addmrn.id=dbl_addmrnqty.issue_id  where site='".$site."'
	 and StoreCode in ('".$store."')
	  and MRNDate <='".$fdate."' and Cancel_status='N' and reciveqty!='0.00' 
	 and mst_itemid in ('".$item."')",$db);	
	 
	while($addmrn=DB_fetch_array($sqlmrn))
	{
	if($addmrn['reciveqty']!='')
	{
	$receive[]=$addmrn['reciveqty'];
	$total[]=$addmrn['total'];
	}
	}
		
	   
	/**************************************  Issue *********************************************/

	$sqlissue=DB_query("SELECT sum(issueqty) AS issueqty,sum(totalamt) as total FROM 
                        issueqty INNER JOIN addissue ON addissue.id=issueqty.issue_id where site='".$site."' and
						mst_storeid in ('".$store."')  and issueqty!='0.00' 
						and issuedate <='".$fdate."' and mst_itemid in ('".$item."') ",$db);	
	while($addissue=DB_fetch_array($sqlissue))
	{
	if($addissue['issueqty']!='')
	{
	$issueqty[]=$addissue['issueqty'];
	$total1[]=$addissue['total'];
	}
	}			
	/**************************************  Receive *********************************************/
	$sqlreceive=DB_query("SELECT sum(receiveqty) AS reciveqty,sum(totalamt) as total
	                    FROM  material_return_details INNER JOIN material_return 
				        ON material_return.id=material_return_details.parentId where site='".$site."' and
						store in ('".$store."') and returndate <='".$fdate."' and  receiveqty!='0.00'
						and itemid in ('".$item."')",$db);
	while($addreceive=DB_fetch_array($sqlreceive))
	{
	if($addreceive['reciveqty']!='')
	{
	$receive[]=$addreceive['reciveqty'];
	$total[]=$addreceive['total'];
	}
	}	
	/**************************************  site to site *********************************************/
	$sqlsite_site=DB_query("SELECT sum(issueqty) AS issueqty,sum(totalamount) as total
	                     FROM  transfer_issue_details INNER JOIN transfer_issue 
				        ON transfer_issue.id=transfer_issue_details.transfer_issue_id where sitecode='".$site."' and
						fromstore in ('".$store."') and issuedate <= '".$fdate."'  and issueqty!='0.00'
						and itemid in ('".$item."')",$db);
	while($addsitetosite=DB_fetch_array($sqlsite_site))
	{
	if($addsitetosite['issueqty']!='')
	{
	$issueqty[]=$addsitetosite['issueqty'];
	$total1[]=$addsitetosite['total'];
	}
	}
	/**************************************  site to site receive *********************************************/
	$sqlsite_site1=DB_query("SELECT sum(reciveqty) AS reciveqty,sum(totalamt) as total
	                      FROM  transfer_receipt_details INNER JOIN transfer_receipt 
				        ON transfer_receipt.id=transfer_receipt_details.transfer_receipt_id
						 where Received_to='".$site."' and
						Received_store in ('".$store."') and MRNDate <= '".$fdate."' and reciveqty!='0.00'
						and itemid in ('".$item."')",$db);
	while($addsitetositereceive=DB_fetch_array($sqlsite_site1))
	{
	if($addsitetositereceive['reciveqty']!='')
	{
	$receive[]=$addsitetositereceive['reciveqty'];
	$total[]=$addsitetositereceive['total'];
	}
	}
	/**************************************  Material Return *********************************************/
	$sqlsite_site1=DB_query("SELECT sum(ReturnQty) AS issueqty,sum(TotalValue) as total
	                    FROM  inv_material_return_details INNER JOIN inv_material_return 
				        ON inv_material_return.id=inv_material_return_details.parentId where sitecode='".$site."' and
						storecode in ('".$store."') and Reqdate <= '".$fdate."' and ReturnQty!='0.00'
						and itemid in ('".$item."')",$db);
	while($addmrtov=DB_fetch_array($sqlsite_site1))
	{
	if($addmrtov['issueqty']!='')
	{
	$issueqty[]=$addmrtov['issueqty'];
	$total1[]=$addmrtov['total'];
	}
	
	}	
	
	/*********************************************************************************************************/
	
	$sqlinvoice1=DB_query("SELECT sum(Quantity) AS issueqty,sum(Amount) as total
	                    FROM  invoice_details INNER JOIN invoice 
				        ON invoice.invoice_id=invoice_details.invoiceid where branch_name='".$site."' and
						store in ('".$store."') and invoiceDate <= '".$fdate."' 
						and ItemCode in ('".$item."')",$db);
	while($addinvoice=DB_fetch_array($sqlinvoice1))
	{
	if($addinvoice['issueqty']!='')
	{
	$issueqty[]=$addinvoice['issueqty'];
	$total1[]=$addmrtov['total'];
	}
	
	}	
	
	
	
	$sqldispaatch1=DB_query("SELECT sum(dispatch_qty) AS issueqty,sum(sub_amt) as total
	                    FROM  dbl_dealer_dispatch INNER JOIN dbl_dealer_dispatch_details 
				        ON dbl_dealer_dispatch.id=dbl_dealer_dispatch_details.parentId where dealer='".$site."' and
						dblstore in ('".$store."') and date <= '".$fdate."' 
						and mst_itemid in ('".$item."')",$db);
	while($adddispatch1=DB_fetch_array($sqldispaatch1))
	{
	if($adddispatch1['issueqty']!='')
	{
	$issueqty[]=$adddispatch1['issueqty'];
	$total1[]=$adddispatch1['total'];
	}
	
	}
	
	$sqldispaatch1=DB_query("SELECT sum(return_qty) AS reciveqty,sum(rate) as total
	                    FROM  dbl_sale_return INNER JOIN dbl_sale_return_details 
				        ON dbl_sale_return.id=dbl_sale_return_details.parant_id where dealer='".$site."' and
						dblstore in ('".$store."') and form_date <= '".$fdate."' 
						and mst_itemid in ('".$item."')",$db);
	while($adddispatch1=DB_fetch_array($sqldispaatch1))
	{
	if($adddispatch1['reciveqty']!='')
	{
	$receive[]=$adddispatch1['reciveqty'];
	$total[]=$adddispatch1['total'];
	}
	
	
	
	
	
	}
	
	
	
	
	
	
	
	
	
	/*******************************************  Opening ******************************************************/
	
	/*echo "select reveive_date as date,itemcode as itemid,stock as reciveqty,amount
	from opening_stockitemwise  where site='".$site."'
	 and store in ('".$store."') and reveive_date <='".$function_filter[3]."' 
	  and itemcode in ('".$item."')";*/
	
	$sqlopen=DB_query("select reveive_date as date,itemcode as itemid,stock as reciveqty,amount
	from opening_stockitemwise  where site='".$site."'
	 and store in ('".$store."') and reveive_date <='".$function_filter[3]."' 
	  and itemcode in ('".$item."')",$db);
	while($addopen=DB_fetch_array($sqlopen))
	{
	if($addopen['reciveqty']!='')
	{
	$receive[]=$addopen['reciveqty'];	
	$total[]=$addopen['amount'];
	}
	}
	//echo array_sum($receive);
	$qty=array_sum($receive)-array_sum($issueqty);
	$amount=array_sum($total)-array_sum($total1);	
	$rate1=$amount/$qty;   
	$rate=round($rate1);
	$return_array[]=$qty;
	$return_array[]=$rate;
	$return_array[]=$amount;
	return $return_array;   
	}
	
	
	                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 in ('".$store."')";
								if($from!='--' && $to!='--')
								$filter[]="reveive_date <= '".$from."'";
								if($item!='')
								$filter[]="itemcode = '".$item."'";
								
								$filter=implode(' and ',$filter);
								$sql1="Select stock, rate,amount from opening_stockitemwise where $filter";
							
								$result1=DB_query($sql1,$db);
								$myrow1=DB_fetch_array($result1);
								$return_array[]=$myrow1['stock'];
								$return_array[]=$myrow1['rate'];
								$return_array[]=$myrow1['amount'];
															
								return $return_array;
							}
                     
                    function receivedMRN($function_filter,$item)
						{
							global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							 $from=$function_filter[2];
					        $to=$function_filter[3];
							
							if($site!='')
							$filter[]="dbl_addmrn.site = '".$site."'";
							if($store!='')
							$filter[]="dbl_addmrn.StoreCode in ('".$store."')";
							if($from!='--' && $to!='--')
					        $filter[]="dbl_addmrn.MRNDate between '".$from."' and '".$to."'";
							if($item!='')
							$filter[]="dbl_addmrnqty.mst_itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
						      	$sql1="Select SUM(dbl_addmrnqty.reciveqty),sum(item_total_rate) as total
								 from dbl_addmrn left join dbl_addmrnqty 
							     on(dbl_addmrn.id=dbl_addmrnqty.issue_id) where Cancel_Status='N' 
								 and reciveqty!='0.00'  and $filter";
								 
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							  $rate1=$myrow1['total']/$myrow1['SUM(dbl_addmrnqty.reciveqty)'];
							   $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(dbl_addmrnqty.reciveqty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
							
							
						}
						

							////1. Issue Slip/////
                       function issueSlip($function_filter,$item)
							{
								global $db;
								$site=$function_filter[0];
								$store=$function_filter[1];
								$from=$function_filter[2];
								 $to=$function_filter[3];
								if($site!='')
								$filter[]="addissue.site = '".$site."'";
								if($store!='')
								$filter[]="addissue.mst_storeid in ('".$store."')";
								if($item!='')
								$filter[]="issueqty.mst_itemid = '".$item."'";
								if($from!='--' && $to!='--')
					          $filter[]="addissue.issuedate between '".$from."' and '".$to."'";
								
								$filter=implode(' and ',$filter);
							
							    	$sql1="Select SUM(issueqty.issueqty),sum(totalamt) as total from addissue left join 
								 issueqty on(addissue.id=issueqty.issue_id) where  issueqty!='0.00' and $filter";
								
								$result1=DB_query($sql1,$db);
								$myrow1=DB_fetch_array($result1);
								
								
								
								$rate1=$myrow1['total']/$myrow1['SUM(issueqty.issueqty)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(issueqty.issueqty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
								
								
								
							}

				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 in ('".$store."')";
					if($from!='--' && $to!='--')
					$filter[]="material_return.returndate between '".$from."' and '".$to."'";
					if($item!='')
					$filter[]="material_return_details.itemid = '".$item."'";
					
					$filter=implode(' and ',$filter);
					  $sql1="Select SUM(material_return_details.receiveqty),sum(totalamt) as total
					   from material_return left join material_return_details   
					   on(material_return.id=material_return_details.parentId) where receiveqty!='0.00' and $filter";
				 
					
					$result1=DB_query($sql1,$db);
					$myrow1=DB_fetch_array($result1);
				
					
					
					       $rate1=$myrow1['total']/$myrow1['SUM(material_return_details.receiveqty)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(material_return_details.receiveqty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
					
					
				}



				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 in ('".$store."')";
					if($from!='--' && $to!='--')
					$filter[]="transfer_receipt.MRNDate between '".$from."' and '".$to."'";
					if($item!='')
					$filter[]="transfer_receipt_details.itemid = '".$item."'";
					
					$filter=implode(' and ',$filter);
					
					 $sql1="Select SUM(transfer_receipt_details.reciveqty) ,sum(totalamt) as total
					 from transfer_receipt left join transfer_receipt_details 
					 on(transfer_receipt.id=transfer_receipt_details.transfer_receipt_id) where
					 reciveqty!='0.00' and
					  $filter";
					$result1=DB_query($sql1,$db);
					$myrow1=DB_fetch_array($result1);
					
					
					
					 $rate1=$myrow1['total']/$myrow1['SUM(transfer_receipt_details.reciveqty)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(transfer_receipt_details.reciveqty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
					
					
				}

						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 in ('".$store."')";
							if($from!='--' && $to!='--')
							$filter[]="transfer_issue.issuedate between '".$from."' and '".$to."'";
							if($item!='')
							$filter[]="transfer_issue_details.itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
								 $sql1="Select SUM(transfer_issue_details.issueqty) ,sum(totalamount) as total 
								 from transfer_issue left join transfer_issue_details 
								 on(transfer_issue.id=transfer_issue_details.transfer_issue_id) where 
								  issueqty!='0.00' and
								 $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							//$return_array=$myrow1['SUM(transfer_issue_details.issueqty)'];
							
							 $rate1=$myrow1['total']/$myrow1['SUM(transfer_issue_details.issueqty)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(transfer_issue_details.issueqty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
							
							
							//$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 in ('".$store."')";
						if($from!='--' && $to!='--')
						$filter[]="inv_material_return.Reqdate between '".$from."' and '".$to."'";
						if($item!='')
						$filter[]="inv_material_return_details.itemid = '".$item."'";
						
						$filter=implode(' and ',$filter);
						
					    $sql1="Select SUM(inv_material_return_details.ReturnQty) ,sum(TotalValue) as total
					    from inv_material_return left join inv_material_return_details  
					    on(inv_material_return.id=inv_material_return_details.parentId) where
						 ReturnQty!='0.00' and
						 $filter";
						$result1=DB_query($sql1,$db);
						$myrow1=DB_fetch_array($result1);
						
						$rate1=$myrow1['total']/$myrow1['SUM(inv_material_return_details.ReturnQty)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(inv_material_return_details.ReturnQty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
							
						
						
						
					}
					
					
					
					
					function Rgpchallan($function_filter,$item)
					  {
					  
					      global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[3];
							
							if($site!='')
							$filter[]="rgp_tab.branch = '".$site."'";
							if($store!='')
							$filter[]="rgp_tab.store in ('".$store."')";
							if($from!='--' && $to!='--')
							$filter[]="rgp_tab.date between '".$from."' and '".$to."'";
							if($item!='')
							$filter[]="rgp_tab_details.mst_itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							  $sql1="Select SUM(rgp_tab_details.qty) ,sum(jobvalue) as total
							  from rgp_tab left join rgp_tab_details 
							  on(rgp_tab.id=rgp_tab_details.parent_id) where
							   qty!='0.00' and
							   $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							
							
							$rate1=$myrow1['total']/$myrow1['SUM(rgp_tab_details.qty)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(rgp_tab_details.qty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
					  
					  
					  		
					  }	
					   function Rgpreturn($function_filter,$item)
					    {
					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[3];
							
							if($site!='')
							$filter[]="rgp_return.branch = '".$site."'";
							if($store!='')
							$filter[]="rgp_return.store in ('".$store."')";
							if($from!='--' && $to!='--')
							$filter[]="rgp_return.return_date between '".$from."' and '".$to."'";
							if($item!='')
							$filter[]="rgp_return_details.item_id = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							   $sql1="Select SUM(rgp_return_details.qty) ,sum(job_value) as total
							  from rgp_return left join rgp_return_details 
							  on(rgp_return.id=rgp_return_details.parent_id) where
							   qty!='0.00' and
							   $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							
							$rate1=$myrow1['total']/$myrow1['SUM(rgp_return_details.qty)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(rgp_return_details.qty)'];
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
															
								return $return_array;
							
							
							
					  
					  
					  		
					  }	
					  
					  function Nrgp($function_filter,$item)
					    {	
									  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[3];
							
							if($site!='')
							$filter[]="nrgp_challan.branch = '".$site."'";
							if($store!='')
							$filter[]="nrgp_challan.store in ('".$store."')";
							if($from!='--' && $to!='--')
							$filter[]="nrgp_challan.date between '".$from."' and '".$to."'";
							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 
							   qty!='0.00' and
							  $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							
							   $rate1=0;
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(nrgp_challan_details.qty)'];
								$return_array[]=$rate;
								$return_array[]=0;
															
								return $return_array;
							
							
							
					  
					  
					  		
					  }	
					  
					
					  
					  function invoice($function_filter,$item)
					    {					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[3];
							
							if($site!='')
							$filter[]="invoice.branch_name = '".$site."'";
							if($store!='')
							$filter[]="invoice.store in ('".$store."')";
							if($from!='--' && $to!='--')
							$filter[]="invoice.invoiceDate between '".$from."' and '".$to."'";
							if($item!='')
							$filter[]="invoice_details.ItemCode = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							   $sql1="Select SUM(invoice_details.Quantity) 
							  from invoice left join invoice_details 
							  on(invoice.invoice_id=invoice_details.invoiceid) where 
							 			  $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							
							   $rate1=$myrow1['Amount']/$myrow1['SUM(invoice_details.Quantity)'];
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(invoice_details.Quantity)'];
								$return_array[]=$rate;
								$return_array[]=0;
															
								return $return_array;
							
							
							
					  
					  
					  		
					  }	
					  
					  
					  
					 
					  
					  
					  
					  
					  
					  function dispatch($function_filter,$item)
					    {		
									  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[3];
							
							if($site!='')
							$filter[]="dbl_dealer_dispatch.dealer = '".$site."'";
							if($store!='')
							$filter[]="dbl_dealer_dispatch_details.dblstore in ('".$store."')";
							if($from!='--' && $to!='--')
							$filter[]="dbl_dealer_dispatch.date between '".$from."' and '".$to."'";
							if($item!='')
							$filter[]="dbl_dealer_dispatch_details.mst_itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							    $sql1="Select SUM(dbl_dealer_dispatch_details.dispatch_qty) 
							  from dbl_dealer_dispatch left join dbl_dealer_dispatch_details 
							  on(dbl_dealer_dispatch.id=dbl_dealer_dispatch_details.parentid) where 
							  $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							
							   $rate1=1;
							     $rate=round($rate1,2);
							    $return_array[]=$myrow1['SUM(dbl_dealer_dispatch_details.dispatch_qty)'];
								$return_array[]=$rate;
								$return_array[]=0;
															
								return $return_array;
							
							
							
					  
					  
					  		
					  }	
					   function dispatchreturn($function_filter,$item)
					    {					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[3];							
							if($site!='')
							$filter[]="dbl_sale_return.dealer = '".$site."'";
							if($store!='')
							$filter[]="dbl_sale_return_details.dblstore in('".$store."')";
							if($from!='--' && $to!='--')
							$filter[]="dbl_sale_return.form_date between '".$from."' and '".$to."'";
							if($item!='')
							$filter[]="dbl_sale_return_details.mst_itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							   $sql1="Select SUM(dbl_sale_return_details.return_qty) 
									from dbl_sale_return left join dbl_sale_return_details 
									on(dbl_sale_return.id=dbl_sale_return_details.parant_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							 $rate1=1;
							     $rate=round($rate1,2);
								  $return_array[]=$myrow1['SUM(dbl_sale_return_details.return_qty)'];
								$return_array[]=$rate;
								$return_array[]=0;
							return $return_array;
					  
					  
					  		
					  }	
					  
					  
					  
					  
					  
					  
					  
					  
					  
					



?>
<div>
 <?php
 $branch=$s->getField('dbl_dealer','id',$_REQUEST['dealer'],'dealer_name');
 if($_REQUEST['dealerstorer']!='0')
  $storedata="store  ".$s->getField('dbl_dealer_store','id',$_REQUEST['dealerstore'],'dealer_store_name');
  else
  $storedata="ALL Store";
  if($_REQUEST['maincat']!='0' && $_REQUEST['dealerstore']=='0')
  {
  $sub=$s->getField('stockcategory','id',$_REQUEST['subcat'],'categorydescription');
  $cat=$s->getField('stockmaincategory','id',$_REQUEST['maincat'],'stockmaincategoryname');
  }
  if($_REQUEST['maincat']!='0')
  {
  $message="Showing Records of  ".$storedata." of Dealer -- ".$branch."   
  Category -- ".$cat." -- and  SubCategory -- ".$sub." and 
  From  ".$from_date." to ".$to_date."";
  }
  else
  {
   $message="Showing Records of   ".$storedata."  of Dealer --  ".$branch."   
   From   ".$from_date." to ".$to_date."";
  }
 
  $_SESSION['mess1']=$message;
   ?>
  <p align="center"><strong><?php echo $message; ?></strong></p>

  <table width="100%" border="1" cellspacing="0" cellpadding="3">
    <tr>
    <th width="21%" rowspan="2">Item Name</th>
	<th width="10%" rowspan="2">UOM</th>
    <th width="17%" height="31"><center>Opening Balance
    </center></th>
    <th width="17%"><center>Received
    </center></th>
    <th width="17%"><center>Issued
    </center></th>
    <th><center>Closing Balance</center></th>
  </tr>
  <tr>
    <td style="margin:0px; padding:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="4">
  <tr>
    <th align="left">QTY.</th>
 <th align="left" >Rate</th>
    <th align="left" >Value</th>
  </tr>
</table>	</td>
    <td style="padding:0px; margin:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <th align="left">QTY.</th>
   <th align="left" >Rate</th>
    <th align="left">Value</th>
  </tr>
</table>	</td>
    <td style="padding:0px; margin:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <th align="left" >QTY.</th>
   <th align="left" >Rate</th>
    <th align="left">Value</th>
  </tr>
</table></td>
   <td style="padding:0px; margin:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <th>QTY.  </th>
   <th>Rate</th>
    <th>Value</th>
  </tr>
</table></td>
  </tr>
    <?php        
			                
  if($filter2!='')
  $sql1="select * from item_master where $filter4";
   else
   $sql1="select * from item_master where 1=1";

  $sql=DB_query($sql1,$db);
   $itemarray=array();
  while($row1=DB_fetch_array($sql))
  {
        $confac=$row1['conv_factor'];
       $privious_open=privious_opening($function_filter,$row1['id']);
	   $privious_stock= $privious_open[0];
	   $privious_rate= $privious_open[1];
	   $privious_amount= $privious_open[2];
     // $op=openingBalance($function_filter,$row1['id']);
	  $in_1=receivedMRN($function_filter,$row1['id']);
	  $out_1=issueSlip($function_filter,$row1['id']);
	  $out_2=issueSiteToSite($function_filter,$row1['id']);	
	  $in_2=receivedSiteToSite($function_filter,$row1['id']);
	  $out_3=issueMatarialReturn($function_filter,$row1['id']);
	  $in_3=receivedReturn($function_filter,$row1['id']);	
	  $out_4=Rgpchallan($function_filter,$row1['id']);
	  $out_5=Nrgp($function_filter,$row1['id']);
	  $in_4=Rgpreturn($function_filter,$row1['id']);
	  $out_6=invoice($function_filter,$row1['id']);
	  $out_7=dispatch($function_filter,$row1['id']);
	  $in_5=dispatchreturn($function_filter,$row1['id']);	   
	   $opening_stock=$privious_open[0];
	  $received_qty=($in_1[0]+$in_2[0]+$in_3[0]+$in_4[0]+$in_5[0]);
	   //$received_qty=($in_1[0]+$in_2[0]+$in_3[0]+$in_4[0]+$in_5[0]);             
	// $received_amount=($in_1[2]+$in_2[2]+$in_3[2]+$in_4[2]);
	  $received_amount=($in_1[2]+$in_2[2]+$in_3[2]+$in_4[2]+$in_5[2]);       
	  $received_rate=round($received_amount/$received_qty,2);
      $issueded_qty=($out_1[0]+$out_2[0]+$out_3[0]+$out_4[0]+$out_5[0]+$out_6[0]+$out_7[0]);
      $issueded_amount=($out_1[2]+$out_2[2]+$out_3[2]+$out_4[2]+$out_5[2]+$out_6[2]+$out_7[2]);
	  $issueded_rate=round($issueded_amount/$issueded_qty,2);
      $invalue=($opening_stock+$in_1[0]+$in_2[0]+$in_3[0]+$in_4[0]+$in_5[0]);
      $inamount=($privious_amount+$in_1[2]+$in_2[2]+$in_3[2]+$in_4[2]+$in_5[2]); 
	  $outvalue=($out_1[0]+$out_2[0]+$out_3[0]+$out_4[0]+$out_5[0]+$out_6[0]+$out_7[0]);
     $outamount=($out_1[2]+$out_2[2]+$out_3[2]+$out_4[2]+$out_5[2]+$out_6[2]+$out_7[2]);
     $totqty=$invalue-$outvalue;
	 $totaamount=$inamount-$outamount;
	 $totalrate=round($totaamount/$totqty,2);
  
  
  if($opening_stock!='' || $received_qty!='' || $issueded_qty!='' || $totqty!='')
  {
  $unit_id=$s->getField('item_master','id',$row1['id'],'mst_unitid');
  $itemarray[]=array('itemname'=>$s->getField('item_master','id',$row1['id'],'item_name'),
  'unit'=>$s->getField('unitsofmeasure','unitid',$unit_id,'unitname'),'itemid'=>$row1['id'],
	'opening_stock'=>$opening_stock,'privious_rate'=>$privious_rate,'privious_amount'=>$privious_amount,
	'received_qty'=>$received_qty,'received_rate'=>$received_rate,'received_amount'=>$received_amount,
	'issueded_qty'=>$issueded_qty,'issueded_rate'=>$issueded_rate,'issueded_amount'=>$issueded_amount,
	'totqty'=>$totqty,'totalrate'=>$totalrate,'totaamount'=>$totaamount);
	$_SESSION['itemarray']=$itemarray;
    //$_SESSION['confac']=$confac;
   ?>
    <tr>
  <td><font size="2"><strong><?php echo $s->getField('item_master','id',$row1['id'],'item_name');?></strong></font></td>
  <td><font size="2"><strong><?php $unit_id=$s->getField('item_master','id',$row1['id'],'mst_unitid'); echo $s->getField('unitsofmeasure','unitid',$unit_id,'unitname');?></strong></font></td>
    <td style="padding:0px; margin:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="4">
  	<tr>
    <td width="33%" align="left" style="border-right:#808080 1px solid;"><?php if($opening_stock=='') { echo "0"; } else {	echo $opening_stock; }?></td>
    <td width="30%" align="left" style="border-right:#808080 1px solid;"><?php if($privious_rate=='') { echo "0"; } else {  echo $privious_rate;}?></td>
    <td align="right" style="text-align:right;"><?php if($privious_amount=='') { echo "0"; } else { echo $privious_amount;}?></td>
  </tr>
</table>	</td>
    <td style="padding:0px; margin:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="4">
		<tr>
    <td width="33%" align="left" style="border-right:#808080 1px solid;"><?php if($received_qty=='') { echo "0"; } else {	echo $received_qty; }?></td>
   <td width="30%" align="left" style="border-right:#808080 1px solid;"><?php  if($received_rate=='') { echo "0"; } else {  echo $received_rate;}?></td>
    <td align="left" style="text-align:right;"><?php if($received_amount=='') { echo "0"; } else { echo $received_amount;}?></td>
  </tr>
</table>	</td>
    <td style="padding:0px; margin:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="4">
  <tr>
    <td width="33%" align="left" style="border-right:#808080 1px solid;"><?php if($issueded_qty=='') { echo "0"; } else {	echo $issueded_qty; }?></td>
   <td width="30%" align="left" style="border-right:#808080 1px solid;"><?php if($issueded_rate=='') { echo "0"; } else {  echo  $issueded_rate;}?></td>
    <td align="left" style="text-align:right;"><?php if($issueded_amount=='') { echo "0"; } else { echo $issueded_amount;}?></td>
  </tr>
</table></td>
   <td style="padding:0px; margin:0px;">
		<table width="100%" border="0" cellspacing="0" cellpadding="4">
		 <tr>
    <td width="28%" align="left" style="border-right:#808080 1px solid;"><?php if($totqty=='') { echo "0"; } else {	echo $totqty; }?></td>
   <td width="36%" align="left" style="border-right:#808080 1px solid;"><?php if($totalrate=='') { echo "0"; } else {  echo $totalrate;}?></td>
    <td align="left" style="text-align:right;"><?php if($totaamount=='') { echo "0"; } else { echo $totaamount;} ?></td>
  </tr>
</table></td>
  </tr>

 

    
    
    
    <?php  } } ?>
  </table>
</div>


 

  <?php } 
?>






<script type="text/javascript">
function showItems(str)
{
    if(str=="")
  {
  document.getElementById("itemid").innerHTML="";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("itemid").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","Get_dealeritemcode.php?partName="+str,true);
xmlhttp.send();
}
function showsitename160(str)
{

var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
	var data=xmlhttp.responseText.split("__");	
	document.getElementById("storedata7").innerHTML=data[0];
	  }
  }
  xmlhttp.open("GET","getstockledgerdealerstore.php?site="+str,true);
	xmlhttp.send();
}

function showsubcat(str)
{
var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
	var data=xmlhttp.responseText.split("__");	
	document.getElementById("subcatdata").innerHTML=data[0];
	document.getElementById("itemid").innerHTML=data[1];
	  }
  }
  xmlhttp.open("GET","get_sitenamedatasubcat.php?site="+str,true);
	xmlhttp.send();




}

function showdata(str)
{
 if(str=="")
  {
  document.getElementById("itemid").innerHTML="";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("itemid").innerHTML=xmlhttp.responseText;
    }
  }
  var cat=document.getElementById("maincat").value;
xmlhttp.open("GET","Get_itemcode1.php?partName="+str+"&main="+cat,true);
xmlhttp.send();

}


</script>
