<?php

namespace App\Models\Referensi;

use App\Models\BaseModel;

class PdksatkerModel extends BaseModel
{
    protected $table            = 'pdk_satker';
    protected $primaryKey       = 'id';
    protected $allowedFields    = ['satker', 'alamat', 'lati', 'longi', 'prov', 'kako', 'kodepos', 'tel1', 'mail1', 'tipe', 'id_kanwil', 'id_area'];

    public function find_by_id($id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker as m');
        $builder->select('m.*, p.nama_prov, k.nama_kako');
        $builder->join('pdk_prov as p', 'm.prov = p.id', 'left');
        $builder->join('pdk_kako as k', 'm.kako = k.id', 'left');
        $builder->where('m.id', $id);

        return $builder->get()->getRow();
    }

    function dt_prov()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_prov');
        $builder->select('id, nama_prov');
        return $builder->get()->getResult();
    }

    function dt_kako($id_prov)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_kako');
        $builder->select('id, nama_kako');
        $builder->where('id_prov', $id_prov);
        return $builder->get()->getResult();
    }

    function dt_wilayah()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker');
        $builder->select('id, satker');
        $builder->where('tipe', 'Wilayah');
        $builder->orderBy('satker', 'ASC');
        return $builder->get()->getResult();
    }

    function dt_area($id_wilayah)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker');
        $builder->select('id, satker');
        $builder->where('tipe', 'Area');
        $builder->where('id_kanwil', $id_wilayah);
        $builder->orderBy('satker', 'ASC');
        return $builder->get()->getResult();
    }

    function get_rows2($limit, $start = 0, $filterRules = NULL, $sortOrder = NULL) {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker as m');
        $builder->select('m.id, m.satker, m.tel1, m.mail1, m.tipe, p.nama_prov, k.nama_kako, w.satker as nama_kanwil, a.satker as nama_area');
        $builder->join('pdk_prov as p', 'm.prov = p.id', 'left');
        $builder->join('pdk_kako as k', 'm.kako = k.id', 'left');
        $builder->join('pdk_satker as w', 'm.id_kanwil = w.id', 'left');
        $builder->join('pdk_satker as a', 'm.id_area = a.id', 'left');
        
        if ($filterRules) {
            $filterRules = json_decode($filterRules);

            foreach ($filterRules as $filter) {
                $value = $filter->value;
                $pecah = explode(';', $value);
                if (count($pecah) > 1) {
                    $builder->groupStart();
                    foreach ($pecah as $p) {
                        if (trim($p) <> '') {
                            $field = $filter->field;
                            if (trim($filter->field) == 'satker') {
                                $field = 'm.satker';
                            }
                            if (trim($filter->field) == 'tel1') {
                                $field = 'm.tel1';
                            }
                            if (trim($filter->field) == 'mail1') {
                                $field = 'm.mail1';
                            }
                            if (trim($filter->field) == 'mail_keu') {
                                $field = 'm.mail_keu';
                            }
                            if (trim($filter->field) == 'nama_prov') {
                                $field = 'p.nama_prov';
                            }
                            if (trim($filter->field) == 'nama_kako') {
                                $field = 'k.nama_kako';
                            }
                            if (trim($filter->field) == 'tipe') {
                                $field = 'm.tipe';
                            }
                            if (trim($filter->field) == 'nama_kanwil') {
                                $field = 'w.satker';
                            }
                            if (trim($filter->field) == 'nama_area') {
                                $field = 'a.satker';
                            }
                            $builder->orLike($field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $field = $filter->field;
                    if (trim($filter->field) == 'satker') {
                        $field = 'm.satker';
                    }
                    if (trim($filter->field) == 'tel1') {
                        $field = 'm.tel1';
                    }
                    if (trim($filter->field) == 'mail1') {
                        $field = 'm.mail1';
                    }
                    if (trim($filter->field) == 'mail_keu') {
                        $field = 'm.mail_keu';
                    }
                    if (trim($filter->field) == 'nama_prov') {
                        $field = 'p.nama_prov';
                    }
                    if (trim($filter->field) == 'nama_kako') {
                        $field = 'k.nama_kako';
                    }
                    if (trim($filter->field) == 'tipe') {
                        $field = 'm.tipe';
                    }
                    if (trim($filter->field) == 'nama_kanwil') {
                        $field = 'w.satker';
                    }
                    if (trim($filter->field) == 'nama_area') {
                        $field = 'a.satker';
                    }
                    $builder->like($field, trim(urldecode($value)));
                }
            }
        }

        if ($sortOrder) {
            $builder->orderBy($sortOrder);
        } else {
            if ($this->orderBy <> '') {
                $builder->orderBy($this->orderBy);
            } else {
                $builder->orderBy($this->primaryKey, $this->sort);
            }
        }

        $builder->limit($limit, $start);
                
        return $builder->get()->getResult();
    }

    public function get_total2($filterRules = null)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker as m');
        $builder->select('m.id, m.satker, m.tel1, m.mail1, m.tipe, p.nama_prov, k.nama_kako, w.satker as nama_kanwil, a.satker as nama_area');
        $builder->join('pdk_prov as p', 'm.prov = p.id', 'left');
        $builder->join('pdk_kako as k', 'm.kako = k.id', 'left');
        $builder->join('pdk_satker as w', 'm.id_kanwil = w.id', 'left');
        $builder->join('pdk_satker as a', 'm.id_area = a.id', 'left');

        if ($filterRules) {
            $filterRules = json_decode($filterRules);
            //var_dump(('Id MOdUL = '. $id_modul)); die();
            foreach ($filterRules as $filter) {
                $value = $filter->value;
                $pecah = explode(';', $value);
                if (count($pecah) > 1) {
                    $builder->groupStart();
                    foreach ($pecah as $p) {
                        if (trim($p) <> '') {
                            $field = $filter->field;
                            if (trim($filter->field) == 'satker') {
                                $field = 'm.satker';
                            }
                            if (trim($filter->field) == 'tel1') {
                                $field = 'm.tel1';
                            }
                            if (trim($filter->field) == 'mail1') {
                                $field = 'm.mail1';
                            }
                            if (trim($filter->field) == 'mail_keu') {
                                $field = 'm.mail_keu';
                            }
                            if (trim($filter->field) == 'nama_prov') {
                                $field = 'p.nama_prov';
                            }
                            if (trim($filter->field) == 'nama_kako') {
                                $field = 'k.nama_kako';
                            }
                            if (trim($filter->field) == 'tipe') {
                                $field = 'm.tipe';
                            }
                            if (trim($filter->field) == 'nama_kanwil') {
                                $field = 'w.satker';
                            }
                            if (trim($filter->field) == 'nama_area') {
                                $field = 'a.satker';
                            }
                            $builder->orLike($field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $field = $filter->field;
                    if (trim($filter->field) == 'satker') {
                        $field = 'm.satker';
                    }
                    if (trim($filter->field) == 'tel1') {
                        $field = 'm.tel1';
                    }
                    if (trim($filter->field) == 'mail1') {
                        $field = 'm.mail1';
                    }
                    if (trim($filter->field) == 'mail_keu') {
                        $field = 'm.mail_keu';
                    }
                    if (trim($filter->field) == 'nama_prov') {
                        $field = 'p.nama_prov';
                    }
                    if (trim($filter->field) == 'nama_kako') {
                        $field = 'k.nama_kako';
                    }
                    if (trim($filter->field) == 'tipe') {
                        $field = 'm.tipe';
                    }
                    if (trim($filter->field) == 'nama_kanwil') {
                        $field = 'w.satker';
                    }
                    if (trim($filter->field) == 'nama_area') {
                        $field = 'a.satker';
                    }
                    $builder->like($field, trim(urldecode($value)));
                }
            }
        }

        return $builder->countAllResults();
    }
}