<?php

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

class AsetController extends BaseController
{
    #region Constant
    const TIPE_ASET_SERTIFIKASI = 1;
    const TIPE_ASET_PENERTIBAN = 2;
    const TIPE_ASET_SARPRAS = 3;
    #endregion

    #region General
    public function listPeriode()
    {
        $this->checkIsAuthenticated();

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

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

        $result = $this->getActivePeriod($validatedRequest['tahun']);

        return new SimpleResponse($result);
    }

    public function uploadAttachment()
    {
        $this->checkSubmitScopes();

        $postParams = $this->request->getPost();

        $params = $this->validate($postParams, [
            'keterangan' => ['validators' => []],
            'metadata' => ['validators' => []]
        ]);

        $minioService = $this->minio;
        $service = new AttachmentService($this->shared, $minioService);

        if ($minioService->isSecondaryS3Available()) {
            $result = $service->uploadUsingSecondaryS3(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => [
                        "application/pdf",
                        "application/vnd.ms-excel",
                        "application/msword",
                        "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                    ],
                    'max-size-kb' => 2048
                ],
                ['tipe' => Attachments::TIPE_PRIVATE],
                $this->request,
                'aset-attachment' . DIRECTORY_SEPARATOR
            );
        }
        else {
            $result = $service->upload(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => [
                        "application/pdf",
                        "application/vnd.ms-excel",
                        "application/msword",
                        "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                    ],
                    'max-size-kb' => 2048
                ],
                ['tipe' => Attachments::TIPE_PRIVATE],
                $this->request,
                'aset-attachment' . DIRECTORY_SEPARATOR
            );
        }

        return new CollectionPaginatedResponse($result);
    }

    public function downloadAttachment($uuid)
    {
        $this->checkViewScopes();

        /** @var Attachments $attachment */
        $attachment = Attachments::findOrFail(
            [
                "uuid = :uuid:",
                "bind" => [
                    "uuid" => $uuid
                ]
            ]
        );

        if (!empty($attachment)) {
            if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                /** @var MinioService $minioService */
                $minioService = $this->minio;
                $filename = $attachment->filename;
                $filekey = 'diskusi_attachment' . DIRECTORY_SEPARATOR . 'aset-attachment' . DIRECTORY_SEPARATOR . $uuid;

                $url = $minioService->checkAndDownloadPrivate(
                    LogAttachments::AttachmentAsetPajak,
                    $attachment->created_at,
                    $filekey,
                    $filename,
                    true
                );

                return new SimpleResponse(str_replace('"', '', $url));
            }
            else if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                $directory = 'aset-attachment' . DIRECTORY_SEPARATOR;
                $filename = AttachmentHelper::getLocalPath($attachment, $directory);

                if (!file_exists($filename)) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found");
                }

