<?php

namespace App\Models\Admin;

use App\Models\BaseModel;

class UsergroupModel extends BaseModel
{
    protected $table            = 'usergroups';
    protected $primaryKey       = 'id_ug';
    protected $allowedFields    = ['user_id', 'group_id'];

    // Validation
    protected $validationRules = [
		'user_id' => [
            'label' => 'user id',
            'rules' => 'required',
        ],
		'group_id' => [
            'label' => 'group id',
            'rules' => 'required',
        ],
    ];

    function get_modul_by_user ($id_user)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('moduls as m');
        $builder->select('m.id_modul, m.nama_modul, m.ket');
        $subQuery = $db->table('usergroups as u')->select('r.id_modul')->join('rolegroups as r', 'u.group_id = r.id_group')->where('u.user_id', $id_user);
        $builder->whereNotIn('m.id_modul', $subQuery);
        
        return $builder->get()->getResult();
    }

    //ambil rolegroup yg array id modulnya ar_id_modul
    function get_group_by_modul ($ar_id_modul)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('rolegroups');
        $builder->select('id_group, id_modul, nama_group');
        $builder->whereIn('id_modul', $ar_id_modul);
        
        return $builder->get()->getResult();
    }

    //ambil 1 data privilege untuk edit
    public function find_by_id($id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('usergroups as u');
        $builder->select('u.*, r.nama_group, m.id_modul, m.nama_modul');
        $builder->join('rolegroups as r', 'r.id_group = u.group_id', 'left');
        $builder->join('moduls as m', 'm.id_modul = r.id_modul', 'left');

        $builder->where('id_ug', $id);

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

    //ambil rolegroup yg id modulnya id_modul (untuk edit)
    function get_rolegroup_by_modul ($id_modul)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('rolegroups');
        $builder->select('id_group, nama_group');
        $builder->where('id_modul', $id_modul);
        
        return $builder->get()->getResult();
    }

    function get_rows2($id_usr, $limit, $start = 0, $filterRules = NULL, $sortOrder = NULL) {
        $db = \Config\Database::connect();
        $builder = $db->table('usergroups as u');
        $builder->select('u.*, r.nama_group, r.ket as ket_rg, m.id_modul, m.nama_modul');
        $builder->join('rolegroups as r', 'r.id_group = u.group_id', 'left');
        $builder->join('moduls as m', 'm.id_modul = r.id_modul', '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) == 'nama_group') {
                                $field = 'r.nama_group';
                            }
                            if (trim($filter->field) == 'nama_modul') {
                                $field = 'm.nama_modul';
                            }
                            if (trim($filter->field) == 'ket_rg') {
                                $field = 'r.ket';
                            }
                            $builder->orLike($field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    if (trim($filter->field) == 'nama_group') {
                        $field = 'r.nama_group';
                    }
                    if (trim($filter->field) == 'nama_modul') {
                        $field = 'm.nama_modul';
                    }
                    if (trim($filter->field) == 'ket_rg') {
                        $field = 'r.ket';
                    }
                    $builder->orLike($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);
        $builder->where('u.user_id', $id_usr);
        return $builder->get()->getResult();
    }

    public function get_total2($id_usr, $filterRules = null)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('usergroups as u');
        $builder->select('u.*, r.nama_group, r.ket as ket_rg, m.nama_modul');
        $builder->join('rolegroups as r', 'r.id_group = u.group_id', 'left');
        $builder->join('moduls as m', 'm.id_modul = r.id_modul', '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) == 'nama_group') {
                                $field = 'r.nama_group';
                            }
                            if (trim($filter->field) == 'nama_modul') {
                                $field = 'm.nama_modul';
                            }
                            if (trim($filter->field) == 'ket_rg') {
                                $field = 'r.ket';
                            }
                            $builder->orLike($field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $field = $filter->field;
                    if (trim($filter->field) == 'nama_group') {
                        $field = 'r.nama_group';
                    }
                    if (trim($filter->field) == 'nama_modul') {
                        $field = 'm.nama_modul';
                    }
                    if (trim($filter->field) == 'ket_rg') {
                        $field = 'r.ket';
                    }
                    $builder->orLike($field, trim(urldecode($value)));
                }
            }
        }
        $builder->where('u.user_id', $id_usr);
        return $builder->countAllResults();
    }

}