<?php

class StranaspkSummaryAksi2024Controller extends BaseController
{
     
    public function summary_aksi(){
        $this->checkIsAuthenticated();
        // $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try{
            $query = "SELECT
                        mka.id AS aksi_id,
                        mka.urutan_aksi, 
                        mko.nama_output, 
                        mkm.nama_milestone,
                        mkm.id as milestone_id,
                        mko.id as output_id,
                        mi.nama_instansi,
                        timv.score_b03,
                        timv.score_b06,
                        timv.score_b09,
                        timv.score_b12,
                        timv.score_b15,
                        timv.score_b18,
                        timv.score_b21,
                        timv.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,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B03') AS keterangan_pelapor_b03,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B06') AS keterangan_pelapor_b06,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B09') AS keterangan_pelapor_b09,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B12') AS keterangan_pelapor_b12,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B15') AS keterangan_pelapor_b15,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B18') AS keterangan_pelapor_b18,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B21') AS keterangan_pelapor_b21,
                        (SELECT 
                        keterangan_pelapor
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B24') AS keterangan_pelapor_b24,
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B03') AS tanggal_laporan_b03,
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B06') AS tanggal_laporan_b06,
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B09') AS tanggal_laporan_b09,
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B12') AS tanggal_laporan_b12,
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B15') AS tanggal_laporan_b15,
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B18') AS tanggal_laporan_b18,                        
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B21') AS tanggal_laporan_b21,
                        (SELECT 
                        tanggal_laporan 
                        FROM t_instansi_milestone AS tim2 
                        WHERE tim2.m_kl_milestone_id = tim.m_kl_milestone_id 
                        AND tim2.m_instansi_id = tim.m_instansi_id AND tim2.periode_milestone = 'B24') AS tanggal_laporan_b24 
                        from m_kl_milestone as mkm
                        left join m_kl_output as mko 
                        on mkm.m_kl_output_id = mko.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' 
                        ORDER BY mka.urutan_aksi ASC";
            $result = $db->query($query);
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

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

}