<?php use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
if(!isset($_COOKIE['ADMIN_KEY']) && !isset($_COOKIE['KEY'])){
  header('location:./login/');
  exit;
}else{
require_once '../../../sw-library/sw-config.php';
require_once '../../../sw-library/sw-function.php';
require_once '../../login/user.php';
$no = 0;

switch (@$_GET['action']){
case 'print':

$filterParts = [];
$spp  = isset($_GET['spp']);


if (!empty($_GET['siswa'])) {
    $siswa = htmlentities(epm_decode($_GET['siswa']??'-'));
    $filterParts[] = "user.user_id='$siswa'";
    $filter = 'WHERE ' . implode(' AND ', $filterParts);

}else if(!empty($_GET['kelas'])) {
    $kelas = htmlentities($_GET['kelas']);
    $filterParts[] = "user.kelas='$kelas'";
    $filter = 'WHERE ' . implode(' AND ', $filterParts);
    $data_wali = getWaliKelas($kelas, $connection);
}else{
    $filter = '';
}

$query_siswa = "SELECT user_id,nama_lengkap,nisn,kelas FROM user $filter";
$result_siswa = $connection->query($query_siswa);
if($result_siswa->num_rows > 0){

echo'
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="s-widodo.com">
    <meta name="author" content="s-widodo.com">
    <title>Laporan SPP</title>
    <style>

    body{font-family:Arial,Helvetica,sans-serif}
    .text-center{
      text-align: center;
    }
    
    .kop {
      position:relative;
      display:contents;
      margin:0px 0px 20px 0px;
    }

    .kop img{
      width:100%;
      height:auto;
    }

    table.datatable{
      width:100%;
      background-color:#fff;
      border-collapse:collapse;
      border-width:1px;
      border-color:#b3b3b3;
      border-style:solid;
      color:#000;
      margin:10px 0px 0px 0px;
  }
    table.datatable td,table.datatable th{
      border-width:1px;
      border-color:#b3b3b3;
      border-style:solid;
      padding:5px;text-align:left;
      
    }
    table.datatable th{
      background-color:#666666;
      color:#ffffff;
    }
    table.datatable td.text-center,
    table.datatable th.text-center{text-align:center}

    .badge {
      font-size: 66%;
      font-weight: 600;
      line-height: 1;
      display: inline-block;
      padding: 0.35rem 0.375rem;
      transition: color .15s ease-in-out, background-color .15s ease-in-out, border-color .15s ease-in-out, box-shadow .15s ease-in-out;
      text-align: center;
      vertical-align: baseline;
      white-space: nowrap;
      border-radius: 0.375rem;
    }
    .badge-success {
      color: #1aae6f;
      background-color: #b0eed3;
    }
    
    .badge-danger {
      color: #f80031;
      background-color: #fdd1da;
    }

    .rounded {
      border-radius: 0.375rem !important;
    }

    </style>
      <script>
        window.onafterprint = window.close;
        window.print();
      </script>
</head>
<body>

  <div class="container">
    <div class="row">
      <div class="col-md-12">
        <div class="kop text-center">';
        if(file_exists("../../../sw-content/$site_kop")){
          echo'
          <img src="../../../sw-content/'.$site_kop.'" class="img-responsive imaged w100">';
        }
        echo'
        </div>
        <hr>
        <h3 class="text-center" style="margin:0px">LAPORAN SPP</h3>
      </div>

      <div class="col-md-12">
          <table class="datatable mt-3">
              <thead>
                <tr>
                    <th rowspan="2" width="40" class="text-center">No</th>
                    <th rowspan="2">Nama Siswa</th>
                    <th rowspan="2">Kelas</th>
                    <th rowspan="2">Tahun Ajaran</th>
                    <th class="text-center" colspan="12">Bulan</th>
                    <th rowspan="2">JUMLAH DIBAYAR</th>
                    <th rowspan="2">TUNGGAKAN</th>
                  </tr>
                  
                  <tr class="bg-primary text-white">';
                  $bulan_nama =array(1=>"Januari","Februari","Maret","April","Mei","Juni","Juli","Agustus","September","Oktober","November","Desember");
                  for($bulan=1; $bulan<=12; $bulan++){
                    echo'
                    <th>'.$bulan_nama[$bulan].'</th>';
                  }
                  echo'
                  </tr>
              </thead>
              <tbody>';
               while ($data_siswa = $result_siswa->fetch_assoc()){$no++;
                
                $data_spp = $connection->query("SELECT nominal, tahun_ajaran
                FROM master_spp WHERE status='Y' AND master_spp_id='$spp'
                ")->fetch_assoc();


                // total pembayaran
                $total_pembayaran = $connection->query("SELECT SUM(nominal) AS total 
                FROM pembayaran 
                WHERE user_id='$data_siswa[user_id]' 
                AND master_spp_id='$spp'
                ")->fetch_assoc()['total'] ?? 0;

                // hitung tunggakan
                $jumlah_tunggakan = $data_spp['nominal'] - $total_pembayaran;

                echo'
                <tr>
                  <td class="text-center">'.$no.'</td>
                  <td>'.($data_siswa['nama_lengkap']??'-').'<br>
                  <span class="badge badge-info">'.$data_siswa['nisn'].'</span></td>
                  <td>'.($data_siswa['kelas']??'-').'</td>
                  <td>'.($data_spp['tahun_ajaran'] ?? '-').'</td>';
                  for($bulan=1; $bulan<=12; $bulan++){

                      $query_pembayaran ="SELECT nominal FROM pembayaran WHERE user_id='$data_siswa[user_id]' AND bulan='$bulan' AND master_spp_id='$spp'";
                      $result_pembayaran = $connection->query($query_pembayaran);
                      if($result_pembayaran->num_rows > 0){
                        $data_pembayaran = $result_pembayaran->fetch_assoc();
                        $nominal_pembayaran = 'Rp '.format_angka($data_pembayaran['nominal']).'';
                      }else{
                        $nominal_pembayaran = '-';
                      }
                    echo'
                    <td>'.$nominal_pembayaran.'</td>';
                  }
                  echo'
                  <td class="text-center"><b class="text-info">Rp '.format_angka($total_pembayaran).'</b></td>
                  <td class="text-center"><b class="text-danger">Rp '.format_angka($jumlah_tunggakan).'</b></td>
                </tr>';
                }
              echo'
              </tbody>
          </table>

          <table style="width:100%;margin-top:30px">
            <tr>
              <td style="width:60%;text-align:left;vertical-align:top;">
          
              </td>

              <td style="width:40%;text-align:left;vertical-align:top;">
              '.$row_site['kabupaten'].', '.tgl_indo($date).'<br>
                <strong>Kepala Sekolah</strong><br>
              '.(file_exists("../../../sw-content/".strip_tags($row_site['stempel']??'stempel.png')) 
                ? '<img src="../../../sw-content/'.strip_tags($row_site['stempel']??'stempel.png').'" style="width:160px;height:auto;margin-left: -40px;"><br>' 
                : '').'
              <b>'.strip_tags($row_site['kepala_sekolah']??'').'</b><br>
              NIP. '.($row_site['nip_kepala_sekolah']??'-').'
              </td>
            </tr>
          </table>
      </div>
    </div>
  </div>
</body>
</html>';

}else{
  echo'<div style="font-size:30px;text-align:center;margin-top:30px;">Data tidak ditemukan</div>
  <center><button onclick="window.close();" style="background:#111111;padding:8px 20px;color:#ffffff;border-radius:10px;">KEMBALI</button></center>';
}


break;
}}