<?php
//require_once 'pdf.php';
 include('db_con.php');
 if (isset($_SESSION['user_id'])) {
        $user_type = $_SESSION['user_type'];
        $user_id = $_SESSION['user_id'];
        $user_name = $_SESSION['user_name'];
        $branch_id = $_SESSION['branch_id'];
        $branch_name = $_SESSION['branch_name'];
        $store_id = $_SESSION['store_id'];
        $store_name = $_SESSION['store_name'];
        $user_acc_no = $_SESSION['user_acc_no'];
}else{
    header('Location: login');
    die();
}
 $html = '';
 $fdate = $_GET['fdate'];
 $acc_no = $_GET['acc_no'];
 $tdate = $_GET['tdate'];
 $opening = 0;
 $count = 0;
 $balance = 0;
 $tot_debit = 0;
 $tot_credit = 0;
 
 $dated = date('Y-m-d');     
  $sql = "SELECT IFNULL(sum(d.debit-d.credit),0)as opening FROM jv m,jv_detail d
  WHERE m.jv_id=d.jv_id and m.dated < '".$fdate."' and d.acc_no ='".$acc_no."' and m.status ='Active' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that ou
           $opening_balance = $statement->fetchColumn();
          
           $opening = $opening + $opening_balance;
            
           //$closing = $closing + $opening;
           $sql = "SELECT acc_name FROM accounts WHERE acc_no ='".$acc_no."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
           $acc_name = $statement->fetchColumn();
            $statement = $connect->prepare("select m.dated,m.source,m.ref_id,m.description dsc,
          CASE WHEN m.source ='AR Inv' THEN (select CONCAT(c.name,' ',' - Sale') from sale s,customers c where s.s_id = m.ref_id and s.cust_id=c.cust_id)
           WHEN m.source ='AR InvR' THEN (select CONCAT(c.name,' ',' - Sale Return') from customers c,sr s where s.sr_id = m.ref_id and s.cust_id=c.cust_id)
      WHEN m.source ='AR Pay' THEN (select CONCAT(a.acc_name,' ', ' - Received') from accounts a,rec_voucher r where r.rec_id = m.ref_id and r.cust_id=a.acc_no)
       WHEN m.source ='AP Pay' THEN (select CONCAT(a.acc_name,' ',' - Pyament') from accounts a,pay_voucher r where r.pay_id = m.ref_id and r.v_id=a.acc_no) END AS description,d.narr,
    d.debit,d.credit from jv m, jv_detail d where m.jv_id=d.jv_id 
    and m.dated between :fdate and :tdate and d.acc_no  = :acc_no and m.status = 'Active' 
    order by dated, ref_id");
 $statement->execute(
  array(
   ':acc_no'       =>  $acc_no,
   ':fdate'       =>  $fdate,
   ':tdate'       =>  $tdate
  )
 );
 $result = $statement->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
  <title>
  </title>
  <style>
* {
  font-size: 11px;
  font-family: arial;
}
  .rightside {text-align: right;}
  table td{   
    border-bottom: 1px solid gray;
  }
  .head_row {
    font-weight: bold;
  }
  thead td {
    font-weight: bold;
     border-bottom: 1px solid gray;
      border-top: 1px solid gray;
       background-color: :  gray;
  }
  .total {
    font-weight: bold;
  }
  </style>
</head>
<body>
  <table width="100%">
<tr>
  <td colspan="4" width="25%"><b>General Ledger</b> as on : <?php echo $dated;?></td>
  </tr>
  <tr>
  <td width="25%">Acc No :</td><td width="35%"><?php echo $acc_no;?></td><td width="15%" class="rightside">From :</td><td width="25%" class="rightside"><?php echo $fdate;?></td>
  </tr>
  <tr>
  <td width="25%">Name : </td><td width="35%"><?php echo $acc_name;?></td><td width="15%" class="rightside">To :</td><td width="25%" class="rightside"><?php echo $tdate;?></td>
  </tr>
  <tr>
  <td  colspan="3" width="80%" class="rightside">Opening Balance : </td><td width="20%" class="rightside"><?php echo $opening;?></td>
  </tr>
  </table>
  <table width="100%" id="table_header">
<thead>
  <tr>
  <td width="3%">#</td>
  <td width="8%">Date</td>
  <td width="12%" class="dataline">Source</td>
  
  <td colspan="2" width="44%">Description</td>
  
  <td width="10%" class="rightside">Debit</td>
  <td width="10%" class="rightside">Credit</td>
  <td width="12%" class="rightside">Balance</td>
  </tr>
</thead>
  
<?php
foreach($result as $row)
 {
  $debit = $row["debit"];
  $credit = $row["credit"];
  $tot_debit = $tot_debit + $debit;
  $tot_credit = $tot_credit + $credit;
  $count = $count+1;
  if($count == 1){$balance = $opening + $debit - $credit; }else{$balance = $balance + $debit - $credit;}
echo '
<tr>
<td width="3%" class="dataline">'.$count.'</td>
  
<td width="8%" class="dataline">'.$row["dated"].'</td>
<td width="12%" class="dataline">'.$row["source"].' - '.$row["ref_id"].'</td>
<td width="20%" class="dataline">'.$row["dsc"].'</td>
<td width="24%" class="dataline">'.$row["description"].' -- '.$row['narr'].'</td>
<td width="10%" class="rightside dataline">'.number_format($debit, 2).'</td>
   <td width="10%" class="rightside dataline">'.number_format($credit, 2).'</td>
   <td width="12%" class="rightside dataline">'.number_format($balance, 2).'</td>
  
   
    
   </tr>
';
}
?>
<tfoot>

</tfoot>
</table>
<table width="100%">
<?php echo '

  <tr>
  <td colspan="5" width="68%" class="rightside total">Total: </td>
  <td width="10%" class="rightside total">'.number_format($tot_debit, 2).'</td>
  <td width="10%" class="rightside total">'.number_format($tot_credit, 2).'</td>
  <td width="12%" class="rightside total">'.number_format($balance, 2).'</td>
  </tr>
  
  ';?>
  </table>
</body>
</html>
