<?php

use PHPOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Carbon\Carbon;

class BansosController extends BaseController
{
    const SCOPE_FOLLOWUP_KELUHAN_IKN = 'keluhan.ikn.follow_up';
    const SCOPE_FOLLOWUP_KELUHAN_DESA = 'keluhan.desa.follow_up';
    const SCOPE_FOLLOWUP_KELUHAN_KAMPUS = 'keluhan.kampus.follow_up';
    const SCOPE_MONITORING_KELUHAN_KAMPUS = 'keluhan.kampus.monitoring';
    const SCOPE_FOLLOWUP_KELUHAN_PELABUHAN = 'keluhan.pelabuhan.follow_up';
    const SCOPE_MONITORING_KELUHAN_PELABUHAN = 'keluhan.pelabuhan.monitoring';
    const SCOPE_MONITORING_KELUHAN_PEMILU = 'keluhan.pemilu.monitoring';
    const SCOPE_FOLLOWUP_KELUHAN_BPJS = 'keluhan.bpjs.follow_up';
    const SCOPE_FOLLOWUP_KELUHAN_PENDIDIKAN = 'keluhan.pendidikan.follow_up';

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

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['id_provinsi'] = array_key_exists('id_provinsi', $params) ? $params['id_provinsi'] : null;
        $params['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : null;
        $params['id_produk'] = array_key_exists('id_produk', $params) ? $params['id_produk'] : null;
        $params['keluhan_type'] = array_key_exists('keluhan_type', $params) ? $params['keluhan_type'] : null;
        $params['rewrite_pemerintah'] = array_key_exists('rewrite_pemerintah', $params) ? $params['rewrite_pemerintah'] : null;

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

        $strWhere = "deleted_at is null and (id_provinsi <> :id_prov_jakarta: or (id_provinsi = :id_prov_jakarta: and id_kota_kabupaten is null))";
        $bind["id_prov_jakarta"] = 31;

        if (!is_null($validatedRequest["id_provinsi"])) {
            $strWhere .= " and id_provinsi = :idprovinsi:";
            $bind["idprovinsi"] = $validatedRequest["id_provinsi"];
        }

        if (!is_null($validatedRequest["id_kota_kabupaten"])) {
            $strWhere .= " and id_kota_kabupaten = :idkotakabupaten:";
            $bind["idkotakabupaten"] = $validatedRequest["id_kota_kabupaten"];
        }

        if (!is_null($validatedRequest["keyword"])) {
            $strWhere .= " and lower(nama) LIKE :keyword:";
            $bind["keyword"] = "%" . mb_strtolower($validatedRequest["keyword"]) . "%";
        }

        if($validatedRequest['id_produk']) {
            if($validatedRequest['id_produk'] == 9) {
                $strWhere .= ' AND id_tipe IN ({id_tipes:array})';
                $bind['id_tipes'] = array_values(['1', '2']);
            }
        }

        if($validatedRequest['keluhan_type']) {
            $strWhere .= ' AND keluhan_type = :keluhan_type:';
            $bind['keluhan_type'] = $validatedRequest['keluhan_type'];
        }

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

        $listInstansi = Instansi::find(
            [
                "columns" => "id as id_instansi, nama, id_provinsi, id_kota_kabupaten",
                $strWhere,
                "bind" => $bind,
                "limit" => $validatedRequest["limit"],
                "offset" => $validatedRequest["offset"],
            ]
        );

        if($validatedRequest['rewrite_pemerintah']) {
            if($validatedRequest['rewrite_pemerintah'] == 1) {
                $listInstansi = $listInstansi->toArray();
                foreach ($listInstansi as $key => $value) {
                    $listInstansi[$key]['nama'] = str_replace('Pemerintah ', '', $value['nama']);
                }
            }
        }

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

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

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

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

        $strWhere = "id_tipe = :idtipe: and id_provinsi is not null";
        $bind["idtipe"] = 1;

        if (!is_null($validatedRequest["keyword"])) {
            $strWhere .= " and lower(nama) like :keyword:";
            $bind["keyword"] = '%' . mb_strtolower($params["keyword"]) . '%';
        }

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

        $listProvinsi = Instansi::find(
            [
                "columns" => "id_provinsi, replace(nama,'Pemerintah ','') as nama_provinsi",
                $strWhere,
                "bind" => $bind,
                "order" => "id_provinsi",
            ]
        );

        return new PaginatedResponse($listProvinsi, $totalCount, 0, 0);
    }

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

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

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

        $strWhere = "deleted_at is null and id_tipe = :idtipe: and id_provinsi = :idprovinsi: and id_kota_kabupaten is not null and id_provinsi <> :id_prov_jakarta:";
        $bind["idtipe"] = 2;
        $bind["idprovinsi"] = $validatedRequest["id_provinsi"];
        $bind["id_prov_jakarta"] = 31;

        if (!is_null($validatedRequest["keyword"])) {
            $strWhere .= " and lower(nama) like :keyword:";
            $bind["keyword"] = '%' . mb_strtolower($params["keyword"]) . '%';
        }

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

        $listKotaKabupaten = Instansi::find(
            [
                "columns" => "id_kota_kabupaten, replace(nama,'Pemerintah ','') as nama_kota_kabupaten",
                $strWhere,
                "bind" => $bind,
                "order" => "id_kota_kabupaten",
            ]
        );

