<?
ob_start();
session_start();
if(isset($_SESSION['ses_name']))
{
$user=$_SESSION['ses_name'];
}else{
header("location:index.php");
}
$parent_name='';
$ledger_name='';
$bank='';
$veh_num='';
$sdate_12 ='';
$page='1';
include("include/config.php");
require_once "include/Database_functions.php";
$sqlObj=new sqlclass();
$msg = '';
$cumulative=0;

if(isset($_REQUEST['from_date']) && $_REQUEST['from_date']!=''){
$from_date=date('d-m-Y',strtotime($_REQUEST['from_date']));
}
else
{
$from_date=date('d-m-Y');
}
if(isset($_REQUEST['to_date']) && $_REQUEST['to_date']!=''){
$to_date=date('d-m-Y',strtotime($_REQUEST['to_date']));
}
else
{
$to_date=date('d-m-Y');
}

if(isset($_REQUEST['bankid']) && $_REQUEST['bankid']!=''){
$bank = $_REQUEST['bankid'];
}

?>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>OIL FILL</title>
<link href="css/styles.css" rel="stylesheet"  />

<link href="css/menu.css" rel="stylesheet" type="text/css" />
<style>
.newcategoryProfile{ background:#FFFFFF; position:absolute; width:400px; height:250px; z-index:5;
					-webkit-box-shadow: 0px 0px 35px 1px rgba(0,0,0,0.75);
					-moz-box-shadow: 0px 0px 35px 1px rgba(0,0,0,0.75);
					box-shadow: 0px 0px 35px 1px rgba(0,0,0,0.75);}


</style>
<link type="text/css" href="css_date/ui-lightness/jquery-ui-1.7.2.custom.css" rel="stylesheet" />
<script type='text/javascript' src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="js/trippopup.js" type="text/javascript"></script>
<!----------------------------css for auto complete-------------------------------->
<link rel="stylesheet" href="css/chosen.css">

<!--end-->

<script type='text/javascript' src="js/jquery.autocomplete.js"></script>
<link rel="stylesheet" type="text/css" href="js/jquery.autocomplete.css" />

<script type="text/javascript" src="scripts_date/jquery-calendar-1.7.2.js"></script>
<script language="javascript">

$(document).ready(function(){
$('#pay_date').datepicker({
showOn: 'both',
buttonImage: 'icons_date/calendar.gif',
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
dateFormat: 'dd-mm-yy'
});
});

$(document).ready(function(){
$('#add_date').datepicker({
showOn: 'both',
buttonImage: 'icons_date/calendar.gif',
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
dateFormat: 'dd-mm-yy'
});
});

$(document).ready(function(){
$('#cheque_date').datepicker({
showOn: 'both',
buttonImage: 'icons_date/calendar.gif',
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
dateFormat: 'dd-mm-yy'
});
});

$(document).ready(function(){
$('#from_date').datepicker({
showOn: 'both',
buttonImage: 'icons_date/calendar.gif',
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
dateFormat: 'dd-mm-yy'
});
});

$(document).ready(function(){
$('#to_date').datepicker({
showOn: 'both',
buttonImage: 'icons_date/calendar.gif',
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
dateFormat: 'dd-mm-yy'
});
});


</script>
</head>

<body>
<!------------------popup area (view, edit)--------------->



<!---------------popup area ends------------------>
<div class="viewEmployeePopup login-popup"  id="login-box" style="height: 220px;width:500px;
overflow-x: scroll;">
    <a href="#" class="close">
    <img src="" class="btn_close" title="Close Window" alt="Close" />
    </a>
    <div id="view_emp"></div>
</div>
<div class="wrapper">
  <div>
<?php 
	include("header.php");
?>
 
