engineN/app/Models/Mbyrlpr.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class Mbyrlpr extends Model
{


    protected $table = 'DASHBOARD_BYRLPR';

    function cdetKat($param)
    {
        $tahun = $param['tahun'];
        $kwl = $param['kwl'];
        $kpp = $param['kpp'];
        $seksi = $param['seksi'];
        $nip = $param['nip'];
        $pilihan = $param['pilihan'];

        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 ($pilihan == '1') {
            $cekpil = "AND JMLBYR IS NOT NULL AND BYRLALU IS NULL";
        } else if ($pilihan == '2') {
            $cekpil = "AND JMLBYR IS NULL AND BYRLALU IS NOT NULL";
        }
        $query = $this->db->query("SELECT COUNT(1) NUMROWS FROM DASHBOARD_BYRLPR WHERE TAHUN = '" . $tahun . "' AND KWLADM = '" . $kwl . "'  $cekpil $cekkpp $cekseksi $ceknip");
        return $query;
    }

    function detKat($param)
    {

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

        if ($pilihan == '1') {
            $jmlbyrn = "JMLBYR IS NOT NULL";
            $jmlbyrp = "BYRLALU IS NULL";
        } else if ($pilihan == '2') {
            $jmlbyrn = "JMLBYR IS NULL";
            $jmlbyrp = "BYRLALU IS NOT NULL";
        }

        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_AR' => $nip];
        }

        $this->builder()
            ->where('TAHUN', $tahun)
            ->where($array)
            ->where($jmlbyrn)
            ->where($jmlbyrp)
            ->limit($limit,  $offset);
        return $this;
    }
}