<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Phalcon\Db\Adapter\Pdo\Postgresql as DbAdapter;

class StranaspkLogframeReportController extends BaseController
{
    public function report01() {
        // Laporan mengenai laporan K/L pada id_periode
        $this->checkIsAuthenticated();
        // $getParams = $this->request->get();
        // $params = $this->validate(
        //     $getParams,
        //     [
        //         'id_periode' => ['validators' => ['required', 'digit']],
        //     ]
        // );
        // $id_periode = $params['id_periode'];

        // dapatkan current triwulan
        $logframeStore = new SleekDbStore('logframe');
        $reportsStore = new SleekDbStore('reports');
        $verificationsStore = new SleekDbStore('verifications');
        $data = [
            'logframe' => $logframeStore->findAll(),
            'reports' => $reportsStore->findAll(),
            'verifications' => $verificationsStore->findAll(),
            'triwulan' => $this->triwulanNow(),
            'findNamaInstansi' => $this->findNamaInstansiAllData(),
        ];
        return new SimpleResponse($data);
        // $milestones = $logframeStore->findBy([
        //     ["level", "=", 4],
        //     ["id_periode", "=", intval($triwulan['id_periode'])],
        //     function($logframe){
        //         return !isset($logframe['is_deleted']);
        //     }
        // ],
        //     ["_id" => "asc"]);
        
        // // print_r(sizeof($milestones)); die;

        // $matrix = [];
        // foreach($milestones as $milestone) {
        //     $output = $logframeStore->findById($milestone['id_parent']);
        //     $outputName = $output['id_logframe'].' '.strip_tags($output['name']['long']);
        //     $outputName = str_replace('&nbsp;', ' ',$outputName);
        //     $aksi = $logframeStore->findById($output['id_parent']);
        //     $fokus = $logframeStore->findById($aksi['id_parent']);

        //     // collect the reports
        //     $reports = [];
        //     if (isset($milestone["reports"]) && is_array($milestone["reports"]) && sizeof($milestone["reports"]) > 0) {
        //         foreach($milestone["reports"] as $id_report) {
        //             $report = $reportsStore->findById($id_report);
        //             if (!$report) continue; // not found, skip
        //             if (array_key_exists('is_deleted', $report) && $report['is_deleted']) continue; // is_deleted -> skipped
        //             if (intval($report['id_triwulan']) > intval($triwulan['id_triwulan']))
        //                 continue;
        //                 // sebenarnya ini impossible case: seharusnya tidak ada report yang id_triwulan-nya melewati triwulan saat ini

        //             $id_instansi = $report["id_instansi"];
        //             $id_triwulan = $report["id_triwulan"];

        //             // report['id_triwulan'] menjadi key
        //             $reports[$id_instansi][$id_triwulan] = [
        //                 "description" => $report["description"],
        //                 "created_at" => $report["created_at"],
        //                 "created_by" => $this->selectNamaUser($report["created_by"])
        //             ];
        //         }
        //     }

        //     // Collect the verifications
        //     $verification = null;
        //     if (sizeof($milestone["verifications"]) > 0) {
        //         foreach($milestone["verifications"] as $id_verification) {
        //             $tempVerification = $verificationsStore->findById($id_verification);
        //             if (!$tempVerification) continue; // not found, skip
        //             if (array_key_exists('is_deleted', $tempVerification) && $tempVerification['is_deleted']) continue; // is_deleted -> skipped
        //             if (intval($tempVerification['id_triwulan']) > intval($triwulan['id_triwulan']))
        //                 continue;
        //                 // sebenarnya ini impossible case: seharusnya tidak ada verification yang id_triwulan-nya melewati triwulan saat ini

        //             $id_triwulan = $tempVerification['id_triwulan'];

        //             $verification[$id_triwulan] = [
        //                 "process" => $tempVerification["process"],
        //                 "result" => $tempVerification["result"],
        //                 "score" => $tempVerification["score"] ? $tempVerification["score"]["value"] : null,
        //                 "created_at" => $tempVerification["created_at"],
        //                 "created_by" => $this->selectNamaUser($tempVerification["created_by"])
        //             ];
        //         }
        //     }

        //     // memasukkan data ke matrix
        //     foreach($milestone['id_instansi'] as $id_instansi) {
        //         $milestoneName = $milestone['id_logframe'].' '.strip_tags($milestone['name']['long']);
        //         $milestoneName = str_replace('&nbsp;', ' ',$milestoneName);
        //         $namaInstansi = $this->findNamaInstansi($id_instansi);
        //         $idLogframeParsed = explode(".", $milestone['id_logframe']); // contoh: 1.3.2
        //         // -> urut_aksi = 1, urut_output = 3, urut_milestone = 2
        //         $urut_aksi = intval($idLogframeParsed[0]);
        //         $urut_output = intval($idLogframeParsed[1]);
        //         $urut_milestone = intval($idLogframeParsed[2]);
        //         $row = [
        //             'klpd' => $namaInstansi, // key 'klpd' hanya untuk pengurut
        //             $namaInstansi,
        //             $fokus['name']['long'],
        //             $aksi['id_logframe'].' '.$aksi['name']['long'],
        //             $outputName,
        //             $output['weight'],
        //             $milestoneName,
        //             strip_tags($milestone['target']),
        //             $milestone['weight']
        //         ];

        //         // memasukkan report dan verification sesuai klasifikasi id_triwulan
        //         $reportVerification = [];
        //         for($i = 1; $i <= intval($triwulan['id_triwulan']); $i++) {
        //             $row = array_merge(
        //                 $row,
        //                 [
        //                     $reports[$id_instansi][$i]['created_by'] ?? '',
        //                     $reports[$id_instansi][$i]['created_at'] ?? '',
        //                     $reports[$id_instansi][$i]['description'] ?? '',
        //                     $verification[$i]['created_by'] ?? '',
        //                     $verification[$i]['created_at'] ?? '',
        //                     $verification[$i]['process'] ?? '',
        //                     $verification[$i]['result'] ?? '',
        //                     $verification[$i]['score'] ?? null,
        //                 ]
        //             );
        //         }
        //         $row = array_merge(
        //             $row,
        //             [
        //                 $urut_aksi,
        //                 $urut_output,
        //                 $urut_milestone,
                        
        //             ]
        //         );
        //         $matrix[] = $row;
        //     }
        // }
        // return new SimpleResponse($matrix);
        // function custom_sort($a, $b)
        // {
        //     return strnatcmp($a['klpd'], $b['klpd']);
        // }
        // usort($matrix, "custom_sort");
        
        // $spreadsheet = new Spreadsheet();
        // $sheet = $spreadsheet->getActiveSheet();
        // $sheet->setCellValue('A1', 'K/L/D');
        // $sheet->setCellValue('B1', 'Fokus');
        // $sheet->setCellValue('C1', 'Aksi');
        // $sheet->setCellValue('D1', 'Output');
        // $sheet->setCellValue('E1', 'Bobot Output');
        // $sheet->setCellValue('F1', 'Milestone');
        // $sheet->setCellValue('G1', 'Target');
        // $sheet->setCellValue('H1', 'Bobot Milestone');

        // // kolom-kolom triwulanan
        // $currentCol = 8;
        // for($i = 0; $i < intval($triwulan['id_triwulan']); $i++) {
        //     $namaTriwulan = 'B'.str_pad(($i + 1) * 3, 2, '0', STR_PAD_LEFT);
        //     $sheet->setCellValueByColumnAndRow(9 + $i * 8, 1, 'Nama Pelapor '.$namaTriwulan);
        //     $sheet->setCellValueByColumnAndRow(10 + $i * 8, 1, 'Tanggal Pelaporan '.$namaTriwulan);
        //     $sheet->setCellValueByColumnAndRow(11 + $i * 8, 1, 'Narasi Laporan '.$namaTriwulan);
        //     $sheet->setCellValueByColumnAndRow(12 + $i * 8, 1, 'Nama Verifikator '.$namaTriwulan);
        //     $sheet->setCellValueByColumnAndRow(13 + $i * 8, 1, 'Tanggal Verifikasi '.$namaTriwulan);
        //     $sheet->setCellValueByColumnAndRow(14 + $i * 8, 1, 'Narasi Verifikasi - Proses '.$namaTriwulan);
        //     $sheet->setCellValueByColumnAndRow(15 + $i * 8, 1, 'Narasi Verifikasi - Hasil '.$namaTriwulan);
        //     $sheet->setCellValueByColumnAndRow(16 + $i * 8, 1, 'Nilai Verifikasi '.$namaTriwulan);
        //     $currentCol += 8;
        // }

        // $sheet->setCellValueByColumnAndRow($currentCol + 1, 1, 'urut_aksi');
        // $sheet->setCellValueByColumnAndRow($currentCol + 2, 1, 'urut_output');
        // $sheet->setCellValueByColumnAndRow($currentCol + 3, 1, 'urut_milestone');
        
        // masukkan ke matrix
        // for($i = 0; $i < sizeof($matrix); $i++) {
        //     unset($matrix[$i]['klpd']); // index pengurutan agar dibuang
        //     for ($j = 0; $j < sizeof($matrix[$i]); $j++) {
        //         $sheet->setCellValueByColumnAndRow($j + 1, $i + 2, $matrix[$i][$j]);
        //     }
        // }

        // $writer = new Xlsx($spreadsheet);
        // save output 2
        // date_default_timezone_set('Asia/Jakarta');
        // $waktu_indonesia = time();
        // $outputFilename = $argv[1] ?? date('Ymdhis', $waktu_indonesia)."-laporan01.xlsx";
        
        // header('Content-type: application/vnd.ms-excel');
        // header('Content-Disposition: attachment; filename="' . $outputFilename . '"');
        // return $writer->save('php://output');

    }

