<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class StranasSummaryController extends BaseController
{
    public function drill()
    {
        $level = $this->request->get('level');
        $config = $this->level_config($level);
        $table = $config['table'];
        $subtable = $config['subtable'];
        $nama_col = $level === 'ukuran_keberhasilan' ? 'keterangan' : 'nama';
        $id_col = 'id_' . $level;
        $params = $this->validate(
            $this->request->get(),
            $level === 'global' ?
                [
                    'instansi_id' => ['validators' => ['required', 'digit']]
                ] : [
                    $id_col => ['validators' => ['required', 'digit']],
                    'instansi_id' => ['validators' => ['required', 'digit']]
                ]
        );
        $where = '(s.id_instansi_satker = :instansi_id or :instansi_id = 0)';
        if ($level !== 'global') {
            $where .= " and ($table.id = :$id_col)";
        }
        $query = "
select id,
	max(nama) as nama,
	sum(nilai) as total_nilai,
	sum(nilai_verification) as total_nilai_verification,
	count(*),
	sum(nilai)::decimal/count(*) as presentase_nilai,
	sum(nilai_verification)::decimal/count(*) as presentase_nilai_verification
from (
	select
		distinct on (s.id_instansi_satker, t.id)
		${subtable}.id as id,
        ${subtable}.${nama_col} as nama,
		coalesce(a.nilai, 0) as nilai,
        coalesce(a.nilai_verification,0) as nilai_verification
    from stranas_survey as s
    inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
    inner join stranas_target t on t.id = stq.id_target
    inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
    inner join stranas_subaksi sa on uk.id_subaksi = sa.id
    inner join stranas_aksi aksi on sa.id_aksi = aksi.id
    inner join stranas_fokus f on aksi.id_fokus = f.id
    inner join stranas_periode p on s.id_periode = p.id
    inner join instansi i on s.id_instansi_satker = i.id
    left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
    where $where
	order by
		s.id_instansi_satker,
		t.id,
		a.nilai_verification desc nulls last,
		a.nilai desc nulls last
) as t
group by id
order by nama
        ";
        $result = $this->rawQuery($query, $params);
        return new CollectionPaginatedResponse($result);
    }

    public function drill2()
    {
        $level = $this->request->get('level');
        $local_scope = $this->request->get('local_scope');
        $config = $this->level_config($level);
        $table = $config['table'];
        $subtable = $config['subtable'];
        $weightStatement = $config['weightStatement'];
        $weightColumn = $config['weightColumn'];
        $nama_col = $level === 'ukuran_keberhasilan' ? 'keterangan' : 'nama';
        $id_col = 'id_' . $level;
        $params = $this->validate(
            $this->request->get(),
            $level === 'global' ?
                [
                    'instansi_id' => ['validators' => ['required', 'digit']],
                    'id_tipe_instansi' => ['validators' => ['digit']],
                    'id_periode' => ['validators' => ['digit']],
                    'operator' => ['validators' => []]
                ] : [
                    $id_col => ['validators' => ['required', 'digit']],
                    'instansi_id' => ['validators' => ['required', 'digit']],
                    'id_tipe_instansi' => ['validators' => ['digit']],
                    'id_periode' => ['validators' => ['digit']],
                    'operator' => ['validators' => []]
                ]
        );

        $params['id_periode'] = !empty($params['id_periode']) ? $params['id_periode'] : -1;
        $params['id_tipe_instansi'] = !empty($params['id_tipe_instansi']) ? $params['id_tipe_instansi'] : -1;

        $periode_operator = (int) $params['id_periode'] < 0 ? '<>' : '=';
        $tipe_instansi_operator = (int) $params['id_tipe_instansi'] < 0 ? '<>' : '=';

        if ($params['id_periode'] > 0) {
            if (array_key_exists('operator', $params) && $params['operator'] == '<=') {
                $periode_operator = '<=';
            }
        }
        unset($params['operator']);

        $where = "(s.id_instansi_satker = :instansi_id or :instansi_id = 0)
            AND s.id_periode $periode_operator :id_periode
            AND i.id_tipe $tipe_instansi_operator :id_tipe_instansi";

        if ($level !== 'global') {
            $where .= " and ($table.id = :$id_col)";
        }

        $globalMasterData = "
with master1 as (
	select
		distinct on (s.id_instansi_satker, t.id)
        t.id as id_target,
        t.id_ukuran_keberhasilan,
        uk.id_subaksi,
        sa.id_aksi,
        aksi.id_fokus,
        ${subtable}.id as id,
        ${subtable}.${nama_col} as nama,
        f.presentase_bobot as bobot_fokus,
        aksi.presentase_bobot as bobot_aksi,
        sa.presentase_bobot as bobot_subaksi,
        bobot_uk.presentase as bobot_ukuran_keberhasilan,
        bobot_target.presentase as bobot_target,
        bobot_instansi.presentase as bobot_instansi,
        a.nilai,
        a.nilai_verification
    from stranas_survey as s
    inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
    inner join stranas_target t on t.id = stq.id_target
    inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
    inner join stranas_subaksi sa on uk.id_subaksi = sa.id
    inner join stranas_aksi aksi on sa.id_aksi = aksi.id
    inner join stranas_fokus f on aksi.id_fokus = f.id
    inner join stranas_periode p on s.id_periode = p.id
    inner join (
        SELECT
            tq.id_target as id,
            100 :: decimal / count(distinct s.id_instansi_satker) as presentase
        FROM
            jaga.stranas_survey s
            JOIN jaga.stranas_survey_template t on s.id_survey_template = t.id
            JOIN jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
        GROUP BY
            tq.id_target
        ORDER BY id
    ) as bobot_instansi on t.id = bobot_instansi.id
    inner join (
        SELECT
            id_ukuran_keberhasilan,
            100 :: decimal / count (*) as presentase
        FROM jaga.stranas_target
        group by id_ukuran_keberhasilan
        order by id_ukuran_keberhasilan
    ) as bobot_target on t.id_ukuran_keberhasilan = bobot_target.id_ukuran_keberhasilan
    inner join (
        SELECT
            id_subaksi,
            100 :: decimal / count(*) as presentase
        FROM jaga.stranas_ukuran_keberhasilan
        group by id_subaksi
        order by id_subaksi
    ) as bobot_uk on uk.id_subaksi = bobot_uk.id_subaksi
    left join jaga.instansi i on s.id_instansi_satker = i.id
    left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
    where $where
	order by
		s.id_instansi_satker,
		t.id,
		a.nilai_verification desc nulls last,
		a.nilai desc nulls last
    ),
    master as (
        select
            id,
            nama,
            coalesce(nilai, 0)::decimal $weightStatement as presentase_nilai,
            coalesce(nilai_verification,0)::decimal $weightStatement as presentase_nilai_verification,
            $weightColumn as bobot
        from master1
    )
        ";

        $localMasterData = "
with master1 as(
    select
        distinct on (s.id_instansi_satker, t.id)
        t.id as id_target,
        t.id_ukuran_keberhasilan,
        uk.id_subaksi,
        sa.id_aksi,
        aksi.id_fokus,
        ${subtable}.id as id,
        ${subtable}.${nama_col} as nama,
        a.nilai,
        a.nilai_verification
    from stranas_survey as s
    inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
    inner join stranas_target t on t.id = stq.id_target
    inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
    inner join stranas_subaksi sa on uk.id_subaksi = sa.id
    inner join stranas_aksi aksi on sa.id_aksi = aksi.id
    inner join stranas_fokus f on aksi.id_fokus = f.id
    inner join stranas_periode p on s.id_periode = p.id
    left join jaga.instansi i on s.id_instansi_satker = i.id
    left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
    where $where
    order by
        s.id_instansi_satker,
        t.id,
        a.nilai_verification desc nulls last,
        a.nilai desc nulls last
),
temp_fokus as (
	select 100::decimal/count(DISTINCT id_fokus) as bobot_fokus from master1
),
temp_aksi as (
	select id_fokus, 100::decimal/count(DISTINCT id_aksi) as bobot_aksi from master1 group by id_fokus
),
temp_subaksi as (
	select id_aksi, 100::decimal/count(DISTINCT id_subaksi) as bobot_subaksi from master1 group by id_aksi
),
temp_uk as (
	select id_subaksi, 100::decimal/count(DISTINCT id_ukuran_keberhasilan) as bobot_ukuran_keberhasilan from master1 group by id_subaksi
),
temp_target as (
	select id_ukuran_keberhasilan, 100::decimal/count(DISTINCT id_target) as bobot_target from master1 group by id_ukuran_keberhasilan
),
temp_instansi as (
	select id_target, 100::decimal/count(*) as bobot_instansi from master1 group by id_target
),
master2 as (
        select m1.*,
	temp_fokus.bobot_fokus,
	temp_aksi.bobot_aksi,
	temp_subaksi.bobot_subaksi,
	temp_uk.bobot_ukuran_keberhasilan,
	temp_target.bobot_target,
	temp_instansi.bobot_instansi
from master1 as m1
	join temp_aksi on m1.id_fokus = temp_aksi.id_fokus
	join temp_subaksi on m1.id_aksi = temp_subaksi.id_aksi
	join temp_uk on m1.id_subaksi = temp_uk.id_subaksi
	join temp_target on m1.id_ukuran_keberhasilan = temp_target.id_ukuran_keberhasilan
	join temp_instansi on m1.id_target = temp_instansi.id_target, temp_fokus
),
master as(
    select
        id,
        nama,
        coalesce(nilai, 0)::decimal $weightStatement as presentase_nilai,
        coalesce(nilai_verification,0)::decimal $weightStatement as presentase_nilai_verification,
        $weightColumn as bobot
    from master2
)
    ";

        $masterData = empty($local_scope) ? $globalMasterData : $localMasterData;

        $query = "
        $masterData
select id,
    max(nama) as nama,
    bobot,
	sum(presentase_nilai) as presentase_nilai,
    sum(presentase_nilai_verification) as presentase_nilai_verification
from master as t
group by id, bobot
order by id
        ";
        $result = $this->rawQuery($query, $params);
        return new CollectionPaginatedResponse($result);
    }

    public function drillCapaianInstansi()
    {
        $level = $this->request->get('level');
        $local_scope = $this->request->get('local_scope');
        $config = $this->level_config($level, 1);
        $table = $config['table'];
        $weightInstansiStatement = $config['weightInstansiStatement'];
        $id_col = 'id_' . $level;
        $params = $this->validate(
            $this->request->get(),
            $level === 'global' ?
                [
                    'instansi_id' => ['validators' => ['required', 'digit']],
                    'id_tipe_instansi' => ['validators' => ['digit']],
                    'id_periode' => ['validators' => ['digit']],
                    'operator' => ['validators' => []]
                ] : [
                    $id_col => ['validators' => ['required', 'digit']],
                    'instansi_id' => ['validators' => ['required', 'digit']],
                    'id_tipe_instansi' => ['validators' => ['digit']],
                    'id_periode' => ['validators' => ['digit']],
                    'operator' => ['validators' => []]
                ]
        );

        $params['id_periode'] = !empty($params['id_periode']) ? $params['id_periode'] : -1;
        $params['id_tipe_instansi'] = !empty($params['id_tipe_instansi']) ? $params['id_tipe_instansi'] : -1;

        $periode_operator = (int) $params['id_periode'] < 0 ? '<>' : '=';
        $tipe_instansi_operator = (int) $params['id_tipe_instansi'] < 0 ? '<>' : '=';

        if ($params['id_periode'] > 0) {
            if (array_key_exists('operator', $params) && $params['operator'] == '<=') {
                $periode_operator = '<=';
            }
        }
        unset($params['operator']);

        $where = "(s.id_instansi_satker = :instansi_id or :instansi_id = 0)
            AND s.id_periode $periode_operator :id_periode
            AND i.id_tipe $tipe_instansi_operator :id_tipe_instansi";

        if ($level !== 'global') {
            $where .= " and ($table.id = :$id_col)";
        }
        $globalMasterData = "
with master1 as (
	select
		distinct on (s.id_instansi_satker, t.id)
        t.id as id_target,
        t.id_ukuran_keberhasilan,
        uk.id_subaksi,
        sa.id_aksi,
        aksi.id_fokus,
        i.id as id,
        i.nama as nama,
        f.presentase_bobot as bobot_fokus,
        aksi.presentase_bobot as bobot_aksi,
        sa.presentase_bobot as bobot_subaksi,
        bobot_uk.presentase as bobot_ukuran_keberhasilan,
        bobot_target.presentase as bobot_target,
        bobot_instansi.presentase as bobot_instansi,
        a.nilai,
        a.nilai_verification
    from stranas_survey as s
    inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
    inner join stranas_target t on t.id = stq.id_target
    inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
    inner join stranas_subaksi sa on uk.id_subaksi = sa.id
    inner join stranas_aksi aksi on sa.id_aksi = aksi.id
    inner join stranas_fokus f on aksi.id_fokus = f.id
    inner join stranas_periode p on s.id_periode = p.id
    inner join (
        SELECT
            tq.id_target as id,
            100 :: decimal / count(distinct s.id_instansi_satker) as presentase
        FROM
            jaga.stranas_survey s
            JOIN jaga.stranas_survey_template t on s.id_survey_template = t.id
            JOIN jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
        GROUP BY
            tq.id_target
        ORDER BY id
    ) as bobot_instansi on t.id = bobot_instansi.id
    inner join (
        SELECT
            id_ukuran_keberhasilan,
            100 :: decimal / count (*) as presentase
        FROM jaga.stranas_target
        group by id_ukuran_keberhasilan
        order by id_ukuran_keberhasilan
    ) as bobot_target on t.id_ukuran_keberhasilan = bobot_target.id_ukuran_keberhasilan
    inner join (
        SELECT
            id_subaksi,
            100 :: decimal / count(*) as presentase
        FROM jaga.stranas_ukuran_keberhasilan
        group by id_subaksi
        order by id_subaksi
    ) as bobot_uk on uk.id_subaksi = bobot_uk.id_subaksi
    left join jaga.instansi i on s.id_instansi_satker = i.id
    left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
    where $where
	order by
		s.id_instansi_satker,
		t.id,
		a.nilai_verification desc nulls last,
		a.nilai desc nulls last
    ),
    master as (
        select
            id,
            nama,
            coalesce(nilai, 0)::decimal $weightInstansiStatement as presentase_nilai,
            coalesce(nilai_verification,0)::decimal $weightInstansiStatement as presentase_nilai_verification
        from master1
    )
        ";

        $localMasterData = "
with master1 as(
    select
        distinct on (s.id_instansi_satker, t.id)
        t.id as id_target,
        t.id_ukuran_keberhasilan,
        uk.id_subaksi,
        sa.id_aksi,
        aksi.id_fokus,
        i.id as id,
        i.nama as nama,
        a.nilai,
        a.nilai_verification
    from stranas_survey as s
    inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
    inner join stranas_target t on t.id = stq.id_target
    inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
    inner join stranas_subaksi sa on uk.id_subaksi = sa.id
    inner join stranas_aksi aksi on sa.id_aksi = aksi.id
    inner join stranas_fokus f on aksi.id_fokus = f.id
    inner join stranas_periode p on s.id_periode = p.id
    left join jaga.instansi i on s.id_instansi_satker = i.id
    left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
    where $where
    order by
        s.id_instansi_satker,
        t.id,
        a.nilai_verification desc nulls last,
        a.nilai desc nulls last
),
temp_fokus as (
	select 100::decimal/count(DISTINCT id_fokus) as bobot_fokus from master1
),
temp_aksi as (
	select id_fokus, 100::decimal/count(DISTINCT id_aksi) as bobot_aksi from master1 group by id_fokus
),
temp_subaksi as (
	select id_aksi, 100::decimal/count(DISTINCT id_subaksi) as bobot_subaksi from master1 group by id_aksi
),
temp_uk as (
	select id_subaksi, 100::decimal/count(DISTINCT id_ukuran_keberhasilan) as bobot_ukuran_keberhasilan from master1 group by id_subaksi
),
temp_target as (
	select id_ukuran_keberhasilan, 100::decimal/count(DISTINCT id_target) as bobot_target from master1 group by id_ukuran_keberhasilan
),
temp_instansi as (
	select id_target, 100::decimal/count(*) as bobot_instansi from master1 group by id_target
),
master2 as (
        select m1.*,
	temp_fokus.bobot_fokus,
	temp_aksi.bobot_aksi,
	temp_subaksi.bobot_subaksi,
	temp_uk.bobot_ukuran_keberhasilan,
	temp_target.bobot_target,
	temp_instansi.bobot_instansi
from master1 as m1
	join temp_aksi on m1.id_fokus = temp_aksi.id_fokus
	join temp_subaksi on m1.id_aksi = temp_subaksi.id_aksi
	join temp_uk on m1.id_subaksi = temp_uk.id_subaksi
	join temp_target on m1.id_ukuran_keberhasilan = temp_target.id_ukuran_keberhasilan
	join temp_instansi on m1.id_target = temp_instansi.id_target, temp_fokus
),
master as (
        select
            id,
            nama,
            coalesce(nilai, 0)::decimal $weightInstansiStatement as presentase_nilai,
            coalesce(nilai_verification,0)::decimal $weightInstansiStatement as presentase_nilai_verification
        from master2
    )
    ";

        $masterData = empty($local_scope) ? $globalMasterData : $localMasterData;

        $query = "
        $masterData
        SELECT
            temp.id as id,
            temp.nama as nama,
            sum(temp.presentase_nilai) as total_presentase_nilai,
            sum(temp.presentase_nilai_verification) as total_presentase_nilai_verification
        FROM master as temp
        GROUP BY temp.id, temp.nama
        ORDER BY total_presentase_nilai_verification DESC NULLS LAST, total_presentase_nilai DESC NULLS LAST
        ";
        $result = $this->rawQuery($query, $params);
        return new CollectionPaginatedResponse($result);
    }

    public function drillTarget()
    {
        $level = $this->request->get('level');
        $config = $this->level_config($level);
        $local_scope = $this->request->get('local_scope');
        $table = $config['table'];
        $id_col = 'id_' . $level;
        $params = $this->validate(
            $this->request->get(),
            $level === 'global' ?
                [
                    'instansi_id' => ['validators' => ['required', 'digit']],
                    'id_tipe_instansi' => ['validators' => ['digit']],
                    'id_periode' => ['validators' => ['digit']],
                    'operator' => ['validators' => []]
                ] : [
                    $id_col => ['validators' => ['required', 'digit']],
                    'instansi_id' => ['validators' => ['required', 'digit']],
                    'id_tipe_instansi' => ['validators' => ['digit']],
                    'id_periode' => ['validators' => ['digit']],
                    'operator' => ['validators' => []]
                ]
        );

        $params['id_periode'] = !empty($params['id_periode']) ? $params['id_periode'] : -1;
        $params['id_tipe_instansi'] = !empty($params['id_tipe_instansi']) ? $params['id_tipe_instansi'] : -1;

        $periode_operator = (int) $params['id_periode'] < 0 ? '<>' : '=';
        $tipe_instansi_operator = (int) $params['id_tipe_instansi'] < 0 ? '<>' : '=';

        if ($params['id_periode'] > 0) {
            if (array_key_exists('operator', $params) && $params['operator'] == '<=') {
                $periode_operator = '<=';
            }
        }
        unset($params['operator']);

        $where = "(s.id_instansi_satker = :instansi_id or :instansi_id = 0)
            AND s.id_periode $periode_operator :id_periode
            AND i.id_tipe $tipe_instansi_operator :id_tipe_instansi";

        if ($level !== 'global') {
            $where .= " and ($table.id = :$id_col)";
        }

        $globalMasterData = "
with master as (
	select
		distinct on (s.id_instansi_satker, stq.id_target)
        t.id as id,
        i.id as id_instansi,
        i.nama as nama_instansi,
        t.keterangan as nama,
        coalesce(a.nilai, 0) as nilai,
        coalesce(a.nilai_verification,0) as nilai_verification,
        f.presentase_bobot as bobot_fokus,
        aksi.presentase_bobot as bobot_aksi,
        sa.presentase_bobot as bobot_subaksi,
        bobot_uk.presentase as bobot_ukuran_keberhasilan,
        bobot_target.presentase as bobot_target,
        bobot_instansi.presentase as bobot_instansi
    from stranas_survey as s
    inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
    inner join stranas_target t on t.id = stq.id_target
    inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
    inner join stranas_subaksi sa on uk.id_subaksi = sa.id
    inner join stranas_aksi aksi on sa.id_aksi = aksi.id
    inner join stranas_fokus f on aksi.id_fokus = f.id
    inner join stranas_periode p on s.id_periode = p.id
    inner join (
        SELECT
            tq.id_target as id,
            100 :: decimal / count(distinct s.id_instansi_satker) as presentase
        FROM
            jaga.stranas_survey s
            JOIN jaga.stranas_survey_template t on s.id_survey_template = t.id
            JOIN jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
        GROUP BY
            tq.id_target
        ORDER BY id
    ) as bobot_instansi on t.id = bobot_instansi.id
    inner join (
        SELECT
            id_ukuran_keberhasilan,
            100 :: decimal / count (*) as presentase
        FROM jaga.stranas_target
        group by id_ukuran_keberhasilan
        order by id_ukuran_keberhasilan
    ) as bobot_target on t.id_ukuran_keberhasilan = bobot_target.id_ukuran_keberhasilan
    inner join (
        SELECT
            id_subaksi,
            100 :: decimal / count(*) as presentase
        FROM jaga.stranas_ukuran_keberhasilan
        group by id_subaksi
        order by id_subaksi
    ) as bobot_uk on uk.id_subaksi = bobot_uk.id_subaksi
    left join jaga.instansi i on s.id_instansi_satker = i.id
    left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
    where $where
	order by
		s.id_instansi_satker,
		stq.id_target,
		a.nilai_verification desc nulls last,
		a.nilai desc nulls last
    )
        ";

        $localMasterData = "
with master1 as(
    select
        distinct on (s.id_instansi_satker, stq.id_target)
        t.id as id,
        i.id as id_instansi,
        i.nama as nama_instansi,
        t.keterangan as nama,
        t.id_ukuran_keberhasilan,
        uk.id_subaksi,
        sa.id_aksi,
        aksi.id_fokus,
        coalesce(a.nilai, 0) as nilai,
        coalesce(a.nilai_verification,0) as nilai_verification
    from stranas_survey as s
    inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
    inner join stranas_target t on t.id = stq.id_target
    inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
    inner join stranas_subaksi sa on uk.id_subaksi = sa.id
    inner join stranas_aksi aksi on sa.id_aksi = aksi.id
    inner join stranas_fokus f on aksi.id_fokus = f.id
    inner join stranas_periode p on s.id_periode = p.id
    left join jaga.instansi i on s.id_instansi_satker = i.id
    left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
    where $where
    order by
        s.id_instansi_satker,
        stq.id_target,
        a.nilai_verification desc nulls last,
        a.nilai desc nulls last
),
temp_fokus as (
	select 100::decimal/count(DISTINCT id_fokus) as bobot_fokus from master1
),
temp_aksi as (
	select id_fokus, 100::decimal/count(DISTINCT id_aksi) as bobot_aksi from master1 group by id_fokus
),
temp_subaksi as (
	select id_aksi, 100::decimal/count(DISTINCT id_subaksi) as bobot_subaksi from master1 group by id_aksi
),
temp_uk as (
	select id_subaksi, 100::decimal/count(DISTINCT id_ukuran_keberhasilan) as bobot_ukuran_keberhasilan from master1 group by id_subaksi
),
temp_target as (
	select id_ukuran_keberhasilan, 100::decimal/count(DISTINCT id) as bobot_target from master1 group by id_ukuran_keberhasilan
),
temp_instansi as (
	select id, 100::decimal/count(*) as bobot_instansi from master1 group by id
),
master as (
        select m1.*,
	temp_aksi.bobot_aksi,
	temp_subaksi.bobot_subaksi,
	temp_uk.bobot_ukuran_keberhasilan,
	temp_target.bobot_target,
	temp_instansi.bobot_instansi
from master1 as m1
	join temp_aksi on m1.id_fokus = temp_aksi.id_fokus
	join temp_subaksi on m1.id_aksi = temp_subaksi.id_aksi
	join temp_uk on m1.id_subaksi = temp_uk.id_subaksi
	join temp_target on m1.id_ukuran_keberhasilan = temp_target.id_ukuran_keberhasilan
	join temp_instansi on m1.id = temp_instansi.id
)
    ";

        $masterData = empty($local_scope) ? $globalMasterData : $localMasterData;

        $query = "
        $masterData
select
    master.*,
    master.nilai as presentase_nilai,
    master.nilai_verification as presentase_nilai_verification
from master";

        // $query = "
        //     SELECT
        //         distinct on (s.id_instansi_satker, stq.id_target)
        //         t.id as id,
        //         i.id as id_instansi,
        //         i.nama as nama_instansi,
        //         t.keterangan as nama,
        //         coalesce(a.nilai, 0) as presentase_nilai,
        //         coalesce(a.nilai_verification,0) as presentase_nilai_verification,
        //         f.presentase_bobot as bobot_fokus,
        //         aksi.presentase_bobot as bobot_aksi,
        //         sa.presentase_bobot as bobot_subaksi,
        //         bobot_uk.presentase as bobot_ukuran_keberhasilan,
        //         bobot_target.presentase as bobot_target,
        //         bobot_instansi.presentase as bobot_instansi
        //     FROM
        //         jaga.stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
        //         join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
        //         join jaga.instansi i on s.id_instansi_satker = i.id
        //         inner join stranas_target t on t.id = stq.id_target
        //         join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
        //         join stranas_subaksi sa on uk.id_subaksi = sa.id
        //         join stranas_aksi aksi on sa.id_aksi = aksi.id
        //         join stranas_fokus f on aksi.id_fokus = f.id
        //         join (
        //             SELECT
        //                 tq.id_target as id,
        //                 100 :: decimal / count(distinct s.id_instansi_satker) as presentase
        //             FROM
        //                 jaga.stranas_survey s
        //                 JOIN jaga.stranas_survey_template t on s.id_survey_template = t.id
        //                 JOIN jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
        //             GROUP BY
        //                 tq.id_target
        //             ORDER BY id
        //         ) as bobot_instansi on t.id = bobot_instansi.id
        //         join (
        //             SELECT
        //                 id_ukuran_keberhasilan,
        //                 100 :: decimal / count (*) as presentase
        //             FROM jaga.stranas_target
        //             group by id_ukuran_keberhasilan
        //             order by id_ukuran_keberhasilan
        //         ) as bobot_target on t.id_ukuran_keberhasilan = bobot_target.id_ukuran_keberhasilan
        //         join (
        //             SELECT
        //                 id_subaksi,
        //                 100 :: decimal / count(*) as presentase
        //             FROM jaga.stranas_ukuran_keberhasilan
        //             group by id_subaksi
        //             order by id_subaksi
        //         ) as bobot_uk on uk.id_subaksi = bobot_uk.id_subaksi
        //         left outer join jaga.stranas_survey_answer a
        //             on s.id = a.id_survey and stq.id_target = a.id_question
        //     where $where
        //     order by s.id_instansi_satker, stq.id_target, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
        // ";
        $result = $this->rawQuery($query, $params);
        return new CollectionPaginatedResponse($result);
    }

    protected function level_config($level)
    {
        if ($level === 'global') {
            return [
                'table' => null,
                'subtable' => 'f',
                'weightStatement' => '* bobot_instansi ::  decimal / 100 * bobot_target :: decimal / 100 * bobot_ukuran_keberhasilan :: decimal/100 * bobot_subaksi :: decimal / 100 * bobot_aksi :: decimal / 100',
                'weightInstansiStatement' => '* bobot_instansi ::  decimal / 100 * bobot_target :: decimal / 100 * bobot_ukuran_keberhasilan :: decimal/100 * bobot_subaksi :: decimal / 100 * bobot_aksi :: decimal / 100 * bobot_fokus :: decimal / 100',
                'weightColumn' => 'bobot_fokus',
                'bobotList' => ', bobot_instansi, bobot_target, bobot_ukuran_keberhasilan, bobot_subaksi, bobot_aksi, bobot_fokus',
            ];
        }
        $levels = ['fokus', 'aksi', 'subaksi', 'ukuran_keberhasilan', 'target', 'periode'];
        $table_alias = ['f', 'aksi', 'sa', 'uk', 't', 'p'];
        $weightStatements = [
            '* bobot_instansi :: decimal / 100 * bobot_target :: decimal / 100 * bobot_ukuran_keberhasilan :: decimal/100 * bobot_subaksi :: decimal / 100',
            '* bobot_instansi :: decimal / 100 * bobot_target :: decimal / 100 * bobot_ukuran_keberhasilan :: decimal/100',
            '* bobot_instansi :: decimal / 100 * bobot_target :: decimal / 100',
            '* bobot_instansi :: decimal / 100 ',
            '',
            ''
        ];
        $weightInstansiStatements = [
            '* bobot_instansi :: decimal / 100 * bobot_target :: decimal / 100 * bobot_ukuran_keberhasilan :: decimal/100 * bobot_subaksi :: decimal / 100 * bobot_aksi :: decimal / 100',
            '* bobot_instansi :: decimal / 100 * bobot_target :: decimal / 100 * bobot_ukuran_keberhasilan :: decimal/100 * bobot_subaksi :: decimal / 100',
            '* bobot_instansi :: decimal / 100 * bobot_target :: decimal / 100 * bobot_ukuran_keberhasilan :: decimal/100',
            '* bobot_instansi :: decimal / 100 * bobot_target :: decimal / 100',
            '* bobot_instansi :: decimal / 100 ',
            ''
        ];
        $weightColumns = [
            'bobot_aksi',
            'bobot_subaksi',
            'bobot_ukuran_keberhasilan',
            'bobot_target',
            '',
            ''
        ];

        $bobotListColumns = [
            ', bobot_instansi, bobot_target, bobot_ukuran_keberhasilan, bobot_subaksi, bobot_aksi',
            ', bobot_instansi, bobot_target, bobot_ukuran_keberhasilan, bobot_subaksi',
            ', bobot_instansi, bobot_target, bobot_ukuran_keberhasilan',
            ', bobot_instansi, bobot_target',
            ', bobot_instansi ',
            ''
        ];
        if (!in_array($level, $levels)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Unkonwn level: " . $level);
        }
        $i = array_search($level, $levels);
        $table = $table_alias[$i];
        $subtable = $table_alias[$i + 1];
        $weightStatement = $weightStatements[$i];
        $weightInstansiStatement = $weightInstansiStatements[$i];
        $weightColumn = $weightColumns[$i];
        $bobotList = $bobotListColumns[$i];
        return compact('table', 'subtable', 'weightStatement', 'weightInstansiStatement', 'weightColumn', 'bobotList');
    }

    public function instansi()
    {
        $terms = $this->request->get('terms', 'string', '');
        $type = $this->request->get('type', 'string', 'nasional');
        $limit = 10;
        $query = "select id, nama as value, nama as label from instansi
            where is_stranas = 1 and nama ilike :terms";
        switch ($type) {
            case 'pemda':
                $query .= " and id_provinsi is not null";
                break;
            case 'kl':
                $query .= " and id_provinsi is null";
                break;
            default:
                break;
        }
        $query .= " limit :limit";

        $result = $this->rawQuery(
            $query,
            [
                'terms' => "%${terms}%",
                'limit' => $limit
            ]
        );
        return new CollectionPaginatedResponse($result);
    }

    public function detailUkuranKeberhasilan()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'id' => ['validators' => ['required', 'digit']]
            ]
        );
        $query = "
