<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <title>Leger Nilai Kelas <?= $_GET['k'] ?></title>
    <style type="text/css">
        body {
            font-family: Arial, Helvetica, sans-serif;
            font-size: 10pt;
        }
    </style>
</head>

<body>

    <?php
    $semester = substr($_GET['ktp'], -1);
    $tahun_awal = substr($_GET['ktp'], 0, 4);
    $tahun_akhir = substr($_GET['ktp'], 4, 4);
    $format_tahun = $tahun_awal . '/' . $tahun_akhir;
    $tahun_pelajaran = $format_tahun;
    ?>

    <?php
    $kode_tapel = $_GET['ktp'];
    $kelas = $_GET['k'];
    $database_file = '../../db/rapor.db';
    $pdo = new PDO("sqlite:$database_file");

    if (isset($_GET['act'])) { ?>
        <div style="page-break-before: auto">
            <h3>Data Kumpulan Nilai Kelas <?= $_GET['k'] ?> Tahun Pelajaran <?= $format_tahun ?> Semester <?= $semester ?></h3>
            <?php
            echo '<table border="1" cellspacing="0" cellpadding="3" width="100%" style="zoom:50%">
            <thead>
            <tr>
            <th>NO</th>
            <th>TAPEL</th>
            <th>NAMA</th>
            <th>KELAS</th>
            <th>NIS</th>
            <th>PABP</th>
            <th>PP</th>
            <th>BIND</th>
            <th>MAT</th>
            <th>IPA</th>
            <th>IPS</th>
            <th>BING</th>
            <th>PJOK</th>
            <th>INFTK</th>
            <th>SP</th>
            <th>BJ</th>
            <th style="background-color:yellow;">JML</th>
            <th style="background-color:yellow;">RATA-RATA</th> 
            </tr>
            </thead>
            <tbody>';

            $pd_query = "SELECT
            peserta_didik.kode_tapel,  
            peserta_didik.kelas, 
            peserta_didik.nis, 
            peserta_didik.nisn, 
            peserta_didik.nama
            FROM
            peserta_didik 
            WHERE kelas = :kelas AND kode_tapel = :kode_tapel";

            $pd_data = $pdo->prepare($pd_query);
            $pd_data->bindParam(':kelas', $kelas, PDO::PARAM_STR);
            $pd_data->bindParam(':kode_tapel', $kode_tapel, PDO::PARAM_STR);
            $pd_data->execute();

            $no = 1;
            $siswa_data = array();

            while ($pd_list = $pd_data->fetch()) {
                $kode_tapel = $pd_list['kode_tapel'];
                $nis = $pd_list['nis'];

                echo "<tr>
                <td>$no</td>
                <td>$tahun_pelajaran</td>
                <td>{$pd_list['kelas']}</td>
                <td>$nis</td>
                <td nowrap>{$pd_list['nama']}</td>";

                $nr_query = "SELECT 
                nilai_sumatif.kode_mapel,
                nilai_sumatif.nr2
                FROM
                nilai_sumatif
                WHERE nis = :nis AND kode_tapel = :kode_tapel AND kode_mapel IN ('PABP', 'PP', 'BIND', 'MAT', 'IPA', 'IPS', 'BING', 'PJOK', 'INFTK', 'SP', 'BJ')
                ORDER BY
                CASE
                WHEN nilai_sumatif.kode_mapel = 'PABP' THEN 1
                WHEN nilai_sumatif.kode_mapel = 'PP' THEN 2
                WHEN nilai_sumatif.kode_mapel = 'BIND' THEN 3
                WHEN nilai_sumatif.kode_mapel = 'MAT' THEN 4
                WHEN nilai_sumatif.kode_mapel = 'IPA' THEN 5
                WHEN nilai_sumatif.kode_mapel = 'IPS' THEN 6
                WHEN nilai_sumatif.kode_mapel = 'BING' THEN 7
                WHEN nilai_sumatif.kode_mapel = 'PJOK' THEN 8
                WHEN nilai_sumatif.kode_mapel = 'INFTK' THEN 9
                WHEN nilai_sumatif.kode_mapel = 'SP' THEN 10
                WHEN nilai_sumatif.kode_mapel = 'BJ' THEN 11
                ELSE 12
                END";

                $nr_data = $pdo->prepare($nr_query);

                $nr_data->bindParam(':nis', $nis, PDO::PARAM_STR);
                $nr_data->bindParam(':kode_tapel', $kode_tapel, PDO::PARAM_STR);

                $nr_data->execute();


                $jml = 0;
                $jumlah_mapel = 0;

                while ($nr_item = $nr_data->fetch()) {
                    echo $nmapel = $nr_item['kode_mapel'];
                    $nilai = $nr_item['nr2'];

                    echo "<td>" . number_format($nilai, 0) . "</td>";

                    $jml += $nilai;
                    $jumlah_mapel++;

                    if (!isset($siswa_data[$nis])) {
                        $siswa_data[$nis] = array('total' => 0, 'jumlah_mapel' => 0);
                    }
                    $siswa_data[$nis]['total'] += $nilai;
                    $siswa_data[$nis]['jumlah_mapel']++;
                }

                $rata_rata = $jumlah_mapel > 0 ? $jml / $jumlah_mapel : 0;

                echo "<td style='background-color:yellow;'>" . number_format($jml, 0) . "</td>";

                echo "<td style='background-color:yellow;'>" . number_format($rata_rata, 2) . "</td>";

                echo "</tr>";

                $no++;
            }

            echo '</tbody>
            </table>';
            ?>



        <?php  } ?>
</body>

</html>