<?php

// Enable full error reporting (for debugging)

// error_reporting(E_ALL);

// ini_set('display_errors', 1);

// ini_set('display_startup_errors', 1);


session_start();

require_once 'system/conn.php';

$date = explode('-', date("Y-m-d", strtotime("last day of previous month")));
$month = $date[1];
$year  = $date[0];

// Get month name (uppercase)
$monthNum  = $month;
$dateObj   = DateTime::createFromFormat('!m', $monthNum);
$monthName = strtoupper($dateObj->format('F'));

// CONNECT TO DATABASE


// Fetch branch name safely

$br = '';

if (isset($_SESSION['branch'])) {

    $query = $conn->prepare("SELECT name FROM branch WHERE id = :param");

    $query->bindParam(':param', $_SESSION['branch'], PDO::PARAM_INT);

    $query->execute();

    $result = $query->fetch(PDO::FETCH_ASSOC);

    $br = $result ? $result['name'] : 'UNKNOWN BRANCH';

} else {

    $br = 'UNKNOWN BRANCH';

}



// Load PhpSpreadsheet

require "excel_test/vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;



// CREATE A NEW SPREADSHEET + POPULATE DATA

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();



$spreadsheet->getActiveSheet()->mergeCells('A1:F1');

foreach (range('A', 'F') as $col) {

    $spreadsheet->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);

}



// Header styles

$styleArray = [

    'font' => ['bold' => true],

    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],

    'fill' => [

        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,

        'rotation' => 90,

        'startColor' => ['argb' => 'FFA0A0A0'],

        'endColor' => ['argb' => 'FFFFFFFF'],

    ],

];

$styleArray1 = [

    'font' => ['bold' => true],

    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],

];



$spreadsheet->getActiveSheet()->getStyle('A3:F3')->applyFromArray($styleArray1);

$spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);



$sheet->setTitle('Gross sheet');



// ✅ FIXED SQL — added missing comma before LEFT JOIN

$stmt = $conn->prepare("

    SELECT 

        emp_salary.emp_id, 

        emp_salary.name, 

        emp_salary.designation, 

        emp_salary.gross_pay, 

        emp_salary.tax

    FROM emp_salary

    LEFT JOIN emp_details 

        ON emp_salary.emp_id = emp_details.e_id 

        AND emp_salary.branch = emp_details.branch

    WHERE emp_salary.branch = :branch 

      AND emp_salary.month = :month 

      AND emp_salary.year = :year

    UNION

    SELECT 

        '', 

        'z', 

        'TOTAL', 

        SUM(gross_pay), 

        SUM(tax)

    FROM emp_salary 

    WHERE branch = :branch 

      AND month = :month 

      AND year = :year

    ORDER BY name ASC

");



$stmt->bindParam(':branch', $_SESSION['branch'], PDO::PARAM_INT);

$stmt->bindParam(':month', $month, PDO::PARAM_STR);

$stmt->bindParam(':year', $year, PDO::PARAM_STR);

$stmt->execute();



// HEADERS

$a = 1;

$i = 4;

$sheet->setCellValue('A1', $br . ' CURRENT PROFESSIONAL TAX WAGE SHEET FOR ' . $monthName . ' ' . $year);

$sheet->setCellValue('A3', 'SR NO');

$sheet->setCellValue('B3', 'NAME');

$sheet->setCellValue('C3', 'DESIGNATION');

$sheet->setCellValue('D3', 'EMP ID');

$sheet->setCellValue('E3', 'GROSS');

$sheet->setCellValue('F3', 'TAX');



// DATA ROWS

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    $sheet->setCellValue('A' . $i, $a);

    $sheet->setCellValue('B' . $i, $row['name']);

    $sheet->setCellValue('C' . $i, $row['designation']);

    $sheet->setCellValue('D' . $i, $row['emp_id']);

    $sheet->setCellValue('E' . $i, $row['gross_pay']);

    $sheet->setCellValue('F' . $i, $row['tax']);

    $i++;

    $a++;

}



$spreadsheet->getActiveSheet()->getStyle('A' . $i . ':F' . $i)->applyFromArray($styleArray1);



// OUTPUT

$writer = new Xlsx($spreadsheet);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="Current_Professional_Tax_Sheet.xlsx"');

header('Cache-Control: max-age=0');

header('Expires: Fri, 11 Nov 2011 11:11:11 GMT');

header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');

header('Cache-Control: cache, must-revalidate');

header('Pragma: public');

$writer->save('php://output');

exit;

?>

