<?php 
include_once('../libs/main.php');
// Include required libraries
require 'vendor/autoload.php'; // Load PhpSpreadsheet via Composer

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$clinicId =2;
$clinicObj = new clinic();

$clinic =$clinicObj->fromId($clinicId);
$clinicName =$clinic->clinic_name;
// Create new spreadsheet
$spreadsheet = new Spreadsheet();

$treatment_plansObj = new treatment_plans();
$fields = array('category','COUNT(dependant_id) AS dependant_count','SUM(amount) AS total_amount','COUNT(treatment_plans_id) AS total_plans');
$where = ' clinic_id = '.$clinicId.' AND ( treatment_plan_status="Pending" OR treatment_plan_status="Scheduled")  ';
$groupBy = array('category');
$orderBy = array('category' => 'ASC');
$arg = array('fields' => $fields, 'where' => $where, 'groupBy' => $groupBy,'orderBy' => $orderBy);
$allPlans = $treatment_plansObj->getAllRecords($arg);
$sheetsData  =array();
if ($allPlans && count($allPlans) > 0) {
    
    $titleArray[] =array("Category","Total Patients","Total Plans","Total Amount");
    foreach ($allPlans as $key => $plan) {
        $titleArray[]=array($plan->category,$plan->dependant_count,$plan->total_plans,$plan->total_amount);
    }
    
}
$sheetsData ['Category'] =$titleArray;
$titleArray=[];

$fields = array('subcategory','COUNT(dependant_id) AS dependant_count','SUM(amount) AS total_amount','COUNT(treatment_plans_id) AS total_plans');
$groupBy = array('subcategory');
$orderBy = array('subcategory' => 'ASC');
$arg = array('fields' => $fields, 'where' => $where, 'groupBy' => $groupBy,'orderBy' => $orderBy);
$allPlans = $treatment_plansObj->getAllRecords($arg);
if ($allPlans && count($allPlans) > 0) {
    
    $titleArray[] =array("Sub category","Total Patients","Total Plans","Total Amount");
    foreach ($allPlans as $key => $plan) {
        $titleArray[]=array($plan->subcategory,$plan->dependant_count,$plan->total_plans,$plan->total_amount);
    }
    
}
$sheetsData ['Sub Category'] =$titleArray;
$titleArray=[];

$fields = array('subcategory_desc','COUNT(dependant_id) AS dependant_count','SUM(amount) AS total_amount','COUNT(treatment_plans_id) AS total_plans');
$groupBy = array('subcategory_desc');
$orderBy = array('subcategory_desc' => 'ASC');
$arg = array('fields' => $fields, 'where' => $where, 'groupBy' => $groupBy,'orderBy' => $orderBy);
$allPlans = $treatment_plansObj->getAllRecords($arg);
if ($allPlans && count($allPlans) > 0) {
    
    $titleArray[] =array("Sub Sub category","Total Patients","Total Plans","Total Amount");
    foreach ($allPlans as $key => $plan) {
        $titleArray[]=array($plan->subcategory_desc,$plan->dependant_count,$plan->total_plans,$plan->total_amount);
    }
    
}
// Loop through the sheetsData array to create sheets
foreach ($sheetsData as $sheetTitle => $data) {
    // Create a new sheet
    $sheet = $spreadsheet->createSheet();
    $sheet->setTitle($sheetTitle);

    // Populate data into the sheet
    $sheet->fromArray($data, NULL, 'A1');
}
// Remove the default blank sheet
$spreadsheet->removeSheetByIndex(0);

// Save the file
$filename = 'multiple_sheets_example.xlsx';
$writer = new Xlsx($spreadsheet);
$writer->save($filename);

// Download the file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Content-Length: ' . filesize($filename));
readfile($filename);
exit;
