<?php
ob_start();
include "../library/config.php"; 

// Mengambil parameter dari URL
$kls = isset($_GET['kelas']) ? mysqli_real_escape_string($con, $_GET['kelas']) : '';
$jrs = isset($_GET['jurusan']) ? mysqli_real_escape_string($con, $_GET['jurusan']) : '';
$tgl_awal  = isset($_GET['tgl_awal']) ? $_GET['tgl_awal'] : '';
$tgl_akhir = isset($_GET['tgl_akhir']) ? $_GET['tgl_akhir'] : '';

// Setting Nama File Excel
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Laporan_BK_{$kls}_{$jrs}.xls");
header("Pragma: no-cache");
header("Expires: 0");
?>

<table border="1">
    <tr>
        <th colspan="8" style="font-size: 18px; font-weight: bold;">LAPORAN DETAIL PELANGGARAN SISWA MINGGUAN</th>
    </tr>
    <tr>
        <th colspan="8" style="font-size: 14px;">SMK BP SUBULUL HUDA</th>
    </tr>
    <tr>
        <th colspan="8">KELAS: <?= $kls ?> <?= $jrs ?> | PERIODE: <?= date('d/m/Y', strtotime($tgl_awal)) ?> s/d <?= date('d/m/Y', strtotime($tgl_akhir)) ?></th>
    </tr>
    <tr><td colspan="8" style="border:none;"></td></tr>
    
    <tr style="background-color: #3c8dbc; color: white; font-weight: bold;">
        <th width="50">No</th>
        <th width="150">NIS</th>
        <th width="300">Nama Lengkap</th>
        <th width="50">L/P</th>
        <th width="120">Poin Baru (Minggu Ini)</th>
        <th width="120">Total Poin (Akumulasi)</th>
        <th width="350">Rincian Pelanggaran Periode Ini</th>
        <th width="200">Status / Tindakan BK</th>
    </tr>

    <?php 
    $no = 1;
    // Mengambil data siswa diurutkan dari poin tertinggi
    $query = mysqli_query($con, "SELECT * FROM siswa WHERE kelas='$kls' AND jurusan='$jrs' ORDER BY poin_sis DESC");
    
    while($d = mysqli_fetch_array($query)) : 
        $nis = $d['nis'];
        
        // Ambil data poin hanya untuk rentang tanggal yang dipilih
        $q_minggu = mysqli_query($con, "SELECT SUM(poin_plg) as jml, GROUP_CONCAT(kode_pl SEPARATOR ', ') as rincian 
                                       FROM riwayat_pelanggaran 
                                       WHERE nis='$nis' AND (tanggal_riw BETWEEN '$tgl_awal' AND '$tgl_akhir')");
        $res_m = mysqli_fetch_assoc($q_minggu);
        $poin_minggu = $res_m['jml'] ? $res_m['jml'] : 0;

        // Logika Status Berdasarkan Total Poin Akumulasi
        $status = "Siswa Teladan";
        if($d['poin_sis'] >= 100) $status = "Panggilan Ortu / SP 3";
        elseif($d['poin_sis'] >= 75) $status = "Peringatan Keras / SP 2";
        elseif($d['poin_sis'] >= 50) $status = "Pembinaan Khusus / SP 1";
        elseif($d['poin_sis'] > 0) $status = "Teguran Lisan";
    ?>
    <tr>
        <td align="center"><?= $no++ ?></td>
        <td align="center">'<?= $d['nis'] ?></td> <td><?= strtoupper($d['nama_siswa']) ?></td>
        <td align="center"><?= $d['gender'] ?></td>
        <td align="center" style="background-color: #ffffcc;"><b>+ <?= $poin_minggu ?></b></td>
        <td align="center" style="color: red; font-weight: bold;"><?= $d['poin_sis'] ?></td>
        <td><?= $res_m['rincian'] ? $res_m['rincian'] : '-' ?></td>
        <td><?= $status ?></td>
    </tr>
    <?php endwhile; ?>
    
    <tr><td colspan="8" style="border:none;"></td></tr>
    <tr>
        <td colspan="5" style="border:none;"></td>
        <td colspan="3" align="center" style="border:none;">
            <br>
            Madiun, <?= date('d F Y') ?><br>
            Guru BK / Kesiswaan,
            <br><br><br><br>
            <b>_________________________</b><br>
            NIP. .........................
        </td>
    </tr>
</table>
<?php ob_end_flush(); ?>