<?php

require "../config.php";

$search = $_GET['search'] ?? '';
$search = $conn->real_escape_string($search);

$where = "
masa_berlaku <= DATE_ADD(CURDATE(), INTERVAL 3 MONTH)
";

if($search != ''){

    $where .= " AND (
        nomor_sk LIKE '%$search%'
        OR nama_perusahaan LIKE '%$search%'
        OR sumber_air LIKE '%$search%'
    )";

}

$data = $conn->query("
SELECT *
FROM master_sk
WHERE $where
ORDER BY masa_berlaku ASC
");

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

?>

<table border="1">

<tr style="background:#ddd;">
    <th>No</th>
    <th>Nomor SK</th>
    <th>Nama Perusahaan</th>
    <th>Sumber Air</th>
    <th>Masa Berlaku</th>
    <th>Status</th>
    <th>Sisa Hari</th>
</tr>

<?php

$no = 1;

while($d = $data->fetch_assoc()):

    $sisa_hari = floor(
        (strtotime($d['masa_berlaku']) - time()) / 86400
    );

    if($sisa_hari < 0){
        $status = "Expired";
    }else{
        $status = "Akan Berakhir";
    }

?>

<tr>

    <td><?= $no++ ?></td>

    <td><?= $d['nomor_sk'] ?></td>

    <td><?= $d['nama_perusahaan'] ?></td>

    <td><?= $d['sumber_air'] ?></td>

    <td>
        <?= date('d-m-Y', strtotime($d['masa_berlaku'])) ?>
    </td>

    <td><?= $status ?></td>

    <td>
        <?php
        if($sisa_hari < 0){
            echo "Expired ".abs($sisa_hari)." Hari";
        }else{
            echo $sisa_hari." Hari";
        }
        ?>
    </td>

</tr>

<?php endwhile; ?>

</table>