checkIsAuthenticated(); $db = $this->getDI()->get('databaseNewStranas2024'); // 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) 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) 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) 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) 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) 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; } $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() ]); } } private function convertKurungKurawalMenjadiKurungSiku($value){ // Menghapus kurung kurawal dari string $resultWithoutBraces = trim($value, '{}'); // Mengganti tanda kurung kurawal menjadi kurung siku $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('databaseNewStranas2024'); $getParams = $this->request->getJsonRawBody(true); $periode_milestone = $getParams["periode"]; $username = $getParams["username"]; $keterangan_verifikator = $getParams['keterangan_verifikator'] ?? null; $score = $getParams["score"] ?? null; // dapatnya dari sini t_instansi_milestone_id -> searchLogframeMilestone() -> FrontEnd -> getParams $id = $getParams['m_kl_milestone_id']; $timv_id = $getParams['timv_id'] ?? null; // $nama_instansi = $getParams['instansi']; $attachment = $getParams['attachments']; $db->begin(); try { $user = $this->shared->user; $umId = $user->um_id; $namaUser = $this->CheckNamaUser($umId); $placeholders_t_milestone = []; $values_attachment = []; $counter = 1; if($periode_milestone == "B03"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b03, nama_verificator_b03, score_b03, tanggal_verif_b03, updated_at_b03) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b03 = :keterangan_verificator, nama_verificator_b03 = :nama_verificator, score_b03 = :score, tanggal_verif_b03 = :tanggal_verif, updated_at_b03 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } }else if($periode_milestone == "B06"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b06, nama_verificator_b06, score_b06, tanggal_verif_b06, updated_at_b06) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b06 = :keterangan_verificator, nama_verificator_b06 = :nama_verificator, score_b06 = :score, tanggal_verif_b06 = :tanggal_verif, updated_at_b06 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } }else if($periode_milestone == "B09"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b09, nama_verificator_b09, score_b09, tanggal_verif_b09, updated_at_b09) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b09 = :keterangan_verificator, nama_verificator_b09 = :nama_verificator, score_b09 = :score, tanggal_verif_b09 = :tanggal_verif, updated_at_b09 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } }else if($periode_milestone == "B12"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b12, nama_verificator_b12, score_b12, tanggal_verif_b12, updated_at_b12) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b12 = :keterangan_verificator, nama_verificator_b12 = :nama_verificator, score_b12 = :score, tanggal_verif_b12 = :tanggal_verif, updated_at_b12 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } }else if($periode_milestone == "B15"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b15, nama_verificator_b15, score_b15, tanggal_verif_b15, updated_at_b15) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b15 = :keterangan_verificator, nama_verificator_b15 = :nama_verificator, score_b15 = :score, tanggal_verif_b15 = :tanggal_verif, updated_at_b15 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } }else if($periode_milestone == "B18"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b18, nama_verificator_b18, score_b18, tanggal_verif_b18, updated_at_b18) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b18 = :keterangan_verificator, nama_verificator_b18 = :nama_verificator, score_b18 = :score, tanggal_verif_b18 = :tanggal_verif, updated_at_b18 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } }else if($periode_milestone == "B21"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b21, nama_verificator_b21, score_b21, tanggal_verif_b21, updated_at_b21) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b21 = :keterangan_verificator, nama_verificator_b21 = :nama_verificator, score_b21 = :score, tanggal_verif_b21 = :tanggal_verif, updated_at_b21 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } }else if($periode_milestone == "B24"){ if($getParams['timv_id'] == null){ $query= "INSERT INTO t_inputan_milestone_verificator (m_kl_milestone_id, keterangan_verificator_b24, nama_verificator_b24, score_b24, tanggal_verif_b24, updated_at_b24) 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); if(count($attachment) != 0){ $lastInsertedId = $db->lastInsertId(); for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); // Insert to t_inputan_milestone_verificator $counter++; } $query_attachment = "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_t_milestone) . " "; $db->execute($query_attachment, $values_attachment); } }else{ $query = "UPDATE t_inputan_milestone_verificator SET keterangan_verificator_b24 = :keterangan_verificator, nama_verificator_b24 = :nama_verificator, score_b24 = :score, tanggal_verif_b24 = :tanggal_verif, updated_at_b24 = :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); if(count($attachment) != 0){ for($i=0; $inow(); $values_attachment[":updated_at{$counter}"] = $this->now(); $counter++; } $query_attachment = "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_t_milestone) . ""; $db->execute($query_attachment, $values_attachment); } } } $db->commit(); return new SimpleResponse([ 1 => "Sukses Insert Kedalam Database" ]); } catch (Exception $e) { // Rollback transaksi jika terjadi kesalahan $db->rollback(); return new SimpleResponse([ '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() ]); } } }