<?php
require "../config.php";

/* ================= FILTER ================= */
$where = "WHERE LOWER(status)='selesai'"; // atau 'Selesai' sesuai sistem kamu

if(isset($_GET['search']) && $_GET['search'] != ""){
    $s = $conn->real_escape_string($_GET['search']);
    $where .= " AND nama LIKE '%$s%'";
}

/* ================= QUERY ================= */
$q = $conn->query("SELECT * FROM pengajuan $where ORDER BY id DESC");

/* ================= HEADER EXCEL ================= */
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Data_Surat_Selesai.xls");

?>

<table border="1">
<tr style="background:#ddd; font-weight:bold; text-align:center;">
<th>No</th>
<th>Kode Tiket</th>
<th>Nama</th>
<th>No HP</th>
<th>Email</th>
<th>Instansi</th>
<th>Pekerjaan</th>
<th>Alamat</th>
<th>Kota</th>
<th>Jenis Kelamin</th>
<th>NIK</th>
<th>No Surat Permohonan</th>
<th>Tujuan</th>
<th>Tanggal Permohonan</th>
<th>Status</th>
<th>Tanggal Masuk</th>
<th>Disposisi</th>
</tr>

<?php 
$no = 1;

if($q && $q->num_rows > 0){

while($d = $q->fetch_assoc()){ 
?>
<tr>
<td><?= $no++ ?></td>
<td><?= htmlspecialchars($d['kode_tiket']) ?></td>
<td><?= htmlspecialchars($d['nama']) ?></td>
<td><?= htmlspecialchars($d['no_hp']) ?></td>
<td><?= htmlspecialchars($d['email']) ?></td>
<td><?= htmlspecialchars($d['instansi']) ?></td>
<td><?= htmlspecialchars($d['pekerjaan']) ?></td>
<td><?= htmlspecialchars($d['alamat']) ?></td>
<td><?= htmlspecialchars($d['kota']) ?></td>
<td><?= htmlspecialchars($d['jenis_kelamin']) ?></td>
<td style="mso-number-format:'\@';">
<?= htmlspecialchars($d['nik']) ?>
</td>
<td><?= htmlspecialchars($d['no_surat_permohonan']) ?></td>
<td><?= htmlspecialchars($d['tujuan']) ?></td>
<td><?= htmlspecialchars($d['tanggal_permohonan']) ?></td>
<td><?= htmlspecialchars($d['status']) ?></td>
<td><?= htmlspecialchars($d['tanggal_masuk']) ?></td>
<td><?= htmlspecialchars($d['disposisi']) ?></td>
</tr>
<?php 
}

}else{
    echo "<tr><td colspan='17'>Data kosong</td></tr>";
}
?>

</table>