<?php
include '../../config/conn.php';

$date = date('Y-m-d');
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Laporan SPP RT-Sore $date.xls");



?>
<!-- page content -->


<?php if (!isset($_GET['periode'])) {
    $bln = date('n');
    $thn = date('Y');
} elseif ($_GET['periode'] == 'y') {
    $bln = $_GET['bulan'];
    $thn = $_GET['tahun'];
} elseif ($_GET['periode'] == 'x') {
    $bln = $_GET['bln'];
    $thn = $_GET['thn'];
}

?>











<table id="datatable" border="1" class="table table-bordered">
    <thead>
        <tr>
            <td colspan="10" align="center" style="font-size: x-large;">LAPORAN SPP RT-Sore <?php echo "Bulan $bln Tahun $thn"; ?></td>
        </tr>
    </thead>
    <thead>
        <tr>
            <!--<th>Tanggal</th>-->
            <!--<th>id transaksi</th> -->
            <th width="15px">NO.</th>
            <th>Id Santri</th>
            <th>Nama Santri</th>
            <th>Kelas</th>
            <th>Tanggal masuk</th>
            <th>Dibayar</th>
            <th>Total Bayar</th>
            <th>Pembayaran</th>
            <th>Sisa</th>
            <th width="100">Status</th>

        </tr>
    </thead>
    <tbody>
        <?php
        $no = 0;
        $count = 1;
        $total_rekap = 0;

        $hari_ini = "$thn-$bln-01";
        $tgl_pertama = date('Y-m-01', strtotime($hari_ini));
        $tgl = date('Y-m-t', strtotime($hari_ini));

        $n = 0;
        $keri1 = mysqli_query($conn, "SELECT * FROM jenis_rt WHERE kategori = '2'");

        //sorting data per Kelas
        if (!isset($_GET['kelas']) or $_GET['kelas'] == 0) {
            $kelas = "";
        } else {
            $kelas = "AND jenis_rt=$_GET[kelas]";
        }

        //sorting data per angkatan
        if (!isset($_GET['angkatan']) or $_GET['angkatan'] == 0) {
            $angkatan = "";
        } else {
            $angkatan = "AND YEAR(tanggal_masuk)=$_GET[angkatan]";
        }

        while ($jn = mysqli_fetch_array($keri1)) {
            $n++;
            if ($n > 1) {
                $rt = $rt . " OR jenis_rt = '$jn[id]'";
            }
            if ($n == 1) {
                $rt = "jenis_rt = '$jn[id]'";
            }
        }


        if ($_GET['tampildata'] == 1) {
            $query = mysqli_query($conn, "SELECT * FROM santri_rt WHERE tanggal_masuk<='$tgl' $angkatan $kelas 
                            AND jenis_rt IN(SELECT id FROM jenis_rt WHERE kategori = '2') AND id_santri IN(SELECT id_santri FROM status_lunas WHERE id_jenis='12' AND spp_bulan='$bln' AND spp_tahun='$thn') order by nama ASC ");
        } elseif ($_GET['tampildata'] == 2) {
            $query = mysqli_query($conn, "SELECT * FROM santri_rt WHERE tanggal_masuk<='$tgl' $angkatan $kelas  
                            AND jenis_rt IN(SELECT id FROM jenis_rt WHERE kategori = '2') AND id_santri IN(SELECT id_santri FROM transaksi WHERE id_jenis='12' AND spp_bulan='$bln' AND spp_tahun='$thn')
                            AND id_santri NOT IN(SELECT id_santri FROM status_lunas WHERE id_jenis='11' AND spp_bulan='$bln' AND spp_tahun='$thn')  order by nama ASC ");
        } elseif ($_GET['tampildata'] == 3) {
            $query = mysqli_query($conn, "SELECT * FROM santri_rt WHERE tanggal_masuk<='$tgl' $angkatan $kelas 
                            AND jenis_rt IN(SELECT id FROM jenis_rt WHERE kategori = '2') AND id_santri NOT IN(SELECT id_santri FROM transaksi WHERE id_jenis='12' AND spp_bulan='$bln' AND spp_tahun='$thn')
                            AND id_santri NOT IN(SELECT id_santri FROM status_lunas WHERE id_jenis='12' AND spp_bulan='$bln' AND spp_tahun='$thn') order by nama ASC ");
        } else {
            $query = mysqli_query($conn, "SELECT * FROM santri_rt WHERE tanggal_masuk<='$tgl' $angkatan $kelas AND jenis_rt IN(SELECT id FROM jenis_rt WHERE kategori = '2') order by nama ASC ");
        }

        $ro = 0;




        while ($row = mysqli_fetch_array($query)) {

            $no++;
        ?>

            <tr style="background: <?php if ($no % 2 == 1) { ?>
                          #ececec;
                          <?php } else { ?>
                            white;
                            <?php } ?>">

                <!--<td><?php //echo $row['id_transaksi'];
                        ?></td>-->
                <td><?php echo $no; ?></td>
                <td><?php echo $row['id_santri']; ?></td>
                <td><?php echo $row['nama']; ?></td>
                <td><?php
                    $query2  = mysqli_query($conn, "SELECT * FROM jenis_rt WHERE id='$row[jenis_rt]' ORDER BY id");
                    $st = mysqli_fetch_array($query2);
                    echo $st['nama_jenis'];
                    ?>
                </td>
                <td><?php echo $row['tanggal_masuk'];  ?></td>
                <td><?php
                    $keri = mysqli_query($conn, "SELECT * FROM transaksi WHERE id_santri='$row[id_santri]' AND id_jenis='12' AND spp_bulan='$bln' AND spp_tahun='$thn' order by tanggal ASC ");
                    while ($ri = mysqli_fetch_array($keri)) {
                        $tgl = date('d/m/y', strtotime($ri['tanggal']));
                        echo "[" . $tgl . "~" . $ri['debit'] . "]<br>";
                    }
                    ?></td>

                <td><?php
                    $total = mysqli_query($conn, "SELECT sum(debit) AS total_bayar FROM transaksi WHERE id_santri='$row[id_santri]' AND id_jenis='12' AND spp_bulan='$bln' AND spp_tahun='$thn' order by tanggal ASC ");
                    $total1 = mysqli_fetch_array($total);
                    echo $total1['total_bayar'];
                    ?></td>

                <td><?php echo $row['biaya_spp'];  ?></td>
                <td><?php $sisa = $row['biaya_spp'] - $total1['total_bayar'];
                    echo $sisa;  ?></td>

                <td>
                    <?php
                    $tet1 = mysqli_query($conn, "SELECT * FROM status_lunas WHERE id_santri='$row[id_santri]' AND id_jenis='12' AND spp_bulan='$bln' AND spp_tahun='$thn' ");
                    $tet = mysqli_fetch_array($tet1);
                    if (isset($tet['id_santri'])) {
                        echo "<font color=#0082da><b>lunas</b></font>";
                    } else {
                        echo "<font color=#f01519><b>Belum Lunas</b></font>";
                    }

                    ?></td>





                <?php
                $count++

                ?>

            </tr>

        <?php } ?>





    </tbody>
</table>
<br><br><br><br>
Data <?php echo date('d-m-Y') . "  " . date('H:i:s'); ?>