<?php			
function privious_opening($function_filter,$item)
	{
	 global $db;
	 $receive=array();
	 $total=array();
	  $total1=array();
	 $issueqty=array();	
	 //$site=$function_filter[0];
    
    
    $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 *********************************************/
	
    $sqlpo=DB_query("select sum(reciveqty) as reciveqty,sum(item_total_rate) as total from addmrnqty 
	inner join addmrn on addmrn.id=addmrnqty.issue_id  where site in('".$site."')
	 and StoreCode in ('".$store."')
	  and MRNDate <='".$fdate."' and Cancel_status='N' and reciveqty!='0.00' 
	 and mst_itemid in ('".$item."')  AND addmrn.Approval_status='0'",$db);	   
    
    
	 
	$sqlmrn=DB_query("select sum(reciveqty) as reciveqty,sum(item_total_rate) as total from addmrnqty 
	inner join addmrn on addmrn.id=addmrnqty.issue_id  where site in('".$site."')
	 and StoreCode in ('".$store."')
	  and MRNDate <='".$fdate."' and Cancel_status='N' and reciveqty!='0.00' 
	 and mst_itemid in ('".$item."')  AND addmrn.Approval_status='0'",$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 in('".$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 in('".$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 in('".$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 in('".$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 in('".$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'];
	}
	
	}	
	
	/*********************************************************************************************************/
	
	
	
	
	
	$sqldispaatch1=DB_query("SELECT sum(dispatchQty) AS issueqty,sum(AssValue) as total
	                    FROM  dispatch_details INNER JOIN dispatch 
				        ON dispatch.id=dispatch_details.parentId where site in('".$site."') and
						store in ('".$store."') and dispatchDate <= '".$fdate."' 
						and item in ('".$item."')",$db);
	while($adddispatch1=DB_fetch_array($sqldispaatch1))
	{
	if($adddispatch1['issueqty']!='')
	{
	$issueqty[]=$adddispatch1['issueqty'];
	$total1[]=$adddispatch1['total'];
	}
	
	}	
	
	
	
	
	
	
	
	
	
	/*******************************************  Opening ******************************************************/
	
	$sqlopen=DB_query("select reveive_date as date,itemcode as itemid,stock as reciveqty,amount
	from opening_stockitemwise  where site in('".$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'];
	}
	}
	// array_sum($receive);
	$qty=array_sum($receive)-array_sum($issueqty);
	$amount=array_sum($total)-array_sum($total1);	
   if($qty=='')
   $rate=0;
   else
   $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 in ( '".$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[]="site in ( '".$site."')";
		if($store!='')
		$filter[]="storecode in ('".$store."')";		
		if($from!='--' && $to!='--')
			$filter[]="addmrn.MRNDate between '".$from."' and '".$to."'";
			if($item!='')
			$filter[]="addmrnqty.mst_itemid = '".$item."'";
			
			$filter=implode(' and ',$filter);
			$sql1="Select SUM(addmrnqty.reciveqty),sum(item_total_rate) as total
				       from addmrn left join addmrnqty  on(addmrn.id=addmrnqty.issue_id)
                   where Cancel_Status='N' and reciveqty!='0.00'  and $filter 
                   AND addmrn.Approval_status='1'";
				 
			$result1=DB_query($sql1,$db);
			$myrow1=DB_fetch_array($result1);
          if($myrow1['SUM(addmrnqty.reciveqty)']!='')
			  $rate1=$myrow1['total']/$myrow1['SUM(addmrnqty.reciveqty)'];
          else
           $rate1=0;
			   $rate=round($rate1,2);
				$return_array[]=$myrow1['SUM(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[]="site in ( '".$site."')";
		if($store!='')
		$filter[]="store 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);
		
		
		if($myrow1['SUM(issueqty.issueqty)']!='')
		$rate1=$myrow1['total']/$myrow1['SUM(issueqty.issueqty)'];
      else
       $rate1=0;
		 $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[]="site in ( '".$site."')";
		if($store!='')
		$filter[]="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);
	
		
		         if($myrow1['SUM(material_return_details.receiveqty)']!='')
			       $rate1=$myrow1['total']/$myrow1['SUM(material_return_details.receiveqty)'];
               else
                $rate1=0; 
               $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[]="Received_to in ( '".$site."')";
		if($store!='')
		$filter[]="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);
		
		
		if($myrow1['SUM(transfer_receipt_details.reciveqty)']!='')
		 $rate1=$myrow1['total']/$myrow1['SUM(transfer_receipt_details.reciveqty)'];
      else
       $rate1=0;
					 $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[]="sitecode in ( '".$site."')";
		if($store!='')
		$filter[]="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)'];
		if($myrow1['SUM(transfer_issue_details.issueqty)']!='')
		 $rate1=$myrow1['total']/$myrow1['SUM(transfer_issue_details.issueqty)'];
      else
       $rate1=0;
			 $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[]="sitecode in ( '".$site."')";
		if($store!='')
		$filter[]="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);
		if($myrow1['SUM(inv_material_return_details.ReturnQty)']!='')
		$rate1=$myrow1['total']/$myrow1['SUM(inv_material_return_details.ReturnQty)'];
      else
       $rate1=0;
				 $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[]="branch in ( '".$site."')";
		if($store!='')
		$filter[]="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);
			
         if($myrow1['SUM(rgp_tab_details.qty)']!='')			
			$rate1=$myrow1['total']/$myrow1['SUM(rgp_tab_details.qty)'];
         else
          $rate1=0;
				 $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[]="branch in ( '".$site."')";
		if($store!='')
		$filter[]="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);
		if($myrow1['SUM(rgp_return_details.qty)']!='')
		$rate1=$myrow1['total']/$myrow1['SUM(rgp_return_details.qty)'];
      else
       $rate1=0;
			 $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[]="branch in ( '".$site."')";
		if($store!='')
		$filter[]="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 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[]="site in('".$site."')";
		if($store!='')
		$filter[]="store in ('".$store."')";
		
		
		if($from!='--' && $to!='--')
		$filter[]="dispatch.dispatchDate between '".$from."' and '".$to."'";
		if($item!='')
		$filter[]="dispatch_details.item = '".$item."'";
		
		$filter=implode(' and ',$filter);
		
			$sql1="Select SUM(dispatch_details.dispatchQty) 
               from dispatch left join dispatch_details 
               on(dispatch.id=dispatch_details.parentId) where  $filter";
		$result1=DB_query($sql1,$db);
		$myrow1=DB_fetch_array($result1);
		   if($myrow1['SUM(dispatch_details.dispatchQty)']!='')
		   $rate1=$myrow1['AssValue']/$myrow1['SUM(dispatch_details.dispatchQty)'];
         else
          $rate1=0;
			 $rate=round($rate1,2);
			$return_array[]=$myrow1['SUM(dispatch_details.dispatchQty)'];
			$return_array[]=$rate;
			$return_array[]=0;
										
			return $return_array;
  	}	
	
   	function FinishedMR($function_filter,$item)
	{					  
		global $db;
		
		 $site=$function_filter[0];
		$store=$function_filter[1];
		$from=$function_filter[2];		
      $to=$function_filter[3];	
		if($site!='')
		$filter[]="site in('".$site."')";
		if($store!='')
		$filter[]="store in ('".$store."')";
									
		
		if($from!='--' && $to!='--')
		$filter[]="finished_recieved.recieved_date <= '".$from."' ";
		if($item!='')
		$filter[]="finished_recieved_details.mst_itemid = '".$item."'";
		
		$filter=implode(' and ',$filter);
		
		   $sql1="Select SUM(finished_recieved_details.reciveqty) 
               from finished_recieved left join finished_recieved_details 
               on(finished_recieved.id=finished_recieved_details.finished_recieved_id) where $filter";
		$result1=DB_query($sql1,$db);
		$myrow1=DB_fetch_array($result1);
      if($myrow1['SUM(finished_recieved_details.reciveqty)']!='')
		 $rate1=$myrow1['AssValue']/$myrow1['SUM(finished_recieved_details.reciveqty)'];
      else
       $rate1=0; 
			 $rate=round($rate1,2);
			  $return_array[]=$myrow1['SUM(finished_recieved_details.reciveqty)'];
			$return_array[]=$rate;
			$return_array[]=0;
		return $return_array;
  	}	
		
		/*-------------------------------------Calculate_Closing_Quantity Function--------------------------------*/
	
		function closingquantity($item,$opening_value)
		{	
			global $to , $from;
			global $db;
			$closing_amt_date = str_replace('/', '-', $_REQUEST['to_date']);
			$change_closing_amt_date= date('Y-m-d', strtotime($closing_amt_date));
			//$qty_query=DB_query("select adjustment_type,adjustment_qty from `physical_stock_details` WHERE mstitemid='".$item."' ",$db);
			$qty_query=DB_query("SELECT phy.id,psd.physical_stock_id,psd.mstitemid,psd.adjustment_qty,psd.adjustment_type,phy.date1 FROM physical_stock_details psd,physical_stock phy WHERE psd.physical_stock_id=phy.id AND phy.date1 BETWEEN '".$from."' AND '".$to."' AND psd.mstitemid='".$item."'",$db);
			while($qty_row1=DB_fetch_array($qty_query))
			{
				if($qty_row1['adjustment_type']=='plus')
				$opening_value+=$qty_row1['adjustment_qty'];
				else if($qty_row1['adjustment_type']=='minus')
				 $opening_value-=$qty_row1['adjustment_qty'];			
			}	
		return $opening_value;
		}
	
							/*------------------------------calculate variance------------------------*/	
	function variance_calculate($item)
	{
	global $to , $from;
	global $db;
	$plus=0;
	$minus=0;
	//echo "SELECT pd.adjustment_type,pd.adjustment_qty FROM physical_stock p, physical_stock_details pd WHERE p.id=pd.physical_stock_id AND pd.mstitemid= '".$item."' AND p.date1 BETWEEN '".$from."' AND '".$to."' ";
	$variance_query="SELECT pd.adjustment_type,pd.adjustment_qty FROM physical_stock p, physical_stock_details pd WHERE p.id=pd.physical_stock_id AND pd.mstitemid= '".$item."' AND p.date1 BETWEEN '".$from."' AND '".$to."' ";
	$variance=DB_query($variance_query,$db);
		while($all_variance=DB_fetch_array($variance))
		{
			if($all_variance['adjustment_type']=='plus')
			{
			$plus+=$all_variance['adjustment_qty'];
			}
			else if($all_variance['adjustment_type']=='minus')
			{
			$minus+=$all_variance['adjustment_qty'];
			}
		}
		$item_rate=DB_fetch_array(DB_query("select  net_rate from item_master where id='".$item."' ",$db));
		$keys = array('total_plus','total_minus','netrate');
		$values = array($plus,$minus,$item_rate['net_rate']);
		$variance_data = array_combine($keys, $values);
		//print_r($variance_data);
		return $variance_data;
	}		

?>



<link rel="stylesheet" href="css/new_mxerp.css">
<script>
	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_itemcode.php?partName="+str,true);
xmlhttp.send();
}
</script>

<?php 
unset($_SESSION['query1']);
$flag=0;
?>

<div class="body_contain">
<div class="tab_div">
	
<h1>Stock Valuation Report</h1>


<form method="post" action="">
   <table width="100%" border="1" cellpadding="3" cellspacing="0" style="border-collapse:collapse; border:#666666 solid 1px;">
  	  <tr>
  	    <th class="tabledata1">Branch</th>
  	    <?php 
				
		?>
  	    <td><select name="site" id="site" style="width:82%">
            <option value="">Select Branch</option>
            <?php 
			if($_SESSION['AssignedSites']!='0'){ 
		$site_sql = "SELECT loccode, locationname from locations where CompanyId='".$_SESSION['ACGCompanyId']."' ";}
		else
		{$site_sql = "SELECT loccode, locationname from locations where CompanyId='".$_SESSION['ACGCompanyId']."'";}
		$site_result = DB_query($site_sql,$db);
		while($site_row = DB_fetch_array($site_result))
		{
			$p='';
			if($site_row['loccode'] == $_POST['site'])	
			$p="selected";
		?>
            <option value="<?php echo $site_row['loccode']; ?>" <?php echo $p;?>><?php echo $site_row['locationname']; ?></option>
            <?php } ?>
          </select>       
  	      All 
          <input type="checkbox" name="checkbox" id="checkbox" /></td>
          
  	    <th class="tabledata1">Period</th>
  	    
  	    <td>From :<input type="text" name="frmdate" size="10" id="frmdate" class="datepicker" style="width:40%" /> To <input type="text" name="todate" size="10" id="todata" class="datepicker" style="width:40%" /></td>
  	    </tr>
  	  <tr>
  	    <th class="tabledata1">Group</th>
  	    
  	    <td><select name="store" id="store" style="width:100%">
            <option value="">Select Group</option>
            <?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['stockmaincategoryid'] == $_POST['store'])	
			$p="selected";
		
		?>
            <option value="<?php echo $store_row['stockmaincategoryid']; ?>" <?php echo $p;?>><?php echo $store_row['stockmaincategoryname']; ?></option>
            <?php } ?>
          </select>  </td>
  	    <th class="tabledata1">Sub Ggroup</th>
  	    <td><select name="categoryid" id="categoryid" style="width:100%">
            <option value="">Select Sub Ggroup</option>
            <?php 
		 $store_sql = "SELECT * FROM stockcategory where CompanyId='".$_SESSION['ACGCompanyId']."'";
		$store_result = DB_query($store_sql,$db);
		while($store_row = DB_fetch_array($store_result))
		{
			$p='';
			if($store_row['categoryid'] == $_POST['store'])	
			$p="selected";
		
		?>
            <option value="<?php echo $store_row['categoryid']; ?>" <?php echo $p;?>><?php echo $store_row['categorydescription']; ?></option>
            <?php } ?>
          </select> </td>
  	    </tr>
  	  <tr>
  	    <th class="tabledata1">Item Desc</th>
  	    <td><select name="itemdesc" id="itemdesc" style="width:100%">
            <option value="">Select Item Desc</option>
            <?php 
		 $store_sql = "SELECT * FROM item_master  where CompanyId='".$_SESSION['ACGCompanyId']."' group by item_name ";
		$store_result = DB_query($store_sql,$db);
		while($store_row = DB_fetch_array($store_result))
		{t
			$p='';
			if($store_row['StockID'] == $_POST['store'])	
			$p="selected";
		
		?>
            <option value="<?php echo $store_row['StockID']; ?>" <?php echo $p;?>><?php echo $store_row['item_name']; ?></option>
            <?php } ?>
          </select></td>
  	    <th class="tabledata1">Method of Calculation</th>
  	  
  	    <td><select name="select4" style="width:100%">
  	      <option>Select</option>
	      </select></td>
  	    </tr>

    </table>
	 <p align="left" style="padding-left:10px;">
      	  <input type="submit" name="view" value="Proceed"></p>
	
	</form>
	
  
  
