<?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\db\Query;

/**
 * class untuk data peta tematik
 */
class Tematik extends Model
{
    /**
     * return json
     * berfungsi untuk menampilkan jenis_bumi
     */
    function getJenisBumi()
    {
        $connection = \Yii::$app->db;
        $model = $connection->createCommand('SELECT row_to_json(fc)
        FROM (SELECT \'FeatureCollection\' AS type, 
            (SELECT crs FROM 
                (SELECT \'name\' AS type, 
                     (SELECT properties 
                        FROM (SELECT \'EPSG:32751\' AS name) AS properties)
            ) AS crs),
              array_to_json(array_agg(f)) As features
           FROM (SELECT \'Feature\' As type
            , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
            , row_to_json(lp) As propertiestable
            FROM public.kelurahan As lg 
         LEFT JOIN (SELECT jns_bumi,
                     case when jns_bumi = \'1\' then \'#FF0000\'
                        when jns_bumi = \'2\' then \'#1AFF00\'
                        when jns_bumi = \'3\' then \'#0D00FF\'
                        when jns_bumi = \'4\' then \'#FF00EA\'
                        else \'#FFFFFF\'
                    end as color, kd_propinsi,kd_dati2,kd_kecamatan,kd_kelurahan,kd_blok,no_urut,kd_jns_op FROM pbb.dat_op_bumi) As lp 
                    ON lp.kd_propinsi||\'\'||lp.kd_dati2||\'\'||lp.kd_kecamatan||\'\'||lp.kd_kelurahan||\'\'||lp.kd_blok||\'\'||lp.no_urut||\'\'||lp.kd_jns_op = lg.d_nop ) As f ) As fc;');
        
        $jns_bumi = $model->queryAll();

        return $jns_bumi;
    }

    public function LegendJnsBumi(){
        $connection = \Yii::$app->db;
        $model = $connection->createCommand('
            select a.jns_bumi,count(a.*) as total,
            case when a.jns_bumi = \'1\' then \'#FF0000\'
                  when a.jns_bumi = \'2\' then \'#1AFF00\'
                  when a.jns_bumi = \'3\' then \'#0D00FF\'
                  when a.jns_bumi = \'4\' then \'#FF00EA\'
             end as color, b.nama
            from
            pbb.dat_op_bumi a inner join pbb.ref_jns_tanah b 
            on a.jns_bumi = b.kode
            group by a.jns_bumi, b.nama
            union all
                select \'5\' as jns_bumi, count(*) as total, \'#FFFFFF\' as color, \'TIDAK ADA DATA\' as nama
                from
                 pbb.dat_op_bumi a 
                 right join public.kelurahan b
                 on a.kd_propinsi||\'\'||a.kd_dati2||\'\'||a.kd_kecamatan||\'\'||a.kd_kelurahan||\'\'||a.kd_blok||\'\'||a.no_urut||\'\'||a.kd_jns_op = b.d_nop
                 where a.jns_bumi is null
        ');
        
        $result = $model->queryAll();
        
        return $result;
    }

    /**
     * return json
     * berfungsi untuk menampilkan ketetapan perbuku
     */
    function getKetetapanPerBuku()
    {
        $connection = \Yii::$app->db;
        $model = $connection->createCommand('SELECT row_to_json(fc)
        FROM (SELECT \'FeatureCollection\' AS type, 
            (SELECT crs FROM 
                (SELECT \'name\' AS type, 
                     (SELECT properties 
                        FROM (SELECT \'EPSG:32751\' AS name) AS properties)
            ) AS crs),
              array_to_json(array_agg(f)) As features
           FROM (SELECT \'Feature\' As type
            , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
            , row_to_json(lp) As propertiestable
            FROM public.kelurahan As lg 
         LEFT JOIN (SELECT pbb_yg_harus_dibayar_sppt,
                     case when (pbb_yg_harus_dibayar_sppt > \'0\' and pbb_yg_harus_dibayar_sppt <= \'100000\') then \'#ff0000\'
                            when (pbb_yg_harus_dibayar_sppt > \'100000\' and pbb_yg_harus_dibayar_sppt <= \'500000\') then \'#eeff00\'
                            when (pbb_yg_harus_dibayar_sppt > \'500000\' and pbb_yg_harus_dibayar_sppt <= \'2000000\') then \'#48ff00\'
                            when (pbb_yg_harus_dibayar_sppt > \'2000000\' and pbb_yg_harus_dibayar_sppt <= \'5000000\') then \'#707320\'
                            when (pbb_yg_harus_dibayar_sppt > \'5000000\') then \'#00f7ff\'
                            else \'#ffffff\'
                       end as color, kd_propinsi,kd_dati2,kd_kecamatan,kd_kelurahan,kd_blok,no_urut,kd_jns_op FROM pbb.sppt where thn_pajak_sppt = \'2017\') As lp 
                    ON lp.kd_propinsi||\'\'||lp.kd_dati2||\'\'||lp.kd_kecamatan||\'\'||lp.kd_kelurahan||\'\'||lp.kd_blok||\'\'||lp.no_urut||\'\'||lp.kd_jns_op = lg.d_nop ) As f ) As fc;');
        
         
        $ketetapan_perbuku = $model->queryAll();

        return $ketetapan_perbuku;
    }

    public function LegendKetetapanPerBuku($tahun){
        $tahun = $tahun != null ? $tahun : date('Y');
        
        $connection = \Yii::$app->db;
        $model = $connection->createCommand('
            SELECT \'Buku I\' AS NAMA, \'#ff0000\' AS COLOR, COUNT(*)AS TOTAL FROM pbb.SPPT
            WHERE 
            KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
            PBB_YG_harus_DIBAYAR_SPPT BETWEEN 1 AND 100000 AND
            THN_PAJAK_SPPT = \''.$tahun.'\'
            UNION ALL
            SELECT \'Buku II\' AS NAMA, \'#eeff00\' AS COLOR, COUNT(*)AS TOTAL FROM pbb.SPPT
            WHERE 
            PBB_YG_harus_DIBAYAR_SPPT BETWEEN 100001 AND 500000 AND
            THN_PAJAK_SPPT = \''.$tahun.'\'
            UNION ALL
            SELECT \'Buku III\' AS NAMA, \'#48ff00\' AS COLOR, COUNT(*)AS TOTAL FROM pbb.SPPT
            WHERE 
            PBB_YG_harus_DIBAYAR_SPPT BETWEEN 500001 AND 2000000 AND
            THN_PAJAK_SPPT = \''.$tahun.'\'
            UNION ALL
            SELECT \'Buku IV\' AS NAMA, \'#707320\' AS COLOR, COUNT(*)AS TOTAL FROM pbb.SPPT
            WHERE 
            PBB_YG_harus_DIBAYAR_SPPT BETWEEN 2000001 AND 5000000 AND
            THN_PAJAK_SPPT = \''.$tahun.'\'
            UNION ALL
            SELECT \'Buku V\' AS NAMA, \'#00f7ff\' AS COLOR, COUNT(*)AS TOTAL FROM pbb.SPPT
            WHERE 
            PBB_YG_harus_DIBAYAR_SPPT > 5000000 AND
            THN_PAJAK_SPPT = \''.$tahun.'\'
            UNION ALL
            SELECT \'Ketetapan Nol\' AS NAMA, \'#ffffff\' AS COLOR,COUNT(*) AS TOTAL FROM pbb.SPPT
            WHERE
            THN_PAJAK_SPPT = \''.$tahun.'\'
            AND PBB_YG_harus_DIBAYAR_SPPT = 0
        ');
        
        $result = $model->queryAll();
        
        return $result;
    }

    /**
     * return json
     * berfungsi untuk menampilkan ketetapan perbuku
     */
    function getNilaiIndividu()
    {
        $connection = \Yii::$app->db;
        $model = $connection->createCommand('SELECT row_to_json(fc)
                        FROM (SELECT \'FeatureCollection\' AS type, 
                            (SELECT crs FROM 
                                (SELECT \'name\' AS type, 
                                    (SELECT properties 
                                        FROM (SELECT \'EPSG:32751\' AS name) AS properties)
                            ) AS crs),
                            array_to_json(array_agg(f)) As features
                        FROM (SELECT \'Feature\' As type
                            , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
                            , row_to_json(lp) As propertiestable
                            FROM public.kelurahan As lg 
                        LEFT JOIN (SELECT KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT, KD_JNS_OP,COLOR
                FROM (SELECT DISTINCT
                        KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT,KD_JNS_OP,
                        \'#3CEDF0\' AS COLOR	
                        FROM
                        pbb.DAT_OP_BANGUNAN WHERE NILAI_SISTEM_BNG IS NOT NULL
                        UNION ALL
                        (
                            SELECT
                            DISTINCT KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT,KD_JNS_OP,
                            \'#E7F03C\' AS COLOR
                            FROM
                                pbb.DAT_OBJEK_PAJAK
                            EXCEPT
                            SELECT DISTINCT
                                KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT,KD_JNS_OP,
                            \'#E7F03C\' AS COLOR
                            FROM
                                pbb.DAT_OP_BANGUNAN WHERE NILAI_SISTEM_BNG IS NOT NULL
                        )
                    )A) As lp 
                                    ON lp.kd_propinsi||\'\'||lp.kd_dati2||\'\'||lp.kd_kecamatan||\'\'||lp.kd_kelurahan||\'\'||lp.kd_blok||\'\'||lp.no_urut||\'\'||lp.kd_jns_op = lg.d_nop ) As f ) As fc;
                        ');
        
         
        $nilai_individu = $model->queryAll();

        return $nilai_individu;
    }

     //untuk informasi legend nilai individu
     public function LegendNilaiIndividu(){

        $connection = \Yii::$app->db;
        $model = $connection->createCommand('
                SELECT \'Sudah Dinilai Individu\' AS NAMA, \'#3CEDF0\' AS COLOR,COUNT(*) AS TOTAL
                FROM
                pbb.DAT_OP_BANGUNAN
                WHERE  NILAI_SISTEM_BNG is not null
                UNION ALL
                    SELECT \'Belum Dinilai Individu\' AS NAMA,
                                \'#E7F03C\' AS COLOR,COUNT(*) AS TOTAL
                    FROM(
                        SELECT
                            DISTINCT KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT,KD_JNS_OP
                        FROM
                            pbb.DAT_OBJEK_PAJAK
                        
                EXCEPT
                SELECT DISTINCT
                        KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT,KD_JNS_OP
                FROM
                        pbb.DAT_OP_BANGUNAN
                WHERE NILAI_SISTEM_BNG is not null)A
        ');
        
        $result = $model->queryAll();
        
        return $result;
    }

    function getStatusPembayaran($tahun)
    {
        $tahun = $tahun != null ? $tahun : date('Y');

        $connection = \Yii::$app->db;
        $model = $connection->createCommand('SELECT row_to_json(fc)
                        FROM (SELECT \'FeatureCollection\' AS type, 
                            (SELECT crs FROM 
                                (SELECT \'name\' AS type, 
                                    (SELECT properties 
                                        FROM (SELECT \'EPSG:32751\' AS name) AS properties)
                            ) AS crs),
                            array_to_json(array_agg(f)) As features
                        FROM (SELECT \'Feature\' As type
                            , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
                            , row_to_json(lp) As propertiestable
                            FROM public.kelurahan As lg 
                        LEFT JOIN (SELECT A.*
                        FROM (
                                SELECT 
                                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP,
                                        \'Tidak Ada SPPT\' AS NAMA, \'#FFFFFF\' AS COLOR
                                FROM
                                        pbb.DAT_OBJEK_PAJAK
                                EXCEPT
                                SELECT
                                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP,
                                        \'Tidak Ada SPPT\' AS NAMA, \'#FFFFFF\' AS COLOR
                                FROM
                                        pbb.SPPT
                                WHERE
                                THN_PAJAK_SPPT = \''.$tahun.'\'
                                UNION ALL
                                SELECT
                                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP,
                                        \'Belum Bayar\' AS NAMA, \'#ff0000\' AS COLOR
                                FROM
                                        pbb.SPPT
                                WHERE
                                THN_PAJAK_SPPT = \''.$tahun.'\'
                                EXCEPT
                                SELECT
                                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP,
                                        \'Belum Bayar\' AS NAMA, \'#ff0000\' AS COLOR
                                FROM
                                        pbb.PEMBAYARAN_SPPT
                                WHERE
                                THN_PAJAK_SPPT = \''.$tahun.'\'
                                UNION ALL 
                                SELECT
                                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP,
                                        \'Sudah Bayar\' AS NAMA, \'#24ff00\' AS COLOR
                                FROM
                                        pbb.PEMBAYARAN_SPPT
                                
                                WHERE THN_PAJAK_SPPT = \''.$tahun.'\') A ) As lp 
                                ON lp.nop = lg.d_nop ) As f ) As fc;
                        ');
        
         
        $status_pembayaran = $model->queryAll();

        return $status_pembayaran;
    }

    public function LegendStatusPembayaran($tahun){
        $tahun = $tahun != null ? $tahun : date('Y');

        $connection = \Yii::$app->db;
        $model = $connection->createCommand('
                SELECT \'Tidak Ada SPPT\' AS NAMA,\'#ffffff\' AS COLOR,
                        COUNT(*) AS TOTAL
                FROM(
                SELECT
                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP
                FROM
                        pbb.DAT_OBJEK_PAJAK
                EXCEPT
                SELECT
                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP
                FROM
                        pbb.SPPT
                WHERE
                    THN_PAJAK_SPPT = \''.$tahun.'\')A
                UNION ALL
                SELECT \'Belum Bayar\' AS NAMA,\'#ff0000\' AS COLOR,
                        COUNT(*) AS TOTAL
                FROM(
                SELECT
                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP
                FROM
                        pbb.SPPT
                WHERE
                    THN_PAJAK_SPPT = \''.$tahun.'\'
                EXCEPT
                SELECT
                        DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP
                FROM
                        pbb.PEMBAYARAN_SPPT
                WHERE
                THN_PAJAK_SPPT = \''.$tahun.'\')B
                UNION ALL
                SELECT \'Sudah Bayar\' AS NAMA,\'#24ff00\' AS COLOR,
                        COUNT(*) AS TOTAL
                FROM
                        pbb.PEMBAYARAN_SPPT
                WHERE
                THN_PAJAK_SPPT = \''.$tahun.'\'
        ');
        
        $result = $model->queryAll();
        
        return $result;
    }

    public function getInfoBangunan($nop)
    {
        $kd_propinsi    = substr($nop, 0, 2);
        $kd_dati2       = substr($nop, 2, 2);
        $kd_kecamatan   = substr($nop, 4, 3);
        $kd_kelurahan   = substr($nop, 7, 3);
        $kd_blok        = substr($nop, 10, 3);
        $no_urut        = substr($nop, 13, 4);
        $kd_jns_op      = substr($nop, 17, 1);

        $connection = \Yii::$app->db;
        $model = $connection->createCommand("select d.nm_wp, a.thn_dibangun_bng, b.nm_jpb, a.thn_renovasi_bng, a.luas_bng, a.jml_lantai_bng,
        a.kd_propinsi||'.'||a.kd_dati2||'.'||a.kd_kecamatan||'.'||a.kd_kelurahan||'.'||a.kd_blok||'.'||a.no_urut||'.'||a.kd_jns_op as nop,
        case a.kondisi_bng when '1' then 'SANGAT BAIK'
                        when '2' then 'BAIK'
                        when '3' then 'SEDANG'
                        when '4' then 'JELEK'
        end as kondisi_bng,
        case a.jns_konstruksi_bng when '1' then 'BAJA'
                                when '2' then 'BETON'
                                when '3' then 'BATU BATA'
                                when '4' then 'KAYU'
        end as jns_konstruksi_bng,
        case a.jns_atap_bng when '1' then 'DECRABON/BETON/GTG GLAZUR'
                            when '2' then 'GTG BETON/ALUMUNIUM'
                            when '3' then 'GTG BIASA/SIRAP'
                            when '4' then 'ASBES'
            else 'SENG'
        end as jns_atap_bng,
        case a.kd_dinding when '1' then 'KACA/ALUMUNIUM'
                        when '2' then 'BETON'
                        when '3' then 'BATU BATA/CONBLOK'
                        when '4' then 'KAYU'
                        when '5' then 'SENG'
            else 'TIDAK ADA'
        end as kd_dinding,
        case a.kd_lantai when '1' then 'MARMER'
                        when '2' then 'KERAMIK'
                        when '3' then 'TERASO'
                        when '4' then 'UBIN PC/PAPAN'
            else 'SEMEN'
        end as kd_lantai,
        case a.kd_langit_langit when '1' then 'AKUSTIK/JATI'
                                when '2' then 'TRIPLEK/ASBES BAMBU'
            else 'TIDAK ADA'
        end as kd_langit_langit
        from pbb.dat_op_bangunan a
        inner join pbb.ref_jpb b on a.kd_jpb = b.kd_jpb
        left join pbb.dat_objek_pajak c on a.kd_propinsi||a.kd_dati2||a.kd_kecamatan||a.kd_kelurahan||a.kd_blok||a.no_urut||a.kd_jns_op  = c.kd_propinsi||c.kd_dati2||c.kd_kecamatan||c.kd_kelurahan||c.kd_blok||c.no_urut||c.kd_jns_op 
        left join pbb.dat_subjek_pajak d on c.subjek_pajak_id = d.subjek_pajak_id
        where
        a.kd_propinsi = '$kd_propinsi'
                            AND a.kd_dati2 = '$kd_dati2'
                            AND a.kd_kecamatan = '$kd_kecamatan'
                            AND a.kd_kelurahan = '$kd_kelurahan'
                            AND a.kd_blok = '$kd_blok'
                            AND a.no_urut = '$no_urut'
                            AND a.kd_jns_op = '$kd_jns_op'");

        $result = $model->queryOne();
        
        return json_encode($result, true);
        
        /*$criteriaBng = new CDbCriteria();
        $criteriaBng->select = "no_bng";
        $criteriaBng->condition = "kd_propinsi = '$kd_propinsi'
                            AND kd_dati2 = '$kd_dati2'
                            AND kd_kecamatan = '$kd_kecamatan'
                            AND kd_kelurahan = '$kd_kelurahan'
                            AND kd_blok = '$kd_blok'
                            AND no_urut = '$no_urut'
                            AND kd_jns_op = '$kd_jns_op'";
        
        $noBng = DatOpBangunan::model()->findAll($criteriaBng);
        
        $criteriaDetilBng = new CDbCriteria();
        $criteriaDetilBng->alias = "a";
        $criteriaDetilBng->select = "a.thn_dibangun_bng, b.nm_jpb, a.thn_renovasi_bng, a.luas_bng, a.jml_lantai_bng,
                                        a.kd_propinsi||'.'||a.kd_dati2||'.'||a.kd_kecamatan||'.'||a.kd_kelurahan||'.'||a.kd_blok||'.'||a.no_urut||'.'||a.kd_jns_op as nop,
                                        case a.kondisi_bng when '1' then 'SANGAT BAIK'
                                                        when '2' then 'BAIK'
                                                        when '3' then 'SEDANG'
                                                        when '4' then 'JELEK'
                                        end as kondisi_bng,
                                        case a.jns_konstruksi_bng when '1' then 'BAJA'
                                                                when '2' then 'BETON'
                                                                when '3' then 'BATU BATA'
                                                                when '4' then 'KAYU'
                                        end as jns_konstruksi_bng,
                                        case a.jns_atap_bng when '1' then 'DECRABON/BETON/GTG GLAZUR'
                                                            when '2' then 'GTG BETON/ALUMUNIUM'
                                                            when '3' then 'GTG BIASA/SIRAP'
                                                            when '4' then 'ASBES'
                                            else 'SENG'
                                        end as jns_atap_bng,
                                        case a.kd_dinding when '1' then 'KACA/ALUMUNIUM'
                                                        when '2' then 'BETON'
                                                        when '3' then 'BATU BATA/CONBLOK'
                                                        when '4' then 'KAYU'
                                                        when '5' then 'SENG'
                                            else 'TIDAK ADA'
                                        end as kd_dinding,
                                        case a.kd_lantai when '1' then 'MARMER'
                                                        when '2' then 'KERAMIK'
                                                        when '3' then 'TERASO'
                                                        when '4' then 'UBIN PC/PAPAN'
                                            else 'SEMEN'
                                        end as kd_lantai,
                                        case a.kd_langit_langit when '1' then 'AKUSTIK/JATI'
                                                                when '2' then 'TRIPLEK/ASBES BAMBU'
                                            else 'TIDAK ADA'
                                        end as kd_langit_langit
                                    ";
        $criteriaDetilBng->join = "inner join iptx_ref_jpb b on a.kd_jpb = b.kd_jpb";
        $criteriaDetilBng->condition = "a.kd_propinsi = '$kd_propinsi'
                            AND a.kd_dati2 = '$kd_dati2'
                            AND a.kd_kecamatan = '$kd_kecamatan'
                            AND a.kd_kelurahan = '$kd_kelurahan'
                            AND a.kd_blok = '$kd_blok'
                            AND a.no_urut = '$no_urut'
                            AND a.kd_jns_op = '$kd_jns_op'";
        
        $detailBng = DatOpBangunan::model()->findAll($criteriaDetilBng);
        
        return array('no_bng' => $noBng, 'detail_bng' => $detailBng);*/  
    }
}