<?php

use Phalcon\Mvc\Controller;

class StranasReportController extends BaseController
{
  public function index()
  { 
    $getParams = $this->request->get();
    $params = $this->validate(
      $getParams,
      [
        'id' => ['validators' => ['digit']],
        'type' => ['validators' => []],
        'target' => ['validators' => ['digit']],
        'front' => ['validators' => ['digit']],
      ]
    );

    $target = ( isset($params['target']) && $params['target'] !== 0) ? $params['target'] : 0;
    $isfront = ( isset($params['front']) && $params['front'] !== 0) ? $params['front'] : 0;
    $query = $this->getLog($isfront, 1, $params['type'], $params['id'], $target);
    // dd($query);
    $targets  = $this->rawQuery($query);
    $response = new \Phalcon\Http\Response();
    $response->setContentType('application/json', 'UTF-8');
    $response->setContent(json_encode([
      'success' => true,
      'data' => [ 'result' => $targets ]
    ]));

    return $response;
  }

  public function dashboard()
  { 
    $getParams = $this->request->get();
    $params = $this->validate(
      $getParams,
      [
        'type' => ['validators' => ['required']],
        'where' => ['validators' => []],
        'id' => ['validators' => ['digit']],
        'periode' => ['validators' => ['digit']],
        'isbreakdown' => ['validators' => ['digit']],
        'max_periode' => ['validators' => ['digit']],
        'whereinst' => ['validators' => ['digit']],
        'wheretipe' => ['validators' => ['digit']],
      ]
    );

    $type = ( isset($params['type']) && $params['type'] !== '') ? $params['type'] : '';
    $where = ( isset($params['where']) && $params['where'] !== '') ? $params['where'] : '';
    $id = ( isset($params['id']) && $params['id'] !== 0) ? $params['id'] : 0;
    $isbreakdown = ( isset($params['isbreakdown']) && $params['isbreakdown'] == 1) ? ', id_periode, periode' : '';

    $max_periode = ( isset($params['max_periode']) && $params['max_periode'] != '') ? ' and id_periode <= '. $params['max_periode'] : '';
    $periode = ( isset($params['periode']) && $params['periode'] != '') ? ' and id_periode = '.$params['periode'] : '';
    $whereinst = ( isset($params['whereinst']) && $params['whereinst'] != '') ? ' and id_instansi = '.$params['whereinst'] : '';
    $wheretipe = ( isset($params['wheretipe']) && $params['wheretipe'] != '') ? ' and id_tipe = '.$params['wheretipe'] : '';

    $wh = ($where == '') ? '' : ' and id_'.$where .' = '.$id;
    $typeinst = $joininst = $groupinst = '';
    if($type == 'instansi') {
      $typeinst = ', tipe, id_tipe';
    }

    $query = "
    select id_$type, $type $isbreakdown $typeinst,
      sum(nilai_ontarget) / (count(id_target) * 100.0) as pct_ontarget,
      sum(nilai_terhutang) / (count(id_target) * 100.0) as pct_terhutang,
      sum(nilai_ontarget + nilai_terhutang) / (count(id_target) * 100.0) as pct_total
    from jaga.target_instansi_hist
    where 1=1 $wh $periode $max_periode $whereinst $wheretipe
    group by id_$type, $type $isbreakdown $typeinst";
    if($isbreakdown == 1) $query .= "order by id_periode";

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


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

    return $response;
  }

  public function getListBobot() {
    $query = $this->getBobot();
    $targets  = $this->rawQuery($query);

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

    return $response;
  }

  public function getTop10Achievement()
  {
    $q1  = "
      select * from jaga.stranas_ranking
      order by ach desc 
      ";
    $top10  = $this->rawQuery($q1);

    $response = new \Phalcon\Http\Response();
    $response->setContentType('application/json', 'UTF-8');

    $response->setContent(json_encode([
      'success' => true,
      'data' => [ 'top10' => $top10 ]
    ]));

    return $response;
  }

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


    $query = $this->getLog(0, 0, 'instansi', $params['id_instansi'], $params['id_target']);
    $target = $this->rawQuery($query);

