<?php
use Phalcon\Validation;
use Phalcon\Validation\Validator\Numericality;
use Phalcon\Validation\Validator\PresenceOf;

class StranaspkNewFormInputController extends BaseController {
    

    public function upload_excel(){
        // $validator = new Validation();
        $this->checkIsAuthenticated();
        $getParams = $this->request->getJsonRawBody(true);

        $results = $this->CheckValidator($getParams);
        $insertIntoDatabaseAksi = $this->SaveToDataBaseAksi($results);
        $insertIntoDatabaseOutput = $this->SaveToDataBaseOutput($results);
        $insertIntoDatabaseMilestone = $this->SaveToDataBaseMilestone($results);
        $insertIntoDatabaseInstansi = $this->SaveToDataBaseInstansi($results);
        $insertIntoDatabaseInstansiMilestone = $this->SaveToDataBaseInstansiMilestone($results);
        
        return new SimpleResponse([
            1 => $insertIntoDatabaseAksi,
            2 => $insertIntoDatabaseOutput,
            3 => $insertIntoDatabaseMilestone,
            4 => $insertIntoDatabaseInstansi,
            5 => $insertIntoDatabaseInstansiMilestone
        ]);
    }


    private function SaveToDataBaseAksi($value){
         $db = $this->getDI()->get('databaseNewStranas2024');
         // Mulai transaksi
         $db->begin();

         try {
            // aksi
            $placeholders_m_kl_aksi = [];
            $values_aksi = [];
              
            $counter = 1;
            for($i=0; $i<sizeof($value["result"]); $i++){
                // aksi
                $placeholders_m_kl_aksi[] = "(:id{$counter}, :nama_aksi{$counter}, :urutan_aksi{$counter}, :created_at{$counter}, :updated_at{$counter})";

                $values_aksi[":id{$counter}"] = $value["result"][$i]['Column3'];
                $values_aksi[":nama_aksi{$counter}"] = $value["result"][$i]['Column4'];
                $values_aksi[":urutan_aksi{$counter}"] = $value["result"][$i]['Column3'];
                $values_aksi[":created_at{$counter}"] = $this->now();
                $values_aksi[":updated_at{$counter}"] = $this->now();
                $counter++;
            }
            
            $result = $db->execute("INSERT INTO m_kl_aksi (id, nama_aksi, urutan_aksi, created_at, updated_at) VALUES " . implode(", ", $placeholders_m_kl_aksi) . " ON CONFLICT (nama_aksi, urutan_aksi) DO NOTHING", $values_aksi);

            // Commit transaksi
            $db->commit();
            return ["instansi" => "berhasil memasukan kedalam database"];
         } catch (Exception $e) {
             // Rollback transaksi jika terjadi kesalahan
            $db->rollback();
             
            return new SimpleResponse([
                    'Message' => "Gagal memasukkan data ke dalam database. Kesalahan: " . $e->getMessage()
            ]);
         }
    }