                return MediaHelper::fileResponse($filename, $attachment->filename, true);
            }
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found or Not active");
        }
    }

    public function listAvailableInstansi()
    {
        $this->checkOneOfScopes(['bpkad.view', 'korsupgah.admin', 'pad.answer', 'aset.answer']);

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

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

        $result = [];
        $totalCount = 0;
        if (!is_null($this->getIdInstansi())) {
            $instansi = Instansi::findFirst($this->getIdInstansi());

            $strWhere = 'id_tipe in ({tipe_instansi:array}) and deleted_at is null';
            $bind['tipe_instansi'] = [1, 2];

            if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
                $strWhere .= ' and id = :id_instansi:';
                $bind['id_instansi'] = $instansi->id;
            }

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

            $result = Instansi::find(
                [
                    $strWhere,
                    "bind" => $bind,
                    "limit" => $validatedRequest['limit'],
                    "offset" => $validatedRequest['offset']
                ]
            )
                ->toArray();
        }

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

    public function deleteAllAset($type)
    {
        $this->checkSubmitScopes();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());
        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk aksi ini.');
        }

        $countSuccess = 0;
        $countFailed = 0;

        $listData = null;

        if($type == self::TIPE_ASET_SERTIFIKASI) {
            $listData = AsetSertifikasi::find();
        } elseif ($type == self::TIPE_ASET_PENERTIBAN) {
            $listData = AsetPenertiban::find();
        } elseif ($type == self::TIPE_ASET_SARPRAS) {
            $listData = AsetPenertibanSarpras::find();
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Silahkan Pilih Tipe Aset yang ingin dihapus');
        }

        if (!is_null($listData)) {
            foreach ($listData as $data) {
                $res = $data->delete();
                if ($res) {
                    $countSuccess++;
                } else {
                    $countFailed++;
                }
            }
        }

        return new SimpleResponse('Berhasil menghapus [' . $countSuccess . '] data dan gagal menghapus [' . $countFailed . '] data.');
    }

    private function checkViewScopes() {
        $this->checkOneOfScopes(['bpkad.view', 'korsupgah.admin', 'aset.answer']);
    }

    private function checkSubmitScopes() {
        $this->checkOneOfScopes(['bpkad.submit', 'aset.answer']);
    }

    private function handleAttachment($listUUID, $tipeSubmodul, $dataId, $mode) //mode: 0=create, 1=edit, 2=delete
    {
        if ($mode == 0) {
            if (!empty($listUUID)) {
                $arrUUID = explode('|', $listUUID);
                if (count($arrUUID) > 5) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Jumlah attachment maksimum adalah 5');
                }

                $listAttachment = Attachments::find(
                    [
                        "uuid in ({list_uuid:array}) and deleted_at is null",
                        "bind" => [
                            "list_uuid" => $arrUUID
                        ]
                    ]
                );
                foreach ($listAttachment as $attachment) {
                    $attachmentData = new AttachmentAsetPajak();
                    $attachmentData->tipe_submodul = $tipeSubmodul;
                    $attachmentData->data_id = $dataId;
                    $attachmentData->attachment_id = $attachment->id;
                    $attachmentData->create();
                }
            }
        } else if ($mode == 1) {
            $listExistingMap = AttachmentAsetPajak::find(
                [
                    "tipe_submodul = :tipe_submodul: and data_id = :data_id: and deleted_at is null",
                    "bind" => [
                        "tipe_submodul" => $tipeSubmodul,
                        "data_id" => $dataId
                    ]
                ]
            );

            $arrExistingAttachmentId = [];
            foreach ($listExistingMap as $existingMap) {
                $arrExistingAttachmentId[] = $existingMap->attachment_id;
            }

            if (!empty($listUUID)) {
                $arrUUID = explode('|', $listUUID);
                if (count($arrUUID) > 5) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Jumlah attachment maksimum adalah 5');
                }

                $listAttachment = Attachments::find(
                    [
                        "uuid in ({list_uuid:array}) and deleted_at is null",
                        "bind" => [
                            "list_uuid" => $arrUUID
                        ]
                    ]
                );

                $arrNewAttachmentId = [];
                foreach ($listAttachment as $attachment) {
                    $arrNewAttachmentId[] = $attachment->id;
                }

                $deletedAttachmentId = array_diff($arrExistingAttachmentId, $arrNewAttachmentId);
                $addedAttachmentId = array_diff($arrNewAttachmentId, $arrExistingAttachmentId);

                //Process Newly Added Attachment
                foreach ($listAttachment as $attachment) {
                    if (in_array($attachment->id, $addedAttachmentId)) {
                        $attachmentData = new AttachmentAsetPajak();
                        $attachmentData->tipe_submodul = $tipeSubmodul;
                        $attachmentData->data_id = $dataId;
                        $attachmentData->attachment_id = $attachment->id;
                        $attachmentData->create();
                    }
                }

                //Process Deleted Attachment
                foreach ($listExistingMap as $existingMap) {
                    if (in_array($existingMap->attachment_id, $deletedAttachmentId)) {
                        $existingMap->deleted_at = 'now()';
                        $existingMap->save();

                        $attachment = Attachments::findFirstById($existingMap->id);
                        $this->deleteAttachment($attachment);
                    }
                }
            } else {
                foreach ($listExistingMap as $existingMap) {
                    $existingMap->deleted_at = 'now()';
                    $existingMap->save();

                    $attachment = Attachments::findFirstById($existingMap->id);
                    $this->deleteAttachment($attachment);
                }
            }
        } else if ($mode == 2) {
            $listExistingMap = AttachmentAsetPajak::find(
                [
                    "tipe_submodul = :tipe_submodul: and data_id = :data_id: and deleted_at is null",
                    "bind" => [
                        "tipe_submodul" => $tipeSubmodul,
                        "data_id" => $dataId
                    ]
                ]
            );

            foreach ($listExistingMap as $existingMap) {
                $existingMap->deleted_at = 'now()';
                $existingMap->save();

                $attachment = Attachments::findFirstById($existingMap->id);
                $this->deleteAttachment($attachment);
            }
        }
    }

    private function deleteAttachment($attachment)
    {
        //AttachmentHelper::delete($attachment, true, $this->minio, 'aset-attachment');
    }

    private function getActivePeriod($tahun)
    {
        $result = [];
        if ($tahun <= 2020) {
            $result[] = 'per 31 Desember ' . $tahun;
        } else {
            $result = [
                'Triwulan I',
                'Triwulan II',
                'Triwulan III',
                'Triwulan IV'
            ];
        }

        return $result;
    }

    private function ConvertDateToLocalDate($dateInput)
    {
        $result['date'] = "";
        $result['datetime'] = "";
        $result['day'] = 0;
        $result['month'] = 0;
        $result['year'] = 0;
        $result['hour'] = 0;
        $result['minute'] = 0;
        $result['second'] = 0;
        $result['milisecond'] = 0;

        if (!is_null($dateInput) && !empty($dateInput) && $dateInput != "") {
            $sepDateTime = explode(" ", $dateInput);
            $sepDate = explode("-", $sepDateTime[0]);
            $sepTime = explode(":", $sepDateTime[1]);
            $sepSecond = explode(".", $sepTime[2]);

            $result['year'] = $sepDate[0];
            $result['month'] = $sepDate[1];
            $result['day'] = $sepDate[2];

            $result['hour'] = $sepTime[0];
            $result['minute'] = $sepTime[1];
            $result['second'] = $sepSecond[0];
            //$result['milisecond'] = $sepSecond[1];
            $result['date'] = DateHelper::formatFromDate($result['year'], $result['month'], $result['day']);
            $result['datetime'] = $result['date'] . " " . $result['hour'] . ":" . $result['minute'];
        }

        return $result;
    }

    private function generateExcelLaporanAset($items, $type)
    {

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $todayLabel = DateHelper::formatToday();

        if ($type == ExcelService::EXCEL_ASET_SERTIFIKASI) {
            #region Aset Sertifikasi
            $sheet->setCellValue("A1", 'Daftar Laporan Aset Sertifikasi');
            $sheet->setCellValue("A2", 'Diunduh Pada ' . $todayLabel);

            $sheet->getStyle('A1:A2')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->mergeCells('A1:M1');
            $sheet->mergeCells('A2:M2');

            $sheet->setCellValue("A4", 'Nama Instansi');
            $sheet->setCellValue("B4", 'Tahun');
            $sheet->setCellValue("C4", 'Periode');
            $sheet->setCellValue("D4", 'Keterangan');
            //Belum bersertifikat
            $sheet->setCellValue("E4", 'Belum Bersertifikat');
            $sheet->setCellValue("E5", 'Jumlah');
            $sheet->setCellValue("F5", 'Bidang');
            $sheet->setCellValue("G5", 'Luas');
            $sheet->setCellValue("H5", 'K1');
            $sheet->setCellValue("I5", 'K2');
            $sheet->setCellValue("J5", 'K3');
            $sheet->setCellValue("K5", 'K4');
            //bersertifikat
            $sheet->setCellValue("L4", 'Bersertifikat');
            $sheet->setCellValue("L5", 'Jumlah');
            $sheet->setCellValue("M5", 'Bidang');
            $sheet->setCellValue("N5", 'Luas');
            ///
            $sheet->setCellValue("O4", 'Nilai');
            $sheet->setCellValue("P4", 'Dibuat Pada');
            $sheet->setCellValue("Q4", 'Dibuat Oleh');

            $sheet->getStyle('A4:P5')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            //Merge Headers
            $sheet->mergeCells('A4:A5');
            $sheet->mergeCells('B4:B5');
            $sheet->mergeCells('C4:C5');
            $sheet->mergeCells('D4:D5');
            $sheet->mergeCells('E4:K4');
            $sheet->mergeCells('H4:J4');
            $sheet->mergeCells('L4:N4');
            $sheet->mergeCells('O4:O5');
            $sheet->mergeCells('P4:P5');
            $sheet->mergeCells('Q4:Q5');

            $row = 6;
            $index = 1;

            foreach ($items as $item) {
                $tanggalLaporan = $this->ConvertDateToLocalDate($item['created_at']);
                $nilai = 'Rp ' . number_format($item['nilai'], 0, ",", ".");

                $sheet->setCellValue("A$row", $item['nama_instansi']);
                $sheet->setCellValue("B$row", $item['tahun']);
                $sheet->setCellValue("C$row", $item['periode']);
                $sheet->setCellValue("D$row", $item['keterangan']);
                $sheet->setCellValue("E$row", $item['non_serti_jumlah']);
                $sheet->setCellValue("F$row", $item['non_serti_bidang']);
                $sheet->setCellValue("G$row", $item['non_serti_luas']);
                $sheet->setCellValue("H$row", $item['jumlah_k1'] != null ? $item['jumlah_k1']  :0);
                $sheet->setCellValue("I$row", $item['jumlah_k2'] != null ? $item['jumlah_k2']  :0);
                $sheet->setCellValue("J$row", $item['jumlah_k3'] != null ? $item['jumlah_k3']  :0);
                $sheet->setCellValue("K$row", $item['jumlah_k4'] != null ? $item['jumlah_k4']  :0);
                $sheet->setCellValue("L$row", $item['serti_jumlah']);
                $sheet->setCellValue("M$row", $item['serti_bidang']);
                $sheet->setCellValue("N$row", $item['serti_luas']);
                $sheet->setCellValueExplicit("O$row", $nilai, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValue("P$row", $tanggalLaporan['datetime']);
                $sheet->setCellValue("Q$row", $item['created_by_name']);

                $index++;
                $row++;
            }

            $sheet->getColumnDimension('A')->setAutoSize(true);
            $sheet->getColumnDimension('B')->setAutoSize(true);
            $sheet->getColumnDimension('C')->setAutoSize(true);
            $sheet->getColumnDimension('D')->setAutoSize(true);
            $sheet->getColumnDimension('E')->setAutoSize(true);
            $sheet->getColumnDimension('F')->setAutoSize(true);
            $sheet->getColumnDimension('G')->setAutoSize(true);
            $sheet->getColumnDimension('H')->setAutoSize(true);
            $sheet->getColumnDimension('I')->setAutoSize(true);
            $sheet->getColumnDimension('J')->setAutoSize(true);
            $sheet->getColumnDimension('K')->setAutoSize(true);
            $sheet->getColumnDimension('L')->setAutoSize(true);
            $sheet->getColumnDimension('M')->setAutoSize(true);
            $sheet->getColumnDimension('N')->setAutoSize(true);
            $sheet->getColumnDimension('O')->setAutoSize(true);
            $sheet->getColumnDimension('P')->setAutoSize(true);
            $sheet->getColumnDimension('Q')->setAutoSize(true);
            $sheet->getColumnDimension('R')->setAutoSize(true);


            $styleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => 'FF000000'],
                    ],
                ],
            ];

            $sheet->getStyle('A4:Q' . ($row - 1))->applyFromArray($styleArray);
            // $sheet->getStyle('P4:R' . ($row - 1))->getAlignment()->setWrapText(true);
            #endregion
        } elseif ($type == ExcelService::EXCEL_ASET_PENERTIBAN) {
            #region Aset Penertiban
            $sheet->setCellValue("A1", 'Daftar Laporan Aset Penertiban');
            $sheet->setCellValue("A2", 'Diunduh Pada ' . $todayLabel);

            $sheet->getStyle('A1:A2')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->mergeCells('A1:O1');
            $sheet->mergeCells('A2:O2');


            $sheet->setCellValue("A4", 'Nama Instansi');
            $sheet->setCellValue("B4", 'Tahun');
            $sheet->setCellValue("C4", 'Periode');
            $sheet->setCellValue("D4", 'Uraian');
            //Tanah
            $sheet->setCellValue("E4", 'Tanah');
            $sheet->setCellValue("E5", 'Unit');
            $sheet->setCellValue("F5", 'Luas');
            $sheet->setCellValue("G5", 'Nilai');
            //Bangunan
            $sheet->setCellValue("H4", 'Bangunan');
            $sheet->setCellValue("H5", 'Unit');
            $sheet->setCellValue("I5", 'Luas');
            $sheet->setCellValue("J5", 'Nilai');
            //Kendaraan Dinas
            $sheet->setCellValue("K4", 'Kendaraan Dinas');
            $sheet->setCellValue("K5", 'Unit');
            $sheet->setCellValue("L5", 'Nilai');

            // tanah dan bangunan
            $sheet->setCellValue("M4", "Tanah dan Bangunan");
            $sheet->setCellValue("M5", "Unit");
            $sheet->setCellValue("N5", "Luas");
            $sheet->setCellValue("O5", "Nilai");
            ///
            $sheet->setCellValue("P4", 'Total Nilai');
            $sheet->setCellValue("Q4", 'Dibuat Pada');
            $sheet->setCellValue("R4", 'Dibuat Oleh');

            $sheet->getStyle('A4:R4')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            //Merge Headers
            $sheet->mergeCells('A4:A5');
            $sheet->mergeCells('B4:B5');
            $sheet->mergeCells('C4:C5');
            $sheet->mergeCells('D4:D5');
            $sheet->mergeCells('E4:G4');
            $sheet->mergeCells('H4:J4');
            $sheet->mergeCells('K4:L4');
            $sheet->mergeCells('M4:O4');
            $sheet->mergeCells('P4:P5');
            $sheet->mergeCells('Q4:Q5');
            $sheet->mergeCells('R4:R5');

            $row = 6;
            $index = 1;

            foreach ($items as $item) {
                $tanggalLaporan = $this->ConvertDateToLocalDate($item['created_at']);
                $nilaiTanah = 'Rp ' . number_format($item['nilai_tanah'], 0, ",", ".");
                $nilaiBangunan = 'Rp ' . number_format($item['nilai_bangunan'], 0, ",", ".");
                $nilaiKendaraan = 'Rp ' . number_format($item['nilai_kendaraan'], 0, ",", ".");
                $nilaiTanahDanBangunan = 'Rp ' . number_format($item['nilai_tanah_bangunan'], 0, ",", ".");
                $nilaiTotal = 'Rp ' . number_format($item['nilai_total'], 0, ",", ".");

                $sheet->setCellValue("A$row", $item['nama_instansi']);
                $sheet->setCellValue("B$row", $item['tahun']);
                $sheet->setCellValue("C$row", $item['periode']);
                $sheet->setCellValue("D$row", $item['uraian']);
                $sheet->setCellValue("E$row", $item['unit_tanah']);
                $sheet->setCellValue("F$row", $item['luas_tanah']);
                $sheet->setCellValueExplicit("G$row", $nilaiTanah, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValue("H$row", $item['unit_bangunan']);
                $sheet->setCellValue("I$row", $item['luas_bangunan']);
                $sheet->setCellValueExplicit("J$row", $nilaiBangunan, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValue("K$row", $item['luas_bangunan']);
                $sheet->setCellValueExplicit("L$row", $nilaiKendaraan, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValue("M$row", $item['unit_tanah_bangunan']);
                $sheet->setCellValue("N$row", $item['luas_tanah_bangunan']);
                $sheet->setCellValueExplicit("O$row", $nilaiTanahDanBangunan, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit("P$row", $nilaiTotal, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValue("Q$row", $tanggalLaporan['datetime']);
                $sheet->setCellValue("R$row", $item['created_by_name']);

                $index++;
                $row++;
            }

            $sheet->getColumnDimension('A')->setAutoSize(true);
            $sheet->getColumnDimension('B')->setAutoSize(true);
            $sheet->getColumnDimension('C')->setAutoSize(true);
            $sheet->getColumnDimension('D')->setAutoSize(true);
            $sheet->getColumnDimension('E')->setAutoSize(true);
            $sheet->getColumnDimension('F')->setAutoSize(true);
            $sheet->getColumnDimension('G')->setAutoSize(true);
            $sheet->getColumnDimension('H')->setAutoSize(true);
            $sheet->getColumnDimension('I')->setAutoSize(true);
            $sheet->getColumnDimension('J')->setAutoSize(true);
            $sheet->getColumnDimension('K')->setAutoSize(true);
            $sheet->getColumnDimension('L')->setAutoSize(true);
            $sheet->getColumnDimension('M')->setAutoSize(true);
            $sheet->getColumnDimension('N')->setAutoSize(true);
            $sheet->getColumnDimension('O')->setAutoSize(true);
            $sheet->getColumnDimension('P')->setAutoSize(true);
            $sheet->getColumnDimension('Q')->setAutoSize(true);
            $sheet->getColumnDimension('R')->setAutoSize(true);

            $styleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => 'FF000000'],
                    ],
                ],
            ];

            $sheet->getStyle('A4:R' . ($row - 1))->applyFromArray($styleArray);
            #endregion
        } elseif ($type == ExcelService::EXCEL_ASET_PENERTIBAN_SARPRAS) {
            #region Aset Penertiban Sarpras
            $sheet->setCellValue("A1", 'Daftar Laporan Aset Penertiban Prasarana, Sarana, dan Utilitas');
            $sheet->setCellValue("A2", 'Diunduh Pada ' . $todayLabel);

            $sheet->getStyle('A1:A2')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->mergeCells('A1:I1');
            $sheet->mergeCells('A2:I2');

            $sheet->setCellValue("A4", 'Nama Instansi');
            $sheet->setCellValue("B4", 'Tahun');
            $sheet->setCellValue("C4", 'Periode');
            $sheet->setCellValue("D4", 'Keterangan');
            $sheet->setCellValue("E4", 'Jumlah Lokasi');
            $sheet->setCellValue("F4", 'Luas/Unit');
            $sheet->setCellValue("G4", 'Nilai');
            $sheet->setCellValue("H4", 'Dibuat Pada');
            $sheet->setCellValue("I4", 'Dibuat Oleh');

            $sheet->getStyle('A4:I4')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            $row = 5;
            $index = 1;

            foreach ($items as $item) {
                $tanggalLaporan = $this->ConvertDateToLocalDate($item['created_at']);
                $nilai = 'Rp ' . number_format($item['nilai'], 0, ",", ".");

                $sheet->setCellValue("A$row", $item['nama_instansi']);
                $sheet->setCellValue("B$row", $item['tahun']);
                $sheet->setCellValue("C$row", $item['periode']);
                $sheet->setCellValue("D$row", $item['keterangan']);
                $sheet->setCellValue("E$row", $item['jumlah_lokasi']);
                $sheet->setCellValue("F$row", $item['luas_unit']);
                $sheet->setCellValueExplicit("G$row", $nilai, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValue("H$row", $tanggalLaporan['datetime']);
                $sheet->setCellValue("I$row", $item['created_by_name']);

                $index++;
                $row++;
            }

            $sheet->getColumnDimension('A')->setAutoSize(true);
            $sheet->getColumnDimension('B')->setAutoSize(true);
            $sheet->getColumnDimension('C')->setAutoSize(true);
            $sheet->getColumnDimension('D')->setAutoSize(true);
            $sheet->getColumnDimension('E')->setAutoSize(true);
            $sheet->getColumnDimension('F')->setAutoSize(true);
            $sheet->getColumnDimension('G')->setAutoSize(true);
            $sheet->getColumnDimension('H')->setAutoSize(true);
            $sheet->getColumnDimension('I')->setAutoSize(true);

            $styleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => 'FF000000'],
                    ],
                ],
            ];

            $sheet->getStyle('A4:I' . ($row - 1))->applyFromArray($styleArray);
            #endregion
        }

        return $spreadsheet;
    }
    #endregion

    #region Aset Sertifikasi
    public function listAsetSertifikasi()
    {

        $this->checkViewScopes();

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

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

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

        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and instansi_id = :instansi_id:';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];
        }

        $asetList = AsetSertifikasi::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc",
            ]
        );

        $result = [];
        foreach ($asetList as $aset) {
            $result[] = $aset->toArraySimple();
        }

        $targetList = AsetSertifikasiTarget::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc",
            ]
        );

        $targetMap = [];
        foreach ($targetList as $target) {
            $targetArray = $target->toArray();
            $targetMap[$targetArray['tahun']] = $targetArray;
        }

        $availableYears = DateHelper::listAvailableYears();
        $categorizedAset = array_fill_keys($availableYears, []);

        foreach ($result as $aset) {
            $year = $aset['tahun'];
            if (in_array($year, $availableYears)) {
                $categorizedAset[$year][] = $aset;
            }
        }

        $finalResult = [];
        foreach ($categorizedAset as $year => $asetList) {
            $sortedResult = CommonTools::arrayMultiSort($asetList, 'tahun', SORT_DESC, 'period_order', SORT_ASC);
            $finalResult[] = [
                'tahun' => $year,
                'result' => $sortedResult
            ];
        }
        foreach ($finalResult as &$result) {
            $year = $result['tahun'];
            if (isset($targetMap[$year])) {
                $result['target'] = $targetMap[$year]['target'];
                $result['anggaran'] = $targetMap[$year]['anggaran'];
            }

        }

        return new SimpleResponse($finalResult);
    }


    public function detailAsetSertifikasi($id)
    {
        $this->checkViewScopes();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetSertifikasi::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 (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            if ($data->instansi_id != $instansi->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
            }
        }

        $result = $data->toArrayDetail();

        return new CollectionPaginatedResponse([$result]);
    }

    public function submitAsetSertifikasi()
    {
        $this->checkSubmitScopes();

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

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required',  'digit']],
                'periode' => ['validators' => []],
                'serti_jumlah' => ['validators' => ['required', 'digit']],
                'serti_bidang' => ['validators' => ['required', 'digit']],
                'serti_luas' => ['validators' => ['required', 'numeric']],
                'non_serti_jumlah' => ['validators' => ['required', 'digit']],
                'non_serti_bidang' => ['validators' => ['required', 'digit']],
                'non_serti_luas' => ['validators' => ['required', 'numeric']],
                'nilai' => ['validators' => ['required', 'numeric']],
                'keterangan' => ['validators' => []],
                'jumlah_k1' => ['validators' => ['digit']],
                'jumlah_k2' => ['validators' => ['digit']],
                'jumlah_k3' => ['validators' => ['digit']],
                'jumlah_k4' => ['validators' => ['digit']],
                'target' => ['validators', ['required', 'numeric']],
                'anggaran' => ['validators', ['required', 'numeric']],
                'attachment_uuid' => ['validators' => []],
            ]
        );

        // echo json_encode($validatedRequest);
        // die();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        // Initialize new AsetSertifikasi object

        // echo json_encode($instansi->id);
        // die();
        $newData = new AsetSertifikasi();

        $newData->serti_jumlah = $validatedRequest['serti_jumlah'];
        $newData->serti_bidang = $validatedRequest['serti_bidang'];
        $newData->serti_luas = $validatedRequest['serti_luas'];
        $newData->non_serti_jumlah = $validatedRequest['non_serti_jumlah'];
        $newData->non_serti_bidang = $validatedRequest['non_serti_bidang'];
        $newData->non_serti_luas = $validatedRequest['non_serti_luas'];
        $newData->nilai = $validatedRequest['nilai'];
        $newData->keterangan = $validatedRequest['keterangan'];
        $newData->jumlah_k1 = $validatedRequest['jumlah_k1'];
        $newData->jumlah_k2 = $validatedRequest['jumlah_k2'];
        $newData->jumlah_k3 = $validatedRequest['jumlah_k3'];
        $newData->jumlah_k4 = $validatedRequest['jumlah_k4'];
        $newData->target = $validatedRequest['target'];
        $newData->anggaran = $validatedRequest['anggaran'];
        $newData->tahun = $validatedRequest['tahun'];
        $newData->periode = $validatedRequest['periode'];
        $newData->instansi_id = $instansi->id;
        $newData->attachment_uuid = $validatedRequest['attachment_uuid'];
        $newData->created_by = $newData->updated_by = $this->shared->user->um_id;

        // Save the new data
        $res = $newData->create();

        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_SERTIFIKASI, $newData->id, 0);

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_aset_sertifikasi" => $newData->id
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Sertifikasi Error");
        }
    }


    public function editAsetSertifikasi($id)
    {

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

        $validatedRequest = $this->validate(
            $params,
            [

                'tahun' => ['validators' => ['required',  'digit']],
                'periode' => ['validators' => []],
                'serti_jumlah' => ['validators' => ['required', 'digit']],
                'serti_bidang' => ['validators' => ['required', 'digit']],
                'serti_luas' => ['validators' => ['required', 'numeric']],
                'non_serti_jumlah' => ['validators' => ['required', 'digit']],
                'non_serti_bidang' => ['validators' => ['required', 'digit']],
                'non_serti_luas' => ['validators' => ['required', 'numeric']],
                'nilai' => ['validators' => ['required', 'numeric']],
                'keterangan' => ['validators' => []],
                'jumlah_k1' => ['validators' => ['digit']],
                'jumlah_k2' => ['validators' => ['digit']],
                'jumlah_k3' => ['validators' => ['digit']],
                'jumlah_k4' => ['validators' => ['digit']],

                'target' => ['validators', ['required', 'numeric']],
                'anggaran' => ['validators', ['required', 'numeric']],
                'attachment_uuid' => ['validators' => []],
            ]
        );


        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetSertifikasi::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 ($data->instansi_id != $instansi->id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
        }

        $data->serti_jumlah = $validatedRequest['serti_jumlah'];
        $data->serti_bidang = $validatedRequest['serti_bidang'];
        $data->serti_luas = $validatedRequest['serti_luas'];
        $data->non_serti_jumlah = $validatedRequest['non_serti_jumlah'];
        $data->non_serti_bidang = $validatedRequest['non_serti_bidang'];
        $data->non_serti_luas = $validatedRequest['non_serti_luas'];
        $data->nilai = $validatedRequest['nilai'];
        $data->keterangan = $validatedRequest['keterangan'];
        $data->updated_by = $this->shared->user->um_id;
        $data->updated_at = 'now();';
        $data->jumlah_k1 = $validatedRequest['jumlah_k1'];
        $data->jumlah_k2 = $validatedRequest['jumlah_k2'];
        $data->jumlah_k3 = $validatedRequest['jumlah_k3'];
        $data->jumlah_k4 = $validatedRequest['jumlah_k4'];
        $data->target = $validatedRequest['target'];
        $data->anggaran = $validatedRequest['anggaran'];
        $data->tahun = $validatedRequest['tahun'];
        $data->periode = $validatedRequest['periode'];
        $data->attachment_uuid = $validatedRequest['attachment_uuid'];
        $data->instansi_id = $instansi->id;

        $res = $data->save();

        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_SERTIFIKASI, $data->id, 1);

            $data = AsetSertifikasi::findFirst($data->id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $data
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Sertifikasi Error");
        }
    }

    public function deleteAsetSertifikasi($id)
    {
        $this->checkSubmitScopes();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetSertifikasi::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 (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            if ($data->instansi_id != $instansi->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
            }
        }

        $data->deleted_by = $this->shared->user->um_id;
        $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 Aset Sertifikasi Error");
        }
    }

    public function uploadAsetSertifikasi()
    {
        $this->checkSubmitScopes();

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

        $successCount = 0;
        $fails = [];
        $values = [];
        $errors = [];

        if (!empty($document)) {
            #region Process Excel
            $excelService = new ExcelService($document, ExcelService::EXCEL_ASET_SERTIFIKASI);
            $values = $excelService->RetrieveExcelData();
            $errors = $excelService->GetErrorMessage();
            if (count($errors) > 0) {
                return new ObjectResponse(
                    [
                        "success" => false,
                        "data" => [
                            "error_messages" => $errors
                        ],
                    ]
                );
            }
            #endregion

            if (empty($errors)) {
                $this->db->begin();
                $rowCount = 1;
                foreach ($values as $data) {
                    //Validate data
                    $instansi = Instansi::findFirst(
                        [
                            "nama = :nama: and deleted_at is null",
                            "bind" => [
                                "nama" => $data['column_1']
                            ]
                        ]
                    );

                    if (empty($instansi)) {
                        $errors[] = 'Instansi [' . $data['column_1'] . '] pada Data [' . $rowCount . '] Tidak Ditemukan';
                        $rowCount++;
                        continue;
                    }

                    //Populate and Insert Data
                    $newData = new AsetSertifikasi();
                    $newData->instansi_id = $instansi->id;
                    $newData->tahun = $data['column_2'] ?: 0;
                    $newData->periode = $data['column_3'] ?: '';
                    $newData->keterangan = $data['column_4'] ?: '';
                    $newData->non_serti_jumlah = $data['column_5'] ?: 0;
                    $newData->non_serti_bidang = $data['column_6'] ?: 0;
                    $newData->non_serti_luas = $data['column_7'] ?: 0;
                    $newData->serti_jumlah = $data['column_8'] ?: 0;
                    $newData->serti_bidang = $data['column_9'] ?: 0;
                    $newData->serti_luas = $data['column_10'] ?: 0;
                    $newData->nilai = $data['column_11'] ?: 0;
                    $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
                    $res = $newData->create();
                    if (!$res) {
                        $errors[] = 'Save Aset Sertifikasi No. [' . $rowCount . '] Error';
                    }
                    $rowCount++;
                }

                if(count($errors) > 0) {
                    $this->db->rollback();
                } else {
                    $this->db->commit();
                }
            }
        } else {
            $errors[] = 'Document is empty';
        }

        return new ObjectResponse(
            [
                "success" => count($errors) > 0 ? false : true,
                "data" => [
                    "messages" => count($errors) > 0 ? $errors : ['[' . count($values) . '] Data Successfully Inserted']
                ],
            ]
        );
    }

    public function downloadAsetSertifikasi()
    {
        $this->checkViewScopes();

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

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

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

        if (strtolower($instansi->nama) != DiskusiBansos::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and instansi_id = :instansi_id:';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];
        }

        $asetList = AsetSertifikasi::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc",
            ]
        );

        $result = [];
        foreach ($asetList as $aset) {
            $result[] = $aset->toArraySimple();
        }

        $result = CommonTools::arrayMultiSort($result, 'tahun', SORT_DESC, 'period_order', SORT_ASC);

        #region Generate Excel File

        $spreadsheet = $this->generateExcelLaporanAset($result, ExcelService::EXCEL_ASET_SERTIFIKASI);
        $todayLabel = DateHelper::formatToday();

        $writer = new Xlsx($spreadsheet);
        $filename = "Export Aset Sertifikasi - $todayLabel.xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');

        #endregion
    }


    public function submitAsetSertifikasiTarget()
    {
        $this->checkSubmitScopes();
        $params = $this->request->getJsonRawBody(true);
        $params['keterangan'] = array_key_exists('keterangan', $params) ? $params['keterangan'] : '';
        $params['attachment_uuid'] = array_key_exists('attachment_uuid', $params) ? $params['attachment_uuid'] : '';
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : '';
        $params['anggaran'] = array_key_exists('anggaran', $params) ? $params['anggaran'] : '';
        $params['target'] = array_key_exists('target', $params) ? $params['target'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['digit']],
                'target' => ['validators' => ['digit']],
                'anggaran' => ['validators' => ['digit']],
                'keterangan' => ['validators' => []],
                'attachment_uuid' => ['validators' => []],
            ]
        );


        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());


        $strWhere = 'deleted_at is null and tahun=:year: and instansi_id = :instansi_id :';
        $bind = [
             'year'         => $validatedRequest['tahun'],
             'instansi_id'  => $instansi->id,
        ];


        $data = AsetSertifikasiTarget::findFirst([
            "conditions" =>  "deleted_at is null and tahun = :year: and instansi_id = :instansi_id:",
            "bind"       => [
                "year"          => $validatedRequest['tahun'],
                "instansi_id"   => $instansi->id
            ]
        ]);

        if(!empty($data)){
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "The data is already exist! Please refresh your page in order to get new content!");
        }


        $newData = new AsetSertifikasiTarget();

        $newData->tahun = $validatedRequest['tahun'];
        $newData->instansi_id = $instansi->id;
        $newData->target = $validatedRequest['target'];
        $newData->anggaran = $validatedRequest['anggaran'];
        $newData->keterangan = $validatedRequest['keterangan'];
        $newData->attachment_uuid = $validatedRequest['attachment_uuid'];
        $newData->created_by = $this->shared->user->um_id;


        $res = $newData->create();

        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_SERTIFIKASI_TARGET, $newData->id, 0);

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_aset_sertifikasi_target" => $newData->id
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Target/Anggaran Sertifikasi Error");
        }
    }

    public function deleteAsetSertifikasiTarget($year)
    {
        $this->checkSubmitScopes();


        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetSertifikasiTarget::findFirst([
            "conditions" => "deleted_at is null and tahun = :year:",
            "bind"       => ["year" => $year]
        ]);

        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 (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            if ($data->instansi_id != $instansi->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
            }
        }

        $data->deleted_by = $this->shared->user->um_id;
        $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 Aset Sertifikasi Error");
        }
    }

    public function editAsetSertifikasiTarget()
    {
        $this->checkSubmitScopes();

        $params = $this->request->getJsonRawBody(true);

        $params['keterangan'] = array_key_exists('keterangan', $params) ? $params['keterangan'] : '';
        $params['attachment_uuid'] = array_key_exists('attachment_uuid', $params) ? $params['attachment_uuid'] : '';
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : '';
        $params['target'] = array_key_exists('target', $params) ? $params['target'] : '';
        $params['anggaran'] = array_key_exists('anggaran', $params) ? $params['anggaran'] : '';


        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required',  'digit']],
                'target' => ['validators' => ['required', 'digit']],
                'anggaran' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => []],
                'attachment_uuid' => ['validators' => []],
            ]
        );

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());
        // $data = AsetSertifikasiTarget::findFirst($id);
        $data = AsetSertifikasiTarget::findFirst([
            "conditions" =>  "deleted_at is null and tahun = :year: and instansi_id = :instansi_id:",
            "bind"       => [
                "year"          => $validatedRequest['tahun'],
                "instansi_id"   => $instansi->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 ($data->instansi_id != $instansi->id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
        }

        $data->tahun = $validatedRequest['tahun'];
        $data->instansi_id = $instansi->id;
        $data->target = $validatedRequest['target'];
        $data->anggaran = $validatedRequest['anggaran'];
        $data->keterangan = $validatedRequest['keterangan'];
        $data->attachment_uuid = $validatedRequest['attachment_uuid'];
        $data->updated_by = $this->shared->user->um_id;
        $data->updated_at = 'now();';

        $res = $data->save();

        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_SERTIFIKASI_TARGET, $data->id, 1);

            $data = AsetSertifikasiTarget::findFirst($data->id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $data
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Target/Anggaran Sertifikasi Error");
        }
    }

    #endregion

    #region Aset Penertiban
    public function listAsetPenertiban()
    {
        $this->checkViewScopes();

        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

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

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

        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and instansi_id = :instansi_id:';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];
        }

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

        $asetList = AsetPenertiban::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc",
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );

        $result = [];
        foreach ($asetList as $aset) {
            $result[] = $aset->toArraySimple();
        }

        $result = CommonTools::arrayMultiSort($result, 'tahun', SORT_DESC, 'period_order', SORT_ASC);

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

    public function detailAsetPenertiban($id)
    {
        $this->checkViewScopes();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetPenertiban::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 (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            if ($data->instansi_id != $instansi->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
            }
        }

        $result = $data->toArrayDetail();

        return new CollectionPaginatedResponse([$result]);
    }

    public function submitAsetPenertiban()
    {
        $this->checkSubmitScopes();
        $params = $this->request->getJsonRawBody(true);
        $params['unit_bangunan'] = array_key_exists('unit_bangunan', $params) ? $params['unit_bangunan'] : 0;
        $params['nilai_bangunan'] = array_key_exists('nilai_bangunan', $params) ? $params['nilai_bangunan'] : 0;
        $params['luas_bangunan'] = array_key_exists('luas_bangunan', $params) ? $params['luas_bangunan'] : 0;
        $params['attachment_uuid'] = array_key_exists('attachment_uuid', $params) ? $params['attachment_uuid'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'periode' => ['validators' => ['required']],
                'uraian' => ['validators' => ['required']],
                'unit_tanah' => ['validators' => ['required', 'digit']],
                'nilai_tanah' => ['validators' => ['required', 'numeric']],
                'luas_tanah' => ['validators' => ['required', 'numeric']],
                'unit_bangunan' => ['validators' => ['digit']],
                'nilai_bangunan' => ['validators' => ['numeric']],
                'luas_bangunan' => ['validators' => ['numeric']],
                'unit_kendaraan' => ['validators' => ['required', 'digit']],
                'nilai_kendaraan' => ['validators' => ['required', 'numeric']],
                'unit_tanah_bangunan' => ['validators' => ['required', 'digit']],
                'luas_tanah_bangunan' => ['validators' => ['required', 'numeric']],
                'nilai_tanah_bangunan' => ['validators' => ['required', 'numeric']],
                'attachment_uuid' => ['validators' => []],
            ]
        );

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        // $currData = AsetPenertiban::findFirst(
        //     [
        //         "tahun = :tahun: and periode = :periode: and instansi_id = :instansi_id: and deleted_at is null",
        //         "bind" => [
        //             "tahun" => $validatedRequest['tahun'],
        //             "periode" => $validatedRequest['periode'],
        //             "instansi_id" => $instansi->id
        //         ]
        //     ]
        // );

        // if (!empty($currData)) {
        //     throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data Aset Penertiban sudah terdaftar untuk tahun dan periode ini. Silahkan melakukan edit.');
        // }

        $newData = new AsetPenertiban();
        $newData->tahun = $validatedRequest['tahun'];
        $newData->periode = $validatedRequest['periode'];
        $newData->instansi_id = $instansi->id;
        $newData->uraian = $validatedRequest['uraian'];
        $newData->unit_tanah = $validatedRequest['unit_tanah'];
        $newData->luas_tanah = $validatedRequest['luas_tanah'];
        $newData->nilai_tanah = $validatedRequest['nilai_tanah'];
        $newData->unit_bangunan = $validatedRequest['unit_bangunan'];
        $newData->luas_bangunan = $validatedRequest['luas_bangunan'];
        $newData->nilai_bangunan = $validatedRequest['nilai_bangunan'];
        $newData->unit_kendaraan = $validatedRequest['unit_kendaraan'];
        $newData->nilai_kendaraan = $validatedRequest['nilai_kendaraan'];
        $newData->nilai_tanah_bangunan = $validatedRequest['nilai_tanah_bangunan'];
        $newData->luas_tanah_bangunan = $validatedRequest['luas_tanah_bangunan'];
        $newData->unit_tanah_bangunan = $validatedRequest['unit_tanah_bangunan'];
        $newData->nilai_total = $validatedRequest['nilai_tanah'] + $validatedRequest['nilai_bangunan'] + $validatedRequest['nilai_kendaraan'] + $validatedRequest['nilai_tanah_bangunan'];
        $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
        $res = $newData->create();
        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_PENERTIBAN, $newData->id, 0);

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_aset_penertiban" => $newData->id
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Penertiban Error");
        }
    }

    public function editAsetPenertiban($id)
    {
        $this->checkSubmitScopes();
        $params = $this->request->getJsonRawBody(true);
        $params['unit_bangunan'] = array_key_exists('unit_bangunan', $params) ? $params['unit_bangunan'] : 0;
        $params['nilai_bangunan'] = array_key_exists('nilai_bangunan', $params) ? $params['nilai_bangunan'] : 0;
        $params['luas_bangunan'] = array_key_exists('luas_bangunan', $params) ? $params['luas_bangunan'] : 0;
        $params['attachment_uuid'] = array_key_exists('attachment_uuid', $params) ? $params['attachment_uuid'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'uraian' => ['validators' => ['required']],
                'unit_tanah' => ['validators' => ['required', 'digit']],
                'nilai_tanah' => ['validators' => ['required', 'numeric']],
                'luas_tanah' => ['validators' => ['required', 'numeric']],
                'unit_bangunan' => ['validators' => ['digit']],
                'nilai_bangunan' => ['validators' => ['numeric']],
                'luas_bangunan' => ['validators' => ['numeric']],
                'unit_kendaraan' => ['validators' => ['required', 'digit']],
                'nilai_kendaraan' => ['validators' => ['required', 'numeric']],
                'unit_tanah_bangunan' => ['validators' => ['required', 'digit']],
                'luas_tanah_bangunan' => ['validators' => ['required', 'numeric']],
                'nilai_tanah_bangunan' => ['validators' => ['required', 'numeric']],
                'attachment_uuid' => ['validators' => []],
            ]
        );

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetPenertiban::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 ($data->instansi_id != $instansi->id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
        }

        $data->uraian = $validatedRequest['uraian'];
        $data->unit_tanah = $validatedRequest['unit_tanah'];
        $data->luas_tanah = $validatedRequest['luas_tanah'];
        $data->nilai_tanah = $validatedRequest['nilai_tanah'];
        $data->unit_bangunan = $validatedRequest['unit_bangunan'];
        $data->luas_bangunan = $validatedRequest['luas_bangunan'];
        $data->nilai_bangunan = $validatedRequest['nilai_bangunan'];
        $data->unit_kendaraan = $validatedRequest['unit_kendaraan'];
        $data->nilai_kendaraan = $validatedRequest['nilai_kendaraan'];
        $data->nilai_tanah_bangunan = $validatedRequest['nilai_tanah_bangunan'];
        $data->luas_tanah_bangunan = $validatedRequest['luas_tanah_bangunan'];
        $data->unit_tanah_bangunan = $validatedRequest['unit_tanah_bangunan'];
        $data->nilai_total = $validatedRequest['nilai_tanah'] + $validatedRequest['nilai_bangunan'] + $validatedRequest['nilai_kendaraan'] + $validatedRequest['nilai_tanah_bangunan'];
        $data->updated_by = $this->shared->user->um_id;
        $data->updated_at = 'now();';
        $res = $data->save();
        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_PENERTIBAN, $data->id, 1);

            $data = AsetPenertiban::findFirst($data->id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $data
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Penertiban Error");
        }
    }

    public function deleteAsetPenertiban($id)
    {
        $this->checkSubmitScopes();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetPenertiban::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 (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            if ($data->instansi_id != $instansi->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
            }
        }

        $data->deleted_by = $this->shared->user->um_id;
        $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 Aset Penertiban Error");
        }
    }

    public function uploadAsetPenertiban()
    {
        $this->checkSubmitScopes();

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

        $successCount = 0;
        $fails = [];
        $values = [];
        $errors = [];

        if (!empty($document)) {
            #region Process Excel
            $excelService = new ExcelService($document, ExcelService::EXCEL_ASET_PENERTIBAN);
            $values = $excelService->RetrieveExcelData();
            $errors = $excelService->GetErrorMessage();
            if (count($errors) > 0) {
                return new ObjectResponse(
                    [
                        "success" => false,
                        "data" => [
                            "error_messages" => $errors
                        ],
                    ]
                );
            }
            #endregion

            if (empty($errors)) {
                $this->db->begin();
                $rowCount = 1;
                foreach ($values as $data) {
                    //Validate data
                    $instansi = Instansi::findFirst(
                        [
                            "nama = :nama: and deleted_at is null",
                            "bind" => [
                                "nama" => $data['column_1']
                            ]
                        ]
                    );

                    if (empty($instansi)) {
                        $errors[] = 'Instansi [' . $data['column_1'] . '] pada Data [' . $rowCount . '] Tidak Ditemukan';
                        $rowCount++;
                        continue;
                    }

                    //Populate and Insert Data
                    $newData = new AsetPenertiban();
                    $newData->instansi_id = $instansi->id;
                    $newData->tahun = $data['column_2'] ?: 0;
                    $newData->periode = $data['column_3'] ?: '';
                    $newData->uraian = $data['column_5'] ?: '';
                    $newData->unit_tanah = $data['column_6'] ?: 0;
                    $newData->luas_tanah = $data['column_7'] ?: 0;
                    $newData->nilai_tanah = $data['column_8'] ?: 0;
                    $newData->unit_bangunan = $data['column_11'] ?: 0;
                    $newData->luas_bangunan = $data['column_12'] ?: 0;
                    $newData->nilai_bangunan = $data['column_13'] ?: 0;
                    $newData->unit_kendaraan = $data['column_9'] ?: 0;
                    $newData->nilai_kendaraan = $data['column_10'] ?: 0;
                    $newData->nilai_total = $newData->nilai_tanah + $newData->nilai_bangunan + $newData->nilai_kendaraan;
                    $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
                    $res = $newData->create();
                    if (!$res) {
                        $errors[] = 'Save Aset Penertiban No. [' . $rowCount . '] Error';
                    }
                    $rowCount++;
                }

                if(count($errors) > 0) {
                    $this->db->rollback();
                } else {
                    $this->db->commit();
                }
            }
        } else {
            $errors[] = 'Document is empty';
        }

        return new ObjectResponse(
            [
                "success" => count($errors) > 0 ? false : true,
                "data" => [
                    "messages" => count($errors) > 0 ? $errors : ['[' . count($values) . '] Data Successfully Inserted']
                ],
            ]
        );
    }

    public function downloadAsetPenertiban()
    {
        $this->checkViewScopes();

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

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

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

        if (strtolower($instansi->nama) != DiskusiBansos::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and instansi_id = :instansi_id:';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];
        }

        $asetList = AsetPenertiban::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc"
            ]
        );

        $result = [];
        foreach ($asetList as $aset) {
            $result[] = $aset->toArraySimple();
        }

        $result = CommonTools::arrayMultiSort($result, 'tahun', SORT_DESC, 'period_order', SORT_ASC);

        #region Generate Excel File

        $spreadsheet = $this->generateExcelLaporanAset($result, ExcelService::EXCEL_ASET_PENERTIBAN);
        $todayLabel = DateHelper::formatToday();

        $writer = new Xlsx($spreadsheet);
        $filename = "Export Aset Penertiban - $todayLabel.xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');

        #endregion
    }
    #endregion

    #region Aset Penertiban Sarpras
    public function listAsetSarpras()
    {
        $this->checkViewScopes();

        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

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

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

        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and instansi_id = :instansi_id:';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];
        }

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

        $asetList = AsetPenertibanSarpras::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc",
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );

        $result = [];
        foreach ($asetList as $aset) {
            $result[] = $aset->toArraySimple();
        }

        $result = CommonTools::arrayMultiSort($result, 'tahun', SORT_DESC, 'period_order', SORT_ASC);

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

    public function detailAsetSarpras($id)
    {
        $this->checkViewScopes();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetPenertibanSarpras::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 (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            if ($data->instansi_id != $instansi->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
            }
        }

        $result = $data->toArrayDetail();

        return new CollectionPaginatedResponse([$result]);
    }

    public function submitAsetSarpras()
    {
        $this->checkSubmitScopes();

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

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'periode' => ['validators' => ['required']],
                'jumlah_lokasi' => ['validators' => ['required', 'digit']],
                'luas_unit' => ['validators' => ['required', 'numeric']],
                'nilai' => ['validators' => ['required', 'numeric']],
                'keterangan' => ['validators' => []],
                'attachment_uuid' => ['validators' => []],
            ]
        );

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        // $currData = AsetPenertibanSarpras::findFirst(
        //     [
        //         "tahun = :tahun: and periode = :periode: and instansi_id = :instansi_id: and deleted_at is null",
        //         "bind" => [
        //             "tahun" => $validatedRequest['tahun'],
        //             "periode" => $validatedRequest['periode'],
        //             "instansi_id" => $instansi->id
        //         ]
        //     ]
        // );

        if (!empty($currData)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data Aset Penertiban sudah terdaftar untuk tahun dan periode ini. Silahkan melakukan edit.');
        }

        $newData = new AsetPenertibanSarpras();
        $newData->tahun = $validatedRequest['tahun'];
        $newData->periode = $validatedRequest['periode'];
        $newData->instansi_id = $instansi->id;
        $newData->jumlah_lokasi = $validatedRequest['jumlah_lokasi'];
        $newData->luas_unit = $validatedRequest['luas_unit'];
        $newData->nilai = $validatedRequest['nilai'];
        $newData->keterangan = $validatedRequest['keterangan'];
        $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
        $res = $newData->create();
        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_PENERTIBAN_SARPRAS, $newData->id, 0);

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_aset_penertiban_sarpras" => $newData->id
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Penertiban Sarpras Error");
        }
    }

    public function editAsetSarpras($id)
    {
        $this->checkSubmitScopes();


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

        $validatedRequest = $this->validate(
            $params,
            [
                'jumlah_lokasi' => ['validators' => ['required', 'digit']],
                'luas_unit' => ['validators' => ['required', 'numeric']],
                'nilai' => ['validators' => ['required', 'numeric']],
                'keterangan' => ['validators' => []],
                'attachment_uuid' => ['validators' => []],
            ]
        );

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetPenertibanSarpras::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 ($data->instansi_id != $instansi->id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
        }


        $data->jumlah_lokasi = $validatedRequest['jumlah_lokasi'];
        $data->luas_unit = $validatedRequest['luas_unit'];
        $data->nilai = $validatedRequest['nilai'];
        $data->keterangan = $validatedRequest['keterangan'];
        $data->updated_by = $this->shared->user->um_id;
        $data->updated_at = 'now();';
        $res = $data->save();
        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_ASET_PENERTIBAN_SARPRAS, $data->id, 1);

            $data = AsetPenertibanSarpras::findFirst($data->id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $data
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Aset Penertiban Sarpras Error");
        }
    }

    public function deleteAsetSarpras($id)
    {
        $this->checkSubmitScopes();

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $data = AsetPenertibanSarpras::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 (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            if ($data->instansi_id != $instansi->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki akses untuk data ini.');
            }
        }

        $data->deleted_by = $this->shared->user->um_id;
        $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 Aset Penertiban Sarpras Error");
        }
    }

    public function uploadAsetSarpras()
    {
        $this->checkSubmitScopes();

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

        $successCount = 0;
        $fails = [];
        $values = [];
        $errors = [];

        if (!empty($document)) {
            #region Process Excel
            $excelService = new ExcelService($document, ExcelService::EXCEL_ASET_PENERTIBAN_SARPRAS);
            $values = $excelService->RetrieveExcelData();
            $errors = $excelService->GetErrorMessage();
            if (count($errors) > 0) {
                return new ObjectResponse(
                    [
                        "success" => false,
                        "data" => [
                            "error_messages" => $errors
                        ],
                    ]
                );
            }
            #endregion

            if (empty($errors)) {
                $this->db->begin();
                $rowCount = 1;
                foreach ($values as $data) {
                    //Validate data
                    $instansi = Instansi::findFirst(
                        [
                            "nama = :nama: and deleted_at is null",
                            "bind" => [
                                "nama" => $data['column_1']
                            ]
                        ]
                    );

                    if (empty($instansi)) {
                        $errors[] = 'Instansi [' . $data['column_1'] . '] pada Data [' . $rowCount . '] Tidak Ditemukan';
                        $rowCount++;
                        continue;
                    }

                    //Populate and Insert Data
                    $newData = new AsetPenertibanSarpras();

                    $newData->instansi_id = $instansi->id;
                    $newData->tahun = $data['column_2'] ?: 0;
                    $newData->periode = $data['column_3'] ?: '';
                    $newData->keterangan = $data['column_5'] ?: '';
                    $newData->jumlah_lokasi = $data['column_6'] ?: 0;
                    $newData->luas_unit = $data['column_7'] ?: 0;
                    $newData->nilai = $data['column_8'] ?: 0;
                    $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
                    $res = $newData->create();
                    if (!$res) {
                        $errors[] = 'Save Aset Penertiban Sarpras No. [' . $rowCount . '] Error';
                    }
                    $rowCount++;
                }

                if(count($errors) > 0) {
                    $this->db->rollback();
                } else {
                    $this->db->commit();
                }
            }
        } else {
            $errors[] = 'Document is empty';
        }

        return new ObjectResponse(
            [
                "success" => count($errors) > 0 ? false : true,
                "data" => [
                    "messages" => count($errors) > 0 ? $errors : ['[' . count($values) . '] Data Successfully Inserted']
                ],
            ]
        );
    }

    public function downloadAsetSarpras()
    {
        $this->checkViewScopes();

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

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

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

        if (strtolower($instansi->nama) != DiskusiBansos::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and instansi_id = :instansi_id:';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];
        }

        $asetList = AsetPenertibanSarpras::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc"
            ]
        );

        $result = [];
        foreach ($asetList as $aset) {
            $result[] = $aset->toArraySimple();
        }

        $result = CommonTools::arrayMultiSort($result, 'tahun', SORT_DESC, 'period_order', SORT_ASC);

        #region Generate Excel File

        $spreadsheet = $this->generateExcelLaporanAset($result, ExcelService::EXCEL_ASET_PENERTIBAN_SARPRAS);
        $todayLabel = DateHelper::formatToday();

        $writer = new Xlsx($spreadsheet);
        $filename = "Export Aset Penertiban Prasarana, Sarana, dan Utilitas - $todayLabel.xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
        #endregion
    }
    #endregion

    #region Dashboard
    #region Admin
    public function dashboardSertifikasi()
    {
        $this->checkOneOfScopes(['bpkad.view', 'korsupgah.admin', 'pad.answer', 'aset.answer']);
        $params = $this->request->get();

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

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $strWhere = 'AsetSertifikasi.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        } else if ($validatedRequest['instansi_id'] == $instansi->id) { // jika instansi KPK maka tampilkan data nasional
            $validatedRequest['instansi_id'] = null;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetSertifikasi::query()
        ->columns("cast(:nama_instansi: as text) as nama_instansi, 
                   CAST(:tahun: as int) as tahun, 
                   COALESCE(SUM(non_serti_bidang + serti_bidang), 0) as jumlah_bidang, 
                   COALESCE(SUM(non_serti_luas + serti_luas), 0) as jumlah_luas, 
                   COALESCE(SUM(nilai), 0) as jumlah_nilai")
        ->where($strWhere)
        ->bind($bind);


        $result = $query->execute()->toArray();
        //Get Sertifikasi Detail
        $arrPeriode = $this->getActivePeriod($validatedRequest['tahun']);

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetSertifikasi.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }
            $queryDetail = AsetSertifikasi::query()
                ->columns('COALESCE(SUM(non_serti_jumlah), 0) as jumlah_belum_bersertifikat, 
                           COALESCE(SUM(serti_jumlah), 0) as jumlah_bersertifikat,
                           COALESCE(SUM(non_serti_luas), 0) as luas_belum_bersertifikat,
                           COALESCE(SUM(serti_luas), 0) as luas_bersertifikat,
                           COALESCE(SUM(nilai), 0) as nilai,
                           CAST(keterangan as text) as keterangan,
                           COALESCE(SUM(jumlah_k1),0) as jumlah_k1,
                           COALESCE(SUM(jumlah_k2), 0) as jumlah_k2,
                           COALESCE(SUM(jumlah_k3), 0) as jumlah_k3,
                           COALESCE(SUM(jumlah_k4), 0) as jumlah_k4,
                           COALESCE(SUM(jumlah_k4), 0) as jumlah_k4'
                           )
                ->where($strWhereDetail)
                ->bind($bindDetail)
                ->groupBy("keterangan");
            $result[0][$periode] = $queryDetail->execute()->toArray();
        }
        return new SimpleResponse($result);
    }

    public function dashboardPenertiban()
    {
        $this->checkOneOfScopes(['bpkad.view', 'korsupgah.admin', 'pad.answer', 'aset.answer']);

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $strWhere = 'AsetPenertiban.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        } else if ($validatedRequest['instansi_id'] == $instansi->id) { // jika instansi KPK maka tampilkan data nasional
            $validatedRequest['instansi_id'] = null;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        //parent data
        $query = AsetPenertiban::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, CAST(:tahun: as int) as tahun, 
                COALESCE(SUM(unit_tanah + unit_bangunan + unit_kendaraan), 0) as jumlah_aset, 
                COALESCE(SUM(unit_tanah + unit_bangunan), 0) as jumlah_tanahbangunan,
                COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan, 
                COALESCE(SUM(nilai_total), 0) as jumlah_nilai
                ")
            ->where($strWhere)
            ->bind($bind);

        $result = $query->execute()->toArray();

        //Get Penertiban Detail
        $arrPeriode = $this->getActivePeriod($validatedRequest['tahun']);

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetPenertiban.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }
            // child data.
            $queryDetail = AsetPenertiban::query()
                ->columns('
                            COALESCE(SUM(unit_tanah), 0) as jumlah_tanah,
                            COALESCE(SUM(luas_tanah), 0) as luas_tanah,
                            COALESCE(SUM(nilai_tanah), 0) as nilai_tanah,
                            COALESCE(SUM(unit_bangunan), 0) as jumlah_bangunan,
                            COALESCE(SUM(luas_bangunan), 0) as luas_bangunan,
                            COALESCE(SUM(nilai_bangunan), 0) as nilai_bangunan,
                            COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan,
                            COALESCE(SUM(nilai_kendaraan), 0) as nilai_kendaraan,
                            COALESCE(SUM(unit_tanah_bangunan), 0) as jumlah_tanahbangunan,
                            COALESCE(CAST(luas_tanah_bangunan as text), "0") as luas_tanah_bangunan,
                            COALESCE(SUM(nilai_tanah_bangunan), 0) as nilai_tanah_bangunan,
                            COALESCE(SUM(nilai_total), 0) as nilai_total,
                            CAST(uraian as text) as uraian'
                         )
                ->where($strWhereDetail)
                ->groupBy('uraian, luas_tanah_bangunan')
                ->bind($bindDetail);
            $result[0][$periode] = $queryDetail->execute()->toArray();
        }

        return new SimpleResponse($result);
    }

    public function dashboardSarpras()
    {
        $this->checkOneOfScopes(['bpkad.view', 'korsupgah.admin', 'pad.answer', 'aset.answer']);

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        if (is_null($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($this->getIdInstansi());

        $strWhere = 'AsetPenertibanSarpras.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            $validatedRequest['instansi_id'] = $instansi->id;
        } else if ($validatedRequest['instansi_id'] == $instansi->id) { // jika instansi KPK maka tampilkan data nasional
            $validatedRequest['instansi_id'] = null;
        }

        if (!is_null($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetPenertibanSarpras::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, cast(:tahun: as int) as tahun, 
                COALESCE(SUM(jumlah_lokasi), 0) as jumlah_lokasi, 
                COALESCE(SUM(nilai), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $result = $query->execute()->toArray();

        return new SimpleResponse($result);
    }
    #endregion

    #region Public
    public function summarySertifikasi()
    {
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;
        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'instansi_id' => ['validators' => ['digit']]
            ]
        );

        $strWhere = 'AsetSertifikasi.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetSertifikasi::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, CAST(:tahun: as int) as tahun, 
                COALESCE(SUM(non_serti_bidang + serti_bidang), 0) as jumlah_bidang, 
                COALESCE(SUM(non_serti_luas + serti_luas), 0) as jumlah_luas, 
                COALESCE(SUM(nilai), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $result = $query->execute()->toArray();

        //Get Sertifikasi Detail
        $arrPeriode = $this->getActivePeriod($validatedRequest['tahun']);

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetSertifikasi.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }

            $queryDetail = AsetSertifikasi::query()
                ->columns('COALESCE(SUM(non_serti_jumlah), 0) as jumlah_belum_bersertifikat, COALESCE(SUM(serti_jumlah), 0) as jumlah_bersertifikat')
                ->where($strWhereDetail)
                ->bind($bindDetail);

            $result[0][$periode] = $queryDetail->execute()->toArray();
        }

        return new SimpleResponse($result);
    }

    public function summaryPenertiban()
    {
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        $strWhere = 'AsetPenertiban.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetPenertiban::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, CAST(:tahun: as int) as tahun, 
                COALESCE(SUM(unit_tanah + unit_bangunan + unit_kendaraan), 0) as jumlah_aset, 
                COALESCE(SUM(unit_tanah + unit_bangunan), 0) as jumlah_tanahbangunan,
                COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan, 
                COALESCE(SUM(nilai_total), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $result = $query->execute()->toArray();

        //Get Penertiban Detail
        $arrPeriode = $this->getActivePeriod($validatedRequest['tahun']);

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetPenertiban.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }

            $queryDetail = AsetPenertiban::query()
                ->columns('COALESCE(SUM(unit_tanah + unit_bangunan), 0) as jumlah_tanahbangunan, COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan')
                ->where($strWhereDetail)
                ->bind($bindDetail);

            $result[0][$periode] = $queryDetail->execute()->toArray();
        }

        return new SimpleResponse($result);
    }

    public function summarySarpras()
    {
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        $strWhere = 'AsetPenertibanSarpras.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetPenertibanSarpras::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, cast(:tahun: as int) as tahun, 
                COALESCE(SUM(jumlah_lokasi), 0) as jumlah_lokasi, 
                COALESCE(SUM(nilai), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $result = $query->execute()->toArray();

        return new SimpleResponse($result);
    }

    public function summaryDashboard() {
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;

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

        $arrPeriode = $this->getActivePeriod($validatedRequest['tahun']);

        $strWhere = 'AsetSertifikasi.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetSertifikasi::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, CAST(:tahun: as int) as tahun, 
                COALESCE(SUM(non_serti_bidang + serti_bidang), 0) as jumlah_bidang, 
                COALESCE(SUM(non_serti_luas + serti_luas), 0) as jumlah_luas, 
                COALESCE(SUM(nilai), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $sertifikasi = $query->execute()->toArray();

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetSertifikasi.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }

            $queryDetail = AsetSertifikasi::query()
                ->columns('COALESCE(SUM(non_serti_jumlah), 0) as jumlah_belum_bersertifikat, COALESCE(SUM(serti_jumlah), 0) as jumlah_bersertifikat')
                ->where($strWhereDetail)
                ->bind($bindDetail);

            $sertifikasi[0][$periode] = $queryDetail->execute()->toArray();
        }

        $strWhere = 'AsetPenertiban.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetPenertiban::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, CAST(:tahun: as int) as tahun, 
                COALESCE(SUM(unit_tanah + unit_bangunan + unit_kendaraan), 0) as jumlah_aset, 
                COALESCE(SUM(unit_tanah + unit_bangunan), 0) as jumlah_tanahbangunan,
                COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan, 
                COALESCE(SUM(nilai_total), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $penertiban = $query->execute()->toArray();

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetPenertiban.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }

            $queryDetail = AsetPenertiban::query()
                ->columns('COALESCE(SUM(unit_tanah + unit_bangunan), 0) as jumlah_tanahbangunan, COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan')
                ->where($strWhereDetail)
                ->bind($bindDetail);

            $penertiban[0][$periode] = $queryDetail->execute()->toArray();
        }

        $strWhere = 'AsetPenertibanSarpras.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetPenertibanSarpras::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, cast(:tahun: as int) as tahun, 
                COALESCE(SUM(jumlah_lokasi), 0) as jumlah_lokasi, 
                COALESCE(SUM(nilai), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $sarpras = $query->execute()->toArray();

        $result = [
            "sertifikasi" => $sertifikasi,
            "penertiban" => $penertiban,
            "sarpras" => $sarpras
        ];

        return new SimpleResponse($result);
    }

    public function summaryDetailedDashboard() {
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;
        $params['provinsi_id'] = array_key_exists('provinsi_id', $params) ? $params['provinsi_id'] : null;
        $params['kabupaten_id'] = array_key_exists('kabupaten_id', $params) ? $params['kabupaten_id'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'instansi_id' => ['validators' => ['digit']],
                'provinsi_id' => ['validators' => ['digit']],
                'kabupaten_id' => ['validators' => ['digit']]
            ]
        );
        if (!empty($params['kabupaten_id']) || !empty($params['provinsi_id'])) {
            $strWhere = 'deleted_at is null';
            $bindWilayah = !empty($params['kabupaten_id']) ? $params['kabupaten_id'] : $params['provinsi_id'];
            $strWhere .= !empty($params['kabupaten_id']) ? ' AND id_kota_kabupaten = :idWilayah: AND id_tipe = 2' : ' AND id_provinsi = :idWilayah: AND id_kota_kabupaten IS NULL AND id_tipe = 1';

            $instansi = Instansi::query()
            ->columns("id as instansi_id")
            ->where($strWhere)
            ->bind(["idWilayah" => $bindWilayah])
            ->execute();

            $validatedRequest['instansi_id'] = $instansi[0]["instansi_id"];
        }

        $arrPeriode = $this->getActivePeriod($validatedRequest['tahun']);

        $strWhere = 'AsetSertifikasi.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetSertifikasi::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, CAST(:tahun: as int) as tahun, 
                COALESCE(SUM(serti_jumlah + non_serti_jumlah), 0) as jumlah_total, 
                COALESCE(SUM(serti_luas + non_serti_luas), 0) as jumlah_total_luas,
                COALESCE(SUM(serti_jumlah), 0) as jumlah_serti, 
                COALESCE(SUM(serti_bidang), 0) as jumlah_serti_bidang, 
                COALESCE(SUM(serti_luas), 0) as jumlah_serti_luas,
                COALESCE(SUM(nilai), 0) as jumlah_serti_nilai,
                COALESCE(SUM(non_serti_jumlah), 0) as jumlah_nonserti, 
                COALESCE(SUM(non_serti_bidang), 0) as jumlah_nonserti_bidang, 
                COALESCE(SUM(non_serti_luas), 0) as jumlah_nonserti_luas,
                COALESCE(SUM(jumlah_k1), 0) as jumlah_k1,
                COALESCE(SUM(jumlah_k2), 0) as jumlah_k2,
                COALESCE(SUM(jumlah_k3), 0) as jumlah_k3,
                COALESCE(SUM(jumlah_k4), 0) as jumlah_k4,
                COALESCE(periode, 'N/A') as last_periode")
            ->where($strWhere)
            ->groupBy('periode')
            ->orderBy('periode DESC')
            ->limit(1)
            ->bind($bind);

        $sertifikasi = $query->execute()->toArray();

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetSertifikasi.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }

            $queryDetail = AsetSertifikasi::query()
                ->columns('COALESCE(SUM(serti_jumlah + non_serti_jumlah), 0) as jumlah_total,
                COALESCE(SUM(non_serti_jumlah), 0) as jumlah_belum_bersertifikat,
                COALESCE(SUM(serti_jumlah), 0) as jumlah_bersertifikat, 
                COALESCE(SUM(nilai), 0) as jumlah_serti_nilai, 
                COALESCE(SUM(jumlah_k1), 0) as jumlah_k1,
                COALESCE(SUM(jumlah_k2), 0) as jumlah_k2,
                COALESCE(SUM(jumlah_k3), 0) as jumlah_k3,
                COALESCE(SUM(jumlah_k4), 0) as jumlah_k4')
                ->where($strWhereDetail)
                ->bind($bindDetail);

            $sertifikasi[0]["progress"][$periode] = $queryDetail->execute()->toArray();
        }

        $strWhere = 'AsetPenertiban.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetPenertiban::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, CAST(:tahun: as int) as tahun, 
                COALESCE(SUM(unit_tanah + unit_bangunan + COALESCE(unit_tanah_bangunan, 0)), 0) as jumlah_total,
                COALESCE(SUM(luas_tanah + luas_bangunan + COALESCE(CAST(luas_tanah_bangunan AS int), 0)), 0) as jumlah_total_luas,
                COALESCE(SUM(nilai_tanah + nilai_bangunan + COALESCE(nilai_tanah_bangunan, 0)), 0) as jumlah_total_nilai,
                COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan,
                COALESCE(SUM(nilai_kendaraan), 0) as jumlah_nilai_kendaraan")
            ->where($strWhere)
            ->bind($bind);

        $penertiban = $query->execute()->toArray();

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetPenertiban.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }

            $queryDetail = AsetPenertiban::query()
                ->columns('COALESCE(SUM(unit_tanah + unit_bangunan + COALESCE(unit_tanah_bangunan, 0)), 0) as jumlah_total,
                COALESCE(SUM(luas_tanah + luas_bangunan + COALESCE(CAST(luas_tanah_bangunan AS int), 0)), 0) as jumlah_total_luas,
                COALESCE(SUM(nilai_tanah + nilai_bangunan + COALESCE(nilai_tanah_bangunan, 0)), 0) as jumlah_total_nilai,
                COALESCE(SUM(unit_kendaraan), 0) as jumlah_kendaraan,
                COALESCE(SUM(nilai_kendaraan), 0) as jumlah_nilai_kendaraan')
                ->where($strWhereDetail)
                ->bind($bindDetail);

            $penertiban[0]["progress"][$periode] = $queryDetail->execute()->toArray();
        }

        $strWhere = 'AsetPenertibanSarpras.deleted_at is null and (tahun = :tahun: or tahun is null)';
        $bind['tahun'] = $validatedRequest['tahun'];

        if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
            $strWhere .= ' and (instansi_id = :instansi_id: or instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

            $dataInstansi = Instansi::findFirst($validatedRequest['instansi_id']);
            $bind['nama_instansi'] = $dataInstansi->nama;
        } else {
            $bind['nama_instansi'] = 'Nasional';
        }

        $query = AsetPenertibanSarpras::query()
            ->columns("cast(:nama_instansi: as text) as nama_instansi, cast(:tahun: as int) as tahun, 
                COALESCE(SUM(jumlah_lokasi), 0) as jumlah_lokasi,
                COALESCE(SUM(luas_unit), 0) as jumlah_luas_unit,
                COALESCE(SUM(nilai), 0) as jumlah_nilai")
            ->where($strWhere)
            ->bind($bind);

        $sarpras = $query->execute()->toArray();

        foreach ($arrPeriode as $periode) {
            $strWhereDetail = 'AsetPenertibanSarpras.deleted_at is null and tahun = :tahun: and lower(periode) = lower(:periode:)';
            $bindDetail['tahun'] = $validatedRequest['tahun'];
            $bindDetail['periode'] = $periode;

            if (!is_null($validatedRequest['instansi_id']) && !empty($validatedRequest['instansi_id'])) {
                $strWhereDetail .= ' and instansi_id = :instansi_id:';
                $bindDetail['instansi_id'] = $validatedRequest['instansi_id'];
            }

            $queryDetail = AsetPenertibanSarpras::query()
                ->columns('COALESCE(SUM(jumlah_lokasi), 0) as jumlah_lokasi, 
                    COALESCE(SUM(luas_unit), 0) as jumlah_luas_unit,
                    COALESCE(SUM(nilai), 0) as jumlah_nilai')
                ->where($strWhereDetail)
                ->bind($bindDetail);

            $sarpras[0]["progress"][$periode] = $queryDetail->execute()->toArray();
        }

        $result = [
            "sertifikasi" => $sertifikasi,
            "penertiban" => $penertiban,
            "sarpras" => $sarpras
        ];

        return new SimpleResponse($result);
    }


    #endregion
    #endregion
}