|
Server IP : 127.0.0.1 / Your IP : 127.0.0.1 Web Server : Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.6.3 System : Windows NT WIN-R7LTCC7BPLI 6.3 build 9200 (Windows Server 2012 R2 Datacenter Edition) i586 User : GerbangSIPAD ( 0) PHP Version : 5.6.3 Disable Function : NONE MySQL : ON | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF Directory (0777) : C:/xampp5/htdocs/bhumie/upload/../protected/models/ |
| [ Home ] | [ C0mmand ] | [ Upload File ] |
|---|
<?php
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
/**
* Description of Tematik
*
* @author rio
*/
class Gis extends CBhumieRecord {
/**
* Returns the static model of the specified AR class.
* @param string $className active record class name.
* @return BackupConfig the static model class
*/
public static function model($className=__CLASS__)
{
return parent::model($className);
}
public function tableName()
{
return 'public."3216"';
}
/**
* @return array customized attribute labels (name=>label)
*/
public function attributeLabels()
{
return array(
'jns_bumi' => 'Jenis Bumi',
'color' => 'Color',
'ogc_fid' => 'ogc_fid',
'd_nop' => 'Nop',
'd_luas' => 'Luas',
'wkb_geometry' => 'Geometry',
);
}
public function getNamaWp($nama_wp){
$command = Yii::app()->db->createCommand('
SELECT A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN || A.KD_BLOK || A.NO_URUT || A.KD_JNS_OP AS NOP,
A.SUBJEK_PAJAK_ID,B.NM_WP, A.JALAN_OP, BLOK_KAV_NO_OP FROM DAT_OBJEK_PAJAK A
INNER JOIN DAT_SUBJEK_PAJAK B ON(A.SUBJEK_PAJAK_ID = B.SUBJEK_PAJAK_ID)
WHERE B.NM_WP like \'%'.$nama_wp.'%\'
');
$result = $command->queryAll();
return $result;
}
public function JenisTanah($nop){
$command = Yii::app()->db->createCommand('select a.jns_bumi,
public.ST_AsGeoJSON(wkb_geometry) as geojson,
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\'
else \'#FFFFFF\'
end as color,
b.ogc_fid, b.d_nop,b.d_luas,b.wkb_geometry
from pbb.dat_op_bumi a
right join public."'.$nop.'" 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 left(b.d_nop,10) = \''.$nop.'\' ');
$result = $command->queryAll();
return $result;
}
public function LegendJnsTanah($nop){
$command = Yii::app()->db->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
where a.kd_propinsi||\'\'||a.kd_dati2||\'\'||a.kd_kecamatan||\'\'||a.kd_kelurahan = \''.$nop.'\'
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."'.$nop.'" 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 left(b.d_nop,10) = \''.$nop.'\' and a.jns_bumi is null
');
$result = $command->queryAll();
return $result;
}
public function KetetapanPerBuku($nop,$tahun){
$command = Yii::app()->db->createCommand('select a.pbb_yg_harus_dibayar_sppt,
public.ST_AsGeoJSON(wkb_geometry) as geojson,
case when (a.pbb_yg_harus_dibayar_sppt > \'0\' and a.pbb_yg_harus_dibayar_sppt <= \'100000\') then \'#ff0000\'
when (a.pbb_yg_harus_dibayar_sppt > \'100000\' and a.pbb_yg_harus_dibayar_sppt <= \'500000\') then \'#eeff00\'
when (a.pbb_yg_harus_dibayar_sppt > \'500000\' and a.pbb_yg_harus_dibayar_sppt <= \'2000000\') then \'#48ff00\'
when (a.pbb_yg_harus_dibayar_sppt > \'2000000\' and a.pbb_yg_harus_dibayar_sppt <= \'5000000\') then \'#707320\'
when (a.pbb_yg_harus_dibayar_sppt > \'5000000\') then \'#00f7ff\'
else \'#ffffff\'
end as color,
b.ogc_fid, b.d_nop,b.d_luas,b.wkb_geometry
from pbb.sppt a
right join public."'.$nop.'" 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.thn_pajak_sppt = \''.$tahun.'\' and left(b.d_nop,10) = \''.$nop.'\' ');
$result = $command->queryAll();
return $result;
}
public function LegendKetetapanPerBuku($nop, $tahun){
$command = Yii::app()->db->createCommand('
SELECT \'Buku I\' AS NAMA, \'#ff0000\' AS COLOR, COUNT(*)AS TOTAL FROM 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 SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
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 SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
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 SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
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 SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
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 SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND THN_PAJAK_SPPT = \''.$tahun.'\'
AND PBB_YG_HARUS_DIBAYAR_SPPT = 0
');
$result = $command->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);
$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 pbb.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);
}
//untuk informasi tematik nilai individu
public function NilaiIndividu($nop){
$command = Yii::app()->db->createCommand('SELECT A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN || A.KD_BLOK || A.NO_URUT || A.KD_JNS_OP AS NOP,A.COLOR,
PUBLIC.ST_ASGEOJSON(wkb_geometry) AS GEOJSON, B.ogc_fid, B.D_NOP,B.D_LUAS,B.wkb_geometry
FROM (SELECT DISTINCT
KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT,KD_JNS_OP,
\'#3CEDF0\' AS COLOR
FROM
PBB.DAT_NILAI_INDIVIDU
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_NILAI_INDIVIDU
)
)A RIGHT JOIN PUBLIC."'.$nop.'" 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
left(b.d_nop,10) = \''.$nop.'\'');
$result = $command->queryAll();
return $result;
}
//untuk informasi legend nilai individu
public function LegendNilaiIndividu($nop){
$command = Yii::app()->db->createCommand('
SELECT \'Sudah Dinilai Individu\' AS NAMA, \'#3CEDF0\' AS COLOR,COUNT(*) AS TOTAL
FROM
pbb.DAT_NILAI_INDIVIDU
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
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
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
EXCEPT
SELECT DISTINCT
KD_PROPINSI, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT,KD_JNS_OP
FROM
pbb.DAT_NILAI_INDIVIDU
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\')A
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan warna status pembayaran pada peta
public function StatusPembayaran($nop, $tahun){
$command = Yii::app()->db->createCommand('
SELECT A.*,PUBLIC.ST_ASGEOJSON(wkb_geometry) AS GEOJSON,
B.* 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
DAT_OBJEK_PAJAK
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
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
SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND 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
SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND 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
PEMBAYARAN_SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND 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
PEMBAYARAN_SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND THN_PAJAK_SPPT = \''.$tahun.'\') A
RIGHT JOIN PUBLIC."'.$nop.'" B
ON (A.NOP =
B.D_NOP)
WHERE
LEFT(D_NOP,10) = \''.$nop.'\'
');
$result = $command->queryAll();
return $result;
}
//untuk informasi legend status pembayaran
public function LegendStatusPembayaran($nop, $tahun){
$command = Yii::app()->db->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
DAT_OBJEK_PAJAK
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
EXCEPT
SELECT
DISTINCT KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN || KD_BLOK || NO_URUT || KD_JNS_OP AS NOP
FROM
SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND 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
SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND 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
PEMBAYARAN_SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND THN_PAJAK_SPPT = \''.$tahun.'\')B
UNION ALL
SELECT \'Sudah Bayar\' AS NAMA,\'#24ff00\' AS COLOR,
COUNT(*) AS TOTAL
FROM
PEMBAYARAN_SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND THN_PAJAK_SPPT = \''.$tahun.'\'
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan warna pada peta zona nilai tanah
public function ZonaNilaiTanah($nop, $tahun)
{
$criteria = Yii::app()->db->createCommand('
SELECT Z.*,PUBLIC.ST_ASGEOJSON(wkb_geometry) AS GEOJSON, Y.*
FROM (
SELECT
DISTINCT A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN || A.KD_BLOK || A.NO_URUT || A.KD_JNS_OP AS NOP,
LTRIM(A.NO_URUT,\'0\') AS URUT,
A.KD_ZNT,
B.THN_NIR_ZNT,
C.WARNA as COLOR
FROM
DAT_OP_BUMI A INNER JOIN DAT_NIR B ON
(A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN
= B.KD_PROPINSI || B.KD_DATI2 || B.KD_KECAMATAN || B.KD_KELURAHAN AND A.KD_ZNT = B.KD_ZNT)
INNER JOIN DAT_PETA_ZNT C ON(B.KD_PROPINSI ||B .KD_DATI2 || B.KD_KECAMATAN || B.KD_KELURAHAN
= C.KD_PROPINSI || C.KD_DATI2 || C.KD_KECAMATAN || C.KD_KELURAHAN AND B.KD_ZNT = C.KD_ZNT)
WHERE
A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN = \''.$nop.'\'
AND B.THN_NIR_ZNT = \''.$tahun.'\') Z
RIGHT JOIN PUBLIC."'.$nop.'" Y ON Z.NOP = Y.D_NOP
WHERE LEFT(D_NOP,10) = \''.$nop.'\'
');
$result = $criteria->queryAll();
return $result;
}
//untuk menampilkan legend zona nilai tanah
public function LegendZnt($nop, $tahun)
{
$command = Yii::app()->db->createCommand('
SELECT DISTINCT Z.*,C.WARNA as COLOR FROM (
SELECT
B.KD_ZNT as NAMA,COUNT(*) AS TOTAL
FROM
DAT_OP_BUMI A INNER JOIN DAT_NIR B ON
(A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN
= B.KD_PROPINSI || B.KD_DATI2 || B.KD_KECAMATAN || B.KD_KELURAHAN AND A.KD_ZNT = B.KD_ZNT)
WHERE
A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN = \''.$nop.'\'
AND B.THN_NIR_ZNT = \''.$tahun.'\'
GROUP BY B.KD_ZNT) Z
INNER JOIN DAT_PETA_ZNT C ON(Z.NAMA = C.KD_ZNT)ORDER BY Z.NAMA
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan peta jenis penggunaan bangunan
public function JenisPenggunaanBangunanBG($nop)
{
$command = Yii::app()->db->createCommand('
SELECT B.*,PUBLIC.ST_ASGEOJSON(wkb_geometry) AS GEOJSON,
C.* FROM (
SELECT DISTINCT A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN || A.KD_BLOK || A.NO_URUT || A.KD_JNS_OP AS NOP, Z.NM_JPB_JPT, Z.COLOR
FROM
DAT_OP_BANGUNAN A INNER JOIN
(SELECT KD_JPB_JPT,NM_JPB_JPT,
CASE
WHEN KD_JPB_JPT = \'01\' THEN \'#0bf42b\'
WHEN KD_JPB_JPT = \'02\' THEN \'#0b41f4\'
WHEN KD_JPB_JPT = \'03\' THEN \'#f40b20\'
WHEN KD_JPB_JPT = \'04\' THEN \'#f4e10b\'
WHEN KD_JPB_JPT = \'05\' THEN \'#f4890b\'
WHEN KD_JPB_JPT = \'06\' THEN \'#0bd6f4\'
WHEN KD_JPB_JPT = \'07\' THEN \'#a40bf4\'
WHEN KD_JPB_JPT = \'08\' THEN \'#f40b5d\'
WHEN KD_JPB_JPT = \'09\' THEN \'#088ab0\'
WHEN KD_JPB_JPT = \'10\' THEN \'#5a0426\'
WHEN KD_JPB_JPT = \'11\' THEN \'#7c5605\'
WHEN KD_JPB_JPT = \'12\' THEN \'#425020\'
WHEN KD_JPB_JPT = \'13\' THEN \'#078021\'
WHEN KD_JPB_JPT = \'14\' THEN \'#466580\'
WHEN KD_JPB_JPT = \'15\' THEN \'#88594a\'
WHEN KD_JPB_JPT = \'16\' THEN \'#f1e583\'
END AS COLOR
FROM JPB_JPT )Z ON (A.KD_JPB=Z.KD_JPB_JPT)
WHERE A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN = \''.$nop.'\')B
RIGHT JOIN PUBLIC."'.$nop.'" C
ON (B.NOP =
C.D_NOP)
WHERE
LEFT(C.D_NOP,10) = \''.$nop.'\'
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan peta jenis penggunaan bangunan bagian blok
public function JenisPenggunaanBangunan($nop)
{
$command = Yii::app()->db->createCommand('
select a.kd_jpb, \'#8f734e\' as color,
public.ST_AsGeoJSON(wkb_geometry) as geojson,
b.ogc_fid, b.d_nop,b.d_luas,b.wkb_geometry
from pbb.dat_op_bangunan a
right join public."'.$nop.'" 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 left(b.d_nop,10) = \''.$nop.'\'
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan legend jenis penggunaan bangunan
public function LegendJenisPenggunaanBangunan($nop)
{
$command = Yii::app()->db->createCommand('
SELECT \'PERUMAHAN\' AS NAMA,\'#0bf42b\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'01\'
UNION ALL
SELECT \'PERKANTORAN SWASTA\' AS NAMA,\'#0b41f4\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'02\'
UNION ALL
SELECT \'PABRIK\' AS NAMA,\'#f40b20\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'03\'
UNION ALL
SELECT \'TOKO/APOTIK/PASAR/RUKO\' AS NAMA,\'#f4e10b\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'04\'
UNION ALL
SELECT \'RUMAH SAKIT/KLINIK\' AS NAMA,\'#f4890b\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'05\'
UNION ALL
SELECT \'OLAH RAGA/REKREASI\' AS NAMA,\'#\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'06\'
UNION ALL
SELECT \'HOTEL/WISMA\' AS NAMA,\'#a40bf4\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'07\'
UNION ALL
SELECT \'BENGKEL/GUDANG/PERTANIAN\' AS NAMA,\'#f40b5d\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'08\'
UNION ALL
SELECT \'GEDUNG PEMERINTAH\' AS NAMA,\'#088ab0\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'09\'
UNION ALL
SELECT \'LAIN-LAIN\' AS NAMA,\'#5a0426\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'10\'
UNION ALL
SELECT \'BANGUNAN TIDAK KENA PAJAK\' AS NAMA,\'#7c5605\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'11\'
UNION ALL
SELECT \'BANGUNAN PARKIR\' AS NAMA,\'#425020\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'12\'
UNION ALL
SELECT \'APARTEMEN\' AS NAMA,\'#078021\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'13\'
UNION ALL
SELECT \'POMPA BENSIN\' AS NAMA,\'#466580\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'14\'
UNION ALL
SELECT \'TANGKI MINYAK\' AS NAMA,\'#88594a\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'15\'
UNION ALL
SELECT \'GEDUNG SEKOLAH\' AS NAMA,\'#f1e583\' AS COLOR,COUNT(*) as TOTAL
FROM DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\' AND
KD_JPB = \'16\'
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan peta kelas tanah
public function KelasTanah($nop, $tahun)
{
$command = Yii::app()->db->createCommand('
SELECT DISTINCT
A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN || A.KD_BLOK || A.NO_URUT || A.KD_JNS_OP AS NOP,
A.KD_KLS_TANAH AS NAMA,
B.WARNA AS COLOR,PUBLIC.ST_ASGEOJSON(wkb_geometry) AS GEOJSON,C.*
FROM
SPPT A
LEFT JOIN KELAS_TANAH B ON(
A.KD_KLS_TANAH = B.KD_KLS_TANAH AND A.THN_AWAL_KLS_TANAH = B.THN_AWAL_KLS_TANAH
) RIGHT JOIN PUBLIC."'.$nop.'" 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.D_NOP)
WHERE
LEFT(C.D_NOP,10) = \''.$nop.'\'
AND A.THN_PAJAK_SPPT = \''.$tahun.'\'
');
$result = $command->queryAll();
return $result;
}
public function LegendKelasTanah($nop, $tahun)
{
$command = Yii::app()->db->createCommand('
SELECT Z.* FROM (
SELECT DISTINCT A.KD_KLS_TANAH AS NAMA, B.WARNA AS COLOR,COUNT(*) AS TOTAL FROM SPPT A
INNER JOIN KELAS_TANAH B ON (A.KD_KLS_TANAH = B.KD_KLS_TANAH AND A.THN_AWAL_KLS_TANAH = B.THN_AWAL_KLS_TANAH)
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND THN_PAJAK_SPPT = \''.$tahun.'\'
GROUP BY A.KD_KLS_TANAH, B.WARNA)Z ORDER BY 1
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan peta tematik kelas bangunan
public function KelasBangunan($nop, $tahun)
{
$command = Yii::app()->db->createCommand('
SELECT DISTINCT
A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN || A.KD_BLOK || A.NO_URUT || A.KD_JNS_OP AS NOP,
A.KD_KLS_BNG AS NAMA,
PUBLIC.ST_ASGEOJSON(wkb_geometry) AS GEOJSON,C.*
FROM
SPPT A
LEFT JOIN KELAS_BANGUNAN B ON(
A.KD_KLS_BNG = B.KD_KLS_BNG AND A.THN_AWAL_KLS_BNG = B.THN_AWAL_KLS_BNG
) RIGHT JOIN PUBLIC."'.$nop.'" 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.D_NOP)
WHERE
LEFT(C.D_NOP,10) = \''.$nop.'\'
AND A.THN_PAJAK_SPPT = \''.$tahun.'\'
');
$result = $command->queryAll();
return $result;
}
//untuk menampilkan legend kelas bangunan
public function LegendKelasBangunan($nop, $tahun)
{
$command = Yii::app()->db->createCommand('
SELECT Z.* FROM (
SELECT DISTINCT A.KD_KLS_BNG AS NAMA, B.WARNA AS COLOR,COUNT(*) AS TOTAL FROM SPPT A
INNER JOIN KELAS_BANGUNAN B ON (A.KD_KLS_BNG = B.KD_KLS_BNG AND A.THN_AWAL_KLS_BNG = B.THN_AWAL_KLS_BNG)
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \''.$nop.'\'
AND THN_PAJAK_SPPT = \''.$tahun.'\'
GROUP BY A.KD_KLS_BNG, B.WARNA)Z ORDER BY 1
');
$result = $command->queryAll();
return $result;
}
}