<?php
require "../../../config/koneksi.php";
require "../../../vendor/autoload.php";
require "../../../config/function.php";
require "../../../config/crud.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();



$style_col = [
    'font' => ['bold' => true],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER 
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], 
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], 
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] 
    ]
];
$style_row = [
    'alignment' => [
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER 
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], 
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], 
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] 
    ]
];
$id_user = $_GET['idus'];
$idbank = $_GET['m'];
$user = fetch($koneksi, 'users', array('id_user' => $id_user));
$mapel = fetch($koneksi, 'banksoal', array('id_bank' => $idbank));
$nama = $mapel['nama'];
$tgl =  buat_tanggal('D, d M Y - H:i', $mapel['date']);
if (date('m') >= 7 and date('m') <= 12) {
	$ajaran = date('Y') . "/" . (date('Y') + 1);
} elseif (date('m') >= 1 and date('m') <= 6) {
	$ajaran = (date('Y') - 1) . "/" . date('Y');
}

$sheet->setCellValue('A1', "Kode Mapel"); 
$sheet->setCellValue('C1', $mapel['kode']); 
$sheet->mergeCells('C1:K1');
$sheet->setCellValue('A2', "Tanggal Ujian"); 
$sheet->setCellValue('C2', $tgl);
$sheet->mergeCells('C2:K2');
$sheet->setCellValue('A3', "Tahun Ajaran"); 
$sheet->setCellValue('C3', $ajaran);
$sheet->mergeCells('C3:K3');

$sheet->setCellValue('A5', "NO"); 
$sheet->setCellValue('B5', "NIS"); 
$sheet->setCellValue('C5', "NO PESERTA"); 
$sheet->setCellValue('D5', "NAMA LENGKAP");
$sheet->setCellValue('E5', "WAKTU");
$sheet->setCellValue('F5', "PG");  
$sheet->setCellValue('G5', "ESAI"); 
$sheet->setCellValue('H5', "TOTAL"); 


$sheet->getStyle('A5')->applyFromArray($style_col);
$sheet->getStyle('B5')->applyFromArray($style_col);
$sheet->getStyle('C5')->applyFromArray($style_col);
$sheet->getStyle('D5')->applyFromArray($style_col);
$sheet->getStyle('E5')->applyFromArray($style_col);
$sheet->getStyle('F5')->applyFromArray($style_col);
$sheet->getStyle('G5')->applyFromArray($style_col);
$sheet->getStyle('H5')->applyFromArray($style_col);


$sheet->getRowDimension('1')->setRowHeight(20);
$sheet->getRowDimension('2')->setRowHeight(20);
$sheet->getRowDimension('3')->setRowHeight(20);


$no = 1; 
$row=6;  
$sql = mysqli_query($koneksi, "SELECT * FROM siswa a join nilai b ON a.id_siswa=b.id_siswa where b.id_bank='$idbank' ORDER BY kelas ASC");
while($data = mysqli_fetch_array($sql)){ 
$benar = $salah = 0;
		$skor = $lama = '-';
		$selisih = 0;
		$nilai = fetch($koneksi, 'nilai', array('id_bank' => $idbank, 'id_siswa' => $data['id_siswa']));
		if ($nilai['ujian_mulai'] <> '' and $nilai['ujian_selesai'] <> '') {
			$selisih = strtotime($nilai['ujian_selesai']) - strtotime($nilai['ujian_mulai']);
		}	
    $sheet->setCellValue('A' . $row, $no);
    $sheet->setCellValue('B' . $row, $data['nis']);
    $sheet->setCellValue('C' . $row, $data['no_peserta']);
    $sheet->setCellValue('D' . $row, $data['nama']);
    $sheet->setCellValue('E' . $row, lamaujian($selisih));
    $sheet->setCellValue('F' . $row, round($nilai['skor'], 2));
	$sheet->setCellValue('G' . $row, round($nilai['skor_esai'], 2));
	$sheet->setCellValue('H' . $row, round($nilai['total'], 2));
	
   
    $sheet->getStyle('A' . $row)->applyFromArray($style_row);
    $sheet->getStyle('B' . $row)->applyFromArray($style_row);
    $sheet->getStyle('C' . $row)->applyFromArray($style_row);
    $sheet->getStyle('D' . $row)->applyFromArray($style_row);
    $sheet->getStyle('E' . $row)->applyFromArray($style_row);
    $sheet->getStyle('F' . $row)->applyFromArray($style_row);
	$sheet->getStyle('G' . $row)->applyFromArray($style_row);
    $sheet->getStyle('H' . $row)->applyFromArray($style_row);
    
   
    $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom No
    $sheet->getStyle('B' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT); // Set text left untuk kolom NIS

    $sheet->getRowDimension($row)->setRowHeight(20); 

    $no++; 
    $row++; 
}

// Set width kolom
$sheet->getColumnDimension('A')->setWidth(5); 
$sheet->getColumnDimension('B')->setWidth(15); 
$sheet->getColumnDimension('C')->setWidth(15); 
$sheet->getColumnDimension('D')->setWidth(30); 
$sheet->getColumnDimension('E')->setWidth(15); 
$sheet->getColumnDimension('F')->setWidth(8);
$sheet->getColumnDimension('G')->setWidth(10); 
$sheet->getColumnDimension('H')->setWidth(8); 


$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);


$sheet->setTitle("Data Nilai");

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=Nilai CBT $mapel[nama] $mapel[date].xlsx"); 

header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');

?>
