<?
session_start();

function startsWith($haystack, $needle) {
    // search backwards starting from haystack length characters from the end
    return $needle === "" || strrpos($haystack, $needle, -strlen($haystack)) !== FALSE;
}

if(isset($_SESSION['ses_name']))
{
$user=$_SESSION['ses_name'];
}else{
header("location:index.php");
}
$parent_name='';
$ledger_name='';
$veh_num='';
$sdate_12 ='';
$page='1';
include("include/config.php");
require_once "include/Database_functions.php";
$sqlObj=new sqlclass();
$msg = '';
$cumulative=0;
//------------- selected ledger -------//
if(isset($_REQUEST['lederid']) && $_REQUEST['lederid']!=''){
$lederid=$_REQUEST['lederid'];
}
else
{
$lederid=0;
}

//-------------from date --------------//
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');
}
//----------------To date-------------------//
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');
}

function print1($selectype,$lederid,$fromdate,$todate)
			{
			
				$dates = date('Y-m-d');
				
				$names = $selectype.$dates;
				$outstr = NULL;
				
				//echo "test";
				
			
				//header("Content-type: application/vnd.ms-excel");
			//	
				//header("Content-Disposition: attachment;Filename=".$names.".xls");
				
				//ob_start();
			
/*    header('Content-type: text/plain');
	header('Content-Disposition: attachment; filename="default-filename.txt"');
echo "test file";

exit;*/
			  //exit;
					//set the directory to get image			
					// Query database to get data
				
				//print_r($_REQUEST);
				$cond='';

				if(isset($_REQUEST['searchcashbook']) && $_REQUEST['searchcashbook']!='')
				{
								
				if(isset($_REQUEST['lederid']) && $_REQUEST['lederid']!=''){
				$lederid=$_REQUEST['lederid'];
				$cond.=" and   b.ledgerid = '".$lederid."'";
				}
				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'";
				}
				
				}
				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'";
				}
				}
				$cond.=" and   b.ledgerid = '".$lederid."'";
				
				//-----------calculate opening amount-------------------------------------------------------//
				$open_date='';
				$crdt='';
				$open_amt= 0;
				$totalpayment = 0;
				$totalreciept = 0;
				$presentdate=date('Y-m-d',strtotime($from_date));
				$sql="select * from cf_ledger where ledger_start_date<='".$presentdate."' and ledgerid = '".$lederid."'";
				$row=mysql_query($sql);
				if($row && mysql_num_rows($row)>0){
				while($rows=mysql_fetch_array($row)){
				$open_date=$rows['ledger_start_date'];
				$open_amt=$rows['opening_balance'];
				$crdt=$rows['cr_dt'];
				$ledgername=$rows['ledgername'];
				}
				}
				//----------------------For add balence from receipt--------------------------------------//
				if($open_date!=''){
				 $balancesum="select sum(amount) as totalreciept from cf_reciept where  `from`='".$lederid."' and pay_date<'".$presentdate."' and pay_date >='".$open_date."'  ";}
				else{
				 $balancesum="select sum(amount) as totalreciept from cf_reciept where  `from`='".$lederid."' and pay_date<'".$presentdate."' ";}
				
				$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'];
				}
				}
				//echo $balancesum;
				//-----------------------For add balence from voucher-----------------------------------------//
				if($open_date!=''){
				 $balancesumvoucher="select sum(amount) as totalpayment from cf_voucher where  `to`='".$lederid."' and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ";}
				else{
				 $balancesumvoucher="select sum(amount) as totalpayment from cf_voucher where  `to`='".$lederid."' 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'];
				}
				}
				
				//---------------------------total journal calculation ---------------------------------------------//
				$getotal_journal =0;
				$journal_contra = 0;
				if($open_date!=''){
				$balancesumjournal=mysql_query("select * from cf_journal where `crd_dbt`='".$lederid."'  and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ");}
				else{
				$balancesumjournal=mysql_query("select * from cf_journal where `crd_dbt`='".$lederid."' and pay_date<'".$presentdate."' ");
				}
				if($balancesumjournal && mysql_num_rows($balancesumjournal)>0){
				while($resjournal=mysql_fetch_array($balancesumjournal))
				{  
					if($resjournal['account_type'] == '1')
					{
						$getotal_journal = $getotal_journal - $resjournal['amount'];
						
					}
					else if($resjournal['account_type'] == '2')
					{
						$getotal_journal = $getotal_journal + $resjournal['amount'];
					}	
				}
				}
				//---------------------------End-------------------------------------------------------------------//
				/*echo "opening Balance : ".$open_amt;
				echo "<br/>";
				echo "Receipt Total : ".$totalreciept;
				echo "<br/>";
				echo "Voucher Total :".$totalpayment;
				echo "<br/>";
				echo "Contra Total : ".$getotal_journal;*/
				
				
				
				$cumulative=$opening_balance=(($open_amt)-($totalreciept+$totalpayment+$getotal_journal));
				
				
				if($crdt==1) {  $cumulative = 0-$cumulative; }
				
				//$cumulative=$opening_balance=(($open_amt)+($totalreciept-$totalpayment));
				
					
					$outstr.='<div id="header" >
					<table  border="0" cellspacing="0" cellpadding="0" class="tableList">
					<tr class="heading">
						<td>Ledger Name</td>
						<td>'.$ledgername.'</td>
						<td>'.date(('d-m-y'),strtotime($_REQUEST['from_date'])).'</td>
						<td>'.date(('d-m-y'),strtotime($_REQUEST['to_date'])).'</td>
						<td></td>
						<td></td>
					</tr>
					<tr class="heading">
						<td>No</td>
						<td>Date</td>
						<td>Ledger Name</td>
						<td>Debit</td>
						<td>Credit</td>
						<td>Running Balance</td>
					</tr>';
					
					
					
						if($opening_balance >0){
					
					$outstr.= '<tr class="heading">
						<td>No</td>
						<td>'.date(('d-m-Y'),strtotime($open_date)).'</td>
						<td>Opening Balance</td>
						<td>'.(($crdt==2)? $opening_balance : "0").'</td>
						<td>'.(($crdt==1)? $opening_balance : "0").' </td>
						<td>'.$cumulative.'</td>
					</tr>';
					 } 
					 
		
