<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
    // === Pie Chart ===
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Layout;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
require 'db.php';

// Inputs
$month = $_POST['month'] ?? '';
$year = $_POST['year'] ?? '';
$reportFormat = $_POST['report_format'] ?? '';
$reportTypes = $_POST['report_type'] ?? [];

if (!$month || !$year || $reportFormat !== 'excel') {
    exit('Invalid parameters');
}
$spreadsheet = new Spreadsheet();
$firstSheetUsed = false;
// Only generate Petty Cash report if the checkbox is selected
if (in_array('petty_cash', $reportTypes)) {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Petty Cash Report');
    $firstSheetUsed = true;
    // Calculate 12-month range: previous month = end, start 11 months before
$selectedMonth = (int)$month; 
$selectedYear = (int)$year;

// End date = last day of selected month
$endDate = new DateTime("$selectedYear-$selectedMonth-01");
$endDate->modify('last day of this month');

// Start date = 11 months before the selected month, first day
$startDate = (clone $endDate)->modify('-11 months')->modify('first day of this month');

$startDateStr = $startDate->format('Y-m-d'); // Dec 1, 2024 if Nov 2025 selected
$endDateStr = $endDate->format('Y-m-d');     // Nov 30, 2025

    // Fetch transactions (Cash only)
    $stmt = $pdo->prepare("
        SELECT 
            MONTH(inv_date) AS month_num, 
            YEAR(inv_date) AS year_num,
            SUM(total_price) AS total
        FROM transactions
        WHERE inv_date BETWEEN :start AND :end
          AND terms = 'Cash'
        GROUP BY YEAR(inv_date), MONTH(inv_date)
        ORDER BY YEAR(inv_date), MONTH(inv_date)
    ");
    $stmt->execute(['start' => $startDateStr, 'end' => $endDateStr]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Prepare 12 months array
$anchor = new DateTime("$selectedYear-$selectedMonth-01");

$months = [];
for ($i = 11; $i >= 0; $i--) {
    $dt = (clone $anchor)->modify("-$i months");
    $months[$dt->format('Y-m')] = 0;
}

    // Fill actual data
    foreach ($rows as $row) {
        $key = sprintf("%04d-%02d", $row['year_num'], $row['month_num']);
        if (isset($months[$key])) {
            $months[$key] = (float)$row['total'];
        }
    }

    // Create Excel sheet
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Petty Cash Report');

    // Title
    $selectedMonthName = $endDate->format('F');
    $selectedYear = $endDate->format('Y');
    $sheet->mergeCells('A1:B1');
    $sheet->setCellValue('A1', "Petty Cash Report for $selectedMonthName, $selectedYear");

    // Styling Title
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    // Column headers
    $sheet->setCellValue('A2', 'Month');
    $sheet->setCellValue('B2', 'MONTHLY EXPENSES (RM)');

    // Styling headers
    $sheet->getStyle('A2:B2')->getFont()->setBold(true);
    $sheet->getStyle('A2:B2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    // Column widths
    $sheet->getColumnDimension('A')->setWidth(59);
    $sheet->getColumnDimension('B')->setWidth(30);

    // Data rows
    $rowNum = 3;
    foreach ($months as $key => $total) {
        $dt = DateTime::createFromFormat('Y-m', $key);
        $sheet->setCellValue("A$rowNum", $dt->format('F Y'));
        $sheet->setCellValue("B$rowNum", $total);
        $sheet->getStyle("B$rowNum")->getNumberFormat()->setFormatCode('#,##0.00');

        // Center alignment
        $sheet->getStyle("A$rowNum:B$rowNum")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        // Borders
        $sheet->getStyle("A$rowNum:B$rowNum")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

        $rowNum++;
    }

    // Totals and averages
    $totals = array_values($months);
    $total12 = array_sum($totals);
    $avg12 = $total12 / 12;
    $last6 = array_slice($totals, -6);
    $total6 = array_sum($last6);
    $avg6 = $total6 / 6;

    // Leave one empty row
    $rowNum++;

    // Total & averages labels and values
    $summary = [
        'TOTAL EXPENSES' => $total12,
        'AVERAGE MONTHLY EXPENSES' => round($avg12,2),
        'TOTAL EXPENSES FOR 6 MONTH' => $total6,
        'AVERAGE MONTHLY EXPENSES FOR 6 MONTH' => round($avg6,2)
    ];

    foreach($summary as $label => $value){
        $sheet->setCellValue("A$rowNum", $label);
        $sheet->setCellValue("B$rowNum", $value);
        $sheet->getStyle("B$rowNum")->getNumberFormat()->setFormatCode('#,##0.00');

        // Bold and center
        $sheet->getStyle("A$rowNum:B$rowNum")->getFont()->setBold(true);
        $sheet->getStyle("A$rowNum:B$rowNum")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        // Borders
        $sheet->getStyle("A$rowNum:B$rowNum")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

        $rowNum++;
    }

    // Add borders for headers
    $sheet->getStyle("A2:B2")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
}

// Only generate Monthly report if the checkbox is selected
if (in_array('monthly', $reportTypes)) {
    if (!$firstSheetUsed) {
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Monthly Report');
        $firstSheetUsed = true;
    } else {
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Monthly Report');
    }

    // Selected month/year
    $selectedMonth = (int)$month;
    $selectedYear = (int)$year;
    $startDate = new DateTime("$selectedYear-$selectedMonth-01");
    $startDateStr = $startDate->format('Y-m-01');
    $endDateStr = $startDate->format('Y-m-t');

    // Fetch top 10 departments by total expenses
    $stmt = $pdo->prepare("
        SELECT department, SUM(total_price) AS total
        FROM transactions
        WHERE inv_date BETWEEN :start AND :end
        GROUP BY department
        ORDER BY total DESC
        LIMIT 10
    ");
    $stmt->execute(['start' => $startDateStr, 'end' => $endDateStr]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $topTotal = array_sum(array_column($rows, 'total'));

    // Title
    $selectedMonthName = $startDate->format('F');
    $selectedYear = $startDate->format('Y');
    $sheet->mergeCells('A1:D1');
    $sheet->setCellValue('A1', "Monthly Report - $selectedMonthName, $selectedYear");
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    // Empty row
    $sheet->mergeCells('A2:D2');
    $sheet->setCellValue('A2', "");

    // Sub-title
    $sheet->mergeCells('A3:D3');
    $sheet->setCellValue('A3', "DEPARTMENTS TOP 10 PERCENTAGE OF EXPENSES ($selectedMonthName $selectedYear)");
    $sheet->getStyle('A3')->getFont()->setBold(true);
    $sheet->getStyle('A3')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    // Column headers
    $headerRow = 4;
    $sheet->setCellValue("A$headerRow", 'No');
    $sheet->setCellValue("B$headerRow", 'DEPARTMENT');
    $sheet->setCellValue("C$headerRow", 'TOTAL EXPENSES (RM)');
    $sheet->setCellValue("D$headerRow", 'PERCENTAGE OF TOTAL EXPENSES (%)');
    $sheet->getRowDimension($headerRow)->setRowHeight(30);
    $sheet->getStyle("A$headerRow:D$headerRow")->getFont()->setBold(true);
            $sheet->getStyle("A$headerRow:E$headerRow")->getBorders()->getAllBorders()
            ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
    $sheet->getStyle("A$headerRow:D$headerRow")->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
        ->setWrapText(true);

    // Column widths
    $sheet->getColumnDimension('A')->setWidth(5);
    $sheet->getColumnDimension('B')->setWidth(52);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(23.9);
    $sheet->getColumnDimension('E')->setWidth(1.1);

    // Data rows
    $rowNum = $headerRow + 1;
    $counter = 1;
    $sumTotal = 0;
    $sumPercentage = 0;
    foreach ($rows as $row) {
        $sheet->setCellValue("A$rowNum", $counter);
        $sheet->setCellValue("B$rowNum", $row['department']);
        $sheet->setCellValue("C$rowNum", $row['total']);
        $sheet->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');

        $percentage = $row['total'] / $topTotal * 100;
        $sheet->setCellValue("D$rowNum", $percentage);
        $sheet->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');

        $sheet->getStyle("A$rowNum:D$rowNum")->getAlignment()
            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $sheet->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()
            ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        $sheet->getRowDimension($rowNum)->setRowHeight(30);  
        

        $sumTotal += $row['total'];
        $sumPercentage += $percentage;
        $rowNum++;
        $counter++;
    }

    // Grand Total
    $sheet->setCellValue("B$rowNum", 'Grand Total');
    $sheet->setCellValue("C$rowNum", $sumTotal);
    $sheet->setCellValue("D$rowNum", round($sumPercentage, 2));
    $sheet->getStyle("B$rowNum:D$rowNum")->getFont()->setBold(true);
    $sheet->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');
    $sheet->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');
    $sheet->getStyle("A$rowNum:D$rowNum")->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()
        ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
    $mergeStartRow = $headerRow;
$mergeEndRow   = $rowNum;

for ($r = $mergeStartRow; $r <= $mergeEndRow; $r++) {
    $sheet->mergeCells("D{$r}:E{$r}");
}
$sheet->getStyle("D{$mergeStartRow}:E{$mergeEndRow}")
    ->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
    ->setWrapText(true);

    // Helper column for pie labels
    $dataStartRow = 5;
    $dataEndRow = $rowNum - 1; // last data row before Grand Total
    $helperColumn = 'Z';
    for ($i = $dataStartRow; $i <= $dataEndRow; $i++) {
        $dept = $sheet->getCell("B$i")->getValue();
        $percent = $sheet->getCell("D$i")->getValue();
        $sheet->setCellValue("$helperColumn$i", $dept . ' ' . round($percent, 2) . '%');
    }

    // Chart data
    $labels = [
        new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues(
            \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues::DATASERIES_TYPE_STRING,
            "'Monthly Report'!\$Z$dataStartRow:\$Z$dataEndRow"
        )
    ];
    $values = [
        new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues(
            \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues::DATASERIES_TYPE_NUMBER,
            "'Monthly Report'!\$D$dataStartRow:\$D$dataEndRow"
        )
    ];

    $series = new \PhpOffice\PhpSpreadsheet\Chart\DataSeries(
        \PhpOffice\PhpSpreadsheet\Chart\DataSeries::TYPE_PIECHART,
        null,
        [0],
        [],
        $labels,
        $values
    );

    $layout = new \PhpOffice\PhpSpreadsheet\Chart\Layout();
    $layout->setShowVal(false);
    $layout->setShowPercent(false);
    $layout->setShowCatName(true);
    $layout->setShowLeaderLines(true);

$legend = new Legend(
    Legend::POSITION_RIGHT,
    null,
    false
);

    $plotArea = new \PhpOffice\PhpSpreadsheet\Chart\PlotArea($layout, [$series]);
    $title = new \PhpOffice\PhpSpreadsheet\Chart\Title('Top 10 Departments Percentage');
    $chart = new \PhpOffice\PhpSpreadsheet\Chart\Chart('top10_pie', $title, $legend, $plotArea, true, 0, null, null);

    // Function to calculate bottom-right cell from top-left using approximate width/height in cm
$chartWidthCm  = 18.5;
$chartHeightCm = 13;

// Conversion
$columnsNeeded = ceil($chartWidthCm / 2.1);   // ≈ 9 columns
$rowsNeeded    = ceil($chartHeightCm / 0.53); // ≈ 25 rows
$startColumn = 'A';
$startRow    = 17;

$endColumn   = 'E';   // A → J (9 columns)
$endRow      = 41;    // 17 + 25 rows

$chart->setTopLeftPosition($startColumn . $startRow);
$chart->setBottomRightPosition($endColumn . $endRow);

    $sheet->addChart($chart);

    // Make helper column narrow
    $sheet->getColumnDimension($helperColumn)->setWidth(0.1);

    // Narrow print margins
    $sheet->getPageMargins()->setTop(0.2);
    $sheet->getPageMargins()->setBottom(0.2);
    $sheet->getPageMargins()->setLeft(0.2);
    $sheet->getPageMargins()->setRight(0.2);
  
// --- Date calculation (6 months range) ---
$selectedMonth = (int)$month; 
$selectedYear = (int)$year;

$endDate = new DateTime("$selectedYear-$selectedMonth-01");
$endDate->modify('last day of this month'); // last day of selected month
$startDate = (clone $endDate)->modify('-5 months')->modify('first day of this month');

$startDateStr = $startDate->format('Y-m-d');
$endDateStr = $endDate->format('Y-m-d');

// --- Fetch top 10 departments from database ---
$stmt = $pdo->prepare("
    SELECT department, SUM(total_price) / 6 AS total
    FROM transactions
    WHERE inv_date BETWEEN :start AND :end
    GROUP BY department
    ORDER BY total DESC
    LIMIT 10
");
$stmt->execute(['start' => $startDateStr, 'end' => $endDateStr]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$topTotal = array_sum(array_column($rows, 'total'));

// --- Set starting row ---
$startRow = 42;

// Title
$sheet->mergeCells("A{$startRow}:D{$startRow}");
$sheet->setCellValue("A{$startRow}", "DEPARTMENTS TOP 10 PERCENTAGE OF AVERAGE EXPENSES (" . $startDate->format('M Y') . " - " . $endDate->format('M Y') . ")");
$sheet->getStyle("A{$startRow}")->getFont()->setBold(true);
$sheet->getStyle("A{$startRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Column headers
$headerRow = $startRow + 1;
$sheet->setCellValue("A$headerRow", 'No');
$sheet->setCellValue("B$headerRow", 'DEPARTMENT');
$sheet->setCellValue("C$headerRow", 'AVERAGE MONTHLY EXPENSES (RM) FOR 6 MONTH');
$sheet->setCellValue("D$headerRow", 'PERCENTAGE OF AVERAGE MONTHLY EXPENSES FOR 6 MONTH (%)');

$sheet->getRowDimension($headerRow)->setRowHeight(30);
$sheet->getStyle("A$headerRow:D$headerRow")->getFont()->setBold(true);
$sheet->getStyle("A$headerRow:E$headerRow")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
$sheet->getStyle("A$headerRow:D$headerRow")->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_CENTER)
    ->setVertical(Alignment::VERTICAL_CENTER)
    ->setWrapText(true);

// Data rows
$rowNum = $headerRow + 1;
$counter = 1;
$sumTotal = 0;
$sumPercentage = 0;

foreach ($rows as $row) {
    $sheet->setCellValue("A$rowNum", $counter);
    $sheet->setCellValue("B$rowNum", $row['department']);
    $sheet->setCellValue("C$rowNum", $row['total']);
    $sheet->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');

    $percentage = $row['total'] / $topTotal * 100;
    $sheet->setCellValue("D$rowNum", $percentage);
    $sheet->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');

    $sheet->getStyle("A$rowNum:D$rowNum")->getAlignment()
        ->setHorizontal(Alignment::HORIZONTAL_CENTER)
        ->setVertical(Alignment::VERTICAL_CENTER);

    $sheet->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    $sheet->getRowDimension($rowNum)->setRowHeight(30);

    $sumTotal += $row['total'];
    $sumPercentage += $percentage;
    $rowNum++;
    $counter++;
}

// Grand Total
$sheet->setCellValue("B$rowNum", 'Grand Total');
$sheet->setCellValue("C$rowNum", $sumTotal);
$sheet->setCellValue("D$rowNum", round($sumPercentage, 2));

$sheet->getStyle("B$rowNum:D$rowNum")->getFont()->setBold(true);
$sheet->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');
$sheet->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');
$sheet->getStyle("A$rowNum:D$rowNum")->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_CENTER)
    ->setVertical(Alignment::VERTICAL_CENTER);
$sheet->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

// Merge helper column for chart labels
$mergeStartRow = $headerRow;
$mergeEndRow = $rowNum;
for ($r = $mergeStartRow; $r <= $mergeEndRow; $r++) {
    $sheet->mergeCells("D{$r}:E{$r}");
}

// Helper column for pie chart labels
$dataStartRow = $headerRow + 1;
$dataEndRow = $rowNum - 1;
$helperColumn = 'Z';
for ($i = $dataStartRow; $i <= $dataEndRow; $i++) {
    $dept = $sheet->getCell("B$i")->getValue();
    $percent = $sheet->getCell("D$i")->getValue();
    $sheet->setCellValue("$helperColumn$i", $dept . ' ' . round($percent, 2) . '%');
}

// Pie Chart
$labels = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, "'Monthly Report'!\$Z$dataStartRow:\$Z$dataEndRow")];
$values = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, "'Monthly Report'!\$D$dataStartRow:\$D$dataEndRow")];
$series = new DataSeries(DataSeries::TYPE_PIECHART, null, [0], [], $labels, $values);
$layout = new \PhpOffice\PhpSpreadsheet\Chart\Layout();
$layout->setShowVal(false)->setShowPercent(false)->setShowCatName(true)->setShowLeaderLines(true);
$plotArea = new PlotArea($layout, [$series]);
$title = new Title('Top 10 Departments Percentage');
$chart = new Chart('top10_pie', $title, null, $plotArea, true, 0, null, null);

$chart->setTopLeftPosition('A' . ($startRow + 14));
$chart->setBottomRightPosition('E' . ($startRow + 40));
$sheet->addChart($chart);

// Make helper column narrow
$sheet->getColumnDimension($helperColumn)->setWidth(0.1);

// --- Create new sheet for Monthly Report 2 ---
$sheet2 = $spreadsheet->createSheet();
$sheet2->setTitle('Monthly Report 2');

// --- Selected month/year ---
$selectedMonth = (int)$month;
$selectedYear = (int)$year;
$startDate = new DateTime("$selectedYear-$selectedMonth-01");
$startDateStr = $startDate->format('Y-m-01');
$endDateStr = $startDate->format('Y-m-t');
$selectedMonthName = $startDate->format('F');

// --- Fetch all departments for the selected month ---
$stmt = $pdo->prepare("
    SELECT department, SUM(total_price) AS total
    FROM transactions
    WHERE inv_date BETWEEN :start AND :end
    GROUP BY department
    ORDER BY total DESC
");
$stmt->execute(['start' => $startDateStr, 'end' => $endDateStr]);
$allRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Remove top 10
$remainingRows = array_slice($allRows, 10);
$topTotal = array_sum(array_column($remainingRows, 'total'));

// --- Title ---
$startRow = 1;
$sheet2->mergeCells("A{$startRow}:D{$startRow}");
$sheet2->setCellValue("A{$startRow}", "Monthly Report 2 - Remaining Departments ($selectedMonthName $selectedYear)");
$sheet2->getStyle("A{$startRow}")->getFont()->setBold(true)->setSize(16);
$sheet2->getStyle("A{$startRow}")->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

// --- Column headers ---
$headerRow = $startRow + 1;
$sheet2->setCellValue("A$headerRow", 'No');
$sheet2->setCellValue("B$headerRow", 'DEPARTMENT');
$sheet2->setCellValue("C$headerRow", "TOTAL EXPENSES (RM) - $selectedMonthName $selectedYear");
$sheet2->setCellValue("D$headerRow", "PERCENTAGE OF TOTAL EXPENSES (%)");

$sheet2->getRowDimension($headerRow)->setRowHeight(30);
$sheet2->getStyle("A$headerRow:D$headerRow")->getFont()->setBold(true);
$sheet2->getStyle("A$headerRow:E$headerRow")->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$sheet2->getStyle("A$headerRow:D$headerRow")->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
    ->setWrapText(true);
    
        // Column widths
    $sheet2->getColumnDimension('A')->setWidth(5);
    $sheet2->getColumnDimension('B')->setWidth(52);
    $sheet2->getColumnDimension('C')->setWidth(20);
    $sheet2->getColumnDimension('D')->setWidth(23.9);
    $sheet2->getColumnDimension('E')->setWidth(1.1);
    
        // Narrow print margins
    $sheet2->getPageMargins()->setTop(0.2);
    $sheet2->getPageMargins()->setBottom(0.2);
    $sheet2->getPageMargins()->setLeft(0.2);
    $sheet2->getPageMargins()->setRight(0.2);

// --- Data rows ---
$rowNum = $headerRow + 1;
$counter = 1;
$sumTotal = 0;
$sumPercentage = 0;

foreach ($remainingRows as $row) {
    $sheet2->setCellValue("A$rowNum", $counter);
    $sheet2->setCellValue("B$rowNum", $row['department']);
    $sheet2->setCellValue("C$rowNum", $row['total']);
    $sheet2->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');

    $percentage = $topTotal > 0 ? $row['total'] / $topTotal * 100 : 0;
    $sheet2->setCellValue("D$rowNum", $percentage);
    $sheet2->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');

    $sheet2->getStyle("A$rowNum:D$rowNum")->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    $sheet2->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
    $sheet2->getRowDimension($rowNum)->setRowHeight(30);

    $sumTotal += $row['total'];
    $sumPercentage += $percentage;
    $rowNum++;
    $counter++;
}

// --- Grand Total ---
$sheet2->setCellValue("B$rowNum", 'Grand Total');
$sheet2->setCellValue("C$rowNum", $sumTotal);
$sheet2->setCellValue("D$rowNum", round($sumPercentage, 2));

$sheet2->getStyle("B$rowNum:D$rowNum")->getFont()->setBold(true);
$sheet2->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');
$sheet2->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');
$sheet2->getStyle("A$rowNum:D$rowNum")->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$sheet2->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$mergeStartRow = $headerRow;
$mergeEndRow   = $rowNum;

for ($r = $mergeStartRow; $r <= $mergeEndRow; $r++) {
    $sheet2->mergeCells("D{$r}:E{$r}");
}
$sheet2->getStyle("D{$mergeStartRow}:E{$mergeEndRow}")
    ->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
    ->setWrapText(true);

// --- Helper column and chart setup same as first sheet ---
$dataStartRow = $headerRow + 1;
$dataEndRow = $rowNum - 1;
$helperColumn = 'Z';
for ($i = $dataStartRow; $i <= $dataEndRow; $i++) {
    $dept = $sheet2->getCell("B$i")->getValue();
    $percent = $sheet2->getCell("D$i")->getValue();
    $sheet2->setCellValue("$helperColumn$i", $dept . ' ' . round($percent, 2) . '%');
}

$labels = [new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues(
    \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues::DATASERIES_TYPE_STRING,
    "'Monthly Report 2'!\$Z$dataStartRow:\$Z$dataEndRow"
)];
$values = [new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues(
    \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues::DATASERIES_TYPE_NUMBER,
    "'Monthly Report 2'!\$D$dataStartRow:\$D$dataEndRow"
)];
$series = new \PhpOffice\PhpSpreadsheet\Chart\DataSeries(
    \PhpOffice\PhpSpreadsheet\Chart\DataSeries::TYPE_PIECHART,
    null,
    [0],
    [],
    $labels,
    $values
);
$layout = new \PhpOffice\PhpSpreadsheet\Chart\Layout();
$layout->setShowVal(false)->setShowPercent(false)->setShowCatName(true)->setShowLeaderLines(true);
$plotArea = new \PhpOffice\PhpSpreadsheet\Chart\PlotArea($layout, [$series]);
$title = new \PhpOffice\PhpSpreadsheet\Chart\Title('Remaining Departments Percentage');
$chart = new \PhpOffice\PhpSpreadsheet\Chart\Chart('remaining_pie', $title, null, $plotArea, true, 0, null, null);

$chart->setTopLeftPosition('A' . ($dataEndRow + 3));
$chart->setBottomRightPosition('E' . ($dataEndRow + 45));

$sheet2->addChart($chart);

// Make helper column narrow
$sheet2->getColumnDimension($helperColumn)->setWidth(0.1);

// --- Date calculation (6 months range) ---
$selectedMonth = (int)$month; 
$selectedYear = (int)$year;

$endDate = new DateTime("$selectedYear-$selectedMonth-01");
$endDate->modify('last day of this month'); // last day of selected month
$startDate = (clone $endDate)->modify('-5 months')->modify('first day of this month');

$startDateStr = $startDate->format('Y-m-d');
$endDateStr = $endDate->format('Y-m-d');

// --- Fetch all departments ---
$stmt = $pdo->prepare("
    SELECT department, SUM(total_price) / 6 AS total
    FROM transactions
    WHERE inv_date BETWEEN :start AND :end
    GROUP BY department
    ORDER BY total DESC
");
$stmt->execute(['start' => $startDateStr, 'end' => $endDateStr]);
$allRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Remove top 10
$remainingRows = array_slice($allRows, 10);
$topTotal = array_sum(array_column($remainingRows, 'total'));

// --- Set starting row ---
$startRow = $dataEndRow + 46;

// Title
$sheet2->mergeCells("A{$startRow}:D{$startRow}");
$sheet2->setCellValue("A{$startRow}", "DEPARTMENTS REMAINING AFTER TOP 10 AVERAGE EXPENSES (" . $startDate->format('M Y') . " - " . $endDate->format('M Y') . ")");
$sheet2->getStyle("A{$startRow}")->getFont()->setBold(true);
$sheet2->getStyle("A{$startRow}")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

// Column headers
$headerRow = $startRow + 1;
$sheet2->setCellValue("A$headerRow", 'No');
$sheet2->setCellValue("B$headerRow", 'DEPARTMENT');
$sheet2->setCellValue("C$headerRow", 'AVERAGE MONTHLY EXPENSES (RM) FOR 6 MONTH');
$sheet2->setCellValue("D$headerRow", 'PERCENTAGE OF AVERAGE MONTHLY EXPENSES FOR 6 MONTH (%)');

$sheet2->getRowDimension($headerRow)->setRowHeight(30);
$sheet2->getStyle("A$headerRow:D$headerRow")->getFont()->setBold(true);
$sheet2->getStyle("A$headerRow:E$headerRow")->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$sheet2->getStyle("A$headerRow:D$headerRow")->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
    ->setWrapText(true);

// Data rows
$rowNum = $headerRow + 1;
$counter = 1;
$sumTotal = 0;
$sumPercentage = 0;

foreach ($remainingRows as $row) {
    $sheet2->setCellValue("A$rowNum", $counter);
    $sheet2->setCellValue("B$rowNum", $row['department']);
    $sheet2->setCellValue("C$rowNum", $row['total']);
    $sheet2->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');

    $percentage = $topTotal > 0 ? $row['total'] / $topTotal * 100 : 0;
    $sheet2->setCellValue("D$rowNum", $percentage);
    $sheet2->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');

    $sheet2->getStyle("A$rowNum:D$rowNum")->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    $sheet2->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
    $sheet2->getRowDimension($rowNum)->setRowHeight(30);

    $sumTotal += $row['total'];
    $sumPercentage += $percentage;
    $rowNum++;
    $counter++;
}

// Grand Total
$sheet2->setCellValue("B$rowNum", 'Grand Total');
$sheet2->setCellValue("C$rowNum", $sumTotal);
$sheet2->setCellValue("D$rowNum", round($sumPercentage, 2));

$sheet2->getStyle("B$rowNum:D$rowNum")->getFont()->setBold(true);
$sheet2->getStyle("C$rowNum")->getNumberFormat()->setFormatCode('"RM"#,##0.00');
$sheet2->getStyle("D$rowNum")->getNumberFormat()->setFormatCode('0.00"%"');
$sheet2->getStyle("A$rowNum:D$rowNum")->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$sheet2->getStyle("A$rowNum:E$rowNum")->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

// Merge helper column for chart labels
$mergeStartRow = $headerRow;
$mergeEndRow = $rowNum;
for ($r = $mergeStartRow; $r <= $mergeEndRow; $r++) {
    $sheet2->mergeCells("D{$r}:E{$r}");
}

// Helper column for pie chart labels
$dataStartRow = $headerRow + 1;
$dataEndRow = $rowNum - 1;
$helperColumn = 'Z';
for ($i = $dataStartRow; $i <= $dataEndRow; $i++) {
    $dept = $sheet2->getCell("B$i")->getValue();
    $percent = $sheet2->getCell("D$i")->getValue();
    $sheet2->setCellValue("$helperColumn$i", $dept . ' ' . round($percent, 2) . '%');
}

// Pie Chart
$labels = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, "'Monthly Report 2'!\$Z$dataStartRow:\$Z$dataEndRow")];
$values = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, "'Monthly Report 2'!\$D$dataStartRow:\$D$dataEndRow")];
$series = new DataSeries(DataSeries::TYPE_PIECHART, null, [0], [], $labels, $values);
$layout = new \PhpOffice\PhpSpreadsheet\Chart\Layout();
$layout->setShowVal(false)->setShowPercent(false)->setShowCatName(true)->setShowLeaderLines(true);
$plotArea = new PlotArea($layout, [$series]);
$title = new Title('Remaining Departments Percentage');
$chart = new Chart('remaining_pie', $title, null, $plotArea, true, 0, null, null);

$chart->setTopLeftPosition('A' . ($dataEndRow + 3));
$chart->setBottomRightPosition('E' . ($dataEndRow + 45));
$sheet2->addChart($chart);

// Make helper column narrow
$sheet2->getColumnDimension($helperColumn)->setWidth(0.1);
}

