<?php



require_once 'pdf.php';

 

 include('db_con.php');

 $html = '';

 //$fdate = $_GET['fdate'];

 

 //$tdate = $_GET['tdate'];

 $count = 0;

 $acc_no = $_GET['acc_no'];

 $fdate = $_GET['fdate'];

 $tdate = $_GET['tdate'];

 $tot_opening = 0;

 $tot_debit = 0;

 $tot_credit = 0;

 $tot_balance = 0;

 $balance = 0;







 $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 a.acc_no,a.acc_name,a.op_balance,a.op_bal,opening,a.debit,a.credit,balance from(

    select b.acc_no,b.acc_name,sum(b.op_balance) op_balance,sum(b.op_bal) op_bal,sum(b.op_balance)+sum(b.op_bal) opening,sum(b.debit) debit,sum(b.credit) credit,sum(b.op_balance)  + sum(b.op_bal) + sum(b.debit) - sum(b.credit) balance from

    (select acc_no,acc_name,op_balance,0 as op_bal ,0 as debit,0 as credit from accounts where acc_parent = :acc_no

      union all

      select d.acc_no,a.acc_name,0 as op_balance, sum(d.debit-d.credit) as op_bal,0 as debit,0 as credit from

    jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.dated < '".$fdate."' and a.acc_parent =:acc_no

    group by acc_no,acc_name

    union all

    select d.acc_no,a.acc_name,0 as op_balance, 0 as op_bal,sum(d.debit) as debit,sum(d.credit) as credit from

    jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.dated between '".$fdate."' and '".$tdate."' and a.acc_parent =:acc_no

    group by acc_no,acc_name

    )b group by b.acc_no,b.acc_name)a ");

 $statement->execute(

  array(

   ':acc_no'       =>  $acc_no

  )

 );

 $result = $statement->fetchAll(); 



 $html .=' <style>

    @page { margin:160px 20px; }

    @page {margin-bottom: 40px;}

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

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

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

    .rightside{text-align:right; font-size:13px;}

    .total{text-align:right;border-top:1px solid black;}

    #footer .page:after { content: counter(page, upper-roman); }

    

    #table_header th {

    border-top:1px solid black;

    border-bottom:1px solid black;

  }



  table.tot_table td{

    border-top :1px solid black;



  }

  

  </style>



  <div id="header">



  <h3>Sub Accounts Report</h3>

  <table width="100%">

  <tr>

  <td width="15%">Parent Account  :</td><td width="55%">'.$acc_name.'</td><td width="10%" class="rightside">From :</td><td width="20%" class="rightside">'.$fdate.'</td>

  </tr>

  <tr>

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

  </tr>

  

  </table>

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



  <tr>

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

  <th width="7%">Acc_No.</th>

  <th width="50%">Acc_Name</th>

  

  <th width="10%" class="rightside">Opening</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)

 {











 

  // if($count == 1){$balance = $opening + $debit - $credit; }else{$balance = $balance + $debit - $credit;}

   

 

  $debit = $row["debit"];

  $credit = $row["credit"];

  $opening = $row["opening"];

  $tot_debit = $tot_debit + $debit;

  $tot_credit = $tot_credit + $credit;

  $balance = $row["balance"];



  $tot_balance = $tot_balance + $balance;

  $tot_opening = $tot_opening + $opening;

  $count = $count+1;

  

$html .= '

  <div id="content">

  

  <table width="100%">

<tr>

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

  

  <td width="7%">'.$row["acc_no"].'</td>

<td width="50%">'.$row["acc_name"].'</td>



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

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

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

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

  

   

    

   </tr>

   

  </table>

<hr />



  </div>





        ';



      }



$html .='

   





  </div>



  <div id="footer">

  <hr />

  <table width="100%">

  <tr>

  <td width="70%" class="rightside"> </td><td width="30%" class="rightside"></td>

  </tr>

  </table>

   

  </div>



        ';





        $html .='

<table width="100%">

  <tr>

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

  <td width="10%" class="rightside">'.number_format($tot_opening, 2).'</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($tot_balance, 2).'</td>

  </tr>

  </table>';

 $pdf = new Pdf();



 $file_name = 'Parent_report.pdf';

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



 $pdf->loadHtml($html);

 $pdf->render();

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



?>