<?php

include '../../config/conn.php';
$date = date('Y-m-d');

header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Laporan tagihan  ($date).xls");
$query = mysqli_query($conn, "SELECT * FROM santri WHERE id_santri='$_GET[id]'");
  $r = mysqli_fetch_array($query);
?>


<table border="1">
<tr>
<th colspan="2" align="right">Nama/Id : </th>
<th colspan="4" align="left"><?php echo $r['nama']."(".$r['id_santri'].")"; ?></th>
</tr>
<tr>
<th colspan="2" align="right">Tgl Masuk : </th>
<th colspan="4" align="left"><?php echo $r['tanggal_masuk']; ?></th>
</tr>
                   <tr>
                   <th colspan="2" align="right">Status : </th>
                   <th colspan="4" align="left"><?php $query2  = "SELECT * FROM jenis_status WHERE id_status='$r[statuus]'";
                    $tampil2 = mysqli_query($conn, $query2);
                   $w = mysqli_fetch_array($tampil2); 
                   echo $w['nama_status']?></th></tr>
                    <thead>
                        <tr>
                            <th width="10px">No</th>
                            <th width="110px">Jenis</th>
                            <th>total</th>
                            <th>Pembayaran</th>
                            <th>Kekurangan</th>
                            <th>Status</th>

                        </tr>
                    </thead>
                    <!-- dam -->
                    <tbody style="background-color:lightgoldenrodyellow;">
                        
                     

<?php

//sort tagihan

$thsantri = date('Y', strtotime($r['tanggal_masuk']));
$blnsantri = date('n', strtotime($r['tanggal_masuk']));
$thini = date('Y');

$t1 = $thsantri;
$t2 = date('Y');
$n = 0;


for ($t2; $t2 >= $t1; $t2--) {
    if ($thsantri == date('Y')) {
        $b2 = date('n');
        $b1 = $blnsantri+1;
    } else {
        if ($t2 > $t1 && $t2 != $thini) {
            $b2 = 12;
            $b1 = 1;
        } elseif ($t2 == $thini) {
            $b2 = date('n');
            $b1 = 1;
        } elseif ($t2 == $t1) {
            $b2 = 12;
            $b1 = $blnsantri+1;
        }
    }

    for ($b2; $b2 >= $b1; $b2--) {
        $n++;
?>
        <!-- spp -->
        <tr>
            <td><?php echo $n; ?></td>
            <td>SPP <?php echo namabulan($b2) ." ". $t2; ?></td>
            <td><?php 
             $tot = 0;
             $keri = mysqli_query($conn, "SELECT * FROM transaksi WHERE id_santri='$_GET[id]' AND id_jenis='5' AND spp_bulan=$b2 AND spp_tahun=$t2 order by tanggal DESC ");
             while ($ri = mysqli_fetch_array($keri)) {
                   $tot = $tot + $ri['debit'];
             }
             $sisa = $r['biaya_spp'] - $tot;
            echo $tot; ?></td>
            <td><?php echo $r['biaya_spp']; ?></td>
            <td><?php echo $sisa; ?></td>
            <td>
                <?php 

                if ($sisa < 0 OR $sisa == 0) {
                    echo "<font color=#0082da><b>lunas</b></font>";
                } else {
                    echo "<font color=#f01519><b>Belum Lunas</b></font>";
                }


                ?>
            </td>
        </tr>
       

<?php

    }
    // $b2 = 12;
}

?>
 
                        <?php

$th1 = date('Y', strtotime($r['tanggal_masuk']));
$th2 = date('Y');


for ($th2; $th2 >= $th1; $th2--) {
    $n++;
?>
    <!-- spp -->

    <tr>
        <td><?php echo $n; ?></td>
        <td>DUT <?php echo $th2; ?></td>
        <td><?php
         $tot = 0;
         $dut1 = mysqli_query($conn, "SELECT * FROM transaksi WHERE id_santri='$_GET[id]' AND id_jenis='17' AND dut_tahun=$th2 order by tanggal DESC ");
         while ($dut = mysqli_fetch_array($dut1)) {
             $tot = $tot + $dut['debit'];
         }
         $sisa = $r['biaya_dut'] - $tot;
        echo $tot; ?></td>
        <td><?php echo $r['biaya_dut']; ?></td>
        <td><?php echo $sisa; ?></td>
        <td>
           

<?php 

if ($sisa < 0 OR $sisa == 0) {
echo "<font color=#0082da><b>lunas</b></font>";
} else {
echo "<font color=#f01519><b>Belum Lunas</b></font>";
}


?>
        </td>
    </tr>

<?php
}

?>
<tr>
                            <td><?php $n++; echo $n; ?></td>
                            <td>DAM</td>
                            <td><?php

                             $tot = 0;
                             $dam1 = mysqli_query($conn, "SELECT * FROM transaksi WHERE id_santri=$r[id_santri] AND id_jenis='14' order by tanggal DESC ");
                             while ($dam = mysqli_fetch_array($dam1)) {
                                 $tot = $tot + $dam['debit'];
                             }
                             $sisa = $r['biaya_dam'] - $tot;
                            
                            echo $tot; ?></td>
                            <td><?php echo $r['biaya_dam']; ?></td>
                            <td><?php echo $sisa; ?></td>
                            <td>
                                <?php 

                                if ($sisa < 0 OR $sisa == 0) {
                                    echo "<font color=#0082da><b>lunas</b></font>";
                                } else {
                                    echo "<font color=#f01519><b>Belum Lunas</b></font>";
                                }


                                ?>

                            </td>


                        </tr>
                        <tr>
        <th  bgcolor="yellow" colspan="4" >Total Kekurangan</th>
        <th  bgcolor="yellow"></th> <th></th>
        </tr>
                    </tbody>
                </table>