You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
709 lines
28 KiB
709 lines
28 KiB
<?php |
|
|
|
namespace App\Models; |
|
|
|
use CodeIgniter\Model; |
|
|
|
class Mseksi extends Model |
|
{ |
|
|
|
|
|
protected $table = 'DASHBOARD_WP'; |
|
protected $allowedFields = ['NPWP', 'NAMA', 'TAHUN', 'TOTAL']; |
|
protected $table2 = "DASHBOARD_BESAR_MAPKJS"; |
|
|
|
|
|
function getRenpen($param) |
|
{ |
|
$kwl = $param['kwl']; |
|
$tahun = $param['tahun']; |
|
$bln = $param['bln']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA" && $nip == "SEMUA") { |
|
|
|
$query = $this->db->query("SELECT SUM(JML_TARGET) RENPEN FROM RENPEN_PER_KPP |
|
WHERE TAHUN = '" . $tahun . "' and BULAN <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip"); |
|
} else { |
|
$query = $this->db->query("SELECT SUM(RENPEN) RENPEN FROM MVTARGET_AR |
|
WHERE TAHUN = '" . $tahun . "' and BULAN <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip"); |
|
} |
|
return $query; |
|
} |
|
|
|
function getPen($param) |
|
{ |
|
$kwl = $param['kwl']; |
|
$tahun = $param['tahun']; |
|
$bln = $param['bln']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
$query = $this->db->query("SELECT SUM(TOTAL) PEN |
|
FROM DASHBOARD_WP_BLN |
|
WHERE TAHUN= '" . $tahun . "' AND BULAN <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
"); |
|
return $query; |
|
} |
|
|
|
function getPenl($param) |
|
{ |
|
$kwl = $param['kwl']; |
|
$tahun = $param['tahun']; |
|
$tahunl = $tahun - 1; |
|
$bln = $param['bln']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
$query = $this->db->query("SELECT SUM(TOTAL) PEN |
|
FROM DASHBOARD_WP_BLN |
|
WHERE TAHUN= '" . $tahunl . "' AND BULAN <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip"); |
|
return $query; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
function getKat($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$tahunl = $tahun - 1; |
|
$bln = $param['bln']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
$query = $this->db->query("SELECT X.KATEGORI KODE,NMKAT,POPULASI,WPBYRN,WPBYRP,KPENN,KPENP FROM |
|
(SELECT CASE WHEN KATEGORI IS NULL THEN 'ZZ' ELSE KATEGORI END KATEGORI, |
|
CASE WHEN NM_KATEGORI IS NULL THEN 'UNKNOWN' ELSE NM_KATEGORI END NMKAT,SUM(POPULASI) POPULASI |
|
FROM POPULASI_KATEGORI |
|
WHERE KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY KATEGORI,NM_KATEGORI) X LEFT JOIN |
|
( |
|
SELECT CASE WHEN KD_KATEGORI IS NULL THEN 'ZZ' ELSE KD_KATEGORI END |
|
KODE, |
|
SUM (CASE WHEN THNBYR = '" . $tahun . "' THEN WPBAYAR ELSE 0 END) |
|
WPBYRN, |
|
SUM (CASE WHEN THNBYR = '" . $tahunl . "' THEN WPBAYAR ELSE 0 END) |
|
WPBYRP, |
|
SUM (CASE WHEN THNBYR = '" . $tahun . "' THEN ROUND (TOTAL) ELSE 0 END) |
|
KPENN, |
|
SUM (CASE WHEN THNBYR = '" . $tahunl . "' THEN ROUND (TOTAL) ELSE 0 END) |
|
KPENP |
|
FROM KWLPERKATEGORI_MV |
|
WHERE KWLADM = '" . $kwl . "' AND BLNBYR = '" . $bln . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY KD_KATEGORI |
|
ORDER BY KD_KATEGORI ASC ) Y ON X.KATEGORI = Y.KODE |
|
"); |
|
|
|
|
|
return $query; |
|
} |
|
|
|
|
|
function getPiePen($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$tahunl = $tahun - 1; |
|
$bln = $param['bln']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
$query = $this->db->query("SELECT NM_PAJAK,TOTALN PENERIMAAN, TOTALP PENL, |
|
(SELECT SUM(TOTALN) |
|
FROM ( |
|
SELECT NM_PAJAK,ROUND(SUM(CASE WHEN THNBYR= '" . $tahun . "' AND BLNBYR <= '" . $bln . "' THEN TOTAL ELSE 0 END)) TOTALN |
|
FROM KWLPERJENIS_MV WHERE KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY NM_PAJAK ORDER BY TOTALN DESC) WHERE ROWNUM < = 7) TOPEN7, |
|
(SELECT ROUND(SUM(TOTAL)) FROM KWLPERJENIS_MV WHERE THNBYR = '" . $tahun . "' AND BLNBYR <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip) PENERIMAAN_TOT, |
|
(SELECT ROUND(SUM(TOTAL)) FROM KWLPERJENIS_MV WHERE THNBYR = '" . $tahunl . "' AND BLNBYR <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip) PENERIMAAN_TOTL |
|
FROM ( |
|
SELECT NM_PAJAK,ROUND(SUM(CASE WHEN THNBYR= '" . $tahun . "' AND BLNBYR <= '" . $bln . "' THEN TOTAL ELSE 0 END)) TOTALN, |
|
ROUND(SUM(CASE WHEN THNBYR= '" . $tahunl . "' AND BLNBYR <= '" . $bln . "' THEN TOTAL ELSE 0 END)) TOTALP |
|
FROM KWLPERJENIS_MV WHERE KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY NM_PAJAK ORDER BY TOTALN DESC) WHERE ROWNUM < = 7 |
|
"); |
|
return $query; |
|
} |
|
|
|
|
|
function paycompNas($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
$bln = $param['bln']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
$query = $this->db->query("SELECT JMLBLN JMLBULAN, COUNT(1) JMLWP FROM( |
|
SELECT NPWP,COUNT(1) JMLBLN |
|
FROM DASHBOARD_WP_BLN WHERE TAHUN = '" . $tahun . "' AND BULAN <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY NPWP) WHERE JMLBLN <= TO_NUMBER('" . $bln . "') |
|
GROUP BY JMLBLN ORDER BY JMLBLN ASC"); |
|
return $query; |
|
} |
|
|
|
function sofNas($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
$bln = $param['bln']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
$query = $this->db->query("SELECT NO,LAPISAN,WPBYR,TOTAL, |
|
(SELECT SUM(WPBYR) FROM KWLSOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip) TOTWPBYR, |
|
(SELECT SUM(ROUND(TOTAL)) FROM KWLSOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip) TOTALL |
|
FROM ( |
|
SELECT NO,LAPISAN,SUM(WPBYR) WPBYR,SUM(ROUND(TOTAL)) TOTAL FROM KWLSOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY NO,LAPISAN) ORDER BY NO ASC "); |
|
return $query; |
|
} |
|
|
|
|
|
|
|
|
|
function getWPterdaftar($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$tahunl = $tahun - 1; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
|
|
$query = $this->db->query("SELECT |
|
SUM(CASE WHEN TAHUN <= '" . $tahun . "' THEN ROUND(JUMLAHWP) ELSE 0 END) WPADMNOW, |
|
SUM(CASE WHEN TAHUN <= '" . $tahunl . "' THEN ROUND(JUMLAHWP) ELSE 0 END) WPADMPAST |
|
FROM WP_TERDAFTAR WHERE KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip"); |
|
return $query; |
|
} |
|
|
|
function laporTahunan($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$tahunl = $tahun - 1; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
$query = $this->db->query("SELECT SUM(CASE WHEN TAHUN = '" . $tahun . "' THEN ROUND(JMLLAPOR) ELSE 0 END) LAPORNOW, |
|
SUM(CASE WHEN TAHUN = '" . $tahunl . "' THEN ROUND(JMLLAPOR) ELSE 0 END) LAPORPAST |
|
FROM DASH_SPTTAHUNAN WHERE KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip"); |
|
return $query; |
|
} |
|
|
|
function getSektdom($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
|
|
$query = $this->db->query("SELECT * FROM ( |
|
SELECT KD_KATEGORI,NM_KATEGORI,SUM(ROUND(TOTAL)) TOTAL FROM KWLPERKATEGORI_MV |
|
WHERE THNBYR = '" . $tahun . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY KD_KATEGORI,NM_KATEGORI |
|
ORDER BY TOTAL DESC) WHERE ROWNUM <= 5"); |
|
return $query; |
|
} |
|
|
|
function Wpbyrlpr($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
|
|
$query = $this->db->query("SELECT SUM(WPNORMAL) WPNORMAL,SUM(WPNE) WPNE, SUM(WPDE)WPDE, |
|
SUM(WPLAMA) WPLAMA, SUM(WPBARU)WPBARU, |
|
SUM(LAMALPR) LAMALPR,SUM(LAMABYR) LAMABYR, |
|
SUM(BARULPR) BARULPR, SUM(BARUBYR)BARUBYR, |
|
SUM(NORMALBYR)NORMALBYR, SUM(NEBYR)NEBYR, |
|
SUM(NORMALLPR) NORMALLPR,SUM(NELPR) NELPR, |
|
SUM(DELPR) DELPR, SUM(DEBYR) DEBYR, SUM(WPOP) WPOP, |
|
SUM(WPBADAN) WPBADAN, SUM(WPPEMUNGUT) WPPEMUNGUT, |
|
SUM(OPBYR) OPBYR, SUM(OPLPR) OPLPR, SUM(BADANBYR) BADANBYR, SUM(BADANLPR) BADANLPR, SUM(PEMUNGUTBYR) PEMUNGUTBYR,SUM(PEMUNGUTLPR) PEMUNGUTLPR, |
|
SUM(BADANNORMAL) BADANNORMAL, SUM(BADANNORMALBYR) BADANNORMALBYR, SUM(BADANNORMALLPR) BADANNORMALLPR, |
|
SUM(BADANNE) BADANNE, SUM(BADANNEBYR) BADANNEBYR, SUM(BADANNELPR) BADANNELPR, |
|
SUM(BADANDE) BADANDE, SUM(BADANDEBYR) BADANDEBYR, SUM(BADANDELPR) BADANDELPR, |
|
SUM(PEMUNGUTNORMAL) PEMUNGUTNORMAL, SUM(PEMUNGUTNORMALBYR) PEMUNGUTNORMALBYR, SUM(PEMUNGUTNORMALLPR) PEMUNGUTNORMALLPR, |
|
SUM(PEMUNGUTNE) PEMUNGUTNE, SUM(PEMUNGUTNEBYR) PEMUNGUTNEBYR,SUM(PEMUNGUTNELPR) PEMUNGUTNELPR, |
|
SUM(PEMUNGUTDE) PEMUNGUTDE, SUM(PEMUNGUTDEBYR) PEMUNGUTDEBYR,SUM(PEMUNGUTDELPR) PEMUNGUTDELPR, |
|
SUM(OPNORMAL) OPNORMAL, SUM(OPNORMALBYR) OPNORMALBYR, SUM(OPNORMALLPR) OPNORMALLPR, |
|
SUM(OPNE) OPNE, SUM(OPNEBYR) OPNEBYR, SUM(OPNELPR) OPNELPR, |
|
SUM(OPDE) OPDE, SUM(OPDEBYR) OPDEBYR, SUM(OPDELPR) OPDELPR, |
|
SUM(LAMANORMAL) LAMANORMAL, SUM(LAMANORMALBYR) LAMANORMALBYR, SUM(LAMANORMALLPR) LAMANORMALLPR, |
|
SUM(LAMANE) LAMANE, SUM(LAMANEBYR) LAMANEBYR, SUM(LAMANELPR) LAMANELPR, |
|
SUM(LAMADE) LAMADE, SUM(LAMADEBYR) LAMADEBYR, SUM(LAMADELPR) LAMADELPR, |
|
SUM(BARUNORMAL) BARUNORMAL, SUM(BARUNORMALBYR) BARUNORMALBYR, SUM(BARUNORMALLPR) BARUNORMALLPR, |
|
SUM(BARUANE) BARUNE, SUM(BARUNEBYR) BARUNEBYR, SUM(BARUNELPR) BARUNELPR, |
|
SUM(BARUDE) BARUDE, SUM(BARUDEBYR) BARUDEBYR, SUM(BARUDELPR) BARUDELPR, SUM(SKRBYRTLT) SKRBYRTLT, SUM(THNLALUBYRNT)THNLALUBYRNT, SUM(NORMALTHNLBYR) NORMALTHNLBYR,SUM(NETHNLBYR) NETHNLBYR, SUM(DETHNLBYR) DETHNLBYR, SUM(NORMALSKRBYR) NORMALSKRBYR, SUM(NESKRBYR) NESKRBYR, SUM(DESKRBYR) DESKRBYR, |
|
SUM(OPCABANG) OPCABANG, SUM(OPPUSAT) OPPUSAT, SUM(BADANCABANG) BADANCABANG, SUM(BADANPUSAT) BADANPUSAT, |
|
SUM(OPBUPOT) OPBUPOT, SUM(OPBUPOTBYR) OPBUPOTBYR, SUM(OPBUPOTTBYR) OPBUPOTTBYR, SUM(NORMALBUPOT) NORMALBUPOT, SUM(NEBUPOT) NEBUPOT, |
|
SUM(DEBUPOT) DEBUPOT, SUM(NORMALBUPOTBYR) NORMALBUPOTBYR, SUM(NEBUPOTBYR) NEBUPOTBYR, SUM(DEBUPOTBYR) DEBUPOTBYR, |
|
SUM(NORMALBUPOTTBYR) NORMALBUPOTTBYR, SUM(NEBUPOTTBYR) NEBUPOTTBYR, SUM(DEBUPOTTBYR) DEBUPOTTBYR, SUM(WPSBUPOT) WPSBUPOT, SUM(WPKBUPOT) WPKBUPOT, |
|
SUM(WPSBUPOTBYR) WPSBUPOTBYR, SUM(WPKBUPOTBYR)WPKBUPOTBYR, SUM(WPSBUPOTTBYR) WPSBUPOTTBYR, SUM(WPKBUPOTTBYR) WPKBUPOTTBYR |
|
FROM DASHBOARD_BESAR_BYRLPR |
|
WHERE TAHUN= '" . $tahun . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip"); |
|
return $query; |
|
} |
|
|
|
function getPPMPKM($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$bln = $param['bln']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
|
|
$query = $this->db->query("SELECT SUM(PPM) PPM, SUM(PKM) PKM, SUM(SBP) SBP, SUM(SBPPPM) SBPPPM, SUM(SBPPKM) SBPPKM, SUM(UNKNOWN) UNK |
|
FROM PPMPKM |
|
WHERE THNBYR = '" . $tahun . "' AND BLNBYR <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip"); |
|
return $query; |
|
} |
|
|
|
|
|
function grafWPTerdaftar($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$tahun1 = $tahun - 1; |
|
$tahun2 = $tahun - 2; |
|
$tahun3 = $tahun - 3; |
|
$tahun4 = $tahun - 4; |
|
$tahun5 = $tahun - 5; |
|
$tahun6 = $tahun - 6; |
|
$tahun7 = $tahun - 7; |
|
$tahun8 = $tahun - 8; |
|
$tahun9 = $tahun - 9; |
|
$tahun10 = $tahun - 10; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
|
|
$query = $this->db->query(" SELECT '$tahun' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun1' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun1 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun2' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun2 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun3' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun3 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun4' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun4 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun5' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun5 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun6' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun6 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun7' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun7 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun8' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun8 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun9' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun9 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
UNION ALL |
|
SELECT '$tahun10' TAHUN, |
|
SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR |
|
WHERE TAHUN <= '" . $tahun10 . "' AND KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
"); |
|
return $query; |
|
} |
|
|
|
function getWPBYR($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$tahunl = $tahun - 1; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
if ($kpp == "SEMUA") { |
|
$cekkpp = ""; |
|
} else { |
|
$cekkpp = "AND KPPADM = '" . $kpp . "'"; |
|
} |
|
|
|
if ($seksi == "SEMUA") { |
|
$cekseksi = ""; |
|
} else { |
|
$cekseksi = |
|
"AND KODESIE = '" . $seksi . "'"; |
|
} |
|
if ($nip == "SEMUA") { |
|
$ceknip = ""; |
|
} else { |
|
$ceknip = "AND NIP = '" . $nip . "'"; |
|
} |
|
|
|
$query = $this->db->query("SELECT |
|
BLNBYR, |
|
SUM(CASE WHEN THNBYR = '" . $tahun . "' THEN 1 ELSE 0 END) WPBYRNOW, |
|
SUM(CASE WHEN THNBYR = '" . $tahunl . "' THEN 1 ELSE 0 END) WPBYRPAST, |
|
SUM(CASE WHEN THNBYR = '" . $tahun . "' THEN TOTAL ELSE 0 END) JMLBYRNOW, |
|
SUM(CASE WHEN THNBYR = '" . $tahunl . "' THEN TOTAL ELSE 0 END) JMLBYRPAST |
|
FROM |
|
(SELECT NPWP,THNBYR,BLNBYR,SUM(TOTAL) TOTAL FROM DASHBOARD_BESAR_MAPKJS |
|
WHERE KWLADM = '" . $kwl . "' $cekkpp $cekseksi $ceknip |
|
GROUP BY NPWP,THNBYR,BLNBYR) |
|
GROUP BY BLNBYR ORDER BY BLNBYR ASC |
|
"); |
|
return $query; |
|
} |
|
|
|
public function detsof($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
$lapisan = $param['lapisan']; |
|
$offset = $param['page']; |
|
$limit = $param['perPage']; |
|
if ($lapisan == 1) { |
|
$saylapisan = "<= 0"; |
|
} else if ($lapisan == 2) { |
|
$saylapisan = "BETWEEN 1 AND 5000"; |
|
} else if ($lapisan == 3) { |
|
$saylapisan = "BETWEEN 5001 AND 10000"; |
|
} else if ($lapisan == 4) { |
|
$saylapisan = "BETWEEN 10001 AND 100000"; |
|
} else if ($lapisan == 5) { |
|
$saylapisan = "BETWEEN 100001 AND 500000"; |
|
} else if ($lapisan == 6) { |
|
$saylapisan = "BETWEEN 500001 AND 1000000"; |
|
} else if ($lapisan == 7) { |
|
$saylapisan = "BETWEEN 1000001 AND 5000000"; |
|
} else if ($lapisan == 8) { |
|
$saylapisan = "BETWEEN 5000001 AND 10000000"; |
|
} else if ($lapisan == 9) { |
|
$saylapisan = "> 10000000"; |
|
} |
|
|
|
if ($kpp == "SEMUA" && $seksi == "SEMUA" && $nip == "SEMUA") { |
|
$array |
|
= ['KWLADM' => $kwl ]; |
|
} else if($kpp != "SEMUA" && $seksi == "SEMUA" && $nip == "SEMUA") { |
|
$array |
|
= [ 'KWLADM' => $kwl, 'KPPADM' => $kpp]; |
|
}else if ($kpp != "SEMUA" && $seksi != "SEMUA" && $nip == "SEMUA"){ |
|
$array |
|
= [ 'KWLADM' => $kwl, 'KPPADM' => $kpp, 'KODESIE' => $seksi]; |
|
} |
|
|
|
else if ($kpp != "SEMUA" && $seksi != "SEMUA" && $nip != "SEMUA") { |
|
$array |
|
= [ 'KWLADM' => $kwl, 'KODESIE' => $seksi, 'NIP' => $nip]; |
|
} |
|
$this->builder()->where('TAHUN', $tahun) |
|
->where($array) |
|
->where('ROUND(TOTAL) ' . $saylapisan . '') |
|
->limit($limit, $offset); |
|
|
|
return $this; |
|
} |
|
|
|
|
|
|
|
function cdetSOF($param) |
|
{ |
|
$tahun = $param['tahun']; |
|
$kwl = $param['kwl']; |
|
$kpp = $param['kpp']; |
|
$seksi = $param['seksi']; |
|
$nip = $param['nip']; |
|
$lapisan = $param['lapisan']; |
|
$offset = $param['page']; |
|
$limit = $param['perPage']; |
|
if ($lapisan == 1) { |
|
$saylapisan = "<= 0"; |
|
} else if ($lapisan == 2) { |
|
$saylapisan = "BETWEEN 1 AND 5000"; |
|
} else if ($lapisan == 3) { |
|
$saylapisan = "BETWEEN 5001 AND 10000"; |
|
} else if ($lapisan == 4) { |
|
$saylapisan = "BETWEEN 10001 AND 100000"; |
|
} else if ($lapisan == 5) { |
|
$saylapisan = "BETWEEN 100001 AND 500000"; |
|
} else if ($lapisan == 6) { |
|
$saylapisan = "BETWEEN 500001 AND 1000000"; |
|
} else if ($lapisan == 7) { |
|
$saylapisan = "BETWEEN 1000001 AND 5000000"; |
|
} else if ($lapisan == 8) { |
|
$saylapisan = "BETWEEN 5000001 AND 10000000"; |
|
} else if ($lapisan == 9) { |
|
$saylapisan = "> 10000000"; |
|
} |
|
if ($kpp == "SEMUA" && $seksi == "SEMUA" && $nip == "SEMUA") { |
|
$array |
|
= ['KWLADM' => $kwl ]; |
|
} else if($kpp != "SEMUA" && $seksi == "SEMUA" && $nip == "SEMUA") { |
|
$array |
|
= [ 'KWLADM' => $kwl, 'KPPADM' => $kpp]; |
|
}else if ($kpp != "SEMUA" && $seksi != "SEMUA" && $nip == "SEMUA"){ |
|
$array |
|
= [ 'KWLADM' => $kwl, 'KPPADM' => $kpp, 'KODESIE' => $seksi]; |
|
} |
|
|
|
else if ($kpp != "SEMUA" && $seksi != "SEMUA" && $nip != "SEMUA") { |
|
$array |
|
= [ 'KWLADM' => $kwl, 'KODESIE' => $seksi, 'NIP' => $nip]; |
|
} |
|
|
|
$builder = $this->db->table('DASHBOARD_WP'); |
|
$builder->where('TAHUN', $tahun); |
|
$builder->where($array); |
|
$builder->where('ROUND(TOTAL) ' . $saylapisan . ''); |
|
$builder->limit($limit, $offset); |
|
|
|
return $builder->get(); |
|
} |
|
}
|
|
|