//echo $cond;
 $sq_arr11="select * from (SELECT a.transaction_bank  as toid,0 as acctype,a.pay_date as pay_date,a.amount as amount,a.reciept_id as reciept_id,a.purpose as purpose,0 as ref,b.ledgername as ledgername, 1 as type 
    	  FROM cf_reciept a
          LEFT JOIN cf_ledger b ON b.ledgerid = a.from where 1 ".$cond."
          UNION SELECT a.transaction_bank  as toid,0 as acctype,a.pay_date as pay_date,a.amount as amount,a.voucher_id as reciept_id,a.purpose as purpose,0 as ref,b.ledgername as ledgername, 2 as type 
          FROM cf_voucher a
          LEFT JOIN cf_ledger b ON b.ledgerid = a.to where 1 ".$cond."
		  
	UNION SELECT a.journal_id as toid,a.account_type as acctype,a.pay_date as pay_date,a.amount as amount,a.ref_no as reciept_id,a.purpose as purpose,a.journal_reference as ref,b.ledgername as ledgername, 3 as type 
		  FROM cf_journal a 
		  LEFT JOIN cf_ledger b ON b.ledgerid = a.crd_dbt where 1  ".$cond."
		  
		  UNION SELECT  a.pur_acc_id as toid,a.account_type as acctype,a.pay_date as pay_date ,a.acc_amount as amount,a.account_reference as reciept_id,b.ledgername as purpose,0 as ref,b.ledgername as ledgername, 4 as type FROM cf_purchase_account a 
			LEFT JOIN cf_ledger b ON b.ledgerid = a.ven_client_id where 1 ".$cond. " )  k order by k.pay_date ASC
		  ";
		 $query=$sq_arr11; 
		 //echo $sq_arr11;
			
/*$function="Searchdate";
include("pagingserch.php");
if(isset($_REQUEST['page'])){
$page=$_REQUEST['page'];
}else{
$page=10;
}*/