    private function SaveToDatabaseOutput($value){
        $db = $this->getDI()->get('databaseNewStranas2024');
        // Mulai transaksi
        $db->begin();

        try{
            // output
            $placeholders_m_kl_output = [];
            $values_output = [];
             
            $counter = 1;
            for($i=0; $i<sizeof($value["result"]); $i++){
                    //output
                    $placeholders_m_kl_output[] = "(:m_kl_aksi_id{$counter}, :nama_output{$counter}, :urutan_output{$counter}, :created_at{$counter}, :updated_at{$counter})";

                    $values_output[":m_kl_aksi_id{$counter}"] = $value["result"][$i]["Column3"];
                    $values_output[":nama_output{$counter}"] = $value["result"][$i]['Column6'];
                    $values_output[":urutan_output{$counter}"] = $value["result"][$i]['Column5'];
                    $values_output[":created_at{$counter}"] = $this->now();
                    $values_output[":updated_at{$counter}"] = $this->now();
                    $counter++;
            }

            $query= "INSERT INTO m_kl_output (m_kl_aksi_id, nama_output, urutan_output, created_at, updated_at) VALUES" . implode(", ", $placeholders_m_kl_output)." ON CONFLICT (nama_output, urutan_output) DO NOTHING";

            $result = $db->execute($query, $values_output);
            $db->commit();
            return ["instansi" => "berhasil memasukan kedalam database"];
        } catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();
            
            return new SimpleResponse([
                    'Message' => "Gagal memasukkan data ke dalam database. Kesalahan: " . $e->getMessage()
            ]);
        }

    }

    private function SaveToDatabaseMilestone($value){
        $db = $this->getDI()->get('databaseNewStranas2024');
        
        // Mulai transaksi
        $db->begin();

        try{
            // milestone
            $placeholders_m_kl_milestone = [];

            $values = [];
            $counter = 1; 
            for($i=0; $i<sizeof($value["result"]); $i++){
                
                $placeholders_m_kl_milestone[] = "((SELECT id FROM m_kl_output WHERE nama_output = :nama_output{$counter} AND urutan_output = :urutan_output{$counter}), :nama_milestone{$counter}, :urutan_milestone{$counter}, :nama_fokus{$counter}, :urutan_fokus{$counter}, :created_at{$counter}, :updated_at{$counter})";
                
                // milestone
                $values[":nama_output{$counter}"] = $value["result"][$i]["Column6"];
                $values[":urutan_output{$counter}"] = $value["result"][$i]["Column5"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_fokus{$counter}"] = $value["result"][$i]["Column2"];
                $values[":urutan_fokus{$counter}"] = $value["result"][$i]["Column1"];
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();
                $counter++;  
            }

            $query= "INSERT INTO m_kl_milestone (m_kl_output_id, nama_milestone, urutan_milestone, nama_fokus, urutan_fokus, created_at, updated_at) VALUES" . implode(", ", $placeholders_m_kl_milestone)." ON CONFLICT (nama_milestone, urutan_milestone) DO NOTHING";

            $db->execute($query, $values);   
            $db->commit();

            return ["instansi" => "berhasil memasukan kedalam database"];
        } catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();
            
            return new SimpleResponse([
                    'Message' => "Gagal memasukkan data ke dalam database. Kesalahan: " . $e->getMessage()
            ]);
        } 
    }

    private function SaveToDatabaseInstansi($value){
        $db = $this->getDI()->get('databaseNewStranas2024');
        
        // Mulai transaksi
        $db->begin();

        try{
            // milestone
            $placeholders_m_instansi= [];

            $values = [];
            $counter = 1; 
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi[] = "(:nama_instansi{$counter}, :urutan_instansi{$counter}, :nama_pendek_instansi{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":nama_pendek_instansi{$counter}"] = $value["result"][$i]["Column11"];
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now(); 
                $counter++;
            }

            $query= "INSERT INTO m_instansi (nama_instansi, urutan_instansi, nama_pendek_instansi, created_at, updated_at) VALUES" . implode(", ", $placeholders_m_instansi)." ON CONFLICT (nama_instansi, urutan_instansi) DO NOTHING";

            $db->execute($query, $values);   
            $db->commit();

            return ["instansi" => "berhasil memasukan kedalam database"];
        } catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();
            
            return new SimpleResponse([
                    'Message' => "Gagal memasukkan data ke dalam database. Kesalahan: " . $e->getMessage()
            ]);
        } 
    }

    private function SaveToDatabaseInstansiMilestone($value){
        $db = $this->getDI()->get('databaseNewStranas2024');
        
        // Mulai transaksi
        $db->begin();

        try{
            // milestone
            $placeholders_m_instansi_milestone= [];

            $values = [];
            $counter = 1;
            
            // B03
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B03";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $query= "INSERT INTO t_instansi_milestone (m_kl_milestone_id, m_instansi_id, periode_milestone, created_at, updated_at) VALUES" . implode(", ", $placeholders_m_instansi_milestone)."";

            $db->execute($query, $values);
            
            $values = [];
            $counter = 1;
            // B06
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B06";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $db->execute($query, $values);

            $values = [];
            $counter = 1;
            // B09
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B09";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $db->execute($query, $values);

            $values = [];
            $counter = 1;
            // B12
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B12";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $db->execute($query, $values);

            $values = [];
            $counter = 1;
            // B15
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B15";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $db->execute($query, $values);

            $values = [];
            $counter = 1;
            // B18
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B18";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $db->execute($query, $values);

            $values = [];
            $counter = 1;
            // B21
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B21";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $db->execute($query, $values);

            $values = [];
            $counter = 1;
            // B24
            for($i=0; $i<sizeof($value["result"]); $i++){   
                $placeholders_m_instansi_milestone[] = "((SELECT id FROM m_kl_milestone WHERE urutan_milestone = :urutan_milestone{$counter} AND nama_milestone = :nama_milestone{$counter}), (SELECT id FROM m_instansi WHERE nama_instansi = :nama_instansi{$counter} AND urutan_instansi = :urutan_instansi{$counter}), :periode_milestone{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // milestone
                $values[":urutan_milestone{$counter}"] = $value["result"][$i]["Column7"];
                $values[":nama_milestone{$counter}"] = $value["result"][$i]["Column8"];
                $values[":nama_instansi{$counter}"] = $value["result"][$i]["Column10"];
                $values[":urutan_instansi{$counter}"] = $value["result"][$i]["Column9"];
                $values[":periode_milestone{$counter}"] = "B24";
                $values[":created_at{$counter}"] = $this->now();
                $values[":updated_at{$counter}"] = $this->now();  
                $counter++;
            }

            $db->execute($query, $values);

            $db->commit();

            return ["instansi" => "berhasil memasukan kedalam database"];
        } catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();
            
            return new SimpleResponse([
                    'Message' => "Gagal memasukkan data ke dalam database. Kesalahan: " . $e->getMessage()
            ]);
        } 
    }

    private function CheckValidator($value){
        $errors = [];
        // $columnWajibInteger = [1,3,5,7,18,25,32,38,46,53,60,67,68,77,78,79,80,81,82,83,84];
        $columnWajibInteger = ['Column1','Column3','Column5','Column7'];
        // $columnWajibTanggal = [13,16,20,23,27,30,34,37,41,44,48,51,55,58,62,65,93,94,95,96,97,98,99,100];
        $columnWajibIsi = ['Column1', 'Column2', 'Column3', 'Column4', 'Column5', 'Column6', 'Column7', 'Column8', 'Column9'];
        
        foreach ($value as $indexValue => $row) {
            $validator = new Validation();
            foreach($row as $index => $column){
                
                if (in_array($index, $columnWajibIsi)) {
                    // Validasi tipe integer
                    $validator->add(
                        $index,
                        new PresenceOf([
                            'message' => $index . ' tidak boleh kosong',
                        ])
                    );
                }

                if (in_array($index, $columnWajibInteger)) {
                    // Validasi tipe integer
                    $validator->add(
                        $index,
                        new Numericality([
                            'message' => $index . ' harus berupa Angka',
                        ])
                    );
                }

                // if (in_array($index, $columnWajibTanggal)) {
                //     // Validasi tipe datetime
                //     $validator->add(
                //         $index,
                //         new Date([
                //             'message' => $index . ' harus berupa tanggal dan waktu',
                //         ])
                //     );
                // }
                

            }

            $messages = $validator->validate($row);
                // Kumpulkan pesan kesalahan jika ada
                if (count($messages)) {
                    // Tambahan: Tampilkan pesan kesalahan secara terperinci
                    foreach ($messages as $message) {
                        $errors[] = [
                            'baris' => $indexValue+1,
                            'errors' => $message->getMessage() .' (' . $message->getField() . ')',
                        ];
                    }
                }
        }

        // Tampilkan hasil validasi
        if (!empty($errors)) {
            // Ada kesalahan, kembalikan pesan kesalahan dalam format JSON
            return [
                'status_code' => 400,
                'result' => $errors,
                'status' => false
            ];
        } else {
            // Tidak ada kesalahan, lakukan tindakan selanjutnya
    
            // Contoh tanggapan JSON jika validasi berhasil
            return [
                'status_code' => 200,
                'result' => $value,
                'status' => true
            ];
        }
    }

    public function searchLogframeMilestone()
    {
        $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";
        $nama_instansi = $getParams["nama_instansi"];
        
        $db->begin();
       
        try {
            // $nama_instansi = "Kementerian Dalam Negeri";
            // bila optionnya aksi
            if($getParams["options"] == "aksi"){
                if($getParams["keyword"] == "all"){
                    $query="SELECT mka.nama_aksi, mko.nama_output, mkm.nama_milestone, tim.periode_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, (SELECT nama_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b03, (SELECT nama_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b06, (SELECT nama_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b09, (SELECT nama_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b12, (SELECT nama_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b15, (SELECT nama_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b18, (SELECT nama_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b21, (SELECT nama_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b24, (SELECT tanggal_verif_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b03, (SELECT tanggal_verif_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b06, (SELECT tanggal_verif_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b09, (SELECT tanggal_verif_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b12, (SELECT tanggal_verif_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b15, (SELECT tanggal_verif_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b18, (SELECT tanggal_verif_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b21, (SELECT tanggal_verif_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_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, (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, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B03' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b03, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B06' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b06, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B09' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b09, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B12' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b12, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B15' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b15, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B18' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b18, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B21' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b21, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B24' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) 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 ARRAY_AGG(array_of_attachment_b03) 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 ARRAY_AGG(array_of_attachment_b06) 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 ARRAY_AGG(array_of_attachment_b09) 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 ARRAY_AGG(array_of_attachment_b12) 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 ARRAY_AGG(array_of_attachment_b15) 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 ARRAY_AGG(array_of_attachment_b18) 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 ARRAY_AGG(array_of_attachment_b21) 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 = 'B03') SELECT ARRAY_AGG(array_of_attachment_b24) AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24, (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.id AND periode_milestone = 'B03') SELECT ARRAY_AGG(array_of_attachment_instansi_b03) AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_instansi_b03, (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.id AND periode_milestone = 'B06') SELECT ARRAY_AGG(array_of_attachment_instansi_b06) AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_instansi_b06, (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.id AND periode_milestone = 'B09') SELECT ARRAY_AGG(array_of_attachment_instansi_b09) AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_instansi_b09, (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.id AND periode_milestone = 'B12') SELECT ARRAY_AGG(array_of_attachment_instansi_b12) AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_instansi_b12, (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.id AND periode_milestone = 'B15') SELECT ARRAY_AGG(array_of_attachment_instansi_b15) AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_instansi_b15, (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.id AND periode_milestone = 'B18') SELECT ARRAY_AGG(array_of_attachment_instansi_b18) AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_instansi_b18, (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.id AND periode_milestone = 'B21') SELECT ARRAY_AGG(array_of_attachment_instansi_b21) AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_instansi_b21, (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.id AND periode_milestone = 'B24') SELECT ARRAY_AGG(array_of_attachment_instansi_b24) AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_instansi_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 tim.id ASC";
                    
                }else{
                    $query="SELECT mka.nama_aksi, mko.nama_output, mkm.nama_milestone, tim.periode_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, (SELECT nama_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b03, (SELECT nama_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b06, (SELECT nama_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b09, (SELECT nama_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b12, (SELECT nama_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b15, (SELECT nama_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b18, (SELECT nama_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b21, (SELECT nama_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b24, (SELECT tanggal_verif_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b03, (SELECT tanggal_verif_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b06, (SELECT tanggal_verif_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b09, (SELECT tanggal_verif_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b12, (SELECT tanggal_verif_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b15, (SELECT tanggal_verif_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b18, (SELECT tanggal_verif_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b21, (SELECT tanggal_verif_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_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, (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, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B03' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b03, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B06' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b06, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B09' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b09, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B12' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b12, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B15' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b15, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B18' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b18, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B21' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b21, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B24' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) 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 ARRAY_AGG(array_of_attachment_b03) 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 ARRAY_AGG(array_of_attachment_b06) 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 ARRAY_AGG(array_of_attachment_b09) 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 ARRAY_AGG(array_of_attachment_b12) 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 ARRAY_AGG(array_of_attachment_b15) 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 ARRAY_AGG(array_of_attachment_b18) 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 ARRAY_AGG(array_of_attachment_b21) 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 = 'B03') SELECT ARRAY_AGG(array_of_attachment_b24) AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24, (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.id AND periode_milestone = 'B03') SELECT ARRAY_AGG(array_of_attachment_instansi_b03) AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_instansi_b03, (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.id AND periode_milestone = 'B06') SELECT ARRAY_AGG(array_of_attachment_instansi_b06) AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_instansi_b06, (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.id AND periode_milestone = 'B09') SELECT ARRAY_AGG(array_of_attachment_instansi_b09) AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_instansi_b09, (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.id AND periode_milestone = 'B12') SELECT ARRAY_AGG(array_of_attachment_instansi_b12) AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_instansi_b12, (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.id AND periode_milestone = 'B15') SELECT ARRAY_AGG(array_of_attachment_instansi_b15) AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_instansi_b15, (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.id AND periode_milestone = 'B18') SELECT ARRAY_AGG(array_of_attachment_instansi_b18) AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_instansi_b18, (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.id AND periode_milestone = 'B21') SELECT ARRAY_AGG(array_of_attachment_instansi_b21) AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_instansi_b21, (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.id AND periode_milestone = 'B24') SELECT ARRAY_AGG(array_of_attachment_instansi_b24) AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_instansi_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 AND mka.nama_aksi ILIKE :keyword ORDER BY tim.id ASC";
                    
                } 
             // bila optionnya output
            }else if ($getParams["options"] == "output"){

                $query="SELECT mka.nama_aksi, mko.nama_output, mkm.nama_milestone, tim.periode_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, (SELECT nama_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b03, (SELECT nama_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b06, (SELECT nama_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b09, (SELECT nama_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b12, (SELECT nama_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b15, (SELECT nama_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b18, (SELECT nama_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b21, (SELECT nama_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b24, (SELECT tanggal_verif_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b03, (SELECT tanggal_verif_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b06, (SELECT tanggal_verif_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b09, (SELECT tanggal_verif_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b12, (SELECT tanggal_verif_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b15, (SELECT tanggal_verif_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b18, (SELECT tanggal_verif_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b21, (SELECT tanggal_verif_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_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, (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, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B03' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b03, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B06' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b06, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B09' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b09, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B12' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b12, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B15' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b15, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B18' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b18, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B21' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b21, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B24' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) 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 ARRAY_AGG(array_of_attachment_b03) 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 ARRAY_AGG(array_of_attachment_b06) 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 ARRAY_AGG(array_of_attachment_b09) 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 ARRAY_AGG(array_of_attachment_b12) 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 ARRAY_AGG(array_of_attachment_b15) 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 ARRAY_AGG(array_of_attachment_b18) 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 ARRAY_AGG(array_of_attachment_b21) 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 = 'B03') SELECT ARRAY_AGG(array_of_attachment_b24) AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24, (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.id AND periode_milestone = 'B03') SELECT ARRAY_AGG(array_of_attachment_instansi_b03) AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_instansi_b03, (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.id AND periode_milestone = 'B06') SELECT ARRAY_AGG(array_of_attachment_instansi_b06) AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_instansi_b06, (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.id AND periode_milestone = 'B09') SELECT ARRAY_AGG(array_of_attachment_instansi_b09) AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_instansi_b09, (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.id AND periode_milestone = 'B12') SELECT ARRAY_AGG(array_of_attachment_instansi_b12) AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_instansi_b12, (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.id AND periode_milestone = 'B15') SELECT ARRAY_AGG(array_of_attachment_instansi_b15) AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_instansi_b15, (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.id AND periode_milestone = 'B18') SELECT ARRAY_AGG(array_of_attachment_instansi_b18) AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_instansi_b18, (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.id AND periode_milestone = 'B21') SELECT ARRAY_AGG(array_of_attachment_instansi_b21) AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_instansi_b21, (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.id AND periode_milestone = 'B24') SELECT ARRAY_AGG(array_of_attachment_instansi_b24) AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_instansi_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 AND mko.nama_output ILIKE :keyword ORDER BY tim.id ASC";
             // bila optionnya target
            }else if ($getParams["options"] == "milestone"){
                $query="SELECT mka.nama_aksi, mko.nama_output, mkm.nama_milestone, tim.periode_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, (SELECT nama_verificator_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b03, (SELECT nama_verificator_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b06, (SELECT nama_verificator_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b09, (SELECT nama_verificator_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b12, (SELECT nama_verificator_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b15, (SELECT nama_verificator_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b18, (SELECT nama_verificator_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b21, (SELECT nama_verificator_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) as nama_verificator_b24, (SELECT tanggal_verif_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b03, (SELECT tanggal_verif_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b06, (SELECT tanggal_verif_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b09, (SELECT tanggal_verif_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b12, (SELECT tanggal_verif_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b15, (SELECT tanggal_verif_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b18, (SELECT tanggal_verif_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_b21, (SELECT tanggal_verif_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = mkm.id) AS tanggal_verif_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, (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, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B03' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b03, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B06' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b06, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B09' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b09, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B12' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b12, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B15' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b15, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B18' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b18, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B21' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) AS keterangan_pelapor_b21, (SELECT tim_3.keterangan_pelapor FROM m_instansi AS mi_3 LEFT JOIN t_instansi_milestone AS tim_3 ON mi_3.id = tim_3.m_instansi_id WHERE tim_3.periode_milestone = 'B24' AND tim_3.m_kl_milestone_id = mkm.id AND tim_3.m_instansi_id = mi_3.id AND mi_3.nama_instansi = :nama_instansi ) 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 ARRAY_AGG(array_of_attachment_b03) 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 ARRAY_AGG(array_of_attachment_b06) 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 ARRAY_AGG(array_of_attachment_b09) 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 ARRAY_AGG(array_of_attachment_b12) 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 ARRAY_AGG(array_of_attachment_b15) 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 ARRAY_AGG(array_of_attachment_b18) 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 ARRAY_AGG(array_of_attachment_b21) 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 = 'B03') SELECT ARRAY_AGG(array_of_attachment_b24) AS attachment_b24 FROM array_of_attachment_b24) AS attachment_b24, (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.id AND periode_milestone = 'B03') SELECT ARRAY_AGG(array_of_attachment_instansi_b03) AS attachment_instansi_b03 FROM array_of_attachment_instansi_b03) AS attachment_instansi_b03, (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.id AND periode_milestone = 'B06') SELECT ARRAY_AGG(array_of_attachment_instansi_b06) AS attachment_instansi_b06 FROM array_of_attachment_instansi_b06) AS attachment_instansi_b06, (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.id AND periode_milestone = 'B09') SELECT ARRAY_AGG(array_of_attachment_instansi_b09) AS attachment_instansi_b09 FROM array_of_attachment_instansi_b09) AS attachment_instansi_b09, (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.id AND periode_milestone = 'B12') SELECT ARRAY_AGG(array_of_attachment_instansi_b12) AS attachment_instansi_b12 FROM array_of_attachment_instansi_b12) AS attachment_instansi_b12, (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.id AND periode_milestone = 'B15') SELECT ARRAY_AGG(array_of_attachment_instansi_b15) AS attachment_instansi_b15 FROM array_of_attachment_instansi_b15) AS attachment_instansi_b15, (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.id AND periode_milestone = 'B18') SELECT ARRAY_AGG(array_of_attachment_instansi_b18) AS attachment_instansi_b18 FROM array_of_attachment_instansi_b18) AS attachment_instansi_b18, (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.id AND periode_milestone = 'B21') SELECT ARRAY_AGG(array_of_attachment_instansi_b21) AS attachment_instansi_b21 FROM array_of_attachment_instansi_b21) AS attachment_instansi_b21, (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.id AND periode_milestone = 'B24') SELECT ARRAY_AGG(array_of_attachment_instansi_b24) AS attachment_instansi_b24 FROM array_of_attachment_instansi_b24) AS attachment_instansi_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 AND mkm.nama_milestone ILIKE :keyword ORDER BY tim.id ASC";
            }
            
            // Eksekusi query dengan parameter binding
            $keyword = '%' . $getParams["keyword"] . '%';
            
            if($getParams["options"] == "aksi"){
                if($getParams["keyword"] == "all"){
                    $results = $db->query($query, ['periode_milestone' => $periode_milestone, 'nama_instansi' => $nama_instansi]);
                }else{
                    $results = $db->query($query, ['keyword' => $keyword, 'periode_milestone' => $periode_milestone, 'nama_instansi' => $nama_instansi]);
                }
            }else if($getParams["options"] == "output"){
                $results = $db->query($query, ['keyword' => $keyword, 'periode_milestone' => $periode_milestone, 'nama_instansi' => $nama_instansi]);
            }else if($getParams["options"] == "milestone"){
                $results = $db->query($query, ['keyword' => $keyword, 'periode_milestone' => $periode_milestone, 'nama_instansi' => $nama_instansi]);
            }

            $db->commit();

            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $results->fetchAll(PDO::FETCH_ASSOC);

            if (!empty($data)) {
                return new SimpleResponse($data);
            } 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 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 submitFormInputMilestone(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);

        $periode_milestone = $getParams["periode"];
        $username = $getParams["username"];

        $keterangan_lapor = $getParams['keterangan_pelapor'] ?? null;
        
        // dapatnya dari sini t_instansi_milestone_id -> searchLogframeMilestone() -> FrontEnd -> getParams
        $id = $getParams['t_instansi_milestone_id'];

        // $nama_instansi = $getParams['instansi'];
        $attachment = $getParams['attachments'];
        
        $db->begin();
       
        try {
            $user = $this->shared->user;
            $umId = $user->um_id;
            $namaUser = $this->CheckNamaUser($umId);
            $updateMilestone = "UPDATE t_instansi_milestone SET keterangan_pelapor = :keterangan_pelapor, nama_pelapor = :nama_pelapor, tanggal_laporan = :tanggal_laporan WHERE id = :id";
  
            $params = [
                'keterangan_pelapor' => $keterangan_lapor,
                'id' => $id,
                'nama_pelapor' => $namaUser[0]['nama'],
                'tanggal_laporan' => $this->now()
            ];

            $result_form_input = $db->execute($updateMilestone, $params);

            $placeholders_t_attachment_instansi = [];
            $values_attachment = [];
            $counter = 1;  
            for($i=0; $i<sizeof($attachment); $i++){
                $placeholders_t_attachment_instansi[] = "(:uuid{$counter}, :t_instansi_milestone_id{$counter}, :nama_label{$counter}, :nama_dokumen{$counter}, :ukuran_file{$counter}, :periode_milestone{$counter}, :created_by{$counter}, :created_at{$counter}, :updated_at{$counter})";
                // Insert into t_attachment_kl_verificator
                $values_attachment[":uuid{$counter}"] = $attachment[$i]["uuid"];
                $values_attachment[":t_instansi_milestone_id{$counter}"] = $id;
                $values_attachment[":nama_label{$counter}"] = $attachment[$i]["label"];
                $values_attachment[":nama_dokumen{$counter}"] = $attachment[$i]["filename"];
                $values_attachment[":ukuran_file{$counter}"] = $attachment[$i]["size"];
                $values_attachment[":periode_milestone{$counter}"] = $periode_milestone;
                $values_attachment[":created_by{$counter}"] = $namaUser[0]['nama'];
                $values_attachment[":created_at{$counter}"] = $this->now();
                $values_attachment[":updated_at{$counter}"] = $this->now();
                $counter++;
            }

            $query_attachment = "INSERT into t_attachment_kl_instansi (uuid, t_instansi_milestone_id, nama_label, nama_dokumen, ukuran_file, periode_milestone, created_by, created_at, updated_at) VALUES " . implode(", ", $placeholders_t_attachment_instansi) . "";

            if(sizeof($attachment) != 0){
                $db->execute($query_attachment, $values_attachment);
            }

            $db->commit();

            return new SimpleResponse([
                // "message1" => $result_attachment,
                2 => $result_form_input
            ]);
        } 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()
            ]);
        } 
    }

}