<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Model_service_form extends CI_Model {

	function __construct()
	{
		parent::__construct();
	}

	// public function getServiceStationery()
	// {
	// 	$query = $this->db->select('*, a.department_id, b.name as dept_name, c.name as branchName')
	// 			->join('company__department b', 'b.id = a.department_id')
	// 			->join('company__branch c', 'c.id = b.branch_id')
	// 			->where('a.is_delete',0)
	// 			->get('sv__stationery a')
	// 			->result_array();
	// 	//die(debug($this->db->last_query()));
	// 	return (!empty($query))?$query:null;
	// }
	
	public function getSystemSupport($ID=0)
	{
		if($ID >0) $this->db->where('ID',$ID);
		return $this->db->get('sv__system')->result_array();
	}

	public function getSystemList($ID = null, $user_id = null)
	{
		$this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, c.name as branchName, d.name as requestor, d.staff_id, e.systemname, f.name')
                ->select('(select status from sv__approval where table_name="sv__system" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app')        
                ->join('company__department b', 'b.id = a.department_id','LEFT')
                ->join('company__branch c', 'c.id = b.branch_id', 'LEFT')
                ->join('user d', 'd.id = a.user_id', 'LEFT')
                ->join(' _ref__system_name e', 'e.ID = a.systemID', 'LEFT')
                ->join('customer f', 'f.ID = a.cust_id', 'LEFT')
                ->where('a.is_delete',0)
                ->order_by('a.ID ASC');
                
        if($ID > 0)
		{
			$this->db->where('a.ID',$ID);
        }

        if(!empty($user_id))
		{
			$this->db->where('a.user_id',$user_id);
        }
        
		$query = $this->db->get('sv__system a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getSystemListForm($ID)
    {
    	if(!empty($ID)){
			$this->db->where('a.ID', $ID);
    	}
        
        $this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, d.name as requestor, e.systemname')
				->join('company__department b', 'b.id = a.department_id', 'LEFT')
				->join('company__branch c', 'c.id = b.branch_id', 'LEFT')
				->join('user d', 'd.id = a.user_id', 'LEFT')
				->join(' _ref__system_name e', 'e.ID = a.systemID', 'LEFT')
				->where('a.is_delete',0)
				->get('sv__system a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
    }

    public function get_system_file($where = array())
    {
		$this->db->select('*');
		
        if($where)
        {
        	foreach ($where as $column => $value) 
			{
				$this->db->where($column,$value);
			}
		}
		$system_files = $this->db->get('sv__system_attachment')
                			->result_array();   
		return $system_files;
    }

    public function update_system($sets = array(), $where = array())
	{
		foreach ($sets as $key => $set) 
		{
			$this->db->set($key,$set);
		}
		foreach ($where as $key => $wh) 
		{
			$this->db->where($key,$wh);
		}
		$this->db->update('sv__system');
		return $this->db->affected_rows();
	}

	public function update_attachment($sets = array(), $where = array())
	{
		foreach ($sets as $key => $set) 
		{
			$this->db->set($key,$set);
		}
		foreach ($where as $key => $wh) 
		{
			$this->db->where($key,$wh);
		}
		$this->db->update('sv__system_attachment');
		return $this->db->affected_rows();
	}

	public function getSystemDetails($ID)
	{
		$this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, c.name as branchName, group_concat(d.ID) as fileID, group_concat(d.filename) as filename, group_concat(d.file_location) as file_location, e.name as requestor, e.staff_id')
                ->select('(select status from sv__approval where table_name="sv__system" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status')        
                ->join('company__department b', 'b.id = a.department_id')
				->join('company__branch c', 'c.id = b.branch_id')
				->join('sv__system_attachment d', 'd.svID = a.ID and d.is_delete=0','LEFT')
				->join('user e', 'e.id = a.user_id', 'left')
				->where('a.ID', $ID)
				->where('a.is_delete',0)
				->get('sv__system a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getDiscussion($ID)
	{
		$query = $this->db->select('a.*, b.name, c.file_location, c.file_name')
						->join('user b','b.id = a.userID')
						->join('user__picture c','c.id = b.profile_picture_id','left')
						->where('a.svID',$ID)
						->get('sv__system_discussion a')
						->result_array();
		return $query;
	}

	function getServiceForm($where = array(), $where_in = array())
	{
		if(!empty($where)) foreach ($where as $key => $row) $this->db->where($key, $row);
		if(!empty($where_in)) foreach ($where_in as $key => $row) $this->db->where_in($key, $row);

		$query = $this->db->select('a.*, b.category as category')
					->join('hr__policyprocedure_category b', 'a.category_id = b.id', 'left')
					->where('a.is_delete',0)
					->where_not_in('a.id',array(30,33,25))
					->group_by('a.id')
			        ->order_by('a.category_id asc, a.id asc')
					->get('hr__form a');
					
		return ($query->num_rows() > 0) ? $query->result_array() : null;
	}
}
?>