$array11=mysql_query($sq_arr11);
if($array11 && mysql_num_rows($array11)>0){
$pagecnt=1;
while($res=mysql_fetch_array($array11))
{
//echo '<pre>';
//print_r($res);
$pagecnt++;	
$toledgername = '';	
//---------------------Ledger name calculation---------------//
//------------------for receipt and voucher--------//
if($res['type'] ==1 || $res['type'] ==2){	
$bankid = $res['toid'];
if($bankid != 0){
	$tblledger=mysql_query("select ledgername from cf_ledger where ledgertype = 'bankledger' and empid = ".$bankid."");
	if($tblledger && mysql_num_rows($tblledger)>0){
	$resledger=mysql_fetch_array($tblledger);							
	$toledgername = $resledger['ledgername'];}
}else{
	 $toledgername = "CASH";
	 }	
}		
//------------------for journal--------------------//
$jledgername = '';
if($res['type'] ==3 ){

$tbljournal = mysql_query("select cf_ledger.ledgername as ledger_name from cf_journal join cf_ledger on cf_journal.crd_dbt = cf_ledger.ledgerid where journal_reference = '".$res['ref']."' and journal_id != '".$res['toid']."'");
		if($tbljournal && mysql_num_rows($tbljournal)>0)
		{
			while($result=mysql_fetch_array($tbljournal))
			{
			  $jledgername[]= $result['ledger_name'];
			  		
			}
		}
$jledgername = array_unique($jledgername);
$toledgername = implode(" @ ",$jledgername);		
}	


//------------------for Purchase--------------------//
/*$jledgername = '';
if($res['type'] ==4 ){

$tbljournal = mysql_query("select cf_ledger.ledgername as ledger_name from cf_purchase_account join cf_ledger on cf_purchase_account.crd_dbt = cf_ledger.ledgerid where account_reference = '".$res['ref']."' and journal_id != '".$res['toid']."'");
		if($tbljournal && mysql_num_rows($tbljournal)>0)
		{
			while($result=mysql_fetch_array($tbljournal))
			{
			  $jledgername[]= $result['ledger_name'];
			  		
			}
		}
$jledgername = array_unique($jledgername);
$toledgername = implode(" @ ",$jledgername);		
}*/

//echo $toledgername;
//--------------------End-----------------------------------//
//echo $cumulative = $cumulative +;

$outstr.= '<tr>
 	<td>'.$pagecnt.'</td>
    <td>'.date('d-m-Y',strtotime($res['pay_date'])).'</td>
	<td>'.$toledgername.'<span style="color:#990033">------:('.$res['purpose'].')</span></td>';
	
	if($res['type'] ==1 ){
	$cumulative=$cumulative-$res['amount'];
	
	$outstr.= '<td>0</td>
	<td>'.$res['amount'].'</td>';
	 } 
	
	if($res['type'] ==2 ){
	$cumulative=$cumulative+$res['amount'];
	
	
	$outstr.= '<td>'.$res['amount'].'</td> 
	<td>0</td>';
    
	 } 
	
	 if($res['type'] ==3 )
	{ 
	if($res['acctype'] ==1){
	$cumulative=$cumulative-$res['amount'];
	
	$outstr.= '<td>0</td>
				<td>'.$res['amount'].'</td>';
				

	 } else {
	$cumulative=$cumulative+$res['amount'];
	
	 $outstr.= '<td>'.$res['amount'].'</td>
	 	<td>0</td>';
	 } 
	}
	
	
	if($res['type'] ==4 )
	{ 
	if($res['acctype'] ==1){
	$cumulative=$cumulative-$res['amount'];
	
	$outstr.= '<td>0</td>
				<td>'.$res['amount'].'</td>';
				

	 } else {
	$cumulative=$cumulative+$res['amount'];
	
	$outstr.= '<td>'.$res['amount'].'</td>
	 	<td>0</td>';
	 } 
	}
	
	$outstr.= '<td>'.$cumulative.'</td>	
</tr>';
 
 }}
  
 $outstr.= '</tr>
    <tr class="heading">
       <td colspan="6" align="right"> Closing Balance : '.$cumulative.'</td>
  	</tr>

