<?php

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

class StranasNewDashboardController extends BaseController
{
    public function index() {
        $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'];

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

        $where1 = $where2 = $where3 = '';
        $where1 .= (isset($_GET['id_instansi']) && $_GET['id_instansi'] != '') ? ' and id_instansi = '.$_GET['id_instansi'] : '';
        $where1 .= (isset($_GET['id_target']) && $_GET['id_target'] != '') ? ' and id_target = '.$_GET['id_target'] : '';
        $where1 .= (isset($_GET['id_tipe']) && $_GET['id_tipe'] != '') ? ' and i.id_tipe = '.$_GET['id_tipe'] : '';
        $where1 .= (isset($_GET['id_provinsi']) && $_GET['id_provinsi'] != '') ? ' and i.id_provinsi = '.$_GET['id_provinsi'] : '';
        $where1 .= (isset($_GET['id_kota_kabupaten']) && $_GET['id_kota_kabupaten'] != '') ? ' and i.id_kota_kabupaten = '.$_GET['id_kota_kabupaten'] : '';

        $where1 .= (isset($_GET['id_periode']) && $_GET['id_periode'] != '') ? ' and a.id_periode = '.$_GET['id_periode'] : ' and a.id_periode = '.$prd;

        $where2 .= (isset($_GET['id_level1']) && $_GET['id_level1'] != '') ? ' and id_level1 = '.$_GET['id_level1'] : '';
        $where2 .= (isset($_GET['id_level2']) && $_GET['id_level2'] != '') ? ' and id_level2 = '.$_GET['id_level2'] : '';
        $where2 .= (isset($_GET['id_level3']) && $_GET['id_level3'] != '') ? ' and id_level3 = '.$_GET['id_level3'] : '';
        $where2 .= (isset($_GET['id_level4']) && $_GET['id_level4'] != '') ? ' and id_level4 = '.$_GET['id_level4'] : '';

        $where3 = ' where 1=1 ';
        $where3 .= (isset($_GET['no_logframe']) && $_GET['no_logframe'] == '1') ? ' and t1.is_special <> 1 ' : '';
        $where3 .= (isset($_GET['is_special']) && $_GET['is_special'] != '') ? ' and t1.is_special = '.$_GET['is_special'] : '';
        $where3 .= (isset($_GET['keyword']) && $_GET['keyword'] != '') ? " and t1.indikator ilike '%".$_GET['keyword']."%'" : '';

        $nilaiTwSubquery = $prd === '1' ? "
          greatest(nilai_tw1,nilai_tw2) nilai_tw2,
          greatest(nilai_tw1,nilai_tw2,nilai_tw3) nilai_tw3,
          greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4) nilai_tw4,
          greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5) nilai_tw5,
          greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6) nilai_tw6,
          greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7) nilai_tw7,
          greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) nilai_tw8,
          greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) nilai_max
        " : 
        "jaga.latest_verification(null, null, null, null, null, null, nilai_tw2, nilai_tw1) nilai_tw2,
          jaga.latest_verification(null, null, null, null, null, nilai_tw3, nilai_tw2, nilai_tw1) nilai_tw3,
          jaga.latest_verification(null, null, null, null, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) nilai_tw4,
          jaga.latest_verification(null, null, null, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) nilai_tw5,
          jaga.latest_verification(null, null, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) nilai_tw6,
          jaga.latest_verification(null, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) nilai_tw7,
          jaga.latest_verification(nilai_tw8, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) nilai_tw8,
          jaga.latest_verification(nilai_tw8, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) nilai_max
        ";

        $table = "with 
            thierarchy as (
                select *
                from jaga.hierachy_by_level
                where 1=1 ".$where2."
            ),
            t1 as (
            select 
                id_instansi, c.id_level".$lowlevel." as id_hierarchy, i.id_tipe as id_tipex, a.id_target,
                c.nama as indikator,
                coalesce(c.is_special,0) as is_special,
                i.id_provinsi,
                i.id_kota_kabupaten,
                i.id_tipe,
                nilai_tw1,
                $nilaiTwSubquery
            from jaga.str_target_master a  
                join jaga.instansi i on i.id=a.id_instansi
                join jaga.str_target c on c.id=a.id_target and c.deleted_at is null
                ".(($where2 !== '') ? 'join thierarchy h on h.id_level'.$lowlevel.' = c.id_level'.$lowlevel : '')."
            where a.deleted_at is null and c.deleted_at is null ".$where1."
            ),
            tinstansi as (
              select 
                i.id as id_instansi,
                i.nama as instansi,
                coalesce(b.singkatan,'') as singkatan
              from jaga.instansi i
                left join jaga.str_abbr b on b.id_instansi = i.id
            ),
            ttipe as (
              select 
                i.id as id_tipe,
                i.nama as tipe_instansi
              from jaga.instansi_tipe i
            ),
            tprov as (
                select 
                cast(i.provinsi_code as int) as id_provinsi,
                i.provinsi_name as provinsi
              from jaga.master_provinsi i
            ),
            tkab as (
              select 
                cast(k.kota_kabupaten_code as int) as id_kota_kabupaten,
                k.kota_kabupaten_name as kota_kabupaten,
                cast(k.provinsi_code as int) as id_provinsi, p.provinsi_name  as provinsi
              from jaga.master_kota_kabupaten k
                join jaga.master_provinsi p on cast(p.provinsi_code as int) = cast(k.provinsi_code as int) 
            ),
            tindikator as (
                select id as id_target, nama as target
                from jaga.str_target
                where 1=1 ".$where2."
            )
            ";
        // echo $table; die;
        $kolom = $join = $groupby = array();
        $orderby = '';
        if (!empty($_GET['kolom']) && $_GET['kolom'] != '') {
            $kol=explode(',', $_GET['kolom']);

            foreach ($kol as $k) {
                // 
                if($k == 'id_instansi') {
                    $kolom[] .= 'i.id_instansi, i.instansi, i.singkatan';
                    $join[] = ' join tinstansi i on i.id_instansi=t1.id_instansi ';
                }
                if($k == 'id_target') {
                    $kolom[] .= 't.id_target, t.target';
                    $join[] = ' join tindikator t on t.id_target=t1.id_target ';
                }
                // tipe
                if($k == 'id_tipe') {
                    $kolom[] .= 't.id_tipe, t.tipe_instansi';
                    $join[] = ' join ttipe t on t.id_tipe=t1.id_tipe ';
                }
                // provinsi
                if($k == 'id_provinsi' && !in_array('id_kota_kabupaten', $kol)) {
                    $kolom[] .= 'p.id_provinsi, p.provinsi';
                    $join[] = ' join tprov p on p.id_provinsi=t1.id_provinsi ';
                }
                // kabupaten
                if($k == 'id_kota_kabupaten') {
                    $kolom[] .= in_array('id_provinsi', $kol) ? 'k.id_provinsi, k.provinsi,k.id_kota_kabupaten, k.kota_kabupaten ' : 'k.id_kota_kabupaten, k.kota_kabupaten ';
                    $join[] = ' join tkab k on k.id_kota_kabupaten=t1.id_kota_kabupaten ';
                }
                // level1
                if($k == 'id_level1') {
                    $kolom[] .= ' h.id_level1, h.level1, h.o1';
                    $join[] = ' join thierarchy h on h.id_level'.$lowlevel.'=t1.id_hierarchy ';
                    $orderby = ' order by h.o1 asc';
                }
                if($k == 'id_level2') {
                    $kolom[] .= ' h.id_level2, h.level2, h.o2';
                    $join[] = ' join thierarchy h on h.id_level'.$lowlevel.'=t1.id_hierarchy ';
                    $orderby = ' order by h.o2 asc';
                }
                if($k == 'id_level3') {
                    $kolom[] .= ' h.id_level3, h.level3, h.o3';
                    $join[] = ' join thierarchy h on h.id_level'.$lowlevel.'=t1.id_hierarchy ';
                    $orderby = ' order by h.o3 asc';
                }
                if($k == 'id_level4') {
                    $kolom[] .= ' h.id_level4, h.level4, h.o4';
                    $join[] = ' join thierarchy h on h.id_level'.$lowlevel.'=t1.id_hierarchy ';
                    $orderby = ' order by h.o4 asc';
                }
            }
        }
        
        $joinby = (count($join) > 0) ? implode('', array_unique($join)) : '';
        $koloml  = (count($kolom) > 0) ? implode(',', $kolom).',' : '';
        $groupby = (count($kolom) > 0) ? ' group by '.implode(',', $kolom) : '';
        // var_dump($koloml); die;

        $q = $table.
            "select 
                count(1) as x,
                ".$koloml."
                sum(nilai_tw1) as tw1, 
                sum(nilai_tw2) as tw2, 
                sum(nilai_tw3) as tw3,
                sum(nilai_tw4) as tw4,
                sum(nilai_tw5) as tw5,
                sum(nilai_tw6) as tw6,
                sum(nilai_tw7) as tw7,
                sum(nilai_tw8) as tw8, 
                sum(nilai_max) as nilai_max, 
                count(1)*100 as nilai_total,
                ( sum(nilai_max) / (count(1)*100.0) ) * 100 as persen
              from t1   
              ".$joinby."
              ".$where3."
              ".$groupby."
              ".$orderby;
        // echo $q; die;
        $data = $this->rawQuery($q);

        if(isset($_GET['download']) && $_GET['download']==1) {
            $header = [];
            foreach ($data[0] as $k => $v) {
                $header[] = $k;
            }

            $rows[] = $header;
            foreach ($data as $v) {
                $rows[] = $v;
            }

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

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

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

        return $response;
    }

    public function target() {

        $getParams = $this->request->get();
        
        $params = $this->validate(
            $getParams,
            [
                'id_triwulan' => ['validators' => ['required', 'digit']],
            ]
        );

        $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'];

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

        if ($params['id_triwulan']>8 or $params['id_triwulan']<1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Triwulan tidak ditemukan');
        }

        $where = '';
        $where .= (isset($_GET['id_instansi']) && $_GET['id_instansi'] != '') ? ' and id_instansi = '.$_GET['id_instansi'] : '';
        $where .= (isset($_GET['id_level1']) && $_GET['id_level1'] != '') ? ' and x.id_level1 = '.$_GET['id_level1'] : '';
        $where .= (isset($_GET['id_level2']) && $_GET['id_level2'] != '') ? ' and x.id_level2 = '.$_GET['id_level2'] : '';
        $where .= (isset($_GET['id_level3']) && $_GET['id_level3'] != '') ? ' and x.id_level3 = '.$_GET['id_level3'] : '';
        $where .= (isset($_GET['id_level4']) && $_GET['id_level4'] != '') ? ' and x.id_level4 = '.$_GET['id_level4'] : '';
        // $where .= (isset($_GET['id_level'.$lowlevel]) && $_GET['id_level'.$lowlevel] != '') ? ' and a.id_level'.$lowlevel.' = '.$_GET['id_level'.$lowlevel] : '';

        $nilaiTwFunction = $prd === '1' ? 'greatest' : 'latest_verification';

        $q = "with t1 as (
            select 
              a.id as id_master,
              --a.id_level".$lowlevel.",
              b.ord,
              coalesce(t.ord, 100) as ord_indikator,
              a.id_target, t.nama as target,
              b.level1, b.level2, b.level3, b.level4, 
              a.id_instansi, i.nama as instansi,
              case when $nilaiTwFunction(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1) = 100 
                or t.is_forced_complete
                then 1 else 0 end as is_complete,
              t.is_forced_complete as is_forced_complete,
              $nilaiTwFunction(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1) as nilai_verification,
              -- klo dia suudah lapor atau complete di tw manapun
              case when (klaim_tw".$params['id_triwulan']." is not null
                or $nilaiTwFunction(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1) = 100) 
                or t.is_forced_complete
                then 1 else 0 end as is_laportw, 
              --case when klaim_tw".$params['id_triwulan'].">0 and nilai_tw".$params['id_triwulan']." = 0 then 1 else 0 end as is_notverifytw, 
              
              -- kalau dia sudah verifikasi meskipun nilai 0, atau sudah complete di tw manapun
              --case when nilai_tw".$params['id_triwulan'].">0 then 1 else 0 end as is_verifytw, 
              case when (coalesce(v.verified_by,0)>0 
                or $nilaiTwFunction(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1) = 100) 
                or t.is_forced_complete
                then 1 else 0 end as is_verifytw, 

              --klaim_tw".$params['id_triwulan']." as klaim,
              nilai_tw".$params['id_triwulan']." as nilai_tw,
              coalesce(x.is_special,0) as is_special,
              y.label as target_type
              /*
              klaim_tw".$params['id_triwulan']." as klaim_tw,
              nilai_tw".$params['id_triwulan']." as nilai_tw
              --klaim_tw1, klaim_tw2, klaim_tw3, klaim_tw4, klaim_tw5, klaim_tw6, klaim_tw7, klaim_tw8, 
              --nilai_tw1, nilai_tw2, nilai_tw3, nilai_tw4, nilai_tw5, nilai_tw6, nilai_tw7, nilai_tw8
              */
            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 (select id_master, verified_by from jaga.str_target_log where id_triwulan=".$params['id_triwulan'].") v on v.id_master = a.id
              join jaga.str_target x on x.id = a.id_target
              join jaga.hierachy_by_level b on b.id_level".$lowlevel." = x.id_level".$lowlevel."
              left join jaga.str_target_type y on y.id = x.is_special
            where t.deleted_at is null and a.deleted_at is null and a.id_periode=".$prd.$where."
              and coalesce(t.start_triwulan,1) <= ".$params['id_triwulan']."
            )
            select * from t1
            order by ord_indikator asc, id_master asc
            ";
          // echo $q; die;
        $data = $this->rawQuery($q);
        
        $response = new \Phalcon\Http\Response();
        $response->setContentType('application/json', 'UTF-8');
        $response->setContent(json_encode([
          'success' => true,
          'data' => [ 'rows' => $data ]
        ]));

        return $response;
    }

    public function rekap() {

        $getParams = $this->request->get();
        
        $params = $this->validate(
            $getParams,
            [
                'id_triwulan' => ['validators' => ['required', 'digit']],
                'type' => ['validators' => []],
            ]
        );

        $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'];

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

        if ($params['id_triwulan']>8 or $params['id_triwulan']<1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Triwulan tidak ditemukan');
        }

        $where = '';
        $where .= (isset($_GET['id_instansi']) && $_GET['id_instansi'] != '') ? ' and id_instansi = '.$_GET['id_instansi'] : '';

        $k = array();
        for ($i=8; $i > 0; $i--) { 
          if ($i > $params['id_triwulan']) {
            $k[] = 'null';
          } else {
            $k[] = 'nilai_tw'.$i;
          }
      }
        $ck = implode(',', $k);
        $id_triwulan = $params['id_triwulan'];
        $instansi = "with t1 as (
            select 
              a.id_instansi, i.id_tipe, it.nama as tipe_instansi, i.nama as instansi, ".$params['id_triwulan']." as triwulan,
              count(a.id) as count_target,
              sum(case when jaga.latest_verification(".$ck.") = 100
                 or ( 
                  t.is_forced_complete
                  and jaga.get_max_id_triwulan_of_target(t.id) <= $id_triwulan
                 )
                then 1 else 0 end
                ) as count_complete,
              
              --sum(case when klaim_tw".$params['id_triwulan'].">0 then 1 else 0 end) as count_laportw, 
              sum(case when (klaim_tw".$params['id_triwulan']." is not null or jaga.latest_verification(nilai_tw8, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) = 100  
                or ( t.is_forced_complete
                  and jaga.get_max_id_triwulan_of_target(t.id) <= $id_triwulan
                )
              ) then 1 else 0 end) as count_laportw, 

              --sum(case when klaim_tw".$params['id_triwulan'].">0 and nilai_tw".$params['id_triwulan']." = 0 then 1 else 0 end) as count_notverifytw, 
              --sum(case when nilai_tw".$params['id_triwulan'].">0 then 1 else 0 end) as count_verifytw
              sum(case when (coalesce(v.verified_by,0)>0 or jaga.latest_verification(nilai_tw8, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) = 100 
                or ( t.is_forced_complete
                  and jaga.get_max_id_triwulan_of_target(t.id) <= $id_triwulan
                )
              ) then 1 else 0 end) as count_verifytw 
              
            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.hierachy_by_level b on b.id_level".$lowlevel." = t.id_level".$lowlevel."
              left join jaga.instansi_tipe it on it.id = i.id_tipe
              left join (select id_master, verified_by from jaga.str_target_log where id_triwulan=".$params['id_triwulan'].") v on v.id_master = a.id
            where t.deleted_at is null and a.deleted_at is null and a.id_periode=".$prd.$where."
            group by a.id_instansi, i.nama, i.id_tipe, it.nama 
            order by i.id_tipe, i.nama
            )
            select * from t1
            ";
        $subaksi = "with t1 as (
            select 
              b.id_level3 as id_subaksi, level3 as subaksi, 
              ".$params['id_triwulan']." as triwulan,
              count(a.id) as count_target,
              sum(case when jaga.latest_verification(".$ck.") = 100 
                or ( t.is_forced_complete
                  and jaga.get_max_id_triwulan_of_target(t.id) <= $id_triwulan
                )
              then 1 else 0 end) as count_complete,
              sum(case when (klaim_tw".$params['id_triwulan']." is not null or jaga.latest_verification(nilai_tw8, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) = 100
                or ( t.is_forced_complete
                  and jaga.get_max_id_triwulan_of_target(t.id) <= $id_triwulan
                ) 
              ) then 1 else 0 end) as count_laportw,
              --sum(case when klaim_tw".$params['id_triwulan'].">0 and nilai_tw".$params['id_triwulan']." = 0 then 1 else 0 end) as count_notverifytw, 
              sum(case when (coalesce(v.verified_by,0)>0 or jaga.latest_verification(nilai_tw8, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) = 100
                or ( t.is_forced_complete
                  and jaga.get_max_id_triwulan_of_target(t.id) <= $id_triwulan
                )
              ) then 1 else 0 end) as count_verifytw
            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.hierachy_by_level b on b.id_level".$lowlevel." = t.id_level".$lowlevel."
              left join jaga.instansi_tipe it on it.id = i.id_tipe
              left join (select id_master, verified_by from jaga.str_target_log where id_triwulan=".$params['id_triwulan'].") v on v.id_master = a.id
            where t.deleted_at is null and a.deleted_at is null and a.id_periode=".$prd.$where."
            group by b.id_level3, b.level3 
            )
            select * from t1
            ";

        $q = (isset($params['type']) && $params['type'] == 'subaksi') ? $subaksi : $instansi;

        // echo $q; die;
        $data = $this->rawQuery($q);

        // get K/L
        $kl = [];
        for($i = sizeof($data) - 1; $i >= 0; $i--) {
          if (array_key_exists('id_tipe', $data[$i])
              && $data[$i]['id_tipe'] === 3) {
            array_unshift($kl, $data[$i]);
            unset($data[$i]);
          }
        }
        
        $response = new \Phalcon\Http\Response();
        $response->setContentType('application/json', 'UTF-8');
        $response->setContent(json_encode([
          'success' => true,
          'data' => [ 'rows' => array_merge($kl, $data) ]
        ]));

        return $response;
    }
}