<?php
include('connect.php');

// Set headers to download file as Excel
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=doctors_list.xls");

// Output the table
echo "ID\tuserid\tfullname\tABM_id\tABM_NAME\tDoctor Name\tNo of patients screened\ttm_fullname\tabm_userid\tabm_fullname\tzbm_userid\tzbm_fullname
\n"; // Column headers (adjust as per your table)

$sql = "SELECT 
    d.id,
    d.doctor_name,
    d.clinic_address,
    d.city AS doctor_city,
    d.state AS doctor_state,
    d.no_of_patients_screened,

    -- TM-level data (if tm_id is TM)
    CASE WHEN tm.designation = 'TM' THEN tm.userid ELSE NULL END AS tm_userid,
    CASE WHEN tm.designation = 'TM' THEN tm.fullname ELSE NULL END AS tm_fullname,

    -- ABM-level data
    CASE 
        WHEN tm.designation = 'TM' THEN abm.userid 
        WHEN tm.designation = 'ABM' THEN tm.userid
        ELSE NULL 
    END AS abm_userid,
    CASE 
        WHEN tm.designation = 'TM' THEN abm.fullname 
        WHEN tm.designation = 'ABM' THEN tm.fullname
        ELSE NULL 
    END AS abm_fullname,

    -- ZBM-level data
    CASE 
        WHEN tm.designation = 'TM' THEN zbm.userid 
        WHEN tm.designation = 'ABM' THEN zbm.userid 
        ELSE NULL 
    END AS zbm_userid,
    CASE 
        WHEN tm.designation = 'TM' THEN zbm.fullname 
        WHEN tm.designation = 'ABM' THEN zbm.fullname 
        ELSE NULL 
    END AS zbm_fullname

FROM doctors d
JOIN usermaster tm ON d.tm_id = tm.userid
LEFT JOIN usermaster abm ON tm.designation = 'TM' AND tm.manager_id = abm.userid AND abm.designation = 'ABM'
LEFT JOIN usermaster zbm 
     ON (
         (tm.designation = 'TM' AND abm.manager_id = zbm.userid) OR
         (tm.designation = 'ABM' AND tm.manager_id = zbm.userid)
     )
     AND zbm.designation = 'ZBM'


 "; // Adjust columns as needed
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
echo $id . "\t" . $row['id'] . "\t" . $row['doctor_name'] . "\t" . $row['clinic_address']. "\t" .  $row['doctor_city'] . "\t" .  $row['doctor_state']. "\t" . $row['no_of_patients_screened'] . "\t" . $row['tm_userid']. "\t" . $row['tm_fullname']. "\t" . $row['abm_userid']. "\t"
    . $row['abm_fullname'] . "\t"
    . $row['zbm_userid'] . "\t" . $row['zbm_fullname'] . "\n";
        
        $id++;
    }
} else {
    echo "No data found";
}

$conn->close();
?>
