leaveplanreport.php

<style>
    .scroll-table {
        overflow: auto;
        max-height: 600px;   /* adjust depending on your screen */
        max-width: 100%;
        border: 1px solid #aaa;
    }

    table {
        border-collapse: collapse;
        white-space: nowrap;   /* keeps columns in one line */
    }

    th, td {
        padding: 4px;
        border: 1px solid #999;
    }

    /* FIX THE TOP HEADER (dates row) */
    thead th {
        position: sticky;
        top: 0;
        /*background: #f3f3f3;*/
        z-index: 5;
    }

    /* FIX FIRST COLUMN (Program) */
    .col-program {
        position: sticky;
        left: 0;
        /*background: #ffffff;*/
        z-index: 6;
        font-weight: bold;
    }

    /* FIX SECOND COLUMN (Employee name) */
    .col-employee {
        position: sticky;
        left: 140px;  /* same width as program column */
        /*background: #ffffff;*/
        z-index: 6;
        font-weight: bold;
    }
</style>



<?php
include "home.php";
include 'connect.php';

// Get all leave plans for the year grouped by employee
$currentYear = date("Y");

$sql = "
    SELECT 
        e.userid, 
        CONCAT(e.lastname,' ',e.firstname) AS name,
        e.program,
        l.leave_date
    FROM users e
    LEFT JOIN hr_employee_leave_plan l 
        ON e.userid = l.employee_id 
        AND YEAR(l.leave_date) = $currentYear
    WHERE e.active = 'YES'
    ORDER BY e.program, e.department, e.lastname, e.firstname
";

$result = $conn->query($sql);

// Build array: employee -> program + leave dates
$employees = [];

while ($row = $result->fetch_assoc()) {
    $emp = $row['name'];

    if (!isset($employees[$emp])) {
        $employees[$emp] = [
            "program" => $row['program'],
            "dates" => []
        ];
    }

    if (!empty($row['leave_date'])) {
        $employees[$emp]["dates"][$row['leave_date']] = 1;
    }
}

// Generate list of all days in the year
$days = [];
$start = new DateTime("$currentYear-01-01");
$end = new DateTime("$currentYear-12-31");

while ($start <= $end) {
    $days[] = $start->format("Y-m-d");
    $start->modify('+1 day');
}
echo "<h2>All Employee Leave Plans ($currentYear)</h2>";

echo "<div class='scroll-table'>";

echo "<table>";
echo "<thead><tr>
        <th class='col-program'>Program</th>
        <th class='col-employee'>Employee</th>";

foreach ($days as $d) {
    echo "<th>$d</th>";
}

echo "<th>Total Planned</th></tr></thead>";
echo "<tbody>";

foreach ($employees as $emp => $data) {
    $program = $data["program"];
    $leaveDates = $data["dates"];
    $totalPlanned = 0;

    echo "<tr>";
    echo "<td class='col-program'>$program</td>";
    echo "<td class='col-employee'>$emp</td>";

    foreach ($days as $d) {
        if (isset($leaveDates[$d])) {
            echo "<td bgcolor='orange'>1</td>";
            $totalPlanned++;
        } else {
            echo "<td></td>";
        }
    }

    echo "<td>$totalPlanned</td>";
    echo "</tr>";
}

echo "</tbody></table></div>";

?>