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

class Model_booking_meeting extends CI_Model {

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

	public function getBookingRoomType($room_id = null)
	{
		if($room_id)
		{
			$this->db->where('a.ID',$room_id);
		}
		
		$query = $this->db->select('a.*, a.ID as room_id')
				// ->where('a.ID !=',7)
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__booking_room_type a')
				->result_array();
		// die(debug($this->db->last_query()));
		// die(debug($query));
		return (!empty($query))?$query:null;
	}

	public function getBookingRoomType2($year)
	{
		$query = $this->db->select('a.*, a.ID as room_id, count(b.room_id) as total_booking')
							->join('sv__booking__room b','b.room_id = a.ID and b.is_delete = 0 and year(b.createdTS) = '.$year.'','LEFT')
							->where('a.is_delete',0)
							->group_by('a.ID')
							->get('sv__booking_room_type a');

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

	public function getBookingRoom($booking_id=null, $requester_id = null, $year = null, $limit = 0)
	{
		$this->db->select('a.*, group_concat(f.equipment_id) as equipment_id, group_concat(h.equipment) as equipment, d.name, d.staff_id, d.email, e.room, e.color_code, a.department_id, b.name as dept_name, c.name as branchName, g.package, g.price as package_price')
				->select('(select status from sv__approval where table_name="sv__booking__room" 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__booking__room" 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')
				->join('company__branch c', 'c.id = b.branch_id')
				->join('user d', 'd.id = a.requester_id')
				->join('sv__booking_room_type e', 'e.id = a.room_id', 'left')
				->join('sv__booking__room_equipment f', 'f.booking_room_id = a.ID and f.is_delete = 0', 'left')
				->join('sv__ref_room_package g', 'g.ID = a.package_id and g.is_delete = 0', 'left')
				->join('sv__ref_room_equipment h', 'h.ID = f.equipment_id and h.is_delete = 0', 'left')
				->where('a.is_delete',0)
				->group_by('a.ID')
				->order_by('a.ID asc');
		
		if(($booking_id))
		{
			$this->db->where('a.ID', $booking_id);
        }
        
        if(!empty($requester_id))
		{
			$this->db->where('a.requester_id',$requester_id);
        }

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

        if($limit)
        {
			// $this->db->where_in('(select status from sv__approval where table_name="sv__booking__room" and id_table=a.ID and is_delete=0 order by ID desc limit 1)',array('Pending'));
   //      	$this->db->limit($limit);
        }

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

	public function getBookingRoomByWeek()
	{
		$query = $this->db->select('a.*, b.*')
				->join('sv__booking_room_type b', 'b.id = a.room_id', 'left')
				->where('DATE(a.start_date) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 WEEK)')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__booking__room a')
				->result_array();
				
		return (!empty($query))?$query:null;
	}

	public function getBookingRoomDaily()
	{
		$query = $this->db->select('a.*, b.*, c.shortName, c.name as dept_name, d.name as branchName')
				->join('sv__booking_room_type b', 'b.id = a.room_id', 'left')
				->join('company__department c', 'c.id = a.department_id', 'left')
				->join('company__branch d', 'd.id = c.branch_id', 'left')
				->where('DATE(NOW()) BETWEEN a.start_date AND a.end_date')
				// ->where('DATE(a.start_date) = DATE(NOW())')
				->where('a.is_delete',0)
				->order_by('a.room_id asc, a.start_date asc, a.start_time,a.ID asc')
				->get('sv__booking__room a')
				->result_array();
				
		return (!empty($query))?$query:null;
	}

	public function getBookingRoomDate($room_id=null,$start_date=null,$other_room=null,$start_time=null,$end_date=null,$end_time=null)
	{
		$this->db->select('a.*, 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)
			->order_by('a.ID desc');

		if($room_id > 0)
		{
			// $this->db->where_not_in('a.room_id',$room_id);
			if($room_id == 7)
			{
				$this->db->where('a.room_id',$room_id);
				$this->db->like('a.other_room',$other_room);
			}else{
				$this->db->where('a.room_id',$room_id);
			}
		}
		if($start_date)
		{
			$this->db->where('"'.$start_date.'" BETWEEN a.start_date AND a.end_date');
        }
        if($start_time)
		{
			$this->db->where('"'.$start_time.'" BETWEEN a.start_time AND a.end_time');
        }
        if($end_date)
		{
			$this->db->where('"'.$end_date.'" BETWEEN a.start_date AND a.end_date');
        }
        if($end_time)
		{
			$this->db->where('"'.$end_time.'" BETWEEN a.start_time AND a.end_time');
        }
		$query = $this->db->get('sv__booking__room a')
				->result_array();
		// debug($this->db->last_query());
		// die();
		return (!empty($query))?$query:null;
	}

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

	public function updateOrCreateNew($booking_id,$equipment_detail = array())
	{
		if(empty($equipment_detail)) return false;
		
		$this->db->set('a.is_delete',1)
				->where_not_in('a.equipment_id',$equipment_detail)
				->where('a.booking_room_id',$booking_id)
				->update('sv__booking__room_equipment a');


		$eqpt_stored = $this->db->select('a.*')
						->where('a.booking_room_id',$booking_id)
						->where('a.is_delete',0)
						->get('sv__booking__room_equipment a')
						->result_array();
		

		if(!empty($eqpt_stored))
		{
			$batch_insert = array();
			foreach ($equipment_detail as $eqpt) 
			{
				$bool = false;
				
				foreach ($eqpt_stored as $key => $eqpts) 
				{
					// die(debug($eqpt));
					if($eqpts['equipment_id'] == $eqpt)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'booking_room_id'   => $booking_id,
						'equipment_id'      => $eqpt,
						'is_delete'     	=> 0,
						'createdBY'    		=> $this->session->userdata('user')['id'],
						'createdTS'    		=> date('Y-m-d H:i:s'),
						'updatedBY'    		=> $this->session->userdata('user')['id'],
						'updatedTS'    		=> date('Y-m-d H:i:s'),
					);
					array_push($batch_insert,$temp);
				}

			}
			if($batch_insert)$this->db->insert_batch('sv__booking__room_equipment',$batch_insert);return true;
		} else {
			$batch_insert = array();
			foreach ($equipment_detail as $eqpt) 
			{
				$temp = array
				(
					'booking_room_id'	=> $booking_id,
					'equipment_id'      => $eqpt,
					'is_delete'     	=> 0,
					'createdBY'    		=> $this->session->userdata('user')['id'],
					'createdTS'   		=> date('Y-m-d H:i:s'),
					'updatedBY'    		=> $this->session->userdata('user')['id'],
					'updatedTS'    		=> date('Y-m-d H:i:s'),
				);
				array_push($batch_insert,$temp);
			}
			if($batch_insert)$this->db->insert_batch('sv__booking__room_equipment',$batch_insert);return true;
		}
	}

	public function getMealMenu($meal_type = null)
	{
		if(isset($meal_type)) $this->db->where('type',$meal_type);

		$query = $this->db->where('is_delete',0)
					->get('sv__ref_meal_menu')
					->result_array();

		return (!empty($query))?$query:null;
	}

	public function getExistingBookingRoomDate($wheres = array(),$wheres_or = array())
	{
		if($wheres) foreach($wheres as $key => $where) if($where) $this->db->where($where,null,false);
		if($wheres_or) foreach($wheres_or as $key => $where_or) if($where_or) $this->db->or_where($where_or);

		$this->db->select('a.*, 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)
			->order_by('a.ID desc');

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

	public function getPackage($package_id = null)
	{
		if($package_id) $this->db->where('ID', $package_id);

		$query = $this->db->where('is_delete',0)
						->order_by('price asc, ID desc')
						->get('sv__ref_room_package')
						->result_array();

		return (!empty($query))?$query:null;
	}

	public function getEquipment($equipment_id = null)
	{
		if($equipment_id) $this->db->where('ID', $equipment_id);

		$query = $this->db->where('is_delete',0)
						->order_by('ID desc')
						->get('sv__ref_room_equipment')
						->result_array();

		return (!empty($query))?$query:null;
	}

	function getBookingMeetingCrmList($ID = null, $user_id = null)
	{
		$query = $this->db->select('a.*, h.name as company_name, b.name as dept_name, c.name as branchName, d.name as requestor, d.staff_id, e.name as preparedByName, f.service_name as engagementName, g.service_name as potentialServiceName')
						->join('company__department b', 'b.id = a.department_id')
						->join('company__branch c', 'c.id = b.branch_id')
						->join('user d', 'd.id = a.user_id', 'left')
						->join('user e', 'e.id = a.prepared_by', 'left')
						->join('sv__engagement_and_potential_services f', 'f.ID = a.engagement', 'left')
                        ->join('sv__engagement_and_potential_services g', 'g.ID = a.potential_services', 'left')
                        ->join('customer h','h.id = a.crm_id')
                        //->join('sv__meeting__crm_attended_by h', 'h.attended_by = a.ID', 'left')
						// ->join('user f', 'f.id = e.attended_by')
						->order_by('a.ID', 'DESC')
                        ->where('a.is_delete', 0);
                        
        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__meeting__crm_report a')
				->result_array();
		return $query; 
    }
    
	function getBookingMeetingCrmListByID($ID)
	{
		$query = $this->db->select('a.*, a.crm_id as name, b.name as dept_name, c.name as branchName, d.name as requestor, e.service_name as engagement_service, f.service_name as potential_service, g.name as preparedByName')
						->join('company__department b', 'b.id = a.department_id')
						->join('company__branch c', 'c.id = b.branch_id')
                        ->join('user d', 'd.id = a.user_id')
						->join('sv__engagement_and_potential_services e', 'e.ID = a.engagement', 'left')
                        ->join('sv__engagement_and_potential_services f', 'f.ID = a.potential_services', 'left')
                        ->join('user g', 'g.id = a.prepared_by')
                        ->join('customer h','h.id = a.crm_id')
						->where('a.id', $ID)
						->where('a.is_delete', 0)
						->get('sv__meeting__crm_report a')
						->result_array();
		// return (!empty($query))?$query:null;

		if(!empty($query)) {
				$query_attend = $this->db->select('a.*, b.name as attendedName')
						->join('user b', 'b.id = a.attended_by')
						->where('a.meeting_crm_report_id',$ID)
						->where('a.is_delete',0)
						->get('sv__meeting__crm_attended_by a')
						->result_array();

				if(!empty($query_attend)) 
				{
					$query[0]['attended_by'] = $query_attend;
				}

				return $query; 
		} else {
			return null;
        }
    }

    function getattendListName()
    {
        $query = $this->db->select('a.meeting_crm_report_id, a.attended_by, b.name as attendedListName')
                        ->where('a.is_delete', 0)
                        ->join('user b', 'b.id = a.attended_by', 'left')
                        ->get('sv__meeting__crm_attended_by a')
						->result_array();
		return (!empty($query))?$query:null;
    }

    // function getattendedListName()
    // {
    //     $query = $this->db->select('a.meeting_crm_booking_id, group_concat(a.attended_by) as attended_by, b.name as attendedListName')
    //                     ->where('a.is_delete', 0)
    //                     ->join('user b', 'b.id = a.attended_by', 'left')
    //                     ->get('sv__meeting__crm_attended_by a')
	// 					->result_array();
	// 	return (!empty($query))?$query:null;
    // }

    // public function getBookingRoomAttendName()
	// {
	// 	$this->db->select('a.*, group_concat(f.attended_by) as attended_by, d.name, d.staff_id, d.email, 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')
	// 			->join('user d', 'd.id = a.user_id')
	// 			->join('sv__meeting__crm_attended_by f', 'f.meeting_crm_booking_id = a.ID and f.is_delete = 0', 'left')
	// 			->where('a.is_delete',0)
	// 			->group_by('a.ID')
	// 			->order_by('a.ID asc');
		
	// 	// if(($ID))
	// 	// {
	// 	// 	$this->db->where('a.ID', $ID);
	// 	// }

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

    
    function getBookingMeetingCrmListIncludeAttend()
	{
		$query = $this->db->select('a.*, b.name as dept_name, c.name as branchName, d.name as requestor, e.service_name as engagement_service, f.service_name as potential_service, g.attended_by, h.name as attended_name')
						->join('company__department b', 'b.id = a.department_id')
						->join('company__branch c', 'c.id = b.branch_id')
						->join('user d', 'd.id = a.user_id')
						->join('sv__engagement_and_potential_services e', 'e.ID = a.engagement', 'left')
                        ->join('sv__engagement_and_potential_services f', 'f.ID = a.potential_services', 'left')
                        ->join('sv__meeting__crm_attended_by g', 'g.meeting_crm_booking_id = a.ID', 'left')
                        ->join('user h', 'h.id = g.attended_by')
						->where('a.is_delete', 0)
						->get('sv__meeting__crm_report a')
						->result_array();
		// return (!empty($query))?$query:null;

		if(!empty($query)) {
				$query_attend = $this->db->select('a.*, b.name as attendedName')
                        ->join('user b', 'b.id = a.attended_by')
                        ->join('sv__meeting__crm_report c', 'c.ID = a.meeting_crm_booking_id', 'left')
						->where('a.meeting_crm_booking_id', 'c.ID')
						->where('a.is_delete',0)
						->get('sv__meeting__crm_attended_by a')
						->result_array();

				if(!empty($query_attend)) 
				{
					$query[0]['attended_by'] = $query_attend;
				}

				return $query; 
		} else {
			return null;
        }
	}

	function getCompanyList()
	{
		$query = $this->db->select('a.*')
						->where('a.is_delete', 0)
						->order_by('a.name')
						->get('customer a')
						->result_array();
		return (!empty($query))?$query:null;
	}

	function getlistNames()
	{
		$query = $this->db->select('a.*')
				->where('a.is_delete',0)
				->order_by('a.name', 'ASC')
				->get('user__employment_detail_new a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function updateMeetingCRM($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__meeting__crm_report');
		return $this->db->affected_rows();
    }
    
    public function updateAttendByOfMeetingCRM($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__meeting__crm_attended_by');
		return $this->db->affected_rows();
	}

	public function fetchAll($table, $select = array('*'), $where = array(), $join = array(), $order = array(), $group = array())
	{
		if(!$table) return NULL;
			
		if ($select) 	foreach($select as $key => $value) $this->db->select($value,false);
		if ($where) 	foreach($where as $key => $value) $this->db->where($key,$value);
		if ($join) 		foreach($join as $key => $value) $this->db->join($value[0],$value[1],$value[2],$value[3],false);
		if ($order)		foreach($order as $key => $value) $this->db->order_by($key, $value);
		if ($group)		foreach($group as $key => $value) $this->db->group_by($value);
		
		$query = $this->db->get($table);
		
		return ($query->num_rows() > 0) ? $query->result_array() : null;
	}
}
?>