</div>
 
 <div class="tabbedArea">
   
  <div class="tabBody">
   <h1>Bank Book</h1>
   <form name="bankbook_frm" id="bankbook_frm" method="post"  >
   <div class="searchBox" style="width:93%; margin-right:0px;  padding:0px; ">
   <div align="left">
   <div style="padding:20px;">
    
    <div class="searchField" style="padding-left:20px;">
     <p class="input1">Vendor</p>
     <select name="bankid" class="input1">
         <option value="">-Select Bank-</option>
		 <?php 
		 $sq_resbank=mysql_query("select * from cf_bank"); 
		 while($resbank=mysql_fetch_array($sq_resbank))
			{
		?>
			<option value="<?=$resbank['bank_id']?>" <?php if($bank == $resbank['bank_id']){?> selected <?php } ?> ><?=$resbank['bankname']?></option>
        <?php
			} ?>
     </select>
    </div>
	
    <div class="searchField">
     <p class="input1">From Date</p>
    		<input type="text" name="from_date" id="from_date"  class="slabinput input1" style="width:70px;" value="<?=$from_date?>" required="required"/></td>

    </div>
   
    <div class="searchField">
     <p class="input1">To Date</p>
    		<input type="text" name="to_date" id="to_date"  class="slabinput input1" style="width:70px;" value="<?=$to_date?>" required="required"/></td>

    </div>

    <div class="searchField">
    <input name="searchbankbook" type="submit" class="input1" value="search" />
    </div>
  </div>
   </div>
   </div>
   </form>
      <div id="popup" align="center"  style="margin-left: 400px;"></div>
   <div id="list">
   
   
   <table  border="0" cellspacing="0" cellpadding="0" class="tableList">
   
     <?php
$i=1;

	
	  /*$sq_arr="SELECT a.*,b.ledgername as ledgername, 1 as type 
    FROM cf_reciept a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.from where cashed=2 
UNION SELECT a.*,b.ledgername as ledgername, 2 as type 
    FROM cf_voucher a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.to where cashed=2";*/
			
	$sq_arr="SELECT  0 as acctype,a.pay_date as pay_date,a.amount as amount,a.reciept_id as reciept_id,a.purpose as purpose,b.ledgername as ledgername, 1 as type 
    FROM cf_reciept a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.from where cashed=2 
UNION SELECT 0 as acctype,a.pay_date as pay_date,a.amount as amount,a.reciept_id as reciept_id,a.purpose as purpose,,b.ledgername as ledgername, 2 as type 
    FROM cf_voucher a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.to where cashed=2
	 UNION SELECT a.account_type as acctype,a.pay_date as pay_date,a.amount as amount,a.ref_no as reciept_id,a.contra_reference as purpose,b.ledgername as ledgername, 3 as type FROM cf_contra a LEFT JOIN cf_ledger b ON b.ledgerid = a.crd_dbt where a.type_book=2		
			
			";
			
	


$condtranbank ='';
$bankcond ='';
$cond='';
 $query=$sq_arr;
if(isset($_REQUEST['searchbankbook']) && $_REQUEST['searchbankbook']!='')
{

if(isset($_REQUEST['ledger_name']) && $_REQUEST['ledger_name']!=''){
$ledger_name=$_REQUEST['ledger_name'];
$cond.=" and   b.ledgername like '%".$ledger_name."%'";
}
if(isset($_REQUEST['from_date']) && $_REQUEST['from_date']!=''){
$from_date=date('Y-m-d',strtotime($_REQUEST['from_date']));
$cond.=" and   a.pay_date >= '$from_date'";
}
else
{
$from_date=date('Y-m-d');
$cond.=" and   a.pay_date >= '$from_date'";
}
if(isset($_REQUEST['to_date']) && $_REQUEST['to_date']!=''){
$to_date=date('Y-m-d',strtotime($_REQUEST['to_date']));
$cond.=" and   a.pay_date <= '$to_date'";
}
else
{
$to_date=date('Y-m-d');
$cond.=" and   a.pay_date <= '$to_date'";
}


if(isset($_REQUEST['bankid']) && $_REQUEST['bankid']!=''){
$bankid = $_REQUEST['bankid'];
$cond.=" and a.transaction_bank = '$bankid'";
$bankcond =" where bank_id = '$bankid'";
$condtranbank.= " transaction_bank = '$bankid' and ";
}

}
else
{
if(isset($_REQUEST['from_date']) && $_REQUEST['from_date']!=''){
$from_date=date('Y-m-d',strtotime($_REQUEST['from_date']));
$cond.=" and   a.pay_date >= '$from_date'";
}
else
{
$from_date=date('Y-m-d');
$cond.=" and   a.pay_date >= '$from_date'";
}
if(isset($_REQUEST['to_date']) && $_REQUEST['to_date']!=''){
$to_date=date('Y-m-d',strtotime($_REQUEST['to_date']));
$cond.=" and   a.pay_date <= '$to_date'";
}
else
{
$to_date=date('Y-m-d');
$cond.=" and   a.pay_date <= '$to_date'";
}

if(isset($_REQUEST['bankid']) && $_REQUEST['bankid']!=''){
$bankid = $_REQUEST['bankid'];
$cond.=" and a.transaction_bank = '$bankid'";
$condtranbank = " transaction_bank = '$bankid' and ";
}


}


