<?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] 
    ]
];






$ids = $_GET['ids'];
$idnilai = $_GET['idnilai'];
$nilai = mysqli_fetch_array(mysqli_query($koneksi, "SELECT * FROM nilai WHERE id_siswa='$ids' and id_nilai='$idnilai'"));
$siswa = fetch($koneksi, 'siswa', array('id_siswa' => $ids));
$mapel = fetch($koneksi, 'banksoal', array('id_bank' => $nilai['id_bank']));
$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->setCellValue('A2', "Tanggal Ujian"); 
$sheet->setCellValue('C2', $tgl);
$sheet->setCellValue('A3', "Nama Siswa"); 
$sheet->setCellValue('C3', $siswa['nama']);


$sheet->setCellValue('A5', "NO"); 
$sheet->setCellValue('B5', "JENIS"); 
$sheet->setCellValue('C5', "JAWABAN"); 




$sheet->getStyle('A5')->applyFromArray($style_col);
$sheet->getStyle('B5')->applyFromArray($style_col);
$sheet->getStyle('C5')->applyFromArray($style_col);


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


$no = 1; 
$row=6;  
$jawaban = unserialize($nilai['jawaban']);
foreach ($jawaban as $key => $value) :
$soal = mysqli_fetch_array(mysqli_query($koneksi, "SELECT * FROM soal WHERE id_soal='$key'"));
if ($value == $soal['jawaban']) :
   $status = "V";
    else :
    $status = "X";
  endif;
    $sheet->setCellValue('A' . $row, $no);
	 $sheet->setCellValue('B' . $row, 'PG');
    $sheet->setCellValue('C' . $row, $value);
   if($status=="V"):    
   $sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00FF7F');
    else:
	$sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FF0000');
   endif;
	
    $no++; 
    $row++; 
	endforeach;
$jawabm = unserialize($nilai['jawaban_multi']);
foreach ($jawabm as $key => $value) :
$soal = mysqli_fetch_array(mysqli_query($koneksi, "SELECT * FROM soal WHERE id_soal='$key'"));
if ($value == $soal['jawaban']) :
   $status = "V";
    else :
    $status = "X";
  endif;
   $sheet->setCellValue('A' . $row, $no);
	 $sheet->setCellValue('B' . $row, 'MULTI');
    $sheet->setCellValue('C' . $row, $value);
   if($status=="V"):    
   $sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00FF7F');
    else:
	$sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FF0000');
   endif;
	
    $no++; 
    $row++; 
	endforeach;
$jawabbs = unserialize($nilai['jawaban_bs']);
foreach ($jawabbs as $key => $value) :
$soal = mysqli_fetch_array(mysqli_query($koneksi, "SELECT * FROM soal WHERE id_soal='$key'"));
if ($value == $soal['jawaban']) :
   $status = "V";
    else :
    $status = "X";
  endif;
   $sheet->setCellValue('A' . $row, $no);
	 $sheet->setCellValue('B' . $row, 'BS - FO');
    $sheet->setCellValue('C' . $row, $value);
   if($status=="V"):    
   $sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00FF7F');
    else:
	$sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FF0000');
   endif;
	
    $no++; 
    $row++; 
	endforeach;	
	$jawajd = unserialize($nilai['jawaban_urut']);
foreach ($jawabjd as $key => $value) :
$soal = mysqli_fetch_array(mysqli_query($koneksi, "SELECT * FROM soal WHERE id_soal='$key'"));
if ($value == $soal['jawaban']) :
   $status = "V";
    else :
    $status = "X";
  endif;
   $sheet->setCellValue('A' . $row, $no);
	 $sheet->setCellValue('B' . $row, 'JODOH');
    $sheet->setCellValue('C' . $row, $value);
   if($status=="V"):    
   $sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00FF7F');
    else:
	$sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FF0000');
   endif;
	
    $no++; 
    $row++; 
	endforeach;
$jesai = unserialize($nilai['jawaban_esai']);
foreach ($jesai as $key => $value) :
$soal = mysqli_fetch_array(mysqli_query($koneksi, "SELECT * FROM soal WHERE id_soal='$key'"));
if ($value == $soal['jawaban']) :
   $status = "V";
    else :
    $status = "X";
  endif;
   $sheet->setCellValue('A' . $row, $no);
	 $sheet->setCellValue('B' . $row, 'ESAI');
    $sheet->setCellValue('C' . $row, $value);
   if($status=="V"):    
   $sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00FF7F');
    else:
	$sheet->getStyle('B' .$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FF0000');
   endif;
	
    $no++; 
    $row++; 
	endforeach;	
	
    $sheet->getRowDimension($row)->setRowHeight(20); 

    


// Set width kolom
$sheet->getColumnDimension('A')->setWidth(5); 
$sheet->getColumnDimension('B')->setWidth(15); 
$sheet->getColumnDimension('C')->setWidth(100); 

$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 $siswa[nama] $mapel[nama].xlsx"); 

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

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

?>
