<?php
include '../../config/conn.php';
$date = date('Y-m-d');
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Laporan DAM $date.xls");
?>
<table id="datatable" class="table table-bordered">
    <thead>
        <tr>
            <td colspan="10" align="center" style="font-size: x-large;">LAPORAN DANA AWAL MASUK <?php echo $date; ?></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>Jenis Kelamin</th>
            <th>Status Santri</th>
            <th>Tanggal masuk</th>
            <th>Pembayaran</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;

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

        //sorting data per status lunas
        if ($_GET['tampildata'] == 1) {
            //data lunas
            $query = mysqli_query($conn, "SELECT * FROM santri WHERE id_santri IN(SELECT id_santri from status_lunas WHERE id_jenis='14') $angkatan order by nama ASC ");
        } elseif ($_GET['tampildata'] == 2) {
            //bayar cicil
            $query = mysqli_query($conn, "SELECT * FROM santri WHERE id_santri IN(SELECT id_santri from transaksi WHERE id_jenis='14') AND id_santri NOT IN (SELECT id_santri from status_lunas WHERE id_jenis='14') $angkatan order by nama ASC ");
        } elseif ($_GET['tampildata'] == 3) {
            //belum bayar                 
            $query = mysqli_query($conn, "SELECT * FROM santri WHERE id_santri NOT IN(SELECT id_santri from status_lunas) AND id_santri NOT IN (select id_santri from transaksi WHERE id_jenis='14') $angkatan order by nama ASC ");
        } else {
            //semua data
            $query = mysqli_query($conn, "SELECT * FROM santri $angkatanall 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 if($row['jenkel']=='l'){
                echo "Laki-laki";
                                }else{
                echo "Perempuan";
                }  ?></td>
                <td><?php
                    $query2  = mysqli_query($conn, "SELECT * FROM jenis_status WHERE id_status='$row[statuus]' ORDER BY id_status");
                    $st = mysqli_fetch_array($query2);
                    echo $st['nama_status'];
                    ?>
                </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='14' order by tanggal ASC ");
                    while ($ri = mysqli_fetch_array($keri)) {
                        $tgl = date('d/m/y', strtotime($ri['tanggal']));
                        echo "[" . $tgl . "~" . $ri['debit'] . "]// ";
                    }
                    ?></td>

                <td><?php
                    $total = mysqli_query($conn, "SELECT sum(debit) AS total_bayar FROM transaksi WHERE id_santri='$row[id_santri]' AND id_jenis='14'order by tanggal ASC ");
                    $total1 = mysqli_fetch_array($total);
                    echo $total1['total_bayar'];
                    ?></td>
                <td><?php echo $row['biaya_dam'];  ?></td>
                <td><?php $sisa = $row['biaya_dam'] - $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='14'");
                    $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'); ?>