<?php

use function DusanKasan\Knapsack\groupBy;

class JendelaDaerahController extends BaseController
{
    public function provinsi()
    {
        $rows = MasterProvinsi::find();
        return new SimpleResponse($rows);
    }

    public function wilayah()
    {
        $req = $this->request->get();
        $this->validate(
            $req,
            [
                'parent_id' => ['validators' => ['required', 'digit']]
            ]
        );
        $parent_id = $req['parent_id'];
        $rows = MasterWilayah::find([
            'parent_id = :parent_id:',
            'bind' => compact('parent_id')
        ]);
        return new SimpleResponse($rows);
    }

    public function kementerian()
    {
        $rows = MasterKementerian::find();
        return new SimpleResponse($rows);
    }

    public function jejakKasus()
    {
        $req = $this->request;
        $this->validate(
            $req->get(),
            [
                'nama' => ['validators' => []],
                'wilayah_id' => ['validators' => ['digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );

        $wilayah_id = $req->get('wilayah_id', 'int', -1);
        $limit = $req->get('limit', 'int', 10);
        $offset = $req->get('offset', 'int', 0);
        $nama = $req->get('nama', 'string', '');

        $opts = [
            'conditions' => '(:wilayah_id: < 0 OR wilayah_id = :wilayah_id:) AND nama ilike :nama:',
            'bind' => [
                'wilayah_id' => $wilayah_id,
                'nama' => '%' . $nama . '%'
            ]
        ];
        $totalRecord = JejakKasus::count($opts);

        $opts['limit'] = $limit;
        $opts['offset'] = $offset;

        $rows = JejakKasus::find($opts)->toArray();

        foreach ($rows as $i => $row) {
            $rows[$i]['foto_url'] = MediaHelper::getUrl($row['foto'], $this->config, $this->minio);
        }

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

    public function jejakKasusSummary()
    {
        $rows = $this->rawQuery("
        select
          wilayah_id,
          w.code as provinsi_code,
          w.name as provinsi_name,
          t.count
        from (
          select wilayah_id, count(*) as count
          from jejak_kasus
          group by wilayah_id
        ) as t
        left join master_wilayah as w on
          w.id = t.wilayah_id
        ");
        return new SimpleResponse($rows);
    }

    public function jejakKasusUpload()
    {
        $this->validatePasskey();

        // csv is prepared from app/migrations/1.8.6/prepare.py
        $file = $this->request->hasFiles() ?
            $this->request->getUploadedFiles()[0] : null;
        if (!$file) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "File is empty");
        }
        $csv = new ParseCsv\Csv();
        $csv->parse($file->getTempName());
        $cols = $csv->titles;
        $rows = $csv->data;
        $table = (new JejakKasus())->getSource();
        $this->db->begin();
        $this->db->delete($table);
        foreach ($rows as $r) {
            $values = array_map(function ($col) use ($r) {
                return $r[$col];
            }, $cols);
            $this->db->insert($table, $values, $cols);
        }
        $this->db->commit();
        return ['inserted' => count($rows)];
    }

    public function laporanGratifikasi()
    {
        $req = $this->request->get();

        $rows = [];
        if ($req['level'] == 'nasional')
            $rows['laporan'] = GratifikasiLaporan::find();
        else { // provinsi
            $wilayah_id = $req['wilayah_id'];
            $param = [
                'wilayah_id = :wilayah_id:',
                'bind' => compact('wilayah_id')
            ];
            $rows['laporan'] = GratifikasiLaporan::find($param);


            $rows['upg_provinsi'] = GratifikasiUpg::find([
                "conditions" => "level = 'provinsi' and wilayah_id=:wilayah_id:",
                "bind" => [
                    "wilayah_id" => $wilayah_id
                ]
            ]);

            $param = [
                'wilayah_id = :wilayah_id:',
                'bind' => compact('wilayah_id')
            ];

            $rows['upg_kota_kabupaten'] = GratifikasiUpg::find([
                "conditions" => "level = 'kabupaten' and wilayah_code in (select kota_kabupaten_code from MasterKotaKabupaten where provinsi_code in (select wilayah_code from GratifikasiUpg where wilayah_id=:wilayah_id: and level = 'provinsi'))",
                "bind" => [
                    "wilayah_id" => $wilayah_id
                ]
            ]);
        }
        return new SimpleResponse($rows);
    }

    public function gratifikasiUpg()
    {
        $req = $this->request->get();
        $this->validate(
            $req,
            [
                'wilayah_id' => ['validators' => ['required', 'digit']]
            ]
        );
        $wilayah_id = $req['wilayah_id'];
        $rows = GratifikasiUpg::find([
            'wilayah_id = :wilayah_id:',
            'bind' => compact('wilayah_id')
        ]);
        return new SimpleResponse($rows);
    }

    public function gratifikasiUpgUpload()
    {
        $this->validatePasskey();

        // csv is prepared from app/migrations/1.8.3/prepare_upg.py
        $file = $this->request->hasFiles() ?
            $this->request->getUploadedFiles()[0] : null;
        if (!$file) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "File is empty");
        }
        $csv = new ParseCsv\Csv();
        $csv->parse($file->getTempName());
        $cols = $csv->titles;
        $rows = $csv->data;
        $table = (new GratifikasiUpg())->getSource();
        $this->db->begin();
        $this->db->delete($table);
        foreach ($rows as $r) {
            $values = array_map(function ($col) use ($r) {
                return $r[$col];
            }, $cols);
            $this->db->insert($table, $values, $cols);
        }
        $this->db->commit();
        return ['inserted' => count($rows)];
    }

    protected function validatePasskey()
    {
        $params = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                'passkey' => ['validators' => ['required']]
            ]
        );
        if ($params['passkey'] != getenv('CRONJOB_PASSKEY')) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Invalid Passkey');
        }
    }

    public function laporanGratifikasiNew()
    {
        $req = $this->request->get();

        $result = [];
        if ($req['level'] == 'nasional')
            $result['laporan'] = GratifikasiLaporanNew::find();
        else { // provinsi
            $wilayah_id = $req['wilayah_id'];
            $param = [
                'wilayah_id = :wilayah_id:',
                'bind' => compact('wilayah_id')
            ];
            $result['laporan'] = GratifikasiLaporanNew::find($param);
        }
        return new SimpleResponse($result);
    }

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

        $laporanData = GratifikasiLaporanNasional::find()->toArray();

        foreach ($laporanData as $itemLaporan) {
            if (!array_key_exists($itemLaporan['year'], $result)) {
                $result[$itemLaporan['year']] = [];
            }
            $selectedYearData = $result[$itemLaporan['year']];
            if (!array_key_exists($itemLaporan['status_code'], $selectedYearData)) {
                $selectedYearData[$itemLaporan['status_code']] = [
                    'status_name' => $itemLaporan['status_name'],
                    'jumlah' => $itemLaporan['jumlah']
                ];
            }
            $result[$itemLaporan['year']] = $selectedYearData;
        }

        return new SimpleResponse($result);
    }

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

        $laporanData = GratifikasiLaporanInstansi::find()->toArray();

        foreach ($laporanData as $itemLaporan) {
            if (!array_key_exists($itemLaporan['year'], $result)) {
                $result[$itemLaporan['year']] = [];
            }
            $selectedYearData = $result[$itemLaporan['year']];
            if (!array_key_exists($itemLaporan['bidang_code'], $selectedYearData)) {
                $selectedYearData[$itemLaporan['bidang_code']] = [
                    'bidang_name' => $itemLaporan['bidang_name'],
                    'jumlah' => $itemLaporan['jumlah']
                ];
            }
            $result[$itemLaporan['year']] = $selectedYearData;
        }

        return new SimpleResponse($result);
    }

    #region Dashboard Summary
    const LEVEL_NASIONAL = 'nasional';
    const LEVEL_PROVINSI = 'provinsi';
    const LEVEL_KABUPATEN = 'kabupaten';

    public function listInstansi()
    {
        $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']],
                'keyword' => ['validators' => []]
            ]
        );

        $result = [];

        $strWhere = 'id_tipe in ({tipe_instansi:array}) and deleted_at is null';
        $bind['tipe_instansi'] = [1, 2];

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= ' and nama ilike :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $totalCount = Instansi::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $instansis = Instansi::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );

        foreach ($instansis as $instansi) {
            array_push($result, $instansi->toArray());
        }


        return new PaginatedResponse($result, $totalCount, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

    public function dashboardKorwil($templateId)
    {
        $getParams = $this->request->get();
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : 0;

        $idTempDanaDesa = 0;
        if ($idTempDanaDesa == 0) {
            $dataKorsupgahTemplate = KorsupgahTemplate::findFirst($templateId);
            if (!empty($dataKorsupgahTemplate)) {
                $idTempDanaDesa = $dataKorsupgahTemplate->id_dana_desa;
            }
        }

        $params = $this->validate(
            $getParams,
            [
                'id_area_intervensi' => ['validators' => []],
                'id_indikator' => ['validators' => []],
                'id_subindikator' => ['validators' => []],
                'id_instansi' => ['validators' => ['digit']]
            ]
        );

        $add_condition = '';
        $id_area_intervensi = empty($params['id_area_intervensi']) ? -1 : $params['id_area_intervensi'];
        $id_indikator = empty($params['id_indikator']) ? -1 : $params['id_indikator'];
        $id_subindikator = empty($params['id_subindikator']) ? -1 : $params['id_subindikator'];
        $instansi_type = KorwilDashboardController::ALL_INSTANSI_TYPE;

        $bind = [
            'templateId' => $templateId,
            'id_area_intervensi' => $id_area_intervensi,
            'id_indikator' => $id_indikator,
            'id_subindikator' => $id_subindikator,
            'instansi_type' => $instansi_type
        ];

        $instansi = Instansi::findFirst($params['id_instansi']);
        if (!empty($instansi)) {
            if ($instansi->id_tipe == KorwilDashboardController::PROVINSI_INSTANSI_TYPE) {
                $add_condition .= ' AND ins.id_provinsi = :info_instansi';
                $bind['info_instansi'] = $instansi->id_provinsi;
            } else {
                $add_condition .= ' AND ins.id_kota_kabupaten = :info_instansi';
                $bind['info_instansi'] = $instansi->id_kota_kabupaten;
            }
        }

        $bobot_query = '';

        if ((!empty($params['id_subindikator']) || !empty($params['id_indikator']) || !empty($params['id_area_intervensi'])) && $params['id_area_intervensi'] == 8) {
            $add_condition .= ' AND ins.flag_dana_desa = 1';
        }

        if (!empty($params['id_subindikator'])) {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float)';
        } else if (!empty($params['id_indikator'])) {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot / 100)';
        } else if (!empty($params['id_area_intervensi'])) {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000)';
        } else {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * 
                CASE WHEN ins.flag_dana_desa = 0 AND ai.id = 6
                THEN ai.presentase_bobot + aidd.presentase_bobot
                WHEN ins.flag_dana_desa = 0
                THEN ai.presentase_bobot_wo_dana_desa
                ELSE ai.presentase_bobot END
                 / 1000000)';
        }

        $template = KorsupgahTemplate::findById($templateId);

        if (empty($template)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Template tidak ditemukan');
        }

        $qIdTemplate = '';
        if ($idTempDanaDesa > 0) {
            $qIdTemplate = '(s.id_template = :templateId OR s.id_template = :templateIdDanaDesa)';
            $bind['templateIdDanaDesa'] = $idTempDanaDesa;
        } else {
            $qIdTemplate = '(s.id_template = :templateId)';
        }

        try {
            $tahun = $template->getFirst()->tahun ?? 0;
        } catch (\Exception $e) {
            $tahun = 0;
        }


        $query = '
            SELECT
                ins.id,
                ins.nama,
                ins.flag_dana_desa,
                coalesce(riw.id_provinsi, ins.id_provinsi) as id_provinsi,
                coalesce(riw.id_kota_kabupaten, ins.id_kota_kabupaten) as id_kota_kabupaten,
                ' . $bobot_query . ' as nilai_total
            FROM "jaga"."korsupgah_survey" s
                join jaga.korsupgah_template t on t.id = s.id_template
                join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
                join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
                join jaga.korsupgah_indikator i on i.id = si.id_indikator
                join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
                join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
                join jaga.instansi ins on ins.id = s.id_instansi
                left join jaga.rel_instansi_wilayah riw on riw.id_instansi = ins.id and '.$tahun.' >= riw.start_year and '.$tahun.' <= riw.end_year
                JOIN jaga.master_provinsi mprov ON coalesce(riw.id_provinsi, ins.id_provinsi) = mprov.provinsi_code::INTEGER
                join jaga.korsupgah_periode p on p.id = s.id_periode
                left outer join (
                    select distinct on (1, 2) a.id_question as id_sub_indikator, a.id_survey, a.id as answer_id, a.nilai_verifikasi, a.nilai_verifikasi_qa, a.nilai_verifikasi_pic
                    from jaga.korsupgah_answer a
                    where a.status >= 1
                    ORDER BY a.id_question, a.id_survey, a.created_at DESC
                ) answer on answer.id_sub_indikator = tsi.id_sub_indikator AND s.id = answer.id_survey
                join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
            WHERE ' . $qIdTemplate . ' AND
                (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi) AND
                (i.id = :id_indikator OR 0 > :id_indikator) AND
                (si.id = :id_subindikator OR 0 > :id_subindikator) AND
                (s.deleted_at IS NULL) AND
                (ins.id_tipe = :instansi_type OR 0 > :instansi_type)
                ' . $add_condition . '
            GROUP BY ins."id", ins.nama, ins.id_provinsi, ins.id_kota_kabupaten, riw.id_provinsi, riw.id_kota_kabupaten
            ORDER BY nilai_total DESC
        ';

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

        for ($i = 0; $i < count($result); $i++) {
            $result[$i]['rank'] = $i + 1;
        }

        return new CollectionPaginatedResponse($result);
    }

    public function dashboardSummary() {
        $getParams = $this->request->get();
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : 0;
        $getParams['level'] = array_key_exists('level', $getParams) ? $getParams['level'] : JendelaPencegahanService::LEVEL_NASIONAL;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['required','digit']],
                'id_instansi' => ['validators' => ['digit']],
                'level' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => [JendelaPencegahanService::LEVEL_NASIONAL, JendelaPencegahanService::LEVEL_PROVINSI, JendelaPencegahanService::LEVEL_KABUPATEN]
                        ]
                    ]
                ],
            ]
        );
        $tahun = $params["tahun"];
        $idInstansi = $params["id_instansi"];
        $level = $params["level"];
        $service = new JendelaPencegahanService($this);

        $result = [
            "mcp" => $service->mcpSkor($tahun, $idInstansi),
            "elhkpn" => $service->elhkpnSkorByBidang($tahun, $idInstansi, $level),
            "gratifikasi" => $service->gratifikasiJumlahLaporan($tahun, $idInstansi),
            "spi" => $service->spiSkor($tahun, $idInstansi)
        ];

        return new SimpleResponse($result);
    }

    const DB = 'databaseElhkpnMart';
    public function dashboardElhkpn()
    {
        //Initialize Data
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['level'] = array_key_exists('level', $params) ? $params['level'] : 'nasional';
        $params['id_instansi'] = array_key_exists('id_instansi', $params) ? $params['id_instansi'] : 0;

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'level' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => [self::LEVEL_NASIONAL, self::LEVEL_PROVINSI, self::LEVEL_KABUPATEN]
                        ]
                    ]
                ],
                'id_instansi' => ['validators' => ['digit']]
            ]
        );

        $instansi = null;
        $wilayah = null;

        if ($validatedRequest['level'] != self::LEVEL_NASIONAL) {
            if ($validatedRequest['id_instansi'] > 0) {
                $instansi = Instansi::findFirst($validatedRequest['id_instansi']);
                if (empty($instansi)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid');
                }

                $bind = [];
                if ($instansi->id_tipe == 1) { //provinsi
                    $bind['code'] = $instansi->id_provinsi;
                } else {
                    $bind['code'] = $instansi->id_kota_kabupaten;
                }

                $wilayah = MasterWilayah::findFirst(
                    [
                        'code = :code: and active = 1',
                        'bind' => $bind
                    ]
                );

                if (empty($wilayah)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid');
                }
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi harus dipilih');
            }
        }
        //Summary Person
        $persons = ElhkpnKepatuhanPerson::query()
            ->columns('year, wilayah_id, level, provinsi, kabupaten, wilayah_join, bidang, nama, id_lhkpn, tanggal_lapor, status_laporan, jabatan, instansi, has_kirim')
            ->where('year = :year: and level = :level: and wilayah_id = :wilayah_id:')
            ->bind(
                [
                    'year' => $validatedRequest['tahun'],
                    'level' => $validatedRequest['level'],
                    'wilayah_id' => $validatedRequest['level'] == self::LEVEL_NASIONAL ? 0 : $wilayah->id
                ]
            )
            ->execute()
            ->toArray();

        //Summary Map Yearly
        $strWhere = 'ElhkpnKepatuhanNew.year = :year: and ElhkpnKepatuhanNew.level = :level:';
        $bind = [
            'year' => $validatedRequest['tahun']
        ];

        if ($validatedRequest['level'] == self::LEVEL_NASIONAL) {
            $bind['level'] = self::LEVEL_PROVINSI;
        } else {
            $strWhere .= $validatedRequest['level'] == self::LEVEL_PROVINSI ? ' and ElhkpnKepatuhanNew.parent_wilayah_id = :wilayah_id:' : ' and ElhkpnKepatuhanNew.wilayah_id = :wilayah_id:';
            $bind['level'] = self::LEVEL_KABUPATEN;
            $bind['wilayah_id'] = $wilayah->id;
        }

        $rows = ElhkpnKepatuhanNew::query()
            ->columns('ElhkpnKepatuhanNew.year, ElhkpnKepatuhanNew.level, MasterWilayah.code as wilayah_code, ElhkpnKepatuhanNew.provinsi, ElhkpnKepatuhanNew.kabupaten, ElhkpnKepatuhanNew.wilayah_join, ElhkpnKepatuhanNew.bidang, ElhkpnKepatuhanNew.cnt, ElhkpnKepatuhanNew.cnt_kirim')
            ->leftJoin("MasterWilayah", "MasterWilayah.id = ElhkpnKepatuhanNew.wilayah_id")
            ->where($strWhere)
            ->bind($bind)
            ->execute()
            ->toArray();

        //Finalize Result

        return new SimpleResponse([
            'rows' => $rows,
            'persons' => $persons
        ]);
    }

    public function dashboardGratifikasi()
    {
        $params = $this->request->get();
        $params['id_instansi'] = array_key_exists('id_instansi', $params) ? $params['id_instansi'] : 0;
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'id_instansi' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['required', 'digit']]
            ]
        );

        $strWhere = 'year = :tahun:';
        $bind['tahun'] = $validatedRequest['tahun'];

        if ($validatedRequest['id_instansi'] > 0) { //provinsi or kabupaten
            $instansi = Instansi::findFirst($validatedRequest['id_instansi']);
            if (empty($instansi)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid');
            }

            $strWhere .= ' and provinsi_code = :id_provinsi:';
            $bind['id_provinsi'] = $instansi->id_provinsi;
        } elseif($validatedRequest['id_instansi'] == 0) { //nasional

        }
        else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid');
        }

        $result['laporan'] = GratifikasiLaporanNew::find(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        return new SimpleResponse($result);
    }

    public function dashboardSpi()
    {
        //Populate Request
        $params = $this->request->get();
        $params['id_instansi'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : 0;
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'id_instansi' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['required', 'digit']]
            ]
        );

        $select = '';
        $strWhere = 'SPIHasilKLPD.tahun = :tahun: and SPIHasilKLPD.is_active = true';
        $bind['tahun'] = $validatedRequest['tahun'];
        $groupBy = '';
        //Get Level and Wilayah
        $wilayahCode = 0;
        $wilayahName = 'Nasional';
        $isNasional = false;
        if ($validatedRequest['id_instansi'] > 0) {
            $instansi = Instansi::findFirst($validatedRequest['id_instansi']);
            if (empty($instansi)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid');
            }

            $select = 'SPIHasilKLPD.tahun, SPIHasilKLPD.code_klpd, SPIHasilKLPD.nama_klpd, Instansi.nama as wilayah_name';
            // $select .= ", Instansi.nama as wilayah_name";
            $wilayahName = $instansi->nama;
            $strWhere .= ' and Instansi.id = :id_instansi:';
            $bind['id_instansi'] = $instansi->id;

            if ($instansi->id_tipe == 1) { //provinsi
                $select .= ", id_provinsi as wilayah_code, SPIHasilKLPD.hasil_spi as hasil_spi_pemda, 0 as hasil_spi_kl, SPIHasilKLPD.hasil_spi as hasil_spi_all";
                $wilayahCode = $instansi->id_provinsi;
            } elseif ($instansi->id_tipe == 2) {
                $select .= ", id_kota_kabupaten as wilayah_code, SPIHasilKLPD.hasil_spi as hasil_spi_pemda, 0 as hasil_spi_kl, SPIHasilKLPD.hasil_spi as hasil_spi_all";
                $wilayahCode = $instansi->id_kota_kabupaten;
            } elseif ($instansi->id_tipe == 3) {
                $select .= ", Instansi.id as wilayah_code, 0 as hasil_spi_pemda, SPIHasilKLPD.hasil_spi as hasil_spi_kl, SPIHasilKLPD.hasil_spi as hasil_spi_all";
                $wilayah_code = $instansi->id;
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid');
            }
        } else {
            // $select .= ", 'Nasional' as wilayah_name, 0 as wilayah_code";
            $select = "SPIHasilKLPD.tahun, 0 as code_klpd, 'Nasional' as nama_klpd, 'Nasional' as wilayah_name, 0 as wilayah_code, AVG(CASE Instansi.id_tipe WHEN 1 THEN SPIHasilKLPD.hasil_spi WHEN 2 THEN SPIHasilKLPD.hasil_spi ELSE 0 END) as hasil_spi_pemda, AVG(CASE Instansi.id_tipe WHEN 3 THEN SPIHasilKLPD.hasil_spi ELSE 0 END) as hasil_spi_kl, AVG(SPIHasilKLPD.hasil_spi) as hasil_spi_all";
            $groupBy = 'SPIHasilKLPD.tahun';
            $isNasional = true;
        }

        //Get Data
        $query = SPIHasilKLPD::query()
            ->columns($select)
            ->join('Instansi', 'Instansi.id = SPIHasilKLPD.code_klpd')
            ->where($strWhere)
            ->bind($bind);

        if (!empty($groupBy)) {
            $query = $query->groupBy($groupBy);
        }

        $arrData = $query->execute()
            ->toArray();

        $data = [];
        if (count($arrData) > 0) {
            $data = $arrData[0];
            // request 20211208, nilai rataan SPI nasional hardcoded
            if ($isNasional) {
                $hasilSpi = $this->config['hasil_spi_nasional'];
                if (array_key_exists($validatedRequest['tahun'], $hasilSpi)) {
                    $hasilSpiAll = $hasilSpi[$validatedRequest['tahun']];
                    $data["hasil_spi_all"] = $hasilSpiAll;
                }
            }
        }

        //Populate Response
        $rows = [
            [
                'year' => $validatedRequest['tahun'],
                'rows' => $data,
            ]
        ];

        return new SimpleResponse([
            'rows' => $rows
        ]);
    }

    public function dashboardStranas()
    {
        //Populate Request
        $params = $this->request->get();
        $params['id_instansi'] = array_key_exists('id_instansi', $params) ? $params['id_instansi'] : 0;
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'id_instansi' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['required', 'digit']]
            ]
        );

        //Validate Stranas Period
        $idPeriode = 0;
        $period = StranasNewPeriode::findFirst(
            [
                'yearstart = :tahun: and deleted_at is null',
                'bind' => [
                    'tahun' => $validatedRequest['tahun']
                ]
            ]
        );

        if (!empty($period)) {
            $idPeriode = $period->id;
        }

        //Prepare Query and Binding
        $select = 'count(1) as x,
            coalesce(sum(nilai_tw1), 0) nilai_tw1,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2)), 0) nilai_tw2,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3)), 0) nilai_tw3,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4)), 0) nilai_tw4,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5)), 0) nilai_tw5,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6)), 0) nilai_tw6,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7)), 0) nilai_tw7,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8)), 0) nilai_tw8,
            coalesce(sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8)), 0) nilai_max,
            count(1)*100 as nilai_total,
            coalesce((sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8)) / (count(1)*100.0) ) * 100, 0) as persen
            ';

        $strWhere = 'StranasNewMasterTarget.deleted_at is null 
            and StranasNewMasterTarget.id_periode = :id_periode: 
            and StranasNewTarget.deleted_at is null';
        $bind['id_periode'] = $idPeriode;

        $nama_instansi = 'Nasional';
        $kode_instansi = 0;

        if ($validatedRequest['id_instansi'] > 0) { //provinsi or kabupaten
            $instansi = Instansi::findFirst($validatedRequest['id_instansi']);
            if (empty($instansi)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid [1]');
            }

            $strWhere .= ' and StranasNewMasterTarget.id_instansi = :id_instansi:';
            $bind['id_instansi'] = $validatedRequest['id_instansi'];

            $nama_instansi = $instansi->nama;

            if ($instansi->id_tipe == 1) { //provinsi
                $kode_instansi = $instansi->id_provinsi;
            } elseif ($instansi->id_tipe == 2) { //kota-kabupaten
                $kode_instansi = $instansi->id_kota_kabupaten;
            } else { //invalid
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid [2]');
            }
        } elseif ($validatedRequest['id_instansi'] == 0) { //nasional

        } else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid [3]');
        }

        //var_dump($bind);
        //Execute Query
        $data = StranasNewMasterTarget::query()
            ->columns($select)
            ->join('StranasNewTarget', 'StranasNewMasterTarget.id_target = StranasNewTarget.id')
            ->where($strWhere)
            ->bind($bind)
            ->execute()
            ->toArray();

        //Populate Response
        $rows = [
            [
                'nama_instansi' => $nama_instansi,
                'kode_instansi' => $kode_instansi,
                'rows' => $data
            ]
        ];

        return new SimpleResponse([
            'rows' => $rows
        ]);
    }
    #endregion

    public function defaultYear(): SimpleResponse
    {
        return new SimpleResponse(AppConfig::getValueByKode('DEFAULT_YEAR'));
    }
}