<?php
// Database connection

include("include/connection.php");


$conn = new mysqli($host, $user, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Query to fetch all collection records
$sql = "SELECT 
member.memberNo,
member.memberName,
loan.date,
loan.TotalLoan,
loan.LoanBalance
FROM member
JOIN loan ON (loan.memberNo = member.memberNo) WHERE loan.LoanBalance > 0";

$result = $conn->query($sql);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Collection Records</title>
    <!-- Bootstrap CSS for responsive design -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css">
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
        }

        /* Custom table styling */
        table.dataTable td, table.dataTable th {
            padding: 10px;
            border-bottom: 1px solid #ddd;
        }

        /* Align text columns */
        .align-left {
            text-align: left;
        }

        /* Responsive styling for mobile */
        @media (max-width: 768px) {
            .container {
                width: 100% !important;
                padding: 10px;
            }
            table {
                width: 100%;
                font-size: 14px;
            }
            th, td {
                text-align: left;
                padding: 8px;
            }
        }

        table.dataTable thead th {
            background-color: #f2f2f2;
            font-weight: bold;
            border-bottom: 2px solid #ccc;
        }

        table.dataTable tfoot th {
            font-weight: bold;
            border-top: 2px solid #ccc;
        }

        /* Button styling */
        .btn-main {
            margin-bottom: 20px;
        }
    </style>
</head>
<body>

<div class="container mt-4">
    <!-- "Back to Main Page" Button -->
    <a href="index.html" class="btn btn-primary btn-main">Back to Main Page</a> <!-- Update href to the actual main page link -->

    <h2 class="text-center mb-4">Customers Records</h2>
    <div class="table-responsive">
        <table id="collectionTable" class="table table-striped table-bordered">
            <thead>
                <tr>
                    <th>MNo</th>
                    <th>MemberName</th>
                    <th>date</th>
					<th>Loan</th>
                    <th>LoanBalance</th>
                </tr>
            </thead>
            <tbody>
                <?php if ($result->num_rows > 0): ?>
                    <?php while($row = $result->fetch_assoc()): ?>
                        <tr>
                            <td><?php echo $row['memberNo']; ?></td>
                            <td><?php echo $row['memberName']; ?></td>
                             <td><?php echo $row['date']; ?></td>
							<td><?php echo number_format($row['TotalLoan']); ?></td>
                            <td><?php echo number_format($row['LoanBalance']); ?></td>
                        </tr>
                    <?php endwhile; ?>
                <?php endif; ?>
            </tbody>
            <tfoot>
                <tr>
                    <th colspan="4">Total Loan Outstanding</th>
                    <th id="totalPaid">0.00</th>
                </tr>
            </tfoot>
        </table>
    </div>
</div>

<!-- Include jQuery and DataTables JS -->
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>

<script>
    $(document).ready(function() {
        $('#collectionTable').DataTable({
            "footerCallback": function(row, data, start, end, display) {
                var api = this.api();

                // Calculate the total over all pages
                var total = api
                    .column(4)
                    .data()
                    .reduce(function (a, b) {
                        return parseFloat(a) + parseFloat(b.replace(/[\$,]/g, ''));
                    }, 0);

                // Calculate the total for the current page
                var pageTotal = api
                    .column(4, { page: 'current' })
                    .data()
                    .reduce(function (a, b) {
                        return parseFloat(a) + parseFloat(b.replace(/[\$,]/g, ''));
                    }, 0);

                // Update footer with the page total
                $(api.column(4).footer()).html(
                    'Tsh' + pageTotal.toLocaleString(undefined, { minimumFractionDigits: 2, maximumFractionDigits: 2 })
                );
            }
        });
    });
</script>

<!-- Bootstrap JS for responsive design -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>

</body>
</html>
