<?php

namespace App\Controllers\Panel\Elearning\Rekap\Agenda;

use App\Controllers\Panel\BC;
use PhpOffice\PhpSpreadsheet\Helper\Html;
use DateInterval;
use DatePeriod;
use DateTime;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use CodeIgniter\API\ResponseTrait;

class Mengajar extends BC
{
    use ResponseTrait;
    private $jl;
    public function index()
    {
        return view('Panel/Elearning/Rekap/Agenda/mengajar');
    }
    public function export()
    {
        $startDate = $this->request->getVar('startDate');
        $endDate = $this->request->getVar('endDate');
        $tahun_pelajaran = $this->request->getVar('tahun');
        $mapel = $this->request->getVar('mapel');
        $guru = $this->request->getVar('guru');

        $db = db_connect();
        $guruData = $db->table('users u')->select('u.id,u.fullname')->join('users_roles ur', 'ur.user_id=u.id')->where(['id' => $guru, 'ur.role_id' => 2])->get()->getRowArray();
        $mapelData = $db->table('m_mapel')->where(['id' => $mapel])->get()->getRowArray();
        $tahun_pelajaranData = $db->table('tahun_pelajaran')->where(['id' => $tahun_pelajaran])->get()->getRowArray();
        $this->jl = $db->table('jadwal_libur')->where("(tanggal BETWEEN '$startDate' AND '$endDate')", null, false)->where('is_nasional', 1, false)->get()->getResultArray();
        $jadwalMengajar = $db->table('jadwal_pelajaran jp');
        $jadwalMengajar->select('jp.*,mk.nama nama_kelas,mm.nama nama_mapel');
        $jadwalMengajar->where(['user_id' => $guru, 'tahun_id' => $tahun_pelajaran, 'mapel_id' => $mapel]);
        $jadwalMengajar->join('m_mapel mm', 'mm.id=jp.mapel_id');
        $jadwalMengajar->join('m_kelas mk', 'mk.id=jp.kelas_id');
        $jadwalMengajar = $jadwalMengajar->get()->getResultArray();
        $begin = new DateTime($startDate);
        $end = new DateTime($endDate . " 00:01");
        $interval = DateInterval::createFromDateString('next day');
        $period = new DatePeriod($begin, $interval, $end);
        $spreadsheet = new Spreadsheet();
        $path = WRITEPATH . "/uploads/rekap-agenda-mengajar.xlsx";
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->mergeCells("A1:K1")->setCellValue('A1', "JURNAL AGENDA MENGAJAR");
        $worksheet->getStyle('A1')->getAlignment()->setHorizontal('center');
        $worksheet->getStyle('A1')->getFont()->setBold(true);
        $worksheet->setCellValue('A3', "Nama Guru : $guruData[fullname]");
        $worksheet->setCellValue('A4', "Mata Pelajaran : $mapelData[nama]");
        $worksheet->setCellValue('A5', "Tahun Pelajaran : $tahun_pelajaranData[nama]");
        $worksheet->setCellValue('A6', "Tanggal : " . ($startDate == $endDate ? $startDate : $startDate . ' s/d ' . $endDate));
        $worksheet->mergeCells("A7:A8")->setCellValue('A7', 'No');
        $worksheet->mergeCells("B7:B8")->setCellValue('B7', 'Tanggal');
        $worksheet->mergeCells("C7:C8")->setCellValue('C7', 'Rangkuman Materi');
        // $worksheet->mergeCells("D7:D8")->setCellValue('D7', 'Pertemuan');
        $worksheet->mergeCells("D7:D8")->setCellValue('D7', 'Kelas');
        $worksheet->mergeCells("E7:E8")->setCellValue('E7', 'Jam Mulai');
        $worksheet->mergeCells("F7:F8")->setCellValue('F7', 'Jam Selesai');
        $worksheet->mergeCells("G7:J7")->setCellValue('G7', 'Kehadiran Siswa');
        $worksheet->setCellValue('G8', 'H')->setCellValue('H8', "S");
        $worksheet->setCellValue('I8', 'I')->setCellValue('J8', "A");
        $worksheet->getStyle('A7:J8')->getAlignment()->setHorizontal('center');
        // $pertemuanCount = [];
        $startRow = 9;
        $startCol = 1;
        $no = 1;
        foreach ($period as $pk => $pv) {
            $jadwal = null;
            foreach ($jadwalMengajar as $jmk => $jmv) {
                if ($jadwal) break;
                $d = $pv->format('N') + 1;
                if ($d > 7) $d = 1;
                if ($jmv['startDay'] == $d) $jadwal = $jmv;
            }
            if (!$jadwal) continue;
            $uid = "$jadwal[id]-$jadwal[kelas_id]";
            // if (isset($pertemuanCount[$uid])) $pertemuanCount[$uid] += 1;
            // else $pertemuanCount[$uid] = 1;
            $agenda = $db->table('agenda_kegiatan')->where(['jadwal_id' => $jadwal['id'], 'user_id' => $guru, 'created_at >=' => strtotime($pv->format('Y-m-d 00:00:00')), 'created_at <=' => strtotime($pv->format('Y-m-d 23:59:59'))])->get()->getRowArray();
            $worksheet->setCellValueByColumnAndRow($startCol, $startRow, $no++);
            $worksheet->setCellValueByColumnAndRow($startCol + 1, $startRow, $pv->format('Y-m-d'));
            if ($agenda) {
                $worksheet->setCellValueByColumnAndRow($startCol + 2, $startRow, (new Html())->toRichTextObject($agenda['rangkuman']));
            } else {
                $presence = $db->table('daily_presence_guru')->where(['created_at >=' => strtotime($pv->format('Y-m-d 00:00:00')), 'created_at <=' => strtotime($pv->format('Y-m-d 23:59:59')), 'user_id' => $guru])->get()->getRowArray();
                $state = "Alpa";
                if ($presence) {
                    switch ($presence['status']) {
                        case '1':
                            $state =  'Hadir';
                            break;
                        case '2':
                            $state =  'Alpa';
                            break;
                        case '3':
                            $state =  'Sakit';
                            break;
                        case '4':
                            $state =  'Izin';
                            break;
                    }
                }
                $worksheet->setCellValueByColumnAndRow($startCol + 2, $startRow, $state);
            }
            // $worksheet->setCellValueByColumnAndRow($startCol + 3, $startRow, $pertemuanCount[$uid]);
            $worksheet->setCellValueByColumnAndRow($startCol + 3, $startRow, $jadwal['nama_kelas']);
            $worksheet->setCellValueByColumnAndRow($startCol + 4, $startRow, $jadwal['startTime']);
            $worksheet->setCellValueByColumnAndRow($startCol + 5, $startRow, $jadwal['endTime']);
            $siswaInClass = $db->table('m_siswa ms');
            $siswaInClass->select('ms.id, ms.*,jas.keterangan,jas.jadwal_id', false);
            $siswaInClass->distinct(true);
            $siswaInClass->join('jadwal_absensi_siswa jas', "jas.siswa_id=ms.id AND jas.jadwal_id=$jadwal[id] AND jas.created_at BETWEEN " . strtotime($pv->format('Y-m-d 00:00:00')) . " AND " . strtotime($pv->format('Y-m-d 23:59:59')), 'left');
            $siswaInClass->where('ms.kelas_id', (int) $jadwal['kelas_id']);
            $siswaInClass = $siswaInClass->get()->getResultArray();
            $sakit = $hadir = $alpa = $izin = 0;
            foreach ($siswaInClass as $sick => $sicv) {
                if (!$sicv['jadwal_id']) {
                    $alpa++;
                    continue;
                };
                switch ($sicv['keterangan']) {
                    case '0':
                        $alpa++;
                        break;
                    case '1':
                        $hadir++;
                        break;
                    case '2':
                        $alpa++;
                        break;
                    case '3':
                        $sakit++;
                        break;
                    case '4':
                        $izin++;
                        break;
                    default:
                        $alpa++;
                        break;
                }
            }
            $worksheet->setCellValueByColumnAndRow($startCol + 6, $startRow, $hadir);
            $worksheet->setCellValueByColumnAndRow($startCol + 7, $startRow, $sakit);
            $worksheet->setCellValueByColumnAndRow($startCol + 8, $startRow, $izin);
            $worksheet->setCellValueByColumnAndRow($startCol + 9, $startRow, $alpa);
            if ($this->is_libur($pv->format('Y-m-d'))) {
                $worksheet
                    ->getStyleByColumnAndRow($startCol, $startRow, $startCol + 9, $startRow)
                    ->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setARGB('FFFF0000');
            }
            $startRow++;
        }

        $startRow--;
        $worksheet->getStyle("A7:J{$startRow}")
            ->getBorders()
            ->getInside()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        $worksheet->getStyle("A7:J{$startRow}")
            ->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);
    }
    private function is_libur($tanggal)
    {
        foreach ($this->jl as $jl) {
            if ($jl['tanggal'] == $tanggal) return $jl['title'];
        }
        return false;
    }
    public function guru()
    {
        $tahun = $this->request->getGet('tahun-pelajaran');
        if (!$tahun) return $this->respond([]);
        $db = db_connect();
        $guru = $db->table('users u');
        $guru->select('u.id,u.fullname,u.nomor_induk');
        $guru->join('users_roles ur', 'ur.user_id=u.id');
        $guru->join('jadwal_pelajaran jp', 'jp.user_id=u.id');
        $guru->where('jp.tahun_id', $tahun);
        $guru->where('ur.role_id', 2);
        if (!user()->hasRole(['admin', 'piket', 'kepsek'])) $guru->where('u.id', user()->id);
        $guru = $guru->get()->getResultArray();
        if (!$guru) $guru = [['id' => user()->id, 'fullname' => user()->fullname, 'nomor_induk' => user()->nomor_induk]];
        return $this->respond($guru);
    }
    public function mapel()
    {
        $guru = $this->request->getGet('guru');
        if (!$guru) return $this->respond([]);
        $db = db_connect();
        $mapel = $db->table('m_mapel mm');
        $mapel->select('mm.id,mm.kode,mm.nama')->distinct();
        $mapel->join('jadwal_pelajaran jp', 'jp.mapel_id=mm.id');
        $mapel->where('jp.user_id', $guru);
        $mapel = $mapel->get()->getResultArray();
        return $this->respond($mapel);
    }
}
