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

class Model_av_it extends CI_Model {

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

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

	public function getEquipmentList($ID = null, $borrower_id = null, $year = null)
	{
		$this->db->select('a.*, b.name as borrower_name, b.staff_id, b.email, c.name as dept_name, d.name as  branchName')
				->select('(select status from sv__approval where table_name="sv__it__equipment" 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__equipment" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason')
				->join('user b','b.id = a.borrower_id','LEFT')
				->join('company__department c','c.id = a.department_id','LEFT')
				->join('company__branch d','d.id = c.branch_id','LEFT')
				->where('a.is_delete',0)
				->order_by('a.ID ASC');

		if($ID > 0)
		{
			$this->db->where('a.ID',$ID);
        }
        
        if(!empty($borrower_id))
		{
			$this->db->where('a.borrower_id',$borrower_id);
        }

        if($year > 0)
		{
			$this->db->where('year(a.createdTS)',$year);
        }

		$query = $this->db->get('sv__it__equipment a')
						->result_array();

		return $query;
	}

	public function getEquipmentRequestByWeek()
	{
		$query = $this->db->select('a.*, b.name as borrower_name, b.staff_id, b.email, c.name as dept_name, d.name as  branchName')
				->join('user b','b.id = a.borrower_id','LEFT')
				->join('company__department c','c.id = a.department_id','LEFT')
				->join('company__branch d','d.id = c.branch_id','LEFT')
				->where('a.check_in BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK)')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__it__equipment a')
				->result_array();
		// debug($this->db->last_query());
		// die();
		return (!empty($query))?$query:null;
	}

	public function getEquipmentRequestRoomByMonth()
	{
		$query = $this->db->select('a.*, b.name as borrower_name, b.staff_id, b.email, c.name as dept_name, d.name as  branchName')
				->join('user b','b.id = a.borrower_id','LEFT')
				->join('company__department c','c.id = a.department_id','LEFT')
				->join('company__branch d','d.id = c.branch_id','LEFT')
				->where('MONTH(a.check_in)',date('m'))
				->where('a.is_delete',0)
				->order_by('a.check_in asc')
				->get('sv__it__equipment a')
				->result_array();
// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getRequestByCompany($year)
	{
		$query = $this->db->select('a.id, a.name as dept_name, c.name as branchName, count(b.department_id) as total_request')
							->join('sv__it__equipment b','b.department_id = a.id and b.is_delete = 0 and year(b.createdTS) = '.$year,'LEFT')
							->join('company__branch c','c.id = a.branch_id and c.is_delete = 0','LEFT')
							->where('a.is_delete',0)
							->group_by('a.id')
							->get('company__department a');

		// die(debug($this->db->last_query()));
		return (!empty($query))?$query->result_array():null;
	}
}
?>