<?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 
/*
$sql = "
    SELECT c.memberNo, m.memberName, m.memberPhoneNo, l.amount, c.amountPaid, c.receiptNo, c.paidDate, c.officerID, o.offerName AS officerName
    FROM collection c
    LEFT JOIN member m ON c.memberNo = m.memberNo
    LEFT JOIN officer o ON c.officerID = o.officerNo
    LEFT JOIN loan l ON l.loanNo = c.loanNo";
*/


$sql = "SELECT * FROM loan_view";





$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>
    <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;
        }
		
        /* CSS for aligning table */
        table.dataTable td, table.dataTable th {
            padding: 10px; /* Add some padding for better spacing */
            border-bottom: 1px solid #ddd; /* Add bottom border */
        }
        
        /* Align text columns (Member Name, Officer Name, etc.) to the left */
        .align-left {
            text-align: left;
        }

        /* Align number columns (Amount Paid, Phone No) to the right */
        .align-right {
            text-align: left;
        }

        /* Header styling */
        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;
        }


    </style>
</head>
<body>

<div class="container">
    <h2>Collection Records</h2>
    <table id="collectionTable" class="display" border="1">
        <thead>
            <tr>
                <th>MemberNo</th>
                <th>MemberName</th>
                <th>PhoneNo</th>
				<th>LoanBalance</th>
                <th>AmountPaid</th>
				 <th>ReceiptNo</th>
				<th>Paid Date</th>
				<th>Officer Name</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['memberPhoneNo']; ?></td>
                        <td><?php echo number_format($row['LoanBalance']); ?></td>
						<td><?php echo number_format($row['amountPaid']); ?></td>
						<td><?php echo $row['receiptNo']; ?></td>
						<td><?php echo $row['paidDate']; ?></td>
                        <td><?php echo $row['officerName']; ?></td>
                        
                    </tr>
                <?php endwhile; ?>
            <?php endif; ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="6">Total Amount Collected</th>
                <th id="totalPaid">0.00</th>
            </tr>
        </tfoot>
    </table>
</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(6)
                    .data()
                    .reduce(function (a, b) {
                        return parseFloat(a) + parseFloat(b.replace(/[\$,]/g, ''));
                    }, 0);

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

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

</body>
</html>