<?php

namespace App\Models\Panel\Ujian;

use App\Models\Panel\BankSoal;
use CodeIgniter\Model;

class Jadwal extends Model
{
    protected $DBGroup              = 'default';
    protected $table                = 'jadwal_ujian';
    protected $primaryKey           = 'id';
    protected $useAutoIncrement     = true;
    protected $insertID             = 0;
    protected $returnType           = 'array';
    protected $useSoftDeletes       = false;
    protected $protectFields        = true;
    protected $allowedFields        = [
        'nama', 'tahun_id', 'locked', 'draft_id', 'ruang_id', 'jenis_id', "pengawas_id",
        "multi_login", 'status', 'startDateTime', 'endDateTime', 'token', 'use_token',
        'strict', 'afktimeout', 'maxCheatCount', 'show_point', 'cache_soal',
    ];

    // Dates
    protected $useTimestamps        = false;
    protected $dateFormat           = 'datetime';
    protected $createdField         = 'created_at';
    protected $updatedField         = 'updated_at';
    protected $deletedField         = 'deleted_at';

    // Validation
    protected $validationRules      = [];
    protected $validationMessages   = [];
    protected $skipValidation       = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks       = true;
    protected $beforeInsert         = [];
    protected $afterInsert          = [];
    protected $beforeUpdate         = [];
    protected $afterUpdate          = [];
    protected $beforeFind           = [];
    protected $afterFind            = [];
    protected $beforeDelete         = [];
    protected $afterDelete          = [];
    protected $select               = [
        "jadwal_ujian.*,mru.kode kode_ruang,mru.nama nama_ruang",
        "du.nama nama_draft,mm.kode kode_mapel,mm.nama nama_mapel",
        "du.acak_soal,users.fullname nama_pengawas,du.bank_soal_id",
        "mju.kode kode_jenis,mju.nama nama_jenis,du.timeout",
        "(SELECT COUNT(*) FROM draft_soal sds WHERE sds.draft_id=du.id) jumlah_soal",
        "users.nomor_induk nomor_induk_pengawas",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=1) visible_pg",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=2) visible_esai",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=3) visible_pgmulti",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=4) visible_pgcouple",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=5) visible_shortentry",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=6) visible_pgtf",
    ];

    public function getAll($opt = [])
    {
        $this->select[] = "(SELECT GROUP_CONCAT(DISTINCT (CASE WHEN smk.kode IS NULL THEN 'Tidak Ada Kelas' ELSE CONCAT(smk.kode,' - ',smk.nama) END)) FROM m_siswa sms JOIN m_kelas smk ON smk.id=sms.kelas_id JOIN ujian_siswa sus ON sus.siswa_id=sms.id WHERE sus.jadwal_id=jadwal_ujian.id GROUP BY sus.jadwal_id) kelas";
        $this->select[] = "(SELECT COUNT(*) FROM ujian_siswa WHERE jadwal_id = jadwal_ujian.id ) total_siswa";
        $this->select[] = "(SELECT (CASE WHEN SUM((CASE WHEN ujian_siswa.soal_generated IS NOT NULL THEN 1 ELSE 0 END)) > 0 THEN 1 ELSE 0 END) FROM ujian_siswa WHERE jadwal_id = jadwal_ujian.id ) hasGenerated";
        $this->select[] = "(SELECT COUNT(*) FROM ujian_siswa sus WHERE sus.jadwal_id=jadwal_ujian.id AND sus.status != '0') siswa_hadir";
        $this->select[] = "(SELECT COUNT(*) FROM ujian_siswa sus WHERE sus.jadwal_id=jadwal_ujian.id AND sus.status = '0') tidak_hadir";
        $builder = $this->db->table($this->table);
        $builder->select(implode(',', $this->select), false);
        $builder->join("draft_ujian du", "du.id=$this->table.draft_id", "left");
        $builder->join("m_ruangujian mru", "mru.id=$this->table.ruang_id", "left");
        $builder->join("m_jenisujian mju", "mju.id=$this->table.jenis_id", "left");
        $builder->join("users", "users.id=$this->table.pengawas_id", "left");
        $builder->join("tahun_pelajaran tp", "tp.id=$this->table.tahun_id", "left");
        $builder->join("m_mapel mm", "mm.id=du.mapel_id", "left");
        $builder->join("m_banksoal mbs", "mbs.id=du.bank_soal_id", "left");
        if (isset($opt['status'])) {
            if (is_array($opt['status'])) $builder->whereIn("$this->table.status", $opt['status']);
            else $builder->where("$this->table.status", $opt['status']);
        }
        if (isset($opt['id'])) {
            if (is_array($opt['id'])) $builder->whereIn('jadwal_ujian.id', $opt['id']);
            else $builder->where("jadwal_ujian.id", $opt['id']);
        }
        if (isset($opt['draft'])) {
            $builder->where("$this->table.draft_id", $opt['draft']);
        } elseif (isset($opt['draft_id'])) {
            $builder->where("$this->table.draft_id", $opt['draft_id']);
        }
        if (isset($opt['startDate']) && isset($opt['endDate'])) {
            $builder->where("$this->table.startDateTime >=", $opt['startDate']);
            $builder->where("$this->table.endDateTime <=", $opt['endDate']);
        } else if (isset($opt['startDate'])) {
            $builder->where("$this->table.startDateTime >=", $opt['startDate']);
        } else if (isset($opt['endDate'])) {
            $builder->where("$this->table.endDateTime <=", $opt['endDate']);
        }
        if (isset($opt['tahun_id'])) {
            $builder->where("tp.id", $opt['tahun_id']);
        } elseif (isset($opt['tahun_pelajaran'])) {
            $builder->where("tp.id", $opt['tahun_pelajaran']);
        }
        if (isset($opt['jenis_id'])) {
            $builder->where("mju.id", $opt['jenis_id']);
        } elseif (isset($opt['jenis_ujian'])) {
            $builder->where("mju.id", $opt['jenis_ujian']);
        }
        if (isset($opt['tahun_active'])) $builder->where("tp.is_active", $opt['tahun_active'] ?? '1');
        if (isset($opt['user_id']) && isset($opt['pengawas_id'])) {
            $tblpid = $this->table . '.pengawas_id';
            $builder->where("(CASE WHEN mbs.user_id = $opt[user_id] THEN 1 WHEN $tblpid = $opt[pengawas_id] THEN 1 ELSE 0 END) = ", 1, false);
        } else {
            if (isset($opt['user_id'])) {
                $builder->where("mbs.user_id", $opt['user_id']);
            }
            if (isset($opt['pengawas_id'])) {
                $builder->where("$this->table.pengawas_id", $opt['pengawas_id']);
            }
        }

        return $builder->get()->getResultArray();
    }
    public function findId($id)
    {
        $this->select[] = "du.bobot_tf,du.bobot_esai";
        $builder = $this->db->table($this->table);
        $builder->select(implode(',', $this->select), false);
        $builder->join("draft_ujian du", "du.id=$this->table.draft_id", "left");
        $builder->join("m_ruangujian mru", "mru.id=$this->table.ruang_id", "left");
        $builder->join("m_jenisujian mju", "mju.id=$this->table.jenis_id", "left");
        $builder->join("users", "users.id=$this->table.pengawas_id", "left");
        $builder->join("m_mapel mm", "mm.id=du.mapel_id", "left");
        $builder->where(["$this->table.id" => $id]);
        $builder->join("m_banksoal mbs", "mbs.id=du.bank_soal_id", "left");
        if (isset($opt['user_id'])) {
            $builder->where("mbs.user_id", $opt['user_id']);
        }
        if (isset($opt['pengawas_id'])) {
            $builder->orWhere("$this->table.pengawas_id", $opt['pengawas_id']);
        }
        return $builder->get()->getRowArray();
    }

    public function peserta($jadwalId, $opt = [])
    {
        $select  = [
            "ms.id,ms.nomor_induk,ms.nama,ms.email,ms.agama,mk.id kelas_id,mk.kode kode_kelas,mk.nama nama_kelas,mt.id tingkat_id",
            "mt.kode kode_tingkat,mj.id jurusan_id,mj.kode kode_jurusan,mj.nama nama_jurusan,mru.kode kode_ruang",
        ];
        $builder = $this->db->table('m_siswa ms');
        $builder->select(implode(',', $select));
        $builder->join('ujian_siswa us', 'us.siswa_id=ms.id');
        $builder->join("kelas_data kd", "kd.kelas_id=ms.kelas_id", "left");
        $builder->join("m_kelas mk", "mk.id=kd.kelas_id", "left");
        $builder->join("m_ruangujian mru", "mru.id=ms.ruang_id", "left");
        $builder->join("m_tingkat mt", "mt.id=kd.tingkat_id", "left");
        $builder->join("m_jurusan mj", "mj.id=kd.jurusan_id", "left");
        $builder->where(['us.jadwal_id' => $jadwalId]);
        if (isset($opt['status'])) {
            if (is_array($opt['status'])) $builder->whereIn('us.status', $opt['status']);
            else $builder->where('us.status', $opt['status']);
        }
        return $builder->get()->getResultArray();
    }

    public function simpanPeserta($jadwalId, $pesertaId)
    {
        $this->db->transStart();
        $builder = $this->db->table('ujian_siswa');
        if ($pesertaId) $builder->whereNotIn('siswa_id', $pesertaId);
        $builder->where('jadwal_id', $jadwalId);
        $builder->delete();
        foreach ($pesertaId as $key => $id) {
            $SQL = "INSERT INTO ujian_siswa (jadwal_id,siswa_id) SELECT * FROM (SELECT $jadwalId as jadwal_id,$id as siswa_id) as tmp WHERE NOT EXISTS (SELECT id FROM ujian_siswa us WHERE us.jadwal_id=$jadwalId AND us.siswa_id=$id) LIMIT 1";
            $this->db->query($SQL);
        }
        $this->db->transComplete();
        return !($this->db->transStatus() === FALSE);
    }

    public function resetToken($jid, $token = null)
    {
        if (!$token) {
            $token = generateRandomString(6);
        }
        $builder = $this->db->table($this->table);
        $builder->where(['id' => $jid]);
        $builder->set(['token' => $token]);
        $builder->update();
        if ($this->db->affectedRows()) {
            return $token;
        } else {
            return false;
        }
    }

    public function token($jid)
    {
        $builder = $this->db->table($this->table);
        $builder->select('id,token');
        if (is_array($jid)) {
            $builder->whereIn('id', $jid);
            return $builder->get()->getResultArray();
        } else {
            $builder->where(['id' => $jid]);
            return $builder->get()->getRowArray();
        }
    }

    public function unstarted_siswa($id)
    {
        $builder = $this->db->table("m_siswa ms");
        $builder->join("ujian_siswa us", "us.siswa_id=ms.id");
        $builder->where(["us.jadwal_id" => $id, 'us.status' => '0']);
        return $builder->get()->getResultArray();
    }
    public function ongoing_siswa($id)
    {
        $builder = $this->db->table("m_siswa ms");
        $builder->join("ujian_siswa us", "us.siswa_id=ms.id");
        $builder->where(["us.jadwal_id" => $id, 'us.status' => '1']);
        return $builder->get()->getResultArray();
    }

    public function countJawaban()
    {
        $total = 0;
        $total += $this->db->table('jawaban_pg_ujian_siswa')->countAllResults();
        $total += $this->db->table('jawaban_pgmulti_ujian_siswa')->countAllResults();
        $total += $this->db->table('jawaban_pgcouple_ujian_siswa')->countAllResults();
        $total += $this->db->table('jawaban_shortentry_ujian_siswa')->countAllResults();
        $total += $this->db->table('jawaban_esai_ujian_siswa')->countAllResults();
        return $total;
    }
    public function countUjian()
    {
        $builder = $this->db->table('ujian_siswa');
        return $builder->countAllResults();
    }

    public function finished_siswa($id)
    {
        $builder = $this->db->table("m_siswa ms");
        $builder->join("ujian_siswa us", "us.siswa_id=ms.id");
        $builder->where(["us.jadwal_id" => $id, 'us.status' => '2']);
        return $builder->get()->getResultArray();
    }

    public function progress($jadwal_id = null, $ujian_id = null, $opt = [], $finall = false)
    {
        $visibleSoal = ['bank_soal_id' => null, 'visible_pg' => 0, 'visible_pgmulti' => 0, 'visible_pgtf' => 0, 'visible_pgcouple' => 0, 'visible_esai' => 0, 'visible_shortentry' => 0];
        $vPG = "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal='" . (new BankSoal())->jenis('pg') . "')";
        $vPGM = "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal='" . (new BankSoal())->jenis('pgmulti') . "')";
        $vPGC = "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal='" . (new BankSoal())->jenis('pgcouple') . "')";
        $vPGTF = "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal='" . (new BankSoal())->jenis('pgtf') . "')";
        $vSE = "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal='" . (new BankSoal())->jenis('shortentry') . "')";
        $vESAI = "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal='" . (new BankSoal())->jenis('esai') . "')";
        $visibleQuery = [$vPG . " visible_pg", $vPGM . " visible_pgmulti", $vPGC . " visible_pgcouple", $vPGTF . " visible_pgtf", $vSE . " visible_shortentry", $vESAI . " visible_esai"];
        if ($jadwal_id) {
            $visibleSoal = $this->db->table('jadwal_ujian ju');
            $visibleSoal->where('ju.id', $jadwal_id);
            $visibleSoal->join('draft_ujian du', 'du.id=ju.draft_id');
            $visibleSoal->select("du.bank_soal_id," . implode(',', $visibleQuery));
            $visibleSoal = $visibleSoal->get()->getRowArray();
        } else if (is_array($ujian_id)) {
            $visibleSoal = $this->db->table('ujian_siswa us');
            $visibleSoal->where('us.id', $ujian_id[0]);
            $visibleSoal->join('jadwal_ujian ju', 'ju.id=us.jadwal_id');
            $visibleSoal->join('draft_ujian du', 'du.id=ju.draft_id');
            $visibleSoal->select("du.bank_soal_id," . implode(',', $visibleQuery));
            $visibleSoal = $visibleSoal->get()->getRowArray();
        } else if ($ujian_id) {
            $visibleSoal = $this->db->table('ujian_siswa us');
            $visibleSoal->where('us.id', $ujian_id);
            $visibleSoal->join('jadwal_ujian ju', 'ju.id=us.jadwal_id');
            $visibleSoal->join('draft_ujian du', 'du.id=ju.draft_id');
            $visibleSoal->select("du.bank_soal_id," . implode(',', $visibleQuery));
            $visibleSoal = $visibleSoal->get()->getRowArray();
        }
        $builder = $this->db->table("jawaban_pgtf" . ($finall ? '_' : '_temp_') . "ujian_siswa s1jpus");
        $builder->select("COUNT(*)");
        $builder->join('soal_opsi', 'soal_opsi.id=s1jpus.opsi_id');
        $builder->where("s1jpus.ujian_id", "jpus.ujian_id", false);
        $builder->where("s1jpus.soal_id", "jpus.soal_id", false);
        $builder->where("s1jpus.opsi_id !=", null);
        $builder->where('soal_opsi.is_key', 's1jpus.tf', false);
        $total_pg_tf_opsi_benar_dipilih = $builder->getCompiledSelect();

        $builder = $this->db->table("soal_opsi");
        $builder->select("COUNT(*)");
        $builder->where('soal_opsi.soal_id', 'jpus.soal_id', false);
        $total_pg_tf_opsi = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgtf" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $builder->select("COUNT(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id)) soal_pgtf_benar,CONCAT(jpus.ujian_id,'-',jpus.soal_id) soal_pgtf_benar_id,jpus.ujian_id,($total_pg_tf_opsi) total_tf_opsi,($total_pg_tf_opsi_benar_dipilih) total_tf_opsi_benar_dipilih", false);
        $builder->having("total_tf_opsi", 'total_tf_opsi_benar_dipilih', false);
        $builder->where("jpus.opsi_id !=", null);
        $builder->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)", false);
        $pgTF = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgmulti" . ($finall ? '_' : '_temp_') . "ujian_siswa s1jpus");
        $builder->select("COUNT(*)");
        $builder->join('soal_opsi', 'soal_opsi.id=s1jpus.opsi_id');
        $builder->where("s1jpus.ujian_id", "jpus.ujian_id", false);
        $builder->where("s1jpus.soal_id", "jpus.soal_id", false);
        $builder->where("s1jpus.opsi_id !=", null);
        $builder->where(['soal_opsi.is_key' => 1]);
        $total_pg_multi_opsi_benar_dipilih = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgmulti" . ($finall ? '_' : '_temp_') . "ujian_siswa s2jpus");
        $builder->select("COUNT(*)");
        $builder->join('soal_opsi', 'soal_opsi.id=s2jpus.opsi_id');
        $builder->where("s2jpus.ujian_id", "jpus.ujian_id", false);
        $builder->where("s2jpus.soal_id", "jpus.soal_id", false);
        $builder->where("s2jpus.opsi_id !=", null);
        $total_pg_multi_opsi_dipilih = $builder->getCompiledSelect();
        $builder = $this->db->table("soal_opsi");
        $builder->select("COUNT(*)");
        $builder->where('soal_opsi.soal_id', 'jpus.soal_id', false);
        $builder->where('soal_opsi.is_key', 1, false);
        $builder->where("jpus.opsi_id !=", null);
        $total_pg_multi_opsi_benar = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgmulti" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $builder->select("COUNT(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id)) soal_pgmulti_benar,CONCAT(jpus.ujian_id,'-',jpus.soal_id) soal_pgmulti_benar_id,jpus.ujian_id,($total_pg_multi_opsi_benar) total_multi_opsi_benar,($total_pg_multi_opsi_benar_dipilih) total_multi_opsi_benar_dipilih,($total_pg_multi_opsi_dipilih) total_multi_opsi_dipilih", false);
        $builder->having(["total_multi_opsi_benar" => "total_multi_opsi_benar_dipilih", "total_multi_opsi_benar_dipilih" => "total_multi_opsi_dipilih"], false, false);
        $builder->where("jpus.opsi_id !=", null);
        $builder->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)", false);
        $pgMulti = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pg" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $builder->select("COUNT(*)");
        $builder->where("jpus.ujian_id", "us.id", false);
        $builder->where("soal_opsi.is_key", 1);
        $builder->groupBy("jpus.ujian_id");
        $builder->join("soal_opsi", "soal_opsi.id=jpus.opsi_id");
        $pg_benar = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_shortentry" . ($finall ? '_' : '_temp_') . "ujian_siswa jsus");
        $builder->select("COUNT(*)");
        $builder->join("soal_data", "soal_data.id=jsus.soal_id AND REPLACE(LOWER(soal_data.shortentry),' ','')=REPLACE(LOWER(jsus.respond),' ','')");
        $builder->where("jsus.ujian_id", "us.id", false);
        $builder->groupBy("jsus.ujian_id");
        $shortentry_benar = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgcouple" . ($finall ? '_' : '_temp_') . "ujian_siswa s1jpus");
        $builder->select("COUNT(*)");
        $builder->join('soal_opsi', 'soal_opsi.id=s1jpus.opsi_id AND soal_opsi.soal_couple_id=s1jpus.couple_id');
        $builder->where("s1jpus.ujian_id", "jpus.ujian_id", false);
        $builder->where("s1jpus.soal_id", "jpus.soal_id", false);
        $builder->where("s1jpus.opsi_id !=", null);
        $total_couple_opsi_benar_dipilih = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgcouple" . ($finall ? '_' : '_temp_') . "ujian_siswa s2jpus");
        $builder->select("COUNT(*)");
        $builder->join('soal_opsi', 'soal_opsi.id=s2jpus.opsi_id');
        $builder->where("s2jpus.ujian_id", "jpus.ujian_id", false);
        $builder->where("s2jpus.soal_id", "jpus.soal_id", false);
        $builder->where("s2jpus.opsi_id !=", null);
        $total_couple_opsi_dipilih = $builder->getCompiledSelect();
        $builder = $this->db->table("soal_opsi");
        $builder->select("COUNT(*)");
        $builder->where('soal_opsi.soal_id', 'jpus.soal_id', false);
        $builder->where('soal_opsi.soal_couple_id !=', null);
        $builder->where("jpus.opsi_id !=", null);
        $total_couple_opsi_benar = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgcouple" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $builder->select("COUNT(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id)) soal_pgcouple_benar,CONCAT(jpus.ujian_id,'-',jpus.soal_id) soal_pgcouple_benar_id,jpus.ujian_id,($total_couple_opsi_benar) total_couple_opsi_benar,($total_couple_opsi_benar_dipilih) total_couple_opsi_benar_dipilih,($total_couple_opsi_dipilih) total_couple_opsi_dipilih", false);
        $builder->having(["total_couple_opsi_benar" => "total_couple_opsi_benar_dipilih", "total_couple_opsi_benar_dipilih" => "total_couple_opsi_dipilih"], false, false);
        $builder->where("jpus.opsi_id !=", null);
        $builder->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)", false);
        $pgCouple = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_esai" . ($finall ? '_' : '_temp_') . "ujian_siswa jeus");
        $builder->select("SUM(jeus.nilai)");
        $builder->where("jeus.ujian_id", "us.id", false);
        $builder->groupBy("jeus.ujian_id");
        $nilai_esai = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_shortentry" . ($finall ? '_' : '_temp_') . "ujian_siswa jsus");
        $builder->select("SUM((CASE WHEN jsus.respond=sd.shortentry THEN sd.true_default_point ELSE sd.false_default_point END))");
        $builder->where("jsus.ujian_id", "us.id", false);
        $builder->join('soal_data sd', 'sd.id=jsus.soal_id');
        $builder->groupBy("jsus.ujian_id");
        $total_point_shortentry = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pg" . ($finall ? '_' : '_temp_') . "ujian_siswa");
        $builder->select("COUNT(DISTINCT jawaban_pg" . ($finall ? '_' : '_temp_') . "ujian_siswa.soal_id)");
        $builder->where('ujian_id', 'us.id', false);
        $builder->groupBy('ujian_id');
        $soal_pg_selesai = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_esai" . ($finall ? '_' : '_temp_') . "ujian_siswa");
        $builder->select("COUNT(DISTINCT jawaban_esai" . ($finall ? '_' : '_temp_') . "ujian_siswa.soal_id)");
        $builder->where('ujian_id', 'us.id', false);
        $builder->groupBy('ujian_id');
        $soal_esai_selesai = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_shortentry" . ($finall ? '_' : '_temp_') . "ujian_siswa");
        $builder->select("COUNT(DISTINCT jawaban_shortentry" . ($finall ? '_' : '_temp_') . "ujian_siswa.soal_id)");
        $builder->where('ujian_id', 'us.id', false);
        $builder->groupBy('ujian_id');
        $soal_shortentry_selesai = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgmulti" . ($finall ? '_' : '_temp_') . "ujian_siswa");
        $builder->select("COUNT(DISTINCT jawaban_pgmulti" . ($finall ? '_' : '_temp_') . "ujian_siswa.soal_id)");
        $builder->where('ujian_id', 'us.id', false);
        $builder->groupBy('ujian_id');
        $soal_pgmulti_selesai = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgtf" . ($finall ? '_' : '_temp_') . "ujian_siswa");
        $builder->select("COUNT(DISTINCT jawaban_pgtf" . ($finall ? '_' : '_temp_') . "ujian_siswa.soal_id)");
        $builder->where('ujian_id', 'us.id', false);
        $builder->groupBy('ujian_id');
        $soal_pgtf_selesai = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgmulti" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $tes = [
            "jpus.ujian_id",
            "CONCAT(jpus.ujian_id,'-',jpus.soal_id) soal_pgmulti_benar_id",
            "SUM((CASE WHEN ($total_pg_multi_opsi_benar)=($total_pg_multi_opsi_benar_dipilih) AND ($total_pg_multi_opsi_benar_dipilih)=($total_pg_multi_opsi_dipilih) THEN ssd.true_default_point ELSE 0 END ))*count(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id))/count(*) total_true_point",
            "SUM((CASE WHEN ($total_pg_multi_opsi_benar)=($total_pg_multi_opsi_benar_dipilih) AND ($total_pg_multi_opsi_benar_dipilih)=($total_pg_multi_opsi_dipilih) THEN 0 ELSE ssd.false_default_point END ))*count(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id))/count(*) total_false_point"
        ];
        $builder->select(implode(',', $tes), false);
        $builder->where("jpus.opsi_id !=", null);
        $builder->join('soal_data ssd', 'ssd.id=jpus.soal_id');
        $builder->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)", false);
        $pointPGMUlti = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgtf" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $tes = [
            "jpus.ujian_id",
            "CONCAT(jpus.ujian_id,'-',jpus.soal_id) soal_pgtf_benar_id",
            "SUM((CASE WHEN ($total_pg_tf_opsi)=($total_pg_tf_opsi_benar_dipilih) THEN ssd.true_default_point ELSE 0 END ))*count(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id))/count(*) total_true_point",
            "SUM((CASE WHEN ($total_pg_tf_opsi)=($total_pg_tf_opsi_benar_dipilih) THEN 0 ELSE ssd.false_default_point END ))*count(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id))/count(*) total_false_point"
        ];
        $builder->select(implode(',', $tes), false);
        $builder->where("jpus.opsi_id !=", null);
        $builder->join('soal_data ssd', 'ssd.id=jpus.soal_id');
        $builder->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)", false);
        $pointPGTF = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pgcouple" . ($finall ? '_' : '_temp_') . "ujian_siswa");
        $builder->select("COUNT(DISTINCT jawaban_pgcouple" . ($finall ? '_' : '_temp_') . "ujian_siswa.soal_id)");
        $builder->where('ujian_id', 'us.id', false);
        $builder->groupBy('ujian_id');
        $soal_pgcouple_selesai = $builder->getCompiledSelect();
        $builder = $this->db->table("jawaban_pgcouple" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $tes = [
            "jpus.ujian_id",
            "CONCAT(jpus.ujian_id,'-',jpus.soal_id) soal_pgcouple_benar_id",
            "SUM((CASE WHEN ($total_couple_opsi_benar)=($total_couple_opsi_benar_dipilih) AND ($total_couple_opsi_benar_dipilih)=($total_couple_opsi_dipilih) THEN ssd.true_default_point ELSE 0 END ))*count(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id))/count(*) total_true_point",
            "SUM((CASE WHEN ($total_couple_opsi_benar)=($total_couple_opsi_benar_dipilih) AND ($total_couple_opsi_benar_dipilih)=($total_couple_opsi_dipilih) THEN 0 ELSE ssd.false_default_point END ))*count(DISTINCT CONCAT(jpus.ujian_id,'-',jpus.soal_id))/count(*) total_false_point"
        ];
        $builder->select(implode(',', $tes), false);
        $builder->where("jpus.opsi_id !=", null);
        $builder->join('soal_data ssd', 'ssd.id=jpus.soal_id');
        $builder->groupBy("CONCAT(jpus.ujian_id,'-',jpus.soal_id)", false);
        $pointPGCouple = $builder->getCompiledSelect();

        $builder = $this->db->table("jawaban_pg" . ($finall ? '_' : '_temp_') . "ujian_siswa jpus");
        $builder->select("SUM((CASE WHEN soal_opsi.is_key=1 THEN ssd.true_default_point ELSE ssd.false_default_point END)) total_point");
        $builder->join("soal_opsi", "soal_opsi.id=jpus.opsi_id");
        $builder->join("soal_data ssd", "ssd.id=soal_opsi.soal_id");
        $builder->where("jpus.ujian_id", "us.id", false);
        $builder->groupBy("jpus.ujian_id");
        $pointPGCommon = $builder->getCompiledSelect();

        $builder = $this->db->table("soal_data ssd");
        $builder->select("ssd.true_default_point total_true_default_point");
        $builder->where("ssd.bank_id", $visibleSoal['bank_soal_id']);
        $builder->where("ssd.jenis_soal", (new BankSoal())->jenis('pg'));
        $builder->orderBy("ssd.id");
        $builder->limit($visibleSoal['visible_pg']);
        $truePointPGCommon = $builder->getCompiledSelect();
        $truePointPGCommon = "SELECT SUM(tpg.total_true_default_point) total_true_default_point FROM ($truePointPGCommon) tpg";

        $builder = $this->db->table("soal_data ssd");
        $builder->select("ssd.true_default_point total_true_default_point");
        $builder->where("ssd.bank_id", $visibleSoal['bank_soal_id']);
        $builder->where("ssd.jenis_soal", (new BankSoal())->jenis('pgmulti'));
        $builder->orderBy("ssd.id");
        $builder->limit($visibleSoal['visible_pgmulti']);
        $truePointPGMulti = $builder->getCompiledSelect();
        $truePointPGMulti = "SELECT SUM(tpgm.total_true_default_point) total_true_default_point FROM ($truePointPGMulti) tpgm";

        $builder = $this->db->table("soal_data ssd");
        $builder->select("ssd.true_default_point total_true_default_point");
        $builder->where("ssd.bank_id", $visibleSoal['bank_soal_id']);
        $builder->where("ssd.jenis_soal", (new BankSoal())->jenis('pgtf'));
        $builder->orderBy("ssd.id");
        $builder->limit($visibleSoal['visible_pgtf']);
        $truePointPGTF = $builder->getCompiledSelect();
        $truePointPGTF = "SELECT SUM(tpgtf.total_true_default_point) total_true_default_point FROM ($truePointPGTF) tpgtf";

        $builder = $this->db->table("soal_data ssd");
        $builder->select("ssd.true_default_point total_true_default_point");
        $builder->where("ssd.bank_id", $visibleSoal['bank_soal_id']);
        $builder->where("ssd.jenis_soal", (new BankSoal())->jenis('pgcouple'));
        $builder->orderBy("ssd.id");
        $builder->limit($visibleSoal['visible_pgcouple']);
        $truePointPGCouple = $builder->getCompiledSelect();
        $truePointPGCouple = "SELECT SUM(tpgc.total_true_default_point) total_true_default_point FROM ($truePointPGCouple) tpgc";

        $builder = $this->db->table("soal_data ssd");
        $builder->select("ssd.true_default_point total_true_default_point");
        $builder->where("ssd.bank_id", $visibleSoal['bank_soal_id']);
        $builder->where("ssd.jenis_soal", (new BankSoal())->jenis('shortentry'));
        $builder->orderBy("ssd.id");
        $builder->limit($visibleSoal['visible_shortentry']);
        $truePointShortEntry = $builder->getCompiledSelect();
        $truePointShortEntry = "SELECT SUM(tps.total_true_default_point) total_true_default_point FROM ($truePointShortEntry) tps";

        $select = [
            "us.*,ms.nomor_induk,ms.nama,mk.nama nama_kelas,mt.kode kode_tingkat,mju.kode jenis_ujian",
            "mm.kode kode_mapel,$vPG visible_pg,du.bobot_tf,du.bobot_esai,IFNULL(($soal_pg_selesai),0) as soal_pg_selesai",
            "$vPGM visible_pgmulti, COUNT(DISTINCT pgm.soal_pgmulti_benar_id) soal_pgmulti_benar",
            "$vPGTF visible_pgtf,IFNULL(($soal_pgtf_selesai),0) as soal_pgtf_selesai, COUNT(DISTINCT pgtf.soal_pgtf_benar_id) soal_pgtf_benar",
            "$vPGC visible_pgcouple, COUNT(DISTINCT pgc.soal_pgcouple_benar_id) soal_pgcouple_benar",
            "IFNULL(($soal_pgcouple_selesai),0) as soal_pgcouple_selesai,IFNULL(($shortentry_benar),0) soal_shortentry_benar",
            "IFNULL(($pg_benar),0) soal_pg_benar,IFNULL(($pointPGCommon),0) total_point_pgcommon",
            "IFNULL(($soal_pgmulti_selesai),0) as soal_pgmulti_selesai,mk.id id_kelas,IFNULL(($soal_esai_selesai),0) as soal_esai_selesai",
            "IFNULL(($soal_shortentry_selesai),0) as soal_shortentry_selesai,$vESAI visible_esai,$vSE visible_shortentry,",
            "IFNULL(($nilai_esai),0) soal_esai_nilai,IFNULL(($total_point_shortentry),0) total_point_shortentry",
            "(CASE WHEN $vPGM >= 1 THEN IFNULL(($truePointPGMulti),0) ELSE 0 END) total_true_default_point_pgmulti",
            "(CASE WHEN $vPGTF >= 1 THEN IFNULL(($truePointPGTF),0) ELSE 0 END) total_true_default_point_pgtf",
            "(CASE WHEN $vPGC >= 1 THEN IFNULL(($truePointPGCouple),0)ELSE 0 END) total_true_default_point_pgcouple",
            "(CASE WHEN $vPG >= 1 THEN IFNULL(($truePointPGCommon),0) ELSE 0 END) total_true_default_point_pgcommon",
            "(CASE WHEN $vSE >= 1 THEN IFNULL(($truePointShortEntry),0) ELSE 0 END) total_true_default_point_shortentry",
            "IFNULL((SUM(ppgm.total_true_point)*count(DISTINCT ppgm.soal_pgmulti_benar_id)/count(*)+(SUM(ppgm.total_false_point)*count(DISTINCT ppgm.soal_pgmulti_benar_id)/count(*))),0) total_point_pgmulti",
            "IFNULL((SUM(ppgtf.total_true_point)*count(DISTINCT ppgtf.soal_pgtf_benar_id)/count(*)+(SUM(ppgtf.total_false_point)*count(DISTINCT ppgtf.soal_pgtf_benar_id)/count(*))),0) total_point_pgtf",
            "IFNULL((SUM(ppgc.total_true_point)*count(DISTINCT ppgc.soal_pgcouple_benar_id)/count(*)+(SUM(ppgc.total_false_point)*count(DISTINCT ppgc.soal_pgcouple_benar_id)/count(*))),0) total_point_pgcouple",
        ];
        $builder = $this->db->table("ujian_siswa us");
        $builder->select(implode(',', $select));
        $builder->join("m_siswa ms", "ms.id=us.siswa_id");
        $builder->join("m_kelas mk", "mk.id=ms.kelas_id", 'left');
        $builder->join("kelas_data kd", "kd.kelas_id=mk.id", 'left');
        $builder->join("m_tingkat mt", "mt.id=kd.tingkat_id", 'left');
        $builder->join("($pgMulti) pgm", "pgm.ujian_id=us.id", "left");
        $builder->join("($pointPGMUlti) ppgm", "ppgm.ujian_id=us.id", "left");
        $builder->join("($pgTF) pgtf", "pgtf.ujian_id=us.id", "left");
        $builder->join("($pointPGTF) ppgtf", "ppgtf.ujian_id=us.id", "left");
        $builder->join("($pgCouple) pgc", "pgc.ujian_id=us.id", "left");
        $builder->join("($pointPGCouple) ppgc", "ppgc.ujian_id=us.id", "left");
        $builder->join("jadwal_ujian ju", "ju.id=us.jadwal_id");
        $builder->join("m_jenisujian mju", "mju.id=ju.jenis_id", "left");
        $builder->join("draft_ujian du", "du.id=ju.draft_id");
        $builder->join("m_mapel mm", "mm.id=du.mapel_id");
        if ($jadwal_id) {
            $builder->where("us.jadwal_id", $jadwal_id);
        }
        if (isset($opt['kelas_id'])) {
            $builder->where("ms.kelas_id", $opt['kelas_id']);
        }
        if (isset($opt['status'])) {
            if (is_array($opt['status'])) {
                $builder->whereIn("us.status", $opt['status']);
            } else {
                $builder->where("us.status", $opt['status']);
            }
        }
        $builder->groupBy("us.id");
        if ($ujian_id) {
            if (is_array($ujian_id)) {
                $builder->whereIn("us.id", $ujian_id);
                return $builder->get()->getResultArray();
            } else {
                $builder->where("us.id", $ujian_id);
                return $builder->get()->getRowArray();
            }
        }
        return $builder->get()->getResultArray();
    }
}
