<?php

use Jaga\Models\UsersSekolah;
use Phalcon\Mvc\Model\Query;
use Jaga\Models\PAK\PAKIsianSekolah;
use Jaga\Models\PAK\PAKIsianSekolahDetail;
use Jaga\Models\PAK\PAKIsianSekolahAttachment;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class PAKIsianSekolahController extends BaseController
{
    protected $user_sekolah_npsn;
    protected $user_sekolah_jenjang;
    protected $user_sekolah_kelas;

    public function onConstruct()
    {
        parent::onConstruct();
        $this->setUserSekolahAttributes();
    }

    protected function setUserSekolahAttributes()
    {
        $user = UmUser::findOrFail($this->userID);

        $columns = [
            'um_id',
            'um_user_name',
            'email',
        ];

        $result = $user->toArray($columns);

        $this->user_sekolah_npsn = isset($result['sekolah'][0]['npsn']) ? $result['sekolah'][0]['npsn'] : 0;
        $this->user_sekolah_jenjang = isset($result['sekolah'][0]['jenjang'][0]['nama_jenjang']) ? $result['sekolah'][0]['jenjang'][0]['nama_jenjang'] : 0;
        $this->user_sekolah_kelas = isset($result['sekolah'][0]['jenjang'][0]['kelas']) ? $result['sekolah'][0]['jenjang'][0]['kelas'] : 0;

        return;
    }

    public function index()
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.view.isiansekolah']);
        $params = $this->validateIndexRequest($this->request);
        $conditions = PAKIsianSekolah::class . ".deleted_at IS NULL";

        // user sekolah melihat isian dirinya sendiri
        if ($this->hasRole('user_sekolah_pak')) {
            $conditions .= " AND sekolah_npsn = '" . $this->user_sekolah_npsn . "'";
        }
        // user dinas melihat isian yang berada di bawah kota/kab dinas ybs
        elseif ($this->hasRole('user_dinas_pak') && !$this->hasRole('admin_pak')) {
            $instansi = $this->shared->user->instansi;
            $allowed_npsn = $instansi->getSekolahNpsn();

            if (!empty($params['keyword'])) {
                $query = new Query(
                    "SELECT npsn FROM Sekolah where LOWER(nama) LIKE LOWER('%".$params['keyword']."%')",
                    $this->getDI()
                );
                $sekolah = $query->execute();
                $npsn = [];
                if (count($sekolah) > 0) {
                    foreach ($sekolah as $item) {
                        if (in_array($item['npsn'], $allowed_npsn)) {
                            $npsn[] = $item['npsn'];
                        }
                    }
                    $npsn_query_string =  "'" . implode("','", $npsn) .  "'";

                    $conditions .= " AND sekolah_npsn in (" . $npsn_query_string . ")";
                } else {
                    $conditions .= " AND sekolah_npsn = '0'";
                }
            } else {
                $allowed_npsn_query_string =  "'" . implode("','", $allowed_npsn) .  "'";
                $conditions .= " AND sekolah_npsn in (" . $allowed_npsn_query_string . ")";
            }
        }
        elseif (!empty($params['keyword'])) {
            $query = new Query(
                "SELECT npsn FROM Sekolah where LOWER(nama) LIKE LOWER('%".$params['keyword']."%')",
                $this->getDI()
            );
            $sekolah = $query->execute();
            $npsn = [];
            if (count($sekolah) > 0) {
                foreach ($sekolah as $item) {
                    $npsn[] = $item['npsn'];
                }
                $npsn_query_string =  "'" . implode("','", $npsn) .  "'";
                $conditions .= " AND sekolah_npsn in (" . $npsn_query_string . ")";
            } else {
                $conditions .= " AND sekolah_npsn = '0'";
            }
        }

        if (isset($params['status']) && $params['status'] > 0) {
            $conditions .= " AND status = " . $params['status'];
        }

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

        if(!empty($params['provinsi'])) {
            $conditions .= " AND LOWER(".Sekolah::class.".provinsi) LIKE '%" . strtolower($params['provinsi']) . "%'";
        }

        if(!empty($params['provinsi_kode'])) {
            $conditions .= " AND ".Sekolah::class.".kode_provinsi_dagri = '" . $params['provinsi_kode'] . "'";
        }

        if(!empty($params['kota_kab'])) {
            $conditions .= " AND LOWER(".Sekolah::class.".kota_kab) LIKE '%" . strtolower($params['kota_kab']) . "%'";
        }

        if(!empty($params['kota_kab_kode'])) {
            $conditions .= " AND ".Sekolah::class.".kode_kabupaten_dagri = '" . $params['kota_kab_kode'] . "'";
        }

        if(!empty($params['jenjang'])) {
            if (ArrayHelper::isStringJson($params['jenjang'])) {
                $arrJenjang = array_values(json_decode($params['jenjang']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "LOWER(".Jenjang::class.".nama_jenjang) LIKE '" . strtolower($jenjang) . "'");
                }
                $conditions .= " AND (" . implode(" OR ", $arrCond) . ")";
            } else {
                $conditions .= " AND LOWER(".Jenjang::class.".nama_jenjang) LIKE '" . strtolower($params['jenjang']) . "'";
            }
        } elseif (!empty($params['jenjang_id'])) {
            if (ArrayHelper::isStringJson($params['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($params['jenjang_id']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "".Sekolah::class.".jenjang_id = " . $jenjang . "");
                }
                $conditions .= " AND (" . implode(" OR ", $arrCond) . ")";
            } else {
                $conditions .= " AND ".Sekolah::class.".jenjang_id) = " . $params['jenjang_id'];
            }
        }

        $all = PAKIsianSekolah::query()
            ->columns("
             ". PAKIsianSekolah::class .".id,
             ". PAKIsianSekolah::class .".sekolah_npsn,
             ". PAKIsianSekolah::class .".tahun_ajaran,
             ". PAKIsianSekolah::class .".deskripsi,
             ". PAKIsianSekolah::class .".status,
             ". PAKIsianSekolah::class .".created_at,
             ". PAKIsianSekolah::class .".created_by,
             ". PAKIsianSekolah::class .".updated_at,
             ". PAKIsianSekolah::class .".updated_by,
             ". PAKIsianSekolah::class .".deleted_at,
             ". PAKIsianSekolah::class .".deleted_by,
             ". PAKIsianSekolah::class .".verified_at,
             ". PAKIsianSekolah::class .".verified_by,
             ". PAKIsianSekolah::class .".is_published,
             ". Sekolah::class .".provinsi,
             ". Sekolah::class .".kode_provinsi_dagri,
             ". Sekolah::class .".kota_kab,
             ". Sekolah::class .".kode_kabupaten_dagri,
             ". Sekolah::class .".nama AS nama_sekolah,
             ". Jenjang::class .".nama_jenjang AS jenjang
            ")
            ->leftJoin(Sekolah::class, PAKIsianSekolah::class .'.sekolah_npsn = '.Sekolah::class.'.npsn')
            ->leftJoin(Jenjang::class, Sekolah::class . '.jenjang_id = '.Jenjang::class.'.id')
            ->conditions($conditions)
            ->execute();
        $total_record = count($all);

        $order_by = '';
        if($params['order_by'] == 'provinsi' || $params['order_by'] == 'kota_kab') {
            $order_by = Sekolah::class.'.' . $params['order_by'];
        }
        else if($params['order_by'] == 'nama_sekolah') {
            $order_by = Sekolah::class.'.nama';
        }
        else if($params['order_by'] == 'jenjang') {
            $order_by = Jenjang::class.'.nama_jenjang';
        }
        else $order_by = PAKIsianSekolah::class .'.'. $params['order_by'];

        $rows = PAKIsianSekolah::query()
            ->columns("
             ". PAKIsianSekolah::class .".id,
             ". PAKIsianSekolah::class .".sekolah_npsn,
             ". PAKIsianSekolah::class .".tahun_ajaran,
             ". PAKIsianSekolah::class .".deskripsi,
             ". PAKIsianSekolah::class .".status,
             ". PAKIsianSekolah::class .".created_at,
             ". PAKIsianSekolah::class .".created_by,
             ". PAKIsianSekolah::class .".updated_at,
             ". PAKIsianSekolah::class .".updated_by,
             ". PAKIsianSekolah::class .".deleted_at,
             ". PAKIsianSekolah::class .".deleted_by,
             ". PAKIsianSekolah::class .".verified_at,
             ". PAKIsianSekolah::class .".verified_by,
             ". PAKIsianSekolah::class .".is_published,
             ". Sekolah::class .".provinsi,
             ". Sekolah::class .".kode_provinsi_dagri,
             ". Sekolah::class .".kota_kab,
             ". Sekolah::class .".kode_kabupaten_dagri,
             ". Sekolah::class .".nama AS nama_sekolah,
             ". Jenjang::class .".nama_jenjang AS jenjang
            ")
            ->leftJoin(Sekolah::class, PAKIsianSekolah::class .'.sekolah_npsn = '.Sekolah::class.'.npsn')
            ->leftJoin(Jenjang::class, Sekolah::class . '.jenjang_id = '.Jenjang::class.'.id')
            ->conditions($conditions)
            ->limit($params['limit'], $params['offset'])
            ->orderBy($order_by . ' ' . $params['sort'])
            ->execute();

        $result = [];
        if(!empty($rows)) {
            foreach ($rows as $key => $row) {
                $theRow = new PAKIsianSekolah();
                $theRow->assign($row->toArray());
                $item = $theRow->getDetail();
                array_push($result, $item);
            }
        }

        return new PaginatedResponse(
            $result,
            $total_record,
            $params['limit'],
            $params['offset']
        );
    }

    protected function validateIndexRequest($request)
    {
        $params = $request->get();
        $params['limit'] = $request->get('limit', null, 8);
        $params['offset'] = $request->get('offset', null, 0);
        $params['keyword'] = $request->get('keyword', null, '');
        $params['order_by'] = $request->get('order_by', null, 'id');
        $params['sort'] = $request->get('sort', null, 'desc');
        $params['status'] = $request->get('status', null, -1);
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['provinsi'] = $request->get('provinsi', null, '');
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab'] = $request->get('kota_kab', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');

        $allowed_order_by = ['id', 'tahun_ajaran', 'provinsi', 'kota_kab', 'jenjang', 'nama_sekolah', 'status', 'created_at', 'updated_at'];
        $allowed_sort = ['asc', 'desc'];

        $rules = [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
            'keyword' => ['validators' => []],
            'status' => ['validators' => ['digit']],
            'order_by' => ['validators' =>  [[
                'name' => 'inclusion_in',
                'params' => $allowed_order_by
            ]]],
            'sort' => ['validators' =>  [[
                'name' => 'inclusion_in',
                'params' => $allowed_sort
            ]]],
            'tahun_ajaran' => ['validators' => []],
            'provinsi' => ['validators' => []],
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab' => ['validators' => []],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang' => ['validators' => []],
            'jenjang_id' => ['validators' => []],
        ];

        $validatedParams = $this->validate($params, $rules);

        return $validatedParams;

    }

    public function view($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.view.isiansekolah']);

        $isiansekolah = PAKIsianSekolah::findOrFail($id);

        if ($this->hasRole('user_sekolah_pak')) {
            if ($this->user_sekolah_npsn != $isiansekolah->sekolah_npsn) {
                throw new CustomErrorException(
                    BaseResponse::INVALID_ACTION,
                    'User not allowed to view this record!'
                );
            }
        }

        if ($this->hasRole('user_dinas_pak')) {
            $instansi = $this->shared->user->instansi;
            $allowed_npsn = $instansi->getSekolahNpsn();
            $allowed_to_view = in_array($isiansekolah->sekolah_npsn, $allowed_npsn) ? true : false;

            if (!$allowed_to_view) {
                throw new CustomErrorException(
                    BaseResponse::INVALID_ACTION,
                    'User not allowed to view this record!'
                );
            }
        }

        return new CollectionPaginatedResponse([$isiansekolah->getDetail()]);
    }

    public function create()
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.manage.isiansekolah']);

        $isiansekolah = new PAKIsianSekolah();

        $params = $this->validateSaveRequest($this->request, $isiansekolah);
        $params['created_by'] = $this->userID;

        return $this->save($isiansekolah, $params);
    }

    public function update($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.manage.isiansekolah']);

        $isiansekolah = PAKIsianSekolah::findOrFail($id);
        $created_by = $isiansekolah->created_by;

//        if($isiansekolah->status == PAKIsianSekolah::STATUS_VERIFIED) {
//            throw new CustomErrorException(
//                BaseResponse::INVALID_ACTION,
//                'Isian sekolah sudah diverifikasi!'
//            );
//        }

        if(
            $this->hasRole('admin_pak') || /*
            $this->hasRole('user_dinas_pak') || */
            $created_by == $this->userID
        ) {
            $params = $this->validateSaveRequest($this->request, $isiansekolah);
            $params['updated_by'] = $this->userID;
            if($isiansekolah->status==PAKIsianSekolah::STATUS_VERIFIED) $params['status'] = PAKIsianSekolah::STATUS_SUBMITTED;

            return $this->save($isiansekolah, $params);
        }
        else  {
            throw new CustomErrorException(
                BaseResponse::INVALID_ACTION,
                'User not allowed to perform this action!'
            );
        }
    }

    protected function validateSaveRequest($request, $isiansekolah)
    {
        $params = $this->request->getJsonRawBody(true);

        $rules = [
            'sekolah_npsn' => ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 100
                ]
            ]],
            'tahun_ajaran' => ['validators' => [
                [
                    'name' => 'string_length',
                    'params' => 9
                ]
            ]],
            'deskripsi' => ['validators' => []],
        ];
        $rules['detail'] = $isiansekolah->isNewRecord() ? ['validators' => ['required']] : ['validators' => []];

        if (isset($params['detail']) && !is_array($params['detail'])) {
            throw new ValidationException([
                'field' => 'detail',
                'message' => 'Format detail Isian sekolah salah'
            ]);
        }

        if (isset($params['detail'])) {
            foreach ($params['detail'] as $key => $detail) {
                $this->validate($detail, [
                    'link' => ['validators' => [
                        [
                            'name' => 'max_string',
                            'params' => 255
                        ]
                    ]],
                    'link_youtube' => ['validators' => [
                        [
                            'name' => 'max_string',
                            'params' => 255
                        ]
                    ]],
                    'link_facebook' => ['validators' => [
                        [
                            'name' => 'max_string',
                            'params' => 255
                        ]
                    ]],
                    'link_blog' => ['validators' => [
                        [
                            'name' => 'max_string',
                            'params' => 255
                        ]
                    ]]
                ]);
                $metode = $detail['metode_id'];
                switch ($metode) {
                    case PAKIsianSekolahDetail::METODE_INSERSI:

                        if(!array_key_exists('mata_pelajaran', $detail)) {
                            throw new ValidationException([
                                'field' => 'mata_pelajaran',
                                'message' => 'Mata pelajaran untuk metode insersi tidak boleh kosong'
                            ]);
                        } else {
                            $this->validate($detail, [
                               'mata_pelajaran' => ['validators' => [
                                   [
                                       'name' => 'max_string',
                                       'params' => 255
                                   ]
                               ]]
                            ]);
                        }

                        if(!array_key_exists('kelas', $detail)) {
                            throw new ValidationException([
                                'field' => 'kelas',
                                'message' => 'Kelas untuk metode insersi tidak boleh kosong'
                            ]);
                        }

                        break;

                    case PAKIsianSekolahDetail::METODE_HABITUASI:

                        if(!array_key_exists('nama_kegiatan', $detail)) {
                            throw new ValidationException([
                                'field' => 'nama_kegiatan',
                                'message' => 'Nama kegiatan untuk metode habituasi tidak boleh kosong'
                            ]);
                        } else {
                            $this->validate($detail, [
                                'nama_kegiatan' => ['validators' => [
                                    [
                                        'name' => 'max_string',
                                        'params' => 100
                                    ]
                                ]]
                            ]);
                        }

                        break;

                    default:
                        break;
                }

                if(array_key_exists('documents', $detail) && !empty($detail['documents'])) {
                    $docs = $detail['documents'];

                    foreach ($docs as $key => $doc) {

                        if(!array_key_exists('attachment_id', $doc) && !array_key_exists('uuid', $doc)) {
                            throw new ValidationException([
                                'field' => 'attachment_id',
                                'message' => 'attachment_id documents tidak boleh kosong'
                            ]);
                        }

                        if (array_key_exists('attachment_id', $doc)) {
                            if ($isiansekolah->isNewRecord() && $attachmentExist = PAKIsianSekolahAttachment::findFirst("attachment_id = " . $doc['attachment_id'])) {
                                throw new ValidationException([
                                    'field' => 'attachment_id',
                                    'message' => 'attachment_id ' . $doc['attachment_id'] . ' sudah digunakan'
                                ]);
                            }

                            Attachments::findOrFail($doc['attachment_id']);
                        } elseif (array_key_exists('uuid', $doc)) {
                            if ($isiansekolah->isNewRecord() && $attachmentExist = Attachments::findFirst("uuid = '" . $doc['uuid'] . "'")) {
                                throw new ValidationException([
                                    'field' => 'uuid',
                                    'message' => 'uuid ' . $doc['uuid'] . ' sudah digunakan'
                                ]);
                            }
                            $this->validate($doc, [
                                'uuid' => ['validators' => ['required']],
                                'filename' => ['validators' => ['required']]
                            ]);
                        }
                    }
                }

                if(array_key_exists('images', $detail) && !empty($detail['images'])) {
                    foreach ($detail['images'] as $key => $imgs) {
                        if(!array_key_exists('attachment_id', $imgs) && !array_key_exists('uuid', $imgs)) {
                            throw new ValidationException([
                                'field' => 'attachment_id',
                                'message' => 'attachment_id images tidak boleh kosong'
                            ]);
                        }

                        if (array_key_exists('attachment_id', $imgs)) {
                            if ($isiansekolah->isNewRecord() && $attachmentExist = PAKIsianSekolahAttachment::findFirst("attachment_id = " . $imgs['attachment_id'])) {
                                throw new ValidationException([
                                    'field' => 'attachment_id',
                                    'message' => 'attachment_id ' .$imgs['attachment_id']. ' sudah digunakan'
                                ]);
                            }

                            Attachments::findOrFail($imgs['attachment_id']);
                        } else if (array_key_exists('uuid', $imgs)) {
                            if ($isiansekolah->isNewRecord() && $attachmentExist = Attachments::findFirst("uuid = '" . $imgs['uuid'] . "'")) {
                                throw new ValidationException([
                                    'field' => 'attachment_id',
                                    'message' => 'attachment_id ' .$imgs['attachment_id']. ' sudah digunakan'
                                ]);
                            }
                            $this->validate($imgs, [
                                'uuid' => ['validators' => ['required']],
                                'filename' => ['validators' => ['required']]
                            ]);
                        }
                    }
                }
            }
        }

        $params['sekolah_npsn'] = empty($params['sekolah_npsn']) ? $this->user_sekolah_npsn : $params['sekolah_npsn'];
        $validatedParams = $this->filterExpectedRequest($params, $rules);

        $sekolahExists = \Sekolah::findOrFail("npsn = '" . $params['sekolah_npsn'] . "'");
        $tahun_ajaran_sekolah_exist = PAKIsianSekolah::findFirst([
                'conditions' => 'tahun_ajaran = :tahun_ajaran: AND sekolah_npsn = :sekolah_npsn:',
                'bind' => [
                    'sekolah_npsn' => $params['sekolah_npsn'],
                    'tahun_ajaran' => $params['tahun_ajaran'],
                ]
        ]);

        if($tahun_ajaran_sekolah_exist
            && ($isiansekolah->isNewRecord()
            || ($isiansekolah->id != $tahun_ajaran_sekolah_exist->id))
        ){
            throw new ValidationException([
                'field' => 'tahun_ajaran',
                'message' => 'Isian sekolah untuk tahun ajaran '. $params['tahun_ajaran'] .' sudah ada.'
            ]);
        }

        return $validatedParams;
    }

    protected function save($isian, $params)
    {
        $this->db->begin();
        try {
            $details = array_key_exists('detail', $params) ? $params['detail'] : null;
            unset($params['detail']);

            $isian->assign($params);
            $isian->status = PAKIsianSekolah::STATUS_SUBMITTED;
            $isian->is_published = array_key_exists('is_published', $params) ? $params['is_published'] : false;
            $saved = $isian->save();

            if (!$saved) {
                $this->throwValidationException($isian->getMessages());
            }

            $detail_ids = [];
            if (!is_null($details)) {
                foreach ($details as $key => $detail) {
                    if(array_key_exists('id', $detail) && !empty($detail['id'] && $detail['id'] != null)){
                        $isiandetail = PAKIsianSekolahDetail::findOrFail($detail['id']);
                    }
                    else{
                        $isiandetail = new PAKIsianSekolahDetail();
                    }
                    $documents = $detail['documents'];
                    $images = $detail['images'];
                    unset($detail['documents']);
                    unset($detail['images']);

                    $isiandetail->assign($detail);
                    $isiandetail->isian_id = $isian->id;
                    $saveDetail = $isiandetail->save();

                    if(!$saveDetail) {
                        $this->throwValidationException($isiandetail->getMessages());
                    }

                    array_push($detail_ids, $isiandetail->id);

                    $doc_ids = [];
                    foreach ($documents as $key => $doc) {

                        if (array_key_exists('uuid', $doc) && array_key_exists('filename', $doc)) {
                            $attachment = new Attachments();
                            $attachment->uuid = $doc['uuid'];
                            $attachment->filename = $doc['filename'];
                            $attachment->tipe = Attachments::TIPE_PUBLIC;
                            $attachment->status = Attachments::STATUS_CREATED;
                            $attachment->created_by = $this->shared->user->um_id;
                            if (!$attachment->save()) {
                                $this->throwValidationException($attachment->getMessages());
                            }

                            $doc['attachment_id'] = $attachment->id;
                        }

                        $attachment = PAKIsianSekolahAttachment::firstOrCreate("attachment_id = " . $doc['attachment_id']);
                        $attachment->isian_detail_id = $isiandetail->id;
                        $attachment->tipe = PAKIsianSekolahAttachment::FILE_TYPE_DOCUMENT;
                        $attachment->attachment_id = $doc['attachment_id'];
                        if (!$attachment->save()) {
                            $this->throwValidationException($attachment->getMessages());
                        }

                        $doc_ids[] = $attachment->id;
                    }

                    $img_ids = [];
                    foreach ($images as $key => $img) {

                        if (array_key_exists('uuid', $img) && array_key_exists('filename', $img)) {
                            $attachment = new Attachments();
                            $attachment->uuid = $img['uuid'];
                            $attachment->filename = $img['filename'];
                            $attachment->tipe = Attachments::TIPE_PUBLIC;
                            $attachment->status = Attachments::STATUS_CREATED;
                            $attachment->created_by = $this->shared->user->um_id;
                            if (!$attachment->save()) {
                                $this->throwValidationException($attachment->getMessages());
                            }

                            $img['attachment_id'] = $attachment->id;
                        }

                        $attachment = PAKIsianSekolahAttachment::firstOrCreate("attachment_id = " . $img['attachment_id']);
                        $attachment->isian_detail_id = $isiandetail->id;
                        $attachment->tipe = PAKIsianSekolahAttachment::FILE_TYPE_IMAGE;
                        $attachment->attachment_id = $img['attachment_id'];
                        if (!$attachment->save()) {
                            $this->throwValidationException($attachment->getMessages());
                        }

                        $img_ids[] = $attachment->id;
                    }

                    $isiandetail->syncAttachments($doc_ids, $img_ids, $this->minio);
                }

                $isian->syncDetails($detail_ids);
            }

            $this->db->commit();

            return new CollectionPaginatedResponse([$isian->getDetail()]);
        }
        catch (\ValidationException $e) {
            $this->db->rollback();
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Form tidak valid", [$e->getMessages()]);
        }
        catch (\CustomErrorException $e) {
            $this->db->rollback();
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Gagal menyimpan data", [$e->getMessages()]);
        }
        catch(\Exception $e) {
            $this->db->rollback();
            throw new CustomErrorException($e->getCode(), "Gagal menyimpan data. Mohon coba beberapa saat lagi", [$e->getMessage()]);
        }
    }

    public function delete($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.manage.isiansekolah']);

        $model = PAKIsianSekolah::findOrFail($id);
        if($model->status == PAKIsianSekolah::STATUS_VERIFIED) {
            throw new CustomErrorException(
                BaseResponse::INVALID_ACTION,
                'Isian sekolah sudah diverifikasi!'
            );
        }

        $deleted = $model->delete($this->userID);

        if (!$deleted) {
            $this->throwValidationException($model->getMessages());
        } else {

            return new CollectionPaginatedResponse([$model]);
        }
    }

    public function restore($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.manage.isiansekolah']);

        $model = PAKIsianSekolah::findOrFail($id, true);
        $restored = $model->restore($this->userID);

        if (!$restored) {
            $this->throwValidationException($model->getMessages());
        } else {

            return new CollectionPaginatedResponse([$model]);
        }
    }

    public function destroy($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.manage.isiansekolah']);

        $model = PAKIsianSekolah::findOrFail($id, true);

        // user sekolah hanya bisa menghapus isiannya sendiri
        if ($this->hasRole('user_sekolah_pak')) {
            if ($this->user_sekolah_npsn != $model->sekolah_npsn) {
                throw new CustomErrorException(
                    BaseResponse::INVALID_ACTION,
                    'User not allowed to perform this action'
                );
            }
        }

        $deleted = $model->forceDelete();

        if (!$deleted) {
            $this->throwValidationException($model->getMessages());
        } else {

            return new CollectionPaginatedResponse([$model]);
        }
    }

    public function verify($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.verify.isiansekolah']);

        $model = PAKIsianSekolah::findOrFail($id);
        $model->status = PAKIsianSekolah::STATUS_VERIFIED;
        $model->verified_by = $this->userID;
        $model->verified_at = date("Y-m-d H:i:s");

        $saved = $model->save();

        if (!$saved) {
            $this->throwValidationException($model->getMessages());
        } else {

            return new CollectionPaginatedResponse([$model->getDetail()]);
        }
    }

    public function batchVerify()
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.verify.isiansekolah']);

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

        if(!isset($rawBody['ids'])) {
            throw new CustomErrorException(
                BaseResponse::INVALID_REQUEST,
                "'ids' is required!"
            );
        }

        $ids = $rawBody['ids'];

        if(empty($ids))
            throw new CustomErrorException(
                BaseResponse::INVALID_REQUEST,
                "'ids' tidak boleh kosong!"
            );

        $isianSekolahs = PAKIsianSekolah::find([
            'conditions' => 'id in ({ids:array}) AND deleted_at is NULL',
            'bind' => [
                'ids' => array_values($ids)
            ]
        ]);

        if(empty($isianSekolahs))
            throw new CustomErrorException(
                BaseResponse::DATA_NOT_FOUND,
                "Data Isian Sekolah tidak ditemukan!"
            );

        $this->db->begin();
        try {
            foreach ($isianSekolahs as $isian) {
                $isian->status = PAKIsianSekolah::STATUS_VERIFIED;
                $isian->verified_by = $this->userID;
                $isian->verified_at = date("Y-m-d H:i:s");
                $saved = $isian->save();
                if (!$saved) {
                    $this->throwValidationException($isian->getMessages());
                }
            }

            $this->db->commit();
            return new CollectionPaginatedResponse($isianSekolahs);
        }
        catch (Exception $exception) {
            $this->db->rollback();
            throw new Exception($exception);
        }
    }

    public function publish($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.publish.isiansekolah']);
        $model = PAKIsianSekolah::findOrFail($id);

        $this->checkPublishAccess($model->sekolah_npsn);
        $published = $model->save(
            [
                'is_published' => true,
                'updated_by' => $this->userID
            ]
        );

        if (!$published) {
            $this->throwValidationException($model->getMessages());
        } else {

            return new CollectionPaginatedResponse([$model]);
        }
    }

    public function unpublish($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.publish.isiansekolah']);
        $model = PAKIsianSekolah::findOrFail($id);

        $this->checkPublishAccess($model->sekolah_npsn);
        $unpublished = $model->save(
            [
                'is_published' => false,
                'updated_by' => $this->userID
            ]
        );

        if (!$unpublished) {
            $this->throwValidationException($model->getMessages());
        } else {

            return new CollectionPaginatedResponse([$model]);
        }
    }

    private function checkPublishAccess($npsn)
    {
        if ($this->hasRole('user_dinas_pak') && !$this->hasOneOfRoles(['admin_pak','verifikator_pak'])) {
            $instansi = $this->shared->user->instansi;
            $allowed_npsn = $instansi->getSekolahNpsn();
            $has_access = in_array($npsn, $allowed_npsn) ? true : false;

            if (!$has_access) {
                throw new CustomErrorException(
                    BaseResponse::INVALID_ACTION,
                    'User not allowed to perform this action'
                );
            }
        }
    }

    public function getDetailByNpsn($npsn){
        $getParams = $this->request->get();

        $getParams['tahun_ajaran'] = array_key_exists('tahun_ajaran', $getParams) ? $getParams['tahun_ajaran'] : null;

        $conditions = 'sekolah_npsn = ?0';
        $bind = [$npsn];

        if($getParams['tahun_ajaran'] != null) {
            $conditions .= ' AND tahun_ajaran = ?1';
            array_push($bind, $getParams['tahun_ajaran']);
        }

        $data = PAKIsianSekolah::find([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

        $result = [];

        foreach ($data as $key => $item) {
            $itemResult = $item->getDetail();
            array_push($result, $itemResult);
        }

        return new SimpleResponse($result);
    }

    public function getTahunAjaranByNpsn($npsn) {
        $this->checkIsAuthenticated();
        $data = PAKIsianSekolah::find([
            'columns' => 'DISTINCT(tahun_ajaran) as tahun_ajaran',
            'conditions' => 'sekolah_npsn = ?0',
            'bind' => [
                $npsn
            ]
        ]);

        return new SimpleResponse($data);
    }

    public function listStatus() {
        $this->checkIsAuthenticated();
        $pakIsianSekolah = new PAKIsianSekolah();

        return new SimpleResponse($pakIsianSekolah->listStatus());
    }

    public function listSekolahWithDetailIsian() {
        $this->checkIsAuthenticated();
        $this->hasOneOfRoles(['pak_verifikator','pak_user_dinas','pak_admin']);
        $request = $this->request;
        $params = $request->get();
        $params['limit'] = $request->get('limit', null, 10);
        $params['offset'] = $request->get('offset', null, 0);
        $params['nama'] = $request->get('nama', null, '');
        $params['order_by'] = $request->get('order_by', null, 'nama');
        $params['sort'] = $request->get('sort', null, 'desc');
        $params['um_user_name'] = $request->get('um_user_name', null, '');
        $params['isian_username_null'] = $request->get('isian_username_null', null, false);
        $params['isian_username_not_null'] = $request->get('isian_username_not_null', null, false);
        $params['isian_isian_null'] = $request->get('isian_isian_null', null, false);
        $params['isian_isian_not_null'] = $request->get('isian_isian_not_null', null, false);
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['status'] = $request->get('status', null, -1);
        $params['provinsi'] = $request->get('provinsi', null, '');
        $params['kota_kab'] = $request->get('kota_kab', null, '');
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');

        $allowed_order_by = ['nama', 'um_user_name', 'tahun_ajaran', 'status'];
        $allowed_sort = ['asc', 'desc'];

        $rules = [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
            'nama' => ['validators' => []],
            'status' => ['validators' => ['digit']],
            'order_by' => ['validators' =>  [[
                'name' => 'inclusion_in',
                'params' => $allowed_order_by
            ]]],
            'sort' => ['validators' =>  [[
                'name' => 'inclusion_in',
                'params' => $allowed_sort
            ]]],
            'um_user_name' => ['validators' => []],
            'tahun_ajaran' => ['validators' => []],
            'isian_username_null' => ['validators' => []],
            'isian_username_not_null' => ['validators' => []],
            'isian_isian_null' => ['validators' => []],
            'isian_isian_not_null' => ['validators' => []],
            'provinsi' => ['validators' => []],
            'kota_kab' => ['validators' => []],
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang' => ['validators' => []],
            'jenjang_id' => ['validators' => []]
        ];

        $validatedParams = $this->validate($params, $rules);

        $conditions = [];
        $conCount = [];

        if (isset($params['status']) && $params['status'] > 0) {
            array_push($conditions, "status = " . $params['status']);
            array_push($conCount, "pis.status = " . $params['status']);
        }
        if(!empty($validatedParams['nama'])) {
            array_push($conditions, "lower(".Sekolah::class.".nama) LIKE '%" . strtolower($validatedParams['nama']) . "%'");
            array_push($conCount, "lower(sk.nama) LIKE '%" . strtolower($validatedParams['nama']) . "%'");
        }
        if($validatedParams['isian_username_null']) {
            array_push($conditions, UmUser::class.".um_user_name IS NULL");
            array_push($conCount, "uu.um_user_name IS NULL");
        }
        if($validatedParams['isian_username_not_null']) {
            array_push($conditions, UmUser::class.".um_user_name IS NOT NULL");
            array_push($conCount, "uu.um_user_name IS NOT NULL");
        }
        else if(!empty($validatedParams['um_user_name'])) {
            array_push($conditions, "lower(".UmUser::class.".um_user_name) LIKE '%" . strtolower($validatedParams['um_user_name']) . "%'");
            array_push($conCount, "lower(uu.um_user_name) LIKE '%" . strtolower($validatedParams['um_user_name']) . "%'");
        }
        if(!empty($validatedParams['tahun_ajaran'])) {
            array_push($conditions, "lower(".PAKIsianSekolah::class.".tahun_ajaran) LIKE '%" . strtolower($validatedParams['tahun_ajaran']) . "%'");
            array_push($conCount, "lower(pis.tahun_ajaran) LIKE '%" . strtolower($validatedParams['tahun_ajaran']) . "%'");
        }
        if($validatedParams['isian_isian_null']) {
            array_push($conditions, PAKIsianSekolah::class.".id IS NULL");
            array_push($conCount, "pis.id IS NULL");
        }
        if($validatedParams['isian_isian_not_null']) {
            array_push($conditions, PAKIsianSekolah::class.".id IS NOT NULL");
            array_push($conCount, "pis.id IS NOT NULL");
        }
        if(!empty($validatedParams['provinsi'])) {
            array_push($conditions, Sekolah::class . ".provinsi ILIKE '%" . strtolower($validatedParams['provinsi']) . "%'");
            array_push($conCount, "sk.provinsi ILIKE '%" . strtolower($validatedParams['provinsi']) . "%'");
        }
        if(!empty($validatedParams['kota_kab'])) {
            array_push($conditions, Sekolah::class . ".kota_kab ILIKE '%" . strtolower($validatedParams['kota_kab']) . "%'");
            array_push($conCount, "sk.kota_kab ILIKE '%" . strtolower($validatedParams['kota_kab']) . "%'");
        }
        if(!empty($validatedParams['provinsi_kode'])) {
            array_push($conditions, Sekolah::class . ".kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
            array_push($conCount, "sk.kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
        }
        if(!empty($validatedParams['kota_kab_kode'])) {
            array_push($conditions, Sekolah::class . ".kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
            array_push($conCount, "sk.kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
        }
        if(!empty($validatedParams['jenjang'])) {
            if (ArrayHelper::isStringJson($validatedParams['jenjang'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang']));
                $arrCond = $arrCount = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, Jenjang::class . ".nama_jenjang ILIKE '" . strtolower($jenjang) . "'");
                    array_push($arrCount, "jj.nama_jenjang ILIKE '" . strtolower($jenjang) . "'");
                }
                array_push($conditions, "(" . implode(" OR ", $arrCond) . ")");
                array_push($conCount, "(" . implode(" OR ", $arrCount) . ")");
            } else {
                array_push($conditions, Jenjang::class . ".nama_jenjang ILIKE '" . strtolower($validatedParams['jenjang']) . "'");
                array_push($conCount, "jj.nama_jenjang ILIKE '" . strtolower($validatedParams['jenjang']) . "'");
            }
        } elseif(!empty($validatedParams['jenjang_id'])) {
            if (ArrayHelper::isStringJson($validatedParams['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang_id']));
                array_push($conditions, Jenjang::class . ".id IN (" . implode(",", $arrJenjang) . ")");
                array_push($conCount, "jj.id IN (" . implode(",", $arrJenjang) . ")");
            } else {
                array_push($conditions, Jenjang::class . ".id = " . $validatedParams['jenjang_id']);
                array_push($conCount, "jj.id = " . $validatedParams['jenjang_id'] );
            }
        }


        $whereCount = empty($conCount) ? "" : "WHERE " . implode(' AND ', $conCount);

        $query = "
            SELECT 
                COUNT(*) AS total
            FROM
            	sekolah sk
            	LEFT JOIN users_sekolah us ON us.npsn = sk.npsn
            	LEFT JOIN um_user uu ON uu.um_id = us.um_id
            	LEFT JOIN pak_isian_sekolah pis ON pis.sekolah_npsn = sk.npsn
            	LEFT JOIN jenjang jj ON jj.id = sk.jenjang_id
            	$whereCount
            	;
        ";

        $total_record = $this->rawQuery($query)[0]['total'];

        $order_by = '';
        if($params['order_by'] == 'nama') {
            $order_by = Sekolah::class . '.' . $params['order_by'];
        }
        else if($params['order_by'] == 'nama_sekolah') {
            $order_by = Sekolah::class . '.nama';
        }
        else if($params['order_by'] == 'jenjang') {
            $order_by = Jenjang::class . '.nama_jenjang';
        }
        else if($params['order_by'] == 'um_user_name') {
            $order_by = UmUser::class . '.' . $params['order_by'];
        }
        else {
            $order_by = PAKIsianSekolah::class . '.' . $params['order_by'];
        }

        $selected = Sekolah::query()
            ->columns("
            ".Sekolah::class.".id,".Sekolah::class.".npsn,".Sekolah::class.".nama,".Sekolah::class.".alamat,".Sekolah::class.".no_telp,".Sekolah::class.".email,".Sekolah::class.".kepsek,".Sekolah::class.".kode_provinsi_dagri,".Sekolah::class.".provinsi,".Sekolah::class.".kode_kabupaten_dagri,".Sekolah::class.".kota_kab,".Sekolah::class.".kecamatan,".Sekolah::class.".akreditasi,".Sekolah::class.".available_photo,".Sekolah::class.".latitude,".Sekolah::class.".longitude,".Sekolah::class.".no_urut_internal,".Sekolah::class.".usia,".Sekolah::class.".nsm,".Sekolah::class.".seen,".Sekolah::class.".id_lapor,".Sekolah::class.".token_lapor,".Sekolah::class.".produk_id,".Sekolah::class.".sekolah_id,".Sekolah::class.".nama_1,".Sekolah::class.".npsn_1,".Sekolah::class.".kode_pos,".Sekolah::class.".kode_wilayah,".Sekolah::class.".bentuk_pendidikan,".Sekolah::class.".kurikulum,".Sekolah::class.".daya_listrik,".Sekolah::class.".luas_tanah_bukan_milik,".Sekolah::class.".luas_tanah_milik,".Sekolah::class.".nama_dusun,".Sekolah::class.".desa_kelurahan,".Sekolah::class.".nomor_fax,".Sekolah::class.".npwp,".Sekolah::class.".tahun_ajaran_id,".Sekolah::class.".lab_bahasa,".Sekolah::class.".lab_biologi,".Sekolah::class.".lab_fisika,".Sekolah::class.".lab_ipa,".Sekolah::class.".lab_ips,".Sekolah::class.".lab_kimia,".Sekolah::class.".pd,".Sekolah::class.".pd_laki,".Sekolah::class.".pd_perempuan,".Sekolah::class.".pd_tingkat1,".Sekolah::class.".pd_tingkat10,".Sekolah::class.".pd_tingkat11,".Sekolah::class.".pd_tingkat12,".Sekolah::class.".pd_tingkat13,".Sekolah::class.".pd_tingkat2,".Sekolah::class.".pd_tingkat3,".Sekolah::class.".pd_tingkat4,".Sekolah::class.".pd_tingkat5,".Sekolah::class.".pd_tingkat6,".Sekolah::class.".pd_tingkat7,".Sekolah::class.".pd_tingkat8,".Sekolah::class.".pd_tingkat9,".Sekolah::class.".perpus_baik,".Sekolah::class.".perpus_rusak_berat,".Sekolah::class.".perpus_rusak_ringan,".Sekolah::class.".perpus_rusak_sedang,".Sekolah::class.".rk_baik,".Sekolah::class.".rk_rusak_berat,".Sekolah::class.".rk_rusak_ringan,".Sekolah::class.".rk_rusak_sedang,".Sekolah::class.".rombel,".Sekolah::class.".rombel_1,".Sekolah::class.".rombel_10,".Sekolah::class.".rombel_11,".Sekolah::class.".rombel_12,".Sekolah::class.".rombel_13,".Sekolah::class.".rombel_2,".Sekolah::class.".rombel_3,".Sekolah::class.".rombel_4,".Sekolah::class.".rombel_5,".Sekolah::class.".rombel_6,".Sekolah::class.".rombel_7,".Sekolah::class.".rombel_8,".Sekolah::class.".rombel_9,".Sekolah::class.".rt,".Sekolah::class.".rw,".Sekolah::class.".sanitasi_guru,".Sekolah::class.".sanitasi_siswa,".Sekolah::class.".semester_id,".Sekolah::class.".sertifikasi_iso,".Sekolah::class.".sinkron_terakhir,".Sekolah::class.".status_sekolah,".Sekolah::class.".sumber_listrik,".Sekolah::class.".guru_agama_budi_pekerti,".Sekolah::class.".guru_bahasa_indonesia,".Sekolah::class.".guru_bahasa_inggris,".Sekolah::class.".guru_belums1,".Sekolah::class.".guru_belumsertifikasi,".Sekolah::class.".guru_gol1,".Sekolah::class.".guru_gol2,".Sekolah::class.".guru_gol3,".Sekolah::class.".guru_gtt,".Sekolah::class.".guru_sejarah_indonesia,".Sekolah::class.".jml_lab,".Sekolah::class.".guru_gty,".Sekolah::class.".guru_kelas,".Sekolah::class.".guru_laki,".Sekolah::class.".guru_matematika,".Sekolah::class.".guru_penjaskes,".Sekolah::class.".guru_perempuan,".Sekolah::class.".guru_pkn,".Sekolah::class.".guru_pns,".Sekolah::class.".guru_s1lebih,".Sekolah::class.".guru_seni_budaya,".Sekolah::class.".guru_sertifikasi,".Sekolah::class.".jml_perpus,".Sekolah::class.".jml_rk,".Sekolah::class.".jml_sanitasi,".Sekolah::class.".waktu_penyelenggaraan,".Sekolah::class.".website,".Sekolah::class.".nss,".Sekolah::class.".akses_internet,".Sekolah::class.".guru_gol4,".Sekolah::class.".guru_gol3_1,".Sekolah::class.".id_area_lapor,".Sekolah::class.".periode_aktif,".Sekolah::class.".laboratorium,".Sekolah::class.".tanggal_update_sekolah,".Sekolah::class.".guru_honor,".Sekolah::class.".data_kosong,".Sekolah::class.".guru_kurang_30_th,".Sekolah::class.".guru_31_35_th,".Sekolah::class.".guru_36_40_th,".Sekolah::class.".guru_41_45_th,".Sekolah::class.".guru_46_50_th,".Sekolah::class.".guru_51_55_th,".Sekolah::class.".guru_lebih_55_th,".Sekolah::class.".sanitasi_guru_baik,".Sekolah::class.".sanitasi_guru_rusak_ringan,".Sekolah::class.".sanitasi_guru_rusak_berat,".Sekolah::class.".sanitasi_siswa_baik,".Sekolah::class.".sanitasi_siswa_rusak_sedang,".Sekolah::class.".sanitasi_siswa_rusak_berat,".Sekolah::class.".bentuk,".Sekolah::class.".jenjang_id,".Sekolah::class.".kecamatan_code,".Sekolah::class.".updated_at,".Sekolah::class.".id_instansi_kemenag,".Sekolah::class.".kode_provinsi_dagri,".Sekolah::class.".kode_kabupaten_dagri,
            ".UmUser::class.".um_id,
            ".UmUser::class.".um_user_name,
            ".PAKIsianSekolah::class.".id as pak_isian_sekolah_id,".PAKIsianSekolah::class.".tahun_ajaran
            ")
            ->leftJoin(UsersSekolah::class, UsersSekolah::class . '.npsn = '.Sekolah::class.'.npsn')
            ->leftJoin(UmUser::class, UmUser::class.'.um_id = '.UsersSekolah::class.'.um_id')
            ->leftJoin(PAKIsianSekolah::class, PAKIsianSekolah::class.'.sekolah_npsn = '.Sekolah::class.'.npsn')
            ->leftJoin(Jenjang::class, Jenjang::class.'.id = '.Sekolah::class.'.jenjang_id')
            ->conditions(implode(' AND ', $conditions))
            ->limit($validatedParams['limit'], $validatedParams['offset'])
            ->orderBy($order_by . ' ' . $validatedParams['sort'])
            ->execute();

        $result = [];
        $pakIsianSekolah = new PAKIsianSekolah();
        foreach ($selected as $item) {
            $theRow = new Sekolah();
            $theRow->assign($item->toArray());
            $itemResult = $theRow->toSimpleArray();
            $userSekolah = $theRow->UsersSekolah;
            $itemResult['user_sekolah'] = ($userSekolah == false) ? null : $userSekolah->toArray();
            $itemResult['user'] = null;
            if ($userSekolah) {
                $user = $userSekolah->UmUser;
                $itemResult['user'] = ($user == false) ? null : $user->toArraySimple();
            }

//            $itemResult['isian_sekolah'] = $theRow->PAKIsianSekolah->toArray();
            $itemResult['isian_sekolah'] = [];
            if (!is_null($item['pak_isian_sekolah_id'])) {
                $modelPAKIsianSekolah = PAKIsianSekolah::find("id = " . $item['pak_isian_sekolah_id']);
                $itemResult['isian_sekolah'] = $modelPAKIsianSekolah->toArray();
            }

            if (!empty($itemResult['isian_sekolah'])) {
                foreach ($itemResult['isian_sekolah'] as $key => $value) {
                    $itemResult['isian_sekolah'][$key]['status'] = $pakIsianSekolah->getStringStatus($value['status']);
                }
            }
            array_push($result, $itemResult);
        }

        return new PaginatedResponse(
            $result,
            $total_record,
            $validatedParams['limit'],
            $validatedParams['offset']
        );
    }

    public function excelListSekolahWithDetail() {
        $this->checkIsAuthenticated();
        $this->hasOneOfRoles(['pak_verifikator','pak_user_dinas','pak_admin']);
        $request = $this->request;
        $params = $request->get();
        $params['nama'] = $request->get('nama', null, '');
        $params['um_user_name'] = $request->get('um_user_name', null, '');
//        $params['isian_username_null'] = $request->get('isian_username_null', null, false);
//        $params['isian_isian_null'] = $request->get('isian_isian_null', null, false);
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['status'] = $request->get('status', null, -1);
        $params['provinsi'] = $request->get('provinsi', null, '');
        $params['kota_kab'] = $request->get('kota_kab', null, '');
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');


        $rules = [
            'nama' => ['validators' => []],
            'status' => ['validators' => ['digit']],
            'um_user_name' => ['validators' => []],
            'tahun_ajaran' => ['validators' => []],
//            'isian_username_null' => ['validators' => []],
//            'isian_isian_null' => ['validators' => []],
            'provinsi' => ['validators' => []],
            'kota_kab' => ['validators' => []],
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang' => ['validators' => []],
            'jenjang_id' => ['validators' => []],
        ];

        $validatedParams = $this->validate($params, $rules);

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

        $conditions = [];

        array_push($conditions, "uu.um_user_name IS NOT NULL");
        array_push($conditions, "pis.id IS NOT NULL");

        if (isset($params['status']) && $params['status'] > 0) {
            array_push($conditions, "status = " . $params['status']);
        }
        if(!empty($validatedParams['nama'])) {
            array_push($conditions, "lower(sk.nama) LIKE '%" . strtolower($validatedParams['nama']) . "%'");
        }
        /*if($validatedParams['isian_username_null']) {
            array_push($conditions, "uu.um_user_name IS NULL");
        }
        else*/
            if(!empty($validatedParams['um_user_name'])) {
            array_push($conditions, "lower(uu.um_user_name) LIKE '%" . strtolower($validatedParams['um_user_name']) . "%'");
        }
        if(!empty($validatedParams['tahun_ajaran'])) {
            array_push($conditions, "lower(pis.tahun_ajaran) LIKE '%" . strtolower($validatedParams['tahun_ajaran']) . "%'");
        }
        /*if($validatedParams['isian_isian_null']) {
            array_push($conditions, "pis.id IS NULL");
        }*/
        if(!empty($validatedParams['provinsi_kode'])) {
            array_push($conditions, "sk.kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
        } elseif(!empty($validatedParams['provinsi'])) {
            array_push($conditions, "sk.provinsi ILIKE '%" . strtolower($validatedParams['provinsi']) . "%'");
        }
        if(!empty($validatedParams['kota_kab_kode'])) {
            array_push($conditions, "sk.kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
        } elseif(!empty($validatedParams['kota_kab'])) {
            array_push($conditions, "sk.kota_kab ILIKE '%" . strtolower($validatedParams['kota_kab']) . "%'");
        }
        if(!empty($validatedParams['jenjang'])) {
            if(ArrayHelper::isStringJson($validatedParams['jenjang'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "LOWER(jj.nama_jenjang) LIKE '" . strtolower($jenjang) . "'");
                }
                array_push($conditions, "(" . implode(" OR ", $arrCond) . ")");
            } else {
                array_push($conditions, "LOWER(jj.nama_jenjang) LIKE '" . strtolower($validatedParams['jenjang']) . "'");
            }
        } elseif(!empty($validatedParams['jenjang_id'])) {
            if(ArrayHelper::isStringJson($validatedParams['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang_id']));
                array_push($conditions, "jj.id IN (" . implode(",", $arrJenjang) . ")");
            } else {
                array_push($conditions, "jj.id = " . $validatedParams['jenjang']);
            }
        }

        $whereString = (!empty($conditions) ? ('WHERE ' . implode(' AND ', $conditions)) : '');

        $query = "
            SELECT
            	sk.npsn,
            	sk.nama,
            	sk.provinsi,
            	sk.kota_kab,
            	uu.um_user_name,
            	pis.tahun_ajaran,
            	CASE 
            	WHEN pis.status = 1 THEN 'Draft'
            	WHEN pis.status = 2 THEN 'Submitted'
            	WHEN pis.status = 3 THEN 'Verified'
            	WHEN pis.status = 4 THEN 'Returned'
            	WHEN pis.id IS NULL THEN null
            	ELSE 'Unknown'
            	END as status
            FROM
            	sekolah sk
            	LEFT JOIN users_sekolah us ON sk.npsn = us.npsn
            	LEFT JOIN um_user uu ON us.um_id = uu.um_id
            	LEFT JOIN pak_isian_sekolah pis ON pis.sekolah_npsn = sk.npsn
               	LEFT JOIN jenjang jj ON jj.id = sk.jenjang_id
         $whereString
            	;
        ";

        $result = $this->rawQuery($query);

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

        $columnIndex = 1;
        $rowIndex = 2;

        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "No");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Sekolah");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Provinsi");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Kota/Kapubaten");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Username");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Tahun Ajaran");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Status");

        foreach ($result as $item) {
            $columnIndex = 1;
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $rowIndex-1);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["nama"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["provinsi"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["kota_kab"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["um_user_name"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["tahun_ajaran"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["status"]);
            $rowIndex++;
        }

        $styleHeader = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => [
                'bold'  => true,
                'size'  => 12,
                'name'  => 'Arial'
            ]
        ];

        $styleBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => array(
                'size'  => 9,
                'name'  => 'Arial'
            ),
        ];

        $rangeHeager = 'A1:G1';
        $sheet->getStyle($rangeHeager)->applyFromArray($styleHeader);

        $rangeBody = "A2:G".($rowIndex-1);
        $sheet->getStyle($rangeBody)->applyFromArray($styleBody);

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

    public function excelIndex()
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.view.isiansekolah']);

        ini_set('max_execution_time', '1200');
        set_time_limit(1200);

        $request = $this->request;
        $params = $request->get();
        $params['keyword'] = $request->get('keyword', null, '');
        $params['status'] = $request->get('status', null, -1);
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['provinsi'] = $request->get('provinsi', null, '');
        $params['kota_kab'] = $request->get('kota_kab', null, '');
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');
        $params['columns'] = $request->get('columns', null, null);

        $columns = [];
        if (!is_null($params['columns'])) {
            $columns = array_unique(explode("|", $params['columns']));
        }

        $selectableColumns = PAKIsianSekolah::selectableColumns();
        $allowedColumns = [];
        foreach ($selectableColumns as $column) {
            $allowedColumns[] = $column['value'];
        }

        foreach ($columns as $column) {
            $req = ['column' => $column];
            $this->validate($req, [
                'column' => ['validators' => [['name' => 'inclusion_in', 'params' => $allowedColumns]]]
            ]);
        }

        $rules = [
            'keyword' => ['validators' => []],
            'status' => ['validators' => ['digit']],
            'tahun_ajaran' => ['validators' => []],
            'provinsi' => ['validators' => []],
            'kota_kab' => ['validators' => []],
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang' => ['validators' => []],
            'jenjang_id' => ['validators' => []],
        ];

        $params = $this->validate($params, $rules);

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

        $conditions = "WHERE pis.deleted_at IS NULL";

        // user sekolah melihat isian dirinya sendiri
        if ($this->hasRole('user_sekolah_pak')) {
            $conditions .= " AND sekolah_npsn = '" . $this->user_sekolah_npsn . "'";
        }
        // user dinas melihat isian yang berada di bawah kota/kab dinas ybs
        elseif ($this->hasRole('user_dinas_pak') && !$this->hasRole('admin_pak')) {
            $instansi = $this->shared->user->instansi;
            $allowed_npsn = $instansi->getSekolahNpsn();

            if (!empty($params['keyword'])) {
                $query = new Query(
                    "SELECT npsn FROM Sekolah where LOWER(nama) LIKE LOWER('%".$params['keyword']."%')",
                    $this->getDI()
                );
                $sekolah = $query->execute();
                $npsn = [];
                if (count($sekolah) > 0) {
                    foreach ($sekolah as $item) {
                        if (in_array($item['npsn'], $allowed_npsn)) {
                            $npsn[] = $item['npsn'];
                        }
                    }
                    $npsn_query_string =  "'" . implode("','", $npsn) .  "'";

                    $conditions .= " AND sekolah_npsn in (" . $npsn_query_string . ")";
                } else {
                    $conditions .= " AND sekolah_npsn = '0'";
                }
            } else {
                $allowed_npsn_query_string =  "'" . implode("','", $allowed_npsn) .  "'";
                $conditions .= " AND sekolah_npsn in (" . $allowed_npsn_query_string . ")";
            }
        }
        elseif (!empty($params['keyword'])) {
            $query = new Query(
                "SELECT npsn FROM Sekolah where LOWER(nama) LIKE LOWER('%".$params['keyword']."%')",
                $this->getDI()
            );
            $sekolah = $query->execute();
            $npsn = [];
            if (count($sekolah) > 0) {
                foreach ($sekolah as $item) {
                    $npsn[] = $item['npsn'];
                }
                $npsn_query_string =  "'" . implode("','", $npsn) .  "'";
                $conditions .= " AND sekolah_npsn in (" . $npsn_query_string . ")";
            } else {
                $conditions .= " AND sekolah_npsn = '0'";
            }
        }

        if (isset($params['status']) && $params['status'] > 0) {
            $conditions .= " AND pis.status = " . $params['status'];
        }

        if(!empty($params['tahun_ajaran'])) {
            $conditions .= " AND pis.tahun_ajaran ='" . $params['tahun_ajaran'] . "' ";
        }

        if(!empty($params['provinsi_kode'])) {
            $conditions .= " AND sk.kode_provinsi_dagri = '" . $params['provinsi_kode'] . "'";
        } elseif(!empty($params['provinsi'])) {
            $conditions .= " AND LOWER(sk.provinsi) LIKE '%" . strtolower($params['provinsi']) . "%'";
        }

        if(!empty($params['kota_kab_kode'])) {
            $conditions .= " AND sk.kode_kabupaten_dagri = '" . $params['kota_kab_kode'] . "'";
        } elseif(!empty($params['kota_kab'])) {
            $conditions .= " AND LOWER(sk.kota_kab) LIKE '%" . strtolower($params['kota_kab']) . "%'";
        }

        if(!empty($params['jenjang'])) {
            if(ArrayHelper::isStringJson($params['jenjang'])) {
                $arrJenjang = array_values(json_decode($params['jenjang']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "LOWER(j.nama_jenjang) LIKE '" . strtolower($jenjang) . "'");
                }
                $conditions .= " AND (" . implode(" OR ", $arrCond) . ")";
            } else {
                $conditions .= " AND LOWER(j.nama_jenjang) LIKE '" . strtolower($params['jenjang']) . "'";
            }
        } elseif(!empty($params['jenjang_id'])) {
            if(ArrayHelper::isStringJson($params['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($params['jenjang_id']));
                $conditions .= " AND j.id IN (" . implode(",", $arrJenjang) . ")";
            } else {
                $conditions .= " AND j.id = " . $params['jenjang_id'];
            }
        }

        $dynamicColumns = $this->generateDynamicColumns($columns);
        $qSelect = strlen($dynamicColumns['select']) > 0 ? $dynamicColumns['select']."," : "";
        $qJoin = $dynamicColumns['join'];
        $qGroup = $dynamicColumns['group'];

        $query = "
            SELECT
            	pis.id,
            	j.nama_jenjang,
            	sk.nama AS nama_sekolah,
            	sk.provinsi,
            	sk.kota_kab,
                pis.created_at,
                $qSelect
            	CASE 
                        	WHEN pis.status = 1 THEN 'Draft'
                        	WHEN pis.status = 2 THEN 'Submitted'
                        	WHEN pis.status = 3 THEN 'Verified'
                        	WHEN pis.status = 4 THEN 'Returned'
                        	WHEN pis.id IS NULL THEN null
                        	ELSE 'Unknown'
                        	END as status
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn = sk.npsn
            	LEFT JOIN jenjang j ON sk.jenjang_id = j.id
                LEFT JOIN um_user us ON us.um_id = pis.created_by
                $qJoin
            $conditions
            $qGroup
            	;
        ";

        $result = $this->rawQuery($query);

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

        $columnIndex = 1;
        $rowIndex = 2;

        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "ID");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Jenjang");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Nama Sekolah");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Provinsi");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Kota/Kapubaten");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Status");
        $sheet->setCellValueByColumnAndRow($columnIndex++, 1, "Tanggal Submit");
        foreach ($columns as $column) {
            $sheet->setCellValueByColumnAndRow($columnIndex++, 1, ucwords(str_replace("_", " ", $column)));
        }

        foreach ($result as $item) {
            $columnIndex = 1;
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["id"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["nama_jenjang"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["nama_sekolah"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["provinsi"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["kota_kab"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["status"]);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item["created_at"]);
            foreach ($columns as $column) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, CommonTools::safeToPrintString($item[$column]));
            }
            $rowIndex++;
        }

        $styleHeader = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => [
                'bold'  => true,
                'size'  => 12,
                'name'  => 'Arial'
            ]
        ];

        $styleBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => array(
                'size'  => 9,
                'name'  => 'Arial'
            ),
        ];

        $alphabet = range('A', 'Z');
        $lastColumn = $alphabet[$columnIndex - 2];
        $rangeHeader = "A1:".$lastColumn."1";
        $sheet->getStyle($rangeHeader)->applyFromArray($styleHeader);

        $rangeBody = "A2:".$lastColumn.($rowIndex-1);
        $sheet->getStyle($rangeBody)->applyFromArray($styleBody);

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

    public function excelIsianKegiatan()
    {
        if (!$this->hasOneOfRoles(['verifikator_pak','user_dinas_pak','admin_pak'])) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }
        $params = $this->validateIndexRequest($this->request);
        unset($params['limit']);
        unset($params['offset']);

        $conditions = "pis.id IS NOT NULL AND pis.deleted_at IS NULL";

        // user dinas melihat isian yang berada di bawah kota/kab dinas ybs
        if ($this->hasRole('user_dinas_pak') && !$this->hasRole('admin_pak')) {
            $instansi = $this->shared->user->instansi;
            $allowed_npsn = $instansi->getSekolahNpsn();

            if (!empty($params['keyword'])) {
                $query = new Query(
                    "SELECT npsn FROM Sekolah where LOWER(nama) LIKE LOWER('%".$params['keyword']."%')",
                    $this->getDI()
                );
                $sekolah = $query->execute();
                $npsn = [];
                if (count($sekolah) > 0) {
                    foreach ($sekolah as $item) {
                        if (in_array($item['npsn'], $allowed_npsn)) {
                            $npsn[] = $item['npsn'];
                        }
                    }
                    $npsn_query_string =  "'" . implode("','", $npsn) .  "'";

                    $conditions .= " AND sk.npsn in (" . $npsn_query_string . ")";
                } else {
                    $conditions .= " AND sk.npsn = '0'";
                }
            } else {
                $allowed_npsn_query_string =  "'" . implode("','", $allowed_npsn) .  "'";
                $conditions .= " AND sk.npsn in (" . $allowed_npsn_query_string . ")";
            }
        }
        elseif (!empty($params['keyword'])) {
            $query = new Query(
                "SELECT npsn FROM Sekolah where LOWER(nama) LIKE LOWER('%".$params['keyword']."%')",
                $this->getDI()
            );
            $sekolah = $query->execute();
            $npsn = [];
            if (count($sekolah) > 0) {
                foreach ($sekolah as $item) {
                    $npsn[] = $item['npsn'];
                }
                $npsn_query_string =  "'" . implode("','", $npsn) .  "'";
                $conditions .= " AND sk.npsn in (" . $npsn_query_string . ")";
            } else {
                $conditions .= " AND sk.npsn = '0'";
            }
        }

        if (isset($params['status']) && $params['status'] > 0) {
            $conditions .= " AND pis.status = " . $params['status'];
        }

        if(!empty($params['tahun_ajaran'])) {
            $conditions .= " AND pis.tahun_ajaran ='" . $params['tahun_ajaran'] . "' ";
        }

        if(!empty($params['provinsi'])) {
            $conditions .= " AND LOWER(sk.provinsi) LIKE '%" . strtolower($params['provinsi']) . "%'";
        }

        if(!empty($params['provinsi_kode'])) {
            $conditions .= " AND sk.kode_provinsi_dagri = '" . $params['provinsi_kode'] . "'";
        }

        if(!empty($params['kota_kab'])) {
            $conditions .= " AND LOWER(sk.kota_kab) LIKE '%" . strtolower($params['kota_kab']) . "%'";
        }

        if(!empty($params['kota_kab_kode'])) {
            $conditions .= " AND sk.kode_kabupaten_dagri = '" . $params['kota_kab_kode'] . "'";
        }

        if(!empty($params['jenjang'])) {
            if (ArrayHelper::isStringJson($params['jenjang'])) {
                $arrJenjang = array_values(json_decode($params['jenjang']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "LOWER(jj.nama_jenjang) LIKE '" . strtolower($jenjang) . "'");
                }
                $conditions .= " AND (" . implode(" OR ", $arrCond) . ")";
            } else {
                $conditions .= " AND LOWER(jj.nama_jenjang) LIKE '" . strtolower($params['jenjang']) . "'";
            }
        } elseif (!empty($params['jenjang_id'])) {
            if (ArrayHelper::isStringJson($params['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($params['jenjang_id']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "sk.jenjang_id = " . $jenjang . "");
                }
                $conditions .= " AND (" . implode(" OR ", $arrCond) . ")";
            } else {
                $conditions .= " AND sk.jenjang_id) = " . $params['jenjang_id'];
            }
        }

        $query = "
                SELECT
                    sk.npsn,
                    sk.nama,
                    jj.nama_jenjang,
                    sk.provinsi,
                    sk.kota_kab,
                    uu.nama AS nama_pic,
                    uu.phone AS kontak_pic,
                    pis.tahun_ajaran,
                    pis.deskripsi,
                    pisd.nama_kegiatan,
                    pisd.deskripsi AS deskripsi_detail,
                    pis.id AS id_pak_isian_sekolah,
                    pisd.id AS id_pak_isian_sekolah_detail,
                   CASE 
                        WHEN pis.status = 1 THEN 'Draft'
                        WHEN pis.status = 2 THEN 'Submitted'
                        WHEN pis.status = 3 THEN 'Verified'
                        WHEN pis.status = 4 THEN 'Returned'
                        WHEN pis.id IS NULL THEN null
                        ELSE 'Unknown'
                    END as status,
                    CASE
                        WHEN pis.is_published = true THEN 'Ya'
                        ELSE 'Tidak'
                    END as publish,
                    CASE
                        WHEN pisd.is_favorited = 1 THEN 'Ya'
                        ELSE 'Tidak'
                    END as is_favorited
                FROM
                    sekolah sk
                    LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
                    LEFT JOIN pak_isian_sekolah pis ON sk.npsn = pis.sekolah_npsn 
                    LEFT JOIN pak_isian_sekolah_detail pisd ON pisd.isian_id::int = pis.id
                    LEFT JOIN users_sekolah us ON us.npsn = sk.npsn
                    LEFT JOIN um_user uu ON us.um_id = uu.um_id
                WHERE
                    ".$conditions."
                ORDER BY sk.npsn ASC;
        ";

        $rows = $this->rawQuery($query);

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

        $column = "A";
        $sheet->setCellValue($column."1", "Nama Sekolah");
        $sheet->setCellValue(++$column."1", "NPSN");
        $sheet->setCellValue(++$column."1", "Jenjang");
        $sheet->setCellValue(++$column."1", "Provinsi");
        $sheet->setCellValue(++$column."1", "Kota/Kapubaten");
        $sheet->setCellValue(++$column."1", "Nama PIC");
        $sheet->setCellValue(++$column."1", "Kontak PIC");
        $sheet->setCellValue(++$column."1", "Status");
        $sheet->setCellValue(++$column."1", "Publish");
        $sheet->setCellValue(++$column."1", "Tahun Ajaran");
        $sheet->setCellValue(++$column."1", "Deskripsi Kegiatan");
        $sheet->setCellValue(++$column."1", "Nama Kegiatan");
        $sheet->setCellValue(++$column."1", "Deskripsi Detail Kegiatan");
        $sheet->setCellValue(++$column."1", "Favorite?");
        $lastColumn = $column;

        $rowIndex = 1;
        foreach ($rows as $row) {
            $column = "A";
            $rowIndex++;
            $sheet->setCellValue($column."".$rowIndex, $row["nama"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["npsn"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["nama_jenjang"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["provinsi"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["kota_kab"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["nama_pic"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["kontak_pic"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["status"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["publish"]);
            $sheet->setCellValue(++$column."".$rowIndex, $row["tahun_ajaran"]);
            $sheet->setCellValue(++$column."".$rowIndex, CommonTools::safeToPrintString($row["deskripsi"]));
            $sheet->setCellValue(++$column."".$rowIndex, $row["nama_kegiatan"]);
            $sheet->setCellValue(++$column."".$rowIndex, CommonTools::safeToPrintString($row["deskripsi_detail"]));
            $sheet->setCellValue(++$column."".$rowIndex, $row["is_favorited"]);
        }

        $styleHeader = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => [
                'bold'  => true,
                'size'  => 12,
                'name'  => 'Arial'
            ]
        ];

        $styleBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => array(
                'size'  => 9,
                'name'  => 'Arial'
            ),
        ];

        $rangeHeager = 'A1:'.$lastColumn.'1';
        $sheet->getStyle($rangeHeager)->applyFromArray($styleHeader);

        $rangeBody = "A2:".$lastColumn.$rowIndex;
        $sheet->getStyle($rangeBody)->applyFromArray($styleBody);

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

    public function listTahunAjaran() {
        $this->checkIsAuthenticated();
        $query = "SELECT DISTINCT(tahun_ajaran) FROM pak_isian_sekolah ORDER BY tahun_ajaran;";

        $result = $this->rawQuery($query);

        return new SimpleResponse(array_column($result, 'tahun_ajaran'));
    }

    public function chartSekolah() {
        $this->checkIsAuthenticated();
        $this->hasOneOfRoles(['pak_verifikator','pak_user_dinas','pak_admin']);
        $request = $this->request;
        $params = $request->get();
        $params['tahun_ajaran'] = $request->get('tahun_ajaran', null, '');
        $params['provinsi'] = $request->get('provinsi', null, '');
        $params['provinsi_kode'] = $request->get('provinsi_kode', null, '');
        $params['kota_kab'] = $request->get('kota_kab', null, '');
        $params['kota_kab_kode'] = $request->get('kota_kab_kode', null, '');
        $params['jenjang'] = $request->get('jenjang', null, '');
        $params['jenjang_id'] = $request->get('jenjang_id', null, '');

        $rules = [
            'tahun_ajaran' => ['validators'  => []],
            'provinsi' => ['validators' => []],
            'provinsi_kode' => ['validators' => ['digit']],
            'kota_kab' => ['validators' => []],
            'kota_kab_kode' => ['validators' => ['digit']],
            'jenjang' => ['validators' => []],
            'jenjang_id' => ['validators' => []]
        ];

        $validatedParams = $this->validate($params, $rules);
        $whereSekolah = "";
        $conditions = [];
        if(!empty($validatedParams['provinsi'])) {
            array_push($conditions, "sk.provinsi ILIKE '%" . strtolower($validatedParams['provinsi']) . "%'");
        }
        if(!empty($validatedParams['provinsi_kode'])) {
            array_push($conditions, "sk.kode_provinsi_dagri = '" . $validatedParams['provinsi_kode'] . "'");
        }
        if(!empty($validatedParams['kota_kab'])) {
            array_push($conditions, "sk.kota_kab ILIKE '%" . strtolower($validatedParams['kota_kab']) . "%'");
        }
        if(!empty($validatedParams['kota_kab_kode'])) {
            array_push($conditions, "sk.kode_kabupaten_dagri = '" . $validatedParams['kota_kab_kode'] . "'");
        }
        if(!empty($validatedParams['jenjang'])) {
            if (ArrayHelper::isStringJson($validatedParams['jenjang'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang']));
                $arrCond = [];
                foreach ($arrJenjang as $jenjang) {
                    array_push($arrCond, "LOWER(jj.nama_jenjang) LIKE '" . strtolower($jenjang) . "'");
                }
                array_push($conditions, "(" . implode(" OR ", $arrCond) . ")");
            } else {
                array_push($conditions, "LOWER(jj.nama_jenjang) LIKE '" . strtolower($validatedParams['jenjang']) . "'");
            }
        } elseif(!empty($validatedParams['jenjang_id'])) {
            if (ArrayHelper::isStringJson($validatedParams['jenjang_id'])) {
                $arrJenjang = array_values(json_decode($validatedParams['jenjang_id']));
                array_push($conditions, "jj.id IN (" . implode(",", $arrJenjang) . ")");
            } else {
                array_push($conditions, "jj.id = " . $validatedParams['jenjang_id']);
            }
        }

        $whereSekolah  = (empty($conditions) ? '' : ("WHERE " . implode(" AND ", $conditions)));

        $whereUser  = (empty($conditions) ? 'WHERE us.deleted_at IS NULL' : ("WHERE us.deleted_at IS NULL AND " . implode(" AND ", $conditions)));

        array_push($conditions, 'pis.deleted_at IS NULL');
        if(!empty($validatedParams['tahun_ajaran'])) {
            array_push($conditions, "pis.tahun_ajaran = '" . strtolower($validatedParams['tahun_ajaran']) . "'");
        }

        $whereIsian = ("WHERE " . implode(" AND ", $conditions));

        $select = "COUNT(DISTINCT(sk.npsn)) AS total";

        $query = "
            SELECT
            	$select
            FROM
            	sekolah sk
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereSekolah
            	;
        ";
        $totalSekolah = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_DRAFT. "
            	;
        ";
        $totalIsianDraft = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_SUBMITTED. "
            	;
        ";
        $totalIsianSubmitted = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_VERIFIED. "
            	;
        ";
        $totalIsianVerified = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status = " . PAKIsianSekolah::STATUS_RETURNED. "
            	;
        ";
        $totalIsianReturned = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian  AND status IS NULL
            	;
        ";
        $totalIsianUnknown = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	pak_isian_sekolah pis
            	LEFT JOIN sekolah sk ON pis.sekolah_npsn= sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereIsian
            	;
        ";
        $totalPieIrisan = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	users_sekolah us
            	LEFT JOIN sekolah sk ON us.npsn = sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	$whereUser
        	;
        ";

        $totalPieUserOnly = $this->rawQuery($query)[0]['total'];

        $query = "
            SELECT
            	$select
            FROM
            	users_sekolah us
            	LEFT JOIN sekolah sk ON us.npsn = sk.npsn
            	LEFT JOIN jenjang jj ON sk.jenjang_id = jj.id
            	LEFT JOIN pak_isian_sekolah pis ON pis.sekolah_npsn = sk.npsn
            	$whereUser AND pis.id IS NULL
        	;
        ";
        $totalUserWoIsian = $this->rawQuery($query)[0]['total'];

        $result = [
            'total_sekolah' => $totalSekolah,
            'total_isian_draft' => $totalIsianDraft,
            'total_isian_submitted' => $totalIsianSubmitted,
            'total_isian_verified' => $totalIsianVerified,
            'total_isian_returned' => $totalIsianReturned,
            'total_isian_unknown' => $totalIsianUnknown,
            'total_sekolah_tanpa_user_isian' => $totalSekolah - $totalPieUserOnly,
            'total_user_tanpa_isian' => $totalUserWoIsian,
            'total_irisan_user_isian' => $totalPieIrisan,
            'total_user_only' => $totalPieUserOnly,
        ];

        return new SimpleResponse($result);
    }

    public function favoriteKegiatan($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.verify.isiansekolah']);

        $data = PAKIsianSekolahDetail::findFirst($id);

        if($data == null)
            throw new ValidationException("Data not found!");

        $data->is_favorited = 1;
        $data->updated_at = date('Y-m-d H:i:s');
        $data->updated_by = $this->userID;

        return new SingleResponse([$data->save()]);
    }

    public function unfavoriteKegiatan($id)
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['pak.verify.isiansekolah']);

        $data = PAKIsianSekolahDetail::findFirst($id);

        if($data == null)
            throw new ValidationException("Data not found!");

        $data->is_favorited = 0;
        $data->updated_at = date('Y-m-d H:i:s');
        $data->updated_by = $this->userID;

        return new SingleResponse([$data->save()]);
    }

    private function generateDynamicColumns($columns) {
        $joinPisd = false;
        $joinPisa = false;
        $select = [];
        foreach ($columns as $column) {
            switch ($column) {
                case "tahun_ajaran":
                    $select[] = "pis.tahun_ajaran";
                    break;
                case "deskripsi":
                    $select[] = "pis.deskripsi";
                    break;
                case "metode_penerapan":
                    $select[] = "
                        CASE 
                            WHEN pisd.metode_id = 1 THEN  'Insersi Mata Pelajaran'
                            WHEN pisd.metode_id = 2 THEN 'Habituasi atau Pembiasaan'
                            ELSE 'Metode Lainnya'
                        END as metode_penerapan
                    ";
                    $joinPisd = true;
                    break;
                case "informasi":
                    $select[] = "pisd.nama_kegiatan as informasi";
                    $joinPisd = true;
                    break;
                case "cerita":
                    $select[] = "pisd.deskripsi as cerita";
                    $joinPisd = true;
                    break;
                case "foto_kegiatan":
                    $select[] = "COUNT(pisa.isian_detail_id) FILTER (WHERE pisa.tipe = 2) as foto_kegiatan";
                    $joinPisd = true;
                    $joinPisa = true;
                    break;
                case "berkas":
                    $select[] = "COUNT(pisa.isian_detail_id) FILTER (WHERE pisa.tipe = 1) as berkas";
                    $joinPisd = true;
                    $joinPisa = true;
                    break;
                case "link_facebook":
                    $select[] = "pisd.link_facebook";
                    $joinPisd = true;
                    break;
                case "link_youtube":
                    $select[] = "pisd.link_youtube";
                    $joinPisd = true;
                    break;
                case "link_blog":
                    $select[] = "pisd.link_blog";
                    $joinPisd = true;
                    break;
                case "nama_pic":
                    $select[] = "us.nama as nama_pic";
                    break;
                case "kontak_pic":
                    $select[] = "us.phone as kontak_pic";
                    break;
                case "is_favorited":
                    $select[] = "
                        CASE
                            WHEN pisd.is_favorited = 1 THEN 'Ya'
                            ELSE 'Tidak'
                        END as is_favorited
                    ";
                    $joinPisd = true;
                    break;
                default:
                    break;
            }
        }

        $join = "";
        $group = "";
        if ($joinPisd) {
            $join .= " LEFT JOIN pak_isian_sekolah_detail pisd ON pisd.isian_id::INTEGER = pis.id";
        }
        if ($joinPisa) {
            $join .= " LEFT JOIN pak_isian_sekolah_attachment pisa ON pisa.isian_detail_id = pisd.id";
            $group .= "GROUP BY j.id, pis.id, sk.id, pisd.id, us.um_id";
        }

        return [
            "select" => implode(",", $select),
            "join" => $join,
            "group" => $group
        ];
    }
}