<?php session_start();
include ('../inc/dbcon.php');
include ('../inc/functions.php');
include ('../inc/cartfunctions.php');
include ('../inc/repfunctions.php');
if(isset($_SESSION["axeslogin"]) && $_SESSION["axeslogin"] == true){    
$aid=$_SESSION['uid'];
$brid=$_SESSION['abrid'];    
$today = strftime("%Y-%m-%d", time());    
$dtnow = date("Y-m-d h:i:s", time());    
}else{
header('Location:../index.php');
exit;    
}
?>
<?php
if(isset($_POST['branch']) && $_POST['branch']!=''){
$ibrid=$_POST['branch'];    
}else{
$ibrid=$brid;    
}

if(isset($_POST['date_from'])){
$fdate=$_POST['date_from'];    		
}else{
$fdate= date('Y').'-'.date('m').'-01';	
}
if(isset($_POST['date_to'])){
$tdate=$_POST['date_to'];	
}else{
$tdate=$today;	
}

function get_monthly_income($mon,$ibrid){
global $con;
$income=0;$expenses=0;

if($mon==0){    
$fdate = date("Y", strtotime("-0 months", time())).'-'.date("m", strtotime("-0 months", time())).'-'.'01'; 
$tdate = date("Y", strtotime("-0 months", time())).'-'.date("m", strtotime("-0 months", time())).'-'.cal_days_in_month(CAL_GREGORIAN,date("m", strtotime("-0 months", time())),date('Y'));
}elseif($mon==1){
$fdate = date("Y", strtotime("-1 months", time())).'-'.date("m", strtotime("-1 months", time())).'-'.'01'; 
$tdate = date("Y", strtotime("-1 months", time())).'-'.date("m", strtotime("-0 months", time())).'-'.cal_days_in_month(CAL_GREGORIAN,date("m", strtotime("-1 months", time())),date('Y'));    
}else{
$fdate = date("Y", strtotime("-2 months", time())).'-'.date("m", strtotime("-2 months", time())).'-'.'01'; 
$tdate = date("Y", strtotime("-2 months", time())).'-'.date("m", strtotime("-2 months", time())).'-'.cal_days_in_month(CAL_GREGORIAN,date("m", strtotime("-2 months", time())),date('Y'));   
}
    
if($ibrid!='A'){
$sql="SELECT COALESCE((SELECT SUM(debit) AS rev FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('SEL','SER') AND debit>0 AND apdate BETWEEN '$fdate' AND '$tdate' AND brid='$ibrid'),0) AS rev"; 
}else{
$sql="SELECT COALESCE((SELECT SUM(debit) FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('SEL','SER') AND debit>0 AND apdate BETWEEN '$fdate' AND '$tdate'),0) AS rev";
}    
$rwsp=mysqli_query($con,$sql)or die(mysqli_error($con));
$row=mysqli_fetch_array($rwsp);
$income=$row['rev'];

if($ibrid!='A'){
$sql="SELECT COALESCE((SELECT SUM(credit) FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('EXP') AND credit>0 AND apdate BETWEEN '$fdate' AND '$tdate'  AND brid='$ibrid'),0) AS exp";
}else{
$sql="SELECT COALESCE((SELECT SUM(credit) AS exp FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('EXP') AND credit>0 AND apdate BETWEEN '$fdate' AND '$tdate'),0) AS exp";    
}    
$rwspan=mysqli_query($con,$sql)or die(mysqli_error($con));
$rows=mysqli_fetch_array($rwspan);
$expenses=$rows['exp'];    

return ($income-$expenses);    
}

if($ibrid!='A'){
$sql="SELECT SUM(debit) AS rev FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('SEL','SER') AND debit>0 AND apdate BETWEEN '$fdate' AND '$tdate' AND brid='$ibrid'";
}else{
$sql="SELECT SUM(debit) AS rev FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('SEL','SER') AND debit>0 AND apdate BETWEEN '$fdate' AND '$tdate'";    
}
$rwspan=mysqli_query($con,$sql)or die(mysqli_error($con));
$rows=mysqli_fetch_array($rwspan);
$income=$rows['rev'];

