<?php
error_reporting(0);
$first_date1=$_SESSION['financialYrStart'];
$first_date=date('d/m/Y',strtotime($first_date1));
$cur_date=date('d/m/Y');
?>
<script type="text/javascript">
function checkval(){
	var idsite = document.getElementById("site").value;
	//var fromdate = document.getElementById("frmdate").value;
	//var todate = document.getElementById("todate").value;
	if(idsite=='0'){
		alert("Select Site Name First.");
		return false;
	}
	
	/*if(fromdate=='' || todate=='')
	{
		alert("Select From - To date.");
		return false;
	}*/
	return true;
}
</script>
<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
 <div class="tab_div">
<h1>Asset Stock Summary</h1>
<form method="post" action="">
<table width="100%" border="0" cellspacing="0" cellpadding="3" style="border-collapse:collapse;">
	<tr class="row1">
    	<td width="13%" align="right"><strong>Branch:</strong><span style="color:#FF0000">*</span></td>
        <td width="20%">
			<select name="site" id="site" style="width:98%;">
                <option value="0">Select Site</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";
				}
				$site_result = DB_query($site_sql,$db);
				while($site_row = DB_fetch_array($site_result)){?>
                <option value="<?php echo $site_row['loccode']; ?>" <?php echo ($site_row['loccode'] == $_REQUEST['site']) ? "selected" : "";?>><?php echo $site_row['locationname']; ?></option>
                <?php } ?>
        	</select>
		</td>
        <td width="13%" align="right"><strong>Asset Group:</strong> <span style="color:#FF0000">*</span></td>
        <td width="20%">
			<select name="asset_type" id="asset_type" onchange="get_asset(this.value)" style="width:98%;">
				<option value="">--Select--</option>
				 <?php
		  $asset_type=DB_query("Select * from machine_group_master where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
			while($asset_type_res=DB_fetch_array($asset_type))
				{
					$se='';
					if($_REQUEST['asset_type']==$asset_type_res['id'])
					$se="selected";
					?>
					<option value=<?php echo $asset_type_res['id'];?> <?php echo $se;?>><?php echo $asset_type_res['machine_gp_location'];?></option>
				<?php }
		  ?>
			</select>
		</td>
		<td width="13%" align="right"><strong>Asset Name:</strong></td>
		<td>
		<select name="asset_name" id="asset_name" style="width:98%;">
				<option value="0">--Select--</option>
				<option value="0">All</option>
		<?php
		$sqlAssets = "SELECT id, name as asset_name, code as asset_code_prefix from machine_center WHERE CompanyId='".$_SESSION['ACGCompanyId']."'";
		$res = DB_query($sqlAssets,$db);
		while($assetRow = DB_fetch_array($res)){?>
          <option value="<?php echo $assetRow['id'];?>" <?php echo ($_REQUEST['asset_name']== $assetRow['id'] ) ? "selected" : "";?>><?php echo $assetRow['asset_name']." (".$assetRow['asset_code_prefix'].")";?></option>
          <?php } ?>
			</select>
		</td>
	</tr>
	<tr class="row1">
        <td align="right"><strong>From Date:</strong></td>
        <td><input type="text" name="from_date" value="<?php if($_REQUEST['from_date']!='') { echo $_REQUEST['from_date']; } else { echo $first_date; } ?>" id="frmdate" class="datepicker" style="width:98%;"></td>
        <td align="right"><strong>To Date:</strong></td>
        <td><input type="text" name="to_date" value="<?php if($_REQUEST['to_date']!='') { echo $_REQUEST['to_date']; } else { echo $cur_date;} ?>" id="todate" class="datepicker" style="width:98%;"></td>
		<td colspan="2">&nbsp;</td>
    </tr>
    <tr class="row1">
    <td colspan="6" align="center"><p align="center"><input type="submit" name="view" value="View" onclick="return checkval()" class="search"></p></td>
    </tr>
</table>
	
</form>
<?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['site'];
	$store=$_REQUEST['store1'];
	$item=$_REQUEST['asset_name'];					
	
	$function_filter[]=$site;
	$function_filter[]=$store;	
	$function_filter[]=$from;
	$function_filter[]=$to;
    
	$SiteName12=$s->GetFieldValue('locations',"loccode='".$_REQUEST['site']."'",'locationname'); 
    $StoreName12=$s->GetFieldValue('storetype',"id='".$_REQUEST['store1']."'",'StoreName');   
	
	function privious_opening($function_filter,$item)
	{
	 global $db;
	 $receive=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);
	
	
	  
	/**************************************  Issue *********************************************/
    $sql1="Select SUM(IA.issue_qty) as issue_qty from tpl_asset_transfer_issue I left join 
			tpl_asset_transfer_issue_details IA on(I.id=IA.issue_id) 
			WHERE I.cancel_issue=0 and I.site='".$site."' and IA.asset_id in('".$item."') and I.issue_date<='".$fdate."'";
	$sqlissue=DB_query($sql1,$db);	
	while($addissue=DB_fetch_array($sqlissue))
	{
		$issueqty[]=$addissue['issue_qty'];
	}			
	
	/**************************************  site to site receive *********************************************/
	
	$sql2="Select SUM(B.received_qty) as received_qty
		   from tpl_asset_transfer_receive A left join tpl_asset_transfer_receive_details B on(A.id=B.receive_id) WHERE
		   A.receive_site = '".$site."' and A.receive_date <= '".$fdate."' and B.asset_id in ('".$item."')";
	
	$sqlsite_site1=DB_query($sql2,$db);
	while($addsitetositereceive=DB_fetch_array($sqlsite_site1))
	{
		$receive[]=$addsitetositereceive['received_qty'];
	}
		
	/*******************************************  Opening ******************************************************/
	
	$sql3 ="Select opening_date as date, stock as reciveqty, rate, id as itemid from opening_stock_for_toolsplants_machine 
		   where site = '".$site."' and opening_date <= '".$function_filter[3]."' and machine_id in ('".$item."')";
	
	$sqlopen=DB_query($sql3,$db);
	while($addopen=DB_fetch_array($sqlopen))
	{
	$receive[]=$addopen['reciveqty'];	
	}
	$receive=array_sum($receive);
	$issued=array_sum($issueqty);
	$receive1=str_replace(",","",$receive);
	$issued1=str_replace(",","",$issued);
	 $total=$receive1-$issued1;
	 $qty=$total;	   
	return $qty;   
	}
	
	
