<?php

namespace App\Models;

use CodeIgniter\HTTP\RequestInterface;
use CodeIgniter\Model;
use Config\Database;
use App\Entities\SysMenuEntity;

class SysMenuModel extends Model
{
    protected $DBGroup = "default";
    protected $table = "sysmenu";
    protected $primaryKey = "row_id";
    protected $useAutoIncrement = true;
	protected $insertID = 0;
	protected $returnType = "App\Entities\SysMenuEntity";
	protected $useSoftDelete = false;
	protected $protectFields = true;
    protected $useTimestamps = false;
	protected $dateFormat = "datetime";
    protected $createdField = "";
    protected $updatedField = "";
    protected $deletedField = "";
	protected $skipValidation = false;
	protected $cleanValidationRules = true;
	protected $validationRules = [];
	protected $validationMessages = [];
    protected $db;
    protected $builder;
    protected $request;
    protected $order = ["row_id" => "DESC"];
    protected $allowedFields = [
        "row_id", 
        "folder_name",
        "seq",
        "icon",
        "is_deleted",
        "created_date",
        "created_by",
        "modified_date",
        "modified_by",
        "deleted_date",
        "deleted_by",
    ];
    protected $column_order = ["row_id"];
    protected $column_search = [
        "sysmenu.folder_name",
        "sysmenu.seq",
        "sysicon.icon_code",
    ];
    public $fieldValue = [
        "row_id" => 0,
        "folder_name" => "",
        "seq" => "",
        "icon" => "",
        "icon_txt" => "",
        "created_date" => "",
        "created_by" => "",
        "modified_date" => "",
        "modified_by" => "",
        "deleted_date" => "",
        "deleted_by",
    ];

    public function __construct(RequestInterface $request=null)
    {
        parent::__construct();
        $this->request = $request;
        $this->db = Database::connect();
        $this->builder = $this->db->table($this->table);
    }

    public function deleteData($id) 
    {
        $data = [
            'is_deleted' => 1,
            'deleted_date'  => getDatetime(),
            'deleted_by' => sesUsername(),
        ];        
        $this->builder->set($data);
        $this->builder->where("row_id", $id);
        $this->builder->update();
    }

    public function getSelect($where=null) 
    {
        $condition = "sysmenu.is_deleted = 0";
		if ($where != null) {
		    $condition .= " and ".$where;
		}
        
        $this->builder->select("sysmenu.*, icon_table.icon_code AS icon_txt");
        $this->builder->join("sysicon icon_table", "icon_table.row_id = sysmenu.icon", "left");
        $this->builder->where($condition);
        $query = $this->builder->get();
        $result = $query->getResultArray();
        if($result == null) {
            return (object)$this->fieldValue;
        } else {
		    $data = new SysMenuEntity();
            $data->fill($result[0]);
            return $data;
        }
    }

    public function getList($where=null)
    {                   
        $condition = "sysmenu.is_deleted = 0";
		if ($where != null) {
		    $condition .= " and ".$where;
		}
        
        $this->builder->select("sysmenu.*, icon_table.icon_code AS icon_txt");
        $this->builder->join("sysicon icon_table", "icon_table.row_id = sysmenu.icon", "left");
        $this->builder->where($condition);
        $query = $this->builder->get();
        $result = $query->getResult(); 
        if($result == null) {
            $result = (object)$this->fieldValue;
        }
        return $result;
    }

    public function exportExcel($where=null)
    {                                
        $condition = "sysmenu.is_deleted = 0";
		if ($where != null) {
		    $condition .= " and ".$where;
		}
        
        $this->builder->select("sysmenu.*, icon_table.icon_code AS icon_txt");
        $this->builder->join("sysicon icon_table", "icon_table.row_id = sysmenu.icon", "left");
        $this->builder->where($condition);
        $query = $this->builder->get();
        return $query;
    }

