<?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'];
}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('d-m-Y');    
  $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,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");
 $statement->execute(
  array(
   ':acc_no'       =>  $acc_no,
   ':fdate'       =>  $fdate,
   ':tdate'       =>  $tdate
  )
 );
 $result = $statement->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
  <title><?php echo basename(__FILE__, '.php'); ?></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;
  }
  </style>
</head>
<body>
  <table width="100%">
<tr>
  <td colspan="4" width="25%"><b>Vendor 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%"><b><?php echo $acc_name;?></b></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="15%" class="dataline">Source</td>
  
  <td width="36%">Description</td>
  
  <td width="12%" class="rightside">Debit</td>
  <td width="12%" class="rightside">Credit</td>
  <td width="14%" 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="15%" class="dataline">'.$row["source"].' - '.$row["ref_id"].'</td>
<td width="36%" class="dataline">'.$row["description"].' --- '.$row["narr"].'</td>
<td width="12%" class="rightside dataline">'.number_format($debit, 2).'</td>
   <td width="12%" class="rightside dataline">'.number_format($credit, 2).'</td>
   <td width="14%" class="rightside dataline">'.number_format($balance, 2).'</td>
  
   
    
   </tr>
';
}
?>
<tfoot>
<?php echo '
  <tr>
  <td colspan="4" width="54%" class="rightside">Total: </td>
  <td width="12%" class="rightside total">'.number_format($tot_debit, 2).'</td>
  <td width="12%" class="rightside total">'.number_format($tot_credit, 2).'</td>
  <td width="16%" class="rightside total">'.number_format($balance, 2).'</td>
  </tr>
';
?>
</tfoot>
</table>
</body>
</html>
