<?php

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

class DesaController extends BaseController
{
    public function listProvinsi()
    {
        $listProv = array_map(
            function ($v) {
                return $v['provinsi'];
            },
            OmspanMasterDesa::find(
                [
                    "columns" => "distinct(nama_provinsi) as provinsi",
                    "order" => "provinsi asc"
                ]
            )
                ->toArray()
        );
        $totalCount = count($listProv);
        return new PaginatedResponse($listProv, $totalCount, $totalCount, 0);
    }

    public function listProvinsiOmspan() {
        $result = OmspanMasterProvinsi::find();
        $totalCount = count($result);
        return new PaginatedResponse($result, $totalCount, $totalCount, 0);
    }

    public function listKabupaten() {
        $params = $this->request->get();
        $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 100;
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";

        $validatedRequest = $this->validate($params, [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
            'keyword' => ['validators' => [[
                'name' => 'min_string',
                'params' => 3
            ]]]
        ]);
        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];
        $keyword = '%' . str_replace(' ', '', $validatedRequest['keyword']) . '%';

        $query = "WITH KabKota AS (
                    SELECT
                        nm_lokasi as kabupaten_kota,
                        regexp_replace(nm_lokasi, '\\s', '', 'g') AS kabupaten_kota_cl
                    FROM
                        omspan_master_pemda
                )

                SELECT kabupaten_kota FROM KabKota WHERE kabupaten_kota_cl ILIKE :keyword LIMIT :limit OFFSET :offset
        ";
        $bind = [
            "keyword" => $keyword,
            "limit" => $limit,
            "offset" => $offset
        ];
        $rows = $this->rawQuery($query, $bind);

        return new CollectionPaginatedResponse($rows);
    }

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

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['nama'] = array_key_exists('nama', $params) ? $params['nama'] : "";
        $params['provinsi'] = array_key_exists('provinsi', $params) ? $params['provinsi'] : "";
        $params['kota'] = array_key_exists('kota', $params) ? $params['kota'] : "";
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : date('Y');
        $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : "nama_desa";
        $params['order_direction'] = array_key_exists('order_direction', $params) ? $params['order_direction'] : "asc";

        $allowedOrder = ['kode_desa', 'nama_desa', 'nama_pemda', 'nama_provinsi', 'nama_kecamatan'];
        $allowedDirection = ['asc', 'desc'];

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

        $strWhere = "";
        $countWhere = "";
        $bind = [];
        if (strlen($params["provinsi"]) > 0) {
            $strWhere = "lower(d.nama_provinsi) = :provinsi";
            $countWhere = "lower(nama_provinsi) = :provinsi:";
            $bind["provinsi"] = strtolower($params["provinsi"]);
        }

        if (strlen($params["kota"]) > 0) {
            if (strlen($strWhere) > 0)
                $strWhere .= " and ";
            if (strlen($countWhere) > 0)
                $countWhere .= " and ";
            $strWhere .= "lower(d.nama_pemda) like :kota";
            $countWhere .= "lower(nama_pemda) like :kota:";
            $bind["kota"] = "%" . strtolower($params["kota"]) . "%";
        }

        if (strlen($params["nama"]) > 0) {
            if (strlen($strWhere) > 0)
                $strWhere .= " and ";
            if (strlen($countWhere) > 0)
                $countWhere .= " and ";
            $strWhere .= "lower(d.nama_desa) like :nama";
            $countWhere .= "lower(nama_desa) like :nama:";
            $bind["nama"] = CommonTools::getDummyFuzzyKeyword($params["nama"]);
        }

        $totalCount = OmspanMasterDesa::count(
            [
                $countWhere,
                "bind" => $bind
            ]
        );

        if ($strWhere != "") {
            $strWhere = " where " . $strWhere;
        }
        $query =
            "select d.kode_desa as id, d.nama_desa as nama, d.nama_pemda as kabupaten_kota, d.nama_provinsi as provinsi, d.nama_kecamatan as kecamatan, pagu.pagu, sum(penyaluran.realisasi) as penyaluran, djp.jumlah_penduduk, djp.jumlah_kk " .
            "from jaga.omspan_master_desa d " .
            "left join jaga.omspan_dana_desa_pagu pagu ON pagu.kode_desa = d.kode_desa AND pagu.tahun = " . $params['tahun'] . " " .
            "left join jaga.omspan_dana_desa_penyaluran penyaluran ON penyaluran.kode_desa = d.kode_desa AND penyaluran.tahun = " . $params['tahun'] . " " .
            "left join jaga.desa_jumlah_penduduk djp ON djp.\"kode_PUM\" = d.kode_desa" .
            $strWhere . " group by d.id, pagu.id, djp.\"kode_PUM\" order by " . "lower(d." . $validatedRequest['order_by'] . ") " . $validatedRequest['order_direction'] . " limit " . $params['limit'] . " offset " . $params['offset'];

        $listDesa = $this->rawQuery($query, $bind);

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

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

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['nama'] = array_key_exists('nama', $params) ? $params['nama'] : "";
        $params['provinsi'] = array_key_exists('provinsi', $params) ? $params['provinsi'] : "";
        $params['kota'] = array_key_exists('kota', $params) ? $params['kota'] : "";
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : date('Y');
        $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : "nama_desa";
        $params['order_direction'] = array_key_exists('order_direction', $params) ? $params['order_direction'] : "asc";

        $allowedOrder = ['kode_desa', 'nama_desa', 'kabupaten_kota', 'provinsi'];
        $allowedDirection = ['asc', 'desc'];

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

        $strWhere = "pagu.tahun = :tahun and pagu.pagu > 0";
        $bind = [
            "tahun" => $validatedRequest['tahun']
        ];
        if (strlen($params["provinsi"]) > 0) {
            $strWhere = " and lower(provinsi.nm_prov) = :provinsi";
            $bind["provinsi"] = strtolower($params["provinsi"]);
        }

        if (strlen($params["kota"]) > 0) {
            $strWhere .= " and lower(pemda.nm_lokasi) like :kota";
            $bind["kota"] = "%" . strtolower($params["kota"]) . "%";
        }

        if (strlen($params["nama"]) > 0) {
            $strWhere .= " and pagu.nama_desa ilike :nama";
            $bind["nama"] = '%' . $params["nama"] . '%';
        }

        if ($strWhere != "") {
            $strWhere = " where " . $strWhere;
        }

        $queryCount = "SELECT COUNT(pagu.id) as total" .
            " FROM jaga.omspan_dana_desa_pagu pagu
            LEFT JOIN jaga.omspan_master_pemda pemda ON pemda.kd_lokasi = pagu.kode_lokasi
            LEFT JOIN jaga.omspan_master_provinsi provinsi ON provinsi.kd_prov = pemda.kd_prov " .
            $strWhere;
        $totalCount = $this->rawQuery($queryCount, $bind)[0]['total'];

        $query =
            "SELECT pagu.kode_desa as id, pagu.nama_desa as nama, pagu.tahun, pagu.status_desa, (coalesce(pagu.pagu,0) + coalesce(pagu.pagu_tambahan,0)) as pagu, pagu.updated_at,pagu.kecamatan, pemda.nm_lokasi AS kabupaten_kota,provinsi.nm_prov AS provinsi,sum(penyaluran.realisasi) AS penyaluran,djp.jumlah_penduduk,djp.jumlah_kk" .
            " FROM jaga.omspan_dana_desa_pagu pagu
            LEFT JOIN jaga.omspan_master_pemda pemda ON pemda.kd_lokasi = pagu.kode_lokasi
            LEFT JOIN jaga.omspan_master_provinsi provinsi ON provinsi.kd_prov = pemda.kd_prov
            LEFT JOIN jaga.omspan_dana_desa_penyaluran penyaluran ON penyaluran.kode_desa = pagu.kode_desa AND penyaluran.tahun = pagu.tahun
            LEFT JOIN jaga.desa_jumlah_penduduk djp ON djp.\"kode_PUM\" = pagu.kode_desa " .
            $strWhere .
            " GROUP BY pagu.id, pemda.kd_lokasi, provinsi.kd_prov, djp.\"kode_PUM\"" .
            " ORDER BY " . "lower(pagu." . $validatedRequest['order_by'] . ") " . $validatedRequest['order_direction'] .
            " LIMIT " . $params['limit'] .
            " OFFSET " . $params['offset'];
        $listDesa = $this->rawQuery($query, $bind);

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

    public function searchNama() {
        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['nama'] = array_key_exists('nama', $params) ? $params['nama'] : "";
        $params['provinsi'] = array_key_exists('provinsi', $params) ? $params['provinsi'] : "";
        $params['kota'] = array_key_exists('kota', $params) ? $params['kota'] : "";
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : date('Y');

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

        $strWhere = "tahun = :tahun and pagu > 0";
        $bind = [
            "tahun" => $validatedRequest['tahun']
        ];
        if (strlen($params["provinsi"]) > 0) {
            $strWhere = " and lower(provinsi.nm_prov) = :provinsi";
            $bind["provinsi"] = strtolower($params["provinsi"]);
        }

        if (strlen($params["kota"]) > 0) {
            $strWhere .= " and lower(pemda.nm_lokasi) like :kota";
            $bind["kota"] = "%" . strtolower($params["kota"]) . "%";
        }

        if (strlen($params["nama"]) > 0) {
            $strWhere .= " and nama_desa ilike :nama";
            $bind["nama"] = '%' . $params["nama"] . '%';
        }

        if ($strWhere != "") {
            $strWhere = " where " . $strWhere;
        }

        $query =
            "SELECT pagu.kode_desa as id, pagu.nama_desa as nama, pagu.kecamatan" .
            " FROM jaga.omspan_dana_desa_pagu pagu
            LEFT JOIN jaga.omspan_master_pemda pemda ON pemda.kd_lokasi = pagu.kode_lokasi
            LEFT JOIN jaga.omspan_master_provinsi provinsi ON provinsi.kd_prov = pemda.kd_prov " .
            $strWhere .
            " LIMIT " . $params['limit'] .
            " OFFSET " . $params['offset'];
        $result = $this->rawQuery($query, $bind);

        return new CollectionPaginatedResponse($result);
    }

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

        $result = OmspanMasterDesa::find(
            [
                "conditions" => "kode_desa = :id:",
                "bind" => ["id" => $id],
                "columns" => "kode_desa as id, nama_desa as nama, nama_pemda as kabupaten_kota, nama_provinsi as provinsi, nama_kecamatan as kecamatan"
            ]
        );

        return new SingleResponse($result, "Desa");
    }

    public function detailPenduduk($id)
    {
        $row = OmspanMasterDesa::findFirstByKodeDesa($id);
        if($row) {
            $result = $row->toDetail();
        } else {
            $row = OmspanDanaDesaPagu::findFirstByKodeDesa($id);
            if ($row) {
                $result = $row->toDetailDesa();
            } else {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data tidak ditemukan!");
            }
        }

        return new CollectionPaginatedResponse($result);
    }

    public function profile()
    {
        $params = $this->request->get();
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : date('Y');
        $validatedRequest = $this->validate(
            $params,
            [
                'kode' => ['validators' => ['required']],
                'tahun' => ['validators' => ['digit']],
            ]
        );

        $desaService = new DesaService($this);
        $res = $desaService->profile($validatedRequest["kode"], $validatedRequest["tahun"]);
        return new PaginatedResponse([$res], 1, 1, 0);
    }

    public function detilAnggaran()
    {
        $params = $this->request->get();
        $params['blt'] = array_key_exists('blt', $params) ? $params['blt'] : 0;
        $anggaranKumulatif = array_key_exists('anggaranKumulatif', $params) ? $params['anggaranKumulatif'] : 0;

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

        $desa = OmspanMasterDesa::findFirst(
            [
                "kode_desa = :kode:",
                "bind" => [
                    "kode" => $params["kode"]
                ]
            ]
        );

        if (!$desa) {
            $desaPagu = OmspanDanaDesaPagu::findFirstByKodeDesa($params["kode"]);
            if (!$desaPagu) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Desa not found');
            }
            $desa = OmspanMasterDesa::modelFromOmspanDanaDesaPagu($desaPagu);
        }

        if ($anggaranKumulatif) {
            $res = $desa->rincianAnggaran($params["tahun"]);
        } else {
            $res = $desa->jsonDetilAnggaran($params["tahun"], boolval($validatedRequest['blt']));
        }
        if (!$res) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, 'Data Tidak Tersedia');
        }
        return new PaginatedResponse([$res], 1, 1, 0);
    }

    public function anggaranNasional() {
        $anggaran = $this->rawQuery("
            SELECT COALESCE(a.tahun, b.tahun) as tahun, a.pagu, b.penyaluran
            FROM (SELECT tahun, sum(pagu) as pagu FROM jaga.omspan_dana_desa_pagu GROUP BY tahun) a
            FULL JOIN (SELECT tahun, sum(realisasi) as penyaluran FROM jaga.omspan_dana_desa_penyaluran GROUP BY tahun) b ON a.tahun = b.tahun
            WHERE a.tahun IS NOT NULL OR b.tahun IS NOT NULL
            ORDER BY tahun;
        ");

        $kesejahteraan = DesaKesejahteraan::find([
            'columns' => "SUM(pra_sejahtera) as pra_sejahtera,
                        SUM(sejahtera_1) as sejahtera_1,
                        SUM(sejahtera_2) as sejahtera_2,
                        SUM(sejahtera_3) as sejahtera_3,
                        SUM(sejahtera_3_plus) as sejahtera_3_plus,
                        SUM(jumlah_kk) as jumlah_kk"
        ]);
        $result = array(
            "anggaran" => $anggaran,
            "kesejahteraan" => $kesejahteraan[0]
        );

        return new SimpleResponse($result);
    }

    #region IDM Kemendesa
    public function idmListProvinsi() {
        $this->checkScopes(['admin.view']);
        $params = $this->request->get();
        $this->validate($params, [
            'tahun' => ['validators' => ['required', 'digit']],
            'nama' => ['validators' => [['name' => 'max_string', 'params' => 100]]]
        ]);

        $conditions = "tahun = :tahun:";
        $bind = [
            "tahun" => $params['tahun']
        ];

        if (!empty($params['nama'])) {
            $conditions .= " AND nama ILIKE :nama:";
            $bind['nama'] = '%' . $params['nama'] . '%';
        }

        $result = KemendesIdmProvinsi::find([
            'columns' => 'id, id_prov, nama',
            'conditions' => $conditions,
            'bind' => $bind,
            'order' => 'nama asc'
        ]);

        return new SimpleResponse($result);
    }

    public function idmStatus() {
        return new SimpleResponse(KemendesClient::IDM_STATUS);
    }

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

        // params tahun 2020 - now
        $allowedYears = [];
        $currentYear = 2020;
        while ($currentYear <= date("Y")) {
            array_push($allowedYears, $currentYear);
            $currentYear++;
        }
        $validatedRequest = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit', ['name' => 'inclusion_in', 'params' => $allowedYears]]],
                'kode' => ['validators' => ['required', 'digit']]
            ]
        );

        try {
            $client = new KemendesClient($this->config["kemendes_prop"]["idm"]);
            $response = $client->idmRumusanDesa($validatedRequest['kode'], $validatedRequest['tahun']);
            if ($response && !$response['error'] && array_key_exists('mapData', $response)) {
                $result = $response['mapData'];
            } elseif (is_null($response)) {
                $result = [
                    "SUMMARIES" => [
                        "STATUS" => "-",
                        "PENAMBAHAN" => 0,
                        "SKOR_SAAT_INI" => 0
                    ],
                ];
            } else {
                $message = $response['message'] ?: "Gagal menghubungi server Kemendesa. Silakan coba beberapa saat lagi.";
                throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $message);
            }

            return new SimpleResponse($result);
        } catch (\CustomErrorException $e) {
            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessages());
        }  catch(\Exception $e) {
            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessage());
        }
    }

    public function idmDatatableDesa()
    {
        $this->checkIsAuthenticated();
        $params = $this->request->get();
        $params['nama_prov'] = array_key_exists('nama_prov', $params) ? $params['nama_prov'] : null;
        $params['nama_kab'] = array_key_exists('nama_kab', $params) ? $params['nama_kab'] : null;
        $params['kode'] = array_key_exists('kode', $params) ? $params['kode'] : null;
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $validatedRequest = $this->validate(
            $params,
            [
                'nama_prov' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'nama_kab' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'kode' => ['validators' => ['digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );
        $namaProv = $validatedRequest['nama_prov'];
        $namaKab = $validatedRequest['nama_kab'];
        $kode = $validatedRequest['kode'];
        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];
        try {
            $client = new KemendesClient($this->config["kemendes_prop"]["idm"]);
            $response = $client->idmDatatablesDesa($offset, $limit, $namaProv, $namaKab, $kode);
            if (array_key_exists('aaData', $response) && array_key_exists('iTotalDisplayRecords', $response) && is_array($response['aaData'])) {
                $result = $response['aaData'];
                $totalData = $response['iTotalDisplayRecords'];

                return new PaginatedResponse($result, $totalData, $limit, $offset);
            } else {
                throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, "Gagal menghubungi server Kemendesa. Silakan coba beberapa saat lagi.");
            }
        } catch(\Exception $e) {
            if ($e instanceof CustomErrorException) {
                throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessages());
            } else {
                throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessage());
            }
        }
    }

    private function baseIdmDatatableProvinsi($params) {
        $params['live_data'] = array_key_exists('live_data', $params) ? $params['live_data'] : 0;
        $this->validate($params, [
            'live_data' => ['validators' => [['name' => 'inclusion_in', 'params' => [0,1]]]]
        ]);

        $result = [];
        if ($params['live_data']) {
            // mengambil data langsung dari API Kemendesa
            try {
                $client = new KemendesClient($this->config["kemendes_prop"]["idm"]);
                $response = $client->idmDatatablesProvinsi();
                if (array_key_exists('aaData', $response) && array_key_exists('iTotalDisplayRecords', $response) && is_array($response['aaData'])) {
                    $res = $response['aaData'];
                    foreach ($res as $item) {
                        $tmp = $item;
                        $tmp['CURRENT_YEAR'] = [];
                        if ($item['STATUS_CURRENT_YEAR']) {
                            $current = explode('|', $item['STATUS_CURRENT_YEAR']);
                            $tmp['CURRENT_YEAR'] = [
                                'YEAR' => $current[0],
                                'INDEX' => $current[1],
                                'STATUS' => $current[2],
                                'IKE' => $current[3],
                                'IKL' => $current[4],
                                'IKS' => $current[5],
                                'DESA' => $current[6]
                            ];
                        }
                        $tmp['LAST_YEAR'] = [];
                        if ($item['STATUS_LAST_YEAR']) {
                            $last = explode('|', $item['STATUS_LAST_YEAR']);
                            $tmp['LAST_YEAR'] = [
                                'YEAR' => $last[0],
                                'INDEX' => $last[1],
                                'STATUS' => $last[2],
                                'IKE' => $last[3],
                                'IKL' => $last[4],
                                'IKS' => $last[5],
                                'DESA' => $last[6]
                            ];
                        }
                        $result[] = $tmp;
                    }
                    return $result;
                } else {
                    throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, "Gagal menghubungi server Kemendesa. Silakan coba beberapa saat lagi.");
                }
            } catch(\Exception $e) {
                if ($e instanceof CustomErrorException) {
                    throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessages());
                } else {
                    throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessage());
                }
            }
        } else {
            // mengambil data dari database
            $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : 'ranking';
            $params['order_direction'] = array_key_exists('order_direction', $params) ? $params['order_direction'] : 'asc';
            $params = $this->validate($params, [
                'tahun' => ['validators' => ['required', 'digit']],
                'status' => ['validators' => [['name' => 'inclusion_in', 'params' => array_merge([null], KemendesClient::IDM_STATUS)]]],
                'keyword' => ['validators' => [['name' => 'max_string', 'params' => 100]]],
                'order_by' => ['validators' => [['name' => 'inclusion_in', 'params' => ['ranking', 'nama', 'total_desa', 'tahun']]]],
                'order_direction' => ['validators' => [['name' => 'inclusion_in', 'params' => ['asc', 'desc']]]]
            ]);

            $conditions = "nama ILIKE :keyword: AND tahun = :tahun:";
            $bind = [
                'keyword' => '%' . $params['keyword'] . '%',
                'tahun' => $params['tahun']
            ];

            if (!empty($params['status'])) {
                $conditions .= " AND status_current_year LIKE :status:";
                $bind['status'] = '%|' . $params['status'] . '|%';
            }

            $rows = KemendesIdmProvinsi::find([
                'conditions' => $conditions,
                'bind' => $bind,
                'order' => $params['order_by'] . ' ' . $params['order_direction']
            ]);

            foreach ($rows as $row) {
                /** @var KemendesIdmProvinsi $row */
                $item = [
                    "RANKING" => $row->ranking,
                    "ID_PROV" => $row->id_prov,
                    "NAMA" => $row->nama,
                    "TOTAL_DESA" => $row->total_desa,
                    "STATUS_CURRENT_YEAR" => $row->status_current_year,
                    "STATUS_LAST_YEAR" => $row->status_last_year
                ];

                $item['CURRENT_YEAR'] = [];
                if ($row->status_current_year) {
                    $current = explode('|', $item['STATUS_CURRENT_YEAR']);
                    $item['CURRENT_YEAR'] = [
                        'YEAR' => $current[0],
                        'INDEX' => $current[1],
                        'STATUS' => $current[2],
                        'IKE' => $current[3],
                        'IKL' => $current[4],
                        'IKS' => $current[5],
                        'DESA' => $current[6]
                    ];
                }
                $item['LAST_YEAR'] = [];
                if ($row->status_last_year) {
                    $last = explode('|', $item['STATUS_LAST_YEAR']);
                    $item['LAST_YEAR'] = [
                        'YEAR' => $last[0],
                        'INDEX' => $last[1],
                        'STATUS' => $last[2],
                        'IKE' => $last[3],
                        'IKL' => $last[4],
                        'IKS' => $last[5],
                        'DESA' => $last[6]
                    ];
                }

                $result[] = $item;
            }

            return $result;
        }
    }

    public function idmDatatableProvinsi()
    {
        $this->checkIsAuthenticated();
        $params = $this->request->get();
        $result = $this->baseIdmDatatableProvinsi($params);

        return new CollectionPaginatedResponse($result);
    }

    public function idmExportProvinsi() {
        $this->checkScopes(['admin.view']);
        $params = $this->request->get();
        $result = $this->baseIdmDatatableProvinsi($params);

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

        $sheet->setCellValue($startCol."1", 'RANKING');
        $sheet->setCellValue(++$startCol."1", 'ID_PROV');
        $sheet->setCellValue(++$startCol."1", 'NAMA_PROV');
        $sheet->setCellValue(++$startCol."1", 'TOTAL_DESA');
        $sheet->setCellValue(++$startCol."1", 'STATUS_CURRENT_YEAR');
        $sheet->setCellValue($startCol."2", 'YEAR');
        $sheet->setCellValue(++$startCol."2", 'INDEX');
        $sheet->setCellValue(++$startCol."2", 'STATUS');
        $sheet->setCellValue(++$startCol."2", 'IKE');
        $sheet->setCellValue(++$startCol."2", 'IKL');
        $sheet->setCellValue(++$startCol."2", 'IKS');
        $sheet->setCellValue(++$startCol."2", 'DESA');
        $sheet->setCellValue(++$startCol."1", 'STATUS_LAST_YEAR');
        $sheet->setCellValue($startCol."2", 'YEAR');
        $sheet->setCellValue(++$startCol."2", 'INDEX');
        $sheet->setCellValue(++$startCol."2", 'STATUS');
        $sheet->setCellValue(++$startCol."2", 'IKE');
        $sheet->setCellValue(++$startCol."2", 'IKL');
        $sheet->setCellValue(++$startCol."2", 'IKS');
        $sheet->setCellValue(++$startCol."2", 'DESA');
        $lastCol = $startCol;
        $startCol = "A";
        $letters = range($startCol, $lastCol);
        $sheet->getStyle($startCol."1:".$lastCol."2")->getFont()->setBold(true);
        $sheet->mergeCells("A1:A2");
        $sheet->mergeCells("B1:B2");
        $sheet->mergeCells("C1:C2");
        $sheet->mergeCells("D1:D2");
        $sheet->mergeCells("E1:K1");
        $sheet->mergeCells("L1:O1");

        $row = 2;
        $num = 1;
        foreach ($result as $res) {
            $row++;
            $sheet->setCellValue($startCol."$row", $res["RANKING"]);
            $sheet->setCellValue(++$startCol."$row", $res["ID_PROV"] ?? $res["ID"]);
            $sheet->setCellValue(++$startCol."$row", $res["NAMA"]);
            $sheet->setCellValue(++$startCol."$row", $res["TOTAL_DESA"]);
            $sheet->setCellValue(++$startCol."$row", $res["CURRENT_YEAR"]['YEAR'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["CURRENT_YEAR"]['INDEX'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["CURRENT_YEAR"]['STATUS'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["CURRENT_YEAR"]['IKE'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["CURRENT_YEAR"]['IKL'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["CURRENT_YEAR"]['IKS'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["CURRENT_YEAR"]['DESA'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["LAST_YEAR"]['YEAR'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["LAST_YEAR"]['INDEX'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["LAST_YEAR"]['STATUS'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["LAST_YEAR"]['IKE'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["LAST_YEAR"]['IKL'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["LAST_YEAR"]['IKS'] ?? '-');
            $sheet->setCellValue(++$startCol."$row", $res["LAST_YEAR"]['DESA'] ?? '-');
            $num++;
            $startCol = "A";
        }

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle($startCol.'1:'. $lastCol . ($row))->applyFromArray($styleArray);
        foreach ($letters as $letter) {
            $sheet->getColumnDimension($letter)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = "IDM Desa per Provinsi - " . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function baseIdmDatatableKabupaten($params) {
        $params['live_data'] = array_key_exists('live_data', $params) ? $params['live_data'] : 0;
        $this->validate($params, [
            'live_data' => ['validators' => [['name' => 'inclusion_in', 'params' => [0,1]]]]
        ]);

        $result = [];
        if ($params['live_data']) {
            // mengambil data langsung dari API Kemendesa
            try {
                $client = new KemendesClient($this->config["kemendes_prop"]["idm"]);
                $response = $client->idmDatatablesKabupaten();
                if (array_key_exists('aaData', $response) && array_key_exists('iTotalDisplayRecords', $response) && is_array($response['aaData'])) {
                    $res = $response['aaData'];
                    foreach ($res as $item) {
                        $tmp = $item;
                        $tmp['CURRENT_YEAR'] = [];
                        if ($item['STATUS_CURRENT_YEAR']) {
                            $current = explode('|', $item['STATUS_CURRENT_YEAR']);
                            $tmp['CURRENT_YEAR'] = [
                                'YEAR' => $current[0],
                                'INDEX' => $current[1],
                                'STATUS' => $current[2],
                                'IKE' => $current[3],
                                'IKL' => $current[4],
                                'IKS' => $current[5],
                                'DESA' => $current[6]
                            ];
                        }

                        $tmp['LAST_YEAR'] = [];
                        if ($item['STATUS_LAST_YEAR']) {
                            $last = explode('|', $item['STATUS_LAST_YEAR']);
                            $tmp['LAST_YEAR'] = [
                                'YEAR' => $last[0],
                                'INDEX' => $last[1],
                                'STATUS' => $last[2],
                                'IKE' => $last[3],
                                'IKL' => $last[4],
                                'IKS' => $last[5],
                                'DESA' => $last[6]
                            ];
                        }
                        $result[] = $tmp;
                    }
                } else {
                    throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, "Gagal menghubungi server Kemendesa. Silakan coba beberapa saat lagi.");
                }
            } catch(\Exception $e) {
                if ($e instanceof CustomErrorException) {
                    throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessages());
                } else {
                    throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $e->getMessage());
                }
            }
        } else {
            // mengambil data dari database
            $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : 'ranking';
            $params['order_direction'] = array_key_exists('order_direction', $params) ? $params['order_direction'] : 'asc';
            $params = $this->validate($params, [
                'tahun' => ['validators' => ['required', 'digit']],
                'id_prov' => ['validators' => ['digit']],
                'status' => ['validators' => [['name' => 'inclusion_in', 'params' => array_merge([null], KemendesClient::IDM_STATUS)]]],
                'keyword' => ['validators' => [['name' => 'max_string', 'params' => 100]]],
                'order_by' => ['validators' => [['name' => 'inclusion_in', 'params' => ['ranking', 'nama', 'total_desa', 'tahun']]]],
                'order_direction' => ['validators' => [['name' => 'inclusion_in', 'params' => ['asc', 'desc']]]]
            ]);

            $conditions = "nama ILIKE :keyword: AND tahun = :tahun:";
            $bind = [
                'keyword' => '%' . $params['keyword'] . '%',
                'tahun' => $params['tahun']
            ];

            if (!empty($params['status'])) {
                $conditions .= " AND status LIKE :status:";
                $bind['status'] = '%' . $params['status'] . '%';
            }

            if (!empty($params['id_prov'])) {
                $conditions .= " AND id_prov = :id_prov:";
                $bind['id_prov'] = $params['id_prov'];
            }

            $rows = KemendesIdmKabupaten::find([
                'conditions' => $conditions,
                'bind' => $bind,
                'order' => $params['order_by'] . ' ' . $params['order_direction']
            ]);

            foreach ($rows as $row) {
                /** @var KemendesIdmKabupaten $row */
                $item = [
                    "RANKING" => $row->ranking,
                    "STATUS" => $row->status,
                    "ID_PROV" => $row->id_prov,
                    "ID_KAB" => $row->id_kab,
                    "NAMA_PROV" => $row->nama_prov,
                    "NAMA_KAB" => $row->nama,
                    "TOTAL_DESA" => $row->total_desa,
                    "TOTAL_VERIFIED" => $row->total_verified,
                    "STATUS_CURRENT_YEAR" => $row->status_current_year,
                    "STATUS_LAST_YEAR" => $row->status_last_year
                ];

                $item['CURRENT_YEAR'] = [];
                if ($row->status_last_year) {
                    $current = explode('|', $item['STATUS_CURRENT_YEAR']);
                    $item['CURRENT_YEAR'] = [
                        'YEAR' => $current[0],
                        'INDEX' => $current[1],
                        'STATUS' => $current[2],
                        'IKE' => $current[3],
                        'IKL' => $current[4],
                        'IKS' => $current[5],
                        'DESA' => $current[6]
                    ];
                }

                $item['LAST_YEAR'] = [];
                if ($item['STATUS_LAST_YEAR']) {
                    $last = explode('|', $item['STATUS_LAST_YEAR']);
                    $item['LAST_YEAR'] = [
                        'YEAR' => $last[0],
                        'INDEX' => $last[1],
                        'STATUS' => $last[2],
                        'IKE' => $last[3],
                        'IKL' => $last[4],
                        'IKS' => $last[5],
                        'DESA' => $last[6]
                    ];
                }

                $result[] = $item;
            }
        }

        return $result;
    }

    public function idmDatatableKabupaten() {
        $this->checkIsAuthenticated();
        $params = $this->request->get();
        $result = $this->baseIdmDatatableKabupaten($params);

        return new CollectionPaginatedResponse($result);
    }

    public function idmExportKabupaten() {
        $this->checkScopes(['admin.view']);
        $params = $this->request->get();
        $result = $this->baseIdmDatatableKabupaten($params);

        $lastCol = "U";
        $letters = range('A', $lastCol);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue("A1", 'RANKING');
        $sheet->setCellValue("B1", 'ID_PROV');
        $sheet->setCellValue("C1", 'ID_KAB');
        $sheet->setCellValue("D1", 'NAMA_PROV');
        $sheet->setCellValue("E1", 'NAMA_KAB');
        $sheet->setCellValue("F1", 'TOTAL_DESA');
        $sheet->setCellValue("G1", 'TOTAL_VERIFIED');
        $sheet->setCellValue("H1", 'STATUS_CURRENT_YEAR');
        $sheet->setCellValue("H2", 'YEAR');
        $sheet->setCellValue("I2", 'INDEX');
        $sheet->setCellValue("J2", 'STATUS');
        $sheet->setCellValue("K2", 'IKE');
        $sheet->setCellValue("L2", 'IKL');
        $sheet->setCellValue("M2", 'IKS');
        $sheet->setCellValue("N2", 'DESA');
        $sheet->setCellValue("O1", 'STATUS_LAST_YEAR');
        $sheet->setCellValue("O2", 'YEAR');
        $sheet->setCellValue("P2", 'INDEX');
        $sheet->setCellValue("Q2", 'STATUS');
        $sheet->setCellValue("R2", 'IKE');
        $sheet->setCellValue("S2", 'IKL');
        $sheet->setCellValue("T2", 'IKS');
        $sheet->setCellValue("U2", 'DESA');
        $sheet->getStyle("A1:".$lastCol."2")->getFont()->setBold(true);

        $row = 2;
        $num = 1;
        foreach ($result as $res) {
            $row++;
            $sheet->setCellValue("A$row", $res["RANKING"]);
            $sheet->setCellValue("B$row", $res["ID_PROV"]);
            $sheet->setCellValue("C$row", $res["ID_KAB"]);
            $sheet->setCellValue("D$row", $res["NAMA_PROV"]);
            $sheet->setCellValue("E$row", $res["NAMA_KAB"]);
            $sheet->setCellValue("F$row", $res["TOTAL_DESA"]);
            $sheet->setCellValue("G$row", $res["TOTAL_VERIFIED"]);
            $sheet->setCellValue("H$row", $res["STATUS_CURRENT_YEAR"]);
            $sheet->setCellValue("H$row", $res["CURRENT_YEAR"]['YEAR'] ?? '-');
            $sheet->setCellValue("I$row", $res["CURRENT_YEAR"]['INDEX'] ?? '-');
            $sheet->setCellValue("J$row", $res["CURRENT_YEAR"]['STATUS'] ?? '-');
            $sheet->setCellValue("K$row", $res["CURRENT_YEAR"]['IKE'] ?? '-');
            $sheet->setCellValue("L$row", $res["CURRENT_YEAR"]['IKL'] ?? '-');
            $sheet->setCellValue("M$row", $res["CURRENT_YEAR"]['IKS'] ?? '-');
            $sheet->setCellValue("N$row", $res["CURRENT_YEAR"]['DESA'] ?? '-');
            $sheet->setCellValue("O$row", $res["STATUS_LAST_YEAR"]);
            $sheet->setCellValue("O$row", $res["LAST_YEAR"]['YEAR'] ?? '-');
            $sheet->setCellValue("P$row", $res["LAST_YEAR"]['INDEX'] ?? '-');
            $sheet->setCellValue("Q$row", $res["LAST_YEAR"]['STATUS'] ?? '-');
            $sheet->setCellValue("R$row", $res["LAST_YEAR"]['IKE'] ?? '-');
            $sheet->setCellValue("S$row", $res["LAST_YEAR"]['IKL'] ?? '-');
            $sheet->setCellValue("T$row", $res["LAST_YEAR"]['IKS'] ?? '-');
            $sheet->setCellValue("U$row", $res["LAST_YEAR"]['DESA'] ?? '-');
            $num++;
        }

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:'. $lastCol . ($row))->applyFromArray($styleArray);
        foreach ($letters as $letter) {
            $sheet->getColumnDimension($letter)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = "IDM Desa per Kabupaten - " . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }
    #endregion

    #region BumDes Kemendesa
    public function bumdesByProv($id) {
        $this->checkIsAuthenticated();
        $this->validate(["id" => $id], ['id' => ['validators' => ['required', 'digit', ['name' => 'string_length', 'params' => 2]]]]);

        $client = new KemendesClient($this->config["kemendes_prop"]["bumdes"]);
        try {
            $response = $client->bumdesByProv($id);
            return new CollectionPaginatedResponse($response);
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $client::ERROR_MESSAGE);
        }
    }

    public function bumdesCountProv($id) {
        $this->checkIsAuthenticated();
        $this->validate(["id" => $id], ["id" => ["validators" => ["required", "digit", ["name" => "string_length", "params" => 2]]]]);

        $client = new KemendesClient($this->config["kemendes_prop"]["bumdes"]);
        try {
            $response = $client->bumdesCountProv($id);
            return new SimpleResponse($response);
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $client::ERROR_MESSAGE);
        }
    }

    public function bumdesByKab($id) {
        $this->checkIsAuthenticated();
        $this->validate(["id" => $id], ['id' => ['validators' => ['required', 'digit', ['name' => 'string_length', 'params' => 4]]]]);

//        $client = new KemendesClient($this->config["kemendes_prop"]["bumdes"]);
//        try {
//            $response = $client->bumdesByKab($id);
//            return new CollectionPaginatedResponse($response);
//        } catch (\Exception $e) {
//            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $client::ERROR_MESSAGE);
//        }

        $response = KemendesBumdes::find([
            'conditions' => 'id_kabupaten = :id: and deleted_at IS NULL',
            'bind' => ['id' => $id]
        ]);

        return new CollectionPaginatedResponse($response);
    }

    public function bumdesCountKab($id) {
        $this->checkIsAuthenticated();
        $this->validate(["id" => $id], ["id" => ["validators" => ["required", "digit", ["name" => "string_length", "params" => 4]]]]);

//        $client = new KemendesClient($this->config["kemendes_prop"]["bumdes"]);
//        try {
//            $response = $client->bumdesCountKab($id);
//            return new SimpleResponse($response);
//        } catch (\Exception $e) {
//            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $client::ERROR_MESSAGE);
//        }

        $result = KemendesBumdesCount::findFirst([
            'conditions' => 'id_kabupaten = :id: and deleted_at IS NULL',
            'bind' => ['id' => $id]
        ]);
        if (!$result) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data tidak ditemukan");

        $response = [
            "bumdes" => [
                "bumdes_mendaftar_nama" => $result->bumdes_mendaftar_nama,
                "bumdes_perbaikan_nama" => $result->bumdes_perbaikan_nama,
                "bumdes_verifikasi_nama" => $result->bumdes_verifikasi_nama,
                "bumdes_mendaftar_badan_hukum" => $result->bumdes_mendaftar_badan_hukum,
                "bumdes_perbaikan_badan_hukum" => $result->bumdes_perbaikan_badan_hukum,
                "bumdes_verifikasi_badan_hukum" => $result->bumdes_verifikasi_badan_hukum,
            ],
            "bumdesma" => [
                "bumdesma_mendaftar_nama" => $result->bumdesma_mendaftar_nama,
                "bumdesma_perbaikan_nama" => $result->bumdesma_perbaikan_nama,
                "bumdesma_verifikasi_nama" => $result->bumdesma_verifikasi_nama,
                "bumdesma_mendaftar_badan_hukum" => $result->bumdesma_mendaftar_badan_hukum,
                "bumdesma_perbaikan_badan_hukum" => $result->bumdesma_perbaikan_badan_hukum,
                "bumdesma_verifikasi_badan_hukum" => $result->bumdesma_verifikasi_badan_hukum,
            ]
        ];

        return new SimpleResponse($response);
    }

    public function bumdesByDesa($id) {
        $this->checkIsAuthenticated();
        $this->validate(["id" => $id], ['id' => ['validators' => ['required', 'digit']]]);

        $client = new KemendesClient($this->config["kemendes_prop"]["bumdes"]);
        try {
            $response = $client->bumdesByDesa($id);
            return new CollectionPaginatedResponse($response);
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $client::ERROR_MESSAGE);
        }
    }

    public function bumdesCountDesa($id) {
        $this->checkIsAuthenticated();
        $this->validate(["id" => $id], ["id" => ["validators" => ["required", "digit"]]]);

        $client = new KemendesClient(($this->config["kemendes_prop"]["bumdes"]));
        try {
            $response = $client->bumdesCountDesa($id);
            return new SimpleResponse($response);
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $client::ERROR_MESSAGE);
        }
    }

    public function bumdesExportByKab($id) {
        $this->checkScopes(['admin.view']);
        $this->validate(["id" => $id], ["id" => ["validators" => ["required", "digit"]]]);

        $client = new KemendesClient(($this->config["kemendes_prop"]["bumdes"]));
        try {
            /** @var MasterKotaKabupaten $kabupaten */
            $kabupaten = MasterKotaKabupaten::findFirst([
                "conditions" => "kota_kabupaten_code = :id:",
                "bind" => ["id" => $id]
            ]);
            if (!$kabupaten) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Kabupaten [$id] tidak ditemukan");
            }
            /*$response = $client->bumdesByKab($id);*/
            $response = KemendesBumdes::find([
                'conditions' => 'id_kabupaten = :id: and deleted_at IS NULL',
                'bind' => ['id' => $id]
            ])->toArray();
            $desas = OmspanMasterDesa::find([
                "conditions" => "kode_pemda = :id:",
                "bind" => ["id" => $id]
            ]);
            $desaKv = [];
            foreach ($desas as $desa) {
                $desaKv[$desa->kode_desa] = $desa->nama_desa;
            }

            $lastCol = "K";
            $letters = range('A', $lastCol);
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $sheet->setCellValue("A1", 'NO');
            $sheet->setCellValue("B1", 'KODE DESA');
            $sheet->setCellValue("C1", 'NAMA DESA');
            $sheet->setCellValue("D1", 'ID');
            $sheet->setCellValue("E1", 'TYPE');
            $sheet->setCellValue("F1", 'NAMA');
            $sheet->setCellValue("G1", 'NAMA LENGKAP');
            $sheet->setCellValue("H1", 'ALAMAT');
            $sheet->setCellValue("I1", 'EMAIL');
            $sheet->setCellValue("J1", 'NO HP KEPALA DESA');
            $sheet->setCellValue("K1", 'STATUS');
            $sheet->getStyle("A1:".$lastCol."1")->getFont()->setBold(true);

            $row = 1;
            $num = 1;
            foreach ($response as $res) {
                $row++;
                $sheet->setCellValue("A$row", $num);
                $sheet->setCellValue("B$row", $res["id_desa"]);
                $sheet->setCellValue("C$row", $desaKv[$res["id_desa"]] ?? "-");
                $sheet->setCellValue("D$row", $res["bumdes_id"]);
                $sheet->setCellValue("E$row", $res["type_bumdes"]);
                $sheet->setCellValue("F$row", $res["nama_bumdes"]);
                $sheet->setCellValue("G$row", $res["nama_full_bumdes"]);
                $sheet->setCellValue("H$row", $res["alamat_lengkap"]);
                $sheet->setCellValue("I$row", $res["email"]);
                $sheet->setCellValue("J$row", $res["no_hp_kepala_desa"]);
                $sheet->setCellValue("K$row", $res["status"]);
                $num++;
            }

            $styleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['argb' => 'FF000000'],
                    ],
                ],
            ];
            $sheet->getStyle('A1:'. $lastCol . ($row))->applyFromArray($styleArray);
            foreach ($letters as $letter) {
                $sheet->getColumnDimension($letter)->setAutoSize(true);
            }

            $writer = new Xlsx($spreadsheet);
            $filename = "BUM Desa - " . $kabupaten->kota_kabupaten_name . " - " . DateHelper::formatTodayIndex() . ".xlsx";
            header('Content-type: application/vnd.ms-excel');
            header('Content-Disposition: attachment; filename="' . $filename . '"');
            return $writer->save('php://output');
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::GUZZLE_ERROR, $client::ERROR_MESSAGE);
        }
    }
    #endregion

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

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

        $paguDanaDesa = $this->rawQuery("SELECT COUNT(kode_desa) as total FROM omspan_dana_desa_pagu WHERE TAHUN = $tahun");
        $penyaluranDanaDesa = $this->rawQuery("SELECT tahap, count(tahap) as total FROM omspan_dana_desa_penyaluran WHERE TAHUN = $tahun AND tahap IS NOT NULL GROUP BY tahap ORDER BY tahap");
        $penyerapanDanaDesa = $this->rawQuery("SELECT count(distinct(kode_desa)) as total FROM omspan_penyerapan_dana_desa WHERE TAHUN = $tahun");

        return new SimpleResponse([
            "pagu" => $paguDanaDesa[0]['total'],
            "penyaluran" => $penyaluranDanaDesa,
            "penyerapan" => $penyerapanDanaDesa[0]['total']
        ]);
    }

    public function listLokasi()
    {
        $this->checkScopes(['admin.view']);
        $params = $this->request->get();
        $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 30;
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $validatedRequest = $this->validate($params, [
            'keyword' => ['validators' => []],
            'kd_prov' => ['validators' => ['digit']],
            'limit' => ['validators' => ['required', 'digit']],
            'offset' => ['validators' => ['required', 'digit']]
        ]);

        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];

        $data = OmspanMasterPemda::find([
            'conditions' => 'nm_lokasi ILIKE :keyword: AND kd_prov LIKE :kd_prov:',
            'bind' => [
                'kd_prov' => '%' . $validatedRequest['kd_prov'] . '%',
                'keyword' => '%' . $validatedRequest['keyword'] . '%',
            ],
            'limit' => $limit,
            'offset' => $offset,
            'order' => 'nm_lokasi'
        ]);
        $total = OmspanMasterPemda::count([
            'conditions' => 'nm_lokasi ILIKE :keyword: AND kd_prov LIKE :kd_prov:',
            'bind' => [
                'kd_prov' => '%' . $validatedRequest['kd_prov'] . '%',
                'keyword' => '%' . $validatedRequest['keyword'] . '%',
            ],
        ]);

        return new PaginatedResponse($data, $total, $limit, $offset);
    }

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

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

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

        $tahun = $validatedRequest['tahun'];
        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];

        $conditions = 'tahun = :tahun: AND nama_desa ILIKE :keyword:';
        $bind = [
            'tahun' => $tahun,
            'keyword' => '%' . $validatedRequest['keyword'] . '%'
        ];
        if (!is_null($validatedRequest['kode_lokasi'])) {
            $conditions .= " AND kode_lokasi LIKE :kode_lokasi:";
            $bind['kode_lokasi'] = '%' . $validatedRequest['kode_lokasi'] . '%';
        }
        if (!is_null($validatedRequest['kode_provinsi'])) {
            $conditions .= " AND kode_lokasi LIKE :kode_provinsi:";
            $bind['kode_provinsi'] = $validatedRequest['kode_provinsi'] . '%';
        }

        $pagus = OmspanDanaDesaPagu::find([
            'conditions' => $conditions,
            'bind' => $bind,
            'order' => 'kode_lokasi, kode_desa',
            'limit' => $limit,
            'offset' => $offset
        ]);
        $total = OmspanDanaDesaPagu::count([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

        $data = [];
        foreach ($pagus as $pagu) {
            /** @var $pagu OmspanDanaDesaPagu */
            $bind = [
                'kode_desa' => $pagu->kode_desa,
                'tahun' => $pagu->tahun
            ];
            $penyaluran = $this->rawQuery("
                SELECT 
	                SUM(realisasi) FILTER (WHERE tahap = 1) as tahap_1,
                    SUM(realisasi) FILTER (WHERE tahap = 2) as tahap_2,
                    SUM(realisasi) FILTER (WHERE tahap = 3) as tahap_3
                FROM omspan_dana_desa_penyaluran 
                WHERE kode_desa LIKE :kode_desa AND tahun = :tahun;
            ", $bind);
            $penyerapan = $this->rawQuery("
                SELECT 
                    SUM(realisasi_tahap_1) as tahap_1,
                    SUM(realisasi_tahap_2) as tahap_2,
                    SUM(realisasi_tahap_3) as tahap_3
                FROM omspan_penyerapan_dana_desa 
                WHERE kode_desa LIKE :kode_desa AND tahun = :tahun;
            ", $bind);
            $datum = [
                'pagu' => $pagu,
                'penyaluran' => $penyaluran,
                'penyerapan' => $penyerapan
            ];
            array_push($data, $datum);
        }

        return new PaginatedResponse($data, $total, $limit, $offset);
    }

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

        ini_set('memory_limit', '2048M');

        $validatedRequest = $this->validate($this->request->get(), [
            'tahun' => ['validators' => ['required', 'digit']],
            'kode_provinsi' => ['validators' => ['digit']],
            'kode_lokasi' => ['validators' => ['digit']],
            'keyword' => ['validators' => []]
        ]);

        $tahun = $validatedRequest['tahun'];

        $conditions = 'tahun = :tahun:';
        $bind = [
            'tahun' => $tahun
        ];
        if (!is_null($validatedRequest['kode_lokasi'])) {
            $conditions .= " AND OmspanMasterPemda.kd_lokasi LIKE :kode_lokasi:";
            $bind['kode_lokasi'] = $validatedRequest['kode_lokasi'];
        }
        if (!is_null($validatedRequest['kode_provinsi'])) {
            $conditions .= " AND OmspanMasterProvinsi.kd_prov LIKE :kode_provinsi:";
            $bind['kode_provinsi'] = $validatedRequest['kode_provinsi'];
        }
        if (!is_null($validatedRequest['keyword'])) {
            $conditions .= " AND OmspanDanaDesaPagu.nama_desa ILIKE :keyword:";
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $data = OmspanDanaDesaPagu::query()
            ->columns("OmspanDanaDesaPagu.*, OmspanMasterPemda.*, OmspanMasterProvinsi.*")
            ->leftJoin(OmspanMasterPemda::class, "OmspanMasterPemda.kd_lokasi = OmspanDanaDesaPagu.kode_lokasi")
            ->leftJoin(OmspanMasterProvinsi::class, "OmspanMasterProvinsi.kd_prov = OmspanMasterPemda.kd_prov")
            ->where($conditions)
            ->bind($bind)
            ->orderBy('OmspanMasterProvinsi.kd_prov, OmspanMasterPemda.kd_lokasi, OmspanDanaDesaPagu.kode_desa')
            ->execute();

        $lastCol = "H";
        $letters = range('A', $lastCol);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'PAGU DANA DESA');
        $sheet->setCellValue("A2", "Tahun " . $tahun);

        $sheet->getStyle("A1:".$lastCol."2")
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:'.$lastCol.'1');
        $sheet->mergeCells('A2:'.$lastCol.'2');

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Kode Provinsi');
        $sheet->setCellValue("C4", 'Provinsi');
        $sheet->setCellValue("D4", 'Kode Lokasi');
        $sheet->setCellValue("E4", 'Kabupaten/Kota');
        $sheet->setCellValue("F4", 'Kode Desa');
        $sheet->setCellValue("G4", 'Nama Desa');
        $sheet->setCellValue("H4", 'Pagu');
        $sheet->getStyle("A4:".$lastCol."4")->getFont()->setBold(true);

        $row = 4;
        $num = 1;
        foreach ($data as $datum) {
            /** @var $pagu OmspanDanaDesaPagu */
            $pagu = $datum->omspanDanaDesaPagu;
            /** @var $provinsi OmspanMasterProvinsi */
            $provinsi = $datum->omspanMasterProvinsi;
            /** @var $pemda OmspanMasterPemda */
            $pemda = $datum->omspanMasterPemda;

            $row++;
            $sheet->setCellValue("A$row", $num);
            $sheet->setCellValue("B$row", $provinsi->kd_prov);
            $sheet->setCellValue("C$row", $provinsi->nm_prov);
            $sheet->setCellValue("D$row", $pemda->kd_lokasi);
            $sheet->setCellValue("E$row", $pemda->nm_lokasi);
            $sheet->setCellValue("F$row", $pagu->kode_desa);
            $sheet->setCellValue("G$row", $pagu->nama_desa);
            $sheet->setCellValue("H$row", $pagu->pagu);
            $num++;
        }

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A4:'. $lastCol . ($row))->applyFromArray($styleArray);
        foreach ($letters as $letter) {
            $sheet->getColumnDimension($letter)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = "Pagu Dana Desa - " . $tahun . " - " . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

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

        ini_set('memory_limit', '2048M');

        $validatedRequest = $this->validate($this->request->get(), [
            'tahun' => ['validators' => ['required', 'digit']],
            'kode_provinsi' => ['validators' => ['digit']],
            'kode_lokasi' => ['validators' => ['digit']],
            'keyword' => ['validators' => []]
        ]);

        $tahun = $validatedRequest['tahun'];

        $conditions = 'tahun = :tahun:';
        $bind = [
            'tahun' => $tahun
        ];
        if (!is_null($validatedRequest['kode_lokasi'])) {
            $conditions .= " AND OmspanMasterPemda.kd_lokasi LIKE :kode_lokasi:";
            $bind['kode_lokasi'] = $validatedRequest['kode_lokasi'];
        }
        if (!is_null($validatedRequest['kode_provinsi'])) {
            $conditions .= " AND OmspanMasterProvinsi.kd_prov LIKE :kode_provinsi:";
            $bind['kode_provinsi'] = $validatedRequest['kode_provinsi'];
        }
        if (!is_null($validatedRequest['keyword'])) {
            $conditions .= " AND OmspanDanaDesaPenyaluran.nama_desa ILIKE :keyword:";
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $data = OmspanDanaDesaPenyaluran::query()
            ->columns("OmspanDanaDesaPenyaluran.*, OmspanMasterProvinsi.*, OmspanMasterPemda.*")
            ->leftJoin(OmspanMasterPemda::class, "OmspanMasterPemda.kd_lokasi = OmspanDanaDesaPenyaluran.kode_lokasi")
            ->leftJoin(OmspanMasterProvinsi::class, "OmspanMasterProvinsi.kd_prov = OmspanMasterPemda.kd_prov")
            ->where($conditions)
            ->bind($bind)
            ->orderBy('OmspanMasterProvinsi.kd_prov, OmspanMasterPemda.kd_lokasi, OmspanDanaDesaPenyaluran.kode_desa, OmspanDanaDesaPenyaluran.tahap')
            ->execute();

        $lastCol = "K";
        $letters = range('A', $lastCol);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'PENYALURAN DANA DESA');
        $sheet->setCellValue("A2", "Tahun " . $tahun);

        $sheet->getStyle("A1:".$lastCol."2")
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:'.$lastCol.'1');
        $sheet->mergeCells('A2:'.$lastCol.'2');

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Kode Provinsi');
        $sheet->setCellValue("C4", 'Provinsi');
        $sheet->setCellValue("D4", 'Kode Lokasi');
        $sheet->setCellValue("E4", 'Kabupaten/Kota');
        $sheet->setCellValue("F4", 'Kode Desa');
        $sheet->setCellValue("G4", 'Nama Desa');
        $sheet->setCellValue("H4", 'Tahap');
        $sheet->setCellValue("I4", 'No SP2D');
        $sheet->setCellValue("J4", 'Tanggal SP2D');
        $sheet->setCellValue("K4", 'Realisasi');
        $sheet->getStyle("A4:".$lastCol."4")->getFont()->setBold(true);

        $row = 4;
        $num = 1;
        foreach ($data as $datum) {
            /** @var $penyaluran OmspanDanaDesaPenyaluran */
            $penyaluran = $datum->omspanDanaDesaPenyaluran;
            /** @var $provinsi OmspanMasterProvinsi */
            $provinsi = $datum->omspanMasterProvinsi;
            /** @var $pemda OmspanMasterPemda */
            $pemda = $datum->omspanMasterPemda;

            $row++;
            $sheet->setCellValue("A$row", $num);
            $sheet->setCellValue("B$row", $provinsi->kd_prov);
            $sheet->setCellValue("C$row", $provinsi->nm_prov);
            $sheet->setCellValue("D$row", $pemda->kd_lokasi);
            $sheet->setCellValue("E$row", $pemda->nm_lokasi);
            $sheet->setCellValue("F$row", $penyaluran->kode_desa);
            $sheet->setCellValue("G$row", $penyaluran->nama_desa);
            $sheet->setCellValue("H$row", $penyaluran->tahap);
            $sheet->setCellValue("I$row", $penyaluran->no_sp2d);
            $sheet->setCellValue("J$row", $penyaluran->tanggal_sp2d);
            $sheet->setCellValue("K$row", $penyaluran->realisasi);
            $num++;
        }

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A4:'. $lastCol . ($row))->applyFromArray($styleArray);

        foreach ($letters as $letter) {
            $sheet->getColumnDimension($letter)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = "Penyaluran Dana Desa - " . $tahun . " - " . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

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

        ini_set('memory_limit', '2048M');

        $validatedRequest = $this->validate($this->request->get(), [
            'tahun' => ['validators' => ['required', 'digit']],
            'kode_provinsi' => ['validators' => ['digit']],
            'kode_lokasi' => ['validators' => ['digit']],
            'keyword' => ['validators' => []]
        ]);

        $tahun = $validatedRequest['tahun'];

        $conditions = 'tahun = :tahun:';
        $bind = [
            'tahun' => $tahun
        ];
        if (!is_null($validatedRequest['kode_lokasi'])) {
            $conditions .= " AND OmspanMasterPemda.kd_lokasi LIKE :kode_lokasi:";
            $bind['kode_lokasi'] = $validatedRequest['kode_lokasi'];
        }
        if (!is_null($validatedRequest['kode_provinsi'])) {
            $conditions .= " AND OmspanMasterProvinsi.kd_prov LIKE :kode_provinsi:";
            $bind['kode_provinsi'] = $validatedRequest['kode_provinsi'];
        }
        if (!is_null($validatedRequest['keyword'])) {
            $conditions .= " AND OmspanMasterDesa.nama_desa ILIKE :keyword:";
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }
        $data = OmspanPenyerapanDanaDesa::query()
            ->columns('OmspanPenyerapanDanaDesa.*, DesaUraianOutputPenyerapan.uraian, OmspanMasterDesa.*, OmspanMasterProvinsi.*, OmspanMasterPemda.*')
            ->leftJoin(DesaUraianOutputPenyerapan::class, 'DesaUraianOutputPenyerapan.kode = OmspanPenyerapanDanaDesa.kode_uraian_output')
            ->leftJoin(OmspanMasterDesa::class, 'OmspanMasterDesa.kode_desa = OmspanPenyerapanDanaDesa.kode_desa')
            ->leftJoin(OmspanMasterPemda::class, "OmspanMasterPemda.kd_lokasi = OmspanPenyerapanDanaDesa.kode_lokasi")
            ->leftJoin(OmspanMasterProvinsi::class, "OmspanMasterProvinsi.kd_prov = OmspanMasterPemda.kd_prov")
            ->where($conditions)
            ->bind($bind)
            ->orderBy('OmspanMasterProvinsi.kd_prov, OmspanMasterPemda.kd_lokasi, OmspanPenyerapanDanaDesa.kode_desa, OmspanPenyerapanDanaDesa.kode_uraian_output')
            ->execute();

        $lastCol = "S";
        $letters = range('A', $lastCol);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'PENYERAPAN DANA DESA');
        $sheet->setCellValue("A2", "Tahun " . $tahun);

        $sheet->getStyle("A1:".$lastCol."2")
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:'.$lastCol.'1');
        $sheet->mergeCells('A2:'.$lastCol.'2');

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Kode Provinsi');
        $sheet->setCellValue("C4", 'Provinsi');
        $sheet->setCellValue("D4", 'Kode Lokasi');
        $sheet->setCellValue("E4", 'Kabupaten/Kota');
        $sheet->setCellValue("F4", 'Kode Desa');
        $sheet->setCellValue("G4", 'Nama Desa');
        $sheet->setCellValue("H4", 'Kode Uraian Output');
        $sheet->setCellValue("I4", 'Uraian Output');
        $sheet->setCellValue("J4", 'Volume');
        $sheet->setCellValue("K4", 'Satuan Output');
        $sheet->setCellValue("L4", 'Cara Pengadaan');
        $sheet->setCellValue("M4", 'Keterangan');
        $sheet->setCellValue("N4", 'Realisasi Tahap 1');
        $sheet->setCellValue("O4", 'Realisasi Tahap 2');
        $sheet->setCellValue("P4", 'Realisasi Tahap 3');
        $sheet->setCellValue("Q4", 'Persentase Capaian 1');
        $sheet->setCellValue("R4", 'Persentase Capaian 2');
        $sheet->setCellValue("S4", 'Persentase Capaian 3');
        $sheet->getStyle("A4:".$lastCol."4")->getFont()->setBold(true);

        $row = 4;
        $num = 1;
        foreach ($data as $datum) {
            /** @var $penyerapan OmspanPenyerapanDanaDesa */
            $penyerapan = $datum->omspanPenyerapanDanaDesa;
            $uraian = $datum->uraian;
            /** @var $desa OmspanMasterDesa */
            $desa = $datum->omspanMasterDesa;
            /** @var $provinsi OmspanMasterProvinsi */
            $provinsi = $datum->omspanMasterProvinsi;
            /** @var $pemda OmspanMasterPemda */
            $pemda = $datum->omspanMasterPemda;

            $row++;
            $sheet->setCellValue("A$row", $num);
            $sheet->setCellValueExplicit("B$row", $provinsi->kd_prov, DataType::TYPE_STRING);
            $sheet->setCellValue("C$row", $provinsi->nm_prov);
            $sheet->setCellValueExplicit("D$row", $pemda->kd_lokasi, DataType::TYPE_STRING);
            $sheet->setCellValue("E$row", $pemda->nm_lokasi);
            $sheet->setCellValue("F$row", $desa->kode_desa);
            $sheet->setCellValue("G$row", $desa->nama_desa);
            $sheet->setCellValue("H$row", $penyerapan->kode_uraian_output);
            $sheet->setCellValue("I$row", $uraian);
            $sheet->setCellValue("J$row", $penyerapan->volume);
            $sheet->setCellValue("K$row", $penyerapan->satuan_output);
            $sheet->setCellValue("L$row", $penyerapan->cara_pengadaan);
            $sheet->setCellValue("M$row", $penyerapan->keterangan);
            $sheet->setCellValue("N$row", $penyerapan->realisasi_tahap_1);
            $sheet->setCellValue("O$row", $penyerapan->realisasi_tahap_2);
            $sheet->setCellValue("P$row", $penyerapan->realisasi_tahap_3);
            $sheet->setCellValue("Q$row", $penyerapan->persentase_realisasi_tahap_1);
            $sheet->setCellValue("R$row", $penyerapan->persentase_realisasi_tahap_2);
            $sheet->setCellValue("S$row", $penyerapan->persentase_realisasi_tahap_3);
            $num++;
        }

//        $styleArray = [
//            'borders' => [
//                'allBorders' => [
//                    'borderStyle' => Border::BORDER_THIN,
//                    'color' => ['argb' => 'FF000000'],
//                ],
//            ],
//        ];
//        $sheet->getStyle('A4:'. $lastCol . ($row))->applyFromArray($styleArray);

//        foreach ($letters as $letter) {
//            $sheet->getColumnDimension($letter)->setAutoSize(true);
//        }

        $writer = new Xlsx($spreadsheet);
        $filename = "Penyerapan Dana Desa - " . $tahun . " - " . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function generateSheetPenyerapan($spreadsheet, $sheetIndex, $tahun, $lastCol)
    {
        $sheet = $spreadsheet->createSheet($sheetIndex++);
        $sheet->setCellValue("A1", 'PENYERAPAN DANA DESA');
        $sheet->setCellValue("A2", "Tahun " . $tahun);

        $sheet->getStyle("A1:".$lastCol."2")
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:'.$lastCol.'1');
        $sheet->mergeCells('A2:'.$lastCol.'2');

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Kode Provinsi');
        $sheet->setCellValue("C4", 'Provinsi');
        $sheet->setCellValue("D4", 'Kode Lokasi');
        $sheet->setCellValue("E4", 'Kabupaten/Kota');
        $sheet->setCellValue("F4", 'Kode Desa');
        $sheet->setCellValue("G4", 'Nama Desa');
        $sheet->setCellValue("H4", 'Kode Uraian Output');
        $sheet->setCellValue("I4", 'Uraian Output');
        $sheet->setCellValue("J4", 'Volume');
        $sheet->setCellValue("K4", 'Satuan Output');
        $sheet->setCellValue("L4", 'Cara Pengadaan');
        $sheet->setCellValue("M4", 'Keterangan');
        $sheet->setCellValue("N4", 'Realisasi Tahap 1');
        $sheet->setCellValue("O4", 'Realisasi Tahap 2');
        $sheet->setCellValue("P4", 'Realisasi Tahap 3');
        $sheet->setCellValue("Q4", 'Persentase Capaian 1');
        $sheet->setCellValue("R4", 'Persentase Capaian 2');
        $sheet->setCellValue("S4", 'Persentase Capaian 3');
        $sheet->getStyle("A4:".$lastCol."4")->getFont()->setBold(true);

        return $sheet;
    }

    public function totalAnggaran() {
        $desa = $this->rawQuery("SELECT COUNT(DISTINCT(kode_desa)) as jml_desa, tahun FROM jaga.omspan_dana_desa_pagu GROUP BY tahun");
        $pagu = $this->rawQuery("SELECT SUM(pagu) as pagu, tahun FROM jaga.omspan_dana_desa_pagu GROUP BY tahun");

        $res = [];
        foreach ($desa as $d) {
            $res[$d['tahun']] = ['jml_desa' => $d['jml_desa']];
        }
        foreach ($pagu as $p) {
            $res[$p['tahun']]['pagu'] = $p['pagu'];
        }

        return new SimpleResponse($res);
    }

    public function summary($kodeDesa, $tahunAnggaran) {
        $params = $this->request->get();
        $anggaranKumulatif = array_key_exists('anggaranKumulatif', $params) ? $params['anggaranKumulatif'] : 0;
        $desaService = new DesaService($this);
        $profile = $desaService->profile($kodeDesa, $tahunAnggaran);
        $penduduk = $desaService->kkppd($kodeDesa);
        if ($anggaranKumulatif) {
            $anggaran = $desaService->anggaranKumulatif($kodeDesa, $tahunAnggaran);
        } else {
            $anggaran = $desaService->anggaran($kodeDesa, $tahunAnggaran);
        }
        return new SimpleResponse([
            "profile" => $profile,
            "anggaran" => $anggaran,
            "penduduk" => $penduduk
        ]);
    }
}