<?php
require_once "db.php";

/* =====================
   FILTER VALUES
===================== */
$from = $_GET['from_month'] ?? '';
$to   = $_GET['to_month'] ?? '';

$whereSalary  = "";
$whereAdvance = "";
$whereBonus   = "";
$monthsCount  = 1;

if ($from && $to) {

    $whereSalary  = " AND sp.salary_month BETWEEN '$from' AND '$to' ";
    $whereAdvance = " AND DATE_FORMAT(ap.advance_date,'%Y-%m') BETWEEN '$from' AND '$to' ";
    $whereBonus   = " AND DATE_FORMAT(bp.bonus_date,'%Y-%m') BETWEEN '$from' AND '$to' ";

    $start = new DateTime($from.'-01');
    $end   = new DateTime($to.'-01');
    $end->modify('+1 month');
    $diff = $start->diff($end);
    $monthsCount = ($diff->y * 12) + $diff->m;
}

/* =====================
   MAIN QUERY
===================== */
$sql = "
SELECT
    e.employee_id,
    e.employee_name,
    e.phone,
    d.title AS designation,
    e.base_salary,

    IFNULL(SUM(DISTINCT sp.paid_amount),0) AS salary_paid,

    (
        SELECT IFNULL(SUM(ap.amount),0)
        FROM advance_pay ap
        WHERE ap.employee_id = e.employee_id
        $whereAdvance
    ) AS advance_paid,

    (
        SELECT IFNULL(SUM(bp.bonus_amount),0)
        FROM bonus_pay bp
        WHERE bp.employee_id = e.employee_id
        $whereBonus
    ) AS bonus_paid

FROM employees e
LEFT JOIN designation d ON d.designation_id = e.designation_id
LEFT JOIN salary_pay sp ON sp.employee_id = e.employee_id $whereSalary
WHERE e.status='Active'
GROUP BY e.employee_id
ORDER BY e.employee_name ASC
";

$result = $conn->query($sql);
if(!$result){
    die("SQL Error: ".$conn->error);
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Employee Salary Report</title>

<style>
body{
    font-family: Arial, sans-serif;
    font-size: 13px;
}
h2{
    text-align:center;
    margin-bottom:5px;
}
p{
    text-align:center;
    margin-top:0;
}
table{
    width:100%;
    border-collapse:collapse;
}
th,td{
    border:1px solid #000;
    padding:6px;
    text-align:center;
}
thead{
    background:#f0f0f0;
}
tfoot{
    font-weight:bold;
}
.print-content{
    padding: 40px;
}
@media print{
    body{ margin:0; }
}
</style>
</head>

<body onload="window.print()"  class="global-print">
<?php
    include("global/global-print-heading.php");
?>
<div class="print-content">
    <h2>Employee Salary Report</h2>
<?php if($from && $to): ?>
<p>Period: <?= htmlspecialchars($from) ?> to <?= htmlspecialchars($to) ?></p>
<?php endif; ?>

<table>
<thead>
<tr>
    <th>#</th>
    <th>Employee</th>
    <th>Designation</th>
    <th>Phone</th>
    <th>Base Salary</th>
    <th>Salary Paid</th>
    <th>Advance Paid</th>
    <th>Total Paid</th>
    <th>Due</th>
    <th>Bonus</th>
    <th>Total Paid + Bonus</th>
</tr>
</thead>

<tbody>
<?php
$sl=1;
$totalBase=$totalSalary=$totalAdvance=$totalPaid=$totalDue=$totalBonus=$grandTotal=0;

while($row=$result->fetch_assoc()):

    $baseSalary = $row['base_salary'] * $monthsCount;
    $salaryPaid = $row['salary_paid'];
    $advancePaid= $row['advance_paid'];
    $paid       = $salaryPaid + $advancePaid;
    $due        = max(0, $baseSalary - $paid);
    $bonus      = $row['bonus_paid'];
    $finalPaid  = $paid + $bonus;

    $totalBase   += $baseSalary;
    $totalSalary += $salaryPaid;
    $totalAdvance+= $advancePaid;
    $totalPaid   += $paid;
    $totalDue    += $due;
    $totalBonus  += $bonus;
    $grandTotal  += $finalPaid;
?>
<tr>
    <td><?= $sl++ ?></td>
    <td><?= $row['employee_name'] ?></td>
    <td><?= $row['designation'] ?></td>
    <td><?= $row['phone'] ?></td>
    <td><?= number_format($baseSalary,2) ?></td>
    <td><?= number_format($salaryPaid,2) ?></td>
    <td><?= number_format($advancePaid,2) ?></td>
    <td><?= number_format($paid,2) ?></td>
    <td><?= number_format($due,2) ?></td>
    <td><?= number_format($bonus,2) ?></td>
    <td><?= number_format($finalPaid,2) ?></td>
</tr>
<?php endwhile; ?>
</tbody>

<tfoot>
<tr>
    <td colspan="4">TOTAL</td>
    <td><?= number_format($totalBase,2) ?></td>
    <td><?= number_format($totalSalary,2) ?></td>
    <td><?= number_format($totalAdvance,2) ?></td>
    <td><?= number_format($totalPaid,2) ?></td>
    <td><?= number_format($totalDue,2) ?></td>
    <td><?= number_format($totalBonus,2) ?></td>
    <td><?= number_format($grandTotal,2) ?></td>
</tr>
</tfoot>
</table>
</div>
<?php
    include("global/global-print-footer.php");
?>
</body>
</html>
