<?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'); 
 $title_id = $_GET['title_id'];
 
$statement = $connect->prepare("select * from titles where  id= :title_id
    
  ");
 $statement->execute(
  array(  
   ':title_id'       =>  $title_id
  )
 );
 $result = $statement->fetchAll();
 
  foreach($result as $row)
 {
  $heading1 = $row['heading1'];
  $heading2 = $row['heading2'];
  $heading3 = $row['heading3'];
 }   
  $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, CASE WHEN m.source='AR Inv' THEN (select CONCAT('Sale' , ' - ',s.description, ' Inv #: ',s.inv_no, ' PO #: ', s.po_no ) from sale s where s.s_id=m.ref_id) WHEN m.source='AR Pay' THEN (select CONCAT(p.description) from rec_voucher p where p.rec_id=m.ref_id) WHEN m.source='AP Pay' THEN (select CONCAT(p.description) from pay_voucher p where p.pay_id=m.ref_id) WHEN m.source='AR InvR'  THEN (select CONCAT('Sale Return' , ' - ',s.description) from sr s where s.sr_id=m.ref_id) 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>Account Ledger Preview</title>
  <style>
* {
  
  font-family: arial;
  padding:3px;
}
@media print {
  @page { margin: 0.5cm; }
  body { margin: 0.1cm;}
}
table.header_table {
  font-size: 14px;
}
table.header_table td {
  font-size: 14px;
  border-bottom: 1px solid gray;
}
table.data_table {
  font-size: 12px;
}

  .rightside {text-align: right;}
  table.data_table td{   
    border-bottom: 1px solid gray;
  }
  .head_row {
    font-weight: bold;
  }
  table.data_table thead td {
    font-weight: bold;
     border-bottom: 1px solid gray;
      border-top: 1px solid gray;
       background-color:#CDCDCD;
       font-size: 12px;
  }
  .total {
    font-weight: bold;
  }
  #heading {
    line-height: 0.1cm;
    font-size: 13px;
  }
  #heading p {
    line-height: 0.1cm;
    font-size: 11px;
  }

  h2 {
    font-size: 18px;
    font-family: calibri;
  }
  </style>
</head>
<body>
  <div id="heading" align="center">
    <br />
  <h2><?php echo $heading1;?></h2>
  <p><?php echo $heading2;?></p>
  <p><?php echo $heading3;?></p>
  </div>
  <table width="100%" class="header_table">
<tr>
  <td colspan="4" width="25%"><b>Account 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%" class="total">Name : </td><td width="35%" class="total"><?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%" class="data_table">
<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>
  <tbody>
<?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, 0).'</td>
   <td width="12%" class="rightside dataline">'.number_format($credit, 0).'</td>
   <td width="14%" class="rightside dataline">'.number_format($balance, 0).'</td>
  
   
    
   </tr>
';
}
echo '
  <tr>
  <td colspan="4" width="54%" class="rightside total">Total: </td>
  <td width="12%" class="rightside total">'.number_format($tot_debit, 0).'</td>
  <td width="12%" class="rightside total">'.number_format($tot_credit, 0).'</td>
  <td width="16%" class="rightside total">'.number_format($balance, 0).'</td>
  
  </tr>
  
';
?>
</tbody>
</table>
</body>
</html>
