<?php
session_start();
include 'config.php';

if (!isset($_SESSION['role']) || $_SESSION['role'] != 'admin') {
    exit("Akses ditolak");
}

$nama = $_GET['nama'] ?? '';
$lokasi = $_GET['lokasi'] ?? '';
$tgl_dari = $_GET['tgl_dari'] ?? '';
$tgl_sampai = $_GET['tgl_sampai'] ?? '';

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Laporan_Absensi.xls");

$sql = "
SELECT users.nama, absensi.*
FROM absensi
JOIN users ON absensi.user_id = users.id
WHERE 1=1
";

if (!empty($tgl_dari) && !empty($tgl_sampai)) {
    $sql .= " AND absensi.tanggal BETWEEN '$tgl_dari' AND '$tgl_sampai'";
}

if (!empty($nama)) {
    $sql .= " AND users.nama LIKE '%$nama%'";
}

if (!empty($lokasi)) {
    $sql .= " AND absensi.lokasi='$lokasi'";
}

$sql .= " ORDER BY absensi.tanggal DESC";

$query = mysqli_query($conn, $sql);

$rekapSql = "
SELECT
SUM(CASE WHEN status='Hadir' THEN 1 ELSE 0 END) AS hadir,
SUM(CASE WHEN status='Sakit' THEN 1 ELSE 0 END) AS sakit,
SUM(CASE WHEN status='Izin' THEN 1 ELSE 0 END) AS izin,
SUM(CASE WHEN status='Cuti' THEN 1 ELSE 0 END) AS cuti,
SUM(CASE WHEN status='Alpha' THEN 1 ELSE 0 END) AS alpha
FROM absensi
JOIN users ON absensi.user_id = users.id
WHERE 1=1
";

if (!empty($tgl_dari) && !empty($tgl_sampai)) {
    $rekapSql .= " AND absensi.tanggal BETWEEN '$tgl_dari' AND '$tgl_sampai'";
}

if (!empty($nama)) {
    $rekapSql .= " AND users.nama LIKE '%$nama%'";
}

if (!empty($lokasi)) {
    $rekapSql .= " AND absensi.lokasi='$lokasi'";
}

$rekap = mysqli_fetch_assoc(
    mysqli_query($conn, $rekapSql)
);

$rekapSql = "
SELECT
SUM(CASE WHEN status='Hadir' THEN 1 ELSE 0 END) AS hadir,
SUM(CASE WHEN status='Sakit' THEN 1 ELSE 0 END) AS sakit,
SUM(CASE WHEN status='Izin' THEN 1 ELSE 0 END) AS izin,
SUM(CASE WHEN status='Cuti' THEN 1 ELSE 0 END) AS cuti,
SUM(CASE WHEN status='Alpha' THEN 1 ELSE 0 END) AS alpha
FROM absensi
JOIN users ON absensi.user_id = users.id
WHERE 1=1
";

if (!empty($tgl_dari) && !empty($tgl_sampai)) {
    $rekapSql .= " AND absensi.tanggal BETWEEN '$tgl_dari' AND '$tgl_sampai'";
}

if (!empty($nama)) {
    $rekapSql .= " AND users.nama LIKE '%$nama%'";
}

if (!empty($lokasi)) {
    $rekapSql .= " AND absensi.lokasi='$lokasi'";
}

$rekap = mysqli_fetch_assoc(
    mysqli_query($conn, $rekapSql)
);

echo "
<h3>Laporan Absensi</h3>

<table border='1'>
<tr>
    <th>Hadir</th>
    <th>Sakit</th>
    <th>Izin</th>
    <th>Cuti</th>
    <th>Alpha</th>
</tr>

<tr>
    <td>{$rekap['hadir']}</td>
    <td>{$rekap['sakit']}</td>
    <td>{$rekap['izin']}</td>
    <td>{$rekap['cuti']}</td>
    <td>{$rekap['alpha']}</td>
</tr>

</table>

<br><br>
";

echo "
<table border='1'>

<tr>
    <th>No</th>
    <th>Nama</th>
    <th>Tanggal</th>
    <th>Jam Masuk</th>
    <th>Jam Pulang</th>
    <th>Lokasi</th>
    <th>Status</th>
</tr>
";

$no = 1;

while($row = mysqli_fetch_assoc($query)){

    echo "
    <tr>
        <td>".$no++."</td>
        <td>".$row['nama']."</td>
        <td>".$row['tanggal']."</td>
        <td>".$row['jam_masuk']."</td>
        <td>".$row['jam_pulang']."</td>
        <td>".$row['lokasi']."</td>
        <td>".$row['status']."</td>
    </tr>
    ";
}

echo "</table>";