    public function report02() {

        $this->checkIsAuthenticated();
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
                'id_triwulan' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['digit']]
            ]
        );
        $id_periode = $params['id_periode'];
        $id_triwulan = $params['id_triwulan'];
        $id_instansi = $params['id_instansi'] ?? null;
        
        $logframeStore = new SleekDbStore('logframe');
        $verificationsStore = new SleekDbStore('verifications');

        $criteria = [
            ["level", "=", 4],
            ["id_periode", "=", intval($id_periode)],
            function($logframe){
                return !isset($logframe['is_deleted']);
            }
        ];
        if ($id_instansi) {
            $criteria[] = ["id_instansi", "CONTAINS", intval($id_instansi)];
        }

        $milestones = $logframeStore->findBy(
            $criteria,
            [   
                "_id" => "asc",
                "id_logframe" => "asc"
            ]
        );

        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $milestoneSheet = $spreadsheet->getActiveSheet();
        $milestoneSheet->setTitle('Milestones');
        $milestoneSheet->setCellValue('A1', 'Nama');
        $milestoneSheet->setCellValue('B1', 'Bobot');
        $milestoneSheet->setCellValue('C1', 'Score');

        // urutkan milestones berdasarkan id_logframe
        $milestones = $this->sortMilestonesByIdLogframe($milestones);

        // printkan 
        for ($i = 0; $i < sizeof($milestones); $i++) {
            
            // score, if there is any verification
            $milestones[$i]["score"] = 0;
            if (array_key_exists("verifications", $milestones[$i])
                && (sizeof($milestones[$i]["verifications"]) > 0)) {
                foreach($milestones[$i]["verifications"] as $id_verification) {
                    // cari yang id_triwulan-nya tepat
                    $verification = $verificationsStore->findById($id_verification);
                    if (is_null($verification) || !$verification) continue;
                    if (array_key_exists('is_deleted', $verification) &&
                        $verification['is_deleted']) {
                        continue;
                    }
                    if (!$verification || $verification['id_triwulan'] !== $id_triwulan) continue;
                    $milestones[$i]["score"] = $verification['score']['value'] ?? 0;
                }
            }

            $milestones[$i]["spreadsheet_name"] = $milestones[$i]["id_logframe"]." ".strip_tags($milestones[$i]["name"]["long"]);
            $milestones[$i]["spreadsheet_name"] = str_replace('&nbsp;', ' ',$milestones[$i]["spreadsheet_name"]);
            $milestoneSheet->setCellValueByColumnAndRow(1, $i + 2, $milestones[$i]["spreadsheet_name"]);
            $milestoneSheet->setCellValueByColumnAndRow(2, $i + 2, $milestones[$i]["weight"]);
            $milestoneSheet->setCellValueByColumnAndRow(3, $i + 2, $milestones[$i]["score"]);
        }
        
        
        // Output
        $outputs = [];
        $outputSheet = [];
        foreach($milestones as $milestone) {
            $output = null;
            if (!array_key_exists($milestone['id_parent'], $outputs)) {
                $output = $logframeStore->findById($milestone['id_parent']);
                $outputs[$milestone['id_parent']] = $output;
                $outputs[$milestone['id_parent']]['children'][] = $milestone;
            } else {
                $outputs[$milestone['id_parent']]['children'][] = $milestone;
                $output = $outputs[$milestone['id_parent']];
            }
        }

        $outputSheet = new Worksheet($spreadsheet, 'Output');
        $spreadsheet->addSheet($outputSheet, 1);
        $spreadsheet->setActiveSheetIndex(1);
        $outputSheet = $spreadsheet->getActiveSheet();
        $outputSheet->setCellValue('A1', 'Nama');
        $outputSheet->setCellValue('B1', 'Bobot');
        $outputSheet->setCellValue('C1', 'Score');
        $outputSheet->setCellValue('D1', 'Sum');
        
        $i = 0;
        foreach($outputs as $id_logframe_milestone => $output) {
            
            $outputs[$id_logframe_milestone]["spreadsheet_name"] = 
                $outputs[$id_logframe_milestone]["id_logframe"]." ".strip_tags($outputs[$id_logframe_milestone]["name"]["long"]);
            $outputs[$id_logframe_milestone]["spreadsheet_name"] =
                str_replace('&nbsp;', ' ',$outputs[$id_logframe_milestone]["spreadsheet_name"]);
            $outputSheet->setCellValueByColumnAndRow(1, $i + 2, $outputs[$id_logframe_milestone]["spreadsheet_name"]);
            $outputSheet->setCellValueByColumnAndRow(2, $i + 2, $output["weight"]);
            
            // sum the milestone's scores
            $sum = 0;
            foreach($output["children"] as $milestone) {
                // show the milestones
                $i++;
                $outputSheet->setCellValueByColumnAndRow(1, $i + 2, $milestone["spreadsheet_name"]);
                $outputSheet->setCellValueByColumnAndRow(2, $i + 2, $milestone["weight"]);
                $outputSheet->setCellValueByColumnAndRow(3, $i + 2, $milestone["score"]);
                
                // score percentage dari milestone ke output
                $scorePercentage = floatval($milestone["weight"]) * floatval($milestone["score"]) / 100;
                $outputSheet->setCellValueByColumnAndRow(4, $i + 2, $scorePercentage);
                $sum += $scorePercentage; // sum the score percentage for output score
            }
            $outputs[$id_logframe_milestone]["score"] = $sum; // record the score of output
            // show the sum
            $i++;
            $outputSheet->setCellValueByColumnAndRow(4, $i + 2, $sum);
            
            $i = $i + 2; // jump one line, for the sake of readability
        }

        // Aksi
        $aksis = [];
        $aksiSheet = [];
        foreach($outputs as $output) {
            $aksi = null;
            if (!array_key_exists($output['id_parent'], $aksis)) {
                $aksi = $logframeStore->findById($output['id_parent']);
                $aksis[$output['id_parent']] = $aksi;
                $aksis[$output['id_parent']]['children'][] = $output;
            } else {
                $aksis[$output['id_parent']]['children'][] = $output;
                $aksi = $aksis[$output['id_parent']];
            }
        }
        
        $aksiSheet = new Worksheet($spreadsheet, 'Aksi');
        $spreadsheet->addSheet($aksiSheet, 2);
        $spreadsheet->setActiveSheetIndex(2);
        $aksiSheet = $spreadsheet->getActiveSheet();
        $aksiSheet->setCellValue('A1', 'Nama');
        $aksiSheet->setCellValue('B1', 'Bobot');
        $aksiSheet->setCellValue('C1', 'Score');
        $aksiSheet->setCellValue('D1', 'Sum');

        $i = 0;
        foreach($aksis as $id_logframe_output => $aksi) {
            $aksiSheet->setCellValueByColumnAndRow(1, $i + 2, $aksi["id_logframe"]." ".$aksi["name"]["long"]);
            $aksiSheet->setCellValueByColumnAndRow(2, $i + 2, $aksi["weight"]);
            
            // sum the output's scores
            $sum = 0;
            foreach($aksi["children"] as $output) {
                // show the outputs
                $i++;
                $aksiSheet->setCellValueByColumnAndRow(1, $i + 2, $output["spreadsheet_name"]);
                $aksiSheet->setCellValueByColumnAndRow(2, $i + 2, $output["weight"]);
                $aksiSheet->setCellValueByColumnAndRow(3, $i + 2, $output["score"]);
                $scorePercentage = floatval($output["weight"]) * floatval($output["score"]) / 100;
                $aksiSheet->setCellValueByColumnAndRow(4, $i + 2, $scorePercentage);
                $sum += $scorePercentage;
            }
            $aksis[$id_logframe_output]["score"] = $sum; // record the score of output
            // show the sum
            $i++;
            $aksiSheet->setCellValueByColumnAndRow(4, $i + 2, $sum);
            
            $i = $i + 2; // jump one line, for the sake of readability
        }

        $stranasPk = [];

        $stranasPkSheet = new Worksheet($spreadsheet, 'Stranas PK');
        $spreadsheet->addSheet($stranasPkSheet, 3);
        $spreadsheet->setActiveSheetIndex(3);
        $stranasPkSheet = $spreadsheet->getActiveSheet();
        $stranasPkSheet->setCellValue('A1', 'Nama');
        $stranasPkSheet->setCellValue('B1', 'Bobot');
        $stranasPkSheet->setCellValue('C1', 'Score');
        $stranasPkSheet->setCellValue('D1', 'Sum');

        $i = 0;
        $sum = 0;
        foreach($aksis as $id_logframe_output => $aksi) {
            $stranasPkSheet->setCellValueByColumnAndRow(1, $i + 2, $aksi["id_logframe"]." ".$aksi["name"]["long"]);
            $stranasPkSheet->setCellValueByColumnAndRow(2, $i + 2, $aksi["weight"]);
            $stranasPkSheet->setCellValueByColumnAndRow(3, $i + 2, $aksi["score"]);
            $scorePercentage = floatval($aksi["weight"]) * floatval($aksi["score"]) / 100;
            $stranasPkSheet->setCellValueByColumnAndRow(4, $i + 2, $scorePercentage);
            
            // show the sum
            $sum += $scorePercentage;
            $i++;
        }
        $i++;
        $stranasPkSheet->setCellValueByColumnAndRow(4, $i + 2, $sum);

        // Kembalikan yang active ke sheet pertama
        $spreadsheet->setActiveSheetIndex(0);
        $writer = new Xlsx($spreadsheet);
        // save output 2
        date_default_timezone_set('Asia/Jakarta');
        $waktu_indonesia = time();
        $outputFilename = $argv[1] ?? date('Ymdhis', $waktu_indonesia)."-laporan02.xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $outputFilename . '"');
        return $writer->save('php://output');
    }

    public function report03() {

        // CAUTION: HARUS History dan harus sesuai triwulan!!!!
        // saat ini baru Triwulan B03 saja

        // rekapitulasi setiap K/L/D:
        // 1. Nama K/L/D
        // 2. Kontribusi di StranasPK
        // 3. Capaian B03
        // 4. Capaian B06 dst

        $this->checkIsAuthenticated();
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
            ]
        );
        $id_periode = $params['id_periode'];

        $logframeStore = new SleekDbStore('logframe');
        
        $queryBuilder = $logframeStore->createQueryBuilder()
            ->where(
                [
                    ["id_periode", "=", intval($id_periode)],
                    ["level", "=", 4], // milestone level
                    function($logframe){
                        return !isset($logframe['is_deleted']);
                    }
                ]
            )
            ->select(['_id', 'id_instansi', 'id_parent', 'weight', 'verifications'])
            ->orderBy(["_id" => "asc", "id_logframe" => "asc"]);
        $milestones = $queryBuilder
            ->getQuery()
            ->fetch();

        for($i = 0; $i < sizeof($milestones); $i++) {
            // hitung nilai stranasPK dan max_contribution setiap milestones
            $calculation = $this->calculateStranasPK($milestones[$i]);
            $milestones[$i]["max_contribution"] = $calculation["max_contribution"];
            $milestones[$i]["achievement_B09"] = $calculation["achievement"]; // CAUTION
        }

        // populate the K/L/D
        $achievements_per_instansi = [];
        foreach($milestones as $milestone) {
            foreach($milestone["id_instansi"] as $id_instansi_item) {
                if (array_key_exists($id_instansi_item, $achievements_per_instansi)) {
                    $achievements_per_instansi[$id_instansi_item]["max_contribution"] +=
                        $milestone["max_contribution"];
                    $achievements_per_instansi[$id_instansi_item]["achievement_B09"] +=
                        $milestone["achievement_B09"];
                        $achievements_per_instansi[$id_instansi_item]["nMilestone"]++;
                } else {
                    $achievements_per_instansi[$id_instansi_item]["max_contribution"] =
                        $milestone["max_contribution"];
                    $achievements_per_instansi[$id_instansi_item]["achievement_B09"] =
                        $milestone["achievement_B09"];
                    $achievements_per_instansi[$id_instansi_item]["nMilestone"] = 1;
                }
            }
        }

        // populate the max_contribution of each $achievements_per_instansi[$id_instansi_item]
        // untuk keperluan perhitungan persentase kontribusi tiap K/L
        $sum_max_contribution = 0;
        foreach($achievements_per_instansi as $id_instansi_item => $instansi) {
            $sum_max_contribution += $instansi["max_contribution"];
        }
        
        foreach($achievements_per_instansi as $id_instansi => $data) {
            // Hitung share max kontribusi tiap KLD secara persentase
            $achievements_per_instansi[$id_instansi]["share_contribution"] = 
                $achievements_per_instansi[$id_instansi]["max_contribution"] /
                $sum_max_contribution * 100;

            $achievements_per_instansi[$id_instansi]["share_achievement_B09"] = 
                $achievements_per_instansi[$id_instansi]["achievement_B09"] /
                $sum_max_contribution * 100;

            // Hitung score Stranas PK per KLD
            $achievements_per_instansi[$id_instansi]["score"] = 
                $achievements_per_instansi[$id_instansi]["achievement_B09"] /
                $achievements_per_instansi[$id_instansi]["max_contribution"] * 100;
            // Dapatkan nama instansi
            $achievements_per_instansi[$id_instansi]["nama_instansi"] =
                $this->findNamaInstansi($id_instansi);
        }
        
        // urutkan 
        function custom_sort($a, $b)
        {
            return strnatcmp($a['nama_instansi'], $b['nama_instansi']);
        }
        usort($achievements_per_instansi, "custom_sort");


        // Print the result
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'K/L/D');
        $sheet->setCellValue('B1', 'Jumlah Milestone per KL');
        $sheet->setCellValue('C1', 'Capaian Maksimal KLD');
        $sheet->setCellValue('D1', '%');
        $sheet->setCellValue('E1', 'Capaian Aktual KLD');
        $sheet->setCellValue('F1', '% dari Total Capaian Aksi PK');
        $sheet->setCellValue('G1', '% dari Capaian Maksimal KLD');

        for($i = 0; $i < sizeof($achievements_per_instansi); $i++) {
            $sheet->setCellValueByColumnAndRow(1,
                $i + 2,
                $achievements_per_instansi[$i]["nama_instansi"]);
            $sheet->setCellValueByColumnAndRow(2,
                $i + 2,
                $achievements_per_instansi[$i]["nMilestone"]);
            $sheet->setCellValueByColumnAndRow(3,
                $i + 2,
                $achievements_per_instansi[$i]["max_contribution"]);
            $sheet->setCellValueByColumnAndRow(4,
                $i + 2,
                $achievements_per_instansi[$i]["share_contribution"]);
            $sheet->setCellValueByColumnAndRow(5,
                $i + 2,
                $achievements_per_instansi[$i]["achievement_B09"]);
            $sheet->setCellValueByColumnAndRow(6,
                $i + 2,
                $achievements_per_instansi[$i]["share_achievement_B09"]);
            $sheet->setCellValueByColumnAndRow(7,
                $i + 2,
                $achievements_per_instansi[$i]["score"]);
        }

        $spreadsheet->setActiveSheetIndex(0);
        $writer = new Xlsx($spreadsheet);
        // save output 2
        date_default_timezone_set('Asia/Jakarta');
        $waktu_indonesia = time();
        $outputFilename = $argv[1] ?? date('Ymdhis', $waktu_indonesia)."-laporan03.xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $outputFilename . '"');
        return $writer->save('php://output');
    }

    public function report04() {
        // Laporan yang sudah komplit (diberi nilai 100)
        $this->checkIsAuthenticated();
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
            ]
        );
        
        $verificationsStore = new SleekDbStore('verifications');
        $logframeStore = new SleekDbStore('logframe');
        $completeVerifications = $verificationsStore->findBy([
            ['score.value', '=', '100.00'],
            // CAUTION: id_periode pun harus menjadi parameter select
        ]);
        $milestones = [];
        // di triwulan berapa milestone tersebut complete
        foreach($completeVerifications as $verification) {
            $milestone = $logframeStore->findById($verification['id_logframe']);
            if (intval($milestone['id_periode']) !== intval($params['id_periode'])) continue;
            if (isset($milestones[$milestone['id_logframe']])) {
                $milestones[$milestone['id_logframe']]['id_triwulan_complete'][] = $verification['id_triwulan'];
            } else {
                $milestone['id_triwulan_complete'] = [$verification['id_triwulan']];
                $milestones[$milestone['id_logframe']] = $milestone;
            }
        }

        // urutkan milestones by id_logframe
        $milestones = $this->sortMilestonesByIdLogframe($milestones);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Complete Milestone');
        $sheet->setCellValue('B1', 'B03');
        $sheet->setCellValue('C1', 'B06');
        $sheet->setCellValue('D1', 'B09');
        $sheet->setCellValue('E1', 'B12');
        $sheet->setCellValue('F1', 'B15');
        $sheet->setCellValue('G1', 'B18');
        $sheet->setCellValue('H1', 'B21');
        $sheet->setCellValue('I1', 'B24');

        $spreadsheet->setActiveSheetIndex(0);
        $row = 2;
        foreach($milestones as $milestone) {
            $milestone_name = $milestone["id_logframe"]." ".$milestone["name"]["long"];
            $sheet->setCellValueByColumnAndRow(1, $row, $milestone_name);
            
            foreach($milestone['id_triwulan_complete'] as $id_triwulan) {
                $sheet->setCellValueByColumnAndRow(1 + $id_triwulan, $row, 100);
            }
            $row++;
        }

        $writer = new Xlsx($spreadsheet);
        // save output 2
        date_default_timezone_set('Asia/Jakarta');
        $waktu_indonesia = time();
        $outputFilename = $argv[1] ?? date('Ymdhis', $waktu_indonesia)."-laporan04.xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $outputFilename . '"');
        return $writer->save('php://output');
    }

    private function findNamaInstansi($id_instansi) {
        // menemukan nama instansi
        $instansi = Instansi::findFirst(
            [
                "id = :id_instansi: and deleted_at is null",
                "bind" => [
                    "id_instansi" => $id_instansi
                ]
            ]
        );
        return $instansi ? $instansi->nama : '';
    }

    public function selectNamaUser($um_id) {
        // Mengambil data id dan nama dari um_user
        $um_user = UmUser::findFirst(
            [
                "um_id = :um_id: and is_active = true",
                "bind" => [
                    "um_id" => $um_id
                ]
            ]
        );
        return $um_user ? $um_user->nama : '';
    }

    public function selectNamaUserWhereIn(){
        $this->checkIsAuthenticated();
        $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('db');
        // Membuat placeholder untuk setiap nilai dalam array
        $placeholders = implode(', ', array_fill(0, count($getParams), '?'));
        // Membuat query dengan placeholder yang telah dibuat
        // $sql = "SELECT um_id, nama FROM um_user WHERE um_id IN ($placeholders)";
        $sql = "WITH id_array AS (SELECT unnest(ARRAY[$placeholders]::integer[]) AS um_id) SELECT ia.um_id, mt.nama FROM id_array ia LEFT JOIN um_user mt ON ia.um_id::text = mt.um_id::text";
        // Menjalankan raw query SELECT
        $result = $db->query($sql, $getParams);
        $data = $result->fetchAll(PDO::FETCH_ASSOC);
        return new SimpleResponse($data);
    }

    public function selectNamaUserAllData(){
        $um_user = UmUser::find();
        $user = array();
        foreach($um_user as $data){
            array_push($user, [
                "nama" => $data->nama,
                "um_id" => $data->um_id
            ]);
        }
        return $user;
    }

    public function findNamaInstansiAllData(){
        $instansi = Instansi::find();
        return $instansi ? $instansi : '';
    }

    private function calculateStranasPk($milestone) {
        // menghitung kontribusi, setiap jenjangnya, dari milestone sampai stranaspk
        $logframeStore = new SleekDbStore('logframe');
        $output = $logframeStore->findById($milestone['id_parent']);
        $aksi = $logframeStore->findById($output['id_parent']);
        $max_contribution = floatval($milestone['weight']) *
            floatval($output['weight']) / 100 *
            floatval($aksi['weight']) / 100;

        // cari nilai verifikasi
        $achievement = 0;
        $verificationsStore = new SleekDbStore('verifications');
        if (is_iterable($milestone["verifications"])) {
            foreach($milestone["verifications"] as $id_verification) {
                $verification = $verificationsStore->findById($id_verification);
                if (array_key_exists('is_deleted', $verification) &&
                    $verification['is_deleted']) {
                    continue;
                }
                
                // CAUTION!!!! B03 saja ini
                $score = !is_null($verification["score"]) ? $verification["score"]["value"] : 0;
                $achievement = intval($score) / 100 * intval($max_contribution);
            }
        }        

        return [
            "achievement" => $achievement,
            "max_contribution" => $max_contribution
        ];
    }

    private function triwulanNow() {
        // private method yang mengembalikan triwulan berjalan
        $now = date('Y-m-d H:i:s');
        $triwulan = StranasNewPeriodeTriwulan::findFirst([
            'conditions' => '{now} >= started_at AND {now} <= verification_ended_at',
            'bind' => [
                'now' => $now
            ]
        ]);
        
        return [
            'id_periode' => $triwulan ? $triwulan->id_periode : null,
            'id_triwulan' => $triwulan ? $triwulan->id_triwulan : null,
            'started_at' => $triwulan ? $triwulan->started_at : null,
            'ended_at' => $triwulan ? $triwulan->ended_at : null,
            'verification_started_at' => $triwulan ? $triwulan->verification_started_at : null,
            'verification_ended_at' => $triwulan ? $triwulan->verification_ended_at : null,
        ];
    }

    private function sortMilestonesByIdLogframe($presortMilestones){
        $milestones = $presortMilestones;
        // mengurutkan milestones berdasarkan id_logframe
        foreach($milestones as $index => $milestone){
            $id_logfame_extraction = explode('.', $milestone['id_logframe']);
            $milestones[$index]['no_aksi'] = intval($id_logfame_extraction[0]);
            $milestones[$index]['no_output'] = intval($id_logfame_extraction[1]);
            $milestones[$index]['no_milestone'] = intval($id_logfame_extraction[2]);
        }
        usort($milestones, function($a, $b) {
            if ($a['no_aksi'] !== $b['no_aksi']) {
                return $a['no_aksi'] <=> $b['no_aksi'];
            } elseif ($a['no_output'] !== $b['no_output']) {
                return $a['no_output'] <=> $b['no_output'];
            } elseif ($a['no_milestone'] !== $b['no_milestone']) {
                return $a['no_milestone'] <=> $b['no_milestone'];
            } else {
                return 0;
            }
        });
        return $milestones;
    }
}