</table>	 

				 </table>
					</div>'; 
				//echo $outstr;
				
			/*foreach (glob('/*.xls') as $file) {
				
				echo $file;
			
			}*/

			
			if ($handle = opendir('.')) {

    while (false !== ($entry = readdir($handle))) {

        if ($entry != "." && $entry != "..") {

             $ext = end(explode(".",$entry));
			if($ext=="xls" && startsWith($entry, "Export") === true)	
				
				unlink($entry);
        }
    }

    closedir($handle);
}


			
				
				$fname = "Export".time().".xls";
				  $file = fopen($fname,"w+");
				  fwrite($file,$outstr);
				  fclose($file);
				  header('Location:'.$fname);
				 // header('Content-Type: application/vnd.ms-excel');
				  //header('Content-Disposition: attachment; filename="'.basename($fname).'"');
				  //readfile($fname);
				 //unlink($fname); 
				
				
				//ob_end_clean(); 
				exit;
				
				 
			
				
			}
			
			
				
			if(isset($_POST['download']))
			{
				$selectype = "Ledger New";
				$lederid = $_POST['lederid'];
				$fromdate = $_POST['from_date'];
				$todate = $_POST['to_date'];
				
				
				print1($selectype,$lederid,$fromdate,$todate);
			}

?>

<!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>Ledger List</h1>
   <form name="cashbook_frm" id="cashbook_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="width: 250px;">
	   <p class="input1">Ledger</p>
	  <select class="chzn-select restselect input1 received_to" name="lederid" required="required" >
     <option value="">Select Ledger</option>
       <?php 
         //$sq_tripno="select * from cf_ledger where ledgertype != 'bankledger' and ledgerid != '195'";
		 $sq_tripno="select * from cf_ledger";
   $arrtripno1=mysql_query($sq_tripno);
   while($restripno=mysql_fetch_array($arrtripno1))
   {
  ?>
   <option  value="<?=$restripno['ledgerid']?>"  <?php if($lederid == $restripno['ledgerid']){?> selected="selected"<?php } ?> ><?=$restripno['ledgername']?></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="searchcashbook" type="submit" class="input1" value="search" />
    </div>
	
	<div class="searchField" style="padding-left:60px;margin-right: 20px;">
    <input name="download" type="submit" class="input1"  value="download" />
    </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;
//-----selected ledeger name ---------------------//
$searchledgername ='';
$sqlledger="select * from cf_ledger where ledgerid='".$lederid."'";
$row=mysql_query($sqlledger);
if($row && mysql_num_rows($row)>0){
while($rows=mysql_fetch_array($row)){
$searchledgername=$rows['ledgername'];
}
}	
//-----------end----------------------------------//
	 /* $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 a.from='".$lederid."' 
               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 a.to='".$lederid."'
			   UNION SELECT a.*,b.ledgername as ledgername, 2 as type FROM cf_journal a
               LEFT JOIN cf_ledger b ON b.ledgerid = a.crd_dbt where a.crd_dbt='".$lederid."'
			   ";*/
			   

$cond='';

if(isset($_REQUEST['searchcashbook']) && $_REQUEST['searchcashbook']!='')
{
if(isset($_REQUEST['lederid']) && $_REQUEST['lederid']!=''){
$lederid=$_REQUEST['lederid'];
$cond.=" and   b.ledgerid = '".$lederid."'";
}
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'";
}

}
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'";
}
}
//-----------calculate opening amount-------------------------------------------------------//
$open_date='';
$crdt='';
$open_amt= 0;
$totalpayment = 0;
$totalreciept = 0;
$presentdate=date('Y-m-d',strtotime($from_date));
$sql="select * from cf_ledger where ledger_start_date<='".$presentdate."' and ledgerid = '".$lederid."'";
$row=mysql_query($sql);
if($row && mysql_num_rows($row)>0){
while($rows=mysql_fetch_array($row)){
$open_date=$rows['ledger_start_date'];
$open_amt=$rows['opening_balance'];
$crdt=$rows['cr_dt'];
}
}
//----------------------For add balence from receipt--------------------------------------//
if($open_date!=''){
 $balancesum="select sum(amount) as totalreciept from cf_reciept where  `from`='".$lederid."' and pay_date<'".$presentdate."' and pay_date >='".$open_date."'  ";}
else{
 $balancesum="select sum(amount) as totalreciept from cf_reciept where  `from`='".$lederid."' and pay_date<'".$presentdate."' ";}

