<?php

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

class PpjController extends BaseController
{
    public function lapppjhal1()
    {
        $this->checkScopes(['admin.view']);

        $params = $this->request->get();
        $validated = $this->validate(
            $params,
            [
                'in_kd_kab' => ['validators' => ['required']],
                'in_thbl' => ['validators' => ['required']]
            ]
        );

        $client = new PpjClient($this->config["ppj_prop"]);
        $client->setLogger($this->logger);
        try {
            $result = $client->getLapPpjHal1($validated['in_kd_kab'], $validated['in_thbl']);
            if ($result['rc'] == '00') {
                return new CollectionPaginatedResponse($result['data']);
            } else {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, 'Gagal mendapatkan data', $result);
            }
        } catch (\Exception $ex) {
            $api_params = "in_kd_kab=" . $validated['in_kd_kab'] . "&in_thbl=" . $validated['in_thbl'];
            $this->apiLog->log_error("PPJ - lapppjhal1", $api_params, $client->getErrorBody());
            throw $ex;
        }
    }

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

        $params = $this->request->get();
        $validated = $this->validate(
            $params,
            [
                'in_kd_kab' => ['validators' => ['required']],
                'in_thbl' => ['validators' => ['required']]
            ]
        );

        $client = new PpjClient($this->config["ppj_prop"]);
        $client->setLogger($this->logger);
        try {
            $result = $client->getLapPpjHal2($validated['in_kd_kab'], $validated['in_thbl']);
            if ($result['rc'] == '00') {
                return new CollectionPaginatedResponse($result['data']);
            } else {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, 'Gagal mendapatkan data', $result);
            }
        } catch (\Exception $ex) {
            $api_params = "in_kd_kab=" . $validated['in_kd_kab'] . "&in_thbl=" . $validated['in_thbl'];
            $this->apiLog->log_error("PPJ - lapppjhal2", $api_params, $client->getErrorBody());
            throw $ex;
        }
    }

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

        $params = $this->request->get();
        $validated = $this->validate(
            $params,
            [
                'in_kd_kab' => ['validators' => ['required']],
                'in_thbl' => ['validators' => ['required']]
            ]
        );

        $client = new PpjClient($this->config["ppj_prop"]);
        $client->setLogger($this->logger);
        try {
            $result = $client->getPiutangPemda($validated['in_kd_kab'], $validated['in_thbl']);
            if ($result['rc'] == '00') {
                return new CollectionPaginatedResponse($result['data']);
            } else {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, 'Gagal mendapatkan data', $result);
            }
        } catch (\Exception $ex) {
            $api_params = "in_kd_kab=" . $validated['in_kd_kab'] . "&in_thbl=" . $validated['in_thbl'];
            $this->apiLog->log_error("PPJ - piutangpemda", $api_params, $client->getErrorBody());
            throw $ex;
        }
    }

    public function dashboard()
    {
        $current_instansi = $this->validateRequestAdminPemda();
        $params = $this->request->get();
        $response = $this->generateDashboard($params, $current_instansi);
        return new SimpleResponse($response);
    }

    public function dashboardUnitInduk()
    {
        $namaUnits = $this->validateRequestUnitInduk();
        $params = $this->request->get();
        $params['kd_kabmendagri'] = array_key_exists('kd_kabmendagri', $params) ? $params['kd_kabmendagri'] : '%%';
        $response = $this->generateDashboardUnitInduk($params, $namaUnits);
        return new SimpleResponse($response);
    }

    public function generateExcelRekap()
    {
        ini_set('memory_limit', '2048M');
        $current_instansi = $this->validateRequestAdminPemda();

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

        $data = $this->generateDashboard($params, $current_instansi);
        $this->excelRekap($params, $data, $current_instansi->nama);
    }

    public function generateExcelRekapUnitInduk()
    {
        ini_set('memory_limit', '2048M');
        $namaUnits = $this->validateRequestUnitInduk();
        $params = $this->request->get();
        $params['kd_kabmendagri'] = array_key_exists('kd_kabmendagri', $params) ? $params['kd_kabmendagri'] : '%%';
        $data = $this->generateDashboardUnitInduk($params, $namaUnits);
        $this->excelRekap($params, $data, "");
    }

    public function generateExcelRincian()
    {
        ini_set('memory_limit', '2048M');
        $current_instansi = $this->validateRequestAdminPemda();

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

        $ppjHal1 = PpjHal1::find([
            'conditions' => "thbllap >= :start: AND thbllap <= :end: AND kd_kabmendagri LIKE :id_kota_kabupaten:",
            'bind' => [
                'start' => $params['start'],
                'end' => $params['end'],
                'id_kota_kabupaten' => $current_instansi->id_kota_kabupaten
            ]
        ]);

        $ppjHal2 = PpjHal2::find([
            'conditions' => "thbllap >= :start: AND thbllap <= :end: AND kd_kabmendagri LIKE :id_kota_kabupaten:",
            'bind' => [
                'start' => $params['start'],
                'end' => $params['end'],
                'id_kota_kabupaten' => $current_instansi->id_kota_kabupaten
            ]
        ]);

        $ppjPiutang = PpjPiutangPemda::find([
            'columns' => "
                namaunitpln,
                SUM(tagihan_pemda_ini) AS tagihan_pemda_ini,
                SUM(setoran_ppj_ini) AS setoran_ppj_ini,
                SUM(tagihan_pemda_kum) AS tagihan_pemda_kum,
                SUM(setoran_ppj_kum) AS setoran_ppj_kum,
                SUM(saldo_akhir_404) AS saldo_akhir_404,
                SUM(saldo_404_baru_taglis) AS saldo_404_baru_taglis,
                SUM(saldo_404_berjalan) AS saldo_404_berjalan,
                SUM(saldo_404_tunggakan) AS saldo_404_tunggakan,
                SUM(rpptl_309_kab) AS rpptl_309_kab,
                SUM(pemkwh_309_kab) AS pemkwh_309_kab
            ",
            'conditions' => "thbllap >= :start: AND thbllap <= :end: AND kd_kabmendagri LIKE :id_kota_kabupaten:",
            'bind' => [
                'start' => $params['start'],
                'end' => $params['end'],
                'id_kota_kabupaten' => $current_instansi->id_kota_kabupaten
            ],
            'group' => 'namaunitpln'
        ]);

        $this->excelRincian($params, $ppjHal1, $ppjHal2, $ppjPiutang, $current_instansi->nama);
    }

    public function generateExcelRincianUnitInduk()
    {
        ini_set('memory_limit', '2048M');
        $namaUnits = $this->validateRequestUnitInduk();
        $params = $this->request->get();
        $ppjHal1 = PpjHal1::find([
            'conditions' => "thbllap >= :start: AND thbllap <= :end: AND namaunitpln IN ('" . implode("','", $namaUnits) . "')",
            'bind' => [
                'start' => $params['start'],
                'end' => $params['end']
            ]
        ]);

        $ppjHal2 = PpjHal2::find([
            'conditions' => "thbllap >= :start: AND thbllap <= :end: AND namaunitpln IN ('" . implode("','", $namaUnits) . "')",
            'bind' => [
                'start' => $params['start'],
                'end' => $params['end']
            ]
        ]);

        $ppjPiutang = PpjPiutangPemda::find([
            'columns' => "
                namaunitpln,
                SUM(tagihan_pemda_ini) AS tagihan_pemda_ini,
                SUM(setoran_ppj_ini) AS setoran_ppj_ini,
                SUM(tagihan_pemda_kum) AS tagihan_pemda_kum,
                SUM(setoran_ppj_kum) AS setoran_ppj_kum,
                SUM(saldo_akhir_404) AS saldo_akhir_404,
                SUM(saldo_404_baru_taglis) AS saldo_404_baru_taglis,
                SUM(saldo_404_berjalan) AS saldo_404_berjalan,
                SUM(saldo_404_tunggakan) AS saldo_404_tunggakan,
                SUM(rpptl_309_kab) AS rpptl_309_kab,
                SUM(pemkwh_309_kab) AS pemkwh_309_kab
            ",
            'conditions' => "thbllap >= :start: AND thbllap <= :end: AND namaunitpln IN ('" . implode("','", $namaUnits) . "')",
            'bind' => [
                'start' => $params['start'],
                'end' => $params['end']
            ],
            'group' => 'namaunitpln'
        ]);

        $this->excelRincian($params, $ppjHal1, $ppjHal2, $ppjPiutang, "");
    }

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

        if (empty($params['in_kd_kab']) && !empty($params['instansi_id'])) {
            $instansi = Instansi::findFirst($params['instansi_id']);
            if (!$instansi) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan!");
            }
            $params['in_kd_kab'] = $instansi->id_kota_kabupaten;
        }


        $this->validate(
            $params,
            [
                'in_kd_kab' => ['validators' => ['required', 'digit']],
                'start' => ['validators' => ['required', 'digit']],
                'end' => ['validators' => ['required', 'digit']]
            ]
        );

        $start = (int) $params['start'];
        $end = (int) $params['end'];
        $this->validate(
            ['start' => $start, 'end' => $end],
            [
                'start' => ['validators' => [['name' => 'max', 'params' => $end]]],
                'end' => ['validators' => [['name' => 'min', 'params' => $end]]]
            ]
        );

        // update 20210920 untuk tagihan pemda ambil dari periode akhir filter pencarian saja
        $where = "thbllap = :end: AND kd_kabmendagri LIKE :in_kd_kab:";
        $bind = [
            "in_kd_kab" => $params['in_kd_kab'],
            "end" => $params['end']
        ];
        $group = "kd_kabmendagri, nama_kabmendagri";

        $response = PpjPiutangPemda::query()
            ->columns("
                kd_kabmendagri,
                nama_kabmendagri,
                SUM(saldo_404_baru_taglis) AS saldo_404_baru_taglis,
                SUM(saldo_404_berjalan) AS saldo_404_berjalan,
                SUM(saldo_404_tunggakan) AS saldo_404_tunggakan,
                MAX(updated_at) as last_update
            ")
            ->where($where)
            ->bind($bind)
            ->groupBy($group)
            ->execute()
            ->toArray();

        if (empty($response)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data Tagihan Pemda periode ' . $this->periodToString($params['end']) . ' belum tersedia');
        }

        return new SimpleResponse($response[0]);
    }

    public function tagihanUnitInduk()
    {
        $namaUnits = $this->validateRequestUnitInduk();
        $params = $this->request->get();
        $params['kd_kabmendagri'] = array_key_exists('kd_kabmendagri', $params) ? $params['kd_kabmendagri'] : '%%';

        // update 20210920 untuk tagihan pemda ambil dari periode akhir filter pencarian saja
        $where = "thbllap = :end: AND namaunitpln IN ('" . implode("','", $namaUnits) . "') AND kd_kabmendagri LIKE :kd_kabmendagri:";
        $bind = [
            "end" => $params['end'],
            "kd_kabmendagri" => $params['kd_kabmendagri']
        ];

        $response = PpjPiutangPemda::query()
            ->columns("
                SUM(saldo_404_baru_taglis) AS saldo_404_baru_taglis,
                SUM(saldo_404_berjalan) AS saldo_404_berjalan,
                SUM(saldo_404_tunggakan) AS saldo_404_tunggakan,
                MAX(updated_at) as last_update
            ")
            ->where($where)
            ->bind($bind)
            ->execute()
            ->toArray();

        if (empty($response) || $response[0]['last_update'] === null) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data Tagihan Unit Induk periode ' . $this->periodToString($params['end']) . ' belum tersedia');
        }

        $result = $response[0];
        $result['namaunitpln'] = $namaUnits;

        return new SimpleResponse($result);
    }

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

        if (empty($params['in_kd_kab']) && !empty($params['instansi_id'])) {
            $instansi = Instansi::findFirst($params['instansi_id']);
            if (!$instansi) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan!");
            }
            $params['in_kd_kab'] = $instansi->id_kota_kabupaten;
        }

        $this->validate(
            $params,
            [
                'in_kd_kab' => ['validators' => ['required', 'digit']],
                'start' => ['validators' => ['required', 'digit']],
                'end' => ['validators' => ['required', 'digit']]
            ]
        );

        $start = (int) $params['start'];
        $end = (int) $params['end'];
        $this->validate(
            ['start' => $start, 'end' => $end],
            [
                'start' => ['validators' => [['name' => 'max', 'params' => $end]]],
                'end' => ['validators' => [['name' => 'min', 'params' => $end]]]
            ]
        );

        $response = $this->getRekeningPemda($params['in_kd_kab'], $start, $end);

        return new SimpleResponse($response);
    }

    public function rekeningUnitInduk()
    {
        $namaUnits = $this->validateRequestUnitInduk();
        $params = $this->request->get();
        $start = (int) $params['start'];
        $end = (int) $params['end'];
        $kd_kabmendagri = array_key_exists('kd_kabmendagri', $params) ? $params['kd_kabmendagri'] : '%%';
        $response = $this->getRekeningUnitInduk($namaUnits, $start, $end, $kd_kabmendagri);

        return new SimpleResponse($response);
    }

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

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

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

        $condition = [
            'conditions' => "(0 > :thbllap: OR thbllap = :thbllap:) AND kd_kabmendagri LIKE :kd_kabmendagri:",
            'bind' => [
                'thbllap' => $params['thbllap'],
                'kd_kabmendagri' => '%' . $params['kd_kabmendagri'] . '%'
            ]
        ];
        $total = PpjHal1::count($condition);
        $condition = array_merge($condition, [
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);
        $records = PpjHal1::find($condition);

        return new PaginatedResponse($records, $total, $params['limit'], $params['offset']);
    }

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

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

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

        $condition = [
            'conditions' => "(0 > :thbllap: OR thbllap = :thbllap:) AND kd_kabmendagri LIKE :kd_kabmendagri:",
            'bind' => [
                'thbllap' => $params['thbllap'],
                'kd_kabmendagri' => '%' . $params['kd_kabmendagri'] . '%'
            ]
        ];
        $total = PpjHal2::count($condition);
        $condition = array_merge($condition, [
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);
        $records = PpjHal2::find($condition);

        return new PaginatedResponse($records, $total, $params['limit'], $params['offset']);
    }

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

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

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

        $condition = [
            'conditions' => "(0 > :thbllap: OR thbllap = :thbllap:) AND kd_kabmendagri LIKE :kd_kabmendagri:",
            'bind' => [
                'thbllap' => $params['thbllap'],
                'kd_kabmendagri' => '%' . $params['kd_kabmendagri'] . '%'
            ]
        ];
        $total = PpjPiutangPemda::count($condition);
        $condition = array_merge($condition, [
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);
        $records = PpjPiutangPemda::find($condition);

        return new PaginatedResponse($records, $total, $params['limit'], $params['offset']);
    }

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

        try {
            $this->db->begin();
            $client = new PpjClient($this->config["ppj_prop"]);
            $client->setLogger($this->logger);

            // set periode = bulan berjalan - 1 bulan
            $period = date("Ym", strtotime(date("Y-m-d", strtotime(date("Y-m-d"))) . "-1 month"));
            $now = date('Y-m-d H:i:s');

            $params = $this->request->get();
            $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 1;
            $params['in_kd_kab'] = array_key_exists('in_kd_kab', $params) ? $params['in_kd_kab'] : -1;
            $params['is_cleansing'] = array_key_exists('is_cleansing', $params) && $params['is_cleansing'] == 1;

            // ambil referensi instansi pemda tingkat 2
            $kabKotas = Instansi::find([
                'conditions' => "(0 > :in_kd_kab: OR id_kota_kabupaten = :in_kd_kab:) AND id_tipe = 2",
                'bind' => [
                    'in_kd_kab' => $params['in_kd_kab'],
                ],
                'columns' => 'id_kota_kabupaten',
                'limit' => $params['limit'],
            ]);

            // data cleansing
            if ($params['is_cleansing']) {
                $this->rawQuery("delete from jaga.ppj_hal1 where thbllap = $period");
                $this->rawQuery("delete from jaga.ppj_hal2 where thbllap = $period");
                $this->rawQuery("delete from jaga.ppj_piutang_pemda where thbllap = $period");
            }

            $idxPpjHal1 = $idxPpjHal2 = $idxPpjPiutangPemda = 0;
            // loop referensi kota kabupaten
            foreach ($kabKotas as $kabKota) {
                // process lap ppj hal 1
                $ppjhal1 = $client->getLapPpjHal1($kabKota['id_kota_kabupaten'], $period);
                if ($ppjhal1['rc'] == '00') {
                    $data = $ppjhal1['data'];
                    foreach ($data as $datum) {
                        $model = new PpjHal1();
                        $model->assign($datum);
                        $model->created_at = $now;
                        $model->updated_at = $now;
                        $status = $model->save();
                        if ($status) {
                            $idxPpjHal1++;
                        } else {
                            throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Create Ppj Hal 1 Response Error", $status->getMessages());
                        }
                    }
                }

                // process lap ppj hal 2
                $ppjhal2 = $client->getLapPpjHal2($kabKota['id_kota_kabupaten'], $period);
                if ($ppjhal2['rc'] == '00') {
                    $data = $ppjhal2['data'];
                    foreach ($data as $datum) {
                        $model = new PpjHal2();
                        $model->assign($datum);
                        $model->created_at = $now;
                        $model->updated_at = $now;
                        $status = $model->save();
                        if ($status) {
                            $idxPpjHal2++;
                        } else {
                            throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Create Ppj Hal 2 Response Error", $status->getMessages());
                        }
                    }
                }

                // process lap ppj piutang pemda
                $ppjpiutangpemda = $client->getPiutangPemda($kabKota['id_kota_kabupaten'], $period);
                if ($ppjpiutangpemda['rc'] == '00') {
                    $data = $ppjpiutangpemda['data'];
                    foreach ($data as $datum) {
                        $model = new PpjPiutangPemda();
                        $model->assign($datum);
                        $model->created_at = $now;
                        $model->updated_at = $now;
                        $status = $model->save();
                        if ($status) {
                            $idxPpjPiutangPemda++;
                        } else {
                            throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Create Ppj Piutang Pemda Response Error", $status->getMessages());
                        }
                    }
                }
            }

            $this->db->commit();

            return new SimpleResponse([
                'ppjhal1_rows' => $idxPpjHal1,
                'ppjhal2_rows' => $idxPpjHal2,
                'ppjpiutangpemda_rows' => $idxPpjPiutangPemda
            ]);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    private function getRekeningPemda($kodePemda, $start, $end)
    {
        $where = " WHERE kd_kabmendagri = '" . $kodePemda . "' AND thbllap >= " . $start . " AND thbllap <= " . $end . "";
        $groupBy = " GROUP BY kd_kabmendagri, nama_kabmendagri";
        $ppjHal1 = $this->rawQuery(
            "
            SELECT
                kd_kabmendagri,
                nama_kabmendagri,
                sum(ts_sorek) as ts_sorek,
                sum(ppj_sorek) as ppj_sorek,
                MAX(updated_at) as last_update
            FROM
                ppj_hal1" . $where . $groupBy
        );
        $ppjHal2 = $this->rawQuery(
            "
            SELECT
                kd_kabmendagri,
                nama_kabmendagri,
                sum(ppj_lpb) as ppj_lpb,
                MAX(updated_at) as last_update
            FROM
                ppj_hal2" . $where . $groupBy
        );
        $ppjPiutangPemda = $this->rawQuery(
            "
            SELECT
                kd_kabmendagri,
                nama_kabmendagri,
                sum(pemkwh_309_kab) as pemkwh_309_kab,
                sum(rpptl_309_kab) as rpptl_309_kab,
                MAX(updated_at) as last_update
            FROM
                ppj_piutang_pemda" . $where . $groupBy
        );

        if (empty($ppjHal1) && empty($ppjHal2) && empty($ppjPiutangPemda)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data Rekening Pemda tidak ditemukan');
        }

        $allPeriods = [];
        $currentDate = DateTime::createFromFormat('!Ym', (string)$start);
        $endDate = DateTime::createFromFormat('!Ym', (string)$end);
        if ($currentDate && $endDate) {
            while ($currentDate <= $endDate) {
                $allPeriods[] = $currentDate->format('Ym');
                $currentDate->modify('+1 month');
            }
        }

        $existHal1 = array_column($this->rawQuery("SELECT DISTINCT thbllap FROM ppj_hal1" . $where), 'thbllap');
        $existHal2 = array_column($this->rawQuery("SELECT DISTINCT thbllap FROM ppj_hal2" . $where), 'thbllap');
        $existPiutang = array_column($this->rawQuery("SELECT DISTINCT thbllap FROM ppj_piutang_pemda" . $where), 'thbllap');

        $missingHal1 = array_diff($allPeriods, array_map('strval', $existHal1));
        $missingHal2 = array_diff($allPeriods, array_map('strval', $existHal2));
        $missingPiutang = array_diff($allPeriods, array_map('strval', $existPiutang));

        $result = [
            "kd_kabmendagri" =>  "",
            "nama_kabmendagri" =>  "",
            "ts_sorek" =>  0,
            "ppj_sorek" =>  0,
            "ppj_lpb" =>  0,
            "pemkwh_309_kab" =>  0,
            "rpptl_309_kab" =>  0
        ];
        $last_updates = [];
        if (sizeof($ppjHal1) > 0) {
            $result = array_merge($result, $ppjHal1[0]);
            if (!empty($ppjHal1[0]['last_update'])) $last_updates[] = $ppjHal1[0]['last_update'];
        }
        if (sizeof($ppjHal2) > 0) {
            $result = array_merge($result, $ppjHal2[0]);
            if (!empty($ppjHal2[0]['last_update'])) $last_updates[] = $ppjHal2[0]['last_update'];
        }
        if (sizeof($ppjPiutangPemda) > 0) {
            $result = array_merge($result, $ppjPiutangPemda[0]);
            if (!empty($ppjPiutangPemda[0]['last_update'])) $last_updates[] = $ppjPiutangPemda[0]['last_update'];
        }

        if (!empty($last_updates)) {
            $result['last_update'] = max($last_updates);
        }

        $missingPages = [];
        if (!empty($missingHal1)) $missingPages[] = "Halaman 1";
        if (!empty($missingHal2)) $missingPages[] = "Halaman 2";
        if (!empty($missingPiutang)) $missingPages[] = "Halaman 3";

        $result['kelengkapan'] = !empty($missingPages) ? "Data Laporan PBJT-TL " . implode(", ", $missingPages) . " belum lengkap." : null;
        $result['missing_thbllap_hal1'] = array_values($missingHal1);
        $result['missing_thbllap_hal2'] = array_values($missingHal2);
        $result['missing_thbllap_piutang'] = array_values($missingPiutang);

        return $result;
    }

    private function getRekeningUnitInduk($namaUnits, $start, $end, $kd_kabmendagri)
    {
        $where = " WHERE namaunitpln IN ('" . implode("','", $namaUnits) . "') AND thbllap >= " . $start . " AND thbllap <= " . $end . " AND kd_kabmendagri LIKE '" . $kd_kabmendagri . "'";
        $ppjHal1 = $this->rawQuery(
            "
            SELECT
                sum(ts_sorek) as ts_sorek,
                sum(ppj_sorek) as ppj_sorek,
                MAX(updated_at) as last_update
            FROM
                ppj_hal1" . $where
        );
        $ppjHal2 = $this->rawQuery(
            "
            SELECT
                sum(ppj_lpb) as ppj_lpb,
                MAX(updated_at) as last_update
            FROM
                ppj_hal2" . $where
        );
        $ppjPiutangPemda = $this->rawQuery(
            "
            SELECT
                sum(pemkwh_309_kab) as pemkwh_309_kab,
                sum(rpptl_309_kab) as rpptl_309_kab,
                MAX(updated_at) as last_update
            FROM
                ppj_piutang_pemda" . $where
        );

        if (empty($ppjHal1) && empty($ppjHal2) && empty($ppjPiutangPemda)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data Rekening Unit Induk tidak ditemukan');
        }

        $allPeriods = [];
        $currentDate = DateTime::createFromFormat('!Ym', (string)$start);
        $endDate = DateTime::createFromFormat('!Ym', (string)$end);
        if ($currentDate && $endDate) {
            while ($currentDate <= $endDate) {
                $allPeriods[] = $currentDate->format('Ym');
                $currentDate->modify('+1 month');
            }
        }

        $existHal1 = array_column($this->rawQuery("SELECT DISTINCT thbllap FROM ppj_hal1" . $where), 'thbllap');
        $existHal2 = array_column($this->rawQuery("SELECT DISTINCT thbllap FROM ppj_hal2" . $where), 'thbllap');
        $existPiutang = array_column($this->rawQuery("SELECT DISTINCT thbllap FROM ppj_piutang_pemda" . $where), 'thbllap');

        $missingHal1 = array_diff($allPeriods, array_map('strval', $existHal1));
        $missingHal2 = array_diff($allPeriods, array_map('strval', $existHal2));
        $missingPiutang = array_diff($allPeriods, array_map('strval', $existPiutang));

        $result = [
            "namaunitpln" =>  $namaUnits,
            "ts_sorek" =>  0,
            "ppj_sorek" =>  0,
            "ppj_lpb" =>  0,
            "pemkwh_309_kab" =>  0,
            "rpptl_309_kab" =>  0
        ];
        
        $last_updates = [];
        if (sizeof($ppjHal1) > 0) {
            $result = array_merge($result, $ppjHal1[0]);
            if (!empty($ppjHal1[0]['last_update'])) $last_updates[] = $ppjHal1[0]['last_update'];
        }
        if (sizeof($ppjHal2) > 0) {
            $result = array_merge($result, $ppjHal2[0]);
            if (!empty($ppjHal2[0]['last_update'])) $last_updates[] = $ppjHal2[0]['last_update'];
        }
        if (sizeof($ppjPiutangPemda) > 0) {
            $result = array_merge($result, $ppjPiutangPemda[0]);
            if (!empty($ppjPiutangPemda[0]['last_update'])) $last_updates[] = $ppjPiutangPemda[0]['last_update'];
        }

        if (!empty($last_updates)) {
            $result['last_update'] = max($last_updates);
        }

        $missingPages = [];
        if (!empty($missingHal1)) $missingPages[] = "Halaman 1";
        if (!empty($missingHal2)) $missingPages[] = "Halaman 2";
        if (!empty($missingPiutang)) $missingPages[] = "Halaman 3";

        $result['kelengkapan'] = !empty($missingPages) ? "Data Laporan PBJT-TL " . implode(", ", $missingPages) . " belum lengkap." : null;
        $result['missing_thbllap_hal1'] = array_values($missingHal1);
        $result['missing_thbllap_hal2'] = array_values($missingHal2);
        $result['missing_thbllap_piutang'] = array_values($missingPiutang);

        return $result;
    }

    private function generateDashboard($params, $current_instansi)
    {
        $where = "kd_kabmendagri LIKE :id_kota_kabupaten: AND thbllap >= :start: AND thbllap <= :end:";
        $bind = [
            "id_kota_kabupaten" => $current_instansi->id_kota_kabupaten,
            "start" => $params['start'],
            "end" => $params['end']
        ];
        $group = "kd_kabmendagri";

        $ppjhal1 = PpjHal1::query()
            ->columns("
                SUM(pemkwh_sorek) AS pemkwh_sorek,
                SUM(pal_sorek) AS pal_sorek,
                SUM(ts_sorek) AS ts_sorek,
                SUM(ppj_sorek) AS ppj_sorek,
                
                SUM(ppj_taglis) AS tot_ppj_taglis,
                
                SUM(pal_dph21) AS pal_dph21,
                SUM(ppj_dph21) AS ppj_dph21,
                SUM(pal_dph22) AS pal_dph22,
                SUM(ppj_dph22) AS ppj_dph22,
                SUM(pal_dph23) AS pal_dph23,
                SUM(ppj_dph23) AS ppj_dph23,
                SUM(pal_dph24) AS pal_dph24,
                SUM(ppj_dph24) AS ppj_dph24,
                SUM(pal_dph25) AS pal_dph25,
                SUM(ppj_dph25) AS ppj_dph25
            ")
            ->where($where)
            ->bind($bind)
            ->groupBy($group)
            ->execute()
            ->toArray();

        $ppjhal2 = PpjHal2::query()
            ->columns("
                SUM(ppj_total) AS ppj_total,
                   
                SUM(ppj_ntl) + SUM(ppj_offl) + SUM(ppj_nota) AS tot_ntl,
                SUM(ppj_lpb) + SUM(ppj_tmp_lpb) + SUM(ppj_ujl_lpb) AS tot_lpb,
                SUM(ppj_res_taglis) + SUM(ppj_res_nontaglis) + SUM(ppj_res_pra) AS tot_res,
                SUM(ppj_koreksi_tambah) AS tot_ppj_koreksi_tambah,
                SUM(ppj_koreksi_kurang) AS tot_ppj_koreksi_kurang,
                   
                SUM(pal_res_taglis) AS pal_res_taglis,
                SUM(ppj_res_taglis) AS ppj_res_taglis,
                SUM(pal_res_nontaglis) AS pal_res_nontaglis,
                SUM(ppj_res_nontaglis) AS ppj_res_nontaglis,
                SUM(pal_res_pra) AS pal_res_pra,
                SUM(ppj_res_pra) AS ppj_res_pra,
                   
                SUM(pal_ntl) AS pal_ntl,
                SUM(ppj_ntl) AS ppj_ntl,
                SUM(pal_offl) AS pal_offl,
                SUM(ppj_offl) AS ppj_offl,
                SUM(pal_nota) AS pal_nota,
                SUM(ppj_nota) AS ppj_nota,
                   
                SUM(pal_lpb) AS pal_lpb,
                SUM(ppj_lpb) AS ppj_lpb,
                SUM(pal_tmp_lpb) AS pal_tmp_lpb,
                SUM(ppj_tmp_lpb) AS ppj_tmp_lpb,
                SUM(pal_ujl_lpb) AS pal_ujl_lpb,
                SUM(ppj_ujl_lpb) AS ppj_ujl_lpb
            ")
            ->where($where)
            ->bind($bind)
            ->groupBy($group)
            ->execute()
            ->toArray();

        $ppjpiutangpemda = PpjPiutangPemda::query()
            ->columns("
                SUM(tagihan_pemda_ini) AS tagihan_pemda_ini,
                SUM(pemkwh_309_kab) AS pemkwh_309_kab,
                SUM(rpptl_309_kab) AS rpptl_309_kab
            ")
            ->where($where)
            ->bind($bind)
            ->groupBy($group)
            ->execute()
            ->toArray();

        $rankgoltarif = PpjHal1::query()
            ->columns("
                goltarif,
                SUM(pemkwh_sorek) as pemkwh_sorek
            ")
            ->where($where)
            ->bind($bind)
            ->groupBy("goltarif")
            ->orderBy("pemkwh_sorek DESC")
            ->execute()
            ->toArray();

        if (empty($ppjhal1) && empty($ppjhal2) && empty($ppjpiutangpemda)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, "Data Periode " . $this->periodToString($params['start']) . " - " . $this->periodToString($params['end']) . " belum tersedia.");
        }

        if (empty($ppjhal1)) {
            $ppjhal1 = [
                [
                    "pemkwh_sorek" => 0,
                    "pal_sorek" => 0,
                    "ts_sorek" => 0,
                    "ppj_sorek" => 0,
                    "tot_ppj_taglis" => 0,
                    "pal_dph21" => 0,
                    "ppj_dph21" => 0,
                    "pal_dph22" => 0,
                    "ppj_dph22" => 0,
                    "pal_dph23" => 0,
                    "ppj_dph23" => 0,
                    "pal_dph24" => 0,
                    "ppj_dph24" => 0,
                    "pal_dph25" => 0,
                    "ppj_dph25" => 0
                ]
            ];
        }
        if (empty($ppjhal2)) {
            $ppjhal2 = [
                [
                    "ppj_total" => 0,
                    "tot_ntl" => 0,
                    "tot_lpb" => 0,
                    "tot_res" => 0,
                    "tot_ppj_koreksi_tambah" => 0,
                    "tot_ppj_koreksi_kurang" => 0,
                    "pal_res_taglis" => 0,
                    "ppj_res_taglis" => 0,
                    "pal_res_nontaglis" => 0,
                    "ppj_res_nontaglis" => 0,
                    "pal_res_pra" => 0,
                    "ppj_res_pra" => 0,
                    "pal_ntl" => 0,
                    "ppj_ntl" => 0,
                    "pal_offl" => 0,
                    "ppj_offl" => 0,
                    "pal_nota" => 0,
                    "ppj_nota" => 0,
                    "pal_lpb" => 0,
                    "ppj_lpb" => 0,
                    "pal_tmp_lpb" => 0,
                    "ppj_tmp_lpb" => 0,
                    "pal_ujl_lpb" => 0,
                    "ppj_ujl_lpb" => 0
                ]
            ];
        }
        if (empty($ppjpiutangpemda)) {
            $ppjpiutangpemda = [
                [
                    "tagihan_pemda_ini" => 0,
                    "pemkwh_309_kab" => 0,
                    "rpptl_309_kab" => 0
                ]
            ];
        }
        $response = array_merge($ppjhal1[0], $ppjhal2[0], $ppjpiutangpemda[0]);
        $response['rank_goltarif'] = $rankgoltarif;

        return $response;
    }

    private function generateDashboardUnitInduk($params, $namaUnits)
    {
        $where = "
            namaunitpln IN ('" . implode("','", $namaUnits) . "') 
            AND thbllap >= :start: AND thbllap <= :end: 
            AND kd_kabmendagri LIKE :kd_kabmendagri:
        ";
        $bind = [
            "start" => $params['start'],
            "end" => $params['end'],
            "kd_kabmendagri" => $params['kd_kabmendagri']
        ];

        $ppjhal1 = PpjHal1::query()
            ->columns("
                SUM(pemkwh_sorek) AS pemkwh_sorek,
                SUM(pal_sorek) AS pal_sorek,
                SUM(ts_sorek) AS ts_sorek,
                SUM(ppj_sorek) AS ppj_sorek,
                
                SUM(ppj_taglis) AS tot_ppj_taglis,
                
                SUM(pal_dph21) AS pal_dph21,
                SUM(ppj_dph21) AS ppj_dph21,
                SUM(pal_dph22) AS pal_dph22,
                SUM(ppj_dph22) AS ppj_dph22,
                SUM(pal_dph23) AS pal_dph23,
                SUM(ppj_dph23) AS ppj_dph23,
                SUM(pal_dph24) AS pal_dph24,
                SUM(ppj_dph24) AS ppj_dph24,
                SUM(pal_dph25) AS pal_dph25,
                SUM(ppj_dph25) AS ppj_dph25
            ")
            ->where($where)
            ->bind($bind)
            //            ->groupBy($group)
            ->execute()
            ->toArray();

        $ppjhal2 = PpjHal2::query()
            ->columns("
                SUM(ppj_total) AS ppj_total,
                   
                SUM(ppj_ntl) + SUM(ppj_offl) + SUM(ppj_nota) AS tot_ntl,
                SUM(ppj_lpb) + SUM(ppj_tmp_lpb) + SUM(ppj_ujl_lpb) AS tot_lpb,
                SUM(ppj_res_taglis) + SUM(ppj_res_nontaglis) + SUM(ppj_res_pra) AS tot_res,
                SUM(ppj_koreksi_tambah) AS tot_ppj_koreksi_tambah,
                SUM(ppj_koreksi_kurang) AS tot_ppj_koreksi_kurang,
                   
                SUM(pal_res_taglis) AS pal_res_taglis,
                SUM(ppj_res_taglis) AS ppj_res_taglis,
                SUM(pal_res_nontaglis) AS pal_res_nontaglis,
                SUM(ppj_res_nontaglis) AS ppj_res_nontaglis,
                SUM(pal_res_pra) AS pal_res_pra,
                SUM(ppj_res_pra) AS ppj_res_pra,
                   
                SUM(pal_ntl) AS pal_ntl,
                SUM(ppj_ntl) AS ppj_ntl,
                SUM(pal_offl) AS pal_offl,
                SUM(ppj_offl) AS ppj_offl,
                SUM(pal_nota) AS pal_nota,
                SUM(ppj_nota) AS ppj_nota,
                   
                SUM(pal_lpb) AS pal_lpb,
                SUM(ppj_lpb) AS ppj_lpb,
                SUM(pal_tmp_lpb) AS pal_tmp_lpb,
                SUM(ppj_tmp_lpb) AS ppj_tmp_lpb,
                SUM(pal_ujl_lpb) AS pal_ujl_lpb,
                SUM(ppj_ujl_lpb) AS ppj_ujl_lpb
            ")
            ->where($where)
            ->bind($bind)
            ->execute()
            ->toArray();

        $ppjpiutangpemda = PpjPiutangPemda::query()
            ->columns("
                SUM(tagihan_pemda_ini) AS tagihan_pemda_ini,
                SUM(pemkwh_309_kab) AS pemkwh_309_kab,
                SUM(rpptl_309_kab) AS rpptl_309_kab
            ")
            ->where($where)
            ->bind($bind)
            ->execute()
            ->toArray();

        $rankgoltarif = PpjHal1::query()
            ->columns("
                goltarif,
                SUM(pemkwh_sorek) as pemkwh_sorek
            ")
            ->where($where)
            ->bind($bind)
            ->groupBy("goltarif")
            ->orderBy("pemkwh_sorek DESC")
            ->execute()
            ->toArray();

        if (empty($ppjhal1) && empty($ppjhal2) && empty($ppjpiutangpemda)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, "Data Periode " . $this->periodToString($params['start']) . " - " . $this->periodToString($params['end']) . " belum tersedia.");
        }

        if (empty($ppjhal1)) {
            $ppjhal1 = [
                [
                    "pemkwh_sorek" => 0,
                    "pal_sorek" => 0,
                    "ts_sorek" => 0,
                    "ppj_sorek" => 0,
                    "tot_ppj_taglis" => 0,
                    "pal_dph21" => 0,
                    "ppj_dph21" => 0,
                    "pal_dph22" => 0,
                    "ppj_dph22" => 0,
                    "pal_dph23" => 0,
                    "ppj_dph23" => 0,
                    "pal_dph24" => 0,
                    "ppj_dph24" => 0,
                    "pal_dph25" => 0,
                    "ppj_dph25" => 0
                ]
            ];
        }
        if (empty($ppjhal2)) {
            $ppjhal2 = [
                [
                    "ppj_total" => 0,
                    "tot_ntl" => 0,
                    "tot_lpb" => 0,
                    "tot_res" => 0,
                    "tot_ppj_koreksi_tambah" => 0,
                    "tot_ppj_koreksi_kurang" => 0,
                    "pal_res_taglis" => 0,
                    "ppj_res_taglis" => 0,
                    "pal_res_nontaglis" => 0,
                    "ppj_res_nontaglis" => 0,
                    "pal_res_pra" => 0,
                    "ppj_res_pra" => 0,
                    "pal_ntl" => 0,
                    "ppj_ntl" => 0,
                    "pal_offl" => 0,
                    "ppj_offl" => 0,
                    "pal_nota" => 0,
                    "ppj_nota" => 0,
                    "pal_lpb" => 0,
                    "ppj_lpb" => 0,
                    "pal_tmp_lpb" => 0,
                    "ppj_tmp_lpb" => 0,
                    "pal_ujl_lpb" => 0,
                    "ppj_ujl_lpb" => 0
                ]
            ];
        }
        if (empty($ppjpiutangpemda)) {
            $ppjpiutangpemda = [
                [
                    "tagihan_pemda_ini" => 0,
                    "pemkwh_309_kab" => 0,
                    "rpptl_309_kab" => 0
                ]
            ];
        }
        $response = array_merge($ppjhal1[0], $ppjhal2[0], $ppjpiutangpemda[0]);
        $response['rank_goltarif'] = $rankgoltarif;

        return $response;
    }

    private function excelRekap($params, $data, $nama)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $yearStart = substr($params['start'], 0, 4);
        $yearEnd = substr($params['end'], 0, 4);
        $monthStart = substr($params['start'], 4, 2);
        $monthEnd = substr($params['end'], 4, 2);
        $dateStart = DateHelper::formatFromDate($yearStart, $monthStart);
        $dateEnd = DateHelper::formatFromDate($yearEnd, $monthEnd);

        $borderStyle = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet->setCellValue("A1", 'Pajak Penerangan Jalan ' . $nama);
        $sheet->setCellValue("A2", $dateStart . ' - ' . $dateEnd);
        $sheet->getStyle('A1:A2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:F1');
        $sheet->mergeCells('A2:F2');

        $sheet->setCellValue("A4", 'Total Penggunaan Listrik (KWh)');
        $sheet->setCellValue("B4", $data['pemkwh_309_kab']);
        $sheet->setCellValue("A5", 'RPPTL (Rp)');
        $sheet->setCellValue("B5", $data['rpptl_309_kab']);
        $sheet->setCellValue("A6", 'Angsuran TS (Rp)');
        $sheet->setCellValue("B6", $data['ts_sorek']);
        $sheet->setCellValue("A7", 'PPJ (Rp)');
        $sheet->setCellValue("B7", $data['ppj_sorek'] + $data['ppj_lpb']);
        $sheet->getStyle('A4:B7')->applyFromArray($borderStyle);

        $sheet->setCellValue("E4", 'Total Pelunasan PPJ');
        $sheet->setCellValue("F4", $data['ppj_total']);
        $sheet->setCellValue("E5", 'Total Tunggakan Listrik');
        $sheet->setCellValue("F5", $data['tagihan_pemda_ini']);
        $sheet->getStyle('E4:F5')->applyFromArray($borderStyle);

        $sheet->setCellValue("A9", "Top 5 Penggunaan Listrik (Kwh)");
        $sheet->getStyle("A9")->getFont()->setBold(true);
        $sheet->mergeCells('A9:B9');
        $idx = 0;
        $row = 10;
        foreach ($data['rank_goltarif'] as $rank) {
            if ($idx < 5) {
                $sheet->setCellValue("A$row", $rank['goltarif']);
                $sheet->setCellValue("B$row", $rank['pemkwh_sorek']);
                $row++;
                $idx++;
            }
        }
        $sheet->getStyle('A9:B' . ($row - 1))->applyFromArray($borderStyle);

        $total = $data['tot_ppj_taglis'] + $data['tot_ntl'] + $data['tot_lpb'] + $data['tot_res'] + $data['tot_ppj_koreksi_tambah'] + $data['tot_ppj_koreksi_kurang'];
        $sheet->setCellValue("E9", "Komponen PPJ");
        $sheet->getStyle("E9")->getFont()->setBold(true);
        $sheet->mergeCells("E9:F9");
        if ($total > 0) {
            $sheet->setCellValue("E10", "Tagihan Listrik");
            $sheet->setCellValue("F10", round((($data['tot_ppj_taglis'] / $total) * 100), 2, PHP_ROUND_HALF_DOWN) . "%");
            $sheet->setCellValue("E11", "Non Tagihan Listrik");
            $sheet->setCellValue("F11", round((($data['tot_ntl'] / $total) * 100), 2, PHP_ROUND_HALF_DOWN) . "%");
            $sheet->setCellValue("E12", "Prabayar");
            $sheet->setCellValue("F12", round((($data['tot_lpb'] / $total) * 100), 2, PHP_ROUND_HALF_DOWN) . "%");
            $sheet->setCellValue("E13", "Restitusi");
            $sheet->setCellValue("F13", round((($data['tot_res'] / $total) * 100), 2, PHP_ROUND_HALF_DOWN) . "%");
            $sheet->setCellValue("E14", "Koreksi Penambah");
            $sheet->setCellValue("F14", round((($data['tot_ppj_koreksi_tambah'] / $total) * 100), 2, PHP_ROUND_HALF_DOWN) . "%");
            $sheet->setCellValue("E15", "Koreksi Pengurang");
            $sheet->setCellValue("F15", round((($data['tot_ppj_koreksi_kurang'] / $total) * 100), 2, PHP_ROUND_HALF_DOWN) . "%");
        } else {
            $sheet->setCellValue("E10", "Tagihan Listrik");
            $sheet->setCellValue("F10", "0%");
            $sheet->setCellValue("E11", "Non Tagihan Listrik");
            $sheet->setCellValue("F11", "0%");
            $sheet->setCellValue("E12", "Prabayar");
            $sheet->setCellValue("F12", "0%");
            $sheet->setCellValue("E13", "Restitusi");
            $sheet->setCellValue("F13", "0%");
            $sheet->setCellValue("E14", "Koreksi Penambah");
            $sheet->setCellValue("F14", "0%");
            $sheet->setCellValue("E15", "Koreksi Pengurang");
            $sheet->setCellValue("F15", "0%");
        }
        $sheet->getStyle("E9:F15")->applyFromArray($borderStyle);

        $sheet->setCellValue("A17", "PPJ Tagihan Listrik");
        $sheet->getStyle("A17")->getFont()->setBold(true);
        $sheet->mergeCells("A17:C17");
        $sheet->setCellValue("B18", "PTL");
        $sheet->setCellValue("C18", "PPJ");
        $sheet->setCellValue("A19", "Offline");
        $sheet->setCellValue("B19", $data["pal_dph21"]);
        $sheet->setCellValue("C19", $data["ppj_dph21"]);
        $sheet->setCellValue("A20", "Online");
        $sheet->setCellValue("B20", $data["pal_dph22"]);
        $sheet->setCellValue("C20", $data["ppj_dph22"]);
        $sheet->setCellValue("A21", "Nota Buku");
        $sheet->setCellValue("B21", $data["pal_dph23"]);
        $sheet->setCellValue("C21", $data["ppj_dph23"]);
        $sheet->setCellValue("A22", "Beban Kantor");
        $sheet->setCellValue("B22", $data["pal_dph24"]);
        $sheet->setCellValue("C22", $data["ppj_dph24"]);
        $sheet->setCellValue("A23", "Legalisasi");
        $sheet->setCellValue("B23", $data["pal_dph25"]);
        $sheet->setCellValue("C23", $data["ppj_dph25"]);
        $sheet->getStyle("A17:C23")->applyFromArray($borderStyle);

        $sheet->setCellValue("D17", "Restitusi");
        $sheet->getStyle("D17")->getFont()->setBold(true);
        $sheet->mergeCells("D17:F17");
        $sheet->setCellValue("E18", "PTL");
        $sheet->setCellValue("F18", "PPJ");
        $sheet->setCellValue("D19", "Tagihan Listrik");
        $sheet->setCellValue("E19", $data["pal_res_taglis"]);
        $sheet->setCellValue("F19", $data["ppj_res_taglis"]);
        $sheet->setCellValue("D20", "Non Tagihan Listrik");
        $sheet->setCellValue("E20", $data["pal_res_nontaglis"]);
        $sheet->setCellValue("F20", $data["ppj_res_nontaglis"]);
        $sheet->setCellValue("D21", "Prabayar");
        $sheet->setCellValue("E21", $data["pal_res_pra"]);
        $sheet->setCellValue("F21", $data["ppj_res_pra"]);
        $sheet->getStyle("D17:F23")->applyFromArray($borderStyle);

        $sheet->setCellValue("A25", "PPJ Non Tagihan Listrik");
        $sheet->getStyle("A25")->getFont()->setBold(true);
        $sheet->mergeCells("A25:C25");
        $sheet->setCellValue("B26", "PTL");
        $sheet->setCellValue("C26", "PPJ");
        $sheet->setCellValue("A27", "Offline");
        $sheet->setCellValue("B27", $data["pal_offl"]);
        $sheet->setCellValue("C27", $data["ppj_offl"]);
        $sheet->setCellValue("A28", "Online");
        $sheet->setCellValue("B28", $data["pal_ntl"]);
        $sheet->setCellValue("C28", $data["ppj_ntl"]);
        $sheet->setCellValue("A29", "Nota Buku");
        $sheet->setCellValue("B29", $data["pal_nota"]);
        $sheet->setCellValue("C29", $data["ppj_nota"]);
        $sheet->getStyle("A25:C29")->applyFromArray($borderStyle);

        $sheet->setCellValue("D25", "PPJ Prabayar");
        $sheet->getStyle("D25")->getFont()->setBold(true);
        $sheet->mergeCells("D25:F25");
        $sheet->setCellValue("E26", "PTL");
        $sheet->setCellValue("F26", "PPJ");
        $sheet->setCellValue("D27", "Token");
        $sheet->setCellValue("E27", $data["pal_lpb"]);
        $sheet->setCellValue("F27", $data["ppj_lpb"]);
        $sheet->setCellValue("D28", "Komp. TMP");
        $sheet->setCellValue("E28", $data["pal_tmp_lpb"]);
        $sheet->setCellValue("F28", $data["ppj_tmp_lpb"]);
        $sheet->setCellValue("D29", "Komp. UJL");
        $sheet->setCellValue("E29", $data["pal_ujl_lpb"]);
        $sheet->setCellValue("F29", $data["ppj_ujl_lpb"]);
        $sheet->getStyle("D25:F29")->applyFromArray($borderStyle);


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

        $writer = new Xlsx($spreadsheet);
        $filename = "Rekap Laporan PPJ " . $nama . " - (" . $dateStart . " - " . $dateEnd . ").xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function excelRincian($params, $ppjHal1, $ppjHal2, $ppjPiutang, $nama)
    {
        $yearStart = substr($params['start'], 0, 4);
        $yearEnd = substr($params['end'], 0, 4);
        $monthStart = substr($params['start'], 4, 2);
        $monthEnd = substr($params['end'], 4, 2);
        $dateStart = DateHelper::formatFromDate($yearStart, $monthStart);
        $dateEnd = DateHelper::formatFromDate($yearEnd, $monthEnd);

        $spreadsheet = new Spreadsheet();

        #region PPJ Hal 1
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Hal 1');

        $sheet->setCellValue("A1", 'Rincian Laporan PPJ (Hal 1) ' . $nama);
        $sheet->setCellValue("A2", $dateStart . ' - ' . $dateEnd);

        $sheet->getStyle('A1:AI2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:AI1');
        $sheet->mergeCells('A2:AI2');

        $sheet->setCellValue("A4", 'No');
        $sheet->mergeCells('A4:A6');
        $sheet->setCellValue("B4", 'Nama Unit PLN');
        $sheet->mergeCells('B4:B6');
        $sheet->setCellValue("C4", 'Tarif');
        $sheet->mergeCells('C4:C6');
        $sheet->setCellValue("D4", 'Daya');
        $sheet->mergeCells('D4:D6');

        $sheet->setCellValue("E4", 'Rekening Tercetak');
        $sheet->mergeCells('E4:J5');
        $sheet->setCellValue("E6", 'Lembar');
        $sheet->setCellValue("F6", 'Jml Plg');
        $sheet->setCellValue("G6", 'Kwh');
        $sheet->setCellValue("H6", 'PTL');
        $sheet->setCellValue("I6", 'Angs TS');
        $sheet->setCellValue("J6", 'PPJ');

        $sheet->setCellValue("K4", 'PELUNASAN PPJ TAGIHAN LISTRIK');
        $sheet->mergeCells('K4:AI4');

        $sheet->setCellValue("K5", 'Pelunasan Online');
        $sheet->mergeCells('K5:N5');
        $sheet->setCellValue("K6", 'Lembar');
        $sheet->setCellValue("L6", 'PTL');
        $sheet->setCellValue("M6", 'Angs TS');
        $sheet->setCellValue("N6", 'PPJ');

        $sheet->setCellValue("O5", 'Pelunasan Offline');
        $sheet->mergeCells('O5:R5');
        $sheet->setCellValue("O6", 'Lembar');
        $sheet->setCellValue("P6", 'PTL');
        $sheet->setCellValue("Q6", 'Angs TS');
        $sheet->setCellValue("R6", 'PPJ');

        $sheet->setCellValue("S5", 'Pelunasan Nota Buku');
        $sheet->mergeCells('S5:V5');
        $sheet->setCellValue("S6", 'Lembar');
        $sheet->setCellValue("T6", 'PTL');
        $sheet->setCellValue("U6", 'Angs TS');
        $sheet->setCellValue("V6", 'PPJ');

        $sheet->setCellValue("W5", 'Pelunasan Beban Kantor');
        $sheet->mergeCells('W5:Z5');
        $sheet->setCellValue("W6", 'Lembar');
        $sheet->setCellValue("X6", 'PTL');
        $sheet->setCellValue("Y6", 'Angs TS');
        $sheet->setCellValue("Z6", 'PPJ');

        $sheet->setCellValue("AA5", 'Legalisasi');
        $sheet->mergeCells('AA5:AD5');
        $sheet->setCellValue("AA6", 'Lembar');
        $sheet->setCellValue("AB6", 'PTL');
        $sheet->setCellValue("AC6", 'Angs TS');
        $sheet->setCellValue("AD6", 'PPJ');

        $sheet->setCellValue("AE5", 'Total Pelunasan PPJ Taglis');
        $sheet->mergeCells('AE5:AI5');
        $sheet->setCellValue("AE6", 'Lembar');
        $sheet->setCellValue("AF6", 'Jml Plg');
        $sheet->setCellValue("AG6", 'PTL');
        $sheet->setCellValue("AH6", 'Angs TS');
        $sheet->setCellValue("AI6", 'PPJ');

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

        $row = 7;
        $idx = 1;
        foreach ($ppjHal1 as $item) {
            /** @var PpjHal1 $item */
            $sheet->setCellValue("A$row", $idx);
            $sheet->setCellValue("B$row", $item->namaunitpln);
            $sheet->setCellValue("C$row", $item->goltarif);
            $sheet->setCellValue("D$row", $item->daya);
            $sheet->setCellValue("E$row", $item->lbr_sorek);
            $sheet->setCellValue("F$row", $item->plg_sorek);
            $sheet->setCellValue("G$row", $item->pemkwh_sorek);
            $sheet->setCellValue("H$row", $item->pal_sorek);
            $sheet->setCellValue("I$row", $item->ts_sorek);
            $sheet->setCellValue("J$row", $item->ppj_sorek);
            $sheet->setCellValue("K$row", $item->lbr_dph21);
            $sheet->setCellValue("L$row", $item->pal_dph21);
            $sheet->setCellValue("M$row", $item->ts_dph21);
            $sheet->setCellValue("N$row", $item->ppj_dph21);
            $sheet->setCellValue("O$row", $item->lbr_dph22);
            $sheet->setCellValue("P$row", $item->pal_dph22);
            $sheet->setCellValue("Q$row", $item->ts_dph22);
            $sheet->setCellValue("R$row", $item->ppj_dph22);
            $sheet->setCellValue("S$row", $item->lbr_dph23);
            $sheet->setCellValue("T$row", $item->pal_dph23);
            $sheet->setCellValue("U$row", $item->ts_dph23);
            $sheet->setCellValue("V$row", $item->ppj_dph23);
            $sheet->setCellValue("W$row", $item->lbr_dph24);
            $sheet->setCellValue("X$row", $item->pal_dph24);
            $sheet->setCellValue("Y$row", $item->ts_dph24);
            $sheet->setCellValue("Z$row", $item->ppj_dph24);
            $sheet->setCellValue("AA$row", $item->lbr_dph25);
            $sheet->setCellValue("AB$row", $item->pal_dph25);
            $sheet->setCellValue("AC$row", $item->ts_dph25);
            $sheet->setCellValue("AD$row", $item->ppj_dph25);
            $sheet->setCellValue("AE$row", $item->lbr_taglis);
            $sheet->setCellValue("AF$row", $item->plg_taglis);
            $sheet->setCellValue("AG$row", $item->pal_taglis);
            $sheet->setCellValue("AH$row", $item->ts_taglis);
            $sheet->setCellValue("AI$row", $item->ppj_taglis);

            $idx++;
            $row++;
        }

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

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

        #region PPJ Hal 2
        $sheet = $spreadsheet->createSheet(1);
        $sheet->setTitle('Hal 2');

        $sheet->setCellValue("A1", 'Rincian Laporan PPJ (Hal 2) ' . $nama);
        $sheet->setCellValue("A2", $dateStart . ' - ' . $dateEnd);

        $sheet->getStyle('A1:AV2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:AV1');
        $sheet->mergeCells('A2:AV2');

        $sheet->setCellValue("A4", 'No');
        $sheet->mergeCells('A4:A6');
        $sheet->setCellValue("B4", 'Nama Unit PLN');
        $sheet->mergeCells('B4:B6');
        $sheet->setCellValue("C4", 'Tarif');
        $sheet->mergeCells('C4:C6');
        $sheet->setCellValue("D4", 'Daya');
        $sheet->mergeCells('D4:D6');

        $sheet->setCellValue("E4", 'Total Pelunasan PPJ Taglis');
        $sheet->mergeCells('E4:I5');
        $sheet->setCellValue("E6", 'Lembar');
        $sheet->setCellValue("F6", 'Jml Plg');
        $sheet->setCellValue("G6", 'PTL');
        $sheet->setCellValue("H6", 'Angs TS');
        $sheet->setCellValue("I6", 'PPJ');

        $sheet->setCellValue("J4", 'Restitusi');
        $sheet->mergeCells('J4:U4');

        $sheet->setCellValue("J5", 'Taglis');
        $sheet->mergeCells('J5:M5');
        $sheet->setCellValue("J6", 'Jml Trans');
        $sheet->setCellValue("K6", 'Jml Plg');
        $sheet->setCellValue("L6", 'PTL');
        $sheet->setCellValue("M6", 'PPJ');

        $sheet->setCellValue("N5", 'Non Taglis');
        $sheet->mergeCells('N5:Q5');
        $sheet->setCellValue("N6", 'Jml Trans');
        $sheet->setCellValue("O6", 'Jml Plg');
        $sheet->setCellValue("P6", 'PTL');
        $sheet->setCellValue("Q6", 'PPJ');

        $sheet->setCellValue("R5", 'Restitusi Prabayar');
        $sheet->mergeCells('R5:U5');
        $sheet->setCellValue("R6", 'Jml Trans');
        $sheet->setCellValue("S6", 'Jml Plg');
        $sheet->setCellValue("T6", 'PTL');
        $sheet->setCellValue("U6", 'PPJ');

        $sheet->setCellValue("V4", 'Pelunasan PPJ Non Taglis');
        $sheet->mergeCells('V4:AG4');

        $sheet->setCellValue("V5", 'Online');
        $sheet->mergeCells('V5:Y5');
        $sheet->setCellValue("V6", 'Jml Trans');
        $sheet->setCellValue("W6", 'Jml Plg');
        $sheet->setCellValue("X6", 'PTL');
        $sheet->setCellValue("Y6", 'PPJ');

        $sheet->setCellValue("Z5", 'Offline');
        $sheet->mergeCells('Z5:AC5');
        $sheet->setCellValue("Z6", 'Jml Trans');
        $sheet->setCellValue("AA6", 'Jml Plg');
        $sheet->setCellValue("AB6", 'PTL');
        $sheet->setCellValue("AC6", 'PPJ');

        $sheet->setCellValue("AD5", 'Nota Buku');
        $sheet->mergeCells('AD5:AG5');
        $sheet->setCellValue("AD6", 'Jml Trans');
        $sheet->setCellValue("AE6", 'Jml Plg');
        $sheet->setCellValue("AF6", 'PTL');
        $sheet->setCellValue("AG6", 'PPJ');

        $sheet->setCellValue("AH4", 'Pelunasan PPJ PraBayar');
        $sheet->mergeCells('AH4:AS4');

        $sheet->setCellValue("AH5", 'Token Tunai (Online)');
        $sheet->mergeCells('AH5:AK5');
        $sheet->setCellValue("AH6", 'Jml Trans');
        $sheet->setCellValue("AI6", 'Jml Plg');
        $sheet->setCellValue("AJ6", 'PTL');
        $sheet->setCellValue("AK6", 'PPJ');

        $sheet->setCellValue("AL5", 'Kompensasi TMP');
        $sheet->mergeCells('AL5:AO5');
        $sheet->setCellValue("AL6", 'Jml Trans');
        $sheet->setCellValue("AM6", 'Jml Plg');
        $sheet->setCellValue("AN6", 'PTL');
        $sheet->setCellValue("AO6", 'PPJ');

        $sheet->setCellValue("AP5", 'Kompensasi UJL');
        $sheet->mergeCells('AP5:AS5');
        $sheet->setCellValue("AP6", 'Jml Trans');
        $sheet->setCellValue("AQ6", 'Jml Plg');
        $sheet->setCellValue("AR6", 'PTL');
        $sheet->setCellValue("AS6", 'PPJ');

        $sheet->setCellValue("AT4", 'Koreksi Penambah');
        $sheet->mergeCells('AT4:AT6');

        $sheet->setCellValue("AU4", 'Koreksi Pengurang');
        $sheet->mergeCells('AU4:AU6');

        $sheet->setCellValue("AV4", 'Total Pelunasan PPJ');
        $sheet->mergeCells('AV4:AV6');

        $sheet->getColumnDimension('AT')->setAutoSize(true);
        $sheet->getColumnDimension('AU')->setAutoSize(true);
        $sheet->getColumnDimension('AV')->setAutoSize(true);

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

        $row = 7;
        $idx = 1;
        foreach ($ppjHal2 as $item) {
            /** @var PpjHal2 $item */
            $sheet->setCellValue("A$row", $idx);
            $sheet->setCellValue("B$row", $item->namaunitpln);
            $sheet->setCellValue("C$row", $item->goltarif);
            $sheet->setCellValue("D$row", $item->daya);
            $sheet->setCellValue("E$row", $item->lbr_taglis);
            $sheet->setCellValue("F$row", $item->plg_taglis);
            $sheet->setCellValue("G$row", $item->pal_taglis);
            $sheet->setCellValue("H$row", $item->ts_taglis);
            $sheet->setCellValue("I$row", $item->ppj_taglis);
            $sheet->setCellValue("J$row", $item->lbr_res_taglis);
            $sheet->setCellValue("K$row", $item->plg_res_taglis);
            $sheet->setCellValue("L$row", $item->pal_res_taglis);
            $sheet->setCellValue("M$row", $item->ppj_res_taglis);
            $sheet->setCellValue("N$row", $item->lbr_res_nontaglis);
            $sheet->setCellValue("O$row", $item->plg_res_nontaglis);
            $sheet->setCellValue("P$row", $item->pal_res_nontaglis);
            $sheet->setCellValue("Q$row", $item->ppj_res_nontaglis);
            $sheet->setCellValue("R$row", $item->lbr_res_pra);
            $sheet->setCellValue("S$row", $item->plg_res_pra);
            $sheet->setCellValue("T$row", $item->pal_res_pra);
            $sheet->setCellValue("U$row", $item->ppj_res_pra);
            $sheet->setCellValue("V$row", $item->lbr_ntl);
            $sheet->setCellValue("W$row", $item->plg_ntl);
            $sheet->setCellValue("X$row", $item->pal_ntl);
            $sheet->setCellValue("Y$row", $item->ppj_ntl);
            $sheet->setCellValue("Z$row", $item->lbr_offl);
            $sheet->setCellValue("AA$row", $item->plg_offl);
            $sheet->setCellValue("AB$row", $item->pal_offl);
            $sheet->setCellValue("AC$row", $item->ppj_offl);
            $sheet->setCellValue("AD$row", $item->lbr_nota);
            $sheet->setCellValue("AE$row", $item->plg_nota);
            $sheet->setCellValue("AF$row", $item->pal_nota);
            $sheet->setCellValue("AG$row", $item->ppj_nota);
            $sheet->setCellValue("AH$row", $item->lbr_lpb);
            $sheet->setCellValue("AI$row", $item->plg_lpb);
            $sheet->setCellValue("AJ$row", $item->pal_lpb);
            $sheet->setCellValue("AK$row", $item->ppj_lpb);
            $sheet->setCellValue("AL$row", $item->lbr_tmp_lpb);
            $sheet->setCellValue("AM$row", $item->plg_tmp_lpb);
            $sheet->setCellValue("AN$row", $item->pal_tmp_lpb);
            $sheet->setCellValue("AO$row", $item->ppj_tmp_lpb);
            $sheet->setCellValue("AP$row", $item->lbr_ujl_lpb);
            $sheet->setCellValue("AQ$row", $item->plg_ujl_lpb);
            $sheet->setCellValue("AR$row", $item->pal_ujl_lpb);
            $sheet->setCellValue("AS$row", $item->ppj_ujl_lpb);
            $sheet->setCellValue("AT$row", $item->ppj_koreksi_tambah);
            $sheet->setCellValue("AU$row", $item->ppj_koreksi_kurang);
            $sheet->setCellValue("AV$row", $item->ppj_total);

            $idx++;
            $row++;
        }

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

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

        #region PPJ Piutang Pemda
        $sheet = $spreadsheet->createSheet(2);
        $sheet->setTitle('Piutang Pemda');

        $sheet->setCellValue("A1", 'Rincian Laporan Piutang ' . $nama);
        $sheet->setCellValue("A2", $dateStart . ' - ' . $dateEnd);

        $sheet->getStyle('A1:L2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:L1');
        $sheet->mergeCells('A2:L2');

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Nama Unit PLN');
        $sheet->setCellValue("C4", 'Tagihan Pemda');
        $sheet->setCellValue("D4", 'Setoran PPJ');
        $sheet->setCellValue("E4", 'Tagihan Pemda Kumulatif');
        $sheet->setCellValue("F4", 'Setoran PPJ Kumulatif');
        $sheet->setCellValue("G4", 'Saldo akhir laporan 404');
        $sheet->setCellValue("H4", 'Saldo laporan 404 baru tagihan listrik');
        $sheet->setCellValue("I4", 'Saldo laporan 404 sedang berjalan');
        $sheet->setCellValue("J4", 'Saldo laporan 404 tertunggak');
        $sheet->setCellValue("K4", 'Rupiah ptl laporan 309 kabupaten');
        $sheet->setCellValue("L4", 'Kwh laporan 309 kabupaten');

        $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->getStyle('A4:L4')
            ->getAlignment()
            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

        $row = 5;
        $idx = 1;
        foreach ($ppjPiutang as $item) {
            /** @var PpjPiutangPemda $item */
            $sheet->setCellValue("A$row", $idx);
            $sheet->setCellValue("B$row", $item->namaunitpln);
            $sheet->setCellValue("C$row", $item->tagihan_pemda_ini);
            $sheet->setCellValue("D$row", $item->setoran_ppj_ini);
            $sheet->setCellValue("E$row", $item->tagihan_pemda_kum);
            $sheet->setCellValue("F$row", $item->setoran_ppj_kum);
            $sheet->setCellValue("G$row", $item->saldo_akhir_404);
            $sheet->setCellValue("H$row", $item->saldo_404_baru_taglis);
            $sheet->setCellValue("I$row", $item->saldo_404_berjalan);
            $sheet->setCellValue("J$row", $item->saldo_404_tunggakan);
            $sheet->setCellValue("K$row", $item->rpptl_309_kab);
            $sheet->setCellValue("L$row", $item->pemkwh_309_kab);

            $idx++;
            $row++;
        }

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

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

        $writer = new Xlsx($spreadsheet);
        $filename = "Rincian Laporan PPJ " . $nama . " - (" . $dateStart . " - " . $dateEnd . ").xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function validateRequestAdminPemda()
    {
        $this->hasScopes(['korsupgah.view']);
        $params = $this->request->get();

        $current_instansi = $this->shared->user->instansi;

        if ($this->hasScopes(['korsupgah.admin'])) {
            $validatedParam = $this->validate(
                $params,
                [
                    'instansi_id' => ['validators' => ['required', 'digit']],
                ]
            );

            $current_instansi = Instansi::findFirst($validatedParam['instansi_id']);
            if (empty($current_instansi)) {
                throw new ValidationException("Instansi tidak ditemukan!");
            }

            $current_instansi->toSimpleArray(['id', 'nama', 'id_tipe', 'deleted_at', 'id_kota_kabupaten']);
        }

        if (empty($current_instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki instansi');
        }

        if (!empty($current_instansi->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi user telah dihapus/tidak aktif');
        }

        if (empty($current_instansi->id_kota_kabupaten)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki instansi Pemda Tingkat 2 (Kota/Kabupaten)');
        }

        $this->validate(
            $params,
            [
                'start' => ['validators' => ['required', 'digit']],
                'end' => ['validators' => ['required', 'digit']],
            ]
        );

        $start = (int) $params['start'];
        $end = (int) $params['end'];
        $this->validate(
            ['start' => $start, 'end' => $end],
            [
                'start' => ['validators' => [['name' => 'max', 'params' => $end]]],
                'end' => ['validators' => [['name' => 'min', 'params' => $end]]]
            ]
        );

        return $current_instansi;
    }

    private function validateRequestUnitInduk()
    {
        if (!$this->hasScopes(['ppj.monitoring.unitinduk'])) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }

        $userUnits = PpjUserUnit::find('um_id = ' . $this->shared->user->um_id)->toArray();
        if (empty($userUnits)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki Unit Induk');
        }
        $unitIds = array();
        foreach ($userUnits as $userUnit) {
            array_push($unitIds, $userUnit['unit_id']);
        }
        $units = PpjUnitInduk::find('id IN (' . implode($unitIds, ",") . ')')->toArray();
        if (empty($units)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Unit Induk tidak ditemukan');
        }
        $unitNamas = array();
        foreach ($units as $unit) {
            array_push($unitNamas, $unit['nama']);
        }

        $params = $this->request->get();
        $this->validate(
            $params,
            [
                'start' => ['validators' => ['required', 'digit']],
                'end' => ['validators' => ['required', 'digit']],
            ]
        );

        $start = (int) $params['start'];
        $end = (int) $params['end'];
        $this->validate(
            ['start' => $start, 'end' => $end],
            [
                'start' => ['validators' => [['name' => 'max', 'params' => $end]]],
                'end' => ['validators' => [['name' => 'min', 'params' => $end]]]
            ]
        );

        return $unitNamas;
    }

    private function periodToString($period)
    {
        $datetime = DateTime::createFromFormat('!Ym', $period);
        return DateHelper::convertMonthDigittoString($datetime->format('n')) . " " . $datetime->format('Y');
    }

    public function instansiList()
    {
        $this->checkIsAuthenticated();
        $this->hasScopes(['korsupgah.admin']);

        $instansiQuery = "
            SELECT id, nama, id_tipe FROM jaga.instansi WHERE id_tipe = 2 AND deleted_at IS NULL ORDER BY nama;
        ";

        $instansiList = $this->rawQuery($instansiQuery);
        $output = [
            'result' => $instansiList,
        ];

        return new SimpleResponse($output);
    }

    private function getKotaKabupatenId()
    {
        $params = $this->request->get();
        $validatedParam = $this->validate(
            $params,
            [
                'instansi_id' => ['validators' => ['required', 'digit']],
            ]
        );

        $instansi = Instansi::findFirst($validatedParam['instansi_id']);
        if (empty($instansi)) {
            throw new ValidationException("Instansi tidak ditemukan!");
        }

        $instansi = $instansi->toSimpleArray(['id', 'id_kota_kabupaten']);

        return $instansi['id_kota_kabupaten'];
    }
}