        return new PaginatedResponse($listKotaKabupaten, $totalCount, 0, 0);
    }

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

        $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 30;
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : null;
        $params['topik_type'] = array_key_exists('topik_type', $params) ? $params['topik_type'] : null;
        $params['topik_sub_type'] = array_key_exists('topik_sub_type', $params) ? $params['topik_sub_type'] : null;
        $params['objek_type'] = array_key_exists('objek_type', $params) ? $params['objek_type'] : null;
        $params['id_kabupaten_kota'] = array_key_exists('id_kabupaten_kota', $params) ? $params['id_kabupaten_kota'] : null;
        $params['id_jenjang'] = array_key_exists('id_jenjang', $params) ? $params['id_jenjang'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'topik_type' => ['validators' => ['required']],
                'topik_sub_type' => ['validators' => []],
                'objek_type' => ['validators' => []],
                'id_kabupaten_kota' => ['validators' => []],
                'id_jenjang' => ['validators' => ['digit']],
            ]
        );

        switch ($validatedRequest["topik_type"]) {
            case TopikDiskusiBansos::TYPE_IKN:
                $strWhere = "deleted_at IS NULL";
                $bind = [];

                if (!is_null($validatedRequest["keyword"])) {
                    $strWhere .= " AND LOWER(nama) LIKE :keyword:";
                    $bind["keyword"] = "%" . mb_strtolower($validatedRequest["keyword"]) . "%";
                }

                $strWhere .= " AND keluhan_type LIKE :keluhan_type:";
                $bind["keluhan_type"] = '%' . TopikDiskusiBansos::TYPE_IKN . '%';

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

                $result = Instansi::find(
                    [
                        "columns" => "id, nama",
                        $strWhere,
                        "bind" => $bind,
                        "limit" => $validatedRequest["limit"],
                        "offset" => $validatedRequest["offset"],
                    ]
                );
                break;
            case TopikDiskusiBansos::TYPE_DESA:
                $wheres = [];
                $bind = [];

                if (!is_null($validatedRequest["keyword"])) {
                    array_push($wheres, "LOWER(nama_desa) LIKE :keyword:");
                    $bind["keyword"] = "%" . mb_strtolower($validatedRequest["keyword"]) . "%";
                }

                if (!is_null($validatedRequest["id_kabupaten_kota"])) {
                    array_push($wheres, "kode_pemda = :kode_pemda:");
                    $bind["kode_pemda"] = $validatedRequest["id_kabupaten_kota"];
                }

                $strWhere = implode(" AND ", $wheres);

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

                $result = OmspanMasterDesa::find(
                    [
                        "columns" => "id, nama_desa as nama, nama_provinsi, nama_pemda, nama_kecamatan",
                        $strWhere,
                        "bind" => $bind,
                        "limit" => $validatedRequest["limit"],
                        "offset" => $validatedRequest["offset"],
                    ]
                );
                break;
            case TopikDiskusiBansos::TYPE_KAMPUS:
                $tipeInstansi = InstansiTipe::findByNama(InstansiTipe::INSTANSI_TIPE_PERGURUAN_TINGGI);
                $wheres = ["id_tipe = :id_tipe:", "deleted_at IS NULL"];
                $bind = ["id_tipe" => $tipeInstansi->id];

                if (!is_null($validatedRequest["keyword"])) {
                    array_push($wheres, "LOWER(nama) LIKE :keyword:");
                    $bind["keyword"] = "%" . mb_strtolower($validatedRequest["keyword"]) . "%";
                }

                $strWhere = implode(" AND ", $wheres);

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

                $result = Instansi::find([
                    'columns' => "id, nama",
                    $strWhere,
                    'bind' => $bind,
                    'limit' => $validatedRequest['limit'],
                    'offset' => $validatedRequest['offset']
                ]);
                break;
            case TopikDiskusiBansos::TYPE_PEMILU:
                $strWhere = 'tipe_objek = ?0';
                $bind = [$validatedRequest['topik_sub_type']];
                $totalCount = ObjekKeluhanPemilu::count([
                    $strWhere,
                    "bind" => $bind
                ]);
                $result = ObjekKeluhanPemilu::find([
                    $strWhere,
                    'bind' => $bind,
                    'limit' => $validatedRequest['limit'],
                    'offset' => $validatedRequest['offset'],
                    'order' => 'view_order'
                ]);
                break;
            case TopikDiskusiBansos::TYPE_BPJS_KESEHATAN:
                if($validatedRequest['objek_type'] == DiskusiObjek::TYPE_PUSKESMAS) {
                    $strWhere = '';
                    $bind = [];
                    if($validatedRequest['keyword'] != null) {
                        $strWhere = 'namapuskesmas ILIKE :keyword:';
                        $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
                    }
                    $totalCount = BpjsPuskesmas::count([
                        $strWhere,
                        "bind" => $bind
                    ]);
                    $result = BpjsPuskesmas::find([
                        'columns' => 'kodepuskesmas AS kode, namapuskesmas AS nama',
                        'conditions' => $strWhere,
                        'bind' => $bind,
                        'limit' => $validatedRequest['limit'],
                        'offset' => $validatedRequest['offset'],
                        'order' => 'namapuskesmas'
                    ]);
                }
                elseif($validatedRequest['objek_type'] == DiskusiObjek::TYPE_RUMAH_SAKIT) {
                    $strWhere = '';
                    $bind = [];
                    if($validatedRequest['keyword'] != null) {
                        $strWhere = 'nama ILIKE :keyword:';
                        $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
                    }
                    $totalCount = RumahSakitOnline::count([
                        $strWhere,
                        "bind" => $bind
                    ]);
                    $result = RumahSakitOnline::find([
                        'columns' => 'kode, nama',
                        'conditions' => $strWhere,
                        'bind' => $bind,
                        'limit' => $validatedRequest['limit'],
                        'offset' => $validatedRequest['offset'],
                        'order' => 'nama'
                    ]);
                }
                break;
            case TopikDiskusiBansos::TYPE_PENDIDIKAN:
                $bind = [];
                if($validatedRequest['objek_type'] == DiskusiObjek::TYPE_SEKOLAH)
                {
                    $strWhere = "deleted_at IS NULL";
                    if (!empty($validatedRequest["keyword"])) {
                        $strWhere .= " AND LOWER(nama) LIKE :keyword:";
                        $bind["keyword"] = "%" . mb_strtolower($validatedRequest["keyword"]) . "%";
                    }
                    if (!empty($validatedRequest["id_kabupaten_kota"])) {
                        $strWhere .= " AND kode_kabupaten_dagri = :id_kabupaten_kota:";
                        $bind["id_kabupaten_kota"] = $validatedRequest["id_kabupaten_kota"];
                    }
                    if (!empty($validatedRequest["id_jenjang"])) {
                        $strWhere .= " AND jenjang_id = :id_jenjang:";
                        $bind["id_jenjang"] = $validatedRequest["id_jenjang"];
                    }

                    $result = Sekolah::find(
                        [
                            "columns" => "npsn as id, nama, kecamatan",
                            $strWhere,
                            "bind" => $bind,
                            "limit" => $validatedRequest["limit"],
                            "offset" => $validatedRequest["offset"],
                        ]
                    );

                    $totalCount = Sekolah::count(
                        [
                            $strWhere,
                            "bind" => $bind,
                        ]
                    );
                }
                elseif($validatedRequest['objek_type'] == DiskusiObjek::TYPE_MADRASAH)
                {
                    $strWhere = "1=1";
                    if (!empty($validatedRequest["keyword"])) {
                        $strWhere .= " AND LOWER(name) LIKE :keyword:";
                        $bind["keyword"] = "%" . mb_strtolower($validatedRequest["keyword"]) . "%";
                    }
                    if (!empty($validatedRequest["id_kabupaten_kota"])) {
                        $strWhere .= " AND city_id = :id_kabupaten_kota:";
                        $bind["id_kabupaten_kota"] = $validatedRequest["id_kabupaten_kota"];
                    }
                    if (!empty($validatedRequest["id_jenjang"])) {
                        $strWhere .= " AND jenjang_id = :id_jenjang:";
                        $bind["id_jenjang"] = $validatedRequest["id_jenjang"];
                    }

                    $result = Madrasah::find(
                        [
                            "columns" => "id, name as nama, district as kecamatan",
                            $strWhere,
                            "bind" => $bind,
                            "limit" => $validatedRequest["limit"],
                            "offset" => $validatedRequest["offset"],
                        ]
                    );

                    $totalCount = Madrasah::count(
                        [
                            $strWhere,
                            "bind" => $bind,
                        ]
                    );
                }
                else
                {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tipe objek tidak valid");
                }
                break;
            default;
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tipe topik tidak valid");
        }

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

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

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

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

        $where = $bind = [];

        switch ($validatedRequest['topik_type']) {
            case TopikDiskusiBansos::TYPE_KAMPUS:
                $instansiTipe = InstansiTipe::findByNama(InstansiTipe::INSTANSI_TIPE_PERGURUAN_TINGGI);
                array_push($where, "(LOWER(nama) LIKE :irjendikbud: OR LOWER(nama) LIKE :irjenmenag: OR id_tipe = :id_tipe:)");
                $bind['irjendikbud'] = mb_strtolower(DiskusiBansos::IRJENKEMDIKBUD_NAME);
                $bind['irjenmenag'] = mb_strtolower(DiskusiBansos::IRJENKEMENAG_NAME);
                $bind['id_tipe'] = $instansiTipe->id;
                break;
            default:
                break;
        }

        if (!is_null($validatedRequest['keyword'])) {
            array_push($where, "LOWER(nama) LIKE :keyword:");
            $bind["keyword"] = "%" . mb_strtolower($validatedRequest["keyword"]) . "%";
        }

        array_push($where, "deleted_at IS NULL");

        $strWhere = implode(" AND ", $where);
        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];

        $total = Instansi::count([
            'conditions' => $strWhere,
            'bind' => $bind
        ]);
        $list = Instansi::find([
            'conditions' => $strWhere,
            'bind' => $bind,
            'limit' => $limit,
            'offset' => $offset
        ]);

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

    public function submitReport()
    {
        try {
            $this->db->begin();

            date_default_timezone_set('Asia/Jakarta');
            $this->checkScopes(['discussion.create']);

            $params = $this->request->getPost();
            $params['jenis_gratifikasi'] = array_key_exists('jenis_gratifikasi', $params) ? $params['jenis_gratifikasi'] : null;
            $params['keterangan_gratifikasi'] = array_key_exists('keterangan_gratifikasi', $params) ? $params['keterangan_gratifikasi'] : null;
            $params['total_nilai_gratifikasi'] = array_key_exists('total_nilai_gratifikasi', $params) ? $params['total_nilai_gratifikasi'] : 0;
            $params['objek_pemilu_id'] = array_key_exists('objek_pemilu_id', $params) ? $params['objek_pemilu_id'] : 0;
            $params['objek_type'] = array_key_exists('objek_type', $params) ? $params['objek_type'] : null;
            $validatedRequest = $this->validate(
                $params,
                [
                    'produk_id' => ['validators' => ['required', 'digit']],
                    'judul' => ['validators' => ['required', [
                        'name' => 'max_string',
                        'params' => 255
                    ]]],
                    'is_anonymous' => ['validators' => ['required', 'digit']],
                    'konten_diskusi' => ['validators' => ['required']],
                    'id_instansi' => ['validators' => ['required', 'digit']],
                    'link_terkait' => ['validators' => []],
                    'objek_id' => ['validators' => ['required', 'digit']],
                    'objek_type' => ['validators' => []],
                    'nama_objek' => ['validators' => ['required']],
                    'nik' => ['validators' => []],
                    'id_topik' => ['validators' => ['required']],
                    'phone' => ['validators' => ['required']],
                    'jenis_gratifikasi' => ['validators' => []],
                    'keterangan_gratifikasi' => ['validators' => []],
                    'total_nilai_gratifikasi' => ['validators' => ['numeric']],
                    'objek_pemilu_id' => ['validators' => ['digit']],
                ]
            );

            $topikDiskusi = TopikDiskusiBansos::findFirst("id = " . $validatedRequest["id_topik"]);
            if (empty($topikDiskusi)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Topik diskusi tidak valid");
            }
            $typeTopik = $topikDiskusi->type;
            $validatedRequest['topik_type'] = $typeTopik;

            if($typeTopik != TopikDiskusiBansos::TYPE_PELABUHAN) {
                $this->validate(
                    $params,
                    [
                        'nik' => ['validators' => ['required']],
                    ]
                );
            }

            $this->AdditionalValidationBansos($validatedRequest);

            $service = new DiskusiService($this->config, $this->shared, $this->request, $this->minio);
            $result = $service->create($validatedRequest, 0);

            if ($result['res']) {
                $diskusi = Diskusi::findFirst(
                    [
                        "id = :id_diskusi:",
                        "bind" => [
                            "id_diskusi" => $result['diskusi']->id
                        ]
                    ]
                );

                $diskusi->is_attachment_public = 0;
                $result['res'] = $diskusi->save();
            }

            $isAllSuccess = $result['res'] && $result['saveRespon'] && $result['saveDiskusi'];

            if ($isAllSuccess) {
                $idInstansi = $validatedRequest['id_instansi'];

                // untuk type topik keluhan desa, instansi merupakan inspektorat kabupaten sesuai lokasi desa yang menjadi objek keluhan
                if ($typeTopik === TopikDiskusiBansos::TYPE_DESA) {
                    $objectDesa = OmspanMasterDesa::findFirst('id = ' . $validatedRequest["objek_id"]);
                    if (empty($objectDesa)) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Objek keluhan Desa tidak ditemukan");
                    }

                    $instansi = Instansi::findFirst([
                       'conditions' => 'id_kota_kabupaten = :kode_pemda: AND id_tipe = 2 AND deleted_at IS NULL',
                        'bind' => [
                            'kode_pemda' => $objectDesa->kode_pemda
                        ]
                    ]);
                    if (empty($instansi)) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Inspektorat Kabupaten tidak ditemukan");
                    }
                    $idInstansi = $instansi->id;
                } elseif ($typeTopik === TopikDiskusiBansos::TYPE_BPJS_KESEHATAN) {
                    if($validatedRequest['objek_type'] == null) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tipe Objek harus diisi");
                    }

                    //NOTE: do not delete the single quote (') surrounding the value below, kode and kodepuskesmas are varchar, so it must equal to string
                    if($validatedRequest['objek_type'] == DiskusiObjek::TYPE_RUMAH_SAKIT) {
                        $objectBpjs = RumahSakitOnline::findFirst('kode = \'' . $validatedRequest['objek_id'] . '\'');
                        if(empty($objectBpjs)) {
                            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Objek keluhan BPJS tidak ditemukan");
                        }
                    }
                    elseif($validatedRequest['objek_type'] == DiskusiObjek::TYPE_PUSKESMAS) {
                        $objectBpjs = BpjsPuskesmas::findFirst('kodepuskesmas = \'' . $validatedRequest['objek_id'] . '\'');
                        if(empty($objectBpjs)) {
                            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Objek keluhan BPJS tidak ditemukan");
                        }
                    }
                    else {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tipe Objek tidak valid");
                    }

                    $instansi = Instansi::findFirst([
                       'conditions' => 'nama = :nama: AND id_tipe = 3 AND deleted_at IS NULL',
                        'bind' => [
                            'nama' => Instansi::BPJS_KESEHATAN_NAME
                        ]
                    ]);
                    if (empty($instansi)) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Inspektorat Kabupaten tidak ditemukan");
                    }
                    $idInstansi = $instansi->id;
                } elseif ($typeTopik === TopikDiskusiBansos::TYPE_PENDIDIKAN) { // diskusi pendidikan diawal akan di-set ke KPK, untuk kemudian diteruskan oleh admin ke instansi terkait
                    if($validatedRequest['objek_type'] == null) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tipe Objek harus diisi");
                    }

                    if($validatedRequest['objek_type'] == DiskusiObjek::TYPE_SEKOLAH) {
                        $objectPendidikan = Sekolah::findFirst([
                            "conditions" => "npsn = :npsn:",
                            "bind" => ["npsn" => $validatedRequest['objek_id']]
                        ]);
                        if(empty($objectPendidikan)) {
                            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Objek keluhan Pendidikan tidak ditemukan");
                        }
                    }
                    elseif($validatedRequest['objek_type'] == DiskusiObjek::TYPE_MADRASAH) {
                        $objectPendidikan = Madrasah::findFirst([
                            "conditions" => "id = :id:",
                            "bind" => ["id" => $validatedRequest['objek_id']]
                        ]);
                        if(empty($objectPendidikan)) {
                            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Objek keluhan Pendidikan tidak ditemukan");
                        }
                    }
                    else {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tipe Objek tidak valid");
                    }
                    $instansi = Instansi::findFirst([
                        'conditions' => 'LOWER(nama) = :nama: AND deleted_at IS NULL',
                        'bind' => [
                            'nama' => Instansi::KPK_NAME
                        ]
                    ]);
                    if (empty($instansi)) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi terkait keluhan tidak ditemukan");
                    }
                    $idInstansi = $instansi->id;
                } else {
                    $instansi = Instansi::findFirst([
                        'conditions' => 'id = :id_instansi: AND deleted_at IS NULL',
                        'bind' => [
                            'id_instansi' => $idInstansi
                        ]
                    ]);
                    if (empty($instansi)) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi terkait keluhan tidak ditemukan");
                    }
                }

                $params["nik"] = array_key_exists("nik", $params) ? $validatedRequest["nik"] : "";
                $now = date('Y-m-d H:i:s');
                $diskusiBansos = new DiskusiBansos();
                $diskusiBansos->id_diskusi = $result['diskusi']->id;
                $diskusiBansos->id_instansi = $diskusiBansos->id_instansi_origin = $idInstansi;
                $diskusiBansos->status = DiskusiBansos::NEW;
                $diskusiBansos->id_topik = $validatedRequest["id_topik"];
                $diskusiBansos->phone = $validatedRequest["phone"];
                $diskusiBansos->created_at = $now;
                $diskusiBansos->updated_at = $now;
                $saveDiskusiBansos = $diskusiBansos->create();

                $saveDiskusiBansosNIK = true;
                if (!empty($validatedRequest["nik"])) {
                    $validatedRequest["nik"] = explode(";", $validatedRequest["nik"]);
                    foreach ($validatedRequest["nik"] as $nikpkh) {
                        if ($saveDiskusiBansosNIK) {
                            $objNIKPKH = explode(",", $nikpkh);
                            $nik = $objNIKPKH[0];
                            $pkh = $objNIKPKH[1];

                            $diskusiBansosNIK = new DiskusiBansosNIK();
                            $diskusiBansosNIK->id_diskusi_bansos = $diskusiBansos->id;
                            $diskusiBansosNIK->nik = $nik;
                            $diskusiBansosNIK->pkh = $pkh;
                            $saveDiskusiBansosNIK = $diskusiBansosNIK->create();
                        }
                    }
                }

                $diskusiBansosHistory = new DiskusiBansosHistory();
                $diskusiBansosHistory->id_diskusi_bansos = $diskusiBansos->id;
                $diskusiBansosHistory->keterangan = 'Membuat Keluhan Bansos';
                $diskusiBansosHistory->status = $diskusiBansos->status;
                $diskusiBansosHistory->id_instansi = $diskusiBansos->id_instansi;
                $diskusiBansosHistory->id_user = $this->shared->user->um_id;
                $diskusiBansosHistory->create();

                //save detail keluhan pemilu
                if($typeTopik == TopikDiskusiBansos::TYPE_PEMILU) {
                    $keluhanPemilu = new DiskusiKeluhanPemilu();
                    $keluhanPemilu->id_diskusi = $result['diskusi']->id;
                    $keluhanPemilu->id_diskusi_bansos = $diskusiBansos->id;
                    $keluhanPemilu->id_objek_pemilu = $validatedRequest['objek_pemilu_id'];
                    $keluhanPemilu->tipe_objek_pemilu = $topikDiskusi->subtype;
                    $keluhanPemilu->jenis_gratifikasi = $validatedRequest['jenis_gratifikasi'];
                    $keluhanPemilu->keterangan_gratifikasi = $validatedRequest['keterangan_gratifikasi'];
                    $keluhanPemilu->total_nilai_gratifikasi = $validatedRequest['total_nilai_gratifikasi'];
                    $keluhanPemilu->isresponded = 0;
                    $keluhanPemilu->created_at = $now;
                    $keluhanPemilu->updated_at = $now;
                    $keluhanPemilu->create();
                }
            }


            if ($isAllSuccess && $saveDiskusiBansos && $saveDiskusiBansosNIK) {
                try {
                    //$service->notifyStakeHolder($result['diskusi'], $result['produk'], $validatedRequest);
                    $service->notifyKorwilPemdaUser($diskusiBansos, DiskusiService::BANSOS_NOTIFICATION_CREATE);
                } catch (\Exception $e) {
                    // pengiriman notifikasi gagal
                }
                $this->db->commit();
                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => [
                            "diskusi_uuid" => $result['diskusi']->uuid,
                            "diskusi_id" => $result['diskusi']->id,
                        ],
                    ]
                );
            } else {
                if (!$result['res']) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Error", $result['diskusi']->getMessages());
                }

                if (!$result['saveRespon']) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Response Error", $result['diskusi']->getMessages());
                }

                if (!$result['saveDiskusi']) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Detail Error", $result['diskusi']->getMessages());
                }

                if (!$saveDiskusiBansos) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Bansos Error", $result['diskusi']->getMessages());
                }

                if (!$saveDiskusiBansosNIK) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Bansos NIK Error", $result['diskusi']->getMessages());
                }

                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "SQL Error", $result['diskusi']->getMessages());
            }
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

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

        $params['id_provinsi'] = array_key_exists('id_provinsi', $params) ? $params['id_provinsi'] : null;
        $params['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;

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

        $strWhere = "deleted_at is null and kode_provinsi = :idprovinsi:";
        $bind["idprovinsi"] = $validatedRequest["id_provinsi"];

        if (!is_null($validatedRequest["id_kota_kabupaten"])) {
            $strWhere .= " and kode_kota_kabupaten = :id_kota_kabupaten:";
            $bind["id_kota_kabupaten"] = $validatedRequest["id_kota_kabupaten"];
        } else {
            $strWhere .= " and kode_kota_kabupaten = :id_kota_kabupaten:";
            $bind["id_kota_kabupaten"] = $validatedRequest["id_provinsi"] . "00";
        }

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

        $listDetailBansos = KemdagriBansosCovid19::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "id",
            ]
        );

        return new PaginatedResponse($listDetailBansos, $totalCount, 0, 0);
    }

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

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

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

        $instansi = Instansi::findFirst(
            [
                "deleted_at is null and id = :instansi_id:",
                "bind" => [
                    "instansi_id" => $validatedRequest['id_instansi'],
                ],
            ]
        );

        if (empty($instansi)) {
            throw new ValidationException([
                [
                    'field' => 'id_instansi',
                    'message' => 'Instansi dengan ID (' . $params['id_instansi'] . ') tidak ditemukan.',
                ],
            ]);
        }

        $strWhere = "deleted_at is null and kode_provinsi = :idprovinsi:";
        $bind["idprovinsi"] = $instansi->id_provinsi;

        if (!is_null($instansi->id_kota_kabupaten)) {
            $strWhere .= " and kode_kota_kabupaten = :id_kota_kabupaten:";
            $bind["id_kota_kabupaten"] = $instansi->id_kota_kabupaten;
        } else {
            $strWhere .= " and kode_kota_kabupaten = :id_kota_kabupaten:";
            $bind["id_kota_kabupaten"] = $instansi->id_provinsi . "00";
        }

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

        $listDetailBansos = KemdagriBansosCovid19::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "id",
            ]
        );

        if ($this->shared) {
            $service = new ActivityService($this->shared, $this->logger);
            $service->createActivity(UserActivity::PRODUK_BANSOS, $params['id_instansi']);
        }

        return new PaginatedResponse($listDetailBansos, $totalCount, 0, 0);
    }

    public function listLaporanBansosAll()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkScopes(['bansos.view_all']);

        $validatedRequest = $this->validateRequestForList();

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

        $conditions = $this->conditionBuilderForList($validatedRequest, $strWhere, $bind);
        $strWhere = $conditions->where;
        $bind = $conditions->bind;

        $totalResult = ViewKeluhan::query()
            ->columns("count(*) as counter")
            ->where($strWhere)
            ->bind($bind)
            ->execute();
        $totalResult = $totalResult[0]['counter'];

        $query = ViewKeluhan::query()
            ->where($strWhere)
            ->bind($bind)
            ->limit($validatedRequest["limit"], $validatedRequest["offset"]);
        $query = $this->sortBuilderForList($validatedRequest, $query);

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

        if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_PEMILU) {
            foreach ($result as $key => $value){
                $result[$key]['instansi_asal'] = str_replace('Pemerintah ', '', $value['instansi_asal']);
                $result[$key]['nama_objek'] = str_replace('Pemerintah ', '', $value['nama_objek']);
            }
        }

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

    public function detailInfo()
    {
        date_default_timezone_set('Asia/Jakarta');
        $params = $this->request->get();

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

        if (!$this->isAuthenticated()) {
            $canAccess = false;
        } else {
            $diskusiBansos = DiskusiBansos::findFirst($validatedRequest['id_diskusi_bansos']);

            if (empty($diskusiBansos)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data tidak ditemukan");
            }

            $diskusi = Diskusi::findFirst($diskusiBansos->id_diskusi);
            if (empty($diskusi)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data tidak ditemukan");
            }

            $topik = TopikDiskusiBansos::findFirst($diskusiBansos->id_topik);

            if (empty($topik)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data tidak ditemukan");
            }

            if ($this->shared->user->um_id != $diskusi->user_id) {
                $canAccess = $this->hasOneOfScopes(['bansos.view_all', 'bansos.view_assigned', 'bansos.confirm_all', 'bansos.confirm_assigned', 'bansos.follow_up', 'covid.dinkes_pemda', self::SCOPE_FOLLOWUP_KELUHAN_IKN, self::SCOPE_FOLLOWUP_KELUHAN_DESA, self::SCOPE_FOLLOWUP_KELUHAN_PENDIDIKAN]);
            } else {
                $canAccess = true;
            }

            //$canAccess = false;

            if ($canAccess) {
                $scopes = explode(' ', $this->shared->jwt->scope);

                if (in_array('bansos.view_all', $scopes) || in_array('bansos.confirm_all', $scopes) || in_array('covid.dinkes_pemda', $scopes)) {
                    $canAccess = true;
                } elseif (in_array('bansos.view_assigned', $scopes) || in_array('bansos.confirm_assigned', $scopes)) {
                    $teamInstansi = KorwilTeamInstansi::findFirst(
                        [
                            "columns" => "instansi_id",
                            "team_id in (SELECT x.team_id FROM KorwilTeamUser x WHERE x.user_id = :user_id:) AND instansi_id = :instansi_id:",
                            "bind" => [
                                "user_id" => $this->shared->user->um_id,
                                "instansi_id" => $diskusiBansos->id_instansi,
                            ],
                        ]
                    );

                    $canAccess = !empty($teamInstansi);
                } elseif (in_array('bansos.follow_up', $scopes)
                    || in_array(self::SCOPE_FOLLOWUP_KELUHAN_IKN, $scopes)
                    || in_array(self::SCOPE_FOLLOWUP_KELUHAN_DESA, $scopes)
                    || in_array(self::SCOPE_FOLLOWUP_KELUHAN_PENDIDIKAN, $scopes)
                ) {
                    $canAccess = $this->shared->user->id_instansi == $diskusiBansos->id_instansi;
                } else {
                    $canAccess = false;
                }
            }
            //$canAccess = true;

            if($canAccess) {
                if($topik->type == TopikDiskusiBansos::TYPE_BANSOS || $topik->type == TopikDiskusiBansos::TYPE_DESA || $topik->type == TopikDiskusiBansos::TYPE_IKN || $topik->type == TopikDiskusiBansos::TYPE_KAMPUS || $topik->type == TopikDiskusiBansos::TYPE_PENDIDIKAN) {
                    if($this->hasOneOfScopes(['bansos.view_all', 'bansos.view_assigned', 'bansos.confirm_all', 'bansos.confirm_assigned'])) {
                        $canAccess = true;
                    }
                    else if($topik->type == TopikDiskusiBansos::TYPE_BANSOS && $this->hasOneOfScopes(['bansos.follow_up'])) {
                        $canAccess = $this->shared->user->id_instansi == $diskusiBansos->id_instansi;
                    }
                    else if($topik->type == TopikDiskusiBansos::TYPE_IKN && $this->hasOneOfScopes([self::SCOPE_FOLLOWUP_KELUHAN_IKN])) {
                        $canAccess = $this->shared->user->id_instansi == $diskusiBansos->id_instansi;
                    }
                    else if($topik->type == TopikDiskusiBansos::TYPE_DESA && $this->hasOneOfScopes([self::SCOPE_FOLLOWUP_KELUHAN_DESA])) {
                        $canAccess = $this->shared->user->id_instansi == $diskusiBansos->id_instansi;
                    }
                    else if($topik->type == TopikDiskusiBansos::TYPE_KAMPUS && $this->hasOneOfScopes([self::SCOPE_FOLLOWUP_KELUHAN_KAMPUS])) {
                        $canAccess = $this->shared->user->id_instansi == $diskusiBansos->id_instansi;
                    }
                    else if($topik->type == TopikDiskusiBansos::TYPE_PENDIDIKAN && $this->hasOneOfScopes([self::SCOPE_FOLLOWUP_KELUHAN_PENDIDIKAN])) {
                        $canAccess = $this->shared->user->id_instansi == $diskusiBansos->id_instansi;
                    }
                    else $canAccess = false;
                }
            }
        }

        if (!$canAccess) {
            $result = [];
        } else {
            $result = $diskusiBansos->RetrieveListDetailKonfirmasiBansos();

            if($result['diskusi']->is_anonymous == 1) {
                if(!$this->hasOneOfScopes(['bansos.view_all', 'bansos.view_assigned', 'bansos.confirm_all', 'bansos.confirm_assigned']) && $this->hasOneOfScopes(['bansos.follow_up', 'bansos.follow_up_view'])) {
                    unset($result['phone']);
                    unset($result['diskusi_bansos_nik']);
                    unset($result['user']);
                    $result['diskusi']->user_id = null;
                }
            }
        }

        return new SimpleResponse($result);
    }

    public function listLaporanBansosAssigned()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkOneOfScopes(['bansos.view_assigned', 'covid.dinkes_pemda']);

        $validatedRequest = $this->validateRequestForList();

        $userID = $this->shared->user->um_id;
        $strWhere = "1 = 1";
        $bind = [];

        $listInstansi = KorwilTeamInstansi::find(
            [
                "columns" => "instansi_id",
                "team_id in (SELECT x.team_id FROM KorwilTeamUser x WHERE x.user_id = :user_id:)",
                "bind" => [
                    "user_id" => $userID,
                ],
            ]
        );

        $arr = [];
        foreach ($listInstansi as $data) {
            $arr[] = $data->instansi_id;
        }

        $strListInstansi = implode(",", $arr);
        if (!empty($strListInstansi)) {
            $strWhere .= " and (id_instansi_respon in (" . $strListInstansi . "))";
        }

        $conditions = $this->conditionBuilderForList($validatedRequest, $strWhere, $bind);
        $strWhere = $conditions->where;
        $bind = $conditions->bind;

        $totalResult = ViewKeluhan::query()
            ->columns("count(*) as counter")
            ->where($strWhere)
            ->bind($bind)
            ->execute();
        $totalResult = $totalResult[0]['counter'];

        $query = ViewKeluhan::query()
            ->where($strWhere)
            ->bind($bind)
            ->limit($validatedRequest["limit"], $validatedRequest["offset"]);
        $query = $this->sortBuilderForList($validatedRequest, $query);

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

        if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_PEMILU) {
            foreach ($result as $key => $value){
                $result[$key]['instansi_asal'] = str_replace('Pemerintah ', '', $value['instansi_asal']);
                $result[$key]['nama_objek'] = str_replace('Pemerintah ', '', $value['nama_objek']);
            }
        }

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

    public function listLaporanBansosFollowUp()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkOneOfScopes(['bansos.follow_up_view', 'covid.dinkes_pemda', self::SCOPE_FOLLOWUP_KELUHAN_DESA, self::SCOPE_FOLLOWUP_KELUHAN_IKN, self::SCOPE_FOLLOWUP_KELUHAN_KAMPUS, self::SCOPE_FOLLOWUP_KELUHAN_PELABUHAN, self::SCOPE_MONITORING_KELUHAN_PEMILU, self::SCOPE_FOLLOWUP_KELUHAN_BPJS, self::SCOPE_FOLLOWUP_KELUHAN_PENDIDIKAN]);

        $validatedRequest = $this->validateRequestForList();

        if(empty($this->shared->user->id_instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak memiliki Instansi");
        }

        $strWhere = "id_instansi_respon = :id_instansi: and status <> :status_new:";
        $bind = [
            'id_instansi' => $this->shared->user->id_instansi,
            'status_new' => DiskusiBansos::NEW
        ];

        $conditions = $this->conditionBuilderForList($validatedRequest, $strWhere, $bind);
        $strWhere = $conditions->where;
        $bind = $conditions->bind;

        $totalResult = ViewKeluhan::query()
            ->columns("count(*) as counter")
            ->where($strWhere)
            ->bind($bind)
            ->execute();
        $totalResult = $totalResult[0]['counter'];

        $query = ViewKeluhan::query()
            ->where($strWhere)
            ->bind($bind)
            ->limit($validatedRequest["limit"], $validatedRequest["offset"]);
        $query = $this->sortBuilderForList($validatedRequest, $query);

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

        if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_PEMILU) {
            foreach ($result as $key => $value){
                $result[$key]['instansi_asal'] = str_replace('Pemerintah ', '', $value['instansi_asal']);
                $result[$key]['nama_objek'] = str_replace('Pemerintah ', '', $value['nama_objek']);
            }
        }

        foreach ($result as $key => $value) {
            if($value['is_anonymous'] == '1') {
                $result[$key]['pelapor'] = "Anonim";
                $result[$key]['nik'] = "";
                $result[$key]['phone'] = "";
            }
        }

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

    public function listLaporanBansosMonitoring()
    {
        $this->checkOneOfScopes([self::SCOPE_MONITORING_KELUHAN_KAMPUS, self::SCOPE_MONITORING_KELUHAN_PELABUHAN]);

        $validatedRequest = $this->validateRequestForList();

        $topikType = null;
        $topikIds = [];
        if ($this->hasScopes([self::SCOPE_MONITORING_KELUHAN_KAMPUS])) {
            $topikType = TopikDiskusiBansos::TYPE_KAMPUS;
        }
        if ($this->hasScopes([self::SCOPE_MONITORING_KELUHAN_PELABUHAN])) {
            $topikType = TopikDiskusiBansos::TYPE_PELABUHAN;

            if (isset($this->shared->user)) {
                $topikIds = ScopesInstansiTopik::topikIds(
                    self::SCOPE_MONITORING_KELUHAN_PELABUHAN,
                    $this->shared->user->id_instansi
                );
            }
        }
        $strWhere = "type = :topik_type: and status <> :status_new:";
        $bind = [
            'topik_type' => $topikType,
            'status_new' => DiskusiBansos::NEW
        ];
        if (!empty($topikIds)) {
            $strWhere .= " and id_topik in ({ids_topik:array})";
            $bind["ids_topik"] = $topikIds;
        }

        $conditions = $this->conditionBuilderForList($validatedRequest, $strWhere, $bind);
        $strWhere = $conditions->where;
        $bind = $conditions->bind;

        $totalResult = ViewKeluhan::query()
            ->columns("count(*) as counter")
            ->where($strWhere)
            ->bind($bind)
            ->execute();
        $totalResult = $totalResult[0]['counter'];

        $query = ViewKeluhan::query()
            ->where($strWhere)
            ->bind($bind)
            ->limit($validatedRequest["limit"], $validatedRequest["offset"]);
        $query = $this->sortBuilderForList($validatedRequest, $query);

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

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

    public function confirmLaporanBansosAll()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkScopes(['bansos.confirm_all']);

        $params = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $params,
            [
                'diskusi_bansos_id' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => ['required']],
                'status' => ['validators' => ['required', 'digit']],
            ]
        );

        $service = new DiskusiService($this->config, $this->shared, $this->request, $this->minio);

        $diskusiBansos = DiskusiBansos::findFirst(
            [
                "id = :diskusi_bansos_id: and deleted_at is null",
                "bind" => [
                    "diskusi_bansos_id" => $validatedRequest['diskusi_bansos_id'],
                ],
            ]
        );

        if (empty($diskusiBansos)) {
            throw new ValidationException([
                [
                    'field' => 'diskusi_bansos_id',
                    'message' => 'Diskusi Bansos dengan ID(' . $validatedRequest['diskusi_bansos_id'] . ') tidak ditemukan.',
                ],
            ]);
        }

        $idInstansi = $diskusiBansos->id_instansi;
        if (
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_PEMDA ||
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_KEMSOS ||
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_DUMASKPK ||
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_KEMENKOPUKM
        ) {
            $diskusiBansos->status = DiskusiBansos::CONFIRMED;
            $namaInstansi = '';

            if ($validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_KEMSOS) {
                $namaInstansi = DiskusiBansos::KEMSOS_NAME;
            } elseif ($validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_DUMASKPK) {
                $namaInstansi = DiskusiBansos::KPK_NAME;
            } elseif ($params['status'] == DIskusiBansos::CONFIRMED_TO_KEMENKOPUKM) {
                $namaInstansi = DiskusiBansos::KEMENKOPUKM_NAME;
            }

            if ($namaInstansi != '') {
                $instansi = Instansi::findFirst(
                    [
                        "lower(nama) = :nama_instansi:",
                        "bind" => [
                            "nama_instansi" => $namaInstansi
                        ]
                    ]
                );

                if (empty($instansi)) {
                    throw new ValidationException([
                        [
                            'field' => 'status',
                            'message' => 'Instansi tujuan tindak lanjut tidak dapat ditemukan',
                        ],
                    ]);
                }

                $idInstansi = $instansi->id;
            }

            $diskusiBansos->id_instansi = $idInstansi;
        } else {
            $diskusiBansos->status = $validatedRequest['status'];
        }

        $resultDiskusi = $diskusiBansos->save();

        $diskusiBansosHistory = new DiskusiBansosHistory();
        $diskusiBansosHistory->id_diskusi_bansos = $diskusiBansos->id;
        $diskusiBansosHistory->keterangan = $validatedRequest['keterangan'];
        $diskusiBansosHistory->status = $diskusiBansos->status;
        $diskusiBansosHistory->id_instansi = $idInstansi;
        $diskusiBansosHistory->id_user = $this->shared->user->um_id;
        $resultHistory = $diskusiBansosHistory->create();

        if ($resultDiskusi && $resultHistory) {
            $service->notifyKorwilPemdaUser($diskusiBansos, DiskusiService::BANSOS_NOTIFICATION_CONFIRMATION);

            //otomatis merubah menjadi done apabila ke dumasKPK
            if ($validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_DUMASKPK) {
                $diskusiBansos = DiskusiBansos::findFirst($validatedRequest['diskusi_bansos_id']);

                $diskusiBansos->status = DiskusiBansos::DONE;
                $resultDiskusi = $diskusiBansos->save();

                $diskusiBansosHistory = new DiskusiBansosHistory();
                $diskusiBansosHistory->id_diskusi_bansos = $diskusiBansos->id;
                $diskusiBansosHistory->keterangan = 'Diselesaikan oleh DuMas KPK';
                $diskusiBansosHistory->status = $diskusiBansos->status;
                $diskusiBansosHistory->id_instansi = $diskusiBansos->id_instansi;
                $diskusiBansosHistory->id_user = $this->shared->user->um_id;
                $resultHistory = $diskusiBansosHistory->create();

                if ($resultDiskusi && $resultHistory) {
                    $service->notifyKorwilPemdaUser($diskusiBansos, DiskusiService::BANSOS_NOTIFICATION_CONFIRMATION);
                } else {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Diskusi Bansos Error");
                }
            }

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_history" => $diskusiBansosHistory->id,
                        "keterangan" => $diskusiBansosHistory->keterangan,
                        "status" => $diskusiBansosHistory->status,
                    ],
                ]
            );
        } elseif (!$resultDiskusi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Diskusi Bansos Error");
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save History Diskusi Bansos Error");
        }
    }

    public function canConfirmAssigned()
    {
        $this->checkScopes(['bansos.confirm_assigned']);
        $params = $this->request->get();

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

        $diskusiBansos = DiskusiBansos::findFirst(
            [
                "id = :diskusi_bansos_id:",
                "bind" => [
                    "diskusi_bansos_id" => $validatedRequest['diskusi_bansos_id'],
                ],
            ]
        );

        if (empty($diskusiBansos)) {
            throw new ValidationException([
                [
                    'field' => 'diskusi_bansos_id',
                    'message' => 'Diskusi Bansos dengan ID(' . $validatedRequest['diskusi_bansos_id'] . ') tidak ditemukan.',
                ],
            ]);
        }

        //NOTE: jangan dihapus dulu
        /*$allowedInstansi = KorwilTeamInstansi::findFirst(
            [
                "team_id in (SELECT x.team_id FROM KorwilTeamUser x WHERE x.user_id = :user_id:)
                    and (instansi_id = :instansi_id: OR instansi_id = :instansi_id_origin: )",
                "bind" => [
                    "user_id" => $this->shared->user->um_id,
                    "instansi_id" => $diskusiBansos->id_instansi,
                    "instansi_id_origin" => $diskusiBansos->id_instansi_origin
                ],
            ]
        );*/

        $allowedInstansi = true;

        return new SimpleResponse(!empty($allowedInstansi));
    }

    public function confirmLaporanBansosAssigned()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkScopes(['bansos.confirm_assigned']);

        $params = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $params,
            [
                'diskusi_bansos_id' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => ['required']],
                'status' => ['validators' => ['required', 'digit']],
            ]
        );

        $service = new DiskusiService($this->config, $this->shared, $this->request, $this->minio);

        $diskusiBansos = DiskusiBansos::findFirst(
            [
                "id = :diskusi_bansos_id:",
                "bind" => [
                    "diskusi_bansos_id" => $validatedRequest['diskusi_bansos_id'],
                ],
            ]
        );

        if (empty($diskusiBansos)) {
            throw new ValidationException([
                [
                    'field' => 'diskusi_bansos_id',
                    'message' => 'Diskusi Bansos dengan ID(' . $validatedRequest['diskusi_bansos_id'] . ') tidak ditemukan.',
                ],
            ]);
        }

        //check Assigned
        $userID = $this->shared->user->um_id;
        //$userID = 50;
        $strWhere = "";
        $bind = [];

        $allowedInstansi = KorwilTeamInstansi::findFirst(
            [
                "team_id in (SELECT x.team_id FROM KorwilTeamUser x WHERE x.user_id = :user_id:)
                    and instansi_id = :instansi_id:",
                "bind" => [
                    "user_id" => $userID,
                    "instansi_id" => $diskusiBansos->id_instansi,
                ],
            ]
        );

        if (empty($allowedInstansi)) {
            throw new ValidationException([
                [
                    'field' => 'diskusi_bansos_id',
                    'message' => 'User (' . $this->shared->user->um_user_name . ') tidak memiliki akses untuk confirm Diskusi Bansos dengan ID (' . $validatedRequest['diskusi_bansos_id'] . ')',
                ],
            ]);
        }

        //update diskusi bansos
        $idInstansi = $diskusiBansos->id_instansi;
        if (
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_PEMDA ||
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_KEMSOS ||
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_DUMASKPK ||
            $validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_DUMASKPK
        ) {
            $diskusiBansos->status = DiskusiBansos::CONFIRMED;
            $namaInstansi = '';

            if ($validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_KEMSOS) {
                $namaInstansi = DiskusiBansos::KEMSOS_NAME;
            } elseif ($validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_DUMASKPK) {
                $namaInstansi = DiskusiBansos::KPK_NAME;
            } elseif ($params['status'] == DIskusiBansos::CONFIRMED_TO_KEMENKOPUKM) {
                $namaInstansi = DiskusiBansos::KEMENKOPUKM_NAME;
            }

            if ($namaInstansi != '') {
                $instansi = Instansi::findFirst(
                    [
                        "lower(nama) = :nama_instansi:",
                        "bind" => [
                            "nama_instansi" => $namaInstansi
                        ]
                    ]
                );

                if (empty($instansi)) {
                    throw new ValidationException([
                        [
                            'field' => 'status',
                            'message' => 'Instansi tujuan tindak lanjut tidak dapat ditemukan',
                        ],
                    ]);
                }

                $idInstansi = $instansi->id;
            }

            $diskusiBansos->id_instansi = $idInstansi;
        } else {
            $diskusiBansos->status = $validatedRequest['status'];
        }
        $resultDiskusi = $diskusiBansos->save();

        $diskusiBansosHistory = new DiskusiBansosHistory();
        $diskusiBansosHistory->id_diskusi_bansos = $diskusiBansos->id;
        $diskusiBansosHistory->keterangan = $validatedRequest['keterangan'];
        $diskusiBansosHistory->status = $diskusiBansos->status;
        $diskusiBansosHistory->id_instansi = $idInstansi;
        $diskusiBansosHistory->id_user = $this->shared->user->um_id;
        $resultHistory = $diskusiBansosHistory->create();

        if ($resultDiskusi && $resultHistory) {
            $service->notifyKorwilPemdaUser($diskusiBansos, DiskusiService::BANSOS_NOTIFICATION_CONFIRMATION);

            //otomatis merubah menjadi done apabila ke dumasKPK
            if ($validatedRequest['status'] == DiskusiBansos::CONFIRMED_TO_DUMASKPK) {
                $diskusiBansos = DiskusiBansos::findFirst($validatedRequest['diskusi_bansos_id']);

                $diskusiBansos->status = DiskusiBansos::DONE;
                $resultDiskusi = $diskusiBansos->save();

                $diskusiBansosHistory = new DiskusiBansosHistory();
                $diskusiBansosHistory->id_diskusi_bansos = $diskusiBansos->id;
                $diskusiBansosHistory->keterangan = 'Diselesaikan oleh DuMas KPK';
                $diskusiBansosHistory->status = $diskusiBansos->status;
                $diskusiBansosHistory->id_instansi = $diskusiBansos->id_instansi;
                $diskusiBansosHistory->id_user = $this->shared->user->um_id;
                $resultHistory = $diskusiBansosHistory->create();

                if ($resultDiskusi && $resultHistory) {
                    $service->notifyKorwilPemdaUser($diskusiBansos, DiskusiService::BANSOS_NOTIFICATION_CONFIRMATION);
                } else {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Diskusi Bansos Error");
                }
            }

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_history" => $diskusiBansosHistory->id,
                        "keterangan" => $diskusiBansosHistory->keterangan,
                        "status" => $diskusiBansosHistory->status,
                    ],
                ]
            );
        } elseif (!$resultDiskusi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Diskusi Bansos Error");
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save History Diskusi Bansos Error");
        }
    }

    public function followUpLaporanBansos()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkOneOfScopes(['bansos.follow_up', self::SCOPE_FOLLOWUP_KELUHAN_IKN, self::SCOPE_FOLLOWUP_KELUHAN_DESA, self::SCOPE_FOLLOWUP_KELUHAN_PENDIDIKAN]);

        $params = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $params,
            [
                'diskusi_bansos_id' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => ['required']],
                'status' => ['validators' => ['required', 'digit']],
            ]
        );

        if ($validatedRequest['status'] != DiskusiBansos::ON_PROGRESS && $validatedRequest['status'] != DiskusiBansos::DONE) {
            throw new ValidationException([
                [
                    'field' => 'status',
                    'message' => 'Status tidak valid.',
                ],
            ]);
        }

        $service = new DiskusiService($this->config, $this->shared, $this->request, $this->minio);

        $diskusiBansos = DiskusiBansos::findFirst(
            [
                "id = :diskusi_bansos_id: and deleted_at is null",
                "bind" => [
                    "diskusi_bansos_id" => $validatedRequest['diskusi_bansos_id'],
                ],
            ]
        );

        if (empty($diskusiBansos)) {
            throw new ValidationException([
                [
                    'field' => 'diskusi_bansos_id',
                    'message' => 'Diskusi Bansos dengan ID(' . $validatedRequest['diskusi_bansos_id'] . ') tidak ditemukan.',
                ],
            ]);
        }

        $userInstansi = $this->shared->user->id_instansi;

        if ($diskusiBansos->id_instansi != $userInstansi) {
            throw new ValidationException([
                [
                    'field' => 'id_instansi',
                    'message' => 'User tidak memiliki akses untuk melakukan follow up pada instansi terkait.',
                ],
            ]);
        }


        $diskusiBansos->status = $validatedRequest['status'];
        $resultDiskusi = $diskusiBansos->save();

        $diskusiBansosHistory = new DiskusiBansosHistory();
        $diskusiBansosHistory->id_diskusi_bansos = $diskusiBansos->id;
        $diskusiBansosHistory->keterangan = $validatedRequest['keterangan'];
        $diskusiBansosHistory->status = $diskusiBansos->status;
        $diskusiBansosHistory->id_instansi = $diskusiBansos->id_instansi;
        $diskusiBansosHistory->id_user = $this->shared->user->um_id;
        $resultHistory = $diskusiBansosHistory->create();

        // $test = $this->getKorwilUserIds($diskusiBansos);
        // var_dump($test);

        if ($resultDiskusi && $resultHistory) {
            $service->notifyKorwilPemdaUser($diskusiBansos, DiskusiService::BANSOS_NOTIFICATION_FOLLOWUP);

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_history" => $diskusiBansosHistory->id,
                        "keterangan" => $diskusiBansosHistory->keterangan,
                        "status" => $diskusiBansosHistory->status,
                    ],
                ]
            );
        } elseif (!$resultDiskusi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Diskusi Bansos Error");
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save History Diskusi Bansos Error");
        }
    }

    public function historyBansos()
    {
        date_default_timezone_set('Asia/Jakarta');
        // $this->checkOneOfScopes(['bansos.view_all','bansos.view_assigned']);

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

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

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

        $diskusiBansos = DiskusiBansos::findFirst(
            [
                "deleted_at is null and id = :id_diskusi_bansos:",
                "bind" => [
                    "id_diskusi_bansos" => $validatedRequest['id_diskusi_bansos'],
                ],
            ]
        );

        if (empty($diskusiBansos)) {
            if (empty($diskusiBansos)) {
                throw new ValidationException([
                    [
                        'field' => 'id_diskusi_bansos',
                        'message' => 'Diskusi Bansos dengan ID(' . $validatedRequest['id_diskusi_bansos'] . ') tidak ditemukan.',
                    ],
                ]);
            }
        }

        $strWhere = "deleted_at is null and id_diskusi_bansos = :id_diskusi_bansos:";
        $bind["id_diskusi_bansos"] = $validatedRequest['id_diskusi_bansos'];

        $totalResult = DiskusiBansosHistory::count(
            [
                $strWhere,
                "bind" => $bind,
            ]
        );

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

        $result = [];

        foreach ($listDiskusiBansosHistory as $history) {
            $result[] = $history->toArrayComplete();
        }

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

    public function exportBansosAll()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkOneOfScopes(['bansos.view_all', self::SCOPE_MONITORING_KELUHAN_KAMPUS, self::SCOPE_MONITORING_KELUHAN_PELABUHAN]);

        $validatedRequest = $this->validateRequestForList();

        $isMonitoring = false;
        $topikIds = [];
        // validasi scope monitoring dengan topik keluhan yg dipilih
        if ($this->hasScopes([self::SCOPE_MONITORING_KELUHAN_KAMPUS]) && !$this->hasScopes(['bansos.view_all'])) {
            if ($validatedRequest['topik_type'] !== TopikDiskusiBansos::TYPE_KAMPUS) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak berhak mengakses.");
            } else {
                $isMonitoring = true;
            }
        }
        if ($this->hasScopes([self::SCOPE_MONITORING_KELUHAN_PELABUHAN]) && !$this->hasScopes(['bansos.view_all'])) {
            if ($validatedRequest['topik_type'] !== TopikDiskusiBansos::TYPE_PELABUHAN) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak berhak mengakses.");
            } else {
                $isMonitoring = true;
            }

            if (isset($this->shared->user)) {
                $topikIds = ScopesInstansiTopik::topikIds(
                    self::SCOPE_MONITORING_KELUHAN_PELABUHAN,
                    $this->shared->user->id_instansi
                );
            }
        }

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

        if (!empty($topikIds)) {
            $strWhere .= " and id_topik in (".join(",", $topikIds).")";
        }

        if ($isMonitoring) {
            $strWhere .= " and status <> " . $this->quote(DiskusiBansos::NEW);
        }

        $conditions = $this->conditionBuilderForList($validatedRequest, $strWhere, $bind);
        $strWhere = $conditions->where;
        $bind = $conditions->bind;

        $query = ViewKeluhan::query()
            ->where($strWhere)
            ->bind($bind);
        $query = $this->sortBuilderForList($validatedRequest, $query);

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

        if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_PEMILU) {
            foreach ($result as $key => $value){
                $result[$key]['instansi_asal'] = str_replace('Pemerintah ', '', $value['instansi_asal']);
                $result[$key]['nama_objek'] = str_replace('Pemerintah ', '', $value['nama_objek']);
            }
        }

        #region Generate Excel File

        $spreadsheet = $this->generateExcelLaporanBansosNew($result, $validatedRequest['topik_type'], $validatedRequest['topik_subtype']);
        $todayLabel = DateHelper::formatToday();

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

        #endregion
    }

    public function exportBansosAssigned()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkOneOfScopes(['bansos.view_assigned', 'covid.dinkes_pemda', self::SCOPE_MONITORING_KELUHAN_PEMILU]);

        $validatedRequest = $this->validateRequestForList();

        $userID = $this->shared->user->um_id;
        $strWhere = "1 = 1";
        $bind = [];

        $listInstansi = KorwilTeamInstansi::find(
            [
                "columns" => "instansi_id",
                "team_id in (SELECT x.team_id FROM KorwilTeamUser x WHERE x.user_id = :user_id:)",
                "bind" => [
                    "user_id" => $userID,
                ],
            ]
        );

        $arr = [];
        foreach ($listInstansi as $data) {
            $arr[] = $data->instansi_id;
        }

        $strListInstansi = implode(",", $arr);
        if (!empty($strListInstansi)) {
            $strWhere .= " and (id_instansi_respon in (" . $strListInstansi . "))";
        }

        $conditions = $this->conditionBuilderForList($validatedRequest, $strWhere, $bind);
        $strWhere = $conditions->where;
        $bind = $conditions->bind;

        $query = ViewKeluhan::query()
            ->where($strWhere)
            ->bind($bind);
        $query = $this->sortBuilderForList($validatedRequest, $query);

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

        if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_PEMILU) {
            foreach ($result as $key => $value){
                $result[$key]['instansi_asal'] = str_replace('Pemerintah ', '', $value['instansi_asal']);
                $result[$key]['nama_objek'] = str_replace('Pemerintah ', '', $value['nama_objek']);
            }
        }

        #region If Bansos, get the instansi to check the Name
        $topik_subtype = null;
        if(!is_null($validatedRequest['topik_type'])) {
            if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_PEMILU && !is_null($validatedRequest['id_instansi'])) {
                $instansi = Instansi::findFirst($validatedRequest['id_instansi']);
                if($instansi->nama == DiskusiBansos::BAWASLU_NAME) {
                    $topik_subtype = TopikDiskusiBansos::SUB_TYPE_POLITIK_UANG;
                }
                else if ($instansi->nama == DiskusiBansos::DKPP_NAME) {
                    $topik_subtype = TopikDiskusiBansos::SUB_TYPE_MENERIMA_SUAP;
                }
            }
        }
        #endregion

        #region Generate Excel File
        $spreadsheet = $this->generateExcelLaporanBansosNew($result, $validatedRequest['topik_type'], $topik_subtype);
        $todayLabel = DateHelper::formatToday();

        $writer = new Xlsx($spreadsheet);
        $filename = "Export Assigned Laporan Keluhan - $todayLabel.xlsx";

        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');

        #endregion
    }

    public function exportBansosFollowUp()
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkOneOfScopes([self::SCOPE_FOLLOWUP_KELUHAN_BPJS]);

        $params = $this->request->get();
        $params['topik_type'] = array_key_exists('topik_type', $params) ? $params['topik_type'] : null;
        $params['topik_subtype'] = array_key_exists('topik_subtype', $params) ? $params['topik_subtype'] : null;
        $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : null;
        $params['order_direction'] = array_key_exists('order_direction', $params) ? $params['order_direction'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'topik_type' => ['validators' => ['required']],
                'topik_subtype' => ['validators' => []],
                'order_by' => ['validators' => []],
                'order_direction' => ['validators' => []],
            ]
        );

        if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_BPJS_KESEHATAN && !$this->hasScopes([self::SCOPE_FOLLOWUP_KELUHAN_BPJS])) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }
        else if($validatedRequest['topik_type'] != TopikDiskusiBansos::TYPE_BPJS_KESEHATAN) {
            //NOTE: Ini karena belum handle untuk topik keluhan lain, nanti jika mau ada keluhan lain, tambahkan handle-nya disini
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }

        $conditions = [
            'type = :type:',
            'status <> :status:'
        ];
        $bind = [
            'type' => $validatedRequest['topik_type'],
            'status' => DiskusiBansos::NEW
        ];


        $query = ViewKeluhan::query()
            ->where(implode(' AND ', $conditions))
            ->bind($bind);
        $query = $this->sortBuilderForList($validatedRequest, $query);

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

        #region Hide informasi anonim
        foreach ($result as $idx => $item) {
            if($item['is_anonymous'] == 1) {
                $result[$idx]['pelapor'] = 'Anonim';
                $result[$idx]['nik'] = 'Anonim';
                $result[$idx]['phone'] = 'Anonim';
            }
        }
        #endregion

        #region Generate Excel File

        $spreadsheet = $this->generateExcelLaporanBansosNew($result, $validatedRequest['topik_type'], $validatedRequest['topik_subtype']);
        $todayLabel = DateHelper::formatToday();

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

        #endregion
    }

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

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

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

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

        if (!is_null($validatedRequest["keyword"])) {
            $strWhere .= " and lower(deskripsi) like :keyword:";
            $bind["keyword"] = '%' . mb_strtolower($validatedRequest["keyword"]) . '%';
        }

        if($params['topik_type']) {
            $strWhere .=  " AND type = :topik_type:";
            $bind['topik_type'] = $params['topik_type'];
        }

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

        $listProvinsi = TopikDiskusiBansos::find(
            [
                "columns" => "id as id_topik, deskripsi, deskripsi_en, type, subtype",
                $strWhere,
                "bind" => $bind,
                "order" => "view_order ASC, id"
            ]
        );

        return new PaginatedResponse($listProvinsi, $totalCount, $totalCount, 0);
    }

    public function addTopikBansos()
    {
        date_default_timezone_set('Asia/Jakarta');

        $params = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $params,
            [
                'deskripsi' => ['validators' => ['required']],
            ]
        );

        if (empty($validatedRequest['deskripsi'])) {
            throw new ValidationException([
                [
                    'field' => 'deskripsi',
                    'message' => 'Deskripsi harus diisi',
                ],
            ]);
        }

        $topikBansos = new TopikDiskusiBansos();
        $topikBansos->deskripsi = $validatedRequest['deskripsi'];
        $res = $topikBansos->create();

        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "id_topik" => $topikBansos->id,
                        "deskripsi" => $topikBansos->deskripsi,
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Save Topik Diskusi Bansos Error");
        }
    }

    public function kontenKeluhan($uuid)
    {
        $service = new DiskusiService($this->config, $this->shared, null, $this->minio);
        $result = $service->getDiskusiDetailForKonten($uuid);

        if (count($result) == 1) {
            $diskusiBansos = DiskusiBansos::findFirst(
                [
                    "id_diskusi = :id_diskusi: and deleted_at is null",
                    "bind" => [
                        "id_diskusi" => $result[0]["id"],
                    ],
                ]
            );

            if (!empty($diskusiBansos)) {
                $result[0]["id_topik"] = $diskusiBansos->id_topik;
                $result[0]["label_topik"] = !empty($diskusiBansos->topikdiskusibansos) ? $diskusiBansos->topikdiskusibansos->deskripsi : "";
                $result[0]["type_topik"] = !empty($diskusiBansos->topikdiskusibansos) ? $diskusiBansos->topikdiskusibansos->type : "";
                $result[0]["phone"] = !is_null($diskusiBansos->phone) ? explode(";", $diskusiBansos->phone) : [];

                $diskusiBansosNIK = DiskusiBansosNIK::find(
                    [
                        "id_diskusi_bansos = :id_diskusi_bansos: and deleted_at is null",
                        "bind" => [
                            "id_diskusi_bansos" => $diskusiBansos->id,
                        ],
                    ]
                );

                if (count($diskusiBansosNIK) > 0) {
                    $listNIk = [];
                    foreach ($diskusiBansosNIK as $item) {
                        if (!is_null($item->nik)) {
                            $listNIK[] = $item->nik;
                        }
                    }

                    $result[0]["nik"] = $listNIK;
                }
            }
        }

        return new PaginatedResponse($result, 1, 1, 0);
    }

    public function editKeluhan($uuid)
    {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkScopes(['discussion.create']);

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

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

        $validatedRequest = $this->validate(
            $params,
            [
                'judul' => ['validators' => ['required']],
                'is_anonymous' => ['validators' => ['required', 'digit']],
                'konten_diskusi' => ['validators' => ['required']],
                'phone' => ['validators' => ['required']],
                'nik' => ['validators' => ['required']],
                'id_topik' => ['validators' => ['required']],
                'link_terkait' => ['validators' => []],
            ]
        );

        $this->AdditionalValidationBansos($validatedRequest);

        $diskusi = Diskusi::findFirst(
            [
                "uuid = :uuid: and user_id = :user_id:",
                "bind" => [
                    "uuid" => $uuid,
                    "user_id" => $this->shared->user->um_id
                ]
            ]
        );

        if (empty($diskusi)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Diskusi tidak ditemukan');
        }

        $diskusiBansos = DiskusiBansos::findFirst(
            [
                "id_diskusi = :id_diskusi: and deleted_at is null",
                "bind" => [
                    "id_diskusi" => $diskusi->id,
                ],
            ]
        );

        if (!empty($diskusiBansos)) {
            if ($diskusiBansos->status != DiskusiBansos::NEW) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tidak bisa mengubah keluhan yang sudah diproses');
            }
        }

        $service = new DiskusiService($this->config, $this->shared, $this->request, $this->minio);
        $result = $service->editDiskusi($uuid, $validatedRequest);

        $diskusiBansos = DiskusiBansos::findFirst(
            [
                "id_diskusi = :id_diskusi: and deleted_at is null",
                "bind" => [
                    "id_diskusi" => $result['diskusi']->id,
                ],
            ]
        );

        if (!empty($diskusiBansos)) {
            $now = date('Y-m-d H:i:s');
            $diskusiBansos->phone = $validatedRequest['phone'];
            $diskusiBansos->id_topik = $validatedRequest['id_topik'];
            $diskusiBansos->updated_at = $now;

            $diskusiBansos->save();

            $diskusiBansosNIK = DiskusiBansosNIK::find(
                [
                    "id_diskusi_bansos = :id_diskusi_bansos: and deleted_at is null",
                    "bind" => [
                        "id_diskusi_bansos" => $diskusiBansos->id,
                    ],
                ]
            );

            foreach ($diskusiBansosNIK as $item) {
                $item->deleted_at = $now;

                $item->save();
            }

            $saveDiskusiBansosNIK = true;
            $nikpkhs = explode(";", $validatedRequest["nik"]);
            foreach ($nikpkhs as $nikpkh) {
                if ($saveDiskusiBansosNIK) {
                    $objNIKPKH = explode(",", $nikpkh);
                    $nik = $objNIKPKH[0];
                    $pkh = $objNIKPKH[1];

                    $newDiskusiBansosNIK = new DiskusiBansosNIK();
                    $newDiskusiBansosNIK->id_diskusi_bansos = $diskusiBansos->id;
                    $newDiskusiBansosNIK->nik = $nik;
                    $newDiskusiBansosNIK->pkh = $pkh;

                    $saveDiskusiBansosNIK = $newDiskusiBansosNIK->create();
                }
            }
        }

        return new ObjectResponse(
            [
                "success" => true,
                "data" => [
                    "diskusi_uuid" => $result['diskusi']->uuid,
                    "diskusi_id" => $result['diskusi']->id,
                ],
            ]
        );
    }

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

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

        $params['id_provinsi'] = array_key_exists('id_provinsi', $params) ? $params['id_provinsi'] : null;
        $params['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;

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

        $strWhere = "deleted_at is null and kode_prov = :id_provinsi:";
        $bind["id_provinsi"] = $validatedRequest["id_provinsi"];

        if (!is_null($validatedRequest["id_kota_kabupaten"])) {
            $strWhere .= " and kode_kab = :id_kota_kabupaten:";
            $bind["id_kota_kabupaten"] = $validatedRequest["id_kota_kabupaten"];
        } else {
            $strWhere .= " and kode_kab = :id_kota_kabupaten:";
            $bind["id_kota_kabupaten"] = "00";
        }

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

        $listDetailApbd = ApbdBansos::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "id",
            ]
        );

        return new PaginatedResponse($listDetailApbd, $totalCount, 0, 0);
    }
    #endregion

    #region Private Function

    private function validateRequestForList()
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : "";
        $params['id_instansi'] = array_key_exists('id_instansi', $params) ? $params['id_instansi'] : null;
        $params['id_instansi_origin'] = array_key_exists('id_instansi_origin', $params) ? $params['id_instansi_origin'] : null;
        $params['status'] = array_key_exists('status', $params) ? $params['status'] : null;
        $params['id_team'] = array_key_exists('id_team', $params) ? $params['id_team'] : null;
        $params['id_provinsi'] = array_key_exists('id_provinsi', $params) ? $params['id_provinsi'] : null;
        $params['id_provinsi_origin'] = array_key_exists('id_provinsi_origin', $params) ? $params['id_provinsi_origin'] : null;
        $params['is_active'] = array_key_exists('is_active', $params) ? $params['is_active'] : 1;
        $params['is_verified'] = array_key_exists('is_verified', $params) ? $params['is_verified'] : 1;
        $params['is_pemda'] = array_key_exists('is_pemda', $params) ? $params['is_pemda'] : 1;
        $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : null;
        $params['order_direction'] = array_key_exists('order_direction', $params) ? $params['order_direction'] : null;
        $params['topik_type'] = array_key_exists('topik_type', $params) ? $params['topik_type'] : null;
        $params['topik_subtype'] = array_key_exists('topik_subtype', $params) ? $params['topik_subtype'] : null;
        $params['isresponded'] = array_key_exists('isresponded', $params) ? $params['isresponded'] : null;
        $params['id_topik'] = array_key_exists('id_topik', $params) ? $params['id_topik'] : null;
        $params['id_objek_pemilu'] = array_key_exists('id_objek_pemilu', $params) ? $params['id_objek_pemilu'] : null;
        $params['pemilu_isresponded'] = array_key_exists('pemilu_isresponded', $params) ? $params['pemilu_isresponded'] : null;

        return $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'id_instansi' => ['validators' => ['digit']],
                'id_instansi_origin' => ['validators' => ['digit']],
                'status' => ['validators' => ['digit']],
                'id_team' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_provinsi_origin' => ['validators' => ['digit']],
                'is_active' => ['validators' => ['digit']],
                'is_verified' => ['validators' => ['digit']],
                'is_pemda' => ['validators' => ['digit']],
                'order_by' => ['validators' => []],
                'order_direction' => ['validators' => []],
                'topik_type' => ['validators' => []],
                'topik_subtype' => ['validators' => []],
                'isresponded' => ['validators' => []],
                'id_topik' => ['validators' => []],
                'id_objek_pemilu' => ['validators' => []],
                'pemilu_isresponded' => ['validators' => []],
            ]
        );
    }

    private function conditionBuilderForList($validatedRequest, $strWhere, $bind)
    {
        if (!is_null($validatedRequest['id_instansi'])) {
            $strWhere .= " and id_instansi_respon = :id_instansi:";
            $bind["id_instansi"] = $validatedRequest["id_instansi"];
        }

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

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

        if (!is_null($validatedRequest['id_team']) && $this->hasScopes(['bansos.view_all'])) {
            $listInstansi = KorwilTeamInstansi::find(
                [
                    "columns" => "instansi_id",
                    "team_id = :team_id:",
                    "bind" => [
                        "team_id" => $validatedRequest['id_team'],
                    ],
                ]
            );

            $arr = [];
            foreach ($listInstansi as $data) {
                $arr[] = $data->instansi_id;
            }

            $strListInstansi = implode(",", $arr);
            if (!empty($strListInstansi)) {
                $strWhere .= " and (id_instansi_respon in (" . $strListInstansi . ") or id_instansi_asal in (" . $strListInstansi . "))";
            }
        }

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

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

        if (!is_null($validatedRequest['is_active'])) {
            $strWhere .= " and is_deleted = :is_deleted:";
            $bind["is_deleted"] = $validatedRequest['is_active'] == 0 ? 1 : 0;
        }

        if (!is_null($validatedRequest['isresponded']) && !empty($validatedRequest['isresponded'])) {
            $strWhere .= " and isresponded = " . $validatedRequest['isresponded'];
        }

        if(!is_null($validatedRequest['id_topik']) && !empty($validatedRequest['id_topik'])) {
            $strWhere .= " AND id_topik = :id_topik:";
            $bind['id_topik'] = $validatedRequest['id_topik'];
        }

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

        if($validatedRequest['topik_type']) {
            $strWhere .= ' AND type = :topik_type:';
            $bind['topik_type'] = $validatedRequest['topik_type'];

            if($validatedRequest['topik_type'] == TopikDiskusiBansos::TYPE_PEMILU && !is_null($validatedRequest['id_instansi'])) {
                $strWhere .= " and is_deleted = :is_deleted:";
                $bind["is_deleted"] = 0;
            }
        }

        if($validatedRequest['topik_subtype']) {
            $strWhere .= ' AND subtype = :topik_subtype:';
            $bind['topik_subtype'] = $validatedRequest['topik_subtype'];
        }

        if(!is_null($validatedRequest['id_objek_pemilu']) && !empty($validatedRequest['id_objek_pemilu'])) {
            $strWhere .= " AND id_objek_pemilu = :id_objek_pemilu:";
            $bind['id_objek_pemilu'] = $validatedRequest['id_objek_pemilu'];
        }

        if(!is_null($validatedRequest['pemilu_isresponded'])) {
            $strWhere .= " AND pemilu_isresponded = :pemilu_isresponded:";
            $bind['pemilu_isresponded'] = $validatedRequest['pemilu_isresponded'];
        }

        return (object) [
            "where" => $strWhere,
            "bind" => $bind
        ];
    }

    private function sortBuilderForList($validatedRequest, $query)
    {
        $orderClause = null;
        if (!is_null($validatedRequest['order_by'])) {
            switch ($validatedRequest['order_by']) {
                case 'topik':
                    $orderClause = 'topik';
                    break;
                case 'status':
                    $orderClause = 'status';
                    break;
                case 'title':
                    $orderClause = 'title';
                    break;
                case 'pelapor':
                    $orderClause = 'pelapor';
                    break;
                case 'instansi':
                    $orderClause = 'nama_objek';
                    break;
                case 'isresponded':
                    $orderClause = 'isresponded '. ($validatedRequest['order_direction'] ?? 'DESC') . ', created_at '. ($validatedRequest['order_direction'] ?? 'DESC');
                    break;
                case 'pemilu_isresponded':
                    $orderClause = 'pemilu_isresponded '. ($validatedRequest['order_direction'] ?? 'DESC') . ', created_at '. ($validatedRequest['order_direction'] ?? 'DESC');
                    break;
                case 'created_at':
                    $orderClause = 'created_at';
                    break;
                default:
                    $orderClause = $validatedRequest['order_by'];
                    break;
            }
        }

        if (!is_null($orderClause)) {
            $query = $query
                ->orderBy(($validatedRequest['order_by'] != 'isresponded' && $validatedRequest['order_by'] != 'pemilu_isresponded') ? ($orderClause . ' ' . $validatedRequest['order_direction']) : $orderClause);
        }
        else {
            $query = $query
                ->orderBy('isresponded DESC');
        }

        return $query;
    }

    private function generateExcelLaporanBansos($items)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $todayLabel = DateHelper::formatToday();

        $sheet->setCellValue("A1", 'Daftar Laporan Bansos');
        $sheet->setCellValue("A2", $todayLabel);

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

        $sheet->mergeCells('A1:K1');
        $sheet->mergeCells('A2:K2');

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Tanggal');
        $sheet->setCellValue("C4", 'Instansi Asal');
        $sheet->setCellValue("D4", 'Instansi Tindak Lanjut');
        $sheet->setCellValue("E4", 'Topik');
        $sheet->setCellValue("F4", 'Status');
        $sheet->setCellValue("G4", 'Judul');
        $sheet->setCellValue("H4", 'Pelapor');
        $sheet->setCellValue("I4", 'Konten');
        $sheet->setCellValue("J4", 'Daftar NIK');
        $sheet->setCellValue("K4", 'Nomor Telepon');

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

        $row = 5;
        $index = 1;

        foreach ($items as $item) {
            $strNik = "";
            $strPhone = "";
            $instansi = "";
            $instansiOrigin = "";
            $topik = "";
            $judul = "";
            $pelapor = "";
            $content = "";
            $tanggal = "";

            foreach ($item['diskusi_bansos_nik'] as $nik) {
                $strNik .= $nik->nik . ";\r";
            }

            if (array_key_exists('phone', $item)) {
                $strPhone = str_replace(";", ";\r", $item['phone']) . ";";
            }

            if (array_key_exists('instansi', $item) && !empty($item['instansi'])) {
                $instansi = $item['instansi']->nama;
            }

            if (array_key_exists('instansi_origin', $item) && !empty($item['instansi_origin'])) {
                $instansiOrigin = $item['instansi_origin']->nama;
            }

            if (array_key_exists('topik', $item) && !empty($item['topik'])) {
                $topik = $item['topik']->deskripsi;
            }

            if (array_key_exists('diskusi', $item) && !empty($item['diskusi'])) {
                $judul = $item['diskusi']->title;
                $content = \Soundasleep\Html2Text::convert($item['diskusi']->content);
                $tanggal = $item['diskusi']->created_at;
            }

            if (array_key_exists('user', $item) && !empty($item['user'])) {
                $pelapor = $item['user']['um_user_name'];
            }

            $sheet->setCellValue("A$row", $index);
            $sheet->setCellValue("B$row", $item['created_at']);
            $sheet->setCellValue("C$row", $instansiOrigin);
            $sheet->setCellValue("D$row", $instansi);
            $sheet->setCellValue("E$row", $topik);
            $sheet->setCellValue("F$row", DiskusiBansos::getStatusLabel($item['status']));
            $sheet->setCellValue("G$row", $judul);
            $sheet->setCellValue("H$row", $pelapor);
            $sheet->setCellValue("I$row", $content);
            $sheet->setCellValue("J$row", $strNik);
            $sheet->setCellValue("K$row", $strPhone);

            $index++;
            $row++;
        }

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

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

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

        return $spreadsheet;
    }

    private function generateExcelLaporanBansosNew($items, $topik_type = null, $topik_subtype = null)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $todayLabel = DateHelper::formatToday();
        $dateNow = Carbon::now();

        $sheet->setCellValue("A1", 'Daftar Keluhan' . (!is_null($topik_type) ? " $topik_type" : ""));
        $sheet->setCellValue("A2", $todayLabel);

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

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Status');
        if($topik_type == TopikDiskusiBansos::TYPE_PEMILU) {
            if(is_null($topik_subtype)) {
                $sheet->setCellValue("C4", 'Tanggal Laporan');
                $sheet->setCellValue("D4", 'Tanggal Diteruskan');
                $sheet->setCellValue("E4", 'Tanggal Tindak Lanjut');
                $sheet->setCellValue("F4", 'Topik');
                $sheet->setCellValue("G4", 'Objek');
                $sheet->setCellValue("H4", 'Lokasi');
                $sheet->setCellValue("I4", 'Judul');
                $sheet->setCellValue("J4", 'Nama Paslon/Sumber Informasi');
                $sheet->setCellValue("K4", 'Jenis');
                $sheet->setCellValue("L4", 'Keterangan');
                $sheet->setCellValue("M4", 'Valuasi');
                $sheet->setCellValue("N4", 'User');
                $sheet->setCellValue("O4", 'Konten');
                $sheet->setCellValue("P4", 'No. HP');
                $sheet->setCellValue("Q4", 'Sedang Aktif');
            }
            else if($topik_subtype == TopikDiskusiBansos::SUB_TYPE_POLITIK_UANG) {
                //Bawaslu

                $sheet->setCellValue("C4", 'Tanggal Laporan (Diterima)');
                $sheet->setCellValue("D4", 'Tanggal Tindak Lanjut');
                $sheet->setCellValue("E4", 'Topik');
                $sheet->setCellValue("F4", 'Objek');
                $sheet->setCellValue("G4", 'Lokasi');
                $sheet->setCellValue("H4", 'Judul');
                $sheet->setCellValue("I4", 'Nama Paslon');
                $sheet->setCellValue("J4", 'Jenis');
                $sheet->setCellValue("K4", 'Keterangan');
                $sheet->setCellValue("L4", 'Valuasi');
                $sheet->setCellValue("M4", 'Konten');
            }
            else if($topik_subtype == TopikDiskusiBansos::SUB_TYPE_MENERIMA_SUAP) {
                //DKPP

                $sheet->setCellValue("C4", 'Tanggal Laporan (Diterima)');
                $sheet->setCellValue("D4", 'Tanggal Tindak Lanjut');
                $sheet->setCellValue("E4", 'Topik');
                $sheet->setCellValue("F4", 'Objek');
                $sheet->setCellValue("G4", 'Lokasi');
                $sheet->setCellValue("H4", 'Judul');
                $sheet->setCellValue("I4", 'Sumber Informasi');
                $sheet->setCellValue("J4", 'Konten');
            }
        }
        else {
            $sheet->setCellValue("C4", 'Tanggal Laporan');
            $sheet->setCellValue("D4", 'Tanggal Respon');
            $sheet->setCellValue("E4", 'Tanggal Diteruskan');
            $sheet->setCellValue("F4", 'Tanggal Selesai');
            $sheet->setCellValue("G4", 'Tanggal Dikembalikan');
            $sheet->setCellValue("H4", 'Tanggal Dihapus');
            $sheet->setCellValue("I4", 'Provinsi Asal');
            $sheet->setCellValue("J4", 'Instansi Asal');
            $sheet->setCellValue("K4", 'Provinsi Tindak Lanjut');
            $sheet->setCellValue("L4", 'Instansi Tindak Lanjut');
            $sheet->setCellValue("M4", 'Topik');
            $sheet->setCellValue("N4", 'Judul');
            $sheet->setCellValue("O4", 'Pelapor');
            $sheet->setCellValue("P4", 'Konten');
            $sheet->setCellValue("Q4", 'Daftar NIK');
            $sheet->setCellValue("R4", 'Nomor Telepon');
            $sheet->setCellValue("S4", 'Sedang Aktif');
        }

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

        $row = 5;
        $index = 1;

        foreach ($items as $item) {
            $tanggalLaporan = $this->ConvertDateToLocalDate($item['created_at']);
            $tanggalRespon = $this->ConvertDateToLocalDate($item['response_date']);
            $tanggalDiteruskan = $this->ConvertDateToLocalDate($item['confirmed_date']);
            $tanggalSelesai = $this->ConvertDateToLocalDate($item['done_date']);
            $tanggalKembali = $this->ConvertDateToLocalDate($item['closed_date']);
            $tanggalResponPemilu = $this->ConvertDateToLocalDate($item['pemilu_respond_at']);
            $isActive = $item['is_deleted'] == 0 ? 1 : 0;

            $sheet->setCellValue("A$row", $index);
            $sheet->setCellValue("B$row", DiskusiBansos::getStatusLabel($item['status']));
            if($topik_type == TopikDiskusiBansos::TYPE_PEMILU) {
                if(is_null($topik_subtype)) {
                    $sheet->setCellValue("C$row", $tanggalLaporan['datetime']);
                    $sheet->setCellValue("D$row", $tanggalDiteruskan['datetime']);
                    $sheet->setCellValue("E$row", $tanggalResponPemilu['datetime']);
                    $sheet->setCellValue("F$row", $item['topik']);
                    $sheet->setCellValue("G$row", $item['nama_objek_pemilu']);
                    $sheet->setCellValue("H$row", $item['instansi_asal']);
                    $sheet->setCellValue("I$row", $item['title']);
                    $sheet->setCellValue("J$row", $item['link_terkait']); //nama paslon
                    $sheet->setCellValue("K$row", str_replace(';', ', ', $item['jenis_gratifikasi_name']));
                    $sheet->setCellValue("L$row", $item['keterangan_gratifikasi']);
                    $sheet->setCellValue("M$row", $item['total_nilai_gratifikasi'], DataType::TYPE_NUMERIC);
                    $sheet->setCellValue("N$row", $item['pelapor']);
                    $sheet->setCellValue("O$row", $item['content']);
                    $sheet->setCellValueExplicit("P$row", $item['phone'], DataType::TYPE_STRING);
                    $sheet->setCellValue("Q$row", $isActive);

                    //TThousands Separator. Note: Tidak jadi dipakai, karena ID pemisahnya (.) bukan (,_
//                $cell = $sheet->getCell("N$row");
//                $cell->getStyle()->getNumberFormat()->setFormatCode('#,##0');
                }
                else if($topik_subtype == TopikDiskusiBansos::SUB_TYPE_POLITIK_UANG) {
                    //Bawaslu

                    $sheet->setCellValue("C$row", $tanggalDiteruskan['datetime']);
                    $sheet->setCellValue("D$row", $tanggalResponPemilu['datetime']);
                    $sheet->setCellValue("E$row", $item['topik']);
                    $sheet->setCellValue("F$row", $item['nama_objek_pemilu']);
                    $sheet->setCellValue("G$row", $item['instansi_asal']);
                    $sheet->setCellValue("H$row", $item['title']);
                    $sheet->setCellValue("I$row", $item['link_terkait']); //nama paslon
                    $sheet->setCellValue("J$row", str_replace(';', ', ', $item['jenis_gratifikasi_name']));
                    $sheet->setCellValue("K$row", $item['keterangan_gratifikasi']);
                    $sheet->setCellValue("L$row", $item['total_nilai_gratifikasi'], DataType::TYPE_NUMERIC);
                    $sheet->setCellValue("M$row", $item['content']);
                }
                else if($topik_subtype == TopikDiskusiBansos::SUB_TYPE_MENERIMA_SUAP) {
                    //DKPP

                    $sheet->setCellValue("C$row", $tanggalDiteruskan['datetime']);
                    $sheet->setCellValue("D$row", $tanggalResponPemilu['datetime']);
                    $sheet->setCellValue("E$row", $item['topik']);
                    $sheet->setCellValue("F$row", $item['nama_objek_pemilu']);
                    $sheet->setCellValue("G$row", $item['instansi_asal']);
                    $sheet->setCellValue("H$row", $item['title']);
                    $sheet->setCellValue("I$row", $item['link_terkait']); //sumber informasi
                    $sheet->setCellValue("J$row", $item['content']);
                }
            }
            else {
                $sheet->setCellValue("C$row", $tanggalLaporan['datetime']);
                $sheet->setCellValue("D$row", $tanggalRespon['datetime']);
                $sheet->setCellValue("E$row", $tanggalDiteruskan['datetime']);
                $sheet->setCellValue("F$row", $tanggalSelesai['datetime']);
                $sheet->setCellValue("G$row", $tanggalKembali['datetime']);
                $sheet->setCellValue("H$row", $item['delete_date']);
                $sheet->setCellValue("I$row", $item['provinsi_origin']);
                $sheet->setCellValue("J$row", $item['instansi_asal']);
                $sheet->setCellValue("K$row", $item['provinsi']);
                $sheet->setCellValue("L$row", $item['instansi_respon']);
                $sheet->setCellValue("M$row", $item['topik']);
                $sheet->setCellValue("N$row", $item['title']);
                $sheet->setCellValue("O$row", $item['pelapor']);
                $sheet->setCellValue("P$row", $item['content']);
                $sheet->setCellValueExplicit("Q$row", $item['nik'], DataType::TYPE_STRING);
                $sheet->setCellValueExplicit("R$row", $item['phone'], DataType::TYPE_STRING);
                $sheet->setCellValue("S$row", $isActive);
            }

            $index++;
            $row++;
        }

        $lastColumn = '';
        //Note: Width 40 untuk Topik
        //Width 30 untuk Konten
        if($topik_type == TopikDiskusiBansos::TYPE_PEMILU) {
            if(is_null($topik_subtype)) {
                $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')->setWidth(40);
                $sheet->getColumnDimension('G')->setAutoSize(true);
                $sheet->getColumnDimension('H')->setAutoSize(true);
                $sheet->getColumnDimension('I')->setAutoSize(true);
                $sheet->getColumnDimension('J')->setAutoSize(true);
                $sheet->getColumnDimension('K')->setAutoSize(true);
                $sheet->getColumnDimension('L')->setAutoSize(true);
                $sheet->getColumnDimension('M')->setAutoSize(true);
                $sheet->getColumnDimension('N')->setAutoSize(true);
                $sheet->getColumnDimension('O')->setWidth(30);
                $sheet->getColumnDimension('P')->setAutoSize(true);
                $sheet->getColumnDimension('Q')->setAutoSize(true);
                $lastColumn = 'Q';

                $sheet->getStyle('F4:F' . ($row - 1))->getAlignment()->setWrapText(true);
                $sheet->getStyle('O4:P' . ($row - 1))->getAlignment()->setWrapText(true);
            }
            else if($topik_subtype == TopikDiskusiBansos::SUB_TYPE_POLITIK_UANG) {
                //Bawaslu

                $sheet->getColumnDimension('A')->setAutoSize(true);
                $sheet->getColumnDimension('B')->setAutoSize(true);
                $sheet->getColumnDimension('C')->setAutoSize(true);
                $sheet->getColumnDimension('D')->setAutoSize(true);
                $sheet->getColumnDimension('E')->setWidth(40);
                $sheet->getColumnDimension('F')->setAutoSize(true);
                $sheet->getColumnDimension('G')->setAutoSize(true);
                $sheet->getColumnDimension('H')->setAutoSize(true);
                $sheet->getColumnDimension('I')->setAutoSize(true);
                $sheet->getColumnDimension('J')->setAutoSize(true);
                $sheet->getColumnDimension('K')->setAutoSize(true);
                $sheet->getColumnDimension('L')->setAutoSize(true);
                $sheet->getColumnDimension('M')->setWidth(30);
                $lastColumn = 'M';

                $sheet->getStyle('E4:E' . ($row - 1))->getAlignment()->setWrapText(true);
                $sheet->getStyle('M4:M' . ($row - 1))->getAlignment()->setWrapText(true);
            }
            else if($topik_subtype == TopikDiskusiBansos::SUB_TYPE_MENERIMA_SUAP) {
                //Bawaslu

                $sheet->getColumnDimension('A')->setAutoSize(true);
                $sheet->getColumnDimension('B')->setAutoSize(true);
                $sheet->getColumnDimension('C')->setAutoSize(true);
                $sheet->getColumnDimension('D')->setAutoSize(true);
                $sheet->getColumnDimension('E')->setWidth(40);
                $sheet->getColumnDimension('F')->setAutoSize(true);
                $sheet->getColumnDimension('G')->setAutoSize(true);
                $sheet->getColumnDimension('H')->setAutoSize(true);
                $sheet->getColumnDimension('I')->setAutoSize(true);
                $sheet->getColumnDimension('J')->setWidth(30);
                $lastColumn = 'J';

                $sheet->getStyle('E4:E' . ($row - 1))->getAlignment()->setWrapText(true);
                $sheet->getStyle('J4:J' . ($row - 1))->getAlignment()->setWrapText(true);
            }
        }
        else {
            $sheet->getColumnDimension('A')->setAutoSize(true);
            $sheet->getColumnDimension('B')->setAutoSize(true);
            $sheet->getColumnDimension('C')->setAutoSize(true);
            $sheet->getColumnDimension('D')->setAutoSize(true);
            $sheet->getColumnDimension('E')->setAutoSize(true);
            $sheet->getColumnDimension('F')->setAutoSize(true);
            $sheet->getColumnDimension('G')->setAutoSize(true);
            $sheet->getColumnDimension('H')->setAutoSize(true);
            $sheet->getColumnDimension('I')->setAutoSize(true);
            $sheet->getColumnDimension('J')->setAutoSize(true);
            $sheet->getColumnDimension('K')->setAutoSize(true);
            $sheet->getColumnDimension('L')->setAutoSize(true);
            $sheet->getColumnDimension('M')->setWidth(40);
            $sheet->getColumnDimension('N')->setAutoSize(true);
            $sheet->getColumnDimension('O')->setAutoSize(true);
            $sheet->getColumnDimension('P')->setWidth(30);
            $sheet->getColumnDimension('Q')->setAutoSize(true);
            $sheet->getColumnDimension('R')->setAutoSize(true);
            $sheet->getColumnDimension('S')->setAutoSize(true);
            $lastColumn = 'S';

            $sheet->getStyle('M4:M' . ($row - 1))->getAlignment()->setWrapText(true);
            $sheet->getStyle('P4:R' . ($row - 1))->getAlignment()->setWrapText(true);
        }


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

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

        return $spreadsheet;
    }

    private function AdditionalValidationBansos($validatedRequest)
    {
        //Phone Validation
        $arrPhone = explode(";", $validatedRequest["phone"]);
        foreach ($arrPhone as $phone) {
            if (strlen($phone) < 10 || strlen($phone) > 13) {
                throw new ValidationException([
                    [
                        'field' => 'phone',
                        'message' => 'Nomor telepon (' . $phone . ') harus memiliki 10-13 digit',
                    ],
                ]);
            }

            $phonePattern = "08";
            if (!($phone[0] === $phonePattern[0] ? strncmp($phone, $phonePattern, strlen($phonePattern)) === 0 : false)) {
                throw new ValidationException([
                    [
                        'field' => 'phone',
                        'message' => 'Nomor telepon (' . $phone . ') tidak valid',
                    ],
                ]);
            }
        }

        if($validatedRequest['topik_type'] != TopikDiskusiBansos::TYPE_PELABUHAN) {
            //NIK Validation
            if (!empty($validatedRequest["nik"])) {
                $nikpkhs = explode(";", $validatedRequest["nik"]);
                foreach ($nikpkhs as $nikpkh) {
                    $objNIKPKH = explode(",", $nikpkh);
                    $nik = $objNIKPKH[0];
                    $pkh = $objNIKPKH[1];

                    if (strlen($nik) != 16) {
                        throw new ValidationException([
                            [
                                'field' => 'nik',
                                'message' => 'NIK harus memiliki panjang 16 karakter(' . $nik . ')',
                            ],
                        ]);
                    }

                    if (!preg_match("/^\d+$/", $nik)) {
                        throw new ValidationException([
                            [
                                'field' => 'nik',
                                'message' => 'NIK harus terdiri dari angka(' . $nik . ')',
                            ],
                        ]);
                    }
                }
            }
        }
    }

    private function ProcessListKeluhan($input, $isVerified, $isPemda)
    {
        $result = [];

        foreach ($input as $data) {
            $data['is_active'] = $data['is_deleted'] == 0 ? 1 : 0;

            $diskusi = Diskusi::findFirstByUuid($data['diskusi_uuid']);
            $latestUserComment = DiskusiComment::findFirst(
                [
                    'diskusi_id = :id_diskusi: and user_id = :id_creator: and is_deleted = :is_deleted:',
                    'bind' => [
                        'id_diskusi' => $diskusi->id,
                        'id_creator' => $diskusi->user_id,
                        'is_deleted' => 0
                    ],
                    'order' => 'created_at DESC'

                ]
            );
            $data['latest_reply'] = !empty($latestUserComment) ? $latestUserComment->created_at : null;

            $diskusiHistoryVerifikator = DiskusiBansosHistory::findFirst(
                [
                    'id_diskusi_bansos = :id_diskusi_bansos: and status in ({verified_status:array}) and deleted_at is null',
                    'bind' => [
                        'id_diskusi_bansos' => $data['id'],
                        'verified_status' => [
                            DiskusiBansos::CONFIRMED,
                            DiskusiBansos::CLOSED
                        ]
                    ],
                    'order' => 'created_at DESC'
                ]
            );
            if (!empty($diskusiHistoryVerifikator)) {
                $data['status_verifikasi'] = $diskusiHistoryVerifikator->status;
                $data['tanggal_verifikasi'] = $diskusiHistoryVerifikator->created_at;
            } else {
                $data['status_verifikasi'] = null;
                $data['tanggal_verifikasi'] = null;
            }

            $diskusiHistoryPemda = DiskusiBansosHistory::findFirst(
                [
                    'id_diskusi_bansos = :id_diskusi_bansos: and status in ({verified_status:array}) and deleted_at is null',
                    'bind' => [
                        'id_diskusi_bansos' => $data['id'],
                        'verified_status' => [
                            DiskusiBansos::ON_PROGRESS,
                            DiskusiBansos::DONE,
                            DiskusiBansos::CONFIRMED_TO_KEMSOS
                        ]
                    ],
                    'order' => 'created_at DESC'
                ]
            );
            if (!empty($diskusiHistoryPemda)) {
                $data['status_pemda'] = $diskusiHistoryPemda->status;
                $data['tanggal_pemda'] = $diskusiHistoryPemda->created_at;
            } else {
                $data['status_pemda'] = null;
                $data['tanggal_pemda'] = null;
            }

            if (($isVerified == 1 && empty($diskusiHistoryVerifikator) || ($isPemda == 1 && empty($diskusiHistoryPemda)))) {
                continue;
            }

            $result[] = $data;
        }

        return $result;
    }

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

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

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

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

        return $result;
    }

    private function quote($string) {
        return "'" . $string . "'";
    }

    private function quotePercent($string) {
        return "'%" . $string . "%'";
    }
    #endregion

    public function anggaranNasional() {
        $result = ApbdBansos::find(
            [
                "columns" => 'sum(bl_kes) as penanganan_kesehatan, sum(bl_jps) as penanganan_dampak_ekonomi, sum(pl_pde) as jaring_pengaman_sosial'
            ]
        )->toArray();

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

    public function barChartJenisKeluhanPemilu() {
        $this->checkOneOfScopes(['bansos.view_all', self::SCOPE_MONITORING_KELUHAN_PEMILU]);

        $result = [];
        $idInstansi = $this->shared->user->id_instansi;
        $instansiBawaslu = Instansi::findFirst([
            'nama = ?0',
            'bind' => [
                DiskusiBansos::BAWASLU_NAME
            ]
        ]);

        if($idInstansi = $instansiBawaslu->id) {
            //Fiilter
            $params = $this->request->get();
            $params['status'] = array_key_exists('status', $params) ? $params['status'] : null;
            $params['id_topik'] = array_key_exists('id_topik', $params) ? $params['id_topik'] : null;
            $params['id_objek_pemilu'] = array_key_exists('id_objek_pemilu', $params) ? $params['id_objek_pemilu'] : null;
            $params['id_instansi'] = array_key_exists('id_instansi', $params) ? $params['id_instansi'] : null;

            $validatedParams = $this->validate(
                $params,
                [
                    'status' => ['validators' => ['digit']], //dari diskusi bansos
                    'id_topik' => ['validators' => []], //dari diskusi bansos
                    'id_objek_pemilu' => ['validators' => ['digit']], //dari pemilu
                    'id_instansi' => ['validators' => ['digit']], //dari diskusi bansos instansi_origin
                ]
            );

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

            if(!is_null($validatedParams['status'])) {
                $where[] = 'db.status = :status';
                $bind['status'] = $validatedParams['status'];
            }

            if(!is_null($validatedParams['id_topik'])) {
                $where[] = 'db.id_topik = :id_topik';
                $bind['id_topik'] = $validatedParams['id_topik'];
            }

            if(!is_null($validatedParams['id_objek_pemilu'])) {
                $where[] = 'dkp.id_objek_pemilu = :id_objek_pemilu';
                $bind['id_objek_pemilu'] = $validatedParams['id_objek_pemilu'];
            }

            if(!is_null($validatedParams['id_instansi'])) {
                $where[] = 'db.id_instansi_origin = :id_instansi';
                $bind['id_instansi'] = $validatedParams['id_instansi'];
            }

            if(count($where) > 0) {
                $strWhere = 'WHERE ' . implode(' AND ', $where);
            }

            $q = "
            SELECT
              COALESCE(COUNT(CASE WHEN elem = '1' THEN 1 END), 0) AS count_1,
              COALESCE(COUNT(CASE WHEN elem = '2' THEN 1 END), 0) AS count_2,
              COALESCE(COUNT(CASE WHEN elem = '3' THEN 1 END), 0) AS count_3
            FROM (
              SELECT unnest(string_to_array(jenis_gratifikasi, ';')) AS elem
              FROM diskusi_keluhan_pemilu dkp
			  LEFT JOIN diskusi_bansos db ON dkp.id_diskusi_bansos = db.id
              $strWhere
            ) subquery;
            ";
            $result = $this->rawQuery($q, $bind);
        }
        return new SimpleResponse($result);
    }
}