|
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/sig-kolaka/models/ |
| [ Home ] | [ C0mmand ] | [ Upload File ] |
|---|
<?php
namespace app\models;
use Yii;
use yii\base\Model;
use yii\db\Query;
/**
* class peta tematik
*/
class PetaTematik extends Model
{
/**
* return json
* berfungsi untuk menampilkan jenis_bumi
*/
function getJenisBumi($nop)
{
$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."' . $nop . '" 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($nop)
{
$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."' . $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.jns_bumi is null
');
$result = $model->queryAll();
return $result;
}
/**
* return json
* berfungsi untuk menampilkan ketetapan perbuku
*/
function getKetetapanPerBuku($nop, $tahun)
{
$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."' . $nop . '" 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 = \'' . $tahun . '\') 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($nop, $tahun)
{
$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 nilai individu
*/
function getNilaiIndividu($nop)
{
$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."' . $nop . '" As lg
LEFT JOIN (SELECT KD_PROPINSI, COLOR, KD_DATI2, KD_KECAMATAN, KD_KELURAHAN, KD_BLOK, NO_URUT, KD_JNS_OP
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($nop)
{
$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 KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \'' . $nop . '\' and 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 KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \'' . $nop . '\' and NILAI_SISTEM_BNG is not null)A
');
$result = $model->queryAll();
return $result;
}
function getStatusPembayaran($nop, $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."' . $nop . '" 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,
\'#FFFFFF\' AS COLOR, \'Tidak Ada SPPT\' AS NAMA
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,
\'#FFFFFF\' AS COLOR, \'Tidak Ada SPPT\' AS NAMA
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,
\'#ff0000\' AS COLOR, \'Belum Bayar\' AS NAMA
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,
\'#ff0000\' AS COLOR, \'Belum Bayar\' AS NAMA
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,
\'#24ff00\' AS COLOR, \'Sudah Bayar\' AS NAMA
FROM
pbb.PEMBAYARAN_SPPT
WHERE THN_PAJAK_SPPT = \'' . $tahun . '\') A ) As lp
ON lp.nop = lg.d_nop ) As f ) As fc;
');
// echo $model->getRawSql();die;
$status_pembayaran = $model->queryAll();
return $status_pembayaran;
}
public function LegendStatusPembayaran($nop, $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 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
pbb.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
pbb.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
pbb.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
pbb.PEMBAYARAN_SPPT
WHERE
KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \'' . $nop . '\' and THN_PAJAK_SPPT = \'' . $tahun . '\'
');
$result = $model->queryAll();
return $result;
}
//untuk menampilkan warna pada peta zona nilai tanah
public function getZnt($nop, $tahun)
{
$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."' . $nop . '" As lg
LEFT JOIN (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,
C.WARNA as COLOR,
LTRIM(A.NO_URUT,\'0\') AS URUT,
A.KD_ZNT,
B.THN_NIR_ZNT
FROM
PBB.DAT_OP_BUMI A INNER JOIN PBB.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 PBB.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 . '\') As lp
ON lp.nop = lg.d_nop ) As f ) As fc;
');
$znt = $model->queryAll();
return $znt;
}
//untuk menampilkan legend zona nilai tanah
public function LegendZnt($nop, $tahun)
{
$connection = \Yii::$app->db;
$model = $connection->createCommand('
SELECT DISTINCT Z.* FROM ( SELECT B.KD_ZNT as NAMA, C.WARNA AS COLOR,COUNT(*) AS TOTAL FROM PBB.DAT_OP_BUMI A
INNER JOIN PBB.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 PBB.DAT_PETA_ZNT C ON (C.KD_ZNT=B.KD_ZNT AND B.KD_PROPINSI || B.KD_DATI2
|| B.KD_KECAMATAN = C.KD_PROPINSI || C.KD_DATI2 || C.KD_KECAMATAN)
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, C.WARNA ORDER BY B.KD_ZNT) Z
ORDER BY Z.NAMA
');
$result = $model->queryAll();
return $result;
}
//untuk menampilkan warna pada peta zona nilai tanah
public function getKelasTanah($nop, $tahun)
{
$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."' . $nop . '" As lg
LEFT JOIN (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,
B.WARNA AS COLOR, A.KD_KLS_TANAH AS NAMA
FROM
PBB.SPPT A
LEFT JOIN PBB.KELAS_TANAH B ON(
A.KD_KLS_TANAH = B.KD_KLS_TANAH AND A.THN_AWAL_KLS_TANAH = B.THN_AWAL_KLS_TANAH
)
WHERE
A.KD_PROPINSI || A.KD_DATI2 || A.KD_KECAMATAN || A.KD_KELURAHAN = \'' . $nop . '\'
AND A.THN_PAJAK_SPPT = \'' . $tahun . '\') As lp
ON lp.nop = lg.d_nop ) As f ) As fc;
');
$kelas_tanah = $model->queryAll();
return $kelas_tanah;
}
//untuk menampilkan legend zona nilai tanah
public function LegendKelasTanah($nop, $tahun)
{
$connection = \Yii::$app->db;
$model = $connection->createCommand('
SELECT Z.* FROM (
SELECT DISTINCT A.KD_KLS_TANAH AS NAMA, B.WARNA AS COLOR,COUNT(*) AS TOTAL FROM PBB.SPPT A
INNER JOIN PBB.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 = $model->queryAll();
return $result;
}
//untuk menampilkan warna pada peta jpb
public function getJpb($nop)
{
$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."' . $nop . '" As lg
LEFT JOIN (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.COLOR, Z.NM_JPB_JPT
FROM
pbb.DAT_OP_BANGUNAN A INNER JOIN
(SELECT KD_JPB_JPT,NM_JPB_JPT,
CASE
WHEN KD_JPB_JPT = \'00\' THEN \'#1d1459\'
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\'
WHEN KD_JPB_JPT = \'50\' THEN \'#991616\'
END AS COLOR
FROM pbb.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 . '\') As lp
ON lp.nop = lg.d_nop ) As f ) As fc;
');
$kelas_tanah = $model->queryAll();
return $kelas_tanah;
}
//untuk menampilkan legend jpb
public function LegendJpb($nop)
{
$connection = \Yii::$app->db;
$model = $connection->createCommand('
SELECT \'TANAH DIBANGUN\' AS NAMA,\'#1d1459\' AS COLOR,COUNT(*) as TOTAL
FROM pbb.DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \'' . $nop . '\' AND
KD_JPB = \'00\'
UNION ALL
SELECT \'PERUMAHAN\' AS NAMA,\'#0bf42b\' AS COLOR,COUNT(*) as TOTAL
FROM pbb.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 pbb.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 pbb.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 pbb.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 pbb.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,\'#0bd6f4\' AS COLOR,COUNT(*) as TOTAL
FROM pbb.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 pbb.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 pbb.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 pbb.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 pbb.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 pbb.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 pbb.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 pbb.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 pbb.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 pbb.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 pbb.DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \'' . $nop . '\' AND
KD_JPB = \'16\'
UNION ALL
SELECT \'TANAH KOSONG\' AS NAMA,\'#991616\' AS COLOR,COUNT(*) as TOTAL
FROM pbb.DAT_OP_BANGUNAN
WHERE KD_PROPINSI || KD_DATI2 || KD_KECAMATAN || KD_KELURAHAN = \'' . $nop . '\' AND
KD_JPB = \'17\'
');
$result = $model->queryAll();
return $result;
}
//untuk menampilkan warna pada peta kelas bangunan
public function getKelasBangunan($nop, $tahun)
{
$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."' . $nop . '" As lg
LEFT JOIN (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,
B.WARNA as color,
A.KD_KLS_BNG AS NAMA,
PUBLIC.ST_ASGEOJSON(wkb_geometry) AS GEOJSON,C.*
FROM
pbb.SPPT A
LEFT JOIN pbb.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 . '\') As lp
ON lp.nop = lg.d_nop ) As f ) As fc;
');
$kelas_bangunan = $model->queryAll();
return $kelas_bangunan;
}
//untuk menampilkan legend jpb
public function LegendKelasBangunan($nop, $tahun)
{
$connection = \Yii::$app->db;
$model = $connection->createCommand('
SELECT Z.* FROM (
SELECT DISTINCT A.KD_KLS_BNG AS NAMA, B.WARNA AS COLOR,COUNT(*) AS TOTAL FROM pbb.SPPT A
INNER JOIN pbb.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 = $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);
}
}