$open_date='';$presentdate=date('Y-m-d',strtotime($from_date));

//echo $sql="select * from tbl_openingbal where open_date<='".$presentdate."' and cashorbank = '2'";
if(isset($_REQUEST['bankid']) && $_REQUEST['bankid']!=''){
$bankid = $_REQUEST['bankid'];
	$sql="select * from cf_bank where bank_id = '$bankid'";
}
else
{
	$sql="select sum(bank_open) as bank_open from cf_bank ";
}

//echo $sql;
$row=mysql_query($sql);
if($row && mysql_num_rows($row)>0){

$rows=mysql_fetch_array($row);
//{
$open_date='2014-07-22';
$open_amt=$rows['bank_open'];
//}

}

if($open_date!='')
{
$balancesum="select sum(amount) as totalreciept from cf_reciept where ".$condtranbank." cashed=2 and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ";
}
else
{
$balancesum="select sum(amount) as totalreciept from cf_reciept where cashed=2 and pay_date<'".$presentdate."' ";
}
 $balancesum;
$balancesum_row=mysql_query($balancesum);
if($balancesum_row && mysql_num_rows($balancesum_row)>0){

while($balancesum_rows=mysql_fetch_array($balancesum_row))
{
$totalreciept=$balancesum_rows['totalreciept'];
}

}

if($open_date!='')
{
$balancesumvoucher="select sum(amount) as totalpayment from cf_voucher where ".$condtranbank." cashed=2 and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ";
}
else
{
$balancesumvoucher="select sum(amount) as totalpayment from cf_voucher where cashed=2 and pay_date<'".$presentdate."' ";
}

$balancesumvoucher_row=mysql_query($balancesumvoucher);
if($balancesumvoucher_row && mysql_num_rows($balancesumvoucher_row)>0){

while($balancesumvoucher_rows=mysql_fetch_array($balancesumvoucher_row))
{
$totalpayment=$balancesumvoucher_rows['totalpayment'];
}

}


if($open_date!='')
{

$getotal_contra = 0;
//echo "select * from cf_contra where ".$condtranbank." type_book=2 and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ";
$balancesumcontra=mysql_query("select * from cf_contra where ".$condtranbank." type_book=2 and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ");
while($rescontra=mysql_fetch_array($balancesumcontra))
{  
	if($rescontra['account_type'] == '1')
	{
		$getotal_contra = $getotal_contra - $rescontra['amount'];
		//echo "<br";
	}
	else if($rescontra['account_type'] == '2')
	{
		$getotal_contra = $getotal_contra + $rescontra['amount'];
	}	
}
}
else
{
$balancesumcontra="select sum(amount) as totalcontra from cf_contra where type_book=2 and pay_date<'".$presentdate."' ";
}
/* $balancesumcontra;
$balancesumcontra_row=mysql_query($balancesumcontra);
if($balancesumcontra_row && mysql_num_rows($balancesumcontra_row)>0){

while($balancesumcontra_rows=mysql_fetch_array($balancesumcontra_row))
{
echo $totalcontra=$balancesumcontra_rows['totalcontra'];
}

}*/



/*echo "opening Balance : ".$open_amt;
echo "<br/>";
echo "Receipt Total : ".$totalreciept;
echo "<br/>";
echo "Voucher Total :".$totalpayment;
echo "<br/>";
echo "Contra Total : ".$getotal_contra;*/



$cumulative=$opening_balance=(($open_amt)+($totalreciept-$totalpayment+$getotal_contra));
?>
   <tr class="heading">
  <td colspan="8" align="right"> Opening Balance : <?=$opening_balance?></td>
  	</tr>

  <tr class="heading">
   <td>No</td>
	<td width="90">Date</td>
	<td>Ref No.</td>   
    <td>Ledger Name</td>
	<td>Description</td>   
	<td>Bank Name</td> 
    <td>Debit</td>
	<td>Credit</td>
	<td>Running Balance</td>
  	</tr>

<?php

/* $sq_arr="SELECT a.*,b.ledgername as ledgername, 1 as type 
    FROM cf_reciept a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.from where cashed=2 ".$cond."
UNION SELECT a.*,b.ledgername as ledgername, 2 as type 
    FROM cf_voucher a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.to where cashed=2 ".$cond."";*/
			
	$sq_arr="select * from (SELECT 0 as acctype,a.pay_date as pay_date,a.amount as amount,a.reciept_id as reciept_id,a.purpose as purpose,b.ledgername as ledgername, 1 as type 
    FROM cf_reciept a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.from where cashed=2 ".$cond."
