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

class StranaspkNewTargetOutput2025Controller extends BaseController {
    

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

        $results = $this->CheckValidator($getParams);
        $insertIntoDatabaseAksi = $this->SaveToDataBaseAksi($results);
        $insertIntoDatabaseOutput = $this->SaveToDataBaseOutput($results);
        $insertIntoDatabaseTarget = $this->SaveToDataBaseTarget($results);
        // $response = new SimpleResponse($results);
        // $response->setStatusCode($results['status_code']);
        // $response->setStatus($results['status']);
        // return $response;
        if($insertIntoDatabaseAksi && $insertIntoDatabaseOutput && $insertIntoDatabaseTarget){
            return new SimpleResponse([
                "Message" => "Berhasil Input Ke Dalam Database"
            ]);
        }else{
            return new SimpleResponse([
                "Message" => "Gagal Input Ke Dalam Database"
            ]);
        }
    }


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

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

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

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

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

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

                    $values_output[":m_kl_aksi_id{$counter}"] = $value["result"][$i]["Column3"];
                    $values_output[":nama_output{$counter}"] = $value["result"][$i]['Column6'];
                    $values_output[":urutan_output{$counter}"] = $value["result"][$i]['Column5'];
                    $values_output[":created_at{$counter}"] = date("Y-m-d H:i:s");
                    $values_output[":updated_at{$counter}"] = date("Y-m-d H:i:s");
                    $counter++;
            }
                

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

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

    }

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

        try{
            // target
            $placeholders_t_kl_target = [];
            $values_target = [];
             
            $counter = 1;
            for($i=0; $i<sizeof($value["result"]); $i++){
                    //target
                $placeholders_t_kl_target[] = "((SELECT id FROM m_kl_output WHERE m_kl_aksi_id = :m_kl_aksi_id{$counter} AND urutan_output = :urutan_output{$counter}), :urutan_target{$counter}, :nama_target{$counter}, :nama_fokus{$counter}, :urutan_fokus{$counter}, :nilai_b03{$counter}, :nilai_b06{$counter}, :nilai_b09{$counter}, :nilai_b12{$counter}, :nilai_b15{$counter}, :nilai_b18{$counter}, :nilai_b21{$counter}, :nilai_b24{$counter}, :nilai_verifikasi_max{$counter}, :keterangan_target_b03{$counter}, :keterangan_target_b06{$counter}, :keterangan_target_b09{$counter}, :keterangan_target_b12{$counter}, :keterangan_target_b15{$counter}, :keterangan_target_b18{$counter}, :keterangan_target_b21{$counter}, :keterangan_target_b24{$counter}, :indikator_penilaian_target{$counter}, :nama_verifikator_b03{$counter}, :nama_verifikator_b06{$counter}, :nama_verifikator_b09{$counter}, :nama_verifikator_b12{$counter}, :nama_verifikator_b15{$counter}, :nama_verifikator_b18{$counter}, :nama_verifikator_b21{$counter}, :nama_verifikator_b24{$counter}, :tanggal_verifikator_b03{$counter}, :tanggal_verifikator_b06{$counter}, :tanggal_verifikator_b09{$counter}, :tanggal_verifikator_b12{$counter}, :tanggal_verifikator_b15{$counter}, :tanggal_verifikator_b18{$counter}, :tanggal_verifikator_b21{$counter}, :tanggal_verifikator_b24{$counter}, :created_at{$counter}, :updated_at{$counter})";
    
                $values_target[":m_kl_aksi_id{$counter}"] = $value["result"][$i]["Column3"];
                $values_target[":urutan_output{$counter}"] = $value["result"][$i]["Column5"];
                $values_target[":urutan_target{$counter}"] = $value["result"][$i]["Column7"];
                $values_target[":nama_target{$counter}"] = $value["result"][$i]["Column8"];          
                $values_target[":nama_fokus{$counter}"] = $value["result"][$i]["Column2"];
                $values_target[":urutan_fokus{$counter}"] = $value["result"][$i]["Column1"];
                $values_target[":nilai_b03{$counter}"] = null;
                $values_target[":nilai_b06{$counter}"] = null;
                $values_target[":nilai_b09{$counter}"] = null;
                $values_target[":nilai_b12{$counter}"] = null;
                $values_target[":nilai_b15{$counter}"] = null;
                $values_target[":nilai_b18{$counter}"] = null;
                $values_target[":nilai_b21{$counter}"] = null;
                $values_target[":nilai_b24{$counter}"] = null;
                $values_target[":nilai_verifikasi_max{$counter}"] = null;
                $values_target[":keterangan_target_b03{$counter}"] = null;
                $values_target[":keterangan_target_b06{$counter}"] = null;
                $values_target[":keterangan_target_b09{$counter}"] = null;
                $values_target[":keterangan_target_b12{$counter}"] = null;
                $values_target[":keterangan_target_b15{$counter}"] = null;
                $values_target[":keterangan_target_b18{$counter}"] = null;
                $values_target[":keterangan_target_b21{$counter}"] = null;
                $values_target[":keterangan_target_b24{$counter}"] = null;
                $values_target[":indikator_penilaian_target{$counter}"] = $value["result"][$i]["Column9"];
                $values_target[":nama_verifikator_b03{$counter}"] = null;
                $values_target[":nama_verifikator_b06{$counter}"] = null;
                $values_target[":nama_verifikator_b09{$counter}"] = null;
                $values_target[":nama_verifikator_b12{$counter}"] = null;
                $values_target[":nama_verifikator_b15{$counter}"] = null;
                $values_target[":nama_verifikator_b18{$counter}"] = null;
                $values_target[":nama_verifikator_b21{$counter}"] = null;
                $values_target[":nama_verifikator_b24{$counter}"] = null;
                $values_target[":tanggal_verifikator_b03{$counter}"] = null;
                $values_target[":tanggal_verifikator_b06{$counter}"] = null;
                $values_target[":tanggal_verifikator_b09{$counter}"] = null;
                $values_target[":tanggal_verifikator_b12{$counter}"] = null;
                $values_target[":tanggal_verifikator_b15{$counter}"] = null;
                $values_target[":tanggal_verifikator_b18{$counter}"] = null;
                $values_target[":tanggal_verifikator_b21{$counter}"] = null;
                $values_target[":tanggal_verifikator_b24{$counter}"] = null;
                $values_target[":created_at{$counter}"] = date("Y-m-d H:i:s");
                $values_target[":updated_at{$counter}"] = date("Y-m-d H:i:s");
                $counter++;
            }
                
            $query= "INSERT INTO t_kl_target (m_kl_output_id, urutan_target, nama_target, nama_fokus, urutan_fokus, nilai_b03, nilai_b06, nilai_b09, nilai_b12, nilai_b15, nilai_b18, nilai_b21, nilai_b24, nilai_verifikasi_max, keterangan_target_b03, keterangan_target_b06, keterangan_target_b09, keterangan_target_b12, keterangan_target_b15, keterangan_target_b18, keterangan_target_b21, keterangan_target_b24, indikator_penilaian_target, nama_verifikator_b03, nama_verifikator_b06, nama_verifikator_b09, nama_verifikator_b12, nama_verifikator_b15, nama_verifikator_b18, nama_verifikator_b21, nama_verifikator_b24, tanggal_verifikator_b03, tanggal_verifikator_b06, tanggal_verifikator_b09, tanggal_verifikator_b12, tanggal_verifikator_b15, tanggal_verifikator_b18, tanggal_verifikator_b21, tanggal_verifikator_b24, created_at, updated_at)
            VALUES " . implode(", ", $placeholders_t_kl_target) . "
            ON CONFLICT (nama_target, urutan_target) DO NOTHING";

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

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

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

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

            }

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

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

    public function searchLogframeAksi()
    {
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        // Mulai transaksi
        $getParams = $this->request->getJsonRawBody(true);
        
        $db->begin();
       
        try {
            // bila optionnya aksi
            if($getParams["options"] == "aksi"){
                if($getParams["keyword"] == "all"){
                    $query = "SELECT tkt.nama_verifikator_b03, tkt.nama_verifikator_b06, tkt.nama_verifikator_b09, tkt.nama_verifikator_b12, tkt.nama_verifikator_b15, tkt.nama_verifikator_b18, tkt.nama_verifikator_b21, tkt.nama_verifikator_b24, tkt.tanggal_verifikator_b03 AS tanggal_verifikator_b03, tkt.tanggal_verifikator_b06 AS tanggal_verifikator_b06, tkt.tanggal_verifikator_b09 AS tanggal_verifikator_b09, tkt.tanggal_verifikator_b12 AS tanggal_verifikator_b12, tkt.tanggal_verifikator_b15 AS tanggal_verifikator_b15, tkt.tanggal_verifikator_b18 AS tanggal_verifikator_b18, tkt.tanggal_verifikator_b21 AS tanggal_verifikator_b21, tkt.tanggal_verifikator_b24 AS tanggal_verifikator_b24, tkt.indikator_penilaian_target AS indikator_penilaian_target, tkt.id AS t_kl_target_id, tkt.nilai_b03 AS nilai_b03, tkt.nilai_b06 AS nilai_b06, tkt.nilai_b09 AS nilai_b09, tkt.nilai_b12 AS nilai_b12, tkt.nilai_b15 AS nilai_b15, tkt.nilai_b18 AS nilai_b18, tkt.nilai_b21 AS nilai_b21, tkt.nilai_b24 AS nilai_b24, tkt.keterangan_target_b03 AS keterangan_target_b03, tkt.keterangan_target_b06 AS keterangan_target_b06, tkt.keterangan_target_b09 AS keterangan_target_b09, tkt.keterangan_target_b12 AS keterangan_target_b12, tkt.keterangan_target_b15 AS keterangan_target_b15, tkt.keterangan_target_b18 AS keterangan_target_b18, tkt.keterangan_target_b21 AS keterangan_target_b21, tkt.keterangan_target_b24 AS keterangan_target_b24, mla.nama_aksi AS nama_aksi, mlo.nama_output AS nama_output, tkt.nama_fokus AS nama_fokus, tkt.nama_target AS nama_target FROM m_kl_aksi AS mla LEFT JOIN m_kl_output AS mlo ON mla.id = mlo.m_kl_aksi_id LEFT JOIN t_kl_target AS tkt ON mlo.id = tkt.m_kl_output_id WHERE tkt.nama_target IS NOT NULL ORDER BY tkt.urutan_target ASC, tkt.id ASC";  
                }else{
                    $query = "SELECT tkt.nama_verifikator_b03, tkt.nama_verifikator_b06, tkt.nama_verifikator_b09, tkt.nama_verifikator_b12, tkt.nama_verifikator_b15, tkt.nama_verifikator_b18, tkt.nama_verifikator_b21, tkt.nama_verifikator_b24, tkt.tanggal_verifikator_b03 AS tanggal_verifikator_b03, tkt.tanggal_verifikator_b06 AS tanggal_verifikator_b06, tkt.tanggal_verifikator_b09 AS tanggal_verifikator_b09, tkt.tanggal_verifikator_b12 AS tanggal_verifikator_b12, tkt.tanggal_verifikator_b15 AS tanggal_verifikator_b15, tkt.tanggal_verifikator_b18 AS tanggal_verifikator_b18, tkt.tanggal_verifikator_b21 AS tanggal_verifikator_b21, tkt.tanggal_verifikator_b24 AS tanggal_verifikator_b24, tkt.indikator_penilaian_target AS indikator_penilaian_target, tkt.id AS t_kl_target_id, tkt.nilai_b03 AS nilai_b03, tkt.nilai_b06 AS nilai_b06, tkt.nilai_b09 AS nilai_b09, tkt.nilai_b12 AS nilai_b12, tkt.nilai_b15 AS nilai_b15, tkt.nilai_b18 AS nilai_b18, tkt.nilai_b21 AS nilai_b21, tkt.nilai_b24 AS nilai_b24, tkt.keterangan_target_b03 AS keterangan_target_b03, tkt.keterangan_target_b06 AS keterangan_target_b06, tkt.keterangan_target_b09 AS keterangan_target_b09, tkt.keterangan_target_b12 AS keterangan_target_b12, tkt.keterangan_target_b15 AS keterangan_target_b15, tkt.keterangan_target_b18 AS keterangan_target_b18, tkt.keterangan_target_b21 AS keterangan_target_b21, tkt.keterangan_target_b24 AS keterangan_target_b24, mla.nama_aksi AS nama_aksi, mlo.nama_output AS nama_output, tkt.nama_fokus AS nama_fokus, tkt.nama_target AS nama_target FROM m_kl_aksi AS mla LEFT JOIN m_kl_output AS mlo ON mla.id = mlo.m_kl_aksi_id LEFT JOIN t_kl_target AS tkt ON mlo.id = tkt.m_kl_output_id WHERE mla.nama_aksi ILIKE :keyword AND tkt.nama_target IS NOT NULL ORDER BY tkt.urutan_target ASC, tkt.id ASC";
                } 
             // bila optionnya output
            }else if ($getParams["options"] == "output"){
                $query = "SELECT tkt.nama_verifikator_b03, tkt.nama_verifikator_b06, tkt.nama_verifikator_b09, tkt.nama_verifikator_b12, tkt.nama_verifikator_b15, tkt.nama_verifikator_b18, tkt.nama_verifikator_b21, tkt.nama_verifikator_b24, tkt.tanggal_verifikator_b03 AS tanggal_verifikator_b03, tkt.tanggal_verifikator_b06 AS tanggal_verifikator_b06, tkt.tanggal_verifikator_b09 AS tanggal_verifikator_b09, tkt.tanggal_verifikator_b12 AS tanggal_verifikator_b12, tkt.tanggal_verifikator_b15 AS tanggal_verifikator_b15, tkt.tanggal_verifikator_b18 AS tanggal_verifikator_b18, tkt.tanggal_verifikator_b21 AS tanggal_verifikator_b21, tkt.tanggal_verifikator_b24 AS tanggal_verifikator_b24, tkt.indikator_penilaian_target AS indikator_penilaian_target, tkt.id AS t_kl_target_id, tkt.nilai_b03 AS nilai_b03, tkt.nilai_b06 AS nilai_b06, tkt.nilai_b09 AS nilai_b09, tkt.nilai_b12 AS nilai_b12, tkt.nilai_b15 AS nilai_b15, tkt.nilai_b18 AS nilai_b18, tkt.nilai_b21 AS nilai_b21, tkt.nilai_b24 AS nilai_b24, tkt.keterangan_target_b03 AS keterangan_target_b03, tkt.keterangan_target_b06 AS keterangan_target_b06, tkt.keterangan_target_b09 AS keterangan_target_b09, tkt.keterangan_target_b12 AS keterangan_target_b12, tkt.keterangan_target_b15 AS keterangan_target_b15, tkt.keterangan_target_b18 AS keterangan_target_b18, tkt.keterangan_target_b21 AS keterangan_target_b21, tkt.keterangan_target_b24 AS keterangan_target_b24, mla.nama_aksi AS nama_aksi, mlo.nama_output AS nama_output, tkt.nama_fokus AS nama_fokus, tkt.nama_target AS nama_target FROM m_kl_aksi AS mla LEFT JOIN m_kl_output AS mlo ON mla.id = mlo.m_kl_aksi_id LEFT JOIN t_kl_target AS tkt ON mlo.id = tkt.m_kl_output_id WHERE mlo.nama_output ILIKE :keyword AND tkt.nama_target IS NOT NULL ORDER BY tkt.urutan_target ASC, tkt.id ASC";
             // bila optionnya target
            }else if ($getParams["options"] == "target"){
                $query = "SELECT tkt.nama_verifikator_b03, tkt.nama_verifikator_b06, tkt.nama_verifikator_b09, tkt.nama_verifikator_b12, tkt.nama_verifikator_b15, tkt.nama_verifikator_b18, tkt.nama_verifikator_b21, tkt.nama_verifikator_b24, tkt.tanggal_verifikator_b03 AS tanggal_verifikator_b03, tkt.tanggal_verifikator_b06 AS tanggal_verifikator_b06, tkt.tanggal_verifikator_b09 AS tanggal_verifikator_b09, tkt.tanggal_verifikator_b12 AS tanggal_verifikator_b12, tkt.tanggal_verifikator_b15 AS tanggal_verifikator_b15, tkt.tanggal_verifikator_b18 AS tanggal_verifikator_b18, tkt.tanggal_verifikator_b21 AS tanggal_verifikator_b21, tkt.tanggal_verifikator_b24 AS tanggal_verifikator_b24, tkt.indikator_penilaian_target AS indikator_penilaian_target, tkt.id AS t_kl_target_id, tkt.nilai_b03 AS nilai_b03, tkt.nilai_b06 AS nilai_b06, tkt.nilai_b09 AS nilai_b09, tkt.nilai_b12 AS nilai_b12, tkt.nilai_b15 AS nilai_b15, tkt.nilai_b18 AS nilai_b18, tkt.nilai_b21 AS nilai_b21, tkt.nilai_b24 AS nilai_b24, tkt.keterangan_target_b03 AS keterangan_target_b03, tkt.keterangan_target_b06 AS keterangan_target_b06, tkt.keterangan_target_b09 AS keterangan_target_b09, tkt.keterangan_target_b12 AS keterangan_target_b12, tkt.keterangan_target_b15 AS keterangan_target_b15, tkt.keterangan_target_b18 AS keterangan_target_b18, tkt.keterangan_target_b21 AS keterangan_target_b21, tkt.keterangan_target_b24 AS keterangan_target_b24, mla.nama_aksi AS nama_aksi, mlo.nama_output AS nama_output, tkt.nama_fokus AS nama_fokus, tkt.nama_target AS nama_target FROM m_kl_aksi AS mla LEFT JOIN m_kl_output AS mlo ON mla.id = mlo.m_kl_aksi_id LEFT JOIN t_kl_target AS tkt ON mlo.id = tkt.m_kl_output_id WHERE tkt.nama_target ILIKE :keyword AND tkt.nama_target IS NOT NULL ORDER BY tkt.urutan_target ASC, tkt.id ASC";
            }
            
            // Eksekusi query dengan parameter binding
            $keyword = '%' . $getParams["keyword"] . '%';

            if($getParams["keyword"] == "all"){
                $results = $db->query($query, null);
            }else{
                $results = $db->query($query, ['keyword' => $keyword]);
            }
            

            $db->commit();

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

            if (!empty($data)) {
                return new SimpleResponse($data);
            } else {
                return new SimpleResponse(["message" => "Tidak ada hasil"]);
            }
        } catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

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

    public function submitLogframeTarget(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);

        $nilai_b03 = $getParams['nilai_b03'];
        $nilai_b06 = $getParams['nilai_b06'];
        $nilai_b09 = $getParams['nilai_b09'];
        $nilai_b12 = $getParams['nilai_b12'];
        $nilai_b15 = $getParams['nilai_b15'];
        $nilai_b18 = $getParams['nilai_b18'];
        $nilai_b21 = $getParams['nilai_b21'];
        $nilai_b24 = $getParams['nilai_b24'];
        $periode = $getParams["periode"];
        $um_user_name = $getParams["username"];

        $keterangan_target_b03 = $getParams['keterangan_target_b03'];
        $keterangan_target_b06 = $getParams['keterangan_target_b06'];
        $keterangan_target_b09 = $getParams['keterangan_target_b09'];
        $keterangan_target_b12 = $getParams['keterangan_target_b12'];
        $keterangan_target_b15 = $getParams['keterangan_target_b15'];
        $keterangan_target_b18 = $getParams['keterangan_target_b18'];
        $keterangan_target_b21 = $getParams['keterangan_target_b21'];
        $keterangan_target_b24 = $getParams['keterangan_target_b24'];

        $id = $getParams['t_kl_target_id'];
        
        $db->begin();
       
        try {
            $updateQuery = "UPDATE t_kl_target SET nilai_b03 = :nilai_b03, nilai_b06 = :nilai_b06, nilai_b09 = :nilai_b09, nilai_b12 = :nilai_b12, nilai_b15 = :nilai_b15, nilai_b18 = :nilai_b18, nilai_b21 = :nilai_b21, nilai_b24 = :nilai_b24, keterangan_target_b03 = :keterangan_target_b03, keterangan_target_b06 = :keterangan_target_b06, keterangan_target_b09 = :keterangan_target_b09, keterangan_target_b12 = :keterangan_target_b12, keterangan_target_b15 = :keterangan_target_b15, keterangan_target_b18 = :keterangan_target_b18, keterangan_target_b21 = :keterangan_target_b21, keterangan_target_b24 = :keterangan_target_b24 WHERE id = :id";
            
            $params = [
                'nilai_b03' => $nilai_b03,
                'nilai_b06' => $nilai_b06,
                'nilai_b09' => $nilai_b09,
                'nilai_b12' => $nilai_b12,
                'nilai_b15' => $nilai_b15,
                'nilai_b18' => $nilai_b18,
                'nilai_b21' => $nilai_b21,
                'nilai_b24' => $nilai_b24,
                'keterangan_target_b03' => $keterangan_target_b03,
                'keterangan_target_b06' => $keterangan_target_b06,
                'keterangan_target_b09' => $keterangan_target_b09,
                'keterangan_target_b12' => $keterangan_target_b12,
                'keterangan_target_b15' => $keterangan_target_b15,
                'keterangan_target_b18' => $keterangan_target_b18,
                'keterangan_target_b21' => $keterangan_target_b21,
                'keterangan_target_b24' => $keterangan_target_b24,
                'id' => $id
            ];

            if($periode == "B03"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b03 = :nama_verifikator, tanggal_verifikator_b03 = :tanggal_verifikator WHERE id = :id"; 
            }else if($periode == "B06"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b06 = :nama_verifikator, tanggal_verifikator_b06 = :tanggal_verifikator WHERE id = :id"; 
            }else if($periode == "B09"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b09 = :nama_verifikator, tanggal_verifikator_b09 = :tanggal_verifikator WHERE id = :id"; 
            }else if($periode == "B12"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b12 = :nama_verifikator, tanggal_verifikator_b12 = :tanggal_verifikator WHERE id = :id";
            }else if($periode == "B15"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b15 = :nama_verifikator, tanggal_verifikator_b15 = :tanggal_verifikator WHERE id = :id";
            }else if($periode == "B18"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b18 = :nama_verifikator, tanggal_verifikator_b18 = :tanggal_verifikator WHERE id = :id";
            }else if($periode == "B21"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b21 = :nama_verifikator, tanggal_verifikator_b21 = :tanggal_verifikator WHERE id = :id";
            }else if($periode == "B24"){
                $updateVerifikator = "UPDATE t_kl_target SET nama_verifikator_b24 = :nama_verifikator, tanggal_verifikator_b24 = :tanggal_verifikator WHERE id = :id";
            }

            $user = $this->shared->user;
            $umId = $user->um_id;
            $namaUser = $this->CheckNamaUser($umId);
            $paramsVerifikator = [
                'nama_verifikator' => $namaUser[0]['nama'],
                'tanggal_verifikator' => date('Y-m-d H:i:s'),
                'id' => $id
            ];

            // Eksekusi query
            $result = $db->execute($updateQuery, $params);
            $result_verifikator = $db->execute($updateVerifikator, $paramsVerifikator);

            $db->commit();

            return new SimpleResponse([
                "Update 1" => $result,
                "Update 2" => $result_verifikator
            ]);
        } catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

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

    public function CheckTanggalPengisian(){
        $dbJaga = $this->getDI()->get('db');
        // $getParams = $this->request->getJsonRawBody(true);
        $dbJaga->begin();
        try {
            $query = "SELECT * FROM str_periode_triwulan WHERE id_periode = 4 ORDER BY id_triwulan ASC";
            $result = $dbJaga->query($query, null);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);
            $dbJaga->commit();
            if (!empty($data)) {
                return new SimpleResponse($data);
            } else {
                return new SimpleResponse(["message" => "Tidak ada hasil"]);
            }
        }catch(Exception $e){

            $dbJaga->rollback();

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

    private function CheckNamaUser($um_user_name){
        $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' => $um_user_name
            ]);
            // 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()
            ]);
        } 
    }

}