<?php

use Phalcon\Mvc\Model\Migration;

class ReportAchMigration_384 extends Migration
{
    public function up()
    {
        $reportAchQuery = <<<QUERY
        CREATE OR REPLACE VIEW jaga.report_ach
        AS
        SELECT a.id,
           a.id AS id_master,
           l1.nama AS level1,
           l2.nama AS level2,
           l3.nama AS level3,
           l4.nama AS level4,
           i.nama AS instansi,
           it.nama AS tipe_instansi,
           t.nama AS target,
           t.info_nilai,
           t.info_lampiran,
               CASE
                   WHEN jaga.latest_verification(a.nilai_tw8, a.nilai_tw7, a.nilai_tw6, a.nilai_tw5, a.nilai_tw4, a.nilai_tw3, a.nilai_tw2, a.nilai_tw1) = 100::numeric
                   OR a.id in (11146, 11147, 11148, 11149, 11150, 11152)
                   THEN 1
                   ELSE 0
               END AS is_completed,
           jaga.latest_verification(a.nilai_tw8, a.nilai_tw7, a.nilai_tw6, a.nilai_tw5, a.nilai_tw4, a.nilai_tw3, a.nilai_tw2, a.nilai_tw1)::numeric(10,2) AS nilai_verification,
               CASE
                   WHEN COALESCE(a.klaim_tw1, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit1,
               CASE
                   WHEN COALESCE(a.klaim_tw2, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit2,
               CASE
                   WHEN COALESCE(a.klaim_tw3, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit3,
               CASE
                   WHEN COALESCE(a.klaim_tw4, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit4,
               CASE
                   WHEN COALESCE(a.klaim_tw5, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit5,
               CASE
                   WHEN COALESCE(a.klaim_tw6, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit6,
               CASE
                   WHEN COALESCE(a.klaim_tw7, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit7,
               CASE
                   WHEN COALESCE(a.klaim_tw8, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit8,
               CASE
                   WHEN COALESCE(a.nilai_tw1, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify1,
               CASE
                   WHEN COALESCE(a.nilai_tw2, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify2,
               CASE
                   WHEN COALESCE(a.nilai_tw3, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify3,
               CASE
                   WHEN COALESCE(a.nilai_tw4, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify4,
               CASE
                   WHEN COALESCE(a.nilai_tw5, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify5,
               CASE
                   WHEN COALESCE(a.nilai_tw6, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify6,
               CASE
                   WHEN COALESCE(a.nilai_tw7, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify7,
               CASE
                   WHEN COALESCE(a.nilai_tw8, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify8,
           a.id_instansi,
           a.id_target,
           a.id_periode,
           l1.id AS id_level1,
           l2.id AS id_level2,
           l3.id AS id_level3,
           l4.id AS id_level4,
           t.is_special,
           t.start_triwulan,
           COALESCE(y.label, ''::character varying) AS target_type
          FROM jaga.str_target_master a
            JOIN jaga.instansi i ON i.id = a.id_instansi
            JOIN jaga.instansi_tipe it ON it.id = i.id_tipe
            JOIN jaga.str_target t ON t.id = a.id_target
            LEFT JOIN jaga.str_target_type y ON y.id = t.is_special
            JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l1 ON l1.id = t.id_level1
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l2 ON l2.id = t.id_level2
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l3 ON l3.id = t.id_level3
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l4 ON l4.id = t.id_level4
         WHERE t.deleted_at IS NULL;
QUERY;
        self::$connection->execute($reportAchQuery);
    }

    public function down()
    {
        $modifyReportAchViewIntoLatestVerification = <<<QUERY
        CREATE OR REPLACE VIEW jaga.report_ach as
            select 
                a.id,
                a.id as id_master,
                l1.nama as level1,
                l2.nama as level2,
                l3.nama as level3,
                l4.nama as level4,
                i.nama as instansi,
                it.nama as tipe_instansi,
                t.nama as target,
                t.info_nilai,
                t.info_lampiran,
                case when greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) = 100 then 1 else 0 end as is_completed,
                CAST(latest_verification(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1) as numeric(10,2)) as nilai_verification,
                case when coalesce(klaim_tw1,0)=0 then 0 else 1 end as is_submit1, 
                case when coalesce(klaim_tw2,0)=0 then 0 else 1 end as is_submit2, 
                case when coalesce(klaim_tw3,0)=0 then 0 else 1 end as is_submit3, 
                case when coalesce(klaim_tw4,0)=0 then 0 else 1 end as is_submit4, 
                case when coalesce(klaim_tw5,0)=0 then 0 else 1 end as is_submit5, 
                case when coalesce(klaim_tw6,0)=0 then 0 else 1 end as is_submit6, 
                case when coalesce(klaim_tw7,0)=0 then 0 else 1 end as is_submit7, 
                case when coalesce(klaim_tw8,0)=0 then 0 else 1 end as is_submit8,
                case when coalesce(nilai_tw1,0)=0 then 0 else 1 end as is_verify1, 
                case when coalesce(nilai_tw2,0)=0 then 0 else 1 end as is_verify2, 
                case when coalesce(nilai_tw3,0)=0 then 0 else 1 end as is_verify3, 
                case when coalesce(nilai_tw4,0)=0 then 0 else 1 end as is_verify4, 
                case when coalesce(nilai_tw5,0)=0 then 0 else 1 end as is_verify5, 
                case when coalesce(nilai_tw6,0)=0 then 0 else 1 end as is_verify6, 
                case when coalesce(nilai_tw7,0)=0 then 0 else 1 end as is_verify7, 
                case when coalesce(nilai_tw8,0)=0 then 0 else 1 end as is_verify8,
                a.id_instansi, a.id_target,a.id_periode,l1.id as id_level1,l2.id as id_level2,l3.id as id_level3,l4.id as id_level4,
                t.is_special,
                t.start_triwulan,
                coalesce(y.label,'') as target_type
            from jaga.str_target_master a 
                join jaga.instansi i on i.id=a.id_instansi
                join jaga.instansi_tipe it on it.id=i.id_tipe
                join jaga.str_target t on t.id=a.id_target
                left join jaga.str_target_type y on y.id = t.is_special
                join (select * from jaga.str_hierarchy where deleted_at is null) l1 on l1.id = t.id_level1 
                left join (select * from jaga.str_hierarchy where deleted_at is null) l2 on l2.id = t.id_level2
                left join (select * from jaga.str_hierarchy where deleted_at is null) l3 on l3.id = t.id_level3
                left join (select * from jaga.str_hierarchy where deleted_at is null) l4 on l4.id = t.id_level4
            where t.deleted_at is null;
        ;
QUERY;
        self::$connection->execute($modifyReportAchViewIntoLatestVerification);
    }
}