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

class Model_hotel extends CI_Model {

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

	public function getHotelBookingList($ID = null, $user_id = null, $year = null)
	{
		$this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, b.department_id, c.name as dept_name, d.name as branchName, b.name as requestor, e.email as requestor_email, b.staff_id, f.name as created_name, f.email as created_email')
				->select('(select status from sv__approval where table_name="sv__hotel" 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__hotel" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason')
                ->join('user__employment_detail b', 'b.id = a.requestor_id', 'LEFT')
				->join('company__department c', 'c.id = b.department_id','LEFT')
                ->join('company__branch d', 'd.id = c.branch_id', 'LEFT')
                ->join('user e', 'e.id = a.requestor_id','LEFT')
                ->join('user f', 'f.id = a.createdBY','LEFT')
                ->where('a.is_delete',0)
                ->order_by('a.createdTS', 'DESC');
                
        if($ID > 0)
		{
			$this->db->where('a.ID',$ID);
        }

        if($user_id > 0)
        {
            $this->db->where('(a.requestor_id = '.$user_id.' OR a.createdBY = '.$user_id.') ');
            // $this->db->where('a.requestor_id',$user_id);
            // $this->db->or_where('a.createdBY',$user_id);
        }

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

        $query = $this->db->get('sv__hotel a')
				->result_array();
// debug($this->db->last_query());
		if($query) foreach($query as $key => $row)
		{
			$room_details = $this->getHotelBookingRoomDetails($ID);
			$query[$key]['room_details'] = (!empty($room_details))?$room_details:array();
		}
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getHotelListForm($ID)
	{
		$this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, b.department_id, c.shortName as department_short_name, c.name as dept_name, b.name as requestor, b.staff_id, d.name as branchName, e.email as primary_email,')
                /* creator detail */
				->select('f.name as created_name, f.email as created_email, g.department_id as created_department_id, h.name as created_dept_name, i.name as created_branchName')
				->where('a.ID', $ID)
				->join('user__employment_detail b', 'b.id = a.requestor_id', 'LEFT')
				->join('company__department c', 'c.id = b.department_id', 'LEFT')
				->join('company__branch d', 'd.id = c.branch_id', 'LEFT')
                ->join('user e', 'e.id = a.requestor_id','LEFT')
                /* creator detail */
                ->join('user f', 'f.id = a.createdBY','LEFT')
                ->join('user__employment_detail g', 'g.id = a.createdBY', 'LEFT')
				->join('company__department h', 'h.id = g.department_id', 'LEFT')
				->join('company__branch i', 'i.id = h.branch_id', 'LEFT')
				->where('a.is_delete',0)
				->get('sv__hotel a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}
    
    public function getCountHotelList($hotel_name)
	{
		$query = $this->db->select('hotel_name, group_concat(ID), count(ID) as total_request')
				->where('a.hotel_name',$hotel_name)
				->where('a.is_delete',0)
				->where('year(a.createdTS)',date('Y'))
				->get('sv__hotel a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getBookingHotelByWeek()
	{
		$query = $this->db->select('a.*, b.name as dept_name, c.name as branchName')
				->join('user__employment_detail d', 'd.id = a.requestor_id', 'LEFT')
				->join('company__department b', 'b.id = d.department_id','left')
				->join('company__branch c', 'c.id = b.branch_id','left')
				->where('DATE(a.checkin_date) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 WEEK)')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__hotel a')
				->result_array();
				
		return (!empty($query))?$query:null;
	}

	public function update_hotel($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__hotel');
		return $this->db->affected_rows();
    }
    
    public function getRequestByCompany($year = null)
	{
		$query = $this->db->select('a.name as dept_name, a.shortName, b.name as branchName, count(d.id) as total_request')
							->join('company__branch b','b.id = a.branch_id','LEFT')
							->join('user__employment_detail c','c.department_id = a.id','LEFT')
							->join('sv__hotel d','d.requestor_id = c.id AND d.is_delete = 0 and YEAR(d.createdTS) = '.$year.'','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;
    }

    public function getHotelBookingRoomDetails($hotel_id = null, $room_id = null)
    {
    	if($hotel_id) $this->db->where('a.ID', $hotel_id);
    	if($room_id) $this->db->where('b.ID', $room_id);

		$query = $this->db->select('b.ID, b.hotel_id, b.room_type, group_concat(c.guest_id) as guest_room_id, group_concat(d.name) as guest_room_name, group_concat(if(e.phone != "",e.phone,"N/A")) as guest_room_phone, group_concat(if(e.ic_no != "",e.ic_no,"N/A")) as guest_room_ic_no')
				->join('sv__hotel_room_detail b','b.hotel_id = a.ID and b.is_delete = 0','LEFT')
				->join('sv__hotel_room_guest c','c.room_id = b.ID and c.is_delete = 0','LEFT')
				->join('user__employment_detail d', 'd.id = c.guest_id', 'LEFT')
				->join('user e', 'e.id = c.guest_id', 'LEFT')
				->where('a.is_delete',0)
    			->group_by('b.ID')
    			->order_by('b.ID asc')
				->get('sv__hotel a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
    }

    public function updateOrCreateNew($room_id, $guest_ID)
	{
		if(empty($room_id)) return false;

		$this->db->set('a.is_delete',1)
				->set('a.updated_by',$_SESSION['user']['id'])
				->set('a.updated_ts',date('Y-m-d H:i:s'))
				->where('a.room_id',$room_id)
				->where_not_in('a.guest_id',$guest_ID)
				->update('sv__hotel_room_guest a');
		

		$staff_id_stored = $this->db->where('a.room_id',$room_id)
								->where_in('a.guest_id',$guest_ID)
								->where('a.is_delete',0)
								->get('sv__hotel_room_guest a')
								->result_array();

		if(!empty($staff_id_stored))
		{
			$batch_insert = array();
			foreach($guest_ID as $key => $guest_id)
			{
				$bool = false;

				foreach ($staff_id_stored as $key => $staffID) 
				{
					if($staffID['guest_id'] == $guest_id)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'room_id'		=> $room_id,
						'guest_id'		=> $guest_id,
						'created_by'	=> $_SESSION['user']['id'],
					);
					array_push($batch_insert,$temp);
				}
			}
			if($batch_insert)
			{
				$insert = $this->db->insert_batch('sv__hotel_room_guest',$batch_insert);return true;
			}
		} 
		else 
		{
			$batch_insert = array();
			foreach($guest_ID as $key => $guest_id)
			{
				$temp = array
				(
					'room_id'		=> $room_id,
					'guest_id'		=> $guest_id,
					'created_by'	=> $_SESSION['user']['id'],
				);
				array_push($batch_insert,$temp);
			}
			$insert = $this->db->insert_batch('sv__hotel_room_guest',$batch_insert);return true;
		}
	}
    
}
?>