<?PHP
ob_start();
session_start();
include 'connect.php';
if(isset($_SESSION['adminemail']))
{
$user=$_SESSION['adminemail'];

$gtp=mysqli_query($conn,"select * from admin where username='$user'");
$gtp1=mysqli_fetch_assoc($gtp);

include 'top.php';
?>


<div id="page-wrapper" >
          <div id="page-inner">
<div class="main-content">
				<div class="main-content-inner">
					<div class="breadcrumbs ace-save-state" id="breadcrumbs">
						<ul class="breadcrumb">
							<li>
								<i class="ace-icon fa fa-home home-icon"></i>
								<a href="#">Home</a>
							</li>
							<li class="active">Bank Ledger</li>
						</ul><!-- /.breadcrumb -->
					</div>

					<div class="page-content">
			

						<div class="page-header">
                        
<?php

$aca=mysqli_query($conn,"select * from financial where user='$user' order by ID desc");
$aca1=mysqli_fetch_assoc($aca);

$financial=$aca1['financial'];

?>                        
                        
							<table style="width:100%;">
                        
                        <tr><td style="width:50%;">
                        
							<h1>
								Bank Ledger
							</h1>
                            
                            </td>
                            
                            <td style="width:50%; text-align:right; padding-right:15px; font-size:15px;">
                            
                            <i class="fa fa-calendar"></i>&nbsp; <?php echo $financial; ?>
                            
                            </td></tr></table>
                            
						</div><!-- /.page-header -->
<br />

						<div class="row">
							<div class="col-xs-12">
                            
								<!-- PAGE CONTENT BEGINS -->
                                
<script src="assets/js/jquery.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.9.0/css/bootstrap-datepicker.min.css">

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.9.0/js/bootstrap-datepicker.min.js"></script>
<script>
  $(function() {
  // Initialize the datepicker for the "From Date" input
  $("#from-datepicker").datepicker({
    format: "mm/dd/yyyy",
    autoclose: true,
    todayHighlight: true
  }).on("show", function(e) {
    var inputOffset = $(this).offset();
    var inputHeight = $(this).outerHeight();
    var datepickerDropdown = $(".datepicker-dropdown");

    datepickerDropdown.css({
      top: inputOffset.top + inputHeight + 10,
      left: inputOffset.left
    });
  });

  // Initialize the datepicker for the "To Date" input
  $("#to-datepicker").datepicker({
    format: "mm/dd/yyyy",
    autoclose: true,
    todayHighlight: true
  }).on("show", function(e) {
    var inputOffset = $(this).offset();
    var inputHeight = $(this).outerHeight();
    var datepickerDropdown = $(".datepicker-dropdown");

    datepickerDropdown.css({
      top: inputOffset.top + inputHeight + 10,
      left: inputOffset.left
    });
  });
});
</script>
<script>
	
jQuery(function($){
		   
	// simple jQuery validation script
	$('#work').submit(function(){
		
		var valid = true;
		var errormsg = 'This field is required!';
		var errorcn = 'error';
		
		$('.' + errorcn, this).remove();			
		
		$('#required', this).each(function(){
			var parent = $(this).parent();
			if( $(this).val() == '' ){
				var msg = $(this).attr('title');
				msg = (msg != '') ? msg : errormsg;
				$('<span style="font-size:13px;color:#FF0000; padding-left:3px;" class="'+ errorcn +'">'+ msg +'</span>')
					.appendTo(parent)
					.fadeIn('fast')
					.click(function(){ $(this).remove(); });
				valid = false;
			};
		});
		
		return valid;
	});
	
});	

</script>
								
<?php


if($_GET['ac'])

{



$ac=$_GET['ac'];



	if (isset($_GET['s'])) 
	{
	 $status=$_GET['s'];
		 if($status=='5')
		 {
		 echo '<div class="alert alert-success">
				<a class="close" data-dismiss="alert">x</a>
					<strong>Values deleted successfully.</strong>
			  </div>';
		 }
	}



if(isset($_GET['submit']))

{

$fromdate=$_GET['fromdate'];

$todate=$_GET['todate'];



?>



<script type="text/javascript">

// Popup window code

function newPopup(url) {

	popupWindow = window.open(

		url,'popUpWindow','height=700,width=1000,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=no,onscreendisplay=yes,menubar=no,location=no,directories=no,status=yes')

}

</script>



<center>



<table>

<tr>



<td style="text-align:center; padding-right:5px;">

<a href="JavaScript:newPopup('printbankledger.php?ac=<?php echo $ac; ?>&fromdate=<?php echo $fromdate; ?>&todate=<?php echo $todate; ?>');"><button type="button" class="btn btn-white btn-primary"><i class="fa fa-print"></i> Print</button></a>

</td>

</tr>

</table>



</center><br />



<?php



$frm=date('Y-m-d',strtotime($fromdate));

$to=date('Y-m-d',strtotime($todate));



$bn=mysqli_query($conn,"select * from bank where ID='$ac'");

$bn1=mysqli_fetch_assoc($bn);



echo "



<center><b>Period : ".$fromdate." - ".$todate."</b></center><br />



<table style='width:100%;'>



<tr><td style='width:50%;'>Bank : <b>".$bn1['bank']."</b></td><td style='width:50%; text-align:right;'>Account Name : <b>".$bn1['accountname']."</b></td></tr>



</table>



<center><table class='table table-striped table-bordered table-condensed'>

<thead><th width='10%'>Date</th><th width='30%'>Description</th><th style='width:12%;'>Type</th><th style='width:10%;'>Voucher No</th><th width='12%'>Credit</th><th width='12%'>Debit</th><th width='12%'>Closing Balance</th></thead>";



$ex=mysqli_query($conn,"select sum(amount) as amount from expense where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and account='$ac'");

$ex1=mysqli_fetch_assoc($ex);


$py=mysqli_query($conn,"select sum(amount) as amount from payment where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and account='$ac'");

$py1=mysqli_fetch_assoc($py);



$rc=mysqli_query($conn,"select sum(amount) as amount from receipt where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and account='$ac'");

$rc1=mysqli_fetch_assoc($rc);



$opl=mysqli_query($conn,"select * from bank_ledger where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and accountname='$ac'");

$opl1=mysqli_fetch_assoc($opl);




$jur=mysqli_query($conn,"select sum(amount) as amount from journal where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and debit='$ac'");

$jur1=mysqli_fetch_assoc($jur);



$juri=mysqli_query($conn,"select sum(amount) as amount from journal where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and credit='$ac'");

$juri1=mysqli_fetch_assoc($juri);




$opcr=0;

$opdb=0;

if($opl1['paymenttype']=='debit')

{

$opdb=$opl1['amount'];

}

elseif($opl1['paymenttype']=='credit')

{

$opcr=$opl1['amount'];

}



$tex=$py1['amount']+$ex1['amount']+$opcr+$juri1['amount'];

$trc=$rc1['amount']+$opdb+$jur1['amount'];



if($trc>$tex)

{

$bl=$trc-$tex;



echo"<tr><td colspan='3'>B/F</td><td></td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td><td></td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td></tr>";



$db=$bl;

$cr=0;



}

elseif($tex>$trc)

{

$bl=$tex-$trc;



echo"<tr><td colspan='3'>B/F</td><td></td><td></td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td><td style='text-align:right;'>-".number_format($bl,2,'.','')."</td></tr>";



$db=0;

$cr=$bl;



}

else

{

$bl=0;

$cr=0;

$db=0;

}





$sql=mysqli_query($conn,"

select time as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Expense' as type from expense where account='$ac' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, customer as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Receipt' as type from receipt where account='$ac' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, supplier as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Payment' as type from payment where account='$ac' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT('Opening Balance ') as details, 'Opening Balance' as type from bank_ledger where accountname='$ac' and paymenttype='credit' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, amount as debit, NULL as credit, CONCAT('Opening Balance ') as details, 'Opening Balance' as type from bank_ledger where accountname='$ac' and paymenttype='debit' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Journal' as type from journal where credit='$ac' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, NULL as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Journal' as type from journal where debit='$ac' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'



order by date asc, time asc") or die(mysqli_error($conn));



/*$cr=0;

$db=0;*/

while($result=mysqli_fetch_assoc($sql))

{



$credit=$result['credit'];

$debit=$result['debit'];



if($debit)$debit=''.number_format($debit,2,'.','');

if($credit)$credit=''.number_format($credit,2,'.','');



if($result['type']=='Receipt')

{

$gn=mysqli_query($conn,"select * from customer where ID='$result[name]'");

$gn1=mysqli_fetch_assoc($gn);

}

elseif($result['type']=='Payment')

{

$gn=mysqli_query($conn,"select * from supplier where ID='$result[name]'");

$gn1=mysqli_fetch_assoc($gn);

}



$cr=$cr+$result['credit'];

$db=$db+$result['debit'];



$bl=$db-$cr;



echo"<tr><td>".date('d-m-Y',strtotime($result['date']))."</td><td>";



if($result['type']!='Opening Balance' && $result['type']!='Journal' && $result['type']!='Expense')

{



if($result['type']=='Receipt')

{

echo"".$result['details']." by ".$gn1['name']."";

}

elseif($result['type']=='Payment')

{

echo"".$result['details']." to ".$gn1['name']."";

}



}

else

{

echo"".$result['details']."";

}



echo"</td><td>".$result['type']."</td><td>".$result['ID']."</td><td style='text-align:right;'>".$debit."</td><td style='text-align:right;'>".$credit."</td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td></tr>";



}





echo"<tr><td colspan='4' style='text-align:right;'>Total</td><td style='text-align:right;'>".number_format($db,2,'.','')."</td><td style='text-align:right;'>".number_format($cr,2,'.','')."</td><td></td></tr>";



if($db>$cr)

{

$cb=$db-$cr;



echo"<tr><td colspan='4' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td style='text-align:right;'></td><td></td></tr>";

}

elseif($cr>$db)

{

$cb=$cr-$db;



echo"<tr><td colspan='4' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'></td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td></td></tr>";

}

else

{

echo"<tr><td colspan='4' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>NIL</td><td style='text-align:right;'>NIL</td><td></td></tr>";

}



echo"</table><br /><br />";



echo"<center><a href='javascript:history.back();'>Back</a></center>";



}



else

{





?>



<script type="text/javascript">

// Popup window code

function newPopup(url) {

	popupWindow = window.open(

		url,'popUpWindow','height=700,width=1000,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=no,onscreendisplay=yes,menubar=no,location=no,directories=no,status=yes')

}

</script>



<center>



<table>

<tr>



<td style="text-align:center; padding-right:5px;">

<a href="JavaScript:newPopup('printbankledger.php?ac=<?php echo $ac; ?>');"><button type="button" class="btn btn-white btn-primary"><i class="fa fa-print"></i> Print</button></a>

</td>

</tr>

</table>



</center><br />



<?php



echo"



<center>	



<form action='bank_ledger.php' method='get' id='wrk'>



<input type='hidden' name='ac' value='".$ac."' />



	<table>

	

	<tr><td>From Date  <input class='form-control' style='width: 30ch; margin-bottom:10px' type='text' name='fromdate' class='fromdate' id='from-datepicker' title='*' /required></td>

	

	<td style='padding-left:15px;'>To Date  <input class='form-control' style='width: 30ch; margin-bottom:10px' type='text' name='todate' class='todate' id='to-datepicker' title='*' /required></td></tr>

	

	</table><br>

	

	<button type='submit' name='submit' class='btn btn btn-primary' value='submit'>Submit</button>

	

</form>



	</center><br>";



$bn=mysqli_query($conn,"select * from bank where ID='$ac'");

$bn1=mysqli_fetch_assoc($bn);



echo "



<table style='width:100%;'>



<tr><td style='width:50%;'>Bank : <b>".$bn1['bank']."</b></td><td style='width:50%; text-align:right;'>Account Name : <b>".$bn1['accountname']."</b></td></tr>



</table>



<center><table class='table table-striped table-bordered table-condensed'>

<thead><th width='10%'>Date</th><th width='30%'>Description</th><th style='width:12%;'>Type</th><th style='width:10%;'>Voucher No</th><th width='12%'>Credit</th><th width='12%'>Debit</th><th width='12%'>Closing Balance</th></thead>";



$sql=mysqli_query($conn,"

select time as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Expense' as type from expense where account='$ac' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, customer as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Receipt' as type from receipt where account='$ac' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, supplier as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Payment' as type from payment where account='$ac' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT('Opening Balance ') as details, 'Opening Balance' as type from bank_ledger where accountname='$ac' and paymenttype='credit' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, amount as debit, NULL as credit, CONCAT('Opening Balance ') as details, 'Opening Balance' as type from bank_ledger where accountname='$ac' and paymenttype='debit' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Journal' as type from journal where credit='$ac' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, NULL as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Journal' as type from journal where debit='$ac' and date between '$aca1[fromdate]' and '$aca1[todate]'



order by date asc, time asc") or die(mysqli_error($conn));



$cr=0;

$db=0;

while($result=mysqli_fetch_assoc($sql))

{



$credit=$result['credit'];

$debit=$result['debit'];



if($debit)$debit=''.number_format($debit,2,'.','');

if($credit)$credit=''.number_format($credit,2,'.','');



if($result['type']=='Receipt')

{

$gn=mysqli_query($conn,"select * from customer where ID='$result[name]'");

$gn1=mysqli_fetch_assoc($gn);

}

elseif($result['type']=='Payment')

{

$gn=mysqli_query($conn,"select * from supplier where ID='$result[name]'");

$gn1=mysqli_fetch_assoc($gn);

}



$cr=$cr+$result['credit'];

$db=$db+$result['debit'];



$bl=$db-$cr;



echo"<tr><td>".date('d-m-Y',strtotime($result['date']))."</td><td>";



if($result['type']!='Opening Balance' && $result['type']!='Journal' && $result['type']!='Expense')

{

if($result['type']=='Receipt')

{

echo"".$result['details']." by ".$gn1['name']."";

}

elseif($result['type']=='Payment')

{

echo"".$result['details']." to ".$gn1['name']."";

}



}

else

{

echo"".$result['details']."";

if($result['type']=='Opening Balance')
{
	echo"<a href='del.php?id=".$result['ID']."&desc=bankledger&ac=".$ac."'><i class='icon-adt_trash'></i></a>";
}

}



echo"</td><td>".$result['type']."</td><td>".$result['ID']."</td><td style='text-align:right;'>".$debit."</td><td style='text-align:right;'>".$credit."</td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td></tr>";



}





echo"<tr><td colspan='4' style='text-align:right;'>Total</td><td style='text-align:right;'>".number_format($db,2,'.','')."</td><td style='text-align:right;'>".number_format($cr,2,'.','')."</td><td></td></tr>";



if($db>$cr)

{

$cb=$db-$cr;



echo"<tr><td colspan='4' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td style='text-align:right;'></td><td></td></tr>";

}

elseif($cr>$db)

{

$cb=$cr-$db;



echo"<tr><td colspan='4' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'></td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td></td></tr>";

}

else

{

echo"<tr><td colspan='4' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>NIL</td><td style='text-align:right;'>NIL</td><td></td></tr>";

}



echo"</table><br /><br />";



echo"<center><a href='bank_ledger.php'>Back</a></center>";



}



}



else

{



if (isset($_GET['s'])) 

	{

	 $status=$_GET['s'];

		 if($status=='1')

		 {

		 echo '<div class="alert alert-success">

				<a class="close" data-dismiss="alert">x</a>

					<strong>Values inserted successfully.</strong>

			  </div>';

		}

		elseif($status=='2')

		 {

		 echo '<div class="alert alert-error">

				<a class="close" data-dismiss="alert">x</a>

					<strong>Values Not inserted. Please try again.</strong>

			  </div>';

		}

		elseif($status=='3')

		 {

		 echo '<div class="alert alert-success">

				<a class="close" data-dismiss="alert">x</a>

					<strong>Values edited successfully.</strong>

			  </div>';

		}

		elseif($status=='4')

		 {

		 echo '<div class="alert alert-error">

				<a class="close" data-dismiss="alert">x</a>

					<strong>Values Not Edited. Please try again.</strong>

			  </div>';

		}

	}

	

echo"

<p><a href='bank_obl.php' style='padding-bottom:11px;'>Opening Balance</a></p>

<table id='dynamic-table' class='table table-striped table-bordered table-hover'>

<thead><th style='width:26%;'>Bank</th><th width='26%'>Account Name</th><th width='14%'>Credit</th><th width='14%'>Debit</th><th width='14%'>Closing Balance</th><th></th></thead>";



$get="select * from bank order by ID ASC";

$get1=mysqli_query($conn,$get) or die("Could Not Get Supplier Details");

	while($getsite5=mysqli_fetch_assoc($get1))

		{

		$ex=mysqli_query($conn,"select sum(amount) as amount from expense where account='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$ex1=mysqli_fetch_assoc($ex);
		

		$sl=mysqli_query($conn,"select sum(amount) as amount from receipt where account='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$sl1=mysqli_fetch_assoc($sl);

		

		$re=mysqli_query($conn,"select sum(amount) as amount from payment where account='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$re1=mysqli_fetch_assoc($re);

		

		$obl=mysqli_query($conn,"select * from bank_ledger where accountname='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$num=mysqli_num_rows($obl);

		$obl1=mysqli_fetch_assoc($obl);

		

		$credit=0;

		$debit=0;

		if($obl1['paymenttype']=='credit')

		{

		$credit=$obl1['amount'];

		$debit=0;

		}

		elseif($obl1['paymenttype']=='debit')

		{

		$credit=0;

		$debit=$obl1['amount'];

		}

		
		

		$jr=mysqli_query($conn,"select sum(amount) as amount from journal where credit='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$jr1=mysqli_fetch_assoc($jr);

		

		$jur=mysqli_query($conn,"select sum(amount) as amount from journal where debit='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$jur1=mysqli_fetch_assoc($jur);
		
		

		//echo"$credit & $debit";

		$deb=$sl1['amount']+$debit+$jur1['amount'];

		$cre=$re1['amount']+$ex1['amount']+$credit+$jr1['amount'];

		

		$cbl=$sl1['amount']+$debit-$re1['amount']-$ex1['amount']-$credit+$jur1['amount']-$jr1['amount'];

				

		echo "<tr><td>$getsite5[bank]</td><td><a href='bank_ledger.php?ac=".$getsite5['ID']."'>$getsite5[accountname]</a></td><td style='text-align:right;'>".number_format($deb,2,'.','')."</td><td style='text-align:right;'>".number_format($cre,2,'.','')."</td><td style='text-align:right;'>".number_format($cbl,2,'.','')."</td><td>";

		

		if($num>0)

		{

		echo"<a href='editbank_obl.php?ledger=".$getsite5['ID']."'><i class='ace-icon fa fa-pencil bigger-100 green'></i></a>";

		}

		

		echo"</td></tr>";

			 

		}

echo '</table>';



}



?>  
                               
							<br>
<br>
	

								<!-- PAGE CONTENT ENDS -->
							</div><!-- /.col -->
						</div><!-- /.row -->
					

</div><!-- /.page-content -->
				</div>
			</div><!-- /.main-content -->
			</div><!-- /.main-content -->
			</div><!-- /.main-content -->


<?php
include 'bottom.php';
}
else
{

 header("location:login.php");
}
ob_flush();


?>