<?php
 include('function.php');
 
 include('db_con.php');
 //stock_update($connect);
 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();
}
date_default_timezone_set('Asia/Karachi');

$curstamp = date('Y-m-d h:i:s a');
 $html = '';
 $sr = 0;
 
 $count = 0;
        $sr = 0;
        $tot_amount= 0;
        $gross_amount = 0;
        $amount = 0;
        $tot = 0;
 
$cust_id = $_GET['cust_id'];
$acc_no = $cust_id;

$statement = $connect->prepare("select name from customers where cust_id = '".$cust_id."'");
$statement->execute();
$acc_name = $statement->fetchColumn();

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

 ?>
<!DOCTYPE html>
<html>
<head>
   <title><?php echo basename(__FILE__, '.php'); ?></title>
  <style>
    * {
      
      font-family: arial;
    }
  .rightside {text-align: right;}
  table td{
    
    border-bottom: 1px solid gray;
    padding: 5px;
  }
  .head_row {
    font-weight: bold;
  }
  thead td {
    font-weight: bold;
     border-bottom: 1px solid gray;
      border-top: 1px solid gray;
       background-color: :  gray;
  }
  table.header {
    font-family: arial; 
    font-size: 16px;
    font-weight: bold;
  }
  </style>
</head>
<body>
 <table class="header" width="100%">
  
 
<tr>
  <td colspan="9" width="100%">Invoice Aging Detail For <?php echo $acc_name;?></td>
  
</tr>
 </table> 
  <table id="main" width="100%">
  
  <thead>
<tr>
  <td width="3%">Sr.</td>
  <td width="5%">ID</td>
  <td width="15%">Date</td>
   
  <td width="26%">Description</td>
  <td width="15%">Due Date</td>
  <td width="10%">DueDays</td>
  
  <td width="8%">Over</td>
  <td width="8%">TotalDays</td>
  <td width="10%" class="rightside">Amount</td>
  
</tr>
  </thead>
  
<tbody>
  
<?php
      $statement = $connect->prepare("
select m.s_id,m.inv_no,m.dated,concat(m.description,' - Sale') description, m.due_day,m.due_dated,sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)   + sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)  * gst_pr / 100 -m.discount + m.freight as amount from sale m, sale_detail d where m.s_id = d.s_id AND m.inv_type = 'Credit' 
and m.cust_id = '".$cust_id."' group by s_id,inv_no,dated,description,m.cust_id
union all
select m.jv_id s_id,'' inv_no,m.dated,concat(m.description,' - General') description, 0 due_day,'2020-06-01' due_dated,sum(d.debit) amount from jv m, jv_detail d where m.jv_id = d.jv_id and m.source ='GL jv' and d.acc_no = '".$acc_no."' group by m.jv_id
union all
  select m.pay_id s_id,'' inv_no,m.dated,concat(m.description,m.paid_by,' ',' - Payment Voucher') description, 0 due_day,m.dated due_dated,sum(m.amount) from pay_voucher m where m.v_id = '".$acc_no."' group by pay_id
  

   order by dated
");
      $statement->execute();
      $result = $statement->fetchAll();
     


      $GL_statement = $connect->prepare("
select sum(d.credit) from jv m,jv_detail d where m.jv_id=d.jv_id and m.source ='AR InvR' and d.acc_no =  '".$acc_no."'
");
      $GL_statement->execute();
      $sreturn = $GL_statement->fetchColumn();


       $GL_statement = $connect->prepare("
select sum(d.credit) from jv m,jv_detail d where m.jv_id=d.jv_id and m.source ='GL jv' and d.acc_no =  '".$acc_no."'
");
      $GL_statement->execute();
      $glpay = $GL_statement->fetchColumn();

       $glpay = $glpay + $sreturn;

     



    

      $payment_statement = $connect->prepare("
select sum(m.amount) from rec_voucher m where m.cust_id = '".$acc_no."'
");
      $payment_statement->execute();
      $payment = $payment_statement->fetchColumn();
 
    $payment = $payment + $glpay;
  
  
  $count = 0; 
  $tot_amount = 0;
 
        foreach($result as $row)
 {
 
  $dated = date('Y-m-d');
  $due_dated = $row['due_dated'];
 $date1_ts = strtotime($due_dated);
    $date2_ts = strtotime($dated);
    $diff = $date2_ts - $date1_ts;
    $diff = round($diff / 86400);
$net_total = 0;
$due_day = $row['due_day'];

//$days = $days - $due_day;
$amount = $row["amount"];
$pre_amount = $row["amount"];
$amount = round($amount);
 


  if($diff > 0){

if($amount > $payment)
{
 $count++;
 if($payment > 0 ){$amount = $amount - $payment;}

$tot_amount = $tot_amount + $amount;
$tot_days = $row['due_day']+$diff;

echo '
  <tr>
<td>'.$count.'</td>
  
<td>'.$row["s_id"].'</td>
<td>'.$row["dated"].'</td>

<td>'.$row["description"].'</td>
<td>'.$row["due_dated"].'</td>
<td>'.$row["due_day"].'</td>


<td>'.$diff.'</td>
<td>'.$tot_days.'</td>

<td class="rightside">'.number_format($amount,2).'</td>  
    
   </tr>';

   }
   $payment = $payment - $pre_amount;
   
}
}
 
  
   echo '
  <tr>
<td colspan="4">Printed at : '.$curstamp.'</td><td colspan="4" class="rightside"><b>Total :</b></td>
  
<td class="rightside"><b>'.number_format($tot_amount,2).'</b></td>
  
  
   
    
 ';
  
 
?>
</tbody>
 
 </table><!--end main table-->
    

 </body>
 </html>