function openingBalance($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 = '".$site."'";
	if($store!='')
	$filter[]="store = '".$store."'";
	if($from!='--' && $to!='--')
	$filter[]="opening_date <='".$from."'";
	if($item!='')
	$filter[]="machine_id = '".$item."'";
	
	$filter=implode(' and ',$filter);
	$sql1="Select stock, rate from opening_stock_for_toolsplants_machine where $filter";

	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	return $myrow1['stock'];
}

function receivedMRN($function_filter,$item)
{
	global $db;
	$site=$function_filter[0];
	$store=$function_filter[1];
	$from=$function_filter[2];
	$to=$function_filter[3];
	
	if($site!='')
	$filter[]="addmrn.site = '".$site."'";
	if($store!='')
	$filter[]="addmrn.StoreCode = '".$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) from addmrn left join addmrnqty 
		   on(addmrn.id=addmrnqty.issue_id) where Cancel_Status='N' and $filter";

		 
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	return $myrow1['SUM(addmrnqty.reciveqty)'];
}


////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[]="I.site = '".$site."'";
	if($store!='')
	$filter[]="I.mst_storeid = '".$store."'";
	if($item!='')
	$filter[]="IA.asset_id = '".$item."'";
	if($from!='--')
	$filter[]="I.issue_date between '".$from."' and '".$to."'";
	
	$filter=implode(' and ',$filter);

	$sql1="Select SUM(IA.issue_qty) from tpl_asset_transfer_issue I left join 
	 tpl_asset_transfer_issue_details IA on(I.id=IA.issue_id) where I.cancel_issue=0 and $filter";
	
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	
	return $myrow1['SUM(IA.issue_qty)'];
}

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



function receivedSiteToSite($function_filter,$item)
{
	global $db;
	$site=$function_filter[0];
	$store=$function_filter[1];
	$from=$function_filter[2];
	$to=$function_filter[3];
	
	if($site!='')
	$filter[]="A.receive_site = '".$site."'";
	if($store!='')
	$filter[]="A.store = '".$store."'";
	if($from!='--')
	$filter[]="A.receive_date between '".$from."' and '".$to."'";
	if($item!='')
	$filter[]="B.asset_id = '".$item."'";
	
	$filter=implode(' and ',$filter);
	
	$sql1="Select SUM(B.received_qty) 
	from tpl_asset_transfer_receive A left join tpl_asset_transfer_receive_details B
	on(A.id=B.receive_id) where $filter";
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	return $myrow1['SUM(B.received_qty)'];
}


