<?php
ob_start();
session_name('CAKEPHP'); session_start();
$PageSecurity = 3;
 include ('includes/session.inc');
 include ('includes/function_lib.php');
 include('includes/fun-php.php');
 include('includes/fun-HRClass.php');
function getField($table,$condition,$val,$field)
{
  	 global $db;
   	 $sql=DB_query("select ".$field." from ".$table." where ".$condition." ='".$val."'",$db);
	 $r=DB_fetch_array($sql);
   	 return ($r[$field]);
}

global $db; 
$Compamies=DB_query("select * from companies",$db);
$CompamiesRow=DB_fetch_array($Compamies);
$CompamiesRow['CompanyId'];
//$branch=$s->GetFieldValue('locations',"CompanyId='".$CompamiesRow['Branch_Permission']."'",'locationname');
 $branch=getField('locations','CompanyId',$CompamiesRow['Branch_Permission'],'locationname'); 
?>
<script language="javascript" type="text/javascript">
  function toggle_visibility(id) {
	
	 
       var e = document.getElementById(id);
	      e.style.display = 'none';
		       //alert(e.style.display);
			   window.print();
    }
  
</script>
<title>Stock Summary Report</title>
<link rel="stylesheet" href="css/purchase_requision_print.css">
<div class="body_contain">
<div class="tab_div">
<?php 
$messeg=$_SESSION['mess'];
?>
<!--<div id="foo">
  <p align="center"><input type="button" onclick="javascript:toggle_visibility('foo');" value="Print" /></p>
</div> 
-->
<p align="center" style="font-size:14px;"><strong><?php echo $messeg; ?></strong></p>
<?php 
	$from_date=$_REQUEST['fromdate'];
	$to_date=$_REQUEST['todate'];	
	$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['store_code'];
	$function_filter[]=$site;
	$function_filter[]=$store;	
	$function_filter[]=$from;
	$function_filter[]=$to;
	
	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 between '".$from."' and '".$to."'";
		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 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 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)']);
	}


