<?php

namespace App\Models;

use CodeIgniter\Model;

class MKanwil extends Model
{


    function getRenpen($param)
    {
        $kwl = $param['kwl'];
        $tahun = $param['tahun'];
        $bln = $param['bln'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $query = $this->db->query("SELECT SUM(JML_TARGET) RENPEN FROM RENPEN_PER_KPP
                                    WHERE TAHUN = '" . $tahun . "'  AND KWLADM = '" . $kwl . "'  $cekkpp AND BULAN <= '" . $bln . "'");
        return $query;
    }

    function getPen($param)
    {
        $kwl = $param['kwl'];
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $query = $this->db->query("SELECT SUM(TOTAL)    PEN
                                    FROM DASHBOARD_WP_BLN
                                    WHERE TAHUN = '" . $tahun . "'  AND KWLADM = '" . $kwl . "' $cekkpp  AND BULAN <= '" . $bln . "'
                                    ");
        return $query;
    }

    function getPenl($param)
    {
        $kwl = $param['kwl'];
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $query = $this->db->query("SELECT SUM(TOTAL)    PEN
                                    FROM DASHBOARD_WP_BLN
                                    WHERE TAHUN= '" . $tahunl . "'  AND KWLADM = '" . $kwl . "' $cekkpp AND BULAN <= '" . $bln . "'");
        return $query;
    }


    function getKat($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $query = $this->db->query("SELECT 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
                            GROUP BY KATEGORI, NM_KATEGORI ORDER BY KATEGORI ASC) 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
                            GROUP BY KD_KATEGORI, NM_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'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $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
                                    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)  PENERIMAAN_TOT,
                                    (SELECT ROUND(SUM(TOTAL)) FROM KWLPERJENIS_MV WHERE THNBYR = '" . $tahunl . "' AND BLNBYR <= '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp) 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
                                    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'];
        $bln = $param['bln'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $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
                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'];
        $bln = $param['bln'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "' ";
        }
        $query = $this->db->query("SELECT NO,LAPISAN,WPBYR,TOTAL,
                        (SELECT SUM(WPBYR) FROM KWLSOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "'  AND KWLADM = '" . $kwl . "' $cekkpp) TOTWPBYR,
                        (SELECT SUM(ROUND(TOTAL)) FROM KWLSOFNAS WHERE TAHUN = '" . $tahun . "'  AND BULAN = '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp) TOTALL
                        FROM (
                        SELECT NO,LAPISAN,SUM(WPBYR) WPBYR,SUM(ROUND(TOTAL)) TOTAL FROM KWLSOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "' AND KWLADM = '" . $kwl . "' $cekkpp
                        GROUP BY NO,LAPISAN)  ORDER BY NO ASC ");
        return $query;
    }


    function getWpsk($param)
    {
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $query = $this->db->query("SELECT SUM(CASE WHEN FLAG_WPS_WPK = 'WPK' THEN ROUND(JUMLAHWP) ELSE 0 END) WPK,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPS' THEN ROUND(JUMLAHWP) ELSE 0 END) WPS
                                FROM WPSK_MV WHERE KWLADM = '" . $kwl . "' $cekkpp");
        return $query;
    }

    function getBreakdownPen($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $query = $this->db->query("SELECT 
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPS' AND THNBYR = '" . $tahun . "' AND BLNBYR = '" . $bln . "' THEN ROUND(WPBYR) ELSE 0 END) WPSWP,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPS' AND THNBYR = '" . $tahunl . "' AND BLNBYR = '" . $bln . "' THEN ROUND(WPBYR) ELSE 0 END) WPSWPL,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPS' AND THNBYR = '" . $tahun . "' AND BLNBYR = '" . $bln . "' THEN ROUND(TOTAL) ELSE 0 END) WPSBYRN,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPS' AND THNBYR = '" . $tahunl . "' AND BLNBYR = '" . $bln . "' THEN ROUND(TOTAL) ELSE 0 END) WPSBYRP,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPK' AND THNBYR = '" . $tahun . "' AND BLNBYR = '" . $bln . "' THEN ROUND(WPBYR) ELSE 0 END) WPKWP,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPK' AND THNBYR = '" . $tahunl . "' AND BLNBYR = '" . $bln . "' THEN ROUND(WPBYR) ELSE 0 END) WPKWPL,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPK' AND THNBYR = '" . $tahun . "' AND BLNBYR = '" . $bln . "' THEN ROUND(TOTAL) ELSE 0 END) WPKBYRN,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPK' AND THNBYR = '" . $tahunl . "' AND BLNBYR = '" . $bln . "' THEN ROUND(TOTAL) ELSE 0 END) WPKBYRP
                                FROM BREAKDOWN_PEN WHERE KWLADM = '" . $kwl . "' $cekkpp ");
        return $query;
    }

    function getBdownfull($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $query = $this->db->query("SELECT 
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPS' AND THNBYR = '" . $tahun . "'  THEN ROUND(WPBYR) ELSE 0 END) WPSWP,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPS' AND THNBYR = '" . $tahunl . "'  THEN ROUND(WPBYR) ELSE 0 END) WPSWPL,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPK' AND THNBYR = '" . $tahun . "'  THEN ROUND(WPBYR) ELSE 0 END) WPKWP,
                                SUM(CASE WHEN FLAG_WPS_WPK = 'WPK' AND THNBYR = '" . $tahunl . "'  THEN ROUND(WPBYR) ELSE 0 END) WPKWPL
                                FROM BREAKDOWN_PENFULL WHERE KWLADM = '" . $kwl . "' $cekkpp ");
        return $query;
    }

    function getWPterdaftar($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }

        $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");
        return $query;
    }

    function laporTahunan($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }
        $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");
        return $query;
    }

    function getSektdom($param)
    {
        $tahun = $param['tahun'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }

        $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
                        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'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }

        $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");
        return $query;
    }

    function getPPMPKM($param)
    {
        $tahun = $param['tahun'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $bln = $param['bln'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }

        $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 ");
        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'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }

        $query = $this->db->query(" SELECT '$tahun' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun . "' AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun1' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun1 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun2' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun2 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun3' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun3 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun4' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun4 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL 
                                    SELECT '$tahun5' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun5 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun6' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun6 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun7' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun7 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun8' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun8 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun9' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun9 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    UNION ALL
                                    SELECT '$tahun10' TAHUN,
                                    SUM(JUMLAHWP) JUMLAHWP FROM WP_TERDAFTAR 
                                    WHERE TAHUN <= '" . $tahun10 . "'  AND KWLADM = '" . $kwl . "' $cekkpp
                                    ");
        return $query;
    }

    function getWPBYR($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }


        $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 
                                    GROUP BY NPWP,THNBYR,BLNBYR)
                                    GROUP BY BLNBYR ORDER BY BLNBYR ASC
                                    ");
        return $query;
    }

    function getPopulasi($param)
    {

        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        if ($kpp == "SEMUA") {
            $cekkpp = "";
        } else {
            $cekkpp = "AND KPPADM = '" . $kpp . "'";
        }


        $query = $this->db->query("SELECT KATEGORI,SUM(POPULASI) POPULASI
        FROM POPULASI_KATEGORI WHERE KWLADM = '" . $kwl . "' $cekkpp
        GROUP BY KATEGORI
        ORDER BY KD_KATEGORI ASC
                                    ");
        return $query;
    }
}