<?php

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

use App\Controllers\Panel\BC;
use App\Models\Panel\Elearning\TahunPelajaran;
use CodeIgniter\API\ResponseTrait;
use DateInterval;
use DatePeriod;
use DateTime;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;

class Mengajar extends BC
{
    use ResponseTrait;
    private $jl;
    public function __construct()
    {
        parent::__construct();
    }
    public function index()
    {
        return view('Panel/Elearning/Rekap/Guru/mengajar', ['page_title' => "Rekap Mengajar Guru"]);
    }
    public function preview()
    {
        $month = $this->request->getGet('month', FILTER_VALIDATE_INT);
        $year = $this->request->getGet('year', FILTER_VALIDATE_INT);
        $tahun_id = $this->request->getGet('tahun_id', FILTER_VALIDATE_INT);
        if (!$month || !$year) return $this->fail(['message' => "Bulan atau Tahun Dibutuhkan"]);
        $parsedDate = date("Y-m", strtotime("$year-$month"));
        $total_day = date('d', strtotime("last day of $year-$month"));
        $db = db_connect();
        $this->jl = $db->table('jadwal_libur')->where(["DATE_FORMAT(tanggal,'%Y-%m')" => "'$parsedDate'", 'is_nasional' => 1], null, false)->get()->getResultArray();
        $begin = new DateTime(date('d-m-Y', strtotime("first day of $parsedDate")));
        $end = new DateTime(date('d-m-Y 00:01', strtotime("last day of $parsedDate")));
        $tahun_id = $this->request->getGet('tahun_id');
        $tahun_pelajaran = (new TahunPelajaran())->find($tahun_id);
        if (!$tahun_pelajaran) return $this->fail(['message' => "Tahun Pelajaran Tidak Valid"]);
        $interval = DateInterval::createFromDateString('next day');
        $period = new DatePeriod($begin, $interval, $end);

        $path = WRITEPATH . "/uploads/rekap-presensi-mengajar-$year-$month.xlsx";
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        // Set up some basic data
        $startRow = 5;
        $s1 = $startRow - 1;
        $worksheet
            ->setCellValue('A1', '')
            ->setCellValue('A2', '')
            ->setCellValue("A{$s1}", "No")->setCellValue("B{$s1}", "Nama Guru")->setTitle("REKAP MENGAJAR");
        $dataMengajar = [];
        $guru = [];
        foreach ($period as $k => $dt) {
            $parsedDate = $dt->format("Y-m-d");
            if ($this->is_libur($parsedDate)) {
                $dataMengajar[$k + 1] = null;
                continue;
            }
            if (strtotime($parsedDate) > time()) {
                $dataMengajar[$k + 1] = null;
                continue;
            }
            $sdtm = strtotime("$parsedDate 00:00:00");
            $edtm = strtotime("$parsedDate 23:59:59");
            $getUserFullname = $db->table('users u')->select('fullname')->where('id', 'jp.user_id', false)->orWhere('id', 'ak.user_id', false)->limit(1)->getCompiledSelect();
            $getUserId = $db->table('users u')->select('id')->where('id', 'jp.user_id', false)->orWhere('id', 'ak.user_id', false)->limit(1)->getCompiledSelect();
            $is_mengajar = "SELECT id FROM `jadwal_pelajaran` `sjp` LEFT JOIN agenda_kegiatan sak ON sak.jadwal_id=sjp.id ";
            $is_mengajar .= "WHERE sjp.tahun_id = tp.id AND (sjp.user_id = ($getUserId) OR sak.user_id = ($getUserId))  LIMIT 1";
            $startDay = $dt->format('w') + 1;
            $is_haveSchedule = "SELECT id FROM `jadwal_pelajaran` `sjp` WHERE (sjp.user_id = ($getUserId) OR sjp.user_id=jp.user_id) AND sjp.id=jp.id AND sjp.startDay = $startDay LIMIT 1";
            $select = ["($getUserFullname) fullname, jp.id,($getUserId) guru_id,ak.created_at tanggal,mk.nama nama_kelas,jp.countJam"];
            $select[] = "(CASE WHEN EXISTS ($is_mengajar) THEN 1 ELSE 0 END) is_mengajar";
            $select[] = "($is_haveSchedule) now_is_schedule,tp.nama nama_tahun";
            $builder = $db->table('jadwal_pelajaran jp');
            $builder->select(implode(',', $select), false);
            $builder->join('agenda_kegiatan ak', "ak.jadwal_id=jp.id", 'left', false);
            $builder->join('m_kelas mk', 'mk.id=jp.kelas_id', 'left');
            $builder->having(['is_mengajar' => 1, 'tanggal >= ' => $sdtm, 'tanggal <= ' => $edtm]);
            $builder->orHaving('now_is_schedule !=', null);
            if ($tahun_id) $builder->join('tahun_pelajaran tp', "tp.id=jp.tahun_id", '', false)->where('tp.id', $tahun_id);
            if (!$tahun_id) $builder->join('tahun_pelajaran tp', "tp.id=jp.tahun_id AND tp.is_active=1", '', false);
            $rawData = $builder->get()->getResultArray();
            $data = [];
            foreach ($rawData as $d) {
                if ($d['tanggal'] < $sdtm || $d['tanggal'] > $edtm) $d['tanggal'] = null;
                if (!isset($guru[$d['guru_id']])) $guru[$d['guru_id']] = $d['fullname'];
                if (isset($data[$d['guru_id']])) {
                    if ($d['tanggal']) {
                        if (isset($data[$d['guru_id']]['kelasMasuk'][$d['nama_kelas']])) {
                            $data[$d['guru_id']]['kelasMasuk'][$d['nama_kelas']] += $d['countJam'];
                        } else {
                            $data[$d['guru_id']]['kelasMasuk'][$d['nama_kelas']] = $d['countJam'];
                        }
                    } else {
                        if (isset($data[$d['guru_id']]['kelasTidakMasuk'][$d['nama_kelas']])) {
                            $data[$d['guru_id']]['kelasTidakMasuk'][$d['nama_kelas']] += $d['countJam'];
                        } else {
                            $data[$d['guru_id']]['kelasTidakMasuk'][$d['nama_kelas']] = $d['countJam'];
                        }
                    }
                } else {
                    $e = [
                        "fullname" => $d['fullname'],
                        "kelasMasuk" => [],
                        "kelasTidakMasuk" => [],
                    ];
                    if ($d['tanggal']) $e['kelasMasuk'][$d['nama_kelas']] = $d["countJam"];
                    if (!$d['tanggal']) $e['kelasTidakMasuk'][$d['nama_kelas']] = $d["countJam"];

                    $data[$d['guru_id']] = $e;
                }
            }
            $dataMengajar[$k + 1] = $data;
        }
        //create no 
        $row = $startRow;
        $i = 1;
        $s = $startRow - 1;
        $maxRow = $startRow + count($guru) - 1;
        $maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($total_day + 3);
        $smaxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($total_day + 2);
        foreach ($guru as $k => $v) {
            $worksheet->setCellValue("A{$row}", $i++);
            $worksheet->setCellValue("B{$row}", $v);
            $worksheet->setCellValue("{$maxCol}{$row}", "=SUM(C{$row}:{$smaxCol}{$row})");
            $row++;
        }
        $worksheet->getStyle("A{$s}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getInside()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        $worksheet->getStyle("A{$s}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getOutline()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        $worksheet->setCellValue("{$maxCol}{$s}", "Total");
        foreach ($dataMengajar as $k => $d) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($k + 2);
            $worksheet->setCellValue("{$col}{$s}", $k);

            if ($d === null) {
                $worksheet->getStyle("{$col}{$s}:{$col}{$maxRow}")
                    ->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setARGB('FFFF0000');
                continue;
            }
            foreach ($d as $gid => $gd) {
                $index = null;
                $sr = $startRow;
                foreach ($guru as $ksg => $sg) {
                    if ($index) continue;
                    if ($ksg == $gid) $index = $sr;
                    $sr++;
                }
                if (!$index) continue;
                $text = "";
                $jamCountMasuk = 0;
                foreach ($gd['kelasMasuk'] as $kkm => $km) {
                    $jamCountMasuk += $km;
                    $text .= "$kkm : $km\n";
                }
                foreach ($gd['kelasTidakMasuk'] as $kktm => $ktm) {
                    $text .= "$kktm : $ktm Tidak Masuk\n";
                }
                $worksheet->setCellValue("{$col}{$index}", "$jamCountMasuk");
                $worksheet->getComment("{$col}{$index}")->getText()->createTextRun($text);
            }
        }
        return $this->respond(['absensi' => $worksheet->toArray(), 'libur' => $this->getDayLibur()]);
    }
    private function getDayLibur()
    {
        $data = [];
        foreach ($this->jl as $jl) {
            $data[(int)date('d', strtotime($jl['tanggal']))] = $jl['title'];
        }
        return $data;
    }
    public function export()
    {
        $month = $this->request->getGet('month', FILTER_VALIDATE_INT);
        $year = $this->request->getGet('year', FILTER_VALIDATE_INT);
        $tahun_id = $this->request->getGet('tahun_id', FILTER_VALIDATE_INT);
        if (!$month || !$year) return $this->fail(['message' => "Bulan atau Tahun Dibutuhkan"]);
        $parsedDate = date("Y-m", strtotime("$year-$month"));
        $total_day = date('d', strtotime("last day of $year-$month"));
        $db = db_connect();
        $this->jl = $db->table('jadwal_libur')->where(["DATE_FORMAT(tanggal,'%Y-%m')" => "'$parsedDate'", 'is_nasional' => 1], null, false)->get()->getResultArray();
        $begin = new DateTime(date('d-m-Y', strtotime("first day of $parsedDate")));
        $end = new DateTime(date('d-m-Y 00:01', strtotime("last day of $parsedDate")));
        $tahun_id = $this->request->getGet('tahun_id');
        $tahun_pelajaran = (new TahunPelajaran())->find($tahun_id);
        if (!$tahun_pelajaran) return $this->fail(['message' => "Tahun Pelajaran Tidak Valid"]);
        $interval = DateInterval::createFromDateString('next day');
        $period = new DatePeriod($begin, $interval, $end);

        $path = WRITEPATH . "/uploads/rekap-presensi-mengajar-$year-$month.xlsx";
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        // Set up some basic data
        $startRow = 5;
        $s1 = $startRow - 1;
        $worksheet
            ->setCellValue('A1', strtoupper('rekap mengajar guru bulan ' . bulanIndo($month)))
            ->setCellValue('A2', strtoupper("tahun pelajaran $tahun_pelajaran[nama] $tahun_pelajaran[semester]"))
            ->setCellValue("A{$s1}", "No")->setCellValue("B{$s1}", "Nama Guru")->setTitle("REKAP MENGAJAR");
        $dataMengajar = [];
        $guru = [];
        foreach ($period as $k => $dt) {
            $parsedDate = $dt->format("Y-m-d");
            if ($this->is_libur($parsedDate)) {
                $dataMengajar[$k + 1] = null;
                continue;
            }
            if (strtotime($parsedDate) > time()) {
                $dataMengajar[$k + 1] = null;
                continue;
            }
            $sdtm = strtotime("$parsedDate 00:00:00");
            $edtm = strtotime("$parsedDate 23:59:59");
            $getUserFullname = $db->table('users u')->select('fullname')->where('id', 'jp.user_id', false)->orWhere('id', 'ak.user_id', false)->limit(1)->getCompiledSelect();
            $getUserId = $db->table('users u')->select('id')->where('id', 'jp.user_id', false)->orWhere('id', 'ak.user_id', false)->limit(1)->getCompiledSelect();
            $is_mengajar = "SELECT id FROM `jadwal_pelajaran` `sjp` LEFT JOIN agenda_kegiatan sak ON sak.jadwal_id=sjp.id ";
            $is_mengajar .= "WHERE sjp.tahun_id = tp.id AND (sjp.user_id = ($getUserId) OR sak.user_id = ($getUserId))  LIMIT 1";
            $startDay = $dt->format('w') + 1;
            $is_haveSchedule = "SELECT id FROM `jadwal_pelajaran` `sjp` WHERE (sjp.user_id = ($getUserId) OR sjp.user_id=jp.user_id) AND sjp.id=jp.id AND sjp.startDay = $startDay LIMIT 1";
            $select = ["($getUserFullname) fullname, jp.id,($getUserId) guru_id,ak.created_at tanggal,mk.nama nama_kelas,jp.countJam"];
            $select[] = "(CASE WHEN EXISTS ($is_mengajar) THEN 1 ELSE 0 END) is_mengajar";
            $select[] = "($is_haveSchedule) now_is_schedule,tp.nama nama_tahun";
            $builder = $db->table('jadwal_pelajaran jp');
            $builder->select(implode(',', $select), false);
            $builder->join('agenda_kegiatan ak', "ak.jadwal_id=jp.id", 'left', false);
            $builder->join('m_kelas mk', 'mk.id=jp.kelas_id', 'left');
            $builder->having(['is_mengajar' => 1, 'tanggal >=' => $sdtm, 'tanggal <=' => $edtm]);
            $builder->orHaving('now_is_schedule !=', null);
            if ($tahun_id) $builder->join('tahun_pelajaran tp', "tp.id=jp.tahun_id", '', false)->where('tp.id', $tahun_id);
            if (!$tahun_id) $builder->join('tahun_pelajaran tp', "tp.id=jp.tahun_id AND tp.is_active=1", '', false);
            $rawData = $builder->get()->getResultArray();
            $data = [];
            foreach ($rawData as $d) {
                if ($d['tanggal'] < $sdtm || $d['tanggal'] > $edtm) $d['tanggal'] = null;
                if (!isset($guru[$d['guru_id']])) $guru[$d['guru_id']] = $d['fullname'];
                if (isset($data[$d['guru_id']])) {
                    if ($d['tanggal']) {
                        if (isset($data[$d['guru_id']]['kelasMasuk'][$d['nama_kelas']])) {
                            $data[$d['guru_id']]['kelasMasuk'][$d['nama_kelas']] += $d['countJam'];
                        } else {
                            $data[$d['guru_id']]['kelasMasuk'][$d['nama_kelas']] = $d['countJam'];
                        }
                    } else {
                        if (isset($data[$d['guru_id']]['kelasTidakMasuk'][$d['nama_kelas']])) {
                            $data[$d['guru_id']]['kelasTidakMasuk'][$d['nama_kelas']] += $d['countJam'];
                        } else {
                            $data[$d['guru_id']]['kelasTidakMasuk'][$d['nama_kelas']] = $d['countJam'];
                        }
                    }
                } else {
                    $e = [
                        "fullname" => $d['fullname'],
                        "kelasMasuk" => [],
                        "kelasTidakMasuk" => [],
                    ];
                    if ($d['tanggal']) $e['kelasMasuk'][$d['nama_kelas']] = $d["countJam"];
                    if (!$d['tanggal']) $e['kelasTidakMasuk'][$d['nama_kelas']] = $d["countJam"];

                    $data[$d['guru_id']] = $e;
                }
            }
            $dataMengajar[$k + 1] = $data;
        }
        //create no 
        $row = $startRow;
        $i = 1;
        $s = $startRow - 1;
        $maxRow = $startRow + count($guru) - 1;
        $maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($total_day + 3);
        $smaxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($total_day + 2);
        foreach ($guru as $k => $v) {
            $worksheet->setCellValue("A{$row}", $i++);
            $worksheet->setCellValue("B{$row}", $v);
            $worksheet->setCellValue("{$maxCol}{$row}", "=SUM(C{$row}:{$smaxCol}{$row})");
            $row++;
        }
        $worksheet->getStyle("A{$s}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getInside()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        $worksheet->getStyle("A{$s}:{$maxCol}{$maxRow}")
            ->getBorders()
            ->getOutline()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('00000000'));
        $worksheet->setCellValue("{$maxCol}{$s}", "Total");
        foreach ($dataMengajar as $k => $d) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($k + 2);
            $worksheet->setCellValue("{$col}{$s}", $k);

            if ($d === null) {
                $worksheet->getStyle("{$col}{$s}:{$col}{$maxRow}")
                    ->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setARGB('FFFF0000');
                continue;
            }
            foreach ($d as $gid => $gd) {
                $index = null;
                $sr = $startRow;
                foreach ($guru as $ksg => $sg) {
                    if ($index) continue;
                    if ($ksg == $gid) $index = $sr;
                    $sr++;
                }
                if (!$index) continue;
                $text = "";
                $jamCountMasuk = 0;
                foreach ($gd['kelasMasuk'] as $kkm => $km) {
                    $jamCountMasuk += $km;
                    $text .= "$kkm : $km\n";
                }
                foreach ($gd['kelasTidakMasuk'] as $kktm => $ktm) {
                    $text .= "$kktm : $ktm Tidak Masuk\n";
                }
                $worksheet->setCellValue("{$col}{$index}", "$jamCountMasuk");
                $worksheet->getComment("{$col}{$index}")->getText()->createTextRun($text);
            }
        }
        $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 true;
        }
        return false;
    }
}
