<?php
session_start();
if(empty($_SESSION['branch']) || $_SESSION['branch'] == ''){
    header("Location: index.php");
    die();
}
require_once 'system/conn.php';
require "excel_test/vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

// 1. GET PARAMETERS
$fromyear  = $_GET['fy'] ?? '';
$frommonth = str_pad($_GET['fm'] ?? '', 2, '0', STR_PAD_LEFT);
$toyear    = $_GET['ty'] ?? '';
$tomonth   = str_pad($_GET['tm'] ?? '', 2, '0', STR_PAD_LEFT);
$ename     = $_GET['enm'] ?? '';
$branch    = $_SESSION['branch'];

// Comparison Periods
$startPeriod = $fromyear . $frommonth;
$endPeriod   = $toyear . $tomonth;
$dateRangeStr = "$frommonth/$fromyear to $tomonth/$toyear";

// 2. FETCH HEADER INFO (Employee & Branch)
$stmtEmp = $conn->prepare("SELECT * FROM `emp_details` WHERE e_id = ? AND branch = ? LIMIT 1");
$stmtEmp->execute([$ename, $branch]);
$empDetails = $stmtEmp->fetch(PDO::FETCH_ASSOC);

$companyName = "UTKAL TEAM CARE PVT LTD";
$address     = 'Plot No-777 Saheed Nagar, "Virayatan", Bhubaneswar-751007';
$empInfoStr  = "";
$branchName  = "";

if($empDetails) {
    $stmtBr = $conn->prepare("SELECT name FROM `branch` WHERE id = ?");
    $stmtBr->execute([$branch]);
    $branchName = $stmtBr->fetchColumn();

    $empInfoStr = "Name: " . $empDetails['e_name'] . 
                  " | Designation: " . $empDetails['designation'] . 
                  " | Emp ID: " . $empDetails['e_id'] . 
                  " | UAN: " . $empDetails['uan_no'];
} else {
    die("Employee not found or access denied.");
}

// 3. CREATE SPREADSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Salary History');

// --- STYLING ARRAYS ---
$styleHeader = [
    'font' => ['bold' => true, 'size' => 14],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
];
$styleSubHeader = [
    'font' => ['bold' => true, 'size' => 10],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
];
$styleTableHead = [
    'font' => ['bold' => true, 'color' => ['argb' => 'FFFFFFFF']],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => 'FF4F81BD'],
    ],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
];
$styleData = [
    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
];

// --- WRITE HEADERS ---
// Row 1: Company Name
$sheet->mergeCells('A1:Q1');
$sheet->setCellValue('A1', $companyName);
$sheet->getStyle('A1')->applyFromArray($styleHeader);

// Row 2: Address
$sheet->mergeCells('A2:Q2');
$sheet->setCellValue('A2', $address);
$sheet->getStyle('A2')->applyFromArray($styleSubHeader);

// Row 3: Employee Info & Branch
$sheet->mergeCells('A3:Q3');
$sheet->setCellValue('A3', $empInfoStr . " | Branch: " . $branchName);
$sheet->getStyle('A3')->applyFromArray($styleSubHeader);

// Row 4: Report Date Range
$sheet->mergeCells('A4:Q4');
$sheet->setCellValue('A4', "Report Period: " . $dateRangeStr);
$sheet->getStyle('A4')->applyFromArray($styleSubHeader);

// Row 6: Table Columns
$headers = [
    'A' => 'SR NO',
    'B' => 'MONTH-YEAR',
    'C' => 'PAID DAYS',
    'D' => 'BASIC',
    'E' => 'HRA',
    'F' => 'CONV',
    'G' => 'MED',
    'H' => 'GROSS',
    'I' => 'PF',
    'J' => 'ESIC',
    'K' => 'STAFF ADV',
    'L' => 'PT',
    'M' => 'TOT DED',
    'N' => 'NET PAY',
    'O' => 'OT AMT',
    'P' => 'ADV TAKEN',
    'Q' => 'TOTAL PAYOUT'
];

foreach($headers as $col => $val) {
    $sheet->setCellValue($col . '6', $val);
    $sheet->getColumnDimension($col)->setAutoSize(true);
}
$sheet->getStyle('A6:Q6')->applyFromArray($styleTableHead);

