<?php
$PageSecurity = 11;
include('includes/DefinePOClass.php');
include('includes/DefineSerialItems.php');
include('includes/SQL_CommonFunctions.inc');
$title = _('Stock Ledger');
$date = date($_SESSION['DefaultDateFormat']);
$_SESSION['modid']=$_REQUEST['modid'];
$_SESSION['frmid']=$_REQUEST['frmid'];
$frmpermission= $px->GetFileAccess($_SESSION['modid'],$_SESSION['frmid'],$_SESSION['ACGRoleId']);
$_SESSION['frmView']=$frmpermission[3];
if($_SESSION['frmView']=='0')
{echo "<strong>You are not authorised to view this form. Contact your administrator</strong>"; exit;}
?>
<script language="javascript" type="text/javascript">

function get_itemsubgroup(str)
{
	if(str=="")
  {
  document.getElementById("sub_id").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("sub_id").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","item_subcategory.php?main="+str,true);
xmlhttp.send();
}
function get_item(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?sub="+str,true);
xmlhttp.send();
}
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>
<h2 align="center">Stock Summary</h2>
<form method="post" action="">
<table width="100%" border="0" cellpadding="2" cellspacing="1">
  <tr>
    <td><table width="60%" border="0" cellspacing="1" cellpadding="2" align="center">
      <tr>
        <th width="28%" ><strong>BRANCH</strong></th>
        <td width="72%" ><select name="site" id="site" style="width:107px">
            <option value="">Select Site</option>
            <?php 
			if($_SESSION['AssignedSites']!='0'){ 
		$site_sql = "SELECT loccode, locationname from locations where loccode in (".$_SESSION['AssignedSites'].")";}
		else
		{$site_sql = "SELECT loccode, locationname from locations";}
		$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>        </td>
      </tr>
      <tr>
        <th><strong>GODOWN</strong></th>
        <td><select name="store" id="store" style="width:107px">
            <option value="">Select Store</option>
            <?php 
		 $store_sql = "SELECT distinct StoreCode, StoreName FROM storetype";
		$store_result = DB_query($store_sql,$db);
		while($store_row = DB_fetch_array($store_result))
		{
			$p='';
			if($store_row['StoreCode'] == $_POST['store'])	
			$p="selected";
		
		?>
            <option value="<?php echo $store_row['StoreCode']; ?>" <?php echo $p;?>><?php echo $store_row['StoreName']; ?></option>
            <?php } ?>
          </select>        </td>
      </tr>
      <tr>
        <th><strong> DATE FROM : </strong></th>
        <td><input type="text" name="from_date" value="<?php echo $_POST['from_date'];?>" class="datepicker" size="20"></td>
      </tr>
      <tr>
        <th><strong> DATE TO : </strong></th>
        <td><input type="text" name="to_date" value="<?php echo $_POST['to_date'];?>" class="datepicker" size="20"></td>
      </tr>
      <tr>
    <th><strong>Enter Part Name Of Item</strong></th>
    <td>
    <input type="text" name="itemName" value="" onkeyup="showItems(this.value)" />
    </td> 
  </tr>
    </table></td>
  </tr>
  <tr>
    <td><table width="30%" border="0" cellspacing="1" cellpadding="2" align="center">
      <tr>
        <td><input type="checkbox" name="monthly" value="" id="monthly" />
          Monthly</td>
         <td><input type="checkbox" name="rate_amount" value="" id="rate_amount" />
           With Rate And Amount </td>
      </tr>
      
    </table></td>
  </tr>
  <tr>
    <td align="center"><table width="60%" border="0" cellspacing="1" cellpadding="2" align="center">
  
  <tr>
    <th><strong>ITEM</strong></th>
    <td><div id="itemid">
	<div style="overflow:auto; height:150px; width:450px; border:1px solid; " >
	<?php 
	$item_sql=DB_query("select StockID,item_name from item_master",$db);
	$c3=0;
	while($row3=DB_fetch_array($item_sql))
	{ 
		$c='';
		if($_REQUEST['item'][$c3]==$row3['StockID'])
		{$c="checked";}
	?>
	<input type="checkbox" name="item[<?php echo $c3;?>]" id="item_<?php echo $c3;?>" value="<?php echo $row3['StockID'];?>" <?php echo $c;?>/><?php echo $row3['StockID']." - ".$row3['item_name'];?><br />
	<?php 	
	$c3++;
	}
	?>
	</div>
		
	</div></td>
  </tr>
</table>
      <p align="center">
        <input type="submit" name="view" value="View">
	<input type="submit" name="print" value="Print">
    <input type="submit" name="cancel" value="Cancel"></p></td>
  </tr>
</table>
</form>
<p>&nbsp;</p>
<form action="" method="post">
<p>
<?php 
$fordate = $_POST['from_date'];
$todate = $_POST['to_date'];
$fromdate = explode("/", $fordate);
$daydate = $fromdate['0'];
$Monthdate = $fromdate['1'];
$yeardate = $fromdate['2'];
$fdate = $yeardate."-".$Monthdate."-".$daydate;
					   
$tredate   = explode("/", $todate);
$dayda     = $tredate['0'];
$Monthda   = $tredate['1'];
$yearda    = $tredate['2'];
$toyrdate  = $yearda."-".$Monthda."-".$dayda;
?>
 <table width="100%" border="0" cellspacing="1" cellpadding="2">
    <tr>
      <th><strong>Part Code</strong></th>
      <th><strong>Item Name</strong></th>
      <th><strong>Opening Qty</strong></th>
      <th><strong>Recieved Qty</strong></th>
      <th><strong>Issue Qty</strong></th>
      <th><strong>Closing Stock Qty</strong></th>
    </tr>
<?php 
if($_POST['view'])
{
		
	$filter = array();
	
	if(isset($_REQUEST['site']) && $_REQUEST['site'] != '')
	$filter[]=$_REQUEST['site'];
	if(isset($_REQUEST['store']) && $_REQUEST['store'] != '')
  	$filter[]=$_REQUEST['store'];
	if(isset($_REQUEST['from_date']) && $_REQUEST['from_date'] != '' && isset($_REQUEST['to_date']) && $_REQUEST['to_date'] != '')
  	$filter[]= $fdate;
  	$filter[]=$toyrdate;
	
	$itemArray=array();
	if($_REQUEST['item']!=''){
	$itemArray=$_REQUEST['item'];
	$itemString="'".implode("','",$itemArray)."'"; }
 	
	if($_REQUEST['item'] != '')
	{
		$itemString="0";
	}
	//echo $itemString;
	 $filter1 = implode(" ,",$filter);
	 
		
			function openingStock($itemcode,$itemString) 
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
					$filter[]= "opening_stockitemwise.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
				$filter[]= "opening_stockitemwise.store='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
				$filter[]="opening_stockitemwise.reveive_date between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '' && $itemString != '0')
				{
				$filter[]="opening_stockitemwise.itemcode='".$itemString."'";
				}
				
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				if($filter == '')
				{
					$filter1 ="0";
				}
				$sql= "SELECT * FROM opening_stockitemwise where $filter1";
				$sqlquery=DB_query($sql,$db);
				while($row=DB_fetch_array($sqlquery))
				{
				return ($row['stock']); 
				}	
			}
			function receivedQty($itemcode,$itemString)
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
				$filter[]= "addmrn.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
				$filter[]= "addmrn.StoreCode='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
				$filter[]="addmrn.BillDate between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '' && $itemString != '0')
				{
					$filter[]="ta.itemcode='".$itemString."'";
				}
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				
				  $sql= "SELECT sum(ta.reciveqty)
					FROM addmrn INNER JOIN  addmrnqty ta ON addmrn.id=ta.issue_id
   					 where $filter1";
				$sqlquery=DB_query($sql,$db);
			
				while($row=DB_fetch_array($sqlquery))
				{
					 	
					 $received=(integer)$row['sum(ta.reciveqty)'];
					return ($received); 
				}	
				 	
			}
			function receivedQty1($itemcode,$itemString)
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
					$filter[]= "material_return.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
					$filter[]= "material_return.store='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
					$filter[]="material_return.returndate between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '' && $itemString != '0')
					{
					$filter[]="mt.itemcode='".$itemString."'";
					}
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				  $sql= "SELECT sum(mt.receiveqty)
					FROM material_return INNER JOIN  material_return_details mt ON material_return.id=mt.parentId
   					 where $filter1";
				 $sqlquery=DB_query($sql,$db);
			
				while($row=DB_fetch_array($sqlquery))
				{
					 	
					 $received=(integer)$row['sum(mt.receiveqty)'];
					return ($received); 
				}	
				 	
			}
			function receivedQty2($itemcode,$itemString)
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
					$filter[]= "job_work_return.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
					$filter[]= "job_work_return.vendor='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
					$filter[]="job_work_return.returnDate between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '')
					{
					$filter[]="jd.itemcode='".$itemString."'";
					}
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				  $sql= "SELECT sum(jd.receivedQty)
					FROM job_work_return INNER JOIN  job_return_details jd ON job_work_return.id=jd.masterId
   					 where $filter1";
				 $sqlquery=DB_query($sql,$db);
			
				while($row=DB_fetch_array($sqlquery))
				{
					 	
					 $received=(integer)$row['sum(jd.receivedQty)'];
					return ($received); 
				}	
				 	
			}
			function receivedQty3($itemcode,$itemString)
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
					$filter[]= "finished_recieved.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
					
					$filter[]= "finished_recieved.store='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
					$filter[]="finished_recieved.recieved_date between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '' && $itemString != '0')
				{
					$filter[]="fd.itemcode='".$itemString."'";
				}
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				 $sql= "SELECT sum(fd.reciveqty)
					FROM finished_recieved INNER JOIN  finished_recieved_details fd ON finished_recieved.id=fd.finished_recieved_id
   					 where $filter1";
				 $sqlquery=DB_query($sql,$db);
			
				while($row=DB_fetch_array($sqlquery))
				{
					 	
					 $received=(integer)$row['sum(fd.reciveqty)'];
					return ($received); 
				}	
				 	
			}
			function issueQty($itemcode,$itemString)
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
					$filter[]= "addissue.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
					
					$filter[]= "addissue.store='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
					$filter[]="addissue.issuedate between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '' && $itemString != '0')
				{
					$filter[]="a.itemcode='".$itemString."'";
				}
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				  $sql= "SELECT SUM(a.issueqty)
  						 FROM addissue INNER JOIN  issueqty a ON addissue.id=a.issue_id
   					 where $filter1";
				$sqlquery=DB_query($sql,$db);
			
				while($row=DB_fetch_array($sqlquery))
				{
					 $received=(integer)$row['SUM(a.issueqty)'];
					
					return ($received); 
				}	
				 	
			}
			function issueQty2($itemcode,$itemString)
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
					$filter[]= "job_work_return.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
					$filter[]= "job_work_return.vendor='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
					$filter[]="job_work_return.returnDate between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '' && $itemString != '0')
					{
					$filter[]="j.itemcode='".$itemString."'";
					}
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				 $sql= "SELECT SUM(j.quantity)
  				FROM job_work_return INNER JOIN  job_work_details j ON job_work_return.id=j.masterId
  		 		where  $filter1";
				$sqlquery=DB_query($sql,$db);
			
				while($row=DB_fetch_array($sqlquery))
				{
					 $received=(integer)$row['SUM(j.quantity)'];
					
					return ($received); 
				}	
			}
			function issueQty3($itemcode,$itemString)
			{
				global $db;
				$opening_var=explode(",",$itemcode);
				if($opening_var[0]!='')
				{
					$filter[]= "nrgp_challan.site='".$opening_var[0]."'";
				}
				if($opening_var[1]!='' && $opening_var[1]!='--')
				{
					$filter[]= "nrgp_challan.store='".$opening_var[1]."'";
				}
				if($opening_var[2]!='' && $opening_var[3]!='')
				{
					$filter[]="nrgp_challan.challan_date between '".$opening_var[2]."' and '".$opening_var[3]."'";
				}
				if($itemString != '' && $itemString != '0')
					{
					$filter[]="j.itemcode='".$itemString."'";
					}
				if($filter != '')
				{
					$filter1 = implode(" and ",$filter);
				}
				 $sql= "SELECT SUM(j.issueqty)
				 FROM nrgp_challan INNER JOIN  nrgp_challan_details j ON nrgp_challan.id=j.nrgp_challan_id
  		 		where  $filter1";
  				
				$sqlquery=DB_query($sql,$db);
			
				while($row=DB_fetch_array($sqlquery))
				{
					 $received=(integer)$row['SUM(j.issueqty)'];
					
					return ($received); 
				}	
			}
	
	?>
	<?php 
	//print_r($itemArray);
	$itemString1=explode(",",implode(",",$itemArray));
	
	
	//print_r($itemString1);
	for($i=0;$i<count($itemString1);$i++)
	//while($itemString1 >0)
	{
	 //echo $itemArray[$i]; ?>
	<tr bgcolor="#FFFFFF">
	  <td><?php echo $itemString1[$i]; ?></td>
      <td><?php $name=$s->getField('addmrnqty','itemcode',$itemString1[$i],'description'); echo $name; ?></td>
      <td><?php echo $openingstock=openingStock($filter1,$itemString1[$i]);?></td>
      <td><?php echo $received=receivedQty($filter1,$itemString1[$i])+ receivedQty1($filter1,$itemString1[$i])+ receivedQty2($filter1,$itemString1[$i])+ receivedQty3($filter1,$itemString1[$i]); ?></td>
      <td><?php echo $issue=issueQty($filter1,$itemString1[$i])+ issueQty2($filter1,$itemString1[$i])+ issueQty3($filter1,$itemString1[$i]); ?></td>
      <td><?php $op_stock=$openingstock;
       			$re_stock=$received;
				$totalreceived=$op_stock + $re_stock;
				if($totalreceived!='')
				{
				$iss_stock=$issue;
				 $closing_stock=$totalreceived - $iss_stock;
				echo $closing_stock;
				}
				
       	?></td>
      
    </tr><?php }  ?>
	<?php 
	 
} ?>
  </table>
  <p>&nbsp;  </p>
</form>
<?php 
include('includes/footer.inc');
?>