Untitled

<?php

// Ensure all required files and libraries are included
require_once("../vendor/autoload.php"); // Assumes PhpSpreadsheet is installed via Composer
require_once("../includes/db_lib.php");
require_once("../includes/db_util.php");
require_once("../includes/user_lib.php");

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Authentication and authorization logic remains unchanged

// Set headers for downloading the Excel file directly
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="report.xlsx"');
header('Cache-Control: max-age=0');

DbUtil::switchToGlobal();
$lab = query_associative_one("SELECT lab_config_id, name, db_name FROM lab_config WHERE lab_config_id = ?", [$lab_id]);

// Parameters and SQL query construction logic remains largely unchanged

// Modify the query to include JOINs for patient_custom_data and specimen_custom_data
$query = <<<EOQ
SELECT $fields_sql
FROM specimen AS s
INNER JOIN specimen_type AS st ON s.specimen_type_id = st.specimen_type_id
INNER JOIN test AS t ON s.specimen_id = t.specimen_id
INNER JOIN patient AS p ON s.patient_id = p.patient_id
LEFT JOIN patient_custom_data AS pcd ON p.patient_id = pcd.patient_id
LEFT JOIN specimen_custom_data AS scd ON s.specimen_id = scd.specimen_id
WHERE s.date_collected BETWEEN ? AND ?
AND t.test_type_id = ?;
EOQ;

// Initialize PhpSpreadsheet object
$spreadsheet = new Spreadsheet();

foreach ($test_type_ids as $test_type_id) {
    $results = query_associative_all($query, [$start_date, $end_date, $test_type_id]);
    $test_type = TestType::getById($test_type_id, $lab['lab_config_id']);
    
    // Sheet creation and data insertion logic adapted for PhpSpreadsheet
    $sheet = $spreadsheet->createSheet();
    $sheet_name = substr(str_replace(['*', ':', '/', '\\', '?', '[', ']'], ' ', $test_type->name), 0, 31);
    $sheet->setTitle($sheet_name);

    // Populate sheet with data from results
    // Headers
    foreach ($headers as $index => $header) {
        $sheet->setCellValueByColumnAndRow($index + 1, 1, $header);
    }
    // Data
    $rowNumber = 2;
    foreach ($results as $row) {
        $columnNumber = 1;
        foreach ($row as $value) {
            $sheet->setCellValueByColumnAndRow($columnNumber, $rowNumber, $value);
            $columnNumber++;
        }
        $rowNumber++;
    }
}

// Remove the placeholder sheet created by default
$spreadsheet->removeSheetByIndex(0);

// Save the Excel file to php://output
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');