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

?>

<script type="text/javascript">
 function checkAll(ele) {
      var checkboxes = document.getElementsByTagName('input');
      if (ele.checked) {
          for (var i = 0; i < checkboxes.length; i++) {
              if (checkboxes[i].type == 'checkbox' ) {
                  checkboxes[i].checked = true;
              }
          }
      } else {
          for (var i = 0; i < checkboxes.length; i++) {
              if (checkboxes[i].type == 'checkbox') {
                  checkboxes[i].checked = false;
              }
          }
      }
  }
</script>
  <!-- page content -->
  <div class="col" role="main">
    <div class="">
      <div class="clearfix"></div>

      <div class="row">
        <div class="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');
              }

              ?>
              <h2>Laporan tagihan Infaq bulanan/SPP bulan <b><?php echo namabulan($bln); ?></b> tahun <b><?php echo $thn; ?></b> <small></small></h2>

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



              <form action="?module=lap-spp-sd&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" onChange="document.getElementById('form_periode').submit();" required>

                        <?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" onChange="document.getElementById('form_periode').submit();" required>

                        <?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 (isset($_GET['periode']) and $_GET['periode'] == "y") {
              $link = "?module=lap-spp-sd&periode=x&bln=$bln&thn=$thn";
            } elseif (isset($_GET['periode']) and $_GET['periode'] == "x") {
              $link = "?module=lap-spp-sd&periode=x&bln=$bln&thn=$thn";
            } else {
              $link = "?module=lap-spp-sd";
            }
            ?>

            <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_sdumum")); ?>
                      <?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">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>

                  <!-- eksport -->
                  <br>
                  <?php
                  //sort lunas
                  if (isset($_POST['tampildata'])) {
                    if ($_POST['tampildata'] == 0) {
                      $sort_lunas = "&tampildata=0";
                    } elseif ($_POST['tampildata'] == 1) {
                      $sort_lunas = "&tampildata=1";
                    } elseif ($_POST['tampildata'] == 2) {
                      $sort_lunas = "&tampildata=2";
                    } elseif ($_POST['tampildata'] == 3) {
                      $sort_lunas = "&tampildata=3";
                    }
                  } else {
                    $sort_lunas = "&tampildata=0";
                  }

                  // sort tahun
                  if (isset($_POST['angkatan'])) {
                    $sort_angkatan = "&angkatan=$_POST[angkatan]";
                  } else {
                    $sort_angkatan = "&angkatan=0";
                  }

                  $eksport = $sort_lunas . $sort_angkatan;
                  ?>

                  <a href="component/com_pembayaran/export_spp.php<?php echo $link . $eksport; ?>" class='open_modal btn  btn-primary btn-xs'>Eksport</a>




                </div>
              </form>
            </div>








            <div class="x_content">

            <form action="component/com_pembayaran/export_all_tagihan.php" method="post">
                <button class="btn btn-danger" type="submit">Export tagihan</button>
                <br><th>RINCIAN <input type="checkbox" name="rincian" ></th><br>

              <table id="datatable" class="table table-bordered">
                <thead>
                  <tr>
                    <!--<th>Tanggal</th>-->
                    <!--<th>id transaksi</th> -->
                    <th width="15px">NO.</th>
                    <th>Pilih <input type="checkbox" onchange="checkAll(this)" name="chk[]" ></th>
                    <th>Id Santri</th>
                    <th>Nama Santri</th>
                    <th>Status Santri</th>
                    <th>Tanggal masuk</th>
                    <th>dibayar</th>
                    <th>Total Bayar</th>
                    <th>Pembayaran</th>
                    <th>Sisa</th>
                    <th>WA</th>
                    <th width="100">Status</th>
                    <th width="100">Aksi</th>
                    <th width="100">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));

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


                  //sorting data per jenis status lunas
                  if ($_POST['tampildata'] == 1) {
                    $query = mysqli_query($conn, "SELECT * FROM santri_sdumum WHERE statuus!='3' AND statuus!='4' AND statuus!='7' AND tanggal_masuk<='$tgl' $angkatan 
                            AND  id_santri IN(SELECT id_santri FROM status_lunas WHERE id_jenis='33' AND spp_bulan='$bln' AND spp_tahun='$thn') order by nama ASC ");
                  } elseif ($_POST['tampildata'] == 2) {
                    $query = mysqli_query($conn, "SELECT * FROM santri_sdumum WHERE statuus!='3' AND statuus!='4' AND statuus!='7' AND tanggal_masuk<='$tgl' $angkatan
                            AND id_santri IN(SELECT id_santri FROM transaksi WHERE id_jenis='33' AND spp_bulan='$bln' AND spp_tahun='$thn')
                            AND id_santri NOT IN(SELECT id_santri FROM status_lunas WHERE id_jenis='33' AND spp_bulan='$bln' AND spp_tahun='$thn') order by nama ASC ");
                  } elseif ($_POST['tampildata'] == 3) {
                    $query = mysqli_query($conn, "SELECT * FROM santri_sdumum WHERE statuus!='3' AND statuus!='4' AND statuus!='7' AND tanggal_masuk<='$tgl' $angkatan
                            AND id_santri NOT IN(SELECT id_santri FROM transaksi WHERE id_jenis='33' AND spp_bulan='$bln' AND spp_tahun='$thn')
                            AND id_santri NOT IN(SELECT id_santri FROM status_lunas WHERE id_jenis='33' AND spp_bulan='$bln' AND spp_tahun='$thn') order by nama ASC ");
                  } else {
                    $query = mysqli_query($conn, "SELECT * FROM santri_sdumum WHERE statuus!='3' AND statuus!='4' AND statuus!='7' AND tanggal_masuk<='$tgl' $angkatan 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 $no; ?></td>
                      <td><div class="form-check">
                        <input class="form-check-input" type="checkbox" name="pilih[]" value="<?php echo $row['id_santri']; ?>" id="flexCheckDefault">
                      </div></td>
                      <td><?php echo $row['id_santri']; ?></td>
                      <td><?php echo $row['nama']; ?></td>
                      <td><?php
                          $query2  = mysqli_query($conn, "SELECT * FROM jenis_status_sdumum 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='33' 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='33' 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 rupiah($row['biaya_spp']);  ?></td>
                      <td><?php $sisa = $row['biaya_spp'] - $total1['total_bayar'];
                          echo rupiah($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 "<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='33' 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-sd&aksi=ubahstatus&bln=$bln&thn=$thn&id=$row[id_santri]&type=33');\">Batal Lunas</a>";
                        } else {
                          echo "<a class=\"btn  btn-danger btn-xs\"  onclick=\"confirm_modal('?module=lap-spp-sd&aksi=ubahstatus&bln=$bln&thn=$thn&id=$row[id_santri]&type=33');\">Lunas</a>";
                        } ?>
                      </td>
                      <td>
                        <a class='open_modal btn  btn-primary btn-xs' href="?module=pembayaran-spp-sd&aksi=tambah_spp&id=<?php echo $row['id_santri']; ?>"><i class="glyphicon glyphicon-edit"></i> Bayar</a>
                      </td>




                      <?php
                      $count++

                      ?>

                    </tr>

                  <?php } ?>





                </tbody>
              </table>
            </form>   
            </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]',
                   '33')");
    //echo "hore";
  }

  echo "<script language='javascript'>document.location='?module=lap-spp-sd&periode=x&bln=$_GET[bln]&thn=$_GET[thn]';</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>