$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'];
}
}
//echo $balancesum;
//-----------------------For add balence from voucher-----------------------------------------//
if($open_date!=''){
 $balancesumvoucher="select sum(amount) as totalpayment from cf_voucher where  `to`='".$lederid."' and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ";}
else{
 $balancesumvoucher="select sum(amount) as totalpayment from cf_voucher where  `to`='".$lederid."' 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'];
}
}

//---------------------------total journal calculation ---------------------------------------------//
$getotal_journal =0;
$journal_contra = 0;
if($open_date!=''){
$balancesumjournal=mysql_query("select * from cf_journal where `crd_dbt`='".$lederid."'  and pay_date<'".$presentdate."' and pay_date >='".$open_date."' ");}
else{
$balancesumjournal=mysql_query("select * from cf_journal where `crd_dbt`='".$lederid."' and pay_date<'".$presentdate."' ");
}
if($balancesumjournal && mysql_num_rows($balancesumjournal)>0){
while($resjournal=mysql_fetch_array($balancesumjournal))
{  
	if($resjournal['account_type'] == '1')
	{
		$getotal_journal = $getotal_journal - $resjournal['amount'];
		
	}
	else if($resjournal['account_type'] == '2')
	{
		$getotal_journal = $getotal_journal + $resjournal['amount'];
	}	
}
}
//---------------------------End-------------------------------------------------------------------//
/*echo "opening Balance : ".$open_amt;
echo "<br/>";
echo "Receipt Total : ".$totalreciept;
echo "<br/>";
echo "Voucher Total :".$totalpayment;
echo "<br/>";
echo "Contra Total : ".$getotal_journal;*/

$cumulative=$opening_balance=(($open_amt)-($totalreciept+$totalpayment+$getotal_journal));


if($crdt==1) {  $cumulative = 0-$cumulative; }

//$cumulative=$opening_balance=(($open_amt)+($totalreciept-$totalpayment));
?>
	<?php /*?><tr class="heading">
	  <td colspan="5" align="left"> Ledger Name : <?=$searchledgername?></td>
	  <td colspan="1" align="right"> Opening Balance : <?=$opening_balance?></td>	
	</tr>
<?php */?>
    <tr class="heading">
		<td>No</td>
		<td>Date</td>
		<td>Ledger Name</td>
		<td>Debit</td>
		<td>Credit</td>
		<td>Running Balance</td>
  	</tr>
	
	
	<?php 
		if($opening_balance >0){
	?>
	<tr class="heading">
		<td>No</td>
		<td><?php echo date(('d-m-Y'),strtotime($open_date)) ?></td>
		<td>Opening Balance</td>
		<td><?php if($crdt==2) { echo $opening_balance; } else { echo "0"; }?></td>
		<td><?php if($crdt==1) { echo $opening_balance; } else { echo "0"; }?></td>
		<td><?php echo $cumulative;?></td>
  	</tr>
	<?php } ?>

<?php
//echo $cond;
 $sq_arr11="select * from (SELECT a.transaction_bank  as toid,0 as acctype,a.pay_date as pay_date,a.amount as amount,a.reciept_id as reciept_id,a.purpose as purpose,0 as ref,b.ledgername as ledgername, 1 as type 
    	  FROM cf_reciept a
          LEFT JOIN cf_ledger b ON b.ledgerid = a.from where 1 ".$cond."
          UNION SELECT a.transaction_bank  as toid,0 as acctype,a.pay_date as pay_date,a.amount as amount,a.voucher_id as reciept_id,a.purpose as purpose,0 as ref,b.ledgername as ledgername, 2 as type 
          FROM cf_voucher a
          LEFT JOIN cf_ledger b ON b.ledgerid = a.to where 1 ".$cond."
		  
	UNION SELECT a.journal_id as toid,a.account_type as acctype,a.pay_date as pay_date,a.amount as amount,a.ref_no as reciept_id,a.purpose as purpose,a.journal_reference as ref,b.ledgername as ledgername, 3 as type 
		  FROM cf_journal a 
		  LEFT JOIN cf_ledger b ON b.ledgerid = a.crd_dbt where 1  ".$cond."
		  
		  UNION SELECT  a.pur_acc_id as toid,a.account_type as acctype,a.pay_date as pay_date ,a.acc_amount as amount,a.account_reference as reciept_id,b.ledgername as purpose,0 as ref,b.ledgername as ledgername, 4 as type FROM cf_purchase_account a 
			LEFT JOIN cf_ledger b ON b.ledgerid = a.ven_client_id where 1 ".$cond. " )  k order by k.pay_date ASC
		  ";
		 $query=$sq_arr11; 
		 //echo $sq_arr11;
			