function InProcess($function_filter,$item)
{
	global $db;
	$site=$function_filter[0];
	$store=$function_filter[1];
	$from=$function_filter[2];
	$to=$function_filter[3];
	
	if($site!='')
	$filter[]="A.receive_site = '".$site."'";
	if($store!='')
	$filter[]="A.store = '".$store."'";
	if($from!='--')
	$filter[]="A.receive_date between '".$from."' and '".$to."'";
	if($item!='')
	$filter[]="B.asset_id = '".$item."'";
	
	$filter=implode(' and ',$filter);
	
	$sql1="Select SUM(B.received_qty), SUM(B.issue_qty) 
	from tpl_asset_transfer_receive A left join tpl_asset_transfer_receive_details B
	on(A.id=B.receive_id) where $filter";
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	return ($myrow1['SUM(B.issue_qty)']-$myrow1['SUM(B.received_qty)']);
	}
	
	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[]="I.sitecode = '".$site."'";
	if($store!='')
	$filter[]="I.fromstore = '".$store."'";
	if($from!='--')
	$filter[]="I.issuedate <= '".$from."' ";
	if($item!='')
	$filter[]="ID.mst_itemid = '".$item."'";
	
	$filter=implode(' and ',$filter);
	
	$sql1="Select SUM(ID.issueqty) 
	from tpl_asset_transfer_issue I left join tpl_asset_transfer_issue_details ID
	on(I.id=ID.transfer_issue_id) where $filter";
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	$return_array=$myrow1['SUM(ID.issueqty)'];
	//$avg_rate=$myrow1['SUM(transfer_issue_details.rate)'];
	//$return_array[]=$avg_rate;
	return $return_array;

}

function issueMatarialReturn($function_filter,$item)
{
	global $db;
	$site=$function_filter[0];
	$store=$function_filter[1];
	$from=$function_filter[2];
	$to=$function_filter[3];
	
	if($site!='')
	$filter[]="inv_material_return.sitecode = '".$site."'";
	if($store!='')
	$filter[]="inv_material_return.storecode = '".$store."'";
	if($from!='--' && $to!='--')
	$filter[]="inv_material_return.Reqdate between '".$from."' and '".$to."'";
	if($item!='')
	$filter[]="inv_material_return_details.mst_itemid = '".$item."'";
	
	$filter=implode(' and ',$filter);
	
	$sql1="Select SUM(inv_material_return_details.ReturnQty) 
	from inv_material_return left join inv_material_return_details  
	on(inv_material_return.id=inv_material_return_details.parentId) where $filter";
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	return $myrow1['SUM(inv_material_return_details.ReturnQty)'];
}


if($_REQUEST['site']!='0' && $_REQUEST['store1']!='' && $_REQUEST['from_date']!='' && $_REQUEST['to_date']!='') 
{
	 echo $massege="Showing Record For Site ".$SiteName12." And Store ".$StoreName12." From ".$_REQUEST['from_date']." To ".$_REQUEST['to_date']."";
}
else if($_REQUEST['site']!='0' && $_REQUEST['store1']!='' && $_REQUEST['from_date']!='' && $_REQUEST['to_date']!='' && $_REQUEST['itemName']=='' && $_REQUEST['item']!='')
{
	echo $massege="Showing Record For Site ".$SiteName12." And Store ".$StoreName12." From ".$_REQUEST['from_date']." To ".$_REQUEST['to_date'].""." For  ".$_REQUEST['itemName']." ";
}
else
{
	echo $massege="<strong style='font-size:12px;'>Showing Record For Site ".$SiteName12." From ".$_REQUEST['from_date']." To ".$_REQUEST['to_date']."</strong>";
}

