<?php
include 'db_config.php';

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="2024 Annual Conference Participants.xls"');
header('Cache-Control: max-age=0');

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $totalMealsAllocated = 0;
    $totalMealsRemaining = 0;
    $totalMealsUsed = 0;

    echo "<h3>2024 Annual Conference Participants List</h3>";
    echo "<table border='1'>";
    echo "<tr>
            <th>ID</th>
            <th>Full Name</th>
            <th>Email</th>
            <th>Phone</th>
            <th>Payment Status</th>
            <th>Commitment Status</th>
            <th>Hotel</th>
            <th>Institution</th>
            <th>Membership Type</th>
            <th>Meals Allocated</th>
            <th>Meals Used</th>
            <th>Meals Remaining</th>
          </tr>";

    while ($row = $result->fetch_assoc()) {
        $mealsUsed = $row['meals_allocated'] - $row['meals_remaining'];
        
        echo "<tr>
                <td>" . $row['id'] . "</td>
                <td>" . $row['fullname'] . "</td>
                <td>" . $row['email'] . "</td>
                <td>" . $row['phone'] . "</td>
                <td>" . $row['payment_status'] . "</td>
                <td>" . $row['commitment_status'] . "</td>
                <td>" . $row['hotel'] . "</td>
                <td>" . $row['institution'] . "</td>
                <td>" . $row['membership_type'] . "</td>
                <td>" . $row['meals_allocated'] . "</td>
                <td>" . $mealsUsed . "</td>
                <td>" . $row['meals_remaining'] . "</td>
              </tr>";

        // Summing up the totals
        $totalMealsAllocated += $row['meals_allocated'];
        $totalMealsRemaining += $row['meals_remaining'];
        $totalMealsUsed += $mealsUsed;
    }

    // Adding the row for totals
    echo "<tr style='font-weight: bold;'>
            <td colspan='9' style='text-align:right;'>Total:</td>
            <td>" . $totalMealsAllocated . "</td>
            <td>" . $totalMealsUsed . "</td>
            <td>" . $totalMealsRemaining . "</td>
          </tr>";

    echo "</table>";
} else {
    echo "No registered users found.";
}

$conn->close();
?>
