<?php

class ViewSpiTematikGratifikasiMigration_526 extends \Phalcon\Mvc\Model\Migration {
    public function up()
    {
        $this->down();

        self::$connection->execute("DROP VIEW IF EXISTS jaga.view_spi_tematik_gratifikasi;");

        self::$connection->execute("
            CREATE 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,
            				indeks,
                int_702b_4 AS pbj,
                (
                    (
                        COALESCE(NULLIF(ext_403, 0), 0) +
                        COALESCE(NULLIF(int_405b, 0), 0) +
                        COALESCE(NULLIF(ext_504a, 0), 0) +
                        COALESCE(NULLIF(int_405a, 0), 0) +
                        COALESCE(NULLIF(ext_505a, 0), 0) +
                        COALESCE(NULLIF(ext_503, 0), 0) +
                        COALESCE(NULLIF(int_403b, 0), 0)
                        )
                        /
                    (
                        CASE WHEN ext_403 IS NOT NULL AND ext_403 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_405b IS NOT NULL AND int_405b <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_504a IS NOT NULL AND ext_504a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_405a IS NOT NULL AND int_405a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_505a IS NOT NULL AND ext_505a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_503 IS NOT NULL AND ext_503 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_403b IS NOT NULL AND int_403b <> 0 THEN 1 ELSE 0 END
                        )
                    )AS pelayanan,
                (
                    (
                        COALESCE(NULLIF(int_602c, 0), 0) +
                        COALESCE(NULLIF(int_602a, 0), 0)
                        )
                        /
                    (
                        CASE WHEN int_602c IS NOT NULL AND int_602c <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_602a IS NOT NULL AND int_602a <> 0 THEN 1 ELSE 0 END
                        )
                    )AS pengelolaan_sdm,
                int_402_3 AS perizinan,
                int_504a AS komitmen_pimpinan,
                (
                    (
                        COALESCE(NULLIF(int_502a, 0), 0) +
                        COALESCE(NULLIF(int_502b, 0), 0)
                        )
                        /
                    (
                        CASE WHEN int_502a IS NOT NULL AND int_502a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_502b IS NOT NULL AND int_502b <> 0 THEN 1 ELSE 0 END
                        )
                    )AS penegakan_aturan,
                (
                    (
                        COALESCE(NULLIF(int_501c_2, 0), 0) +
                        COALESCE(NULLIF(int_501c_3, 0), 0)
                        )
                        /
                    (
                        CASE WHEN int_501c_2 IS NOT NULL AND int_501c_2 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_501c_3 IS NOT NULL AND int_501c_3 <> 0 THEN 1 ELSE 0 END
                        )
                    )AS sosialisasi,
                (
                    (
                        COALESCE(NULLIF(ext_402, 0), 0) +
                        COALESCE(NULLIF(ext_401, 0), 0) +
                        COALESCE(NULLIF(int_401b, 0), 0) +
                        COALESCE(NULLIF(int_401a, 0), 0)
                        )
                        /
                    (
                        CASE WHEN ext_402 IS NOT NULL AND ext_402 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_401 IS NOT NULL AND ext_401 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_401b IS NOT NULL AND int_401b <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_401a IS NOT NULL AND int_401a <> 0 THEN 1 ELSE 0 END
                        )
                    )AS transparansi
            FROM
                spi_internal_eksternal_detail spi
            		LEFT JOIN instansi i ON spi.id_instansi = i.id
            ),
            
            SummaryCTE AS (
            SELECT
                tahun,
                id_instansi,
            		id_tipe_instansi,
            		id_provinsi,
            		id_kota_kabupaten,
            		nama_instansi,
            		opd,
            		indeks,
                (
                    (
                        COALESCE(NULLIF(pbj, 0), 0) +
                        COALESCE(NULLIF(pelayanan, 0), 0) +
                        COALESCE(NULLIF(pengelolaan_sdm, 0), 0) +
                        COALESCE(NULLIF(perizinan, 0), 0)
                        )
                        /
                    (
                        CASE WHEN pbj IS NOT NULL AND pbj <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN pelayanan IS NOT NULL AND pelayanan <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN pengelolaan_sdm IS NOT NULL AND pengelolaan_sdm <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN perizinan IS NOT NULL AND perizinan <> 0 THEN 1 ELSE 0 END
                        )
                    ) AS keberadaan,
                (
                    (
                        COALESCE(NULLIF(komitmen_pimpinan, 0), 0) +
                        COALESCE(NULLIF(penegakan_aturan, 0), 0) +
                        COALESCE(NULLIF(sosialisasi, 0), 0) +
                        COALESCE(NULLIF(transparansi, 0), 0)
                        )
                        /
                    (
                        CASE WHEN komitmen_pimpinan IS NOT NULL AND komitmen_pimpinan <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN penegakan_aturan IS NOT NULL AND penegakan_aturan <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN sosialisasi IS NOT NULL AND sosialisasi <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN transparansi IS NOT NULL AND transparansi <> 0 THEN 1 ELSE 0 END
                        )
                    ) AS UpayaPPG
            FROM
                GratifikasiCTE
            ),
            
            
            
            IntOpdCTE AS (
            	SELECT spi.*, i.id_tipe AS id_tipe_instansi FROM spi_internal_opd_detail spi LEFT JOIN instansi i ON spi.id_instansi = i.id WHERE id_unit_kerja IS NULL
            ),
            
            ExtOpdCTE AS (
            	SELECT spi.*, i.id_tipe AS id_tipe_instansi FROM spi_external_opd_detail spi LEFT JOIN instansi i ON spi.id_instansi = i.id WHERE 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 OUTER JOIN ExtOpdCTE se ON si.id_instansi = se.id_instansi AND si.opd = se.opd
            ),
            
            GratifikasiOPDCTE AS (
            SELECT DISTINCT
                (CASE WHEN tahun_int IS NULL THEN tahun_ext ELSE tahun_int END ) AS tahun,
            		(CASE WHEN id_instansi_int IS NULL THEN id_instansi_ext ELSE id_instansi_int END) AS id_instansi,
            		(CASE WHEN id_tipe_instansi_int IS NULL THEN id_tipe_instansi_ext ELSE id_tipe_instansi_int END) AS id_tipe_instansi,
            		(CASE WHEN id_provinsi_int IS NULL THEN id_provinsi_ext ELSE id_provinsi_int END) AS id_provinsi,
            		(CASE WHEN id_kota_kabupaten_int IS NULL THEN id_kota_kabupaten_ext ELSE id_kota_kabupaten_int END) AS id_kota_kabupaten,
            		(CASE WHEN nama_instansi_int IS NULL THEN nama_instansi_ext ELSE nama_instansi_int END) AS nama_instansi,
            		(CASE WHEN opd_int IS NULL THEN opd_ext ELSE opd_int END) AS opd,
                int_702b_4 AS pbj,
                (
                    (
                        COALESCE(NULLIF(ext_403, 0), 0) +
                        COALESCE(NULLIF(int_405b, 0), 0) +
                        COALESCE(NULLIF(ext_504a, 0), 0) +
                        COALESCE(NULLIF(int_405a, 0), 0) +
                        COALESCE(NULLIF(ext_505a, 0), 0) +
                        COALESCE(NULLIF(ext_503, 0), 0) +
                        COALESCE(NULLIF(int_403b, 0), 0)
                        )
                        /
                    (
                        CASE WHEN ext_403 IS NOT NULL AND ext_403 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_405b IS NOT NULL AND int_405b <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_504a IS NOT NULL AND ext_504a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_405a IS NOT NULL AND int_405a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_505a IS NOT NULL AND ext_505a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_503 IS NOT NULL AND ext_503 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_403b IS NOT NULL AND int_403b <> 0 THEN 1 ELSE 0 END
                        )
                    )AS pelayanan,
                (CASE WHEN (
                        CASE WHEN int_602c IS NOT NULL AND int_602c <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_602a IS NOT NULL AND int_602a <> 0 THEN 1 ELSE 0 END
                        ) <> 0 THEN 
                    (
                        COALESCE(NULLIF(int_602c, 0), 0) +
                        COALESCE(NULLIF(int_602a, 0), 0)
                        )
                        /
                    (
                        CASE WHEN int_602c IS NOT NULL AND int_602c <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_602a IS NOT NULL AND int_602a <> 0 THEN 1 ELSE 0 END
                        )
            						ELSE NULL END
                    )AS pengelolaan_sdm,
                int_402_3 AS perizinan,
                int_504a AS komitmen_pimpinan,
                (CASE WHEN (CASE WHEN int_502a IS NOT NULL AND int_502a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_502b IS NOT NULL AND int_502b <> 0 THEN 1 ELSE 0 END) <> 0 THEN (
                    (
                        COALESCE(NULLIF(int_502a, 0), 0) +
                        COALESCE(NULLIF(int_502b, 0), 0)
                        )
                        /
                    (
                        CASE WHEN int_502a IS NOT NULL AND int_502a <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_502b IS NOT NULL AND int_502b <> 0 THEN 1 ELSE 0 END
                        )
            						)
            						ELSE NULL END
                    )AS penegakan_aturan,
                (CASE WHEN CASE WHEN int_501c_2 IS NOT NULL AND int_501c_2 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_501c_3 IS NOT NULL AND int_501c_3 <> 0 THEN 1 ELSE 0 END <> 0 THEN (
                    (
                        COALESCE(NULLIF(int_501c_2, 0), 0) +
                        COALESCE(NULLIF(int_501c_3, 0), 0)
                        )
                        /
                    (
                        CASE WHEN int_501c_2 IS NOT NULL AND int_501c_2 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_501c_3 IS NOT NULL AND int_501c_3 <> 0 THEN 1 ELSE 0 END
                        )
            						)
            						ELSE NULL END
                    )AS sosialisasi,
                (
                    (
                        COALESCE(NULLIF(ext_402, 0), 0) +
                        COALESCE(NULLIF(ext_401, 0), 0) +
                        COALESCE(NULLIF(int_401b, 0), 0) +
                        COALESCE(NULLIF(int_401a, 0), 0)
                        )
                        /
                    (
                        CASE WHEN ext_402 IS NOT NULL AND ext_402 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN ext_401 IS NOT NULL AND ext_401 <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_401b IS NOT NULL AND int_401b <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN int_401a IS NOT NULL AND int_401a <> 0 THEN 1 ELSE 0 END
                        )
                    )AS transparansi
            FROM
                OpdCTE
            		),
            		
            SummaryOPDCTE AS (
            SELECT
                tahun,
                id_instansi,
            		id_tipe_instansi,
            		id_provinsi,
            		id_kota_kabupaten,
            		nama_instansi,
            		opd,
                (
                    (
                        COALESCE(NULLIF(pbj, 0), 0) +
                        COALESCE(NULLIF(pelayanan, 0), 0) +
                        COALESCE(NULLIF(pengelolaan_sdm, 0), 0) +
                        COALESCE(NULLIF(perizinan, 0), 0)
                        )
                        /
                    (
                        CASE WHEN pbj IS NOT NULL AND pbj <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN pelayanan IS NOT NULL AND pelayanan <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN pengelolaan_sdm IS NOT NULL AND pengelolaan_sdm <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN perizinan IS NOT NULL AND perizinan <> 0 THEN 1 ELSE 0 END
                        )
                    ) AS keberadaan,
                (
                    (
                        COALESCE(NULLIF(komitmen_pimpinan, 0), 0) +
                        COALESCE(NULLIF(penegakan_aturan, 0), 0) +
                        COALESCE(NULLIF(sosialisasi, 0), 0) +
                        COALESCE(NULLIF(transparansi, 0), 0)
                        )
                        /
                    (
                        CASE WHEN komitmen_pimpinan IS NOT NULL AND komitmen_pimpinan <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN penegakan_aturan IS NOT NULL AND penegakan_aturan <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN sosialisasi IS NOT NULL AND sosialisasi <> 0 THEN 1 ELSE 0 END +
                        CASE WHEN transparansi IS NOT NULL AND transparansi <> 0 THEN 1 ELSE 0 END
                        )
                    ) AS UpayaPPG
            FROM
                GratifikasiOPDCTE
            )
            		
            SELECT
            	tahun,
                id_instansi,
            		id_tipe_instansi,
            		id_provinsi,
            		id_kota_kabupaten,
            		nama_instansi,
            		opd,
            	CASE WHEN (CASE WHEN keberadaan IS NOT NULL AND keberadaan <> 0 THEN 1 ELSE 0 END +
                            CASE WHEN upayaPPG IS NOT NULL AND upayaPPG <> 0 THEN 1 ELSE 0 END) > 0 THEN
            	(
                        (
                            COALESCE(NULLIF(keberadaan, 0), 0) +
                            COALESCE(NULLIF(upayaPPG, 0), 0)
                            )
                            /
                        (
                            CASE WHEN keberadaan IS NOT NULL AND keberadaan <> 0 THEN 1 ELSE 0 END +
                            CASE WHEN upayaPPG IS NOT NULL AND upayaPPG <> 0 THEN 1 ELSE 0 END
                            )
                        ) ELSE null END AS indeks_gratif,
		'INSTANSI' AS src
            	
            FROM
            	SummaryCTE
            	
            	UNION 
            	
            SELECT
            	tahun,
                id_instansi,
            		id_tipe_instansi,
            		id_provinsi,
            		id_kota_kabupaten,
            		nama_instansi,
            		opd,
            	CASE WHEN (CASE WHEN keberadaan IS NOT NULL AND keberadaan <> 0 THEN 1 ELSE 0 END +
                            CASE WHEN upayaPPG IS NOT NULL AND upayaPPG <> 0 THEN 1 ELSE 0 END) > 0 THEN
            	(
                        (
                            COALESCE(NULLIF(keberadaan, 0), 0) +
                            COALESCE(NULLIF(upayaPPG, 0), 0)
                            )
                            /
                        (
                            CASE WHEN keberadaan IS NOT NULL AND keberadaan <> 0 THEN 1 ELSE 0 END +
                            CASE WHEN upayaPPG IS NOT NULL AND upayaPPG <> 0 THEN 1 ELSE 0 END
                            )
                        ) ELSE null END AS indeks_gratif,
		'OPD' AS src
            	
            FROM
            	SummaryOPDCTE
                        ;
        ");
    }

    public function down()
    {
        self::$connection->execute("DROP VIEW IF EXISTS jaga.view_spi_tematik_gratifikasi;");
    }
}