<?php

class StranaspkLogframeMilestone2025Controller extends BaseController
{
     public function read_milestone_instansi(){
        $this->checkIsAuthenticated();
        $getParams = $this->request->getJsonRawBody(true);
        $id_instansi = $getParams["id"];
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "SELECT
            mi.id as instansi_id,
            mi.nama_instansi,
            mka.id as aksi_id,
            mko.id as output_id,
            mkm.id as milestone_id,
            mka.urutan_aksi,
            mko.urutan_output,
            mkm.urutan_milestone,
            mkm.nama_milestone,
            mko.nama_output,
            mka.nama_aksi,
            timv.score_b03, 
            timv.score_b06, 
            timv.score_b09, 
            timv.score_b12, 
            timv.score_b15, 
            timv.score_b18, 
            timv.score_b21, 
            timv.score_b24
            from m_kl_milestone as mkm 
            left join m_kl_output as mko on mko.id = mkm.m_kl_output_id
            left join m_kl_aksi as mka on mka.id = mko.m_kl_aksi_id
            left join t_instansi_milestone as tim on tim.m_kl_milestone_id = mkm.id
            left join m_instansi as mi on mi.id = tim.m_instansi_id
            left join t_inputan_milestone_verificator as timv on timv.m_kl_milestone_id = mkm.id
            where tim.periode_milestone = 'B03' AND mi.id = :id order by mka.urutan_aksi";
            $values = [
                'id' => $id_instansi
            ];
            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        } 
     }

     public function get_data_milestone_instansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $milestone_id = $getParams['milestone_id'];
        $db->begin();
        try{
            $query = "SELECT m_instansi_id FROM t_instansi_milestone WHERE m_kl_milestone_id = :id and periode_milestone = 'B03'";
            $values = [
                'id' => $milestone_id
            ];

            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_nilai_milestone(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $milestone_id = $getParams["milestone_id"];
        $db->begin();
        try {
            $query = "SELECT * FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id";

            $values = [
                'id' => $milestone_id
            ];

            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_all_score_instansi(){ 
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        // $all_id_instansi = $getParams["id"];
        // Mengonversi array PHP menjadi string yang sesuai untuk klausa IN dalam SQL
        // Buat string placeholder dengan jumlah yang sesuai
        $placeholders = implode(', ', array_fill(0, count($getParams), '?'));
        $db->begin();
        try {
            $query = "WITH InstansiCTE AS (
                SELECT
                    id AS instansi_id,
                    nama_instansi
                FROM
                    m_instansi
                WHERE
                    m_jenis_instansi_id = 1 -- Ganti dengan nilai yang sesuai
            ),
            InstansiScores as (
            	select 
            		tim2.m_kl_milestone_id,
            		tim2.m_instansi_id,
            		MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN timvkib03.score END) AS instansi_score_b03,
            		MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN timvkib06.score END) AS instansi_score_b06,
            		MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN timvkib09.score END) AS instansi_score_b09,
            		MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN timvkib12.score END) AS instansi_score_b12,
            		MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN timvkib15.score END) AS instansi_score_b15,
            		MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN timvkib18.score END) AS instansi_score_b18,
            		MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN timvkib21.score END) AS instansi_score_b21,
            		MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN timvkib24.score END) AS instansi_score_b24
            	from t_instansi_milestone tim2 
            	left join t_inputan_milestone_verificator_kl_instansi timvkib03 on tim2.id = timvkib03.t_instansi_milestone_id and tim2.periode_milestone = 'B03'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib06 on tim2.id = timvkib06.t_instansi_milestone_id and tim2.periode_milestone = 'B06'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib09 on tim2.id = timvkib09.t_instansi_milestone_id and tim2.periode_milestone = 'B09'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib12 on tim2.id = timvkib12.t_instansi_milestone_id and tim2.periode_milestone = 'B12'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib15 on tim2.id = timvkib15.t_instansi_milestone_id and tim2.periode_milestone = 'B15'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib18 on tim2.id = timvkib18.t_instansi_milestone_id and tim2.periode_milestone = 'B18'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib21 on tim2.id = timvkib21.t_instansi_milestone_id and tim2.periode_milestone = 'B21'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib24 on tim2.id = timvkib24.t_instansi_milestone_id and tim2.periode_milestone = 'B24'
            	GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            )
            SELECT
                ic.instansi_id,
                ic.nama_instansi,
                mka.id AS aksi_id,
                mko.id AS output_id,
                mkm.id AS milestone_id,
                mka.urutan_aksi,
                mko.urutan_output,
                mkm.urutan_milestone,
                mkm.nama_milestone,
                mko.nama_output,
                mka.nama_aksi,
                mka.nama_pendek_aksi,
                timv.score_b03,
                timv.score_b06,
                timv.score_b09,
                timv.score_b12,
                timv.score_b15,
                timv.score_b18,
                timv.score_b21,
                timv.score_b24,                
                COALESCE(timv.score_b03, 0) AS new_score_b03,
                COALESCE(timv.score_b06, timv.score_b03) AS new_score_b06,
                COALESCE(timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b09,
                COALESCE(timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b12,
                COALESCE(timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b15,
                COALESCE(timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b18,
                COALESCE(timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b21,
                COALESCE(timv.score_b24, timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b24,
                COALESCE(InstansiScores.instansi_score_b03, 0) AS instansi_score_b03,
                COALESCE(InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b06,
                COALESCE(InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b09,
                COALESCE(InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b12,
                COALESCE(InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b15,
                COALESCE(InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b18,
                COALESCE(InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b21,
                COALESCE(InstansiScores.instansi_score_b24, InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b24
            FROM
                m_kl_milestone AS mkm
            LEFT JOIN
                m_kl_output AS mko ON mko.id = mkm.m_kl_output_id
            LEFT JOIN
                m_kl_aksi AS mka ON mka.id = mko.m_kl_aksi_id
            LEFT JOIN
                t_instansi_milestone AS tim ON tim.m_kl_milestone_id = mkm.id
            LEFT JOIN
                InstansiCTE AS ic ON ic.instansi_id = tim.m_instansi_id
            LEFT JOIN
                t_inputan_milestone_verificator AS timv ON timv.m_kl_milestone_id = mkm.id
            left join 
                InstansiScores ON mkm.id = InstansiScores.m_kl_milestone_id AND ic.instansi_id = InstansiScores.m_instansi_id
            WHERE
                tim.periode_milestone = 'B03' AND
                ic.instansi_id IN ($placeholders)
            ORDER BY
                mka.urutan_aksi";
                
            $result = $db->query($query, $getParams);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_all_score_instansi_2025(){ 
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        // $all_id_instansi = $getParams["id"];
        // Mengonversi array PHP menjadi string yang sesuai untuk klausa IN dalam SQL
        // Buat string placeholder dengan jumlah yang sesuai
        $placeholders = implode(', ', array_fill(0, count($getParams), '?'));
        $db->begin();
        try {
            $query = "WITH InstansiCTE AS (
                SELECT
                    id AS instansi_id,
                    nama_instansi
                FROM
                    m_instansi
                WHERE
                    m_jenis_instansi_id = 1 -- Ganti dengan nilai yang sesuai
            ),
            InstansiScores as (
            	select 
            		tim2.m_kl_milestone_id,
            		tim2.m_instansi_id,
            		MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN timvkib03.score END) AS instansi_score_b03,
            		MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN timvkib06.score END) AS instansi_score_b06,
            		MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN timvkib09.score END) AS instansi_score_b09,
            		MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN timvkib12.score END) AS instansi_score_b12,
            		MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN timvkib15.score END) AS instansi_score_b15,
            		MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN timvkib18.score END) AS instansi_score_b18,
            		MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN timvkib21.score END) AS instansi_score_b21,
            		MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN timvkib24.score END) AS instansi_score_b24
            	from t_instansi_milestone tim2 
            	left join t_inputan_milestone_verificator_kl_instansi timvkib03 on tim2.id = timvkib03.t_instansi_milestone_id and tim2.periode_milestone = 'B03'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib06 on tim2.id = timvkib06.t_instansi_milestone_id and tim2.periode_milestone = 'B06'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib09 on tim2.id = timvkib09.t_instansi_milestone_id and tim2.periode_milestone = 'B09'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib12 on tim2.id = timvkib12.t_instansi_milestone_id and tim2.periode_milestone = 'B12'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib15 on tim2.id = timvkib15.t_instansi_milestone_id and tim2.periode_milestone = 'B15'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib18 on tim2.id = timvkib18.t_instansi_milestone_id and tim2.periode_milestone = 'B18'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib21 on tim2.id = timvkib21.t_instansi_milestone_id and tim2.periode_milestone = 'B21'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib24 on tim2.id = timvkib24.t_instansi_milestone_id and tim2.periode_milestone = 'B24'
            	GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            )
            SELECT
                ic.instansi_id,
                ic.nama_instansi,
                mka.id AS aksi_id,
                mko.id AS output_id,
                mkm.id AS milestone_id,
                mka.urutan_aksi,
                mko.urutan_output,
                mkm.urutan_milestone,
                mkm.nama_milestone,
                mko.nama_output,
                mka.nama_aksi,
                mka.nama_pendek_aksi,                
                COALESCE(InstansiScores.instansi_score_b03, 0) AS new_score_b03,
                COALESCE(InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b06,
                COALESCE(InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b09,
                COALESCE(InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b12,
                COALESCE(InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b15,
                COALESCE(InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b18,
                COALESCE(InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b21,
                COALESCE(InstansiScores.instansi_score_b24, InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b24
            FROM
                m_kl_milestone AS mkm
            LEFT JOIN
                m_kl_output AS mko ON mko.id = mkm.m_kl_output_id
            LEFT JOIN
                m_kl_aksi AS mka ON mka.id = mko.m_kl_aksi_id
            LEFT JOIN
                t_instansi_milestone AS tim ON tim.m_kl_milestone_id = mkm.id
            LEFT JOIN
                InstansiCTE AS ic ON ic.instansi_id = tim.m_instansi_id
            LEFT JOIN
                t_inputan_milestone_verificator AS timv ON timv.m_kl_milestone_id = mkm.id
            left join 
                InstansiScores ON mkm.id = InstansiScores.m_kl_milestone_id AND ic.instansi_id = InstansiScores.m_instansi_id
            WHERE
                tim.periode_milestone = 'B03' AND
                ic.instansi_id IN ($placeholders)
            ORDER BY
                mka.urutan_aksi";
                
            $result = $db->query($query, $getParams);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_milestone_by_output_id_perubahan(){
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $output_id = $getParams["output_id"];
        $db->begin();
        try {
            $query = "SELECT * from m_kl_milestone where m_kl_output_id = :output_id ORDER BY urutan_milestone";
            $values = [
                'output_id' => $output_id
            ];
            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        } 
     }

     public function read_milestone(){
        $getParams = $this->request->getJsonRawBody(true);
        $output_id = $getParams["output_id"];
        $instansi_id = $getParams["instansi_id"];
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "
            WITH InstansiScores as (
            	select 
            		tim2.m_kl_milestone_id,
            		tim2.m_instansi_id,
            		MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN timvkib03.score END) AS instansi_score_b03,
            		MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN timvkib06.score END) AS instansi_score_b06,
            		MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN timvkib09.score END) AS instansi_score_b09,
            		MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN timvkib12.score END) AS instansi_score_b12,
            		MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN timvkib15.score END) AS instansi_score_b15,
            		MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN timvkib18.score END) AS instansi_score_b18,
            		MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN timvkib21.score END) AS instansi_score_b21,
            		MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN timvkib24.score END) AS instansi_score_b24
            	from t_instansi_milestone tim2 
            	left join t_inputan_milestone_verificator_kl_instansi timvkib03 on tim2.id = timvkib03.t_instansi_milestone_id and tim2.periode_milestone = 'B03'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib06 on tim2.id = timvkib06.t_instansi_milestone_id and tim2.periode_milestone = 'B06'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib09 on tim2.id = timvkib09.t_instansi_milestone_id and tim2.periode_milestone = 'B09'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib12 on tim2.id = timvkib12.t_instansi_milestone_id and tim2.periode_milestone = 'B12'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib15 on tim2.id = timvkib15.t_instansi_milestone_id and tim2.periode_milestone = 'B15'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib18 on tim2.id = timvkib18.t_instansi_milestone_id and tim2.periode_milestone = 'B18'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib21 on tim2.id = timvkib21.t_instansi_milestone_id and tim2.periode_milestone = 'B21'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib24 on tim2.id = timvkib24.t_instansi_milestone_id and tim2.periode_milestone = 'B24'
            	GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            )
            SELECT
            mi.id as instansi_id,
            mi.nama_instansi,
            mka.id as aksi_id,
            mko.id as output_id,
            mkm.id as milestone_id,
            mka.urutan_aksi,
            mko.urutan_output,
            mkm.urutan_milestone,
            mkm.nama_milestone,
            mko.nama_output,
            mka.nama_aksi,
            mka.nama_pendek_aksi,
            timv.score_b03, 
            timv.score_b06, 
            timv.score_b09, 
            timv.score_b12, 
            timv.score_b15, 
            timv.score_b18, 
            timv.score_b21, 
            timv.score_b24,
            COALESCE(timv.score_b03, 0) AS new_score_b03,
            COALESCE(timv.score_b06, timv.score_b03) AS new_score_b06,
            COALESCE(timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b09,
            COALESCE(timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b12,
            COALESCE(timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b15,
            COALESCE(timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b18,
            COALESCE(timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b21,
            COALESCE(timv.score_b24, timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b24,
            COALESCE(InstansiScores.instansi_score_b03, 0) AS instansi_score_b03,
            COALESCE(InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b06,
            COALESCE(InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b09,
            COALESCE(InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b12,
            COALESCE(InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b15,
            COALESCE(InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b18,
            COALESCE(InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b21,
            COALESCE(InstansiScores.instansi_score_b24, InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b24
            from m_kl_milestone as mkm 
            left join m_kl_output as mko on mko.id = mkm.m_kl_output_id
            left join m_kl_aksi as mka on mka.id = mko.m_kl_aksi_id
            left join t_instansi_milestone as tim on tim.m_kl_milestone_id = mkm.id
            left join m_instansi as mi on mi.id = tim.m_instansi_id
            left join t_inputan_milestone_verificator as timv on timv.m_kl_milestone_id = mkm.id
            left join InstansiScores ON mkm.id = InstansiScores.m_kl_milestone_id AND mi.id = InstansiScores.m_instansi_id
            where tim.periode_milestone = 'B03' AND mkm.m_kl_output_id = :output_id AND mi.id = :instansi_id order by mkm.urutan_milestone";
            $values = [
                'output_id' => $output_id,
                'instansi_id' => $instansi_id
            ];
            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        } 
     }

     public function read_milestone_2025(){
        $getParams = $this->request->getJsonRawBody(true);
        $output_id = $getParams["output_id"];
        $instansi_id = $getParams["instansi_id"];
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "
            WITH InstansiScores as (
            	select 
            		tim2.m_kl_milestone_id,
            		tim2.m_instansi_id,
            		MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN timvkib03.score END) AS instansi_score_b03,
            		MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN timvkib06.score END) AS instansi_score_b06,
            		MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN timvkib09.score END) AS instansi_score_b09,
            		MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN timvkib12.score END) AS instansi_score_b12,
            		MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN timvkib15.score END) AS instansi_score_b15,
            		MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN timvkib18.score END) AS instansi_score_b18,
            		MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN timvkib21.score END) AS instansi_score_b21,
            		MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN timvkib24.score END) AS instansi_score_b24
            	from t_instansi_milestone tim2 
            	left join t_inputan_milestone_verificator_kl_instansi timvkib03 on tim2.id = timvkib03.t_instansi_milestone_id and tim2.periode_milestone = 'B03'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib06 on tim2.id = timvkib06.t_instansi_milestone_id and tim2.periode_milestone = 'B06'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib09 on tim2.id = timvkib09.t_instansi_milestone_id and tim2.periode_milestone = 'B09'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib12 on tim2.id = timvkib12.t_instansi_milestone_id and tim2.periode_milestone = 'B12'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib15 on tim2.id = timvkib15.t_instansi_milestone_id and tim2.periode_milestone = 'B15'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib18 on tim2.id = timvkib18.t_instansi_milestone_id and tim2.periode_milestone = 'B18'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib21 on tim2.id = timvkib21.t_instansi_milestone_id and tim2.periode_milestone = 'B21'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib24 on tim2.id = timvkib24.t_instansi_milestone_id and tim2.periode_milestone = 'B24'
            	GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            )
            SELECT
            mi.id as instansi_id,
            mi.nama_instansi,
            mka.id as aksi_id,
            mko.id as output_id,
            mkm.id as milestone_id,
            mka.urutan_aksi,
            mko.urutan_output,
            mkm.urutan_milestone,
            mkm.nama_milestone,
            mko.nama_output,
            mka.nama_aksi,
            mka.nama_pendek_aksi,
            COALESCE(InstansiScores.instansi_score_b03, 0) AS new_score_b03,
            COALESCE(InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b06,
            COALESCE(InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b09,
            COALESCE(InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b12,
            COALESCE(InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b15,
            COALESCE(InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b18,
            COALESCE(InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b21,
            COALESCE(InstansiScores.instansi_score_b24, InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b24
            from m_kl_milestone as mkm 
            left join m_kl_output as mko on mko.id = mkm.m_kl_output_id
            left join m_kl_aksi as mka on mka.id = mko.m_kl_aksi_id
            left join t_instansi_milestone as tim on tim.m_kl_milestone_id = mkm.id
            left join m_instansi as mi on mi.id = tim.m_instansi_id
            left join t_inputan_milestone_verificator as timv on timv.m_kl_milestone_id = mkm.id
            left join InstansiScores ON mkm.id = InstansiScores.m_kl_milestone_id AND mi.id = InstansiScores.m_instansi_id
            where tim.periode_milestone = 'B03' AND mkm.m_kl_output_id = :output_id AND mi.id = :instansi_id order by mkm.urutan_milestone";
            $values = [
                'output_id' => $output_id,
                'instansi_id' => $instansi_id
            ];
            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        } 
     }

     public function read_output(){
        $getParams = $this->request->getJsonRawBody(true);
        $aksi_id = $getParams["aksi_id"];
        $instansi_id = $getParams["instansi_id"];
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "
            WITH InstansiScores as (
            	select 
            		tim2.m_kl_milestone_id,
            		tim2.m_instansi_id,
            		MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN timvkib03.score END) AS instansi_score_b03,
            		MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN timvkib06.score END) AS instansi_score_b06,
            		MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN timvkib09.score END) AS instansi_score_b09,
            		MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN timvkib12.score END) AS instansi_score_b12,
            		MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN timvkib15.score END) AS instansi_score_b15,
            		MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN timvkib18.score END) AS instansi_score_b18,
            		MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN timvkib21.score END) AS instansi_score_b21,
            		MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN timvkib24.score END) AS instansi_score_b24
            	from t_instansi_milestone tim2 
            	left join t_inputan_milestone_verificator_kl_instansi timvkib03 on tim2.id = timvkib03.t_instansi_milestone_id and tim2.periode_milestone = 'B03'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib06 on tim2.id = timvkib06.t_instansi_milestone_id and tim2.periode_milestone = 'B06'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib09 on tim2.id = timvkib09.t_instansi_milestone_id and tim2.periode_milestone = 'B09'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib12 on tim2.id = timvkib12.t_instansi_milestone_id and tim2.periode_milestone = 'B12'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib15 on tim2.id = timvkib15.t_instansi_milestone_id and tim2.periode_milestone = 'B15'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib18 on tim2.id = timvkib18.t_instansi_milestone_id and tim2.periode_milestone = 'B18'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib21 on tim2.id = timvkib21.t_instansi_milestone_id and tim2.periode_milestone = 'B21'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib24 on tim2.id = timvkib24.t_instansi_milestone_id and tim2.periode_milestone = 'B24'
            	GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            )
            SELECT
            mi.id as instansi_id,
            mi.nama_instansi,
            mka.id as aksi_id,
            mko.id as output_id,
            mko.m_kl_aksi_id,
            mkm.id as milestone_id,
            mka.urutan_aksi,
            mko.urutan_output,
            mkm.urutan_milestone,
            mkm.nama_milestone,
            mko.nama_output,
            mka.nama_aksi,
            mka.nama_pendek_aksi,
            timv.score_b03, 
            timv.score_b06, 
            timv.score_b09, 
            timv.score_b12, 
            timv.score_b15, 
            timv.score_b18, 
            timv.score_b21, 
            timv.score_b24,
            COALESCE(timv.score_b03, 0) AS new_score_b03,
            COALESCE(timv.score_b06, timv.score_b03) AS new_score_b06,
            COALESCE(timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b09,
            COALESCE(timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b12,
            COALESCE(timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b15,
            COALESCE(timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b18,
            COALESCE(timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b21,
            COALESCE(timv.score_b24, timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b24,
            COALESCE(InstansiScores.instansi_score_b03, 0) AS instansi_score_b03,
            COALESCE(InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b06,
            COALESCE(InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b09,
            COALESCE(InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b12,
            COALESCE(InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b15,
            COALESCE(InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b18,
            COALESCE(InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b21,
            COALESCE(InstansiScores.instansi_score_b24, InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b24
            from m_kl_milestone as mkm 
            left join m_kl_output as mko on mko.id = mkm.m_kl_output_id
            left join m_kl_aksi as mka on mka.id = mko.m_kl_aksi_id
            left join t_instansi_milestone as tim on tim.m_kl_milestone_id = mkm.id
            left join m_instansi as mi on mi.id = tim.m_instansi_id
            left join t_inputan_milestone_verificator as timv on timv.m_kl_milestone_id = mkm.id
            left join InstansiScores ON mkm.id = InstansiScores.m_kl_milestone_id AND mi.id = InstansiScores.m_instansi_id
            where tim.periode_milestone = 'B03' AND mko.m_kl_aksi_id = :aksi_id AND mi.id = :instansi_id order by mko.urutan_output";
            $values = [
                'aksi_id' => $aksi_id,
                'instansi_id' => $instansi_id
            ];
            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        } 
     }

     public function read_output_2025(){
        $getParams = $this->request->getJsonRawBody(true);
        $aksi_id = $getParams["aksi_id"];
        $instansi_id = $getParams["instansi_id"];
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "
            WITH InstansiScores as (
            	select 
            		tim2.m_kl_milestone_id,
            		tim2.m_instansi_id,
            		MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN timvkib03.score END) AS instansi_score_b03,
            		MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN timvkib06.score END) AS instansi_score_b06,
            		MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN timvkib09.score END) AS instansi_score_b09,
            		MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN timvkib12.score END) AS instansi_score_b12,
            		MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN timvkib15.score END) AS instansi_score_b15,
            		MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN timvkib18.score END) AS instansi_score_b18,
            		MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN timvkib21.score END) AS instansi_score_b21,
            		MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN timvkib24.score END) AS instansi_score_b24
            	from t_instansi_milestone tim2 
            	left join t_inputan_milestone_verificator_kl_instansi timvkib03 on tim2.id = timvkib03.t_instansi_milestone_id and tim2.periode_milestone = 'B03'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib06 on tim2.id = timvkib06.t_instansi_milestone_id and tim2.periode_milestone = 'B06'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib09 on tim2.id = timvkib09.t_instansi_milestone_id and tim2.periode_milestone = 'B09'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib12 on tim2.id = timvkib12.t_instansi_milestone_id and tim2.periode_milestone = 'B12'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib15 on tim2.id = timvkib15.t_instansi_milestone_id and tim2.periode_milestone = 'B15'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib18 on tim2.id = timvkib18.t_instansi_milestone_id and tim2.periode_milestone = 'B18'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib21 on tim2.id = timvkib21.t_instansi_milestone_id and tim2.periode_milestone = 'B21'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib24 on tim2.id = timvkib24.t_instansi_milestone_id and tim2.periode_milestone = 'B24'
            	GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            )
            SELECT
            mi.id as instansi_id,
            mi.nama_instansi,
            mka.id as aksi_id,
            mko.id as output_id,
            mko.m_kl_aksi_id,
            mkm.id as milestone_id,
            mka.urutan_aksi,
            mko.urutan_output,
            mkm.urutan_milestone,
            mkm.nama_milestone,
            mko.nama_output,
            mka.nama_aksi,
            mka.nama_pendek_aksi,
            COALESCE(InstansiScores.instansi_score_b03, 0) AS new_score_b03,
            COALESCE(InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b06,
            COALESCE(InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b09,
            COALESCE(InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b12,
            COALESCE(InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b15,
            COALESCE(InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b18,
            COALESCE(InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b21,
            COALESCE(InstansiScores.instansi_score_b24, InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS new_score_b24
            from m_kl_milestone as mkm 
            left join m_kl_output as mko on mko.id = mkm.m_kl_output_id
            left join m_kl_aksi as mka on mka.id = mko.m_kl_aksi_id
            left join t_instansi_milestone as tim on tim.m_kl_milestone_id = mkm.id
            left join m_instansi as mi on mi.id = tim.m_instansi_id
            left join t_inputan_milestone_verificator as timv on timv.m_kl_milestone_id = mkm.id
            left join InstansiScores ON mkm.id = InstansiScores.m_kl_milestone_id AND mi.id = InstansiScores.m_instansi_id
            where tim.periode_milestone = 'B03' AND mko.m_kl_aksi_id = :aksi_id AND mi.id = :instansi_id order by mko.urutan_output";
            $values = [
                'aksi_id' => $aksi_id,
                'instansi_id' => $instansi_id
            ];
            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        } 
     }


     public function get_all_logframe_milestone(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "SELECT 
                            mkm.id, 
                            mkm.m_kl_output_id, 
                            mkm.nama_milestone, 
                            mkm.urutan_milestone, 
                            mko.nama_output, 
                            mkm.nama_fokus, 
                            mkm.urutan_fokus,
                            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_of_instansi AS (
                                    SELECT
                                        mi.id, 
                                        mi.nama_pendek_instansi 
                                    FROM m_instansi AS mi
                                    LEFT JOIN t_instansi_milestone AS tim ON mi.id = tim.m_instansi_id 
                                    WHERE tim.periode_milestone = 'B03' 
                                    AND tim.m_kl_milestone_id = mkm.id
                                ) 
                                SELECT STRING_AGG(id || ':' || nama_pendek_instansi, ',') 
                                FROM array_of_instansi
                            ) AS nama_pendek_instansi
                        FROM m_kl_milestone AS mkm 
                        LEFT JOIN m_kl_output AS mko ON mkm.m_kl_output_id = mko.id
                        LEFT JOIN t_inputan_milestone_verificator AS timv ON mkm.id = timv.m_kl_milestone_id
                        ORDER BY mkm.id ASC";
            $result = $db->query($query);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return $data;
            } else {
                return null;
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function read_milestone_with_attachment(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $milestone_id = $getParams["milestone_id"];
        $periode_milestone = $getParams["periode_milestone"];
        $db->begin();
        try {
            $query = "SELECT 
                        takv.uuid, takv.t_inputan_milestone_verificator_id, takv.nama_label, takv.nama_dokumen, takv.ukuran_file, takv.periode_milestone
                        FROM m_kl_milestone AS mkm 
                        LEFT JOIN t_inputan_milestone_verificator AS timv ON mkm.id = timv.m_kl_milestone_id
                        LEFT JOIN t_attachment_kl_verificator AS takv ON takv.t_inputan_milestone_verificator_id = timv.id
                        WHERE mkm.id = :milestone_id AND takv.periode_milestone = :periode_milestone
                        ORDER BY mkm.id ASC";
            
            $values = [
                'milestone_id' => $milestone_id,
                'periode_milestone' => $periode_milestone
            ];

            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

    //  public function submit_logframe_milestone(){
    //     $this->checkIsAuthenticated();
    //     $db = $this->getDI()->get('databaseNewStranas2025');
        
    //     $getParams = $this->request->getJsonRawBody(true);
    //     $nama_milestone = $getParams["nama_milestone"];
    //     $nomor_urut_milestone = $getParams["nomor_urut_milestone"];
    //     $m_kl_output_id = $getParams["m_kl_output_id"];
    //     $nama_fokus = $getParams["nama_fokus"];
    //     $urutan_fokus = $getParams["urutan_fokus"];
    //     $m_instansi_id = $getParams["m_instansi_id"];
    //     $username = $getParams["username"];
    //     $db->begin();

    //     try{

    //         $this->setchange_by($username);

    //         $query_milestone = "INSERT INTO m_kl_milestone (m_kl_output_id, nama_milestone, urutan_milestone, nama_fokus, urutan_fokus, created_at, updated_at) VALUES(:m_kl_output_id, :nama_milestone, :urutan_milestone, :nama_fokus, :urutan_fokus, :created_at, :updated_at) RETURNING id";
           
    //         $values = [
    //             'm_kl_output_id' => $m_kl_output_id,
    //             'nama_milestone' => $nama_milestone,
    //             'urutan_milestone' => intval($nomor_urut_milestone),
    //             'nama_fokus' => $nama_fokus,
    //             'urutan_fokus' => $urutan_fokus,
    //             'created_at' => date('Y-m-d H:i:s'),
    //             'updated_at' => date('Y-m-d H:i:s')
    //         ];

    //         $db->execute($query_milestone, $values);

    //         $generatedId = $db->lastInsertId();

    //         for($i=0; $i<sizeof($m_instansi_id); $i++){
    //             $query_instansi_milestone = "INSERT INTO t_instansi_milestone(m_kl_milestone_id, m_instansi_id, periode_milestone, created_at, updated_at) VALUES(:m_kl_milestone_id, :m_instansi_id, :periode_milestone, :created_at, :updated_at)";

    //             $values_instansi_milestone_b03 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B03',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b03);

    //             $values_instansi_milestone_b06 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B06',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b06);

    //             $values_instansi_milestone_b09 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B09',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b09);

    //             $values_instansi_milestone_b12 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B12',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b12);

    //             $values_instansi_milestone_b15 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B15',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b15);

    //             $values_instansi_milestone_b18 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B18',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b18);

    //             $values_instansi_milestone_b21 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B21',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b21);

    //             $values_instansi_milestone_b24 = [
    //                 'm_kl_milestone_id' => $generatedId,
    //                 'm_instansi_id' => $m_instansi_id[$i],
    //                 'created_at' => date('Y-m-d H:i:s'),
    //                 'periode_milestone' => 'B24',
    //                 'updated_at' => date('Y-m-d H:i:s')
    //             ];
    //             $db->execute($query_instansi_milestone, $values_instansi_milestone_b24);
    //         }
            
    //        $db->commit();

    //        return new SimpleResponse([
    //           'message' => "Sukses Insert Kedalam Database"
    //        ]);
    //     }catch(Exception $e){
    //         $db->rollback();

    //         return new SimpleResponse([
    //             'message' => "Gagal Mengupdate Data " . $e->getMessage()
    //         ]);
    //     }
    //  }

    public function submit_logframe_milestone(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        
        $getParams = $this->request->getJsonRawBody(true);
        $nama_milestone = $getParams["nama_milestone"];
        $nomor_urut_milestone = $getParams["nomor_urut_milestone"];
        $m_kl_output_id = $getParams["m_kl_output_id"];
        $nama_fokus = $getParams["nama_fokus"];
        $urutan_fokus = $getParams["urutan_fokus"];
        $m_instansi_id = $getParams["m_instansi_id"];
        $username = $getParams["username"];
        $db->begin();
    
        try{
            $this->setchange_by();
    
            $query_milestone = "INSERT INTO m_kl_milestone (m_kl_output_id, nama_milestone, urutan_milestone, nama_fokus, urutan_fokus, created_at, updated_at) VALUES(:m_kl_output_id, :nama_milestone, :urutan_milestone, :nama_fokus, :urutan_fokus, :created_at, :updated_at) RETURNING id";
            
            $values = [
                'm_kl_output_id' => $m_kl_output_id,
                'nama_milestone' => $nama_milestone,
                'urutan_milestone' => intval($nomor_urut_milestone),
                'nama_fokus' => $nama_fokus,
                'urutan_fokus' => $urutan_fokus,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s')
            ];
    
            // Execute the query and fetch the result
            $result = $db->query($query_milestone, $values);
            $generatedId = $result->fetch()["id"];
    
            for($i=0; $i<sizeof($m_instansi_id); $i++){
                $query_instansi_milestone = "INSERT INTO t_instansi_milestone(m_kl_milestone_id, m_instansi_id, periode_milestone, created_at, updated_at) VALUES(:m_kl_milestone_id, :m_instansi_id, :periode_milestone, :created_at, :updated_at)";
    
                $values_instansi_milestone = [
                    'm_kl_milestone_id' => $generatedId,
                    'm_instansi_id' => $m_instansi_id[$i],
                    'created_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s')
                ];
    
                $periodes = ['B03', 'B06', 'B09', 'B12', 'B15', 'B18', 'B21', 'B24'];
                foreach ($periodes as $periode) {
                    $values_instansi_milestone['periode_milestone'] = $periode;
                    $db->execute($query_instansi_milestone, $values_instansi_milestone);
                }
            }
    
            $db->commit();
    
            return new SimpleResponse([
                'message' => "Sukses Insert Kedalam Database"
            ]);
        } catch(Exception $e) {
            $db->rollback();
    
            return new SimpleResponse([
                'message' => "Gagal Mengupdate Data " . $e->getMessage()
            ]);
        }
    }
    

    //  public function edit_logframe_milestone()
    //  {
    //      $this->checkIsAuthenticated();
    //      $db = $this->getDI()->get('databaseNewStranas2025');
         
    //      $getParams = $this->request->getJsonRawBody(true);
    //      $nama_milestone = $getParams["nama_milestone"];
    //      $nomor_urut_milestone = $getParams["nomor_urut_milestone"];
    //      $m_kl_output_id = $getParams["m_kl_output_id"];
    //      $nama_fokus = $getParams["nama_fokus"];
    //      $urutan_fokus = $getParams["urutan_fokus"];
    //      $m_instansi_id = $getParams["m_instansi_id"];
    //      $id = $getParams["id"];
    //      $db->begin();
     
    //      try {
    //          $query = "UPDATE m_kl_milestone 
    //                    SET m_kl_output_id = :m_kl_output_id, urutan_milestone = :urutan_milestone, 
    //                        nama_milestone = :nama_milestone, nama_fokus = :nama_fokus, 
    //                        urutan_fokus = :urutan_fokus, updated_at = :updated_at 
    //                    WHERE id = :id";
      
    //          $values = [
    //              'id' => $id,
    //              'm_kl_output_id' => $m_kl_output_id,
    //              'nama_milestone' => $nama_milestone,
    //              'urutan_milestone' => $nomor_urut_milestone,
    //              'nama_fokus' => $nama_fokus,
    //              'urutan_fokus' => $urutan_fokus,
    //              'updated_at' => date('Y-m-d H:i:s')
    //          ];
     
    //          if (!$db->execute($query, $values)) {
    //              throw new Exception("Failed to update m_kl_milestone.");
    //          }
     
    //          foreach ($m_instansi_id as $instansi_id) {
    //              // Check if m_instansi_id exists in the table
    //              $sql = "SELECT m_instansi_id FROM t_instansi_milestone WHERE m_instansi_id = :m_instansi_id AND m_kl_milestone_id = :m_kl_milestone_id";
    //              $result = $db->query($sql, ['m_instansi_id' => $instansi_id, 'm_kl_milestone_id' => $id]);
     
    //              $isUpdate = $result->fetch() ? true : false;
     
    //              // Common values for both insert and update
    //              $periods = ['B03', 'B06', 'B09', 'B12', 'B15', 'B18', 'B21', 'B24'];
    //              foreach ($periods as $periode_milestone) {
    //                  $values_instansi_milestone_insert = [
    //                      'm_kl_milestone_id' => $id,
    //                      'm_instansi_id' => $instansi_id,
    //                      'updated_at' => date('Y-m-d H:i:s'),
    //                      'created_at' => date('Y-m-d H:i:s'),
    //                      'periode_milestone' => $periode_milestone
    //                  ];

    //                  $values_instansi_milestone_update = [
    //                     'm_kl_milestone_id' => $id,
    //                     'm_instansi_id' => $instansi_id,
    //                     'updated_at' => date('Y-m-d H:i:s'),
    //                     'periode_milestone' => $periode_milestone
    //                 ];
     
    //                  if ($isUpdate) {
    //                      $query_instansi_milestone = "UPDATE t_instansi_milestone 
    //                                                   SET updated_at = :updated_at 
    //                                                   WHERE periode_milestone = :periode_milestone 
    //                                                   AND m_kl_milestone_id = :m_kl_milestone_id 
    //                                                   AND m_instansi_id = :m_instansi_id";
    //                     if (!$db->execute($query_instansi_milestone, $values_instansi_milestone_update)) {
    //                         throw new Exception("Failed to update t_instansi_milestone for m_instansi_id: " . $instansi_id);
    //                     }
    //                  } else {
    //                      $query_instansi_milestone = "INSERT INTO t_instansi_milestone (m_kl_milestone_id, m_instansi_id, updated_at, created_at, periode_milestone) 
    //                                                   VALUES (:m_kl_milestone_id, :m_instansi_id, :updated_at, :created_at, :periode_milestone)";

    //                     if (!$db->execute($query_instansi_milestone, $values_instansi_milestone_insert)) {
    //                         throw new Exception("Failed to insert t_instansi_milestone for m_instansi_id: " . $instansi_id);
    //                     }
    //                  }
                      
                     
    //              }
    //          }
     
    //          $db->commit();
     
    //          return new SimpleResponse([
    //              'message' => "Sukses Mengupdate Database"
    //          ]);
    //      } catch (Exception $e) {
    //          $db->rollback();
    //          return new SimpleResponse([
    //              'message' => "Gagal Mengupdate Data: " . $e->getMessage()
    //          ]);
    //      }
    //  }

    public function edit_logframe_nilai_milestone(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        
        $m_kl_milestone_id = $getParams['m_kl_milestone_id'];
        $score_b03 = $getParams['score_b03'];
        $score_b06 = $getParams['score_b06'];
        $score_b09 = $getParams['score_b09'];
        $score_b12 = $getParams['score_b12'];
        $score_b15 = $getParams['score_b15'];
        $score_b18 = $getParams['score_b18'];
        $score_b21 = $getParams['score_b21'];
        $score_b24 = $getParams['score_b24'];
        $username = $getParams['username'];
        $db->begin();
        try{
            $this->setchange_by();

            $queryCheck = 'SELECT * FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
            $valuesCheck = [
                'id' => $m_kl_milestone_id
            ];
            

            $check = $db->query($queryCheck, $valuesCheck);
            $data = $check->fetchAll(PDO::FETCH_ASSOC);
           
            if(count($data) > 0){
                $queryUpdateOrInsert = 'UPDATE t_inputan_milestone_verificator SET score_b03 = :score_b03, score_b06 = :score_b06, score_b09 = :score_b09, score_b12 = :score_b12, score_b15 = :score_b15, score_b18 = :score_b18, score_b21 = :score_b21, score_b24 = :score_b24 WHERE m_kl_milestone_id = :id'; 
            }else{
                $queryUpdateOrInsert = 'INSERT INTO t_inputan_milestone_verificator(m_kl_milestone_id, score_b03, score_b06, score_b09, score_b12, score_b15, score_b18, score_b21, score_b24) VALUES(:id, :score_b03, :score_b06, :score_b09, :score_b12, :score_b15, :score_b18, :score_b21, :score_b24)';
            }

            $values = [
                'score_b03' => $score_b03 === '' ? null : $score_b03,
                'score_b06' => $score_b06 === '' ? null : $score_b06,
                'score_b09' => $score_b09 === '' ? null : $score_b09,
                'score_b12' => $score_b12 === '' ? null : $score_b12,
                'score_b15' => $score_b15 === '' ? null : $score_b15,
                'score_b18' => $score_b18 === '' ? null : $score_b18,
                'score_b21' => $score_b21 === '' ? null : $score_b21,
                'score_b24' => $score_b24 === '' ? null : $score_b24,
                'id' => $m_kl_milestone_id
            ];


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

            return new SimpleResponse([
                'message' => "Sukses Mengupdate Database"
            ]);
        }catch (Exception $e) {
            $db->rollback();
            return new SimpleResponse([
                'message' => "Gagal Mengupdate Data: " . $e->getMessage()
            ]);
        }
    }

    public function edit_logframe_milestone()
    {
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');

        $getParams = $this->request->getJsonRawBody(true);
        $nama_milestone = $getParams["nama_milestone"];
        $nomor_urut_milestone = $getParams["nomor_urut_milestone"];
        $m_kl_output_id = $getParams["m_kl_output_id"];
        $nama_fokus = $getParams["nama_fokus"];
        $urutan_fokus = $getParams["urutan_fokus"];
        $m_instansi_id = $getParams["m_instansi_id"];
        $id = $getParams["id"];
        $username = $getParams["username"];
        $db->begin();
        try {
            // Mengatur current user dari aplikasi
            $this->setchange_by();

            $query = "UPDATE m_kl_milestone 
                    SET m_kl_output_id = :m_kl_output_id, urutan_milestone = :urutan_milestone, 
                        nama_milestone = :nama_milestone, nama_fokus = :nama_fokus, 
                        urutan_fokus = :urutan_fokus, updated_at = :updated_at 
                    WHERE id = :id";
    
            $values = [
                'id' => $id,
                'm_kl_output_id' => $m_kl_output_id,
                'nama_milestone' => $nama_milestone,
                'urutan_milestone' => $nomor_urut_milestone,
                'nama_fokus' => $nama_fokus,
                'urutan_fokus' => $urutan_fokus,
                'updated_at' => date('Y-m-d H:i:s')
            ];

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

            if (!$result) {
                throw new Exception("Failed to update m_kl_milestone.");
            }
            
            // Get current m_instansi_id in t_instansi_milestone
            $sql = "SELECT m_instansi_id FROM t_instansi_milestone WHERE m_kl_milestone_id = :m_kl_milestone_id";
            $result = $db->query($sql, ['m_kl_milestone_id' => $id]);
            $current_instansi_ids = array_column($result->fetchAll(), 'm_instansi_id');

            // Determine ids to delete and insert
            $ids_to_delete = array_diff($current_instansi_ids, $m_instansi_id);
            $ids_to_insert = array_diff($m_instansi_id, $current_instansi_ids);

            // Delete ids that are no longer in the request
            if (!empty($ids_to_delete)) {
                $sql_delete = "DELETE FROM t_instansi_milestone WHERE m_kl_milestone_id = :m_kl_milestone_id AND m_instansi_id = :m_instansi_id";
                foreach ($ids_to_delete as $id_to_delete) {
                    if (!$db->execute($sql_delete, ['m_kl_milestone_id' => $id, 'm_instansi_id' => $id_to_delete])) {
                        throw new Exception("Failed to delete m_instansi_id: " . $id_to_delete);
                    }
                }
            }

            // Insert new ids that are in the request but not in the table
            if (!empty($ids_to_insert)) {
                $sql_insert = "INSERT INTO t_instansi_milestone (m_kl_milestone_id, m_instansi_id, updated_at, created_at, periode_milestone) 
                            VALUES (:m_kl_milestone_id, :m_instansi_id, :updated_at, :created_at, :periode_milestone)";
                foreach ($ids_to_insert as $id_to_insert) {
                    $periods = ['B03', 'B06', 'B09', 'B12', 'B15', 'B18', 'B21', 'B24'];
                    foreach ($periods as $periode_milestone) {
                        $values_instansi_milestone = [
                            'm_kl_milestone_id' => $id,
                            'm_instansi_id' => $id_to_insert,
                            'updated_at' => date('Y-m-d H:i:s'),
                            'created_at' => date('Y-m-d H:i:s'),
                            'periode_milestone' => $periode_milestone
                        ];

                        if (!$db->execute($sql_insert, $values_instansi_milestone)) {
                            throw new Exception("Failed to insert m_instansi_id: " . $id_to_insert);
                        }
                    }
                }
            }

            // Update existing ids
            foreach ($m_instansi_id as $instansi_id) {
                if (in_array($instansi_id, $current_instansi_ids)) {
                    $query_instansi_milestone = "UPDATE t_instansi_milestone 
                                                SET updated_at = :updated_at 
                                                WHERE periode_milestone = :periode_milestone 
                                                AND m_kl_milestone_id = :m_kl_milestone_id 
                                                AND m_instansi_id = :m_instansi_id";

                    $periods = ['B03', 'B06', 'B09', 'B12', 'B15', 'B18', 'B21', 'B24'];
                    foreach ($periods as $periode_milestone) {
                        $values_instansi_milestone = [
                            'm_kl_milestone_id' => $id,
                            'm_instansi_id' => $instansi_id,
                            'updated_at' => date('Y-m-d H:i:s'),
                            'periode_milestone' => $periode_milestone
                        ];

                        if (!$db->execute($query_instansi_milestone, $values_instansi_milestone)) {
                            throw new Exception("Failed to update m_instansi_id: " . $instansi_id);
                        }
                    }
                }
            }

            $db->commit();

            return new SimpleResponse([
                'message' => "Sukses Mengupdate Database"
            ]);
        } catch (Exception $e) {
            $db->rollback();
            return new SimpleResponse([
                'message' => "Gagal Mengupdate Data: " . $e->getMessage()
            ]);
        }
    }

     public function delete_logframe_milestone(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $id = $getParams["id"];
        $username = $getParams["username"];
        $db->begin();

        try{
            $this->setchange_by();

            $query = "DELETE FROM m_kl_milestone WHERE id = :id";
 
            $values = [
             'id' => $id
            ];

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

            $db->commit();

            return new SimpleResponse([
                'message' => "Sukses Delete data output"
            ]);
        }catch(Exception $e){
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Gagal Mengupdate Data " . $e->getMessage()
            ]);
        }
     }

     public function search_logframe_milestone(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        // Eksekusi query dengan parameter binding
        $keyword = '%' . $getParams["keyword"] . '%';
        $db->begin();

        try{
            $query = "SELECT 
            mkm.id, 
            mkm.m_kl_output_id, 
            mkm.nama_milestone, 
            mkm.urutan_milestone, 
            mko.nama_output, 
            mkm.nama_fokus, 
            mkm.urutan_fokus,
            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_of_instansi AS (
                    SELECT
                        mi.id, 
                        mi.nama_pendek_instansi 
                    FROM m_instansi AS mi
                    LEFT JOIN t_instansi_milestone AS tim ON mi.id = tim.m_instansi_id 
                    WHERE tim.periode_milestone = 'B03' 
                    AND tim.m_kl_milestone_id = mkm.id
                ) 
                SELECT STRING_AGG(id || ':' || nama_pendek_instansi, ',') 
                FROM array_of_instansi
            ) AS nama_pendek_instansi
        FROM m_kl_milestone AS mkm                         
        LEFT JOIN m_kl_output AS mko ON mkm.m_kl_output_id = mko.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 mkm.id ASC";
 
            $values = [
              'keyword' => $keyword
            ];

            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            // Menggunakan fetchAll untuk mendapatkan hasil query
            if (!empty($data)) {
                return new SimpleResponse($data);
             } else {
                return new SimpleResponse([]);
             }
        }catch(Exception $e){
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Gagal Mengupdate Data " . $e->getMessage()
            ]);
        }
     }

     public function upload_data_dukung(){
        $this->checkIsAuthenticated();
        $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('databaseNewStranas2025');
        $m_kl_milestone_id = $getParams['m_kl_milestone_id'];
        $attachment = $getParams['attachments'];
        $periode_milestone = $getParams['periode_milestone'];
        $db->begin();
        try {
            $query = "SELECT id FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :m_kl_milestone_id";
            $values = [
                'm_kl_milestone_id' => $m_kl_milestone_id
            ];

            $result = $db->query($query, $values);    

            $id = $result->fetchAll(PDO::FETCH_ASSOC);
            $counter = 1;
            for($i=0; $i<count($attachment); $i++){
                $placeholders_data_dukung_milestone[] = "(:uuid{$counter}, :t_inputan_milestone_verificator_id{$counter}, :nama_label{$counter}, :nama_dokumen{$counter}, :created_at{$counter}, :updated_at{$counter}, :periode_milestone{$counter})";

                // Insert into t_attachment_kl_verificator
                $values_attachment[":uuid{$counter}"] = $attachment[$i]["uuid"];
                $values_attachment[":t_inputan_milestone_verificator_id{$counter}"] = $id[0]['id']; 
                $values_attachment[":nama_label{$counter}"] = $attachment[$i]["label"];
                $values_attachment[":nama_dokumen{$counter}"] = $attachment[$i]["filename"];
                $values_attachment[":created_at{$counter}"] = date("Y-m-d H:i:s");
                $values_attachment[":updated_at{$counter}"] = date("Y-m-d H:i:s");
                $values_attachment[":periode_milestone{$counter}"] = $periode_milestone; 
                // 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, created_at, updated_at, periode_milestone) VALUES " . implode(", ", $placeholders_data_dukung_milestone) . "
            ";

            $db->execute($query_attachment, $values_attachment);

            $db->commit();

            // Menggunakan fetchAll untuk mendapatkan hasil query
            if (!empty($data)) {
                return new SimpleResponse($data);
             } else {
                return new SimpleResponse([]);
             }
        }catch(Exception $e){
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function download_data_dukung_stranaspk_2024(){
        $this->checkIsAuthenticated();
        $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "SELECT 
                      mka.nama_aksi, 
                      mka.urutan_aksi, 
                      mko.nama_output, 
                      mko.urutan_output, 
                      mkm.nama_milestone,
                      mkm.urutan_milestone, 
                      mi.nama_instansi, 
                      mi.urutan_instansi, 
                      takv.uuid as uuid_verificator, 
                      taki.uuid AS uuid_instansi, 
                      takv.periode_milestone AS periode_milestone_verificator, 
                      taki.periode_milestone AS periode_milestone_instansi, 
                      tim.tanggal_laporan AS tanggal_laporan_instansi, 
                      timv.tanggal_verif_b03, 
                      timv.tanggal_verif_b06, 
                      timv.tanggal_verif_b09, 
                      timv.tanggal_verif_b12, 
                      timv.tanggal_verif_b15, 
                      timv.tanggal_verif_b18, 
                      timv.tanggal_verif_b21, 
                      timv.tanggal_verif_b24, 
                      tim.keterangan_pelapor, 
                      timv.keterangan_verificator_b03, 
                      timv.keterangan_verificator_b06, 
                      timv.keterangan_verificator_b09, 
                      timv.keterangan_verificator_b12, 
                      timv.keterangan_verificator_b15, 
                      timv.keterangan_verificator_b18, 
                      timv.keterangan_verificator_b21, 
                      timv.keterangan_verificator_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 
                      FROM m_kl_aksi AS mka
                      LEFT JOIN m_kl_output AS mko ON mko.m_kl_aksi_id = mka.id
                      LEFT JOIN m_kl_milestone AS mkm ON mkm.m_kl_output_id = mko.id
                      LEFT JOIN t_instansi_milestone AS tim ON tim.m_kl_milestone_id = mkm.id
                      LEFT JOIN m_instansi AS mi ON mi.id = tim.m_instansi_id
                      LEFT JOIN t_inputan_milestone_verificator AS timv ON timv.m_kl_milestone_id = mkm.id
                      LEFT JOIN t_attachment_kl_verificator AS takv ON timv.id = takv.t_inputan_milestone_verificator_id
                      LEFT JOIN t_attachment_kl_instansi AS taki ON tim.id = taki.t_instansi_milestone_id
            ";

            $result = $db->query($query, null);    

            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            // Menggunakan fetchAll untuk mendapatkan hasil query
            if (!empty($data)) {
                return new SimpleResponse($data);
             } else {
                return new SimpleResponse([]);
             }
        }catch(Exception $e){
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function download_laporan_milestone_2024(){
        $this->checkIsAuthenticated();
        $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('databaseNewStranas2025');
        $periode_milestone = $getParams['periode_milestone'];
        $tanggal_maksimal = $getParams['tanggal_maksimal'].' 23:59:59';
        $db->begin();
        try {
            $query = "WITH MilestoneData AS (
                SELECT 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id,
                    MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b03,
                    MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b06,
                    MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b09,
                    MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b12,
                    MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b15,
                    MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b18,
                    MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b21,
                    MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN tim2.keterangan_pelapor END) AS keterangan_pelapor_b24,
                    MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN tim2.nama_pelapor END) AS nama_pelapor_b03,
                    MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN tim2.nama_pelapor END) AS nama_pelapor_b06,
                    MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN tim2.nama_pelapor END) AS nama_pelapor_b09,
                    MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN tim2.nama_pelapor END) AS nama_pelapor_b12,
                    MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN tim2.nama_pelapor END) AS nama_pelapor_b15,
                    MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN tim2.nama_pelapor END) AS nama_pelapor_b18,
                    MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN tim2.nama_pelapor END) AS nama_pelapor_b21,
                    MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN tim2.nama_pelapor END) AS nama_pelapor_b24,
                    MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b03,
                    MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b06,
                    MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b09,
                    MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b12,
                    MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b15,
                    MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b18,
                    MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b21,
                    MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN tim2.tanggal_laporan END) AS tanggal_laporan_b24
                FROM 
                    t_instansi_milestone AS tim2 
                GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            ),
            InstansiScores as (
            	select 
            		tim2.m_kl_milestone_id,
            		tim2.m_instansi_id,
            		MAX(CASE WHEN tim2.periode_milestone = 'B03' THEN timvkib03.score END) AS instansi_score_b03,
            		MAX(CASE WHEN tim2.periode_milestone = 'B06' THEN timvkib06.score END) AS instansi_score_b06,
            		MAX(CASE WHEN tim2.periode_milestone = 'B09' THEN timvkib09.score END) AS instansi_score_b09,
            		MAX(CASE WHEN tim2.periode_milestone = 'B12' THEN timvkib12.score END) AS instansi_score_b12,
            		MAX(CASE WHEN tim2.periode_milestone = 'B15' THEN timvkib15.score END) AS instansi_score_b15,
            		MAX(CASE WHEN tim2.periode_milestone = 'B18' THEN timvkib18.score END) AS instansi_score_b18,
            		MAX(CASE WHEN tim2.periode_milestone = 'B21' THEN timvkib21.score END) AS instansi_score_b21,
            		MAX(CASE WHEN tim2.periode_milestone = 'B24' THEN timvkib24.score END) AS instansi_score_b24
            	from t_instansi_milestone tim2 
            	left join t_inputan_milestone_verificator_kl_instansi timvkib03 on tim2.id = timvkib03.t_instansi_milestone_id and tim2.periode_milestone = 'B03'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib06 on tim2.id = timvkib06.t_instansi_milestone_id and tim2.periode_milestone = 'B06'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib09 on tim2.id = timvkib09.t_instansi_milestone_id and tim2.periode_milestone = 'B09'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib12 on tim2.id = timvkib12.t_instansi_milestone_id and tim2.periode_milestone = 'B12'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib15 on tim2.id = timvkib15.t_instansi_milestone_id and tim2.periode_milestone = 'B15'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib18 on tim2.id = timvkib18.t_instansi_milestone_id and tim2.periode_milestone = 'B18'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib21 on tim2.id = timvkib21.t_instansi_milestone_id and tim2.periode_milestone = 'B21'
            	left join t_inputan_milestone_verificator_kl_instansi timvkib24 on tim2.id = timvkib24.t_instansi_milestone_id and tim2.periode_milestone = 'B24'
            	GROUP BY 
                    tim2.m_kl_milestone_id,
                    tim2.m_instansi_id
            )
            SELECT 
                mka.nama_aksi,
                mka.urutan_aksi, 
                mko.nama_output,
                mko.urutan_output, 
                mkm.nama_milestone,
                mkm.urutan_milestone, 
                mi.nama_instansi,
                mi.m_jenis_instansi_id AS kode_instansi,
                mi.nama_pendek_instansi,
                mi.urutan_instansi, 
                mka.id as aksi_id, 
                mko.id as output_id, 
                mkm.id as milestone_id, 
                timv.keterangan_verificator_b03,
                timv.keterangan_verificator_b06,
                timv.keterangan_verificator_b09,
                timv.keterangan_verificator_b12,
                timv.keterangan_verificator_b15,
                timv.keterangan_verificator_b18,
                timv.keterangan_verificator_b21,
                timv.keterangan_verificator_b24,
                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,
                COALESCE(timv.score_b03, 0) AS new_score_b03,
                COALESCE(timv.score_b06, timv.score_b03) AS new_score_b06,
                COALESCE(timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b09,
                COALESCE(timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b12,
                COALESCE(timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b15,
                COALESCE(timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b18,
                COALESCE(timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b21,
                COALESCE(timv.score_b24, timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b24,
                timv.tanggal_verif_b03,
                timv.tanggal_verif_b06,
                timv.tanggal_verif_b09,
                timv.tanggal_verif_b12,
                timv.tanggal_verif_b15,
                timv.tanggal_verif_b18,
                timv.tanggal_verif_b21,
                timv.tanggal_verif_b24,
                tkt.nama_target,
                tkt.urutan_target,
                tkt.indikator_penilaian_target,
                tkt.nilai_b03 as nilai_target_b03,
                tkt.nilai_b06 as nilai_target_b06,
                tkt.nilai_b09 as nilai_target_b09,
                tkt.nilai_b12 as nilai_target_b12,
                tkt.nilai_b15 as nilai_target_b15,
                tkt.nilai_b18 as nilai_target_b18,
                tkt.nilai_b21 as nilai_target_b21,
                tkt.nilai_b24 as nilai_target_b24,
                COALESCE(tkt.nilai_b03, 0) AS new_nilai_target_b03,
                COALESCE(tkt.nilai_b06, tkt.nilai_b03) AS new_nilai_target_b06,
                COALESCE(tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_nilai_target_b09,
                COALESCE(tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_nilai_target_b12,
                COALESCE(tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_nilai_target_b15,
                COALESCE(tkt.nilai_b18, tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_nilai_target_b18,
                COALESCE(tkt.nilai_b21, tkt.nilai_b18, tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_nilai_target_b21,
                COALESCE(tkt.nilai_b24, tkt.nilai_b21, tkt.nilai_b18, tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_nilai_target_b24,
                tkt.nilai_verifikasi_max,
                tkt.keterangan_target_b03,
                tkt.keterangan_target_b06,
                tkt.keterangan_target_b09,
                tkt.keterangan_target_b12,
                tkt.keterangan_target_b15,
                tkt.keterangan_target_b18,
                tkt.keterangan_target_b21,
                tkt.keterangan_target_b24,
                tkt.nama_verifikator_b03 as nama_verifikator_target_b03,
                tkt.nama_verifikator_b06 as nama_verifikator_target_b06,
                tkt.nama_verifikator_b09 as nama_verifikator_target_b09,
                tkt.nama_verifikator_b12 as nama_verifikator_target_b12,
                tkt.nama_verifikator_b15 as nama_verifikator_target_b15,
                tkt.nama_verifikator_b18 as nama_verifikator_target_b18,
                tkt.nama_verifikator_b21 as nama_verifikator_target_b21,
                tkt.nama_verifikator_b24 as nama_verifikator_target_b24,
                tkt.tanggal_verifikator_b03 as tanggal_verifikator_target_b03,
                tkt.tanggal_verifikator_b06 as tanggal_verifikator_target_b06,
                tkt.tanggal_verifikator_b09 as tanggal_verifikator_target_b09,
                tkt.tanggal_verifikator_b12 as tanggal_verifikator_target_b12,
                tkt.tanggal_verifikator_b15 as tanggal_verifikator_target_b15,
                tkt.tanggal_verifikator_b18 as tanggal_verifikator_target_b18,
                tkt.tanggal_verifikator_b21 as tanggal_verifikator_target_b21,
                tkt.tanggal_verifikator_b24 as tanggal_verifikator_target_b24,
                MilestoneData.keterangan_pelapor_b03,
                MilestoneData.keterangan_pelapor_b06,
                MilestoneData.keterangan_pelapor_b09,
                MilestoneData.keterangan_pelapor_b12,
                MilestoneData.keterangan_pelapor_b15,
                MilestoneData.keterangan_pelapor_b18,
                MilestoneData.keterangan_pelapor_b21,
                MilestoneData.keterangan_pelapor_b24,
                MilestoneData.nama_pelapor_b03,
                MilestoneData.nama_pelapor_b06,
                MilestoneData.nama_pelapor_b09,
                MilestoneData.nama_pelapor_b12,
                MilestoneData.nama_pelapor_b15,
                MilestoneData.nama_pelapor_b18,
                MilestoneData.nama_pelapor_b21,
                MilestoneData.nama_pelapor_b24,
                MilestoneData.tanggal_laporan_b03,
                MilestoneData.tanggal_laporan_b06,
                MilestoneData.tanggal_laporan_b09,
                MilestoneData.tanggal_laporan_b12,
                MilestoneData.tanggal_laporan_b15,
                MilestoneData.tanggal_laporan_b18,
                MilestoneData.tanggal_laporan_b21,
                MilestoneData.tanggal_laporan_b24,
                COALESCE(InstansiScores.instansi_score_b03, 0) AS instansi_score_b03,
                COALESCE(InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b06,
                COALESCE(InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b09,
                COALESCE(InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b12,
                COALESCE(InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b15,
                COALESCE(InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b18,
                COALESCE(InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b21,
                COALESCE(InstansiScores.instansi_score_b24, InstansiScores.instansi_score_b21, InstansiScores.instansi_score_b18, InstansiScores.instansi_score_b15, InstansiScores.instansi_score_b12, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b09, InstansiScores.instansi_score_b06, InstansiScores.instansi_score_b03) AS instansi_score_b24,
                tim.id AS pelapor_id,
                tim.updated_at as tanggal_pasang_milestone
                FROM
                m_kl_milestone AS mkm
                left join t_inputan_milestone_verificator as timv on mkm.id = timv.m_kl_milestone_id 
                left join t_instansi_milestone tim on tim.m_kl_milestone_id = mkm.id 
                left join m_instansi as mi on mi.id = tim.m_instansi_id 
                left join m_kl_output as mko on mko.id = mkm.m_kl_output_id 
                left join m_kl_aksi as mka on mka.id = mko.m_kl_aksi_id
                left join t_kl_target as tkt on mko.id = tkt.m_kl_output_id
                left join MilestoneData ON mkm.id = MilestoneData.m_kl_milestone_id AND mi.id = MilestoneData.m_instansi_id
                left join InstansiScores ON mkm.id = InstansiScores.m_kl_milestone_id AND mi.id = InstansiScores.m_instansi_id
                WHERE tim.periode_milestone = :periode_milestone AND tim.updated_at <= :tanggal_maksimal";
            
            $values = [
                'periode_milestone' => $periode_milestone,
                'tanggal_maksimal' => $tanggal_maksimal
            ];

            $result = $db->query($query, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            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' => "Data tidak di temukan " . $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()
            ]);
        } 
    }

    private function setchange_by(){
            $db = $this->getDI()->get('databaseNewStranas2025');
            $user = $this->shared->user;
            $umId = $user->um_id;
            $namaUser = $this->CheckNamaUser($umId);
            $current_user = $namaUser[0]['nama'];
            $set_username = "UPDATE session_username SET nama = :nama WHERE id = 1";
            $value = [
                'nama' => $current_user
            ];
            $db->execute($set_username, $value);
    }
}