<?php

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

class PajakController extends BaseController
{
    #region Constant
    const TIPE_PAJAK_CAPAIAN = 1;
    const TIPE_PAJAK_PENAGIHAN = 2;
    const TIPE_PAJAK_TUNGGAKAN = 3;
    #endregion

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

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

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

        $result = DateHelper::listAvailableMonth($validatedRequest['tahun']);

        return new SimpleResponse($result);
    }

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

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

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

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

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

        if (strtolower($instansi->nama) != Instansi::KPK_NAME) {
            $arrTipeInstansi[] = $instansi->id_tipe;
        } else {
            $arrTipeInstansi = [1, 2];
        }

        $taxTypes = [
            'PKB' => 1,
            'BBNKB' => 2,
            'PBB_KB' => 3,
            'PAP' => 4,
            'PAJAK_ROKOK' => 5,
            'PBB_P2' => 6,
            'BPHTB' => 7,
            'PAJAK_HOTEL' => 8,
            'PAJAK_RESTORAN' => 9,
            'PAJAK_HIBURAN' => 10,
            'PAJAK_PARKIR' => 11,
            'PAJAK_REKLAME' => 12,
            'PAJAK_PENERANGAN_JALAN' => 13,
            'PAJAK_AIR_BAWAH_TANAH' => 14,
            'PAJAK_MINERAL_BUKAN_LOGAM' => 15,
            'PAJAK_SARANG_BURUNG_WALET' => 16,
            'PAB' => 17,
            'MBLB' => 18,
            'PBJT' => 19,
            'PAJAK_AIR_TANAH' => 20,
            'PKB_kab' => 21,
            'BBNKB_kab' => 22
        ];

        $taxGroups = [
            'before_2024' => [
                $taxTypes['PKB'],
                $taxTypes['BBNKB'],
                $taxTypes['PBB_KB'],
                $taxTypes['PAP'],
                $taxTypes['PAJAK_ROKOK'],
                $taxTypes['PBB_P2'],
                $taxTypes['BPHTB'],
                $taxTypes['PAJAK_HOTEL'],
                $taxTypes['PAJAK_RESTORAN'],
                $taxTypes['PAJAK_HIBURAN'],
                $taxTypes['PAJAK_PARKIR'],
                $taxTypes['PAJAK_REKLAME'],
                $taxTypes['PAJAK_PENERANGAN_JALAN'],
                $taxTypes['PAJAK_AIR_BAWAH_TANAH'],
                $taxTypes['PAJAK_MINERAL_BUKAN_LOGAM'],
                $taxTypes['PAJAK_SARANG_BURUNG_WALET'],
            ],
            'after_2024' => [
                $taxTypes['PKB'],
                $taxTypes['BBNKB'],
                $taxTypes['PAB'],
                $taxTypes['PBB_KB'],
                $taxTypes['PAP'],
                $taxTypes['PAJAK_ROKOK'],
                $taxTypes['MBLB'],
                $taxTypes['PBB_P2'],
                $taxTypes['BPHTB'],
                $taxTypes['PAJAK_REKLAME'],
                $taxTypes['PAJAK_MINERAL_BUKAN_LOGAM'],
                $taxTypes['PAJAK_SARANG_BURUNG_WALET'],
                $taxTypes['PBJT'],
                $taxTypes['PAJAK_AIR_TANAH'],
                $taxTypes['PKB_kab'],
                $taxTypes['BBNKB_kab']
            ]
        ];

        $validTaxIds = $validatedRequest['tahun'] >= 2024
        ? $taxGroups['after_2024']
        : $taxGroups['before_2024'];


        $result = MasterJenisPajak::find(
            [
                'tipe_instansi in ({tipe_instansi:array}) AND id in ({validTaxIds:array}) AND deleted_at is null',
                "bind" => [
                    "tipe_instansi" => $arrTipeInstansi,
                    "validTaxIds" => $validTaxIds
                ]
            ]
        );

        return new SimpleResponse($result);
    }

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

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

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

        /** @var MinioService $minioService */
        $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,
                'pajak-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,
                'pajak-attachment' . DIRECTORY_SEPARATOR
            );
        }
        return new CollectionPaginatedResponse($result);
    }

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

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

        $directory = 'pajak-attachment' . DIRECTORY_SEPARATOR;

        if (!empty($attachment)) {
            if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                /** @var MinioService $minioService */
                $minioService = $this->minio;
                $filename = $attachment->filename;
                $filekey = 'diskusi_attachment' . DIRECTORY_SEPARATOR . 'pajak-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) {
                $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 deleteAllPajak($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_PAJAK_CAPAIAN) {
            $listData = PajakCapaian::find();
        } elseif ($type == self::TIPE_PAJAK_PENAGIHAN) {
            $listData = PajakPenagihan::find();
        } elseif ($type == self::TIPE_PAJAK_TUNGGAKAN) {
            $listData = PajakTunggakan::find();
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Silahkan Pilih Tipe Pajak 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', 'pad.answer']);
    }

    private function checkSubmitScopes() {
        $this->checkOneOfScopes(['bpkad.submit', 'pad.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, 'pajak-attachment');
    }

    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 generateExcelLaporanPajak($items, $type)
    {


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

        $todayLabel = DateHelper::formatToday();

        if ($type == ExcelService::EXCEL_PAJAK_CAPAIAN) {
            #region Pajak Capaian
            $sheet->setCellValue("A1", 'Daftar Laporan Capaian Pajak');
            $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", 'Bulan');
            $sheet->setCellValue("D4", 'Jenis Pajak');
            $sheet->setCellValue("E4", 'Pajak Tahun Sebelumnya');
            $sheet->setCellValue("F4", 'Target Tahun Berjalan');
            $sheet->setCellValue("G4", 'Pajak Tahun Berjalan');
            $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']);
                $pajakTahunSebelum = 'Rp ' . number_format($item['pajak_tahun_sebelum'], 0, ",", ".");
                $targetTahunBerjalan = 'Rp ' . number_format($item['target_tahun_berjalan'], 0, ",", ".");
                $pajakTahunBerjalan = 'Rp ' . number_format($item['pajak_tahun_berjalan'], 0, ",", ".");

                $sheet->setCellValue("A$row", $item['nama_instansi']);
                $sheet->setCellValue("B$row", $item['tahun']);
                $sheet->setCellValue("C$row", $item['bulan']);
                $sheet->setCellValue("D$row", $item['jenis_pajak']);
                $sheet->setCellValueExplicit("E$row", $pajakTahunSebelum, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit("F$row", $targetTahunBerjalan, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit("G$row", $pajakTahunBerjalan, \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
        } elseif ($type == ExcelService::EXCEL_PAJAK_TUNGGAKAN) {
            #region Pajak Tunggakan
            $sheet->setCellValue("A1", 'Daftar Laporan Tunggakan Pajak');
            $sheet->setCellValue("A2", 'Diunduh Pada ' . $todayLabel);

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

            $sheet->mergeCells('A1:E1');
            $sheet->mergeCells('A2:E2');
            $sheet->mergeCells('A4:A5');
            $sheet->mergeCells('B4:B5');
            $sheet->mergeCells('C4:C5');

            foreach ($items as $itemTaxType) {
               $dataMasterPajak = MasterJenisPajak ::find([
                 "conditions"  =>  "tipe_instansi = :value:",
                 "bind" => [
                    "value" => $itemTaxType['instansi_tipe']
                 ]
                 ]);

               $countData= count($dataMasterPajak);

                 if($itemTaxType['instansi_tipe'] === 1 ){

                    $letterField = ['E', 'F', 'G', 'H', 'I'];

                    $sheet->mergeCells('E4:I4');
                    $sheet->mergeCells('J4:J5');
                    $sheet->mergeCells('K4:K5');

                    $sheet->setCellValue("A4", 'Nama Instansi');
                    $sheet->setCellValue("B4", 'Tahun');
                    $sheet->setCellValue("C4", 'Tunggakan Pajak');
                    $sheet->setCellValue("D4", 'Total Penagihan Pajak');
                    $sheet->setCellValue("E4", 'Jenis Pajak');


                    $sheet->setCellValue("J4", 'Dibuat Pada');
                    $sheet->setCellValue("K4", 'Dibuat Oleh');

                    $sheet->getStyle('A4:J4')
                    ->getAlignment()
                    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $row = 6;
                    $index = 1;

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

                        // Setting basic item data
                        $sheet->setCellValue("A$row", $item['nama_instansi']);
                        $sheet->setCellValue("B$row", $item['tahun']);
                        $sheet->setCellValueExplicit("C$row", $tunggakanPajak, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValueExplicit("D$row", $totalPenagihanPajak, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValue("J$row", $tanggalLaporan['datetime']);
                        $sheet->setCellValue("K$row", $item['created_by_name']);


                        foreach ($item['pajakTunggakanDetail'] as $detailIndex => $detail) {
                            $tunggakanPajakDetail = 'Rp ' . number_format($detail['tunggakan'], 0, ",", ".");

                            $sheet->setCellValue($letterField[$detailIndex] . $rowField, $detail['jenis_pajak']);
                            $sheet->setCellValue($letterField[$detailIndex] . $row, $tunggakanPajakDetail != null ? $tunggakanPajakDetail : 0, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        }

                        $row++;
                        $index++;
                    }

                    $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('J')->setAutoSize(true);
                    $sheet->getColumnDimension('K')->setAutoSize(true);

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

                    $sheet->getStyle('A4:K' . ($row - 1))->applyFromArray($styleArray);

                }

                else if($itemTaxType['instansi_tipe'] === 2){

                    $letterField = ['E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O'];
                    $sheet->setCellValue("A4", 'Nama Instansi');
                    $sheet->setCellValue("B4", 'Tahun');
                    $sheet->setCellValue("C4", 'Tunggakan Pajak');
                    $sheet->setCellValue("D4", 'Total Penagihan Pajak');
                    $sheet->setCellValue("E4", 'Jenis Pajak');
                    $sheet->setCellValue("P4", 'Dibuat Pada');
                    $sheet->setCellValue("Q4", 'Dibuat Oleh');

                    $sheet->mergeCells('D4:D5');
                    $sheet->mergeCells('E4:O4');
                    $sheet->mergeCells('P4:P5');
                    $sheet->mergeCells('Q4:Q5');

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

                    $rowField = 5;
                    $row = 6;
                    $index = 1;

                    foreach ($items as $item) {
                        $tanggalLaporan = $this->ConvertDateToLocalDate($item['created_at']);
                        $tunggakanPajak = 'Rp ' . number_format($item['tunggakan_pajak'], 0, ",", ".");
                        $totalPenagihanPajak = 'Rp ' . number_format($item['total_penagihan_pajak'], 0, ",", ".");
                        // Setting basic item data
                        $sheet->setCellValue("A$row", $item['nama_instansi']);
                        $sheet->setCellValue("B$row", $item['tahun']);
                        $sheet->setCellValueExplicit("C$row", $tunggakanPajak, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValueExplicit("D$row", $totalPenagihanPajak, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValue("P$row", $tanggalLaporan['datetime']);
                        $sheet->setCellValue("Q$row", $item['created_by_name']);


                        foreach ($item['pajakTunggakanDetail'] as $detailIndex => $detail) {
                            $tunggakanPajakDetail = 'Rp ' . number_format($detail['tunggakan'], 0, ",", ".");

                            $sheet->setCellValue($letterField[$detailIndex] . $rowField, $detail['jenis_pajak']);
                            $sheet->setCellValue($letterField[$detailIndex] . $row, $tunggakanPajakDetail !=null ? $tunggakanPajakDetail : 0, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        }

                        $row++;
                        $index++;
                    }

                    $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('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);

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

                    $sheet->getStyle('A4:Q' . ($row - 1))->applyFromArray($styleArray);


                }
            }
            #endregion
        } elseif ($type == ExcelService::EXCEL_PAJAK_PENAGIHAN) {
            #region Pajak Penagihan

            $sheet->setCellValue("A1", 'Daftar Laporan Penagihan Pajak');
            $sheet->setCellValue("A2", 'Diunduh Pada ' . $todayLabel);

            $sheet->getStyle('A1:A2')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->mergeCells('A1:G1');
            $sheet->mergeCells('A2:G2');
            $sheet->mergeCells('A4:A5');
            $sheet->mergeCells('B4:B5');
            $sheet->mergeCells('C4:C5');
            $sheet->mergeCells('D4:D5');
            $sheet->mergeCells('E4:E5');


            foreach ($items as $itemTaxType) {
                $dataMasterPajak = MasterJenisPajak ::find([
                  "conditions"  =>  "tipe_instansi = :value:",
                  "bind" => [
                     "value" => $itemTaxType['instansi_tipe']
                  ]
                  ]);

                $countData= count($dataMasterPajak);

                if($itemTaxType['instansi_tipe'] === 1 ){

                    $sheet->mergeCells('F4:J4');
                    $sheet->mergeCells('K4:K5');
                    $sheet->mergeCells('L4:L5');

                    $letterField = ['F', 'G', 'H', 'I', 'J'];

                    $sheet->setCellValue("A4", 'Nama Instansi');
                    $sheet->setCellValue("B4", 'Tahun');
                    $sheet->setCellValue("C4", 'Bulan');
                    $sheet->setCellValue("D4", 'Tunggakan Pajak');
                    $sheet->setCellValue("E4", 'Capaian Penagihan Pajak');
                    $sheet->setCellValue("F4", "Jenis Pajak");
                    $sheet->setCellValue("K4", 'Dibuat Pada');
                    $sheet->setCellValue("L4", 'Dibuat Oleh');

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

                    $rowField = 5;
                    $row = 6;
                    $index = 1;

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

                        $sheet->setCellValue("A$row", $item['nama_instansi']);
                        $sheet->setCellValue("B$row", $item['tahun']);
                        $sheet->setCellValue("C$row", $item['bulan']);
                        $sheet->setCellValueExplicit("D$row", $tunggakanPajak, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValueExplicit("E$row", $capaianPenagihan, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValue("K$row", $tanggalLaporan['datetime']);
                        $sheet->setCellValue("L$row", $item['created_by_name']);

                        foreach ($item['PajakPenagihanDetail'] as $detailIndex => $detail) {
                            $penagihanPajakDetail = 'Rp ' . number_format($detail['penagihan'], 0, ",", ".");

                            $sheet->setCellValue($letterField[$detailIndex] . $rowField, $detail['jenis_pajak']);
                            $sheet->setCellValue($letterField[$detailIndex] . $row, $penagihanPajakDetail != null ? $penagihanPajakDetail : 0, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        }
                        $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);

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

                    $sheet->getStyle('A4:L' . ($row - 1))->applyFromArray($styleArray);

                }
                else if($itemTaxType['instansi_tipe'] === 2 ){

                    $letterField = ['F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P'];

                    $sheet->mergeCells('F4:P4');
                    $sheet->mergeCells('Q4:Q5');
                    $sheet->mergeCells('R4:R5');

                    $sheet->setCellValue("A4", 'Nama Instansi');
                    $sheet->setCellValue("B4", 'Tahun');
                    $sheet->setCellValue("C4", 'Bulan');
                    $sheet->setCellValue("D4", 'Tunggakan Pajak');
                    $sheet->setCellValue("E4", 'Capaian Penagihan Pajak');
                    $sheet->setCellValue("F4", "Jenis Pajak");
                    $sheet->setCellValue("Q4", 'Dibuat Pada');
                    $sheet->setCellValue("R4", 'Dibuat Oleh');

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

                    $rowField = 5;
                    $row = 6;
                    $index = 1;

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

                        $sheet->setCellValue("A$row", $item['nama_instansi']);
                        $sheet->setCellValue("B$row", $item['tahun']);
                        $sheet->setCellValue("C$row", $item['bulan']);
                        $sheet->setCellValueExplicit("D$row", $tunggakanPajak, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValueExplicit("E$row", $capaianPenagihan, \PHPOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        $sheet->setCellValue("K$row", $tanggalLaporan['datetime']);
                        $sheet->setCellValue("L$row", $item['created_by_name']);

                        foreach ($item['PajakPenagihanDetail'] as $detailIndex => $detail) {
                            $penagihanPajakDetail = 'Rp ' . number_format($detail['penagihan'], 0, ",", ".");

                            $sheet->setCellValue($letterField[$detailIndex] . $rowField, $detail['jenis_pajak']);
                            $sheet->setCellValue($letterField[$detailIndex] . $row, $penagihanPajakDetail != null ? $penagihanPajakDetail : 0, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        }
                        $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);

                }

            }

        }

        return $spreadsheet;
    }
    #endregion

    #region Pajak Capaian
    public function listPajakCapaian()
    {
        $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['bulan'] = array_key_exists('bulan', $params) ? $params['bulan'] : null;
        $params['jenis'] = array_key_exists('jenis', $params) ? $params['jenis'] : 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']],
                'bulan' => ['validators' => []],
                'jenis' => ['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 (!is_null($validatedRequest['bulan'])) {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }

            $strWhere .= ' and bulan = :bulan:';
            $bind['bulan'] = $digitBulan;
        }

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

        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 = PajakCapaian::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $pajakList = PajakCapaian::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "tahun desc, bulan",
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );

        $result = [];
        foreach ($pajakList as $pajak) {
            $result[] = $pajak->toArraySimple();
        }

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

    public function detailPajakCapaian($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 = PajakCapaian::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 submitPajakCapaian()
    {
        $this->checkSubmitScopes();
        $params = $this->request->getJsonRawBody(true);
        $params['attachment_uuid'] = array_key_exists('attachment_uuid', $params) ? $params['attachment_uuid'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'bulan' => ['validators' => ['required']],
                'id_jenis_pajak' => ['validators' => ['required', 'digit']],
                'pajak_sebelum' => ['validators' => ['required', 'numeric']],
                'pajak_berjalan' => ['validators' => ['required', 'numeric']],
                'target_berjalan' => ['validators' => ['required', 'numeric']],
                'attachment_uuid' => ['validators' => []],
            ]
        );


        $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
        if ($digitBulan == 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
        }

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

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

        $currData = PajakCapaian::findFirst(
            [
                "tahun = :tahun: and bulan = :bulan: and id_jenis_pajak = :id_jenis_pajak: and instansi_id = :instansi_id: and deleted_at is null",
                "bind" => [
                    "tahun" => $validatedRequest['tahun'],
                    "bulan" => $digitBulan,
                    "id_jenis_pajak" => $validatedRequest['id_jenis_pajak'],
                    "instansi_id" => $instansi->id
                ]
            ]
        );

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

        $jenisPajak = MasterJenisPajak::findFirstById($validatedRequest['id_jenis_pajak']);
        if ($jenisPajak->tipe_instansi != $instansi->id_tipe) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Jenis Pajak tidak valid.');
        }

        $newData = new PajakCapaian();
        $newData->tahun = $validatedRequest['tahun'];
        $newData->bulan = $digitBulan;
        $newData->id_jenis_pajak = $validatedRequest['id_jenis_pajak'];
        $newData->instansi_id = $instansi->id;
        $newData->pajak_tahun_sebelum = $validatedRequest['pajak_sebelum'];
        $newData->pajak_tahun_berjalan = $validatedRequest['pajak_berjalan'];
        $newData->target_tahun_berjalan = $validatedRequest['target_berjalan'];
        $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
        $res = $newData->create();
        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_PAJAK_CAPAIAN, $newData->id, 0);

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

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

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

        $validatedRequest = $this->validate(
            $params,
            [
                'pajak_sebelum' => ['validators' => ['required', 'numeric']],
                'pajak_berjalan' => ['validators' => ['required', 'numeric']],
                'target_berjalan' => ['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 = PajakCapaian::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->pajak_tahun_sebelum = $validatedRequest['pajak_sebelum'];
        $data->pajak_tahun_berjalan = $validatedRequest['pajak_berjalan'];
        $data->target_tahun_berjalan = $validatedRequest['target_berjalan'];
        $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_PAJAK_CAPAIAN, $data->id, 1);

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

    public function deletePajakCapaian($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 = PajakCapaian::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 Pajak Capaian Error");
        }
    }

    public function uploadPajakCapaian()
    {
        $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_PAJAK_CAPAIAN);
            $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;
                    }

                    $jenisPajak = MasterJenisPajak::findFirst(
                        [
                            "jenis_pajak = :jenis_pajak: and tipe_instansi = :tipe_instansi: and deleted_at is null",
                            "bind" => [
                                "jenis_pajak" => $data['column_4'] ?: '',
                                "tipe_instansi" => $instansi->id_tipe
                            ]
                        ]
                    );

                    if(empty($jenisPajak)) {
                        $errors[] = 'Jenis Pajak [' . $data['column_4'] . '] pada Data [' . $rowCount . '] Tidak Ditemukan';
                        $rowCount++;
                        continue;
                    }

                    $digitBulan = DateHelper::convertMonthStringtoDigit($data['column_3'] ?: '');
                    if($digitBulan == 0) {
                        $errors[] = 'Bulan [' . $data['column_3'] . '] pada Data [' . $rowCount . '] Tidak Valid';
                        $rowCount++;
                        continue;
                    }

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

                    $newData->instansi_id = $instansi->id;
                    $newData->tahun = $data['column_2'] ?: 0;
                    $newData->bulan = $digitBulan;
                    $newData->id_jenis_pajak = $jenisPajak->id ?: 0;
                    $newData->pajak_tahun_sebelum = $data['column_5'] ?: 0;
                    $newData->pajak_tahun_berjalan = $data['column_6'] ?: 0;
                    $newData->target_tahun_berjalan = $data['column_7'] ?: 0;
                    $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
                    $res = $newData->create();
                    if (!$res) {
                        $errors[] = 'Save Pajak Capaian 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 downloadPajakCapaian()
    {
        $this->checkViewScopes();

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

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['digit']],
                'bulan' => ['validators' => []],
                'jenis' => ['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 (!is_null($validatedRequest['bulan'])) {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }

            $strWhere .= ' and bulan = :bulan:';
            $bind['bulan'] = $digitBulan;
        }

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

        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'];
        }

        $pajakList = PajakCapaian::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "tahun desc, bulan",
            ]
        );

        $result = [];
        foreach ($pajakList as $pajak) {
            $result[] = $pajak->toArraySimple();
        }

        #region Generate Excel File

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

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

        #endregion
    }
    #endregion

    #region Pajak Penagihan
    public function listPajakPenagihan()
    {
        $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['bulan'] = array_key_exists('bulan', $params) ? $params['bulan'] : 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']],
                'bulan' => ['validators' => []],
                '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 = 'PajakPenagihan.deleted_at is null';
        $bind = [];

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

        if (!is_null($validatedRequest['bulan'])) {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }

            $strWhere .= ' and bulan = :bulan:';
            $bind['bulan'] = $digitBulan;
        }

        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 = PajakPenagihan::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $pajakList = PajakPenagihan::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "tahun desc, bulan",
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );


        $pajakListDetail = PajakPenagihan::query()
        ->columns(' PajakPenagihanDetail.*')
        ->leftJoin('PajakPenagihanDetail', 'PajakPenagihanDetail.pajak_penagihan_id = PajakPenagihan.id')
        ->where($strWhere)
        ->bind($bind)
        ->orderBy('PajakPenagihan.tahun DESC')
        ->limit($validatedRequest['limit'])
        // ->offset($validatedRequest['offset'])
        ->execute();

        $result = [];
        foreach ($pajakList as $pajak) {
            $result[] = $pajak->toArraySimple();
        }
        $pajakListDetailArray = $pajakListDetail->toArray();

        foreach ($result as &$res) {
                       $res['PajakPenagihanDetail'] = array_filter($pajakListDetailArray, function ($detail) use ($res) {
                return $detail['pajak_penagihan_id'] === $res['id'];
            });
        }

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

    public function detailPajakPenagihan($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 = PajakPenagihan::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.');
            }
        }

        $dataPajakPenagihanDetail = PajakPenagihanDetail::find([
            "conditions" => "pajak_penagihan_id = :id: AND deleted_at IS NULL",
            "bind"       => ["id" => $id]
        ]);

        $pajakListDetailArray = $dataPajakPenagihanDetail->toArray();

        // Convert $data to array
        $result = $data->toArrayDetail();

        // Since $result is a single associative array, add the details directly
        $result['pajakPenagihanDetail'] = array_filter($pajakListDetailArray, function ($detail) use ($result) {
            return $detail['pajak_penagihan_id'] === $result['id'];
        });

        return new CollectionPaginatedResponse([$result]);
    }

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

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'bulan' => ['validators' => ['required']],
                'jenisPajakList' => ['validators' => ['required']],
                'capaian_penagihan' => ['validators' => ['required', 'numeric']],
                'attachment_uuid' => ['validators' => []],
            ]
        );

        if (isset($params['jenisPajakList']) && is_array($params['jenisPajakList'])) {
            foreach ($params['jenisPajakList'] as $jenisPajak)
                if (!isset($jenisPajak['id']) || !isset($jenisPajak['penagihan'])) {
                    throw new \Exception('Each jenisPajak must have an id and penagihan.');
                }
        } else {
            throw new \Exception('Invalid or missing jenisPajakList.');
        }

        $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
        if ($digitBulan == 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
        }

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

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

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

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

        $tunggakanPajak = $this->getAmountTunggakan($validatedRequest['tahun'], $instansi->id);

        $newData = new PajakPenagihan();
        $newData->tahun = $validatedRequest['tahun'];
        $newData->bulan = $digitBulan;
        $newData->instansi_id = $instansi->id;
        $newData->tunggakan_pajak = $tunggakanPajak;
        $newData->capaian_penagihan_pajak = $validatedRequest['capaian_penagihan'];
        $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
        //parent data
        $res = $newData->create();
        $pajakPenagihanId  = $newData->id;


        foreach ($params['jenisPajakList'] as $jenisPajak) {
            $detail = new PajakPenagihanDetail();
            $type_taxes  = MasterJenisPajak::findFirst([ "conditions" => "id = :id:",
            "bind"       => ["id" => $jenisPajak['id']]]);
            if (!$type_taxes) {
                throw new \Exception('Jenis pajak with id ' . $jenisPajak['id'] . ' not found.');
            }

            $detail->pajak_penagihan_id = $pajakPenagihanId;
            $detail->id_mata_pajak = $jenisPajak['id'];
            $detail->jenis_pajak = $type_taxes->jenis_pajak;
            $detail->penagihan = $jenisPajak['penagihan'];
            $detail->tipe_instansi = $instansi->id_tipe;

            // Save detail data
            if (!$detail->create()) {
                throw new \Exception('Failed to save pajak penagihan detail.');
            }
        }
        if ($res) {
            $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_PAJAK_PENAGIHAN, $newData->id, 0);

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

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

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

        $validatedRequest = $this->validate(
            $params,
            [
                'capaian_penagihan' => ['validators' => ['required', 'numeric']],
                'jenisPajakList' => ['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 = PajakPenagihan::findFirst($id);

        $pajakPenagihanId = $data->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.');
        }

        $tunggakanPajak = $this->getAmountTunggakan($data->tahun, $data->instansi_id);

        $data->tunggakan_pajak = $tunggakanPajak;
        // $data->mata_pajak= $validatedRequest['mata_pajak'];
        $data->capaian_penagihan_pajak = $validatedRequest['capaian_penagihan'];
        $data->updated_by = $this->shared->user->um_id;
        $data->updated_at = 'now();';
        $res = $data->save();

        $dataPenagihanDetail= PajakPenagihanDetail :: find([ "conditions" => "pajak_penagihan_id = :id:",
        "bind"       => ["id" => $id]]);

        foreach($dataPenagihanDetail as $jenisPajak){
            $jenisPajak->deleted_at = 'now();';
            if (!$jenisPajak->save()) {
                throw new \Exception('Failed to save pajak detail.');
            }
        }


        foreach ($params['jenisPajakList'] as $jenisPajak) {
            $detail = new PajakPenagihanDetail();
            $type_taxes  = MasterJenisPajak::findFirst([ "conditions" => "id = :id:",
            "bind"       => ["id" => $jenisPajak['id']]]);
            $detail->pajak_penagihan_id = $pajakPenagihanId;
            $detail->id_mata_pajak = $jenisPajak['id'];
            $detail->jenis_pajak = $jenisPajak['jenis_pajak'];
            $detail->penagihan = $jenisPajak['penagihan'];
            $detail->tipe_instansi = $instansi->id_tipe;
            $detail->updated_by = $this->shared->user->um_id;
            $detail->updated_at = 'now();';

            // Save detail data
            if (!$detail->create()) {
                throw new \Exception('Failed to save pajak penagihan detail.');
            }
        }

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

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

    public function deletePajakPenagihan($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 = PajakPenagihan::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()';

        $detail_data = PajakPenagihanDetail ::find([ "conditions" => "pajak_penagihan_id = :id:",
        "bind"       => ["id" => $id]]);

        foreach ($detail_data as $jenisPajak) {
            $jenisPajak->deleted_at = date('Y-m-d H:i:s');

            if (!$jenisPajak->save()) {
                // If saving fails, handle the error
                throw new \Exception('Failed to save pajak detail: ' . implode(", ", $jenisPajak->getMessages()));
            }
        }

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

    public function uploadPajakPenagihan()
    {
        $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_PAJAK_PENAGIHAN);
            $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;
                    }

                    $digitBulan = DateHelper::convertMonthStringtoDigit($data['column_3'] ?: '');
                    if($digitBulan == 0) {
                        $errors[] = 'Bulan [' . $data['column_3'] . '] pada Data [' . $rowCount . '] Tidak Valid';
                        $rowCount++;
                        continue;
                    }

                    $tunggakanPajak = $this->getAmountTunggakan($data['column_2'] ?: 0, $instansi->id);

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

                    $newData->instansi_id = $instansi->id;
                    $newData->tahun = $data['column_2'] ?: 0;
                    $newData->bulan = $digitBulan;
                    $newData->tunggakan_pajak = $tunggakanPajak;
                    $newData->capaian_penagihan_pajak = $data['column_4'] ?: 0;
                    $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
                    $res = $newData->create();
                    if (!$res) {
                        $errors[] = 'Save Pajak Penagihan 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 downloadPajakPenagihan()
    {
        $this->checkViewScopes();

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

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['digit']],
                'bulan' => ['validators' => []],
                '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 = 'PajakPenagihan.deleted_at is null';
        $bind = [];

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

        if (!is_null($validatedRequest['bulan'])) {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }

            $strWhere .= ' and bulan = :bulan:';
            $bind['bulan'] = $digitBulan;
        }

        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'];
        }

        $strWhere2 = $strWhere . ' and PajakPenagihanDetail.deleted_at is null';

        $pajakList = PajakPenagihan::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "tahun desc, bulan"
            ]
        );


        $pajakListDetail = PajakPenagihan::query()
            ->columns('PajakPenagihanDetail.*')
            ->leftJoin('PajakPenagihanDetail', 'PajakPenagihanDetail.pajak_penagihan_id = PajakPenagihan.id')
            ->where($strWhere2)
            ->bind($bind)
            ->orderBy('PajakPenagihan.tahun DESC')
            ->execute();

        $result = [];

        foreach ($pajakList as $pajak) {
            $pajakArray = $pajak->toArraySimple();
            $pajakArray['instansi_tipe'] = $instansi->id_tipe;
            $result[] = $pajakArray;
        }

        $pajakListDetailArray = $pajakListDetail->toArray();


        // Adding 'pajakTunggakanDetail' to result
        foreach ($result as &$res) {
            // Filter pajakListDetail based on 'pajak_tunggakan_id' matching 'id' in result
            $res['PajakPenagihanDetail'] = array_values(array_filter($pajakListDetailArray, function ($detail) use ($res) {
                return $detail['pajak_penagihan_id'] === $res['id'];
            }));
        }

        #region Generate Excel File

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

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

        #endregion
    }
    #endregion

    #region Pajak Tunggakan
    public function listPajakTunggakan()
    {

        $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 = 'PajakTunggakan.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 = PajakTunggakan::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $pajakList = PajakTunggakan::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "tahun desc",
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );

        $pajakListDetail = PajakTunggakan::query()
        ->columns(' PajakTunggakanDetail.*')
        ->leftJoin('PajakTunggakanDetail', 'PajakTunggakanDetail.pajak_tunggakan_id = PajakTunggakan.id')
        ->where($strWhere)
        ->bind($bind)
        ->orderBy('PajakTunggakan.tahun DESC')
        ->limit($validatedRequest['limit'])
        // ->offset($validatedRequest['offset'])
        ->execute();

        $result = [];
        foreach ($pajakList as $pajak) {
            $result[] = $pajak->toArraySimple();
        }
        $pajakListDetailArray = $pajakListDetail->toArray();
        // Adding 'pajakTunggakanDetail' to result
        foreach ($result as &$res) {
            // Filter pajakListDetail based on 'pajak_tunggakan_id' matching 'id' in result
            $res['pajakTunggakanDetail'] = array_filter($pajakListDetailArray, function ($detail) use ($res) {
                return $detail['pajak_tunggakan_id'] === $res['id'];
            });
        }

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

    public function detailPajakTunggakan($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 = PajakTunggakan::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.');
            }
        }

        $dataPajakTunggakanDetail = PajakTunggakanDetail::find([
            "conditions" => "pajak_tunggakan_id = :id: AND deleted_at IS NULL",
            "bind"       => ["id" => $id]
        ]);

        $pajakListDetailArray = $dataPajakTunggakanDetail->toArray();

        // Convert $data to array
        $result = $data->toArrayDetail();

        // Since $result is a single associative array, add the details directly
        $result['pajakTunggakanDetail'] = array_filter($pajakListDetailArray, function ($detail) use ($result) {
            return $detail['pajak_tunggakan_id'] === $result['id'];
        });

        return new CollectionPaginatedResponse([$result]);
    }


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

        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'tunggakan_pajak' => ['validators' => ['required', 'numeric']],
                'jenisPajakList' => ['validators' => ['required']],
                'attachment_uuid' => ['validators' => []],
            ]
        );


        if (isset($params['jenisPajakList']) && is_array($params['jenisPajakList'])) {
            foreach ($params['jenisPajakList'] as $jenisPajak)
                if (!isset($jenisPajak['id']) || !isset($jenisPajak['tunggakan'])) {
                    throw new \Exception('Each jenisPajak must have an id and tunggakan.');
                }
        } else {
            throw new \Exception('Invalid or missing jenisPajakList.');
        }

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

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

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

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

        $newData = new PajakTunggakan();
        $newData->tahun = $validatedRequest['tahun'];
        $newData->instansi_id = $instansi->id;
        $newData->tunggakan_pajak = $validatedRequest['tunggakan_pajak'];
        $newData->created_by = $newData->updated_by = $this->shared->user->um_id;

    // Save parent data xixixi
    if (!$newData->create()) {
        throw new \Exception('Failed to save pajak_tunggakan.');
    }

    $pajakTunggakanId = $newData->id;

    foreach ($params['jenisPajakList'] as $jenisPajak) {
        $detail = new PajakTunggakanDetail();
        $type_taxes  = MasterJenisPajak::findFirst([ "conditions" => "id = :id:",
        "bind"       => ["id" => $jenisPajak['id']]]);
        $detail->pajak_tunggakan_id = $pajakTunggakanId;
        $detail->id_mata_pajak = $jenisPajak['id'];
        $detail->jenis_pajak = $type_taxes->jenis_pajak;
        $detail->tunggakan = $jenisPajak['tunggakan'];
        $detail->tipe_instansi = $instansi->id_tipe;

        // Save detail data
        if (!$detail->create()) {
            throw new \Exception('Failed to save pajak detail.');
        }
    }

    if ($newData) {
        $this->syncAmountTunggakan($newData);
        $this->handleAttachment($validatedRequest['attachment_uuid'], AttachmentAsetPajak::SUB_MODUL_PAJAK_TUNGGAKAN, $newData->id, 0);
        return new ObjectResponse(
            [
                "success" => true,
                "data" => [
                    "id_pajak_tunggakan" => $newData->id
                ],
            ]
        );
    } else {
        throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Tunggakan Pajak Error");
    }
}

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

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

        $validatedRequest = $this->validate(
            $params,
            [
                'tunggakan_pajak' => ['validators' => ['required', 'numeric']],
                'attachment_uuid' => ['validators' => []],
                'jenisPajakList' => ['validators' => []] // disabled when the previous data is missing or null
            ]
        );

        if (isset($params['jenisPajakList']) && is_array($params['jenisPajakList'])) {
            foreach ($params['jenisPajakList'] as $jenisPajak)
                if (!isset($jenisPajak['id_mata_pajak']) || !isset($jenisPajak['tunggakan'])) {
                    throw new \Exception('Each jenisPajak must have an id and tunggakan.');
                }
        } else {
            throw new \Exception('Invalid or missing jenisPajakList.');
        }


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

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

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

        $pajakTunggakanId = $data->id;

        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.');
        }

        $dataTunggakanDetail = PajakTunggakanDetail::find([
            "conditions" => "pajak_tunggakan_id = :id:",
            "bind"       => ["id" => $id]
        ]);

        foreach($dataTunggakanDetail as $jenisPajak){
            // Set the 'deleted_at' field for the existing record
            $jenisPajak->deleted_at = 'now();';

            if (!$jenisPajak->save()) {
                throw new \Exception('Failed to save pajak detail.');
            }
        }

        $data->tunggakan_pajak = $validatedRequest['tunggakan_pajak'];
        // $data->mata_pajak = $validatedRequest['mata_pajak'];
        $data->updated_by = $this->shared->user->um_id;
        $data->updated_at = 'now();';

        // save parent data xixixixi
        $res = $data->save();
        // $type_taxes  = MasterJenisPajak::findFirst($jenisPajak['id_mata_pajak']);

        foreach ($params['jenisPajakList'] as $jenisPajak) {
            // echo json_encode($jenisPajak->jenis_pajak);
            $detail = new PajakTunggakanDetail();
            $type_taxes  = MasterJenisPajak::findFirst([ "conditions" => "id = :id:",
            "bind"       => ["id" => $jenisPajak['id']]]);
            $detail->pajak_tunggakan_id = $pajakTunggakanId;
            $detail->id_mata_pajak = $jenisPajak['id'];
            $detail->jenis_pajak = $jenisPajak['jenis_pajak'];
            $detail->tunggakan = $jenisPajak['tunggakan'];
            $detail->tipe_instansi = $instansi->id_tipe;
            $detail->updated_by = $this->shared->user->um_id;
            $detail->updated_at = 'now();';

            // Save detail data
            if (!$detail->create()) {
                throw new \Exception('Failed to save pajak detail.');
            }
        }

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

            $data = PajakTunggakan::findFirst($data->id);
            $this->syncAmountTunggakan($data);

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => $data->toArraySimple()
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Tunggakan Pajak Error");
        }
    }

    public function deletePajakTunggakan($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 = PajakTunggakan::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.');
            }
        }

        $detail_data = PajakTunggakanDetail ::find([ "conditions" => "pajak_tunggakan_id = :id:",
        "bind"       => ["id" => $id]]);

        $data->deleted_by = $this->shared->user->um_id;
        $data->deleted_at = 'now()';
        $res = $data->save();

        foreach ($detail_data as $jenisPajak) {
            $jenisPajak->deleted_at = date('Y-m-d H:i:s');

            if (!$jenisPajak->save()) {
                // If saving fails, handle the error
                throw new \Exception('Failed to save pajak detail: ' . implode(", ", $jenisPajak->getMessages()));
            }
        }

        if ($res) {
            $data->tunggakan_pajak = 0;
            $this->syncAmountTunggakan($data);

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "message" => 'Data berhasil dihapus'
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Delete Tunggakan Pajak Error");
        }
    }

    public function uploadPajakTunggakan()
    {
        $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_PAJAK_TUNGGAKAN);
            $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 PajakTunggakan();

                    $newData->instansi_id = $instansi->id;
                    $newData->tahun = $data['column_2'] ?: 0;
                    $newData->tunggakan_pajak = $data['column_3'] ?: 0;
                    $newData->created_by = $newData->updated_by = $this->shared->user->um_id;
                    $res = $newData->create();
                    if (!$res) {
                        $errors[] = 'Save Pajak Penagihan No. [' . $rowCount . '] Error';
                    } else {
                        $this->syncAmountTunggakan($newData);
                    }
                    $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 downloadPajakTunggakan()
    {
        $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 = 'PajakTunggakan.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'];
        }

        $strWhere2 = $strWhere . ' and PajakTunggakanDetail.deleted_at is null';

        $pajakList = PajakTunggakan::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "tahun desc"
            ]
        );


        $pajakListDetail = PajakTunggakan::query()
            ->columns('PajakTunggakanDetail.*')
            ->leftJoin('PajakTunggakanDetail', 'PajakTunggakanDetail.pajak_tunggakan_id = PajakTunggakan.id')
            ->where($strWhere2)
            ->bind($bind)
            ->orderBy('PajakTunggakan.tahun DESC')
            ->execute();


        $result = [];

        foreach ($pajakList as $pajak) {
            $pajakArray = $pajak->toArraySimple();
            $pajakArray['instansi_tipe'] = $instansi->id_tipe;
            $result[] = $pajakArray;
        }

        $pajakListDetailArray = $pajakListDetail->toArray();

        // Adding 'pajakTunggakanDetail' to result
        foreach ($result as &$res) {
            // Filter pajakListDetail based on 'pajak_tunggakan_id' matching 'id' in result
            $res['pajakTunggakanDetail'] = array_values(array_filter($pajakListDetailArray, function ($detail) use ($res) {
                return $detail['pajak_tunggakan_id'] === $res['id'];
            }));
        }

        #region Generate Excel File

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

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

        #endregion
    }

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

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


        $result = $this->getAmountTunggakan($validatedRequest['tahun'], $this->getIdInstansi());

        return new SimpleResponse($result);
    }

    private function getAmountTunggakan($tahun, $idInstansi)
    {
        if (empty($idInstansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User belum terdaftar pada instansi.');
        }

        $instansi = Instansi::findFirst($idInstansi);

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

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

        $data = PajakTunggakan::findFirst(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $result = 0;
        if (!empty($data)) {
            $result = $data->tunggakan_pajak;
        }

        return $result;
    }

    private function syncAmountTunggakan($dataTunggakan)
    {
        $listPenagihan = PajakPenagihan::find(
            [
                "instansi_id = :instansi_id: and tahun = :tahun: and deleted_at is null",
                "bind" => [
                    "instansi_id" => $dataTunggakan->instansi_id,
                    "tahun" => $dataTunggakan->tahun
                ]
            ]
        );

        foreach ($listPenagihan as $penagihan) {
            $penagihan->tunggakan_pajak = $dataTunggakan->tunggakan_pajak;
            $penagihan->updated_by = $this->shared->user->um_id;
            $penagihan->updated_at = 'now()';
            $penagihan->save();
        }
    }
    #endregion

    #region Dashboard
    #region Admin
    public function dashboardCapaian()
    {
        $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['bulan'] = array_key_exists('bulan', $params) ? $params['bulan'] : 'all';
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;
        $params['jenis_pajak'] = array_key_exists('jenis_pajak', $params) ? $params['jenis_pajak'] : null;

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

        $digitBulan = 0;
        if ($validatedRequest['bulan'] != 'all') {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }
        }

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

        $instansi = Instansi::findFirst($this->getIdInstansi());
        $isKotaKabupaten = false;
        $isProvinsi = false;
        $namaInstansi = '';

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

        if($digitBulan > 0) {
            $strWhere .= ' and (bulan = :bulan: or bulan is null)';
            $bind['bulan'] = $digitBulan;
        }

        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']);
            $namaInstansi = $dataInstansi->nama;

            if ($dataInstansi->id_tipe == 1) {
                $isProvinsi = true;
            } else if ($dataInstansi->id_tipe == 2) {
                $isKotaKabupaten = true;
            }
        } else {
            $namaInstansi = 'Nasional';

            $isProvinsi = $isKotaKabupaten = true;
        }

        $result['nama_instansi'] = $namaInstansi;

        //Get Data Kota/Kabupaten
        $result['kota_kabupaten'] = null;
        if ($isKotaKabupaten) {
            $arrIdJenisPajak = [];

            if (!is_null($validatedRequest['jenis_pajak'])) {
                $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
            } else {
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi = :tipe_instansi:",
                        "bind" => [
                            "tipe_instansi" => 2
                        ]
                    ]
                );

                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }

            $strWhereKota = $strWhere;
            $bindKota = $bind;
            $strWhereKota .= ' and MasterJenisPajak.id in ({id_jenis_pajak:array})';
            $bindKota['id_jenis_pajak'] = $arrIdJenisPajak;


            $resultKota = MasterJenisPajak::query()
                ->columns("MasterJenisPajak.id as id_jenis_pajak, MasterJenisPajak.jenis_pajak as jenis_pajak, 
                    COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) as pajak_tahun_sebelum, 
                    COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) as target_tahun_berjalan,
                    COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) as pajak_tahun_berjalan")
                ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
                ->where($strWhereKota)
                ->bind($bindKota)
                ->groupBy('MasterJenisPajak.id, MasterJenisPajak.jenis_pajak')
                ->orderBy('MasterJenisPajak.id')
                ->execute()
                ->toArray();

            foreach ($resultKota as $kota) {
                $result['kota_kabupaten'][] = $kota;
            }
        }

        //Get Data Provinsi
        $result['provinsi'] = null;
        if ($isProvinsi) {
            $arrIdJenisPajak = [];
            if (!is_null($validatedRequest['jenis_pajak'])) {
                $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
            } else {
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi = :tipe_instansi:",
                        "bind" => [
                            "tipe_instansi" => 1
                        ]
                    ]
                );

                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }

            $strWhereProvinsi = $strWhere;
            $bindProvinsi = $bind;
            $strWhereProvinsi .= ' and MasterJenisPajak.id in ({id_jenis_pajak:array})';
            $bindProvinsi['id_jenis_pajak'] = $arrIdJenisPajak;

            $resultProvinsi = MasterJenisPajak::query()
                ->columns("MasterJenisPajak.id as id_jenis_pajak, MasterJenisPajak.jenis_pajak as jenis_pajak, 
                    COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) as pajak_tahun_sebelum, 
                    COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) as target_tahun_berjalan,
                    COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) as pajak_tahun_berjalan")
                ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
                ->where($strWhereProvinsi)
                ->bind($bindProvinsi)
                ->groupBy('MasterJenisPajak.id, MasterJenisPajak.jenis_pajak')
                ->orderBy('MasterJenisPajak.id')
                ->execute()
                ->toArray();

            foreach ($resultProvinsi as $provinsi) {
                $result['provinsi'][] = $provinsi;
            }
        }

        return new SimpleResponse($result);
    }

    public function dashboardPenagihan()
    {
        $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());
        $namaInstansi = '';

        $strWhere = 'PajakPenagihan.deleted_at is null and PajakTunggakan.deleted_at is null and (PajakPenagihan.tahun = :tahun: or PajakPenagihan.tahun is null)';

        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 (PajakPenagihan.instansi_id = :instansi_id: or PajakPenagihan.instansi_id is null)';
            $bind['instansi_id'] = $validatedRequest['instansi_id'];

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

        for ($i = 0; $i < 5; $i++) {
            $tahun = $validatedRequest['tahun'] - $i;
            $bind['tahun'] = $tahun;

            $res = PajakPenagihan::query()
                ->columns('COALESCE(PajakTunggakan.tunggakan_pajak, 0) as tunggakan_pajak, COALESCE(SUM(PajakPenagihan.capaian_penagihan_pajak), 0) as capaian_penagihan_pajak')
                ->leftJoin('PajakTunggakan', 'PajakTunggakan.instansi_id = PajakPenagihan.instansi_id and PajakTunggakan.tahun = PajakPenagihan.tahun')
                ->where($strWhere)
                ->groupBy(['PajakPenagihan.tahun', 'PajakTunggakan.tunggakan_pajak'])
                ->bind($bind)
                ->execute()
                ->toArray();

            $result[$tahun] = $res[0] ?? ['tunggakan_pajak' => 0, 'capaian_penagihan_pajak' => 0];
        }

        return new SimpleResponse($result);
    }
    #endregion

    #region Public
    public function summaryCapaian()
    {
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['bulan'] = array_key_exists('bulan', $params) ? $params['bulan'] : null;
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;
        $params['jenis_pajak'] = array_key_exists('jenis_pajak', $params) ? $params['jenis_pajak'] : null;

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

        $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
        if ($digitBulan == 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
        }

        $isKotaKabupaten = false;
        $isProvinsi = false;
        $namaInstansi = '';

        $strWhere = 'PajakCapaian.deleted_at is null and (tahun = :tahun: or tahun is null) and (bulan = :bulan: or bulan is null)';
        $bind = [
            'tahun' => $validatedRequest['tahun'],
            'bulan' => $digitBulan
        ];

        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']);
            $namaInstansi = $dataInstansi->nama;

            if ($dataInstansi->id_tipe == 1) {
                $isProvinsi = true;
            } else if ($dataInstansi->id_tipe == 2) {
                $isKotaKabupaten = true;
            }
        } else {
            $namaInstansi = 'Nasional';

            $isProvinsi = $isKotaKabupaten = true;
        }
        $result['nama_instansi'] = $namaInstansi;

        //Get Data Kota/Kabupaten
        $result['kota_kabupaten'] = null;
        if ($isKotaKabupaten) {
            $arrIdJenisPajak = [];

            if (!is_null($validatedRequest['jenis_pajak'])) {
                $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
            } else {
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi = :tipe_instansi:",
                        "bind" => [
                            "tipe_instansi" => 2
                        ]
                    ]
                );

                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }

            $strWhereKota = $strWhere;
            $bindKota = $bind;
            $strWhereKota .= ' and MasterJenisPajak.id in ({id_jenis_pajak:array})';
            $bindKota['id_jenis_pajak'] = $arrIdJenisPajak;

            $resultKota = MasterJenisPajak::query()
                ->columns("MasterJenisPajak.id as id_jenis_pajak, MasterJenisPajak.jenis_pajak as jenis_pajak, 
                    COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) as pajak_tahun_sebelum, 
                    COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) as target_tahun_berjalan,
                    COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) as pajak_tahun_berjalan")
                ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
                ->where($strWhereKota)
                ->bind($bindKota)
                ->groupBy('MasterJenisPajak.id, MasterJenisPajak.jenis_pajak')
                ->orderBy('MasterJenisPajak.id')
                ->execute()
                ->toArray();

            foreach ($resultKota as $kota) {
                $result['kota_kabupaten'][] = $kota;
            }
        }

        //Get Data Provinsi
        $result['provinsi'] = null;
        if ($isProvinsi) {
            $arrIdJenisPajak = [];
            if (!is_null($validatedRequest['jenis_pajak'])) {
                $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
            } else {
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi = :tipe_instansi:",
                        "bind" => [
                            "tipe_instansi" => 1
                        ]
                    ]
                );

                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }

            $strWhereProvinsi = $strWhere;
            $bindProvinsi = $bind;
            $strWhereProvinsi .= ' and MasterJenisPajak.id in ({id_jenis_pajak:array})';
            $bindProvinsi['id_jenis_pajak'] = $arrIdJenisPajak;

            $resultProvinsi = MasterJenisPajak::query()
                ->columns("MasterJenisPajak.id as id_jenis_pajak, MasterJenisPajak.jenis_pajak as jenis_pajak, 
                    COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) as pajak_tahun_sebelum, 
                    COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) as target_tahun_berjalan,
                    COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) as pajak_tahun_berjalan")
                ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
                ->where($strWhereProvinsi)
                ->bind($bindProvinsi)
                ->groupBy('MasterJenisPajak.id, MasterJenisPajak.jenis_pajak')
                ->orderBy('MasterJenisPajak.id')
                ->execute()
                ->toArray();

            foreach ($resultProvinsi as $provinsi) {
                $result['provinsi'][] = $provinsi;
            }
        }

        return new SimpleResponse($result);
    }

    public function summaryPenagihan()
    {

        $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']]
            ]
        );

        $namaInstansi = '';

        $strWhere = 'PajakPenagihan.deleted_at is null and (tahun = :tahun: or tahun is null)';

        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']);
            $namaInstansi = $dataInstansi->nama;
        } else {
            $namaInstansi = 'Nasional';
        }
        $result['nama_instansi'] = $namaInstansi;

        for ($i = 0; $i < 5; $i++) {
            $tahun = $validatedRequest['tahun'] - $i;
            $bind['tahun'] = $tahun;

            $result[$tahun] = PajakPenagihan::query()
                ->columns('COALESCE(SUM(tunggakan_pajak), 0) as tunggakan_pajak, COALESCE(SUM(capaian_penagihan_pajak), 0) as capaian_penagihan_pajak')
                ->where($strWhere)
                ->bind($bind)
                ->execute()
                ->toArray()[0];
        }

        return new SimpleResponse($result);
    }


    public function summaryPenagihanQuarter()
    {

        $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['province_id'] = array_key_exists('province_id', $params) ? $params['province_id'] : null;
        $params['id_kota_kabupaten']= array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;


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


        $namaInstansi = '';

        $strWhere = 'PajakPenagihan.deleted_at is null and (tahun = :tahun: or tahun is null)';

        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']);
            $namaInstansi = $dataInstansi->nama;
        }
        else if(!empty($validatedRequest['province_id']) && empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten is null AND id_tipe = 1',
                "bind" => [
                    "id_provinsi"   => $validatedRequest['province_id']
                    ]
                ]);

            if($dataInstansi){
                $namaInstansi = $dataInstansi->nama ;
                $idInstansi = $dataInstansi->id;
            }
            else{
                $namaInstansi = 'The government is not exists in the list!';
                $idInstansi = 0;
            }
            $isProvinsi = 1;
            $strWhere.= ' and PajakPenagihan.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;

        }
        else if(!empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten = :id_kota_kabupaten: and id_tipe = 2 ',
                "bind" => [
                    "id_provinsi"       => $validatedRequest['province_id'],
                    "id_kota_kabupaten" => $validatedRequest['id_kota_kabupaten'],
                ]
                ]);

            if($dataInstansi){
                $namaInstansi = $dataInstansi->nama ;
                $idInstansi = $dataInstansi->id;
            }
            else{
                $namaInstansi = 'The government is not exists in the list!';
                $idInstansi = 0;
            }
            $isKotaKabupaten = 1;

            $strWhere.= ' and PajakPenagihan.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;
        }

        else {
            $namaInstansi = 'Nasional';
        }
        $result['nama_instansi'] = $namaInstansi;

            $tahun = $validatedRequest['tahun'];
            $bind['tahun'] = $tahun;


            $result = PajakPenagihan::query()
                ->columns('COALESCE(tunggakan_pajak,0) as tunggakan_pajak, COALESCE(SUM(capaian_penagihan_pajak), 0) as capaian_penagihan_pajak')
                ->where($strWhere)
                ->groupBy('PajakPenagihan.tunggakan_pajak')
                ->bind($bind)
                ->execute()
                ->toArray();

                if (!empty($result)) {
                    $result = $result[0];
                } else {
                    $result = [
                        'tunggakan_pajak' => 0,
                        'capaian_penagihan_pajak' => 0
                    ];
                }

            $quarters = [
                'TW-I' => [1, 2, 3],
                'TW-II' => [4, 5, 6],
                'TW-III' => [7, 8, 9],
                'TW-IV' => [10, 11, 12]
            ];


                foreach ($quarters as $quarterName => $months) {
                    $strWhereKota = $strWhere;
                    // Add the month condition to the WHERE clause
                    $strWhereKota .= ' AND PajakPenagihan.bulan IN (' . implode(', ', $months) . ')';

                    $bindKota = $bind; // Use the common bind values

                    // Execute the query for the current quarter
                    $result[$quarterName] = PajakPenagihan::query()
                    ->columns('COALESCE(SUM(tunggakan_pajak), 0) as tunggakan_pajak, COALESCE(SUM(capaian_penagihan_pajak), 0) as capaian_penagihan_pajak')
                    ->where($strWhereKota)
                    ->bind($bind)
                    ->execute();

                }


        return new SimpleResponse($result);
    }

    public function dashboardCapaianQuarter()
    {

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['bulan'] = array_key_exists('bulan', $params) ? $params['bulan'] : 'all';
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;
        $params['jenis_pajak'] = array_key_exists('jenis_pajak', $params) ? $params['jenis_pajak'] : null;
        $params['province_id'] = array_key_exists('province_id', $params) ? $params['province_id'] : null;
        $params['id_kota_kabupaten']= array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;
        $params['quarter'] = array_key_exists('quarter', $params) ? $params['quarter'] : null;


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


        $digitBulan = 0;
        if ($validatedRequest['bulan'] != 'all') {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }
        }


        $quarterQuery = '';
        if($validatedRequest['quarter'] == 1){
            $quarterQuery = '1, 2, 3';
        }
        else if($validatedRequest['quarter'] == 2){
            $quarterQuery = ' 4, 5, 6' ;
        }
        else if($validatedRequest['quarter'] == 3){
            $quarterQuery = '7, 8, 9 ';
        }
        else if($validatedRequest['quarter'] == 4){
            $quarterQuery = '10, 11, 12';
        }

        $isKotaKabupaten = false;
        $isProvinsi = false;
        $namaInstansi = '';

        $strWhere = 'PajakCapaian.deleted_at is null and tahun = :tahun:';
        $bind = [
            'tahun' => $validatedRequest['tahun'],

        ];

        if($digitBulan > 0) {
            $strWhere .= ' and (bulan = :bulan: or bulan is null)';
            $bind['bulan'] = $digitBulan;
        }


        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']);
            $namaInstansi = $dataInstansi->nama;

            if ($dataInstansi->id_tipe == 1) {
                $isProvinsi = true;
            } else if ($dataInstansi->id_tipe == 2) {
                $isKotaKabupaten = true;
            }
        } else if(!empty($validatedRequest['province_id']) && empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten is null AND id_tipe = 1',
                "bind" => [
                    "id_provinsi"   => $validatedRequest['province_id']
                    ]
                ]);

            if($dataInstansi){
                $namaInstansi = $dataInstansi->nama ;
                $idInstansi = $dataInstansi->id;
            }
            else{
                $namaInstansi = 'The government is not exists in the list!';
                $idInstansi = 0;
            }

            $isProvinsi = 1;
            $strWhere.= ' and PajakCapaian.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;

        }
        else if(!empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten = :id_kota_kabupaten: and id_tipe = 2 ',
                "bind" => [
                    "id_provinsi"       => $validatedRequest['province_id'],
                    "id_kota_kabupaten" => $validatedRequest['id_kota_kabupaten'],
                ]
                ]);


            if($dataInstansi){
                $namaInstansi = $dataInstansi->nama ;
                $idInstansi = $dataInstansi->id;
            }
            else{
                $namaInstansi = 'The government is not exists in the list!';
                $idInstansi = 0;
            }

            $isKotaKabupaten = 1;

            $strWhere.= ' and PajakCapaian.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;
        }
        else {
            $namaInstansi = 'Nasional';

        }


        if($isKotaKabupaten){

            $result = [
                'nama_instansi' => '',
                'TW-I' => [],
                'TW-II' => [],
                'TW-III' => [],
                'TW-IV' => []
            ];

            $result['nama_instansi'] = $namaInstansi;

            $totalPajakTahunSebelum = 0;
            $totalTargetTahunBerjalan = 0;
            $totalPajakTahunBerjalan = 0;

            // Get the 'jenis_pajak' IDs
            $arrIdJenisPajak = [];

            if (!is_null($validatedRequest['jenis_pajak'])) {
                $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
            } else {
                $arrJenisPajak = MasterJenisPajak::find([
                    "deleted_at IS NULL AND tipe_instansi = :tipe_instansi:",
                    "bind" => ["tipe_instansi" => 2]
                ]);

                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }

            $quarters = [
                'TW-I' => [1, 2, 3],
                'TW-II' => [4, 5, 6],
                'TW-III' => [7, 8, 9],
                'TW-IV' => [10, 11, 12]
            ];


            foreach ($quarters as $quarterName => $months) {
                $strWhereKota = $strWhere;
                // Add the month condition to the WHERE clause
                $strWhereKota .= ' AND PajakCapaian.bulan IN (' . implode(', ', $months) . ')';
                $bindKota = $bind; // Use the common bind values

                // If jenis_pajak IDs exist, add them to the query with proper binding
                if (!empty($arrIdJenisPajak)) {
                    $strWhereKota .= ' AND MasterJenisPajak.id IN ({id_jenis_pajak:array})';
                    $bindKota['id_jenis_pajak'] = $arrIdJenisPajak; // This will automatically be handled as an array
                }

                // Execute the query for the current quarter
                $resultKota = MasterJenisPajak::query()
                    ->columns("
                        COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) AS pajak_tahun_sebelum,
                        COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) AS target_tahun_berjalan,
                        COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) AS pajak_tahun_berjalan
                    ")
                    ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
                    ->where($strWhereKota)
                    ->bind($bindKota)
                    ->execute()
                    ->getFirst();

                // Calculate totals for each quarter
                $pajakTahunSebelum = (float)$resultKota->pajak_tahun_sebelum;
                $targetTahunBerjalan = (float)$resultKota->target_tahun_berjalan;
                $pajakTahunBerjalan = (float)$resultKota->pajak_tahun_berjalan;

                // Add these values to the result for the current quarter
                $result[$quarterName][] = [
                    "pajak_tahun_sebelum" => number_format($pajakTahunSebelum, 2, '.', ''),
                    "target_tahun_berjalan" => number_format($targetTahunBerjalan, 2, '.', ''),
                    "pajak_tahun_berjalan" => number_format($pajakTahunBerjalan, 2, '.', '')
                ];

                // Accumulate the total for the entire year
                $totalPajakTahunSebelum += $pajakTahunSebelum;
                $totalTargetTahunBerjalan += $targetTahunBerjalan;
                $totalPajakTahunBerjalan += $pajakTahunBerjalan;
            }


            $result['total'] = [
                'pajak_tahun_sebelum' => number_format($totalPajakTahunSebelum, 2, '.', ''),
                'target_tahun_berjalan' => number_format($totalTargetTahunBerjalan, 2, '.', ''),
                'pajak_tahun_berjalan' => number_format($totalPajakTahunBerjalan, 2, '.', '')
            ];

        }

        if ($isProvinsi) {
            $result = [
                'nama_instansi' => '',
                'TW-I' => [],
                'TW-II' => [],
                'TW-III' => [],
                'TW-IV' => []
            ];

            $result['nama_instansi'] = $namaInstansi;

            $totalPajakTahunSebelum = 0;
            $totalTargetTahunBerjalan = 0;
            $totalPajakTahunBerjalan = 0;
            $arrIdJenisPajak = [];
            if (!is_null($validatedRequest['jenis_pajak'])) {
                $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
            } else {
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi = :tipe_instansi:",
                        "bind" => [
                            "tipe_instansi" => 1
                        ]
                    ]
                );

                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }

            $quarters = [
                'TW-I' => [1, 2, 3],
                'TW-II' => [4, 5, 6],
                'TW-III' => [7, 8, 9],
                'TW-IV' => [10, 11, 12]
            ];

            foreach ($quarters as $quarterName => $months) {
                $strWhereKota = $strWhere;

                $strWhereKota .= ' AND PajakCapaian.bulan IN (' . implode(', ', $months) . ')';
                $bindKota = $bind;


                if (!empty($arrIdJenisPajak)) {
                    $strWhereKota .= ' AND MasterJenisPajak.id IN ({id_jenis_pajak:array})';
                    $bindKota['id_jenis_pajak'] = $arrIdJenisPajak;
                }


                $resultKota = MasterJenisPajak::query()
                    ->columns("
                        COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) AS pajak_tahun_sebelum,
                        COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) AS target_tahun_berjalan,
                        COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) AS pajak_tahun_berjalan
                    ")
                    ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
                    ->where($strWhereKota)
                    ->bind($bindKota)
                    ->execute()
                    ->getFirst();

                // Calculate totals for each quarter
                $pajakTahunSebelum = (float)$resultKota->pajak_tahun_sebelum;
                $targetTahunBerjalan = (float)$resultKota->target_tahun_berjalan;
                $pajakTahunBerjalan = (float)$resultKota->pajak_tahun_berjalan;


                $result[$quarterName][] = [
                    "pajak_tahun_sebelum" => number_format($pajakTahunSebelum, 2, '.', ''),
                    "target_tahun_berjalan" => number_format($targetTahunBerjalan, 2, '.', ''),
                    "pajak_tahun_berjalan" => number_format($pajakTahunBerjalan, 2, '.', '')
                ];


                $totalPajakTahunSebelum += $pajakTahunSebelum;
                $totalTargetTahunBerjalan += $targetTahunBerjalan;
                $totalPajakTahunBerjalan += $pajakTahunBerjalan;
            }

            // Add the total for the entire year to the result array
            $result['total'] = [
                'pajak_tahun_sebelum' => number_format($totalPajakTahunSebelum, 2, '.', ''),
                'target_tahun_berjalan' => number_format($totalTargetTahunBerjalan, 2, '.', ''),
                'pajak_tahun_berjalan' => number_format($totalPajakTahunBerjalan, 2, '.', '')
            ];


       }

       if($isProvinsi == false  && $isKotaKabupaten == false){
        $result = [
            'nama_instansi' => '',
            'TW-I' => [],
            'TW-II' => [],
            'TW-III' => [],
            'TW-IV' => []
        ];

        $result['nama_instansi'] = $namaInstansi;
        // Initialize total values for the year
        $totalPajakTahunSebelum = 0;
        $totalTargetTahunBerjalan = 0;
        $totalPajakTahunBerjalan = 0;

        $arrIdJenisPajak = [];
        if (!is_null($validatedRequest['jenis_pajak'])) {
            $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
        } else {
            $arrJenisPajak = MasterJenisPajak::find(
                [
                    "deleted_at is null ",
                ]
            );

            foreach ($arrJenisPajak as $jenisPajak) {
                $arrIdJenisPajak[] = $jenisPajak->id;
            }
        }

        $quarters = [
            'TW-I' => [1, 2, 3],
            'TW-II' => [4, 5, 6],
            'TW-III' => [7, 8, 9],
            'TW-IV' => [10, 11, 12]
        ];

        foreach ($quarters as $quarterName => $months) {
            $strWhereKota = $strWhere;
            // Add the month condition to the WHERE clause
            $strWhereKota .= ' AND PajakCapaian.bulan IN (' . implode(', ', $months) . ')';
            $bindKota = $bind; // Use the common bind values

            // If jenis_pajak IDs exist, add them to the query with proper binding
            if (!empty($arrIdJenisPajak)) {
                $strWhereKota .= ' AND MasterJenisPajak.id IN ({id_jenis_pajak:array})';
                $bindKota['id_jenis_pajak'] = $arrIdJenisPajak;
            }

            $resultKota = MasterJenisPajak::query()
                ->columns("
                    COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) AS pajak_tahun_sebelum,
                    COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) AS target_tahun_berjalan,
                    COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) AS pajak_tahun_berjalan
                ")
                ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
                ->where($strWhereKota)
                ->bind($bindKota)
                ->execute()
                ->getFirst();


            $pajakTahunSebelum = (float)$resultKota->pajak_tahun_sebelum;
            $targetTahunBerjalan = (float)$resultKota->target_tahun_berjalan;
            $pajakTahunBerjalan = (float)$resultKota->pajak_tahun_berjalan;

            $result[$quarterName][] = [
                "pajak_tahun_sebelum" => number_format($pajakTahunSebelum, 2, '.', ''),
                "target_tahun_berjalan" => number_format($targetTahunBerjalan, 2, '.', ''),
                "pajak_tahun_berjalan" => number_format($pajakTahunBerjalan, 2, '.', '')
            ];


            $totalPajakTahunSebelum += $pajakTahunSebelum;
            $totalTargetTahunBerjalan += $targetTahunBerjalan;
            $totalPajakTahunBerjalan += $pajakTahunBerjalan;
        }

        // Add the total for the entire year to the result array
        $result['total'] = [
            'pajak_tahun_sebelum' => number_format($totalPajakTahunSebelum, 2, '.', ''),
            'target_tahun_berjalan' => number_format($totalTargetTahunBerjalan, 2, '.', ''),
            'pajak_tahun_berjalan' => number_format($totalPajakTahunBerjalan, 2, '.', '')
        ];


       }

        return new SimpleResponse($result);
    }


    public function dashboardCapaianYearly(){

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

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


        $digitBulan = 0;
        if ($validatedRequest['bulan'] != 'all') {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }
        }

        $isKotaKabupaten = false;
        $isProvinsi = false;
        $namaInstansi = '';
        $strWhere = 'PajakCapaian.deleted_at is null and tahun = :tahun:';
        $bind = [
            'tahun' => $validatedRequest['tahun'],
            // 'quarter' => $quarterQuery
        ];

        if($digitBulan > 0) {
            $strWhere .= ' and (bulan = :bulan: or bulan is null)';
            $bind['bulan'] = $digitBulan;
        }


        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']);
            $namaInstansi = $dataInstansi->nama;

            if ($dataInstansi->id_tipe == 1) {
                $isProvinsi = true;
            } else if ($dataInstansi->id_tipe == 2) {
                $isKotaKabupaten = true;
            }
        } else if(!empty($validatedRequest['province_id']) && empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten is null AND id_tipe = 1',
                "bind" => [
                    "id_provinsi"   => $validatedRequest['province_id']
                    ]
                ]);

            $namaInstansi = $dataInstansi->nama;
            $idInstansi = $dataInstansi->id;
            $isProvinsi = 1;
            $strWhere.= ' and PajakCapaian.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;

        }
        else if(!empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten = :id_kota_kabupaten: and id_tipe = 2 ',
                "bind" => [
                    "id_provinsi"       => $validatedRequest['province_id'],
                    "id_kota_kabupaten" => $validatedRequest['id_kota_kabupaten'],
                ]
                ]);


            $namaInstansi = $dataInstansi->nama;
            $idInstansi = $dataInstansi->id;
            $isKotaKabupaten = 1;

            $strWhere.= ' and PajakCapaian.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;
        }
        else {
            $namaInstansi = 'Nasional';

        }


        for ($i = 0; $i < 5; $i++) {
            $tahun = $validatedRequest['tahun'] - $i;
            $bind['tahun'] = $tahun;

            $result[$tahun] = PajakCapaian::query()
                ->columns('COALESCE(SUM(pajak_tahun_berjalan), 0) as capaian_pajak, COALESCE(SUM(target_tahun_berjalan), 0) as target_tahun_berjalan')
                ->where($strWhere)
                ->bind($bind)
                ->execute()
                ->toArray()[0];


        }


        return new SimpleResponse($result);
    }

    public function dashboardCapaianAllTaxYearly()
    {

        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : null;
        $params['bulan'] = array_key_exists('bulan', $params) ? $params['bulan'] : 'all';
        $params['instansi_id'] = array_key_exists('instansi_id', $params) ? $params['instansi_id'] : null;
        $params['jenis_pajak'] = array_key_exists('jenis_pajak', $params) ? $params['jenis_pajak'] : null;
        $params['province_id'] = array_key_exists('province_id', $params) ? $params['province_id'] : null;
        $params['id_kota_kabupaten']= array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;
        $params['quarter'] = array_key_exists('quarter', $params) ? $params['quarter'] : null;


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

        $digitBulan = 0;
        if ($validatedRequest['bulan'] != 'all') {
            $digitBulan = DateHelper::convertMonthStringtoDigit($validatedRequest['bulan']);
            if ($digitBulan == 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Input bulan tidak valid');
            }
        }

        // disimpan untuk kebutuhan berikut
        $quarterQuery = '';
        if($validatedRequest['quarter'] == 1){
            $quarterQuery = '1, 2, 3';
        }
        else if($validatedRequest['quarter'] == 2){
            $quarterQuery = ' 4, 5, 6' ;
        }
        else if($validatedRequest['quarter'] == 3){
            $quarterQuery = '7, 8, 9 ';
        }
        else if($validatedRequest['quarter'] == 4){
            $quarterQuery = '10, 11, 12';
        }

        $isKotaKabupaten = false;
        $isProvinsi = false;
        $namaInstansi = '';

        $strWhere = 'PajakCapaian.deleted_at is null and tahun = :tahun:';
        $bind = [
            'tahun' => $validatedRequest['tahun'],

        ];

        if($digitBulan > 0) {
            $strWhere .= ' and (bulan = :bulan: or bulan is null)';
            $bind['bulan'] = $digitBulan;
        }


        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']);
            $namaInstansi = $dataInstansi->nama;

            if ($dataInstansi->id_tipe == 1) {
                $isProvinsi = true;
            } else if ($dataInstansi->id_tipe == 2) {
                $isKotaKabupaten = true;
            }
        } else if(!empty($validatedRequest['province_id']) && empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten is null AND id_tipe = 1',
                "bind" => [
                    "id_provinsi"   => $validatedRequest['province_id']
                    ]
                ]);

            if(!empty($dataInstansi)){
                $namaInstansi = $dataInstansi->nama;
                $idInstansi = $dataInstansi->id;
            }
            else{
                $namaInstansi = 'The government is not exist in the list!' ;
                $idInstansi = 0;
            }

            $isProvinsi = true;
            $strWhere.= ' and PajakCapaian.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;

        }
        else if(!empty($validatedRequest['id_kota_kabupaten'])){
            $dataInstansi  = Instansi::findFirst([
                "conditions" => 'id_provinsi = :id_provinsi: and deleted_at is null and id_kota_kabupaten = :id_kota_kabupaten: and id_tipe  = :id_tipe: ',
                "bind" => [
                    "id_provinsi"       => $validatedRequest['province_id'],
                    "id_kota_kabupaten" => $validatedRequest['id_kota_kabupaten'],
                    "id_tipe"           => 2
                ]
                ]);

            if(!empty($dataInstansi)){
                $namaInstansi = $dataInstansi->nama;
                $idInstansi = $dataInstansi->id;
            }
            else{
                $namaInstansi = 'The government is not exist in the list!';
                $idInstansi = 0;
            }
            $isKotaKabupaten = true;

            $strWhere.= ' and PajakCapaian.instansi_id = :instansi_id: ';
            $bind['instansi_id'] = $idInstansi;
        }
        else {
            $namaInstansi = 'Nasional';

            $isProvinsi = $isKotaKabupaten = true;
        }

        $result['nama_instansi'] = $namaInstansi;
        $result['id_type'] = !empty($dataInstansi) ? $dataInstansi->id_tipe : 0;

        $bindTaxType = [];

        $arrIdJenisPajak = [];
        if (!is_null($validatedRequest['jenis_pajak'])) {
            $arrIdJenisPajak = explode(',', $validatedRequest['jenis_pajak']);
        } else {

            if($isKotaKabupaten){
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi = :tipe_instansi:",
                        "bind" => [
                            "tipe_instansi" => 2
                        ]
                    ]
                );
                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }
            else if($isProvinsi){
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi = :tipe_instansi:",
                        "bind" => [
                            "tipe_instansi" => 1
                        ]
                    ]
                );
                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }
            else{
                $arrJenisPajak = MasterJenisPajak::find(
                    [
                        "deleted_at is null and tipe_instansi IN ({tipe_instansi:array})",
                        "bind" => [
                            "tipe_instansi" => [1, 2, 3]
                        ]
                    ]
                );
                foreach ($arrJenisPajak as $jenisPajak) {
                    $arrIdJenisPajak[] = $jenisPajak->id;
                }
            }
        }

        $strWhereKota = $strWhere;
        $bindKota = $bind;
        $strWhereKota .= ' and MasterJenisPajak.id in ({id_jenis_pajak:array})';

        $bindKota['id_jenis_pajak'] = $arrIdJenisPajak;
        $resultKota = MasterJenisPajak::query()
            ->columns("MasterJenisPajak.id as id_jenis_pajak, MasterJenisPajak.jenis_pajak as jenis_pajak, 
                COALESCE(SUM(PajakCapaian.pajak_tahun_sebelum), 0) as pajak_tahun_sebelum, 
                COALESCE(SUM(PajakCapaian.target_tahun_berjalan), 0) as target_tahun_berjalan,
                COALESCE(SUM(PajakCapaian.pajak_tahun_berjalan), 0) as pajak_tahun_berjalan")
            ->leftJoin('PajakCapaian', 'MasterJenisPajak.id = PajakCapaian.id_jenis_pajak')
            ->where($strWhereKota)
            ->bind($bindKota)
            ->groupBy('MasterJenisPajak.id, MasterJenisPajak.jenis_pajak')
            ->orderBy('MasterJenisPajak.id')
            ->execute()
            ->toArray();

        if(!empty($resultKota)){
            foreach ($resultKota as $kota) {
                $result['result'][] = $kota;
            }
        }else{
            $result['result'] = 0;
        }

        return new SimpleResponse($result);
    }


    #endregion
    #endregion
}