<?php require_once('../auth/auth.php');?>
<?php
   $bbid       = $_GET['bbid'];
   $bbrid      = $_GET['bbrid'];
   $gcatid     = $_GET['gcatid'];
   $fdate      = $_GET['fdate'];
   $edate      = $_GET['edate'];

   $co_rs = mysqli_query($conn,"SELECT co_name,owner_name,co_address,co_phone,co_email FROM company WHERE coid='$coid'") or die (mysqli_error($conn));
   $cor= mysqli_fetch_assoc($co_rs);

    if ($bbid=='0') {
       $bbidcond = '';
    }elseif ($bbid >0){
        $bbidcond= " AND t.bid=$bbid";
    }

    if ($bbrid=='0') {
       $bbridcond = '';
       $branch_name = 'All Branch';       
    }elseif ($bbrid >0){
        $bbridcond= " AND t.brid=$bbrid";
        $branch_name = mysqli_fetch_assoc(mysqli_query($conn,"SELECT branch_name FROM branch WHERE coid='$coid' AND brid='$bbrid'"))['branch_name'];
    }


if ($gcatid=='0') {
    
$subbal = mysqli_query($conn,"SELECT a.acid,a.ac_name,g.ac_g_cat_name,
SUM(COALESCE(CASE WHEN t.dr_acid = a.acid THEN amount END,0)) debit,
SUM(COALESCE(CASE WHEN t.cr_acid = a.acid THEN amount END,0)) credit FROM transaction t LEFT JOIN accounts a ON (t.dr_acid=a.acid || t.cr_acid=a.acid) LEFT JOIN ac_sub_g_category g ON(a.gcatid=g.ac_g_catid) WHERE t.coid='$coid' $bbidcond $bbridcond AND t.active=1 AND date(t.date) BETWEEN '$fdate' AND '$edate' GROUP BY a.acid HAVING (debit > 0 || credit > 0) ORDER BY g.ac_g_catid ASC") or die(mysqli_error($conn));

// $subbal = mysqli_query($conn,"SELECT a.acid,a.ac_name,g.ac_g_cat_name,
// SUM(COALESCE(CASE WHEN t.dr_gcatid = g.ac_g_catid THEN amount END,0)) debit,
// SUM(COALESCE(CASE WHEN t.cr_gcatid = g.ac_g_catid THEN amount END,0)) credit FROM transaction t LEFT JOIN ac_sub_g_category g ON (t.dr_gcatid=g.ac_g_catid ||t.cr_gcatid=g.ac_g_catid) LEFT JOIN accounts a ON(a.acid=(IF(t.dr_gcatid = g.ac_g_catid, t.dr_acid, t.cr_acid))) WHERE t.coid='$coid' $bbidcond $bbridcond AND t.active=1 AND date(t.date) BETWEEN '$fdate' AND '$edate' GROUP BY a.acid HAVING (debit > 0 || credit > 0) ORDER BY g.ac_g_catid ASC") or die(mysqli_error($conn));

} else {
    
$subbal = mysqli_query($conn,"SELECT a.acid,a.ac_name,g.ac_g_cat_name,
SUM(COALESCE(CASE WHEN t.dr_acid = a.acid THEN amount END,0)) debit,
SUM(COALESCE(CASE WHEN t.cr_acid = a.acid THEN amount END,0)) credit FROM transaction t LEFT JOIN accounts a ON (t.dr_acid=a.acid || t.cr_acid=a.acid) LEFT JOIN ac_sub_g_category g ON(a.gcatid=g.ac_g_catid) WHERE t.coid='$coid' $bbidcond $bbridcond AND t.active=1 AND a.gcatid = '$gcatid' AND date(t.date) BETWEEN '$fdate' AND '$edate' GROUP BY a.acid HAVING (debit > 0 || credit > 0) ORDER BY g.ac_g_catid ASC") or die(mysqli_error($conn));

// $subbal = mysqli_query($conn,"SELECT a.acid,a.ac_name,g.ac_g_cat_name,
// SUM(COALESCE(CASE WHEN t.dr_gcatid = g.ac_g_catid THEN amount END,0)) debit,
// SUM(COALESCE(CASE WHEN t.cr_gcatid = g.ac_g_catid THEN amount END,0)) credit FROM transaction t LEFT JOIN ac_sub_g_category g ON (t.dr_gcatid=g.ac_g_catid || t.cr_gcatid=g.ac_g_catid) LEFT JOIN accounts a ON(a.acid=(IF(t.dr_gcatid = g.ac_g_catid, t.dr_acid, t.cr_acid))) WHERE t.coid='$coid' $bbidcond $bbridcond AND t.active=1 AND a.gcatid = '$gcatid' AND date(t.date) BETWEEN '$fdate' AND '$edate' GROUP BY a.acid HAVING (debit > 0 || credit > 0) ORDER BY g.ac_g_catid ASC") or die(mysqli_error($conn));

}
      $sbdata = [];
    while($sbbl= mysqli_fetch_assoc($subbal)){
      $sbdata[]=$sbbl;
    }
?>

 <table class="ttable border-bottom-0 d-none" id="header" cellpadding="0" cellspacing="0">
    <thead>
       <tr>
          <th rowspan="3" class="thborder border-bottom-0 text-center" style="width:10%;">
             <img class="profile-user-img img-fluid img-circle" src="../../dist/img/a.png" style="width:80px;height:77px;">
          </th>
          <th rowspan="3" class="thborder border-bottom-0" style="width:70%;">
                <dt style="font-size:18px;font-weight: 600;"><?php echo $cor['co_name'];?></dt>
                <dt><?php echo $cor['owner_name'];?></dt>
                <dt><?php echo $cor['co_address'];?></dt>
                <dt><?php echo $cor['co_phone'];?>#<?php echo $cor['co_email'];?></dt>
          </th>
          <th class="thborder text-center" style="width:20%;"><?php echo $branch_name;?></th>
       </tr>
       <tr>
          <th class="thborder text-center"><?php echo language(183);?></th>
       </tr>
       <tr>
          <th class="thborder text-center border-bottom-0"><?php echo date('d/m/y', strtotime($fdate));?> To <?php echo date('d/m/y', strtotime($edate));?>
          </th>
       </tr>
    </thead>
 </table>

<table id="gledger_table" class="ttable" cellpadding="0" cellspacing="0">
  <input type="hidden" id="vounameset" value="Group Ledger Report <?php echo date('d/M/y', strtotime($fdate));?> To <?php echo date('d/M/y', strtotime($edate));?> ">

  <thead>
    <tr>
       <th class="text-center" style="width:5%;">S/l</th>
       <th class="text-center" style="width:45%;">Account Name</th>
       <th class="text-center" style="width:20%;">Group Name</th>
       <th class="text-center" style="width:10%;">Debit</th>
       <th class="text-center" style="width:10%;">Credit</th>
       <th class="text-center" style="width:10%;">balance</th>
    </tr>
  </thead>
  <tbody></tbody>
   <tfoot>                                
      <tr>
         <th class="text-right" colspan="3">Sub Total</th>
         <th class="text-right" id="ssumdrb"></th>
         <th class="text-right" id="ssumcrb"></th>
         <th class="text-right" id="ssbalnc"></th>
      </tr>
   </tfoot>
</table>

<script id="script">
  $(document).ready(function () {
      var sbdata = '';
      var x = 1;
      var debit = 0;
      var credit = 0;
      var balance = 0;
      var ssumdrb = 0;
      var ssumcrb = 0;
      $.each(<?php echo json_encode($sbdata)?>, function (key, svalue) {

      ssumdrb += Number(svalue.debit);
      ssumcrb += Number(svalue.credit);

       debitt  = (Number(svalue.debit) <= 0)?'':bden.format(Number(svalue.debit));
       creditt = (Number(svalue.credit) <= 0)?'':bden.format(Number(svalue.credit));        

        sbdata += '<tr>';
        sbdata += '<td class="text-center">' + x++ + '</td>';

        sbdata += '<td class="btn-outline-dark" onclick="vprofile('+svalue.acid+')">' + svalue.ac_name +'</td>';

        sbdata += '<td class="text-center">' + svalue.ac_g_cat_name +'</td>';

        sbdata += '<td class="text-right">' + debitt + '</td>';

        sbdata += '<td class="text-right">' + creditt + '</td>';

        sbdata += '<td class="text-right">' + bden.format(Number(svalue.debit-svalue.credit)) + '</td>';

        sbdata += '</tr>';
      });
      
      $('#gledger_table tbody').append(sbdata);
      $('#gledger_table #ssumdrb').text(bden.format(ssumdrb));
      $('#gledger_table #ssumcrb').text(bden.format(ssumcrb));
      $('#gledger_table #ssbalnc').text(bden.format(ssumdrb-ssumcrb));
      $('#script').remove();
        toastr.success(Showing, yourdata);
  });
</script>
<script>
  function vprofile(acid){    
    $("#modal-xl").modal({backdrop: "static"});
    pagecall('../accounts/ac_profile.php?acid='+acid,'#xlmdview');   
   }    
</script>


