<?php
include "auth.php";
checkPermission(['admin','manager']);
require_once "db.php";

$year = $_GET['year'] ?? date('Y');

$sql = "
SELECT 
    e.employee_name,
    e.phone,
    d.title AS designation,
    e.base_salary,

    IFNULL(SUM(DISTINCT sp.paid_amount),0) AS salary_paid,
    IFNULL(SUM(DISTINCT ap.amount),0) AS advance_paid,
    IFNULL(SUM(DISTINCT bp.bonus_amount),0) 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
    AND YEAR(sp.pay_date) = '$year'

LEFT JOIN advance_pay ap 
    ON ap.employee_id = e.employee_id
    AND YEAR(ap.created_at) = '$year'

LEFT JOIN bonus_pay bp 
    ON bp.employee_id = e.employee_id
    AND YEAR(bp.bonus_date) = '$year'

GROUP BY e.employee_id
ORDER BY e.employee_name
";

$res = $conn->query($sql);
if(!$res){ die($conn->error); }
?>
<!DOCTYPE html>
<html>
<head>
<title>Yearly Employee Report</title>
<style>
body{font-family:Arial;font-size:13px}
h2{text-align:center;margin-bottom:5px}
p{text-align:center;margin-top:0}
table{width:100%;border-collapse:collapse;margin-top:10px}
th,td{border:1px solid #000;padding:6px;text-align:center}
th{background:#f0f0f0}
.print-content{
    padding: 40px;
}
@media print{button{display:none}}
</style>
</head>

<body onload="window.print()" class="global-print">
<?php
    include("global/global-print-heading.php");
?>
<div class="print-content">
    <h2>Employee Yearly Report</h2>
<p>Year: <?= $year ?></p>

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

<tbody>
<?php
$i=1;
$tb=$ta=$ts=$td=$tbo=$tp=0;

while($r=$res->fetch_assoc()):
    $yearly_base = $r['base_salary']*12;
    $due = max(0,$yearly_base-$r['advance_paid']-$r['salary_paid']);
    $paid = $r['salary_paid']+$r['advance_paid']+$r['bonus_paid'];

    $tb+=$yearly_base;
    $ta+=$r['advance_paid'];
    $ts+=$r['salary_paid'];
    $td+=$due;
    $tbo+=$r['bonus_paid'];
    $tp+=$paid;
?>
<tr>
<td><?= $i++ ?></td>
<td><?= $r['employee_name'] ?></td>
<td><?= $r['designation'] ?></td>
<td><?= $r['phone'] ?></td>
<td><?= number_format($yearly_base,2) ?></td>
<td><?= number_format($r['advance_paid'],2) ?></td>
<td><?= number_format($r['salary_paid'],2) ?></td>
<td><?= number_format($due,2) ?></td>
<td><?= number_format($r['bonus_paid'],2) ?></td>
<td><?= number_format($paid,2) ?></td>
</tr>
<?php endwhile; ?>
</tbody>

<tfoot>
<tr>
<th colspan="4">TOTAL</th>
<th><?= number_format($tb,2) ?></th>
<th><?= number_format($ta,2) ?></th>
<th><?= number_format($ts,2) ?></th>
<th><?= number_format($td,2) ?></th>
<th><?= number_format($tbo,2) ?></th>
<th><?= number_format($tp,2) ?></th>
</tr>
</tfoot>
</table>
</div>
<?php
    include("global/global-print-footer.php");
?>
</body>
</html>
