<?php
// Connect to MySQL
include_once './dbconn.php';

// Define the column to group by
$groupByColumn = 'hospitals';

// Fetch data from database ordered by the grouping column
$sql = "SELECT * FROM `web_app_enrollee_data` ORDER BY `$groupByColumn`";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // Set headers to force download
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="panellist.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');

    // Create a file pointer connected to the output stream
    $output = fopen('php://output', 'w');

    // Initialize serial number
    $serial = 1;

    // Output CSV column headers with Serial Number as the first column
    fputcsv($output, array('SN', 'ID', 'ENROLLMENT SERIAL NO', 'COMPANY NAME', 'SURNAME','FIRST NAME','MIDDLE NAME','GENDER','PHONE NUMBER','EMAIL ADDRESS','DATE OF BIRTH','PLAN CATEGORY','PLAN TYPE', 'HMO ID', 'PRINCIPAL ID','ADDRESS','CITY','STATE','LGA','EMPLOYEE ID','HOSPITAL','GENOTYPE','BLOOD GROUP','ALLERGIES','HAD PRE-EXISTING CONDITION','PRE EXISTING CONDITION','HAS SURGICAL HISTORY','SURGICAL HISTORY','PASSPORT LINK','STATUS','CREATED', 'ENROLLEE TYPE'));

    // Initialize variables for grouping
    $currentGroup = null;

    // Output each row of the data, grouped by the specified column
    while ($row = mysqli_fetch_assoc($result)) {
        $groupValue = $row[$groupByColumn];

        // Check if we are starting a new group
        if ($groupValue !== $currentGroup) {
            // If we are not on the first group, add a blank row before the next group
            if ($currentGroup !== null) {
                fputcsv($output, []); // Add blank row between groups
            }

            $currentGroup = $groupValue;
            // Output a row to indicate the start of a new group
            fputcsv($output, ["Provider: $currentGroup"]);
        }

        // Prepare row data with serial number
        $rowData = array('SN' => $serial++);
        $rowData = array_merge($rowData, $row);

        // Output the row data to CSV
        fputcsv($output, $rowData);
    }

    // Close the file pointer
    fclose($output);
    
    // Close connection
    mysqli_close($conn);

    // Redirect after export
    header("Location: index.php?exported");
    exit();
} else {
    echo "No data found";
}

// Close connection
mysqli_close($conn);
?>