if($ibrid!='A'){
$sql="SELECT SUM(credit) AS exp FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('EXP') AND credit>0 AND apdate BETWEEN '$fdate' AND '$tdate'  AND brid='$ibrid'";
}else{
$sql="SELECT SUM(credit) AS exp FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('EXP') AND credit>0 AND apdate BETWEEN '$fdate' AND '$tdate'";    
}    
$rwspan=mysqli_query($con,$sql)or die(mysqli_error($con));
$rows=mysqli_fetch_array($rwspan);
$expenses=$rows['exp'];

$pflo=($income-$expenses);

$output='';
$output.='<div class="col-md-4">';
$output.='<h4 class="text-uppercase text-center">Profit & Loss</h4>';
$output.='<div class="row">';
$output.='<div class="col-md-6">';
$output.='<div class="callout " style="border-left-color: #00a65a;color: #666;margin-top: 39px;padding-right: 0px;">';
$output.='<h4 style="font-size: 16px;" id="overview_net_income">'.numtolocal(ABS($pflo),'BDT').'</h4>';
if($pflo>=0){
$output.='<p id="overview_net_income_text">NET PROFIT</p>';    
}else{
$output.='<p id="overview_net_income_text">NET LOSE</p>';    
}
$output.='</div>';
$output.='</div>';
$output.='<div class="col-md-6">';
$output.='<div class="callout " style="border-left-color: #a8cf46;color: #999;padding-right: 0px;">';
$output.='<h4 style="font-size: 16px;" id="overview_income">'.numtolocal($income,'BDT').'</h4>';
$output.='<p>INCOME</p>';
$output.='</div>';
$output.='<div class="callout  " style="border-left-color: #ff8c00;color: #999;padding-right: 0px;">';
$output.='<h4 style="font-size: 16px;" id="overview_expense">'.numtolocal($expenses,'BDT').'</h4>';
$output.='<p>EXPENSE</p>';
$output.='</div>';
$output.='</div>';
$output.='</div>';
$output.='</div>';
$output.='<div class="col-md-2 nopadding">';
$output.='<h4 class="text-uppercase text-center">Revenue from Sales & Services</h4>';
$output.='<table class="table table-bordered table-striped" id="overview_services">';
$output.='<tbody>';
if($ibrid!='A'){
$sql="SELECT IF(invty='SEL','Product Sales','Service Sales') AS name,SUM(debit) AS rev FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('SEL','SER') AND debit>0 AND apdate BETWEEN '$fdate' AND '$tdate' AND brid='$ibrid' GROUP BY invty";
}else{
$sql="SELECT IF(invty='SEL','Product Sales','Service Sales') AS name,SUM(debit) AS rev FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,SUBSTRING(invno,1,3) AS invty,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE invty IN ('SEL','SER') AND debit>0 AND apdate BETWEEN '$fdate' AND '$tdate' GROUP BY invty";    
}
$rwspan=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rows=mysqli_fetch_array($rwspan)){
$output.='<tr>';
$output.='<td>'.strtoupper($rows['name']).'</td>';
$output.='<td class="text-right">'.numtolocal($rows['rev'],'BDT').'</td>';
$output.='</tr>';
}
$output.='</tbody>';
$output.='</table>';
$output.='</div>';
$output.='<div class="col-md-3">';
$output.='<h4 class="text-uppercase text-center">Net Income Graph - Last 3 months </h4>';
$output.='<div class="business_overview_graph_div"></div>';
$output.='</div>';
$output.='<div class="col-md-3">';
$output.='<h4 class="text-uppercase text-center">Cash & Bank Balance</h4>';
$output.='<div class="row">';
if($ibrid!='A'){
$sql="SELECT opt.lid,opt.ledger,IFNULL(dur.debit,0) AS debit,IFNULL(dur.credit,0) AS credit,IFNULL(dur.balance,0) AS balance FROM (SELECT lid,lty,ledger FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid GROUP BY lid) opt LEFT JOIN (SELECT lid,ledger,SUM(debit) AS debit,SUM(credit) AS credit,SUM(debit-credit) AS balance FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE brid='$ibrid' GROUP BY lid) dur ON dur.lid=opt.lid WHERE opt.lty!='LE' ORDER BY ledger ASC";    
}else{
$sql="SELECT opt.lid,opt.ledger,IFNULL(dur.debit,0) AS debit,IFNULL(dur.credit,0) AS credit,IFNULL(dur.balance,0) AS balance FROM (SELECT lid,lty,ledger FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid GROUP BY lid) opt LEFT JOIN (SELECT lid,ledger,SUM(debit) AS debit,SUM(credit) AS credit,SUM(debit-credit) AS balance FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid GROUP BY lid) dur ON dur.lid=opt.lid WHERE opt.lty!='LE' ORDER BY ledger ASC";    
}
$rwspan=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rows=mysqli_fetch_array($rwspan)){
$output.='<div class="col-md-12 bank_accounts_div">';
$output.='<a href="" class="popup">';
$output.='<div class="info-box bg-light-blue cash_balance_box"> <span class="info-box-icon"><i class="fa fa-bank"></i></span>';
$output.='<div class="info-box-content"> <span class="info-box-number">BDT '.number_format($rows['balance'],2).'</span> <span class="progress-description" title="0192697603001">'.strtoupper($rows['ledger']).'</span> </div>';
$output.='</div>';
$output.='</a>';
$output.='</div>';
}
if($ibrid!='A'){
$sql="SELECT opt.lid,opt.ledger,IFNULL(dur.debit,0) AS debit,IFNULL(dur.credit,0) AS credit,IFNULL(dur.balance,0) AS balance FROM (SELECT lid,lty,ledger FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid GROUP BY lid) opt LEFT JOIN (SELECT lid,ledger,SUM(debit) AS debit,SUM(credit) AS credit,SUM(debit-credit) AS balance FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid GROUP BY lid) dur ON dur.lid=opt.lid WHERE opt.lty='LE' ORDER BY ledger ASC";    
}else{
$sql="SELECT opt.lid,opt.ledger,IFNULL(dur.debit,0) AS debit,IFNULL(dur.credit,0) AS credit,IFNULL(dur.balance,0) AS balance FROM (SELECT lid,lty,ledger FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid GROUP BY lid) opt LEFT JOIN (SELECT lid,ledger,SUM(debit) AS debit,SUM(credit) AS credit,SUM(debit-credit) AS balance FROM (SELECT apdate,date,dty AS lty,CONCAT(dty,did) AS lid,invno,refinv,otnote,ref,chkno,chkdt,cty AS type,CONCAT(cty,cid) AS opid,amo AS debit,0 AS credit,brid FROM tbl_traledger WHERE dty IN ('BA','MO') OR (dty='LE' AND did IN ('1','2')) UNION ALL SELECT apdate,date,cty AS lty,CONCAT(cty,cid) AS lid,invno,refinv,otnote,ref,chkno,chkdt,dty AS type,CONCAT(dty,did) AS opid,0 AS debit,amo AS credit,brid FROM tbl_traledger WHERE cty IN ('BA','MO') OR (cty='LE' AND cid IN ('1','2'))) cfs LEFT JOIN (SELECT id,ledger FROM tbl_ledger) led on led.id=cfs.lid LEFT JOIN (SELECT id,ledger AS name,sgroup FROM tbl_ledger) opn ON opn.id=cfs.opid WHERE brid='$ibrid' GROUP BY lid) dur ON dur.lid=opt.lid WHERE opt.lty='LE' ORDER BY ledger ASC";
}
$rwspan=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rows=mysqli_fetch_array($rwspan)){
$output.='<div class="col-md-12 cash_accounts_div">';
$output.='<a href="" class="popup">';
$output.='<div class="info-box bg-green cash_balance_box"> <span class="info-box-icon"><i class="fa fa-money"></i></span>';
$output.='<div class="info-box-content"> <span class="info-box-number">BDT '.number_format($rows['balance'],2).'</span> <span class="progress-description">'.strtoupper($rows['ledger']).'</span> </div>';
$output.='</div>';
$output.='</a>';
$output.='</div>';
}
$output.='</div>';
$output.='</div>';
$fout='{"html":'.json_encode($output).',"graph":{"months":["'.date("M Y", strtotime("-2 months", time())).'","'.date("M Y", strtotime("-1 months", time())).'","'.date("M Y", strtotime("-0 months", time())).'"],"values":['.get_monthly_income(2,$ibrid).','.get_monthly_income(1,$ibrid).','.get_monthly_income(0,$ibrid).']}}';
echo $fout;
?>