<?php

namespace App\Models;

use CodeIgniter\Model;

class Mpemby extends Model
{

    protected $table = 'DASHBOARD_BESAR_MAPKJS';
    protected $allowedFields = ['NPWP', 'NAMA', 'THNBYR', 'TOTAL'];

    function getRenpen($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bln'];
        $query = $this->db->query("SELECT SUM(JML_TARGET) RENPEN FROM RENPEN_PER_KPP
                                    WHERE TAHUN = '" . $tahun . "' and BULAN <= '" . $bln . "'");
        return $query;
    }

    function getPen($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bln'];
        $tahunl = $tahun - 1;
        $query = $this->db->query("SELECT SUM(TOTAL)    PEN
                                    FROM DASHBOARD_WP_BLN
                                    WHERE TAHUN = '" . $tahun . "' AND BULAN BETWEEN '01' AND '" . $bln . "' 
                                    ");
        return $query;
    }

    function getPenl($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bln'];
        $tahunl = $tahun - 1;
        $query = $this->db->query("SELECT SUM(TOTAL)    PEN
                                    FROM DASHBOARD_WP_BLN
                                    WHERE TAHUN= '" . $tahunl . "' AND BULAN BETWEEN '01' AND '" . $bln . "'  ");
        return $query;
    }


    function getKat($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $tw1 = array('01', '02', '03');
        $tw2 = array('04', '05', '06');
        $tw3 = array('07', '08', '09');
        $tw4 = array('10', '11', '12');
        if (in_array($bln, $tw1)) {
            $cektw = "TWI";
        } else if (in_array($bln, $tw2)) {
            $cektw = "TWII";
        } else if (in_array($bln, $tw3)) {
            $cektw = "TWIII";
        } else if (in_array($bln, $tw4)) {
            $cektw = "TWIV";
        }


        $query = $this->db->query("SELECT B.KODE,NMKAT,POPULASI,WPBYRN,WPBYRP,KPENN,KPENP,GDP 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 
                            GROUP BY KATEGORI,NM_KATEGORI ORDER BY KATEGORI ASC NULLS LAST) A LEFT JOIN(
                                        SELECT CASE WHEN KATEGORI IS NULL THEN 'ZZ' ELSE KATEGORI END KODE,
                                        SUM(CASE WHEN THNBYR = '" . $tahun . "' AND BLNBYR = '" . $bln . "'  THEN ROUND(WPBAYAR) ELSE 0 END)WPBYRN,
                                        SUM(CASE WHEN THNBYR = '" . $tahunl . "'   AND BLNBYR = '" . $bln . "'  THEN ROUND(WPBAYAR) ELSE 0 END)WPBYRP,
                                        SUM(CASE WHEN THNBYR = '" . $tahun . "'   AND BLNBYR = '" . $bln . "'  THEN ROUND(TOTAL) ELSE 0 END) KPENN,
                                        SUM(CASE WHEN THNBYR = '" . $tahunl . "'   AND BLNBYR = '" . $bln . "'  THEN ROUND(TOTAL) ELSE 0 END) KPENP
                                    FROM PERKATEGORI_MV 
                                    GROUP BY KATEGORI
                                    ) B ON A.KATEGORI = B.KODE
                                    LEFT JOIN (SELECT KODE,$cektw*1000000000 GDP FROM REF_BI_GDP WHERE TAHUN  = '" . $tahunl . "') C ON A.KATEGORI = C.KODE
                                   
                                    ");
        return $query;
    }


    function getPiePen($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $query = $this->db->query("SELECT NM_PAJAK,TOTALN PENERIMAAN, TOTALP PENL,
                                    (SELECT ROUND(SUM(TOTAL)) FROM PERJENIS_MV WHERE THNBYR = '" . $tahun . "'  AND BLNBYR <= '" . $bln . "')  PENERIMAAN_TOT,
                                    (SELECT ROUND(SUM(TOTAL)) FROM PERJENIS_MV WHERE THNBYR = '" . $tahunl . "' AND BLNBYR <= '" . $bln . "') PENERIMAAN_TOTL
                                    FROM (
                                    SELECT NM_PAJAK,ROUND(SUM(CASE WHEN THNBYR= '" . $tahun . "' AND BLNBYR <= '" . $bln . "' THEN ROUND(TOTAL) ELSE 0 END)) TOTALN,
                                    ROUND(SUM(CASE WHEN THNBYR= '" . $tahunl . "' AND BLNBYR <= '" . $bln . "' THEN ROUND(TOTAL) ELSE 0 END)) TOTALP
                                    FROM PERJENIS_MV 
                                    GROUP BY NM_PAJAK ORDER BY TOTALN DESC) WHERE ROWNUM < = 7
                                    ");
        return $query;
    }


    function paycompNas($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bln'];
        $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 . "'
                GROUP BY NPWP)
                WHERE JMLBLN <= TO_NUMBER('" . $bln . "')
                GROUP BY JMLBLN ORDER BY JMLBLN ASC");
        return $query;
    }

     
    function sofNas($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bln'];
        $query = $this->db->query("SELECT NO,LAPISAN,WPBYR,TOTAL,
                        (SELECT SUM(ROUND(WPBYR)) FROM SOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "') TOTWPBYR,
                        (SELECT SUM(ROUND(TOTAL)) FROM SOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "') TOTALL
                        FROM (
                        SELECT NO,LAPISAN,SUM(ROUND(WPBYR)) WPBYR,SUM(ROUND(TOTAL)) TOTAL FROM SOFNAS WHERE TAHUN = '" . $tahun . "' AND BULAN = '" . $bln . "'
                        GROUP BY NO,LAPISAN)  ORDER BY NO ASC ");
        return $query;
    }


    function getWpsk()
    {
        $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");
        return $query;
    }

    function getBreakdownPen($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $bln = $param['bln'];
        $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");
        return $query;
    }

    function getWPterdaftar($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;


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

    function laporTahunan($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;

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

    function getSektdom($param)
    {
        $tahun = $param['tahun'];
        $query = $this->db->query("SELECT * FROM (
                        SELECT KATEGORI KD_KATEGORI,NM_KATEGORIPDK NM_KATEGORI,SUM(ROUND(TOTAL)) TOTAL FROM PERKATEGORI_MV A
                        LEFT JOIN REF_KLU B ON A.KATEGORI = B.KD_KATEGORI
                        WHERE THNBYR = '" . $tahun . "' 
                        GROUP BY KATEGORI,NM_KATEGORIPDK
                        ORDER BY TOTAL DESC) WHERE ROWNUM <= 5");
        return $query;
    }

    function Wpbyrlpr($param)
    {
        $tahun = $param['tahun'];


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

    function getPPMPKM($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bln'];

        $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 . "' ");
        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;


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

    function getWPBYR($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;

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

    function gmapkjs($param)
    {
        $tahun = $param['tahun'];
        $tahunl = $tahun - 1;
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $seksi = $param['seksi'];
        $nip = $param['nip'];
        $kdmap = $param['kdmap'];
        $kjs = $param['kjs'];
        $kategori = $param['kategori'];

        if ($kwl == "SEMUA") {
            $cekkwl = "";
        } else {
            $cekkwl = "AND KWLADM = '" . $kwl . "'";
        }
        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 ($kjs == "SEMUA") {
            $cekkjs = "";
        } else {
            $cekkjs = "AND KJS = '" . $kjs . "'";
        }
        if ($kategori == "SEMUA") {
            $cekkat = "";
        } else {
            $cekkat = "AND KATEGORI = '" . $kategori . "'";
        }


        if ($kdmap == "SEMUA") {
            $ceksubq = "SELECT SUM(WPLAMA) + SUM(WPBARU) JMLWP FROM DASHBOARD_BESAR_BYRLPR
                    WHERE TAHUN = '" . $tahun . "'  $cekkpp $cekseksi $ceknip ";
            $cekmap = "";
        } else {
            $ceksubq = "SELECT COUNT(NPWP)POPULASI FROM KEWAJIBAN_FLAG
                                    WHERE  KD_MAP = '" . $kdmap . "' $cekkwl  $cekkpp $cekseksi $ceknip  $cekkat";
            $cekmap = "AND KD_MAP  = '" . $kdmap . "'";
        }


        $query = $this->db->query("SELECT 
                                    BLNBYR,
                                    ($ceksubq) POPULASI,
                                    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 THNBYR BETWEEN '" . $tahunl . "' AND '" . $tahun . "' $cekkwl  $cekkpp $cekseksi $ceknip $cekmap $cekkjs $cekkat
                                    GROUP BY NPWP,THNBYR,BLNBYR
                                    )
                                    GROUP BY BLNBYR ORDER BY BLNBYR ASC
                                    ");
        return $query;
    }

    function getPPM($param)
    {

        $tahun = $param['tahun'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $seksi = $param['seksi'];
        $nip = $param['nip'];
        $kdmap = $param['kdmap'];
        $kjs = $param['kjs'];
        $offset = $param['page'];
        $limit = $param['perPage'];

        if ($kjs == "SEMUA") {
            $array =
                ['THNBYR' => $tahun, 'KWLADM' => $kwl, 'KPPADM' => $kpp, 'KODESIE' => $seksi, 'NIP' => $nip, 'KD_MAP' => $kdmap];
        } else {
            $array
                = ['THNBYR' => $tahun, 'KWLADM' => $kwl, 'KPPADM' => $kpp, 'KODESIE' => $seksi, 'NIP' => $nip, 'KD_MAP' => $kdmap, 'KJS' => $kjs];
        }
        $this->builder()->select('NPWP,NAMA')
            ->selectSum("CASE WHEN BLNBYR = '01' THEN TOTAL ELSE 0 END", 'JAN')
            ->selectSum("CASE WHEN BLNBYR = '02' THEN TOTAL ELSE 0 END", 'FEB')
            ->selectSum("CASE WHEN BLNBYR = '03' THEN TOTAL ELSE 0 END", 'MAR')
            ->selectSum("CASE WHEN BLNBYR = '04' THEN TOTAL ELSE 0 END", 'APR')
            ->selectSum("CASE WHEN BLNBYR = '05' THEN TOTAL ELSE 0 END", 'MEI')
            ->selectSum("CASE WHEN BLNBYR = '06' THEN TOTAL ELSE 0 END", 'JUN')
            ->selectSum("CASE WHEN BLNBYR = '07' THEN TOTAL ELSE 0 END", 'JUL')
            ->selectSum("CASE WHEN BLNBYR = '08' THEN TOTAL ELSE 0 END", 'AGU')
            ->selectSum("CASE WHEN BLNBYR = '09' THEN TOTAL ELSE 0 END", 'SEP')
            ->selectSum("CASE WHEN BLNBYR = '10' THEN TOTAL ELSE 0 END", 'OKT')
            ->selectSum("CASE WHEN BLNBYR = '11' THEN TOTAL ELSE 0 END", 'NOP')
            ->selectSum("CASE WHEN BLNBYR = '12' THEN TOTAL ELSE 0 END", 'DES')
            ->where($array)
            ->groupBy('NPWP,NAMA')
            ->limit($limit, $offset);

        return $this;
    }

    function cgetPPM($param)
    {
        $tahun = $param['tahun'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $seksi = $param['seksi'];
        $nip = $param['nip'];
        $kdmap = $param['kdmap'];
        $kjs = $param['kjs'];

        if ($kjs == "SEMUA") {
            $array =
                ['THNBYR' => $tahun, 'KWLADM' => $kwl, 'KPPADM' => $kpp, 'KODESIE' => $seksi, 'NIP' => $nip, 'KD_MAP' => $kdmap];
        } else {
            $array
                = ['THNBYR' => $tahun, 'KWLADM' => $kwl, 'KPPADM' => $kpp, 'KODESIE' => $seksi, 'NIP' => $nip, 'KD_MAP' => $kdmap, 'KJS' => $kjs];
        }

        $builder = $this->db->table('DASHBOARD_BESAR_MAPKJS');
        $builder->select('NPWP,NAMA');
        $builder->where($array);

        return $builder->get();
    }


    function getMAPKJSPoC($param)
    {
        $tahun = $param['tahun'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $seksi = $param['seksi'];
        $nip = $param['nip'];
        $kdmap = $param['kdmap'];
        $kjs = $param['kjs'];
        $kategori = $param['kategori'];

        if ($kwl == "SEMUA") {
            $cekkwl = "";
        } else {
            $cekkwl = "AND KWLADM = '" . $kwl . "'";
        }
        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 ($kjs == "SEMUA") {
            $cekkjs = "";
        } else {
            $cekkjs = "AND KJS = '" . $kjs . "'";
        }
        if ($kategori == "SEMUA") {
            $cekkat = "";
        } else {
            $cekkat = "AND KATEGORI = '" . $kategori . "'";
        }


        if ($kdmap == "SEMUA") {
            $cekmap = "";
        } else {
            $cekmap = "AND KD_MAP  = '" . $kdmap . "'";
        }

        $bln = date('m');


        $query = $this->db->query(" SELECT JMLBLN JMLBULAN, COUNT (1) JMLWP
                        FROM (  SELECT NPWP,  COUNT(1) JMLBLN
                                FROM (SELECT NPWP,THNBYR,BLNBYR,KWLADM, KPPADM, KODESIE, NIP,COUNT(1) FROM DASHBOARD_BESAR_MAPKJS
                                WHERE THNBYR = '" . $tahun . "'
                                    $cekkwl
                                    $cekkpp
                                    $cekseksi
                                    $ceknip
                                    $cekmap 
                                    $cekkjs 
                                    $cekkat
                                GROUP BY NPWP,THNBYR,BLNBYR,KWLADM, KPPADM, KODESIE, NIP
                                )   
                            GROUP BY NPWP)
                    WHERE JMLBLN <= TO_NUMBER ('" . $bln . "')
                    GROUP BY JMLBLN
                    ORDER BY JMLBLN ASC");
        return $query;

    }


    function getMAPKJSSoF($param)
    {
        $tahun = $param['tahun'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $seksi = $param['seksi'];
        $nip = $param['nip'];
        $kdmap = $param['kdmap'];
        $kjs = $param['kjs'];
        $kategori = $param['kategori'];

        if ($kwl == "SEMUA") {
            $cekkwl = "";
        } else {
            $cekkwl = "AND KWLADM = '" . $kwl . "'";
        }
        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 ($kjs == "SEMUA") {
            $cekkjs = "";
        } else {
            $cekkjs = "AND KJS = '" . $kjs . "'";
        }
        if ($kategori == "SEMUA") {
            $cekkat = "";
        } else {
            $cekkat = "AND KATEGORI = '" . $kategori . "'";
        }


        if ($kdmap == "SEMUA") {
            $cekmap = "";
        } else {
            $cekmap = "AND KD_MAP  = '" . $kdmap . "'";
        }

        $bln = date('m');


        $query = $this->db->query("SELECT NPWP,SUM(TOTAL)TOTAL FROM DASHBOARD_BESAR_MAPKJS
                                WHERE THNBYR = '" . $tahun . "'
                                    $cekkwl
                                    $cekkpp
                                    $cekseksi
                                    $ceknip
                                    $cekmap 
                                    $cekkjs 
                                    $cekkat
                                GROUP BY NPWP");
        return $query;

    }

    function wpsk_paycompNas($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bulan'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $source = $param['src'];
        if ($kwl == 'SEMUA') {
            $cekkwl = "";
        }else{
            $cekkwl = "AND KWLADM = '".$kwl."'";
        }

        if ($kpp == 'SEMUA'){
            $cekkpp = "";
        }else{
            $cekkpp = "AND KPPADM = '".$kpp."'";
        }
        if($source == '1'){
            $src = "WPS";
        }else{
            $src = "WPK";
        }
        $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 FLAG_WPS_WPK = '".$src."' $cekkwl $cekkpp
                GROUP BY NPWP)
                WHERE JMLBLN <= TO_NUMBER('".$bln."')
                GROUP BY JMLBLN ORDER BY JMLBLN ASC");
        return $query;
    }

     function wpsk_sofNas($param)
    {
        $tahun = $param['tahun'];
        $bln = $param['bulan'];
         $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $source = $param['src'];
         if ($kwl == 'SEMUA') {
            $cekkwl = "";
        }else{
            $cekkwl = "AND KWLADM = '".$kwl."'";
        }

        if ($kpp == 'SEMUA'){
            $cekkpp = "";
        }else{
            $cekkpp = "AND KPPADM = '".$kpp."'";
        }
        if($source == '1'){
            $src = "WPS";
        }else{
            $src = "WPK";
        }
        $query = $this->db->query("SELECT NPWP,SUM(TOTAL)TOTAL FROM DASHBOARD_WP_BLN
                                WHERE TAHUN = '" . $tahun . "' AND BULAN <= '".$bln."' 
                                AND FLAG_WPS_WPK = '".$src."' $cekkwl $cekkpp
                                GROUP BY NPWP");
        return $query;
    }
}