    $data = [];
    foreach($target as $t) {

      $ans = $comm = [];
      if( $t['id_answer'] && $t['id_answer'] !== '') {
        // $answer
        $answer = $this->rawQuery('select a.*, u.nama as answer_by, v.nama as verified_by from jaga.stranas_survey_answer a 
            left join jaga.um_user u on u.um_id = a.created_by 
            left join jaga.um_user v on v.um_id = a.verified_by 
            where id = '.$t['id_answer']);

        $answerattch = $this->rawQuery('select a.*, c.uuid, c.filename, c.keterangan, c.metadata from jaga.stranas_survey_answer_attachment a join jaga.attachments c on c.id = a.id_attachment where id_answer = '.$t['id_answer']);
        foreach ($answer as $a) {
          $a['attachments'] = $answerattch;
          $ans = $a;
        }

        // comments
        $comment = $this->rawQuery('select a.*, u.nama as comment_by from jaga.stranas_survey_answer_comment a 
              left join jaga.um_user u on u.um_id = a.created_by where id_answer = '.$t['id_answer']);
        foreach ($comment as $a) {
          $commentattch = $this->rawQuery('select uuid, filename,keterangan, metadata from jaga.stranas_survey_answer_comment_attachment a join jaga.attachments c on c.id = a.id_attachment 
                  where id_comment = '.$a['id']);

          $a['attachments'] = $commentattch;
          $comm[] = $a;
        }
      }

      $t['answer'] = $ans;
      $t['comments'] = $comm;
      $data[] = $t;
    }

    // $targets = StranasTargetLogWithAttachment::find([
    //   'conditions' => 'id_instansi = :id_instansi: and id_target = :id_target:',
    //   'bind' => [
    //     'id_instansi' => $params['id_instansi'],
    //     'id_target' => $params['id_target'],
    //   ]
    // ]);

    $hierarchy = StranasTargetHierarchy::find([
      'conditions' => 'id_target = :id_target:',
      'bind' => [
        'id_target' => $params['id_target']
      ]
    ]);

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

    return $response;
  }

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

    $targets = StranasTargetLog::find([
      'conditions' => 'id_instansi = :id_instansi: and id_target = :id_target:',
      'bind' => [
        'id_instansi' => $params['id_instansi'],
        'id_target' => $params['id_target'],
      ]
    ]);

    $hierarchy = StranasTargetHierarchy::find([
      'conditions' => 'id_target = :id_target:',
      'bind' => [
        'id_target' => $params['id_target']
      ]
    ]);

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

    return $response;
  }

  public function generateDashboard()
  {
    $this->checkScopes(['stranas.admin']);
    $res  = $this->rawQuery('select jaga.generate_historical()');

    $response = new \Phalcon\Http\Response();
    $response->setContentType('application/json', 'UTF-8');
    $response->setContent(json_encode([
      'success' => true,
      'data' => [ 'result' => $res ]
    ]));
    return $response;
  }
  public function generateAchievement()
  {
    $this->checkScopes(['stranas.admin']);
    $res  = $this->rawQuery('select jaga.generate_ranking()');

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

  public function genExcel() {
    $getParams = $this->request->get();
    $params = $this->validate(
      $getParams,
      [
        'whereinst' => ['validators' => ['digit']],
        'xls' => ['validators' => ['digit']],
      ]
    );

    $xls = ( isset($params['xls']) && $params['xls'] != '') ? $params['xls']  : 0;

    $whereinst = ( isset($params['whereinst']) && $params['whereinst'] != '') ? ' and id_instansi = '.$params['whereinst'] : '';
    $query = "select 
      generate_time,instansi,tipe,target,subaksi,aksi,fokus,periode,periode_berjalan,
      nilai_ontarget,nilai_terhutang,nilai_akhir,is_verified,is_completed,
      id_instansi,id_tipe,id_target,id_subaksi,id_aksi,id_fokus,id_periode,id_periode_berjalan,id_question,id_survey
    from jaga.target_instansi_hist where 1=1 $whereinst";
    $res  = $this->rawQuery($query);


    if($xls == 0) {
      $response = new \Phalcon\Http\Response();
      $response->setContentType('application/json', 'UTF-8');
      $response->setContent(json_encode([
        'success' => true,
        'data' => [ 'result' => $res ]
      ]));
      return $response;
    } else {
      $date = date("YmdHis");
      $filename = "stranas_report_$date.xls"; 
      header("Content-Disposition: attachment; filename=\"$filename\"");
      header("Content-Type: application/vnd.ms-excel");
      $i = 0;
      foreach ($res as $k => $v) {
        if ($i == 0) {
          echo implode("\t", array_keys($v)) . "\r\n";
        }
        echo implode("\t", array_values($v)) . "\r\n";
        $i++;
      }
    }
  }

  public function genNewPeriod() {
    $this->checkScopes(['stranas.admin']);
    $getParams = $this->request->get();
    $params = $this->validate(
      $getParams,
      [
        'periode' => ['validators' => ['required', 'digit']],
      ]
    );

    $response = new \Phalcon\Http\Response();
    $response->setContentType('application/json', 'UTF-8');

    $periode = ( isset($params['periode']) && $params['periode'] !== 0) ? $params['periode'] : 0;
    // check periode status
    $check  = $this->rawQuery('select id from jaga.stranas_periode where current_timestamp <= ended_at and current_timestamp >= started_at');
    
    $p = array();
    foreach ($check as $v) {
      $p[] = $v['id'];
    }

    if (!in_array($periode, $p)) {
      $response->setContent(json_encode([
        'success' => false,
        'msg' => 'periode not valid'
      ]));

      return $response;
    }

    // if ok run
    $q1  = $this->rawQuery('truncate table jaga.temp_newtarget');
    $query = 'with t0 as (
          select *, row_number () over (partition by id_survey, id_question order by verified_at desc) as selected
          from jaga.stranas_survey_answer
      ),
      tver as (
          select id_survey, id_question, nilai_verification  
          from t0 where selected = 1
      ),
      tdaftar as (
      select 
        a.id_instansi_satker as id_instansi,
        a.id_periode,
        a.id as id_survey,
        a.id_survey_template,
        b.id_target,
        coalesce(v.nilai_verification,0) as nilai_verification
      from jaga.stranas_survey a 
        join jaga.stranas_survey_template_question b on b.id_survey_template = a.id_survey_template
        left join tver v on v.id_survey = a.id and v.id_question = b.id_target 
      ),
      tkemaren as (
      select *, nilai_verification as verifikasi_sebelum from tdaftar where id_periode = '.($periode - 2).'
      ),
      tsekarang as (
      select *, nilai_verification as verifikasi_terakhir from tdaftar where id_periode = '.($periode - 1).'
      ),
      ttarget as (
      select *, nilai_verification as verifikasi_terakhir from tdaftar where id_periode = '.$periode.'
      ),
      tsurv as (
      select distinct id_periode, id_survey_template, id_survey, id_instansi from ttarget
      ),
            tcarryover as (
      select 
        a.id_periode,
        a.id_instansi,
        a.id_survey,
        a.id_survey_template,
        a.id_target,
        a.verifikasi_terakhir
      from tsekarang a 
        left join tkemaren b on b.id_target = a.id_target
      ),
      tnew as (
      select a.id_instansi, a.id_periode, '.$periode.' as new_periode, a.id_survey, a.id_survey_template, a.id_target, 
      b.id_target as target_check, 
      a.verifikasi_terakhir, 
      coalesce(c.id_survey, b.id_survey) as new_id_survey,
      coalesce(c.id_survey_template, b.id_survey_template) as new_id_survey_template
      from tcarryover a 
        left join ttarget b on b.id_instansi = a.id_instansi and b.id_target = a.id_target
        left join tsurv c on c.id_instansi = a.id_instansi 
      where a.verifikasi_terakhir < 100 
      ),
      tall as (select distinct * from tnew)
      insert into jaga.temp_newtarget 
      select * 
      from tall where target_check is null';

    $q2  = $this->rawQuery($query);
    usleep(20000);
    $q21  = $this->rawQuery("select a.*, 
            b.nama || ' - ' || p.nama as title,
            coalesce(new_id_survey,0) as new_id_survey, 
            coalesce(new_id_survey_template,0) as new_id_survey_template 
          from jaga.temp_newtarget a join jaga.instansi b on b.id = a.id_instansi 
          join jaga.stranas_periode p on p.id = a.new_id_periode 
          order by id_instansi");

    $q3  = $this->rawQuery('select max(id) from jaga.stranas_survey');
    $q4  = $this->rawQuery('select max(id) from jaga.stranas_survey_template');
    $q5  = $this->rawQuery('select max(id) from jaga.stranas_survey_template_question');

    $newidsurvey   = $q3[0]['max'];
    $newidtemplate = $q4[0]['max'];
    $newidquest    = $q5[0]['max'];
    $createdby     = 1; //$this->shared->user->um_id;


    $idinst = 0;
    $target = $detail = [];
    foreach ($q21 as $v) {
      if($v['new_id_survey'] == 0 && $idinst !== $v['id_instansi']) {
        $newidsurvey++;
        $newidtemplate++;
      }
      
      $target[$v['id_instansi']]['title'] = $v['title'];
      $target[$v['id_instansi']]['id_survey'] = ($v['new_id_survey'] == 0) ? $newidsurvey : $v['new_id_survey'];
      $target[$v['id_instansi']]['id_survey_template'] =  ($v['new_id_survey_template'] == 0) ? $newidtemplate : $v['new_id_survey_template'];
      $target[$v['id_instansi']]['detail'][] = $v;
    }
    // dd($target);

    foreach ($target as $k => $v) {
      // survey_template

      $newidx = ($v['id_survey_template']) ? $v['id_survey_template'] : $newidtemplate + 1;
      $qsurveytemplate  = 'insert into jaga.stranas_survey_template (id, nama, created_at, created_by) values 
        ('.$newidx.', \''.$v['title'].'\', current_timestamp, '.$createdby.');';
      // dd($qsurveytemplate);
      // survey
      $newids = ($v['id_survey']) ? $v['id_survey'] : $newidsurvey + 1;
      $qsurvey = 'insert into jaga.stranas_survey (id, id_periode, id_instansi_satker, id_survey_template, created_at, created_by) values 
        ('.$newids.', '.$periode.', '.$k.', '.$v['id_survey_template'].', current_timestamp, '.$createdby.');';
 
      $quest = 'insert into jaga.stranas_survey_template_question (id, id_survey_template , id_target, created_at, created_by ) values'; 
      $q = [];
      foreach($v['detail'] as $x) {
        $newidquest++;
        $q[] = '('.$newidquest.', '.$newidx.', '.$x['id_target'].', current_timestamp, '.$createdby.')'; 
      }
      $quest .= implode(',', $q);
     
      //
      if(!($v['id_survey_template'])) $this->rawQuery($qsurveytemplate); 
      if(!($v['id_survey'])) $this->rawQuery($qsurvey); 
      $this->rawQuery($quest); 
    }

    $count = $this->rawQuery('select count(1) from jaga.temp_newtarget');
    // yg udah ada
    $response->setContent(json_encode([
      'success' => true,
      'data' => [ 'result' => $count ]
    ]));

    return $response;
  }

  private function getBobot() {
    $query = "
      with t1 as (
      select id_instansi, id_periode, id_target, id_subaksi, nilai_ontarget, nilai_akhir
      from jaga.target_instansi_hist
      ),
      tins as (
      select 
        id_instansi,
        id_periode, 
        id_subaksi,
        count(1) as jumlah_target,
        sum(nilai_ontarget) as nilai_ontarget,
        sum(nilai_akhir) as nilai_akhir,
        count(1)*100 as nilai_full
      from t1 group by id_instansi, id_periode, id_subaksi
      ),
      tweight as (
      select id_instansi, count(distinct id_periode) as t, 1/(count(distinct id_periode)*1.0) as bobot from tins group by id_instansi
      ),
      tmaxperiod as (
      select max(id_periode) as maxperiod from t1 where nilai_ontarget > 0
      ),
      tres as (
      select 
        a.id_instansi, 
        a.id_periode,
        count(distinct id_subaksi) as jumlah_subaksi,
        sum(jumlah_target) as jumlah_target,
        round(((sum(nilai_ontarget) * bobot) / sum(nilai_full)) * 100,2) as nilai_ontarget,
        round(((sum(nilai_akhir-nilai_ontarget)  * bobot) / sum(nilai_full)) * 100,2) as nilai_terhutang,
        round(((sum(nilai_akhir)  * bobot) / sum(nilai_full)) * 100,2) as nilai_achievement,
        round(((sum(nilai_full) * bobot) / sum(nilai_full)) * 100,2) as nilai_total
      from tins a 
        left join tweight b on b.id_instansi = a.id_instansi
      group by  a.id_instansi, a.id_periode, bobot
      )
      select 
        a.id_instansi, 
        i.nama as instansi,
        i.id_tipe,
        t.nama as tipe_instansi,
        --sum(jumlah_target) as jumlah_target,
        --sum(jumlah_subaksi) as jumlah_subaksi,
        x.jumlah_target,
        x.jumlah_subaksi,
        count(1) as count_periode,
        sum(nilai_ontarget) as nilai_ontarget, 
        sum(nilai_terhutang) as nilai_terhutang, 
        sum(nilai_achievement) as nilai_achievement, 
        sum(nilai_total) - sum(nilai_achievement) as nilai_selisih,
        sum(nilai_total) as nilai_total
      from tres  a 
        join (select id_instansi, count(1) as jumlah_target, count(distinct id_subaksi) as jumlah_subaksi from tins 
          group by id_instansi) x on x.id_instansi = a.id_instansi
        join tmaxperiod p on 1=1
        join jaga.instansi i on i.id = a.id_instansi
        join jaga.instansi_tipe t on t.id = i.id_tipe
      where a.id_periode <= p.maxperiod
      group by a.id_instansi, i.nama, i.id_tipe, t.nama,
        x.jumlah_target,
        x.jumlah_subaksi
    ";

    return $query;
  }

  private function getLog($isfront = 0, $islog = 0, $type = '', $id, $target = 0) {

    $where = '';
    switch ($type) {
      case 'target':
        $where = ($isfront == 1) ? ' and id_target = '.$id : ' and q.id_target = '.$id;
        break;
      case 'instansi':
        $where = ($isfront == 1) ? ' and id_instansi = '.$id : ' and ss.id_instansi_satker = '.$id;
        break;
      case 'fokus':
      case 'aksi':
      case 'subaksi':
        $where = ($isfront == 1) ? ' and id_'.$type.' = '.$id : ' and h.id_'.$type.' = '.$id;
        break;
      default:
        $where = '';
    }
 
    if($isfront == 1) {
      return "select * from jaga.target_instansi_hist where 1=1 $where";
    }
    $target = ($target !== 0) ? ' and q.id_target = '.$target : '';

    $log = "
      with t0 as (
          select *, row_number () over (partition by id_survey, id_question order by verified_at desc) as selected
          from jaga.stranas_survey_answer
      ),
      tver as (
          select * from t0 where selected = 1
      ),
      t1 as (
      select 
          ss.id_instansi_satker as id_instansi, 
          ii.nama as instansi,
          p.nama as periode, 
          ta.keterangan as target,
          coalesce(c.status, 0) as status,
          c.created_by,
          c.created_at,
          c.updated_by,
          c.updated_at,
          c.verified_by,
          c.verified_at,
          coalesce(nilai, 0) as nilai_klaim,
          coalesce(nilai_verification, 0) as nilai_verification,
          q.id_target, 
          h.id_fokus,
          h.id_aksi,
          h.id_subaksi,
          q.id as id_question,
          ss.id as id_survey,
          p.id as id_periode,
          c.id as id_answer, 
          row_number() over (partition by q.id_target, ss.id_instansi_satker order by p.id asc) as target_period,
          row_number() over (partition by q.id_target, ss.id_instansi_satker order by p.id desc) as last_period
      from jaga.stranas_survey ss
          join jaga.stranas_periode p on ss.id_periode = p.id
          join jaga.stranas_survey_template t on ss.id_survey_template = t.id
          join jaga.stranas_survey_template_question q on q.id_survey_template = t.id 
          left join tver c on c.id_survey  = ss.id and c.id_question = q.id_target 
          join (select * from jaga.stranas_target where deleted_at is null) ta on ta.id = q.id_target 
          join jaga.instansi ii on ii.id = ss.id_instansi_satker
          join jaga.target_hierarchy h on h.id_target = q.id_target
      where 1=1 
        $where $target
      ),
      tperiode as (
      select id_instansi, id_target, id_periode  from t1 where target_period = 1
      ),
      tsrc as (
      select 
          --id 
          a.id_instansi,
          a.id_target,
          a.id_subaksi,
          b.id_periode as ori_id_periode,
          a.id_periode,
          id_survey,
          id_question,
          id_answer,
          --status pelaporan
          status,
          nilai_klaim,
          nilai_verification,
          a.created_by,
          coalesce(c.nama, '') as created_name,
          a.created_at,
          case when a.created_at is not null then 1 else 0 end as is_submitted,
          a.updated_by,
          a.updated_at,
          a.verified_by,
          coalesce(v.nama, '') as verified_name,
          a.verified_at,
          instansi,
          target,
          periode,
          -- flagging
          target_period,
          last_period
      from t1 a 
          join tperiode b on b.id_instansi = a.id_instansi and b.id_target = a.id_target
          left join jaga.um_user c on c.um_id = a.created_by
          left join jaga.um_user v on v.um_id = a.verified_by
      )
    ";

    $sum = $log." ,
      tonperiod as (
          select * from tsrc where target_period = 1
      ),
      tlast_verify as (
          select *, row_number() over (partition by id_target, id_instansi order by id_periode desc) as last_period2
          from tsrc 
          where target_period <> 1
      ),
      tlast as (
          select *, case when verified_at is not null or status = 1 then 1 else 0 end as is_verified from tlast_verify where last_period2 = 1
      ),
      tmax as (
          select *, row_number() over (partition by id_target, id_instansi order by nilai_verification desc) as max_nilai
          from tsrc --where ori_id_periode <> id_periode
      ),
      tmaxone as (
          select * from tmax where max_nilai = 1
      ),
      tins as (
      select id_instansi, id_target, count(1) as count_log from tsrc group by id_instansi, id_target
      ),
      tsum as (
      select
          a.id_instansi,
          a.id_target,
          a.id_subaksi,
          a.ori_id_periode as id_periode,
          a.id_question,
          a.id_survey,
          a.status,
          case when i.count_log = 1 then a.verified_at else b.verified_at end as verified_at,
          case when i.count_log = 1 then a.created_at else b.created_at end as created_at,
          --label
          a.instansi,
          a.target,
          a.periode,
          coalesce(b.id_periode, a.ori_id_periode) as id_periode_berjalan,
          p.nama as periode_berjalan,
          a.nilai_verification as nilai_ontarget,
          coalesce(b.nilai_verification,0) as nilai_terakhir,
          coalesce(m.nilai_verification,0) as nilai_max,
          case 
              when coalesce(m.nilai_verification,0) > coalesce(b.nilai_verification,0)
              then coalesce(m.nilai_verification,0)
              else coalesce(b.nilai_verification,0)
          end as nilai_akhir
      from tonperiod a 
          left join tlast b on b.id_instansi = a.id_instansi and b.id_target = a.id_target
          left join jaga.stranas_periode p on p.id = coalesce(b.id_periode, a.ori_id_periode)
          left join tmaxone m on m.id_instansi = a.id_instansi and m.id_target = a.id_target
          left join tins i on i.id_instansi = a.id_instansi and i.id_target = a.id_target
      ),
      tfinal as (
      select 
          a.*, 
          case when a.created_at is not null then 1 else 0 end as is_submitted,
          case when a.verified_at is not null then 1 else 0 end as is_verified,
          b.nama as subaksi,
          case 
              -- jika periode akhir dan periode target sama, maka tidak ada terhutang
              when id_periode = id_periode_berjalan then 0
              -- jika max value = 0 maka tidak ada terhutan
              when nilai_max = 0 then 0
              else nilai_max - nilai_ontarget 
          end as nilai_terhutang,
          case when nilai_ontarget = 100 or nilai_max = 100 then 1 else 0 end is_completed
      from tsum a 
      join jaga.stranas_subaksi b on b.id = a.id_subaksi
      )
    ";


    $query = ($islog == 0) ? $log.' select * from tsrc' : $sum.' select * from tfinal';
    return $query;
  }

}