<?php


class StranaspkNewDashboardTargetOutput2025Controller extends BaseController
{

     public function read_target_output(){
        $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "SELECT
            mka.id as aksi_id,
            mko.id as output_id,
            tkt.id as target_id, 
            mka.nama_aksi,
            mko.nama_output,
            tkt.nama_target,
            mka.urutan_aksi,
            mko.urutan_output,
            tkt.urutan_target,
            mka.nama_pendek_aksi,
            tkt.nilai_b03,
            tkt.nilai_b06,
            tkt.nilai_b09,
            tkt.nilai_b12,
            tkt.nilai_b15,
            tkt.nilai_b18,
            tkt.nilai_b21,
            tkt.nilai_b24,
            tkt.urutan_fokus,
            COALESCE(tkt.nilai_b03, 0) AS new_score_b03,
            COALESCE(tkt.nilai_b06, tkt.nilai_b03) AS new_score_b06,
            COALESCE(tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b09,
            COALESCE(tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b12,
            COALESCE(tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b15,
            COALESCE(tkt.nilai_b18, tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_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_score_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_score_b24
            FROM t_kl_target AS tkt 
            LEFT JOIN m_kl_output AS mko on mko.id = tkt.m_kl_output_id
            LEFT JOIN m_kl_aksi AS mka on mka.id = mko.m_kl_aksi_id
            order by mka.urutan_aksi, mko.urutan_output, tkt.urutan_target
            ";
            $result = $db->query($query);
            // 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_target_output_by_aksi_id(){
        $getParams = $this->request->getJsonRawBody(true);
        $aksi_id = $getParams['aksi_id'];
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "SELECT
            mka.id as aksi_id,
            mko.id as output_id,
            tkt.id as target_id, 
            mka.nama_aksi,
            mko.nama_output,
            tkt.nama_target,
            mka.urutan_aksi,
            mko.urutan_output,
            tkt.urutan_target,
            mka.nama_pendek_aksi,
            tkt.nilai_b03,
            tkt.nilai_b06,
            tkt.nilai_b09,
            tkt.nilai_b12,
            tkt.nilai_b15,
            tkt.nilai_b18,
            tkt.nilai_b21,
            tkt.nilai_b24,
            COALESCE(tkt.nilai_b03, 0) AS new_score_b03,
            COALESCE(tkt.nilai_b06, tkt.nilai_b03) AS new_score_b06,
            COALESCE(tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b09,
            COALESCE(tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b12,
            COALESCE(tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b15,
            COALESCE(tkt.nilai_b18, tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_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_score_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_score_b24
            FROM t_kl_target AS tkt 
            LEFT JOIN m_kl_output AS mko on mko.id = tkt.m_kl_output_id
            LEFT JOIN m_kl_aksi AS mka on mka.id = mko.m_kl_aksi_id
            WHERE mka.id = :aksi_id
            order by mka.urutan_aksi, mko.urutan_output, tkt.urutan_target
            ";

            $values = [
                'aksi_id' => $aksi_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_target_output_by_output_id(){
        $getParams = $this->request->getJsonRawBody(true);
        $output_id = $getParams['output_id'];
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "SELECT
            mka.id as aksi_id,
            mko.id as output_id,
            tkt.id as target_id, 
            mka.nama_aksi,
            mko.nama_output,
            tkt.nama_target,
            mka.urutan_aksi,
            mko.urutan_output,
            tkt.urutan_target,
            mka.nama_pendek_aksi,
            tkt.nilai_b03,
            tkt.nilai_b06,
            tkt.nilai_b09,
            tkt.nilai_b12,
            tkt.nilai_b15,
            tkt.nilai_b18,
            tkt.nilai_b21,
            tkt.nilai_b24,
            COALESCE(tkt.nilai_b03, 0) AS new_score_b03,
            COALESCE(tkt.nilai_b06, tkt.nilai_b03) AS new_score_b06,
            COALESCE(tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b09,
            COALESCE(tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b12,
            COALESCE(tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_b15,
            COALESCE(tkt.nilai_b18, tkt.nilai_b15, tkt.nilai_b12, tkt.nilai_b09, tkt.nilai_b06, tkt.nilai_b03) AS new_score_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_score_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_score_b24
            FROM t_kl_target AS tkt 
            LEFT JOIN m_kl_output AS mko on mko.id = tkt.m_kl_output_id
            LEFT JOIN m_kl_aksi AS mka on mka.id = mko.m_kl_aksi_id
            WHERE mko.id = :output_id
            order by mka.urutan_aksi, mko.urutan_output, tkt.urutan_target
            ";

            $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()
            ]);
        } 
     }

}