<?php

use \Phalcon\Mvc\Model\Migration;

class AlterViewSpiTematikGratifikasiMigration_573 extends Migration
{
    public function up()
    {
        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_spi_tematik_gratifikasi;
            CREATE OR REPLACE VIEW jaga.view_spi_tematik_gratifikasi AS
                 WITH gratifikasicte AS (
                 SELECT spi.tahun,
                    spi.id_instansi,
                    i.id_tipe AS id_tipe_instansi,
                    spi.id_provinsi,
                    spi.id_kota_kabupaten,
                    spi.nama_instansi,
                    NULL::text AS opd,
                    spi.indeks,
                    spi.int_702b_4 AS pbj,
                    (COALESCE(NULLIF(spi.ext_403, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_405b, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_504a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_405a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_505a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_503, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_403b, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.ext_403 IS NOT NULL AND spi.ext_403 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_405b IS NOT NULL AND spi.int_405b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_504a IS NOT NULL AND spi.ext_504a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_405a IS NOT NULL AND spi.int_405a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_505a IS NOT NULL AND spi.ext_505a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_503 IS NOT NULL AND spi.ext_503 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_403b IS NOT NULL AND spi.int_403b <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS pelayanan,
                    (COALESCE(NULLIF(spi.int_602c, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_602a, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.int_602c IS NOT NULL AND spi.int_602c <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_602a IS NOT NULL AND spi.int_602a <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS pengelolaan_sdm,
                    spi.int_402_3 AS perizinan,
                    spi.int_504a AS komitmen_pimpinan,
                    (COALESCE(NULLIF(spi.int_502a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_502b, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.int_502a IS NOT NULL AND spi.int_502a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_502b IS NOT NULL AND spi.int_502b <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS penegakan_aturan,
                    (COALESCE(NULLIF(spi.int_501c_2, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_501c_3, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.int_501c_2 IS NOT NULL AND spi.int_501c_2 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_501c_3 IS NOT NULL AND spi.int_501c_3 <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS sosialisasi,
                    (COALESCE(NULLIF(spi.ext_402, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_401, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_401b, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_401a, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.ext_402 IS NOT NULL AND spi.ext_402 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_401 IS NOT NULL AND spi.ext_401 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_401b IS NOT NULL AND spi.int_401b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_401a IS NOT NULL AND spi.int_401a <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS transparansi
                   FROM jaga.spi_internal_eksternal_detail spi
                     LEFT JOIN jaga.instansi i ON spi.id_instansi = i.id
                ), summarycte AS (
                 SELECT gratifikasicte.tahun,
                    gratifikasicte.id_instansi,
                    gratifikasicte.id_tipe_instansi,
                    gratifikasicte.id_provinsi,
                    gratifikasicte.id_kota_kabupaten,
                    gratifikasicte.nama_instansi,
                    gratifikasicte.opd,
                    gratifikasicte.indeks,
                    (COALESCE(NULLIF(gratifikasicte.pbj, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.pelayanan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.pengelolaan_sdm, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.perizinan, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasicte.pbj IS NOT NULL AND gratifikasicte.pbj <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.pelayanan IS NOT NULL AND gratifikasicte.pelayanan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.pengelolaan_sdm IS NOT NULL AND gratifikasicte.pengelolaan_sdm <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.perizinan IS NOT NULL AND gratifikasicte.perizinan <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS keberadaan,
                    (COALESCE(NULLIF(gratifikasicte.komitmen_pimpinan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.penegakan_aturan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.sosialisasi, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.transparansi, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasicte.komitmen_pimpinan IS NOT NULL AND gratifikasicte.komitmen_pimpinan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.penegakan_aturan IS NOT NULL AND gratifikasicte.penegakan_aturan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.sosialisasi IS NOT NULL AND gratifikasicte.sosialisasi <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.transparansi IS NOT NULL AND gratifikasicte.transparansi <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS upayappg
                   FROM gratifikasicte
                ), intopdcte AS (
                 SELECT spi.id,
                    spi.tahun,
                    spi.id_instansi,
                    spi.id_provinsi,
                    spi.id_kota_kabupaten,
                    spi.kode_wilayah,
                    spi.nama_instansi,
                    spi.opd,
                    spi.n_int,
                    spi.int_401a,
                    spi.int_401b,
                    spi.int_402_1,
                    spi.int_402_2,
                    spi.int_402_3,
                    spi.int_402_4,
                    spi.int_402_5,
                    spi.int_402_6,
                    spi.int_403a,
                    spi.int_403b,
                    spi.int_404a,
                    spi.int_404b,
                    spi.int_405a,
                    spi.int_405b,
                    spi.int_501c_1,
                    spi.int_501c_2,
                    spi.int_501c_3,
                    spi.int_501c_4,
                    spi.int_501c_5,
                    spi.int_502a,
                    spi.int_502b,
                    spi.int_503c,
                    spi.int_504a,
                    spi.int_504b,
                    spi.int_601a_1,
                    spi.int_601a_2,
                    spi.int_601a_3,
                    spi.int_601b,
                    spi.int_602a,
                    spi.int_602c,
                    spi.int_701,
                    spi.int_702a,
                    spi.int_702b_1,
                    spi.int_702b_2,
                    spi.int_702b_3,
                    spi.int_702b_4,
                    spi.int_702b_5,
                    spi.int_703a,
                    spi.int_703b,
                    spi.int_705a,
                    spi.int_705b,
                    spi.int_706a,
                    spi.int_706b,
                    spi.transparansi,
                    spi.sosialisasi_antikorupsi,
                    spi.trading_in_influence,
                    spi.pengelolaan_sdm,
                    spi.pengelolaan_pbj,
                    spi.pengelolaan_anggaran,
                    spi.integritas_tugas,
                    spi.indeks_internal,
                    spi.id_unit_kerja,
                    spi.nama_unit_kerja,
                    spi.int_501b,
                    i.id_tipe AS id_tipe_instansi
                   FROM jaga.spi_internal_opd_detail spi
                     LEFT JOIN jaga.instansi i ON spi.id_instansi = i.id
                  WHERE spi.id_unit_kerja IS NULL
                ), extopdcte AS (
                 SELECT spi.id,
                    spi.tahun,
                    spi.id_instansi,
                    spi.id_provinsi,
                    spi.id_kota_kabupaten,
                    spi.kode_wilayah,
                    spi.nama_instansi,
                    spi.opd,
                    spi.n_ext,
                    spi.ext_401,
                    spi.ext_402,
                    spi.ext_403,
                    spi.ext_404,
                    spi.ext_405a,
                    spi.ext_406,
                    spi.ext_407a,
                    spi.ext_501,
                    spi.ext_502,
                    spi.ext_503,
                    spi.ext_504a,
                    spi.ext_505a,
                    spi.upaya_pencegah_korupsi,
                    spi.transparansi_keadilan_layanan,
                    spi.integritas_pegawai,
                    spi.indeks_eksternal,
                    spi.id_unit_kerja,
                    spi.nama_unit_kerja,
                    spi.ext_405b,
                    spi.ext_407b,
                    spi.ext_407c,
                    i.id_tipe AS id_tipe_instansi
                   FROM jaga.spi_external_opd_detail spi
                     LEFT JOIN jaga.instansi i ON spi.id_instansi = i.id
                  WHERE spi.id_unit_kerja IS NULL
                ), opdcte AS (
                 SELECT si.tahun AS tahun_int,
                    si.id_instansi AS id_instansi_int,
                    si.id_tipe_instansi AS id_tipe_instansi_int,
                    si.id_provinsi AS id_provinsi_int,
                    si.id_kota_kabupaten AS id_kota_kabupaten_int,
                    si.nama_instansi AS nama_instansi_int,
                    si.opd AS opd_int,
                    si.n_int,
                    si.int_401a,
                    si.int_401b,
                    si.int_402_1,
                    si.int_402_2,
                    si.int_402_3,
                    si.int_402_4,
                    si.int_402_5,
                    si.int_402_6,
                    si.int_403a,
                    si.int_403b,
                    si.int_404a,
                    si.int_404b,
                    si.int_405a,
                    si.int_405b,
                    si.int_501c_1,
                    si.int_501c_2,
                    si.int_501c_3,
                    si.int_501c_4,
                    si.int_501c_5,
                    si.int_502a,
                    si.int_502b,
                    si.int_503c,
                    si.int_504a,
                    si.int_504b,
                    si.int_601a_1,
                    si.int_601a_2,
                    si.int_601a_3,
                    si.int_601b,
                    si.int_602a,
                    si.int_602c,
                    si.int_701,
                    si.int_702a,
                    si.int_702b_1,
                    si.int_702b_2,
                    si.int_702b_3,
                    si.int_702b_4,
                    si.int_702b_5,
                    si.int_703a,
                    si.int_703b,
                    si.int_705a,
                    si.int_705b,
                    si.int_706a,
                    si.int_706b,
                    si.int_501b,
                    si.id_unit_kerja AS id_unit_kerja_int,
                    si.nama_unit_kerja AS nama_unit_kerja_int,
                    se.tahun AS tahun_ext,
                    se.id_instansi AS id_instansi_ext,
                    se.id_tipe_instansi AS id_tipe_instansi_ext,
                    se.id_provinsi AS id_provinsi_ext,
                    se.id_kota_kabupaten AS id_kota_kabupaten_ext,
                    se.nama_instansi AS nama_instansi_ext,
                    se.opd AS opd_ext,
                    se.n_ext,
                    se.ext_401,
                    se.ext_402,
                    se.ext_403,
                    se.ext_404,
                    se.ext_405a,
                    se.ext_406,
                    se.ext_407a,
                    se.ext_501,
                    se.ext_502,
                    se.ext_503,
                    se.ext_504a,
                    se.ext_505a,
                    se.id_unit_kerja AS id_unit_kerja_ext,
                    se.nama_unit_kerja AS nama_unit_kerja_ext,
                    se.ext_405b,
                    se.ext_407b,
                    se.ext_407c
                   FROM intopdcte si
                     FULL JOIN extopdcte se ON si.id_instansi = se.id_instansi AND si.opd::text = se.opd::text
                ), gratifikasiopdcte AS (
                 SELECT DISTINCT
                        CASE
                            WHEN opdcte.tahun_int IS NULL THEN opdcte.tahun_ext
                            ELSE opdcte.tahun_int
                        END AS tahun,
                        CASE
                            WHEN opdcte.id_instansi_int IS NULL THEN opdcte.id_instansi_ext
                            ELSE opdcte.id_instansi_int
                        END AS id_instansi,
                        CASE
                            WHEN opdcte.id_tipe_instansi_int IS NULL THEN opdcte.id_tipe_instansi_ext
                            ELSE opdcte.id_tipe_instansi_int
                        END AS id_tipe_instansi,
                        CASE
                            WHEN opdcte.id_provinsi_int IS NULL THEN opdcte.id_provinsi_ext
                            ELSE opdcte.id_provinsi_int
                        END AS id_provinsi,
                        CASE
                            WHEN opdcte.id_kota_kabupaten_int IS NULL THEN opdcte.id_kota_kabupaten_ext
                            ELSE opdcte.id_kota_kabupaten_int
                        END AS id_kota_kabupaten,
                        CASE
                            WHEN opdcte.nama_instansi_int IS NULL THEN opdcte.nama_instansi_ext
                            ELSE opdcte.nama_instansi_int
                        END AS nama_instansi,
                        CASE
                            WHEN opdcte.opd_int IS NULL THEN opdcte.opd_ext
                            ELSE opdcte.opd_int
                        END AS opd,
                    opdcte.int_702b_4 AS pbj,
                    (COALESCE(NULLIF(opdcte.ext_403, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_405b, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_504a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_405a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_505a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_503, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_403b, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN opdcte.ext_403 IS NOT NULL AND opdcte.ext_403 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_405b IS NOT NULL AND opdcte.int_405b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_504a IS NOT NULL AND opdcte.ext_504a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_405a IS NOT NULL AND opdcte.int_405a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_505a IS NOT NULL AND opdcte.ext_505a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_503 IS NOT NULL AND opdcte.ext_503 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_403b IS NOT NULL AND opdcte.int_403b <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS pelayanan,
                        CASE
                            WHEN (
                            CASE
                                WHEN opdcte.int_602c IS NOT NULL AND opdcte.int_602c <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_602a IS NOT NULL AND opdcte.int_602a <> 0::numeric THEN 1
                                ELSE 0
                            END) <> 0 THEN (COALESCE(NULLIF(opdcte.int_602c, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_602a, 0::numeric), 0::numeric)) / (
                            CASE
                                WHEN opdcte.int_602c IS NOT NULL AND opdcte.int_602c <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_602a IS NOT NULL AND opdcte.int_602a <> 0::numeric THEN 1
                                ELSE 0
                            END)::numeric
                            ELSE NULL::numeric
                        END AS pengelolaan_sdm,
                    opdcte.int_402_3 AS perizinan,
                    opdcte.int_504a AS komitmen_pimpinan,
                        CASE
                            WHEN (
                            CASE
                                WHEN opdcte.int_502a IS NOT NULL AND opdcte.int_502a <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_502b IS NOT NULL AND opdcte.int_502b <> 0::numeric THEN 1
                                ELSE 0
                            END) <> 0 THEN (COALESCE(NULLIF(opdcte.int_502a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_502b, 0::numeric), 0::numeric)) / (
                            CASE
                                WHEN opdcte.int_502a IS NOT NULL AND opdcte.int_502a <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_502b IS NOT NULL AND opdcte.int_502b <> 0::numeric THEN 1
                                ELSE 0
                            END)::numeric
                            ELSE NULL::numeric
                        END AS penegakan_aturan,
                        CASE
                            WHEN (
                            CASE
                                WHEN opdcte.int_501c_2 IS NOT NULL AND opdcte.int_501c_2 <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_501c_3 IS NOT NULL AND opdcte.int_501c_3 <> 0::numeric THEN 1
                                ELSE 0
                            END) <> 0 THEN (COALESCE(NULLIF(opdcte.int_501c_2, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_501c_3, 0::numeric), 0::numeric)) / (
                            CASE
                                WHEN opdcte.int_501c_2 IS NOT NULL AND opdcte.int_501c_2 <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_501c_3 IS NOT NULL AND opdcte.int_501c_3 <> 0::numeric THEN 1
                                ELSE 0
                            END)::numeric
                            ELSE NULL::numeric
                        END AS sosialisasi,
                    (COALESCE(NULLIF(opdcte.ext_402, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_401, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_401b, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_401a, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN opdcte.ext_402 IS NOT NULL AND opdcte.ext_402 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_401 IS NOT NULL AND opdcte.ext_401 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_401b IS NOT NULL AND opdcte.int_401b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_401a IS NOT NULL AND opdcte.int_401a <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS transparansi
                   FROM opdcte
                ), summaryopdcte AS (
                 SELECT gratifikasiopdcte.tahun,
                    gratifikasiopdcte.id_instansi,
                    gratifikasiopdcte.id_tipe_instansi,
                    gratifikasiopdcte.id_provinsi,
                    gratifikasiopdcte.id_kota_kabupaten,
                    gratifikasiopdcte.nama_instansi,
                    gratifikasiopdcte.opd,
                    (COALESCE(NULLIF(gratifikasiopdcte.pbj, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.pelayanan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.pengelolaan_sdm, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.perizinan, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasiopdcte.pbj IS NOT NULL AND gratifikasiopdcte.pbj <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.pelayanan IS NOT NULL AND gratifikasiopdcte.pelayanan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.pengelolaan_sdm IS NOT NULL AND gratifikasiopdcte.pengelolaan_sdm <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.perizinan IS NOT NULL AND gratifikasiopdcte.perizinan <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS keberadaan,
                    (COALESCE(NULLIF(gratifikasiopdcte.komitmen_pimpinan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.penegakan_aturan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.sosialisasi, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.transparansi, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasiopdcte.komitmen_pimpinan IS NOT NULL AND gratifikasiopdcte.komitmen_pimpinan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.penegakan_aturan IS NOT NULL AND gratifikasiopdcte.penegakan_aturan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.sosialisasi IS NOT NULL AND gratifikasiopdcte.sosialisasi <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.transparansi IS NOT NULL AND gratifikasiopdcte.transparansi <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS upayappg
                   FROM gratifikasiopdcte
                )
         SELECT summarycte.tahun,
            summarycte.id_instansi,
            summarycte.id_tipe_instansi,
            summarycte.id_provinsi,
            summarycte.id_kota_kabupaten,
            summarycte.nama_instansi,
            summarycte.opd,
                CASE
                    WHEN (
                    CASE
                        WHEN summarycte.keberadaan IS NOT NULL AND summarycte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summarycte.upayappg IS NOT NULL AND summarycte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END) > 0 THEN (COALESCE(NULLIF(summarycte.keberadaan, 0::numeric), 0::numeric) + COALESCE(NULLIF(summarycte.upayappg, 0::numeric), 0::numeric)) / (
                    CASE
                        WHEN summarycte.keberadaan IS NOT NULL AND summarycte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summarycte.upayappg IS NOT NULL AND summarycte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END)::numeric
                    ELSE NULL::numeric
                END AS indeks_gratif,
            'INSTANSI'::text AS src,
            sig.id_instansi_gol
           FROM summarycte LEFT JOIN sig_instansi sig ON sig.id_instansi_jaga = summarycte.id_instansi
        UNION
         SELECT summaryopdcte.tahun,
            summaryopdcte.id_instansi,
            summaryopdcte.id_tipe_instansi,
            summaryopdcte.id_provinsi,
            summaryopdcte.id_kota_kabupaten,
            summaryopdcte.nama_instansi,
            summaryopdcte.opd,
                CASE
                    WHEN (
                    CASE
                        WHEN summaryopdcte.keberadaan IS NOT NULL AND summaryopdcte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summaryopdcte.upayappg IS NOT NULL AND summaryopdcte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END) > 0 THEN (COALESCE(NULLIF(summaryopdcte.keberadaan, 0::numeric), 0::numeric) + COALESCE(NULLIF(summaryopdcte.upayappg, 0::numeric), 0::numeric)) / (
                    CASE
                        WHEN summaryopdcte.keberadaan IS NOT NULL AND summaryopdcte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summaryopdcte.upayappg IS NOT NULL AND summaryopdcte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END)::numeric
                    ELSE NULL::numeric
                END AS indeks_gratif,
            'OPD'::text AS src,
            sig.id_instansi_gol
           FROM summaryopdcte LEFT JOIN sig_instansi sig ON sig.id_instansi_jaga = summaryopdcte.id_instansi
        ");
        self::$connection->execute("
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1533, 'PT Aviasi Pariwisata Indonesia');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1534, 'Badan Pengelola Tabungan Perumahan Rakyat');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1535, 'PT. BANK JABAR BANTEN SYARIAH');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1537, 'PT Integrasi Aviasi Solusi');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1539, 'Badan Karantina Indonesia');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1541, 'Universitas Islam Negeri Walisongo Semarang');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1542, 'Universitas Brawijaya');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1543, 'Rumah Sakit Akademik Universitas Gadjah Mada');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1547, 'Rumah Sakit Akademik UGM');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1548, 'Kementerian Haji dan Umroh');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1552, 'Kementerian Koordinator Bidang Infrastruktur dan Pembangunan Kewilayahan');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1564, 'Kementerian Koordinator Bidang Pemberdayaan Masyarakat');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1565, 'Kementerian Koordinator Bidang Pangan');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1566, 'Kementerian Imigrasi dan Pemasyarakatan');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1567, 'Universitas Negeri Yogyakarta');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1568, 'Pemerintah Daerah Kabupaten Mimika');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1570, 'Kementerian Kebudayaan');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1572, 'KEMENTERIAN PERUMAHAN DAN KAWASAN PERMUKIMAN');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1573, 'KEMENTERIAN PEKERJAAN UMUM');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1575, 'KEMENTERIAN PENDIDIKAN TINGGI, SAINS, DAN TEKNOLOGI');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1576, 'KEMENTERIAN PENDIDIKAN DASAR DAN MENENGAH');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1577, 'KEMENTERIAN KEBUDAYAAN');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1578, 'Kementerian Koordinator Bidang Politik dan Keamanan');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1579, 'Kementerian Koordinator Bidang Hukum, Hak Asasi Manusia, Imigrasi, dan Pemasyarakatan');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1580, 'Kementerian Hukum');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1581, 'Kementerian Hak Asasi Manusia');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1582, 'Kementerian Desa dan Pembangunan Daerah Tertinggal');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1583, 'Kementerian Transmigrasi');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1584, 'Kementerian Kehutanan');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1585, 'Kementerian Lingkungan Hidup/Badan Pengendalian Lingkungan Hidup');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1586, 'Kementerian Koperasi');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1587, 'Kementerian Usaha Mikro, Kecil, dan Menengah');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1588, 'Kementerian Pariwisata');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1589, 'Kementerian Ekonomi Kreatif/Badan Ekonomi Kreatif');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1590, 'Badan Penyelenggara Jaminan Produk Halal');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1591, 'PT AIR BERSIH JATIM (PERSERODA)');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1592, 'Badan Gizi Nasional');
            INSERT INTO jaga.sig_instansi (id_instansi_gol, nama_instansi_gol) VALUES (1593, 'PT Banten Global Development');

            UPDATE jaga.sig_instansi s
            SET id_instansi_jaga = i.id
            FROM jaga.instansi i
            WHERE s.id_instansi_jaga IS NULL
              AND LOWER(TRIM(s.nama_instansi_gol)) = LOWER(TRIM(i.nama));

            UPDATE jaga.sig_instansi SET id_instansi_jaga = 20478 WHERE id_instansi_gol = 1578;
            UPDATE jaga.sig_instansi SET id_instansi_jaga = 343 WHERE id_instansi_gol = 1568;
            UPDATE jaga.sig_instansi SET id_instansi_jaga = 20480 WHERE id_instansi_gol = 1552;
            UPDATE jaga.sig_instansi SET id_instansi_jaga = 20477 WHERE id_instansi_gol = 1534;
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_spi_tematik_gratifikasi;
            CREATE OR REPLACE VIEW jaga.view_spi_tematik_gratifikasi AS
                 WITH gratifikasicte AS (
                 SELECT spi.tahun,
                    spi.id_instansi,
                    i.id_tipe AS id_tipe_instansi,
                    spi.id_provinsi,
                    spi.id_kota_kabupaten,
                    spi.nama_instansi,
                    NULL::text AS opd,
                    spi.indeks,
                    spi.int_702b_4 AS pbj,
                    (COALESCE(NULLIF(spi.ext_403, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_405b, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_504a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_405a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_505a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_503, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_403b, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.ext_403 IS NOT NULL AND spi.ext_403 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_405b IS NOT NULL AND spi.int_405b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_504a IS NOT NULL AND spi.ext_504a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_405a IS NOT NULL AND spi.int_405a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_505a IS NOT NULL AND spi.ext_505a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_503 IS NOT NULL AND spi.ext_503 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_403b IS NOT NULL AND spi.int_403b <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS pelayanan,
                    (COALESCE(NULLIF(spi.int_602c, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_602a, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.int_602c IS NOT NULL AND spi.int_602c <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_602a IS NOT NULL AND spi.int_602a <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS pengelolaan_sdm,
                    spi.int_402_3 AS perizinan,
                    spi.int_504a AS komitmen_pimpinan,
                    (COALESCE(NULLIF(spi.int_502a, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_502b, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.int_502a IS NOT NULL AND spi.int_502a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_502b IS NOT NULL AND spi.int_502b <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS penegakan_aturan,
                    (COALESCE(NULLIF(spi.int_501c_2, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_501c_3, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.int_501c_2 IS NOT NULL AND spi.int_501c_2 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_501c_3 IS NOT NULL AND spi.int_501c_3 <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS sosialisasi,
                    (COALESCE(NULLIF(spi.ext_402, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.ext_401, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_401b, 0::numeric), 0::numeric) + COALESCE(NULLIF(spi.int_401a, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN spi.ext_402 IS NOT NULL AND spi.ext_402 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.ext_401 IS NOT NULL AND spi.ext_401 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_401b IS NOT NULL AND spi.int_401b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN spi.int_401a IS NOT NULL AND spi.int_401a <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS transparansi
                   FROM jaga.spi_internal_eksternal_detail spi
                     LEFT JOIN jaga.instansi i ON spi.id_instansi = i.id
                ), summarycte AS (
                 SELECT gratifikasicte.tahun,
                    gratifikasicte.id_instansi,
                    gratifikasicte.id_tipe_instansi,
                    gratifikasicte.id_provinsi,
                    gratifikasicte.id_kota_kabupaten,
                    gratifikasicte.nama_instansi,
                    gratifikasicte.opd,
                    gratifikasicte.indeks,
                    (COALESCE(NULLIF(gratifikasicte.pbj, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.pelayanan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.pengelolaan_sdm, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.perizinan, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasicte.pbj IS NOT NULL AND gratifikasicte.pbj <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.pelayanan IS NOT NULL AND gratifikasicte.pelayanan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.pengelolaan_sdm IS NOT NULL AND gratifikasicte.pengelolaan_sdm <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.perizinan IS NOT NULL AND gratifikasicte.perizinan <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS keberadaan,
                    (COALESCE(NULLIF(gratifikasicte.komitmen_pimpinan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.penegakan_aturan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.sosialisasi, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasicte.transparansi, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasicte.komitmen_pimpinan IS NOT NULL AND gratifikasicte.komitmen_pimpinan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.penegakan_aturan IS NOT NULL AND gratifikasicte.penegakan_aturan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.sosialisasi IS NOT NULL AND gratifikasicte.sosialisasi <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasicte.transparansi IS NOT NULL AND gratifikasicte.transparansi <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS upayappg
                   FROM gratifikasicte
                ), intopdcte AS (
                 SELECT spi.id,
                    spi.tahun,
                    spi.id_instansi,
                    spi.id_provinsi,
                    spi.id_kota_kabupaten,
                    spi.kode_wilayah,
                    spi.nama_instansi,
                    spi.opd,
                    spi.n_int,
                    spi.int_401a,
                    spi.int_401b,
                    spi.int_402_1,
                    spi.int_402_2,
                    spi.int_402_3,
                    spi.int_402_4,
                    spi.int_402_5,
                    spi.int_402_6,
                    spi.int_403a,
                    spi.int_403b,
                    spi.int_404a,
                    spi.int_404b,
                    spi.int_405a,
                    spi.int_405b,
                    spi.int_501c_1,
                    spi.int_501c_2,
                    spi.int_501c_3,
                    spi.int_501c_4,
                    spi.int_501c_5,
                    spi.int_502a,
                    spi.int_502b,
                    spi.int_503c,
                    spi.int_504a,
                    spi.int_504b,
                    spi.int_601a_1,
                    spi.int_601a_2,
                    spi.int_601a_3,
                    spi.int_601b,
                    spi.int_602a,
                    spi.int_602c,
                    spi.int_701,
                    spi.int_702a,
                    spi.int_702b_1,
                    spi.int_702b_2,
                    spi.int_702b_3,
                    spi.int_702b_4,
                    spi.int_702b_5,
                    spi.int_703a,
                    spi.int_703b,
                    spi.int_705a,
                    spi.int_705b,
                    spi.int_706a,
                    spi.int_706b,
                    spi.transparansi,
                    spi.sosialisasi_antikorupsi,
                    spi.trading_in_influence,
                    spi.pengelolaan_sdm,
                    spi.pengelolaan_pbj,
                    spi.pengelolaan_anggaran,
                    spi.integritas_tugas,
                    spi.indeks_internal,
                    spi.id_unit_kerja,
                    spi.nama_unit_kerja,
                    spi.int_501b,
                    i.id_tipe AS id_tipe_instansi
                   FROM jaga.spi_internal_opd_detail spi
                     LEFT JOIN jaga.instansi i ON spi.id_instansi = i.id
                  WHERE spi.id_unit_kerja IS NULL
                ), extopdcte AS (
                 SELECT spi.id,
                    spi.tahun,
                    spi.id_instansi,
                    spi.id_provinsi,
                    spi.id_kota_kabupaten,
                    spi.kode_wilayah,
                    spi.nama_instansi,
                    spi.opd,
                    spi.n_ext,
                    spi.ext_401,
                    spi.ext_402,
                    spi.ext_403,
                    spi.ext_404,
                    spi.ext_405a,
                    spi.ext_406,
                    spi.ext_407a,
                    spi.ext_501,
                    spi.ext_502,
                    spi.ext_503,
                    spi.ext_504a,
                    spi.ext_505a,
                    spi.upaya_pencegah_korupsi,
                    spi.transparansi_keadilan_layanan,
                    spi.integritas_pegawai,
                    spi.indeks_eksternal,
                    spi.id_unit_kerja,
                    spi.nama_unit_kerja,
                    spi.ext_405b,
                    spi.ext_407b,
                    spi.ext_407c,
                    i.id_tipe AS id_tipe_instansi
                   FROM jaga.spi_external_opd_detail spi
                     LEFT JOIN jaga.instansi i ON spi.id_instansi = i.id
                  WHERE spi.id_unit_kerja IS NULL
                ), opdcte AS (
                 SELECT si.tahun AS tahun_int,
                    si.id_instansi AS id_instansi_int,
                    si.id_tipe_instansi AS id_tipe_instansi_int,
                    si.id_provinsi AS id_provinsi_int,
                    si.id_kota_kabupaten AS id_kota_kabupaten_int,
                    si.nama_instansi AS nama_instansi_int,
                    si.opd AS opd_int,
                    si.n_int,
                    si.int_401a,
                    si.int_401b,
                    si.int_402_1,
                    si.int_402_2,
                    si.int_402_3,
                    si.int_402_4,
                    si.int_402_5,
                    si.int_402_6,
                    si.int_403a,
                    si.int_403b,
                    si.int_404a,
                    si.int_404b,
                    si.int_405a,
                    si.int_405b,
                    si.int_501c_1,
                    si.int_501c_2,
                    si.int_501c_3,
                    si.int_501c_4,
                    si.int_501c_5,
                    si.int_502a,
                    si.int_502b,
                    si.int_503c,
                    si.int_504a,
                    si.int_504b,
                    si.int_601a_1,
                    si.int_601a_2,
                    si.int_601a_3,
                    si.int_601b,
                    si.int_602a,
                    si.int_602c,
                    si.int_701,
                    si.int_702a,
                    si.int_702b_1,
                    si.int_702b_2,
                    si.int_702b_3,
                    si.int_702b_4,
                    si.int_702b_5,
                    si.int_703a,
                    si.int_703b,
                    si.int_705a,
                    si.int_705b,
                    si.int_706a,
                    si.int_706b,
                    si.int_501b,
                    si.id_unit_kerja AS id_unit_kerja_int,
                    si.nama_unit_kerja AS nama_unit_kerja_int,
                    se.tahun AS tahun_ext,
                    se.id_instansi AS id_instansi_ext,
                    se.id_tipe_instansi AS id_tipe_instansi_ext,
                    se.id_provinsi AS id_provinsi_ext,
                    se.id_kota_kabupaten AS id_kota_kabupaten_ext,
                    se.nama_instansi AS nama_instansi_ext,
                    se.opd AS opd_ext,
                    se.n_ext,
                    se.ext_401,
                    se.ext_402,
                    se.ext_403,
                    se.ext_404,
                    se.ext_405a,
                    se.ext_406,
                    se.ext_407a,
                    se.ext_501,
                    se.ext_502,
                    se.ext_503,
                    se.ext_504a,
                    se.ext_505a,
                    se.id_unit_kerja AS id_unit_kerja_ext,
                    se.nama_unit_kerja AS nama_unit_kerja_ext,
                    se.ext_405b,
                    se.ext_407b,
                    se.ext_407c
                   FROM intopdcte si
                     FULL JOIN extopdcte se ON si.id_instansi = se.id_instansi AND si.opd::text = se.opd::text
                ), gratifikasiopdcte AS (
                 SELECT DISTINCT
                        CASE
                            WHEN opdcte.tahun_int IS NULL THEN opdcte.tahun_ext
                            ELSE opdcte.tahun_int
                        END AS tahun,
                        CASE
                            WHEN opdcte.id_instansi_int IS NULL THEN opdcte.id_instansi_ext
                            ELSE opdcte.id_instansi_int
                        END AS id_instansi,
                        CASE
                            WHEN opdcte.id_tipe_instansi_int IS NULL THEN opdcte.id_tipe_instansi_ext
                            ELSE opdcte.id_tipe_instansi_int
                        END AS id_tipe_instansi,
                        CASE
                            WHEN opdcte.id_provinsi_int IS NULL THEN opdcte.id_provinsi_ext
                            ELSE opdcte.id_provinsi_int
                        END AS id_provinsi,
                        CASE
                            WHEN opdcte.id_kota_kabupaten_int IS NULL THEN opdcte.id_kota_kabupaten_ext
                            ELSE opdcte.id_kota_kabupaten_int
                        END AS id_kota_kabupaten,
                        CASE
                            WHEN opdcte.nama_instansi_int IS NULL THEN opdcte.nama_instansi_ext
                            ELSE opdcte.nama_instansi_int
                        END AS nama_instansi,
                        CASE
                            WHEN opdcte.opd_int IS NULL THEN opdcte.opd_ext
                            ELSE opdcte.opd_int
                        END AS opd,
                    opdcte.int_702b_4 AS pbj,
                    (COALESCE(NULLIF(opdcte.ext_403, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_405b, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_504a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_405a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_505a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_503, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_403b, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN opdcte.ext_403 IS NOT NULL AND opdcte.ext_403 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_405b IS NOT NULL AND opdcte.int_405b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_504a IS NOT NULL AND opdcte.ext_504a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_405a IS NOT NULL AND opdcte.int_405a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_505a IS NOT NULL AND opdcte.ext_505a <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_503 IS NOT NULL AND opdcte.ext_503 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_403b IS NOT NULL AND opdcte.int_403b <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS pelayanan,
                        CASE
                            WHEN (
                            CASE
                                WHEN opdcte.int_602c IS NOT NULL AND opdcte.int_602c <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_602a IS NOT NULL AND opdcte.int_602a <> 0::numeric THEN 1
                                ELSE 0
                            END) <> 0 THEN (COALESCE(NULLIF(opdcte.int_602c, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_602a, 0::numeric), 0::numeric)) / (
                            CASE
                                WHEN opdcte.int_602c IS NOT NULL AND opdcte.int_602c <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_602a IS NOT NULL AND opdcte.int_602a <> 0::numeric THEN 1
                                ELSE 0
                            END)::numeric
                            ELSE NULL::numeric
                        END AS pengelolaan_sdm,
                    opdcte.int_402_3 AS perizinan,
                    opdcte.int_504a AS komitmen_pimpinan,
                        CASE
                            WHEN (
                            CASE
                                WHEN opdcte.int_502a IS NOT NULL AND opdcte.int_502a <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_502b IS NOT NULL AND opdcte.int_502b <> 0::numeric THEN 1
                                ELSE 0
                            END) <> 0 THEN (COALESCE(NULLIF(opdcte.int_502a, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_502b, 0::numeric), 0::numeric)) / (
                            CASE
                                WHEN opdcte.int_502a IS NOT NULL AND opdcte.int_502a <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_502b IS NOT NULL AND opdcte.int_502b <> 0::numeric THEN 1
                                ELSE 0
                            END)::numeric
                            ELSE NULL::numeric
                        END AS penegakan_aturan,
                        CASE
                            WHEN (
                            CASE
                                WHEN opdcte.int_501c_2 IS NOT NULL AND opdcte.int_501c_2 <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_501c_3 IS NOT NULL AND opdcte.int_501c_3 <> 0::numeric THEN 1
                                ELSE 0
                            END) <> 0 THEN (COALESCE(NULLIF(opdcte.int_501c_2, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_501c_3, 0::numeric), 0::numeric)) / (
                            CASE
                                WHEN opdcte.int_501c_2 IS NOT NULL AND opdcte.int_501c_2 <> 0::numeric THEN 1
                                ELSE 0
                            END +
                            CASE
                                WHEN opdcte.int_501c_3 IS NOT NULL AND opdcte.int_501c_3 <> 0::numeric THEN 1
                                ELSE 0
                            END)::numeric
                            ELSE NULL::numeric
                        END AS sosialisasi,
                    (COALESCE(NULLIF(opdcte.ext_402, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.ext_401, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_401b, 0::numeric), 0::numeric) + COALESCE(NULLIF(opdcte.int_401a, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN opdcte.ext_402 IS NOT NULL AND opdcte.ext_402 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.ext_401 IS NOT NULL AND opdcte.ext_401 <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_401b IS NOT NULL AND opdcte.int_401b <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN opdcte.int_401a IS NOT NULL AND opdcte.int_401a <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS transparansi
                   FROM opdcte
                ), summaryopdcte AS (
                 SELECT gratifikasiopdcte.tahun,
                    gratifikasiopdcte.id_instansi,
                    gratifikasiopdcte.id_tipe_instansi,
                    gratifikasiopdcte.id_provinsi,
                    gratifikasiopdcte.id_kota_kabupaten,
                    gratifikasiopdcte.nama_instansi,
                    gratifikasiopdcte.opd,
                    (COALESCE(NULLIF(gratifikasiopdcte.pbj, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.pelayanan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.pengelolaan_sdm, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.perizinan, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasiopdcte.pbj IS NOT NULL AND gratifikasiopdcte.pbj <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.pelayanan IS NOT NULL AND gratifikasiopdcte.pelayanan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.pengelolaan_sdm IS NOT NULL AND gratifikasiopdcte.pengelolaan_sdm <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.perizinan IS NOT NULL AND gratifikasiopdcte.perizinan <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS keberadaan,
                    (COALESCE(NULLIF(gratifikasiopdcte.komitmen_pimpinan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.penegakan_aturan, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.sosialisasi, 0::numeric), 0::numeric) + COALESCE(NULLIF(gratifikasiopdcte.transparansi, 0::numeric), 0::numeric)) / (
                        CASE
                            WHEN gratifikasiopdcte.komitmen_pimpinan IS NOT NULL AND gratifikasiopdcte.komitmen_pimpinan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.penegakan_aturan IS NOT NULL AND gratifikasiopdcte.penegakan_aturan <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.sosialisasi IS NOT NULL AND gratifikasiopdcte.sosialisasi <> 0::numeric THEN 1
                            ELSE 0
                        END +
                        CASE
                            WHEN gratifikasiopdcte.transparansi IS NOT NULL AND gratifikasiopdcte.transparansi <> 0::numeric THEN 1
                            ELSE 0
                        END)::numeric AS upayappg
                   FROM gratifikasiopdcte
                )
         SELECT summarycte.tahun,
            summarycte.id_instansi,
            summarycte.id_tipe_instansi,
            summarycte.id_provinsi,
            summarycte.id_kota_kabupaten,
            summarycte.nama_instansi,
            summarycte.opd,
                CASE
                    WHEN (
                    CASE
                        WHEN summarycte.keberadaan IS NOT NULL AND summarycte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summarycte.upayappg IS NOT NULL AND summarycte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END) > 0 THEN (COALESCE(NULLIF(summarycte.keberadaan, 0::numeric), 0::numeric) + COALESCE(NULLIF(summarycte.upayappg, 0::numeric), 0::numeric)) / (
                    CASE
                        WHEN summarycte.keberadaan IS NOT NULL AND summarycte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summarycte.upayappg IS NOT NULL AND summarycte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END)::numeric
                    ELSE NULL::numeric
                END AS indeks_gratif,
            'INSTANSI'::text AS src
           FROM summarycte
        UNION
         SELECT summaryopdcte.tahun,
            summaryopdcte.id_instansi,
            summaryopdcte.id_tipe_instansi,
            summaryopdcte.id_provinsi,
            summaryopdcte.id_kota_kabupaten,
            summaryopdcte.nama_instansi,
            summaryopdcte.opd,
                CASE
                    WHEN (
                    CASE
                        WHEN summaryopdcte.keberadaan IS NOT NULL AND summaryopdcte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summaryopdcte.upayappg IS NOT NULL AND summaryopdcte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END) > 0 THEN (COALESCE(NULLIF(summaryopdcte.keberadaan, 0::numeric), 0::numeric) + COALESCE(NULLIF(summaryopdcte.upayappg, 0::numeric), 0::numeric)) / (
                    CASE
                        WHEN summaryopdcte.keberadaan IS NOT NULL AND summaryopdcte.keberadaan <> 0::numeric THEN 1
                        ELSE 0
                    END +
                    CASE
                        WHEN summaryopdcte.upayappg IS NOT NULL AND summaryopdcte.upayappg <> 0::numeric THEN 1
                        ELSE 0
                    END)::numeric
                    ELSE NULL::numeric
                END AS indeks_gratif,
            'OPD'::text AS src
           FROM summaryopdcte
        ");
        self::$connection->execute("
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1533;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1534;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1535;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1537;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1539;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1541;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1542;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1543;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1547;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1548;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1552;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1564;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1565;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1566;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1567;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1568;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1570;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1572;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1573;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1575;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1576;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1577;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1578;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1579;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1580;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1581;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1582;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1583;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1584;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1585;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1586;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1587;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1588;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1589;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1590;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1591;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1592;
            DELETE FROM jaga.sig_instansi WHERE id_instansi_gol = 1593;
        ");
    }
}