select
    i.id as instansi_id,
    i.nama as instansi,
    p.id as periode_id,
    p.nama as periode,
    t.keterangan as target,
    coalesce(a.nilai, 0) as nilai,
    coalesce(a.nilai_verification, 0) as nilai_verification
from stranas_survey as s
inner join stranas_survey_template_question stq on stq.id_survey_template = s.id_survey_template
inner join stranas_target t on t.id = stq.id_target
inner join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
inner join stranas_subaksi sa on uk.id_subaksi = sa.id
inner join stranas_aksi aksi on sa.id_aksi = aksi.id
inner join stranas_fokus f on aksi.id_fokus = f.id
inner join stranas_periode p on s.id_periode = p.id
inner join instansi i on s.id_instansi_satker = i.id
left join stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
where uk.id = :id
order by i.id, p.id, a.nilai_verification desc
        ";
        $result = $this->rawQuery($query, $params);
        return new CollectionPaginatedResponse($result);
    }

    // FIXME: delete if the replacement function has stable
    /*
    public function global()
    {
        $result = $this->rawQuery("
select summary.id, summary.nama, sum(nilai) as total_nilai, sum(nilai_verification) as total_nilai_verification, count(*), sum(nilai)::decimal/count(*) as presentase_nilai, sum(nilai_verification)::decimal/count(*) as presentase_nilai_verification
from
	( SELECT distinct on (s.id_instansi_satker, t.id) f.id as id, f.nama as nama, coalesce(a.nilai, 0) as nilai, coalesce(a.nilai_verification,0) as nilai_verification
			FROM jaga. stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
			join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
			left outer join jaga.stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
			join jaga.stranas_target t on t.id = stq.id_target
			join jaga.stranas_ukuran_keberhasilan uk on t.id_ukuran_keberhasilan = uk.id
			join jaga.stranas_subaksi sa on uk.id_subaksi = sa.id
			join jaga.stranas_aksi aksi on sa.id_aksi = aksi.id
			join jaga.stranas_fokus f on aksi.id_fokus = f.id
			order by s.id_instansi_satker, t.id, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
	) AS summary
group by id, nama
order by id
        ", []);

        return new CollectionPaginatedResponse($result);
    }

    public function fokus()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'id_fokus' => ['validators' => ['required', 'digit']]
            ]
        );


        $result = $this->rawQuery("
select summary.id, summary.nama, sum(nilai) as total_nilai, sum(nilai_verification) as total_nilai_verification, count(*), sum(nilai)::decimal/count(*) as presentase_nilai, sum(nilai_verification)::decimal/count(*) as presentase_nilai_verification
from
	( SELECT distinct on (s.id_instansi_satker, t.id) aksi.id as id, aksi.nama as nama, coalesce(a.nilai, 0) as nilai, coalesce(a.nilai_verification,0) as nilai_verification
			FROM jaga. stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
			join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
			left outer join jaga.stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
			join jaga.stranas_target t on t.id = stq.id_target
			join jaga.stranas_ukuran_keberhasilan uk on t.id_ukuran_keberhasilan = uk.id
			join jaga.stranas_subaksi sa on uk.id_subaksi = sa.id
            join jaga.stranas_aksi aksi on sa.id_aksi = aksi.id
            where aksi.id_fokus=:id_fokus
			order by s.id_instansi_satker, t.id, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
	) AS summary
group by id, nama
order by id
        ", [
            'id_fokus' => $params['id_fokus']
        ]);

        return new CollectionPaginatedResponse($result);
    }

    public function aksi()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'id_aksi' => ['validators' => ['required', 'digit']]
            ]
        );


        $result = $this->rawQuery("
select summary.id, summary.nama, sum(nilai) as total_nilai, sum(nilai_verification) as total_nilai_verification, count(*), sum(nilai)::decimal/count(*) as presentase_nilai, sum(nilai_verification)::decimal/count(*) as presentase_nilai_verification
from
	( SELECT distinct on (s.id_instansi_satker, t.id) sa.id as id, sa.nama as nama, coalesce(a.nilai, 0) as nilai, coalesce(a.nilai_verification,0) as nilai_verification
			FROM jaga. stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
			join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
			left outer join jaga.stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
			join jaga.stranas_target t on t.id = stq.id_target
			join jaga.stranas_ukuran_keberhasilan uk on t.id_ukuran_keberhasilan = uk.id
			join jaga.stranas_subaksi sa on uk.id_subaksi = sa.id
			where sa.id_aksi= :id_aksi
			order by s.id_instansi_satker, t.id, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
	) AS summary
group by id, nama
order by id
        ", [
            'id_aksi' => $params['id_aksi']
        ]);

        return new CollectionPaginatedResponse($result);
    }

    public function subaksi()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'id_subaksi' => ['validators' => ['required', 'digit']]
            ]
        );


        $result = $this->rawQuery("
select summary.id, summary.nama, sum(nilai) as total_nilai, sum(nilai_verification) as total_nilai_verification, count(*), sum(nilai)::decimal/count(*) as presentase_nilai, sum(nilai_verification)::decimal/count(*) as presentase_nilai_verification
from
	( SELECT distinct on (s.id_instansi_satker, t.id) uk.id as id, uk.nama as nama, coalesce(a.nilai, 0) as nilai, coalesce(a.nilai_verification,0) as nilai_verification
			FROM jaga. stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
			join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
			left outer join jaga.stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
			join jaga.stranas_target t on t.id = stq.id_target
			join jaga.stranas_ukuran_keberhasilan uk on t.id_ukuran_keberhasilan = uk.id
			where uk.id_subaksi= :id_subaksi
			order by s.id_instansi_satker, t.id, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
	) AS summary
group by id, nama
order by id
        ", [
            'id_subaksi' => $params['id_subaksi']
        ]);

        return new CollectionPaginatedResponse($result);
    }

    public function ukuran_keberhasilan()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'id_ukuran_keberhasilan' => ['validators' => ['required', 'digit']]
            ]
        );


        $result = $this->rawQuery("
select summary.id, summary.nama, sum(nilai) as total_nilai, sum(nilai_verification) as total_nilai_verification, count(*), sum(nilai)::decimal/count(*) as presentase_nilai, sum(nilai_verification)::decimal/count(*) as presentase_nilai_verification
from
	( SELECT distinct on (s.id_instansi_satker, t.id) t.id as id, t.keterangan as nama, coalesce(a.nilai, 0) as nilai, coalesce(a.nilai_verification,0) as nilai_verification
			FROM jaga. stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
			join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
			left outer join jaga.stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
			join jaga.stranas_target t on t.id = stq.id_target
			where t.id_ukuran_keberhasilan= :id_ukuran_keberhasilan
			order by s.id_instansi_satker, t.id, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
	) AS summary
group by id, nama
order by id
        ", [
            'id_ukuran_keberhasilan' => $params['id_ukuran_keberhasilan']
        ]);

        return new CollectionPaginatedResponse($result);
    }
*/
    public function target()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'id_target' => ['validators' => ['required', 'digit']]
            ]
        );


        $result = $this->rawQuery("
SELECT distinct on (s.id_instansi_satker, stq.id_target) i.id as id, i.nama as nama, a.nilai, a.nilai_verification
FROM jaga. stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
join jaga.instansi i on s.id_instansi_satker = i.id
left outer join jaga.stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
where stq.id_target= :id_target
order by s.id_instansi_satker, stq.id_target, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
        ", [
            'id_target' => $params['id_target']
        ]);

        return new CollectionPaginatedResponse($result);
    }

    public function target_instansi()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'id_target' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['required', 'digit']]
            ]
        );


        $result = $this->rawQuery("
SELECT i.id as id_instansi, i.nama as nama_instansi, p.id as id_periode, p.nama as nama_periode, p.started_at as periode_start, p.ended_at as periode_end, a.nilai, a.nilai_verification
FROM jaga.stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
join jaga.stranas_periode p on s.id_periode = p.id
join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
join jaga.instansi i on s.id_instansi_satker = i.id
left outer join jaga.stranas_survey_answer a on s.id = a.id_survey and stq.id_target = a.id_question
where stq.id_target=:id_target AND s.id_instansi_satker=:id_instansi
order by s.id_periode
        ", [
            'id_target' => $params['id_target'],
            'id_instansi' => $params['id_instansi']
        ]);

        return new CollectionPaginatedResponse($result);
    }

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

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'percent_completion';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';
        $getParams['id_periode'] = array_key_exists('id_periode', $getParams) ? $getParams['id_periode'] : -1;
        $getParams['id_tipe_instansi'] = array_key_exists('id_tipe_instansi', $getParams) ? $getParams['id_tipe_instansi'] : -1;

        $allowed_order = ['percent_completion', 'percent_verification', 'nama', 'total_question', 'total_answered_question'];
        $allowed_order_direction = ['asc', 'desc'];

        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]],
                'id_periode' => ['validators' => ['digit']],
                'id_tipe_instansi' => ['validators' => ['digit']]
            ]
        );

        $order_direction = $params['order_direction'];
        $order_column = $params['order_by'];

        $periode_operator = (int) $params['id_periode'] < 0 ? '<>' : '=';
        $tipe_instansi_operator = (int) $params['id_tipe_instansi'] < 0 ? '<>' : '=';

        $result = $this->rawQuery("
        select q.*,
            round((q.total_verified_question * 100.0) / NULLIF(q.total_answered_question,0), 2) as percent_verification,
            round((q.total_answered_question * 100.0) / NULLIF(q.total_question,0), 2) as percent_completion
            from (
                    select i.id,
                            i.nama,
                            (
                                select count(*)
                                from jaga.stranas_survey
                                where id_instansi_satker = i.id AND id_periode $periode_operator :id_periode
                            ) total_survey,
                            (
                                select count(*)
                                from jaga.stranas_survey ss
                                    join jaga.stranas_periode p on ss.id_periode = p.id
                                    join jaga.stranas_survey_template t on ss.id_survey_template = t.id
                                    join jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
                                where id_instansi_satker = i.id AND ss.id_periode $periode_operator :id_periode
                            ) total_question,
                            (
                                select count(*)
                                from jaga.stranas_survey ss
                                    join jaga.stranas_periode p on ss.id_periode = p.id
                                    join jaga.stranas_survey_template t on ss.id_survey_template = t.id
                                    join jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
                                    join jaga.stranas_survey_answer sa on ss.id = sa.id_survey AND sa.id_question = tq.id_target
                                where id_instansi_satker = i.id AND ss.id_periode $periode_operator :id_periode
                            ) total_answered_question,
                            (
                                select count(*)
                                from jaga.stranas_survey ss
                                    join jaga.stranas_periode p on ss.id_periode = p.id
                                    join jaga.stranas_survey_template t on ss.id_survey_template = t.id
                                    join jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
                                    join jaga.stranas_survey_answer sa on ss.id = sa.id_survey AND sa.id_question = tq.id_target
                                where id_instansi_satker = i.id AND sa.status = :status_verified AND ss.id_periode $periode_operator :id_periode
                            ) total_verified_question
                        from jaga.instansi i
                        where i.nama ilike :keyword AND i.is_stranas = 1 AND i.id_tipe $tipe_instansi_operator :id_tipe_instansi
            ) q
            order by $order_column $order_direction NULLS LAST
            limit :limit
            offset :offset;
        ", [
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'keyword' => '%' . $params['keyword'] . '%',
            'status_verified' => StranasSurveyAnswer::STATUS_VERIFIED,
            'id_periode' => $params['id_periode'],
            'id_tipe_instansi' => $params['id_tipe_instansi']
        ]);

        $total_record = Instansi::count([
            "nama ilike :keyword: AND is_stranas = 1 AND id_tipe $tipe_instansi_operator :id_tipe_instansi:",
            "bind" => [
                "keyword" => '%' . $params['keyword'] . '%',
                'id_tipe_instansi' => $params['id_tipe_instansi']
            ]
        ]);

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


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

        $query = "
            SELECT
                distinct on (s.id_instansi_satker, stq.id_target)
                i.id as id_instansi,
                i.nama as instansi,
                p.id as id_periode,
                p.nama as periode,
                f.id as id_fokus,
                f.nama as fokus,
                aksi.id as id_aksi,
                aksi.nama as aksi,
                sa.id as id_subaksi,
                sa.nama as subaksi,
                uk.id as id_uk,
                uk.nama as ukuran_keberhasilan,
                t.id as id_target,
                t.keterangan as target,
                coalesce(a.nilai, 0) as nilai,
                coalesce(a.nilai_verification,0) as nilai_verification,
                bobot_instansi.presentase as bobot_instansi,
                bobot_target.presentase as bobot_target,
                bobot_uk.presentase as bobot_ukuran_keberhasilan,
                sa.presentase_bobot as bobot_subaksi,
                aksi.presentase_bobot as bobot_aksi,
                f.presentase_bobot as bobot_fokus
            FROM
                jaga.stranas_survey s join jaga.stranas_survey_template st on s.id_survey_template = st.id
                join jaga.stranas_survey_template_question stq on st.id = stq.id_survey_template
                join jaga.instansi i on s.id_instansi_satker = i.id
                inner join stranas_target t on t.id = stq.id_target
                join stranas_ukuran_keberhasilan as uk on t.id_ukuran_keberhasilan = uk.id
                join stranas_subaksi sa on uk.id_subaksi = sa.id
                join stranas_aksi aksi on sa.id_aksi = aksi.id
                join stranas_fokus f on aksi.id_fokus = f.id
                join stranas_periode p on s.id_periode = p.id
                join (
                    SELECT
                        tq.id_target as id,
                        100 :: decimal / count(distinct s.id_instansi_satker) as presentase
                    FROM
                        jaga.stranas_survey s
                        JOIN jaga.stranas_survey_template t on s.id_survey_template = t.id
                        JOIN jaga.stranas_survey_template_question tq on t.id = tq.id_survey_template
                    GROUP BY
                        tq.id_target
                    ORDER BY id
                ) as bobot_instansi on t.id = bobot_instansi.id
                join (
                    SELECT
                        id_ukuran_keberhasilan,
                        100 :: decimal / count (*) as presentase
                    FROM jaga.stranas_target
                    group by id_ukuran_keberhasilan
                    order by id_ukuran_keberhasilan
                ) as bobot_target on t.id_ukuran_keberhasilan = bobot_target.id_ukuran_keberhasilan
                join (
                    SELECT
                        id_subaksi,
                        100 :: decimal / count(*) as presentase
                    FROM jaga.stranas_ukuran_keberhasilan
                    group by id_subaksi
                    order by id_subaksi
                ) as bobot_uk on uk.id_subaksi = bobot_uk.id_subaksi
                left outer join jaga.stranas_survey_answer a
                    on s.id = a.id_survey and stq.id_target = a.id_question
            order by s.id_instansi_satker, stq.id_target, a.nilai_verification DESC NULLS LAST, a.nilai DESC NULLS LAST
        ";

        $result = $this->rawQuery($query, []);

        $header = [
            "id_instansi",
            "instansi",
            "id_periode",
            "periode",
            "id_fokus",
            "fokus",
            "id_aksi",
            "aksi",
            "id_subaksi",
            "subaksi",
            "id_uk",
            "ukuran_keberhasilan",
            "id_target",
            "target",
            "nilai",
            "nilai_verification",
            "bobot_instansi",
            "bobot_target",
            "bobot_ukuran_keberhasilan",
            "bobot_subaksi",
            "bobot_aksi",
            "bobot_fokus"
        ];
        $rows = [];
        $rows[] = $header;
        foreach ($result as $index => $data) {
            $rows[] = [
                $data['id_instansi'],
                $data['instansi'],
                $data['id_periode'],
                $data['periode'],
                $data['id_fokus'],
                $data['fokus'],
                $data['id_aksi'],
                $data['aksi'],
                $data['id_subaksi'],
                $data['subaksi'],
                $data['id_uk'],
                $data['ukuran_keberhasilan'],
                $data['id_target'],
                $data['target'],
                $data['nilai'],
                $data['nilai_verification'],
                $data['bobot_instansi'],
                $data['bobot_target'],
                $data['bobot_ukuran_keberhasilan'],
                $data['bobot_subaksi'],
                $data['bobot_aksi'],
                $data['bobot_fokus'],
            ];
        }
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray($rows, NULL, 'A1');

        $writer = new Xlsx($spreadsheet);
        $filename = "export_target_stranas_" . date('Ymd') . ".xls";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $writer->save('php://output');
    }
}