<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school_monir_final";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch all months
$months = [
    'January', 'February', 'March', 'April', 'May', 'June',
    'July', 'August', 'September', 'October', 'November', 'December'
];

// Fetch all employees
$sql_employees = "SELECT EmployeeId, EmployeeName FROM employee_details where designation='teacher'";
$result_employees = $conn->query($sql_employees);

if ($result_employees->num_rows > 0) {
    echo "<table>";
    echo "<thead>";
    echo "<tr>";
    echo "<th>Employee Name</th>";
    foreach ($months as $month) {
        echo "<th>$month</th>";
    }
    echo "</tr>";
    echo "</thead>";
    echo "<tbody>";

    while ($employee = $result_employees->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $employee['EmployeeName'] . "</td>";

        foreach ($months as $month) {
            // Fetch salary data for the employee for the specific month
            $sql_salary = "SELECT full_salary, paid_salary FROM salaries 
                           WHERE employee_id = {$employee['EmployeeId']} AND month = '$month'";
            $result_salary = $conn->query($sql_salary);

            if ($result_salary->num_rows > 0) {
                $salary_data = $result_salary->fetch_assoc();
                $full_salary = $salary_data['full_salary'];
                $paid_salary = $salary_data['paid_salary'];

                // Check payment status and set color
                if ($paid_salary >= $full_salary) {
                    echo "<td class='fully-paid'>৳" . number_format($paid_salary, 2) . "</td>";
                } else {
                    echo "<td class='half-paid'>৳" . number_format($paid_salary, 2) . "</td>";
                }
            } else {
                // If no data is found for the month
                echo "<td>৳0.00</td>";
            }
        }

        echo "</tr>";
    }

    echo "</tbody>";
    echo "</table>";
} else {
    echo "No employees found.";
}

$conn->close();
?>
