checkIsAuthenticated(); $db = $this->getDI()->get('databaseNewStranas2025'); // Mulai transaksi $getParams = $this->request->getJsonRawBody(true); $periode_milestone = $getParams["periode"]; // $nama_instansi = "Kementerian Koordinator Bidang Perekonomian"; // $nama_instansi = "Kementerian Keuangan"; // Eksekusi query dengan parameter binding $keyword = '%' . $getParams["keyword"] . '%'; $db->begin(); try { // $nama_instansi = "Kementerian Dalam Negeri"; // bila optionnya aksi if($getParams["options"] == "aksi"){ if($getParams["keyword"] == "all"){ $query="SELECT timv.nama_verificator_b03, timv.nama_verificator_b06, timv.nama_verificator_b09, timv.nama_verificator_b12, timv.nama_verificator_b15, timv.nama_verificator_b18, timv.nama_verificator_b21, timv.nama_verificator_b24, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b03, timezone('Asia/Jakarta', timv.tanggal_verif_b06) as tanggal_verif_b06, timezone('Asia/Jakarta', timv.tanggal_verif_b09) as tanggal_verif_b09, timezone('Asia/Jakarta', timv.tanggal_verif_b12) as tanggal_verif_b12, timezone('Asia/Jakarta', timv.tanggal_verif_b15) as tanggal_verif_b15, timezone('Asia/Jakarta', timv.tanggal_verif_b18) as tanggal_verif_b18, timezone('Asia/Jakarta', timv.tanggal_verif_b21) as tanggal_verif_b21, timezone('Asia/Jakarta', timv.tanggal_verif_b24) as tanggal_verif_b24, (SELECT id FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS timv_id, mkm.id AS m_kl_milestone_id, mka.nama_aksi, mko.nama_output, mkm.nama_milestone, mka.urutan_aksi, mko.urutan_output, mkm.urutan_milestone, (SELECT keterangan_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b03, (SELECT keterangan_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b06, (SELECT keterangan_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b09, (SELECT keterangan_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b12, (SELECT keterangan_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b15, (SELECT keterangan_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b18, (SELECT keterangan_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b21, (SELECT keterangan_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b24, (SELECT score_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b03, (SELECT score_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b06, (SELECT score_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b09, (SELECT score_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b12, (SELECT score_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b15, (SELECT score_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b18, (SELECT score_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b21, (SELECT score_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b24, (WITH array_boolean_pelaporan_b03 AS ( SELECT COALESCE(tim.keterangan_pelapor, 'false') AS keterangan_pelapor FROM t_instansi_milestone AS tim WHERE tim.m_kl_milestone_id = mkm.id AND tim.periode_milestone = :periode_milestone ) SELECT STRING_AGG(CASE WHEN keterangan_pelapor = 'false' THEN 'false' ELSE 'true' END, ',') FROM array_boolean_pelaporan_b03) as sudah_lapor, (SELECT CASE WHEN score_b03 IS NULL THEN 'false' ELSE 'true' END AS score_b03 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b03, (SELECT CASE WHEN score_b06 IS NULL THEN 'false' ELSE 'true' END AS score_b06 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b06, (SELECT CASE WHEN score_b09 IS NULL THEN 'false' ELSE 'true' END AS score_b09 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b09, (SELECT CASE WHEN score_b12 IS NULL THEN 'false' ELSE 'true' END AS score_b12 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b12, (SELECT CASE WHEN score_b15 IS NULL THEN 'false' ELSE 'true' END AS score_b15 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b15, (SELECT CASE WHEN score_b18 IS NULL THEN 'false' ELSE 'true' END AS score_b18 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b18, (SELECT CASE WHEN score_b21 IS NULL THEN 'false' ELSE 'true' END AS score_b21 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b21, (SELECT CASE WHEN score_b24 IS NULL THEN 'false' ELSE 'true' END AS score_b24 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b24, (WITH array_of_instansi AS (SELECT mi_1.nama_pendek_instansi FROM m_instansi AS mi_1 LEFT JOIN t_instansi_milestone AS tim_1 ON mi_1.id = tim_1.m_instansi_id WHERE tim_1.periode_milestone = :periode_milestone AND tim_1.m_kl_milestone_id = mkm.id order by mi_1.nama_pendek_instansi, mi_1.m_jenis_instansi_id asc, mi_1.nama_instansi asc) SELECT ARRAY_AGG(array_of_instansi.nama_pendek_instansi) AS nama_pendek_instansi FROM array_of_instansi) AS nama_pendek_instansi, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b03 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_of_instansi_b03, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b03, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b03, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b06 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_of_instansi_b06, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b06, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b06, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b09 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_of_instansi_b09, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b09, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b09, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b12 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_of_instansi_b12, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b12, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b12, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b15 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_of_instansi_b15, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b15, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b15, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b18 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_of_instansi_b18, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b18, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b18, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b21 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_of_instansi_b21, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b21, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b21, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b24 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_of_instansi_b24, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b24, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b24, (WITH array_of_attachment_b03 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b03 FROM array_of_attachment_b03) AS attachment_b03, (WITH array_of_attachment_b06 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b06 FROM array_of_attachment_b06) AS attachment_b06, (WITH array_of_attachment_b09 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b09 FROM array_of_attachment_b09) AS attachment_b09, (WITH array_of_attachment_b12 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b12 FROM array_of_attachment_b12) AS attachment_b12, (WITH array_of_attachment_b15 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b15 FROM array_of_attachment_b15) AS attachment_b15, (WITH array_of_attachment_b18 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b18 FROM array_of_attachment_b18) AS attachment_b18, (WITH array_of_attachment_b21 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b21 FROM array_of_attachment_b21) AS attachment_b21, (WITH array_of_attachment_b24 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24 FROM m_kl_aksi AS mka LEFT JOIN m_kl_output AS mko ON mka.id = mko.m_kl_aksi_id LEFT JOIN m_kl_milestone AS mkm ON mko.id = mkm.m_kl_output_id LEFT JOIN t_inputan_milestone_verificator AS timv ON mkm.id = timv.m_kl_milestone_id ORDER BY mka.urutan_aksi ASC, mko.urutan_output ASC, mkm.urutan_milestone ASC"; }else{ $query="SELECT timv.nama_verificator_b03, timv.nama_verificator_b06, timv.nama_verificator_b09, timv.nama_verificator_b12, timv.nama_verificator_b15, timv.nama_verificator_b18, timv.nama_verificator_b21, timv.nama_verificator_b24, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b03, timezone('Asia/Jakarta', timv.tanggal_verif_b06) as tanggal_verif_b06, timezone('Asia/Jakarta', timv.tanggal_verif_b09) as tanggal_verif_b09, timezone('Asia/Jakarta', timv.tanggal_verif_b12) as tanggal_verif_b12, timezone('Asia/Jakarta', timv.tanggal_verif_b15) as tanggal_verif_b15, timezone('Asia/Jakarta', timv.tanggal_verif_b18) as tanggal_verif_b18, timezone('Asia/Jakarta', timv.tanggal_verif_b21) as tanggal_verif_b21, timezone('Asia/Jakarta', timv.tanggal_verif_b24) as tanggal_verif_b24, (SELECT id FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS timv_id, mkm.id AS m_kl_milestone_id, mka.nama_aksi, mko.nama_output, mkm.nama_milestone, mka.urutan_aksi, mko.urutan_output, mkm.urutan_milestone, (SELECT keterangan_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b03, (SELECT keterangan_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b06, (SELECT keterangan_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b09, (SELECT keterangan_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b12, (SELECT keterangan_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b15, (SELECT keterangan_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b18, (SELECT keterangan_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b21, (SELECT keterangan_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b24, (SELECT score_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b03, (SELECT score_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b06, (SELECT score_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b09, (SELECT score_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b12, (SELECT score_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b15, (SELECT score_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b18, (SELECT score_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b21, (SELECT score_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b24, (WITH array_boolean_pelaporan_b03 AS ( SELECT COALESCE(tim.keterangan_pelapor, 'false') AS keterangan_pelapor FROM t_instansi_milestone AS tim WHERE tim.m_kl_milestone_id = mkm.id AND tim.periode_milestone = :periode_milestone ) SELECT STRING_AGG(CASE WHEN keterangan_pelapor = 'false' THEN 'false' ELSE 'true' END, ',') FROM array_boolean_pelaporan_b03) as sudah_lapor, (SELECT CASE WHEN score_b03 IS NULL THEN 'false' ELSE 'true' END AS score_b03 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b03, (SELECT CASE WHEN score_b06 IS NULL THEN 'false' ELSE 'true' END AS score_b06 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b06, (SELECT CASE WHEN score_b09 IS NULL THEN 'false' ELSE 'true' END AS score_b09 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b09, (SELECT CASE WHEN score_b12 IS NULL THEN 'false' ELSE 'true' END AS score_b12 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b12, (SELECT CASE WHEN score_b15 IS NULL THEN 'false' ELSE 'true' END AS score_b15 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b15, (SELECT CASE WHEN score_b18 IS NULL THEN 'false' ELSE 'true' END AS score_b18 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b18, (SELECT CASE WHEN score_b21 IS NULL THEN 'false' ELSE 'true' END AS score_b21 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b21, (SELECT CASE WHEN score_b24 IS NULL THEN 'false' ELSE 'true' END AS score_b24 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b24, (WITH array_of_instansi AS (SELECT mi_1.nama_pendek_instansi FROM m_instansi AS mi_1 LEFT JOIN t_instansi_milestone AS tim_1 ON mi_1.id = tim_1.m_instansi_id WHERE tim_1.periode_milestone = :periode_milestone AND tim_1.m_kl_milestone_id = mkm.id order by mi_1.nama_pendek_instansi ) SELECT ARRAY_AGG(array_of_instansi.nama_pendek_instansi) AS nama_pendek_instansi FROM array_of_instansi ) AS nama_pendek_instansi, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b03 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_of_instansi_b03, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b03, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b03, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b06 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_of_instansi_b06, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b06, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b06, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b09 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_of_instansi_b09, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b09, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b09, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b12 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_of_instansi_b12, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b12, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b12, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b15 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_of_instansi_b15, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b15, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b15, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b18 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_of_instansi_b18, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b18, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b18, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b21 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_of_instansi_b21, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b21, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b21, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b24 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_of_instansi_b24, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b24, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b24, (WITH array_of_attachment_b03 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b03 FROM array_of_attachment_b03) AS attachment_b03, (WITH array_of_attachment_b06 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b06 FROM array_of_attachment_b06) AS attachment_b06, (WITH array_of_attachment_b09 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b09 FROM array_of_attachment_b09) AS attachment_b09, (WITH array_of_attachment_b12 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b12 FROM array_of_attachment_b12) AS attachment_b12, (WITH array_of_attachment_b15 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b15 FROM array_of_attachment_b15) AS attachment_b15, (WITH array_of_attachment_b18 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b18 FROM array_of_attachment_b18) AS attachment_b18, (WITH array_of_attachment_b21 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b21 FROM array_of_attachment_b21) AS attachment_b21, (WITH array_of_attachment_b24 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24 FROM m_kl_aksi AS mka LEFT JOIN m_kl_output AS mko ON mka.id = mko.m_kl_aksi_id LEFT JOIN m_kl_milestone AS mkm ON mko.id = mkm.m_kl_output_id LEFT JOIN t_inputan_milestone_verificator AS timv ON mkm.id = timv.m_kl_milestone_id WHERE mka.nama_aksi ILIKE :keyword ORDER BY mka.urutan_aksi ASC, mko.urutan_output ASC, mkm.urutan_milestone ASC"; } // bila optionnya output }else if ($getParams["options"] == "output"){ $query="SELECT timv.nama_verificator_b03, timv.nama_verificator_b06, timv.nama_verificator_b09, timv.nama_verificator_b12, timv.nama_verificator_b15, timv.nama_verificator_b18, timv.nama_verificator_b21, timv.nama_verificator_b24, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b03, timezone('Asia/Jakarta', timv.tanggal_verif_b06) as tanggal_verif_b06, timezone('Asia/Jakarta', timv.tanggal_verif_b09) as tanggal_verif_b09, timezone('Asia/Jakarta', timv.tanggal_verif_b12) as tanggal_verif_b12, timezone('Asia/Jakarta', timv.tanggal_verif_b15) as tanggal_verif_b15, timezone('Asia/Jakarta', timv.tanggal_verif_b18) as tanggal_verif_b18, timezone('Asia/Jakarta', timv.tanggal_verif_b21) as tanggal_verif_b21, timezone('Asia/Jakarta', timv.tanggal_verif_b24) as tanggal_verif_b24, (SELECT id FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS timv_id, mkm.id AS m_kl_milestone_id, mka.nama_aksi, mko.nama_output, mkm.nama_milestone, mka.urutan_aksi, mko.urutan_output, mkm.urutan_milestone, (SELECT keterangan_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b03, (SELECT keterangan_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b06, (SELECT keterangan_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b09, (SELECT keterangan_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b12, (SELECT keterangan_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b15, (SELECT keterangan_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b18, (SELECT keterangan_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b21, (SELECT keterangan_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b24, (SELECT score_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b03, (SELECT score_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b06, (SELECT score_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b09, (SELECT score_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b12, (SELECT score_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b15, (SELECT score_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b18, (SELECT score_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b21, (SELECT score_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b24, (WITH array_boolean_pelaporan_b03 AS ( SELECT COALESCE(tim.keterangan_pelapor, 'false') AS keterangan_pelapor FROM t_instansi_milestone AS tim WHERE tim.m_kl_milestone_id = mkm.id AND tim.periode_milestone = :periode_milestone ) SELECT STRING_AGG(CASE WHEN keterangan_pelapor = 'false' THEN 'false' ELSE 'true' END, ',') FROM array_boolean_pelaporan_b03) as sudah_lapor, (SELECT CASE WHEN score_b03 IS NULL THEN 'false' ELSE 'true' END AS score_b03 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b03, (SELECT CASE WHEN score_b06 IS NULL THEN 'false' ELSE 'true' END AS score_b06 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b06, (SELECT CASE WHEN score_b09 IS NULL THEN 'false' ELSE 'true' END AS score_b09 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b09, (SELECT CASE WHEN score_b12 IS NULL THEN 'false' ELSE 'true' END AS score_b12 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b12, (SELECT CASE WHEN score_b15 IS NULL THEN 'false' ELSE 'true' END AS score_b15 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b15, (SELECT CASE WHEN score_b18 IS NULL THEN 'false' ELSE 'true' END AS score_b18 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b18, (SELECT CASE WHEN score_b21 IS NULL THEN 'false' ELSE 'true' END AS score_b21 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b21, (SELECT CASE WHEN score_b24 IS NULL THEN 'false' ELSE 'true' END AS score_b24 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b24, (WITH array_of_instansi AS (SELECT mi_1.nama_pendek_instansi FROM m_instansi AS mi_1 LEFT JOIN t_instansi_milestone AS tim_1 ON mi_1.id = tim_1.m_instansi_id WHERE tim_1.periode_milestone = :periode_milestone AND tim_1.m_kl_milestone_id = mkm.id order by mi_1.nama_pendek_instansi) SELECT ARRAY_AGG(array_of_instansi.nama_pendek_instansi) AS nama_pendek_instansi FROM array_of_instansi ) AS nama_pendek_instansi, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b03 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_of_instansi_b03, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b03, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b03, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b06 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_of_instansi_b06, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b06, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b06, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b09 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_of_instansi_b09, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b09, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b09, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b12 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_of_instansi_b12, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b12, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b12, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b15 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_of_instansi_b15, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b15, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b15, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b18 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_of_instansi_b18, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b18, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b18, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b21 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_of_instansi_b21, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b21, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b21, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b24 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_of_instansi_b24, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b24, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b24, (WITH array_of_attachment_b03 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b03 FROM array_of_attachment_b03) AS attachment_b03, (WITH array_of_attachment_b06 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b06 FROM array_of_attachment_b06) AS attachment_b06, (WITH array_of_attachment_b09 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b09 FROM array_of_attachment_b09) AS attachment_b09, (WITH array_of_attachment_b12 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b12 FROM array_of_attachment_b12) AS attachment_b12, (WITH array_of_attachment_b15 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b15 FROM array_of_attachment_b15) AS attachment_b15, (WITH array_of_attachment_b18 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b18 FROM array_of_attachment_b18) AS attachment_b18, (WITH array_of_attachment_b21 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b21 FROM array_of_attachment_b21) AS attachment_b21, (WITH array_of_attachment_b24 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24 FROM m_kl_aksi AS mka LEFT JOIN m_kl_output AS mko ON mka.id = mko.m_kl_aksi_id LEFT JOIN m_kl_milestone AS mkm ON mko.id = mkm.m_kl_output_id LEFT JOIN t_inputan_milestone_verificator AS timv ON mkm.id = timv.m_kl_milestone_id WHERE mko.nama_output ILIKE :keyword ORDER BY mka.urutan_aksi ASC, mko.urutan_output ASC, mkm.urutan_milestone ASC"; // bila optionnya target }else if ($getParams["options"] == "milestone"){ $query="SELECT timv.nama_verificator_b03, timv.nama_verificator_b06, timv.nama_verificator_b09, timv.nama_verificator_b12, timv.nama_verificator_b15, timv.nama_verificator_b18, timv.nama_verificator_b21, timv.nama_verificator_b24, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b03, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b06, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b09, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b12, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b15, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b18, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b21, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b24, (SELECT id FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS timv_id, mkm.id AS m_kl_milestone_id, mka.nama_aksi, mko.nama_output, mkm.nama_milestone, mka.urutan_aksi, mko.urutan_output, mkm.urutan_milestone, (SELECT keterangan_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b03, (SELECT keterangan_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b06, (SELECT keterangan_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b09, (SELECT keterangan_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b12, (SELECT keterangan_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b15, (SELECT keterangan_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b18, (SELECT keterangan_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b21, (SELECT keterangan_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS keterangan_verifikator_b24, (WITH array_boolean_pelaporan_b03 AS ( SELECT COALESCE(tim.keterangan_pelapor, 'false') AS keterangan_pelapor FROM t_instansi_milestone AS tim WHERE tim.m_kl_milestone_id = mkm.id AND tim.periode_milestone = :periode_milestone ) SELECT STRING_AGG(CASE WHEN keterangan_pelapor = 'false' THEN 'false' ELSE 'true' END, ',') FROM array_boolean_pelaporan_b03) as sudah_lapor, (SELECT CASE WHEN score_b03 IS NULL THEN 'false' ELSE 'true' END AS score_b03 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b03, (SELECT CASE WHEN score_b06 IS NULL THEN 'false' ELSE 'true' END AS score_b06 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b06, (SELECT CASE WHEN score_b09 IS NULL THEN 'false' ELSE 'true' END AS score_b09 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b09, (SELECT CASE WHEN score_b12 IS NULL THEN 'false' ELSE 'true' END AS score_b12 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b12, (SELECT CASE WHEN score_b15 IS NULL THEN 'false' ELSE 'true' END AS score_b15 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b15, (SELECT CASE WHEN score_b18 IS NULL THEN 'false' ELSE 'true' END AS score_b18 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b18, (SELECT CASE WHEN score_b21 IS NULL THEN 'false' ELSE 'true' END AS score_b21 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b21, (SELECT CASE WHEN score_b24 IS NULL THEN 'false' ELSE 'true' END AS score_b24 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b24, (SELECT score_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b03, (SELECT score_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b06, (SELECT score_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b09, (SELECT score_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b12, (SELECT score_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b15, (SELECT score_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b18, (SELECT score_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b21, (SELECT score_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS score_b24, (WITH array_of_instansi AS (SELECT mi_1.nama_pendek_instansi FROM m_instansi AS mi_1 LEFT JOIN t_instansi_milestone AS tim_1 ON mi_1.id = tim_1.m_instansi_id WHERE tim_1.periode_milestone = :periode_milestone AND tim_1.m_kl_milestone_id = mkm.id order by mi_1.nama_pendek_instansi) SELECT ARRAY_AGG(array_of_instansi.nama_pendek_instansi) AS nama_pendek_instansi FROM array_of_instansi ) AS nama_pendek_instansi, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b03 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_of_instansi_b03, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b03, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b03, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b06 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_of_instansi_b06, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b06, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b06, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b09 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_of_instansi_b09, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b09, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b09, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b12 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_of_instansi_b12, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b12, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b12, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b15 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_of_instansi_b15, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b15, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b15, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b18 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_of_instansi_b18, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b18, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b18, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b21 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_of_instansi_b21, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b21, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b21, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b24 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_of_instansi_b24, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b24, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b24, (WITH array_of_attachment_b03 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b03 FROM array_of_attachment_b03) AS attachment_b03, (WITH array_of_attachment_b06 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b06 FROM array_of_attachment_b06) AS attachment_b06, (WITH array_of_attachment_b09 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b09 FROM array_of_attachment_b09) AS attachment_b09, (WITH array_of_attachment_b12 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b12 FROM array_of_attachment_b12) AS attachment_b12, (WITH array_of_attachment_b15 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b15 FROM array_of_attachment_b15) AS attachment_b15, (WITH array_of_attachment_b18 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b18 FROM array_of_attachment_b18) AS attachment_b18, (WITH array_of_attachment_b21 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b21 FROM array_of_attachment_b21) AS attachment_b21, (WITH array_of_attachment_b24 AS (SELECT takv.uuid, takv.nama_label, takv.nama_dokumen, takv.ukuran_file FROM t_inputan_milestone_verificator AS timv LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id WHERE timv.m_kl_milestone_id = mkm.id AND takv.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24 FROM m_kl_aksi AS mka LEFT JOIN m_kl_output AS mko ON mka.id = mko.m_kl_aksi_id LEFT JOIN m_kl_milestone AS mkm ON mko.id = mkm.m_kl_output_id LEFT JOIN t_inputan_milestone_verificator AS timv ON mkm.id = timv.m_kl_milestone_id WHERE mkm.nama_milestone ILIKE :keyword ORDER BY mka.urutan_aksi ASC, mko.urutan_output ASC, mkm.urutan_milestone ASC"; }else if ($getParams["options"] == "instansi"){ $query="SELECT timv.nama_verificator_b03, timv.nama_verificator_b06, timv.nama_verificator_b09, timv.nama_verificator_b12, timv.nama_verificator_b15, timv.nama_verificator_b18, timv.nama_verificator_b21, timv.nama_verificator_b24, timezone('Asia/Jakarta', timv.tanggal_verif_b03) as tanggal_verif_b03, timezone('Asia/Jakarta', timv.tanggal_verif_b06) as tanggal_verif_b06, timezone('Asia/Jakarta', timv.tanggal_verif_b09) as tanggal_verif_b09, timezone('Asia/Jakarta', timv.tanggal_verif_b12) as tanggal_verif_b12, timezone('Asia/Jakarta', timv.tanggal_verif_b15) as tanggal_verif_b15, timezone('Asia/Jakarta', timv.tanggal_verif_b18) as tanggal_verif_b18, timezone('Asia/Jakarta', timv.tanggal_verif_b21) as tanggal_verif_b21, timezone('Asia/Jakarta', timv.tanggal_verif_b24) as tanggal_verif_b24, timv.id AS timv_id, mka.nama_aksi, mko.nama_output, mkm.nama_milestone, mka.urutan_aksi, mko.urutan_output, mkm.urutan_milestone, mkm.id AS m_kl_milestone_id, timv.keterangan_verificator_b03 AS keterangan_verifikator_b03, timv.keterangan_verificator_b06 AS keterangan_verifikator_b06, timv.keterangan_verificator_b09 AS keterangan_verifikator_b09, timv.keterangan_verificator_b12 AS keterangan_verifikator_b12, timv.keterangan_verificator_b15 AS keterangan_verifikator_b15, timv.keterangan_verificator_b18 AS keterangan_verifikator_b18, timv.keterangan_verificator_b21 AS keterangan_verifikator_b21, timv.keterangan_verificator_b24 AS keterangan_verifikator_b24, timv.score_b03, timv.score_b06, timv.score_b09, timv.score_b12, timv.score_b15, timv.score_b18, timv.score_b21, timv.score_b24, (WITH array_boolean_pelaporan_b03 AS ( SELECT COALESCE(tim.keterangan_pelapor, 'false') AS keterangan_pelapor FROM t_instansi_milestone AS tim WHERE tim.m_kl_milestone_id = mkm.id AND tim.periode_milestone = :periode_milestone ) SELECT STRING_AGG(CASE WHEN keterangan_pelapor = 'false' THEN 'false' ELSE 'true' END, ',') FROM array_boolean_pelaporan_b03) as sudah_lapor, (SELECT CASE WHEN score_b03 IS NULL THEN 'false' ELSE 'true' END AS score_b03 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b03, (SELECT CASE WHEN score_b06 IS NULL THEN 'false' ELSE 'true' END AS score_b06 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b06, (SELECT CASE WHEN score_b09 IS NULL THEN 'false' ELSE 'true' END AS score_b09 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b09, (SELECT CASE WHEN score_b12 IS NULL THEN 'false' ELSE 'true' END AS score_b12 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b12, (SELECT CASE WHEN score_b15 IS NULL THEN 'false' ELSE 'true' END AS score_b15 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b15, (SELECT CASE WHEN score_b18 IS NULL THEN 'false' ELSE 'true' END AS score_b18 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b18, (SELECT CASE WHEN score_b21 IS NULL THEN 'false' ELSE 'true' END AS score_b21 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b21, (SELECT CASE WHEN score_b24 IS NULL THEN 'false' ELSE 'true' END AS score_b24 FROM t_inputan_milestone_verificator AS timv WHERE timv.m_kl_milestone_id = mkm.id) AS sudah_terverifikasi_b24, (WITH array_of_boolean_pelapor AS (SELECT COALESCE(tim_3.keterangan_pelapor, '') AS boolean_pelapor FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = :periode_milestone) SELECT ARRAY_AGG(array_of_boolean_pelapor) AS boolean_pelapor FROM array_of_boolean_pelapor) AS boolean_pelapor, tim.periode_milestone, (WITH array_of_instansi AS (SELECT mi_1.nama_pendek_instansi FROM m_instansi AS mi_1 LEFT JOIN t_instansi_milestone AS tim_1 ON mi_1.id = tim_1.m_instansi_id WHERE tim_1.periode_milestone = :periode_milestone AND tim_1.m_kl_milestone_id = mkm.id order by mi_1.nama_pendek_instansi) SELECT ARRAY_AGG(array_of_instansi.nama_pendek_instansi) AS nama_pendek_instansi FROM array_of_instansi ) AS nama_pendek_instansi, (SELECT tim_2.id FROM m_instansi AS mi_2 LEFT JOIN t_instansi_milestone AS tim_2 ON mi_2.id = tim_2.m_instansi_id WHERE tim_2.periode_milestone = :periode_milestone AND tim_2.m_kl_milestone_id = mkm.id AND tim_2.m_instansi_id = mi_2.id AND mi_2.nama_instansi = :nama_instansi) AS t_instansi_milestone_id, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b03 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_of_instansi_b03, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b03, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b03, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b06 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_of_instansi_b06, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b06, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b06, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b09 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_of_instansi_b09, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b09, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b09, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b12 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_of_instansi_b12, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b12, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b12, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b15 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_of_instansi_b15, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b15, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b15, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b18 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_of_instansi_b18, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b18, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b18, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b21 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_of_instansi_b21, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b21, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan, '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b21, (WITH array_of_keterangan_pelapor AS (SELECT (WITH array_of_attachment_instansi_b24 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_instansi WHERE t_instansi_milestone_id = tim_3.id AND periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';' , nama_label, ';' , nama_dokumen, ';' , ukuran_file), ':') AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_of_instansi_b24, tim_3.keterangan_pelapor, tim_3.id, mi_3.nama_instansi, mi_3.nama_pendek_instansi, tim_3.nama_pelapor, timezone('Asia/Jakarta', tim_3.tanggal_laporan) as tanggal_laporan FROM t_instansi_milestone AS tim_3 LEFT JOIN m_kl_milestone AS mkm_3 ON tim_3.m_kl_milestone_id = mkm_3.id LEFT JOIN m_instansi AS mi_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.m_kl_milestone_id = mkm.id AND tim_3.periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(attachment_of_instansi_b24, '(-)', keterangan_pelapor, '(-)', id, '(-)', nama_instansi, '(-)', nama_pendek_instansi, '(-)', nama_pelapor, '(-)', tanggal_laporan , '(-)'), '(+)') AS keterangan_pelapor FROM array_of_keterangan_pelapor) AS keterangan_pelapor_b24, (WITH array_of_attachment_b03 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B03') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b03 FROM array_of_attachment_b03) AS attachment_b03, (WITH array_of_attachment_b06 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B06') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b06 FROM array_of_attachment_b06) AS attachment_b06, (WITH array_of_attachment_b09 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B09') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b09 FROM array_of_attachment_b09) AS attachment_b09, (WITH array_of_attachment_b12 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B12') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b12 FROM array_of_attachment_b12) AS attachment_b12, (WITH array_of_attachment_b15 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B15') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b15 FROM array_of_attachment_b15) AS attachment_b15, (WITH array_of_attachment_b18 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B18') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b15 FROM array_of_attachment_b18) AS attachment_b18, (WITH array_of_attachment_b21 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B21') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b21 FROM array_of_attachment_b21) AS attachment_b21, (WITH array_of_attachment_b24 AS (SELECT uuid, nama_label, nama_dokumen, ukuran_file FROM t_attachment_kl_verificator WHERE t_inputan_milestone_verificator_id = timv.id AND periode_milestone = 'B24') SELECT STRING_AGG(CONCAT(uuid, ';', nama_label, ';', nama_dokumen, ';', ukuran_file, ';'), '(+)') AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24 FROM m_kl_aksi AS mka LEFT JOIN m_kl_output AS mko ON mka.id = mko.m_kl_aksi_id LEFT JOIN m_kl_milestone AS mkm ON mko.id = mkm.m_kl_output_id LEFT JOIN t_instansi_milestone AS tim ON mkm.id = tim.m_kl_milestone_id LEFT JOIN m_instansi AS mi ON mi.id = tim.m_instansi_id LEFT JOIN t_inputan_milestone_verificator AS timv ON mkm.id = timv.m_kl_milestone_id WHERE mi.nama_instansi = :nama_instansi AND tim.periode_milestone = :periode_milestone ORDER BY mka.urutan_aksi ASC, mko.urutan_output ASC, mkm.urutan_milestone ASC"; } if($getParams["options"] == "aksi"){ if($getParams["keyword"] == "all"){ $results = $db->query($query, ['periode_milestone' => $periode_milestone]); }else{ $results = $db->query($query, ['keyword' => $keyword, 'periode_milestone' => $periode_milestone]); } }else if ($getParams["options"] == "output"){ $results = $db->query($query, ['keyword' => $keyword, 'periode_milestone' => $periode_milestone]); // bila optionnya target }else if ($getParams["options"] == "milestone"){ $results = $db->query($query, ['keyword' => $keyword, 'periode_milestone' => $periode_milestone]); }else if ($getParams["options"] == "instansi"){ $results = $db->query($query, ['nama_instansi' => $getParams["keyword"], 'periode_milestone' => $periode_milestone]); } $data = $results->fetchAll(PDO::FETCH_OBJ); // Di Pisah $result = []; for($k = 0; $k < sizeof($data); $k++){ $result[$k]["attachment_b03"] = $data[$k]->attachment_b03; $result[$k]["attachment_b06"] = $data[$k]->attachment_b06; $result[$k]["attachment_b09"] = $data[$k]->attachment_b09; $result[$k]["attachment_b12"] = $data[$k]->attachment_b12; $result[$k]["attachment_b15"] = $data[$k]->attachment_b15; $result[$k]["attachment_b18"] = $data[$k]->attachment_b18; $result[$k]["attachment_b21"] = $data[$k]->attachment_b21; $result[$k]["attachment_b24"] = $data[$k]->attachment_b24; $result[$k]["sudah_lapor"] = $data[$k]->sudah_lapor; $result[$k]["sudah_terverifikasi_b03"] = $data[$k]->sudah_terverifikasi_b03; $result[$k]["sudah_terverifikasi_b06"] = $data[$k]->sudah_terverifikasi_b06; $result[$k]["sudah_terverifikasi_b09"] = $data[$k]->sudah_terverifikasi_b09; $result[$k]["sudah_terverifikasi_b12"] = $data[$k]->sudah_terverifikasi_b12; $result[$k]["sudah_terverifikasi_b15"] = $data[$k]->sudah_terverifikasi_b15; $result[$k]["sudah_terverifikasi_b18"] = $data[$k]->sudah_terverifikasi_b18; $result[$k]["sudah_terverifikasi_b21"] = $data[$k]->sudah_terverifikasi_b21; $result[$k]["sudah_terverifikasi_b24"] = $data[$k]->sudah_terverifikasi_b24; $result[$k]["nama_verificator_b03"] = $data[$k]->nama_verificator_b03; $result[$k]["nama_verificator_b06"] = $data[$k]->nama_verificator_b06; $result[$k]["nama_verificator_b09"] = $data[$k]->nama_verificator_b09; $result[$k]["nama_verificator_b12"] = $data[$k]->nama_verificator_b12; $result[$k]["nama_verificator_b15"] = $data[$k]->nama_verificator_b15; $result[$k]["nama_verificator_b18"] = $data[$k]->nama_verificator_b18; $result[$k]["nama_verificator_b21"] = $data[$k]->nama_verificator_b21; $result[$k]["nama_verificator_b24"] = $data[$k]->nama_verificator_b24; $result[$k]["tanggal_verif_b03"] = $data[$k]->tanggal_verif_b03; $result[$k]["tanggal_verif_b06"] = $data[$k]->tanggal_verif_b06; $result[$k]["tanggal_verif_b09"] = $data[$k]->tanggal_verif_b09; $result[$k]["tanggal_verif_b12"] = $data[$k]->tanggal_verif_b12; $result[$k]["tanggal_verif_b15"] = $data[$k]->tanggal_verif_b15; $result[$k]["tanggal_verif_b18"] = $data[$k]->tanggal_verif_b18; $result[$k]["tanggal_verif_b21"] = $data[$k]->tanggal_verif_b21; $result[$k]["tanggal_verif_b24"] = $data[$k]->tanggal_verif_b24; $result[$k]["keterangan_pelapor_b03"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b03); $result[$k]["keterangan_pelapor_b06"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b06); $result[$k]["keterangan_pelapor_b09"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b09); $result[$k]["keterangan_pelapor_b12"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b12); $result[$k]["keterangan_pelapor_b15"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b15); $result[$k]["keterangan_pelapor_b18"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b18); $result[$k]["keterangan_pelapor_b21"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b21); $result[$k]["keterangan_pelapor_b24"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->keterangan_pelapor_b24); $result[$k]["keterangan_verifikator_b03"] = $data[$k]->keterangan_verifikator_b03; $result[$k]["keterangan_verifikator_b06"] = $data[$k]->keterangan_verifikator_b06; $result[$k]["keterangan_verifikator_b09"] = $data[$k]->keterangan_verifikator_b09; $result[$k]["keterangan_verifikator_b12"] = $data[$k]->keterangan_verifikator_b12; $result[$k]["keterangan_verifikator_b15"] = $data[$k]->keterangan_verifikator_b15; $result[$k]["keterangan_verifikator_b18"] = $data[$k]->keterangan_verifikator_b18; $result[$k]["keterangan_verifikator_b21"] = $data[$k]->keterangan_verifikator_b21; $result[$k]["keterangan_verifikator_b24"] = $data[$k]->keterangan_verifikator_b24; $result[$k]["m_kl_milestone_id"] = $data[$k]->m_kl_milestone_id; $result[$k]["nama_aksi"] = $data[$k]->nama_aksi; $result[$k]["nama_milestone"] = $data[$k]->nama_milestone; $result[$k]["nama_output"] = $data[$k]->nama_output; $result[$k]['urutan_aksi'] = $data[$k]->urutan_aksi; $result[$k]['urutan_output'] = $data[$k]->urutan_output; $result[$k]['urutan_milestone'] = $data[$k]->urutan_milestone; $result[$k]["nama_pendek_instansi"] = $this->convertKurungKurawalMenjadiKurungSiku($data[$k]->nama_pendek_instansi); $result[$k]["score_b03"] = $data[$k]->score_b03; $result[$k]["score_b06"] = $data[$k]->score_b06; $result[$k]["score_b09"] = $data[$k]->score_b09; $result[$k]["score_b12"] = $data[$k]->score_b12; $result[$k]["score_b15"] = $data[$k]->score_b15; $result[$k]["score_b18"] = $data[$k]->score_b18; $result[$k]["score_b21"] = $data[$k]->score_b21; $result[$k]["score_b24"] = $data[$k]->score_b24; $result[$k]["timv_id"] = $data[$k]->timv_id; //add new attributes $result[$k]["instansi_score_b03"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B03'); $result[$k]["instansi_score_b06"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B06'); $result[$k]["instansi_score_b09"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B09'); $result[$k]["instansi_score_b12"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B12'); $result[$k]["instansi_score_b15"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B15'); $result[$k]["instansi_score_b18"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B18'); $result[$k]["instansi_score_b21"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B21'); $result[$k]["instansi_score_b24"] = $this->searchVerifMilestoneInstansi($db, $data[$k]->m_kl_milestone_id, 'B24'); } $db->commit(); // Menggunakan fetchAll untuk mendapatkan hasil query if (!empty($data)) { return new SimpleResponse($result); } else { return new SimpleResponse([]); } } catch (Exception $e) { // Rollback transaksi jika terjadi kesalahan $db->rollback(); return new SimpleResponse([ 'Message' => "Gagal mendapatkan data " . $e->getMessage() ]); } } /** * Fetch semua current scores untuk milestone * @return array */ private function fetchCurrentScores($db, $milestone_id) { $query = "SELECT score_b03, score_b06, score_b09, score_b12, score_b15, score_b18, score_b21, score_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :milestone_id LIMIT 1"; $result = $db->query($query, ['milestone_id' => $milestone_id]); $row = $result->fetch(PDO::FETCH_ASSOC); if (!$row) { return [ 'b03' => null, 'b06' => null, 'b09' => null, 'b12' => null, 'b15' => null, 'b18' => null, 'b21' => null, 'b24' => null ]; } return [ 'b03' => $row['score_b03'], 'b06' => $row['score_b06'], 'b09' => $row['score_b09'], 'b12' => $row['score_b12'], 'b15' => $row['score_b15'], 'b18' => $row['score_b18'], 'b21' => $row['score_b21'], 'b24' => $row['score_b24'] ]; } /** * Apply forward fill logic ke score yang akan di-insert * Jika ada score sebelumnya = 100, score baru harus = 100 * @return int|float score yang sudah di-forward-fill */ private function applyForwardFillLogic($current_scores, $periode, $new_score) { $periods_order = ['b03', 'b06', 'b09', 'b12', 'b15', 'b18', 'b21', 'b24']; $current_index = array_search(strtolower($periode), $periods_order); // Jika ada score sebelumnya yang = 100, paksa score baru = 100 for ($i = 0; $i < $current_index; $i++) { if ((int)($current_scores[$periods_order[$i]] ?? 0) === 100) { return 100; // Force score menjadi 100 } } return $new_score; // Kembalikan score yang diberikan user } /** * Dapatkan nama field database untuk periode tertentu * @return array */ private function getPeriodFields($periode) { $mapping = [ 'B03' => [ 'keterangan' => 'keterangan_verificator_b03', 'nama' => 'nama_verificator_b03', 'score' => 'score_b03', 'tanggal' => 'tanggal_verif_b03', 'updated_at' => 'updated_at_b03' ], 'B06' => [ 'keterangan' => 'keterangan_verificator_b06', 'nama' => 'nama_verificator_b06', 'score' => 'score_b06', 'tanggal' => 'tanggal_verif_b06', 'updated_at' => 'updated_at_b06' ], 'B09' => [ 'keterangan' => 'keterangan_verificator_b09', 'nama' => 'nama_verificator_b09', 'score' => 'score_b09', 'tanggal' => 'tanggal_verif_b09', 'updated_at' => 'updated_at_b09' ], 'B12' => [ 'keterangan' => 'keterangan_verificator_b12', 'nama' => 'nama_verificator_b12', 'score' => 'score_b12', 'tanggal' => 'tanggal_verif_b12', 'updated_at' => 'updated_at_b12' ], 'B15' => [ 'keterangan' => 'keterangan_verificator_b15', 'nama' => 'nama_verificator_b15', 'score' => 'score_b15', 'tanggal' => 'tanggal_verif_b15', 'updated_at' => 'updated_at_b15' ], 'B18' => [ 'keterangan' => 'keterangan_verificator_b18', 'nama' => 'nama_verificator_b18', 'score' => 'score_b18', 'tanggal' => 'tanggal_verif_b18', 'updated_at' => 'updated_at_b18' ], 'B21' => [ 'keterangan' => 'keterangan_verificator_b21', 'nama' => 'nama_verificator_b21', 'score' => 'score_b21', 'tanggal' => 'tanggal_verif_b21', 'updated_at' => 'updated_at_b21' ], 'B24' => [ 'keterangan' => 'keterangan_verificator_b24', 'nama' => 'nama_verificator_b24', 'score' => 'score_b24', 'tanggal' => 'tanggal_verif_b24', 'updated_at' => 'updated_at_b24' ] ]; return $mapping[$periode] ?? []; } /** * Insert attachments ke database */ private function insertAttachments($db, $attachments, $timv_id, $periode, $created_by) { $placeholders = []; $values = []; $counter = 1; for($i = 0; $i < count($attachments); $i++){ $placeholders[] = "(:uuid{$counter}, :t_inputan_milestone_verificator_id{$counter}, :nama_label{$counter}, :nama_dokumen{$counter}, :ukuran_file{$counter}, :periode_milestone{$counter}, :created_by{$counter}, :created_at{$counter}, :updated_at{$counter})"; $values[":uuid{$counter}"] = $attachments[$i]["uuid"]; $values[":t_inputan_milestone_verificator_id{$counter}"] = $timv_id; $values[":nama_label{$counter}"] = $attachments[$i]["label"]; $values[":nama_dokumen{$counter}"] = $attachments[$i]["filename"]; $values[":ukuran_file{$counter}"] = $attachments[$i]["size"]; $values[":periode_milestone{$counter}"] = $periode; $values[":created_by{$counter}"] = $created_by; $values[":created_at{$counter}"] = $this->now(); $values[":updated_at{$counter}"] = $this->now(); $counter++; } $query = "INSERT into t_attachment_kl_verificator (uuid, t_inputan_milestone_verificator_id, nama_label, nama_dokumen, ukuran_file, periode_milestone, created_by, created_at, updated_at) VALUES " . implode(", ", $placeholders); $db->execute($query, $values); } /** * Update forward filled periods setelah insert periode sekarang * Jika periode sekarang = 100, update semua periode setelahnya = 100 */ private function updateForwardFilledPeriods($db, $timv_id, $current_periode, $nama_verificator) { $periods_order = ['B03', 'B06', 'B09', 'B12', 'B15', 'B18', 'B21', 'B24']; $current_index = array_search($current_periode, $periods_order); // Fetch score periode sekarang yang baru di-insert/update $query = "SELECT " . $this->getPeriodFields($current_periode)['score'] . " as current_score FROM t_inputan_milestone_verificator WHERE id = :id"; $result = $db->query($query, ['id' => $timv_id]); $row = $result->fetch(PDO::FETCH_ASSOC); if (!$row || (int)$row['current_score'] !== 100) { return; // Tidak perlu forward fill jika score tidak = 100 } // Score periode sekarang = 100, maka update semua periode setelahnya = 100 $now = $this->now(); for ($i = $current_index + 1; $i < count($periods_order); $i++) { $next_periode = $periods_order[$i]; $fields = $this->getPeriodFields($next_periode); // Cek apakah periode sudah ada data atau belum $check_query = "SELECT {$fields['score']} FROM t_inputan_milestone_verificator WHERE id = :id"; $check_result = $db->query($check_query, ['id' => $timv_id]); $check_row = $check_result->fetch(PDO::FETCH_ASSOC); // Jika score periode ini belum 100, update menjadi 100 if (!$check_row || (int)($check_row[$fields['score']] ?? 0) < 100) { $update_query = "UPDATE t_inputan_milestone_verificator SET {$fields['score']} = 100, {$fields['nama']} = :nama, {$fields['tanggal']} = :tanggal, {$fields['updated_at']} = :updated_at WHERE id = :id"; $db->execute($update_query, [ 'id' => $timv_id, 'nama' => $nama_verificator, 'tanggal' => $now, 'updated_at' => $now ]); } } } /** * Forward fill untuk tabel t_inputan_milestone_verificator_kl_instansi * Jika score periode sekarang = 100, update semua instansi di periode setelahnya = 100 */ private function updateForwardFilledInstansiPeriods($db, $timv_id, $current_periode, $milestone_id) { $periods_order = ['B03', 'B06', 'B09', 'B12', 'B15', 'B18', 'B21', 'B24']; $current_index = array_search($current_periode, $periods_order); if ($current_index === false || $current_index >= count($periods_order) - 1) { return; // Tidak ada periode setelahnya } // ✅ CEK SCORE: Pastikan score periode sekarang = 100 dulu sebelum forward fill $fields = $this->getPeriodFields($current_periode); $query = "SELECT {$fields['score']} as current_score FROM t_inputan_milestone_verificator WHERE id = :id"; $result = $db->query($query, ['id' => $timv_id]); $row = $result->fetch(PDO::FETCH_ASSOC); if (!$row || (int)$row['current_score'] !== 100) { return; // 🛑 Tidak perlu forward fill jika score tidak = 100 } $now = $this->now(); for ($i = $current_index + 1; $i < count($periods_order); $i++) { $next_periode = $periods_order[$i]; // Dapatkan semua t_instansi_milestone untuk milestone & periode ini $query = "SELECT tim.id FROM t_instansi_milestone tim WHERE tim.m_kl_milestone_id = :milestone_id AND tim.periode_milestone = :periode"; $results = $db->query($query, [ 'milestone_id' => $milestone_id, 'periode' => $next_periode ]); $tim_records = $results->fetchAll(PDO::FETCH_OBJ); foreach ($tim_records as $tim) { // Cek apakah sudah ada record di t_inputan_milestone_verificator_kl_instansi $check_query = "SELECT id, score FROM t_inputan_milestone_verificator_kl_instansi WHERE t_inputan_milestone_verificator_id = :timv_id AND t_instansi_milestone_id = :tim_id"; $check_result = $db->query($check_query, [ 'timv_id' => $timv_id, 'tim_id' => $tim->id ]); $existing = $check_result->fetch(PDO::FETCH_ASSOC); if ($existing) { // Update jika score belum 100 if ((int)$existing['score'] < 100) { $update_query = "UPDATE t_inputan_milestone_verificator_kl_instansi SET score = 100, updated_at = :updated_at WHERE id = :id"; $db->execute($update_query, [ 'id' => $existing['id'], 'updated_at' => $now ]); } } else { // Insert baru dengan score=100 $insert_query = "INSERT INTO t_inputan_milestone_verificator_kl_instansi (t_inputan_milestone_verificator_id, t_instansi_milestone_id, score, updated_at) VALUES (:timv_id, :tim_id, 100, :updated_at)"; $db->execute($insert_query, [ 'timv_id' => $timv_id, 'tim_id' => $tim->id, 'updated_at' => $now ]); } } } } private function convertKurungKurawalMenjadiKurungSiku($value){ $resultWithoutBraces = trim($value, '{}'); $resultWithSquareBrackets = str_replace('"', '', $resultWithoutBraces); return $resultWithSquareBrackets; } private function now() { $dt = new DateTime(); $dt->setTimezone(new DateTimeZone('Asia/Jakarta')); $dt->setTimestamp(round(microtime(true))); return $dt->format('Y-m-d H:i:s'); } public function submitVerifMilestone(){ $this->checkIsAuthenticated(); $db = $this->getDI()->get('databaseNewStranas2025'); $getParams = $this->request->getJsonRawBody(true); $periode_milestone = $getParams["periode"]; $username = $getParams["username"]; $keterangan_verifikator = $getParams['keterangan_verifikator'] ?? null; $score = $getParams["score"] ?? null; $id = $getParams['m_kl_milestone_id']; $timv_id = $getParams['timv_id'] ?? null; $attachment = $getParams['attachments']; $db->begin(); try { $user = $this->shared->user; $umId = $user->um_id; $namaUser = $this->CheckNamaUser($umId); // ===== STEP 1: FETCH CURRENT SCORES ===== // Ambil semua score yang sudah ada di database SEBELUM insert/update $current_scores = $this->fetchCurrentScores($db, $id); // ===== STEP 2: APPLY FORWARD FILL LOGIC ===== // Apply forward fill ke score baru yang akan di-insert // Jika ada score sebelumnya = 100, paksa score baru = 100 juga $score = $this->applyForwardFillLogic($current_scores, $periode_milestone, $score); // ===== STEP 3: INSERT/UPDATE KE DATABASE ===== $placeholders_t_milestone = []; $values_attachment = []; $counter = 1; // Map periode ke field yang sesuai $period_fields = $this->getPeriodFields($periode_milestone); if($timv_id == null){ // INSERT baru $query = "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, {$period_fields['keterangan']}, {$period_fields['nama']}, {$period_fields['score']}, {$period_fields['tanggal']}, {$period_fields['updated_at']}) VALUES (:m_kl_milestone_id, :keterangan_verificator, :nama_verificator, :score, :tanggal_verif, :updated_at)"; $values['m_kl_milestone_id'] = $id; $values['keterangan_verificator'] = $keterangan_verifikator; $values['nama_verificator'] = $namaUser[0]['nama']; $values['score'] = $score; $values['tanggal_verif'] = $this->now(); $values['updated_at'] = $this->now(); $db->execute($query, $values); $lastInsertedId = $db->lastInsertId(); $timv_id = $lastInsertedId; // Simpan untuk digunakan nanti // Insert attachments jika ada if(count($attachment) != 0){ $this->insertAttachments($db, $attachment, $lastInsertedId, $periode_milestone, $namaUser[0]['nama']); } } else { // UPDATE existing $query = "UPDATE t_inputan_milestone_verificator SET {$period_fields['keterangan']} = :keterangan_verificator, {$period_fields['nama']} = :nama_verificator, {$period_fields['score']} = :score, {$period_fields['tanggal']} = :tanggal_verif, {$period_fields['updated_at']} = :updated_at WHERE id = :id"; $values['id'] = $timv_id; $values['keterangan_verificator'] = $keterangan_verifikator; $values['nama_verificator'] = $namaUser[0]['nama']; $values['score'] = $score; $values['tanggal_verif'] = $this->now(); $values['updated_at'] = $this->now(); $db->execute($query, $values); // Insert attachments jika ada if(count($attachment) != 0){ $this->insertAttachments($db, $attachment, $timv_id, $periode_milestone, $namaUser[0]['nama']); } } // ===== STEP 4: UPDATE FORWARD FILLED PERIODS ===== // Setelah insert/update periode ini, forward fill ke periode-periode setelahnya $this->updateForwardFilledPeriods($db, $timv_id, $periode_milestone, $namaUser[0]['nama']); // ===== STEP 5: SUBMIT INSTANSI SCORES (dengan forward fill per-instansi) ===== // Forward fill sekarang dilakukan PER INSTANSI: // - Jika Instansi A score=100 → forward fill Instansi A di periode setelahnya // - Jika Instansi B score=80 → TIDAK forward fill Instansi B if ($periode_milestone == "B03") $instansi_scores = $getParams['instansi_score_b03']; else if ($periode_milestone == "B06") $instansi_scores = $getParams['instansi_score_b06']; else if ($periode_milestone == "B09") $instansi_scores = $getParams['instansi_score_b09']; else if ($periode_milestone == "B12") $instansi_scores = $getParams['instansi_score_b12']; else if ($periode_milestone == "B15") $instansi_scores = $getParams['instansi_score_b15']; else if ($periode_milestone == "B18") $instansi_scores = $getParams['instansi_score_b18']; else if ($periode_milestone == "B21") $instansi_scores = $getParams['instansi_score_b21']; else $instansi_scores = $getParams['instansi_score_b24']; $db = $this->submitVerifMilestoneInstansi($db, $timv_id, $instansi_scores, $periode_milestone, $id); $db->commit(); return new SimpleResponse([ 'success' => true, 'message' => "Sukses Insert Kedalam Database", 'applied_forward_fill' => $score == $getParams["score"] ? false : true ]); } catch (Exception $e) { $db->rollback(); return new SimpleResponse([ 'success' => false, 'Message' => "Gagal Mengupdate Data " . $e->getMessage() ]); } } public function CheckTanggalPengisian(){ $this->checkIsAuthenticated(); $dbJaga = $this->getDI()->get('db'); // $getParams = $this->request->getJsonRawBody(true); $dbJaga->begin(); try { $query = "SELECT * FROM str_periode_triwulan WHERE id_periode = 3 ORDER BY id_triwulan ASC"; $result = $dbJaga->query($query, null); // Menggunakan fetchAll untuk mendapatkan hasil query $data = $result->fetchAll(PDO::FETCH_ASSOC); $dbJaga->commit(); if (!empty($data)) { return new SimpleResponse($data); } else { return new SimpleResponse(["message" => "Tidak ada hasil"]); } }catch(Exception $e){ $dbJaga->rollback(); return new SimpleResponse([ 'Message' => "Gagal Menampilkan Data " . $e->getMessage() ]); } } private function CheckNamaUser($username){ $dbJaga = $this->getDI()->get('db'); $dbJaga->begin(); try { $query = "SELECT nama FROM um_user WHERE um_id = :um_id"; $result = $dbJaga->query($query, [ 'um_id' => $username ]); // Menggunakan fetchAll untuk mendapatkan hasil query $data = $result->fetchAll(PDO::FETCH_ASSOC); $dbJaga->commit(); if (!empty($data)) { return $data; } else { return null; } }catch(Exception $e){ $dbJaga->rollback(); return new SimpleResponse([ 'Message' => "Gagal Menampilkan Data" . $e->getMessage() ]); } } public function searchVerifMilestoneInstansi($db, $milestoneId, $periode) { $dataVerifInstansi = []; $query = "select tim.id as tim_id, tim.m_kl_milestone_id, mkm.nama_milestone, tim.m_instansi_id, mi.nama_instansi, timvki.score, timvki.id as timvki_id from stranas.t_instansi_milestone tim left join stranas.t_inputan_milestone_verificator_kl_instansi timvki on timvki.t_instansi_milestone_id = tim.id left join stranas.t_inputan_milestone_verificator timv on timvki.t_inputan_milestone_verificator_id = timv.id left join stranas.m_kl_milestone mkm on tim.m_kl_milestone_id = mkm.id left join stranas.m_instansi mi on tim.m_instansi_id = mi.id where tim.periode_milestone = :periode_milestone and tim.m_kl_milestone_id = :milestone_id order by mi.nama_instansi"; $results = $db->query($query, ['milestone_id' => $milestoneId, 'periode_milestone' => $periode]); $instansiScore = $results->fetchAll(PDO::FETCH_OBJ); for($k = 0; $k < sizeof($instansiScore); $k++){ array_push($dataVerifInstansi, [ 'instansi_id' => $instansiScore[$k]->m_instansi_id, 'nama_instansi' => $instansiScore[$k]->nama_instansi, 'periode_milestone' => $periode, 'milestone_id' => $milestoneId, 'timvki_id' => $instansiScore[$k]->timvki_id, 'tim_id' => $instansiScore[$k]->tim_id, 'score' => $instansiScore[$k]->score, ]); } return $dataVerifInstansi; } public function submitVerifMilestoneInstansi($db, $timv_id, $instansi_scores, $current_periode = null, $milestone_id = null) { $periods_order = ['B03', 'B06', 'B09', 'B12', 'B15', 'B18', 'B21', 'B24']; $current_index = array_search($current_periode, $periods_order); foreach ($instansi_scores as $ins) { $score = $ins['score'] ? (float)$ins['score'] : 0; $timvki_id = $ins['timvki_id'] ? (int)$ins['timvki_id'] : null; $tim_id = $ins['tim_id'] ? (int)$ins['tim_id'] : null; // Simpan score instansi saat ini $values = []; if ($timvki_id){ $query = "UPDATE t_inputan_milestone_verificator_kl_instansi SET score = :score, updated_at = :updated_at WHERE id = :timvki_id"; $values['timvki_id'] = $timvki_id; $values['score'] = $score; $values['updated_at'] = $this->now(); $db->execute($query, $values); } else { $query= "INSERT INTO t_inputan_milestone_verificator_kl_instansi (t_inputan_milestone_verificator_id, t_instansi_milestone_id, score, updated_at) VALUES (:timv_id, :tim_id, :score, :updated_at)"; $values['timv_id'] = $timv_id; $values['tim_id'] = $tim_id; $values['score'] = $score; $values['updated_at'] = $this->now(); $db->execute($query, $values); } // ===== FORWARD FILL PER-INSTANSI ===== // Jika score instansi ini = 100, forward fill instansi yg SAMA di periode setelahnya // Contoh: Instansi A submit B18=100 → Instansi A di B21 & B24 otomatis = 100 // Instansi B submit B18=80 → Instansi B di B21 & B24 TIDAK ikut berubah if ($score === 100.0 && $current_index !== false && $current_index < count($periods_order) - 1) { $now = $this->now(); // Cari m_instansi_id dari tim_id (t_instansi_milestone) saat ini $query_instansi = "SELECT m_instansi_id FROM t_instansi_milestone WHERE id = :tim_id"; $result_instansi = $db->query($query_instansi, ['tim_id' => $tim_id]); $tim_row = $result_instansi->fetch(PDO::FETCH_ASSOC); $m_instansi_id = $tim_row ? $tim_row['m_instansi_id'] : null; if ($m_instansi_id) { // Forward fill untuk semua periode setelahnya, untuk instansi yg SAMA for ($i = $current_index + 1; $i < count($periods_order); $i++) { $next_periode = $periods_order[$i]; // Cari tim (t_instansi_milestone) untuk instansi yg sama di periode berikutnya $query_next_tim = "SELECT id FROM t_instansi_milestone WHERE m_kl_milestone_id = :milestone_id AND m_instansi_id = :instansi_id AND periode_milestone = :periode LIMIT 1"; $result_next_tim = $db->query($query_next_tim, [ 'milestone_id' => $milestone_id, 'instansi_id' => $m_instansi_id, 'periode' => $next_periode ]); $next_tim = $result_next_tim->fetch(PDO::FETCH_ASSOC); if ($next_tim) { // Cek apakah sudah ada record di t_inputan_milestone_verificator_kl_instansi $check_query = "SELECT id, score FROM t_inputan_milestone_verificator_kl_instansi WHERE t_inputan_milestone_verificator_id = :timv_id AND t_instansi_milestone_id = :tim_id"; $check_result = $db->query($check_query, [ 'timv_id' => $timv_id, 'tim_id' => $next_tim['id'] ]); $existing = $check_result->fetch(PDO::FETCH_ASSOC); if ($existing) { // Update jika score belum 100 if ((int)$existing['score'] < 100) { $update_query = "UPDATE t_inputan_milestone_verificator_kl_instansi SET score = 100, updated_at = :updated_at WHERE id = :id"; $db->execute($update_query, [ 'id' => $existing['id'], 'updated_at' => $now ]); } } else { // Insert baru dengan score=100 $insert_query = "INSERT INTO t_inputan_milestone_verificator_kl_instansi (t_inputan_milestone_verificator_id, t_instansi_milestone_id, score, updated_at) VALUES (:timv_id, :tim_id, 100, :updated_at)"; $db->execute($insert_query, [ 'timv_id' => $timv_id, 'tim_id' => $next_tim['id'], 'updated_at' => $now ]); } } } } } } return $db; } }