<?php

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

class StranasNewReportController extends BaseController
{
    public function index()
    {
        $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'] = array_key_exists('order', $getParams) ? $getParams['order'] : '';

        $withcomment = (isset($_GET['withcomment']) && $_GET['withcomment']==1) ? 1 : 0;

        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'order' => ['validators' => []]
            ]
        );

        $id_periode = $getParams['id_periode'] ?? null;
        $download = $getParams['download'] ?? null;
        if ($id_periode === '1' && $download === '1'){
            $this->downloadReport2019();
        }
        // $kol = ($withcomment == 1)?',cc.cmt1, cc.ver1, cc.cmt2, cc.ver2, cc.cmt3, cc.ver3, cc.cmt4, cc.ver4, cc.cmt5, cc.ver5, cc.cmt6, cc.ver6, cc.cmt7, cc.ver7, cc.cmt8, cc.ver8':'';
        // $join = ($withcomment == 1)?' left join jaga.log_comment cc on cc.id_master = a.id':'';

        // $kol = '';
        // $join = '';

        $tkom = "
            t0 as 
            (
                select 
                    a.id_master, a.id_triwulan, a.keterangan, keterangan_verification,
                    u.nama as verified_by, a.verified_at, u1.nama as created_by, a.created_at::timestamp(0),
                    a.nilai 
                from jaga.str_target_log a 
                    left join jaga.str_target_master b on b.id=a.id_master 
                    left join um_user u on u.um_id = a.verified_by
                    left join um_user u1 on u1.um_id = a.created_by
                where b.id_periode = 2
            ),
            tkom as (
                select id_master, 
                --1
                max(case when id_triwulan = 1 then keterangan else '' end) as cmt1,
                max(case when id_triwulan = 1 then keterangan_verification else '' end) as ver1,
                max(case when id_triwulan = 1 then verified_by else '' end) as verifikator_1,
                max(case when id_triwulan = 1 then verified_at else null end) as tglverifikator_1,
                max(case when id_triwulan = 1 then created_by else '' end) as klaimer_1,
                max(case when id_triwulan = 1 and nilai is not null then created_at else null end) as tglklaim_1,
                --2
                max(case when id_triwulan = 2 then keterangan else '' end) as cmt2,
                max(case when id_triwulan = 2 then keterangan_verification else '' end) as ver2,
                max(case when id_triwulan = 2 then verified_by else '' end) as verifikator_2,
                max(case when id_triwulan = 2 then verified_at else null end) as tglverifikator_2,
                max(case when id_triwulan = 2 then created_by else '' end) as klaimer_2,
                max(case when id_triwulan = 2 and nilai is not null then created_at else null end) as tglklaim_2,
                --3
                max(case when id_triwulan = 3 then keterangan else '' end) as cmt3,
                max(case when id_triwulan = 3 then keterangan_verification else '' end) as ver3,
                max(case when id_triwulan = 3 then verified_by else '' end) as verifikator_3,
                max(case when id_triwulan = 3 then verified_at else null end) as tglverifikator_3,
                max(case when id_triwulan = 3 then created_by else '' end) as klaimer_3,
                max(case when id_triwulan = 3 and nilai is not null then created_at else null end) as tglklaim_3,
                --4
                max(case when id_triwulan = 4 then keterangan else '' end) as cmt4,
                max(case when id_triwulan = 4 then keterangan_verification else '' end) as ver4,
                max(case when id_triwulan = 4 then verified_by else '' end) as verifikator_4,
                max(case when id_triwulan = 4 then verified_at else null end) as tglverifikator_4,
                max(case when id_triwulan = 4 then created_by else '' end) as klaimer_4,
                max(case when id_triwulan = 4 and nilai is not null then created_at else null end) as tglklaim_4,
                --5
                max(case when id_triwulan = 5 then keterangan else '' end) as cmt5,
                max(case when id_triwulan = 5 then keterangan_verification else '' end) as ver5,
                max(case when id_triwulan = 5 then verified_by else '' end) as verifikator_5,
                max(case when id_triwulan = 5 then verified_at else null end) as tglverifikator_5,
                max(case when id_triwulan = 5 then created_by else '' end) as klaimer_5,
                max(case when id_triwulan = 5 and nilai is not null then created_at else null end) as tglklaim_5,
                --6
                max(case when id_triwulan = 6 then keterangan else '' end) as cmt6,
                max(case when id_triwulan = 6 then keterangan_verification else '' end) as ver6,
                max(case when id_triwulan = 6 then verified_by else '' end) as verifikator_6,
                max(case when id_triwulan = 6 then verified_at else null end) as tglverifikator_6,
                max(case when id_triwulan = 6 then created_by else '' end) as klaimer_6,
                max(case when id_triwulan = 6 and nilai is not null then created_at else null end) as tglklaim_6,
                --7
                max(case when id_triwulan = 7 then keterangan else '' end) as cmt7,
                max(case when id_triwulan = 7 then keterangan_verification else '' end) as ver7,
                max(case when id_triwulan = 7 then verified_by else '' end) as verifikator_7,
                max(case when id_triwulan = 7 then verified_at else null end) as tglverifikator_7,
                max(case when id_triwulan = 7 then created_by else '' end) as klaimer_7,
                max(case when id_triwulan = 7 and nilai is not null then created_at else null end) as tglklaim_7,
                --8
                max(case when id_triwulan = 8 then keterangan else '' end) as cmt8,
                max(case when id_triwulan = 8 then keterangan_verification else '' end) as ver8,
                max(case when id_triwulan = 8 then verified_by else '' end) as verifikator_8,
                max(case when id_triwulan = 8 then verified_at else null end) as tglverifikator_8,
                max(case when id_triwulan = 8 then created_by else '' end) as klaimer_8,
                max(case when id_triwulan = 8 and nilai is not null then created_at else null end) as tglklaim_8
                
                from t0
                group by id_master
            ),
            t_activities as (
                select c.id_master, string_agg(c.name, '\n' order by id) AS key_activities
                    from jaga.str_target_activities c
                    group by c.id_master
            ),
            t_guides as (
                select g.id_target,
                string_agg(
                    CONCAT (g.ord, '. ', g.aspek, '<br/>Bobot: ', g.bobot, 
                        (case when g.catatan is not null then '%<br/>Catatan:<br/>' else '' end), g.catatan),
                        '<br/>' order by id, ord)
                    AS aspek_penilaian
                    from jaga.str_target_guide g
                    group by g.id_target
            ),
        ";

        // tkom as (select id_master, keterangan, keterangan_verification, row_number() over (partition by id_master order by id_triwulan desc) as ord from jaga.str_target_log),
        
        $isdownload = (isset($_GET['download']) && $_GET['download']==1) ? 1 : 0;

        $table = "with ". 
                ($isdownload ? $tkom : '')
                ."tres as (
                select 
                    a.id,
                    l1.nama as level1,
                    l2.nama as level2,
                    l3.nama as level3,
                    l4.nama as level4,
                    i.nama as instansi,
                    t.nama as target,
                    t.info_lampiran,
                    t.is_forced_complete,
                    nilai_tw1::INTEGER,nilai_tw2::INTEGER,nilai_tw3::INTEGER,nilai_tw4::INTEGER,
                    nilai_tw5::INTEGER,nilai_tw6::INTEGER,nilai_tw7::INTEGER,nilai_tw8::INTEGER,
                    klaim_tw1,klaim_tw2,klaim_tw3,klaim_tw4,klaim_tw5,klaim_tw6,klaim_tw7,klaim_tw8,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2+nilai_tw3+nilai_tw4+nilai_tw5+nilai_tw6+nilai_tw7+nilai_tw8
                    else 
                        latest_verification(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1)
                    end as nilai_verifikasi,
                    nilai_tw1 as max_tw1,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2
                    else 
                        greatest(nilai_tw1,nilai_tw2) 
                    end as max_tw2,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2+nilai_tw3
                    else 
                        greatest(nilai_tw1,nilai_tw2,nilai_tw3) 
                    end as max_tw3,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2+nilai_tw3+nilai_tw4
                    else
                        greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4) 
                    end as max_tw4,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2+nilai_tw3+nilai_tw4+nilai_tw5
                    else
                        greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5) 
                    end as max_tw5,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2+nilai_tw3+nilai_tw4+nilai_tw5+nilai_tw6
                    else
                        greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6) 
                    end as max_tw6,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2+nilai_tw3+nilai_tw4+nilai_tw5+nilai_tw6+nilai_tw7
                    else
                        greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7) 
                    end as max_tw7,
                    case when t.is_special > 1 then 
                        nilai_tw1+nilai_tw2+nilai_tw3+nilai_tw4+nilai_tw5+nilai_tw6+nilai_tw7+nilai_tw8
                    else
                        greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) 
                    end as max_tw8,
                    a.id_instansi, a.id_target,a.id_periode,a.id_level1,a.id_level2,a.id_level3,a.id_level4,
                    klaim_tw1 as is_laportw1, klaim_tw2 as is_laportw2, klaim_tw3 as is_laportw3, klaim_tw4 as is_laportw4, klaim_tw5 as is_laportw5, klaim_tw6 as is_laportw6, klaim_tw7 as is_laportw7, klaim_tw8 as is_laportw8, 
                    nilai_tw1 as is_veriftw1, nilai_tw2 as is_veriftw2, nilai_tw3 as is_veriftw3, nilai_tw4 as is_veriftw4, nilai_tw5 as is_veriftw5, nilai_tw6 as is_veriftw6, nilai_tw7 as is_veriftw7, nilai_tw8 as is_veriftw8,
                    case when jaga.latest_verification(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1) = 100 then 1 else 0 end as is_completed,
                    l1.ord as o1,
                    l2.ord as o2,
                    l3.ord as o3,
                    l4.ord as o4, 
                    t.ord as oindikator,
                    t.is_special
                from jaga.str_target_master a 
                    join jaga.instansi i on i.id=a.id_instansi
                    join jaga.str_target t on t.id=a.id_target and t.deleted_at is null 
                    join jaga.str_hierarchy l1 on l1.id = t.id_level1 
                    left join jaga.str_hierarchy l2 on l2.id = t.id_level2
                    left join jaga.str_hierarchy l3 on l3.id = t.id_level3
                    left join jaga.str_hierarchy l4 on l4.id = t.id_level4
                where a.deleted_at is null 
                )
            ";
        // echo $table; die;
        // 2
        $where = " where 1=1 ";
        $periode = $this->rawQuery("select * from jaga.str_periode where is_active=true"); 
        $prd   = (isset($_GET['id_periode']) && $_GET['id_periode'] != '') ? $_GET['id_periode'] : $periode[0]['id'];

        $where .= ' and id_periode = '.$prd;
        $where .= (isset($_GET['id_instansi']) && $_GET['id_instansi'] != '') ? ' and id_instansi = '.$_GET['id_instansi'] : '';
        $where .= (isset($_GET['id_level1']) && $_GET['id_level1'] != '') ? ' and id_level1 = '.$_GET['id_level1'] : '';
        $where .= (isset($_GET['id_level2']) && $_GET['id_level2'] != '') ? ' and id_level2 = '.$_GET['id_level2'] : '';
        $where .= (isset($_GET['id_level3']) && $_GET['id_level3'] != '') ? ' and id_level3 = '.$_GET['id_level3'] : '';
        $where .= (isset($_GET['id_level4']) && $_GET['id_level4'] != '') ? ' and id_level4 = '.$_GET['id_level4'] : '';
        $where .= (isset($_GET['id_master']) && $_GET['id_master'] != '') ? ' and id = '.$_GET['id_master'] : '';
        // $where .= (isset($_GET['target']) && $_GET['target'] != '') ? ' and target = '.$_GET['target'] : '';
        $where .= (isset($_GET['keyword']) && $_GET['keyword'] != '') ? " and target ilike '%".$_GET['keyword']."%'" : '';

        // 3
        $order   = (isset($_GET['order']) && $_GET['order'] != '') ? ' order by '.$_GET['order'] : '';
        $offset  = ' offset '.$params['offset'];
        $limit   = ' limit '.$params['limit'];

        // echo $table.' 
        // select a.*, 
        // b.cmt1, b.ver1, b.verifikator_1, b.tglverifikator_1, nilai_tw1,
        // b.cmt2, b.ver2, b.verifikator_2, b.tglverifikator_2, nilai_tw2,
        // b.cmt3, b.ver3, b.verifikator_3, b.tglverifikator_3, nilai_tw3,
        // b.cmt4, b.ver4, b.verifikator_4, b.tglverifikator_4, nilai_tw4,
        // b.cmt5, b.ver5, b.verifikator_5, b.tglverifikator_5, nilai_tw5,
        // b.cmt6, b.ver6, b.verifikator_6, b.tglverifikator_6, nilai_tw6,
        // b.cmt7, b.ver7, b.verifikator_7, b.tglverifikator_7, nilai_tw7,
        // b.cmt8, b.ver8, b.verifikator_8, b.tglverifikator_8, nilai_tw8
        // from tres a 
        //     left join (select * from tkom) b on b.id_master = a.id '. $where; die;
        if($isdownload) {
            $tempQuery = $table.' 
            select a.*, 
            b.cmt1, b.ver1, b.verifikator_1, b.tglverifikator_1,
            b.cmt2, b.ver2, b.verifikator_2, b.tglverifikator_2,
            b.cmt3, b.ver3, b.verifikator_3, b.tglverifikator_3,
            b.cmt4, b.ver4, b.verifikator_4, b.tglverifikator_4,
            b.cmt5, b.ver5, b.verifikator_5, b.tglverifikator_5,
            b.cmt6, b.ver6, b.verifikator_6, b.tglverifikator_6,
            b.cmt7, b.ver7, b.verifikator_7, b.tglverifikator_7,
            b.cmt8, b.ver8, b.verifikator_8, b.tglverifikator_8,
            b.klaimer_1, b.tglklaim_1,
            b.klaimer_2, b.tglklaim_2,
            b.klaimer_3, b.tglklaim_3,
            b.klaimer_4, b.tglklaim_4,
            b.klaimer_5, b.tglklaim_5,
            b.klaimer_6, b.tglklaim_6,
            b.klaimer_7, b.tglklaim_7,
            b.klaimer_8, b.tglklaim_8,
            c.key_activities,
            g.aspek_penilaian
            from tres a 
                left join (select * from tkom) b on b.id_master = a.id 
                left join (select * from t_activities) c on c.id_master = a.id
                left join (select * from t_guides) g on g.id_target = a.id_target'. $where;
            // echo $tempQuery; die;
            $result  = $this->rawQuery($tempQuery
                , []
                );
                
            $header = ($withcomment == 1) ?
                        ['id',
                            'o1','o2','o3','o4','oindikator','is_special',
                            'nilai_tw1',        
                            'nilai_tw2',        
                            'nilai_tw3',        
                            'nilai_tw4',        
                            'nilai_tw5',        
                            'nilai_tw6',        
                            'nilai_tw7',        
                            'nilai_tw8',        
                            'nilai_verifikasi','id_instansi','id_target','id_periode','id_level1','id_level2','id_level3','id_level4','level1','level2','level3','level4','instansi','target', 'aspek_penilaian', 'info_lampiran',
                            'cmt1','ver1','verby_1','verat_1',
                            'cmt2','ver2','verby_2','verat_2',
                            'cmt3','ver3','verby_3','verat_3',
                            'cmt4','ver4','verby_4','verat_4',
                            'cmt5','ver5','verby_5','verat_5',
                            'cmt6','ver6','verby_6','verat_6',
                            'cmt7','ver7','verby_7','verat_7',
                            'cmt8','ver8','verby_8','verat_8',
                            'klaim_by1', 'klaim_at1', 'klaim_tw1',
                            'klaim_by2', 'klaim_at2', 'klaim_tw2',
                            'klaim_by3', 'klaim_at3', 'klaim_tw3',
                            'klaim_by4', 'klaim_at4', 'klaim_tw4',
                            'klaim_by5', 'klaim_at5', 'klaim_tw5',
                            'klaim_by6', 'klaim_at6', 'klaim_tw6',
                            'klaim_by7', 'klaim_at7', 'klaim_tw7',
                            'klaim_by8', 'klaim_at8', 'klaim_tw8',
                            'is_completed',
                            'key_activities'
                        ]
                        :
                        ['id','nilai_verifikasi','id_instansi','id_target','id_periode','id_level1','id_level2','id_level3','id_level4','level1','level2','level3','level4','instansi','target', 'aspek_penilaian', 'info_lampiran'];

            $rows = [];
            $rows[] = $header;
            if ($withcomment == 1) {
                foreach ($result as $index => $data) {
                    $rows[] = [
                        $data['id'],
                        $data['o1'],$data['o2'],$data['o3'],$data['o4'],$data['oindikator'],$data['is_special'],
                        strval($data['nilai_tw1']),
                        strval($data['nilai_tw2']),
                        strval($data['nilai_tw3']),
                        strval($data['nilai_tw4']),
                        strval($data['nilai_tw5']),
                        strval($data['nilai_tw6']),
                        strval($data['nilai_tw7']),
                        strval($data['nilai_tw8']),
                        $data['nilai_verifikasi'],
                        $data['id_instansi'],
                        $data['id_target'],
                        $data['id_periode'],
                        $data['id_level1'],
                        $data['id_level2'],
                        $data['id_level3'],
                        $data['id_level4'],
                        $data['level1'],
                        $data['level2'],
                        $data['level3'],
                        $data['level4'],
                        $data['instansi'],
                        $data['target'],
                        $this->html2Text($data['aspek_penilaian']),
                        $this->html2Text($data['info_lampiran']),
                        $this->html2Text($data['cmt1']),
                        $this->html2Text($data['ver1']),
                        $data['verifikator_1'],$data['tglverifikator_1'],
                        $this->html2Text($data['cmt2']),
                        $this->html2Text($data['ver2']),
                        $data['verifikator_2'],$data['tglverifikator_2'],
                        $this->html2Text($data['cmt3']),
                        $this->html2Text($data['ver3']),
                        $data['verifikator_3'],$data['tglverifikator_3'],
                        $this->html2Text($data['cmt4']),
                        $this->html2Text($data['ver4']),
                        $data['verifikator_4'],$data['tglverifikator_4'],
                        $this->html2Text($data['cmt5']),
                        $this->html2Text($data['ver5']),
                        $data['verifikator_5'],$data['tglverifikator_5'],
                        $this->html2Text($data['cmt6']),
                        $this->html2Text($data['ver6']),
                        $data['verifikator_6'],$data['tglverifikator_6'],
                        $this->html2Text($data['cmt7']),
                        $this->html2Text($data['ver7']),
                        $data['verifikator_7'],$data['tglverifikator_7'],
                        $this->html2Text($data['cmt8']),
                        $this->html2Text($data['ver8']),
                        $data['verifikator_8'],$data['tglverifikator_8'],
                        $data['klaimer_1'] ?? '',
                        $data['tglklaim_1'] ?? '',
                        strval($data['klaim_tw1']),
                        $data['klaimer_2'] ?? '',
                        $data['tglklaim_2'] ?? '',
                        strval($data['klaim_tw2']),
                        $data['klaimer_3'] ?? '',
                        $data['tglklaim_3'] ?? '',
                        strval($data['klaim_tw3']),
                        $data['klaimer_4'] ?? '',
                        $data['tglklaim_4'] ?? '',
                        strval($data['klaim_tw4']),
                        $data['klaimer_5'] ?? '',
                        $data['tglklaim_5'] ?? '',
                        strval($data['klaim_tw5']),
                        $data['klaimer_6'] ?? '',
                        $data['tglklaim_6'] ?? '',
                        strval($data['klaim_tw6']),
                        $data['klaimer_7'] ?? '',
                        $data['tglklaim_7'] ?? '',
                        strval($data['klaim_tw7']),
                        $data['klaimer_8'] ?? '',
                        $data['tglklaim_8'] ?? '',
                        strval($data['klaim_tw8']),
                        $data['is_forced_complete'] || intval($data['nilai_verifikasi']) === 100 ? '1' : '',
                        $data['key_activities'] ?? ''
                    ];
                } 
            } else {
                foreach ($result as $index => $data) {
                    $rows[] = [
                        $data['id'],
                        $data['o1'],$data['o2'],$data['o3'],$data['o4'],$data['oindikator'],$data['is_special'],
                        strval($data['nilai_tw1']),
                        strval($data['nilai_tw2']),
                        strval($data['nilai_tw3']),
                        strval($data['nilai_tw4']),
                        strval($data['nilai_tw5']),
                        strval($data['nilai_tw6']),
                        strval($data['nilai_tw7']),
                        strval($data['nilai_tw8']),
                        $data['nilai_verifikasi'],
                        $data['id_instansi'],
                        $data['id_target'],
                        $data['id_periode'],
                        $data['id_level1'],
                        $data['id_level2'],
                        $data['id_level3'],
                        $data['id_level4'],
                        $data['level1'],
                        $data['level2'],
                        $data['level3'],
                        $data['level4'],
                        $data['instansi'],
                        $data['target'],
                        $this->html2Text($data['aspek_penilaian']),
                        $this->html2Text($data['info_lampiran']),
                    ];
                }
            }

            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->fromArray($rows, NULL, 'A1');

            $writer = new Xlsx($spreadsheet);

            // keterangan waktu untuk filename
            $datetime = $this->rawQuery('select now()');
            $now = date_parse($datetime[0]['now']);
            $year = $now['year'];
            $month = str_pad($now['month'], 2, '0', STR_PAD_LEFT);
            $day = str_pad($now['day'], 2, '0', STR_PAD_LEFT);
            $hour = str_pad($now['hour'], 2, '0', STR_PAD_LEFT);
            $minute = str_pad($now['minute'], 2, '0', STR_PAD_LEFT);

            $filename = "export_target_stranas_$year$month$day"."_"."$hour$minute.xls";
            header('Content-type: application/vnd.ms-excel');
            header('content-disposition: attachment; filename="' . $filename . '"');
            $writer->save('php://output');
            die;
        }
        $total = $this->rawQuery($table.'select 
                count(1) as total_record, 
                -- tidak lapor dan tidak komplit [1]
                sum(case when is_laportw1 = 0 and max_tw1 <> 100 then 1 else 0 end) as tw1_notlapor,
                    -- lapor dan tidak komplit [2]
                    sum(case when is_laportw1 > 0 and max_tw1 <> 100 then 1 else 0 end) as tw1_lapor,
                    -- lapor, belum verify dan tidak komplit [3]
                    sum(case when is_laportw1 > 0 and is_veriftw1 = 0 and max_tw1 <> 100 then 1 else 0 end) as tw1_notverify,
                    -- lapor, verify dan tidak komplit [4]
                    sum(case when is_laportw1 > 0 and is_veriftw1  > 0 and max_tw1 <> 100 then 1 else 0 end) as tw1_verify,
                    sum(case when max_tw1 = 100 then 1 else 0 end) as tw1_complete,
                sum(case when is_laportw2 = 0 and max_tw2 <> 100 then 1 else 0 end) as tw2_notlapor,
                    sum(case when is_laportw2 > 0 and max_tw2 <> 100 then 1 else 0 end) as tw2_lapor,
                    sum(case when is_laportw2 > 0 and is_veriftw2 = 0 and max_tw2 <> 100 then 1 else 0 end) as tw2_notverify,
                    sum(case when is_laportw2 > 0 and is_veriftw2  > 0 and max_tw2 <> 100 then 1 else 0 end) as tw2_verify,
                    sum(case when max_tw2 = 100 then 1 else 0 end) as tw2_complete,
                sum(case when is_laportw3 = 0 and max_tw3 <> 100 then 1 else 0 end) as tw3_notlapor,
                    sum(case when is_laportw3 > 0 and max_tw3 <> 100 then 1 else 0 end) as tw3_lapor,
                    sum(case when is_laportw3 > 0 and is_veriftw3 = 0 and max_tw3 <> 100 then 1 else 0 end) as tw3_notverify,
                    sum(case when is_laportw3 > 0 and is_veriftw3  > 0 and max_tw3 <> 100 then 1 else 0 end) as tw3_verify,
                    sum(case when max_tw3 = 100 then 1 else 0 end) as tw3_complete,
                sum(case when is_laportw4 = 0 and max_tw4 <> 100 then 1 else 0 end) as tw4_notlapor,
                    sum(case when is_laportw4 > 0 and max_tw4 <> 100 then 1 else 0 end) as tw4_lapor,
                    sum(case when is_laportw4 > 0 and is_veriftw4 = 0 and max_tw4 <> 100 then 1 else 0 end) as tw4_notverify,
                    sum(case when is_laportw4 > 0 and is_veriftw4  > 0 and max_tw4 <> 100 then 1 else 0 end) as tw4_verify,
                    sum(case when max_tw4 = 100 then 1 else 0 end) as tw4_complete,
                sum(case when is_laportw5 = 0 and max_tw5 <> 100 then 1 else 0 end) as tw5_notlapor,
                    sum(case when is_laportw5 > 0 and max_tw5 <> 100 then 1 else 0 end) as tw5_lapor,
                    sum(case when is_laportw5 > 0 and is_veriftw5 = 0 and max_tw5 <> 100 then 1 else 0 end) as tw5_notverify,
                    sum(case when is_laportw5 > 0 and is_veriftw5  > 0 and max_tw5 <> 100 then 1 else 0 end) as tw5_verify,
                    sum(case when max_tw5 = 100 then 1 else 0 end) as tw5_complete,
                sum(case when is_laportw6 = 0 and max_tw6 <> 100 then 1 else 0 end) as tw6_notlapor,
                    sum(case when is_laportw6 > 0 and max_tw6 <> 100 then 1 else 0 end) as tw6_lapor,
                    sum(case when is_laportw6 > 0 and is_veriftw6 = 0 and max_tw6 <> 100 then 1 else 0 end) as tw6_notverify,
                    sum(case when is_laportw6 > 0 and is_veriftw6  > 0 and max_tw6 <> 100 then 1 else 0 end) as tw6_verify,
                    sum(case when max_tw6 = 100 then 1 else 0 end) as tw6_complete,
                sum(case when is_laportw7 = 0 and max_tw7 <> 100 then 1 else 0 end) as tw7_notlapor,
                    sum(case when is_laportw7 > 0 and max_tw7 <> 100 then 1 else 0 end) as tw7_lapor,
                    sum(case when is_laportw7 > 0 and is_veriftw7 = 0 and max_tw7 <> 100 then 1 else 0 end) as tw7_notverify,
                    sum(case when is_laportw7 > 0 and is_veriftw7  > 0 and max_tw7 <> 100 then 1 else 0 end) as tw7_verify,
                    sum(case when max_tw7 = 100 then 1 else 0 end) as tw7_complete,
                sum(case when is_laportw8 = 0 and max_tw8 <> 100 then 1 else 0 end) as tw8_notlapor,
                    sum(case when is_laportw8 > 0 and max_tw8 <> 100 then 1 else 0 end) as tw8_lapor,
                    sum(case when is_laportw8 > 0 and is_veriftw8 = 0 and max_tw8 <> 100 then 1 else 0 end) as tw8_notverify,
                    sum(case when is_laportw8 > 0 and is_veriftw8  > 0 and max_tw8 <> 100 then 1 else 0 end) as tw8_verify,
                sum(case when max_tw8 = 100 then 1 else 0 end) as tw8_complete
                from tres '.$where);
        // $rows  = $this->rawQuery($table.' select a.*, b.keterangan, b.keterangan_verification, nilai_tw1, nilai_tw2, nilai_tw3, nilai_tw4, nilai_tw5, nilai_tw6, nilai_tw7, nilai_tw8 from tres a left join (select * from tkom where ord=1) b  on b.id_master = a.id '.$where.$order.$limit.$offset);
        // echo $table.' select a.*, nilai_tw1, nilai_tw2, nilai_tw3, nilai_tw4, nilai_tw5, nilai_tw6, nilai_tw7, nilai_tw8 from tres a '.$where.$order.$limit.$offset; die;

        $isshowall = (isset($_GET['show_all']) && $_GET['show_all']==1) ? 1 : 0;
        if ($isshowall) {
            // echo $table.' select a.*, nilai_tw1, nilai_tw2, nilai_tw3, nilai_tw4, nilai_tw5, nilai_tw6, nilai_tw7, nilai_tw8 from tres a '.$where.$order;
            // die;
            $rows  = $this->rawQuery($table.' select a.*, nilai_tw1, nilai_tw2, nilai_tw3, nilai_tw4, nilai_tw5, nilai_tw6, nilai_tw7, nilai_tw8 from tres a '.$where.$order);
            $response = new \Phalcon\Http\Response();
            $response->setContentType('application/json', 'UTF-8');
            $response->setContent(json_encode([
            'success' => true,
            'data' => $rows
            ]));

            return $response;
        }

        $rows  = $this->rawQuery($table.' select a.*, nilai_tw1, nilai_tw2, nilai_tw3, nilai_tw4, nilai_tw5, nilai_tw6, nilai_tw7, nilai_tw8 from tres a '.$where.$order.$limit.$offset);


        $info = [
            'tw1_notlapor' => $total[0]['tw1_notlapor'],'tw1_lapor' => $total[0]['tw1_lapor'],'tw1_notverify' => $total[0]['tw1_notverify'],'tw1_verify' => $total[0]['tw1_verify'],'tw1_complete' => $total[0]['tw1_complete'],
            'tw2_notlapor' => $total[0]['tw2_notlapor'],'tw2_lapor' => $total[0]['tw2_lapor'],'tw2_notverify' => $total[0]['tw2_notverify'],'tw2_verify' => $total[0]['tw2_verify'],'tw2_complete' => $total[0]['tw2_complete'],
            'tw3_notlapor' => $total[0]['tw3_notlapor'],'tw3_lapor' => $total[0]['tw3_lapor'],'tw3_notverify' => $total[0]['tw3_notverify'],'tw3_verify' => $total[0]['tw3_verify'],'tw3_complete' => $total[0]['tw3_complete'],
            'tw4_notlapor' => $total[0]['tw4_notlapor'],'tw4_lapor' => $total[0]['tw4_lapor'],'tw4_notverify' => $total[0]['tw4_notverify'],'tw4_verify' => $total[0]['tw4_verify'],'tw4_complete' => $total[0]['tw4_complete'],
            'tw5_notlapor' => $total[0]['tw5_notlapor'],'tw5_lapor' => $total[0]['tw5_lapor'],'tw5_notverify' => $total[0]['tw5_notverify'],'tw5_verify' => $total[0]['tw5_verify'],'tw5_complete' => $total[0]['tw5_complete'],
            'tw6_notlapor' => $total[0]['tw6_notlapor'],'tw6_lapor' => $total[0]['tw6_lapor'],'tw6_notverify' => $total[0]['tw6_notverify'],'tw6_verify' => $total[0]['tw6_verify'],'tw6_complete' => $total[0]['tw6_complete'],
            'tw7_notlapor' => $total[0]['tw7_notlapor'],'tw7_lapor' => $total[0]['tw7_lapor'],'tw7_notverify' => $total[0]['tw7_notverify'],'tw7_verify' => $total[0]['tw7_verify'],'tw7_complete' => $total[0]['tw7_complete'],
            'tw8_notlapor' => $total[0]['tw8_notlapor'],'tw8_lapor' => $total[0]['tw8_lapor'],'tw8_notverify' => $total[0]['tw8_notverify'],'tw8_verify' => $total[0]['tw8_verify'],'tw8_complete' => $total[0]['tw8_complete'],
        ];
        return new PaginatedResponse($rows, $total[0]['total_record'], $params['limit'], $params['offset'], $info);
    }

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

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

        $idMaster = (isset($_GET['id_master']) && $_GET['id_master'] != '') ? $_GET['id_master'] : null;
        $periode = $idMaster ? 
                    $this->rawQuery("select id_periode as id from jaga.str_target_master where id='$idMaster'") :
                    $this->rawQuery("select * from jaga.str_periode where is_active=true"); 

        $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']) : 'id';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = [
            'id',
            'id_master',
            'level1',
            'level2',
            'level3',
            'level4',
            'instansi',
            'target'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                // 'order' => ['validators' => []],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $where = " 1=1 ";
        $prd   = (isset($_GET['id_periode']) && $_GET['id_periode'] != '') ? $_GET['id_periode'] : $periode[0]['id'];
        $where .= ' and id_periode = '.$prd;

        $where .= (isset($_GET['id_instansi']) && $_GET['id_instansi'] != '') ? ' and id_instansi = '.$_GET['id_instansi'] : '';
        $where .= (isset($_GET['id_target']) && $_GET['id_target'] != '') ? ' and id_target = '.$_GET['id_target'] : '';
        $where .= (isset($_GET['id_level1']) && $_GET['id_level1'] != '') ? ' and id_level1 = '.$_GET['id_level1'] : '';
        $where .= (isset($_GET['id_level2']) && $_GET['id_level2'] != '') ? ' and id_level2 = '.$_GET['id_level2'] : '';
        $where .= (isset($_GET['id_level3']) && $_GET['id_level3'] != '') ? ' and id_level3 = '.$_GET['id_level3'] : '';
        $where .= (isset($_GET['id_level4']) && $_GET['id_level4'] != '') ? ' and id_level4 = '.$_GET['id_level4'] : '';
        $where .= (isset($_GET['id_master']) && $_GET['id_master'] != '') ? ' and id_master = '.$_GET['id_master'] : '';
        // $where .= (isset($_GET['target']) && $_GET['target'] != '') ? " and target ilike '%".$_GET['target']."%'" : '';
        $where .= (isset($_GET['keyword']) && $_GET['keyword'] != '') ? " and (target ilike '%".$_GET['keyword']."%' or instansi ilike '%".$_GET['keyword']."%')"  : '';

        $data = StranasNewReportAch::find([
            'conditions' => $where,
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => "{$params['order_by']} {$params['order_direction']}"
        ]);

        $total = $this->rawQuery('select count(1) as total_record from jaga.report_ach where '.$where);

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

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

        $getParams = $this->request->get();
        $periode = $this->rawQuery("select * from jaga.str_periode where is_active=true"); 

        $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']) : 'id';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = [
            'id_level1',
            'id_instansi'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $params = $this->validate(
            $getParams,
            [
                'tipe' => ['validators' => ['required']],

                'id_level1' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
                'id_tipe' => ['validators' => ['digit']],

                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );
        
        $prd   = (isset($_GET['id_periode']) && $_GET['id_periode'] != '') ? $_GET['id_periode'] : $periode[0]['id'];

        $wherei = ' 1=1 ';
        $wherei .= (isset($_GET['id_instansi']) && $_GET['id_instansi'] != '') ? ' and a.id_instansi = '.$_GET['id_instansi'] : '';
        $wherei .= (isset($_GET['id_tipe']) && $_GET['id_tipe'] != '') ? ' and i.id_tipe = '.$_GET['id_tipe'] : '';

        $instansi = "
            with t1 as (
            select
              a.id_periode, id_instansi,
              count(id_target)*100.0 as total,
              sum(case when klaim_tw1>0 then 1 else 0 end) as islapor_tw1, sum(case when klaim_tw2>0 then 1 else 0 end) as islapor_tw2, sum(case when klaim_tw3>0 then 1 else 0 end) as islapor_tw3, sum(case when klaim_tw4>0 then 1 else 0 end) as islapor_tw4, sum(case when klaim_tw5>0 then 1 else 0 end) as islapor_tw5, sum(case when klaim_tw6>0 then 1 else 0 end) as islapor_tw6, sum(case when klaim_tw7>0 then 1 else 0 end) as islapor_tw7, sum(case when klaim_tw8>0 then 1 else 0 end) as islapor_tw8,
              sum(case when nilai_tw1>0 then 1 else 0 end) as isverify_tw1, sum(case when nilai_tw2>0 then 1 else 0 end) as isverify_tw2, sum(case when nilai_tw3>0 then 1 else 0 end) as isverify_tw3, sum(case when nilai_tw4>0 then 1 else 0 end) as isverify_tw4, sum(case when nilai_tw5>0 then 1 else 0 end) as isverify_tw5, sum(case when nilai_tw6>0 then 1 else 0 end) as isverify_tw6, sum(case when nilai_tw7>0 then 1 else 0 end) as isverify_tw7, sum(case when nilai_tw8>0 then 1 else 0 end) as isverify_tw8,
              sum(nilai_tw1) as nilai_tw1, sum(nilai_tw2) as nilai_tw2, sum(nilai_tw3) as nilai_tw3, sum(nilai_tw4) as nilai_tw4, sum(nilai_tw5) as nilai_tw5, sum(nilai_tw6) as nilai_tw6, sum(nilai_tw7) as nilai_tw7, sum(nilai_tw8) as nilai_tw8,
              sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8)) as nilai_max
            from jaga.str_target_master a
                join jaga.str_target t on t.id=a.id_target and t.deleted_at is null
            where a.deleted_at is null and a.id_periode = ".$prd."
            group by a.id_periode, id_instansi
            ),
            tres as (
            select 
                a.id_periode,
                i.nama as tipe,
                islapor_tw1, islapor_tw2, islapor_tw3, islapor_tw4, islapor_tw5, islapor_tw6, islapor_tw7, islapor_tw8, 
                isverify_tw1, isverify_tw2, isverify_tw3, isverify_tw4, isverify_tw5, isverify_tw6, isverify_tw7, isverify_tw8, 
                nilai_tw1, nilai_tw2, nilai_tw3, nilai_tw4, nilai_tw5, nilai_tw6, nilai_tw7, nilai_tw8, 
                nilai_max,
                total as nilai_total,
                --nilai_tw1/total as pct_tw1, nilai_tw2/total as pct_tw2, nilai_tw3/total as pct_tw3, nilai_tw4/total as pct_tw4, nilai_tw5/total as pct_tw5, nilai_tw6/total as pct_tw6, nilai_tw7/total as pct_tw7, nilai_tw8/total as pct_tw8, pct_total/total as pct_total,
                a.id_instansi,
                i.nama as instansi,
                i.id_tipe as id_tipe_instansi,
                t.nama as tipe_instansi,
                i.id_provinsi,
                mp.provinsi_name  as provinsi,
                i.id_kota_kabupaten,
                mk.kota_kabupaten_name as kota_kabupaten
            from t1 a
                join jaga.instansi i on i.id = a.id_instansi 
                join jaga.instansi_tipe t on t.id=i.id_tipe 
                --join jaga.hierachy_by_level l on l.id_level3=a.id_lowlevel 
                left join jaga.master_provinsi mp on cast(mp.provinsi_code as int) =  i.id_provinsi 
                left join jaga.master_kota_kabupaten mk on cast(mk.kota_kabupaten_code as int) =  i.id_kota_kabupaten 
            where ".$wherei."
            )
        ";


        $id = (isset($_GET['id']) && $_GET['id'] != 0) ? $_GET['id'] : 0;
        $level = (isset($_GET['level']) && $_GET['level'] != 0) ? $_GET['level'] : 1;
        $whereh = ' 1=1 ';
        $whereh .= (($level>1) ? " and l.id_level".($level-1)."=".$id : "");

        $hierarchy = "
        with t1 as (
        select
          a.id_periode, id_instansi,
          t.id_level3 as id_lowlevel,
          count(id_target)*100.0 as total,
          sum(nilai_tw1) as nilai_tw1, sum(nilai_tw2) as nilai_tw2, sum(nilai_tw3) as nilai_tw3, sum(nilai_tw4) as nilai_tw4, sum(nilai_tw5) as nilai_tw5, sum(nilai_tw6) as nilai_tw6, sum(nilai_tw7) as nilai_tw7, sum(nilai_tw8) as nilai_tw8,
          sum(greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8)) as nilai_max
        from jaga.str_target_master a
            join jaga.str_target t on t.id=a.id_target and t.deleted_at is null
        where a.deleted_at is null and a.id_periode = ".$prd."
        group by a.id_periode, id_instansi, t.id_level3
        ),
        tres as (
        select 
            --a.id_periode,
            l.id_level".$level." as id, ".$level." as level,
            l.level".$level." as tipe, 
            sum(nilai_tw1) as nilai_tw1, sum(nilai_tw2) as nilai_tw2, sum(nilai_tw3) as nilai_tw3, sum(nilai_tw4) as nilai_tw4, sum(nilai_tw5) as nilai_tw5, sum(nilai_tw6) as nilai_tw6, sum(nilai_tw7) as nilai_tw7, sum(nilai_tw8) as nilai_tw8,
            sum(nilai_max) as nilai_max,
            sum(total) as nilai_total
        from t1 a
            join jaga.hierachy_by_level l on l.id_level3=a.id_lowlevel
            where ".$whereh."
        group by l.level".$level.", l.id_level".$level."
        )
        ";


        $query = ($params['tipe'] == 'instansi') ? $instansi : $hierarchy;
        // echo $query; die;
        $total = $this->rawQuery($query.' select count(1) as total_record from tres');
        $rows  = $this->rawQuery($query.' select * from tres');

        $response = new \Phalcon\Http\Response();
        $response->setContentType('application/json', 'UTF-8');
        $response->setContent(json_encode([
          'success' => true,
          'data' => [ 'total'=> $total[0]['total_record'], 'result' => $rows ]
        ]));

        return $response;
    }

    // submit
    public function submit() {
        $this->checkScopes(['stranas.answer']);

        $postParams = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $postParams,
            [
                'id_master' => ['validator' => ['required', 'digit']], // id StranasNewMasterTarget
                'id_triwulan' => ['validator' => ['required', 'digit']], // id_triwulan
                'nilai' => ['validator' => ['required', 'digit']],
                'keterangan' => ['validator' => ['required']],
                'attachments' => ['validator' => []],
                'activities' => ['validator' => []],
                'deleted_attachments' => ['validator' => []]
            ]
        );

        // check bener gak target dan instansi
        // $target     = $this->rawQuery('select * from jaga.str_target_master where id='.$params['id_master']);
        $target     = StranasNewMasterTarget::findFirst($params['id_master']);
        if (empty($target)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target instansi tidak ditemukan');
        }
        // cek sudah dihapus
        // if (!empty($target[0]['deleted_at'])) {
        //     throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target instansi sudah tidak aktif');
        // }

        if ($params['id_triwulan']>8) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID Triwulan tidak valid');
        }
        // cek periode pengisian
        $triwulan   = $this->rawQuery('select *, case when now() between started_at and ended_at then 1 else 0 end as is_in_period from jaga.str_periode_triwulan where id_periode = '.$target->id_periode.' and id_triwulan = '.$params['id_triwulan']);
        if ($triwulan[0]['is_in_period'] < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian (triwulan) tidak valid, [".$triwulan[0]['started_at']."-".$triwulan[0]['ended_at']."]");
        }

        // cek user instansi
        if ($this->shared->user->id_instansi != $target->id_instansi) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi user tidak sesuai');
        }

        // cek nilai range
        if ($params['nilai'] >100 ||  $params['nilai']<0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai klaim tidak valid [0-100]');
        }
        
        // cek keterangan
        if (is_null($params['keterangan']) || trim($this->html2Text($params['keterangan'])) === "") {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Keterangan wajib diisi');
        }

        // cek attachment
        if ( sizeof($params['attachments']) === 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Wajib upload data dukung');
        }

        // save log, harus di cek pernah save belum 
        $log = StranasNewTargetLog::findFirst([
            'conditions' => 'id_master='.$params['id_master'].' AND id_triwulan='.$params['id_triwulan']
        ]);
        
        if(empty($log)) { // insert
            $log = new StranasNewTargetLog();    
        }
        $log->id_triwulan       = $params['id_triwulan'];
        $log->id_master  = $params['id_master'];
        $log->keterangan        = $params['keterangan'];
        $log->nilai             = $params['nilai'];
        $log->created_by = $this->shared->user->um_id;
        $res = $log->save();

        // update nilai target instansi
        // $target = StranasNewMasterTarget::findFirst($params['id_master']); 
        $master = $this->rawQuery('update jaga.str_target_master set klaim_tw'.$params['id_triwulan'].'='.$params['nilai'].' where id='.$params['id_master']);

        if (!empty($params['attachments']) && !is_array($params['attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Attachments tidak valid');
        }

        // BERSIHKAN ATTACHMENTS
        if (!empty($params['activities']) && count($params['activities'])>0) {
            $act = implode(',', $params['activities']);
            // reset
            $this->rawQuery('update jaga.str_target_activities set is_submit = false where id_master = '.$params['id_master']);
            // update
            $this->rawQuery('update jaga.str_target_activities set is_submit = true where id in ('.$act.')');
        }

        if (!empty($params['deleted_attachments'])) {
            foreach (array_unique($params['deleted_attachments']) as $uuid) {
                
                $attch_files = Attachments::findFirstByUuid($uuid);

                if (!empty($attch_files)) {
                    $attachments = StranasNewAttachment::findFirstByIdAttachment($attch_files->id);

                    if (!empty($attachments) && $attachments->tipe == 0) {
                        $attachments->delete();
                    }
                    // AttachmentHelper::delete($attch_files, true, $this->minio);
                }
            }
        }
        
        // PASANG ATTACHMENTS
        if (!empty($params['attachments'])) {
            // $reset   = StranasNewAttachment::findByIdLog($log->id)->delete(); 
            $reset   = $this->rawQuery('delete from jaga.str_target_attachment where tipe=0 and id_log='.$log->id);

            foreach (array_unique($params['attachments']) as $key => $uuid) {
                $attch_files = Attachments::findFirstByUuid($uuid);
                
                if (!empty($attch_files) && empty($attch_files->deleted_at)) {
                    // save attachment
                    $attachments = new StranasNewAttachment();
                    $attachments->id_attachment = $attch_files->id;
                    $attachments->id_log = $log->id;
                    $attachments->tipe = 0; // submit
                    $now = date('Y-m-d H:i:s');
                    $attachments->created_at = $now;
                    $attachments->created_by = $this->shared->user->um_id;
                    $attachments->save();

                    // update file attachment status
                    $attch_files->status = Attachments::STATUS_REFERRED;
                    $attch_files->referer = Attachments::REFERER_STRANAS_ANSWER;
                    $attch_files->save();
                }
            }
        }

        return new CollectionPaginatedResponse([$log]);
    }

    // comment
    public function commentdelete($id) {
        $this->checkScopes(['stranas.comment']);

        $tbl = StranasNewReportLogComments::findFirst($id);
        if (empty($tbl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Comment tidak ditemukan');
        }
        if ($tbl->created_by != $this->shared->user->um_id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Anda bukan pembuat comment ini');
        }

        $status = $tbl->delete();

        if (!$status) {
            $errors = $tbl->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessage()];
            }

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$tbl]);
        }
    }

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

        $postParams = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $postParams,
            [
                'id' => ['validator' => ['digit']], // id StranasNewMasterTarget
                'id_log' => ['validator' => ['required', 'digit']], // id StranasNewMasterTarget
                'id_triwulan' => ['validator' => ['required', 'digit']], // id_triwulan
                'comment' => ['validator' => []],
                'attachments' => ['validator' => []],
                // 'deleted_attachments' => ['validator' => []]
            ]
        );

        $log = StranasNewTargetLog::findFirst($params['id_log']);
        if(empty($log)) { // insert
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Belum ada laporan untuk triwulan ini.");
        }

        $target     = StranasNewMasterTarget::findFirst($log->id_master);
        if (empty($target)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target instansi tidak ditemukan');
        }

        // cek periode pengisian
        $triwulan   = $this->rawQuery('select *, case when now() between started_at and ended_at then 1 else 0 end as is_in_period from jaga.str_periode_triwulan where id_periode = '.$target->id_periode.' and id_triwulan = '.$log->id_triwulan);
        if ($triwulan[0]['is_in_period'] < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian (triwulan) tidak valid, [".$triwulan[0]['started_at']."-".$triwulan[0]['ended_at']."]");
        }
        
        $id = (isset($params['id']) && $params['id'] != '') ? $params['id'] : 0;

        // cek sudah commentkah
        $tbl = StranasNewReportLogComments::findFirst($id);
        if(empty($tbl)) { // insert
            $tbl = new StranasNewReportLogComments();
        }

        // $comment->id_triwulan   = $params['id_triwulan'];
        $tbl->id_log        = $log->id;
        $tbl->comment       = $params['comment'];

        $now = date('Y-m-d H:i:s');
        if($id != 0) {
            $tbl->updated_at = $now;
            $tbl->updated_by = $this->shared->user->um_id;            
        } else {
            $tbl->created_at = $now;
            $tbl->created_by = $this->shared->user->um_id;    
        }

        $res = $tbl->save();

        if (!empty($params['attachments']) && !is_array($params['attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Attachments tidak valid');
        }

        // PASANG ATTACHMENTS
        if (!empty($params['attachments'])) {
            // $reset   = StranasNewAttachment::findByIdLog($log->id)->delete(); 
            $reset   = $this->rawQuery('delete from jaga.str_target_comment_attachment where id_comment='.$tbl->id);

            foreach (array_unique($params['attachments']) as $key => $uuid) {
                $attch_files = Attachments::findFirstByUuid($uuid);
                
                if (!empty($attch_files) && empty($attch_files->deleted_at)) {
                    // save attachment
                    $attachments = new StranasNewCommentAttachment();
                    $attachments->id_comment = $tbl->id;
                    $attachments->id_attachment = $attch_files->id;
                    // $attachments->id_log = $log->id;
                    // $attachments->tipe = 2; // submit
                    $now = date('Y-m-d H:i:s');
                    $attachments->created_at = $now;
                    $attachments->created_by = $this->shared->user->um_id;
                    $attachments->save();

                    // update file attachment status
                    $attch_files->status = Attachments::STATUS_REFERRED;
                    $attch_files->referer = Attachments::REFERER_STRANAS_ANSWER;
                    $attch_files->save();
                }
            }
        }

        return new CollectionPaginatedResponse([$tbl]);
    }

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

        $postParams = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $postParams,
            [
                'id_master' => ['validator' => ['required', 'digit']], // id StranasNewMasterTarget
                'id_triwulan' => ['validator' => ['required', 'digit']], // id_triwulan
                'nilai_verification' => ['validator' => ['required', 'digit']],
                'keterangan_verification' => ['validator' => ['required']],
                'attachments' => ['validator' => []],
                'activities' => ['validator' => []],
                'deleted_attachments' => ['validator' => []]
            ]
        );

        // check bener gak target dan instansi
        $target     = StranasNewMasterTarget::findFirst($params['id_master']);
        // print_r($target); die;
        if ($params['id_triwulan']>8) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID Triwulan tidak valid');
        }

        // cek periode pengisian
        $triwulan   = $this->rawQuery('select p.special_month, a.*, case when now() between a.verification_started_at and verification_ended_at then 1 else 0 end as is_in_period from jaga.str_periode_triwulan a join jaga.str_periode p on p.id = a.id_periode where id_periode = '.$target->id_periode.' and id_triwulan = '.$params['id_triwulan']);
        if ($triwulan[0]['is_in_period'] < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa verifikasi (triwulan) tidak valid, [".$triwulan[0]['verification_started_at']."-".$triwulan[0]['verification_ended_at']."]");
        }

        // cek nilai range
        if ($params['nilai_verification'] >100 ||  $params['nilai_verification']<0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai klaim tidak valid [0-100]');
        }

        // min verif
        if ($params['id_triwulan'] > 1) {
            $minverif   = $this->rawQuery('select max(coalesce(nilai_verification,0)) as nilai_verification from jaga.str_target_log a join (select id from jaga.str_target_master where id_target='.$target->id_target.') b on b.id=a.id_master where id_triwulan = '.($params['id_triwulan'] - 1));
            // cek nilai tidak boleh lebih kecil dari nilai_max
            if ( $params['nilai_verification'] < $minverif[0]['nilai_verification'] ) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai verifikasi tidak boleh lebih kecil dari nilai sebelumnya: '.$minverif[0]['nilai_verification'].'%');
            }
        }
        
        // cek keterangan
        if (is_null($params['keterangan_verification']) || 
            trim($this->html2Text($params['keterangan_verification'])) === "") {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Keterangan verifikasi wajib diisi');
        }

        $trg     = $this->rawQuery('select coalesce(is_special,1) as is_special, start_triwulan from jaga.str_target where id='.$target->id_target);
        
        // dispatch
        $is_special = ($trg[0]['is_special'] > 1) ? 1 : 0;
        // if special, maka masukkan nilai current month, lalu prorate kan
        if($is_special) {
            if ($trg[0]['start_triwulan'] >  $params['id_triwulan']) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Indikator belum bisa diverifikasi');
            }

            // get pembagi, in str_periode
            $pembagi = 9 - $trg[0]['start_triwulan'];

            $nilai_asli = $params['nilai_verification'];
            $params['nilai_verification'] = $nilai_asli / $pembagi;

            // echo 'update jaga.str_log_special set nilai_tw'.$params['id_triwulan'].' = '.$nilai_asli.' where id_master = '.$params['id_master']; die;
            $this->rawQuery('update jaga.str_log_special set nilai_tw'.$params['id_triwulan'].' = '.$nilai_asli.' where id_master = '.$params['id_master']);

            $master = $this->rawQuery('update jaga.str_target_master set nilai_tw'.$params['id_triwulan'].'='.$params['nilai_verification'].' where id = '.$params['id_master']);
        }

        // save log, harus di cek pernah save belum 
        $log = StranasNewTargetLog::findFirst([
            'conditions' => 'id_master='.$params['id_master'].' AND id_triwulan='.$params['id_triwulan']
        ]);
        
        if(empty($log)) { // insert
            $log = new StranasNewTargetLog();
        }

        $log->id_triwulan       = $params['id_triwulan'];
        $log->id_master  = $params['id_master'];
        $log->keterangan_verification        = $params['keterangan_verification'];
        $log->nilai_verification             = $params['nilai_verification'];
        $dt = new DateTime();
        $dt->setTimezone(new DateTimeZone('Asia/Jakarta'));
        $dt->setTimestamp(	round(microtime(true)));
        $now = $dt->format('Y-m-d H:i:s');
        $log->verified_at = $now;
        $log->verified_by = $this->shared->user->um_id;
        $res = $log->save();

        // update nilai target instansi
        if(!$is_special) {
            $master = $this->rawQuery('update jaga.str_target_master set nilai_tw'.$params['id_triwulan'].'='.$params['nilai_verification'].' where id_target='.$target->id_target);
            
            // check if other has log, if not insert
            $this->insertOtherLogsForCollaboratives($params['id_master'],
                $params['id_triwulan']);

            // update log, update all verification log
            $logs = $this->rawQuery("update jaga.str_target_log 
                            set nilai_verification = ".$params['nilai_verification']."
                            where id_master in (select id from jaga.str_target_master where id_target = ".$target->id_target.")
                                and id_triwulan = ".$params['id_triwulan']);
        }
        

        // jika ingin mengupdate nilai verifikasi yang bersangkutan saja
        // $master = $this->rawQuery('update jaga.str_target_master set nilai_tw'.$params['id_triwulan'].'='.$params['nilai_verification'].' where id='.$params['id_master']);

        if (!empty($params['attachments']) && !is_array($params['attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Attachments tidak valid');
        }

        if (!empty($params['activities']) && count($params['activities'])>0) {
            $act = implode(',', $params['activities']);
            // reset
            $this->rawQuery('update jaga.str_target_activities set is_verify = false where id_master = '.$params['id_master']);
            // update
            $this->rawQuery('update jaga.str_target_activities set is_verify = true where id in ('.$act.')');
        }

        // BERSIHKAN ATTACHMENTS
        if (!empty($params['deleted_attachments'])) {
            foreach (array_unique($params['deleted_attachments']) as $uuid) {
                $attch_files = Attachments::findFirstByUuid($uuid);

                if (!empty($attch_files)) {
                    $attachments = StranasNewAttachment::findFirstByIdAttachment($attch_files->id);

                    if (!empty($attachments) && $attachments->tipe == 1) {
                        $attachments->delete();
                    }
                    // AttachmentHelper::delete($attch_files, true, $this->minio);
                }
            }
        }

        // PASANG ATTACHMENTS
        if (!empty($params['attachments'])) {
            // $reset   = StranasNewAttachment::findByIdLog($log->id)->delete(); 
            $reset   = $this->rawQuery('delete from jaga.str_target_attachment where tipe=1 and id_log='.$log->id);


            foreach (array_unique($params['attachments']) as $key => $uuid) {
                $attch_files = Attachments::findFirstByUuid($uuid);
                
                if (!empty($attch_files) && empty($attch_files->deleted_at)) {
                    // save attachment
                    $attachments = new StranasNewAttachment();
                    $attachments->id_attachment = $attch_files->id;
                    $attachments->id_log = $log->id;
                    $attachments->tipe = 1; // verify
                    $now = date('Y-m-d H:i:s');
                    $attachments->created_at = $now;
                    $attachments->created_by = $this->shared->user->um_id;
                    $attachments->save();

                    // update file attachment status
                    $attch_files->status = Attachments::STATUS_REFERRED;
                    $attch_files->referer = Attachments::REFERER_STRANAS_ANSWER;
                    $attch_files->save();
                }
            }
        }

        return new CollectionPaginatedResponse([$log]);
    }

    // insert other logs for K/L in collaborative indicators
    private function insertOtherLogsForCollaboratives($id_master, $id_triwulan) {

        /****************************************************************************************** */
        // Function ini dibuat karena adanya kejadian dalam indikator kolaboratif
        // satu atau lebih K/L tidak klaim nilai sehingga tidak ada lognya pada jaga.str_target_log
        // sementara verifikator wajib memberikan nilai verifikasi untuk semua K/L
        // sehingga diputuskan bahwa log akan dibuat otomatis di jaga.str_target_log
        // bagi K/L yang tidak klaim nilai
        /****************************************************************************************** */
        
        /****************************************************************************************** */
        // Rancang Algoritma SQL:
        // 1. select id_target from jaga.str_target_master where id_master = '$id_master'
        // 2. select A.id from jaga.str_target_master as A where A.id_target = '$id_target' and A.id <> '$id_master'
        //          and not exists (select 1 from jaga.str_target_log as B where 
        //          B.id_master = A.id and id_triwulan = '$id_triwulan' )
        // 3. for each A.id
        //      insert into jaga.str_target_log(id_triwulan, id_master, nilai_verification) values
        //          ('A.id', '$id_triwulan', $nilai_verification);
        /****************************************************************************************** */

        // Implementasi
        // 1. select id_target from jaga.str_target_master where id_master = '$id_master'
        $targetMaster = StranasNewMasterTarget::findFirst(
            [
                'conditions' => "id = $id_master"
            ]
        );
        $id_target = $targetMaster->id_target;
        
        // 2. select A.id from jaga.str_target_master as A where A.id_target = '$id_target' and A.id <> '$id_master'
        //          and not exists (select 1 from jaga.str_target_log as B where 
        //          B.id_master = A.id and id_triwulan = '$id_triwulan' )
        $query = <<<QUERY
        select A.id from jaga.str_target_master as A where A.id_target = '$id_target'
            and not exists (select 1 from jaga.str_target_log as B where 
                  B.id_master = A.id and id_triwulan = '$id_triwulan' )
QUERY;
        $result = $this->rawQuery($query);
        
        // 3. for each A.id
        //      insert into jaga.str_target_log(id_triwulan, id_master, nilai_verification) values
        //          ('A.id', '$id_triwulan', $nilai_verification);
        foreach($result as $row) {
            $id = $row['id'];
            $query = <<<QUERY
            INSERT INTO jaga.str_target_log(id_master, id_triwulan)
                VALUES ('$id', '$id_triwulan')
QUERY;
            $this->rawQuery($query);
        }
    }

    /******
        @param $htmlString string
        returns $htmlString which will be stripped from HTML tags,
        and its special html characters be decoded
    */
    private function html2Text($htmlString) {
        $html2TextConverter = new \Html2Text\Html2Text($htmlString);
        return $html2TextConverter->getText();
    }

    public function twreport01() {
        // Laporan pelaporan setiap TW tertentu group by instansi
        $getParams = $this->request->get();
        
        // validasi
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
                'aksi' => ['validators' => []]
            ]
        );

        $prd = $params['id_periode'];
        $per = $this->rawQuery("select * from jaga.str_periode where id=".$prd); 
        $lowlevel = $per[0]['level_count'];

        // current triwulan
        $currentTriwulanWhere = "id_periode = :id_periode: and started_at <= now()";
        $currentTriwulan = StranasNewPeriodeTriwulan::findFirst([
            $currentTriwulanWhere,
            "bind" => [
                "id_periode" => $prd
            ],
            "order" => "started_at desc",
            "columns" => "id_triwulan"
        ]);
        $maxTriwulan = $currentTriwulan->id_triwulan;
        
        // all triwulans
        $allTriwulanRows = StranasNewPeriodeTriwulan::find([
            "id_periode = :id_periode: and id_triwulan <= :id_triwulan:",
            "bind" => [
                "id_periode" => $prd,
                "id_triwulan" => $maxTriwulan
            ],
            "order" => "id_triwulan",
            "columns" => "id_triwulan, ended_at"
        ]);
        $triwulans = [];
        foreach($allTriwulanRows as $row) {
            $triwulans[] = [
                "id_triwulan" => $row->id_triwulan,
                "ended_at" => $row->ended_at
            ];
        }

        // query
        $sumQueries = [];
        for($i = 0; $i < $maxTriwulan; $i++) {
            $ended_at = $triwulans[$i]['ended_at'];
            $iPlus1 = $i + 1;
            $iPlus2 = $i <= 6 ? $i + 2 : null; 
                // untuk keperluan cek nilai setelah iPlus1,
                // untuk perhitungan jumlah complete indikator:
                // bahwa iPlus2 harus null dan jaga.str_target terkait harus is_forced_complete
            $index = ( $i + 1 ) * 3;

            // buat string nilai_tw8, nilai_tw7, ...
            $klaim_tws = [];
            for($j = 8; $j > 0; $j--) {
                if ($j > $iPlus1) {
                    $klaim_tws[] = "null";    
                } else {
                    $klaim_tws[] = "nilai_tw$j";
                }
            }
            $klaim_tw = implode(",", $klaim_tws);

            $sumQuery = "
            ( sum(case when t.created_at <= '$ended_at'
                and ( t.deleted_at is null or t.deleted_at > '$ended_at'
                ) then 1 else 0 end) 
            ) as i$index,
            sum(case when a.klaim_tw$iPlus1 is not null then 1 else 0 end) as l$index,
            sum(case when a.nilai_tw$iPlus1 is not null and
                a.id_target = t.id
                and t.created_at <= '$ended_at'
                and ( t.deleted_at is null or t.deleted_at > '$ended_at' )
                then 1 else 0 end) as v$index,
            ";

            // query untuk jumlah indikator complete
            $isForcedCompleteQuery = $iPlus2 ? 
                "or (a.nilai_tw$iPlus1 is not null and a.nilai_tw$iPlus2 is null 
                and t.is_forced_complete)" : "";

            $sumQuery .= "sum(case when a.nilai_tw$iPlus1 = 100 $isForcedCompleteQuery 
                    then 1 else 0 end) as c$index";

            $sumQueries[] = $sumQuery;
        }
        $triwulanQuery = implode(",", $sumQueries);
        
        $aksiCond = "";
        if ($params['aksi'] && !is_null($params['aksi']) && sizeof($params['aksi']) > 0) {
            $aksiCond = "and t.id_level2 in (".implode(',', $params['aksi']).")";
        }

        $q = "with t1 as (
            select 
                a.id_instansi, i.nama as instansi, abbr.singkatan, i.id_tipe,
                $triwulanQuery
            from jaga.str_target_master a
              join jaga.instansi i on i.id = a.id_instansi 
              join jaga.str_target t on t.id = a.id_target and t.deleted_at is null 
              left join jaga.str_abbr abbr on a.id_instansi = abbr.id_instansi
            where t.deleted_at is null and a.deleted_at is null and a.id_periode=".$prd.$aksiCond."
            group by a.id_instansi, i.nama, abbr.singkatan, i.id_tipe
            order by i.nama
            )
            select * from t1
        ";

        $data = $this->rawQuery($q);

        // processing data:
        // - belum lapor
        // - akumulasi penurunan jumlah indikator per triwulan (selesai)
        for($i = 0; $i < sizeof($data); $i++) {
            $accumulated_selesai = 0; // akumulasi jumlah indikator yang selesai
            foreach($triwulans as $triwulan) {
                $id_triwulan = $triwulan["id_triwulan"] * 3;
                // jumlah indikator dikurangi dengan akumulasi indikator yg sudah selesai sebelumnya
                $data[$i]["i$id_triwulan"] -= $accumulated_selesai;
                $accumulated_selesai += $data[$i]["c$id_triwulan"];
                $data[$i]["bl$id_triwulan"] = $data[$i]["i$id_triwulan"] -
                    $data[$i]["l$id_triwulan"]; // jumlah belum lapor
                $data[$i]["bv$id_triwulan"] = $data[$i]["i$id_triwulan"] -
                    $data[$i]["v$id_triwulan"]; // jumlah belum verifikasi
            }
        }
        // urutkan: 1)K/L, 2)Prov, 3)Kab/Kota
        $kl = [];
        for($i = sizeof($data) - 1; $i >= 0; $i--) {
          if ($data[$i]['id_tipe'] === 3) {
            array_unshift($kl, $data[$i]);
            unset($data[$i]);
          }
        }

        $data = array_merge($kl, $data);

        // final processing: unset id_tipe
        for($i = 0; $i < sizeof($data); $i++) {
            unset($data[$i]['id_tipe']);
        }

        $response = new \Phalcon\Http\Response();
        $response->setContentType('application/json', 'UTF-8');
        $response->setContent(json_encode([
            'success' => true,
            "maxTriwulan" => $maxTriwulan,
            'data' => $data
        ]));

        return $response;
    }

    public function twreport02() {
        // Laporan pelaporan setiap TW tertentu group by aksi
        $getParams = $this->request->get();
        
        // validasi
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
            ]
        );

        $prd = $params['id_periode'];
        $per = $this->rawQuery("select * from jaga.str_periode where id=".$prd); 
        $lowlevel = $per[0]['level_count'];

        // current triwulan
        $currentTriwulanWhere = "id_periode = :id_periode: and started_at <= now()";
        $currentTriwulan = StranasNewPeriodeTriwulan::findFirst([
            $currentTriwulanWhere,
            "bind" => [
                "id_periode" => $prd
            ],
            "order" => "started_at desc",
            "columns" => "id_triwulan"
        ]);
        $maxTriwulan = $currentTriwulan->id_triwulan;
        
        // all triwulans
        $allTriwulanRows = StranasNewPeriodeTriwulan::find([
            "id_periode = :id_periode: and id_triwulan <= :id_triwulan:",
            "bind" => [
                "id_periode" => $prd,
                "id_triwulan" => $maxTriwulan
            ],
            "order" => "id_triwulan",
            "columns" => "id_triwulan, ended_at"
        ]);
        $triwulans = [];
        foreach($allTriwulanRows as $row) {
            $triwulans[] = [
                "id_triwulan" => $row->id_triwulan,
                "ended_at" => $row->ended_at
            ];
        }

        // query
        $sumQueries = [];
        for($i = 0; $i < $maxTriwulan; $i++) {
            $ended_at = $triwulans[$i]['ended_at'];
            $iPlus1 = $i + 1;
            $iPlus2 = $i <= 6 ? $i + 2 : null; 
                // untuk keperluan cek nilai setelah iPlus1,
                // untuk perhitungan jumlah complete indikator:
                // bahwa iPlus2 harus null dan jaga.str_target terkait harus is_forced_complete
            $index = ( $i + 1 ) * 3;

            // buat string nilai_tw8, nilai_tw7, ...
            $klaim_tws = [];
            for($j = 8; $j > 0; $j--) {
                if ($j > $iPlus1) {
                    $klaim_tws[] = "null";    
                } else {
                    $klaim_tws[] = "nilai_tw$j";
                }
            }
            $klaim_tw = implode(",", $klaim_tws);

            $sumQuery = "
            ( sum(case when t.created_at <= '$ended_at'
                and ( t.deleted_at is null or t.deleted_at > '$ended_at'
                ) then 1 else 0 end) 
            ) as i$index,
            sum(case when a.klaim_tw$iPlus1 is not null then 1 else 0 end) as l$index,
            sum(case when a.nilai_tw$iPlus1 is not null and
                a.id_target = t.id
                and t.created_at <= '$ended_at'
                and ( t.deleted_at is null or t.deleted_at > '$ended_at' )
                then 1 else 0 end) as v$index,
            ";

            // query untuk jumlah indikator complete
            $isForcedCompleteQuery = $iPlus2 ? 
                "or (a.nilai_tw$iPlus1 is not null and a.nilai_tw$iPlus2 is null 
                and t.is_forced_complete)" : "";

            $sumQuery .= "sum(case when a.nilai_tw$iPlus1 = 100 $isForcedCompleteQuery 
                    then 1 else 0 end) as c$index";

            $sumQueries[] = $sumQuery;
        }
        $triwulanQuery = implode(",", $sumQueries);

        $q = "with t1 as (
            select 
                aksi.id, aksi.nama,
                $triwulanQuery
            from jaga.str_target_master a 
              join jaga.str_target t on t.id = a.id_target and t.deleted_at is null 
              join jaga.str_hierarchy aksi on aksi.id = t.id_level2
            where t.deleted_at is null and a.deleted_at is null and a.id_periode=".$prd."
            group by aksi.id, aksi.nama
            order by aksi.id
            )
            select * from t1
        ";

        $data = $this->rawQuery($q);

        // processing data:
        // - belum lapor
        // - akumulasi penurunan jumlah indikator per triwulan (selesai)
        for($i = 0; $i < sizeof($data); $i++) {
            $accumulated_selesai = 0; // akumulasi jumlah indikator yang selesai
            foreach($triwulans as $triwulan) {
                $id_triwulan = $triwulan["id_triwulan"] * 3;
                // jumlah indikator dikurangi dengan akumulasi indikator yg sudah selesai sebelumnya
                $data[$i]["i$id_triwulan"] -= $accumulated_selesai;
                $accumulated_selesai += $data[$i]["c$id_triwulan"];
                $data[$i]["bl$id_triwulan"] = $data[$i]["i$id_triwulan"] -
                    $data[$i]["l$id_triwulan"]; // jumlah belum lapor
                $data[$i]["bv$id_triwulan"] = $data[$i]["i$id_triwulan"] -
                    $data[$i]["v$id_triwulan"]; // jumlah belum verifikasi
            }
        }

        $response = new \Phalcon\Http\Response();
        $response->setContentType('application/json', 'UTF-8');
        $response->setContent(json_encode([
            'success' => true,
            "maxTriwulan" => $maxTriwulan,
            'data' => $data
        ]));

        return $response;
    }

    private function downloadReport2019() {
        // mengambil report tahun 2019-2020
        $q0 = "
            select distinct
                    a.id as id_target,
                    a.keterangan as target,
                    a.info_nilai as aspek_penilaian,
                    a.info_lampiran,
                    a.id as id_level4,
                    a.keterangan as level4,
                    b.id as id_level3,
                    b.nama as level3,
                    c.id as id_level2,
                    c.nama as level2,
                    d.id as id_level1,
                    d.nama as level1
                from
                    jaga.stranas_target a
                join jaga.stranas_subaksi b
                    on b.id = a.id_subaksi
                join jaga.stranas_aksi c
                    on c.id = b.id_aksi
                join jaga.stranas_fokus d
                    on d.id = c.id_fokus
                where
                    exists (select * from jaga.stranas_survey_template_question e 
                        where e.id_target = a.id)
            group by a.id, b.id, c.id, d.id
            order by a.id";

        $q1 = "select
            B.id_instansi_satker as id_instansi,
            A.id_question as id_target,
            E.nama as instansi,
            max(CASE when B.id_periode = '1' THEN A.nilai else NULL end) as klaim_tw1,
            max(CASE when B.id_periode = '2' THEN A.nilai else NULL end) as klaim_tw2,
            max(CASE when B.id_periode = '3' THEN A.nilai else NULL end) as klaim_tw3,
            max(CASE when B.id_periode = '4' THEN A.nilai else NULL end) as klaim_tw4,
            max(CASE when B.id_periode = '5' THEN A.nilai else NULL end) as klaim_tw5,
            max(CASE when B.id_periode = '6' THEN A.nilai else NULL end) as klaim_tw6,
            max(CASE when B.id_periode = '7' THEN A.nilai else NULL end) as klaim_tw7,
            max(CASE when B.id_periode = '8' THEN A.nilai else NULL end) as klaim_tw8,
            max(CASE when B.id_periode = '1' THEN A.nilai_verification else NULL end) as nilai_tw1,
            max(CASE when B.id_periode = '2' THEN A.nilai_verification else NULL end) as nilai_tw2,
            max(CASE when B.id_periode = '3' THEN A.nilai_verification else NULL end) as nilai_tw3,
            max(CASE when B.id_periode = '4' THEN A.nilai_verification else NULL end) as nilai_tw4,
            max(CASE when B.id_periode = '5' THEN A.nilai_verification else NULL end) as nilai_tw5,
            max(CASE when B.id_periode = '6' THEN A.nilai_verification else NULL end) as nilai_tw6,
            max(CASE when B.id_periode = '7' THEN A.nilai_verification else NULL end) as nilai_tw7,
            max(CASE when B.id_periode = '8' THEN A.nilai_verification else NULL end) as nilai_tw8,
            max(A.nilai_verification) as nilai_verifikasi,
            string_agg(CASE when B.id_periode = '1' THEN A.keterangan else NULL end, '') as cmt1,
            string_agg(CASE when B.id_periode = '2' THEN A.keterangan else NULL end, '') as cmt2,
            string_agg(CASE when B.id_periode = '3' THEN A.keterangan else NULL end, '') as cmt3,
            string_agg(CASE when B.id_periode = '4' THEN A.keterangan else NULL end, '') as cmt4,
            string_agg(CASE when B.id_periode = '5' THEN A.keterangan else NULL end, '') as cmt5,
            string_agg(CASE when B.id_periode = '6' THEN A.keterangan else NULL end, '') as cmt6,
            string_agg(CASE when B.id_periode = '7' THEN A.keterangan else NULL end, '') as cmt7,
            string_agg(CASE when B.id_periode = '8' THEN A.keterangan else NULL end, '') as cmt8,
            string_agg(CASE when B.id_periode = '1' THEN A.keterangan_verification else NULL end, '') as ver1,
            string_agg(CASE when B.id_periode = '2' THEN A.keterangan_verification else NULL end, '') as ver2,
            string_agg(CASE when B.id_periode = '3' THEN A.keterangan_verification else NULL end, '') as ver3,
            string_agg(CASE when B.id_periode = '4' THEN A.keterangan_verification else NULL end, '') as ver4,
            string_agg(CASE when B.id_periode = '5' THEN A.keterangan_verification else NULL end, '') as ver5,
            string_agg(CASE when B.id_periode = '6' THEN A.keterangan_verification else NULL end, '') as ver6,
            string_agg(CASE when B.id_periode = '7' THEN A.keterangan_verification else NULL end, '') as ver7,
            string_agg(CASE when B.id_periode = '8' THEN A.keterangan_verification else NULL end, '') as ver8,
            string_agg(CASE when B.id_periode = '1' THEN F.nama else NULL end, '') as klaim_by1,
            string_agg(CASE when B.id_periode = '2' THEN F.nama else NULL end, '') as klaim_by2,
            string_agg(CASE when B.id_periode = '3' THEN F.nama else NULL end, '') as klaim_by3,
            string_agg(CASE when B.id_periode = '4' THEN F.nama else NULL end, '') as klaim_by4,
            string_agg(CASE when B.id_periode = '5' THEN F.nama else NULL end, '') as klaim_by5,
            string_agg(CASE when B.id_periode = '6' THEN F.nama else NULL end, '') as klaim_by6,
            string_agg(CASE when B.id_periode = '7' THEN F.nama else NULL end, '') as klaim_by7,
            string_agg(CASE when B.id_periode = '8' THEN F.nama else NULL end, '') as klaim_by8,
            string_agg(CASE when B.id_periode = '1' THEN G.nama else NULL end, '') as verby_1,
            string_agg(CASE when B.id_periode = '2' THEN G.nama else NULL end, '') as verby_2,
            string_agg(CASE when B.id_periode = '3' THEN G.nama else NULL end, '') as verby_3,
            string_agg(CASE when B.id_periode = '4' THEN G.nama else NULL end, '') as verby_4,
            string_agg(CASE when B.id_periode = '5' THEN G.nama else NULL end, '') as verby_5,
            string_agg(CASE when B.id_periode = '6' THEN G.nama else NULL end, '') as verby_6,
            string_agg(CASE when B.id_periode = '7' THEN G.nama else NULL end, '') as verby_7,
            string_agg(CASE when B.id_periode = '8' THEN G.nama else NULL end, '') as verby_8,
            string_agg(CASE when B.id_periode = '1' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at1,
            string_agg(CASE when B.id_periode = '2' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at2,
            string_agg(CASE when B.id_periode = '3' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at3,
            string_agg(CASE when B.id_periode = '4' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at4,
            string_agg(CASE when B.id_periode = '5' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at5,
            string_agg(CASE when B.id_periode = '6' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at6,
            string_agg(CASE when B.id_periode = '7' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at7,
            string_agg(CASE when B.id_periode = '8' THEN 
                to_char(A.created_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as klaim_at8,
            string_agg(CASE when B.id_periode = '1' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_1,
            string_agg(CASE when B.id_periode = '2' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_2,
            string_agg(CASE when B.id_periode = '3' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_3,
            string_agg(CASE when B.id_periode = '4' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_4,
            string_agg(CASE when B.id_periode = '5' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_5,
            string_agg(CASE when B.id_periode = '6' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_6,
            string_agg(CASE when B.id_periode = '7' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_7,
            string_agg(CASE when B.id_periode = '8' THEN 
                to_char(A.verified_at::timestamp, 'YYYY-MM-DD HH24:MI:SS')
                else NULL end, '') as verat_8
        FROM
        jaga.stranas_survey_answer A join jaga.stranas_survey B
            on A.id_survey = B.id
        join jaga.stranas_survey_template_question C
            on B.id_survey_template = C.id_survey_template
        join jaga.stranas_target D
            on D.id = C.id_target and D.id = A.id_question -- PENTING: D.id = A.id_question
        join jaga.instansi E
            on B.id_instansi_satker = E.id
        left join jaga.um_user F
            on A.created_by = F.um_id
        left join jaga.um_user G
            on A.verified_by = G.um_id
        where 
            exists (select * from jaga.stranas_survey_template_question E where E.id_target = D.id)
            group by B.id_instansi_satker, A.id_question, E.nama
            order by B.id_instansi_satker, A.id_question";

        $query = "with A as ($q0),
            B as ($q1)
            select * from A join B
                on A.id_target = B.id_target
        ";
        $rows = $this->rawQuery($query);
        
        $header = ['id',
            'o1','o2','o3','o4','oindikator','is_special',
            'nilai_tw1',
            'nilai_tw2',
            'nilai_tw3',
            'nilai_tw4',
            'nilai_tw5', 
            'nilai_tw6',        
            'nilai_tw7',        
            'nilai_tw8',        
            'nilai_verifikasi','id_instansi','id_target','id_periode','id_level1','id_level2','id_level3','id_level4','level1','level2','level3','level4','instansi','target', 'aspek_penilaian', 'info_lampiran',
            'cmt1','ver1','verby_1','verat_1',
            'cmt2','ver2','verby_2','verat_2',
            'cmt3','ver3','verby_3','verat_3',
            'cmt4','ver4','verby_4','verat_4',
            'cmt5','ver5','verby_5','verat_5',
            'cmt6','ver6','verby_6','verat_6',
            'cmt7','ver7','verby_7','verat_7',
            'cmt8','ver8','verby_8','verat_8',
            'klaim_by1', 'klaim_at1', 'klaim_tw1',
            'klaim_by2', 'klaim_at2', 'klaim_tw2',
            'klaim_by3', 'klaim_at3', 'klaim_tw3',
            'klaim_by4', 'klaim_at4', 'klaim_tw4',
            'klaim_by5', 'klaim_at5', 'klaim_tw5',
            'klaim_by6', 'klaim_at6', 'klaim_tw6',
            'klaim_by7', 'klaim_at7', 'klaim_tw7',
            'klaim_by8', 'klaim_at8', 'klaim_tw8',
            'is_completed',
            'key_activities'
        ];
        $result[] = $header;

        foreach($rows as $row) {
            $result[] = [
                $row['id_target'] ?? '',
                '',// $row['o1'] ?? '',
                '',// $row['o2'] ?? '',
                '',// $row['o3'] ?? '',
                '',// $row['o4'] ?? '',
                '',// $row['oindikator'] ?? '',
                '',// $row['is_special'] ?? '',
                strval($row['nilai_tw1']) ?? '',
                strval($row['nilai_tw2']) ?? '',
                strval($row['nilai_tw3']) ?? '',
                strval($row['nilai_tw4']) ?? '',
                strval($row['nilai_tw5']) ?? '',
                strval($row['nilai_tw6']) ?? '',
                strval($row['nilai_tw7']) ?? '',
                strval($row['nilai_tw8']) ?? '',
                strval($row['nilai_verifikasi']) ?? '',
                $row['id_instansi'] ?? '',
                $row['id_target'] ?? '',
                '1', // id_periode
                $row['id_level1'] ?? '',
                $row['id_level2'] ?? '',
                $row['id_level3'] ?? '',
                $row['id_level4'] ?? '',
                $row['level1'] ?? '',
                $row['level2'] ?? '',
                $row['level3'] ?? '',
                $row['level4'] ?? '',
                $row['instansi'] ?? '',
                $this->html2Text(trim($row['target'] ?? '')),
                $this->html2Text(trim($row['aspek_penilaian'] ?? '')),
                $this->html2Text(trim($row['info_lampiran'] ?? '')),
                $this->html2Text(trim($row['cmt1'] ?? '')),
                $this->html2Text(trim($row['ver1'] ?? '')),
                $row['verby_1'] ?? '',
                $row['verat_1'] ?? '',
                $this->html2Text(trim($row['cmt2'] ?? '')),
                $this->html2Text(trim($row['ver2'] ?? '')),
                $row['verby_2'] ?? '',
                $row['verat_2'] ?? '',
                $this->html2Text(trim($row['cmt3'] ?? '')),
                $this->html2Text(trim($row['ver3'] ?? '')),
                $row['verby_3'] ?? '',
                $row['verat_3'] ?? '',
                $this->html2Text(trim($row['cmt4'] ?? '')),
                $this->html2Text(trim($row['ver4'] ?? '')),
                $row['verby_4'] ?? '',
                $row['verat_4'] ?? '',
                $this->html2Text(trim($row['cmt5'] ?? '')),
                $this->html2Text(trim($row['ver5'] ?? '')),
                $row['verby_5'] ?? '',
                $row['verat_5'] ?? '',
                $this->html2Text(trim($row['cmt6'] ?? '')),
                $this->html2Text(trim($row['ver6'] ?? '')),
                $row['verby_6'] ?? '',
                $row['verat_6'] ?? '',
                $this->html2Text(trim($row['cmt7'] ?? '')),
                $this->html2Text(trim($row['ver7'] ?? '')),
                $row['verby_7'] ?? '',
                $row['verat_7'] ?? '',
                $this->html2Text(trim($row['cmt8'] ?? '')),
                $this->html2Text(trim($row['ver8'] ?? '')),
                $row['verby_8'] ?? '',
                $row['verat_8'] ?? '',
                $row['klaim_by1'] ?? '',
                $row['klaim_at1'] ?? '',
                strval($row['klaim_tw1']) ?? '',
                $row['klaim_by2'] ?? '',
                $row['klaim_at2'] ?? '',
                strval($row['klaim_tw2']) ?? '',
                $row['klaim_by3'] ?? '',
                $row['klaim_at3'] ?? '',
                strval($row['klaim_tw3']) ?? '',
                $row['klaim_by4'] ?? '',
                $row['klaim_at4'] ?? '',
                strval($row['klaim_tw4']) ?? '',
                $row['klaim_by5'] ?? '',
                $row['klaim_at5'] ?? '',
                strval($row['klaim_tw5']) ?? '',
                $row['klaim_by6'] ?? '',
                $row['klaim_at6'] ?? '',
                strval($row['klaim_tw6']) ?? '',
                $row['klaim_by7'] ?? '',
                $row['klaim_at7'] ?? '',
                strval($row['klaim_tw7']) ?? '',
                $row['klaim_by8'] ?? '',
                $row['klaim_at8'] ?? '',
                strval($row['klaim_tw8']) ?? '',
                array_key_exists('nilai_verifikasi', $row) && intval($row['nilai_verifikasi']) === 100 ? '1' : '',
                '' // $row['key_activities'] ?? ''
            ];
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray($result, NULL, 'A1');

        $writer = new Xlsx($spreadsheet);

        // keterangan waktu untuk filename
        $datetime = $this->rawQuery('select now()');
        $now = date_parse($datetime[0]['now']);
        $year = $now['year'];
        $month = str_pad($now['month'], 2, '0', STR_PAD_LEFT);
        $day = str_pad($now['day'], 2, '0', STR_PAD_LEFT);
        $hour = str_pad($now['hour'], 2, '0', STR_PAD_LEFT);
        $minute = str_pad($now['minute'], 2, '0', STR_PAD_LEFT);

        $filename = "export_target_stranas_$year$month$day"."_"."$hour$minute.xls";
        header('Content-type: application/vnd.ms-excel');
        header('content-disposition: attachment; filename="' . $filename . '"');
        $writer->save('php://output');
        die;
    }
}