<?php

class JendelaPencegahanService {
    const LEVEL_NASIONAL = 'nasional';
    const LEVEL_PROVINSI = 'provinsi';
    const LEVEL_KABUPATEN = 'kabupaten';

    public $context;
    public function __construct($context)
    {
        $this->context = $context;
    }

    public function mcpSkor($tahun, $idInstansi = 0) {
        try {
            $template = KorsupgahTemplate::findFirst([
                "conditions" => "tahun = :tahun: and deleted_at is null and is_dana_desa = false",
                "bind" => ["tahun" => $tahun]
            ]);
            if (!empty($template)) {
                $idTempDanaDesa = $template->id_dana_desa;
                $templateId = $template->id;
            } else {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Formulir tidak ditemukan");
            }

            $add_condition = '';
            $instansi_type = KorwilDashboardController::ALL_INSTANSI_TYPE;

            $bind = [
                'templateId' => $templateId,
                'instansi_type' => $instansi_type
            ];

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

            $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)';

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

            $query = '
            SELECT ins.id, ' . $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
                (s.deleted_at IS NULL) AND
                (ins.id_tipe = :instansi_type OR 0 > :instansi_type)
                ' . $add_condition . '
            GROUP BY ins.id
        ';

            $result = $this->context->rawQuery($query, $bind);
            $total = 0;
            foreach ($result as $item) {
                $total += $item["nilai_total"];
            }
            return $total / count($result);
        } catch (\Exception $e) {
            return 0;
        }
    }

    public function elhkpnSkorByBidang($tahun, $idInstansi = 0, $level = self::LEVEL_NASIONAL) {
        try{
            $wilayah = null;
            if ($level != self::LEVEL_NASIONAL) {
                if ($idInstansi > 0) {
                    $instansi = Instansi::findFirst($idInstansi);
                    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');
                }
            }

            $strWhere = 'ElhkpnKepatuhanNew.year = :year: and ElhkpnKepatuhanNew.level = :level:';
            $bind = [
                'year' => $tahun
            ];

            if ($level == self::LEVEL_NASIONAL) {
                $bind['level'] = self::LEVEL_PROVINSI;
            } else {
                $strWhere .= $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.bidang, ElhkpnKepatuhanNew.cnt, ElhkpnKepatuhanNew.cnt_kirim')
                ->leftJoin("MasterWilayah", "MasterWilayah.id = ElhkpnKepatuhanNew.wilayah_id")
                ->where($strWhere)
                ->bind($bind)
                ->execute()
                ->toArray();

            $totalCnt = [];
            $totalCntKirim = [];
            foreach($rows as $row) {
                $bidang = $row["bidang"];
                if (!array_key_exists($bidang, $totalCnt)) {
                    $totalCnt[$bidang] = intval($row["cnt"]);
                } else {
                    $totalCnt[$bidang] += intval($row["cnt"]);
                }

                if (!array_key_exists($bidang, $totalCntKirim)) {
                    $totalCntKirim[$bidang] = intval($row["cnt_kirim"]);
                } else {
                    $totalCntKirim[$bidang] += intval($row["cnt_kirim"]);
                }
            }
            $bidangs = array_keys($totalCnt);
            $result = [];
            foreach ($bidangs as $bidang) {
                $result[$bidang] = ($totalCntKirim[$bidang] * 100) / $totalCnt[$bidang];
            }

            return $result;
        } catch (\Exception $e) {
            return [];
        }
    }

    public function elhkpnPerson($year, $level = self::LEVEL_NASIONAL, $wilayahId = 0) {
        $rows = $this->context->rawQuery("
            select 
                year,
                wilayah_id,
                level,
                provinsi,
                kabupaten,
                wilayah_join,
                bidang,
                nama,
                id_lhkpn,
                tanggal_lapor,
                status_laporan,
                jabatan,
                instansi,
                has_kirim,
                total_harta_tidak_bergerak,
                total_alat_transportasi,
                total_harta_bergerak_lain,
                total_surat_berharga,
                total_kas,
                total_harta_lainnya,
                total_hutang
            from elhkpn_kepatuhan_person
            where year = :year and wilayah_id = :wilayah_id and level = :level
        ", [
            "year" => $year,
            "wilayah_id" => $wilayahId,
            "level" => $level
        ]);
        $res = [];
        foreach ($rows as $row) {
            $item = $row;
            $total_harta_tidak_bergerak = is_numeric($item['total_harta_tidak_bergerak']) ? $item['total_harta_tidak_bergerak'] : 0;
            $total_alat_transportasi = is_numeric($item['total_alat_transportasi']) ? $item['total_alat_transportasi'] : 0;
            $total_harta_bergerak_lain = is_numeric($item['total_harta_bergerak_lain']) ? $item['total_harta_bergerak_lain'] : 0;
            $total_surat_berharga = is_numeric($item['total_surat_berharga']) ? $item['total_surat_berharga'] : 0;
            $total_kas = is_numeric($item['total_kas']) ? $item['total_kas'] : 0;
            $total_harta_lainnya = is_numeric($item['total_harta_lainnya']) ? $item['total_harta_lainnya'] : 0;
            $total_hutang = is_numeric($item['total_hutang']) ? $item['total_hutang'] : 0;
            $item['total_harta'] = $total_harta_tidak_bergerak + $total_alat_transportasi + $total_harta_bergerak_lain + $total_surat_berharga + $total_kas + $total_harta_lainnya - $total_hutang;
            array_push($res, $item);
        }
        return $res;
    }

    public function elhkpnKepatuhan($year, $level = self::LEVEL_NASIONAL, $wilayahId = 0) {
        $viewLevel = $level === self::LEVEL_NASIONAL ? self::LEVEL_PROVINSI : self::LEVEL_KABUPATEN;
        $where = [
            "year = :year",
            "t.level = :level"
        ];
        $bind = [
            "year" => $year,
            "level" => $viewLevel
        ];
        if ($viewLevel !== self::LEVEL_PROVINSI) {
            $col = $level === self::LEVEL_PROVINSI ? 'parent_wilayah_id' : 'wilayah_id';
            $where[] = "t.$col = :wilayah_id";
            $bind["wilayah_id"] = $wilayahId;
        }
        $queryWhere = implode(" and ", $where);
        $query = "SELECT t.wilayah_id, w.code as wilayah_code, (sum(cnt_kirim) * 100) / sum(cnt) as indeks
            FROM jaga.elhkpn_kepatuhan t 
            LEFT JOIN jaga.master_wilayah w ON w.id = t.wilayah_id
            WHERE $queryWhere 
            GROUP BY t.wilayah_id, w.code";
        return $this->context->rawQuery($query, $bind);
    }

    public function gratifikasiJumlahLaporan($tahun, $idInstansi = 0) {
        $grafik = $this->gratifikasiGrafik($tahun, $idInstansi);
        $jumlahLaporan = 0;
        if (array_key_exists("jumlah_laporan", $grafik) && array_key_exists("laporan_gratifikasi", $grafik["jumlah_laporan"])) {
            $jumlahLaporan = $grafik["jumlah_laporan"]["laporan_gratifikasi"];
        }
        return $jumlahLaporan;
    }

    public function gratifikasiGrafik($tahun, $idInstansi) {
        $sigClient = new SigClient($this->context->config["sig_prop"], $this->context);
        $result = [];
        try {
            $kodeProvinsi = $kodeKabKota = 0;
            if($idInstansi > 0) {
                /** @var Instansi $instansi */
                $instansi = Instansi::findFirst(["conditions" => "id = :id:", "bind" => ["id" => $idInstansi]]);
                if ($instansi) {
                    $kodeProvinsi = $instansi->id_provinsi;
                    if ($instansi->id_kota_kabupaten != null) {
                        $kodeKabKota = $instansi->id_kota_kabupaten;
                        $kodeKabKota = substr($kodeKabKota, 0, 2) . "." . substr($kodeKabKota, 2, 2);
                    }
                }
            }

            $sigClient->setLogger($this->context->logger);
            $response = $sigClient->grafik(0, $kodeProvinsi, $kodeKabKota, $tahun);
            if (empty($response)) {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal Menghubungi Service SIG");
            }

            if (array_key_exists('status_code', $response) && (int)$response['status_code'] == 0) {
                $result = $response['data'];
            }
        }
        catch (Exception $e) {

        }

        return $result;
    }

    public function spiSkor($tahun, $idInstansi = 0) {
        try {
            $strWhere = 'SPIHasilKLPD.tahun = :tahun: and SPIHasilKLPD.is_active = true';
            $bind['tahun'] = $tahun;
            $isNasional = false;
            $select = "AVG(SPIHasilKLPD.hasil_spi) as hasil_spi_all";
            if ($idInstansi > 0) {
                $instansi = Instansi::findFirst($idInstansi);
                if (empty($instansi)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi yang dipilih tidak valid');
                }

                $strWhere .= ' and Instansi.id = :id_instansi:';
                $bind['id_instansi'] = $instansi->id;
            } else {
                $isNasional = true;
            }

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

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

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

            return floatval($data["hasil_spi_all"]);
        } catch (Exception $e) {
            return 0;
        }
    }
}