<?php 
include("../config.php");
$sd = date("Y-m-d", strtotime($_POST['sd']));
$ed = date("Y-m-d", strtotime($_POST['ed']));
$act = "SELECT * FROM `act_master` WHERE act_code = '".$_POST['employee_id']."'";
$act_res = mysqli_query($conn,$act);
$act_row = mysqli_fetch_array($act_res);


$report     = "SELECT * FROM report_setting WHERE company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
$report_res = mysqli_query($conn,$report);
$report_row = mysqli_fetch_assoc($report_res);

$master_accounts       = combine(getAllRecords('act_master'), 'act_code', 'ac_name');

$gl = "SELECT * FROM `general_ledger` WHERE voucher_date BETWEEN '".$sd."' AND '".$ed."' AND (credit_account = '".$_POST['employee_id']."' OR debit_account = '".$_POST['employee_id']."') AND amount > 0 AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
$gl_res = mysqli_query($conn,$gl);
/*$year  = explode('/', $_POST['sd']);
$start_date = $year[2] . '-04-01';*/
$year           = explode('-', $_POST['sd']);
$start_date     = '01-04-' . $year[2];

?>
<section>
  <div class="card">
    <div class="card-body">
      <?php echo $report_row['header_data']; ?>
      <table id="example1" class="table table-bordered table-striped list_data">
        <thead>
          <tr>
            <td colspan="7" style="text-align: center;"><span style="font-size: 13px;"><b>Account Ledger FROM <?php echo risFormatDate($sd);?> TO <?php echo risFormatDate($ed);?></span><br/><span style="font-size: 15px;"><b>Account :  <?php echo $act_row['ac_name']; ?></b></span></td>
          </tr>
          
          <tr>
            <th width="90">VOUCHER NO.</th>
            <th width="40">DATE</th>
            <th width="90">FLAG</th>
            <th>ACCOUNT</th>
            <th width="90" class="text-right">DEBIT</th>
            <th width="90" class="text-right">CREDIT</th>
            <th width="90" class="text-right">BALANCE</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td colspan="4" class="text-left"><b><span style="position: fixed;left: 163px;">OPENING BALANCE</span></b></td>
            <?php
              $dr_amount = "SELECT sum(amount) as dr_amnt FROM `general_ledger` WHERE debit_account = '".$_POST['employee_id']."' AND voucher_date >= '".$start_date."' AND voucher_date < '".$sd."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
              $dr_amount_res = mysqli_query($conn,$dr_amount);
              $dr_amount_row = mysqli_fetch_assoc($dr_amount_res);
              $dr_amounts    = $dr_amount_row['dr_amnt'];
              $dr_open_bal   = $act_row['dr_open_bal'];
              $total_debit   = ($dr_open_bal + $dr_amounts);
             
              $cr_amount = "SELECT sum(amount) as cr_amnt FROM `general_ledger` WHERE credit_account = '".$_POST['employee_id']."' AND voucher_date >= '".$start_date."' AND voucher_date < '".$sd."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
              $cr_amount_res = mysqli_query($conn,$cr_amount);
              $cr_amount_row = mysqli_fetch_assoc($cr_amount_res);
              $cr_amounts    = $cr_amount_row['cr_amnt'];
              $cr_open_bal   = $act_row['cr_open_bal'];
              $total_credit  = ($cr_amounts + $cr_open_bal);
              
              

              $bal           = $total_debit - $total_credit;

              if($bal >= 0){
                $opening_debit = ABS($bal);
                $flags = 'DR';
                $color = 'color:green';    
              }else if($bal < 0){
                $opening_credit = ABS($bal);
                $flags = 'CR';
                $color = 'color:red';
              }else{
                $flags = '';
                $color = '';
              }
              $bal = $opening_debit - $opening_credit;
              
            ?>
            <td class="text-right"><?php echo customFormatMoney($opening_debit);?></td>
            <td class="text-right"><?php echo customFormatMoney($opening_credit)?></td>
            <td style="<?php echo $color;?>" class="text-right"><?php 
            
            echo customFormatMoney(abs($bal));
            ?><?php echo $flags;?></td>
          </tr>
          <?php
            $total_dr = 0;
            $total_cr = 0;
            while ($rows = mysqli_fetch_array($gl_res)) {
              
              $cr   = 0;
              $dr   = 0;

              if($rows['debit_account'] == $_POST['employee_id']){
                $dr        = $rows['amount'];
              }

              if($rows['credit_account'] == $_POST['employee_id']){
                $cr         = $rows['amount'];
              }

              if($rows['debit_account'] != $_POST['employee_id']){
                $a = @$master_accounts[$rows['debit_account']];
              }

              if($rows['credit_account'] != $_POST['employee_id']){
                $a = @$master_accounts[$rows['credit_account']];
              }

              $bal = ($bal + $dr) - $cr;
              
              if($bal < 0){
                $flag      = 'CR';
                $highlight = "color:red";
              }else{
                $flag      = 'DR';
                $highlight  = "color:green";

              }
              $total_cr += $cr;
              $total_dr += $dr;
              ?>

                <tr>
                  <td><?php echo $rows['voucher_no']; ?></td>
                  <td><?php echo risFormatDate($rows['voucher_date'], 'd.m.Y'); ?></td>
                  <td><?php echo $rows['flag']; ?></td>
                  <td><?php echo $a; ?></td>
                  <td class="text-right"><?php echo customFormatMoney($dr); ?></td>
                  <td class="text-right"><?php echo customFormatMoney($cr); ?></td>
                  <td class="text-right" style="<?php echo $highlight;?>"><?php echo customFormatMoney(abs($bal)); ?> <span><?php echo $flag;?></span></td>
                </tr>

              <?php
            }
          ?>
          <tr>
            <td colspan="4" class="text-right"></td>
            <td class="text-right"><?php echo customFormatMoney($opening_debit + $total_dr); ?></td>
            <td class="text-right"><?php echo customFormatMoney($opening_credit + $total_cr); ?></td>
            <td></td>
          </tr>
        </tbody>
      </table>
      
    </div>
  </div>
</section>