    public function checkValidation($data=null) 
    {        
        $where = "";
        $unique = "";
        $valid = true;
        $msg = "";
        foreach($data as $row) {
            $type = array_key_exists('type', $row) ? $row['type'] : "";
            $field = array_key_exists('field', $row) ? $row['field'] : "";
            $value = array_key_exists('value', $row) ? $row['value'] : "";  
            
            if($type == "unique") {
                if($unique == "") {
                    $unique .= $field." = '".$value ."'";
                } else {
                    $unique .= " OR ".$field." = '".$value ."'";
                }
            }
        }
        
        if($unique != "") {
            if($where != "") {
                $where .= " and (".$unique.")";
            } else {
                $where .= " (".$unique.")";
            }
        }

        $condition = "sysmenu.is_deleted = 0";
		if ($where != "") {
		    $condition .= " and ".$where;
		}
        
        $this->builder->select("sysmenu.*");
        $this->builder->where($condition);
        $query = $this->builder->get();
        $result = $query->getResult();
        if($result == null) {
            $valid = true;
        } else {
            $valid = false;    
            foreach($result as $line)
            {
                foreach($data as $row) {
                    $type = array_key_exists('type', $row) ? $row['type'] : "";
                    $field = array_key_exists('field', $row) ? $row['field'] : "";
                    $value = array_key_exists('value', $row) ? $row['value'] : "";  
                    
                    if($type == "unique") {
                        if($line->$field == $value) {
                            if($msg == "") {
                                $msg .= str("duplicate_date")." ".$value;
                            } else {
                                $msg .= ', '.str("duplicate_date").' '.$value;
                            }
                        }
                    }
                }
            }        
        }
        $ret = [
            "valid" => $valid,
            "msg" => $msg
        ];
        return $ret;
    }

    private function getDatatablesQuery($where)
    {
        $i = 0;
        $this->builder->select('sysmenu.*, icon_table.icon_code AS icon_txt');
        $this->builder->join("sysicon icon_table", "icon_table.row_id = sysmenu.icon", "left");

        foreach ($this->column_search as $item) {
            if ($this->request->getPost('search')['value']) {
                if ($i === 0) {
                    $this->builder->groupStart();
                    $this->builder->like($item, $this->request->getPost('search')['value']);
                } else {
                    $this->builder->orLike($item, $this->request->getPost('search')['value']);
                }
                if (count($this->column_search) - 1 == $i)
                    $this->builder->groupEnd();
            }
            $i++;
        }

        if($where != "") {
            $where = " and ".$where;
        }
        $this->builder->where("sysmenu.is_deleted = 0".$where);

        if ($this->request->getPost('order')) {
            $this->builder->orderBy($this->column_order[$this->request->getPost('order')['0']['column']], $this->request->getPost('order')['0']['dir']);
        } else if (isset($this->order)) {
            $order = $this->order;
            $this->builder->orderBy(key($order), $order[key($order)]);
        }
    }

    public function getDatatables($where)
    {
        $this->getDatatablesQuery($where);
        if ($this->request->getPost('length') != -1) {
            $this->builder->limit($this->request->getPost('length'), $this->request->getPost('start'));
        }
        $query = $this->builder->get();
        return $query->getResultArray();
    }

    public function countFiltered($where)
    {
        $this->getDatatablesQuery($where);
        return $this->builder->countAllResults();
    }

    public function countAll()
    {
        $tbl_storage = $this->db->table($this->table);
        return $tbl_storage->countAllResults();
    }     

    public function getMenuForAdmin()
    {
        $qry = "
            select 
                sysmenulist.line_id as menu_id, 
                'Full control' as access_control, 
                sysmenulist.menu_name, 
                sysmenulist.route, 
                sysmenulist.seq as menu_seq, 
                sysmenulist.route, sysmenu.folder_name, sysmenu.seq as folder_seq,
                sysicon.icon_code
            from sysmenu sysmenu 
            inner join sysmenulist sysmenulist on sysmenulist.row_id = sysmenu.row_id and sysmenulist.is_deleted = 0
            inner join sysicon sysicon on sysicon.row_id = sysmenu.icon and sysicon.is_deleted = 0     
            order by sysmenu.seq asc, sysmenulist.seq asc     
        ";
        $query = $this->db->query($qry);
        return $query->getResult();
    }

    public function getMenuByUserAccessId($user_access_id)
    {
        $qry = "
            select 
                sysuseraccessaccess.menu_id, 
                sysuseraccessaccess.access_control, 
                sysmenulist.menu_name, 
                sysmenulist.route, 
                sysmenulist.seq as menu_seq, 
                sysmenu.folder_name, 
                sysmenu.seq as folder_seq,
                sysicon.icon_code
            from sysuseraccess sysuseraccess
            inner join sysuseraccessaccess sysuseraccessaccess on sysuseraccessaccess.row_id = sysuseraccess.row_id and sysuseraccessaccess.is_deleted = 0 
            inner join sysmenulist sysmenulist on sysmenulist.line_id = sysuseraccessaccess.menu_id and sysmenulist.is_deleted = 0
            inner join sysmenu sysmenu on sysmenu.row_id = sysmenulist.row_id and sysmenu.is_deleted = 0
            inner join sysicon sysicon on sysicon.row_id = sysmenu.icon and sysicon.is_deleted = 0
            where sysuseraccess.is_deleted = 0 and sysuseraccess.row_id = ".$user_access_id."   
            order by sysmenu.seq asc, sysmenulist.seq asc        
        ";
        $query = $this->db->query($qry);
        return $query->getResult();
    }
}