<?php
include '../../config/conn.php';
$date = date('Y-m-d');
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Laporan DONATUR $date.xls");

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

?>
<table border="1">
    <thead>
        <tr>
            <td colspan="8" align="center" style="font-size: x-large;">LAPORAN DONATUR <?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 Donatur</th>
            <th>Nama Donatur</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));




        //sorting data per jenis status lunas
        if ($_GET['tampildata'] == 1) {
            $query = mysqli_query($conn, "SELECT * FROM donatur WHERE id_donatur IN(SELECT id_donatur FROM status_lunas_donatur WHERE bulan='$bln' AND tahun='$thn') order by nama_donatur ASC ");
        } elseif ($_GET['tampildata'] == 2) {
            $query = mysqli_query($conn, "SELECT * FROM donatur WHERE id_donatur IN(SELECT id_donatur FROM transaksi WHERE spp_bulan='$bln' AND spp_tahun='$thn')
                            AND id_donatur NOT IN(SELECT id_donatur FROM status_lunas_donatur WHERE bulan='$bln' AND tahun='$thn') order by nama_donatur ASC ");
        } elseif ($_GET['tampildata'] == 3) {
            $query = mysqli_query($conn, "SELECT * FROM donatur WHERE id_donatur NOT IN(SELECT id_donatur FROM transaksi WHERE spp_bulan='$bln' AND spp_tahun='$thn')
                            AND id_donatur NOT IN(SELECT id_donatur FROM status_lunas_donatur WHERE bulan='$bln' AND tahun='$thn') order by nama_donatur ASC ");
        } else {
            $query = mysqli_query($conn, "SELECT * FROM donatur order by nama_donatur 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_donatur']; ?></td>
                <td><?php echo $row['nama_donatur']; ?></td>
                <td><?php
                    $keri = mysqli_query($conn, "SELECT * FROM transaksi WHERE id_donatur='$row[id_donatur]' AND id_jenis='10' 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_donatur='$row[id_donatur]' AND id_jenis='10' 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_donasi'];  ?></td>
                <td><?php $sisa = $row['biaya_donasi'] - $total1['total_bayar'];
                    echo $sisa;  ?></td>
                <td><?php
                    $tet1 = mysqli_query($conn, "SELECT * FROM status_lunas_donatur WHERE id_donatur='$row[id_donatur]' AND bulan='$bln' AND tahun='$thn' ");
                    $tet = mysqli_fetch_array($tet1);
                    if (isset($tet['id_donatur'])) {
                        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'); ?>