<?php
use Jaga\Models\PAK\PAKIsianSekolah;
use Jaga\Models\PAK\PAKIsianSekolahAttachment;
use Jaga\Models\PAK\PAKIsianSekolahDetail;

class SekolahController extends BaseController
{
    public function testMadrasah()
    {
        $client = new MadrasahClient($this->config["madrasah_prop"]);
        $result = $client->get_data("20622329", "lbg");
        return $result;
    }

    public function index()
    {
        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['nama'] = array_key_exists('nama', $getParams) ? $getParams['nama'] : '';
        $getParams['npsn'] = array_key_exists('npsn', $getParams) ? $getParams['npsn'] : '';
        $getParams['kota'] = array_key_exists('kota', $getParams) ? $getParams['kota'] : '';
        $getParams['provinsi'] = array_key_exists('provinsi', $getParams) ? $getParams['provinsi'] : '';
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';

        $validatedRequest = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        if (array_key_exists('npsn', $getParams)) {
            return $this->detail();
        }

        //NOTE: Kodingan di bawah unreachable, karena npsn required

        $model = new Sekolah();

        $result = $model->search(
            $getParams['keyword'],
            $getParams['nama'],
            $getParams['npsn'],
            $getParams['kota'],
            $getParams['provinsi'],
            $getParams['limit'],
            $getParams['offset']
        )
            ->toArray();

        $totalResult = count($result);
        return new PaginatedResponse($result, $totalResult, $totalResult, 0);
    }

    function is_json($string) {
        $decoded = json_decode($string);
        if ( !is_object($decoded) && !is_array($decoded) ) {
            return false;
        }
        return json_last_error() === JSON_ERROR_NONE;
    }

    public function search()
    {
        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['nama'] = array_key_exists('nama', $getParams) ? $getParams['nama'] : '';
        $getParams['jenjang'] = array_key_exists('jenjang', $getParams) ?  $getParams['jenjang'] : -1;
        $getParams['kota'] = array_key_exists('kota', $getParams) ? $getParams['kota'] : '';
        $getParams['id_provinsi'] = array_key_exists('id_provinsi', $getParams) ? $getParams['id_provinsi'] : 0;
        $getParams['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $getParams) ? $getParams['id_kota_kabupaten'] : 0;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : -1;
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? $getParams['order_by'] : "nama";
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? $getParams['order_direction'] : "asc";

        $allowedOrder = ['nama'];
        $allowedDirection = ['asc', 'desc'];
        $mappedOrder = [
            'nama' => 'TRIM(LEADING FROM sekolah.nama)'
        ];

        $validatedRequest = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => ['validators' => [
                    [
                        'name' => 'inclusion_in',
                        'params' => $allowedOrder
                    ]
                ]],
                'order_direction' => ['validators' => [
                    [
                        'name' => 'inclusion_in',
                        'params' => $allowedDirection
                    ]
                ]],
            ]
        );
        $orderBy = $mappedOrder[$validatedRequest['order_by']];

        $OR = '';
        $ORcount = '';
        $arrBind = [
            'nama' => '%' . $getParams['nama'] . '%'
        ];

        if($getParams['id_kota_kabupaten'] > 0) {
            // update 20210901, API detail sekolah dari Kemdikbud include kode dagri wilayah sekolah
            // update 20230918, semua data sekolah telah memiliki kode dagri wilayah sekolah
            $OR .= "kode_kabupaten_dagri = :kota_kabupaten_code";
            $ORcount .= "kode_kabupaten_dagri = :kota_kabupaten_code:";
            $arrBind['kota_kabupaten_code'] = $getParams['id_kota_kabupaten'];
        }

        else if($getParams['id_provinsi'] > 0) {
            // update 20210901, API detail sekolah dari Kemdikbud include kode dagri wilayah sekolah
            // update 20230918, semua data sekolah telah memiliki kode dagri wilayah sekolah
            $OR .= "kode_provinsi_dagri = :provinsi_code";
            $ORcount .= "kode_provinsi_dagri = :provinsi_code:";
            $arrBind['provinsi_code'] = $getParams['id_provinsi'];
        }
        else {
            $arrBind = array_merge($arrBind, ['kota' => '%' . strtolower($getParams['kota']) . '%']);
            $OR .= "lower(kota_kab) like :kota
                    OR lower(master_kota_kabupaten.kota_kabupaten_name) like :kota
                    OR lower(provinsi) like :kota
                    OR lower(master_provinsi.provinsi_name) like :kota";
            $ORcount .= "lower(kota_kab) like :kota:
                    OR lower(MasterKotaKabupaten.kota_kabupaten_name) like :kota:
                    OR lower(provinsi) like :kota:
                    OR lower(MasterProvinsi.provinsi_name) like :kota:";
        }

        $where = '('.$OR.') and nama ilike :nama and sekolah.deleted_at IS NULL';
        $whereCount = '('.$ORcount.') and lower(nama) like :nama: and Sekolah.deleted_at IS NULL';

        if($this->is_json($getParams['jenjang'])) {
            $where .= ' AND jenjang.id IN ('.implode(",", array_values(json_decode($getParams['jenjang']))).')';
            $whereCount .= ' AND Jenjang.id IN ({jenjangCount:array})';
            $arrBind['jenjangCount'] = array_values(json_decode($getParams['jenjang']));
        }
        else if ($getParams['jenjang'] > 0) {
            $where .= ' and jenjang.id = :jenjang';
            $whereCount .= ' and Jenjang.id = :jenjang:';
            $arrBind['jenjang'] = $getParams['jenjang'];
        }
        else if ($getParams['id_instansi'] > 0) {
            $instansi = Instansi::findOrFail("id = " . $getParams['id_instansi']);
            $jenjangs = Jenjang::findByInstansiTipe($instansi->id_tipe);
            $arrJenjang = [];
            foreach ($jenjangs as $jenjang) {
                array_push($arrJenjang, $jenjang->id);
            }
            if (sizeof($arrJenjang) > 0) {
                $where .= ' AND jenjang.id IN ('.implode(",", $arrJenjang).')';
                $whereCount .= ' AND Jenjang.id IN ({jenjangCount:array})';
                $arrBind['jenjangCount'] = $arrJenjang;
            }
            else {
                $where .= ' AND jenjang.id = 0';
                $whereCount .= ' AND Jenjang.id = 0';
            }
        }

        // Show PAK Condition
        $arrBind['pak_status'] = \Jaga\Models\PAK\PAKIsianSekolah::STATUS_VERIFIED;
        $arrBind['is_published'] = true;

        $totalResult = Sekolah::query()
            ->columns('count(distinct npsn) as jumlah')
            ->leftjoin('Jenjang', 'Sekolah.jenjang_id = Jenjang.id')
            ->leftjoin('MasterKecamatan', 'Sekolah.kecamatan_code = MasterKecamatan.kecamatan_code')
            ->leftjoin('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code=MasterKecamatan.kota_kabupaten_code')
            ->leftjoin('MasterProvinsi', 'MasterProvinsi.provinsi_code=MasterKotaKabupaten.provinsi_code')
            ->leftjoin('Jaga\Models\PAK\PAKIsianSekolah', 'Sekolah.npsn=Jaga\Models\PAK\PAKIsianSekolah.sekolah_npsn AND Jaga\Models\PAK\PAKIsianSekolah.status = :pak_status: AND Jaga\Models\PAK\PAKIsianSekolah.is_published = :is_published:')
            ->where($whereCount)
            ->bind($arrBind)
            ->execute()[0]['jumlah'];
        unset($arrBind['jenjangCount']);

        $arrBind['limit'] = $validatedRequest['limit'];
        $arrBind['offset'] = $validatedRequest['offset'];

        $q = "
            SELECT DISTINCT TRIM(LEADING FROM sekolah.nama) AS nama, COALESCE(master_kota_kabupaten.kota_kabupaten_name, sekolah.kota_kab) AS kota_kab, sekolah.kode_kabupaten_dagri, COALESCE(master_provinsi.provinsi_name, sekolah.provinsi) AS provinsi, sekolah.kode_provinsi_dagri, sekolah.npsn AS npsn, sekolah.kepsek AS kepsek, sekolah.tanggal_update_sekolah AS tanggal_update_sekolah, sekolah.latitude AS latitude, sekolah.longitude AS longitude, sekolah.jenjang_id AS jenjang_id, jenjang.nama_jenjang AS nama_jenjang, COALESCE(pak_isian_sekolah.status = :pak_status AND pak_isian_sekolah.is_published = :is_published, FALSE) AS is_pak, COUNT(sekolah_like.sekolah_npsn) AS liked_count, 0 AS is_liked, 'sekolah' AS src  
            FROM jaga.sekolah  LEFT JOIN jaga.jenjang ON sekolah.jenjang_id = jenjang.id LEFT JOIN jaga.master_kota_kabupaten ON master_kota_kabupaten.kota_kabupaten_code = sekolah.kode_kabupaten_dagri LEFT JOIN jaga.master_provinsi ON master_provinsi.provinsi_code = sekolah.kode_provinsi_dagri LEFT JOIN jaga.pak_isian_sekolah ON sekolah.npsn = pak_isian_sekolah.sekolah_npsn AND pak_isian_sekolah.status = :pak_status AND pak_isian_sekolah.is_published = :is_published LEFT JOIN jaga.sekolah_like ON sekolah.npsn = sekolah_like.sekolah_npsn 
            WHERE ".$where." 
            GROUP BY sekolah.nama, sekolah.kota_kab, sekolah.kode_kabupaten_dagri, master_kota_kabupaten.kota_kabupaten_name, sekolah.provinsi, sekolah.kode_provinsi_dagri, master_provinsi.provinsi_name, sekolah.npsn, sekolah.kepsek, sekolah.tanggal_update_sekolah, sekolah.latitude, sekolah.longitude, sekolah.jenjang_id, jenjang.nama_jenjang, pak_isian_sekolah.status, pak_isian_sekolah.is_published, sekolah_like.sekolah_npsn 
            ORDER BY ".$orderBy." ".$validatedRequest['order_direction']." 
            LIMIT :limit OFFSET :offset
        ";
        $result = $this->rawQuery($q, $arrBind);

        $resultNpsn = [];
        foreach ($result as $item) {
            $resultNpsn[] = $item['npsn'];
        }

        if(!empty($resultNpsn) && $this->userID != null) {
            $liked = array_column(SekolahLike::find([
                'conditions' => 'user_id = :user_id: AND sekolah_npsn IN ({result_npsn:array})',
                'bind' => [
                    'user_id' => $this->userID,
                    'result_npsn' => $resultNpsn
                ]
            ])->toArray(),
            'sekolah_npsn'
            );

            if(!empty($liked)) {
                foreach ($result as $key => $value) {
                    $result[$key]['is_liked'] = in_array($value['npsn'], $liked) ? 1 : 0;
                }
            }
        }

