<?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();
    }
}