$function="Searchdate";
include("pagingserch.php");
if(isset($_REQUEST['page'])){
$page=$_REQUEST['page'];
}else{
$page=10;
}

$array11=mysql_query($sq_arr11);
if($array11 && mysql_num_rows($array11)>0){
$pagecnt=$start;
while($res=mysql_fetch_array($array11))
{
//echo '<pre>';
//print_r($res);
$pagecnt++;	
$toledgername = '';	
//---------------------Ledger name calculation---------------//
//------------------for receipt and voucher--------//
if($res['type'] ==1 || $res['type'] ==2){	
$bankid = $res['toid'];
if($bankid != 0){
	$tblledger=mysql_query("select ledgername from cf_ledger where ledgertype = 'bankledger' and empid = ".$bankid."");
	if($tblledger && mysql_num_rows($tblledger)>0){
	$resledger=mysql_fetch_array($tblledger);							
	$toledgername = $resledger['ledgername'];}
}else{
	 $toledgername = "CASH";
	 }	
}		
//------------------for journal--------------------//
$jledgername = '';
if($res['type'] ==3 ){

$tbljournal = mysql_query("select cf_ledger.ledgername as ledger_name from cf_journal join cf_ledger on cf_journal.crd_dbt = cf_ledger.ledgerid where journal_reference = '".$res['ref']."' and journal_id != '".$res['toid']."'");
		if($tbljournal && mysql_num_rows($tbljournal)>0)
		{
			while($result=mysql_fetch_array($tbljournal))
			{
			  $jledgername[]= $result['ledger_name'];
			  		
			}
		}
$jledgername = array_unique($jledgername);
$toledgername = implode(" @ ",$jledgername);		
}	


//------------------for Purchase--------------------//
/*$jledgername = '';
if($res['type'] ==4 ){

$tbljournal = mysql_query("select cf_ledger.ledgername as ledger_name from cf_purchase_account join cf_ledger on cf_purchase_account.crd_dbt = cf_ledger.ledgerid where account_reference = '".$res['ref']."' and journal_id != '".$res['toid']."'");
		if($tbljournal && mysql_num_rows($tbljournal)>0)
		{
			while($result=mysql_fetch_array($tbljournal))
			{
			  $jledgername[]= $result['ledger_name'];
			  		
			}
		}
$jledgername = array_unique($jledgername);
$toledgername = implode(" @ ",$jledgername);		
}*/

//echo $toledgername;
//--------------------End-----------------------------------//
//echo $cumulative = $cumulative +;
  ?> 
<tr>
 	<td><?php echo $pagecnt;?></td>
    <td><?php echo date('d-m-Y',strtotime($res['pay_date']));?></td>
	<td><?php echo $toledgername?><span style="color:#990033">------:(<?php echo $res['purpose']?> )</span></td>
	<!----------for receipt-------------->
	<?php if($res['type'] ==1 ){
	$cumulative=$cumulative-$res['amount'];
	?>
	<td>0</td>
	<td><?php echo $res['amount'];?></td>
	<?php } ?>
	<!--------for voucher---------------->
	<?php if($res['type'] ==2 ){
	$cumulative=$cumulative+$res['amount'];
	?>
	
	<td><?php echo $res['amount'];?></td> 
	<td>0</td>
    
	<?php } ?>
	<!--------for journal---------------->
	<?php if($res['type'] ==3 )
	{ 
	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 } 
	}?>
	
	<!--------for Purchase---------------->
	<?php if($res['type'] ==4 )
	{ 
	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 } 
	}?>
	
	<td><?php echo $cumulative;?></td>	
</tr>
 
<?php }}?>
  
 </tr>
    <tr class="heading">
       <td colspan="6" align="right"> Closing Balance : <?=$cumulative?></td>
  	</tr>

</table>

  <div align="right" style="padding-top:10px;"><?=$pagination?></div> 
  </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>