<?php

namespace App\Models\Admin;

use CodeIgniter\Shield\Models\UserModel;

class UsersModel extends UserModel
{
    protected $table         = 'users';
    protected $primaryKey    = 'id';
    protected $sort          = 'DESC';
    protected $allowedFields = ['username', 'nip', 'unit', 'jabatan', 'id_kantor', 'whatsapp', 'tfa', 'stfa', 'avatar', 'status', 'status_message', 'active', 'last_active', 'deleted_at'];

    // Validation
    protected $validationRules = [
        'username' => 'required',
    ];

    //get nip utk validasi login SSO
    public function get_by_nip($nip)
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('users');
        $builder->select('nip');
        $builder->where('nip', $nip);
        $builder->where('active', 1);

        return $builder->countAllResults();
    }

    public function get_by_id($id)
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('users as u');

        $builder->select('u.*, a.secret, b.nama_sunit, c.nama_sjab, d.satker as nama_kantor');

        $builder->join('auth_identities as a', 'a.user_id = u.id');
        $builder->join('pdk_unit as b', 'b.id = u.unit', 'left');
        $builder->join('pdk_jabatan as c', 'c.id = u.jabatan', 'left');
        $builder->join('pdk_satker as d', 'd.id = u.id_kantor', 'left');

        $builder->where('u.id', $id);
        $builder->where('a.type', 'email_password');

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

    //ambil nama dan kontak
    public function get_nakonby_id($id)
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('users');

        $builder->select('username, whatsapp');
        $builder->where('id', $id);

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

    public function get_total($filterRules = null)
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('users as u');
        $builder->select('u.id, b.nama_sunit, c.nama_sjab, d.satker as nama_kantor');
        $builder->join('auth_identities as a', 'a.user_id = u.id');
        $builder->join('pdk_unit as b', 'u.unit = b.id', 'left');
        $builder->join('pdk_jabatan as c', 'u.jabatan = c.id', 'left');
        $builder->join('pdk_satker as d', 'd.id = u.id_kantor', '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) !== '') {
                            $builder->orLike($filter->field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $builder->like($filter->field, trim(urldecode($value)));
                }
            }
        }

        $builder->groupStart();
        $builder->where('a.type', 'email_password');
        $builder->groupEnd();

        return $builder->countAllResults();
    }

    public function get_rows($limit, $start = 0, $filterRules = null, $sortOrder = null)
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('users as u');
        $builder->select('u.*, a.secret, b.nama_sunit, c.nama_sjab, d.satker as nama_kantor');
        $builder->join('auth_identities as a', 'a.user_id = u.id');
        $builder->join('pdk_unit as b', 'u.unit = b.id', 'left');
        $builder->join('pdk_jabatan as c', 'u.jabatan = c.id', 'left');
        $builder->join('pdk_satker as d', 'd.id = u.id_kantor', '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) !== '') {
                            $builder->orLike($filter->field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $builder->like($filter->field, trim(urldecode($value)));
                }
            }
        }

        if ($sortOrder) {
            $builder->orderBy($sortOrder);
        } else {
            $builder->orderBy('u.username', 'asc');
        }

        $builder->limit($limit, $start);

        $builder->groupStart();
        $builder->where('a.type', 'email_password');
        $builder->groupEnd();

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

    public function get_excel($limit, $filterRules = null, $sortOrder = null)
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('users as u');
        $builder->select('u.*, a.secret, b.nama_sunit, c.nama_sjab, d.satker as nama_kantor');
        $builder->join('auth_identities as a', 'a.user_id = u.id');
        $builder->join('pdk_unit as b', 'u.unit = b.id', 'left');
        $builder->join('pdk_jabatan as c', 'u.jabatan = c.id', 'left');
        $builder->join('pdk_satker as d', 'd.id = u.id_kantor', '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) !== '') {
                            $builder->orLike($filter->field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $builder->like($filter->field, trim(urldecode($value)));
                }
            }
        }

        if ($sortOrder) {
            $builder->orderBy($sortOrder);
        } else {
            $builder->orderBy('u.id', 'desc');
        }

        if ((int) $limit !== 0) {
            $builder->limit($limit, 0);
        }

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

    function dd_pdk_unit()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_unit');
        $result = $builder->get()->getResult();

        $option[''] = 'Please Select';
        foreach ($result as $res) {
                $option[$res->id] = $res->nama_sunit;
        }
        return $option;
    }

    function dd_pdk_jabatan()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_jabatan');
        $result = $builder->get()->getResult();

        $option[''] = 'Please Select';
        foreach ($result as $res) {
                $option[$res->id] = $res->nama_sjab;
        }
        return $option;
    }

    function dd_pdk_satker()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker');
        $builder->select('id, satker');
        $builder->orderBy('satker', 'ASC');
        $result = $builder->get()->getResult();

        $option[''] = 'Please Select';
        foreach ($result as $res) {
                $option[$res->id] = $res->satker;
        }
        return $option;
    }
}