$_SESSION['mess']=$massege;
?>
<p align="right"><a href="asset_stock_summary_print.php?site=<?php echo $_REQUEST['site'];?>&store1=<?php echo $_REQUEST['store1']?>&asset_name=<?php echo $_REQUEST['asset_name']?>&fromdate=<?php echo $_REQUEST['from_date']?>&todate=<?php echo $_REQUEST['to_date']?>" target="_blank"><input type="button" name="print" value="Print" class="search" /></a>
&nbsp;
<a href="Asset_Stock_SummaryExl.php?site=<?php echo $_REQUEST['site'];?>&store1=<?php echo $_REQUEST['store1']?>&from=<?php echo $_REQUEST['from_date'];?>&to=<?php echo $_REQUEST['to_date'];?>&item=<?php echo $item;?>"><input type="button" name="Export" value="Export" class="search" /></a>
</p>


  <table width="100%" border="1" cellspacing="0" cellpadding="3" style="border:#808080 1px solid; border-collapse:collapse;">
    <tr>
	    <th width="3%">S.No.</th>
	    <th width="8%">Asset Code</th>
	    <th>Asset Name</th>
		<th width="10%">UOM</th>
	    <th width="10%">Opening Balance Qty.</th>
	    <th width="10%">Received Qty.</th>
		<th width="10%">In Process Qty.</th>
	    <th width="10%">Issued Qty.</th>
	    <th width="10%">Closing Balance Qty.</th>	
    </tr>
    <!-- <tr>
	    <th>QTY.</th>
	   <th>Rate</th>
	    <th>Value</th
	    <th>QTY.</th>
	   <th>Rate</th>
	    <th>Value</th>
	    <th>QTY.</th>
	  <th>Rate</th>
	    <th>Value</th>
	    <th>QTY.</th>
	   <th>Rate</th>
	    <th>Value</th>
	    <th>QTY.</th>
	   <th>Rate</th>
	    <th>Value</th>
    </tr> -->
  <?php
  if($item!='0')
  {
  $sql1="select * from machine_center where id in('".$item."')";
  }
  else
  {
  $sql1="select * from machine_center where CompanyId='".$_SESSION['ACGCompanyId']."'";
  }
  $_SESSION['sql1']=$sql1;
  $sql=DB_query($sql1,$db);
   $i=1;
  while($row1=DB_fetch_array($sql))
  {
 
      $privious_open=privious_opening($function_filter,$row1['id']); 
     	//$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']);	
	  $InProcess = InProcess($function_filter,$row1['id']);
	 
     
     $opening_stock=number_format($privious_open,5);
	 $opening_stock;
     $received=number_format(($in_2),5);
     $issueded=number_format(($out_1),5);
 
    $invalue=($privious_open+$in_2);
    $invalue1=number_format($invalue,5);
	$invalue2=str_replace(",","",$invalue1);
    $outvalue=($out_1);
	
	$outvalue1=number_format($outvalue,5);
	$outvalue2=str_replace(",","",$outvalue1);
    
    $tot=number_format(($invalue2-$outvalue2),5);

  ?>
   

    <tr bgcolor="#FFFFFF">
    <td><?php echo $i; ?></td>
    <td><?php echo $row1['code'];?></td>
    <td><?php echo $row1['name'];?></td>
    <td><?php echo $s->getField('unitsofmeasure','unitid',$row1['unit'],'unitname');?></td>
    <td style="text-align:right;"><?php if($opening_stock=='') { echo "0"; } else {	echo $opening_stock; }?></td>
   <!-- <td><?php //if($opening_rate=='') { echo "0"; } else {  echo $opening_rate;}?></td>
    <td><?php //if($opening_value=='') { echo "0"; } else { echo $opening_value;}?></td>-->
    <td style="text-align:right;"><?php if($received=='') { echo "0"; } else {	echo $received; }?></td>
   <!--<td><?php // if($inrate=='') { echo "0"; } else {  echo $inrate;}?></td>
    <td><?php //if($inwards_qtydata['total']=='') { echo "0"; } else { echo $inwards_qtydata['total'];}?></td>-->
    <td style="text-align:right;"><?php if($InProcess=='') { echo "0"; } else {	echo $InProcess; }?></td>
   <!--<td><?php // if($inrate=='') { echo "0"; } else {  echo $inrate;}?></td>
    <td><?php //if($inwards_qtydata['total']=='') { echo "0"; } else { echo $inwards_qtydata['total'];}?></td>-->
    <td style="text-align:right;"><?php if($issueded=='') { echo "0"; } else {	echo $issueded; }?></td>
  <!-- <td><?php //if( $outrate=='') { echo "0"; } else {  echo  $outrate;}?></td>
    <td><?php //if($outwards_qtydata['totalout']=='') { echo "0"; } else { echo $outwards_qtydata['totalout'];}?></td>-->
    <td style="text-align:right;"><?php if($tot=='') { echo "0"; } else {	echo $tot; }?></td>
  <!-- <td><?php //if($closeqty['closing_rate']=='') { echo "0"; } else {  echo $closeqty['closing_rate'];}?></td>
    <td><?php //if($closeqty['closing_value']=='') { echo "0"; } else { echo $closeqty['closing_value'];} ?></td>-->
   </tr>
    
    <?php $i++;  } ?>    
    
  </table>

  <?php  } 
?>
<script type="text/javascript">
function GetAssets(str)
{
	if (str.length==0)
	{
	document.getElementById("asset_name").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("asset_name").innerHTML=xmlhttp.responseText;
	}
	}
	
	xmlhttp.open("GET","GetAsset.php?asset="+str,true);
	xmlhttp.send();
}
</script>

<script>
function get_asset(str)
{
  //alert("hi");
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("asset_name").innerHTML=xmlhttp.responseText;
	}
	}
	
	xmlhttp.open("GET","GetAsset_logreport.php?group="+str,true);
	xmlhttp.send();
}
</script>


</div>
</div>

