down(); self::$connection->execute(" CREATE OR REPLACE VIEW jaga.stranas_target_instansi AS SELECT a.id, a.id_periode, a.id_level1, a.id_level2, a.id_level3, a.id_level4, a.nama, a.info_nilai, a.info_lampiran, a.created_by, a.created_at, a.updated_by, a.updated_at, a.deleted_by, a.deleted_at, b.instansi, a.is_special FROM jaga.str_target a JOIN ( SELECT a_1.id_target, string_agg(b_1.nama::text, ', '::text) AS instansi FROM jaga.str_target_master a_1 JOIN jaga.instansi b_1 ON b_1.id = a_1.id_instansi GROUP BY a_1.id_target) b ON b.id_target = a.id WHERE a.deleted_at IS NULL; "); self::$connection->execute(" create 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, greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) 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 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 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; ; " ); } public function down() { self::$connection->execute('DROP VIEW IF EXISTS jaga.stranas_target_instansi'); self::$connection->execute("DROP VIEW IF EXISTS jaga.report_ach;"); } }