<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class StranaspkNewDashboard2023Controller extends BaseController
{
  public function index() {
    // Function yang mengembalikan node Stranas PK
    // dan anak-anaknya (kalau ada, tidak termasuk cucu-cicit)
    $getParams = $this->request->get();  
    $params = $this->validate(
        $getParams,
        [
          'id_periode' => ['validators' => ['required', 'digit']],
          'id_logframe' => ['validators' => []],
          'id_instansi' => ['validators' => []],
        ]
    );

    if (!isset($params['id_instansi'])) {
      return $this->retrieveGeneralLogframe($params); // semua instansi
    } else {
      return $this->retrieveInstansiLogframe($params); // khusus instansi tertentu
    }
  }

  public function klSummary() {
    // menghitung summary nilai seluruh K/L 
    $getParams = $this->request->get();
    $params = $this->validate(
      $getParams,
      [
        'id_periode' => ['validators' => ['required', 'digit']],
        'id_tipe' => ['validators' => ['required', 'digit']]
      ]
    );
    $id_periode = intval($params['id_periode']);
    $id_tipe = intval($params['id_tipe']);
    
    $dashboardStore = new SleekDbStore('dashboard');
    $instansiStore = new SleekDbStore('instansi');
    
    // get all instansi pada id_periode dan id_tipe tertentu
    $daftarInstansi = $instansiStore->findBy([
      ['id_periode', 'CONTAINS', $id_periode],
      ['id_tipe', '=', $id_tipe]
    ],[
      'nama' => 'asc'
    ]);
    // param untuk select dashboard berdasarkan id_instansi
    $instansiParams = null;
    if (!is_null($daftarInstansi)) {
      $instansiParams = [['id_instansi', 'CONTAINS', intval($daftarInstansi[0]['id_instansi'])]];
      for ($i = 1; $i < sizeof($daftarInstansi); $i++) {
        $instansiParams[] = 'OR';
        $instansiParams[] = ['id_instansi', 'CONTAINS', intval($daftarInstansi[$i]['id_instansi'])];
      }
    }

    // ambil id_triwulan terbesar pada id_periode tersebut
    $maxIdTriwulan = $dashboardStore
      ->createQueryBuilder()
      ->select(["maxIdTriwulan" => ["MAX" => "id_triwulan"]])
      ->where( [ 
        ['id_periode', '=', $id_periode],
        function($dashboardItem) {
          return !isset($dashboardItem['is_deleted']);
        }] )
      ->getQuery()
      ->fetch();  
    $id_triwulan = $maxIdTriwulan[0]['maxIdTriwulan']; // asumsi: selalu ada max id_triwulan di dashboardStore

    // dapatkan dashboard items: hanya yang tidak dihapus dan hanya yg triwulan terkini
    $dashboardItems = $dashboardStore->findBy([
      ['id_periode', '=', $id_periode],
      ['id_triwulan', '=', $id_triwulan],
      ['level', '=', 4],
      [$instansiParams],
      function($dashboardItem) {
        return !isset($dashboardItem['is_deleted']);
      },
    ]);

    // hitung score per instansi
    $scorePerInstansi = [];
    foreach($daftarInstansi as $instansi) {
      
      $scorePerInstansi[$instansi['id_instansi']] = [
        'id_instansi' => $instansi['id_instansi'],
        'nama' => $instansi['nama'],
        'jumlah_kontribusi_permilestone' => [],
        'score' => 0,
        'max_contribution' => 0,
        'max_contribution_per_milestone' => [],
        'pelaksana' => [],
        'target_kld' => 0,
        'score_per_milestone' => [],
        'kontribusi_aktual' => [],
        'nilai_total_kontribusi_aktual' => 0,
        'b06-kld' => []
      ];
      
      if (array_key_exists('id_provinsi', $instansi)) {
        $scorePerInstansi[$instansi['id_instansi']]['id_provinsi'] = $instansi['id_provinsi'];
      }
      if (array_key_exists('id_kota_kabupaten', $instansi)) {
        $scorePerInstansi[$instansi['id_instansi']]['id_kota_kabupaten'] = $instansi['id_kota_kabupaten'];
      }
      if (array_key_exists('id_provinsi', $instansi)) {
        $scorePerInstansi[$instansi['id_instansi']]['id_provinsi'] = $instansi['id_provinsi'];
      }
      if (array_key_exists('singkatan', $instansi)) {
        $scorePerInstansi[$instansi['id_instansi']]['singkatan'] = $instansi['singkatan'];
      }
    }
    
    foreach($dashboardItems as $item) {
      // if (!$item["score"] || is_null($item["score"])) continue; // harus ada score
      foreach($item['id_instansi'] as $id_instansi) {
        // $scorePerInstansi[$id_instansi]['max_peraksi'] = $item['max_contribution'] / count($item['id_instansi']);
        if (!array_key_exists($id_instansi, $scorePerInstansi)) continue;
        // lewat apabila id_instansi tidak termasuk dalam kategori parameter id_tipe di atas
        // array_push($scorePerInstansi[$id_instansi]['score_per_milestone'], $item['score'] / 100 * $item['max_contribution']); 
        // $scorePerInstansi[$id_instansi]['score'] += $item['score'] / 100 * $item['max_contribution'];
        // array_push($scorePerInstansi[$id_instansi]['max_contribution_per_milestone'], $item['max_contribution']);
        // array_push($scorePerInstansi[$id_instansi]['jumlah_kontribusi_permilestone'], $item['id_logframe']);
        // array_push($scorePerInstansi[$id_instansi]['pelaksana'], count($item['id_instansi']));
        // $scorePerInstansi[$id_instansi]['target_kld'] += ($item['max_contribution'] / count($item['id_instansi']));
        // array_push($scorePerInstansi[$id_instansi]['kontribusi_aktual'], ($item['score'] / 100 * $item['max_contribution'] /  count($item['id_instansi'])));
        // $scorePerInstansi[$id_instansi]['nilai_total_kontribusi_aktual'] +=  ($item['score'] / 100 * $item['max_contribution'] /  count($item['id_instansi']));
        // $scorePerInstansi[$id_instansi]['max_contribution'] += $item['max_contribution'];
        
        array_push($scorePerInstansi[$id_instansi]['score_per_milestone'], floatval($item['score']) / 100 * $item['max_contribution']); 
        $scorePerInstansi[$id_instansi]['score'] += floatval($item['score']) / 100 * $item['max_contribution'];
        array_push($scorePerInstansi[$id_instansi]['max_contribution_per_milestone'], $item['max_contribution']);
        array_push($scorePerInstansi[$id_instansi]['jumlah_kontribusi_permilestone'], $item['id_logframe']);
        array_push($scorePerInstansi[$id_instansi]['pelaksana'], count($item['id_instansi']));
        $scorePerInstansi[$id_instansi]['target_kld'] += ($item['max_contribution'] / count($item['id_instansi']));
        array_push($scorePerInstansi[$id_instansi]['kontribusi_aktual'], (floatval($item['score']) / 100 * $item['max_contribution'] /  count($item['id_instansi'])));
        $scorePerInstansi[$id_instansi]['nilai_total_kontribusi_aktual'] +=  (floatval($item['score']) / 100 * $item['max_contribution'] /  count($item['id_instansi']));
        $scorePerInstansi[$id_instansi]['max_contribution'] += $item['max_contribution'];
      }
    }
    // output
    $scores = [];
    foreach($scorePerInstansi as $scoreItem) {
      // hitung pencapaian dulu
      // $scorePerInstansi[$id_instansi]['achievement'] += $scoreItem['target_kld'];
      // $scoreItem['achievement'] = $scoreItem['score'] / $scoreItem['max_contribution'] * 100;
      $scoreItem['achievement'] = $scoreItem['nilai_total_kontribusi_aktual'] / $scoreItem['target_kld'] * 100;
      // $scoreItem['achievement2'] = $scoreItem['score'] / $scoreItem['max_contribution'] * 100;
      $scores[] = $scoreItem;
    }

    $response = [      
      "scores" => $scores,
      "id_periode" => $id_periode,
      "id_triwulan" => $id_triwulan
    ];
    return new SimpleResponse($response);
  }

  public function init() {
    // function untuk mengisiasi dashboard pada id_periode dan id_triwulan tertentu.
    // Perhatian: function ini hanya untuk dijalankan
    // pada saat tenaga ahli monev sudah menyatakan bahwa nilai verifikator
    // sudah final dan Stranas PK sedang dalam proses pelaporan ke presiden RI.
    // Data dari logframeStore dan verificationsStore akan dipetakan ke dashboardStore.

    // $this->checkIsAuthenticated();
    // $getParams = $this->request->getJsonRawBody(true);// CAUTION: akan diganti menjadi POST
    $getParams = $this->request->get();// CAUTION: akan diganti menjadi POST
    $myParams = $this->validate(
        $getParams,
        [
          'id_periode' => ['validators' => ['required', 'digit']],
          'id_triwulan' => ['validators' => ['required', 'digit']],
        ]
    );
    // return new SimpleResponse($myParams);
    $id_periode = $myParams["id_periode"];
    $id_triwulan = $myParams["id_triwulan"];
    $logframeStore = new SleekDbStore("logframe");
    $verificationsStore = new SleekDbStore("verifications");
    $dashboardStore = new SleekDbStore('dashboard');
    $instansiStore = new SleekDbStore('instansi');

    /************/
    /* Inisiasi */
    /************/
    // Hapus semua yang sebelumnya ada pada dashboardStore
    // yang memiliki id_periode dan id_triwulan seperti pada input di atas

    $dashboardItems = $dashboardStore->findBy([
      ["id_periode", "=", intval($id_periode)],
      ["id_triwulan", "=", intval($id_triwulan)],
      function($dashboardItem) {
        return !isset($dashboardItem["is_deleted"]);
      }
    ]);
    foreach($dashboardItems as $dashboardItem) {
      $dashboardItem["is_deleted"] = true;
      $dashboardItem["deleted_at"] = date('Y-m-d H:i:s');
      $dashboardItem["deleted_by"] = $this->shared->user->um_id ?? 1; // CAUTION
      $dashboardStore->update($dashboardItem);
    }

    // Ambil milestones dari logframeStore yang tidak is_deleted
    $milestones = $logframeStore->findBy([
      ["id_periode", "=", intval($id_periode)],
      ["level", "=", 4],
      function($logframe){
          return !isset($logframe['is_deleted']);
      }], 
      ["_id" => "asc"]);

    $outputs = []; // himpunan output
    $aksis = []; // himpunan aksi
    $stranaspk = [
      "id_logframe" => "0",
      "name" => [
          "long" => "Strategi Nasional Pencegahan Korupsi",
          "short" => "Stranas PK"
      ],
      "score" => 0,
      "level" => 1,
      "id_periode" => intval($id_periode),
      "id_triwulan" => intval($id_triwulan),
      "weight" => 100,
      "created_at" => date('Y-m-d H:i:s'),
      "created_by" => $this->shared->user->um_id ?? 1 // CAUTION
    ]; // puncak piramida perhitungan Stranas PK, 1 data only

    /***************/
    /* Perhitungan */
    /***************/
    // hitung dulu semua nilai di tiap node logframe: milestone - output - aksi - stranaspk
    foreach($milestones as $key => $milestone) {
      // hitung kontribusi maksimal tiap milestone
      $output = $logframeStore->findById($milestone['id_parent']);

      // populate output
      if (!array_key_exists($output["_id"], $outputs)) {
          $output["score"] = 0; // score from 0
          $output["id_triwulan"] = intval($id_triwulan);
          $outputs[$output["_id"]] = $output;
      }

      // populate aksi
      $aksi = $logframeStore->findById($output['id_parent']);
      if (!array_key_exists($aksi["_id"], $aksis)) {
          $aksi["score"] = 0; // score from 0
          $aksi["id_triwulan"] = intval($id_triwulan); // id_triwulan
          $aksis[$aksi["_id"]] = $aksi;
      }
      
      $milestones[$key]['max_contribution'] = floatval($milestone['weight']) *
          floatval($output['weight']) / 100 *
          floatval($aksi['weight']) / 100; 
      $milestones[$key]["id_triwulan"] = intval($id_triwulan);
      // Ketika init juga akan menjalankan rumus baru
      // inisiasi properti dengan null
      $daftarIdInstansi = [];
      $daftarInstansiPadaMilestone = [];
      $countInstansiInMilestone = null;
      // kumpulkan daftar instansi pada logframe
      if(isset($milestone["id_instansi"])) {
        $countInstansiInMilestone = count($milestone["id_instansi"]);
        $daftarIdInstansi = $milestone["id_instansi"];
      }
      $daftarInstansi = $instansiStore->findBy(['id_instansi', 'IN', $daftarIdInstansi]);
      $instansiDiMilestone = [];
      foreach($daftarInstansi as $instansi) {
        $instansiDiMilestone[] = ['id_instansi' => $instansi['id_instansi'], 'nama' => $instansi['nama']];
      }
      
      // cari nilai verif
      $milestones[$key]["score"] = null;
      $nilaiVerifMilestone[$key]["score"] = null;
      $kumpulanScoreVerif = [];
      foreach($milestone["verifications"] as $id_verification) {
          $verification = $verificationsStore->findById($id_verification);
          if (isset($verification["is_deleted"])) continue;
          // hilangkan ini jika ingin kumpulkan score semua triwulan
          // if (intval($verification["id_triwulan"]) !== intval($id_triwulan)) continue;

          // kumpulkan nilai verif semua triwulan
          if(isset($verification["score"]["value"])) {
            $kumpulanScoreVerif[] = $verification["score"]["value"];
          }

          $nilaiVerifMilestone[$key]["score"] = $verification["score"]["value"] ?? null;
          // Jika $nilaiVerifMilestone[$key]["score"] tidak null dan mengandung koma, ganti koma dengan titik
          if ($nilaiVerifMilestone[$key]["score"] !== null && strpos($nilaiVerifMilestone[$key]["score"], ',') !== false) {
            // Menggunakan str_replace
            $nilaiVerifMilestone[$key]["score"] = str_replace(',', '.', $nilaiVerifMilestone[$key]["score"]);
          }
      }

      // start
      // jika pada triwulan sebelumnya ada 100 maka ambil 100 jika tidak maka ambil yang terakhir
      foreach($kumpulanScoreVerif as $score) {
        if(isset($score) && $score === "100.00") {
          $nilaiVerifMilestone[$key]["score"] = "100.00";
          $milestones[$key]["score"] = "100.00";
        } else {
          $nilaiVerifMilestone[$key]["score"] = end($kumpulanScoreVerif);
          $milestones[$key]["score"] = end($kumpulanScoreVerif);
        } 
      }
      if ($nilaiVerifMilestone[$key]["score"] !== null && strpos($nilaiVerifMilestone[$key]["score"], ',') !== false) {
          $nilaiVerifMilestone[$key]["score"] = str_replace(',', '.', $nilaiVerifMilestone[$key]["score"]);
      }
      // end 

      
      // jika null check ke triwulan sebelumnya apakah ada nilai 100
      if($nilaiVerifMilestone[$key]["score"] == null) {
        foreach($milestone["verifications"] as $id_verification) {
          $verification = $verificationsStore->findById($id_verification);
          if (isset($verification["is_deleted"])) continue;
          // jika pada triwulan sebelumnya ada nilai 100 maka ambil nilai 100
          if ($verification["score"]["value"] == "100.00") {
            $milestones[$key]["score"] = "100.00";
            $nilaiVerifMilestone[$key]["score"] = "100.00";
          }
        }
      }

      // START RUMUS BARU MILESTONE 
      $milestones[$key]["nilai_target_milestone_kontribusi"] = null;
      $milestones[$key]["nilai_aktual_milestone_kontribusi"] = null;
      $milestones[$key]["nilai_target_milestone_mandiri"] = 1;
      $milestones[$key]["nilai_aktual_milestone_mandiri"] = null;
      
      // print_r('bobot' . $milestones[$key]['weight']); die;
      $milestones[$key]['nilai_target_milestone_kontribusi'] = $milestones[$key]["weight"] / 100 * 1 / $countInstansiInMilestone * $countInstansiInMilestone;
      $milestones[$key]['nilai_aktual_milestone_kontribusi'] = floatval($milestones[$key]["weight"]) / 100 * 1 / $countInstansiInMilestone * floatval($nilaiVerifMilestone[$key]["score"]) / 100 * $countInstansiInMilestone;
      $milestones[$key]["nilai_aktual_milestone_mandiri"] = $milestones[$key]['nilai_aktual_milestone_kontribusi'] / $milestones[$key]['nilai_target_milestone_kontribusi'];
      
      // rumus nilai KLD
      $nilaiInstansiMilestone = [
        'kld_target_milestone_kontribusi' => $milestones[$key]["weight"] / 100 * 1 / $countInstansiInMilestone,
        'kld_aktual_milestone_kontribusi' => $milestones[$key]["weight"] / 100 * 1 / $countInstansiInMilestone * $nilaiVerifMilestone[$key]["score"] / 100,
        'kld_target_output_kontribusi' => $output["weight"] / 100 * $milestones[$key]["weight"] / 100 * 1 / $countInstansiInMilestone, // bobot output / 100 * bobot milestone / 100 * 1 / jumlah kld
        'kld_aktual_output_kontribusi' => $output["weight"] / 100 * $milestones[$key]["weight"] / 100 * 1 / $countInstansiInMilestone * $nilaiVerifMilestone[$key]["score"] / 100, // bobot output / 100 * bobot milestone / 100 * 1 / jumlah kld / * nilai verif / 100
        'kld_target_aksi_kontribusi' => 1 / 15 * $output["weight"] / 100 * $milestones[$key]["weight"] / 100 * 1 / $countInstansiInMilestone, // 1 / jumlah aksi * bobot output / 100 * bobot milestone / 100 * 1 / jumlah pelaksana
        'kld_aktual_aksi_kontribusi' => 1 / 15 * $output["weight"] / 100 * $milestones[$key]["weight"] / 100 * 1 / $countInstansiInMilestone * $nilaiVerifMilestone[$key]["score"] / 100, // 1 / jumlah aksi * bobot output / 100 * bobot milestone /100 * 1 / jumlah pelaksana * nilai verif / 100
      ];
      $nilaiInstansiMilestone['kld_target_milestone_mandiri'] = 1;
      $nilaiInstansiMilestone['kld_aktual_milestone_mandiri'] = $nilaiInstansiMilestone["kld_aktual_milestone_kontribusi"] / $nilaiInstansiMilestone["kld_target_milestone_kontribusi"];
      $nilaiInstansiMilestone['kld_target_output_mandiri'] = 1;
      $nilaiInstansiMilestone['kld_aktual_output_mandiri'] = $nilaiInstansiMilestone["kld_aktual_milestone_kontribusi"] / $nilaiInstansiMilestone["kld_target_milestone_kontribusi"]; // aktual output kontribusi / target output kontribusi
      $nilaiInstansiMilestone['kld_target_aksi_mandiri'] = 1;
      $nilaiInstansiMilestone['kld_aktual_aksi_mandiri'] = $nilaiInstansiMilestone["kld_aktual_aksi_kontribusi"] / $nilaiInstansiMilestone["kld_target_aksi_kontribusi"];

      for($i = 0; $i < count($instansiDiMilestone); $i++) {
        $instansiDiMilestone[$i] = array_merge($instansiDiMilestone[$i], $nilaiInstansiMilestone);
      }
      // instansi di milestone
      $milestones[$key]['nilai_kld'] = $instansiDiMilestone;

      // END RUMUS BARU MILESTONE
      $outputs[$milestone['id_parent']]["score"] +=
              floatval($milestones[$key]["score"] ?? 0) * floatval($milestone["weight"]) / 100;


    }

    // hitung aksi dan assign id_triwulan into aksi
    foreach($outputs as $key => $output) {
      $aksis[$output['id_parent']]["score"] +=
          $output["score"] * floatval($output["weight"]) / 100;
    }

    // hitung stranas
    foreach($aksis as $key => $aksi) {
      $stranaspk["score"] += $aksi["score"] * $aksi["weight"] / 100;
    }

    /****************************/
    /* Insert to DashboardStore */
    /****************************/
    // stranaspk to dashboard
    $stranaspk = $dashboardStore->insert($stranaspk);
    $stranaspk["id_children"] = [];

    $jumlahNilaiAktualMilestone = null;
    $jumlahNilaiTargetMilestone = null;

    foreach($aksis as $aksiKey => $aksi) {
      // insert aksi
      $aksi["id_parent"] = $stranaspk["_id"];
      unset($aksi["_id"]); // _id dari logframeStore harus dihapus agar dokumen dapat masuk ke dashboardStore
      $aksi["created_at"] = date('Y-m-d H:i:s');
      $aksi["created_by"] = $this->shared->user->um_id ?? 1; // CAUTION
      $aksi = $dashboardStore->insert($aksi);
      $stranaspk["id_children"][] = $aksi["_id"];
      
      // insert output
      // pilih output yg $output=id_parent = key
      $childrenOutputs = array_filter($outputs,
          function($output) use ($aksiKey) {
              return $output["id_parent"] === $aksiKey;
          });
      $aksi["id_children"] = [];
      foreach($childrenOutputs as $outputKey => $output) {
          unset($output["_id"]);  // _id dari logframeStore harus dihapus agar dokumen dapat masuk ke dashboardStore
          $output["id_parent"] = $aksi["_id"];
          $output["created_at"] = date('Y-m-d H:i:s');
          $output["created_by"] = $this->shared->user->um_id ?? 1; // CAUTION
          $output = $dashboardStore->insert($output);
          $aksi["id_children"][] = $output["_id"]; // masukkan _id output sebagai id_children dari aksi
          // insert milestone
          $childrenMilestones = array_filter($milestones,
          function($milestone) use ($outputKey) {
              return $milestone["id_parent"] === $outputKey;
          });

          $output["id_children"] = [];

          $nilaiAktualMilestonesKontribusi = [];
          $nilaiTargetMilestonesKontribusi = [];
          $nilaiTargetOutputKontribusi = [];
          $nilaiAktualOutputKontribusi = [];
          $jumlahNilaiTargetMilestone = null;
          $jumlahNilaiAktualMilestone = null;
          $jumlahNilaiTargetOutput = null;
          $jumlahNilaiAktualOutput = null;
          $kumpulanInstansi = [];
          $scoreKldAllMilestone = [];
          foreach($childrenMilestones as $child) {
            if(isset($child["nilai_aktual_milestone_kontribusi"])) {
              $nilaiAktualMilestonesKontribusi[] = $child["nilai_aktual_milestone_kontribusi"];
            }
            if(isset($child["nilai_target_milestone_kontribusi"])) {
              $nilaiTargetMilestonesKontribusi[] = $child["nilai_target_milestone_kontribusi"];
            }
            if(isset($child["id_instansi"])) {
              $kumpulanInstansi[] = $child["id_instansi"];
            }
            if(isset($child["nilai_kld"])) {
              $scoreKldAllMilestone[] = $child["nilai_kld"];
            }
          }
          // print_r($child); die;
          $jumlahNilaiAktualMilestone = array_sum($nilaiAktualMilestonesKontribusi);
          $jumlahNilaiTargetMilestone = array_sum($nilaiTargetMilestonesKontribusi);
          $jumlahNilaiTargetOutput = array_sum($nilaiTargetOutputKontribusi);
          $jumlahNilaiAktualOutput = array_sum($nilaiAktualOutputKontribusi);
          
          $output["nilai_target_output_kontribusi"] = floatval($output["weight"]) / 100 * floatval($jumlahNilaiTargetMilestone);
          if($output["nilai_target_output_kontribusi"] != 0) {
            $output["nilai_target_output_mandiri"] = floatval($output["nilai_target_output_kontribusi"]) / floatval($output["nilai_target_output_kontribusi"]);
          }
          $output["nilai_aktual_output_kontribusi"] = floatval($output["weight"]) / 100 * floatval($jumlahNilaiAktualMilestone);
          if($output["nilai_aktual_output_kontribusi"] != 0) {
            $output["nilai_aktual_output_mandiri"] = floatval($output["nilai_aktual_output_kontribusi"]) / floatval($output["nilai_target_output_kontribusi"]);
          }

          // daftar instansi pada output
          $instansiYes = [];
          foreach($kumpulanInstansi as $ins) {
            $instansiYes = array_merge($instansiYes, $ins);
          }
          $instansiUnique = array_values(array_unique($instansiYes));
          $countInstansiOutput = count($instansiUnique);

          $daftarInstansiPadaOutput = $instansiStore->findBy(["id_instansi", "IN", $instansiUnique]);
          $instansiOutput = [];
          foreach($daftarInstansiPadaOutput as $ins) {
            $instansiOutput[] = ["id_instansi" => $ins["id_instansi"], "nama" => $ins["nama"]];
          }
          // end daftar instansi pada output

          // jumlah setiap target kontribusi dan aktual kontribusi
          $nilaiKldMilestone = call_user_func_array('array_merge_recursive', $scoreKldAllMilestone);

          foreach($instansiOutput as &$instansi) {

            $id_instansi = $instansi["id_instansi"];
            $totalKldTargetOutputKontribusi = 0;
            $totalKldAktualOutputKontribusi = 0;
            $totalKldTargetAksiKontribusi = 0;
            $totalKldAktualAksiKontribusi = 0;

            foreach($nilaiKldMilestone as $kldMilestone) {
              if($id_instansi == $kldMilestone["id_instansi"] && isset($kldMilestone["kld_target_output_kontribusi"])) {
                $totalKldTargetOutputKontribusi += $kldMilestone["kld_target_output_kontribusi"];
              }
              
              if($id_instansi == $kldMilestone["id_instansi"] && isset($kldMilestone["kld_aktual_output_kontribusi"])) {
                $totalKldAktualOutputKontribusi += $kldMilestone["kld_aktual_output_kontribusi"];
              }

              if($id_instansi == $kldMilestone["id_instansi"] && isset($kldMilestone["kld_target_aksi_kontribusi"])) {
                // print_r($kldMilestone["kld_target_aksi_kontribusi"]); die;
                $totalKldTargetAksiKontribusi += $kldMilestone["kld_target_aksi_kontribusi"];
              }

              if($id_instansi == $kldMilestone["id_instansi"] && isset($kldMilestone["kld_aktual_aksi_kontribusi"])) {
                $totalKldAktualAksiKontribusi += $kldMilestone["kld_aktual_aksi_kontribusi"];
              }

            }
            
            // start nilai KLD output
            $instansi["nilai_target_output_kontribusi"] = $totalKldTargetOutputKontribusi;
            $instansi["nilai_aktual_output_kontribusi"] = $totalKldAktualOutputKontribusi;
            $instansi["nilai_aktual_output_mandiri"] = $totalKldAktualOutputKontribusi / $totalKldTargetOutputKontribusi;
            $instansi["nilai_target_aksi_kontribusi"] = $totalKldTargetAksiKontribusi;
            $instansi["nilai_aktual_aksi_kontribusi"] = $totalKldAktualAksiKontribusi;
            $instansi["nilai_aktual_aksi_mandiri"] = $totalKldAktualAksiKontribusi / $totalKldTargetAksiKontribusi;
          }
          
          $outputKld["nilai_target_output_mandiri"] = 1;
          $outputKld["nilai_target_aksi_mandiri"] = 1;

          foreach($instansiOutput as &$instansi) {
            $instansi = array_merge($instansi, $outputKld);
          }
          
          $output["nilai_kld"] = $instansiOutput;

          // end nilai KLD output

          foreach($childrenMilestones as $milestone) {
              unset($milestone["_id"]);  // _id dari logframeStore harus dihapus agar dokumen dapat masuk ke dashboardStore
              $milestone["id_parent"] = $output["_id"];
              $milestone["created_at"] = date('Y-m-d H:i:s');
              $milestone["created_by"] = $this->shared->user->um_id ?? 1; // CAUTION
              $milestone = $dashboardStore->insert($milestone);
              $output["id_children"][] = $milestone["_id"];
          }
          $dashboardStore->update($output); // memasukkan id_children
      }

      // ---------- Start kode untuk menghitung nilai KLD pada level aksi ------------------
      // TO DO !!
      // 1. ambil semua instansi yang ada pada aksi
      // kode berikut sudah mengambil instansi pada aksi namun masih looping terus
      // nanti coba di explore saja
      // 2. jika semua instansi pada aksi sudah berhasil di ambil tinggal tambahkan rumusnya.
      // rumus aksi bisa di contek di logframeController
      // if(isset($aksi["id_children"])) {
      //   $childrenAksi = $aksi["id_children"];
      // }
      // $anak = $dashboardStore->findBy(["_id", "IN", $childrenAksi]);
      // // $kldPadaOutput = [];
      // foreach($anak as $child) {
      //   if(isset($child["id_logframe"])) {
      //     $kldPadaOutput[] = $child["nilai_kld"];
      //   }
      // }
      // // $daftarInstansiPadaAksi = [];
      // foreach($kldPadaOutput as $kld) {
      //   foreach($kld as $kldYes) {
      //     $daftarInstansiPadaAKsi[] = $kldYes["id_instansi"];
      //   }
      // }
      // $instansiUnik = array_unique($daftarInstansiPadaAKsi);
      // $instansiDiAksi = $instansiStore->findBy(["id_instansi", "IN", $instansiUnik]);
      // foreach($instansiDiAksi as $inss) {
      //   $kumpulanInstansiPadaAksi[] = ["id_instansi" => $inss["id_instansi"], "nama" => $inss["nama"]];
      // }
      // $jmlIns = count($kumpulanInstansiPadaAksi);
      // $aksi["children_aksi"] = $childrenAksi;
      // $aksi["jumlah_instansi"] = $jmlIns;
      // $aksi["instansi"] = $kumpulanInstansiPadaAksi;
      // ----------- END kode instansi pada aksi ----------------

      $dashboardStore->update($aksi); // memasukkan id_children
    }
    $dashboardStore->update($stranaspk); // memasukkan id_children
  }

  private function retrieveGeneralLogframe($params) {
    // mengambil logframe secara umum (semua instansi)
    // asumsi: !isset($params['id_instansi'])
    $dashboardStore = new SleekDbStore('dashboard');
    $instansiStore = new SleekDbStore('instansi');
    $logframeStore = new SleekDbStore('logframe');

    /***************************************/
    /* PARAMETER SELECT ke STORE dashboard */
    /***************************************/    
    $findByParams = [ // tentukan parameter untuk select ke store dashboard
      ["id_periode", "=", intval($params["id_periode"])], // berdasarkan id_periode
      function($dashboardItem) { // tidak boleh memasukkan yang sudah dihapus
        return !isset($dashboardItem["is_deleted"]);
      }
    ];
    if (!isset($params["id_logframe"])) { 
      // tidak ada specific id_logframe -> ambil pucuk Stranas PK
      $findByParams[] = ["level", "=", 1];
    } else {
       // specific logframe
      $findByParams[] = ["id_logframe", "=", $params["id_logframe"]];
    }

    $dashboardItems = $dashboardStore->findBy($findByParams, ["_id" => "asc"]);

    if (sizeof($dashboardItems) === 0 || is_null($dashboardItems)) {
      // dashboard belum diinit atau id_logframe salah
      $errMessage = !isset($params["id_logframe"]) ?
        'Dashboard not yet init' :
        "Wrong id_logframe: ".$params["id_logframe"];
      throw new Exception($errMessage);
    }

    // get the $dashboardItems' children
    for($i = 0; $i < sizeof($dashboardItems); $i++) {
      if (!isset($dashboardItems[$i]["id_children"])) continue; // no children
      $dashboardItems[$i]["children"] = [];
      foreach($dashboardItems[$i]["id_children"] as $id_child) {
        $child = $dashboardStore->findById($id_child);
        if ($child['level'] === 3) { // output
          $output = $logframeStore->findOneBy([
            ["id_periode", "=", intval($params["id_periode"])], // berdasarkan id_periode
            function($dashboardItem) { // tidak boleh memasukkan yang sudah dihapus
              return !isset($dashboardItem["is_deleted"]);
            },
            ["id_logframe", "=", $child["id_logframe"]]
          ]);
          $child["targets"] = (!is_null($output) && isset($output["targets"])) 
            ? $output["targets"] : []; 
        }
        $dashboardItems[$i]["children"][] = $child;
      }
    }

    /*****************************************************************/
    /* SETUP the array of SCORE from logframes BASED ON id_logframe */
    /***************************************************************/
    $result = [
      "id_logframe" => $dashboardItems[0]["id_logframe"] ?? '',
      "name" => $dashboardItems[0]["name"],
      "level" => $dashboardItems[0]["level"],
      "scores" => [null, null, null, null, null, null, null, null], // 8 angka score, sesuai 8 triwulan
    ];

    // dapatkan daftar id_instansi terakhir, yang paling update, jika ada
    $lastDashboardItem = $dashboardItems[sizeof($dashboardItems) - 1];
    if (isset($lastDashboardItem["id_instansi"])) {
      $result["id_instansi"] = $lastDashboardItem["id_instansi"];

      // get data about instansi
      $result["instansi"] = [];
      foreach($lastDashboardItem["id_instansi"] as $id_instansi) {
        $instansi = $instansiStore->findOneBy([
          ["id_instansi", "=", $id_instansi]
        ]);
        $result["instansi"][] = [
          "id_instansi" => $instansi["id_instansi"],
          "nama" => $instansi["nama"]
        ];
      }
    }

    // Populate children hanya pada triwulan 3
    $childrenTriwulanTiga = [];
    foreach($dashboardItems as $dashboardChildTriwulanTiga) {
      // children pada nilai kontribusi dan mandiri
      // saat ini masih yang triwulan 4
      if ($dashboardChildTriwulanTiga["id_triwulan"] != 4) continue;
      $childrenTriwulanTiga = $dashboardChildTriwulanTiga["children"];
    }

    // populate the children
    $children = [];
    foreach($dashboardItems as $dashboardItem) {
      $item_id_triwulan = intval($dashboardItem["id_triwulan"]);
      $result["scores"][$item_id_triwulan - 1] = $dashboardItem["score"];
      if (!isset($dashboardItem["children"])) continue;

      foreach($dashboardItem["children"] as $child) {
        if (!isset($children[$child["id_logframe"]])) {
          $children[$child["id_logframe"]] = [
            "id_logframe" => $child["id_logframe"],
            "name" => $child["name"],
            "level" => $child["level"],
            "scores" => [null, null, null, null, null, null, null, null], // 8 angka score, sesuai 8 triwulan
            "weight" => $child["weight"],
          ];
          // Jika level = 2 tampilkan nilai kontribusi dan mandiri pada aksi tersebut
          if ((isset($child["level"])) && ($child["level"] == 2)) {

            foreach($childrenTriwulanTiga as $childrenAksi) {
              if ($childrenAksi["id_logframe"] === $child["id_logframe"]) {
                if (isset($childrenAksi["nilai_aktual_aksi_kontribusi"])) {
                  $children[$child["id_logframe"]]["nilai_aktual_aksi_kontribusi"] = $childrenAksi["nilai_aktual_aksi_kontribusi"];
                } else {
                  $children[$child["id_logframe"]]["nilai_aktual_aksi_kontribusi"] = null;
                }
                if (isset($childrenAksi["nilai_aktual_aksi_mandiri"])) {
                  $children[$child["id_logframe"]]["nilai_aktual_aksi_mandiri"] = $childrenAksi["nilai_aktual_aksi_mandiri"];
                } else {
                  $children[$child["id_logframe"]]["nilai_aktual_aksi_mandiri"] = null;
                }
                if (isset($childrenAksi["nilai_target_aksi_mandiri"])) {
                  $children[$child["id_logframe"]]["nilai_target_aksi_mandiri"] = $childrenAksi["nilai_target_aksi_mandiri"];
                } else {
                  $children[$child["id_logframe"]]["nilai_target_aksi_mandiri"] = null;
                }
                if (isset($childrenAksi["nilai_target_aksi_kontribusi"])) {
                  $children[$child["id_logframe"]]["nilai_target_aksi_kontribusi"] = $childrenAksi["nilai_target_aksi_kontribusi"];
                } else {
                  $children[$child["id_logframe"]]["nilai_target_aksi_kontribusi"] = null;
                }
              }
            }
          }
          if (isset($child["targets"])) {
            // khusus output
            $children[$child["id_logframe"]]["targets"] = $child["targets"];
            // check apakah pada output memiliki nilai target output kontribusi
            foreach($childrenTriwulanTiga as $embuhLah) {
              
              if ($embuhLah["id_logframe"] === $child["id_logframe"]) {

                if(isset($embuhLah["nilai_target_output_kontribusi"])) {
                  $children[$child["id_logframe"]]["nilai_target_output_kontribusi"] = $embuhLah["nilai_target_output_kontribusi"];
                } else {
                  $children[$child["id_logframe"]]["nilai_target_output_kontribusi"] = null;
                }
  
                if(isset($embuhLah["nilai_target_output_mandiri"])) {
                  $children[$child["id_logframe"]]["nilai_target_output_mandiri"] = $embuhLah["nilai_target_output_mandiri"];
                } else {
                  $children[$child["id_logframe"]]["nilai_target_output_mandiri"] = null;
                }
  
                if(isset($embuhLah["nilai_aktual_output_kontribusi"])) {
                  $children[$child["id_logframe"]]["nilai_aktual_output_kontribusi"] = $embuhLah["nilai_aktual_output_kontribusi"];
                } else {
                  $children[$child["id_logframe"]]["nilai_aktual_output_kontribusi"] = null;
                }
  
                if(isset($embuhLah["nilai_aktual_output_mandiri"])) {
                  $children[$child["id_logframe"]]["nilai_aktual_output_mandiri"] = $embuhLah["nilai_aktual_output_mandiri"];
                } else {
                  $children[$child["id_logframe"]]["nilai_aktual_output_mandiri"] = null;
                }
              }
              
            }

          }

          if (isset($child["id_instansi"])) {
            // khusus milestone
            $children[$child["id_logframe"]]["daftar_instansi"] = 
              $this->getDaftarInstansi($child["id_instansi"]);

            // check apakah milestone memiliki nilai kontribusi dan mandiri
              foreach ($childrenTriwulanTiga as $childrenMilestone) {

                if ($childrenMilestone["id_logframe"] === $child["id_logframe"]) {

                  if (isset($childrenMilestone["nilai_target_milestone_kontribusi"])) {
                    $children[$child["id_logframe"]]["nilai_target_milestone_kontribusi"] = $childrenMilestone["nilai_target_milestone_kontribusi"];
                  } else {
                    $children[$child["id_logframe"]]["nilai_target_milestone_kontribusi"] = null;
                  }

                  if (isset($childrenMilestone["nilai_target_milestone_mandiri"])) {
                    $children[$child["id_logframe"]]["nilai_target_milestone_mandiri"] = $childrenMilestone["nilai_target_milestone_mandiri"];
                  } else {
                    $children[$child["id_logframe"]]["nilai_target_milestone_mandiri"] = null;
                  }

                  if (isset($childrenMilestone["nilai_aktual_milestone_kontribusi"])) {
                    $children[$child["id_logframe"]]["nilai_aktual_milestone_kontribusi"] = $childrenMilestone["nilai_aktual_milestone_kontribusi"];
                  } else {
                    $children[$child["id_logframe"]]["nilai_aktual_milestone_kontribusi"] = null;
                  }

                  if (isset($childrenMilestone["nilai_aktual_milestone_mandiri"])) {
                    $children[$child["id_logframe"]]["nilai_aktual_milestone_mandiri"] = $childrenMilestone["nilai_aktual_milestone_mandiri"];
                  } else {
                    $children[$child["id_logframe"]]["nilai_aktual_milestone_mandiri"] = null;
                  }
                  
                }

              }
          }
        }
        $child_id_triwulan = intval($child["id_triwulan"]);
        // redundant: mestinya tidak perlu, karena $item_id_triwulan === $child_id_triwulan
        
        $children[$child["id_logframe"]]["scores"][$child_id_triwulan - 1] =
          $child["score"] * $child["weight"] / 100;
          // children only: all scores are weighted scores
      }
    }
    
    // set last_score and weighted_score
    $scores = $result["scores"];
    $lastScore = null;
    for($i = sizeof($scores) - 1; $i >= 0; $i--) {
      if (is_null($scores[$i])) continue;
      $lastScore = $scores[$i];
      break;
    }
    $result["last_score"] = $lastScore;
    if (isset($dashboardItems[0]["weight"])) { // not for root, the root doesn't have weight
      $result["weight"] = $dashboardItems[0]["weight"];
      $result["weighted_score"] = $result["last_score"] * $dashboardItems[0]["weight"] / 100;
    }

    // set last score and weighted_score for children
    foreach($children as $key=>$child) {
      $scores = $child["scores"];
      $lastScore = null;
      for($i = sizeof($scores) - 1; $i >= 0; $i--) {
        if (is_null($scores[$i])) continue;
        $lastScore = $scores[$i];
        break;
      }
      $children[$key]["last_score"] = $lastScore;
      $children[$key]["weighted_score"] =
        is_null($children[$key]["last_score"])
        ? null
        : $children[$key]["last_score"]; // children only: last score = weighted_score
    }

    foreach($children as $child) {
      $result["children"][] = $child;
    }
    return new SimpleResponse($result);
  }

  private function retrieveInstansiLogframe($params) {
    // mengambil logframe untuk instansi tertentu
    // asumsi: isset($params['id_instansi'])
    $dashboardStore = new SleekDbStore('dashboard');
    $instansiStore = new SleekDbStore('instansi');
    $logframeStore = new SleekDbStore('logframe');

    // Peroleh dahulu root ancestor
    $findAncestorByParams = [
      ["id_periode", "=", intval($params["id_periode"])], // berdasarkan id_periode
      function($dashboardItem) { // tidak boleh memasukkan yang sudah dihapus
        return !isset($dashboardItem["is_deleted"]);
      }
    ];
    if (isset($params['id_logframe'])) {
      $findAncestorByParams[] =  // tentukan parameter untuk select ancestor
        ["id_logframe", "=", $params['id_logframe']]; // milestone
    } else {
      $findAncestorByParams[] = ["level", "=", 1];
    }
    $ancestor = $dashboardStore->findOneBy($findAncestorByParams);
    
    // cek dulu apakah matching
    $isNullAncestor = $ancestor === null; // bila ancestor tidak ditemukan
    $isMilestoneAncestor = intval($ancestor["level"]) === 4; // bila milestone
    $isWrongIdInstansi = $isMilestoneAncestor && !in_array(intval($params['id_instansi']), $ancestor['id_instansi']);
    if ($isNullAncestor || $isWrongIdInstansi) {
      throw new Exception("id_logframe and id_instansi mismatched");
    }
    // pilih keturunan yang sesuai dengan id_instansi
    $findByParams = [ // tentukan parameter untuk select ke store dashboard
      ["id_periode", "=", intval($params["id_periode"])], // berdasarkan id_periode
      function($dashboardItem) { // tidak boleh memasukkan yang sudah dihapus
        return !isset($dashboardItem["is_deleted"]);
      },
      ["level", "=", 4], // milestone
      ["id_instansi", "CONTAINS", intval($params["id_instansi"])]
    ];

    $dashboardItems = $dashboardStore->findBy($findByParams, ["_id" => "asc"]);

    /******************************************************************/
    /* Bila ada $params['id_logframe'], filter dulu dashboard items!  */
    /* Cukup ambil dashboard item level 4 yang memiliki               */
    /* garis keturunan dari $params['id_logframe']                    */
    /******************************************************************/
    $isMilestoneLevel = sizeof($dashboardItems) === 1 && intval($dashboardItems[0]['level']) === 4;
    if (!$isMilestoneLevel && isset($params['id_logframe'])) {
      $filteredItems = [];
      foreach($dashboardItems as $item) {
        $included = $this->checkIfLogframeHasItem($item['id_parent'], $params['id_logframe']);
        if (!$included) continue;
        $filteredItems[] = $item;
      }
      $dashboardItems = $filteredItems;
    }
    
    if (sizeof($dashboardItems) === 0) { // bila tidak punya dashboardItems milestone yang terkait
      throw new Exception("No milestones!");
    }
    // Dapatkan parents dan children
    $ancestorIdLogframe = $params['id_logframe'] ?? "0";
    $calculatedParentsAndChildren = $this->calculateStranasPKScoreBottomUp($dashboardItems, $ancestorIdLogframe);
    
    $insntsansiChoose = $instansiStore->findOneBy([
      ["id_instansi", "=", intval($params['id_instansi'])]
    ]);
    $idInsntsansiChoose = $insntsansiChoose['id_instansi'];

    // populate parents and children
    $populatedParent = $this->populateByIdTriwulan($calculatedParentsAndChildren["parents"], false, $idInsntsansiChoose);
    $populatedChildren = $this->populateByIdTriwulan($calculatedParentsAndChildren["children"], true, $idInsntsansiChoose);


    // get output' target, children only
    $populatedChildren = $this->getOutputTargetForChildren(
      $populatedChildren,
      intval($params["id_periode"]));

    // dapatkan nama instansi
    $instansi = $instansiStore->findOneBy([
      ["id_instansi", "=", intval($params['id_instansi'])]
    ]);

    // set the response
    $response = [
      "id_logframe" => $populatedParent[0]["id_logframe"] ?? '',
      "id_instansi" => intval($params['id_instansi']),
      "nama_instansi" => $instansi["nama"],
      "max_contribution" => $populatedParent[0]["max_contribution"],
      "name" => $populatedParent[0]["name"],
      "level" => $populatedParent[0]["level"],
      "scores" => $populatedParent[0]["scores"],
      "last_score" => $populatedParent[0]["last_score"],
      "weight" => $populatedParent[0]["weight"],
      "weighted_score" => $populatedParent[0]["weighted_score"],
      "nilai_aksi_mandiri_kld" => null,
      "nilai_output_mandiri_kld" => null,
      "mandiri_kld" => null,
      "children" => $populatedChildren
    ];
    $sumNilaiTargetAksi = [];
    $sumNilaiAktualAksi = [];
    if($response["level"] == 1) {
      if (isset($response["children"])) {
        foreach ($response["children"] as &$child) {
            if(isset($child["nilai_kld"])) {
              if (isset($child["nilai_kld"]["nilai_target_aksi_kontribusi"])) {
                  $sumNilaiTargetAksi[] = $child["nilai_kld"]["nilai_target_aksi_kontribusi"];
              }
                if (isset($child["nilai_kld"]["nilai_aktual_aksi_kontribusi"])) {
                  $sumNilaiAktualAksi[] = $child["nilai_kld"]["nilai_aktual_aksi_kontribusi"];
              }
            } else {
              $child["nilai_kld"]["nilai_aktual_aksi_mandiri"] = 0;
            }
        }
      }
      
      $sumTarget = array_sum($sumNilaiTargetAksi);
      $sumAktual = array_sum($sumNilaiAktualAksi);
      if(isset($sumAktual) && $sumAktual !== 0) {
          $response["mandiri_kld"] = $sumAktual / $sumTarget;
      } else {
          $response["mandiri_kld"] = 0;
      }
    }


    if($response["level"] == 3) {
      foreach($populatedChildren as $children) {
        $logframes = $dashboardStore->findBy(['id_logframe', '=', $children['id_logframe']]);
        foreach($logframes as $logframe) {
          if($logframe['id_triwulan'] != 4) continue;
          $parent = $dashboardStore->findById($logframe['id_parent']);
          // ambil nilai instansi yang di pilih
          if(isset($parent['nilai_kld'])) {
            foreach($parent['nilai_kld'] as $kld) {
              if($kld['id_instansi'] == $response['id_instansi']) {
                if(isset($kld['nilai_aktual_output_mandiri'])) {
                  $response['nilai_output_mandiri_kld'] = $kld['nilai_aktual_output_mandiri'];
                } else {
                  $response['nilai_output_mandiri_kld'] = null;
                }
              }
            }
          }
        }
      }
    }

    if($response['level'] == 2) {
      foreach($populatedParent as $parent) {
        if(isset($parent['nilai_kld'])) {
          $response['nilai_aksi_mandiri_kld'] = $parent['nilai_kld']['nilai_aktual_aksi_mandiri'];
        } 
      }
    }
    return new SimpleResponse($response);
  }

  private function calculateStranasPKScoreBottomUp($dashboardItems, $ancestorIdLogframe) {
    // menghitung capaian StranasPK untuk dashboardItems
    // milestones -> StranasPK (pada prakteknya, paling banter sampai Aksi saja)
    $parentScore = 0;
    $dashboardStore = new SleekDbStore('dashboard');
    $verificationsStore = new SleekDbStore('verifications');
    $parents = []; // kumpulkan parents
    // hitung ulang score parent, karena milestone scopenya sama atau lebih kecil
    // daripada score yang ada pada dashboardStore, tergantung id_instansi
    foreach($dashboardItems as $index => $item) {
      $weighted_score = null;
      if (isset($item["verifications"])) {
        foreach($item["verifications"] as $id_verification) {
          $verification = $verificationsStore->findById($id_verification);
          if (isset($verification['is_deleted'])) continue;
          $score = $verification["score"]["value"] ?? null;
          $weight = $item["weight"];
          $weighted_score = $score === null ? null : 
            floatval($score) * floatval($weight) / 100;
        }
      } else if(isset($item['score'])) {
        // note: untuk output atau aksi -> score sudah dihitung di recurrence sebelumnya
        $weighted_score = $item['score'] === null ? null : 
          floatval($item['score']) * floatval($item['weight']) / 100;
      }
      
      if (!isset($item['id_parent'])) {
        throw new Exception('No id_parent in calculateStranasPKScoreBottomUp');
      }
      $parent = $dashboardStore->findById($item['id_parent']);
      
      if (!isset($parents[$parent['id_logframe']][$parent['id_triwulan']])) {
        $parent["score"] = 0; // harus nol, karena score dihitung ulang berdasarkan instansi
        $parent["max_contribution"] = 0;
        $parents[$parent['id_logframe']][$parent['id_triwulan']] = $parent;
      } 
      $parents[$parent['id_logframe']][$parent['id_triwulan']]["score"] += 
        is_null($weighted_score) ? 0 : $weighted_score;
      $parents[$parent['id_logframe']][$parent['id_triwulan']]["max_contribution"] += 
        floatval($item["max_contribution"]);
    }
    // test apakah id_logframe sudah sama dengan $ancestorIdLogframe
    // benar: harus sama semua atau beda semua dengan $ancestorIdLogframe
    $uniqueIdLogframe = [];
    foreach($parents as $id_logframe => $parent) {
      if (in_array($id_logframe, $uniqueIdLogframe))
        continue;
      $uniqueIdLogframe[] = $id_logframe;
    }
    if (sizeof($uniqueIdLogframe) > 1 && sizeof($uniqueIdLogframe) < sizeof($parents)) {
      throw new Exception ('Wrong id_parents in dashboard data.');
    }
    
    // flat the $parents' array
    $flattenedParents = [];
    foreach($parents as $id_logframe => $parentPerIdTriwulan) {
      foreach($parentPerIdTriwulan as $parent) {
        $flattenedParents[] = $parent;
      }
    }

    $ancestorReached = sizeof($uniqueIdLogframe) === 1 
      && strval($uniqueIdLogframe[0]) === strval($ancestorIdLogframe); // sudah level Stranas PK

    if ($ancestorReached) {
      // basis
      return [
        "parents" => $flattenedParents,
        "children" => $dashboardItems
      ];
    } else {
      // recurrence
      return $this->calculateStranasPKScoreBottomUp($flattenedParents, $ancestorIdLogframe);
    }
  }

  private function populateByIdTriwulan($dashboardItems, $isChildren = false, $idInsntsansiChoose) {
    // populate the dashboard items based on id_triwulan
    // $isChildren: true if $dashboardItems are of children nodes
    $tempResult = [];
    foreach($dashboardItems as $item) {
      if (!isset($tempResult[$item['id_logframe']])) {
        $tempResult[$item['id_logframe']] = [
          "id_logframe" => $item['id_logframe'],
          "name" => $item['name'],
          "level" => $item['level'],
          "scores" => [null, null, null, null, null, null, null, null],
          "last_score" => null,
          "weighted_score" => null,
          "weight" => $item['weight'],
          "max_contribution" => [null, null, null, null, null, null, null, null],
        ];
        if (isset($item['id_instansi'])) {
          $tempResult[$item['id_logframe']]['daftar_instansi'] = 
            $this->getDaftarInstansi($item['id_instansi']);
        }

        // mendapatkan aksi pada triwulan sekarang 
        if ($item['level'] == 1) {
          $tempResult[$item['id_logframe']]['nilai_aksi_mandiri'] = 100;
        }

        if ($item['level'] == 2) {
          $nilaiKld = [];
          // dapatkan semua aksi
          $aksiTriwulanNow = $this->getAksiTriwulanNow();
          foreach($aksiTriwulanNow as $aksi) {  
            $aksiName = null;
            if(isset($aksi['id_logframe'])) {
              $idLogframe = $aksi['id_logframe'];
            }
            if(isset($aksi['nilai_kld'])) {
              foreach($aksi['nilai_kld'] as $kld) {
                if((isset($kld['id_instansi']) && $kld['id_instansi'] == $idInsntsansiChoose)) {
                  $nilaiKld[] = [
                      'id_instansi' => $kld['id_instansi'],
                      'nama' => $kld['nama'],
                      'id_logframe' => $aksi['id_logframe'],
                      'nilai_target_aksi_kontribusi' => $kld['nilai_target_aksi_kontribusi'],
                      'nilai_aktual_aksi_kontribusi' => $kld['nilai_aktual_aksi_kontribusi'],
                      'nilai_target_aksi_mandiri' => $kld['nilai_target_aksi_mandiri'],
                      'nilai_aktual_aksi_mandiri' => $kld['nilai_aktual_aksi_mandiri'],
                  ];
                }
              }
            }
          }
          foreach($nilaiKld as $logframe) {
            if(isset($logframe['id_logframe']) && $logframe['id_logframe'] == $tempResult[$item['id_logframe']]['id_logframe']) {
              $tempResult[$item['id_logframe']]['nilai_kld'] = $logframe;
            } 
          }
        }
        if ($item['level'] == 3) {
            $tempResult[$item['id_logframe']]['nilai_aktual_output_kontribusi'] = null;
            $tempResult[$item['id_logframe']]['nilai_aktual_output_mandiri'] = null;
            $tempResult[$item['id_logframe']]['nilai_target_output_kontribusi'] = null;
            $tempResult[$item['id_logframe']]['nilai_target_output_mandiri'] = null;
            $tempResult[$item['id_logframe']]['nilai_kld'] = [];

            // get output triwulan active
            $outputTriwulanActive = $this->getOutputTriwulanNow();
            foreach($outputTriwulanActive as $output) {
              if($output['id_logframe'] == $tempResult[$item['id_logframe']]['id_logframe']) {
                if(isset($output['nilai_aktual_output_kontribusi'])) {
                  $tempResult[$item['id_logframe']]['nilai_aktual_output_kontribusi'] = $output['nilai_aktual_output_kontribusi'];
                }
                if(isset($output['nilai_aktual_output_mandiri'])) {
                  $tempResult[$item['id_logframe']]['nilai_aktual_output_mandiri'] = $output['nilai_aktual_output_mandiri'];
                }
                if(isset($output['nilai_target_output_mandiri'])) {
                  $tempResult[$item['id_logframe']]['nilai_target_output_mandiri'] = $output['nilai_target_output_mandiri'];
                }
                if(isset($output['nilai_target_output_kontribusi'])) {
                  $tempResult[$item['id_logframe']]['nilai_target_output_kontribusi'] = $output['nilai_target_output_kontribusi'];
                }
                // apakah output memiliki nilai KLD
                $nilaiKld = [];
                if(isset($output['nilai_kld']) && $output) {
                  $nilaiKld = $output['nilai_kld'];
                }
                // ambil nilai KLD yang id instansinya sama dengan parameter id instansi
                foreach($nilaiKld as $kldScore) {
                  if(isset($kldScore) && $kldScore['id_instansi'] == $idInsntsansiChoose) {
                    if(!isset($tempResult[$item['id_logframe']])) {
                      $tempResult[$item['id_logframe']] = [];
                    }
                    $tempResult[$item['id_logframe']]['nilai_kld'] = $kldScore;
                  }
                }
              }
            }

            // jika milestone
        }
        if ($item['level'] == 4) {
          $tempResult[$item['id_logframe']]['nilai_aktual_milestone_kontribusi'] = null;
          $tempResult[$item['id_logframe']]['nilai_aktual_milestone_mandiri'] = null;
          $tempResult[$item['id_logframe']]['nilai_target_milestone_kontribusi'] = null;
          $tempResult[$item['id_logframe']]['nilai_target_milestone_mandiri'] = 1;

          $milestoneTriwulanNow = $this->getMilestoneTriwulanNow();
          foreach($milestoneTriwulanNow as $milestone) {
            if($milestone['id_logframe'] === $tempResult[$item['id_logframe']]['id_logframe']) {
              $tempResult[$item['id_logframe']]['nilai_aktual_milestone_kontribusi'] = $milestone['nilai_aktual_milestone_kontribusi'];
              $tempResult[$item['id_logframe']]['nilai_aktual_milestone_mandiri'] = $milestone['nilai_aktual_milestone_mandiri'];
              $tempResult[$item['id_logframe']]['nilai_target_milestone_kontribusi'] = $milestone['nilai_target_milestone_kontribusi'];
              $tempResult[$item['id_logframe']]['nilai_target_milestone_mandiri'] = $milestone['nilai_target_milestone_mandiri'];
              $tempResult[$item['id_logframe']]['nilai_kld'] = [];
              $nilaiKld = [];
              if (isset($milestone['nilai_kld'])) {
                $nilaiKld = $milestone['nilai_kld'];
              } else {
                $nilaiKld = [];
              }
              foreach($nilaiKld as $kld) {
                if(isset($kld) && $kld['id_instansi'] == $idInsntsansiChoose) {
                  if(!isset($tempResult[$item['id_logframe']])) {
                    $tempResult[$item['id_logframe']]['nilai_kld'] = [];
                  }
                  $tempResult[$item['id_logframe']]['nilai_kld'] = $kld;
                }
              }

            }
          }
        }
        
      }
      $id_triwulan = $item["id_triwulan"];
      $score = $isChildren
        ? $item["score"] * $item["weight"] / 100 // for children, all scores are weighted scores
        : $item["score"];
      $max_contribution = $item["max_contribution"];
      $tempResult[$item['id_logframe']]["scores"][$id_triwulan - 1] = $score;
      $tempResult[$item['id_logframe']]["max_contribution"][$id_triwulan - 1] = $max_contribution;
    }

    // hitung last_score, weighted_score
    foreach($tempResult as $id_logframe => $logframe) {
      for ($i = sizeof($logframe['scores']) - 1; $i >= 0; $i--) {
        if (is_null($logframe['scores'][$i])) continue;
        $tempResult[$id_logframe]['last_score'] = $logframe['scores'][$i];
        break;
      }
      for ($i = sizeof($logframe['max_contribution']) - 1; $i >= 0; $i--) {
        if (is_null($logframe['max_contribution'][$i])) continue;
        $tempResult[$id_logframe]['max_contribution'] = $logframe['max_contribution'][$i];
        // cukup ambil max_contribution yang terakhir
        break;
      }
      // for children, weighted_score equals last_score
      $tempResult[$id_logframe]['weighted_score'] = $isChildren
          ? $tempResult[$id_logframe]['last_score']
          : floatval($tempResult[$id_logframe]['last_score']) * 
            floatval($tempResult[$id_logframe]['weight']) /
            100;
    }

    // index id_logframe removed
    $result = [];
    foreach($tempResult as $resultItem) {
      $result[] = $resultItem;
    }

    return $result;
  }

  private function getMilestoneTriwulanNow() {
    $dashboardStore = new SleekDbStore('dashboard');
    $findMilestones = $dashboardStore->findBy([['level', '=', 4], ['id_triwulan', '=', 4]]);
    $milestoneActive = [];
    foreach($findMilestones as $milestone) {
      if(isset($milestone['is_deleted'])) continue;
      $milestoneActive[] = $milestone;
    }
    return $milestoneActive;
  }

  private function getOutputTriwulanNow() {
    $dashboardStore = new SleekDbStore('dashboard');
    $findOutput = $dashboardStore->findBy([['level', '=', 3], ['id_triwulan', '=', 4]]);
    $milestoneActive = [];
    foreach($findOutput as $output) {
      if(isset($output['is_deleted'])) continue;
      $outputActive[] = $output;
    }
    return $outputActive;
  }

  private function getAksiTriwulanNow() {
    $dashboardStore = new SleekDbStore('dashboard');
    $findAksi = $dashboardStore->findBy([['level', '=', 2], ['id_triwulan', '=', 4]]);
    $aksiActive = [];
    foreach($findAksi as $aksi) {
      if(isset($aksi['is_deleted'])) continue;
      $aksiActive[] = $aksi;
    }
    return $aksiActive;
  }

  private function getOutputTargetForChildren($children, $id_periode) {
    // get the logframe's targets for OUTPUT only (level 3)
    // Note: this is for children dashboard item only
    $logframeStore = new SleekDbStore('logframe');
    foreach($children as $index => $child) {
      if ($child['level'] !== 3) return $children; // output only
      $output = $logframeStore->findOneBy([
        ["id_periode", "=", $id_periode],
        ["id_logframe", "=", $child["id_logframe"]]
      ]);
      $children[$index]["targets"] = $output["targets"];
    }
    return $children;
  }

  private function checkIfLogframeHasItem($id_parent, $id_logframe_ancestor) {
    // check apakah 
    $dashboardStore = new SleekDbStore('dashboard');
    $parent = $dashboardStore->findById($id_parent);
    if (!array_key_exists("id_logframe", $parent) || intval($parent["level"]) === 1) {
      // sudah sampai puncak, dan tidak ketemu :-(
      // basis
      return false;
    }
    if ($parent["id_logframe"] === $id_logframe_ancestor)
      // basis
      return true;
    else {
      // recurrence
      return $this->checkIfLogframeHasItem($parent['id_parent'], $id_logframe_ancestor);
    }
  }

  private function getDaftarInstansi($array_id_instansi) {
    // get daftar instansi from array of id_instansi
    // data source: SleekdDb store instansi
    $instansiStore = new SleekDbStore('instansi');
    $daftarInstansi = [];
    foreach($array_id_instansi as $id_instansi) {
      $instansi = $instansiStore->findOneBy([
        ['id_instansi', '=', intval($id_instansi)]
      ]);
      $daftarInstansi[] = [
        "id_instansi" => $instansi["id_instansi"],
        "nama" => $instansi["nama"]
      ];
    }
    return $daftarInstansi;
  }


   // StranaspkLogframeController
   public function dashboardgetLogFrameIndex() {
    // $this->checkIsAuthenticated();
    $getParams = $this->request->get();
    $params = $this->validate(
        $getParams,
        [
            'id_periode' => ['validators' => ['required', 'digit']],
            'id_instansi' => ['validators' => ['digit']],
            'id_logframe' => ['validators' => ['digit']],
            'level' => ['validators' => ['digit']],
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
        ]
    );
    $id_periode = $params['id_periode'];
    $id_instansi = $params['id_instansi'] ?? null;
    $id_logframe = $params['id_logframe'] ?? null;
    $level = $params['level'] ?? null;
    if($level == 4){
        //menampilkan milestone, level 4 = milestone
        $logframes = $this->dashboardgetLogframeLevel4($id_periode, $level);
    }else{
        // menampilkan output, level 3 = output, level 2 = Aksi
        $logframes = $this->dashboardgetLogframeLevel3($id_periode, $level);
    }
    
    // $instansi = $this->getInstansi(20005);
    
    return new SimpleResponse($logframes);
}

public function dashboardsearchLogframes(){
    $stores = new SleekDbStore('dashboard');
    $instansi = new SleekDbStore('instansi');
    $getParams = $this->request->getJsonRawBody(true);
    $myParams = $this->validate(
        $getParams,
        [
          'id_logframe' => ['validators' => ['required']],
        ]
    );
    $logframes = $stores->findBy([
        ['id_logframe','like', "%" . $myParams['id_logframe'] . "%"],
        function($logframe){
            return !isset($logframe['is_deleted']);
        }
    ],
    [
        "_id" => "asc", "id_logframe" => "asc" // urut bds _id dan id_logframe
    ]);
    for($i=0; $i<count($logframes); $i++){
        for($j=0; $j<count($logframes[$i]['id_instansi']); $j++){
            $logframes[$i]['id_instansi'][$j] = 
            $instansi->findBy(
                ['id_instansi','=',$logframes[$i]['id_instansi'][$j]],
                ['name' => 'asc']);
        }
    }
    return new SimpleResponse($logframes);
}

public function dashboardsearchLogframesByIdLogFrames(){
  $stores = new SleekDbStore('dashboard');
  $instansi = new SleekDbStore('instansi');
  $getParams = $this->request->getJsonRawBody(true);
  $myParams = $this->validate(
      $getParams,
      [
        'id_parent' => ['validators' => ['required']],
      ]
  );
  $logframes = $stores->findBy([
      ['id_parent','=', $myParams['id_parent']],
      function($logframe){
          return !isset($logframe['is_deleted']);
      }
  ],
  [
      "_id" => "asc", "id_logframe" => "asc" // urut bds _id dan id_logframe
  ]);
  for($i=0; $i<count($logframes); $i++){
      for($j=0; $j<count($logframes[$i]['id_instansi']); $j++){
          $logframes[$i]['id_instansi'][$j] = 
          $instansi->findBy(
              ['id_instansi','=',$logframes[$i]['id_instansi'][$j]],
              ['name' => 'asc']);
      }
  }
  return new SimpleResponse($logframes);
}

public function dashboardgetAllInstansi(){
    $store = new SleekDbStore('instansi');
    $allInstansi = $store->findAll();
    return new SimpleResponse($allInstansi);
}

public function dashboarddeleteLogFrameByID(){
    // $this->checkIsAuthenticated();
    $stores = new SleekDbStore('dashboard');
    $getParams = $this->request->getJsonRawBody(true);
    $myParams = $this->validate(
        $getParams,
        [
          'id' => ['validators' => ['required']]
        ]
    );
    $id = $myParams['id'];
    $stores->updateById($id, ["is_deleted" => true]);
    return new SimpleResponse($stores); 
}

public function dashboardpostNewLogFrame(){
    // $this->checkIsAuthenticated();
    $stores = new SleekDbStore('dashboard');
    $getParams = $this->request->getJsonRawBody(true);
    $myParams = $this->validate(
        $getParams,
        [
          'id_logframe' => ['validators' => ['required']],
          'id_parent' => ['validators' => ['required']],
          'bobot_milestone' => ['validators' => ['required']],
          'target' => ['validators' => ['required']],
          'long_name' => ['validators' => ['required']],
          'short_name' => ['validators' => ['required']],
          'id_instansi' => ['validators' => ['required']],
        ]
    );
    $id_logframe = $myParams['id_logframe'];
    $id_parent = $myParams['id_parent']['id'];
    $id_periode = 3;
    $bobot_milestone = $myParams['bobot_milestone'];
    $target = $myParams['target'];
    $long_name = $myParams['long_name'];
    $short_name = $myParams['short_name'];
    $level = 4;
    $id_instansi = [$myParams['id_instansi']['id_instansi']];
    $logframe = [
        'id_logframe' => $id_logframe,
        'id_parent' => $id_parent,
        'id_periode' => $id_periode,
        'level'=> $level,
        'name' => [
            'long' => $long_name,
            'short' => $short_name
        ],
        'weight' => $bobot_milestone,
        'target' => $target,
        'id_instansi' => $id_instansi
    ];
    // Insert the data.
    $data = $stores->insert($logframe);
    return new SimpleResponse($data);
}

public function dashboardpostInstansiByMilestone(){
  $getParams = $this->request->getJsonRawBody(true);
  $myParams = $this->validate(
      $getParams,
      [
        'id_logframe' => ['validators' => ['required']],
        'id_instansi' => ['validators' => ['required']],
      ]
  );
  $store = new SleekDbStore('dashboard');
  // retrieve a logframe
  $logframe = $store->findById($myParams['id_logframe']);
  // update logframe
  $logframe["id_instansi"] = $myParams['id_instansi'];  
  $store->update( $logframe );

  
}

public function dashboardupdateLogFrame(){
    // $this->checkIsAuthenticated();
    $stores = new SleekDbStore('dashboard');
    $getParams = $this->request->getJsonRawBody(true);
    $myParams = $this->validate(
        $getParams,
        [
          'id_logframe' => ['validators' => ['required']],
          'bobot_milestone' => ['validators' => ['required']],
          'target' => ['validators' => ['required']],
          'long_name' => ['validators' => ['required']],
          'short_name' => ['validators' => ['required']],
          'id'=> ['validators' => ['required']],
        ]
    );
    $id = $myParams['id'];
    $id_logframe = $myParams['id_logframe'];
    $bobot_milestone = $myParams['bobot_milestone'];
    $target = $myParams['target'];
    $long_name = $myParams['long_name'];
    $short_name = $myParams['short_name'];
    $logframe = $stores->findById($id);
    // input logframe
    $logframe['id_logframe'] = $id_logframe;
    $logframe['name']['long'] = $long_name;
    $logframe['name']['short'] = $short_name;
    $logframe['weight'] = $bobot_milestone;
    $logframe['target'] = $target;
    // update data
    $data = $stores->update($logframe);
    return new SimpleResponse($data); 
}

private function dashboardgetLogframeLevel4($id_periode, $level){
    // $this->checkIsAuthenticated();
    $stores = new SleekDbStore('dashboard');
    $instansi = new SleekDbStore('instansi');
    $logframes = $stores->findBy([
        ['id_periode', '=', intval($id_periode)],
        ['level', '=', intval($level)],
        function($logframe){
            return !isset($logframe['is_deleted']);
        }
    ],
    [
        "_id" => "asc", "id_logframe" => "asc" // urut bds _id dan id_logframe
    ]);
    for($i=0; $i<count($logframes); $i++){
        for($j=0; $j<count($logframes[$i]['id_instansi']); $j++){
            $logframes[$i]['id_instansi'][$j] = 
            $instansi->findBy(
                ['id_instansi','=',$logframes[$i]['id_instansi'][$j]],
                ['name' => 'asc']);
        }
    }
    return $logframes;
    $triwulan = $this->triwulanNow();

    $response = [
        'id_triwulan' => intval($triwulan['id_periode']) === intval($id_periode)
            ? $triwulan['id_triwulan']
            : null,
        'started_at' => intval($triwulan['id_periode']) === intval($id_periode)
            ? $triwulan['started_at']
            : null,
        'ended_at' => intval($triwulan['id_periode']) === intval($id_periode) 
            ? $triwulan['ended_at']
            : null,
        'verification_started_at' => intval($triwulan['id_periode']) === intval($id_periode)
            ? $triwulan['verification_started_at']
            : null,
        'verification_ended_at' => intval($triwulan['id_periode']) === intval($id_periode) 
            ? $triwulan['verification_ended_at']
            : null,
        'logframes' => $logframes
    ];

    // is now the reporting time or verifying time
    $now = new DateTime(date('Y-m-d H:i:s'));
    $started_at = $response['started_at'] ? new DateTime($response['started_at']) : null;
    $ended_at = $response['ended_at'] ? new DateTime($response['ended_at']) : null;
    $verification_started_at = $response['verification_started_at'] ? 
        new DateTime($response['verification_started_at']) : null;
    $verification_ended_at = $response['verification_ended_at'] ? 
        new DateTime($response['verification_ended_at']) : null;

    $response['is_reporting_time'] = $started_at <= $now && $now <= $ended_at;
    $response['is_verifying_time'] = $verification_started_at <= $now 
        && $now <= $verification_ended_at;

    return new SimpleResponse($response);
}

private function dashboardgetLogframeLevel3($id_periode, $level){
    // $this->checkIsAuthenticated();
    $stores = new SleekDbStore('dashboard');
    $instansi = new SleekDbStore('instansi');
    $logframes = $stores->findBy([
        ['id_periode', '=', intval($id_periode)],
        ['level', '=', intval($level)],
        function($logframe){
            return !isset($logframe['is_deleted']);
        }
    ],
    [
        "_id" => "asc", "id_logframe" => "asc" // urut bds _id dan id_logframe
    ]);
    return $logframes;
}


}