<?php

use Phalcon\Mvc\Model\Query;
use Jaga\Models\PAK\PAKImplementasi;
use Jaga\Models\PAK\PAKImplementasiVerifikasi;
use Jaga\Models\PAK\PAKImplementasiAttachment;
use Jaga\Models\PAK\PAKIsianSekolah;
use Jaga\Models\PAK\PAKIsianSekolahDetail;
use Jaga\Models\PAK\PAKIsianSekolahAttachment;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class PAKDashboardController extends BaseController
{
    public function onConstruct()
    {
        parent::onConstruct();
        $this->checkIsAuthenticated();
    }

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

        // update 20230223, pemda dalam provinsi Jakarta dihitung satu kesatuan
        $conditions_instansi = 'deleted_at IS NULL AND flag_pak = true AND (id_tipe = 1 OR id_tipe = 2) AND id_provinsi <> 31';

        if (array_key_exists('provinsi_id', $params)) {
            $rules = [
                'provinsi_id' => ['validators' => ['digit']],
            ];

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

            $provinsi_id = (int)$params['provinsi_id'];
            $conditions_instansi .= "  AND id_provinsi = " . $provinsi_id;
        }

        $all_instansi = Instansi::find(['conditions' => $conditions_instansi]);
        $total_instansi = count($all_instansi);

        $instansi_ids = [];
        foreach ($all_instansi as $key => $instansi) {
            $instansi_ids[] = $instansi->id;
        }

        if(count($instansi_ids) > 0) {
            $instansi_id_query_string = "(" . implode(',', $instansi_ids) . ")";
            $conditions_implementasi = 'instansi_id in ' . $instansi_id_query_string;

            $all_implementasi = PAKImplementasi::find([
                'columns' => 'instansi_id',
                'conditions' => $conditions_implementasi,
                'group' => 'instansi_id'
            ]);
            $total_implementasi = count($all_implementasi);
        }
        else $total_implementasi = 0;

        // update 20230223, pemda dalam provinsi Jakarta dihitung satu kesatuan
        if (!array_key_exists('provinsi_id', $params) || (array_key_exists('provinsi_id', $params) && $params['provinsi_id'] == 31)) {
            $conditions_instansi = 'deleted_at IS NULL AND flag_pak = true AND (id_tipe = 1 OR id_tipe = 2) AND id_provinsi = 31';
            $instansiJakarta = Instansi::find([
                'columns' => 'id_provinsi',
                'conditions' => $conditions_instansi,
                'group' => 'id_provinsi'
            ]);
            $total_instansi += count($instansiJakarta);

            $implementasiJakarta = $this->rawQuery("
                SELECT count(*) as total FROM
                (
                    SELECT distinct i.id_provinsi
                    FROM pak_implementasi pi 
                    LEFT JOIN instansi i ON pi.instansi_id = i.id
                    WHERE 
                    i.deleted_at IS NULL AND i.flag_pak = true AND (i.id_tipe = 1 OR i.id_tipe = 2) AND i.id_provinsi = 31
                    GROUP BY i.id_provinsi
                ) a
            ");
            $total_implementasi += $implementasiJakarta[0]['total'];
        }

        $implemented = $total_instansi == 0 ? 0 : $total_implementasi/$total_instansi*100;
        $not_implemented = 100-$implemented;

        $result = [
            'sudah_menerapkan' => number_format((float)$implemented, 2, '.', ''),
            'belum_menerapkan' => number_format((float)$not_implemented, 2, '.', ''),
        ];

        $nominal = [
            'sudah_menerapkan' => $total_implementasi,
            'belum_menerapkan' => $total_instansi - $total_implementasi
        ];

        return new SimpleResponse(['persentase' => $result, 'nominal' => $nominal]);
    }

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

        $conditions = "deleted_at IS NULL";

        if (!empty($params['jenjang_id'])) {
            if ($this->is_json($params['jenjang_id'])) {
                $query = new Query(
                    "SELECT npsn FROM Sekolah where jenjang_id IN (" . implode(",", array_values(json_decode($params['jenjang_id']))) . ")",
                    $this->getDI()
                );
            } else {
                $jenjang_id = $params['jenjang_id'];

                $query = new Query(
                    "SELECT npsn FROM Sekolah where jenjang_id = " . $jenjang_id,
                    $this->getDI()
                );
            }

            $sekolah = $query->execute();

            if ($this->hasRole('user_dinas_pak') && !$this->hasOneOfRoles(['admin_pak','verifikator_pak'])) {
                $instansi = $this->shared->user->instansi;
                $allowed_npsn = $instansi->getSekolahNpsn();
            }

            $npsn = [];

            if (count($sekolah) > 0) {
                foreach ($sekolah as $item) {
                    if ($this->hasRole('user_dinas_pak') && !$this->hasOneOfRoles(['admin_pak','verifikator_pak'])) {
                        if (in_array($item['npsn'], $allowed_npsn)) {
                            $npsn[] = (string)$item['npsn'];
                        }
                    }
                    else {
                        $npsn[] = (string)$item['npsn'];
                    }
                }
                $npsn_query_string = "'" . implode("','", $npsn) . "'";

                $conditions .= " AND sekolah_npsn in (" . $npsn_query_string . ") ";
            } else {
                $conditions .= " AND sekolah_npsn = '0'";
            }
        }

        $result = PAKIsianSekolah::find([
            'columns' => 'tahun_ajaran, count(tahun_ajaran) as total',
            'conditions' => $conditions,
            'group' => 'tahun_ajaran',
        ]);

        return new SimpleResponse($result);
    }

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

        $limit = $this->request->get('limit', null, 10);

        $conditions = "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->hasOneOfRoles(['admin_pak','verifikator_pak'])) {
            $instansi = $this->shared->user->instansi;
            $allowed_npsn = $instansi->getSekolahNpsn();
            $allowed_npsn_query_string = "'" . implode( "','", $allowed_npsn) . "'";
            $conditions .= " AND sekolah_npsn in (" . $allowed_npsn_query_string . ") ";
        }

        $rows = PAKIsianSekolah::find([
                    'conditions' => $conditions,
                    'limit' => $limit,
                    'offset' => 0,
                    'order' =>  'created_at desc',
        ]);

        $result = [];
        if(!empty($rows)) {
            foreach ($rows as $key => $row) {
                $item = $row->getSimpleDetail();
                array_push($result, $item);
            }
        }

        return new PaginatedResponse(
            $result,
            count($rows),
            $limit,
            0
        );
    }

    public function getRegulasi() {
        $conditions = "";
        if ($this->hasRole('user_dinas_pak') && !$this->hasRole('admin_pak')) {
            $instansi_id = $this->shared->user->id_instansi;
            $instansi = Instansi::findFirst([
               'conditions' => 'id = :id:',
               'bind' => ['id' => $instansi_id]
            ]);
            if (!$instansi) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
            }
            if ($instansi->id_tipe == 1) {
                $conditions .= " AND (ins.id_provinsi = " . $instansi->id_provinsi . " OR ins.id_tipe = 3)";
            } else if ($instansi->id_tipe == 2) {
                $conditions .= " AND (ins.id_kota_kabupaten = " . $instansi->id_kota_kabupaten . " OR ins.id_tipe = 3)";
            } else {
                $conditions .= " AND (ins.id = " . $instansi_id . " OR ins.id_tipe = 3)";
            }
        }

        $countRegulasi = $this->rawQuery("
            SELECT 
            	COUNT(*) FILTER (WHERE ins.id_tipe = 3) AS total_kementerian,
            	COUNT(*) FILTER (WHERE ins.id_tipe = 1) AS total_provinsi,
            	COUNT(*) FILTER (WHERE ins.id_tipe = 2) AS total_kab_kota
            FROM
            	pak_verifikasi_implementasi pvi
            	LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
            	LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
            	LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                LEFT JOIN pak_verifikasi_implementasi_attachment pvia ON pvia.verifikasi_id = pvi.id
            WHERE
            	pvi.nilai_verifikasi = 100
            	AND pq.questionnaire_category_id = 1
                ".$conditions."
            ;
        ")[0];

        $countPemda = $this->rawQuery("
            SELECT 
                COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 3) AS count_kementerian,
                COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 1) AS count_provinsi,
                COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 2) AS count_kab_kota
            FROM
                pak_verifikasi_implementasi pvi
                LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                LEFT JOIN instansi ins ON pi.instansi_id = ins.id
            WHERE
                pvi.nilai_verifikasi = 100
                AND pq.questionnaire_category_id = 1
                ".$conditions."
            ;
        ")[0];

        $result = array_merge($countRegulasi, $countPemda);

        return new SimpleResponse($result);
    }

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

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'implementasi_id';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'asc';
        $params['tahun'] = array_key_exists('tahun', $params) ? $params['tahun'] : 0;

        $allowed_order = ['implementasi_id', 'jenis_dokumen', 'nama_instansi', 'nomor', 'tahun', 'tentang', 'nama_provinsi', 'nama_kab_kota'];
        $allowed_order_direction = ['asc', 'desc'];
        $order_by_mapping = [
            'implementasi_id' => 'pi.id',
            'jenis_dokumen' => 'pjdq.jenis_dokumen',
            'nama_instansi' => 'ins.nama',
            'nomor' => 'pvia.nomor',
            'tahun' => 'pvia.tahun',
            'tentang' => 'pvia.tentang',
            'nama_provinsi' => 'mp.provinsi_name',
            'nama_kab_kota' => 'mkk.kota_kabupaten_name'
        ];

        $params = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'tipe_instansi' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'tahun' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $order_by_column = $order_by_mapping[$params['order_by']];
        $order_direction = $params['order_direction'];

        $conditions = "pi.deleted_at IS NULL AND pvi.deleted_at IS NULL AND pvi.nilai_verifikasi = 100 AND pq.questionnaire_category_id = 1 AND ins.flag_pak = true";

        if (!empty($params['tipe_instansi'])) {
            $conditions .= " AND ins.id_tipe = " . (int)$params['tipe_instansi'];
        } else {
            $conditions .= " AND ins.id_tipe IN (1,2,3)";
        }

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

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

        if (!empty($params['keyword'])) {
            $conditions .= " AND (";
            $conditions .= " pvia.tentang ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= " OR pvia.nomor ILIKE '" . $params['keyword'] . "'";
            $conditions .= " OR pjdq.jenis_dokumen ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= " OR ins.nama ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= ")";
        }

        if ($params['tahun'] > 0) {
            $conditions .= " AND pvia.tahun = " . $params['tahun'];
        }

        if ($this->hasRole('user_dinas_pak') && !$this->hasRole('admin_pak')) {
            $instansi_id = $this->shared->user->id_instansi;
            $instansi = Instansi::findFirst([
                'conditions' => 'id = :id:',
                'bind' => ['id' => $instansi_id]
            ]);
            if (!$instansi) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
            }
            if ($instansi->id_tipe == 1) {
                $conditions .= " AND (ins.id_provinsi = " . $instansi->id_provinsi . " OR ins.id_tipe = 3)";
            } else if ($instansi->id_tipe == 2) {
                $conditions .= " AND (ins.id_kota_kabupaten = " . $instansi->id_kota_kabupaten . " OR ins.id_tipe = 3)";
            } else {
                $conditions .= " AND ins.id = " . $instansi_id;
            }
        }

        $query = "
            SELECT
                pvia.*, a.uuid as attachment_uuid, pi.id as implementasi_id, pjdq.jenis_dokumen, ins.id as id_instansi, ins.nama as nama_instansi, ins.id_tipe as tipe_instansi, ins.id_provinsi, ins.id_kota_kabupaten, mp.provinsi_name as nama_provinsi, mkk.kota_kabupaten_name as nama_kab_kota
            FROM
                pak_verifikasi_implementasi pvi
                LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                LEFT JOIN master_provinsi mp ON mp.provinsi_code::INTEGER = ins.id_provinsi
                LEFT JOIN master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = ins.id_kota_kabupaten
                LEFT JOIN pak_verifikasi_implementasi_attachment pvia ON pvia.verifikasi_id = pvi.id
                LEFT JOIN attachments a ON a.id = pvia.attachment_id
                LEFT JOIN pak_jenis_dokumen_questionnaire pjdq ON pjdq.id = pvia.jenis_dokumen_id
            WHERE
               ".$conditions."
            ORDER BY ".$order_by_column." ".$order_direction." NULLS LAST
        ";

        $total = $this->rawQuery($query);
        $rows = $this->rawQuery($query . " LIMIT " . $params['limit'] . " OFFSET " . $params['offset']);

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

    public function listPemda() {
        $params = $this->request->get();
        $result = $this->baseListPemda($params);

        return new CollectionPaginatedResponse($result);
    }

    public function exportListPemda() {
        $params = $this->request->get();
        $rows = $this->baseListPemda($params);

        $lastCol = "E";
        $columns = range('A', $lastCol);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'Daerah yang Sudah Menerbitkan Regulasi terkait Pendidikan Antikorupsi');
        $sheet->setCellValue("A2", "per " . DateHelper::formatToday());

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

        $sheet->setCellValue("A4", 'No');
        $sheet->setCellValue("B4", 'Instansi');
        $sheet->setCellValue("C4", 'Provinsi');
        $sheet->setCellValue("D4", 'Kab/Kota');
        $sheet->setCellValue("E4", 'Status');
        $sheet->getStyle("A4:".$lastCol."4")->getFont()->setBold(true);

        $rowIdx = 4;
        $num = 1;
        foreach ($rows as $row) {
            $rowIdx++;
            if ($row['jumlah_regulasi'] > 0) {
                $status = 'Sudah';
                $color = new Color(Color::COLOR_GREEN);
            } else {
                $status = 'Belum';
                $color = new Color(Color::COLOR_RED);
            }
            $sheet->setCellValue("A$rowIdx", $num);
            $sheet->setCellValue("B$rowIdx", $row['nama']);
            $sheet->setCellValue("C$rowIdx", $row['nama_provinsi']);
            $sheet->setCellValue("D$rowIdx", $row['nama_kota_kabupaten']);
            $sheet->setCellValue("E$rowIdx", $status);
            $sheet->getStyle("E$rowIdx")->getFill()->setFillType(Fill::FILL_SOLID)->setStartColor($color);
            $num++;
        }

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

        foreach ($columns as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }

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

    private function baseListPemda($params)
    {
        $conditions = "instansi.flag_pak = true AND instansi.deleted_at IS NULL";
        if (!empty($params['tipe_instansi'])) {
            $conditions .= " AND instansi.id_tipe = " . (int)$params['tipe_instansi'];
        }

        if ($this->hasRole('user_dinas_pak') && !$this->hasRole('admin_pak')) {
            $instansi_id = $this->shared->user->id_instansi;
            $instansi = Instansi::findFirst([
                'conditions' => 'id = :id:',
                'bind' => ['id' => $instansi_id]
            ]);
            if (!$instansi) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
            }
            if ($instansi->id_tipe == 1) {
                $conditions .= " AND instansi.id_provinsi = " . $instansi->id_provinsi;
            } else {
                $conditions .= " AND instansi.id = " . $instansi_id;
            }
        }

        $query = "
            SELECT
                instansi.id,
                instansi.nama,
                instansi.id_provinsi,
                instansi.id_kota_kabupaten,
                master_provinsi.provinsi_name as nama_provinsi,
                master_kota_kabupaten.kota_kabupaten_name as nama_kota_kabupaten,   
                COALESCE(jumlah_regulasi, 0) as jumlah_regulasi
            FROM
                instansi
                LEFT JOIN (
                SELECT
                    instansi.id,
                    instansi.nama,
                    instansi.id_provinsi,
                    instansi.id_kota_kabupaten,
                    count(instansi.id) as jumlah_regulasi
                FROM
                    instansi
                    LEFT JOIN pak_implementasi ON pak_implementasi.instansi_id = instansi.id
                    LEFT JOIN pak_verifikasi_implementasi ON pak_verifikasi_implementasi.implementasi_id = pak_implementasi.id
                    LEFT JOIN pak_questionnaire ON pak_questionnaire.id = pak_verifikasi_implementasi.questionnaire_id
                WHERE
                    pak_questionnaire.questionnaire_category_id = 1 AND
                    pak_verifikasi_implementasi.nilai_verifikasi = 100 
                GROUP BY
                    instansi.id,
                    pak_implementasi.instansi_id
                ) tmp ON tmp.id = instansi.id
                LEFT JOIN master_provinsi ON master_provinsi.provinsi_code::INTEGER = instansi.id_provinsi
                LEFT JOIN master_kota_kabupaten ON master_kota_kabupaten.kota_kabupaten_code::INTEGER = instansi.id_kota_kabupaten
            WHERE " . $conditions . "
            ORDER BY instansi.id_tipe, instansi.nama
        ";

        return $this->rawQuery($query);
    }

    public function listTahunRegulasi() {
        return PAKImplementasiAttachment::query()
            ->columns("DISTINCT(tahun) as tahun")
            ->where("tahun IS NOT NULL")
            ->orderBy("tahun desc")
            ->execute()
            ->toArray();
    }

    private function is_json($string) {
        $decoded = json_decode($string);
        if ( !is_object($decoded) && !is_array($decoded) ) {
            return false;
        }
        return json_last_error() === JSON_ERROR_NONE;
    }

    public function optionColumnIsianSekolah() {
        return new SimpleResponse(PAKIsianSekolah::selectableColumns());
    }

    public function exportCountUserSekolahByWilayah() {
        $query = "
        SELECT
            s.provinsi,
            CASE
            WHEN k1.kota_kabupaten_code IS NOT NULL THEN k1.kota_kabupaten_code
            WHEN k2.kota_kabupaten_code IS NOT NULL THEN k2.kota_kabupaten_code
            ELSE null 
            END as kode,
            CASE
            WHEN k1.kota_kabupaten_name IS NOT NULL THEN k1.kota_kabupaten_name
            WHEN k2.kota_kabupaten_name IS NOT NULL THEN k2.kota_kabupaten_name
            ELSE s.kota_kab
            END as kota_kabupaten,
            count(s.npsn) as jumlah
        FROM
            users_sekolah us
            LEFT JOIN um_user um ON um.um_id = us.um_id
            LEFT JOIN sekolah s ON s.npsn = us.npsn
            LEFT JOIN master_kota_kabupaten k1 ON k1.kota_kabupaten_code = s.kode_kabupaten_dagri
            LEFT JOIN master_kota_kabupaten k2 ON k2.kota_kabupaten_name ILIKE s.kota_kab
        WHERE
            s.npsn IS NOT NULL AND um.is_active = TRUE AND um.is_banned = FALSE
        GROUP BY s.provinsi, k1.kota_kabupaten_name, k2.kota_kabupaten_name, s.kota_kab, k1.kota_kabupaten_code, k2.kota_kabupaten_code
            
        -- 	UPDATE sekolah set kode_kabupaten_dagri = 5272 WHERE npsn = '50204841';
        -- 	UPDATE sekolah set kode_kabupaten_dagri = NULL WHERE npsn = '50204841';
        ";

        $provRef = MasterProvinsi::find(['columns' => 'provinsi_code, provinsi_name']);
        $provs = [];
        foreach ($provRef as $p) {
            $provs[$p['provinsi_code']] = $p['provinsi_name'];
        }

        $kotaRef = MasterKotaKabupaten::find(['columns' => 'provinsi_code, kota_kabupaten_name as kota_kabupaten, 0 as jumlah, 1 as is_master', 'order' => 'provinsi_code'])->toArray();
        $kotas = [];
        foreach ($kotaRef as $k) {
            $kotas[$k['kota_kabupaten']] = $k;
            $kotas[$k['kota_kabupaten']]['provinsi'] = array_key_exists($k['provinsi_code'], $provs) ? $provs[$k['provinsi_code']] : null;
        }

        $rows = $this->rawQuery($query);
        foreach ($rows as $row) {
            if (array_key_exists($row['kota_kabupaten'], $kotas)) {
                $kotas[$row['kota_kabupaten']]['jumlah'] = $row['jumlah'];
            } else {
                $kotas[$row['kota_kabupaten']] = $row;
                $kotas[$row['kota_kabupaten']]['is_master'] = 0;
                $kotas[$row['kota_kabupaten']]['provinsi'] = $row['provinsi'];
            }
        }

        $kotas = array_values($kotas);

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

        $sheet->setCellValue("A1", 'No');
        $sheet->setCellValue("B1", 'Provinsi');
        $sheet->setCellValue("C1", 'Kab/Kota');
        $sheet->setCellValue("D1", 'Jumlah');
        $sheet->setCellValue("E1", 'Data Master Tersedia');
        $sheet->getStyle("A1:".$lastCol."1")->getFont()->setBold(true);

        $rowIdx = 1;
        $num = 1;
        foreach ($kotas as $kota) {
            $rowIdx++;
            $sheet->setCellValue("A$rowIdx", $num);
            $sheet->setCellValue("B$rowIdx", $kota['provinsi']);
            $sheet->setCellValue("C$rowIdx", $kota['kota_kabupaten']);
            $sheet->setCellValue("D$rowIdx", $kota['jumlah']);
            $sheet->setCellValue("E$rowIdx", $kota['is_master'] == 1 ? 'Ya' : 'Tidak');
            $num++;
        }

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

        foreach ($columns as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = "User Sekolah per Kota Kabupaten - " . DateHelper::getNowTimezoned() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }
}