UNION SELECT 0 as acctype,a.pay_date as pay_date,a.amount as amount,a.voucher_id as reciept_id,a.purpose as purpose,b.ledgername as ledgername, 2 as type 
    FROM cf_voucher a
        LEFT JOIN cf_ledger b
            ON b.ledgerid = a.to where cashed=2 ".$cond."
			UNION SELECT a.account_type as acctype,a.pay_date as pay_date,a.amount as amount,a.ref_no as reciept_id,a.contra_reference as purpose,b.ledgername as ledgername, 3 as type FROM cf_contra a LEFT JOIN cf_ledger b ON b.ledgerid = a.crd_dbt where a.type_book=2  ".$cond." ) k order by k.pay_date,type ASC";
			
			//echo $sq_arr;
$function="Searchdate";
//include("pagingserch.php");

if(isset($_REQUEST['page'])){
$page=$_REQUEST['page'];
}else{
$page=10;
}

$array=mysql_query($sq_arr);
if($array && mysql_num_rows($array)>0){
$pagecnt=0;
while($res=mysql_fetch_array($array))
{

$pagecnt++;

if($res['type'] !=3 )
	{
  ?> 
  <tr>
    <td><?php echo $pagecnt;?></td>
    <td><?php echo date('d-m-Y',strtotime($res['pay_date']));?></td>
	 
    <td><?php echo $res['reciept_id'];?></td>
	<td><?php echo $res['ledgername'];?></td>
	<td><?php echo $res['purpose'];?></td>
	<td><?php echo $res['purpose'];?></td>
	<?php if($res['type'] ==1 )
	{
	$cumulative=$cumulative+$res['amount'];
	?>
    <td><?php echo $res['amount'];?></td>
	<td>0</td>
	<?php
	
	}
	 ?>
	 <?php if($res['type'] ==2 )
	{
	$cumulative=$cumulative-$res['amount'];
	?>
	<td>0</td>
    <td><?php echo $res['amount'];?></td>
	 
	<?php
	}
	 ?>
	  <td><?php echo $cumulative;?></td>

   		
  </tr>
  
   <?php 
   } else {
   		?>
		<tr>
    
	<td><?php echo $pagecnt;?></td>
    <td><?php echo date('d-m-Y',strtotime($res['pay_date']));?></td>
	 
    <td><?php echo $res['reciept_id'];?></td>
	<?php 
		$sqledger = mysql_query("select cf_ledger.ledgername as ledger_name from cf_contra join cf_ledger on cf_contra.crd_dbt = cf_ledger.ledgerid where contra_reference = '".$res['purpose']."' and type_book = '1'");
	
	
		$rw = mysql_fetch_array($sqledger);
	?>
	<td><?php if($rw['ledger_name'] ==''){echo "BANK";}else{echo $rw['ledger_name'];}?></td>
	<td><?php echo "Contra";?></td>
	
	<td><?php echo $res['ledgername'];?></td>
	<?php if($res['type'] ==3 )
	{
	
	?>
	<?php 
	if($res['acctype'] ==1){
	$cumulative=$cumulative-$res['amount'];
	?>
		<td>0</td>
		<td><?php echo $res['amount'];?></td>
	<?php } else {
	$cumulative=$cumulative+$res['amount'];
	?>
	 	<td><?php echo $res['amount'];?></td>
	 	<td>0</td>
	<?php } ?>
		
   <?php }
  ?>
   <td><?php echo $cumulative;?></td>
  <?php 
    }}}
  ?>
    <tr class="heading">
  <td colspan="8" align="right"> Closing Balance : <?=$cumulative?></td>
  	</tr>

</table>

  <?php /*?><div align="right" style="padding-top:10px;"><?=$pagination?></div> <?php */?>
  </div>
  </div>
 </div>
 <div class="footer"><p>Powered by <a href="http://www.aadhocc.co.uk/" target="_blank">Aadhocc</a></p></div>
 
</div>
<?php /*?><div class="quicLInksCover">
 <?php 
	include("leftlink.php");
?>

</div><?php */?>
</body>
<!----------------------- script for autocomplete------------------------------>
		<script src="js/chosen.jquery.min.js" type="text/javascript">
		</script>
		<script type="text/javascript"> 
			$(".chzn-select").chosen({search_contains: true}); 
		</script>

<!----------------end of script -------------------------------->

</html>