$header="<table width='100%' border='1' cellspacing='0' cellpadding='3' style='font-size:12px; border:#000000 1px solid;'>";
$header.="<tr>";
$header.="<th rowspan='2'>S.No.</th>";
$header.="<th rowspan='2'>Asset Code</th>";
$header.="<th rowspan='2'>Asset Name</th>";
$header.="<th rowspan='2'>UOM</th>";
$header.="<th><center>Opening Balance</center></th>";
$header.="<th><center>Received</center></th>";
$header.="<th><center>In Process</center></th>";
$header.="<th><center>Issued</center></th>";
$header.="<th>Closing Balance</th>";
$header.="</tr>";
$header.="<tr>";
$header.="<td>";
$header.="<table width='100%' border='0' cellspacing='0' cellpadding='0'>";
$header.="<tr>";
$header.="<th><center>QTY.</center></th>";
$header.="</tr>";
$header.="</table>";
$header.="</td>";
$header.="<td>";
$header.="<table width='100%' border='0' cellspacing='0' cellpadding='0'>";
$header.="<tr>";
$header.="<th><center>QTY.</center></th>";
$header.="</tr>";
$header.="</table>";
$header.="</td>";
$header.="<td>";
$header.="<table width='100%' border='0' cellspacing='0' cellpadding='0'>";
$header.="<tr>";
$header.="<th><center>QTY.</center></th>";
$header.="</tr>";
$header.="</table>";
$header.="</td>";
$header.="<td>";
$header.="<table width='100%' border='0' cellspacing='0' cellpadding='0'>";
$header.="<tr>";
$header.="<th><center>QTY.</center></th>";
$header.="</tr>";
$header.="</table>";
$header.="</td>";
$header.="<td>";
$header.="<table width='100%' border='0' cellspacing='0' cellpadding='0'>";
$header.="<tr>";
$header.="<th><center>QTY.</center></th>";
$header.="</tr>";
$header.="</table>";
$header.="</td>";
$header.="</tr>";
echo $header;




   $sql1=$_SESSION['sql1'];
  $sql=DB_query($sql1,$db);
  $j=1;
 $i=0;
 $flag23=0;
  while($row1=DB_fetch_array($sql))
  {
 if($flag23==0)
 {
 	$divisor=29;
 }
      $privious_open=privious_opening($function_filter,$row1['id']); 
     	
	  $out_1=issueSlip($function_filter,$row1['id']);
	  $in_2=receivedSiteToSite($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); 
   $i++;
   
  ?>
   

    <tr style="border-right:#000000 solid 1px;">
    <td style="padding-left:5px; padding-top:5px; padding-bottom:5px;"><font size="2"><strong><?php echo $j;?></strong></font></td>
     <td style="padding-left:5px; padding-top:5px; padding-bottom:5px;"><font size="2"><strong><?php echo $row1['code'];?></strong></font></td>
  <td style="padding-left:5px; padding-top:5px; padding-bottom:5px;"><font size="2"><strong><?php echo $row1['name'];?></strong></font></td>
  <td style="padding-left:5px; padding-top:5px; padding-bottom:5px;"><font size="2"><strong><?php echo $s->getField('unitsofmeasure','unitid',$row1['unit'],'unitname');?></strong></font></td>
    <td>
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
  	<tr>
    <td><center><?php if($opening_stock=='') { echo "0"; } else {	echo $opening_stock; }?></center></td>
   <!-- <th><center><?php //if($opening_rate=='') { echo "0"; } else {  echo $opening_rate;}?></center></th>
    <th><center><?php //if($opening_value=='') { echo "0"; } else { echo $opening_value;}?></center></th>-->
  </tr>
 
</table>	</td>
    <td>
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
		<tr>
    <td><center><?php if($received=='') { echo "0"; } else {	echo $received; }?></center></td>
   <!--<th><center><?php // if($inrate=='') { echo "0"; } else {  echo $inrate;}?></center></th>
    <th><center><?php //if($inwards_qtydata['total']=='') { echo "0"; } else { echo $inwards_qtydata['total'];}?></center></th>-->
  </tr>
 
</table>	</td>

 <td>
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
		<tr>
    <td><center><?php if($InProcess=='') { echo "0"; } else {	echo $InProcess; }?></center></td>
   <!--<th><center><?php // if($inrate=='') { echo "0"; } else {  echo $inrate;}?></center></th>
    <th><center><?php //if($inwards_qtydata['total']=='') { echo "0"; } else { echo $inwards_qtydata['total'];}?></center></th>-->
  </tr>
 
</table>	</td>
    <td>
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><center><?php if($issueded=='') { echo "0"; } else {	echo $issueded; }?></center></td>
  <!-- <th><center><?php //if( $outrate=='') { echo "0"; } else {  echo  $outrate;}?></center></th>
    <th><center><?php //if($outwards_qtydata['totalout']=='') { echo "0"; } else { echo $outwards_qtydata['totalout'];}?></center></th>-->
  </tr>
</table></td>
   <td>
		<table width="100%" border="0" cellspacing="0" cellpadding="0">
		 <tr>
    <td><center><?php if($tot=='') { echo "0"; } else {	echo $tot; }?></center></td>
  <!-- <th><center><?php //if($closeqty['closing_rate']=='') { echo "0"; } else {  echo $closeqty['closing_rate'];}?></center></th>
    <th><center><?php //if($closeqty['closing_value']=='') { echo "0"; } else { echo $closeqty['closing_value'];} ?></center></th>-->
  </tr>
   
</table></td>

  </tr>

    
<?php 
if($i%$divisor==0)
{
$divisor=$divisor+34;
$flag23=$i;

?>
</table>
</div>
</div>
<div class="body_contain">
<div class="tab_div">
<p align="center" style="font-size:14px;"><strong><?php echo $messeg; ?></strong></p>
<?php echo $header; ?>

<?php }	
	
	$j++;
}  // END OF WHILE LOOP
?>
    
    
  </table>

 
<br />
<div class="authorize">
	<div style="float:left; padding-top:30px;"><img src="images/footer_bg_left.jpg" /></div>
	<div style="float:right"><img src="images/footer_bg_right.jpg" /></div>
	<div style="clear:both;"></div>
</div><!---END OF authorize DIV -------->

<!--</div><!---END OF HEIGHT DIV -------->

</div><!---END OF tab_div DIV -------->

</div><!---END OF body_contain DIV -------->
<?php
$header='';  		
header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=AssetStockedgerEXL.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data"; 
?>