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

class Model_it_service extends CI_Model {

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

    public function getServiceList($ID = null, $user_id = null, $year = 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, d.email')
                ->select('(select status from sv__approval where table_name="sv__it_service" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app')
                ->select('(select reason from sv__approval where table_name="sv__it_service" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason')
                ->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')
                ->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);
        }

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

    public function getServiceListForm($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, d.email')
				->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')
				->where('a.is_delete',0)
				->get('sv__it_service a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
    }


    public function update_service($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__it_service');
		return $this->db->affected_rows();
	}
}
?>