<?php
include '../../config/conn.php';
if ($_GET['aksi'] == '') {
  $jenis = $_GET['type'];



?>
  <!-- page content -->
  <div class="col" role="main">
    <div class="">
      <div class="clearfix"></div>

      <div class="row">
        <div class="col-md-12 col-md-12 col-sm-12 col-xs-12 ">
          <div class="x_panel">
            <div class="x_title" style="text-transform: capitalize;">

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

              $bln1  = mysqli_query($conn, "SELECT * FROM bulan WHERE id_bulan=$bln order by id_bulan asc");
              $bln2 = mysqli_fetch_array($bln1);
              ?>
              <h2>Laporan tagihan Infaq bulanan/SPP Rumah Tahfidz Pagi bulan <b><?php echo $bln2['nama_bulan']; ?></b> tahun <b><?php echo $thn; ?></b> <small></small></h2>

              <div class="clearfix"></div>



              <form action="?module=lap-spp-rt-pagi&periode=y" id="form_periode" enctype="multipart/form-data" method="POST">


                <div class="well" style="overflow: auto">

                  <div class="col-md-12 col-sm-12 col-xs-12" style="padding: 4px;">
                    <label for="nama">Pilih Periode :</label>
                  </div>

                  <div class="col-md-6 col-sm-12 col-xs-12" style="padding: 4px;">

                    <div class="input-group">
                      <div class="input-group-addon">Bulan</div>
                      <select id="heard" class="form-control" name="bulan" required onChange="document.getElementById('form_periode').submit();">

                        <?php
                        $query  = mysqli_query($conn, "SELECT * FROM bulan order by id_bulan asc");
                        while ($r = mysqli_fetch_array($query)) { ?>
                          <option value="<?php echo $r['id_bulan']; ?>" <?php if ($r['id_bulan'] == $bln) {
                                                                          echo "selected";
                                                                        } ?>><?php echo $r['nama_bulan']; ?></option>
                        <?php
                        }
                        ?>
                      </select>

                      <div class="input-group-addon">Tahun</div>
                      <select id="heard" class="form-control" name="tahun" required onChange="document.getElementById('form_periode').submit();">

                        <?php

                        for ($tahun = 2018; $tahun <= date('Y') + 3; $tahun++) {
                        ?>

                          <option value="<?php echo $tahun; ?>" <?php if ($tahun == $thn) {
                                                                  echo "selected";
                                                                } ?>><?php echo $tahun; ?></option>
                        <?php
                        }
                        ?>
                      </select>
                    </div>
                  </div>

                  <div class="col-md-6 col-sm-12 col-xs-12" style="padding: 4px;">


              </form>


            </div>
            <?php
            if ($_GET['periode'] == "y") {
              $link = "?module=lap-spp-rt-pagi&periode=x&bln=$bln&thn=$thn";
              $link_ex = "?periode=x&bln=$bln&thn=$thn";
            } elseif ($_GET['periode'] == "x") {
              $link = "?module=lap-spp-rt-pagi&periode=x&bln=$bln&thn=$thn";
              $link_ex = "?periode=x&bln=$bln&thn=$thn";
            } else {
              $link = "?module=lap-spp-rt-pagi";
              $link_ex = "?";
            }
            ?>

            <div class="col-md-12 col-sm-12 col-xs-12" style="padding: 4px;">
              <form action="<?php echo $link; ?>" id="form_id" enctype="multipart/form-data" method="POST">
                <div class="col-md-3">
                  <div class="input-group">
                    <div class="input-group-addon">Angkatan</div>
                    <select id="heard" class="form-control" required name="angkatan" onChange="document.getElementById('form_id').submit();">
                      <option value="0">Semua Tahun</option>
                      <?php $sort_th = mysqli_fetch_array(mysqli_query($conn, "SELECT MAX(YEAR(tanggal_masuk)) as max_th, MIN(YEAR(tanggal_masuk)) as min_th FROM santri_rt")); ?>
                      <?php for ($x = $sort_th['min_th']; $x <= $sort_th['max_th']; $x++) { ?>
                        <option <?php if ($_POST['angkatan'] == $x) {
                                  echo "selected";
                                } ?> value="<?php echo $x; ?>"><?php echo $x; ?></option>
                      <?php } ?>
                    </select>
                  </div>

                  <div class="input-group">
                    <div class="input-group-addon">Kelas</div>
                    <select id="heard" class="form-control" required name="kelas" onChange="document.getElementById('form_id').submit();">
                      <option value="0">Semua Kelas</option>
                      <?php $sort_kls = mysqli_query($conn, "SELECT * FROM jenis_rt WHERE kategori='1'"); ?>
                      <?php while ($kls = mysqli_fetch_array($sort_kls)) { ?>
                        <option <?php if ($_POST['kelas'] == $kls['id']) {
                                  echo "selected";
                                } ?> value="<?php echo $kls['id']; ?>"><?php echo $kls['nama_jenis']; ?></option>
                      <?php } ?>
                    </select>
                  </div>

                  <div class="input-group">
                    <div class="input-group-addon">Status</div>
                    <select id="heard" class="form-control" required name="tampildata" onChange="document.getElementById('form_id').submit();">
                      <option value="0">Semua data</option>
                      <option value="1" <?php if ($_POST['tampildata'] == 1) {
                                          echo "selected";
                                        } ?>>Lunas</option>
                      <option value="2" <?php if ($_POST['tampildata'] == 2) {
                                          echo "selected";
                                        } ?>>Bayar belum lunas</option>
                      <option value="3" <?php if ($_POST['tampildata'] == 3) {
                                          echo "selected";
                                        } ?>>Belum bayar</option>
                    </select>
                  </div>
                </div>

              </form>

              <?php
              //sorting data per Kelas
              if (!isset($_POST['kelas']) or $_POST['kelas'] == 0) {
                $kelas = "";
                $kelas_ex = "";
              } else {
                $kelas = "AND jenis_rt=$_POST[kelas]";
                $kelas_ex = "&kelas=$_POST[kelas]";
              }

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

              //sorting data per lunas/tdk lunas
              if (!isset($_POST['tampildata']) or $_POST['tampildata'] == 0) {
                $lunas = "&tampildata=0";
              } elseif ($_POST['tampildata'] == 1) {
                $lunas = "&tampildata=1";
              } elseif ($_POST['tampildata'] == 2) {
                $lunas = "&tampildata=2";
              } elseif ($_POST['tampildata'] == 3) {
                $lunas = "&tampildata=3";
              }

              $export = $kelas_ex . $angkatan_ex;
              ?>


              <a href="component/com_pembayaran/export_spp_rt_pagi.php<?php echo $link_ex . $export . $lunas; ?>" class='open_modal btn  btn-primary btn-xs'>Eksport</a>

            </div>






            <div class="x_content">

              <table id="datatable" class="table table-bordered">
                <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>WA</th>
                    <th>Status</th>
                    <th>Aksi</th>
                    <th>Bayar</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 = '1'");


                  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 ($_POST['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 = '1') AND id_santri IN(SELECT id_santri FROM status_lunas WHERE id_jenis='11' AND spp_bulan='$bln' AND spp_tahun='$thn') order by nama ASC ");
                  } elseif ($_POST['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 = '1') AND id_santri IN(SELECT id_santri FROM transaksi WHERE id_jenis='11' 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 ($_POST['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 = '1') AND id_santri NOT IN(SELECT id_santri FROM transaksi WHERE id_jenis='11' 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 ");
                  } 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 = '1') 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='11' 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 . "~" . rupiah($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='11' AND spp_bulan='$bln' AND spp_tahun='$thn' order by tanggal ASC ");
                          $total1 = mysqli_fetch_array($total);
                          echo rupiah($total1['total_bayar']);
                          ?></td>

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

                      <?php $wa=substr_replace("$row[no_hp]","+62",0,1); ?>
                      <td><a href="https://wa.me/<?php echo $wa;?>" target="_blank"><?php echo $row['no_hp']."<i class='glyphicon glyphicon-send'></i>"; ?></a></td>
                      <td>
                        <?php
                        $tet1 = mysqli_query($conn, "SELECT * FROM status_lunas WHERE id_santri='$row[id_santri]' AND id_jenis='11' 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>
                      <td>
                        <?php
                        if (isset($tet['id_santri'])) {
                          echo "<a class=\"btn  btn-success btn-xs\"  onclick=\"confirm_modal('?module=lap-spp-rt-pagi&aksi=ubahstatus&bln=$bln&thn=$thn&id=$row[id_santri]&type=11');\">Batal Lunas</a>";
                        } else {
                          echo "<a class=\"btn  btn-danger btn-xs\"  onclick=\"confirm_modal('?module=lap-spp-rt-pagi&aksi=ubahstatus&bln=$bln&thn=$thn&id=$row[id_santri]&type=11');\">Lunas</a>";
                        } ?>
                      </td>
                      <td>
                        <a class='open_modal btn  btn-primary btn-xs' href="?module=pembayaran-spp-rt-pagi&aksi=tambah_spp&id=<?php echo $row['id_santri']; ?>"><i class="glyphicon glyphicon-edit"></i> Bayar</a>
                      </td>




                      <?php
                      $count++

                      ?>

                    </tr>

                  <?php } ?>





                </tbody>
              </table>
            </div>
          </div>
        </div>

      </div>
    </div>
  </div>
  <!-- /page content --><br>





<?php } elseif ($_GET['aksi'] == "ubahstatus") {
  $tet1 = mysqli_query($conn, "SELECT * FROM status_lunas WHERE id_santri='$_GET[id]' AND id_jenis='$_GET[type]' AND spp_bulan='$_GET[bln]' AND spp_tahun='$_GET[thn]' ");
  $tet = mysqli_fetch_array($tet1);
  if (isset($tet['id_santri'])) {
    mysqli_query($conn, "DELETE from status_lunas WHERE id_santri='$_GET[id]' AND id_jenis='$_GET[type]' AND spp_bulan='$_GET[bln]' AND spp_tahun='$_GET[thn]' ");
  } else {
    mysqli_query($conn, "INSERT INTO status_lunas(id_santri,
    spp_bulan,
    spp_tahun,
    id_jenis) VALUES('$_GET[id]',
                   '$_GET[bln]',
                   '$_GET[thn]',
                   '$_GET[type]')");
    //echo "hore";
  }

  echo "<script language='javascript'>document.location='?module=lap-spp-rt-pagi&periode=x&bln=$_GET[bln]&thn=$_GET[thn]';</script>";
  // echo "<script>alert('Status diubah!');window.history.go(-2);</script>";
}
?>
<div class="modal fade" id="modal_delete">
  <div class="modal-dialog">
    <div class="modal-content" style="margin-top:100px;">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
        <h4 class="modal-title" style="text-align:center;">Ubah status lunas ?</h4>
      </div>
      <div class="modal-footer" style="margin:0px; border-top:0px; text-align:center;">
        <a href="#" class="btn btn-danger btn-sm" id="delete_link">Ubah</a>
        <button type="button" class="btn btn-success btn-sm" data-dismiss="modal">Batal</button>
      </div>
    </div>
  </div>
</div>