<?php

require_once 'pdf.php';

 

 include('db_con.php');

 

 

 

 $html = '';



 $cur_date = date('Y-m-d');



 $dated = date('d-m-Y');



 $sql = "SELECT date_sub(curdate(),interval 30 day)";

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

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

$thirtyday = $statement->fetchColumn();





$sql = "SELECT date_sub(curdate(),interval 60 day)";

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

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

$sixtyday = $statement->fetchColumn();



$balance  = 0;



  $_30days = 0;

  $_60days = 0;

  $_90days = 0;

  $payment = 0;

  $op_balance= 0;

 

  



 















 $sql = "SELECT LEFT(a.name,35) name,a.op_balance,30days,60days,90days, payment,sreturn,totpayment from(

    select b.name,sum(op_balance) as op_balance,sum(30days) 30days,sum(60days) as 60days,sum(90days) as 90days,sum(payment) payment,sum(sreturn) sreturn,sum(payment)+sum(sreturn) totpayment from(

    select name,0 as op_balance,0 as 30days,0 as 60days,0 as 90days,0 as payment,0 as sreturn from customers    

    UNION ALL

    select a.acc_name as name,0 as op_balance, sum(d.debit) as 30days,0 as 60days,0 as 90days,0 as payment,0 as sreturn from jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and a.acc_no=d.acc_no and a.acc_type = 'DEBTOR' and m.source='AR Inv' and m.dated between '".$thirtyday."' and '".$cur_date."' group by m.jv_id

    union all

    select a.acc_name as name,0 as op_balance, 0 as 30days,sum(d.debit) as 60days,0 as 90days,0 as payment,0 as sreturn  from jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and a.acc_no=d.acc_no and a.acc_type = 'DEBTOR' and m.source='AR Inv' and m.dated >= '".$sixtyday."' and  m.dated < '".$thirtyday."' group by m.jv_id

    union all

   select a.acc_name as name,0 as op_balance,0 as 30days,0 as 60days,sum(d.debit) as 90days,0 as payment,0 as sreturn  from jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and a.acc_no=a.acc_no and a.acc_type = 'DEBTOR' and m.source='AR Inv' and  m.dated < '".$sixtyday."' group by m.jv_id

    union all

    select a.acc_name as name,0 as op_balance, 0 as 30days,0 as 60days,0 as 90days,sum(d.credit) as payment,0 as sreturn from jv_detail  d, jv m, accounts a where  m.jv_id=d.jv_id and a.acc_no=d.acc_no  and a.acc_type = 'DEBTOR' and m.source='AR Pay'  group by m.jv_id

    UNION ALL

    select a.acc_name as name,0 as op_balance,0 as 30days,0 as 60days,0 as 90days,0 as payment,sum(d.credit) as sreturn from jv_detail d, jv m, accounts a  where m.jv_id=d.jv_id  and a.acc_no=d.acc_no and a.acc_type = 'DEBTOR' and m.source='AR InvR' and a.acc_no=d.acc_no group by m.jv_id

    

    

    ) b  group by name) a ";

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

            $statement->execute();



         $result = $statement->fetchAll();

         



 

 

 

 $html .=' <style>

    @page { margin:100px 20px; }

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

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

    #content {margin:0;}

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

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

    

    #table_header th {

    border-top:1px solid black;

    border-bottom:1px solid black;

  }

  </style>



  <div id="header">



  <h3>Receivable Aging Report</h3>

  <table width="100%">

  </table>

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



  <tr>

  

  <th width="52%">Name</th>

  

  

  <th width="12%" class="rightside">30days</th>

  <th width="12%" class="rightside">60Days</th>

  <th width="12%" class="rightside">90Days</th>

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







  </tr>



  </table>



  </div>

  

';

foreach($result as $row)

 {



  

  $_30days = $row["30days"];

  $_60days = $row["60days"];

  $_90days = $row["90days"];

  $payment = $row["totpayment"];

 

  //$balance = $row["balance"];

  $op_balance = $row["op_balance"];

 // $sreturn = $row["sreturn"];







  

  





$_90days= $_90days+$op_balance;





$_90days = $_90days - $payment;



//$_90days = $_90days - $sreturn;



if($_90days < 0){$_60days=$_60days+$_90days;$_90days=0;}





if($_60days < 0){$_30days = $_30days+$_60days;$_60days=0;}







//if($payment >= $_90days){$payment = $payment-$_90days;$_90days=0;}else{$_90days=$_90days-$payment;}



//if($payment >= $_60days){$payment=$payment-$_60days;$_60days=0;}else{$_60days=$_60days-$payment;}



//if($payment >= $_30days){$payment=$payment-$_30days;$_30days=0;}else{$_30days=$_30days-$payment;}













$balance =  floatval($_90days)+ floatval($_60days)+ floatval($_30days) ;  







  

$html .= '

  <div id="content">

  

  <table width="100%">

  ';

  if($balance <> 0)

  {

    $html .='

  



<tr>

  

<td width="52%">'.$row["name"].'</td>



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

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

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

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

  

   

    

   </tr>

   ';

   }

$html .='

   

  </table>





  </div>



  <div id="footer">

  <hr />

  <table width="100%">

  <tr>

  <td width="10%">Printed at:</td><td width="90%">'.$dated.'</td>

  </tr>

  </table>

    

  </div>



        ';



}

 $pdf = new Pdf();



 $file_name = 'rpt_aging.pdf';

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



 $pdf->loadHtml($html);

 $pdf->render();

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



?>