<?php

namespace App\Controllers\Panel\Ujian\Jadwal\Detail\Result;

use App\Controllers\Panel\BC;
use App\Models\Panel\BankSoal;
use App\Models\Panel\Ujian\Jadwal;
use CodeIgniter\API\ResponseTrait;
use PhpOffice\PhpSpreadsheet\Helper\Html;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Export extends BC
{
    use ResponseTrait;
    private $options = ["A", "B", "C", "D", "E"];
    private $pgcmsd = [];
    private  $pgcmsud = [];
    private  $pgmsd = [];
    private  $pgmsud = [];
    private  $pgtfsd = [];
    private  $pgtfsud = [];
    private  $pgcsd = [];
    private  $pgcsud = [];
    private  $sesd = [];
    private  $sesud = [];
    private  $UjianData = [];
    private  $uid = [];
    private  $js = [];
    private  $jawabanEsai = [];
    private $jadwalData, $path, $jadwalModel, $spreadsheet, $worksheet, $db, $html, $id, $wjs, $currentRow;
    private $tdp_pg = 0;
    private $fdp_pg = 0;
    private $tdp_se = 0;
    private $fdp_se = 0;
    private $SNstartRow = 4;
    private $wjcol = ['c' => 14, 'a' => 14];
    private $SAstartRow = 2;
    public function __construct()
    {
        parent::__construct();
        $this->jadwalModel = new Jadwal();
        $this->spreadsheet = new Spreadsheet();
        $this->spreadsheet->getActiveSheet()->setTitle("Nilai");
        $this->worksheet = $this->spreadsheet->setActiveSheetIndex(0);
        $this->html = new Html();
        $this->db = db_connect();
    }
    public function index($id)
    {
        set_time_limit(0);
        $this->id = base64_decode($id);
        $this->uid = explode(',', $this->request->getGet('id') ?? '');
        if (!$this->uid) return $this->failNotFound();
        $this->jadwalData = $this->jadwalModel->findId($this->id);
        if (!$this->jadwalData) return $this->failNotFound();
        $this->UjianData = $this->jadwalModel->progress($this->id, $this->uid, [], true);
        if (!$this->UjianData) return $this->failNotFound();
        $this->path = WRITEPATH . "/file/ujian/{$this->jadwalData['kode_ruang']}-{$this->jadwalData['kode_mapel']}.xlsx";
        $pgonbank = $this->db->table('soal_data')->select('COUNT(*) jumlah,jenis_soal')->join('draft_soal ds', 'ds.soal_id=soal_data.id')->where(['bank_id' => $this->jadwalData['bank_soal_id'], 'ds.draft_id' => $this->jadwalData['draft_id']])->groupBy('jenis_soal')->get()->getResultArray();
        $pgonbank = array_column($pgonbank, 'jumlah', 'jenis_soal');
        // dd($pgonbank);
        $this->jadwalData['pgcmonbank'] = ($pgonbank[(new BankSoal())->jenis('pg')] ?? 0);
        // dd($this->jadwalData['pgcmonbank']);
        $this->jadwalData['pgmonbank'] = ($pgonbank[(new BankSoal())->jenis('pgmulti')] ?? 0);
        $this->jadwalData['pgtfonbank'] = ($pgonbank[(new BankSoal())->jenis('pgtf')] ?? 0);
        $this->jadwalData['pgconbank'] = ($pgonbank[(new BankSoal())->jenis('pgcouple')] ?? 0);
        $this->jadwalData['seonbank'] = ($pgonbank[(new BankSoal())->jenis('shortentry')] ?? 0);
        $this->jadwalData['esaionbank'] = ($pgonbank[(new BankSoal())->jenis('esai')] ?? 0);
        // dd($this->jadwalData);
        // if ($this->jadwalData['acak_soal']) {
        $this->wjs['pg'] = $this->jadwalData['pgcmonbank'];
        $this->wjs['pgmulti'] = $this->jadwalData['pgmonbank'];
        $this->wjs['pgtf'] = $this->jadwalData['pgtfonbank'];
        $this->wjs['pgcouple'] = $this->jadwalData['pgconbank'];
        $this->wjs['shortentry'] = $this->jadwalData['seonbank'];
        $this->wjs['esai'] = $this->jadwalData['esaionbank'];
        // Set up some basic data
        $this->jadwalData['jumlah_soal_pgs'] = $this->wjs['pg'] + $this->wjs['pgcouple'] + $this->wjs['pgmulti'] + $this->wjs['pgtf'];
        if ($this->jadwalData['jumlah_soal_pgs']) {
            $this->js[] = "{$this->jadwalData['jumlah_soal_pgs']} PG";
        }
        if ($this->wjs['shortentry']) {
            $this->js[] = "{$this->wjs['shortentry']} SE";
        }
        if ($this->wjs['esai']) {
            $this->js[] = "{$this->wjs['esai']} ESAI";
        }
        // } else {
        //     $this->wjs['pg'] = $this->jadwalData['pgcmonbank'] >= $this->wjs['pg'] && $this->wjs['pg'] > 0 ? $this->wjs['pg'] : 0;
        //     $this->wjs['pgmulti'] = $this->jadwalData['pgmonbank'] >= $this->wjs['pgmulti'] && $this->wjs['pgmulti'] > 0 ? $this->wjs['pgmulti'] : 0;
        //     $this->wjs['pgtf'] = $this->jadwalData['pgtfonbank'] >= $this->wjs['pgtf'] && $this->wjs['pgtf'] > 0 ? $this->wjs['pgtf'] : 0;
        //     $this->wjs['pgcouple'] = $this->jadwalData['pgconbank'] >= $this->wjs['pgcouple'] && $this->wjs['pgcouple'] > 0 ? $this->wjs['pgcouple'] : 0;
        //     $this->wjs['shortentry'] = $this->jadwalData['seonbank'] >= $this->wjs['shortentry'] && $this->wjs['shortentry'] > 0 ? $this->wjs['shortentry'] : 0;
        //     $this->wjs['esai'] = $this->jadwalData['esaionbank'] >= $this->wjs['esai'] && $this->wjs['esai'] > 0 ? $this->wjs['esai'] : 0;
        // }
        $this->setHeader();
        $this->currentRow = $this->SNstartRow + 1;
        $this->worksheet = $this->spreadsheet->createSheet()->setTitle("Analisis");
        if ($this->wjs['esai'] > 0) {
            $this->setEsai();
            $rowEsai = 2;
            $this->jawabanEsai = db_connect()->table('jawaban_esai_ujian_siswa jeus');
            $this->jawabanEsai->join('ujian_siswa us', 'us.id=jeus.ujian_id');
            $this->jawabanEsai->join('soal_data sd', 'sd.id=jeus.soal_id');
            $this->jawabanEsai->where('us.jadwal_id', $this->jadwalData['id']);
            $this->jawabanEsai->select('us.siswa_id,sd.question,jeus.esai,jeus.nilai');
            $this->jawabanEsai = $this->jawabanEsai->get()->getResultArray();
        }
        foreach ($this->UjianData as $key => $value) {
            $this->cs(0);
            $this->tdp_pg = 0;
            $this->fdp_pg = 0;
            $this->tdp_se = 0;
            $this->fdp_se = 0;
            $examTime = "-";
            if ($value['start_at'] && $value['status']) {
                $examTime = "";
                $times = secondsToTime($value['end_at'] - $value['start_at']);
                if ($times['d']) $examTime .= $times['d'] . ' Hari ';
                if ($times['h']) $examTime .= $times['h'] . ' Jam ';
                if ($times['m']) $examTime .= $times['m'] . ' Menit ';
                if ($times['s']) $examTime .= $times['s'] . ' Detik ';
            }
            $pgb = $value['soal_pg_benar'] + $value['soal_pgmulti_benar'] + $value['soal_pgtf_benar'] + $value['soal_pgcouple_benar'];
            $this->worksheet
                ->setCellValue("A{$this->currentRow}", $key + 1)
                ->setCellValue("B{$this->currentRow}", $value['nomor_induk'])
                ->setCellValue("C{$this->currentRow}", $value['nama'])
                ->setCellValue("D{$this->currentRow}", $value['nama_kelas'])
                ->setCellValue("E{$this->currentRow}", $value['status'] == 0 ? 'Belum Dikerjakan' : ($value['status'] == 1 ? 'Sedang Dikerjakan' : 'Sudah Dikerjakan'))
                ->setCellValue("F{$this->currentRow}", $examTime)
                ->setCellValue("G{$this->currentRow}", $pgb)
                ->setCellValue("H{$this->currentRow}", ($value['status'] == 2 ? $value['total_pg'] : $this->jadwalData['jumlah_soal_pgs']) - $pgb);
            $this->wjcol['c'] = $this->wjcol['a'];
            switch ($this->default_point()) {
                case 1:
                    $this->setResultPGCommon($value['id']);
                    $this->setResultPGMulti($value['id']);
                    $this->setResultPGTF($value['id']);
                    $this->setResultPGCouple($value['id']);
                    $this->setResultShortEntry($value['id']);
                    $this->worksheet
                        ->setCellValue("I{$this->currentRow}", $this->tdp_pg)
                        ->setCellValue("J{$this->currentRow}", $this->fdp_pg)
                        ->setCellValue("K{$this->currentRow}", $value['soal_shortentry_benar'])
                        ->setCellValue("L{$this->currentRow}", ($value['status'] == 2 ? $value['total_shortentry'] : $this->wjs['shortentry']) - $value['soal_shortentry_benar'])
                        ->setCellValue("M{$this->currentRow}", $this->tdp_se)
                        ->setCellValue("N{$this->currentRow}", $this->fdp_se)
                        ->setCellValue("O{$this->currentRow}", $value['nilai_pg'])
                        ->setCellValue("P{$this->currentRow}", $value['nilai_esai'])
                        ->setCellValue("Q{$this->currentRow}", "=O{$this->currentRow}+P{$this->currentRow}");
                    break;
                case 2:
                    $this->setResultPGCommon($value['id']);
                    $this->setResultPGMulti($value['id']);
                    $this->setResultPGTF($value['id']);
                    $this->setResultPGCouple($value['id']);
                    $this->worksheet
                        ->setCellValue("I{$this->currentRow}", $this->tdp_pg)
                        ->setCellValue("J{$this->currentRow}", $this->fdp_pg)
                        ->setCellValue("K{$this->currentRow}", $value['soal_shortentry_benar'])
                        ->setCellValue("L{$this->currentRow}", ($value['status'] == 2 ? $value['total_shortentry'] : $this->wjs['shortentry']) - $value['soal_shortentry_benar'])
                        ->setCellValue("M{$this->currentRow}", $value['nilai_pg'])
                        ->setCellValue("N{$this->currentRow}", $value['nilai_esai'])
                        ->setCellValue("O{$this->currentRow}", "=M{$this->currentRow}+N{$this->currentRow}");
                    break;
                case 3:
                    $this->setResultShortEntry($value['id']);
                    $this->worksheet
                        ->setCellValue("I{$this->currentRow}", $value['soal_shortentry_benar'])
                        ->setCellValue("J{$this->currentRow}", ($value['status'] == 2 ? $value['total_shortentry'] : $this->wjs['shortentry']) - $value['soal_shortentry_benar'])
                        ->setCellValue("K{$this->currentRow}", $this->tdp_se)
                        ->setCellValue("L{$this->currentRow}", $this->fdp_se)
                        ->setCellValue("M{$this->currentRow}", $value['nilai_pg'])
                        ->setCellValue("N{$this->currentRow}", $value['nilai_esai'])
                        ->setCellValue("O{$this->currentRow}", "=M{$this->currentRow}+N{$this->currentRow}");
                    break;
                case 4:
                    $this->worksheet
                        ->setCellValue("I{$this->currentRow}", $value['soal_shortentry_benar'])
                        ->setCellValue("J{$this->currentRow}", ($value['status'] == 2 ? $value['total_shortentry'] : $this->wjs['shortentry']) - $value['soal_shortentry_benar'])
                        ->setCellValue("K{$this->currentRow}", $value['nilai_pg'])
                        ->setCellValue("L{$this->currentRow}", $value['nilai_esai'])
                        ->setCellValue("M{$this->currentRow}", "=K{$this->currentRow}+L{$this->currentRow}");
                    break;
            }
            $this->currentRow++;
            if ($this->wjs['esai'] > 0) {
                $this->cs(2);
                $this->worksheet
                    ->setCellValue("A{$rowEsai}", $key + 1)
                    ->setCellValue("B{$rowEsai}", $value['nama'])
                    ->setCellValue("C{$rowEsai}", $value['nomor_induk'])
                    ->setCellValue("D{$rowEsai}", $value['nama_kelas']);
                $esai = $this->getJawabanEsaiByUserID($value['siswa_id']);
                if (!$esai) {
                    $this->worksheet
                        ->setCellValue("E{$rowEsai}", "X")
                        ->getStyle("E{$rowEsai}")->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB("D3D3D3");
                    $this->worksheet
                        ->setCellValue("F{$rowEsai}", "X")
                        ->getStyle("F{$rowEsai}")->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB("D3D3D3");
                    $this->worksheet
                        ->setCellValue("G{$rowEsai}", "0")
                        ->getStyle("G{$rowEsai}")->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB("D3D3D3");
                    $rowEsai++;
                } elseif ($esai) {
                    $er = $rowEsai + count($esai) - 1;
                    $this->worksheet
                        ->mergeCells("A{$rowEsai}:A{$er}")
                        ->mergeCells("B{$rowEsai}:B{$er}")
                        ->mergeCells("C{$rowEsai}:C{$er}")
                        ->mergeCells("D{$rowEsai}:D{$er}");
                }
                foreach ($esai as $ke => $e) {
                    $this->worksheet
                        ->setCellValue("E{$rowEsai}", strip_tags($this->html->toRichTextObject($e['question'])))
                        ->setCellValue("F{$rowEsai}", $e['esai'])
                        ->setCellValue("G{$rowEsai}", $e['nilai']);
                    $rowEsai++;
                }
            }
        }
        $this->cs(0);
        for ($i = 0; $i <= $this->wjcol['c']; $i++) {
            $this->worksheet->getColumnDimensionByColumn($i)->setAutoSize(true);
        }
        $this->worksheet
            ->getStyleByColumnAndRow(1, $this->default_point() == 4 ? $this->SNstartRow : $this->SNstartRow - 2, $this->wjcol['c'] - 1, $this->currentRow - 1)
            ->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN)
            ->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('00000000'));
        $this->setAnalisis();
        $this->worksheet = $this->spreadsheet->createSheet()->setTitle("Pelanggaran");
        $this->cs($this->wjs['esai'] > 0 ? 3 : 2);
        $this->worksheet->setCellValue("A1", "No");
        $this->worksheet->setCellValue("B1", "Nomor Induk");
        $this->worksheet->setCellValue("C1", "Nama");
        $this->worksheet->setCellValue("D1", "Kelas");
        $this->worksheet->setCellValue("E1", "Pelanggaran");
        $rs = 2;
        foreach ($this->UjianData as $k => $ud) {
            $this->worksheet->setCellValue("A" . ($rs + $k), $k + 1);
            $this->worksheet->setCellValue("B" . ($rs + $k), $ud['nomor_induk']);
            $this->worksheet->setCellValue("C" . ($rs + $k), $ud['nama']);
            $this->worksheet->setCellValue("D" . ($rs + $k), $ud['nama_kelas']);
            $this->worksheet->setCellValue("E" . ($rs + $k), $ud['hasCheating'] == '0' ? '-' : 'Telah Melakukan Pelanggaran');
            $this->worksheet->getColumnDimensionByColumn($k + 1)->setAutoSize(true);
        }
        $this->cs(0);
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($this->spreadsheet, "Xlsx");
        $writer->save($this->path);
        return $this->response->download($this->path, null);
    }

    private function getJawabanEsaiByUserID($uid)
    {
        $esai = [];
        foreach ($this->jawabanEsai as $je) {
            if ($je['siswa_id'] == $uid) {
                $esai[] = $je;
            }
        }
        return $esai;
    }
    private function setEsai()
    {
        $this->worksheet = $this->spreadsheet->createSheet()->setTitle("Jawaban Esai");
        $this->worksheet
            ->setCellValue("A1", "No")
            ->setCellValue("B1", "Nama")
            ->setCellValue("C1", "Nomor Induk")
            ->setCellValue("D1", "Kelas")
            ->setCellValue("E1", "Soal")
            ->setCellValue("F1", "Jawaban")
            ->setCellValue("G1", "Point");
    }
    private function setAnalisis()
    {
        $this->cs(1);
        $this->worksheet
            ->setCellValue("A1", "No")
            ->setCellValue("B1", "Soal")
            ->setCellValue("C1", "Menjawab Benar (Σx)")
            ->setCellValue("D1", "Jumlah Peserta(N)")
            ->setCellValue("E1", "True Point (Sm)")
            ->setCellValue("F1", "Proporsi Soal (p)")
            ->setCellValue("G1", "Kategori");
        $no = 1;
        foreach ($this->pgcmsd as $key => $value) {
            $this->worksheet
                ->setCellValue("A{$this->SAstartRow}", $no++)
                ->setCellValue("B{$this->SAstartRow}", strip_tags($this->html->toRichTextObject($value['question'])))
                ->setCellValue("C{$this->SAstartRow}", $value['peserta_benar'])
                ->setCellValue("D{$this->SAstartRow}", $value['peserta_mengerjakan'])
                ->setCellValue("E{$this->SAstartRow}", $value['true_default_point'])
                ->setCellValue("F{$this->SAstartRow}", "=C{$this->SAstartRow}/(E{$this->SAstartRow}*D{$this->SAstartRow})")
                ->setCellValue("G{$this->SAstartRow}", "=IF(F{$this->SAstartRow}<0.33,\"SUKAR\",IF(F{$this->SAstartRow}<0.67,\"SEDANG\",\"MUDAH\"))");
            $this->SAstartRow++;
        }
        foreach ($this->pgmsd as $key => $value) {
            $this->worksheet
                ->setCellValue("A{$this->SAstartRow}", $no++)
                ->setCellValue("B{$this->SAstartRow}", strip_tags($this->html->toRichTextObject($value['question'])))
                ->setCellValue("C{$this->SAstartRow}", $value['peserta_benar'])
                ->setCellValue("D{$this->SAstartRow}", $value['peserta_mengerjakan'])
                ->setCellValue("E{$this->SAstartRow}", $value['true_default_point'])
                ->setCellValue("F{$this->SAstartRow}", "=C{$this->SAstartRow}/(E{$this->SAstartRow}*D{$this->SAstartRow})")
                ->setCellValue("G{$this->SAstartRow}", "=IF(F{$this->SAstartRow}<0.33,\"SUKAR\",IF(F{$this->SAstartRow}<0.67,\"SEDANG\",\"MUDAH\"))");
            $this->SAstartRow++;
        }
        foreach ($this->pgtfsd as $key => $value) {
            $this->worksheet
                ->setCellValue("A{$this->SAstartRow}", $no++)
                ->setCellValue("B{$this->SAstartRow}", strip_tags($this->html->toRichTextObject($value['question'])))
                ->setCellValue("C{$this->SAstartRow}", $value['peserta_benar'])
                ->setCellValue("D{$this->SAstartRow}", $value['peserta_mengerjakan'])
                ->setCellValue("E{$this->SAstartRow}", $value['true_default_point'])
                ->setCellValue("F{$this->SAstartRow}", "=C{$this->SAstartRow}/(E{$this->SAstartRow}*D{$this->SAstartRow})")
                ->setCellValue("G{$this->SAstartRow}", "=IF(F{$this->SAstartRow}<0.33,\"SUKAR\",IF(F{$this->SAstartRow}<0.67,\"SEDANG\",\"MUDAH\"))");
            $this->SAstartRow++;
        }
        foreach ($this->pgcsd as $key => $value) {
            $this->worksheet
                ->setCellValue("A{$this->SAstartRow}", $no++)
                ->setCellValue("B{$this->SAstartRow}", strip_tags($this->html->toRichTextObject($value['question'])))
                ->setCellValue("C{$this->SAstartRow}", $value['peserta_benar'])
                ->setCellValue("D{$this->SAstartRow}", $value['peserta_mengerjakan'])
                ->setCellValue("E{$this->SAstartRow}", $value['true_default_point'])
                ->setCellValue("F{$this->SAstartRow}", "=C{$this->SAstartRow}/(E{$this->SAstartRow}*D{$this->SAstartRow})")
                ->setCellValue("G{$this->SAstartRow}", "=IF(F{$this->SAstartRow}<0.33,\"SUKAR\",IF(F{$this->SAstartRow}<0.67,\"SEDANG\",\"MUDAH\"))");
            $this->SAstartRow++;
        }
        foreach ($this->sesd as $key => $value) {
            $this->worksheet
                ->setCellValue("A{$this->SAstartRow}", $no++)
                ->setCellValue("B{$this->SAstartRow}", strip_tags($this->html->toRichTextObject($value['question'])))
                ->setCellValue("C{$this->SAstartRow}", $value['peserta_benar'])
                ->setCellValue("D{$this->SAstartRow}", $value['peserta_mengerjakan'])
                ->setCellValue("E{$this->SAstartRow}", $value['true_default_point'])
                ->setCellValue("F{$this->SAstartRow}", "=C{$this->SAstartRow}/(E{$this->SAstartRow}*D{$this->SAstartRow})")
                ->setCellValue("G{$this->SAstartRow}", "=IF(F{$this->SAstartRow}<0.33,\"SUKAR\",IF(F{$this->SAstartRow}<0.67,\"SEDANG\",\"MUDAH\"))");
            $this->SAstartRow++;
        }
        for ($i = 1; $i < 8; $i++) {
            if ($i == 2) continue;
            $this->worksheet->getColumnDimensionByColumn($i)->setAutoSize(true);
        }
        $this->worksheet
            ->getStyleByColumnAndRow(1, 1, 7, $this->SAstartRow - 1)
            ->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN)
            ->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('00000000'));
    }
    /**
     * Change Sheet
     * @param int $index = 0
     */
    private function cs($index = 0)
    {
        $this->worksheet = $this->spreadsheet->setActiveSheetIndex($index);
    }
    private function setResultPGCommon($ujian_id)
    {
        foreach ($this->pgcmsd as $pgcmsdk => $pgcmsdv) {
            $pgj = $this->getPGJawaban($pgcmsdv['soal_id'], $ujian_id);
            if ($pgj) {
                $this->pgcmsd[$pgcmsdk]['peserta_mengerjakan']++;
                if ($pgj['is_key']) {
                    $this->pgcmsd[$pgcmsdk]['peserta_benar']++;
                    $this->tdp_pg += (float) $pgcmsdv['true_default_point'];
                } else {
                    $this->fdp_pg += (float) $pgcmsdv['false_default_point'];
                }
            }
            $this->worksheet
                ->setCellValueByColumnAndRow($this->wjcol['c'], $this->currentRow, $pgj == null ? "X" : $pgj['index'])
                ->getStyleByColumnAndRow($this->wjcol['c'], $this->currentRow)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB($pgj == null ? "D3D3D3" : ($pgj['is_key'] ? 'FF00FF00' : 'FFFF0000'));
            $this->wjcol['c']++;
        }
    }
    private function setResultPGMulti($ujian_id)
    {
        foreach ($this->pgmsd as $pgmsdk => $pgmsdv) {
            $pgj = $this->getPGMultiJawaban($pgmsdv['soal_id'], $ujian_id);
            if ($pgj['index']) {
                $this->pgmsd[$pgmsdk]['peserta_mengerjakan']++;
                if ($pgj['match']) {
                    $this->pgmsd[$pgmsdk]['peserta_benar']++;
                    $this->tdp_pg += (float) $pgmsdv['true_default_point'];
                } else {
                    $this->fdp_pg += (float) $pgmsdv['false_default_point'];
                }
            }
            $this->worksheet
                ->setCellValueByColumnAndRow($this->wjcol['c'], $this->currentRow, !$pgj['index'] ? "X" : implode(',', $pgj['index']))
                ->getStyleByColumnAndRow($this->wjcol['c'], $this->currentRow)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB(!$pgj['index'] ? "D3D3D3" : ($pgj['match'] ? 'FF00FF00' : 'FFFF0000'));
            $this->wjcol['c']++;
        }
    }
    private function setResultPGTF($ujian_id)
    {
        foreach ($this->pgtfsd as $pgtfsdk => $pgtfsdv) {
            $pgj = $this->getPGTFJawaban($pgtfsdv['soal_id'], $ujian_id);
            if ($pgj['index']) {
                $this->pgtfsd[$pgtfsdk]['peserta_mengerjakan']++;
                if ($pgj['match']) {
                    $this->pgtfsd[$pgtfsdk]['peserta_benar']++;
                    $this->tdp_pg += (float) $pgtfsdv['true_default_point'];
                } else {
                    $this->fdp_pg += (float) $pgtfsdv['false_default_point'];
                }
            }
            $this->worksheet
                ->setCellValueByColumnAndRow($this->wjcol['c'], $this->currentRow, !$pgj['index'] ? "X" : implode(',', $pgj['index']))
                ->getStyleByColumnAndRow($this->wjcol['c'], $this->currentRow)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB(!$pgj['index'] ? "D3D3D3" : ($pgj['match'] ? 'FF00FF00' : 'FFFF0000'));
            $this->wjcol['c']++;
        }
    }
    private function setResultPGCouple($ujian_id)
    {
        foreach ($this->pgcsd as $pgcsdk => $pgcsdv) {
            $pgj = $this->getPGCoupleJawaban($pgcsdv['soal_id'], $ujian_id);
            if ($pgj['index']) {
                $this->pgcsd[$pgcsdk]['peserta_mengerjakan']++;
                if ($pgj['match']) {
                    $this->pgcsd[$pgcsdk]['peserta_benar']++;
                    $this->tdp_pg += (float) $pgcsdv['true_default_point'];
                } else {
                    $this->fdp_pg += (float) $pgcsdv['false_default_point'];
                }
            }
            $this->worksheet
                ->setCellValueByColumnAndRow($this->wjcol['c'], $this->currentRow, !$pgj['index'] ? "X" : implode(',', $pgj['index']))
                ->getStyleByColumnAndRow($this->wjcol['c'], $this->currentRow)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB(!$pgj['index'] ? "D3D3D3" : ($pgj['match'] ? 'FF00FF00' : 'FFFF0000'));
            $this->wjcol['c']++;
        }
    }
    private function setResultShortEntry($ujian_id)
    {
        foreach ($this->sesd as $sesdk => $sesdv) {
            $pgj = $this->getSEJawaban($sesdv['soal_id'], $ujian_id);
            if ($pgj) {
                $this->sesd[$sesdk]['peserta_mengerjakan']++;
                if ($pgj['match']) {
                    $this->sesd[$sesdk]['peserta_benar']++;
                    $this->tdp_se += (float) $sesdv['true_default_point'];
                } else {
                    $this->fdp_se += (float) $sesdv['false_default_point'];
                }
            }
            $this->worksheet
                ->setCellValueByColumnAndRow($this->wjcol['c'], $this->currentRow, !$pgj ? "X" : str_replace("=", "`=", $pgj['index'] ?? ''))
                ->getStyleByColumnAndRow($this->wjcol['c'], $this->currentRow)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB(!$pgj ? "D3D3D3" : ($pgj['match'] ? 'FF00FF00' : 'FFFF0000'));
            $this->wjcol['c']++;
        }
    }
    private function setHeader()
    {
        $srp = $this->SNstartRow + 2;
        switch ($this->default_point()) {
            case 1:
                $this->wjcol['c'] = 18;
                $this->wjcol['a'] = 18;
                if ($this->wjs['pg']) $this->loadPGCommon();
                if ($this->wjs['pgmulti']) $this->loadPGMulti();
                if ($this->wjs['pgtf']) $this->loadPGTF();
                if ($this->wjs['pgcouple']) $this->loadPGCouple();
                if ($this->wjs['shortentry']) $this->loadShortEntry();
                $this->worksheet
                    ->setCellValue("I{$this->SNstartRow}", "Poin PG\nBenar")->mergeCells("I{$this->SNstartRow}:I{$srp}")
                    ->setCellValue("J{$this->SNstartRow}", "Poin PG\nSalah")->mergeCells("J{$this->SNstartRow}:J{$srp}")
                    ->setCellValue("K{$this->SNstartRow}", 'SE Benar')->mergeCells("K{$this->SNstartRow}:K{$srp}")
                    ->setCellValue("L{$this->SNstartRow}", 'SE Salah')->mergeCells("L{$this->SNstartRow}:L{$srp}")
                    ->setCellValue("M{$this->SNstartRow}", "Poin SE\nBenar")->mergeCells("M{$this->SNstartRow}:M{$srp}")
                    ->setCellValue("N{$this->SNstartRow}", "Poin SE\nSalah")->mergeCells("N{$this->SNstartRow}:N{$srp}")
                    ->setCellValue("O{$this->SNstartRow}", 'Nilai PG+SE')->mergeCells("O{$this->SNstartRow}:O{$srp}")
                    ->setCellValue("P{$this->SNstartRow}", 'Nilai Esai')->mergeCells("P{$this->SNstartRow}:P{$srp}")
                    ->setCellValue("Q{$this->SNstartRow}", 'Nilai Akhir')->mergeCells("Q{$this->SNstartRow}:Q{$srp}")
                    ->setCellValue("R{$this->SNstartRow}", 'Jawaban')->mergeCellsByColumnAndRow($this->wjcol['a'], $this->SNstartRow, $this->wjcol['c'] - 1, $this->SNstartRow);
                break;
            case 2:
                $this->wjcol['c'] = 16;
                $this->wjcol['a'] = 16;
                if ($this->wjs['pg']) $this->loadPGCommon();
                if ($this->wjs['pgmulti']) $this->loadPGMulti();
                if ($this->wjs['pgtf']) $this->loadPGTF();
                if ($this->wjs['pgcouple']) $this->loadPGCouple();
                $this->worksheet
                    ->setCellValue("I{$this->SNstartRow}", "Poin PG\nBenar")->mergeCells("I{$this->SNstartRow}:I{$srp}")
                    ->setCellValue("J{$this->SNstartRow}", "Poin PG\nSalah")->mergeCells("J{$this->SNstartRow}:J{$srp}")
                    ->setCellValue("K{$this->SNstartRow}", 'SE Benar')->mergeCells("K{$this->SNstartRow}:K{$srp}")
                    ->setCellValue("L{$this->SNstartRow}", 'SE Salah')->mergeCells("L{$this->SNstartRow}:L{$srp}")
                    ->setCellValue("M{$this->SNstartRow}", 'Nilai PG+SE')->mergeCells("M{$this->SNstartRow}:M{$srp}")
                    ->setCellValue("N{$this->SNstartRow}", 'Nilai Esai')->mergeCells("N{$this->SNstartRow}:N{$srp}")
                    ->setCellValue("O{$this->SNstartRow}", 'Nilai Akhir')->mergeCells("O{$this->SNstartRow}:O{$srp}")
                    ->setCellValue("P{$this->SNstartRow}", 'Jawaban')->mergeCellsByColumnAndRow($this->wjcol['a'], $this->SNstartRow, $this->wjcol['c'] - 1, $this->SNstartRow);
                break;
            case 3:
                $this->wjcol['c'] = 16;
                $this->wjcol['a'] = 16;
                $this->loadShortEntry();
                $this->worksheet
                    ->setCellValue("I{$this->SNstartRow}", 'SE Benar')->mergeCells("I{$this->SNstartRow}:I{$srp}")
                    ->setCellValue("J{$this->SNstartRow}", 'SE Salah')->mergeCells("J{$this->SNstartRow}:J{$srp}")
                    ->setCellValue("K{$this->SNstartRow}", "Poin SE\nBenar")->mergeCells("K{$this->SNstartRow}:K{$srp}")
                    ->setCellValue("L{$this->SNstartRow}", "Poin SE\nSalah")->mergeCells("L{$this->SNstartRow}:L{$srp}")
                    ->setCellValue("M{$this->SNstartRow}", 'Nilai PG+SE')->mergeCells("M{$this->SNstartRow}:M{$srp}")
                    ->setCellValue("N{$this->SNstartRow}", 'Nilai Esai')->mergeCells("N{$this->SNstartRow}:N{$srp}")
                    ->setCellValue("O{$this->SNstartRow}", 'Nilai Akhir')->mergeCells("O{$this->SNstartRow}:O{$srp}")
                    ->setCellValue("P{$this->SNstartRow}", 'Jawaban')->mergeCellsByColumnAndRow($this->wjcol['a'], $this->SNstartRow, $this->wjcol['c'] - 1, $this->SNstartRow);
                break;
            case 4:
                $this->worksheet
                    ->setCellValue("I{$this->SNstartRow}", 'SE Benar')
                    ->setCellValue("J{$this->SNstartRow}", 'SE Salah')
                    ->setCellValue("K{$this->SNstartRow}", 'Nilai PG+SE')
                    ->setCellValue("L{$this->SNstartRow}", 'Nilai Esai')
                    ->setCellValue("M{$this->SNstartRow}", 'Nilai Akhir');
                break;
        }
        $this->worksheet
            ->mergeCellsByColumnAndRow(1, 1, $this->wjcol['c'] - 1, 1)
            ->mergeCellsByColumnAndRow(1, 2, $this->wjcol['c'] - 1, 2)
            ->mergeCellsByColumnAndRow(1, 3, $this->wjcol['c'] - 1, 3)
            ->setCellValue('A1', "Kode Mapel: {$this->jadwalData['kode_mapel']}")
            ->setCellValue('A2', "Jumlah Soal: " . implode('|', $this->js))
            ->setCellValue('A3', "Bobot Soal: True/False {$this->jadwalData['bobot_tf']}% | Esai {$this->jadwalData['bobot_esai']}%")
            ->setCellValue("A{$this->SNstartRow}", 'No')
            ->setCellValue("B{$this->SNstartRow}", 'Nomor Induk')
            ->setCellValue("C{$this->SNstartRow}", 'Nama')
            ->setCellValue("D{$this->SNstartRow}", 'Kelas')
            ->setCellValue("E{$this->SNstartRow}", 'Status')
            ->setCellValue("F{$this->SNstartRow}", 'Lama Ujian')
            ->setCellValue("G{$this->SNstartRow}", 'PG Benar')
            ->setCellValue("H{$this->SNstartRow}", 'PG Salah');
        if ($this->default_point() != 4) {
            $this->worksheet
                ->mergeCells("A{$this->SNstartRow}:A{$srp}")
                ->mergeCells("B{$this->SNstartRow}:B{$srp}")
                ->mergeCells("C{$this->SNstartRow}:C{$srp}")
                ->mergeCells("D{$this->SNstartRow}:D{$srp}")
                ->mergeCells("E{$this->SNstartRow}:E{$srp}")
                ->mergeCells("F{$this->SNstartRow}:F{$srp}")
                ->mergeCells("G{$this->SNstartRow}:G{$srp}")
                ->mergeCells("H{$this->SNstartRow}:H{$srp}");
            $this->SNstartRow = $srp;
        }
    }
    private function getPGCoupleJawaban($soal_id, $ujian_id)
    {
        $opt = ['index' => [],  'match' => false];
        foreach ($this->pgcsud as $k => $v) {
            if ($v['id'] == $ujian_id && $v['soal_id'] == $soal_id) {
                $opt['responds'] = explode(',', $v['opsi_couple'] ?? '');
                $key = $this->getPGCoupleOptions($v['soal_id'], $v['opsi_couple']);
                if (!$key) continue;
                $opt['index'] = $key['indexs'];
                $opt['match'] = !array_diff($key['correctCouples'], explode(',', $v['opsi_couple'] ?? ''));
                break;
            }
        }
        return $opt;
    }

    private function getPGCoupleOptions($soal_id, $opsi_couple)
    {
        $alpha = ["A", "B", "C", "D", "E"];
        $keys = ["options" => [], 'indexs' => [], "couples" => [], 'correctCouples' => []];
        foreach ($this->pgcsd as $k => $v) {
            if ($v['soal_id'] == $soal_id) {
                $keys['correctCouples'] = explode(',', $v['correct_options'] ?? '');
                foreach (explode(',', $v['options'] ?? '') as $kk => $vv) {
                    if ($kk > count($alpha) - 1) continue;
                    $vv = explode('-', $vv ?? '');
                    $keys["options"][$vv[0]] = $alpha[$kk];
                }
                foreach (explode(',', $v['couple'] ?? '') as $kk => $vv) {
                    if ($kk > count($alpha) - 1) continue;
                    $keys['couples'][$vv] = $alpha[$kk];
                }
                $responds = [];
                foreach (explode(',', $opsi_couple ?? '') as $koc => $voc) {
                    if (!$voc) continue;
                    $voc = explode('-', $voc ?? '');
                    $responds[$voc[0]] = $voc[1];
                }
                asort($responds);
                foreach ($responds as $key => $value) {
                    $keys['indexs'][] = $keys['couples'][$value] . "-" . $keys['options'][$key];
                }
                return $keys;
            }
        }
        return null;
    }
    private function getPGMultiJawaban($soal_id, $ujian_id)
    {
        $opt = ['index' => [], 'match' => false];
        foreach ($this->pgmsud as $k => $v) {
            if ($v['id'] == $ujian_id && $v['soal_id'] == $soal_id) {
                $key = $this->getPGMultiOptions($v['soal_id'], $v['opsi_id']);
                if (!$key) continue;
                $opt['index'] = $key['indexs'];
                $opt['match'] = !array_diff($key['keys'], $key['indexs']) && count($key['keys']) == count($key['indexs']);
            }
        }
        return $opt;
    }
    private function getPGMultiOptions($soal_id, $opsi_id)
    {
        $alpha = ["A", "B", "C", "D", "E"];
        $keys = ["options" => [], "indexs" => [], 'keys' => []];
        foreach ($this->pgmsd as $k => $v) {
            if ($v['soal_id'] == $soal_id) {
                $options = explode(',', $v['options']);
                foreach ($options as $kk => $vv) {
                    if ($kk > count($alpha) - 1) continue;
                    $vv = explode('-', $vv);
                    foreach (explode(',', $opsi_id ?? '') as $ko => $vo) {
                        if ($vv[0] == $vo) {
                            $keys["indexs"][] = $alpha[$kk];
                        }
                    }
                    $keys["options"][$vv[0]] = $alpha[$kk];
                    if ($vv[1] == '1') $keys['keys'][] = $alpha[$kk];
                }
                return $keys;
            }
        }
        return null;
    }
    private function getPGTFJawaban($soal_id, $ujian_id)
    {
        $opt = ['index' => [], 'match' => false];
        foreach ($this->pgtfsud as $k => $v) {
            if ($v['id'] == $ujian_id && $v['soal_id'] == $soal_id) {
                $key = $this->getPGTFOptions($v['soal_id'], $v['opsi_id']);
                if (!$key) continue;
                $opt['index'] = $key['indexs'];
                $opt['match'] = !array_diff($key['keys'], $key['indexs']) && count($key['keys']) == count($key['indexs']);
            }
        }
        return $opt;
    }
    private function getPGTFOptions($soal_id, $opsi_id)
    {
        $alpha = ["A", "B", "C", "D", "E"];
        $keys = ["indexs" => [], 'keys' => []];
        foreach ($this->pgtfsd as $k => $v) {
            if ($v['soal_id'] == $soal_id) {
                $options = explode(',', $v['options']);
                foreach ($options as $kk => $vv) {
                    if ($kk > count($alpha) - 1) continue;
                    $vv = explode('-', $vv);
                    foreach (explode(',', $opsi_id ?? '') as $ko => $vo) {
                        $vo = explode('-', $vo);
                        if ($vv[0] == $vo[0]) {
                            $keys["indexs"][] = $alpha[$kk] . "-" . $vo[1];
                        }
                    }
                    $keys['keys'][$kk] = $alpha[$kk] . "-" . $vv[1];
                }
                return $keys;
            }
        }
        return null;
    }
    private function getSEJawaban($soal_id, $ujian_id)
    {
        foreach ($this->sesud as $k => $v) {
            if ($v['id'] == $ujian_id && $v['soal_id'] == $soal_id)  return [
                'match' => strtolower($v['respond'] ?? '') == strtolower($v['shortentry'] ?? ''),
                'index' => $v['respond'],
            ];
        }
        return null;
    }
    private function getPGJawaban($soal_id, $ujian_id)
    {
        foreach ($this->pgcmsud as $k => $v) {
            if ($v['id'] == $ujian_id && $v['soal_id'] == $soal_id) {
                $key = $this->getPGOptions($v['soal_id'], $v['opsi_id']);
                if (!$key) return null;
                return $key;
            }
        }
        return null;
    }
    private function getPGOptions($soal_id, $opsi_id)
    {
        $alpha = ["A", "B", "C", "D", "E"];
        foreach ($this->pgcmsd as $k => $v) {
            if ($v['soal_id'] == $soal_id) {
                $options = explode(',', $v['options'] ?? '');
                foreach ($options as $kk => $vv) {
                    if ($kk > count($alpha) - 1) continue;
                    $vv = explode('-', $vv ?? '');
                    if ($vv[0] == $opsi_id) {
                        return ['id' => $vv[0], 'index' => $alpha[$kk], 'is_key' => $vv[1]];
                    }
                }
            }
        }
        return null;
    }
    private function default_point()
    {
        if (($this->wjs['pg'] || $this->wjs['pgmulti'] || $this->wjs['pgtf'] || $this->wjs['pgcouple']) && $this->wjs['shortentry']) {
            return 1;
        } elseif ($this->wjs['pg'] || $this->wjs['pgmulti'] || $this->wjs['pgtf'] || $this->wjs['pgcouple']) {
            return 2;
        } elseif ($this->wjs['shortentry']) {
            return 3;
        } else {
            return 4;
        }
    }
    private function loadPGCommon()
    {
        $this->pgcmsd = $this->db->table('soal_data sd')->where('jenis_soal', (new BankSoal())->jenis('pg'));
        $this->pgcmsd->join('soal_opsi so', 'so.soal_id = sd.id');
        $this->pgcmsd->select("sd.id soal_id,sd.question,true_default_point,false_default_point,GROUP_CONCAT(CONCAT(so.id,'-',so.is_key)) options");
        $this->pgcmsd->groupBy('sd.id')->where('sd.bank_id', $this->jadwalData['bank_soal_id']);
        $this->pgcmsd = $this->pgcmsd->limit($this->wjs['pg'])->orderBy('so.soal_id,so.id')->get()->getResultArray();
        $this->pgcmsud = $this->db->table('jawaban_pg_ujian_siswa jpus')->select('us.id,jpus.soal_id,jpus.opsi_id');
        $this->pgcmsud->join('soal_data sd', 'sd.id = jpus.soal_id', 'left');
        $this->pgcmsud->join('ujian_siswa us', 'us.id = jpus.ujian_id')->whereIn('us.id', $this->uid);
        $this->pgcmsud = $this->pgcmsud->where('us.jadwal_id', $this->id)->get()->getResultArray();
        foreach ($this->pgcmsd as $key => $sd) {
            $this->pgcmsd[$key]['peserta_benar'] = 0;
            $this->pgcmsd[$key]['peserta_mengerjakan'] = 0;
            $ik = "";
            if ($sd['options']) {
                $iks = explode(',', $sd['options'] ?? '');
                foreach ($iks as $ikk => $ikv) {
                    $ikv = explode('-', $ikv ?? '');
                    if ($ikv[1]) $ik = $this->options[$ikk];
                }
            }
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 1, ($key + 1) . "|$ik");
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 2, "$sd[true_default_point]|$sd[false_default_point]");
        }
        $this->wjcol['c'] += $this->wjs['pg'];
    }
    private function loadPGMulti()
    {

        $this->pgmsd = $this->db->table('soal_data sd')->where('jenis_soal', (new BankSoal())->jenis('pgmulti'));
        $this->pgmsd->join('soal_opsi so', 'so.soal_id = sd.id');
        $this->pgmsd->select("sd.id soal_id,sd.question,true_default_point,false_default_point,GROUP_CONCAT(CONCAT(so.id,'-',so.is_key)) options");
        $this->pgmsd->groupBy('sd.id')->where('sd.bank_id', $this->jadwalData['bank_soal_id']);
        $this->pgmsd = $this->pgmsd->limit($this->wjs['pgmulti'])->orderBy('so.soal_id,so.id')->get()->getResultArray();

        $this->pgmsud = $this->db->table('jawaban_pgmulti_ujian_siswa jpus')->select("us.id,jpus.soal_id,GROUP_CONCAT(jpus.opsi_id) opsi_id");
        $this->pgmsud->join('soal_data sd', 'sd.id = jpus.soal_id', 'left');
        $this->pgmsud->join('ujian_siswa us', 'us.id = jpus.ujian_id')->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)");
        $this->pgmsud = $this->pgmsud->where('us.jadwal_id', $this->id)->whereIn('us.id', $this->uid)->get()->getResultArray();
        foreach ($this->pgmsd as $key => $sd) {
            $this->pgmsd[$key]['peserta_benar'] = 0;
            $this->pgmsd[$key]['peserta_mengerjakan'] = 0;
            $ik = [];
            if ($sd['options']) {
                $iks = explode(',', $sd['options'] ?? '');
                foreach ($iks as $ikk => $ikv) {
                    $ikv = explode('-', $ikv ?? '');
                    if ($ikv[1]) $ik[] = $this->options[$ikk];
                }
            }
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 1, ($key + 1) . "|" . implode(',', $ik));
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 2, "$sd[true_default_point]|$sd[false_default_point]");
        }
        $this->wjcol['c'] += $this->wjs['pgmulti'];
    }
    private function loadPGTF()
    {

        $this->pgtfsd = $this->db->table('soal_data sd')->where('jenis_soal', (new BankSoal())->jenis('pgtf'));
        $this->pgtfsd->join('soal_opsi so', 'so.soal_id = sd.id');
        $this->pgtfsd->select("sd.id soal_id,sd.question,true_default_point,false_default_point,GROUP_CONCAT(CONCAT(so.id,'-',so.is_key)) options");
        $this->pgtfsd->groupBy('sd.id')->where('sd.bank_id', $this->jadwalData['bank_soal_id']);
        $this->pgtfsd = $this->pgtfsd->limit($this->wjs['pgtf'])->orderBy('so.soal_id,so.id')->get()->getResultArray();
        foreach ($this->pgtfsd as $key => $sd) {
            if ($sd['options']) $sd['options'] = implode(',', $this->_sortPGTFO(explode(',', $sd['options'] ?? '')));
            $this->pgtfsd[$key] = $sd;
        }
        $this->pgtfsud = $this->db->table('jawaban_pgtf_ujian_siswa jpus')->select("us.id,jpus.soal_id,GROUP_CONCAT(jpus.opsi_id,'-',jpus.tf) opsi_id");
        $this->pgtfsud->join('soal_data sd', 'sd.id = jpus.soal_id', 'left');
        $this->pgtfsud->join('ujian_siswa us', 'us.id = jpus.ujian_id')->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)");
        $this->pgtfsud = $this->pgtfsud->where('us.jadwal_id', $this->id)->whereIn('us.id', $this->uid)->get()->getResultArray();
        foreach ($this->pgtfsd as $key => $sd) {
            $this->pgtfsd[$key]['peserta_benar'] = 0;
            $this->pgtfsd[$key]['peserta_mengerjakan'] = 0;
            $ik = [];
            if ($sd['options']) {
                $iks = $this->_sortPGTFO(explode(',', $sd['options'] ?? ''));
                foreach ($iks as $ikk => $ikv) {
                    $ikv = explode('-', $ikv ?? '');
                    $ik[] = $this->options[$ikk] . "-" . $ikv[1];
                }
            }
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 1, ($key + 1) . "|" . implode(',', $ik));
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 2, "$sd[true_default_point]|$sd[false_default_point]");
        }
        $this->wjcol['c'] += $this->wjs['pgtf'];
    }

    private function loadPGCouple()
    {
        $this->pgcsd = $this->db->table('soal_data sd')->where('jenis_soal', (new BankSoal())->jenis('pgcouple'));
        $this->pgcsd->join('soal_opsi so', 'so.soal_id = sd.id', 'left');
        $this->pgcsd->join('soal_data_couple sdc', 'sdc.soal_id = sd.id', 'left');
        $this->pgcsd->select("sd.id soal_id,sd.question,true_default_point,false_default_point,GROUP_CONCAT(DISTINCT CONCAT(sdc.id)) couple,GROUP_CONCAT(DISTINCT CONCAT(so.id,'-',IFNULL(so.soal_couple_id,0))) options,GROUP_CONCAT(DISTINCT CONCAT(so.id,'-',so.soal_couple_id)) correct_options");
        $this->pgcsd->groupBy('sd.id')->where('sd.bank_id', $this->jadwalData['bank_soal_id']);
        $this->pgcsd = $this->pgcsd->limit($this->wjs['pgcouple'])->orderBy('so.soal_id,so.id,sdc.id')->get()->getResultArray();

        $this->pgcsud = $this->db->table('jawaban_pgcouple_ujian_siswa jpus')->select("us.id,jpus.soal_id,GROUP_CONCAT(jpus.couple_id) couple_id,GROUP_CONCAT(jpus.opsi_id) opsi_id,GROUP_CONCAT(jpus.opsi_id,'-',jpus.couple_id) opsi_couple");
        $this->pgcsud->join('soal_data sd', 'sd.id = jpus.soal_id', 'left');
        $this->pgcsud->join('ujian_siswa us', 'us.id = jpus.ujian_id')->orderBy('jpus.opsi_id,jpus.couple_id', 'ASC')->whereIn('us.id', $this->uid);
        $this->pgcsud = $this->pgcsud->where('us.jadwal_id', $this->id)->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)")->get()->getResultArray();
        foreach ($this->pgcsd as $key => $sd) {
            $this->pgcsd[$key]['peserta_benar'] = 0;
            $this->pgcsd[$key]['peserta_mengerjakan'] = 0;
            $ik = [];
            if ($sd['options']) {
                $iks = explode(',', $sd['options'] ?? '');
                foreach ($iks as $ikk => $ikv) {
                    $ikv = explode('-', $ikv ?? '');
                    foreach (explode(',', $sd['couple'] ?? '') as $ck => $cv) {
                        if ($ikv[1] == $cv) $ik[] = "{$this->options[$ck]}-{$this->options[$ikk]}";
                    }
                }
            }
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 1, ($key + 1) . "|" . implode(',', $ik));
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 2, "$sd[true_default_point]|$sd[false_default_point]");
        }
        $this->wjcol['c'] += $this->wjs['pgcouple'];
    }
    private function loadShortEntry()
    {
        $this->sesd = $this->db->table('soal_data sd')->where('jenis_soal', (new BankSoal())->jenis('shortentry'));
        $this->sesd->select("sd.id soal_id,sd.question,sd.shortentry,true_default_point,false_default_point");
        $this->sesd->groupBy('sd.id')->where('sd.bank_id', $this->jadwalData['bank_soal_id']);
        $this->sesd = $this->sesd->limit($this->wjs['shortentry'])->get()->getResultArray();
        $this->sesud = $this->db->table('jawaban_shortentry_ujian_siswa jsus')->select('us.id,jsus.soal_id,jsus.respond,sd.shortentry');
        $this->sesud->join('ujian_siswa us', 'us.id = jsus.ujian_id');
        $this->sesud->join('soal_data sd', 'sd.id=jsus.soal_id')->whereIn('us.id', $this->uid);
        $this->sesud = $this->sesud->where('us.jadwal_id', $this->id)->get()->getResultArray();
        foreach ($this->sesd as $key => $sd) {
            $this->sesd[$key]['peserta_benar'] = 0;
            $this->sesd[$key]['peserta_mengerjakan'] = 0;
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 1, ($key + 1) . "|$sd[shortentry]");
            $this->worksheet->setCellValueByColumnAndRow($this->wjcol['c'] + $key, $this->SNstartRow + 2, "$sd[true_default_point]|$sd[false_default_point]");
        }
        $this->wjcol['c'] += $this->wjs['shortentry'];
    }
    private function _sortPGTFO($arr)
    {
        $sort = [];
        foreach ($arr as $key => $value) {
            $value = explode('-', $value);
            $sort[$value[0]] = $value[1];
        }
        ksort($sort);
        $i = 0;
        $arr = [];
        foreach ($sort as $key => $value) {
            $arr[$i++] = $key . '-' . $value;
        }
        return $arr;
    }
}