// Only generate Details report if the checkbox is selected
if (in_array('details', $reportTypes)) {
        if (!$firstSheetUsed) {
        // Use the default active sheet if no sheet has been used
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Details Report');
    } else {
        // Otherwise, create a new sheet
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Details Report');
    }
    
    // Selected month/year
    $selectedMonth = (int)$month;
    $selectedYear = (int)$year;

$endDate = new DateTime("$selectedYear-$selectedMonth-01");
$startDate = (clone $endDate)->modify('-11 months'); // 12 months range
$endDate->modify('last day of this month'); // include full November

$startStr = $startDate->format('Y-m-d');
$endStr = $endDate->format('Y-m-d');
$stmtDept = $pdo->query("SELECT name FROM departments ORDER BY name ASC");
$departments = $stmtDept->fetchAll(PDO::FETCH_COLUMN);

$stmt = $pdo->prepare("
    SELECT 
        d.name AS department, 
        DATE_FORMAT(t.inv_date, '%Y-%m') AS month, 
        IFNULL(SUM(t.total_price), 0) AS monthly_total
    FROM departments d
    LEFT JOIN transactions t 
        ON t.department = d.name 
        AND t.inv_date BETWEEN :start AND :end
    GROUP BY d.name, month
    ORDER BY d.name, month
");
$stmt->execute([
    ':start' => $startStr,
    ':end' => $endStr
]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$data = [];
foreach ($departments as $dept) {
    $data[$dept] = [];
    $period = clone $startDate;
    for ($i=0; $i<12; $i++) {
        $monthKey = $period->format('Y-m');
        $data[$dept][$monthKey] = 0.00;
        $period->modify('+1 month');
    }
}

// Fill actual transaction data
foreach ($rows as $row) {
    $data[$row['department']][$row['month']] = (float)$row['monthly_total'];
}

// Set header text
$sheet->setCellValue("A1", "Details Report for " . $endDate->format('F, Y'));

// Make it bold and bigger
$sheet->getStyle("A1")->getFont()->setBold(true)->setSize(16);

// Optional: merge across columns if you want it centered
$sheet->mergeCells("A1:B1"); // merge A1 to B1
$sheet->getStyle("A1")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Row counter
$rowCounter = 3;

foreach ($data as $dept => $months) {
// Set department title
$sheet->setCellValue("A$rowCounter", "Department: $dept");

// Merge columns A and B
$sheet->mergeCells("A$rowCounter:B$rowCounter");

// Center the text horizontally
$sheet->getStyle("A$rowCounter")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

// Optional: make it bold
$sheet->getStyle("A$rowCounter")->getFont()->setBold(true);

$rowCounter++;

// Column headers - bold + border + center alignment
$sheet->setCellValue("A$rowCounter", "Month");
$sheet->setCellValue("B$rowCounter", "MONTHLY EXPENSES (RM)");
$sheet->getStyle("A$rowCounter:B$rowCounter")->getFont()->setBold(true);
$sheet->getStyle("A$rowCounter:B$rowCounter")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
$sheet->getStyle("A$rowCounter:B$rowCounter")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$rowCounter++;

// Month data - border + right alignment for numbers
$monthlyTotals = [];
foreach ($months as $month => $total) {
    $sheet->setCellValue("A$rowCounter", date("M Y", strtotime($month . "-01")));
    $sheet->setCellValue("B$rowCounter", $total);
    $sheet->getStyle("A$rowCounter:B$rowCounter")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    $sheet->getStyle("B$rowCounter")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
       // Currency format with 2 decimals

// Format column B as RM currency with 2 decimals
$sheet->getStyle("B$rowCounter")
      ->getNumberFormat()
      ->setFormatCode('#,##0.00');
    $monthlyTotals[] = $total;
    $rowCounter++;
}

// Calculations - bold + border + right alignment for numbers
$total12 = array_sum($monthlyTotals);
$avg12 = $total12 / 12;
$last6Months = array_slice($monthlyTotals, 6); // last 6 months
$total6 = array_sum($last6Months);
$avg6 = $total6 / 6;
$calculationRows = [
    "TOTAL EXPENSES" => $total12,
    "AVERAGE MONTHLY EXPENSES" => $avg12,
    "TOTAL EXPENSES FOR 6 MONTHS" => $total6,
    "AVERAGE MONTHLY EXPENSES FOR 6 MONTHS" => $avg6
];

foreach ($calculationRows as $label => $value) {
    $sheet->setCellValue("A$rowCounter", $label);
    $sheet->setCellValue("B$rowCounter", $value);
    $sheet->getStyle("A$rowCounter:B$rowCounter")->getFont()->setBold(true);
    $sheet->getStyle("A$rowCounter:B$rowCounter")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    $sheet->getStyle("B$rowCounter")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle("B$rowCounter")
      ->getNumberFormat()
      ->setFormatCode('#,##0.00');
    $rowCounter++;
    
// Add an empty row before the next department

    
        // Column widths
    $sheet->getColumnDimension('A')->setWidth(59);
    $sheet->getColumnDimension('B')->setWidth(30);
}
$rowCounter++;
}

// --- Create new sheet for Monthly Report 2 ---
$sheet2 = $spreadsheet->createSheet();
$sheet2->setTitle('Summary Report');

// 12-month range (Dec 2024 - Nov 2025)
$endDate12 = new DateTime("$selectedYear-$selectedMonth-01");
$startDate12 = (clone $endDate12)->modify('-11 months');
$endDate12->modify('last day of this month'); // 30 Nov 2025

$startStr12 = $startDate12->format('Y-m-d');
$endStr12 = $endDate12->format('Y-m-d');

// 6-month range (Jun 2025 - Nov 2025)
$startDate6 = (clone $endDate12)->modify('-5 months')->modify('first day of this month');
$startStr6 = $startDate6->format('Y-m-d');
$endStr6 = $endDate12->format('Y-m-d'); // same end date

// Fetch departments
$stmtDept = $pdo->query("SELECT name FROM departments ORDER BY name ASC");
$departments = $stmtDept->fetchAll(PDO::FETCH_COLUMN);

// 12 months totals
$stmt12 = $pdo->prepare("
    SELECT department, SUM(total_price) as total_12
    FROM transactions
    WHERE inv_date BETWEEN :start AND :end
    GROUP BY department
");
$stmt12->execute(['start' => $startStr12, 'end' => $endStr12]);
$data12 = $stmt12->fetchAll(PDO::FETCH_KEY_PAIR); // dept => total_12

$grandTotal12 = array_sum($data12);

// 6 months totals
$stmt6 = $pdo->prepare("
    SELECT department, SUM(total_price) as total_6
    FROM transactions
    WHERE inv_date BETWEEN :start AND :end
    GROUP BY department
");
$stmt6->execute(['start' => $startStr6, 'end' => $endStr6]);
$data6 = $stmt6->fetchAll(PDO::FETCH_KEY_PAIR);

$grandTotal6 = array_sum($data6);

// Header
$sheet2->mergeCells('A1:H1');
$sheet2->setCellValue('A1', "DEPARTMENTS PERCENTAGE OF EXPENSES ({$startDate12->format('M Y')} - {$endDate12->format('M Y')})");
$sheet2->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$sheet2->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Column titles
$headers = [
    'No.', 'Department', 
    'Total Expenses - 12 Months (RM)', 'Average Monthly Expenses (RM)', 
    'Total Expenses - 6 Months (RM)', 'Average Monthly Expenses - 6 Months (RM)', 
    'Percentage of Total Expenses (%)', 'Percentage of Avg Monthly Expenses - 6 Months (%)'
];

$sheet2->fromArray($headers, NULL, 'A3');

// Styling
$sheet2->getStyle('A3:H3')->getFont()->setBold(true);
$sheet2->getStyle('A3:H3')->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_CENTER)
    ->setVertical(Alignment::VERTICAL_CENTER)
    ->setWrapText(true);

$sheet2->getStyle('A3:H3')->getBorders()->getAllBorders()
    ->setBorderStyle(Border::BORDER_THIN);

// Row height
$sheet2->getRowDimension(3)->setRowHeight(30);

// Data rows
$rowNum = 4;
$no = 1;
foreach ($departments as $dept) {
    $total12 = $data12[$dept] ?? 0;
    $avg12 = $total12 / 12;
    $total6 = $data6[$dept] ?? 0;
    $avg6 = $total6 / 6;

    $perc12 = $grandTotal12 > 0 ? ($total12 / $grandTotal12) * 100 : 0;
    $perc6 = $grandTotal6 > 0 ? ($total6 / $grandTotal6) * 100 : 0;

// Set values (keep them numeric)
$sheet2->setCellValue("A$rowNum", $no);
$sheet2->setCellValue("B$rowNum", $dept);

$sheet2->setCellValue("C$rowNum", $total12);
$sheet2->setCellValue("D$rowNum", $avg12);
$sheet2->setCellValue("E$rowNum", $total6);
$sheet2->setCellValue("F$rowNum", $avg6);

// Percentages must be decimals (e.g. 25% = 0.25)
$sheet2->setCellValue("G$rowNum", $perc12 / 100);
$sheet2->setCellValue("H$rowNum", $perc6 / 100);

// Amount formatting: comma + 2 decimals
$sheet2->getStyle("C$rowNum:F$rowNum")
    ->getNumberFormat()
    ->setFormatCode('#,##0.00');

// Percentage formatting: 2 decimals + %
$sheet2->getStyle("G$rowNum:H$rowNum")
    ->getNumberFormat()
    ->setFormatCode('0.00%');

    // Border & Center
    $sheet2->getStyle("A$rowNum:H$rowNum")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    $sheet2->getStyle("A$rowNum:H$rowNum")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    
// Wrap text + vertical center
$sheet2->getStyle("A$rowNum:H$rowNum")->getAlignment()
    ->setWrapText(true)
    ->setVertical(Alignment::VERTICAL_CENTER);

// Row height
$sheet2->getRowDimension($rowNum)->setRowHeight(30);
// Column widths
$sheet2->getColumnDimension('A')->setWidth(5);   // No.
$sheet2->getColumnDimension('B')->setWidth(50);  // Department

foreach (range('C', 'H') as $col) {
    $sheet2->getColumnDimension($col)->setWidth(20);
}

    $rowNum++;
    $no++;
}
$dataStart = 4;
$dataEnd   = $rowNum - 1;
$grandRow  = $rowNum;

$sheet2->setCellValue("B$grandRow", 'GRAND TOTAL');

$sheet2->setCellValue("C$grandRow", "=SUM(C$dataStart:C$dataEnd)");
$sheet2->setCellValue("D$grandRow", "=SUM(D$dataStart:D$dataEnd)");
$sheet2->setCellValue("E$grandRow", "=SUM(E$dataStart:E$dataEnd)");
$sheet2->setCellValue("F$grandRow", "=SUM(F$dataStart:F$dataEnd)");
$sheet2->setCellValue("G$grandRow", "=SUM(G$dataStart:G$dataEnd)");
$sheet2->setCellValue("H$grandRow", "=SUM(H$dataStart:H$dataEnd)");

// Formats
$sheet2->getStyle("C$grandRow:F$grandRow")
    ->getNumberFormat()->setFormatCode('#,##0.00');

$sheet2->getStyle("G$grandRow:H$grandRow")
    ->getNumberFormat()->setFormatCode('0.00%');

// Styling
$sheet2->getStyle("A$grandRow:H$grandRow")->getFont()->setBold(true);
$sheet2->getStyle("A$grandRow:H$grandRow")->getBorders()
    ->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

$sheet2->getStyle("A$grandRow:H$grandRow")->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_CENTER)
    ->setVertical(Alignment::VERTICAL_CENTER);

$sheet2->getRowDimension($grandRow)->setRowHeight(30);

}
    

// Output Excel
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="petty_cash_report.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->setIncludeCharts(true);
$writer->save('php://output');
exit;