        return new PaginatedResponse($result, $totalResult, $getParams['limit'], $getParams['offset']);
    }

    public function detail()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'npsn' => ['validators' => ['required']],
            ]
        );

        $sekolah = Sekolah::findFirstByNpsn($params["npsn"]);

        if (empty($sekolah)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Not Found');
        }

        $now = new DateTime(date('Y-m-d H:i:s'));
        $last_update =  empty($sekolah->updated_at)
            ? new DateTime('2000-01-01')
            : new DateTime($sekolah->updated_at);

        $interval = $now->diff($last_update);

        $liveData = true;
        $errMessage = '';

        if (
            $sekolah->jenjang->nama_jenjang == Sekolah::MADRASAH_JENJANG_MAS
            || $sekolah->jenjang->nama_jenjang == Sekolah::MADRASAH_JENJANG_MAN
        ) {
            try {
                $client = new MadrasahClient($this->config["madrasah_prop"]);
                $client->setLogger($this->logger);
                $resultProfil = $client->get_data($params['npsn'], Sekolah::MADRASAH_JENISDATA_PROFIL);

                if (!empty($resultProfil) && count($resultProfil) > 0) {
                    //var_dump($resultProfil);
                    SekolahHelper::updateMadrasah($sekolah, $resultProfil[0], Sekolah::MADRASAH_JENISDATA_PROFIL);
                }

                $resultSarpras = $client->get_data($params['npsn'], Sekolah::MADRASAH_JENISDATA_SARPRAS);
                if (!empty($resultSarpras) && count($resultSarpras) > 0) {
                    SekolahHelper::updateMadrasah($sekolah, $resultSarpras[0], Sekolah::MADRASAH_JENISDATA_SARPRAS);
                }

                $resultSanitasi = $client->get_data($params['npsn'], Sekolah::MADRASAH_JENISDATA_SANITASI);
                if (!empty($resultSanitasi) && count($resultSanitasi) > 0) {
                    SekolahHelper::updateMadrasah($sekolah, $resultSanitasi[0], Sekolah::MADRASAH_JENISDATA_SANITASI);
                }
            } catch (\Exception $ex) {
                $liveData = false;
                $errMessage = $ex->getMessage();
            }

            $output = $sekolah->toArray();
            $output['source'] = "KEMENAG";
            $output['is_live_data'] = $liveData;
            $output['error_message'] = $errMessage;
        } else {
            try {
                $pegawai = $sekolah->SekolahPegawai;

                $updateLimit = AppConfig::getIntValueByKode('SEKOLAH_DAYS_UPDATE');

                if ($interval->days > $updateLimit) {
                    $client = new SekolahNewClient($this->config["sekolah_new_prop"]);
                    $client->setLogger($this->logger);
                    $res = $client->getSekolah($params['npsn']);
                    if (!empty($res) && count($res) > 0) {
                        SekolahHelper::updateSekolah($sekolah, $res);
                    }

                    $pegawai = $this->doGetListGuru($params["npsn"]);
                    if (is_array($pegawai) && !empty($pegawai)) {
                        $this->db->begin();
                        try {
                            $this->rawQuery("DELETE FROM jaga.sekolah_pegawai WHERE npsn = :npsn", ['npsn' => $params["npsn"]]);
                            SekolahPegawai::saveForNpsn($params["npsn"], $pegawai);
                            $sekolah->last_update_pegawai = date('Y-m-d H:i:s');
                            $sekolah->save();
                            $this->db->commit();
                        } catch (\Exception $e) {
                            $this->db->rollback();
                            throw $e;
                        }
                    }
                }
            } catch (\Exception $ex) {
                $liveData = false;
                $errMessage = $ex->getMessage();
            }

            $output = $sekolah->toArray();
            $output['pegawai'] = $pegawai;
            $output['source'] = "KEMDIKBUD";
            $output['is_live_data'] = $liveData;
            $output['error_message'] = $errMessage;
        }

        if ($this->shared) {
            $service = new ActivityService($this->shared, $this->logger);
            $service->createActivity(UserActivity::PRODUK_PENDIDIKAN, $sekolah->npsn);
        }

        //get if sekolah already liked by user
        $liked = SekolahLike::count([
            "conditions" => "sekolah_npsn = ?0 AND user_id = ?1",
            "bind" => [
                $params['npsn'],
                $this->userID
            ]
        ]);

        $output["liked"] = ($liked > 0);

        if (strtolower($output['source']) == 'kemenag') {
            $years = [2019];
        } else {
            /*try {
                $bosClient = new NeoBosClient($this->config["neo_bos_prop"]);
                $bosClient->setLogger($this->logger);
                $result = $bosClient->getYears();
                if ($result['message'] == 'Sukses') {
                    $years = array_values($result['data']);
                } else {
                    $years = [2020, 2021, 2022, 2023];
                }
            } catch (\Exception $e) {
                $years = [2020, 2021, 2022, 2023, 2024];
            }*/
            $years = [2020, 2021, 2022, 2023, 2024, 2025, 2026];
        }
        $output['bos_years'] = $years;


        return new CollectionPaginatedResponse([$output]);
    }

    public function findAndUpdate($npsn)
    {
        $this->checkScopes(['pak.manage.user']);

        $liveData = false;
        $errMessage = '';
        $source = 'JAGA';

        $sekolah = Sekolah::findFirstByNpsn($npsn);

        if (empty($sekolah)) {
            $sekolah = new Sekolah();
            $client = new SekolahNewClient($this->config["sekolah_new_prop"]);
            $client->setLogger($this->logger);
            $client->initializeJenjangLookup();
            try {
                $result =  $client->getSekolah($npsn);
                if (!is_array($result) && is_string($result)) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, $result);
                }
            } catch (CustomErrorException $e) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, "Data sekolah tidak tersedia di Dapodik", [$e->getMessages()]);
            } catch (\Exception $e) {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Proses pencarian sekolah di Dapodik gagal. Mohon coba beberapa saat lagi", [$e->getMessage()]);
            }

            if (!empty($result) && count($result) > 0) {
                try {
                    $client->updateSekolah($sekolah, $result);
                    $sekolah = Sekolah::findFirstByNpsn($npsn);
                    $source = "KEMDIKBUD";
                    $liveData = true;
                } catch (\Exception $e) {
                    throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Pembaruan data sekolah sedang dalam pemeliharaan. Mohon coba beberapa saat lagi. Jika masih terdapat kendala silakan hubungi Admin JAGA", [$e->getMessage()]);
                }
            }
        }

        #validasi data sekolah berdasarkan kota/kab dan provinsi
        $params = $this->request->get();
        $params['id_provinsi'] = array_key_exists('id_provinsi', $params) ? $params['id_provinsi'] : 0;
        $params['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : 0;
        $params['id_instansi'] = array_key_exists('id_instansi', $params) ? $params['id_instansi'] : -1;
        $params['jenjang'] = array_key_exists('jenjang', $params) ? $params['jenjang'] : 0;

        $where = "npsn = :npsn:";
        $arrBind = ['npsn' => $npsn];
        if($this->is_json($params['jenjang'])) {
            $where .= ' AND Jenjang.id IN ({jenjang:array})';
            $arrBind['jenjang'] = array_values(json_decode($params['jenjang']));
        }
        else if ($params['jenjang'] > 0) {
            $where .= ' and Jenjang.id = :jenjang:';
            $arrBind['jenjang'] = $params['jenjang'];
        }
        else if ($params['id_instansi'] > 0) {
            $instansi = Instansi::findOrFail("id = " . $params['id_instansi']);
            if ($instansi->id_provinsi != 31) {
                $jenjangs = Jenjang::findByInstansiTipe($instansi->id_tipe);
                $arrJenjang = [];
                foreach ($jenjangs as $jenjang) {
                    array_push($arrJenjang, $jenjang->id);
                }
                if (sizeof($arrJenjang) > 0) {
                    $where .= ' AND Jenjang.id IN ({jenjang:array})';
                    $arrBind['jenjang'] = $arrJenjang;
                }
                else {
                    $where .= ' AND Jenjang.id = 0';
                }
            }
        }

        if($params['id_kota_kabupaten'] > 0) {
            $dataKota = MasterKotaKabupaten::find([
                'conditions' => 'kota_kabupaten_code = ?0',
                'bind' => [
                    $params['id_kota_kabupaten']
                ]
            ]);
            $dataWilayah = MasterNamaWilayah::find([
                'conditions' => 'kode = ?0',
                'bind' => [
                    $params['id_kota_kabupaten']
                ]
            ]);
            if($dataKota->count() > 0) {
                $OR = "((lower(regexp_replace(kota_kab, '[^a-zA-Z]','', 'g')) like :kotaname: OR lower(regexp_replace(MasterKotaKabupaten.kota_kabupaten_name, '[^a-zA-Z]','', 'g')) like :kotaname:)";

                // update 20210901, API detail sekolah dari Kemdikbud include kode dagri wilayah sekolah
                $OR .= " OR kode_kabupaten_dagri = :kota_kabupaten_code:";
                $arrBind['kota_kabupaten_code'] = $dataKota[0]->kota_kabupaten_code;

                if ($dataWilayah->count() > 0) {
                    foreach ($dataWilayah as $key => $wilayah) {
                        $OR .= " OR lower(kota_kab) like :wilayah" . $key . ":";
                        $arrBind['wilayah' . $key] = strtolower($wilayah->nama);
                    }
                }
                $where .= " AND " . $OR . ")";
                $arrBind['kotaname'] = '%' . strtolower(preg_replace('/[^a-zA-Z]/', '', $dataKota[0]->kota_kabupaten_name));
            }
        }
        else if($params['id_provinsi'] > 0) {
            $dataProv = MasterProvinsi::find([
                'conditions' => 'provinsi_code = ?0',
                'bind' => [
                    $params['id_provinsi']
                ]
            ]);
            $dataWilayah = MasterNamaWilayah::find([
                'conditions' => 'kode = ?0',
                'bind' => [
                    $params['id_provinsi']
                ]
            ]);

            if($dataProv->count() > 0) {
                $OR = "((lower(regexp_replace(provinsi, '[^a-zA-Z]','', 'g')) like :provname: OR lower(regexp_replace(MasterProvinsi.provinsi_name, '[^a-zA-Z]','', 'g')) like :provname:)";

                // update 20210901, API detail sekolah dari Kemdikbud include kode dagri wilayah sekolah
                $OR .= " OR kode_provinsi_dagri = :provinsi_code:";
                $arrBind['provinsi_code'] = $dataProv[0]->provinsi_code;

                if ($dataWilayah->count() > 0) {
                    foreach ($dataWilayah as $key => $wilayah) {
                        $OR .= " OR lower(provinsi) like :wilayah" . $key . ":";
                        $arrBind['wilayah' . $key] = strtolower($wilayah->nama);
                    }
                }
                $where .= " AND " . $OR . ")";
                $arrBind['provname'] = '%' . strtolower(preg_replace('/[^a-zA-Z]/', '', $dataProv[0]->provinsi_name));
            }
        }

        if (sizeof($arrBind) > 0 && strlen($where) > 0) {
            $found = Sekolah::query()
                ->columns('count(distinct npsn) as jumlah')
                ->leftjoin('Jenjang', 'Sekolah.jenjang_id = Jenjang.id')
                ->leftjoin('MasterKecamatan', 'Sekolah.kecamatan_code = MasterKecamatan.kecamatan_code')
                ->leftjoin('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code=MasterKecamatan.kota_kabupaten_code')
                ->leftjoin('MasterProvinsi', 'MasterProvinsi.provinsi_code=MasterKotaKabupaten.provinsi_code')
                ->where($where)
                ->bind($arrBind)
                ->execute()[0]['jumlah'];

            if (!$found) {
                $jenjang = $sekolah->jenjang->nama_jenjang ?? "-";
                $lokasiSekolah = $sekolah->kode_kabupaten_dagri ?? "-";
                $lokasiInstansi = isset($instansi) ? $instansi->id_kota_kabupaten : "-";
                $tipeInstansi = $sekolah->jenjang->InstansiTipe->nama ?? "-";
                $additionalInfo = "Jenjang [" . $jenjang . "] Tipe Instansi [" . $tipeInstansi . "] Kode Wilayah Sekolah [" . $lokasiSekolah . "] Kode Wilayah Instansi [" . $lokasiInstansi . "]";
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Lokasi sekolah " . $sekolah->nama . " tidak sesuai dengan wilayah/tipe instansi yang dipilih. " . $additionalInfo);
            }
        }
        #endvalidasi

        $output = $sekolah->toArray();
        $output['source'] = $source;
        $output['is_live_data'] = $liveData;
        $output['error_message'] = $errMessage;

        return new CollectionPaginatedResponse([$output]);
    }

    public function jenjang()
    {
        $result = Jenjang::query()
            ->where("src like 'sekolah'")
            ->orderBy("sort_order")
            ->execute()
            ->toArray();
        return new PaginatedResponse($result, count($result), count($result), 0);
    }

    public function jenjangAll()
    {
        $params = $this->request->get();
        $params['src'] = array_key_exists('src', $params) ? $params['src'] : null;
        $params['is_shown'] = array_key_exists('is_shown', $params) ? $params['is_shown'] : null;

        $result = Jenjang::query();
        $where = [];
        $bind = [];

        if($params['src'] != null) {
            $where[] = "src = :src:";
            $bind['src'] = strtolower($params['src']);

        }

        if($params['is_shown'] != null) {
            $where[] = "is_shown = :is_shown:";
            $bind['is_shown'] = $params['is_shown'];
        }

        if(!empty($where)) {
            $result
                ->where(implode(" AND ", $where))
                ->bind($bind);
        }

        $result = $result->orderBy("sort_order, nama_jenjang")
            ->execute()
            ->toArray();
        return new PaginatedResponse($result, count($result), count($result), 0);
    }

    public function jenjangList() {
        $params = $this->request->get();
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['is_shown'] = array_key_exists('is_shown', $params) ? $params['is_shown'] : null;
        $params['id_instansi_tipe'] = array_key_exists('id_instansi_tipe', $params) ? $params['id_instansi_tipe'] : null;

        $params = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'is_shown' => ['validators' => ['digit']],
                'id_instansi_tipe' => ['validators' => ['digit']],
                'keyword' => ['validators' => []]
            ]
        );

        $conditions = ['nama_jenjang ILIKE :keyword:'];
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%'
        ];

        $isShown = $params['is_shown'];
        if (!is_null($isShown)) {
            if ($isShown == 0 || $isShown == 1) {
                array_push($conditions, 'is_shown = :is_shown:');
                $bind['is_shown'] = $isShown;
            }
        }
        if (!is_null($params['id_instansi_tipe'])) {
            array_push($conditions, 'id_instansi_tipe = :id_instansi_tipe:');
            $bind['id_instansi_tipe'] = $params['id_instansi_tipe'];
        }

        $total = Jenjang::find([
            'conditions' => implode(" AND ", $conditions),
            'bind' => $bind
        ])->count();
        $jenjangs = Jenjang::find([
            'conditions' => implode(" AND ", $conditions),
            'bind' => $bind,
            "order" => "sort_order, nama_jenjang ASC",
            "limit" => $params["limit"],
            "offset" => $params["offset"]
        ]);

        $result = [];
        foreach ($jenjangs as $jenjang) {
            array_push($result, $jenjang->toDetail());
        }

        return new PaginatedResponse($result, $total, $params['limit'], $params['offset']);
    }

    public function jenjangUpdate($id) {
        if (!$this->hasRole('admin_pak') && !$this->hasScopes(['admin.view'])) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_instansi_tipe' => ['validators' => ['required', 'digit']],
                'is_shown' => ['validators' => ['required', 'digit', [
                    'name' => 'inclusion_in',
                    'params' => ['0', '1']
                ]]],
                'sort_order' => ['validators' => ['required', 'digit']],
                'group_jenjang' => ['validators' => []]
            ]
        );

        $jenjang = Jenjang::findFirst($id);
        if (empty($jenjang)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Jenjang dengan ID " . $id . " tidak ditemukan");
        }
        $instansiTipe = InstansiTipe::findFirst($params['id_instansi_tipe']);
        if (empty($instansiTipe)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tipe Instansi dengan ID " . $params['id_instansi_tipe'] . " tidak ditemukan");
        }

        $jenjang->id_instansi_tipe = $params['id_instansi_tipe'];
        $jenjang->is_shown = $params['is_shown'];
        $jenjang->sort_order = $params['sort_order'];
        $jenjang->group_jenjang = $params['group_jenjang'];
        if (!$jenjang->save()) {
            $this->throwValidationException($jenjang->getMessages());
        }

        return new SimpleResponse($jenjang);
    }

    public function jenjangMassUpdate() {
        if (!$this->hasRole('admin_pak') && !$this->hasScopes(['admin.view'])) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User tidak berhak mengakses');
        }

        $this->db->begin();
        try {
            $rawBody = $this->request->getJsonRawBody(true);
            $data = [];
            foreach ($rawBody as $row) {
                $this->validate(
                    $row,
                    [
                        'id' => ['validators' => ['required', 'digit']],
                        'id_instansi_tipe' => ['validators' => ['digit']],
                        'is_shown' => ['validators' => ['required', 'digit', [
                            'name' => 'inclusion_in',
                            'params' => ['0', '1']
                        ]]],
                        'sort_order' => ['validators' => ['digit']],
                    ]
                );

                /** @var Jenjang $jenjang */
                $jenjang = Jenjang::findFirst($row['id']);
                if (!$jenjang) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Jenjang dengan ID [' . $row['id'] . '] tidak ditemukan');
                $jenjang->is_shown = $row['is_shown'];
                $jenjang->sort_order = $row['sort_order'];

                if (array_key_exists('id_instansi_tipe', $row)) {
                    $instansiTipe = InstansiTipe::findFirst($row['id_instansi_tipe']);
                    if (!$instansiTipe) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Tipe Instansi dengan ID [' . $row['id_instansi_tipe'] . '] tidak ditemukan');
                    $jenjang->id_instansi_tipe = $row['id_instansi_tipe'];
                }

                if (!$jenjang->save()) {
                    $errors = $jenjang->getMessages();
                    $error_messages = [];

                    foreach ($errors as $key => $error) {
                        $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessage()];
                    }

                    throw new ValidationException($error_messages);
                }
                array_push($data, $jenjang);
            }
            $this->db->commit();
            return new CollectionPaginatedResponse($data);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function anggaran_list_provinsi()
    {
        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $arrParams = array(
            'columns' => 'count(distinct provinsi) as jumlah',
            'lower(provinsi) like :provinsi:',
            'bind' => array(
                'provinsi' => '%' . strtolower($params['keyword']) . '%'
            )
        );

        $totalResult = SekolahAnggaranComp::find($arrParams)[0]['jumlah'];

        $arrParams['columns'] = 'distinct provinsi';
        $arrParams['limit'] = $params['limit'];
        $arrParams['offset'] = $params['offset'];
        $arrParams['order'] = 'provinsi ASC';
        $result = SekolahAnggaranComp::find($arrParams)->toArray();

        return new PaginatedResponse($result, $totalResult, $params['limit'], $params['offset']);
    }

    public function anggaran_list_kota_kabupaten()
    {
        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'provinsi' => ['validators' => ['required']]
            ]
        );

        $arrParams = array(
            'columns' => 'count(distinct kota_kab) as jumlah',
            'lower(provinsi) = :provinsi: and lower(kota_kab) like :kota_kab:',
            'bind' => array(
                'kota_kab' => '%' . strtolower($params['keyword']) . '%',
                'provinsi' => strtolower($params['provinsi'])

            )
        );

        $totalResult = SekolahAnggaranComp::find($arrParams)[0]['jumlah'];

        $arrParams['columns'] = 'distinct kota_kab';
        $arrParams['limit'] = $params['limit'];
        $arrParams['offset'] = $params['offset'];
        $arrParams['order'] = 'kota_kab ASC';
        $result = SekolahAnggaranComp::find($arrParams)->toArray();

        return new PaginatedResponse($result, $totalResult, $params['limit'], $params['offset']);
    }

    public function anggaran_list_sekolah()
    {
        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'provinsi' => ['validators' => ['required']],
                'kota_kabupaten' => ['validators' => ['required']]
            ]
        );

        $arrParams = array(
            'columns' => 'count(*) as jumlah',
            'lower(provinsi) = :provinsi: and lower(kota_kab) = :kota_kab: and lower(nama) like :nama:',
            'bind' => array(
                'nama' => '%' . strtolower($params['keyword']) . '%',
                'kota_kab' => strtolower($params['kota_kabupaten']),
                'provinsi' => strtolower($params['provinsi'])

            )
        );

        $totalResult = SekolahAnggaranComp::find($arrParams)[0]['jumlah'];

        $arrParams['columns'] = 'id, npsn, nama, kota_kab as kota, provinsi';
        $arrParams['limit'] = $params['limit'];
        $arrParams['offset'] = $params['offset'];
        $result = SekolahAnggaranComp::find($arrParams)->toArray();

        return new PaginatedResponse($result, $totalResult, $params['limit'], $params['offset']);
    }

    public function anggaran()
    {
        $validatedRequest = $this->validate(
            $this->request->get(),
            [
                'npsn' => ['validators' => ['required']]
            ]
        );

        $result = SekolahAnggaranComp::anggaran($validatedRequest['npsn']);
        $totalResult = count($result);
        return new PaginatedResponse($result, $totalResult, $totalResult, 0);
    }

    public function fasilitas()
    {
        $validatedRequest = $this->validate(
            $this->request->get(),
            [
                'npsn' => ['validators' => ['required']]
            ]
        );

        $npsn = $validatedRequest["npsn"];

        $result = Sekolah::findFirst(
            array(
                "columns" =>
                "COALESCE(lab_bahasa,0) as lab_bahasa,
                    COALESCE(lab_biologi,0) as lab_biologi,
                    COALESCE(lab_fisika,0) as lab_fisika,
                    COALESCE(lab_ipa,0) as lab_ipa,
                    COALESCE(lab_ips,0) as lab_ips,
                    COALESCE(lab_kimia,0) as lab_kimia,
                    COALESCE(perpus_baik,0) as perpus_baik,
                    COALESCE(perpus_rusak_ringan,0) as perpus_rusak_ringan,
                    COALESCE(perpus_rusak_sedang,0) as perpus_rusak_sedang,
                    COALESCE(perpus_rusak_berat,0) as perpus_rusak_berat,
                    COALESCE(rk_baik,0) as rk_baik,
                    COALESCE(rk_rusak_ringan,0) as rk_rusak_ringan,
                    COALESCE(rk_rusak_sedang,0) as rk_rusak_sedang,
                    COALESCE(rk_rusak_berat,0) as rk_rusak_berat,
                    COALESCE(sanitasi_guru,0) as sanitasi_guru,
                    COALESCE(sanitasi_siswa,0) as sanitasi_siswa,
                    COALESCE(sumber_listrik,'') as sumber_listrik,
                    COALESCE(sertifikasi_iso,'') as sertifikasi_iso,
                    COALESCE(guru_agama_budi_pekerti,0) as guru_agama_budi_pekerti,
                    COALESCE(guru_bahasa_indonesia,0) as guru_bahasa_indonesia,
                    COALESCE(guru_bahasa_inggris,0) as guru_bahasa_inggris,
                    COALESCE(guru_matematika,0) as guru_matematika,
                    COALESCE(guru_penjaskes,0) as guru_penjaskes,
                    COALESCE(guru_pkn,0) as guru_pkn,
                    COALESCE(guru_pns,0) as guru_pns,
                    COALESCE(guru_s1lebih,0) as guru_s1lebih,
                    COALESCE(guru_laki,0) as guru_laki,
                    COALESCE(guru_perempuan,0) as guru_perempuan,
                    COALESCE(guru_kelas,0) as guru_kelas,
                    COALESCE(guru_belums1,0) as guru_belums1,
                    COALESCE(guru_belumsertifikasi,0) as guru_belumsertifikasi,
                    COALESCE(guru_gol1,0) as guru_gol1,
                    COALESCE(guru_gol2,0) as guru_gol2,
                    COALESCE(guru_gol3,0) as guru_gol3,
                    COALESCE(guru_gol3_1,0) as guru_gol3_1,
                    COALESCE(guru_gol4,0) as guru_gol4,
                    COALESCE(rombel_1,0) as rombel_1,
                    COALESCE(rombel_2,0) as rombel_2,
                    COALESCE(rombel_3,0) as rombel_3,
                    COALESCE(rombel_4,0) as rombel_4,
                    COALESCE(rombel_5,0) as rombel_5,
                    COALESCE(rombel_6,0) as rombel_6,
                    COALESCE(rombel_7,0) as rombel_7,
                    COALESCE(rombel_8,0) as rombel_8,
                    COALESCE(rombel_9,0) as rombel_9,
                    COALESCE(rombel_10,0) as rombel_10,
                    COALESCE(rombel_11,0) as rombel_11,
                    COALESCE(rombel_12,0) as rombel_12,
                    COALESCE(rombel_13,0) as rombel_13,
                    COALESCE(pd_laki,0)+COALESCE(pd_perempuan,0) as jml_pd,
                    COALESCE(guru_laki,0)+COALESCE(guru_perempuan,0) as jml_guru,
                    alamat, latitude, longitude, COALESCE(available_photo, 0) as available_photo",
                "npsn = :npsn:",
                "bind" => array(
                    "npsn" => $npsn
                )
            )
        );
        if ($result) {
            try {
                /** @var $sekolah Sekolah */
                $sekolah = Sekolah::findFirst(array("npsn = :npsn:",
                    "bind" => array(
                        "npsn" => $npsn
                    )
                ));
                if (!$sekolah) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Sekolah tidak ditemukan");
                }
                $now = new DateTime(date('Y-m-d H:i:s'));
                $last_update =  empty($sekolah->last_update_pegawai)
                    ? new DateTime('2000-01-01')
                    : new DateTime($sekolah->last_update_pegawai);

                $interval = $now->diff($last_update);
            } catch (\Exception $e) {
                $interval = null;
            }
            $updateLimit = AppConfig::getIntValueByKode('SEKOLAH_DAYS_UPDATE');
            if ($interval == null || $interval->days > $updateLimit) {
                $listGuru = $this->doGetListGuru($validatedRequest["npsn"]);
                if (is_array($listGuru) && !empty($listGuru)) {
                    $this->db->begin();
                    try {
                        $this->rawQuery("DELETE FROM jaga.sekolah_pegawai WHERE npsn = :npsn", ['npsn' => $npsn]);
                        SekolahPegawai::saveForNpsn($npsn, $listGuru);
                        $sekolah->last_update_pegawai = date('Y-m-d H:i:s');
                        $sekolah->save();
                        $this->db->commit();
                    } catch (\Exception $e) {
                        $this->db->rollback();
                        throw $e;
                    }
                }
                $result->pegawai = $listGuru;
            } else {
                $result->pegawai = $sekolah->SekolahPegawai;
            }

            $result->jml_peg = is_array($result->pegawai) ? count($result->pegawai) : 0;
            $result->photos = [];
            for ($i = 1; $i <= $result->available_photo; $i++) {
                $result->photos[] = $this->config["photo_sekolah_base_url"] . $validatedRequest["npsn"] . "/" . $i . ".jpg";
            }
            $totalCount = 1;
        } else {
            $totalCount = 0;
        }

        return new PaginatedResponse([$result], $totalCount, 1, 0);
    }

    private function doGetListGuru($npsn, $filterNama = '')
    {
        $client = new SekolahNewClient($this->config["sekolah_new_prop"]);
        $client->setLogger($this->logger);
        try {
            $r = $client->gtk($npsn);
            if(is_array($r)) {
                for ($i = 0; $i < count($r); $i++) {
                    if (array_key_exists('nama_mata_pelajaran', $r[$i])) {
                        $r[$i]['mata_pelajaran'] = $r[$i]['nama_mata_pelajaran'];
                        unset($r[$i]['nama_mata_pelajaran']);
                    }
                }
            }

            $result = [];

            if(!empty($filterNama)) {
                if(is_array($r)) {
                    foreach ($r as $guru) {
                        if (array_key_exists('nama', $guru)) {
                            if (str_contains(strtolower($guru['nama']), strtolower($filterNama))) {
                                array_push($result, $guru);
                            }
                        }
                    }
                }
                else {
                    $result = $r;
                }
            }
            else {
                $result = $r;
            }

            return $result;
        } catch (\Exception $ex) {
            throw $ex;
        }
    }

    public function getListGuru()
    {
        $params = $this->request->get();
        $validatedRequest = $this->validate(
            $params,
            [
                'npsn' => ['validators' => ['required']],
                'nama' => []
            ]
        );

        $res = $this->doGetListGuru($params["npsn"], $validatedRequest['nama']);

        return new PaginatedResponse($res, is_array($res) ? count($res) : 0, is_array($res) ? count($res) : 0, 0);
    }

    public function listProvinsi() {
        $params = $this->request->get();
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : null;
        $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 50;
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;

        $rules = [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']]
        ];

        $conditions = $bind = [];

        if (!is_null($params['keyword'])) {
            array_push($conditions, "provinsi ILIKE :keyword:");
            $bind['keyword'] = '%' . $params['keyword'] . '%';
            $rules['keyword'] = ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 50
                ],
                [
                    'name' => 'min_string',
                    'params' => 3
                ]
            ]];
        }

        array_push($conditions, "provinsi NOT ILIKE '%luar negeri%'");

        $validatedRequest = $this->validate($params, $rules);

        $total = Sekolah::find([
            'columns' => 'COUNT(DISTINCT(upper(provinsi))) as total',
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ])[0]['total'];

        $result = Sekolah::find([
            'columns' => 'DISTINCT(upper(provinsi)) as provinsi',
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
            'order' => 'provinsi',
            'limit' => $validatedRequest['limit'],
            'offset' => $validatedRequest['offset']
        ]);

        return new PaginatedResponse($result, $total, $validatedRequest['limit'], $validatedRequest['offset']);
    }

    public function listKota() {
        $params = $this->request->get();
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : null;
        $params['provinsi'] = array_key_exists('provinsi', $params) ? $params['provinsi'] : null;
        $params['jenjang'] = array_key_exists('jenjang', $params) ?  $params['jenjang'] : -1;
        $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 50;
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;

        $rules = [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']]
        ];

        $conditions = $bind = [];

        if (!is_null($params['keyword'])) {
            array_push($conditions, "(kota_kab ILIKE :keyword:)");
            $bind['keyword'] = '%' . $params['keyword'] . '%';
            $rules['keyword'] = ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 50
                ],
                [
                    'name' => 'min_string',
                    'params' => 3
                ]
            ]];
        }

        if($this->is_json($params['jenjang'])) {
            array_push($conditions, "jenjang_id IN ({jenjang:array})");
            $bind["jenjang"] = array_values(json_decode($params['jenjang']));
        }
        else if ($params['jenjang'] > 0) {
            array_push($conditions, "jenjang_id = :jenjang:");
            $bind['jenjang'] = $params['jenjang'];
        }

        if (!is_null($params['provinsi'])) {
            array_push($conditions, "provinsi ILIKE :provinsi:");
            $bind['provinsi'] = '%' . $params['provinsi'] . '%';
            $rules['provinsi'] = ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 50
                ],
                [
                    'name' => 'min_string',
                    'params' => 3
                ]
            ]];
        }

        array_push($conditions, "provinsi NOT ILIKE '%luar negeri%'");

        $validatedRequest = $this->validate($params, $rules);

        $total = Sekolah::find([
            'columns' => 'COUNT(DISTINCT (upper(kota_kab))) as total',
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ])[0]['total'];

        $result = Sekolah::find([
            'columns' => 'DISTINCT (upper(kota_kab)) as kota_kab',
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
            'order' => 'upper(kota_kab)',
            'limit' => $validatedRequest['limit'],
            'offset' => $validatedRequest['offset']
        ]);

        return new PaginatedResponse($result, $total, $validatedRequest['limit'], $validatedRequest['offset']);
    }

    public function importExcelMadrasah()
    {
        AuthHelper::validateToolsKey($this->request, $this->config);

        $document = $this->getAndValidateImportFile('document', ['text/csv', 'application/vnd.ms-excel']);
        $jenjangList = Jenjang::find()->toArray();
        $jenjangColumns = array_column($jenjangList, 'nama_jenjang');

        $imported = 0;
        $failed = [];
        $skipped = 0;
        $skippedJenjangNotFound = [];

        if (!empty($document)) {
            $filename = $document['tmp_name'];
            $csv = new ParseCsv\Csv();
            $csv->parse($filename);

            $random = new \Phalcon\Security\Random();

            foreach ($csv->data as $key => $row) {
                if (empty($row['instansi_id']) || empty($row['instansi']) || empty($row['kabkota'] || empty($row['provinsi']))) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Id Instansi/Nama Instansi/Provinsi/KabKota tidak ditemukan pada baris ' . $key);
                }

                if (empty($row['jenjang'])) {
                    $skipped++;
                    continue;
                }

                $index = array_search($row['jenjang'], $jenjangColumns);

                if ($index === false) {
                    if (!array_key_exists($row['jenjang'], $skippedJenjangNotFound)) {
                        $skippedJenjangNotFound[$row['jenjang']] = 0;
                    }

                    $skippedJenjangNotFound[$row['jenjang']]++;
                    continue;
                }

                $jenjang = $jenjangList[$index];

                $sekolah = Sekolah::findFirst([
                    'conditions' => 'id_instansi_kemenag=:id_instansi_kemenag: or npsn=:npsn:',
                    'bind' => [
                        'id_instansi_kemenag' => $row['instansi_id'],
                        'npsn'                => $row['npsn']
                    ]
                ]);

                if (empty($sekolah)) {
                    $theUuid = $random->uuid();

                    $sekolah = new Sekolah();
                    $sekolah->id = $theUuid;
                }

                $sekolah->id_instansi_kemenag = $row['instansi_id'];
                $sekolah->nama = $row['instansi'];
                $sekolah->npsn = $row['npsn'];
                $sekolah->provinsi = $row['provinsi'];
                $sekolah->kota_kab = $row['kabkota'];
                $sekolah->jenjang = $jenjang['id'];
                $sekolah->email = '';
                $status = $sekolah->save();

                if (!$status) {
                    $failed[] = $row['instansi_id'];
                } else {
                    $imported++;
                }
            }
        }

        return new SimpleResponse(compact('imported', 'failed', 'skipped', 'skippedJenjangNotFound'));
    }

    /**
     * Mengembalikan daftar PAKIsianSekolahDetail secara acak
     * @return CollectionPaginatedResponse
     * @throws Exception
     */
    public function kegiatanPAK() {
        $limit = $this->request->get('limit', null, 10);
        $this->validate(
            $this->request->get(),
            [
                'limit' => ['validators' => ['digit']]
            ]
        );

        $rawIds = $this->rawQuery("
            SELECT d.id
            FROM pak_isian_sekolah_detail d LEFT JOIN pak_isian_sekolah i ON i.id = d.isian_id::INTEGER
            WHERE i.status = ".PAKIsianSekolah::STATUS_VERIFIED." AND i.is_published = true
        ");

        if (sizeof($rawIds) > 0) {
            $ids = $this->pakGenerateRandomIds($rawIds, $limit);
            $rows = $this->rawQuery(
                $this->pakBaseQuery() .
                "WHERE d.id IN (".implode(",", $ids).")"
            );

            $result = $this->pakGetAttachments($rows);

            return new CollectionPaginatedResponse($result);
        } else {
            return new CollectionPaginatedResponse([]);
        }
    }

    /**
     * Mengembalikan daftar PAKIsianSekolahDetail secara acak dengan isian link_youtube tidak kosong
     * @return CollectionPaginatedResponse
     * @throws Exception
     */
    public function mediaKegiatanPAK() {
        $limit = $this->request->get('limit', null, 10);
        $this->validate(
            $this->request->get(),
            [
                'limit' => ['validators' => ['digit']]
            ]
        );

        $rawIds = $this->rawQuery("
            SELECT d.id
            FROM pak_isian_sekolah_detail d LEFT JOIN pak_isian_sekolah i ON i.id = d.isian_id::INTEGER
            WHERE i.status = ".PAKIsianSekolah::STATUS_VERIFIED." AND i.is_published = true AND d.link_youtube IS NOT NULL AND d.link_youtube <> ''
        ");

        if (sizeof($rawIds) > 0) {
            $ids = $this->pakGenerateRandomIds($rawIds, $limit);
            $rows = $this->rawQuery(
                $this->pakBaseQuery() .
                "WHERE d.id IN (".implode(",", $ids).")"
            );

            $result = $this->pakGetAttachments($rows);

            return new CollectionPaginatedResponse($result);
        } else {
            return new CollectionPaginatedResponse([]);
        }
    }

    /**
     * Mengembalikan daftar PAKIsianSekolahDetail berdasarkan metode_id
     * @param $methodId
     * @return PaginatedResponse
     * @throws ValidationException
     */
    public function kegiatanPAKByMethod($methodId) {
        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['nama_sekolah'] = array_key_exists('nama_sekolah', $getParams) ? $getParams['nama_sekolah'] : null;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : null;
        $getParams['tahun_ajaran'] = array_key_exists('tahun_ajaran', $getParams) ? $getParams['tahun_ajaran'] : null;

        $currentYear = date("Y");
        $allowed = [
            'tahun_ajaran' => [
                ($currentYear - 3) . '-' . ($currentYear - 2),
                ($currentYear - 2) . '-' . ($currentYear - 1),
                ($currentYear - 1) . '-' . ($currentYear),
                null
            ]
        ];
        $validatedRequest = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'nama_sekolah' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 50
                ]]],
                'keyword' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 50
                ]]],
                'tahun_ajaran' => ['validators' => [[
                    'name' => 'inclusion_in',
                    'params' => $allowed['tahun_ajaran']
                ]]]
            ]
        );

        $where = "i.status = ".PAKIsianSekolah::STATUS_VERIFIED." AND i.is_published = true ".($methodId > 0 ? "AND d.metode_id = ".$methodId : "");

        if (!is_null($validatedRequest['nama_sekolah'])) {
            $where .= " AND s.nama ILIKE '" . $validatedRequest['nama_sekolah'] . "'";
        }

        if (!is_null($validatedRequest['keyword'])) {
            $where .= " AND d.deskripsi ILIKE '%" . $validatedRequest['keyword'] . "%'";
        }

        if (!is_null($validatedRequest['tahun_ajaran'])) {
            $where .= " AND i.tahun_ajaran = '" . $validatedRequest['tahun_ajaran'] . "'";
        }

        $total = $this->rawQuery("
            SELECT COUNT(*)
            FROM pak_isian_sekolah_detail d
            LEFT JOIN pak_isian_sekolah i ON i.id = d.isian_id::INTEGER
            LEFT JOIN sekolah s ON i.sekolah_npsn = s.npsn
            WHERE ".$where."
        ")[0]['count'];

        $rows = $this->rawQuery(
            $this->pakBaseQuery() .
            "WHERE ".$where."
            LIMIT ".$validatedRequest['limit']." OFFSET ".$validatedRequest['offset']."
        ");

        $result = $this->pakGetAttachments($rows);

        return new PaginatedResponse($result, $total, $validatedRequest['limit'], $validatedRequest['offset']);
    }

    public function pakDashboard() {
        $result = [];

        $result['regulasi'] = $this->pakStatRegulasi();
        $result['isian_sekolah'] = $this->pakStatIsianSekolah();

        return new SimpleResponse($result);
    }

    public function pakListRegulasi() {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'implementasi_id';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'asc';
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : 0;

        $allowed_order = ['implementasi_id', 'jenis_dokumen', 'nama_instansi', 'nomor', 'tahun', 'tentang', 'nama_provinsi', 'nama_kab_kota'];
        $allowed_order_direction = ['asc', 'desc'];
        $order_by_mapping = [
            'implementasi_id' => 'pi.id',
            'jenis_dokumen' => 'pjdq.jenis_dokumen',
            'nama_instansi' => 'ins.nama',
            'nomor' => 'pvia.nomor',
            'tahun' => 'pvia.tahun',
            'tentang' => 'pvia.tentang',
            'nama_provinsi' => 'mp.provinsi_name',
            'nama_kab_kota' => 'mkk.kota_kabupaten_name'
        ];

        $params = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'tipe_instansi' => ['validators' => ['digit']],
                'sub_tipe_instansi' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'tahun' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $order_by_column = $order_by_mapping[$params['order_by']];
        $order_direction = $params['order_direction'];

        $conditions = "pi.deleted_at IS NULL AND pvi.deleted_at IS NULL AND pvi.nilai_verifikasi = 100 AND pq.questionnaire_category_id = 1 AND ins.flag_pak = true";

        if (!empty($params['tipe_instansi'])) {
            $conditions .= " AND ins.id_tipe = " . (int)$params['tipe_instansi'];
        } else {
            $conditions .= " AND ins.id_tipe IN (1,2,3)";
        }

        if (!empty($params['sub_tipe_instansi'])) {
            $conditions .= " AND ins.id_sub_tipe = " . (int)$params['sub_tipe_instansi'];
        }

        if (!empty($params['id_provinsi'])) {
            $conditions .= " AND ins.id_provinsi = " . $params['id_provinsi'];
        }

        if (!empty($params['id_kota_kabupaten'])) {
            $conditions .= " AND ins.id_kota_kabupaten = " . $params['id_kota_kabupaten'];
        }

        if (!empty($params['keyword'])) {
            $conditions .= " AND (";
            $conditions .= " pvia.tentang ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= " OR pvia.nomor ILIKE '" . $params['keyword'] . "'";
            $conditions .= " OR pjdq.jenis_dokumen ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= " OR ins.nama ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= ")";
        }

        if ($params['tahun'] > 0) {
            $conditions .= " AND pvia.tahun = " . $params['tahun'];
        }

        $query = "
            SELECT
                pvia.*, a.uuid as attachment_uuid, pi.id as implementasi_id, pjdq.jenis_dokumen, ins.id as id_instansi, ins.nama as nama_instansi, ins.id_tipe as tipe_instansi, ins.id_sub_tipe as sub_tipe_instansi, ins.id_provinsi, ins.id_kota_kabupaten, mp.provinsi_name as nama_provinsi, mkk.kota_kabupaten_name as nama_kab_kota
            FROM
                pak_verifikasi_implementasi pvi
                LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                LEFT JOIN master_provinsi mp ON mp.provinsi_code::INTEGER = ins.id_provinsi
                LEFT JOIN master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = ins.id_kota_kabupaten
                LEFT JOIN pak_verifikasi_implementasi_attachment pvia ON pvia.verifikasi_id = pvi.id
                LEFT JOIN attachments a ON a.id = pvia.attachment_id
                LEFT JOIN pak_jenis_dokumen_questionnaire pjdq ON pjdq.id = pvia.jenis_dokumen_id
            WHERE
               ".$conditions."
            ORDER BY ".$order_by_column." ".$order_direction." NULLS LAST
        ";

        $total = $this->rawQuery($query);
        $rows = $this->rawQuery($query . " LIMIT " . $params['limit'] . " OFFSET " . $params['offset']);

        return new PaginatedResponse($rows, sizeof($total), $params['limit'], $params['offset']);
    }

    public function pakListPemda() {
        $params = $this->request->get();

        $conditions = "instansi.flag_pak = true AND instansi.deleted_at IS NULL";
        if (!empty($params['tipe_instansi'])) {
            $conditions .= " AND instansi.id_tipe = " . (int)$params['tipe_instansi'];
        }

        $query = "
            SELECT
                instansi.id,
                instansi.nama,
                instansi.id_provinsi,
                instansi.id_kota_kabupaten,
                master_provinsi.provinsi_name as nama_provinsi,
                master_kota_kabupaten.kota_kabupaten_name as nama_kota_kabupaten,   
                COALESCE(jumlah_regulasi, 0) as jumlah_regulasi
            FROM
                instansi
                LEFT JOIN (
                SELECT
                    instansi.id,
                    instansi.nama,
                    instansi.id_provinsi,
                    instansi.id_kota_kabupaten,
                    count(instansi.id) as jumlah_regulasi
                FROM
                    instansi
                    LEFT JOIN pak_implementasi ON pak_implementasi.instansi_id = instansi.id
                    LEFT JOIN pak_verifikasi_implementasi ON pak_verifikasi_implementasi.implementasi_id = pak_implementasi.id
                    LEFT JOIN pak_questionnaire ON pak_questionnaire.id = pak_verifikasi_implementasi.questionnaire_id
                WHERE
                    pak_questionnaire.questionnaire_category_id = 1 AND
                    pak_verifikasi_implementasi.nilai_verifikasi = 100 
                GROUP BY
                    instansi.id,
                    pak_implementasi.instansi_id
                ) tmp ON tmp.id = instansi.id
                LEFT JOIN master_provinsi ON master_provinsi.provinsi_code::INTEGER = instansi.id_provinsi
                LEFT JOIN master_kota_kabupaten ON master_kota_kabupaten.kota_kabupaten_code::INTEGER = instansi.id_kota_kabupaten
            WHERE " . $conditions . "
            ORDER BY instansi.id_tipe, instansi.nama
        ";

        $result = $this->rawQuery($query);

        return new CollectionPaginatedResponse($result);
    }

    public function pakLastIsiansekolah() {
        $rows = $this->rawQuery(
            $this->pakBaseQuery() .
            "WHERE i.status = ".PAKIsianSekolah::STATUS_VERIFIED." AND i.is_published = true " .
            "ORDER BY i.id DESC LIMIT 10 OFFSET 0
        ");

        $result = $this->pakGetAttachments($rows);

        return new CollectionPaginatedResponse($result);
    }

    public function listWithDetailIsian() {
        $request = $this->request;
        $params = $request->get();
        $params['limit'] = $request->get('limit', null, 10);
        $params['offset'] = $request->get('offset', null, 0);
        $params['nama'] = $request->get('nama', null, '');
        $params['order_by'] = $request->get('order_by', null, 'nama');
        $params['sort'] = $request->get('sort', null, 'desc');
        $params['isian_isian_null'] = $request->get('isian_isian_null', null, false);
        $params['isian_isian_not_null'] = $request->get('isian_isian_not_null', null, false);
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['status'] = $request->get('status', null, -1);
        $params['provinsi'] = $request->get('provinsi', null, '');
        $params['kota_kab'] = $request->get('kota_kab', null, '');
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');
        $params['metode_id'] = $request->get('metode_id', null, '');

        $allowed_order_by = ['nama', 'tahun_ajaran', 'status'];
        $allowed_sort = ['asc', 'desc'];

        $rules = [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
            'nama' => ['validators' => []],
            'status' => ['validators' => ['digit']],
            'order_by' => ['validators' =>  [[
                'name' => 'inclusion_in',
                'params' => $allowed_order_by
            ]]],
            'sort' => ['validators' =>  [[
                'name' => 'inclusion_in',
                'params' => $allowed_sort
            ]]],
            'tahun_ajaran' => ['validators' => []],
            'isian_isian_null' => ['validators' => []],
            'isian_isian_not_null' => ['validators' => []],
            'provinsi' => ['validators' => []],
            'kota_kab' => ['validators' => []],
            'provinsi_kode' => ['validators' => []],
            'kota_kab_kode' => ['validators' => []],
            'jenjang' => ['validators' => []],
            'jenjang_id' => ['validators' => []],
            'metode_id' => ['validators' => ['digit']]
        ];

        $validatedParams = $this->validate($params, $rules);

        $conditions = ["1=1"];
        $conCount = [];

        if (isset($params['status']) && $params['status'] > 0) {
            array_push($conditions, "status = " . $params['status']);
            array_push($conCount, "pis.status = " . $params['status']);
        }
        if(!empty($validatedParams['nama'])) {
            array_push($conditions, "lower(sekolah.nama) LIKE '%" . strtolower($validatedParams['nama']) . "%'");
            array_push($conCount, "lower(sk.nama) LIKE '%" . strtolower($validatedParams['nama']) . "%'");
        }
        if(!empty($validatedParams['tahun_ajaran'])) {
            array_push($conditions, "lower(pak_isian_sekolah.tahun_ajaran) LIKE '%" . strtolower($validatedParams['tahun_ajaran']) . "%'");
            array_push($conCount, "lower(pis.tahun_ajaran) LIKE '%" . strtolower($validatedParams['tahun_ajaran']) . "%'");
        }
        if($validatedParams['isian_isian_null']) {
            array_push($conditions, "pak_isian_sekolah.id IS NULL");
            array_push($conCount, "pis.id IS NULL");
        }
        if($validatedParams['isian_isian_not_null']) {
            array_push($conditions, "pak_isian_sekolah.id IS NOT NULL");
            array_push($conCount, "pis.id IS NOT NULL");
        }
        if(!empty($validatedParams['provinsi'])) {
            array_push($conditions, "sekolah.provinsi ILIKE '%" . strtolower($validatedParams['provinsi']) . "%'");
            array_push($conCount, "sk.provinsi ILIKE '%" . strtolower($validatedParams['provinsi']) . "%'");
        }
        if(!empty($validatedParams['provinsi_kode'])) {
            array_push($conditions, "sekolah.kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
            array_push($conCount, "sk.kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
        }
        if(!empty($validatedParams['kota_kab'])) {
            array_push($conditions, "sekolah.kota_kab ILIKE '%" . strtolower($validatedParams['kota_kab']) . "%'");
            array_push($conCount, "sk.kota_kab ILIKE '%" . strtolower($validatedParams['kota_kab']) . "%'");
        }
        if(!empty($validatedParams['kota_kab_kode'])) {
            array_push($conditions, "sekolah.kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
            array_push($conCount, "sk.kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
        }
        if(!empty($validatedParams['jenjang'])) {
            if($this->is_json($validatedParams['jenjang'])) {
                $arrJenjang = array_values(json_decode($params['jenjang']));
                $arrCond = $arrCountCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "jenjang.nama_jenjang ILIKE '" . $jenjang . "'");
                    array_push($arrCountCond, "jj.nama_jenjang ILIKE '" . $jenjang . "'");
                }
                array_push($conditions, "(" . implode(" OR ", $arrCond) . ")");
                array_push($conCount, "(" . implode(" OR ", $arrCountCond) . ")");
            } else {
                array_push($conditions, "jenjang.nama_jenjang ILIKE '" . $validatedParams['jenjang'] . "'");
                array_push($conCount, "jj.nama_jenjang ILIKE '" . $validatedParams['jenjang'] . "'");
            }
        } elseif(!empty($validatedParams['jenjang_id'])) {
            if($this->is_json($validatedParams['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang_id']));
                array_push($conditions, "jenjang.id IN (" . implode(",", $arrJenjang) . ")");
                array_push($conCount, "jj.id IN (" . implode(",", $arrJenjang) . ")");
            } else {
                array_push($conditions, "jenjang.id = " . $validatedParams['jenjang_id']);
                array_push($conCount, "jj.id = " . $validatedParams['jenjang_id']);
            }
        }
        if(!empty($validatedParams['metode_id'])) {
            array_push($conditions, "pak_isian_sekolah_detail.metode_id = " . $validatedParams['metode_id']);
            array_push($conCount, "pisd.metode_id = " . $validatedParams['metode_id']);
        }


        $whereCount = empty($conCount) ? "" : "WHERE " . implode(' AND ', $conCount);

        $query = "
            SELECT 
                COUNT(*) AS total
            FROM
                (
                    SELECT 
                        sk.id
                    FROM
                        sekolah sk
                        LEFT JOIN pak_isian_sekolah pis ON pis.sekolah_npsn = sk.npsn AND pis.status = ".PAKIsianSekolah::STATUS_VERIFIED." AND pis.is_published = true
                        LEFT JOIN jenjang jj ON jj.id = sk.jenjang_id
                        LEFT JOIN pak_isian_sekolah_detail pisd ON pisd.isian_id::INTEGER = pis.id
                        $whereCount
                        GROUP BY sk.id, pis.id
                ) a
            	;
        ";

        $total_record = $this->rawQuery($query)[0]['total'];

        if($params['order_by'] == 'nama') {
            $order_by = 'sekolah.' . $params['order_by'];
        }
        else {
            $order_by = 'pak_isian_sekolah.' . $params['order_by'];
        }

        $selected = $this->rawQuery("
            SELECT
                sekolah.id AS id,
                sekolah.npsn AS npsn,
                sekolah.nama AS nama,
                sekolah.provinsi AS provinsi,
                sekolah.kota_kab AS kota_kab,
                sekolah.kecamatan AS kecamatan,
                sekolah.jenjang_id AS jenjang_id,
                sekolah.kode_provinsi_dagri AS kode_provinsi_dagri,
                sekolah.kode_kabupaten_dagri AS kode_kabupaten_dagri,
                sekolah.kecamatan_code AS kode_kecamatan_dagri,
                pak_isian_sekolah.tahun_ajaran AS tahun_ajaran,
                pak_isian_sekolah.id AS isian_sekolah_id
            FROM
                jaga.sekolah
                LEFT JOIN jaga.pak_isian_sekolah ON pak_isian_sekolah.sekolah_npsn = sekolah.npsn AND pak_isian_sekolah.status = ".\Jaga\Models\PAK\PAKIsianSekolah::STATUS_VERIFIED." AND pak_isian_sekolah.is_published = true 
                LEFT JOIN jaga.pak_isian_sekolah_detail ON pak_isian_sekolah_detail.isian_id::INTEGER = pak_isian_sekolah.id
                LEFT JOIN jaga.jenjang ON jenjang.id = sekolah.jenjang_id
            WHERE ".implode(' AND ', $conditions)."
            GROUP BY pak_isian_sekolah.id, sekolah.id
            ORDER BY ".$order_by." ".$validatedParams['sort']."
            LIMIT ".$validatedParams['limit']." OFFSET ".$validatedParams['offset']."
        ");

        $result = [];
        foreach ($selected as $item) {
            $theRow = new Sekolah();
            $theRow->assign($item);
            $itemResult = $theRow->toSimpleArray();

            $isianSekolahId = $item['isian_sekolah_id'];
            if (!is_null($isianSekolahId)) {
                $pakIsianSekolah = PAKIsianSekolah::findOrFail('id = ' . $isianSekolahId);
                $itemResult['isian_sekolah'] = $pakIsianSekolah->toArray(['id', 'sekolah_npsn', 'tahun_ajaran', 'deskripsi', 'status', 'is_published']);

                $itemResult['isian_sekolah']['isian_sekolah_detail'] = [];
                $details = $pakIsianSekolah->detail;
                if (!empty($details)) {
                    foreach ($details as $detail) {
                        array_push($itemResult['isian_sekolah']['isian_sekolah_detail'], $detail->toArray(['id', 'isian_id', 'metode_id', 'mata_pelajaran', 'kelas', 'nama_kegiatan', 'deskripsi', 'tgl_penerapan']));
                    }
                }
            } else {
                $itemResult['isian_sekolah'] = null;
            }

            array_push($result, $itemResult);
        }

        return new PaginatedResponse(
            $result,
            $total_record,
            $validatedParams['limit'],
            $validatedParams['offset']
        );
    }

    public function likeSekolah($npsn) {
        $this->checkIsAuthenticated();

        $rawBody = $this->request->getJsonRawBody(true);
        $rawBody['src'] = $rawBody ? (array_key_exists('src', $rawBody) ? (($rawBody['src'] != null && !empty($rawBody['src'])) ? strtolower($rawBody['src']) : 'sekolah' ) : 'sekolah') : 'sekolah';

        $this->db->begin();
        try {

            if($rawBody['src'] == 'sekolah') {
                //region Check if npsn exist
                $countSekolah = Sekolah::count([
                    'conditions' => 'npsn = ?0',
                    'bind' => [
                        $npsn
                    ]
                ]);
                if ($countSekolah == 0)
                    throw new ValidationException("Sekolah tidak ditemukan!");
                //endregion

                $liked = SekolahLike::count([
                    "conditions" => "sekolah_npsn = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                if ($liked == 0) {
                    $data = new SekolahLike();
                    $data->sekolah_npsn = $npsn;
                    $data->user_id = $this->userID;
                    $data->save();
                }

                $liked = SekolahLike::count([
                    "conditions" => "sekolah_npsn = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                $result = [
                    "liked" => ($liked > 0)
                ];
            }
            else if($rawBody['src'] == 'madrasah') {
                //region Check if id madrasah exist
                $countMadrasah = Madrasah::count([
                    'conditions' => 'id = ?0',
                    'bind' => [
                        $npsn
                    ]
                ]);
                if ($countMadrasah == 0)
                    throw new ValidationException("Madrasah tidak ditemukan!");
                //endregion

                $liked = MadrasahLike::count([
                    "conditions" => "madrasah_id = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                if ($liked == 0) {
                    $data = new MadrasahLike();
                    $data->madrasah_id = $npsn;
                    $data->user_id = $this->userID;
                    $data->save();
                }

                $liked = MadrasahLike::count([
                    "conditions" => "madrasah_id = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                $result = [
                    "liked" => ($liked > 0)
                ];
            }

            $this->db->commit();

            return new SimpleResponse($result);
        }
        catch (Exception $ex) {
            $this->db->rollback();
            throw $ex;
        }
    }

    public function unlikeSekolah($npsn) {
        $this->checkIsAuthenticated();

        $rawBody = $this->request->getJsonRawBody(true);
        $rawBody['src'] = $rawBody ? (array_key_exists('src', $rawBody) ? (($rawBody['src'] != null && !empty($rawBody['src'])) ? strtolower($rawBody['src']) : 'sekolah' ) : 'sekolah') : 'sekolah';

        $this->db->begin();
        try {
            if($rawBody['src'] == 'sekolah') {
                //region Check if npsn exist
                $countSekolah = Sekolah::count([
                    'conditions' => 'npsn = ?0',
                    'bind' => [
                        $npsn
                    ]
                ]);
                if ($countSekolah == 0)
                    throw new ValidationException("Sekolah tidak ditemukan!");
                //endregion

                $liked = SekolahLike::count([
                    "conditions" => "sekolah_npsn = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                if ($liked > 0) {
                    $data = SekolahLike::findFirst([
                        "conditions" => "sekolah_npsn = ?0 AND user_id = ?1",
                        "bind" => [
                            $npsn,
                            $this->userID
                        ]
                    ]);
                    $data->delete();
                }

                $liked = SekolahLike::count([
                    "conditions" => "sekolah_npsn = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                $result = [
                    "liked" => ($liked > 0)
                ];
            }
            else if($rawBody['src'] == 'madrasah') {
                //region Check if id madrasah exist
                $countMadrasah = Madrasah::count([
                    'conditions' => 'id = ?0',
                    'bind' => [
                        $npsn
                    ]
                ]);
                if ($countMadrasah == 0)
                    throw new ValidationException("Madrasah tidak ditemukan!");
                //endregion

                $liked = MadrasahLike::count([
                    "conditions" => "madrasah_id = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                if ($liked > 0) {
                    $query = "DELETE FROM jaga.madrasah_like WHERE madrasah_id = :id AND user_id = :user_id";
                    $this->rawQuery($query, [
                        'id' => $npsn,
                        'user_id' => $this->userID
                    ]);
                }

                $liked = MadrasahLike::count([
                    "conditions" => "madrasah_id = ?0 AND user_id = ?1",
                    "bind" => [
                        $npsn,
                        $this->userID
                    ]
                ]);

                $result = [
                    "liked" => ($liked > 0)
                ];
            }

            $this->db->commit();

            return new SimpleResponse($result);
        }
        catch (Exception $ex) {
            $this->db->rollback();
            throw $ex;
        }
    }

    public function listSekolahWithFavoritedKegiatan() {
        $request = $this->request;
        $params = $request->get();
        $params['limit'] = $request->get('limit', null, 10);
        $params['offset'] = $request->get('offset', null, 0);
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['insersi_favorited'] = $request->get('insersi_favorited', null, 0);
        $params['habituasi_favorited'] = $request->get('habituasi_favorited', null, 0);
        $params['lainnya_favorited'] = $request->get('lainnya_favorited', null, 0);
        $params['jenjang_id'] = $request->get('jenjang_id', null, 0);

        $validatedParams = $this->validate($params, [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
            'tahun_ajaran' => ['validators' => []],
            'insersi_favorited' => ['validators' => [[
                'name' => 'inclusion_in',
                'params' => [1,0]
            ]]],
            'habituasi_favorited' => ['validators' => [[
                'name' => 'inclusion_in',
                'params' => [1,0]
            ]]],
            'lainnya_favorited' => ['validators' => [[
                'name' => 'inclusion_in',
                'params' => [1,0]
            ]]],
            'jenjang_id' => ['validators' => ['digit']],
        ]);

        $innerConditions = [];
        $outerConditions = [];
        $isianConditions = [];

        $innerConditions[] = "pis.verified_at IS NOT NULL";
        $innerConditions[] = "pis.deleted_at IS NULL";
        $innerConditions[] = "pis.is_published = 't'";
        if(!empty($validatedParams['tahun_ajaran'])) {
            $innerConditions[] = "pis.tahun_ajaran = '" . $validatedParams['tahun_ajaran'] . "'";
        }

        //Jika semua filter dinyalakan, maka tampilkan semua data dan tidak perlu difilter
        //Tetapi jika hanya 1 atau 2 filter yg dinyalakan, maka hanya menampilkan data yang memang metode tersebut difavoritekan
        //Filter dibawah ini untuk handle hanya 1/2 filter yg dinyalakan
        if($validatedParams['insersi_favorited'] + $validatedParams['habituasi_favorited'] + $validatedParams['lainnya_favorited'] < 3 && $validatedParams['insersi_favorited'] + $validatedParams['habituasi_favorited'] + $validatedParams['lainnya_favorited'] > 0) {
            if($validatedParams['insersi_favorited'] == 1) {
                $outerConditions[] = "table_sekolah.total_insersi_favorited > 0";
                $isianConditions[] = "pisd.metode_id = 1";
            }
            if($validatedParams['habituasi_favorited'] == 1) {
                $outerConditions[] = "table_sekolah.total_habituasi_favorited > 0";
                $isianConditions[] = "pisd.metode_id = 2";
            }
            if($validatedParams['lainnya_favorited'] == 1) {
                $outerConditions[] = "table_sekolah.total_lainnya_favorited > 0";
                $isianConditions[] = "pisd.metode_id = 3";
            }
        }
        else if($validatedParams['insersi_favorited'] + $validatedParams['habituasi_favorited'] == 0) {
//            $outerConditions[] = "(table_sekolah.total_insersi_favorited + table_sekolah.total_habituasi_favorited + table_sekolah.total_lainnya_favorited) = 0";
            $outerConditions[] = "(table_sekolah.total_insersi_favorited + table_sekolah.total_habituasi_favorited + table_sekolah.total_lainnya_favorited) > 0";
        }
        else {
//            $outerConditions[] = "(table_sekolah.total_insersi_favorited + table_sekolah.total_habituasi_favorited + table_sekolah.total_lainnya_favorited) > 0";

            $outerConditions[] = "table_sekolah.total_insersi_favorited > 0";
            $outerConditions[] = "table_sekolah.total_habituasi_favorited > 0";
            $outerConditions[] = "table_sekolah.total_lainnya_favorited > 0";
        }

        if($validatedParams['jenjang_id'] > 0)
            $innerConditions[] = "s.jenjang_id = " . $validatedParams['jenjang_id'];

        $whereInner = empty($innerConditions) ? "" : "WHERE " . implode(" AND ", $innerConditions);
        $whereOuter = empty($outerConditions) ? "" : "WHERE " . implode(" AND ", $outerConditions);
        $whereIsian = empty($isianConditions) ? "" : "AND (" . implode(" OR ", $isianConditions) . ")";

        $queryCount = "
            SELECT
            	COUNT(*) as total
            FROM
            	(
            	SELECT
            		s.id,
            		s.npsn,
            		s.nama,
            		s.provinsi,
            		s.kota_kab,
            		s.kecamatan,
            		COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 1 AND pisd.is_favorited = 1 ) AS total_insersi_favorited,
            		COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 2 AND pisd.is_favorited = 1 ) AS total_habituasi_favorited,
            		COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 3 AND pisd.is_favorited = 1 ) AS total_lainnya_favorited 
            	FROM
            		sekolah s
            		LEFT JOIN pak_isian_sekolah pis ON s.npsn = pis.sekolah_npsn
            		LEFT JOIN pak_isian_sekolah_detail pisd ON pis.ID = pisd.isian_id :: INT 
            	$whereInner
            	GROUP BY
            		s.id,
            		s.npsn,
            		s.nama,
            		s.provinsi,
            		s.kota_kab,
            		s.kecamatan
            	) table_sekolah
            	$whereOuter
            	;
        ";

        $totalRecord = $this->rawQuery($queryCount)[0]['total'];

        $query = "
            SELECT
            	* 
            FROM
            	(
            	SELECT
            		s.id,
            		s.npsn,
            		s.nama,
            		s.provinsi,
            		s.kota_kab,
            		s.kecamatan,
            		COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 1 AND pisd.is_favorited = 1 ) AS total_insersi_favorited,
            		COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 2 AND pisd.is_favorited = 1 ) AS total_habituasi_favorited,
            		COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 3 AND pisd.is_favorited = 1 ) AS total_lainnya_favorited 
            	FROM
            		sekolah s
            		LEFT JOIN pak_isian_sekolah pis ON s.npsn = pis.sekolah_npsn
            		LEFT JOIN pak_isian_sekolah_detail pisd ON pis.ID = pisd.isian_id :: INT 
            	$whereInner
            	GROUP BY
            		s.id,
            		s.npsn,
            		s.nama,
            		s.provinsi,
            		s.kota_kab,
            		s.kecamatan
            	) table_sekolah
            	$whereOuter
            	ORDER BY (table_sekolah.total_insersi_favorited + table_sekolah.total_habituasi_favorited + table_sekolah.total_lainnya_favorited) DESC
            	LIMIT ". $validatedParams['limit'] . " OFFSET ". $validatedParams['offset'] . "
            	;
        ";

        $data = $this->rawQuery($query);
        $result = [];
        foreach ($data as $datum) {
            $q = "
                select 
                   pisd.*,
                   case pisd.metode_id when 1 then 'Metode Insersi' when 2 then 'Metode Habituasi' else 'Metode Lainnya' end as metode
                from pak_isian_sekolah_detail pisd 
                left join pak_isian_sekolah pis on pis.id = pisd.isian_id :: INT 
                where pis.sekolah_npsn = '".$datum['npsn']."' and pisd.is_favorited = 1 and pis.verified_at IS NOT NULL and pis.deleted_at IS NULL and pis.is_published = 't' ".$whereIsian."
            ";
            $datum['isian_sekolah'] = $this->rawQuery($q);
            array_push($result, $datum);
        }
        return new PaginatedResponse(
            $result,
            $totalRecord,
            $validatedParams['limit'],
            $validatedParams['offset']
        );
    }

    public function getTotalKegiatanFavorite() {
        $request = $this->request;
        $params = $request->get();
        $params['jenjang_id'] = $request->get('jenjang_id', null, 0);
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['insersi_favorited'] = $request->get('insersi_favorited', null, 0);
        $params['habituasi_favorited'] = $request->get('habituasi_favorited', null, 0);
        $params['lainnya_favorited'] = $request->get('lainnya_favorited', null, 0);

        $validatedParams = $this->validate($params, [
            'tahun_ajaran' => ['validators' => []],
            'insersi_favorited' => ['validators' => [[
                'name' => 'inclusion_in',
                'params' => [1,0]
            ]]],
            'habituasi_favorited' => ['validators' => [[
                'name' => 'inclusion_in',
                'params' => [1,0]
            ]]],
            'lainnya_favorited' => ['validators' => [[
                'name' => 'inclusion_in',
                'params' => [1,0]
            ]]],
            'jenjang_id' => ['validators' => ['digit']]
        ]);

        $innerConditions = [];
        $outerConditions = [];

        $innerConditions[] = "pis.verified_at IS NOT NULL";
        $innerConditions[] = "pis.deleted_at IS NULL";
        $innerConditions[] = "pis.is_published = 't'";

        if($validatedParams["jenjang_id"] > 0)
            $innerConditions[] = "s.jenjang_id = " . $validatedParams["jenjang_id"];

        if($validatedParams['insersi_favorited'] + $validatedParams['habituasi_favorited'] + $validatedParams['lainnya_favorited'] < 3 && $validatedParams['insersi_favorited'] + $validatedParams['habituasi_favorited'] + $validatedParams['lainnya_favorited'] > 0) {
            if($validatedParams['insersi_favorited'] == 1)
                $outerConditions[] = "table_sekolah.total_insersi_favorited > 0";
            if($validatedParams['habituasi_favorited'] == 1)
                $outerConditions[] = "table_sekolah.total_habituasi_favorited > 0";
            if($validatedParams['lainnya_favorited'] == 1)
                $outerConditions[] = "table_sekolah.total_lainnya_favorited > 0";
        }
        else if($validatedParams['insersi_favorited'] + $validatedParams['habituasi_favorited'] == 0) {
//            $outerConditions[] = "(table_sekolah.total_insersi_favorited + table_sekolah.total_habituasi_favorited + table_sekolah.total_lainnya_favorited) = 0";
            $outerConditions[] = "(table_sekolah.total_insersi_favorited + table_sekolah.total_habituasi_favorited + table_sekolah.total_lainnya_favorited) > 0";
        }
        else {
//            $outerConditions[] = "(table_sekolah.total_insersi_favorited + table_sekolah.total_habituasi_favorited + table_sekolah.total_lainnya_favorited) > 0";

            $outerConditions[] = "table_sekolah.total_insersi_favorited > 0";
            $outerConditions[] = "table_sekolah.total_habituasi_favorited > 0";
            $outerConditions[] = "table_sekolah.total_lainnya_favorited > 0";
        }

        $whereInner = empty($innerConditions) ? "" : "WHERE " . implode(" AND ", $innerConditions);
        $whereOuter = empty($outerConditions) ? "" : "WHERE " . implode(" AND ", $outerConditions);

        $query = "
            SELECT 
            	SUM ( table_favorite.total_insersi_favorited ) AS total_insersi_favorited,
            	SUM ( table_favorite.total_habituasi_favorited ) AS total_habituasi_favorited,
            	SUM ( table_favorite.total_lainnya_favorited ) AS total_lainnya_favorited 
            FROM
            	(
            	SELECT
            		* 
            	FROM
            		(
            		SELECT
            			s.ID,
            			s.npsn,
            			s.nama,
            			s.provinsi,
            			s.kota_kab,
            			s.kecamatan,
            			COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 1 AND pisd.is_favorited = 1 ) AS total_insersi_favorited,
            			COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 2 AND pisd.is_favorited = 1 ) AS total_habituasi_favorited,
            			COUNT ( pisd.* ) FILTER ( WHERE pisd.metode_id = 3 AND pisd.is_favorited = 1 ) AS total_lainnya_favorited 
            		FROM
            			sekolah s
            			LEFT JOIN pak_isian_sekolah pis ON s.npsn = pis.sekolah_npsn
            			LEFT JOIN pak_isian_sekolah_detail pisd ON pis.ID = pisd.isian_id :: INT 
            		$whereInner
            		GROUP BY
            			s.ID,
            			s.npsn,
            			s.nama,
            			s.provinsi,
            			s.kota_kab,
            			s.kecamatan 
            		) table_sekolah 
            	$whereOuter 
            	) table_favorite
        ";

        $data = $this->rawQuery($query);
        return new SimpleResponse($data);
    }

    public function tableauPAKTab($tab) {
        //tableau URL: https://statistik.kpk.go.id/#/views/PAK_instansi_sekolah_tab_1/PAK_Tab1
        $viewName = "";
        if($tab == 1)
            $viewName = "PAK_instansi_sekolah_tab_1/PAK_Tab1";
        else if($tab == 2)
            $viewName = "PAK_instansi_sekolah_tab_2/PAK_Tab2";
        else if($tab == 3)
            $viewName = "PAK_instansi_sekolah_tab_3/PAK_Tab3";
        else if($tab == 4)
            $viewName = "PAK_instansi_sekolah_tab_4/PAK_Tab4";
        else if($tab == 5)
            $viewName = "PAK_instansi_sekolah_tab_5/PAK_Tab5";
        else if($tab == 6)
            $viewName = "PAK_instansi_sekolah_tab_6/PAK_Tab6";
        else
            throw new ValidationException("Tab is not exist!");

        $config = $this->config["tableau_pak_config"];

        return new SimpleResponse(TableauHelper::get_trusted_url_tableau($config["username"], $config["host"], $viewName));
    }

    public function countByJenjang() {
        $params = $this->request->get();
        $params['jenjang'] = array_key_exists('jenjang', $params) ? $params['jenjang'] : null;
        $params['group_jenjang'] = array_key_exists('group_jenjang', $params) ? $params['group_jenjang'] : null;

        $useGroupJenjang = false;
        if (!is_null($params['jenjang'])) {
            $arrJenjang = explode(",", strtolower($params['jenjang']));
            $jenjangs = Jenjang::find(['conditions' => 'lower(nama_jenjang) IN ({jenjangs:array})', 'bind' => ['jenjangs' => $arrJenjang]]);
        } else if (!is_null($params['group_jenjang'])) {
            if($this->is_json($params['group_jenjang'])) {
                $arrJenjang = array_values(json_decode($params['group_jenjang']));
                $jenjangs = Jenjang::find(['conditions' => 'lower(group_jenjang) IN ({jenjangs:array})', 'bind' => ['jenjangs' => $arrJenjang]]);
            } else {
                $jenjangs = Jenjang::find(['conditions' => 'group_jenjang ILIKE :jenjang:', 'bind' => ['jenjang' => $params['group_jenjang']]]);
            }
            $useGroupJenjang = true;
        } else {
            $jenjangs = Jenjang::find();
        }
        $arrSelect = [];
        foreach ($jenjangs as $jenjang) {
            /** @var Jenjang $jenjang */
            if (!$useGroupJenjang) {
                array_push($arrSelect, "COUNT(*) FILTER (WHERE jenjang_id = ".$jenjang->id.") AS " . $this->cleanNamaJenjang($jenjang->nama_jenjang));
            } else {
                array_push($arrSelect, "COUNT(*) FILTER (WHERE group_jenjang = '".$jenjang->group_jenjang."') AS " . $this->cleanNamaJenjang($jenjang->group_jenjang));
            }
        }
        $result = $this->rawQuery("select " . implode($arrSelect, ",") . " from jaga.sekolah s left join jaga.jenjang j on j.id = s.jenjang_id where s.deleted_at is null");

        return new SimpleResponse($result[0]);
    }

    public function countUserByJenjang() {
        $request = $this->request;
        $params = $request->get();
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['group_jenjang'] = $request->get('group_jenjang', null, '');

        $rules = [
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang' => ['validators' => []],
            'group_jenjang' => ['validators' => []],
        ];

        $validatedParams = $this->validate($params, $rules);
        $conditions = ["sk.deleted_at is null"];
        if(!empty($validatedParams['provinsi_kode'])) {
            array_push($conditions, "sk.kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
        }
        if(!empty($validatedParams['kota_kab_kode'])) {
            array_push($conditions, "sk.kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
        }
        $useGroupJenjang = false;
        if (!empty($validatedParams['jenjang'])) {
            $arrJenjang = explode(",", strtolower($validatedParams['jenjang']));
            $jenjangs = Jenjang::find(['conditions' => 'lower(nama_jenjang) IN ({jenjangs:array})', 'bind' => ['jenjangs' => $arrJenjang]]);
        } else if (!empty($validatedParams['group_jenjang'])) {
            if($this->is_json($validatedParams['group_jenjang'])) {
                $arrJenjang = array_values(json_decode($validatedParams['group_jenjang']));
                $jenjangs = Jenjang::find(['conditions' => 'lower(group_jenjang) IN ({jenjangs:array})', 'bind' => ['jenjangs' => $arrJenjang]]);
            } else {
                $jenjangs = Jenjang::find(['conditions' => 'group_jenjang ILIKE :jenjang:', 'bind' => ['jenjang' => $validatedParams['group_jenjang']]]);
            }
            $useGroupJenjang = true;
        } else {
            $jenjangs = Jenjang::find();
        }

        $arrSelect = [];
        foreach ($jenjangs as $jenjang) {
            /** @var Jenjang $jenjang */
            if (!$useGroupJenjang) {
                array_push($arrSelect, "COUNT(DISTINCT(sk.npsn)) FILTER (WHERE sk.jenjang_id = ".$jenjang->id.") AS " . $this->cleanNamaJenjang($jenjang->nama_jenjang));
            } else {
                array_push($arrSelect, "COUNT(DISTINCT(sk.npsn)) FILTER (WHERE j.group_jenjang = '".$jenjang->group_jenjang."') AS " . $this->cleanNamaJenjang($jenjang->group_jenjang));
            }
        }

        $whereSekolah  = (empty($conditions) ? '' : ("WHERE " . implode(" AND ", $conditions)));

        $whereUser  = (empty($conditions) ? 'WHERE us.deleted_at IS NULL' : ("WHERE us.deleted_at IS NULL AND " . implode(" AND ", $conditions)));

        $select = implode(",", $arrSelect);

        $query = "
            SELECT $select
            FROM jaga.sekolah sk
            LEFT JOIN jaga.jenjang j ON sk.jenjang_id = j.id
            $whereSekolah
            ;
        ";
        $totalSekolah = $this->rawQuery($query)[0];

        $query = "
            SELECT $select
            FROM users_sekolah us
            LEFT JOIN sekolah sk ON us.npsn = sk.npsn
            LEFT JOIN jenjang j ON j.id = sk.jenjang_id
            $whereUser
        	;
        ";
        $totalUser = $this->rawQuery($query)[0];

        return [
            'total_sekolah' => $totalSekolah,
            'total_user' => $totalUser,
        ];
    }

    public function laporanSekolah() {
        $request = $this->request;
        $params = $request->get();
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');
        $params['group_jenjang'] = $request->get('group_jenjang', null, '');
        $params['metode_id'] = $request->get('metode_id', null, '');
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['limit'] = $request->get('limit', null, 5);
        $params['offset'] = $request->get('offset', null, 0);

        $rules = [
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang_id' => ['validators' => ['digit']],
            'group_jenjang' => ['validators' => []],
            'metode_id' => ['validators' => ['digit']],
            'tahun_ajaran' => ['validators' => []],
            'limit' => ['validators' => ['required', 'digit']],
            'offset' => ['validators' => ['required', 'digit']],
            'keyword' => ['validators' => [[
                'name' => 'max_string',
                'params' => 50
            ]]]
        ];

        $validatedParams = $this->validate($params, $rules);
        $where = "pis.deleted_at IS NULL";
        $bind = [];

        if (!empty($validatedParams['provinsi_kode'])) {
            $where .= " and mp.provinsi_code = :provinsi_kode";
            $bind['provinsi_kode'] = $validatedParams['provinsi_kode'];
        }
        if (!empty($validatedParams['kota_kab_kode'])) {
            $where .= " and mkk.kota_kabupaten_code = :kota_kab_kode";
            $bind['kota_kab_kode'] = $validatedParams['kota_kab_kode'];
        }
        if (!empty($validatedParams['jenjang_id'])) {
            $where .= " and j.id = :jenjang_id";
            $bind['jenjang_id'] = $validatedParams['jenjang_id'];
        } else if (!empty($validatedParams['group_jenjang'])) {
            $arrJenjang = array_values(json_decode($validatedParams['group_jenjang']));
            $jenjangs = [];
            foreach ($arrJenjang as $j) {
                array_push($jenjangs, "'".strtoupper($j)."'");
            }
            $where .= " and j.group_jenjang IN (".implode(", ", $jenjangs).")";
        }
        if (!empty($validatedParams['metode_id'])) {
            $where .= " and pisd.metode_id = :metode_id";
            $bind['metode_id'] = $validatedParams['metode_id'];
        }
        if (!empty($validatedParams['tahun_ajaran'])) {
            $where .= " and pis.tahun_ajaran = :tahun_ajaran";
            $bind['tahun_ajaran'] = $validatedParams['tahun_ajaran'];
        }
        if (!empty($validatedParams['keyword'])) {
            $where .= " and s.nama ILIKE :keyword";
            $bind['keyword'] = '%' . $validatedParams['keyword'] . '%';
        }
        $limit = $validatedParams['limit'];
        $offset = $validatedParams['offset'];

        $totalSelect = "SELECT COUNT(*) as total";
        $rowSelect = "SELECT
                j.nama_jenjang,
                s.nama,
                s.npsn,
                mp.provinsi_name as provinsi,
                mkk.kota_kabupaten_name as kabupaten_kota,
                pis.id as isian_id,
                pis.tahun_ajaran,
                pisd.id as isian_detail_id,
                pisd.is_favorited,
                CASE 
                    WHEN pisd.metode_id = 1 THEN  'Insersi'
                    WHEN pisd.metode_id = 2 THEN 'Habituasi'
                    ELSE 'Lainnya'
                END as metode";

        $query = "
            FROM jaga.pak_isian_sekolah pis
            LEFT JOIN jaga.pak_isian_sekolah_detail pisd ON pisd.isian_id::INTEGER = pis.id
            LEFT JOIN jaga.sekolah s ON s.npsn = pis.sekolah_npsn
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code = s.kode_provinsi_dagri
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code = s.kode_kabupaten_dagri
            LEFT JOIN jaga.jenjang j ON j.id = s.jenjang_id
            WHERE $where
        ";

        $totalQuery = "$totalSelect $query;";
        $total = $this->rawQuery("$totalQuery", $bind)[0]['total'];

        $rowQuery = "$rowSelect $query LIMIT $limit OFFSET $offset;";
        $rows = $this->rawQuery($rowQuery, $bind);

        return new PaginatedResponse($rows, $total, $limit, $offset);
    }

    public function countLaporanByMetode() {
        $request = $this->request;
        $params = $request->get();
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');
        $params['group_jenjang'] = $request->get('group_jenjang', null, '');
        $params['metode_id'] = $request->get('metode_id', null, '');
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');

        $rules = [
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang_id' => ['validators' => ['digit']],
            'group_jenjang' => ['validators' => []],
            'metode_id' => ['validators' => ['digit']],
            'tahun_ajaran' => ['validators' => []],
            'keyword' => ['validators' => [[
                'name' => 'max_string',
                'params' => 50
            ]]]
        ];

        $validatedParams = $this->validate($params, $rules);
        $where = "pis.deleted_at IS NULL";
        $bind = [];

        if (!empty($validatedParams['provinsi_kode'])) {
            $where .= " and mp.provinsi_code = :provinsi_kode";
            $bind['provinsi_kode'] = $validatedParams['provinsi_kode'];
        }
        if (!empty($validatedParams['kota_kab_kode'])) {
            $where .= " and mkk.kota_kabupaten_code = :kota_kab_kode";
            $bind['kota_kab_kode'] = $validatedParams['kota_kab_kode'];
        }
        if (!empty($validatedParams['jenjang_id'])) {
            $where .= " and j.id = :jenjang_id";
            $bind['jenjang_id'] = $validatedParams['jenjang_id'];
        } else if (!empty($validatedParams['group_jenjang'])) {
            $arrJenjang = array_values(json_decode($validatedParams['group_jenjang']));
            $jenjangs = [];
            foreach ($arrJenjang as $j) {
                array_push($jenjangs, "'".strtoupper($j)."'");
            }
            $where .= " and j.group_jenjang IN (".implode(", ", $jenjangs).")";
        }
        if (!empty($validatedParams['metode_id'])) {
            $where .= " and pisd.metode_id = :metode_id";
            $bind['metode_id'] = $validatedParams['metode_id'];
        }
        if (!empty($validatedParams['tahun_ajaran'])) {
            $where .= " and pis.tahun_ajaran = :tahun_ajaran";
            $bind['tahun_ajaran'] = $validatedParams['tahun_ajaran'];
        }
        if (!empty($validatedParams['keyword'])) {
            $where .= " and s.nama ILIKE :keyword";
            $bind['keyword'] = '%' . $validatedParams['keyword'] . '%';
        }

        $query = "
            SELECT
            	COUNT(*) FILTER (WHERE pisd.metode_id = 1) as insersi,
                COUNT(*) FILTER (WHERE pisd.metode_id = 2) as habituasi,
                COUNT(*) FILTER (WHERE pisd.metode_id NOT IN (1,2)) as lainnya
            FROM jaga.pak_isian_sekolah pis
            LEFT JOIN jaga.pak_isian_sekolah_detail pisd ON pisd.isian_id::INTEGER = pis.id
            LEFT JOIN jaga.sekolah s ON s.npsn = pis.sekolah_npsn
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code = s.kode_provinsi_dagri
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code = s.kode_kabupaten_dagri
            LEFT JOIN jaga.jenjang j ON j.id = s.jenjang_id
            WHERE $where
        ";

        $result = $this->rawQuery($query, $bind)[0];

        return new SimpleResponse($result);
    }

    public function mapRegulasi() {
        $params = $this->request->get();
        $params = $this->validate(
            $params,
            [
                'id_provinsi' => ['validators' => ['digit']]
            ]
        );
        $where = "";
        $bind = [];
        $select = "id_provinsi as id_region,";
        if (!empty($params['id_provinsi'])) {
            $select = "id_kota_kabupaten as id_region,";
            $where = " and i.id_provinsi = :id_provinsi and i.id_kota_kabupaten IS NOT NULL";
            $bind['id_provinsi'] = $params['id_provinsi'];
        }
        $regulasi = $this->rawQuery("
            SELECT
                $select
                count(*) as jumlah_pemda,
                COALESCE(sum(regulasi), 0) as jumlah_regulasi
            FROM (
                SELECT i.id, i.id_provinsi, i.id_kota_kabupaten, a.regulasi 
                FROM instansi i FULL JOIN (
                    SELECT 
                        ins.id,
                        CASE 
                            WHEN (count(*) > 0) THEN 1 
                            ELSE 0 
                        END AS regulasi
                    FROM
                        jaga.pak_verifikasi_implementasi pvi
                        LEFT JOIN jaga.pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                        LEFT JOIN jaga.pak_implementasi pi ON pvi.implementasi_id = pi.id 
                        LEFT JOIN jaga.instansi ins ON pi.instansi_id = ins.id
                    WHERE
                        pvi.nilai_verifikasi = 100 
                        AND pq.questionnaire_category_id = 1
                        AND ins.id_tipe IN (1,2)
                    GROUP BY
                        ins.id
                ) a ON a.id = i.id
                WHERE i.id_tipe IN (1,2) AND i.deleted_at IS NULL $where
                ORDER BY id_provinsi, id_kota_kabupaten NULLS FIRST
            ) b GROUP BY id_region
        ", $bind);

        $map = [];
        foreach ($regulasi as $reg) {
            try {
                $value = ($reg['jumlah_regulasi'] / $reg['jumlah_pemda']) * 100;
            } catch (\Exception $e) {
                $value = 0;
            }
            $arr = [
                'id_region' => $reg['id_region'],
                'value' => $value
            ];
            array_push($map, $arr);
        }

        return new SimpleResponse($map);
    }

    public function mapAkunSekolah() {
        $params = $this->request->get();
        $params = $this->validate(
            $params,
            [
                'id_provinsi' => ['validators' => ['digit']]
            ]
        );
        if (!empty($params['id_provinsi'])) {
            $data = $this->mapAkunSekolahByKabupaten($params['id_provinsi']);
        } else {
            $data = $this->mapAkunSekolahByProvinsi();
        }

        $map = [];
        foreach ($data as $datum) {
            try {
                $value = ($datum['jumlah_akun'] / $datum['jumlah_sekolah']) * 100;
            } catch (\Exception $e) {
                $value = 0;
            }

            $arr = [
                'id_region' => $datum['id_region'],
                'value' => $value
            ];
            array_push($map, $arr);
        }

        return new SimpleResponse($map);
    }

    public function mapLaporanSekolah() {
        $params = $this->request->get();
        $params = $this->validate(
            $params,
            [
                'id_provinsi' => ['validators' => ['digit']]
            ]
        );
        if (!empty($params['id_provinsi'])) {
            $data = $this->mapLaporanSekolahByKabupaten($params['id_provinsi']);
        } else {
            $data = $this->mapLaporanSekolahByProvinsi();
        }

        $map = [];
        foreach ($data as $datum) {
            try {
                $value = ($datum['jumlah_laporan'] / $datum['jumlah_sekolah']) * 100;
            } catch (\Exception $e) {
                $value = 0;
            }

            $arr = [
                'id_region' => $datum['id_region'],
                'value' => $value
            ];
            array_push($map, $arr);
        }

        return new SimpleResponse($map);
    }

    public function listTahunAjaran() {
        $query = "SELECT DISTINCT(tahun_ajaran) FROM pak_isian_sekolah ORDER BY tahun_ajaran;";

        $result = $this->rawQuery($query);

        return new SimpleResponse(array_column($result, 'tahun_ajaran'));
    }

    public function filterSearch() {
        $result = [];
        $jenjangs = Jenjang::query()
            ->columns("id, nama_jenjang, src, group_jenjang")
            ->where("is_shown = 1")
            ->andWhere("src = 'sekolah'")
            ->orderBy("sort_order")
            ->execute();
        $result["jenjang"] = $jenjangs;
        $provinsis = MasterProvinsi::query()
            ->columns("provinsi_code, provinsi_name")
            ->orderBy("provinsi_name")
            ->execute();
        $result["provinsi"] = $provinsis;
        $kabKotas = MasterKotaKabupaten::query()
            ->columns("provinsi_code, kota_kabupaten_code, kota_kabupaten_name")
            ->orderBy("provinsi_code, kota_kabupaten_name")
            ->execute();
        $result["kab_kota"] = $kabKotas;

        return new SimpleResponse($result);
    }

    public function searchNama() {
        $params = $this->request->get();
        $params["limit"] = array_key_exists("limit", $params) ? $params["limit"] : 30;
        $params = $this->validate($params, [
            "keyword" => ["validators" => ["required", ["name" => "min_string", "params" => 3], ["name" => "max_string", "params" => 50]]],
            "id_jenjang" => ["validators" => ["required", "digit"]],
            "limit" => ["validators" => ["digit"]],
            "id_provinsi" => ["validators" => ["digit"]],
            "id_kota_kabupaten" => ["validators" => ["digit"]],
        ]);

        $where[] = "deleted_at is null";
        $where[] = "jenjang_id = :id_jenjang:";
        $where[] = "nama ILIKE :keyword:";
        $bind["id_jenjang"] = $params["id_jenjang"];
        $bind["keyword"] = "%" . $params["keyword"] . "%";
        if (!empty($params["id_provinsi"])) {
            $where[] = "kode_provinsi_dagri = :id_provinsi:";
            $bind["id_provinsi"] = $params["id_provinsi"];
        }
        if (!empty($params["id_kota_kabupaten"])) {
            $where[] = "kode_kabupaten_dagri = :id_kota_kabupaten:";
            $bind["id_kota_kabupaten"] = $params["id_kota_kabupaten"];
        }

        $result = Sekolah::query()
            ->columns("nama, kode_provinsi_dagri, kode_kabupaten_dagri")
            ->where(implode(" and ", $where))
            ->bind($bind)
            ->limit($params["limit"])
            ->execute();

        return new CollectionPaginatedResponse($result);
    }

    public function detailWithFasilitas() {
        $getParams = $this->request->get();
        $getParams['npsn'] = array_key_exists('npsn', $getParams) ? $getParams['npsn'] : null;

        $validatedRequest = $this->validate(
            $getParams,
            [
                'npsn' => ['validators' => ['required']]
            ]
        );

        $result = [];

        $totalResult = count($result);
        return new PaginatedResponse($result, $totalResult, $totalResult, 0);
    }

    #region PRIVATE
    private function pakBaseQuery() {
        return "
            SELECT
                i.id as isian_id,
                d.id as isian_detail_id,
                i.sekolah_npsn as npsn,
                s.nama as nama_sekolah,
                s.provinsi,
                s.kota_kab,
                s.kecamatan,
                i.tahun_ajaran,
                i.deskripsi,
                d.deskripsi as deskripsi_kegiatan,
                d.tgl_penerapan,
                d.nama_kegiatan,
                d.link_youtube,
                d.link_facebook,
                d.link_blog,
                d.metode_id,
                CASE d.metode_id
                WHEN 1 THEN
                    'Insersi Mata Pelajaran'
                WHEN 2 THEN
                    'Habituasi atau Pembiasaan'
                ELSE
                    'Metode Lainnya'
            END as metode
            FROM
                pak_isian_sekolah_detail d
            LEFT JOIN pak_isian_sekolah i ON i.id = d.isian_id::INTEGER
            LEFT JOIN sekolah s ON i.sekolah_npsn = s.npsn
        ";
    }

    private function pakGetAttachments($rows) {
        $result = [];
        foreach ($rows as $row) {
            $detail = new PAKIsianSekolahDetail();
            $detail->id = $row['isian_detail_id'];
            if (!empty($detail->attachments)) {
                foreach ($detail->attachments as $a) {
                    if ($a->tipe == PAKIsianSekolahAttachment::FILE_TYPE_DOCUMENT) {
                        $row['documents'][] = $a->toArray(['id', 'attachment_id']);
                    }
                    if ($a->tipe == PAKIsianSekolahAttachment::FILE_TYPE_IMAGE) {
                        $row['images'][] = $a->toArray(['id', 'attachment_id']);
                    }
                }
            }
            array_push($result, $row);
        }

        return $result;
    }

    private function pakGenerateRandomIds($rows, $limit = 5) {
        $arrIds = array_column($rows, 'id');
        $ids = [];
        if (sizeof($arrIds) <= $limit) {
            $ids = $arrIds;
        } else {
            for ($i = 0; $i < $limit; $i++) {
                try {
                    $rand = random_int(0, sizeof($arrIds) - 1);
                    while (in_array($arrIds[$rand], $ids)) {
                        $rand = random_int(0, sizeof($arrIds) - 1);
                    }
                    array_push($ids, $arrIds[$rand]);
                } catch (\Exception $e) {
                    throw $e;
                }
            }
        }

        return $ids;
    }

    private function pakStatRegulasi() {
        $countRegulasi = $this->rawQuery("
            SELECT 
            	COUNT(*) FILTER (WHERE ins.id_tipe = 3) AS total_kementerian,
            	COUNT(*) FILTER (WHERE ins.id_tipe = 1) AS total_provinsi,
            	COUNT(*) FILTER (WHERE ins.id_tipe = 2) AS total_kab_kota
            FROM
            	pak_verifikasi_implementasi pvi
            	LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
            	LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
            	LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                LEFT JOIN pak_verifikasi_implementasi_attachment pvia ON pvia.verifikasi_id = pvi.id
            WHERE
            	pvi.nilai_verifikasi = 100
            	AND pq.questionnaire_category_id = 1
            ;
        ")[0];

        $countPemda = $this->rawQuery("
            SELECT 
                COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 3) AS count_kementerian,
                COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 1) AS count_provinsi,
                COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 2) AS count_kab_kota
            FROM
                pak_verifikasi_implementasi pvi
                LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                LEFT JOIN instansi ins ON pi.instansi_id = ins.id
            WHERE
                pvi.nilai_verifikasi = 100
                AND pq.questionnaire_category_id = 1
            ;
        ")[0];

        return array_merge($countRegulasi, $countPemda);
    }

    private function pakStatIsianSekolah() {
        $request = $this->request;
        $params = $request->get();
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['provinsi'] = $request->get('provinsi', null, '');
        $params['kota_kab'] = $request->get('kota_kab', null, '');
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');

        $rules = [
            'tahun_ajaran' => ['validators'  => []],
            'provinsi' => ['validators' => []],
            'kota_kab' => ['validators' => []],
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang' => ['validators' => []],
            'group_jenjang' => ['validators' => []],
            'jenjang_id' => ['validators' => []]
        ];

        $validatedParams = $this->validate($params, $rules);
        $whereSekolah = "";
        $conditions = ["sk.deleted_at is null"];
        if(!empty($validatedParams['provinsi'])) {
            array_push($conditions, "sk.provinsi ILIKE '%" . strtolower($validatedParams['provinsi']) . "%'");
        }
        if(!empty($validatedParams['provinsi_kode'])) {
            array_push($conditions, "sk.kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
        }
        if(!empty($validatedParams['kota_kab'])) {
            array_push($conditions, "sk.kota_kab ILIKE '%" . strtolower($validatedParams['kota_kab']) . "%'");
        }
        if(!empty($validatedParams['kota_kab_kode'])) {
            array_push($conditions, "sk.kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
        }
        if(!empty($validatedParams['jenjang'])) {
            if($this->is_json($validatedParams['jenjang'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "LOWER(jj.nama_jenjang) LIKE '" . strtolower($jenjang) . "'");
                }
                array_push($conditions, "(" . implode(" OR ", $arrCond) . ")");
            } else {
                array_push($conditions, "LOWER(jj.nama_jenjang) LIKE '" . strtolower($validatedParams['jenjang']) . "'");
            }
        } elseif(!empty($validatedParams['jenjang_id'])) {
            if($this->is_json($validatedParams['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang_id']));
                array_push($conditions, "jj.id IN (" . implode(",", $arrJenjang) . ")");
            } else {
                array_push($conditions, "jj.id = " . $validatedParams['jenjang_id']);
            }
        } elseif (!empty($validatedParams['group_jenjang'])) {
            if($this->is_json($validatedParams['group_jenjang'])) {
                $arrJenjang = array_values(json_decode($validatedParams['group_jenjang']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "jj.group_jenjang ILIKE '" . strtolower($jenjang) . "'");
                }
                array_push($conditions, "(" . implode(" OR ", $arrCond) . ")");
            } else {
                array_push($conditions, "jj.group_jenjang ILIKE '" . strtolower($validatedParams['group_jenjang']) . "'");
            }
        }

        $whereSekolah  = (empty($conditions) ? '' : ("WHERE " . implode(" AND ", $conditions)));

        $whereUser  = (empty($conditions) ? 'WHERE us.deleted_at IS NULL' : ("WHERE us.deleted_at IS NULL AND " . implode(" AND ", $conditions)));

        array_push($conditions, 'pis.deleted_at IS NULL');
        if(!empty($validatedParams['tahun_ajaran'])) {
            array_push($conditions, "pis.tahun_ajaran = '" . strtolower($validatedParams['tahun_ajaran']) . "'");
        }

        $whereIsian = ("WHERE " . implode(" AND ", $conditions));

        $select = "COUNT(DISTINCT(sk.npsn)) AS total";

        $query = "
            SELECT
            	$select
            FROM
            	sekolah sk
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereSekolah
            	;
        ";
        $totalSekolah = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_DRAFT. "
            	;
        ";
        $totalIsianDraft = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_SUBMITTED. "
            	;
        ";
        $totalIsianSubmitted = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_VERIFIED. "
            	;
        ";
        $totalIsianVerified = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_RETURNED. "
            	;
        ";
        $totalIsianReturned = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status IS NULL
            	;
        ";
        $totalIsianUnknown = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian
            	;
        ";
        $totalPieIrisan = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	users_sekolah us
            	LEFT JOIN sekolah sk ON us.npsn = sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereUser
        	;
        ";

        $totalPieUserOnly = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	users_sekolah us
            	LEFT JOIN sekolah sk ON us.npsn = sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	LEFT JOIN pak_isian_sekolah pis ON pis.sekolah_npsn = sk.npsn
            	$whereUser AND pis.id IS NULL
        	;
        ";
        $totalUserWoIsian = $this->rawQuery($query)[0]['total'];

        return [
            'total_sekolah' => $totalSekolah,
            'total_isian_draft' => $totalIsianDraft,
            'total_isian_submitted' => $totalIsianSubmitted,
            'total_isian_verified' => $totalIsianVerified,
            'total_isian_returned' => $totalIsianReturned,
            'total_isian_unknown' => $totalIsianUnknown,
            'total_sekolah_tanpa_user_isian' => $totalSekolah - $totalPieUserOnly,
            'total_user_tanpa_isian' => $totalUserWoIsian,
            'total_irisan_user_isian' => $totalPieIrisan,
            'total_user_only' => $totalPieUserOnly,
        ];
    }

    private function mapAkunSekolahByProvinsi() {
        return $this->rawQuery("
            SELECT a.id_region, COALESCE(jumlah_sekolah, 0) as jumlah_sekolah, COALESCE(jumlah_akun,0) as jumlah_akun
            FROM (
                SELECT s.kode_provinsi_dagri as id_region, count(*) as jumlah_sekolah FROM jaga.sekolah s WHERE s.deleted_at IS NULL AND s.kode_provinsi_dagri IS NOT NULL AND s.kode_provinsi_dagri <> '' GROUP BY s.kode_provinsi_dagri
            ) a
            LEFT JOIN	(
                SELECT 
                    s.kode_provinsi_dagri as id_region,
                    count(distinct(us.npsn)) as jumlah_akun
                FROM
                    jaga.users_sekolah us
                    LEFT JOIN jaga.sekolah s ON s.npsn = us.npsn
                WHERE
                    s.deleted_at IS NULL AND us.deleted_at IS NULL AND kode_provinsi_dagri IS NOT NULL
                GROUP BY
                    s.kode_provinsi_dagri
            ) b ON b.id_region = a.id_region
        ");
    }

    private function mapAkunSekolahByKabupaten($idProvinsi) {
        return $this->rawQuery("
            SELECT a.id_region, COALESCE(jumlah_sekolah, 0) as jumlah_sekolah, COALESCE(jumlah_akun,0) as jumlah_akun
            FROM (
                SELECT s.kode_kabupaten_dagri as id_region, count(*) as jumlah_sekolah FROM jaga.sekolah s WHERE s.deleted_at IS NULL AND s.kode_provinsi_dagri = :id_provinsi AND s.kode_kabupaten_dagri IS NOT NULL AND s.kode_kabupaten_dagri <> '' GROUP BY s.kode_kabupaten_dagri
            ) a
            LEFT JOIN	(
                SELECT 
                    s.kode_kabupaten_dagri as id_region,
                    count(distinct(us.npsn)) as jumlah_akun
                FROM
                    jaga.users_sekolah us
                    LEFT JOIN jaga.sekolah s ON s.npsn = us.npsn
                WHERE
                    s.deleted_at IS NULL AND us.deleted_at IS NULL AND kode_kabupaten_dagri IS NOT NULL AND kode_provinsi_dagri = :id_provinsi
                GROUP BY
                    s.kode_kabupaten_dagri
            ) b ON b.id_region = a.id_region
        ", ['id_provinsi' => $idProvinsi]);
    }

    private function mapLaporanSekolahByProvinsi() {
        return $this->rawQuery("
            SELECT a.id_region, COALESCE(jumlah_sekolah, 0) as jumlah_sekolah, COALESCE(jumlah_laporan, 0) as jumlah_laporan FROM (
                SELECT s.kode_provinsi_dagri as id_region, count(*) as jumlah_sekolah FROM jaga.sekolah s WHERE s.deleted_at IS NULL AND s.kode_provinsi_dagri IS NOT NULL AND s.kode_provinsi_dagri <> '' GROUP BY s.kode_provinsi_dagri
            ) a
            LEFT JOIN (
                SELECT s.kode_provinsi_dagri as id_region, sum(jumlah_laporan) as jumlah_laporan FROM (
                    SELECT
                        sekolah_npsn as npsn,
                        CASE 
                            WHEN (count(*) > 0) THEN 1
                            ELSE 0
                        END as jumlah_laporan
                    FROM jaga.pak_isian_sekolah pis
                    GROUP BY sekolah_npsn
                ) a
                LEFT JOIN jaga.sekolah s ON s.npsn = a.npsn
                GROUP BY s.kode_provinsi_dagri
            ) b ON b.id_region = a.id_region
        ");
    }

    private function mapLaporanSekolahByKabupaten($idProvinsi) {
        return $this->rawQuery("
            SELECT a.id_region, COALESCE(jumlah_sekolah, 0) as jumlah_sekolah, COALESCE(jumlah_laporan, 0) as jumlah_laporan FROM (
                SELECT s.kode_kabupaten_dagri as id_region, count(*) as jumlah_sekolah FROM jaga.sekolah s WHERE s.deleted_at IS NULL AND s.kode_provinsi_dagri = :id_provinsi GROUP BY s.kode_kabupaten_dagri
            ) a
            LEFT JOIN (
                SELECT s.kode_kabupaten_dagri as id_region, sum(jumlah_laporan) as jumlah_laporan FROM (
                    SELECT
                        sekolah_npsn as npsn,
                        CASE 
                            WHEN (count(*) > 0) THEN 1
                            ELSE 0
                        END as jumlah_laporan
                    FROM jaga.pak_isian_sekolah pis
                    GROUP BY sekolah_npsn
                ) a
                LEFT JOIN jaga.sekolah s ON s.npsn = a.npsn
                WHERE s.deleted_at IS NULL AND s.kode_provinsi_dagri = :id_provinsi
                GROUP BY s.kode_kabupaten_dagri
            ) b ON b.id_region = a.id_region
        ", ['id_provinsi' => $idProvinsi]);
    }

    private function cleanNamaJenjang($string) {
        $string = str_replace(' ', '', $string);

        return preg_replace('/[^A-Za-z_]/', '_', $string);
    }
    #endregion
}