<?php

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

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 $jl, $presenceData, $presenceTime, $karyawan;
    public function __construct()
    {
        parent::__construct();
    }
    public function index()
    {
        return view('Panel/Elearning/Rekap/Karyawan/absensi', ['page_title' => "Rekap Presensi Karyawan"]);
    }
    public function all()
    {
        if (!$this->request->isAJAX() && ENVIRONMENT != 'development') {
            exit('No direct script access allowed');
        }
        $startDate = $this->request->getGet('startDate');
        $endDate = $this->request->getGet('endDate');
        $begin = new DateTime($startDate);
        $end = new DateTime($endDate . " 00:01");
        $status = $this->request->getGet('status');
        $karyawan = $this->request->getGet('karyawan');
        $karyawan = $karyawan ? explode(',', $karyawan) : [];
        $db = db_connect();
        $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->karyawan = [];
        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");
            // for table
            $select = ["TO_BASE64(CONCAT(u.id,'/',IFNULL(dpk.created_at,$sdt))) id"];
            $select[] = "(CASE WHEN dpk.latIn IS NOT NULL AND dpk.lonIn IS NOT NULL THEN REPLACE(CONCAT('$map1',dpk.latIn,',',dpk.lonIn,'$map2'), ' ', '') ELSE null END) google_map_in";
            $select[] = "(CASE WHEN dpk.latOut IS NOT NULL AND dpk.lonOut IS NOT NULL THEN REPLACE(CONCAT('$map1',dpk.latOut,',',dpk.lonOut,'$map2'), ' ', '') ELSE null END) google_map_out";
            $select[] = "u.nomor_induk,u.fullname,dpk.methodIn,methodOut,IFNULL(dpk.status,2) status_presence,dpk.suhuIn,dpk.suhuOut,dpk.timeIn,dpk.timeOut,IFNULL(dpk.created_at,$sdt) created_at";
            //for export 
            $builder = $db->table('users u')->join('daily_presence_karyawan dpk', "dpk.user_id=u.id AND (dpk.created_at>=$sdt AND dpk.created_at<=$edt)", 'left');
            $builder->select(implode(',', $select));
            $builder->join('users_roles ur', 'ur.user_id=u.id AND role_id=6');
            if (in_array($status, ['1', '2', '3', '4']))  $builder->having('status_presence', $status);
            if ($karyawan) $builder->whereIn('u.id', $karyawan);
            $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()
    {
        $startDate = $this->request->getGet('startDate');
        $endDate = $this->request->getGet('endDate');
        $status = $this->request->getGet('status');
        $karyawan = $this->request->getGet('karyawan');
        $karyawan = $karyawan ? explode(',', $karyawan) : [];
        $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();
        $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 = 4;
        $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->getSheet(0);
        $worksheet->setCellValue('A1', strtoupper("rekap presensi kehadiran karyawan $textDate"))->setTitle("REKAP KEHADIRAN")
            ->setCellValue("A{$s1}", "No")->setCellValue("B{$s1}", "Nama Karyawan");
        // ->setCellValue("A2", $textTahun);
        $this->presenceData = [];
        $this->karyawan = [];
        $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 = ["u.id,u.nomor_induk,u.fullname,IFNULL(dpk.status,2) status_presence,timeIn,timeOut"];
            $builder = $db->table('users u')->join('daily_presence_karyawan dpk', "dpk.user_id=u.id AND (dpk.created_at>=$sdt AND dpk.created_at<=$edt)", 'left');
            $builder->select(implode(',', $select));
            $builder->join('users_roles ur', 'ur.user_id=u.id AND role_id=6');
            // if (in_array($status, ['1', '2', '3', '4']))  $builder->having('status_presence', $status);
            if ($karyawan) $builder->whereIn('u.id', $karyawan);
            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->karyawan[$d['id']])) $this->karyawan[$d['id']] = [
                    'fullname' => $d['fullname'],
                ];
                $sdata[$d['id']] = $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 + 4);

        $colH = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 3);
        $worksheet->setCellValue("{$colH}{$s}", "H");
        $colS = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 4);
        $worksheet->setCellValue("{$colS}{$s}", "S");
        $colI = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 5);
        $worksheet->setCellValue("{$colI}{$s}", "I");
        $colA = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 6);
        $worksheet->setCellValue("{$colA}{$s}", "A");
        foreach ($this->karyawan as $k => $v) {
            $worksheet->setCellValue("A{$row}", $i++);
            $worksheet->setCellValue("B{$row}", $v['fullname']);
            $worksheet->setCellValue("{$colH}{$row}", "=COUNTIF(C{$row}:{$maxCol}{$row},\"H\")");
            $worksheet->setCellValue("{$colS}{$row}", "=COUNTIF(C{$row}:{$maxCol}{$row},\"S\")");
            $worksheet->setCellValue("{$colI}{$row}", "=COUNTIF(C{$row}:{$maxCol}{$row},\"I\")");
            $worksheet->setCellValue("{$colA}{$row}", "=COUNTIF(C{$row}:{$maxCol}{$row},\"A\")");
            $row++;
        }
        //create border
        $maxRow = $startRow + count($this->karyawan) - 1;
        $s = $startRow - 1;
        $maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($this->presenceData) + 6);
        $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 + 2);
            $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->findKaryawanIndex($noinduk);
                if ($si) {
                    $pos = $si['position'] + $startRow - 1;
                    $worksheet->setCellValue("{$col}{$pos}", $spd);
                }
            }
        }

        // Sheet2
        $startCol2 = 3;
        $startRow2 = 4;
        $worksheet2 = $spreadsheet->getSheet(1);
        $worksheet2
            ->setCellValue('A1', strtoupper("rekap presensi kehadiran karyawan $textDate"))
            // ->setCellValue("A2", $textTahun)
            ->setTitle('DETAIL ABSENSI')
            ->mergeCells('A4:A5')->mergeCells("B4:B5")
            ->setCellValue("A$startRow2", "NO")
            ->setCellValue("B$startRow2", "Nama");
        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['fullname']);
                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('users u')->select("u.id,dpk.created_at,dpk.timeIn,dpk.timeOut")->join('daily_presence_karyawan dpk', "dpk.user_id=u.id AND ((dpk.created_at>=$sdt AND dpk.created_at<=$edt) OR (dpk.created_at>=$sdt AND dpk.created_at<=$edt))", 'left', false)->where('u.id', $id[0])->get()->getRowArray();
        if (!$exist) return $this->fail(['message' => 'Tidak Valid!']);
        $basicData['updated_at'] = time();
        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 = [
            'user_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_karyawan')->insert($startedData);
        if ($exist['created_at']) $db->table('daily_presence_karyawan')->where(['user_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 findKaryawanIndex($nomorInduk)
    {
        $position = 1;
        foreach ($this->karyawan 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";
        }
    }

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