<?php



require_once 'pdf.php';

 

 include('db_con.php');

 $html = '';

 $fdate = $_GET['fdate'];

 $cust_id = $_GET['cust_id'];

 $tdate = $_GET['tdate'];

 $opening = 0;

 $debit = 0;

 $credit = 0;

 $cust_name = 'Demo Customer';

 $count = 0;

 $balance = 0;



 

 $sql = "SELECT acc_name FROM accounts WHERE acc_no='".$cust_id."'";

           $statement = $connect->prepare($sql);

           $statement->execute(); // no need to add `$sql` here, you can take that out

           $acc_name = $statement->fetchColumn();





 $sql = "SELECT sum(d.debit - d.credit) from jv_detail d, jv m  WHERE m.jv_id=d.jv_id and m.dated < '".$fdate."' and  d.acc_no='".$cust_id."'";

           $statement = $connect->prepare($sql);

           $statement->execute(); // no need to add `$sql` here, you can take that out

           $opening = $statement->fetchColumn();



           





  



         





  $statement = $connect->prepare("select date_format(m.dated,'%d-%m-%Y') as dated,CONCAT(m.source,' - ',m.ref_id) source,m.description,d.debit,d.credit from jv_detail d, jv m where m.jv_id=d.jv_id and d.acc_no = '".$cust_id."' and m.dated between :fdate and :tdate

  ");

 $statement->execute(

  array(

   

   ':fdate'       =>  $fdate,

   ':tdate'       =>  $tdate

  )

 );

 $result = $statement->fetchAll(); 



 $html .=' <style>

    @page { margin:150px 20px; }

    @page {margin-bottom: 40px;}

    #header { position: fixed; left: 0px; top: -150px; right: 0px; height: 150px; text-align: center; }

    #footer { position: fixed; left: 0px; bottom: -20px; right: 0px; height: 40px; background-color: #fff; }

    #content, table {margin:0;font-size:13px;font-weight:normal;}

    .rightside{text-align:right;}

    #footer .page:after { content: counter(page); }

    

    #table_header th {

    border-top:1px solid black;

    border-bottom:1px solid black;

  }

  .dataline {

    border-bottom:1px solid gray;

  }

  </style>



  <div id="header">



  <h3>Customer Ledger</h3>

  <table width="100%">

  <tr>

  <td width="15%">ID :</td><td width="20%">'.$cust_id.'</td><td width="55%" class="rightside">From :</td><td width="20%" class="rightside">'.$fdate.'</td>

  </tr>

  <tr>

  <td width="15%">Name : </td><td width="70%" style="font-size:12px">'.$acc_name.'</td><td width="20%" class="rightside">To :</td><td width="20%" class="rightside">'.$tdate.'</td>

  </tr>

  <tr>

  <td width="10%"></td><td width="20%"></td><td width="70%" class="rightside">Opening Balance :</td><td width="20%" class="rightside">'.number_format($opening, 2).'</td>

  </tr>

  </table>

  <table width="100%" id="table_header">



  <tr>

  <th width="4%">#</th>

  <th width="12%">Date</th>

  <th width="13%">Source</th>

  

  <th width="41%">Description</th>

  <th width="10%" class="rightside">Debit</th>

  <th width="10%" class="rightside">Credit</th>

  <th width="10%" class="rightside">Balance</th>







  </tr>



  </table>



  </div>

  

';







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;}

   

 

  

    

  

  



$html .= '

  <div id="content">

  

  <table width="100%">



<tr>

<td width="4%" class="dataline">'.$count.'</td>

  <td width="12%" class="dataline">'.$row["dated"].'</td>

  <td width="13%" class="dataline">'.$row["source"].'</td>



<td width="41%" class="dataline">'.$row["description"].'</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="10%" class="rightside dataline">'.number_format($balance, 2).'</td>

  

   

    

   </tr>

   

  </table>

  





  </div>



  <div id="footer">

  



    

  </div>





        ';



}



$html .='

<table width="100%">

  <tr>

  <td width="70%" class="rightside">Closing Balance : </td>

<td width="10%" class="rightside">'.number_format($tot_debit, 2).'</td>

<td width="10%" class="rightside">'.number_format($tot_credit, 2).'</td>

  <td width="10%" class="rightside">'.number_format($balance, 2).'</td>

  </tr>

  </table>';



$html .='

  </table>

  

  <br />

  <h3>Pending Cheque List </h3>

  <table width="100%">



    <tr>

  <th width="4%">#</th>

  <th width="6%">ID</th>

  

  <th width="68%">Description</th>

  <th width="12%">Chq No</th>

  <th width="10%">Chq Date</th>

  <th width="10%" class="rightside">Amount</th>

  





  ';









  $statement = $connect->prepare("select rec_id as id,description,chq_no,chq_date,amount

    from rec_voucher where pay_status = 'Not Cleared' and cust_id= :cust_id ");

 $statement->execute(

  array(

   ':cust_id'       =>  $cust_id

   

  )

 );

 $result = $statement->fetchAll(); 

 $amount= 0;

 $count = 0;

 $tot_amount = 0;



 foreach($result as $row)

 {

  $amount = $row["amount"];

  $count = $count +1;

  $tot_amount = $tot_amount + $amount;  

  

  



$html .= '

 

<tr>

<td width="4%">'.$count.'</td>

  <td width="6%">'.$row["id"].'</td>

<td width="68%">'.$row["description"].'</td>

<td width="12%">'.$row["chq_no"].'</td>

<td width="10%">'.$row["chq_date"].'</td>

<td width="10%" class="rightside">'.number_format($amount, 2).'</td>

   

  

   

    

   </tr>

   



        ';



}









  $html .='

  </table>

  <hr />



  <table width="100%">



<tr>

<td width="80%" class="rightside">Total :</td><td width="20%" class="rightside">'.number_format($tot_amount, 2).'</td>

  </tr>





  </table>

  





  </div>



  <div id="footer">

  <hr />

  <table width="100%">

  <tr>

  <td width="70%" class="rightside">Closing Balance : </td><td width="30%" class="rightside">'.number_format($balance, 2).'</td>

  </tr>

  </table>

   

  </div>';

 $pdf = new Pdf();



 $file_name = 'Cust_Ledger.pdf';

 $pdf->setPaper('A4', 'portrait');



 $pdf->loadHtml($html);

 $pdf->render();

 $pdf->stream($file_name, array("Attachment" => false));



?>