<?php 

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

class TargetRankMigration_264 extends Migration
{
    public function up()
    {
        self::$connection->execute("
            --[1]
            drop table if exists jaga.stranas_ranking;
            create table if not exists jaga.stranas_ranking (
                generate_time timestamp,
                id_instansi int4 NULL,
                instansi varchar(100) NULL,
                id_tipe int4 NULL,
                tipe_instansi varchar(100) NULL,
                ach numeric NULL,
                jml_subaksi int8 NULL,
                jml_target int8 NULL,
                nilai_akhir float NULL
            );

            drop function IF EXISTS jaga.generate_ranking();
            CREATE OR REPLACE FUNCTION jaga.generate_ranking()        
              RETURNS TABLE (cnt bigint) as
              $$
              BEGIN
                  TRUNCATE TABLE jaga.stranas_ranking;
                  insert into jaga.stranas_ranking
                  select 
                    CURRENT_TIMESTAMP as generate_time,
                    a.id_instansi,
                    b.nama as instansi, 
                    b.id_tipe, 
                    c.nama as tipe_instansi,
                    a.ach,
                    a.jml_subaksi,
                    a.jml_target,
                    a.nilai_akhir
                  from 
                    (
                    select 
                      id_instansi, 
                      sum(nilai_ontarget + nilai_terhutang) / (count(id_target) * 100.0) as ach, 
                      count(distinct id_subaksi) as jml_subaksi, 
                      count(id_target) as jml_target, 
                      sum(nilai_ontarget + nilai_terhutang) as nilai_akhir
                    from jaga.target_instansi 
                    group by id_instansi 
                    ) a 
                    join jaga.instansi b on b.id = a.id_instansi
                    left join jaga.instansi_tipe c on c.id = b.id_tipe
                  order by ach desc
                  ;
                RETURN QUERY
                select count(1) as cnt from jaga.stranas_ranking;
              RETURN;
              END;    
            $$ LANGUAGE plpgsql;
            ");
    }
}