<?php

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

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

class Absensi extends BC
{
    use ResponseTrait;
    private $presenceTime, $presenceData, $siswa, $jl;
    public function __construct()
    {
        parent::__construct();
    }
    public function index()
    {
        return view('Panel/Elearning/Rekap/Siswa/absensi', ['page_title' => "Rekap Presensi Siswa"]);
    }
    public function kelas()
    {
        $db = db_connect();
        $kelas_id = $this->request->getGet('kelas_id');
        if (!$kelas_id) $kelas_id = '0';
        $kelas_id = implode(',', array_unique(explode(',', $kelas_id)));
        $date = $this->request->getGet('date') ?? date('Y-m-d');
        $sdt = strtotime($date . " 00:00:00");
        $edt = strtotime($date . " 23:59:59");
        $esdate = $db->escape($date);
        $select = ["mt.kode kode_tingkat,mk.nama nama_kelas,COUNT(*) total_siswa"];
        $select[] = "COUNT(CASE WHEN dps.status = '1' THEN 1 ELSE NULL END) hadir";
        $select[] = "COUNT(CASE WHEN dps.status = '3' THEN 1 ELSE NULL END) sakit";
        $select[] = "COUNT(CASE WHEN dps.status = '4' THEN 1 ELSE NULL END) izin";
        $select[] = "COUNT(CASE WHEN dps.status IS NULL OR dps.status = '2' THEN 1 ELSE NULL END) alpa";
        $builder = $db->table('m_kelas mk')->join('kelas_data kd', 'kd.kelas_id=mk.id');
        $builder->select(implode(',', $select), false);
        $builder->join('m_tingkat mt', 'mt.id=kd.tingkat_id');
        $builder->join('m_siswa ms', 'ms.kelas_id=mk.id');
        $builder->join('jadwal_libur jl', "jl.tanggal=$esdate AND is_nasional=1", 'left');
        $builder->join('daily_presence_siswa dps', "dps.siswa_id=ms.id AND (dps.created_at>=$sdt AND dps.created_at<=$edt)", 'left');
        $builder->where("mk.id IN ($kelas_id)", null, false);
        $builder->where('jl.tanggal', null);
        $builder->groupBy('mk.id');
        return $this->respond($builder->get()->getResultArray());
    }
    public function all()
    {
        $kelas_id = $this->request->getGet('kelas_id');
        if (!$kelas_id) $kelas_id = '0';
        // if (!$month || !$year) return $this->fail(['message' => "Tanggal Tidak Valid"]);


        $status = $this->request->getGet('status');
        $startDate = $this->request->getGet('startDate');
        $endDate = $this->request->getGet('endDate');
        $begin = new DateTime($startDate);
        $end = new DateTime($endDate . " 00:01");

        $db = db_connect();
        $kelas_data = $db->table('m_kelas')->select('nama')->whereIn('id', explode(',', $kelas_id))->get()->getResultArray();
        $kelas_name = [];
        foreach ($kelas_data as $kd) {
            $kelas_name[] = $kd['nama'];
        }
        $kelas_name = implode(',', $kelas_name);
        // if (empty($kelas_name)) return $this->fail(['message' => "Kelas Tidak Valid"]);
        $this->jl = $db->table('jadwal_libur')->where("(tanggal BETWEEN '$startDate' AND '$endDate')", null, false)->where('is_nasional', 1, false)->get()->getResultArray();
        $interval = DateInterval::createFromDateString('next day');
        $period = new DatePeriod($begin, $interval, $end);
        $map1 = "https://maps.google.com/maps?q=";
        $map2 = "&hl=id&z=14&amp;output=embed";

        $this->presenceData = [];
        $this->siswa = [];
        foreach ($period as $k => $dt) {
            $parsedDate = $dt->format("Y-m-d");
            $il = $this->is_libur($parsedDate);
            if ($il) continue;

            if (strtotime($parsedDate) > time()) continue;

            $sdt = strtotime("$parsedDate 00:00:00");
            $edt = strtotime("$parsedDate 23:59:59");
            // if ($this->request->getGet('today') && $parsedDate != "2022-01-08") continue;
            // for table
            $select = ["TO_BASE64(CONCAT(ms.id,'/',IFNULL(dps.created_at,$sdt))) id"];
            $select[] = "(CASE WHEN dps.latIn IS NOT NULL AND dps.lonIn IS NOT NULL THEN REPLACE(CONCAT('$map1',dps.latIn,',',dps.lonIn,'$map2'), ' ', '') ELSE null END) google_map_in";
            $select[] = "(CASE WHEN dps.latOut IS NOT NULL AND dps.lonOut IS NOT NULL THEN REPLACE(CONCAT('$map1',dps.latOut,',',dps.lonOut,'$map2'), ' ', '') ELSE null END) google_map_out";
            $select[] = "ms.nomor_induk,ms.nama,dps.methodIn,dps.methodOut,IFNULL(dps.status,2) status_presence,dps.suhuIn,dps.suhuOut,dps.timeIn,dps.timeOut,IFNULL(dps.created_at,$sdt) created_at";
            $select[] = "mk.nama nama_kelas";
            //for export 
            // $select = ["ms.nomor_induk,mk.nama nama_kelas,ms.nama,IFNULL(dps.status,2) status_presence"];
            $builder = $db->table('m_siswa ms')->join('daily_presence_siswa dps', "dps.siswa_id=ms.id AND (dps.created_at>=$sdt AND dps.created_at<=$edt)", 'left');
            $builder->select(implode(',', $select));
            $builder->join('m_kelas mk', 'mk.id=ms.kelas_id', 'left');
            $builder->where("ms.kelas_id IN ($kelas_id)", null, false);
            if (in_array($status, ['1', '2', '3', '4']))  $builder->having('status_presence', $status);
            $rawData = $builder->get()->getResultArray();
            if ($rawData) {
                $this->presenceData = [...$rawData, ...$this->presenceData];
            }
        }
        return $this->respond(['absensi' => $this->presenceData, 'allow_edit' => user()->hasRole('admin'), "csrf" => ['name' => csrf_token(), 'value' => csrf_hash()]]);
    }
    public function export()
    {
        $kelas_id = $this->request->getGet('kelas_id');
        $status = $this->request->getGet('status');
        $startDate = $this->request->getGet('startDate');
        $endDate = $this->request->getGet('endDate');
        $begin = new DateTime($startDate);
        $end = new DateTime($endDate . " 00:01");

        // Creates DateTime objects
        $datetime1 = date_create($startDate);
        $datetime2 = date_create($endDate);

        // Calculates the difference between DateTime objects
        $interval = date_diff($datetime1, $datetime2);

        // Display the result
        $total_day = $interval->format('%a');

        $db = db_connect();
        $kelas_data = $db->table('m_kelas')->select('nama')->whereIn('id', explode(',', $kelas_id))->get()->getResultArray();
        $tahun_pelajaran = $db->table('tahun_pelajaran')->where('is_active', 1)->get()->getRowArray();
        $textTahun = $tahun_pelajaran ? strtoupper("tahun pelajaran $tahun_pelajaran[nama] $tahun_pelajaran[semester]") : "";
        $kelas_name = [];
        foreach ($kelas_data as $kd) {
            $kelas_name[] = $kd['nama'];
        }
        $kelas_name = implode(',', $kelas_name);
        if (empty($kelas_name)) return $this->fail(['message' => "Kelas Tidak Valid"]);
        $this->jl = $db->table('jadwal_libur')->where("(tanggal BETWEEN '$startDate' AND '$endDate')", null, false)->where('is_nasional', 1, false)->get()->getResultArray();
        $interval = DateInterval::createFromDateString('next day');
        $period = new DatePeriod($begin, $interval, $end);
        $startRow = 5;
        $s1 = $startRow - 1;
        $range = $startDate == $endDate ? $startDate : "$startDate-$endDate";
        $path = WRITEPATH . "/uploads/rekap-presensi-harian-$range.xlsx";
        $td1 = date("d", strtotime($startDate)) . " " . bulanIndo(date('m', strtotime($startDate))) . " " . date('Y', strtotime($startDate));
        $td2 = date("d", strtotime($endDate)) . " " . bulanIndo(date('m', strtotime($endDate))) . " " . date('Y', strtotime($endDate));
        $textDate = $td1 == $td2 ? $td1 : "$td1 - $td2";
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $spreadsheet->createSheet(1);
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setCellValue('A1', strtoupper("rekap presensi kehadiran siswa $textDate"))->setTitle("REKAP KEHADIRAN")
            ->setCellValue("A{$s1}", "No")->setCellValue("B{$s1}", "Nomor Induk")->setCellValue("C{$s1}", "Nama Siswa")->setCellValue("D{$s1}", "Kelas")
            ->setCellValue("A2", $textTahun);
        $this->presenceData = [];
        $this->siswa = [];
        $this->presenceTime = [];
        foreach ($period as $k => $dt) {
            $parsedDate = $dt->format("Y-m-d");
            $il = $this->is_libur($parsedDate);
            if ($il) {
                $this->presenceData[$dt->format('Y-m-d')] = $il;
                continue;
            }

            if (strtotime($parsedDate) > time()) {
                $this->presenceData[$dt->format('Y-m-d')] = $il;
                continue;
            }
            $sdt = strtotime("$parsedDate 00:00:00");
            $edt = strtotime("$parsedDate 23:59:59");
            //for export 
            $select = ["ms.nomor_induk,mk.nama nama_kelas,ms.nama,IFNULL(dps.status,2) status_presence,timeIn,timeOut"];
            $builder = $db->table('m_siswa ms')->join('daily_presence_siswa dps', "dps.siswa_id=ms.id AND (dps.created_at>=$sdt AND dps.created_at<=$edt)", 'left');
            $builder->select(implode(',', $select));
            $builder->join('m_kelas mk', 'mk.id=ms.kelas_id', 'left');
            $builder->whereIn('ms.kelas_id', explode(',', $kelas_id));
            if (in_array($status, ['1', '2', '3', '4']))  $builder->having('status_presence', $status);
            $rawData = $builder->get()->getResultArray();
            $sdata = [];
            foreach ($rawData as $d) {
                if (!isset($this->siswa[$d['nomor_induk']])) $this->siswa[$d['nomor_induk']] = [
                    'nama' => $d['nama'],
                    "kelas" => $d['nama_kelas']
                ];
                $sdata[$d['nomor_induk']] = $this->getKeterangan($d['status_presence']);
            }
            $this->presenceTime[$dt->format("Y-m-d")] = $rawData;
            $this->presenceData[$dt->format('Y-m-d')] = $sdata;
        }

        //create no 
        $row = $startRow;
        $i = 1;
        $s = $startRow - 1;
        $maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($total_day + 5);

        $colH = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 5);
        $worksheet->setCellValue("{$colH}{$s}", "H");
        $colS = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 6);
        $worksheet->setCellValue("{$colS}{$s}", "S");
        $colI = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 7);
        $worksheet->setCellValue("{$colI}{$s}", "I");
        $colA = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 8);
        $worksheet->setCellValue("{$colA}{$s}", "A");

        foreach ($this->siswa as $k => $v) {
            $worksheet->setCellValue("A{$row}", $i++);
            $worksheet->setCellValue("B{$row}", $k);
            $worksheet->setCellValue("C{$row}", $v['nama']);
            $worksheet->setCellValue("D{$row}", $v['kelas']);
            $worksheet->setCellValue("{$colH}{$row}", "=COUNTIF(E{$row}:{$maxCol}{$row},\"H\")");
            $worksheet->setCellValue("{$colS}{$row}", "=COUNTIF(E{$row}:{$maxCol}{$row},\"S\")");
            $worksheet->setCellValue("{$colI}{$row}", "=COUNTIF(E{$row}:{$maxCol}{$row},\"I\")");
            $worksheet->setCellValue("{$colA}{$row}", "=COUNTIF(E{$row}:{$maxCol}{$row},\"A\")");
            $row++;
        }
        //create border
        $maxRow = $startRow + count($this->siswa) - 1;
        $s = $startRow - 1;
        $maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 8);
        $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'));
        $k = 0;
        foreach ($this->presenceData as $d => $pd) {
            $k++;
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($k + 4);
            $worksheet->setCellValue("{$col}{$s}", date('d ', strtotime($d)) . bulanIndo(date('m', strtotime($d))));
            if (!is_array($pd)) {
                $worksheet->getStyle("{$col}{$s}:{$col}{$maxRow}")
                    ->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setARGB('FFFF0000');
                $worksheet->getComment("{$col}{$s}")->getText()->createTextRun($pd);
                continue;
            }
            foreach ($pd as $noinduk => $spd) {
                $si = $this->findSiswaIndex($noinduk);
                if ($si) {
                    $pos = $si['position'] + $startRow - 1;
                    $worksheet->setCellValue("{$col}{$pos}", $spd);
                }
            }
        }


        // Sheet2
        $startCol2 = 4;
        $startRow2 = 4;
        $worksheet2 = $spreadsheet->getSheet(1);
        $worksheet2
            ->setCellValue('A1', strtoupper("rekap presensi kehadiran guru $textDate"))
            ->setCellValue("A2", $textTahun)->setTitle('DETAIL ABSENSI')
            ->setCellValue("A$startCol2", "NO")->setCellValue("B$startCol2", "Nomor Induk")
            ->setCellValue("C$startCol2", "Nama");
        $worksheet2->mergeCells('A4:A5')->mergeCells("B4:B5")->mergeCells("C4:C5");
        foreach ($this->presenceTime as $month => $value) {
            $colD = 1;
            $i = 1;
            $rowD = 6;
            foreach ($value as $key => $presenceTime) {
                $worksheet2->setCellValueByColumnAndRow($colD, $rowD, $i++);
                $worksheet2->setCellValueByColumnAndRow($colD + 1, $rowD, $presenceTime['nomor_induk']);
                $worksheet2->setCellValueByColumnAndRow($colD + 2, $rowD, $presenceTime['nama']);
                if ($presenceTime['timeIn']) {
                    $worksheet2->setCellValueByColumnAndRow($startCol2, $rowD, date("H:i", $presenceTime['timeIn']));
                } else {
                    if ($month > date('Y-m-d')) {
                        $worksheet2->getStyleByColumnAndRow($startCol2, $rowD)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF0000');
                    } else {
                        $worksheet2->setCellValueByColumnAndRow($startCol2, $rowD, "X");
                        $worksheet2->getStyleByColumnAndRow($startCol2, $rowD)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
                    }
                }
                if ($presenceTime['timeOut']) {
                    $worksheet2->setCellValueByColumnAndRow($startCol2 + 1, $rowD, date("H:i", $presenceTime['timeOut']));
                } else {
                    if ($month > date('Y-m-d')) {
                        $worksheet2->getStyleByColumnAndRow($startCol2 + 1, $rowD)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF0000');
                    } else {
                        $worksheet2->setCellValueByColumnAndRow($startCol2 + 1, $rowD, "X");
                        $worksheet2->getStyleByColumnAndRow($startCol2 + 1, $rowD)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
                    }
                }

                $rowD++;
            }
            $worksheet2->mergeCellsByColumnAndRow($startCol2, $startRow2, $startCol2 + 1, $startRow2);
            $worksheet2->setCellValueByColumnAndRow($startCol2, $startRow2, date('d ', strtotime($month)) . bulanIndo(date('m', strtotime($month))));
            $worksheet2->setCellValueByColumnAndRow($startCol2, $startRow2 + 1, 'Masuk');
            $worksheet2->setCellValueByColumnAndRow($startCol2 + 1, $startRow2 + 1, 'Keluar');

            $startCol2 += 2;
        }
        $spreadsheet->setActiveSheetIndex(0);
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");
        $writer->save($path);
        return $this->response->download($path, null);
    }
    public function save($id)
    {
        $id = explode('/', base64_decode($id));
        if (count($id) < 2) return $this->fail(['Tidak Valid!']);
        $id[1] = date("Y-m-d", $id[1]);
        $sdt = strtotime("$id[1] 00:00:00");
        $edt = strtotime("$id[1] 23:59:59");
        $db = db_connect();
        $exist = $db->table('m_siswa ms')->select("ms.id,dps.created_at,dps.timeIn,dps.timeOut")->join('daily_presence_siswa dps', "dps.siswa_id=ms.id AND ((dps.created_at>=$sdt AND dps.created_at<=$edt) OR (dps.created_at>=$sdt AND dps.created_at<=$edt))", 'left', false)->where('ms.id', $id[0])->get()->getRowArray();
        if (!$exist) return $this->fail(['Tidak Valid!']);
        $basicData['updated_at'] = date("Y-m-d H:i:s");
        if ($this->request->getVar('status', FILTER_VALIDATE_INT)) $basicData['status'] = $this->request->getVar('status', FILTER_VALIDATE_INT);
        $timeIn = $this->request->getVar('timeIn');
        if ($timeIn) {
            $rules['timeIn'] = [
                'rules' => "integer",
            ];
            if (!$this->validate($rules) && $timeIn != 'null') {
                return $this->fail(['message' => "Waktu Masuk Tidak Valid"]);
            }
            $basicData['timeIn'] = strlen($timeIn) < 10 ? NULL : $timeIn;
        };
        $timeOut = $this->request->getVar('timeOut');
        if ($timeOut) {
            $rules['timeOut'] = [
                'rules' => "integer",
            ];
            if (!$this->validate($rules) && $timeOut != 'null') {
                return $this->fail(['message' => "Waktu Masuk Tidak Valid"]);
            }
            $basicData['timeOut'] = strlen($timeOut) < 10 ? NULL : $timeOut;
        };
        $startedData = [
            'siswa_id' => $id[0],
            'created_at' => strtotime("$id[1] " . date("H:i:s")),
            'updated_at' => time(),
        ];
        foreach ($basicData as $k => $bd) {
            $startedData[$k] = $bd;
        }
        $db->transStart();
        if (!$exist['created_at']) $db->table('daily_presence_siswa')->insert($startedData);
        if ($exist['created_at']) $db->table('daily_presence_siswa')->where(['siswa_id' => $id[0], "created_at" => $exist['created_at']])->set($basicData)->update();
        $db->transComplete();
        if ($db->transStatus() === FALSE) return $this->fail(['message' => "Gagal Menyimpan Status"]);
        return $this->respond(['message' => "Berhasil Menyimpan Status"]);
    }

    private function findSiswaIndex($nomorInduk)
    {
        $position = 1;
        foreach ($this->siswa as $i => $ni) {
            if ($i == $nomorInduk) {
                $ni['position'] = $position;
                return $ni;
            }
            $position++;
        }
        return false;
    }
    private function getPresenceDataByDate($date)
    {
        $data = [];
        foreach ($this->presenceData as $pd) {
            if ($pd['created_at']);
        }
        return $data;
    }

    private function getKeterangan($i)
    {
        switch ($i) {
            case "0":
                return "A";
            case "2":
                return "A";
            case "1":
                return "H";
            case "3":
                return "S";
            case "4":
                return "I";
            default:
                return "A";
        }
    }
    public function list_kelas()
    {
        $builder = db_connect()->table('m_kelas mk');
        $builder->select("mk.*,mt.id tingkat_id,mt.kode kode_tingkat,mj.id jurusan_id,mj.kode kode_jurusan,mj.nama nama_jurusan");
        $builder->join("kelas_data kd", "kd.kelas_id=mk.id", "left");
        $builder->join("m_tingkat mt", "mt.id=kd.tingkat_id", "left");
        $builder->join("m_jurusan mj", "mj.id=kd.jurusan_id", "left");
        if (!user()->hasRole(['admin', 'piket'])) {
            $builder->join('jadwal_pelajaran jp', 'jp.kelas_id=mk.id');
            $builder->where('jp.user_id', user()->id);
        }
        $data =  $builder->get()->getResultArray();
        return $this->respond(['data' => $data, 'csrf' => ['name' => csrf_token(), 'value' => csrf_hash()]]);
    }

    private function is_libur($tanggal)
    {
        foreach ($this->jl as $jl) {
            if ($jl['tanggal'] == $tanggal) return $jl['title'];
        }
        return false;
    }
}