<div >

<table width="100%" border="1" cellpadding="3" cellspacing="0" style="border-collapse:collapse; border:#666666 solid 1px;">
    	        <tr style="background-color:#CCCCCC">
    	          <td class="tabledata1">Company Name</td>
    	          <td class="tabledata1">Webtech Packing India Pvt Ltd</td>
    	          <td class="tabledata1">Date From</td>
    	          <td class="tabledata1"><?php echo $_REQUEST['frmdate']; ?>  To <?php echo $_REQUEST['todate']; ?>
    	          </td>
                </tr>
  	        </table>
				
<table width="100%" border="1" cellspacing="0" cellpadding="3">
    <tr>
	<th width="10%" rowspan="2">Item Code</th>
    <th rowspan="2">Item Name</th>
	<!--<th width="7%" rowspan="2">Type</th>-->
	<th width="10%" rowspan="2">UOM</th>
    <th width="15%" height="31"><center>Opening Balance
    </center></th>
    <th width="15%"><center>Received
    </center></th>
    <th width="15%"><center>Issued
    </center></th>
	   <th width="15%"><center>Variance
    </center></th>
    <th width="15%"><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 align="left">Plus</th>
    <th align="left">Minus</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  
 
     
     $sitedata=DB_query("select * from locations where 
     CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
    while($site1=DB_fetch_array($sitedata))
    {
    $site2[]=$site1['loccode']; 
    } 
    print_r($site2);
   
    
    $site=implode("','",$site2);
    $storedata=DB_query("select * from storetype where Store_Site in('".$site."')",$db);
    while($store1=DB_fetch_array($storedata))
    {
    $store2[]=$store1['id']; 
    } 
    $store=implode("','",$store2);
    
  
   
   
 
 
               $function_filter[]=$site;
               $function_filter[]=$store;
               $function_filter[]=$_SESSION['financialYrStart'];
					$function_filter[]=$_SESSION['financialYrEnd'];
               
               
              
               
   
 $sql_cat=DB_query("select * from stockmaincategory where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
 while($row2=DB_fetch_array($sql_cat))
 {
 $main_cat_array[]=$row2['id']; 
 }
 
// print_r($main_cat_array);
 
for($rd=0;$rd<count($main_cat_array);$rd++){
if($filter2!='')
    $sql1="select * from item_master where SubCategoryID='".$main_cat_array[$rd]."' ORDER BY StockID ASC";
   else
   $sql1="select * from item_master where SubCategoryID='".$main_cat_array[$rd]."' ORDER BY StockID ASC";
   
   //echo $sql1;
    $_SESSION['sqlquery_'.$rd]=$sql1;
 $sql2=$sql1;
	$sql=DB_query($sql1,$db);
	$sql2=DB_query($sql2,$db);
    $num_row2=DB_num_rows($sql2);
   while($row1=DB_fetch_array($sql2))
  {
        
       $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=FinishedMR($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]); 
     if($received_qty!='')
	  $received_rate=round($received_amount/$received_qty,2);
     else
      $received_rate=0;
      $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]);
      if($issueded_qty!='')
      $issueded_rate=round($issueded_amount/$issueded_qty,2);
      else
       $issueded_rate=0;
      $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;
     if($totqty!='')
     $totalrate=round($totaamount/$totqty,2);
	 else
     $totalrate=0;
  

  if($opening_stock=='' && $received_qty=='' && $issueded_qty=='' && $totqty=='')
  {
     $flag=0;
  }  
  else $flag=1;
   }
  
   $_SESSION['flagdata_'.$rd]=$flag;
    if($num_row2>0 && $flag==1)
  {
?>
 <tr bgcolor="#CCCCCC">
	<td colspan="8" align="left" style="text-align:left;"><b><?php echo $s->GetFieldValue('stockmaincategory','id="'.$s->GetFieldValue('stockcategory','id="'.$main_cat_array[$rd].'"','stockmaincategory').'"','stockmaincategoryname');?>&nbsp;<img src="images/orange-right-arrow-button.png" height="20" width="20" />&nbsp;<?php echo $s->GetFieldValue('stockcategory','id="'.$main_cat_array[$rd].'"','categorydescription')?></b></td>
	
</tr>
<?php		                
 $itemarray=array();
  while($row1=DB_fetch_array($sql))
  {
       
	 
       $_SESSION['opening_'.$rd]=$opening_stock;
  
  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> <?php echo $itemcode=$s->getField('item_master','id',$row1['id'],'StockID'); ?></td>
  <?php /*?><!--<td width="10%" rowspan="2"><?php echo $s->getField('item_master','id',$row1['id'],'item_name');?></td>--><?php */?>
  
  <td ><?php echo $s->getField('item_master','id',$row1['id'],'item_name');?></td>
  <!--<td>Primary</td>-->
  <td><?php $unit_id=$s->getField('item_master','id',$row1['id'],'mst_unitid'); echo $s->getField('unitsofmeasure','unitid',$unit_id,'unitname');?></td>
    <td style="padding:0px; margin:0px;">
		<table width="100%" cellspacing="0" cellpadding="4" border="0">
  	<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="33%" align="left" style="border-right:#808080 1px solid;"><?php   $op1=variance_calculate($row1['id']); 
	 echo $op1[total_plus];  ?></td>
   <td width="30%" align="left" style="border-right:#808080 1px solid;"><?php echo $op1[total_minus]; ?></td>
    <td align="left" style="text-align:right;">
	<?php  
		 $diff=$op1[total_plus]-$op1[total_minus];
		 echo $variance_value=$diff*$op1[netrate];
		 ?></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 $closeqty=closingquantity($row1['id'],$totqty); }?></td>
	<?php $tot1=$variance_value+$totaamount ;
	$totalrate=$tot1/$closeqty; ?>
   <td width="36%" align="left" style="border-right:#808080 1px solid;"><?php   echo round($totalrate);?></td>
   <?php // $totamt=$closeqty*$totalrate; ?>
    <td align="left" style="text-align:right;"><?php  if($tot1=='') { echo "0"; } else { echo $tot1;} ?></td>
  </tr>
</table></td>
  </tr>

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

</div></div>


	
