<?php
session_start();
include "../../inc/config.php";


require_once '../../lib/Writer.php';


      $jurusan = trim($_POST['jur_filter']);
      if ($jurusan=='all') {
          $jurusan = "";
          if ($_SESSION['level']!=1) {
              $jurusan = "and trim(kode_jurusan)='".$_SESSION['jurusan']."'";
          }
          $nama_jurusan = "Semua";
      } else {
          $jurusan = "and trim(kode_jurusan)='$jurusan'";

        $nama_jurusan = $db->fetch_custom_single("select nama_jurusan from jurusan where id!='' $jurusan");
        $nama_jurusan = $nama_jurusan->nama_jurusan;
        $nama_jurusan = str_replace(" ", "_", $nama_jurusan);

      }




       if (isset($_POST['sem_filter'])) {
          
        if ($_POST['sem_filter']=='all') {
          $semester = '';
          $nama_semester = "";
        } else {
          $semester = "and semester='".$_POST['sem_filter']."'";
          $nama_semester = $_POST['sem_filter'];
        }
       

        if ($_POST['valid']=='all') {
          $valid = '';
          
        } else {
          $valid = "and valid='".$_POST['valid']."'";
          
        }


        if ($_POST['status_kuliah']=='all') {
          $status_kuliah = '';
           $nama_status = "";
        } else {
          $status_kuliah = "and status_kuliah='".$_POST['status_kuliah']."'";
           $stat = array(
              'A' => "Aktif",
              'C' => "Cuti",
              'N' => "Non-aktif",
              'G' => "Sedang_Double_Degree"
              );
          $nama_status = $stat[$_POST['status_kuliah']];
        }




           if ($_POST['status']=='all') {
          $status = '';
        } else {
          $status = "and status_error='".$_POST['status']."'";
        }

        }


        $filter_akm = "where semester!='' $jurusan $semester $valid $status_kuliah $status";



$writer = new XLSXWriter();
$style =
        array (
                      array(
              'border' => array(
                'style' => 'thin',
                'color' => '000000'
                ),
            'allfilleddata' => true
            ),
            array(
                'fill' => array(
                    'color' => 'ff0000'
                    ),
                'cells' => array(
                    'A1',
                    'C1',
                    'D1',
                    'E1',
                    'F1',
                    'G1',
                    'H1',
                    'I1',
                    'J1',
                    'K1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                    'B1',
                    'L1',
                    'M1'
                   
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );



//column width
$col_width = array(
  1 => 19,
  2 => 27,
  3 => 15,
  4 => 15,
  5 => 15,
  6 => 15,
  7 => 15,
  8 => 15,
  9 => 20,
  10 => 20,
  11 => 20,
  12 => 20,
  13 => 39
  );
$writer->setColWidth($col_width);


$header = array(
  'NIM'=>'string',
  'NAMA'=>'string',
  'Semester'=>'string',
  'SKS Semester'=>'string',
  'IP Semester'=>'string',
  'SKS Kumulatif'=>'string',
  'IP Kumulatif'=>'string',
  'Status'=>'string',
  'Kode Prodi'=>'string',
  'Biaya Kuliah'=>'string',
  'Jenis Pembiayaan' => 'string',
  'Ket Valid'=>'string',
  'Ket Status'=>'string',
);



$data_rec = array();

        $order_by = "order by semester,nim ASC";

        $temp_data = $db->fetch_custom("select * from nilai_akm $filter_akm $order_by");


     
        foreach ($temp_data as $key) {

                                  if ($key->status_error==0) {
                                    $ket = "Belum diproses";
                                  } elseif ($key->status_error==1) {
                                    $ket = "Sukses";
                                  } else {
                                    $ket = strip_tags($key->keterangan);
                                  }
                                  
                                  if ($key->valid==0) {
                                    $ket_valid = "Belum divalidasi";
                                  } elseif ($key->valid==1) {
                                    $ket_valid = "Valid";
                                  } else {
                                    $ket_valid = strip_tags($key->ket_sks_sem);
                                  }
                          
                        $data_rec[] = array(
                                      $key->nim,
                                      $key->nama,
                                      $key->semester,
                                      $key->sks_smt,
                                      $key->ips,
                                      $key->sks_total,
                                      $key->ipk,
                                      $key->status_kuliah,
                                      $key->kode_jurusan,
                                      $key->biaya_smt,
                                      $key->id_pembiayaan,
                                      $ket_valid,
                                      $ket

                        );
                         
                       
                       
            }



//jalur pendaftaran
$status_mhs = $db->fetch_custom("select * from status_mahasiswa_akm");
foreach ($status_mhs as $status) {
  $data_status[] = $status->id_status." : ".$status->nama_status;
}
//pembiayaan
$pembiayaan_mhs = $db->fetch_custom("select * from pembiayaan");
foreach ($pembiayaan_mhs as $pembiayaan_mhs) {
  $data_pembiayaan[] = $pembiayaan_mhs->id_pembiayaan." : ".$pembiayaan_mhs->nm_pembiayaan;
}
$comments = array(
'A1' => 'Nomor Induk Mahasiswa

Wajib Diisi',
'B1' => 'Nama Mahasiswa

Boleh Kosong',
'C1' => 'Periode Semester Pelaporan
format : tahun+semester (1 : Ganjil, 2 : Genap, 3 : Pendek)
contoh : 20231 (berarti 2023 ganjil)

Wajib disi',
'D1' => 'Jumlah SKS Semester

WAJIB DIISI',
'E1' => 'IP Semester

WAJIB DIISI',
'F1' => 'Jumlah SKS Kumulatif

WAJIB DIISI',
'G1' => 'IP Kumulatif

WAJIB DIISI',
'H1' => 'Status Mahasiswa
'.implode("\n", $data_status).'

WAJIB DIISI',
'I1' => 'Kode Prodi/Jurusan, 
Lihat di menu master data -> jurusan

Wajib diisi',
'J1' => 'Biaya kuliah Semester, isi tanpa koma atau titik. 
Contoh : 2500000

Wajib diisi',
'K1' => 'Jenis Pembiayaan
'.implode("\n", $data_pembiayaan).'

Wajib diisi',
);
        $writer->setComment($comments);
$filename = "Data AKM_".$nama_jurusan.$nama_semester.$nama_status.'.xlsx';
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$writer->writeSheet($data_rec,'Data Akm '.$nama_jurusan.' '.$nama_semester.$nama_status, $header, $style);
$writer->writeToStdOut();
exit(0);
?>