<?php include 'header.php'; ?>

<div class="content-wrapper">

  <section class="content-header">
    <h1>
      DATA LAPORAN POINT SISWA
      <!-- <small>Data Laporan Point Siswa</small> -->
    </h1>
    <!-- <ol class="breadcrumb">
      <li><a href="#"><i class="fa fa-dashboard"></i> Home</a></li>
      <li class="active">Dashboard</li>
    </ol> -->
  </section>

  <section class="content">
    <div class="row">
      <section class="col-lg-12">
        <div class="box box-primary">
          <div class="box-header">
            <h3 class="box-title">Filter Laporan Point</h3>
          </div>
          <div class="box-body">
            <form method="get" action="">
              <div class="row">
                <div class="col-md-4">

                  <div class="form-group">
                    <label>Mulai Tanggal</label>
                    <input autocomplete="off" type="text" value="<?php if(isset($_GET['tanggal_dari'])){echo $_GET['tanggal_dari'];}else{echo "";} ?>" name="tanggal_dari" class="form-control datepicker2" placeholder="Mulai Tanggal" required="required">
                  </div>

                  <div class="form-group">
                    <label>Sampai Tanggal</label>
                    <input autocomplete="off" type="text" value="<?php if(isset($_GET['tanggal_sampai'])){echo $_GET['tanggal_sampai'];}else{echo "";} ?>" name="tanggal_sampai" class="form-control datepicker2" placeholder="Sampai Tanggal" required="required">
                  </div>

                  <div class="form-group">
                    <label>Urutkan</label>
                    <select name="urutkan" class="form-control">
                      <option value="">Tanpa Pengurutan</option>
                      <option <?php if(isset($_GET['urutkan'])){ if($_GET['urutkan'] == "prestasi_terbanyak"){echo "selected='selected'";}} ?> value="prestasi_terbanyak">Prestasi Terbanyak</option>
                      <option <?php if(isset($_GET['urutkan'])){ if($_GET['urutkan'] == "pelanggaran_terbanyak"){echo "selected='selected'";}} ?> value="pelanggaran_terbanyak">Pelanggaran Terbanyak</option>
                    </select>
                  </div>

                  <div class="form-group">
                    <input type="submit" value="TAMPILKAN" class="btn btn-sm btn-primary">
                  </div>

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

        <div class="box box-primary">
          <div class="box-header">
            <h3 class="box-title">Laporan</h3>
          </div>
          <div class="box-body">

            <?php 
            if(isset($_GET['tanggal_sampai']) && isset($_GET['tanggal_dari']) && isset($_GET['urutkan'])){
              $tgl_dari = $_GET['tanggal_dari'];
              $tgl_sampai = $_GET['tanggal_sampai'];
              $urutkan = $_GET['urutkan'];
              ?>

              <div class="row">
                <div class="col-lg-6">
                  <table class="table table-bordered">
                    <tr>
                      <th width="30%">DARI TANGGAL</th>
                      <th width="1%">:</th>
                      <td><?php echo $tgl_dari; ?></td>
                    </tr>
                    <tr>
                      <th>SAMPAI TANGGAL</th>
                      <th>:</th>
                      <td><?php echo $tgl_sampai; ?></td>
                    </tr>
                    <tr>
                      <th>URUTKAN</th>
                      <th>:</th>
                      <td><?php echo $urutkan; ?></td>
                    </tr>
                  </table>
                  
                </div>
              </div>


              <a href="laporan_pdf.php?tanggal_dari=<?php echo $tgl_dari ?>&tanggal_sampai=<?php echo $tgl_sampai ?>&urutkan=<?php echo $urutkan ?>" target="_blank" class="btn btn-sm btn-success"><i class="fa fa-file-pdf-o"></i> &nbsp CETAK PDF</a>

              <div class="table-responsive">
                <table class="table table-bordered table-striped" id="table-datatable">
                  <thead>
                    <tr>
                      <th width="1%">NO</th>
                      <th>NAMA SISWA</th>
                      <th class="text-center">NIS</th>
                      <th class="text-center">TOTAL PRESTASI</th>
                      <th class="text-center">TOTAL PELANGGARAN</th>
                      <th class="text-center">OPSI</th>
                    </tr>
                  </thead>
                  <tbody>
                    <?php 
                    $no=1;
                    if(isset($_GET['urutkan'])){
                      if($urutkan == ""){
                        $u = "";
                      }else if($urutkan == "prestasi_terbanyak"){
                        $u = " order by total_prestasi desc";
                      }else if($urutkan == "pelanggaran_terbanyak"){
                        $u = " order by total_pelanggaran desc";
                      }
                    }else{
                      $u = "";
                    }
                    
                    $data = mysqli_query($koneksi,"SELECT siswa_id, siswa_nama, siswa_nis, sum(prestasi_point) as total_prestasi, sum(pelanggaran_point) as total_pelanggaran FROM siswa, input_pelanggaran, input_prestasi, pelanggaran, prestasi WHERE input_prestasi.siswa=siswa_id and input_pelanggaran.siswa=siswa_id and input_pelanggaran.pelanggaran=pelanggaran_id and input_prestasi.prestasi=prestasi_id and (date(input_pelanggaran.waktu) >= '$tgl_dari' AND date(input_pelanggaran.waktu) <= '$tgl_sampai') or (date(input_prestasi.waktu) >= '$tgl_dari' AND date(input_prestasi.waktu) <= '$tgl_sampai') group by siswa_id $u");
                    while($d = mysqli_fetch_array($data)){
                      $id_siswa = $d['siswa_id'];
                      ?>
                      <tr>
                        <td><?php echo $no++; ?></td>
                        <td><?php echo $d['siswa_nama']; ?></td>
                        <td class="text-center"><?php echo $d['siswa_nis']; ?></td>
                        <td class="text-center">
                          <?php 
                          $jumlah_prestasi = mysqli_query($koneksi, "select sum(prestasi_point) as total from prestasi, input_prestasi where prestasi_id=input_prestasi.prestasi and input_prestasi.siswa='$id_siswa'");
                          $j = mysqli_fetch_assoc($jumlah_prestasi);
                          echo $j['total'];
                          ?>
                        </td>
                         <td class="text-center">
                          <?php 
                          $jumlah_pelanggaran = mysqli_query($koneksi, "select sum(pelanggaran_point) as total from pelanggaran, input_pelanggaran where pelanggaran_id=input_pelanggaran.pelanggaran and input_pelanggaran.siswa='$id_siswa'");
                          $j = mysqli_fetch_assoc($jumlah_pelanggaran);
                          echo $j['total'];
                          ?>
                        </td>
                        <td class="text-center">
                           <a class="btn btn-success btn-sm" target="_blank" href="siswa_riwayat.php?id=<?php echo $d['siswa_id'] ?>"><i class="fa fa-info-circle"></i> Detail</a>
                        </td>
                      </tr>
                      <?php 
                    }
                    ?>
                  </tbody>
                </table>
              </div>

              <?php 
            }else{
              ?>

              <div class="alert alert-primary text-center">
                Silahkan Filter Laporan Terlebih Dulu.
              </div>

              <?php
            }
            ?>

          </div>
        </div>
      </section>
    </div>
  </section>

</div>
<?php include 'footer.php'; ?>