// 4. FETCH DATA (Exact Logic from View Page)
$sql = "
    SELECT 
        'old' as source, month, year, attendance, 
        basic_pay, hra, convyance, medical, gross_pay, 
        pf, esic, advance, tax, deduction, net_pay, ac_no 
    FROM prv_emp_salary 
    WHERE emp_id = ? AND branch = ? 
      AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) BETWEEN ? AND ?

    UNION ALL

    SELECT 
        'current' as source, month, year, attendance, 
        basic_pay, hra, convyance, medical, gross_pay, 
        pf, esic, advance, tax, deduction, net_pay, ac_no 
    FROM emp_salary 
    WHERE emp_id = ? AND branch = ? 
      AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) BETWEEN ? AND ?

    ORDER BY year ASC, CAST(month AS UNSIGNED) ASC
";

$stmt = $conn->prepare($sql);
$params = [$ename, $branch, $startPeriod, $endPeriod, $ename, $branch, $startPeriod, $endPeriod];
$stmt->execute($params);

$rowNum = 7;
$cnt = 1;

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // 1. Fetch OT
    $otTable = ($row['source'] == 'old') ? 'prv_ot_details' : 'ot_details';
    $stmtOT = $conn->prepare("SELECT amount FROM `$otTable` WHERE month = ? AND year = ? AND emp_id = ? AND branch = ?");
    $stmtOT->execute([$row['month'], $row['year'], $ename, $branch]);
    $otAmount = $stmtOT->fetchColumn(); 
    $ot = $otAmount ? $otAmount : 0;

    // 2. Fetch Advance Taken
    $cleanMonth = str_pad($row['month'], 2, '0', STR_PAD_LEFT); 
    $checkDate = $row['year']."-".$cleanMonth."-01";
    
    $stmtAdv = $conn->prepare("SELECT amount FROM `advance` WHERE status=1 AND `date` = ? AND `e_id` = ? AND branch = ?");
    $stmtAdv->execute([$checkDate, $ename, $branch]);
    $advTaken = $stmtAdv->fetchColumn();
    $adv = $advTaken ? $advTaken : 0;

    // 3. Calculation
    $totalPayout = $row['net_pay'] + $ot + $adv;
    $monthName = date('M', mktime(0, 0, 0, (int)$row['month'], 10));
    $monthYear = $monthName . "-" . $row['year'];

    // 4. Write Data
    $sheet->setCellValue('A' . $rowNum, $cnt);
    $sheet->setCellValue('B' . $rowNum, $monthYear);
    $sheet->setCellValue('C' . $rowNum, $row['attendance']);
    $sheet->setCellValue('D' . $rowNum, $row['basic_pay']);
    $sheet->setCellValue('E' . $rowNum, $row['hra']);
    $sheet->setCellValue('F' . $rowNum, $row['convyance']);
    $sheet->setCellValue('G' . $rowNum, $row['medical']);
    $sheet->setCellValue('H' . $rowNum, $row['gross_pay']);
    $sheet->setCellValue('I' . $rowNum, $row['pf']);
    $sheet->setCellValue('J' . $rowNum, $row['esic']);
    $sheet->setCellValue('K' . $rowNum, $row['advance']); // Staff Adv Deducted
    $sheet->setCellValue('L' . $rowNum, $row['tax']);     // PT
    $sheet->setCellValue('M' . $rowNum, $row['deduction']);
    $sheet->setCellValue('N' . $rowNum, $row['net_pay']);
    $sheet->setCellValue('O' . $rowNum, $ot);
    $sheet->setCellValue('P' . $rowNum, $adv);            // Adv Taken
    $sheet->setCellValue('Q' . $rowNum, $totalPayout);

    $rowNum++;
    $cnt++;
}

// Apply borders to data
if ($rowNum > 7) {
    $sheet->getStyle('A7:Q' . ($rowNum - 1))->applyFromArray($styleData);
} else {
    $sheet->setCellValue('A7', 'No Records Found');
    $sheet->mergeCells('A7:Q7');
}

// 5. OUTPUT FILE
$filename = "Employee_Report_" . $ename . "_" . date('Ymd') . ".xlsx";

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

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