<?PHP 
$total_overd = 0; 
$total_due = 0; 
$current_date = time(); 

// Select Overdue and Due Loan Instalments from LTRANS
$query_overd_due = "SELECT * FROM ltrans 
                    LEFT JOIN loans ON ltrans.loan_id = loans.loan_id 
                    LEFT JOIN customer ON loans.cust_id = customer.cust_id 
                    WHERE ltrans_date IS NULL AND loanstatus_id = 2 
                    AND (ltrans_due <= $current_date) 
                    ORDER BY ltrans_due";

$query_overd_due_result = mysqli_query($db_link, $query_overd_due);

// Select Overdue and Due Group Loan Instalments from ITRANS
$query_group_overd_due = "SELECT 
                            group_ltrans.*,
                            group_loans.loan_no,
                            `group`.group_name,
                            (SELECT member_contact 
                             FROM group_member 
                             WHERE group_id = group_loans.group_id 
                             ORDER BY member_id LIMIT 1) as member_contact
                        FROM 
                            group_ltrans 
                        LEFT JOIN 
                            group_loans ON group_ltrans.loan_id = group_loans.loan_id 
                        LEFT JOIN 
                            `group` ON group_loans.group_id = `group`.group_id 
                        WHERE 
                            ltrans_date IS NULL 
                            AND group_loans.loanstatus_id = 2 
                            AND (ltrans_due <= $current_date) 
                        ORDER BY 
                            ltrans_due";

$query_group_overd_due_result = mysqli_query($db_link, $query_group_overd_due);
?>

<table id="tb_table">
    <colgroup>
        <col width="15%">
        <col width="35%">
        <col width="20%">
        <col width="20%">
        <col width="25%">
    </colgroup>
    <tr>
        <form class="export" action="rep_export.php" method="post">
            <th class="title" colspan="5">Loan Instalments Due or Overdue 
                <!-- Export Button -->
                <input type="submit" name="export_rep" value="Export" />
            </th>
        </form>
    </tr>
    <tr>
        <th>Loan No.</th>
        <th>Customer/Group Name</th>
        <th>Due Date</th>
        <th>Amount Due</th>
        <th>Contact</th>
    </tr>

<?PHP 
// Display individual loan instalments
if (!$query_overd_due_result) {
    echo "Error: " . mysqli_error($db_link);
} else {
    while ($row_overd_due = mysqli_fetch_assoc($query_overd_due_result)){
        if ($row_overd_due['ltrans_due'] < $current_date) {
            $status = 'Overdue';
        } elseif ($row_overd_due['ltrans_due'] == $current_date) {
            $status = 'DUE';
        } else {
            $status = 'Not Due';
        }
        echo '<tr> 
                <td><a href="loan.php?lid='.$row_overd_due['loan_id'].'">'.$row_overd_due['loan_no'].'</a></td> 
                <td>'.$row_overd_due['cust_name'].'</td> 
                <td>'.date("d.m.Y",$row_overd_due['ltrans_due']).'</td> 
                <td>'.number_format($row_overd_due['ltrans_principaldue']+$row_overd_due['ltrans_interestdue']).' '.$_SESSION['set_cur'].'</td> 
                <td>'.$row_overd_due['cust_phone'].'</td> 
              </tr>' ;
        $total_due += $row_overd_due['ltrans_principaldue'] + $row_overd_due['ltrans_interestdue'];
    }
}

// Display group loan instalments
if (!$query_group_overd_due_result) {
    echo "Error: " . mysqli_error($db_link);
} else {
    while ($row_group_overd_due = mysqli_fetch_assoc($query_group_overd_due_result)){
        if ($row_group_overd_due['ltrans_due'] < $current_date) {
            $status = 'Overdue';
        } elseif ($row_group_overd_due['ltrans_due'] == $current_date) {
            $status = 'DUE';
        } else {
            $status = 'Not Due';
        }
        echo '<tr> 
                <td><a href="group_loan.php?loan_id='.$row_group_overd_due['loan_id'].'">'.$row_group_overd_due['loan_no'].'</a></td> 
                <td>'.$row_group_overd_due['group_name'].'</td> 
                <td>'.date("d.m.Y",$row_group_overd_due['ltrans_due']).'</td> 
                <td>'.number_format($row_group_overd_due['ltrans_principaldue']+$row_group_overd_due['ltrans_interestdue']).' '.$_SESSION['set_cur'].'</td> 
                <td>'.$row_group_overd_due['member_contact'].'</td> 
              </tr>';
        $total_due += $row_group_overd_due['ltrans_principaldue'] + $row_group_overd_due['ltrans_interestdue'];
    }
}

echo '<tr class="balance"> 
        <td colspan="5">Total Amount Due: '.number_format($total_due).' '.$_SESSION['set_cur'].'</td> 
      </tr>';
?>
</table>
