<?php
if (session_status() === PHP_SESSION_NONE) { session_start(); }
require_once('../koneksi.php');

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

$id_guru = mysqli_fetch_array(mysqli_query($koneksi, "SELECT id_guru FROM users WHERE id_user = '{$_SESSION['id_user']}'"))['id_guru'];
$wali = mysqli_fetch_array(mysqli_query($koneksi, "SELECT id_rombel, kelas FROM rombel WHERE wali_kelas = '$id_guru'"));
$id_rombel = $wali['id_rombel'];

$mapel = mysqli_fetch_all(mysqli_query($koneksi, "SELECT * FROM master_mapel ORDER BY kelompok ASC, urutan ASC"), MYSQLI_ASSOC);
$siswa = mysqli_query($koneksi, "SELECT s.id_siswa, s.nama, ar.keputusan_akhir FROM master_siswa s JOIN anggota_rombel ar ON s.id_siswa = ar.id_siswa WHERE ar.id_rombel = '$id_rombel'");
?>

<table border="1">
    <thead>
        <tr><th>No</th><th>Nama Siswa</th><?php foreach($mapel as $m) echo "<th>{$m['nama_mapel']}</th>"; ?><th>Status</th></tr>
    </thead>
    <tbody>
        <?php $n=1; while($s = mysqli_fetch_array($siswa)) {
            echo "<tr><td>{$n}</td><td>{$s['nama']}</td>";
            foreach($mapel as $m) {
                $r = mysqli_fetch_array(mysqli_query($koneksi, "SELECT AVG(nilai_rapor) as r FROM nilai_siswa ns JOIN tugas_guru tg ON ns.id_tugas=tg.id_tugas WHERE ns.id_siswa='{$s['id_siswa']}' AND tg.id_mapel='{$m['id_mapel']}'"));
                echo "<td>".($r['r'] ? number_format($r['r'], 0) : "-")."</td>";
            }
            echo "<td>{$s['keputusan_akhir']}</td></tr>";
            $n++;
        } ?>
    </tbody>
</table>