<?php

namespace App\Controllers\Panel\Ujian;

use App\Controllers\Panel\BC;
use App\Models\Panel\Elearning\TahunPelajaran;
use App\Models\Panel\MasterData\JenisUjian;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use App\Models\Panel\MasterData\Kelas;
use CodeIgniter\API\ResponseTrait;

class LeggerNilai extends BC
{
    use ResponseTrait;
    private $kelasModel;
    public function __construct()
    {
        parent::__construct();
        $this->kelasModel = new Kelas();
    }
    public function index()
    {
        $data['page_title'] = "Legger Nilai";
        return view('Panel/Ujian/LeggerNilai/index', $data);
    }
    public function all()
    {
        if (!$this->request->isAJAX() && ENVIRONMENT != 'development') {
            exit('No direct script access allowed');
        }
        $data = $this->kelasModel->getAll();
        return $this->respond(['data' => $data, 'csrf' => ['name' => csrf_token(), 'value' => csrf_hash()]]);
    }
    public function jenis_ujian()
    {
        if (!$this->request->isAJAX() && ENVIRONMENT != 'development') {
            exit('No direct script access allowed');
        }
        $data = (new JenisUjian())->findAll();
        return $this->respond(['data' => $data, 'csrf' => ['name' => csrf_token(), 'value' => csrf_hash()]]);
    }
    public function tahun_pelajaran()
    {
        if (!$this->request->isAJAX() && ENVIRONMENT != 'development') {
            exit('No direct script access allowed');
        }
        $data = (new TahunPelajaran())->findAll();
        return $this->respond(['data' => $data, 'csrf' => ['name' => csrf_token(), 'value' => csrf_hash()]]);
    }
    public function export()
    {
        $jenisUjian = $this->request->getGet('jenis-ujian');
        $kelas = $this->request->getGet('kelas');
        $tahun = $this->request->getGet('tahun-pelajaran');
        if (!$jenisUjian || !$kelas || !$tahun) {
            return $this->fail(['message' => "Parameter tidak lengkap"]);
        }
        $db = db_connect();

        $siswaData = $db->table('m_siswa ms')->select('ms.id,ms.nomor_induk,ms.nama')->where('ms.kelas_id', $kelas)->get()->getResultArray();
        $kelasData = $db->table('m_kelas')->where('id', $kelas)->get()->getRowArray();
        $jenisData = $db->table('m_jenisujian')->where('id', $jenisUjian)->get()->getRowArray();
        $tahunData = $db->table('tahun_pelajaran')->where('id', $tahun)->get()->getRowArray();
        $spreadsheet = new Spreadsheet();
        $path = WRITEPATH . "/uploads/rekap-nilai-kelas-{$kelasData['nama']}.xlsx";
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setCellValue('A1', "REKAP NILAI KELAS {$kelasData['nama']} UJIAN {$jenisData['nama']}");
        $worksheet->setCellValue('A2', "TAHUN PELAJARAN {$tahunData['nama']}")->setTitle('REKAP NILAI KELAS ' . $kelasData['kode']);
        $select = [];
        $select[] = 'ms.nomor_induk,ms.nama,du.mapel_id,us.siswa_id';
        $select[] = 'mm.nama nama_mapel,us.status status_ujian';
        $select[] = 'us.nilai_pg,us.nilai_esai,mju.nama jenis_ujian';
        $data = $db->table('ujian_siswa us');
        $data->select(implode(',', $select));
        $data->join('jadwal_ujian ju', 'ju.id=us.jadwal_id');
        $data->join('m_siswa ms', 'ms.id=us.siswa_id');
        $data->join('m_jenisujian mju', 'mju.id=ju.jenis_id');
        $data->join('draft_ujian du', 'du.id=ju.draft_id');
        $data->join('m_mapel mm', 'mm.id=du.mapel_id');
        $data->where('ms.kelas_id', $kelas);
        $data->where('ju.tahun_id', $tahun);
        $data->where('ju.jenis_id', $jenisUjian);
        $data = $data->get()->getResultArray();
        $mapels = [];
        foreach ($data as $key => $value) {
            if (isset($mapels[$value['mapel_id']])) continue;
            $mapels[$value['mapel_id']] = $value['nama_mapel'];
        }
        $nilai = [];
        foreach ($siswaData as $key => $value) {
            $n = [
                'nama' => $value['nama'],
                'nomor_induk' => $value['nomor_induk'],
                'nilai' => [],
            ];
            foreach ($mapels as $sk => $sv) {
                $sn = null;
                foreach ($data as $ssk => $ssv) {
                    if ($ssv['mapel_id'] != $sk || $value['id'] != $ssv['siswa_id']) continue;
                    if ($sn != null || !$ssv['status_ujian']) break;
                    $sn = $ssv['nilai_pg'] + $ssv['nilai_esai'];
                }
                $n['nilai'][$sv] = $sn;
            }
            $nilai[] = $n;
        }
        $startCol = 4;
        $startRow = 4;
        $worksheet->setCellValue('A4', 'NO')->setCellValue('B4', 'NOMOR INDUK')->setCellValue('C4', 'NAMA');
        $sc = $startCol;
        $sr = $startRow;
        foreach ($mapels as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($sc, $sr, $value);
            $sc++;
        }
        $rrsc = $sc;
        $worksheet->setCellValueByColumnAndRow($sc, $sr, "RATA RATA");

        $sr = $startRow + 1;
        foreach ($nilai as $key => $value) {
            $sc = 1;
            $worksheet->setCellValueByColumnAndRow($sc++, $sr, $key + 1);
            $worksheet->setCellValueByColumnAndRow($sc++, $sr, $value['nomor_induk']);
            $worksheet->setCellValueByColumnAndRow($sc++, $sr, $value['nama']);
            $startSC = $sc;
            $startSR = $sr;
            foreach ($value['nilai'] as $nk => $nv) {
                if (!$nv) $worksheet->getStyleByColumnAndRow($sc, $sr)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF0000');
                $worksheet->setCellValueByColumnAndRow($sc++, $sr, $nv ?? '0');
            }
            $endSC = $sc;
            $endSR = $sr;
            if ($startSC != $endSC) {
                $SC = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($startSC);
                $EC = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($endSC - 1);
                $worksheet->setCellValueByColumnAndRow($sc++, $sr, "=ROUND(AVERAGE({$SC}{$startSR}:{$EC}{$endSR}),2)");
            }
            $sr++;
        }

        $maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($rrsc);
        $maxRow = $sr - 1;
        $worksheet->getStyle("A{$startRow}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getInside()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        $worksheet->getStyle("A{$startRow}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getOutline()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");
        $writer->save($path);
        return $this->response->download($path, null);
    }
    public function preview()
    {
        $jenisUjian = $this->request->getGet('jenis-ujian');
        $kelas = $this->request->getGet('kelas');
        $tahun = $this->request->getGet('tahun-pelajaran');
        if (!$jenisUjian || !$kelas || !$tahun) {
            return $this->fail(['message' => "Parameter tidak lengkap"]);
        }
        $db = db_connect();

        $siswaData = $db->table('m_siswa ms')->select('ms.id,ms.nomor_induk,ms.nama')->where('ms.kelas_id', $kelas)->get()->getResultArray();
        $kelasData = $db->table('m_kelas')->where('id', $kelas)->get()->getRowArray();
        $jenisData = $db->table('m_jenisujian')->where('id', $jenisUjian)->get()->getRowArray();
        $tahunData = $db->table('tahun_pelajaran')->where('id', $tahun)->get()->getRowArray();
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setCellValue('A1', "REKAP NILAI KELAS {$kelasData['nama']} UJIAN {$jenisData['nama']}");
        $worksheet->setCellValue('A2', "TAHUN PELAJARAN {$tahunData['nama']}")->setTitle('REKAP NILAI KELAS ' . $kelasData['kode']);
        $select = [];
        $select[] = 'ms.nomor_induk,ms.nama,du.mapel_id,us.siswa_id';
        $select[] = 'mm.nama nama_mapel,us.status status_ujian';
        $select[] = 'us.nilai_pg,us.nilai_esai,mju.nama jenis_ujian';
        $data = $db->table('ujian_siswa us');
        $data->select(implode(',', $select));
        $data->join('jadwal_ujian ju', 'ju.id=us.jadwal_id');
        $data->join('m_siswa ms', 'ms.id=us.siswa_id');
        $data->join('m_jenisujian mju', 'mju.id=ju.jenis_id');
        $data->join('draft_ujian du', 'du.id=ju.draft_id');
        $data->join('m_mapel mm', 'mm.id=du.mapel_id');
        $data->where('ms.kelas_id', $kelas);
        $data->where('ju.tahun_id', $tahun);
        $data->where('ju.jenis_id', $jenisUjian);
        $data = $data->get()->getResultArray();
        // dd($data);
        $mapels = [];
        foreach ($data as $key => $value) {
            if (isset($mapels[$value['mapel_id']])) continue;
            $mapels[$value['mapel_id']] = $value['nama_mapel'];
        }
        // dd($mapels);
        $nilai = [];
        foreach ($siswaData as $key => $value) {
            $n = [
                'nama' => $value['nama'],
                'nomor_induk' => $value['nomor_induk'],
                'nilai' => [],
            ];
            foreach ($mapels as $sk => $sv) {
                $sn = null;
                foreach ($data as $ssk => $ssv) {
                    if ($ssv['mapel_id'] != $sk || $value['id'] != $ssv['siswa_id']) continue;
                    if ($sn != null || !$ssv['status_ujian']) break;
                    $sn = $ssv['nilai_pg'] + $ssv['nilai_esai'];
                    //if ($ssv['siswa_id'] == 445) dd($sn);

                }
                $n['nilai'][$sv] = $sn;
            }
            $nilai[] = $n;
        }
        $startCol = 4;
        $startRow = 4;
        $worksheet->setCellValue('A4', 'NO')->setCellValue('B4', 'NOMOR INDUK')->setCellValue('C4', 'NAMA');
        $sc = $startCol;
        $sr = $startRow;
        foreach ($mapels as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($sc, $sr, $value);
            $sc++;
        }
        $rrsc = $sc;
        $worksheet->setCellValueByColumnAndRow($sc, $sr, "RATA RATA");

        $sr = $startRow + 1;
        foreach ($nilai as $key => $value) {
            $sc = 1;
            $worksheet->setCellValueByColumnAndRow($sc++, $sr, $key + 1);
            $worksheet->setCellValueByColumnAndRow($sc++, $sr, $value['nomor_induk']);
            $worksheet->setCellValueByColumnAndRow($sc++, $sr, $value['nama']);
            $startSC = $sc;
            $startSR = $sr;
            // d($value);
            foreach ($value['nilai'] as $nk => $nv) {
                if (!$nv) {
                }
                $worksheet->setCellValueByColumnAndRow($sc++, $sr, $nv ?? '0');
            }
            $endSC = $sc;
            $endSR = $sr;
            if ($startSC != $endSC) {
                $SC = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($startSC);
                $EC = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($endSC - 1);
                $worksheet->setCellValueByColumnAndRow($sc++, $sr, "=ROUND(AVERAGE({$SC}{$startSR}:{$EC}{$endSR}),2)");
            }
            $sr++;
        }
        $maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($rrsc);
        $maxRow = $sr - 1;
        $worksheet->getStyle("A{$startRow}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getInside()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        $worksheet->getStyle("A{$startRow}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getOutline()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        return $this->respond(['data' => $worksheet->toArray()]);
    }
}
