<?php

use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class PerguruanTinggiController extends BaseController
{
    #region General Function
    #endregion

    #region BackOffice Function

    #region List PTNBH
    public function listPtnbh()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";
        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
            ]
        );

        $strWhere = "deleted_at is null";
        $bind = [];

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= ' and perguruan_tinggi_id in (select id_pt from PerguruanTinggi where nama ilike :keyword:)';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $totalCount = ListPTNBH::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $listPtnbh = ListPTNBH::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $params["limit"],
                "offset" => $params["offset"]
            ]
        );

        $result = [];
        foreach ($listPtnbh as $ptnbh) {
            $result[] = $ptnbh->toArrayDetail();
        }

        return new PaginatedResponse($result, $totalCount, $params["limit"], $params["offset"]);
    }

    public function submitPtnbh()
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->getJsonRawBody(true);
        $params['perguruan_tinggi_id'] = array_key_exists('perguruan_tinggi_id', $params) ? $params['perguruan_tinggi_id'] : 0;
        $params['url_report'] = array_key_exists('url_report', $params) ? $params['url_report'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'perguruan_tinggi_id' => ['validators' => ['required', 'digit']],
                'url_report' => ['validators' => []],
            ]
        );

        $ptnbh = ListPTNBH::findFirst(
            [
                "perguruan_tinggi_id = :id_pt: and deleted_at is null",
                "bind" => [
                    "id_pt" => $validatedRequest['perguruan_tinggi_id']
                ]
            ]
        );

        if (!empty($ptnbh)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi sudah terdaftar');
        }

        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $validatedRequest['perguruan_tinggi_id']
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak valid');
        }

        $ptnbh = new ListPTNBH();
        $ptnbh->perguruan_tinggi_id = $validatedRequest['perguruan_tinggi_id'];
        $ptnbh->url_report = $validatedRequest['url_report'];
        $ptnbh->created_by = $ptnbh->updated_by = $this->shared->user->um_id;
        $res = $ptnbh->create();

        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_ptnbh" => $ptnbh->id
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save PTNBH Error");
        }
    }

    public function detailPtnbh($id)
    {
        $this->checkScopes(['admin.view']);

        $data = ListPTNBH::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        $result = $data->toArrayDetail();

        return new CollectionPaginatedResponse([$result]);
    }

    public function editPtnbh($id)
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->getJsonRawBody(true);
        $params['url_report'] = array_key_exists('url_report', $params) ? $params['url_report'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'url_report' => ['validators' => []],
            ]
        );

        $ptnbh = ListPTNBH::findFirstById($id);

        if (empty($ptnbh)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'PTNBH tidak valid');
        }

        $ptnbh->url_report = $validatedRequest['url_report'];
        $ptnbh->updated_at = 'now()';
        $ptnbh->updated_by = $this->shared->user->um_id;
        $res = $ptnbh->save();

        if ($res) {
            $ptnbh = ListPTNBH::findFirstById($id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $ptnbh
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save PTNBH Error");
        }
    }

    public function deletePtnbh($id)
    {
        $this->checkScopes(['admin.view']);

        $data = ListPTNBH::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        $data->deleted_at = 'now()';
        $res = $data->save();
        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "message" => 'Data berhasil dihapus'
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Delete Data PTNBH Error");
        }
    }
    #endregion

    #region Alokasi BOPTN BPPTNBH
    public function listAlokasi()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['perguruan_tinggi_id]'] = array_key_exists('perguruan_tinggi_id', $params) ? $params['perguruan_tinggi_id'] : 0;
        $params['keyword]'] = array_key_exists('keyword', $params) ? $params['keyword'] : null;
        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'perguruan_tinggi_id' => ['validators' => ['digit']],
                'keyword' => ['validators' => []]
            ]
        );

        $strWhere = 'AlokasiBOPTNBPPTNBH.deleted_at is null';
        $bind = [];

        if ($validatedRequest['perguruan_tinggi_id'] > 0) {
            $strWhere .= ' and AlokasiBOPTNBPPTNBH.perguruan_tinggi_id = :pt_id:';
            $bind['pt_id'] = $validatedRequest['perguruan_tinggi_id'];
        }

        if (!is_null($validatedRequest['keyword'])) {
            $strWhere .= ' and PerguruanTinggi.nama ILIKE :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $totalCount = AlokasiBOPTNBPPTNBH::query()
            ->columns("count(*) as total")
            ->leftJoin(PerguruanTinggi::class, "PerguruanTinggi.id_pt = AlokasiBOPTNBPPTNBH.perguruan_tinggi_id")
            ->where($strWhere)
            ->bind($bind)
            ->execute()
            ->toArray()[0]['total'];

        $listAlokasi = AlokasiBOPTNBPPTNBH::query()
            ->columns("AlokasiBOPTNBPPTNBH.*")
            ->leftJoin(PerguruanTinggi::class, "PerguruanTinggi.id_pt = AlokasiBOPTNBPPTNBH.perguruan_tinggi_id")
            ->where($strWhere)
            ->bind($bind)
            ->limit($params['limit'], $params['offset'])
            ->execute();

        $result = [];
        foreach ($listAlokasi as $alokasi) {
            $result[] = $alokasi->toArrayDetail();
        }

        return new PaginatedResponse($result, $totalCount, $params["limit"], $params["offset"]);
    }

    public function uploadAlokasi()
    {
        $this->checkScopes(['admin.view']);

        $document = $this->getAndValidateImportFile2(
            'document',
            ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'],
            $this->request->getUploadedFiles()
        );

        $successCount = 0;
        $fails = [];

        if (!empty($document)) {
            $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($document->getTempName());
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(false);
            //$reader->setLoadSheetsOnly(['Pilkada_Data_Calon']);

            $spreadsheet = $reader->load($document->getTempName());
            //$spreadsheet->setActiveSheetIndexByName('Pilkada_Data_Calon');
            $worksheet = $spreadsheet->getActiveSheet();

            $expectedHeaders = [
                'nama_perguruan_tinggi',
                'tahun',
                'nilai_alokasi'
            ];

            $rows = [];
            $headers = null;
            $rowCount = 0;

            foreach ($worksheet->getRowIterator() as $row) {
                $values = [];

                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,

                foreach ($cellIterator as $cell) {
                    if ($rowCount > 0 && count($values) == 3) {
                        $values[] = $cell->getValue();
                    } else {
                        $values[] = str_replace(',', '', $cell->getFormattedValue());
                    }
                }

                if (count($values) < count($expectedHeaders)) {
                    break;
                }

                if (empty($values[0]) && empty($values[1])) {
                    break;
                }

                if ($rowCount == 0) {
                    $headers = $values;
                } else {
                    $rows[] = $values;
                }

                $rowCount++;
            }

            if (empty($headers)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Judul kolom/headers tidak ditemukan');
            }

            if (count($headers) < count($expectedHeaders)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Jumlah kolom < ' . count($expectedHeaders));
            }

            if (count($headers) > count($expectedHeaders)) {
                $headers = array_slice($headers, 0, count($expectedHeaders));
            }

            for ($i = 0; $i < count($expectedHeaders); $i++) {
                if ($expectedHeaders[$i] != $headers[$i]) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Nama kolom ke-$i tidak valid. Expected: " . $expectedHeaders[$i] . '. Got: ' . $headers[$i]);
                }
            }

            $errors = [];

            $rules = [
                'nama_perguruan_tinggi' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
                'nilai_alokasi' => ['validators' => ['required', 'numeric']]
            ];


            foreach ($rows as $key => $row) {
                $rowPayload = [
                    'nama_perguruan_tinggi' => trim($row[0]),
                    'tahun' => trim($row[1]),
                    'nilai_alokasi' => trim($row[2])
                ];

                $validationResult = $this->baseValidate($rowPayload, $rules);

                if (count($validationResult['errors']) > 0) {
                    $errors[] = [
                        'row_id' => $key + 1,
                        'value' => $rowPayload,
                        'errors' => $validationResult['errors']
                    ];
                }

                //Validate Perguruan Tinggi Existence
                $pt = PerguruanTinggi::findFirst(
                    [
                        "lower(nama) = :nama: and deleted_at is null",
                        "bind" => [
                            "nama" => strtolower($rowPayload['nama_perguruan_tinggi'])
                        ]
                    ]
                );

                if (empty($pt)) {
                    $pt = $this->getLiveDataPtSingle($rowPayload['nama_perguruan_tinggi']);
                }
            }

            if (count($errors) > 0) {
                throw new ValidationException($errors);
            }

            $this->db->begin();
            try {
                foreach ($rows as $key => $row) {
                    $rowPayload = [
                        'nama_perguruan_tinggi' => trim($row[0]),
                        'tahun' => trim($row[1]),
                        'nilai_alokasi' => trim($row[2])
                    ];

                    $pt = PerguruanTinggi::findFirst(
                        [
                            "lower(nama) = :nama: and deleted_at is null",
                            "bind" => [
                                "nama" => strtolower($rowPayload['nama_perguruan_tinggi'])
                            ]
                        ]
                    );

                    if (!empty($pt)) {
                        $existingAlokasi = AlokasiBOPTNBPPTNBH::findFirst(
                            [
                                'perguruan_tinggi_id = :id: and tahun = :tahun: and deleted_at is null',
                                "bind" => [
                                    "id" => $pt->id_pt,
                                    "tahun" => $rowPayload['tahun']
                                ]
                            ]
                        );

                        if (!empty($existingAlokasi)) {
                            $existingAlokasi->total_alokasi = $rowPayload['nilai_alokasi'] ?: 0;
                            $existingAlokasi->updated_at = 'now()';
                            $result = $existingAlokasi->save();

                            if (!$result) {
                                $errorMessage = '';

                                foreach ($existingAlokasi->getMessages() as $message) {
                                    $errorMessage .= $message . ", ";
                                }

                                $fails[] = [
                                    'perguruan_tinggi_id' => $existingAlokasi->perguruan_tinggi_id,
                                    'tahun' => $existingAlokasi->tahun,
                                    'error_message' => $errorMessage
                                ];
                            } else {
                                $successCount++;
                            }
                        } else {
                            $newAlokasi = new AlokasiBOPTNBPPTNBH();
                            $newAlokasi->perguruan_tinggi_id = $pt->id_pt;
                            $newAlokasi->tahun = $rowPayload['tahun'];
                            $newAlokasi->total_alokasi = $rowPayload['nilai_alokasi'] ?: 0;
                            $result = $newAlokasi->create();

                            if (!$result) {
                                $errorMessage = '';

                                foreach ($newAlokasi->getMessages() as $message) {
                                    $errorMessage .= $message . ", ";
                                }

                                $fails[] = [
                                    'perguruan_tinggi_id' => $newAlokasi->perguruan_tinggi_id,
                                    'tahun' => $newAlokasi->tahun,
                                    'error_message' => $errorMessage
                                ];
                            } else {
                                $successCount++;
                            }
                        }
                    } else {
                        $fails[] = [
                            'perguruan_tinggi' => $rowPayload['nama_perguruan_tinggi'],
                            'tahun' => $rowPayload['tahun'],
                            'error_message' => 'Perguruan Tinggi Tidak Valid'
                        ];
                    }
                }
                $this->db->commit();
            } catch (\Exception $e) {
                $this->db->rollback();

                throw $e;
            }


            return new CollectionPaginatedResponse([
                'success_count' => $successCount,
                'fails' => $fails
            ]);
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found or Not active");
        }
    }

    public function deleteAllALokasi($tahun)
    {
        $this->checkScopes(['admin.view']);

        if ($tahun > 0) {
            $listAlokasi = AlokasiBOPTNBPPTNBH::findByTahun($tahun);
            $count = 0;
            foreach ($listAlokasi as $alokasi) {
                $listDetailAlokasi = DetailAlokasiBOPTN::findByAlokasiId($alokasi->id);
                foreach ($listDetailAlokasi as $detailAlokasi) {
                    $detailAlokasi->delete();
                }
                $res = $alokasi->delete();

                if (!$res)
                    $count++;
            }

            if ($count > 0) {
                return new SimpleResponse('Terdapat' . $count . ' yang gagal dihapus. Silahkan lakukan proses hapus lagi');
            } else {
                return new SimpleResponse('Semua data berhasil dihapus');
            }
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Tahun tidak valid');
        }
    }

    #endregion

    #region Detil Alokasi BOPTN

    public function listDetilAlokasiBOPTN()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['alokasi_id'] = array_key_exists('alokasi_id', $params) ? $params['alokasi_id'] : 0;
        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'alokasi_id' => ['validators' => ['digit']],
            ]
        );

        $strWhere = 'deleted_at is null';
        $bind = [];

        if ($validatedRequest['alokasi_id'] > 0) {
            $strWhere .= ' and alokasi_id = :alokasi_id:';
            $bind['alokasi_id'] = $validatedRequest['alokasi_id'];
        }

        $totalCount = DetailAlokasiBOPTN::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $listAlokasi = DetailAlokasiBOPTN::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $params["limit"],
                "offset" => $params["offset"]
            ]
        );

        $result = [];
        foreach ($listAlokasi as $alokasi) {
            $result[] = $alokasi->toArrayDetail();
        }

        return new PaginatedResponse($result, $totalCount, $params["limit"], $params["offset"]);
    }

    public function uploadDetilAlokasi()
    {
        $this->checkScopes(['admin.view']);

        $document = $this->getAndValidateImportFile2(
            'document',
            ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'],
            $this->request->getUploadedFiles()
        );

        $successCount = 0;
        $fails = [];

        if (!empty($document)) {
            $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($document->getTempName());
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(false);
            //$reader->setLoadSheetsOnly(['Pilkada_Data_Calon']);

            $spreadsheet = $reader->load($document->getTempName());
            //$spreadsheet->setActiveSheetIndexByName('Pilkada_Data_Calon');
            $worksheet = $spreadsheet->getActiveSheet();

            $expectedHeaders = [
                'nama_perguruan_tinggi',
                'tahun',
                'kode_alokasi',
                'nilai_alokasi'
            ];

            $rows = [];
            $headers = null;
            $rowCount = 0;

            foreach ($worksheet->getRowIterator() as $row) {
                $values = [];

                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,

                foreach ($cellIterator as $cell) {
                    if ($rowCount > 0 && count($values) == 3) {
                        $values[] = $cell->getValue();
                    } else {
                        $values[] = str_replace(',', '', $cell->getFormattedValue());
                    }
                }

                if (count($values) < count($expectedHeaders)) {
                    break;
                }

                if (empty($values[0]) && empty($values[1])) {
                    break;
                }

                if ($rowCount == 0) {
                    $headers = $values;
                } else {
                    $rows[] = $values;
                }

                $rowCount++;
            }

            if (empty($headers)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Judul kolom/headers tidak ditemukan');
            }

            if (count($headers) < count($expectedHeaders)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Jumlah kolom < ' . count($expectedHeaders));
            }

            if (count($headers) > count($expectedHeaders)) {
                $headers = array_slice($headers, 0, count($expectedHeaders));
            }

            for ($i = 0; $i < count($expectedHeaders); $i++) {
                if ($expectedHeaders[$i] != $headers[$i]) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Nama kolom ke-$i tidak valid. Expected: " . $expectedHeaders[$i] . '. Got: ' . $headers[$i]);
                }
            }

            $errors = [];

            $rules = [
                'nama_perguruan_tinggi' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
                'kode_alokasi' => ['validators' => ['required']],
                'nilai_alokasi' => ['validators' => ['required', 'numeric']]
            ];

            $nikList = [];

            foreach ($rows as $key => $row) {
                $rowPayload = [
                    'nama_perguruan_tinggi' => trim($row[0]),
                    'tahun' => trim($row[1]),
                    'kode_alokasi' => trim($row[2]),
                    'nilai_alokasi' => trim($row[3])
                ];

                $validationResult = $this->baseValidate($rowPayload, $rules);

                if (count($validationResult['errors']) > 0) {
                    $errors[] = [
                        'row_id' => $key + 1,
                        'errors' => $validationResult['errors'],
                        'values' => $rowPayload
                    ];
                }

                //Validate Perguruan Tinggi Existence
                $pt = PerguruanTinggi::findFirst(
                    [
                        "lower(nama) = :nama: and deleted_at is null",
                        "bind" => [
                            "nama" => strtolower($rowPayload['nama_perguruan_tinggi'])
                        ]
                    ]
                );

                if (empty($pt)) {
                    $pt = $this->getLiveDataPtSingle($rowPayload['nama_perguruan_tinggi']);
                }
            }

            if (count($errors) > 0) {
                throw new ValidationException($errors);
            }

            $this->db->begin();
            try {
                foreach ($rows as $key => $row) {
                    $rowPayload = [
                        'nama_perguruan_tinggi' => trim($row[0]),
                        'tahun' => trim($row[1]),
                        'kode_alokasi' => trim($row[2]),
                        'nilai_alokasi' => trim($row[3])
                    ];

                    $pt = PerguruanTinggi::findFirst(
                        [
                            "lower(nama) = :nama: and deleted_at is null",
                            "bind" => [
                                "nama" => strtolower($rowPayload['nama_perguruan_tinggi'])
                            ]
                        ]
                    );

                    if (!empty($pt)) {
                        $dataAlokasi = AlokasiBOPTNBPPTNBH::findFirst(
                            [
                                'perguruan_tinggi_id = :id: and tahun = :tahun: and deleted_at is null',
                                "bind" => [
                                    "id" => $pt->id_pt,
                                    "tahun" => $rowPayload['tahun']
                                ]
                            ]
                        );

                        if (!empty($dataAlokasi)) {
                            $infoAlokasi = InfoAlokasiBOPTN::findFirst(
                                [
                                    "kode_output = :kode_alokasi: and tahun = :tahun: and deleted_at is null",
                                    "bind" => [
                                        "kode_alokasi" => $rowPayload['kode_alokasi'],
                                        "tahun" => $rowPayload['tahun']
                                    ]
                                ]
                            );

                            if (!empty($infoAlokasi)) {
                                $existingAlokasi = DetailAlokasiBOPTN::findFirst(
                                    [
                                        'alokasi_id = :alokasi_id: and info_alokasi_boptn_id = :info_id: and deleted_at is null',
                                        "bind" => [
                                            'alokasi_id' => $dataAlokasi->id,
                                            'info_id' => $infoAlokasi->id
                                        ]
                                    ]
                                );

                                if (!empty($existingAlokasi)) {
                                    $existingAlokasi->nilai_alokasi = $rowPayload['nilai_alokasi'] ?: 0;
                                    $existingAlokasi->updated_at = 'now()';
                                    $result = $existingAlokasi->save();

                                    if (!$result) {
                                        $errorMessage = '';

                                        foreach ($existingAlokasi->getMessages() as $message) {
                                            $errorMessage .= $message . ", ";
                                        }

                                        $fails[] = [
                                            'perguruan_tinggi_id' => $existingAlokasi->perguruan_tinggi_id,
                                            'info_alokasi_boptn_id' => $existingAlokasi->info_alokasi_boptn_id,
                                            'error_message' => $errorMessage
                                        ];
                                    } else {
                                        $successCount++;
                                    }
                                } else {
                                    $newAlokasi = new DetailAlokasiBOPTN();
                                    $newAlokasi->alokasi_id = $dataAlokasi->id;
                                    $newAlokasi->info_alokasi_boptn_id = $infoAlokasi->id;
                                    $newAlokasi->nilai_alokasi = $rowPayload['nilai_alokasi'] ?: 0;
                                    $result = $newAlokasi->create();

                                    if (!$result) {
                                        $errorMessage = '';

                                        foreach ($newAlokasi->getMessages() as $message) {
                                            $errorMessage .= $message . ", ";
                                        }

                                        $fails[] = [
                                            'perguruan_tinggi_id' => $newAlokasi->perguruan_tinggi_id,
                                            'info_alokasi_boptn_id' => $newAlokasi->info_alokasi_boptn_id,
                                            'error_message' => $errorMessage
                                        ];
                                    } else {
                                        $successCount++;
                                    }
                                }
                            } else {
                                $fails[] = [
                                    'kode_alokasi' => $rowPayload['kode_alokasi'],
                                    'tahun' => $rowPayload['tahun'],
                                    'error_message' => 'Info Alokasi tidak ditemukan'
                                ];
                            }
                        } else {
                            $fails[] = [
                                'perguruan_tinggi' => $rowPayload['nama_perguruan_tinggi'],
                                'tahun' => $rowPayload['tahun'],
                                'error_message' => 'Data Alokasi tidak ditemukan'
                            ];
                        }
                    } else {
                        $fails[] = [
                            'perguruan_tinggi' => $rowPayload['nama_perguruan_tinggi'],
                            'tahun' => $rowPayload['tahun'],
                            'error_message' => 'Perguruan Tinggi Tidak Valid'
                        ];
                    }
                }
                $this->db->commit();
            } catch (\Exception $e) {
                $this->db->rollback();

                throw $e;
            }


            return new CollectionPaginatedResponse([
                'success_count' => $successCount,
                'fails' => $fails
            ]);
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found or Not active");
        }
    }

    #endregion

    #region Pendapatan Belanja PTNBH

    public function listPendapatanBelanjaPTNBH()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['perguruan_tinggi_id]'] = array_key_exists('perguruan_tinggi_id', $params) ? $params['perguruan_tinggi_id'] : 0;
        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'perguruan_tinggi_id' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
            ]
        );

        $strWhere = 'PendapatanBelanjaPTNBH.deleted_at is null and PerguruanTinggi.nama ilike :keyword:';
        $bind = ["keyword" => "%". $validatedRequest["keyword"] . "%"];

        if ($validatedRequest['perguruan_tinggi_id'] > 0) {
            $strWhere .= ' and perguruan_tinggi_id = :pt_id:';
            $bind['pt_id'] = $validatedRequest['perguruan_tinggi_id'];
        }

        $totalCount = PendapatanBelanjaPTNBH::query()
            ->columns("count(*) as total")
            ->leftJoin("PerguruanTinggi", "PerguruanTinggi.id_pt = PendapatanBelanjaPTNBH.perguruan_tinggi_id")
            ->where($strWhere)
            ->bind($bind)
            ->execute()
            ->toArray()[0]['total'];

        $listAlokasi = PendapatanBelanjaPTNBH::query()
            ->columns("PendapatanBelanjaPTNBH.*")
            ->leftJoin("PerguruanTinggi", "PerguruanTinggi.id_pt = PendapatanBelanjaPTNBH.perguruan_tinggi_id")
            ->where($strWhere)
            ->limit($params["limit"], $params["offset"])
            ->bind($bind)
            ->execute();

        $result = [];
        foreach ($listAlokasi as $alokasi) {
            $result[] = $alokasi->toArrayDetail();
        }

        return new PaginatedResponse($result, $totalCount, $params["limit"], $params["offset"]);
    }

    public function uploadPendapatanBelanja()
    {
        $this->checkScopes(['admin.view']);

        $document = $this->getAndValidateImportFile2(
            'document',
            ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'],
            $this->request->getUploadedFiles()
        );

        $successCount = 0;
        $fails = [];

        if (!empty($document)) {
            $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($document->getTempName());
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(false);
            //$reader->setLoadSheetsOnly(['Pilkada_Data_Calon']);

            $spreadsheet = $reader->load($document->getTempName());
            //$spreadsheet->setActiveSheetIndexByName('Pilkada_Data_Calon');
            $worksheet = $spreadsheet->getActiveSheet();

            $expectedHeaders = [
                'nama_perguruan_tinggi',
                'info_alokasi_pendapatan_belanja_id',
                'tahun',
                'nominal'
            ];

            $rows = [];
            $headers = null;
            $rowCount = 0;

            foreach ($worksheet->getRowIterator() as $row) {
                $values = [];

                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,

                foreach ($cellIterator as $cell) {
                    if ($rowCount > 0 && count($values) == 3) {
                        $values[] = $cell->getValue();
                    } else {
                        $values[] = str_replace(',', '', $cell->getFormattedValue());
                    }
                }

                if (count($values) < count($expectedHeaders)) {
                    break;
                }

                if (empty($values[0]) && empty($values[1])) {
                    break;
                }

                if ($rowCount == 0) {
                    $headers = $values;
                } else {
                    $rows[] = $values;
                }

                $rowCount++;
            }

            if (empty($headers)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Judul kolom/headers tidak ditemukan');
            }

            if (count($headers) < count($expectedHeaders)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Jumlah kolom < ' . count($expectedHeaders));
            }

            if (count($headers) > count($expectedHeaders)) {
                $headers = array_slice($headers, 0, count($expectedHeaders));
            }

            for ($i = 0; $i < count($expectedHeaders); $i++) {
                if ($expectedHeaders[$i] != $headers[$i]) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Nama kolom ke-$i tidak valid. Expected: " . $expectedHeaders[$i] . '. Got: ' . $headers[$i]);
                }
            }

            $errors = [];

            $rules = [
                'nama_perguruan_tinggi' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
                'info_alokasi_pendapatan_belanja_id' => ['validators' => ['required', 'digit']],
                'nilai_alokasi' => ['validators' => ['required', 'numeric']]
            ];

            $nikList = [];

            foreach ($rows as $key => $row) {
                $rowPayload = [
                    'nama_perguruan_tinggi' => trim($row[0]),
                    'info_alokasi_pendapatan_belanja_id' => trim($row[1]),
                    'tahun' => trim($row[2]),
                    'nilai_alokasi' => trim($row[3])
                ];

                $validationResult = $this->baseValidate($rowPayload, $rules);

                if (count($validationResult['errors']) > 0) {
                    $errors[] = [
                        'row_id' => $key + 1,
                        'errors' => $validationResult['errors'],
                        'values' => $rowPayload
                    ];
                }

                //Validate Perguruan Tinggi Existence
                $pt = PerguruanTinggi::findFirst(
                    [
                        "lower(nama) = :nama: and deleted_at is null",
                        "bind" => [
                            "nama" => strtolower($rowPayload['nama_perguruan_tinggi'])
                        ]
                    ]
                );

                if (empty($pt)) {
                    $pt = $this->getLiveDataPtSingle($rowPayload['nama_perguruan_tinggi']);
                }
            }

            if (count($errors) > 0) {
                throw new ValidationException($errors);
            }

            $this->db->begin();
            try {
                foreach ($rows as $key => $row) {
                    $rowPayload = [
                        'nama_perguruan_tinggi' => trim($row[0]),
                        'info_alokasi_pendapatan_belanja_id' => trim($row[1]),
                        'tahun' => trim($row[2]),
                        'nilai_alokasi' => trim($row[3])
                    ];

                    $pt = PerguruanTinggi::findFirst(
                        [
                            "lower(nama) = :nama: and deleted_at is null",
                            "bind" => [
                                "nama" => strtolower($rowPayload['nama_perguruan_tinggi'])
                            ]
                        ]
                    );

                    if (!empty($pt)) {
                        $existingAlokasi = PendapatanBelanjaPTNBH::findFirst(
                            [
                                'perguruan_tinggi_id = :pt_id: and tahun = :tahun: and info_alokasi_pendapatan_belanja_id = :info_id: and deleted_at is null',
                                "bind" => [
                                    'pt_id' => $pt->id_pt,
                                    'tahun' => $rowPayload['tahun'],
                                    'info_id' => $rowPayload['info_alokasi_pendapatan_belanja_id']
                                ]
                            ]
                        );

                        if (!empty($existingAlokasi)) {
                            $existingAlokasi->nilai_alokasi = $rowPayload['nilai_alokasi'] ?: 0;
                            $existingAlokasi->updated_at = 'now()';
                            $result = $existingAlokasi->save();

                            if (!$result) {
                                $errorMessage = '';

                                foreach ($existingAlokasi->getMessages() as $message) {
                                    $errorMessage .= $message . ", ";
                                }

                                $fails[] = [
                                    'perguruan_tinggi_id' => $existingAlokasi->perguruan_tinggi_id,
                                    'tahun' => $existingAlokasi->tahun,
                                    'info_alokasi_pendapatan_belanja_id' => $existingAlokasi->info_alokasi_pendapatan_belanja_id,
                                    'error_message' => $errorMessage
                                ];
                            } else {
                                $successCount++;
                            }
                        } else {
                            $newAlokasi = new PendapatanBelanjaPTNBH();
                            $newAlokasi->perguruan_tinggi_id = $pt->id_pt;
                            $newAlokasi->tahun = $rowPayload['tahun'];
                            $newAlokasi->info_alokasi_pendapatan_belanja_id = $rowPayload['info_alokasi_pendapatan_belanja_id'];
                            $newAlokasi->nilai_alokasi = $rowPayload['nilai_alokasi'] ?: 0;
                            $result = $newAlokasi->create();

                            if (!$result) {
                                $errorMessage = '';

                                foreach ($newAlokasi->getMessages() as $message) {
                                    $errorMessage .= $message . ", ";
                                }

                                $fails[] = [
                                    'perguruan_tinggi_id' => $newAlokasi->perguruan_tinggi_id,
                                    'tahun' => $newAlokasi->tahun,
                                    'info_alokasi_pendapatan_belanja_id' => $newAlokasi->info_alokasi_pendapatan_belanja_id,
                                    'error_message' => $errorMessage
                                ];
                            } else {
                                $successCount++;
                            }
                        }
                    } else {
                        $fails[] = [
                            'perguruan_tinggi' => $rowPayload['nama_perguruan_tinggi'],
                            'tahun' => $rowPayload['tahun'],
                            'error_message' => 'Perguruan Tinggi Tidak Valid'
                        ];
                    }
                }
                $this->db->commit();
            } catch (\Exception $e) {
                $this->db->rollback();

                throw $e;
            }


            return new CollectionPaginatedResponse([
                'success_count' => $successCount,
                'fails' => $fails
            ]);
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found or Not active");
        }
    }

    public function deleteAllPendapatanBelanja()
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->get();

        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : 0;
        $params['perguruan_tinggi_id]'] = array_key_exists('perguruan_tinggi_id', $params) ? $params['perguruan_tinggi_id'] : 0;
        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['digit']],
                'perguruan_tinggi_id' => ['validators' => ['digit']],
            ]
        );

        $strWhere = '1 = 1';
        $bind = [];

        if ($validatedRequest['tahun'] > 0) {
            $strWhere .= ' and tahun = :tahun:';
            $bind['tahun'] = $validatedRequest['tahun'];
        }

        if ($validatedRequest['perguruan_tinggi_id'] > 0) {
            $strWhere .= ' and perguruan_tinggi_id = :pt_id:';
            $bind['pt_id'] = $validatedRequest['perguruan_tinggi_id'];
        }

        if (count($bind) > 0) {
            $listAlokasi = PendapatanBelanjaPTNBH::find(
                [
                    $strWhere,
                    "bind" => $bind
                ]
            );

            $count = 0;
            foreach ($listAlokasi as $alokasi) {
                $res = $alokasi->delete();

                if (!$res)
                    $count++;
            }

            if ($count > 0) {
                return new SimpleResponse('Terdapat' . $count . ' yang gagal dihapus. Silahkan lakukan proses hapus lagi');
            } else {
                return new SimpleResponse('Semua data berhasil dihapus');
            }
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Silahkan Pilih Tahun atau Perguruan Tinggi');
        }
    }

    #endregion

    #region Administration Universitas

    public function deletePerguruanTinggi($id)
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->get();

        $params['is_hard'] = array_key_exists('is_hard', $params) ? $params['is_hard'] : 0;
        $validatedRequest = $this->validate(
            $params,
            [
                'is_hard' => ['validators' => ['digit']],
            ]
        );

        $pt = PerguruanTinggi::findFirstByIdPt($id);
        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi Tidak Valid');
        }

        if (!is_null($pt->deleted_at) && $validatedRequest['is_hard'] == 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi Sudah Terhapus');
        }

        $res = null;

        if ($validatedRequest['is_hard'] == 0) {
            $pt->deleted_at = 'now()';
            $res = $pt->save();
        } else {
            $res = $pt->delete();
        }

        if ($res) {
            return new SimpleResponse('Data Berhasil Dihapus');
        } else {
            return new SimpleResponse('Data Gagal Dihapus');
        }
    }

    #endregion

    #region Info Alokasi BOPTN

    public function listInfoAlokasi()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
            ]
        );

        $strWhere = 'deleted_at is null';
        $bind = [];

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= ' and kode_output ilike :keyword: or nama_output ilike :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $totalCount = InfoAlokasiBOPTN::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $result = InfoAlokasiBOPTN::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $validatedRequest["limit"],
                "offset" => $validatedRequest["offset"],
                "order" => "id"
            ]
        );

        return new PaginatedResponse($result, $totalCount, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

    public function detilInfoAlokasi($id)
    {
        $this->checkIsAuthenticated();

        $data = InfoAlokasiBOPTN::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        $result = $data->toArrayDetail();

        return new CollectionPaginatedResponse([$result]);
    }

    public function submitInfoAlokasi()
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->getJsonRawBody(true);
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : 0;
        $params['kode_output'] = array_key_exists('kode_output', $params) ? $params['kode_output'] : '';
        $params['nama_output'] = array_key_exists('nama_output', $params) ? $params['nama_output'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required','digit']],
                'kode_output' => ['validators' => ['required']],
                'nama_output' => ['validators' => ['required']],
            ]
        );

        $existingData = InfoAlokasiBOPTN::findFirst(
            [
                'tahun = :tahun: and kode_output = :kode_output: and deleted_at is null',
                "bind" => [
                    "tahun" => $validatedRequest['tahun'],
                    "kode_output" => $validatedRequest['kode_output']
                ]
            ]
        );

        if(!empty($existingData)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data untuk tahun [' . $validatedRequest['tahun'] . '] dan kode [' . $validatedRequest['kode_output'] . '] sudah terdaftar.');
        }

        $dataInfo = new InfoAlokasiBOPTN();
        $dataInfo->tahun = $validatedRequest['tahun'];
        $dataInfo->kode_output = $validatedRequest['kode_output'];
        $dataInfo->nama_output = $validatedRequest['nama_output'];
        $res = $dataInfo->create();

        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_info" => $dataInfo->id
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Info Alokasi BOPTN Error");
        }
    }

    public function editInfoAlokasi($id)
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->getJsonRawBody(true);
        $params['nama_output'] = array_key_exists('nama_output', $params) ? $params['nama_output'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'nama_output' => ['validators' => ['required']],
            ]
        );

        $data = InfoAlokasiBOPTN::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        $data->nama_output = $validatedRequest['nama_output'];
        $data->updated_at = 'now()';
        $res = $data->save();

        if ($res) {
            $data = InfoAlokasiBOPTN::findFirst($id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $data
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Info Alokasi BOPTN Error");
        }
    }

    public function deleteInfoAlokasi($id)
    {
        $this->checkScopes(['admin.view']);

        $data = InfoAlokasiBOPTN::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        $data->deleted_at = 'now()';
        $res = $data->save();
        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "message" => 'Data berhasil dihapus'
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Delete Data Info Alokasi Error");
        }
    }

    public function exportInfoAlokasi()
    {
        $this->checkScopes(['admin.view']);

        $rows = InfoAlokasiBOPTN::find(
            [
                'deleted_at is null',
                'order' => 'id, tahun'
            ]
        );
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'ID');
        $sheet->setCellValue("B1", 'TAHUN');
        $sheet->setCellValue("C1", "KODE OUTPUT");
        $sheet->setCellValue("D1", "NAMA OUTPUT");
        $sheet->getStyle('A1:D1')->getFont()->setBold(true);

        $index = 1;
        foreach ($rows as $row) {
            $index++;
            $sheet->setCellValue("A$index", $row->id);
            $sheet->setCellValue("B$index", $row->tahun);
            $sheet->setCellValue("C$index", $row->kode_output);
            $sheet->setCellValue("D$index", $row->nama_output);
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:D' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(3);
        $sheet->getColumnDimension("B")->setAutoSize(true);
        $sheet->getColumnDimension("C")->setAutoSize(true);
        $sheet->getColumnDimension("D")->setAutoSize(true);

        // petunjuk pengisian
        $index = $index + 2;
        $sheet->setCellValue("A$index", "Petunjuk Pengisian:");
        $sheet->getStyle("A$index")->getFont()->setBold(true);
        $sheet->getStyle("A$index")->getFont()->setSize(14);
        $index++;

        $richText = new RichText();
        $richText->createTextRun('Template_Detail_BOPTN.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('nama_perguruan_tinggi ')->getFont()->setBold(true);
        $richText->createText('diisi dengan nama Perguruan Tinggi sesuai penamaan pada aplikasi JAGA (lihat sheet ');
        $richText->createTextRun('Referensi PT')->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createTextRun('Template_Detail_BOPTN.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('kode_alokasi ')->getFont()->setBold(true);
        $richText->createText('diisi dengan ');
        $richText->createTextRun('KODE OUTPUT ')->getFont()->setBold(true);
        $richText->createText('dari ');
        $richText->createTextRun('kolom C ')->getFont()->setBold(true);
        $richText->createText('tabel diatas (misal ');
        $richText->createTextRun('BEI.001')->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $index++;
        $richText = new RichText();
        $richText->createTextRun('Template_Detail_BOPTN.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('tahun ')->getFont()->setBold(true);
        $richText->createText('diisi dengan ');
        $richText->createTextRun('TAHUN ')->getFont()->setBold(true);
        $richText->createText('dari ');
        $richText->createTextRun('kolom B ')->getFont()->setBold(true);
        $richText->createText('tabel diatas (bersesuaian dengan ');
        $richText->createTextRun('KODE OUTPUT ')->getFont()->setBold(true);
        $richText->createText('yang dipilih, misal ');
        $richText->createTextRun('2022 ')->getFont()->setBold(true);
        $richText->createText('untuk kode output ');
        $richText->createTextRun('BEI.001')->getFont()->setBold(true);
        $richText->createText(').');
        $sheet->setCellValue("A$index", $richText);

        $index++;
        $richText = new RichText();
        $richText->createTextRun('Template_Detail_BOPTN.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('nilai_alokasi ')->getFont()->setBold(true);
        $richText->createText('diisi dengan nilai alokasi (misal:  ');
        $richText->createTextRun('22717849000')->getFont()->setBold(true);
        $richText->createText(').');
        $sheet->setCellValue("A$index", $richText);

        // generate sheet referensi Perguruan Tinggi
        $this->generateSheetPerguruanTinggi($spreadsheet);

        $writer = new Xlsx($spreadsheet);
        $filename = "Info Alokasi BOPTN ".date('Y-m-d H:i:s').".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    #endregion

    #region Info Pendapatan Belanja

    public function listInfoPendapatanBelanja()
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
            ]
        );

        $strWhere = 'deleted_at is null';
        $bind = [];

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= ' and kode_output ilike :keyword: or nama_output ilike :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $totalCount = InfoAlokasiPendapatanBelanja::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $rows = InfoAlokasiPendapatanBelanja::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $validatedRequest["limit"],
                "offset" => $validatedRequest["offset"],
                "order" => "id"
            ]
        );

        $result = [];
        foreach ($rows as $row) {
            array_push($result, $row->toArrayDetail());
        }

        return new PaginatedResponse($result, $totalCount, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

    public function detilInfoPendapatanBelanja($id)
    {
        $this->checkIsAuthenticated();

        $data = InfoAlokasiPendapatanBelanja::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        $result = $data->toArrayDetail();

        return new CollectionPaginatedResponse([$result]);
    }

    public function submitInfoPendapatanBelanja()
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->getJsonRawBody(true);
        $params['tipe_alokasi'] = array_key_exists('tipe_alokasi', $params) ? $params['tipe_alokasi'] : '';
        $params['parent_id'] = array_key_exists('parent_id', $params) ? $params['parent_id'] : '';
        $params['kode_output'] = array_key_exists('kode_output', $params) ? $params['kode_output'] : '';
        $params['nama_output'] = array_key_exists('nama_output', $params) ? $params['nama_output'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'tipe_alokasi' => ['validators' => ['required']],
                'parent_id' => ['validators' => ['required','digit']],
                'kode_output' => ['validators' => ['required']],
                'nama_output' => ['validators' => ['required']],
            ]
        );

        $existingData = InfoAlokasiPendapatanBelanja::findFirst(
            [
                'tipe_alokasi = :tipe_alokasi: and kode_output = :kode_output: and deleted_at is null',
                "bind" => [
                    "tipe_alokasi" => $validatedRequest['tipe_alokasi'],
                    "kode_output" => $validatedRequest['kode_output']
                ]
            ]
        );

        if(!empty($existingData)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data untuk tipe_alokasi [' . $validatedRequest['tipe_alokasi'] . '] dan kode [' . $validatedRequest['kode_output'] . '] sudah terdaftar.');
        }

        if ($validatedRequest['parent_id'] > 0) {
            $parent = InfoAlokasiPendapatanBelanja::findFirst($validatedRequest['parent_id']);
            if (empty($parent)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data parent tidak ditemukan.');
            }

            if (!is_null($parent->deleted_at)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data parent sudah dihapus.');
            }
        }

        $dataInfo = new InfoAlokasiPendapatanBelanja();
        $dataInfo->tipe_alokasi = $validatedRequest['tipe_alokasi'];
        $dataInfo->parent_id = $validatedRequest['parent_id'];
        $dataInfo->kode_output = $validatedRequest['kode_output'];
        $dataInfo->nama_output = $validatedRequest['nama_output'];
        $res = $dataInfo->create();

        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_info" => $dataInfo->id
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Info Alokasi Pendapatan Belanja Error");
        }
    }

    public function editInfoPendapatanBelanja($id)
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->getJsonRawBody(true);
        $params['nama_output'] = array_key_exists('nama_output', $params) ? $params['nama_output'] : '';
        $params['parent_id'] = array_key_exists('parent_id', $params) ? $params['parent_id'] : 0;

        $validatedRequest = $this->validate(
            $params,
            [
                'nama_output' => ['validators' => ['required']],
                'parent_id' => ['validators' => ['required', 'digit']]
            ]
        );

        $data = InfoAlokasiPendapatanBelanja::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        if ($validatedRequest['parent_id'] > 0) {
            $parent = InfoAlokasiPendapatanBelanja::findFirst($validatedRequest['parent_id']);
            if (empty($parent)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data parent tidak ditemukan.');
            }

            if (!is_null($parent->deleted_at)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data parent sudah dihapus.');
            }
        }

        $data->nama_output = $validatedRequest['nama_output'];
        $data->parent_id = $validatedRequest['parent_id'];
        $data->updated_at = 'now()';
        $res = $data->save();

        if ($res) {
            $data = InfoAlokasiPendapatanBelanja::findFirst($id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $data
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Info Alokasi Pendapatan Belanja Error");
        }
    }

    public function deleteInfoPendapatanBelanja($id)
    {
        $this->checkScopes(['admin.view']);

        $data = InfoAlokasiPendapatanBelanja::findFirst($id);
        if (empty($data)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Data tidak ditemukan.');
        }

        if (!is_null($data->deleted_at)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data sudah dihapus.');
        }

        $data->deleted_at = 'now()';
        $res = $data->save();
        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "message" => 'Data berhasil dihapus'
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Delete Data Info Alokasi Pendapatan Belanja Error");
        }
    }

    public function exportInfoPendapatanBelanja()
    {
        $this->checkScopes(['admin.view']);

        $rows = InfoAlokasiPendapatanBelanja::find(
            [
                'deleted_at is null',
                'order' => 'id'
            ]
        );
        $groupedRows = [];
        foreach ($rows as $row) {
            if ($row->parent_id === 0) {
                $groupedRows[$row->id] = array_merge($row->toArray(), ['childrens' => []]);
            } else {
                if (array_key_exists($row->parent_id, $groupedRows)) {
                    $groupedRows[$row->parent_id]['childrens'][$row->id] = array_merge($row->toArray(), ['childrens' => []]);
                } else {
                    foreach ($groupedRows as $k1 => $l1) {
                        foreach ($l1['childrens'] as $k2 => $l2) {
                            if ($row->parent_id === $l2['id']) {
                                $groupedRows[$k1]['childrens'][$k2]['childrens'][$row->id] = $row->toArray();
                            }
                        }
                    }
                }
            }
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'ID');
        $sheet->setCellValue("B1", "PARENT ID");
        $sheet->setCellValue("C1", 'TIPE ALOKASI');
        $sheet->setCellValue("D1", "KODE OUTPUT");
        $sheet->setCellValue("E1", "NAMA OUTPUT");
        $sheet->getStyle("A1:E1")->getFont()->setBold(true);

        $example = [
            0 => ['key' => 0, 'value' => ''],
            1 => ['values' => []]
        ];

        $index = 1;
        foreach ($groupedRows as $row) {
            $index++;
            $sheet->setCellValue("A$index", $row['id']);
            $sheet->setCellValue("B$index", $row['parent_id']);
            $sheet->setCellValue("C$index", $row['tipe_alokasi']);
            $sheet->setCellValue("D$index", $row['kode_output']);
            $sheet->setCellValue("E$index", $row['nama_output']);
            $sheet->getStyle("A$index:E$index")->getFont()->setBold(true);
            foreach ($row['childrens'] as $children) {
                // simpan value untuk contoh pada Petunjuk Penggunaan [1]
                if (!in_array($row['kode_output'], $example[1]['values'])) {
                    array_push($example[1]['values'], $row['kode_output']);
                }
                $index++;
                $sheet->setCellValue("A$index", $children['id']);
                $sheet->setCellValue("B$index", $children['parent_id']);
                $sheet->setCellValue("C$index", $children['tipe_alokasi']);
                $sheet->setCellValue("D$index", $children['kode_output']);
                $sheet->setCellValue("E$index", $children['nama_output']);
                $sheet->getStyle("C$index:E$index")->getAlignment()->setIndent(2);

                if (!empty($children['childrens'])) {
                    $sheet->getStyle("A$index:E$index")->getFont()->setBold(true);
                }

                foreach ($children['childrens'] as $child) {
                    // simpan value untuk contoh pada Petunjuk Penggunaan [1]
                    if (!in_array($children['kode_output'], $example[1]['values'])) {
                        array_push($example[1]['values'], $children['kode_output']);
                    }
                    $index++;
                    $sheet->setCellValue("A$index", $child['id']);
                    $sheet->setCellValue("B$index", $child['parent_id']);
                    $sheet->setCellValue("C$index", $child['tipe_alokasi']);
                    $sheet->setCellValue("D$index", $child['kode_output']);
                    $sheet->setCellValue("E$index", $child['nama_output']);
                    $sheet->getStyle("C$index:E$index")->getAlignment()->setIndent(4);

                    // simpan value untuk contoh pada Petunjuk Penggunaan [0]
                    if ($example[0]['key'] === 0) {
                        $example[0]['key'] = $child['id'];
                        $example[0]['value'] = $child['nama_output'];
                    }
                }
            }
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:E' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(3);
        $sheet->getColumnDimension("B")->setAutoSize(true);
        $sheet->getColumnDimension("C")->setAutoSize(true);
        $sheet->getColumnDimension("D")->setAutoSize(true);
        $sheet->getColumnDimension("E")->setAutoSize(true);

        // petunjuk pengisian
        $index = $index + 2;
        $sheet->setCellValue("A$index", "Petunjuk Pengisian:");
        $sheet->getStyle("A$index")->getFont()->setBold(true);
        $sheet->getStyle("A$index")->getFont()->setSize(14);
        $index++;

        $richText = new RichText();
        $richText->createTextRun('Template_Pendapatan_Belanja.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('nama_perguruan_tinggi ')->getFont()->setBold(true);
        $richText->createText('diisi dengan nama Perguruan Tinggi sesuai penamaan pada aplikasi JAGA (lihat sheet ');
        $richText->createTextRun('Referensi PT')->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createTextRun('Template_Pendapatan_Belanja.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('info_alokasi_pendapatan_belanja_id ')->getFont()->setBold(true);
        $richText->createText('diisi dengan ');
        $richText->createTextRun('ID ')->getFont()->setBold(true);
        $richText->createText('dari ');
        $richText->createTextRun('kolom A ')->getFont()->setBold(true);
        $richText->createText('tabel diatas (misal ');
        $richText->createTextRun($example[0]['key'] . ' ')->getFont()->setBold(true);
        $richText->createText('untuk output ');
        $richText->createTextRun($example[0]['value'] . '')->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createTextRun('Template_Pendapatan_Belanja.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('tahun ')->getFont()->setBold(true);
        $richText->createText('diisi dengan tahun anggaran (misal ');
        $richText->createTextRun('2022')->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createTextRun('Template_Pendapatan_Belanja.xlsx ')->getFont()->setBold(true);
        $richText->createText('kolom ');
        $richText->createTextRun('nominal ')->getFont()->setBold(true);
        $richText->createText('diisi dengan nominal (misal ');
        $richText->createTextRun('247943826028')->getFont()->setBold(true);
        $richText->createText('). Untuk output yang memiliki turunan, tidak perlu mengisi nilai nominal (');
        $richText->createTextRun(implode(",", $example[1]['values']))->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        // generate sheet referensi Perguruan Tinggi
        $this->generateSheetPerguruanTinggi($spreadsheet);

        $writer = new Xlsx($spreadsheet);
        $filename = "Info Pendapatan & Belanja ".date('Y-m-d H:i:s').".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    #endregion

    #endregion

    #region Public Function
    public function listWilayah()
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
            ]
        );

        $strWhere = "deleted_at is null and (provinsi <> '' or kota_kab <> '')";
        $bind = [];

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= " and (provinsi ilike :keyword: or kota_kab ilike :keyword:)";
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $listPt = PerguruanTinggi::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $validatedRequest["limit"],
                "offset" => $validatedRequest["offset"]
            ]
        );

        $listWilayah = [];
        foreach ($listPt as $pt) {
            if (!empty($pt->kota_kab)) {
                $listWilayah[] = $pt->kota_kab;
            }

            if (!empty($pt->provinsi)) {
                $listWilayah[] = $pt->provinsi;
            }
        }

        $listWilayah = array_values(array_unique($listWilayah));

        $totalCount = count($listWilayah);

        return new PaginatedResponse($listWilayah, $totalCount, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

    public function searchPt()
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";
        $params['is_simple'] = array_key_exists('is_simple', $params) ? $params['is_simple'] : 0;
        $params['wilayah'] = array_key_exists('wilayah', $params) ? $params['wilayah'] : "";
        $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : "nama";
        $params['order_direction'] = array_key_exists('order_direction', $params) ? $params['order_direction'] : "asc";

        $allowedOrder = ['nama'];
        $allowedDirection = ['asc', 'desc'];

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'is_simple' => ['validators' => ['digit']],
                'wilayah' => ['validators' => []],
                'order_by' => ['validators' => [
                    [
                        'name' => 'inclusion_in',
                        'params' => $allowedOrder
                    ]
                ]],
                'order_direction' => ['validators' => [
                    [
                        'name' => 'inclusion_in',
                        'params' => $allowedDirection
                    ]
                ]],
            ]
        );

        $strWhere = "deleted_at is null and status <> 'L'";
        $bind = [];

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= ' and nama ilike :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        if (!empty($validatedRequest['wilayah'])) {
            $strWhere .= ' and (kota_kab like :wilayah: or provinsi like :wilayah:)';
            $bind['wilayah'] = $validatedRequest['wilayah'];
        }

        $totalCount = PerguruanTinggi::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $listPt = [];

        if ($validatedRequest['is_simple'] == 1) {
            if ($totalCount > 0) {
                $listPt = PerguruanTinggi::find(
                    [
                        $strWhere,
                        "bind" => $bind,
                        "order" => "lower(" . $validatedRequest['order_by'] . ") " . $validatedRequest['order_direction'],
                        "limit" => $params["limit"],
                        "offset" => $params["offset"]
                    ]
                );
            } else {
                $listPt = $this->getLiveDataPt($validatedRequest);
                $totalCount = count($listPt);
            }
        } else {
            $listPt = PerguruanTinggi::find(
                [
                    $strWhere,
                    "bind" => $bind,
                    "order" => "lower(" . $validatedRequest['order_by'] . ") " . $validatedRequest['order_direction'],
                    "limit" => $params["limit"],
                    "offset" => $params["offset"]
                ]
            );
        }

        return new PaginatedResponse($listPt, $totalCount, $params["limit"], $params["offset"]);
    }

    public function searchNamaPt()
    {
        $params = $this->request->get();

        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";

        $validatedRequest = $this->validate(
            $params,
            [
                'keyword' => ['validators' => ['required', ['name' => 'min_string', 'params' => 3]]]
            ]
        );

        $strWhere = "deleted_at is null and status <> 'L'";
        $bind = [];

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= ' and nama ilike :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $result = PerguruanTinggi::find(
            [
                $strWhere,
                "columns" => "id_pt, kode, nama",
                "bind" => $bind,
                "limit" => 30
            ]
        );

        return new CollectionPaginatedResponse($result);
    }

    public function summaryPt($id) {
        $service = new PerguruanTinggiService($this);
        $result = [
            "detail" => $service->detail($id),
            "ptnbh" => $service->ptnbh($id),
            "anggaran" => $service->anggaran($id),
            "prodi" => $service->prodi($id),
            "prodi-summary" => $service->prodiSummary($id),
            "pendapatan-percentage" => []
        ];
        if (count($result["ptnbh"]) > 0) {
            $result["pendapatan-percentage"] = $service->pendapatanPercentage($id, ArrayHelper::maxAttributeInArray($result["ptnbh"], "tahun"));
        }
        return new SimpleResponse($result);
    }

    public function detailPt($id)
    {
        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak diketemukan');
        }

        $pt = $this->syncDetailPt($pt);

        return new SimpleResponse($pt);
    }

    public function summaryDosenMahasiswaPt($id)
    {
        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak diketemukan');
        }

        $this->syncDetailDosenMahasiswaPt($pt);

        $data = JumlahDosenMahasiswaPt::find(
            [
                "perguruan_tinggi_id = :id_pt: and deleted_at is null",
                "bind" => [
                    "id_pt" => $pt->id_pt
                ]
            ]
        );

        $result = [];
        foreach ($data as $item) {
            $result[] = $item->toArrayDetail();
        }

        return new SimpleResponse($result);
    }

    public function summaryProdiPt($id)
    {
        if($id == 'undefined')
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Parameter ID tidak boleh "undefined"!');

        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak diketemukan');
        }

        $this->syncDetailProdi($pt);

        $result = DetailProdiPt::query()
            ->columns('jenjang as jenjang, count(1) as jumlah_prodi')
            ->where('perguruan_tinggi_id = :id: and deleted_at is null')
            ->bind(['id' => $pt->id_pt])
            ->groupBy('jenjang')
            ->execute()
            ->toArray();

        return new SimpleResponse($result);
    }

    public function listProdiPt($id)
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";
        $params['kategori'] = array_key_exists('kategori', $params) ? $params['kategori'] : "";
        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'kategori' => ['validators' => []],
            ]
        );

        $strWhere = "perguruan_tinggi_id = :id: and deleted_at is null";
        $bind['id'] = $id;

        if (!empty($validatedRequest['keyword'])) {
            $strWhere .= ' and nama_prodi ilike :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        if (!empty($validatedRequest['kategori'])) {
            $strWhere .= ' and jenjang ilike :jenjang:';
            $bind['jenjang'] = '%' . $validatedRequest['jenjang'] . '%';
        }

        $totalCount = DetailProdiPt::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $listProdi = DetailProdiPt::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $params["limit"],
                "offset" => $params["offset"]
            ]
        );

        return new PaginatedResponse($listProdi, $totalCount, $params["limit"], $params["offset"]);
    }

    public function detailAnggaranPt($id)
    {
        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak ditemukan');
        }

        $result['nama_perguruan_tinggi'] = $pt->nama;
        //Get Perguruan Tinggi Type
        $ptnbh = ListPTNBH::findFirst(
            [
                "perguruan_tinggi_id = :pt_id: and deleted_at is null",
                "bind" => [
                    "pt_id" => $pt->id_pt
                ]
            ]
        );

        if (!empty($ptnbh)) {
            $result['tipe_pt'] = 'PTNBH';
        } else {
            $result['tipe_pt'] = 'PTN';
        }

        //Get Alokasi BOPTN/BPPTNBH
        $result['summary_alokasi'] = [];

        $listAlokasi = AlokasiBOPTNBPPTNBH::find(
            [
                "perguruan_tinggi_id = :pt_id: and deleted_at is null",
                "bind" => [
                    "pt_id" => $pt->id_pt
                ]
            ]
        );

        foreach ($listAlokasi as $alokasi) {
            $data = $alokasi->toArray();
            $data['detail_alokasi'] = DetailAlokasiBOPTN::query()
                ->columns("InfoAlokasiBOPTN.kode_output, InfoAlokasiBOPTN.nama_output, DetailAlokasiBOPTN.nilai_alokasi")
                ->join("AlokasiBOPTNBPPTNBH", "AlokasiBOPTNBPPTNBH.id = DetailAlokasiBOPTN.alokasi_id")
                ->join("InfoAlokasiBOPTN", "InfoAlokasiBOPTN.id = DetailAlokasiBOPTN.info_alokasi_boptn_id")
                ->where("AlokasiBOPTNBPPTNBH.id = :alokasi_id: and AlokasiBOPTNBPPTNBH.tahun = :tahun: and DetailAlokasiBOPTN.deleted_at is null")
                ->bind(["alokasi_id" => $alokasi->id, "tahun" => $alokasi->tahun])
                ->execute()
                ->toArray();


            //Get Jumlah Anggaran tiap Mahasiswa untuk PTNBH
            $data['anggaran_per_mahasiswa'] = 0;
            if (!empty($ptnbh)) {
                $rawJumlahMahasiswa = JumlahDosenMahasiswaPt::query()
                    ->columns("SUM(jumlah_mahasiswa) as jumlah_mahasiswa")
                    ->where("perguruan_tinggi_id = :id_pt: and deleted_at is null")
                    ->bind(["id_pt" => $pt->id_pt])
                    ->execute()
                    ->toArray()[0];

                $jumlahMahasiswa = $rawJumlahMahasiswa['jumlah_mahasiswa'] ?: 0;

                if ($jumlahMahasiswa > 0) {
                    $data['anggaran_per_mahasiswa'] = $data['total_alokasi'] / $jumlahMahasiswa;
                }
            }
            $result['summary_alokasi'][] = $data;
        }

        return new SimpleResponse($result);
    }

    public function summaryPendapatanBelanja($id)
    {
        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak ditemukan');
        }

        //Get Perguruan Tinggi Type
        $ptnbh = ListPTNBH::findFirst(
            [
                "perguruan_tinggi_id = :pt_id: and deleted_at is null",
                "bind" => [
                    "pt_id" => $pt->id_pt
                ]
            ]
        );

        $result = [];

        if (!empty($ptnbh)) {
            $result['nama_perguruan_tinggi'] = $pt->nama;

            $data = PendapatanBelanjaPTNBH::query()
                ->columns('PendapatanBelanjaPTNBH.tahun, InfoAlokasiPendapatanBelanja.tipe_alokasi, PendapatanBelanjaPTNBH.nilai_alokasi')
                ->join('InfoAlokasiPendapatanBelanja', 'InfoAlokasiPendapatanBelanja.id = PendapatanBelanjaPTNBH.info_alokasi_pendapatan_belanja_id')
                ->where('PendapatanBelanjaPTNBH.perguruan_tinggi_id = :id_pt: and InfoAlokasiPendapatanBelanja.id NOT IN (SELECT x.parent_id FROM InfoAlokasiPendapatanBelanja x) and PendapatanBelanjaPTNBH.deleted_at is null')
                ->bind(["id_pt" => $pt->id_pt])
                ->execute()
                ->toArray();

            $processedData = [];
            foreach ($data as $content) {
                if (!array_key_exists($content['tahun'], $processedData)) {
                    $processedData[$content['tahun']]['pendapatan'] = $processedData[$content['tahun']]['belanja'] = 0;
                }

                if ($content['tipe_alokasi'] == 'Pendapatan') {
                    $processedData[$content['tahun']]['pendapatan'] += $content['nilai_alokasi'];
                } else {
                    $processedData[$content['tahun']]['belanja'] += $content['nilai_alokasi'];
                }
            }

            $summary = [];
            foreach ($processedData as $tahun => $data) {
                $detail['tahun'] = $tahun;
                $detail['pendapatan'] = $data['pendapatan'];
                $detail['belanja'] = $data['belanja'];
                $detail['status'] = $data['pendapatan'] > $data['belanja'] ? 'Surplus' : 'Defisit';

                $summary[] = $detail;
            }

            $result['data'] = $summary;
        }

        return new SimpleResponse($result);
    }

    public function detailPendapatanBelanja($id)
    {
        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak ditemukan');
        }

        //Get Perguruan Tinggi Type
        $ptnbh = ListPTNBH::findFirst(
            [
                "perguruan_tinggi_id = :pt_id: and deleted_at is null",
                "bind" => [
                    "pt_id" => $pt->id_pt
                ]
            ]
        );

        $result = [];

        if (!empty($ptnbh)) {
            $result['nama_perguruan_tinggi'] = $pt->nama;
            $result['data'] = [];

            $listTahun = PendapatanBelanjaPTNBH::query()
                ->columns('distinct tahun')
                ->Where('perguruan_tinggi_id = :id_pt: and deleted_at is null')
                ->bind(["id_pt" => $pt->id_pt])
                ->execute()
                ->toArray();

            $yearData = [];
            foreach ($listTahun as $rawTahun) {
                $tahun = $rawTahun['tahun'];

                $rawData = PendapatanBelanjaPTNBH::find(
                    [
                        'perguruan_tinggi_id = :id_pt: and tahun = :tahun: and deleted_at is null',
                        'bind' => [
                            "id_pt" => $pt->id_pt,
                            "tahun" => $tahun,
                        ]
                    ]
                );

                $processedData = [];
                foreach ($rawData as $data) {
                    $processedData[$data->info_alokasi_pendapatan_belanja_id] = $data->nilai_alokasi;
                }

                $yearData[$tahun] = $processedData;

                // $currYearData['tahun'] = $tahun;
                // $currYearData['pendapatan'] = $this->getDetailPendapatanBelanjaByParent(null, InfoAlokasiPendapatanBelanja::TIPE_ALOKASI_PENDAPATAN, $processedData);
                // $currYearData['belanja'] = $this->getDetailPendapatanBelanjaByParent(null, InfoAlokasiPendapatanBelanja::TIPE_ALOKASI_BELANJA, $processedData);

                // $result['data'][] = $currYearData;
            }

            if (count($yearData) > 0) {
                $resultData['pendapatan'] = $this->getDetailPendapatanBelanjaByParent(null, InfoAlokasiPendapatanBelanja::TIPE_ALOKASI_PENDAPATAN, $yearData);
                $resultData['belanja'] = $this->getDetailPendapatanBelanjaByParent(null, InfoAlokasiPendapatanBelanja::TIPE_ALOKASI_BELANJA, $yearData);

                $result['data'] = $resultData;
            }
        }

        return new SimpleResponse($result);
    }

    public function getPercentagePendapatanBelanja($id)
    {
        $params = $this->request->get();

        $params['kode'] = array_key_exists('kode', $params) ? $params['kode'] : 'PSC005';
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'kode' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']]
            ]
        );

        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak ditemukan');
        }

        $service = new PerguruanTinggiService($this);
        $result = $service->pendapatanPercentage($pt->id_pt, $validatedRequest["tahun"], $validatedRequest["kode"]);

        return new SimpleResponse($result);
    }

    public function getDataKipk()
    {
        $params = $this->request->get();

        $params['nim'] = array_key_exists('nim', $params) ? $params['nim'] : null;
        $params['id_pt'] = array_key_exists('id_pt', $params) ? $params['id_pt'] : null;
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : 0;


        $validatedRequest = $this->validate(
            $params,
            [
                'nim' => ['validators' => ['required']],
                'id_pt' => ['validators' => ['required', 'digit']],
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $pt = PerguruanTinggi::findFirst(
            [
                "id_pt = :id: and deleted_at is null",
                "bind" => [
                    "id" => $validatedRequest['id_pt']
                ]
            ]
        );

        if (empty($pt)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak valid');
        }

        $pt = $this->syncDetailPt($pt);

        if (empty($pt->kode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak ditemukan');
        }

        $result = $this->getLiveDataKIPK($pt->kode, $validatedRequest['nim'], $validatedRequest['tahun']);

        return new SimpleResponse($result);
    }

    public function getDetailPtnbhByPt($id)
    {
        $ptnbh = ListPTNBH::findFirst(
            [
                "perguruan_tinggi_id = :id: and deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        if (empty($ptnbh)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Perguruan Tinggi tidak terdaftar sebagai PTNBH');
        }

        return new SimpleResponse($ptnbh->toArrayDetail());
    }

    public function getJumlahKampusByBentuk()
    {
        $data = PerguruanTinggi::query()
            ->columns("CASE bentuk_pendidikan WHEN '' THEN 'Tidak Diketahui' ELSE bentuk_pendidikan END as bentuk_pendidikan, COUNT(1) as jumlah_perguruan_tinggi")
            ->where('deleted_at is null')
            ->groupBy('bentuk_pendidikan')
            ->execute()
            ->toArray();

        return new SimpleResponse($data);
    }

    public function getJumlahKampusByProvinsi()
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['required', 'digit']]
            ]
        );

        $data = PerguruanTinggi::query()
            ->columns("provinsi, COUNT(1) as jumlah_perguruan_tinggi")
            ->where("deleted_at is null and provinsi <> ''")
            ->groupBy('provinsi')
            ->orderBy('jumlah_perguruan_tinggi DESC')
            ->limit($validatedRequest['limit'], 0)
            ->execute()
            ->toArray();

        return new SimpleResponse($data);
    }

    public function getJumlahKampusByBentukProvinsi() {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['required', 'digit']]
            ]
        );

        $byBentuk = PerguruanTinggi::query()
            ->columns("CASE bentuk_pendidikan WHEN '' THEN 'Tidak Diketahui' ELSE bentuk_pendidikan END as bentuk_pendidikan, COUNT(1) as jumlah_perguruan_tinggi")
            ->where('deleted_at is null')
            ->groupBy('bentuk_pendidikan')
            ->execute()
            ->toArray();

        $byProvinsi = PerguruanTinggi::query()
            ->columns("provinsi, COUNT(1) as jumlah_perguruan_tinggi")
            ->where("deleted_at is null and provinsi <> ''")
            ->groupBy('provinsi')
            ->orderBy('jumlah_perguruan_tinggi DESC')
            ->limit($validatedRequest['limit'], 0)
            ->execute()
            ->toArray();

        $result = [
            "bentuk" => $byBentuk,
            "provinsi" => $byProvinsi
        ];

        return new SimpleResponse($result);
    }

    private function getLiveDataPt($request)
    {
        //Get Live Data
        $kemdikbudClient = new KemdikbudClient($this->config["pt_prop"]);
        $kemdikbudClient->setLogger($this->logger);
        $result = [];

        try {
            $result = $kemdikbudClient->getPt(str_replace(' ', '+', $request['keyword']), $request['limit'], $request['offset']);
        } catch (\Exception $ex) {

        }

        $pt = [];

        if (count($result) > 0) {
            //Insert Local Data
            $arrID = [];
            foreach ($result as $liveData) {
                $pt = new PerguruanTinggi();
                $pt->kode = $liveData['kode'] ?: '';
                $pt->nama = $liveData['nama'];
                $pt->nama_singkat = $liveData['nama_singkat'] ?: '';
                $pt->keterangan = $liveData['kode_satker'] ?: '';
                $pt->status_badanhukum = '';
                $pt->tgl_sk_pendirian = $liveData['tgl_sk_pendirian'] ?: '';
                $pt->status = $liveData['status'] ?: '';
                $pt->telepon = $liveData['telepon'] ?: '';
                $pt->faksimile = $liveData['faksimile'] ?: '';
                $pt->website = $liveData['website'] ?: '';
                $pt->email = $liveData['email'] ?: '';
                $pt->bentuk_pendidikan = $liveData['bentuk_pendidikan']['nama'] ?: '';
                $pt->last_update_dikti = $liveData['last_update'];
                $pt->kota_kab = $liveData['alamat']['kab_kota']['nama'] ?: '';
                $pt->provinsi = $liveData['propinsi']['nama'] ?: '';

                $pt->create();

                $arrID[] = $pt->id_pt;
            }

            $pt = PerguruanTinggi::find(
                [
                    "id_pt in ({arrid:array}) and deleted_at is null",
                    "bind" => [
                        "arrid" => $arrID
                    ]
                ]
            );
        }

        return $pt;
    }

    private function getLiveDataPtSingle($nama)
    {
        //Get Live Data
        $kemdikbudClient = new KemdikbudClient($this->config["pt_prop"]);
        $kemdikbudClient->setLogger($this->logger);
        $result = [];

        try {
            $result = $kemdikbudClient->getPtbyName(str_replace(' ', '+', $nama));
        } catch (\Exception $ex) {

        }

        $pt = null;

        if (count($result) > 0) {
            $liveData = $result[0];

            $pt = new PerguruanTinggi();
            $pt->kode = $liveData['kode'] ?: '';
            $pt->nama = $liveData['nama'];
            $pt->nama_singkat = $liveData['nama_singkat'] ?: '';
            $pt->keterangan = $liveData['kode_satker'] ?: '';
            $pt->status_badanhukum = '';
            $pt->tgl_sk_pendirian = $liveData['tgl_sk_pendirian'] ?: '';
            $pt->status = $liveData['status'] ?: '';
            $pt->telepon = $liveData['telepon'] ?: '';
            $pt->faksimile = $liveData['faksimile'] ?: '';
            $pt->website = $liveData['website'] ?: '';
            $pt->email = $liveData['email'] ?: '';
            $pt->bentuk_pendidikan = $liveData['bentuk_pendidikan']['nama'] ?: '';
            $pt->last_update_dikti = $liveData['last_update'];
            $pt->kota_kab = $liveData['alamat']['kab_kota']['nama'] ?: '';
            $pt->provinsi = $liveData['propinsi']['nama'] ?: '';

            $pt->create();
        }

        return $pt;
    }

    private function syncDetailPt($pt)
    {
        //Get Live Data
        $kemdikbudClient = new KemdikbudClient($this->config["pt_prop"]);
        $kemdikbudClient->setLogger($this->logger);
        $result = [];
        $resultAkreditasi = [];

        try {
            $result = $kemdikbudClient->getPtbyName(str_replace(' ', '+', $pt->nama), true);
        } catch (\Exception $ex) {
            \Sentry\captureException($ex);
        }

        if (count($result) > 0) {
            //Update Local Data
            $liveData = $result[0];

            $pt->kode = $liveData['kode'] ?: '';
            $pt->nama_singkat = $liveData['nama_singkat'] ?: '';
            $pt->keterangan = $liveData['kode_satker'] ?: '';
            $pt->tgl_sk_pendirian = $liveData['tgl_sk_pendirian'] ?: '';
            $pt->status = $liveData['status'] ?: '';
            $pt->telepon = $liveData['telepon'] ?: '';
            $pt->faksimile = $liveData['faksimile'] ?: '';
            $pt->website = $liveData['website'] ?: '';
            $pt->email = $liveData['email'] ?: '';
            $pt->bentuk_pendidikan = $liveData['bentuk_pendidikan']['nama'] ?: '';
            $pt->last_update_dikti = $liveData['last_update'] ?: '';
            $pt->kota_kab = $liveData['alamat']['kab_kota']['nama'] ?: '';
            $pt->provinsi = $liveData['propinsi']['nama'] ?: '';
            $pt->updated_at = 'now()';

            try {
                $resultAkreditasi = $kemdikbudClient->getAkreditasibyPT($pt->kode);
            } catch (\Exception $ex) {

            }

            if (count($resultAkreditasi) > 0) {
                $pt->status = $resultAkreditasi[0]['nilai'] ?: '';
            }

            $pt->save();

            //region Update Meilisearch Data
            if(filter_var(getenv("MEILI_SEARCH_ACTIVE"), FILTER_VALIDATE_BOOLEAN)) {
                $meilisearch = new MeiliSearchService($this);
                $itemMeili = [
                    "id" => "perguruan-tinggi-" . $pt->id_pt,
                    "sector" => $meilisearch::SECTOR_PELAYANAN_PUBLIK,
                    "category" => $meilisearch::CATEGORY_PERGURUAN_TINGGI,
                    "substance" => $meilisearch::SUBSTANCE_DETAIL_PT,
                    "title" => $pt->nama,
                    "content" => $pt->nama . " " . $pt->nama_singkat . ", Provinsi " . $pt->provinsi . " Kota/Kabupaten " . $pt->kota_kab,
                    "keywords" => "Nama " . $pt->nama . " Nama Singkat " . $pt->nama_singkat . "  keterangan" . $pt->keterangan . " status badan hukum badanhukum " . $pt->status_badanhukum . " akreditasi " . $pt->status . " no telepon tlp " . $pt->telepon . " bentuk pendidikan " . $pt->bentuk_pendidikan,
                    "FEURL" => "/pelayanan-publik/universitas/" . $pt->id_pt . "/" . $pt->kode . "/" . $pt->nama
                ];
                $meilisearch->updateSingleDocument($itemMeili);
            }
            //endregion

            $pt = PerguruanTinggi::findFirst(
                [
                    "id_pt = :id: and deleted_at is null",
                    "bind" => [
                        "id" => $pt->id_pt
                    ]
                ]
            );
        }

        return $pt;
    }

    private function syncDetailDosenMahasiswaPt($pt)
    {
        //Get Live Data
        $kemdikbudClient = new KemdikbudClient($this->config["pt_prop"]);
        $kemdikbudClient->setLogger($this->logger);
        $tahun = DateHelper::getCurrentDate()->year;

        $resultDosen = [];
        $resultMahasiswa = [];

        try {
            $resultDosen = $kemdikbudClient->getDosenJenjang($pt->kode, 2017, $tahun, '2017-01-01');
            $resultMahasiswa = $kemdikbudClient->getJumlahMahasiswa($pt->kode, 2017, $tahun, '2017-01-01');
        } catch (\Exception $ex) {

        }

        #region Initialize Array
        $data = [
            "s1" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "s2" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "s3" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "d1" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "d2" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "d3" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "d4" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "prof" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "sp1" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
            "sp2" => [
                "dosen" => 0,
                "mahasiswa" => 0
            ],
        ];
        #endregion

        //Process Data Dosen
        if (count($resultDosen) > 0) {
            $dataDosen = $resultDosen[0];
            $data['s1']['dosen'] = $dataDosen['jml_dosen_s1'] ?: 0;
            $data['s2']['dosen'] = $dataDosen['jml_dosen_s2'] ?: 0;
            $data['s3']['dosen'] = $dataDosen['jml_dosen_s3'] ?: 0;
            $data['d1']['dosen'] = $dataDosen['jml_dosen_d1'] ?: 0;
            $data['d2']['dosen'] = $dataDosen['jml_dosen_d2'] ?: 0;
            $data['d3']['dosen'] = $dataDosen['jml_dosen_d3'] ?: 0;
            $data['d4']['dosen'] = $dataDosen['jml_dosen_d4'] ?: 0;
            $data['prof']['dosen'] = $dataDosen['jml_dosen_prof'] ?: 0;
            $data['sp1']['dosen'] = $dataDosen['jml_dosen_sp1'] ?: 0;
            $data['sp2']['dosen'] = $dataDosen['jml_dosen_sp2'] ?: 0;
        }

        //Process Data Mahasiswa
        if (count($resultMahasiswa) > 0) {
            $dataMahasiswa = $resultMahasiswa[0];
            $data['s1']['mahasiswa'] = $dataMahasiswa['jml_mhs_s1'] ?: 0;
            $data['s2']['mahasiswa'] = $dataMahasiswa['jml_mhs_s2'] ?: 0;
            $data['s3']['mahasiswa'] = $dataMahasiswa['jml_mhs_s3'] ?: 0;
            $data['d1']['mahasiswa'] = $dataMahasiswa['jml_mhs_d1'] ?: 0;
            $data['d2']['mahasiswa'] = $dataMahasiswa['jml_mhs_d2'] ?: 0;
            $data['d3']['mahasiswa'] = $dataMahasiswa['jml_mhs_d3'] ?: 0;
            $data['d4']['mahasiswa'] = $dataMahasiswa['jml_mhs_d4'] ?: 0;
            $data['prof']['mahasiswa'] = $dataMahasiswa['jml_mhs_prof'] ?: 0;
            $data['sp1']['mahasiswa'] = $dataMahasiswa['jml_mhs_sp1'] ?: 0;
            $data['sp2']['mahasiswa'] = $dataMahasiswa['jml_mhs_sp2'] ?: 0;
        }

        foreach ($data as $jenjang => $jumlah) {
            $dataDetail = JumlahDosenMahasiswaPt::findFirst(
                [
                    "perguruan_tinggi_id = :pt_id: and lower(jenjang) = lower(:jenjang:) and deleted_at is null",
                    "bind" => [
                        "pt_id" => $pt->id_pt,
                        "jenjang" => $jenjang
                    ]
                ]
            );

            if (!empty($dataDetail)) {
                $dataDetail->jumlah_dosen = $jumlah['dosen'];
                $dataDetail->jumlah_mahasiswa = $jumlah['mahasiswa'];
                $dataDetail->updated_at = 'now()';
                $dataDetail->save();
            } else {
                $dataDetail = new JumlahDosenMahasiswaPt();
                $dataDetail->perguruan_tinggi_id = $pt->id_pt;
                $dataDetail->jenjang = $jenjang;
                $dataDetail->jumlah_dosen = $jumlah['dosen'];
                $dataDetail->jumlah_mahasiswa = $jumlah['mahasiswa'];
                $dataDetail->create();
            }
        }
    }

    private function syncDetailProdi($pt)
    {
        //Get Live Data
        $kemdikbudClient = new KemdikbudClient($this->config["pt_prop"]);
        $kemdikbudClient->setLogger($this->logger);
        $result = [];

        try {
            $result = $kemdikbudClient->getProdibyPT($pt->kode);
        } catch (\Exception $ex) {

        }

        if (count($result) > 0) {
            foreach ($result as $prodi) {
                $currentProdi = DetailProdiPt::findFirst(
                    [
                        "perguruan_tinggi_id = :id: and jenjang ilike :jenjang: and kode_prodi = :kode_prodi: and deleted_at is null",
                        "bind" => [
                            "id" => $pt->id_pt,
                            "jenjang" => $prodi['jenjang_didik']['nama'],
                            "kode_prodi" => $prodi['kode']
                        ]
                    ]
                );

                if (!empty($currentProdi)) {
                    $currentProdi->nama_prodi = $prodi['nama'] ?: '';
                    $currentProdi->akreditasi = $prodi['status'] ?: '';
                    $currentProdi->sks_lulus = $prodi['sks_lulus'] ?: 0;
                    $currentProdi->updated_at = 'now()';
                    $currentProdi->save();
                } else {
                    $newProdi = new DetailProdiPt();
                    $newProdi->perguruan_tinggi_id = $pt->id_pt;
                    $newProdi->jenjang = $prodi['jenjang_didik']['nama'] ?: '';
                    $newProdi->kode_prodi = $prodi['kode'] ?: '';
                    $newProdi->nama_prodi = $prodi['nama'] ?: '';
                    $newProdi->akreditasi = $prodi['status'] ?: '';
                    $newProdi->sks_lulus = $prodi['sks_lulus'] ?: 0;
                    $newProdi->create();
                }
            }
        }
    }

    private function getDetailPendapatanBelanjaByParent($parent, $tipe, $yearData)
    {
        $result['kode_alokasi'] = !is_null($parent) ? $parent->kode_output : $tipe;
        $result['nama_alokasi'] = !is_null($parent) ? $parent->nama_output : $tipe;
        $result['nilai_alokasi'] = [];
        $parentId = !is_null($parent) ? $parent->id : 0;

        $listChild = InfoAlokasiPendapatanBelanja::find(
            [
                'tipe_alokasi ilike :tipe: and parent_id = :parent_id: and deleted_at is null',
                "bind" => [
                    "tipe" => '%' . $tipe . '%',
                    "parent_id" => $parentId
                ]
            ]
        );

        if (count($listChild) > 0) {
            $result['sub_kategori'] = [];
            $totalNilai = [];

            foreach ($listChild as $child) {
                $childData = $this->getDetailPendapatanBelanjaByParent($child, $tipe, $yearData);

                // $totalNilai += $childData['nilai_alokasi'];
                foreach ($childData['nilai_alokasi'] as $year => $nilai) {
                    if (!array_key_exists($year, $totalNilai)) {
                        $totalNilai[$year] = 0;
                    }

                    $totalNilai[$year] += $nilai;
                }
                $result['sub_kategori'][] = $childData;
            }

            // $result['nilai_alokasi'] = $totalNilai;
            foreach ($totalNilai as $year => $nilai) {
                $result['nilai_alokasi'][$year] = $nilai;
            }
        } else {
            // $result['nilai_alokasi'] = array_key_exists($parentId, $data) ? $data[$parentId] : 0;
            foreach ($yearData as $year => $data) {
                $result['nilai_alokasi'][$year] = array_key_exists($parentId, $data) ? $data[$parentId] : 0;
            }
        }

        return $result;
    }

    private function getLiveDataKIPK($kode_pt, $nim, $tahun)
    {
        $result = [];

        $kemdikbudClient = new KemdikbudClient($this->config["kipk_prop"]);
        $kemdikbudClient->setLogger($this->logger);

        try {
            $data = $kemdikbudClient->getTrackingKIPK($kode_pt, $nim, $tahun);
            $result = $data['success']['data'];
        } catch (\Exception $ex) {

        }

        return $result;
    }

    public function getDashboardBoptnKampus($tahun) {
        $data = $this->rawQuery("SELECT tahun, COUNT(DISTINCT(perguruan_tinggi_id)) AS total_perguruan_tinggi, SUM(total_alokasi) AS total_alokasi FROM alokasi_boptn_bpptnbh WHERE tahun = $tahun GROUP BY tahun");
        $result = [
            'tahun' => $tahun,
            'total_perguruan_tinggi' => 0,
            'total_alokasi' => 0
        ];
        if(!empty($data)) {
            $result['total_perguruan_tinggi'] = $data[0]['total_perguruan_tinggi'];
            $result['total_alokasi'] = $data[0]['total_alokasi'];
        }

        return new SimpleResponse($result);
    }

    public function kemdikbudGet() {
        $this->checkScopes(['admin.view']);
        $json = $this->request->getJsonRawBody(true);
        $method = $json['method'];
        $params = $json['params'];
        $path = $json['path'];
        $kemdikbudClient = new KemdikbudClient($this->config["pt_prop"]);
        $kemdikbudClient->setLogger($this->logger);
        $res = $kemdikbudClient->doGet($method, $path, $params);

        return new SimpleResponse($res);
    }
    #endregion

    #region Private Function
    private function generateSheetPerguruanTinggi(Spreadsheet $spreadsheet)
    {
        $sheet = new PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Referensi PT');
        $spreadsheet->addSheet($sheet);
        $sheet->setCellValue("A1", 'ID');
        $sheet->setCellValue("B1", "KODE");
        $sheet->setCellValue("C1", 'NAMA');
        $sheet->setCellValue("D1", "KABUPATEN/KOTA");
        $sheet->setCellValue("E1", "PROVINSI");
        $sheet->getStyle("A1:E1")->getFont()->setBold(true);

        $rows = PerguruanTinggi::find();
        $index = 1;
        foreach ($rows as $row) {
            $index++;
            $sheet->setCellValue("A$index", $row->id_pt);
            $sheet->setCellValue("B$index", $row->kode);
            $sheet->setCellValue("C$index", $row->nama);
            $sheet->setCellValue("D$index", $row->kota_kab);
            $sheet->setCellValue("E$index", $row->provinsi);
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet->getStyle('A1:E' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(3);
        $sheet->getColumnDimension("B")->setAutoSize(true);
        $sheet->getColumnDimension("C")->setAutoSize(true);
        $sheet->getColumnDimension("D")->setAutoSize(true);
        $sheet->getColumnDimension("E")->setAutoSize(true);

        $spreadsheet->setActiveSheetIndex(0);
    }
    #endregion
}