<?php
class Dropdown_Option_Model extends CI_Model
{

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

	function getOptionList($option_type_arr = array(), $user_id, $getQueueId, $fullcontrol, $queue_id = 0)
	{
		$ret_data	= array();
		try {
			foreach ($option_type_arr as $key => $option_type) {
				$type = $option_type;
				switch ($type) {
					case "tms_user_type":
						$this->db->select('id, title,manager_id ,title as text');
						$this->db->where('status', 1);
						$this->db->order_by('title', 'asc');
						$res = $this->db->get('tms_user_type');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;

					case "manager":
						$this->db->select('t1.id, name ,name as text');
						$this->db->from('tms_user_master t1');
						$this->db->join('tms_user_type t2', 't1.user_type = t2.id', 'left');
						$this->db->where('t1.status', 1);
						$childId = $this->getChildUserDetails($user_id);

						if ($childId != "") {
						} else {
							$this->db->where('t1.id !=', $user_id);
						}
						$this->db->where('t1.user_type =', 2);
						$this->db->order_by('name', 'asc');
						$res = $this->db->get();
						// print_r($this->db->last_query());die;
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					case 'master_theme':
						$this->db->select('id, theme, class, is_default,color');
						$this->db->where('status', 1);
						$res = $this->db->get('acc_theme_master');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					case 'tms_break_master':
						$this->db->select('id, title, break_time');
						$this->db->where('status', 1);
						$res = $this->db->get('tms_break_master');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						break;
					case "tms_user_master":
						$this->db->select('id, name as "title",name as text ');
						$this->db->where('status', 1);
						if ($fullcontrol == 0) {							
							$this->db->where('full_control', 0);
						}
						$this->db->order_by('name', 'asc');
						$res = $this->db->get('tms_user_master');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}						
						$ret_data[$option_type] = $res->result_array();	
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);					
						break;
					case "queue_cat":
						$this->db->select("t1.category_id as id,t1.category_name as text");
						$this->db->order_by('category_name', 'asc');
						$res = $this->db->get($this->table->tableName[$option_type].'_'.$getQueueId . " As t1");
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();


						// Add the new element at the beginning of the array
						array_unshift($ret_data[$option_type], $newElement);
						break;
					case "tms_shift_master":
						$this->db->select("t1.id,CONCAT(t1.title,'(',SUBSTRING(t1.time_from,1,5),'-',SUBSTRING(t1.time_to,1,5),')') AS text,CONCAT(t1.title,'(',SUBSTRING(t1.time_from,1,5),'-',SUBSTRING(t1.time_to,1,5),')') AS text");
						$this->db->where('status', 1);
						$this->db->order_by('title', 'asc');
						$res = $this->db->get('tms_shift_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();						
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;

					case "tms_pagination_master":
						$this->db->select("t1.record,t1.status,t1.is_default");
						$this->db->where('status', 1);
						$this->db->order_by('record', 'asc');
						$res = $this->db->get('tms_pagination_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						// array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;

					case "acc_state_master":
						$this->db->select("t1.*, t1.state_name as text , t1.state_id as id");
						// $this->db->where('status', 1);
						// $this->db->order_by('record', 'asc');
						$res = $this->db->get('acc_state_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					case "acc_districts_master":
						$this->db->select("t1.*");
						// $this->db->where('status', 1);
						// $this->db->order_by('record', 'asc');
						$res = $this->db->get('acc_districts_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);

						break;

					case "tms_email_account":
						$this->db->select("t1.email,t1.id , t1.email as text");
						$this->db->where('status', 1);
						$this->db->order_by('email', 'asc');
						$res = $this->db->get('tms_email_account t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;

					case "tms_email_account_incomming":
						$this->db->select("t1.email,t1.id,t1.email as text");
						$this->db->where('status', 1);
						#$this->db->where('id NOT IN (SELECT incoming_email_account FROM tms_queue_email where queue_id !=' . $queue_id . ' )', NULL, false);
						$this->db->order_by('email', 'asc');
						$res = $this->db->get('tms_email_account t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;

					case "tms_email_account_outgoing":
						$this->db->select("t1.email,t1.id,t1.email as text");
						$this->db->where('status', 1);
						// if ($queue_id != 0) {
						// 	$this->db->where('id != (SELECT incoming_email_account FROM tms_queue_email where queue_id =' . $queue_id . ' )', NULL, false);
						// }
						$this->db->order_by('email', 'asc');
						$res = $this->db->get('tms_email_account t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);

						break;

					case "tms_queue_master":
						$this->db->select("t1.queue,t1.id,t1.queue as text");
						$this->db->where('status', 1);
						if ($fullcontrol == 0) {
							$this->db->where_in('id', explode(',', $getQueueId));
						}
						$this->db->order_by('queue', 'asc');
						$res = $this->db->get('tms_queue_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						// array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;

					case "ticket_status_type":
						$this->db->select("t1.statustype_id as id ,t1.status_type as text");
						$this->db->order_by('status_type', 'asc');
						$res = $this->db->get('statustype_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;

					case "tms_conversation_type_master":
						$this->db->select("t1.id,t1.title as text,con_type_img,mobile_mandatory,mail_mandatory,is_default");
						$this->db->where('status', 1);
						$this->db->order_by('title', 'asc');
						$res = $this->db->get('tms_conversation_type_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					case "table_name":
						$database_name = $this->db->database;
						$this->db->select("table_name as id,table_name as text");
						$this->db->where('table_schema', $database_name);
						// $this->db->order_by('title', 'asc');
						$res = $this->db->get('information_schema.tables t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					default:
						break;
				}
			}
		} catch (Exception $e) {
			return ['error' => $e->getMessage()];
		}
		return $ret_data;
	}

	public function getQueuePriorityOptionList($option_type = "")
	{
		$ret_data = array();
		if (!empty($option_type)) {
			$this->db->select("t1.priority_id as id,t1.priority_name as text");
			$this->db->order_by('priority_name', 'asc');
			$res = $this->db->get("$option_type t1");
			if ($res === false) {
				throw new Exception("Error counting results: " . $this->db->error()['message']);
			}
			$ret_data['queue_priority'] = $res->result_array();
			array_unshift($ret_data['queue_priority'], ['id' => '0', 'text' => 'Select']);
		}
		return $ret_data;
	}

	public function getQueueStatusOptionList($option_type = "")
	{
		try {
			$ret_data = array();
			if (!empty($option_type)) {
				$this->db->select("t1.status_id,t1.status_name");
				$this->db->order_by('status_name', 'asc');
				$ret_data['queue_status'] = $this->db->get("$option_type t1")->result_array();
				// array_unshift($ret_data['queue_status'], ['id' => '0', 'text' => 'Select']);
			}
		} catch (Exception $e) {
			return ['error' => $e->getMessage()];
		}
		return $ret_data;
	}

	public function getQueueTemplateOptionList($option_type = "")
	{
		try {
			$ret_data = array();
			if (!empty($option_type)) {
				$this->db->select("t1.template_id as id,t1.template as text");
				$this->db->order_by('template', 'asc');
				$this->db->where('status',1);
				$res = $this->db->get("$option_type t1");
				if ($res === false) {
					throw new Exception("Error counting results: " . $this->db->error()['message']);
				}
				$ret_data['queue_template'] = $res->result_array();
				array_unshift($ret_data['queue_template'], ['id' => '0', 'text' => 'Select']);
			}
			return $ret_data;
		} catch (Exception $e) {
			return ['error' => $e->getMessage()];
		}
	}

	public function getQueueOptionList($option_type_arr = array(), $queue_id, $user_id)
	{
		$ret_data = array();

		try {
			foreach ($option_type_arr as $key => $option_type) {
				$type = $option_type;
				switch ($type) {
					case "tms_queue_priority":
						$this->db->select('priority_id as id, priority_name as text');
						$this->db->order_by('priority_name', 'asc');
						$res = $this->db->get("tms_queue_priority_$queue_id");
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					case "tms_queue_sub_category":
						$this->db->select("t2.sub_category_id as id, t2.sub_category_name as text");
						$this->db->from("tms_queue_category_$queue_id t1");
						$this->db->join("tms_queue_sub_category_$queue_id t2", "t1.category_id = t2.category_id", "left");
						$this->db->order_by('t2.sub_category_name', 'asc');
						$res = $this->db->get();
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					case "tms_queue_category":
						$this->db->select("t1.category_id as id, t1.category_name as text");
						$this->db->where('status',1);
						$this->db->from("tms_queue_category_$queue_id t1");
						$res = $this->db->get();
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					case "tms_queue_status":
						$this->db->select('status_id as id, status_name as text');
						$this->db->order_by('status_name', 'asc');
						$res = $this->db->get("tms_queue_status_$queue_id");
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						break;
					case "user":
						$this->db->select("t1.id, case when t1.`id` != $user_id then name else 'self' end as text, CASE WHEN t1.`id` != $user_id THEN 2 ELSE 1 END 'pp'");
						$this->db->where('status', 1);
						$this->db->where('full_control', 0);
						$this->db->order_by("pp", 'asc');
						$this->db->order_by('text', 'asc');
						$res = $this->db->get('tms_user_master t1');
						if ($res === false) {
							throw new Exception("Error counting results: " . $this->db->error()['message']);
						}
						$ret_data[$option_type] = $res->result_array();
						array_unshift($ret_data[$option_type], ['id' => '0', 'text' => 'Select']);
						break;
					default:
						break;
				}
			}
		} catch (Exception $e) {
			return ['error' => $e->getMessage()];
		}

		return $ret_data;
	}

	public function getChildUserDetails($userid)
	{
		// $userid =47;
		$query = $this->db->query("SET @child='';");
		$query = $this->db->query("CALL getChildUsers(" . $userid . ",@child)");

		$query = $this->db->query("SELECT @child as id");
		// $this->db->get();

		if ($query) {
			$row = $query->row();
			$childId = $row->id;
			return $childId;
			// Do something with $childId
		}
	}

	function getCategoryIdForEscalation($queue_id){
		if(!empty($queue_id)){
		$newElement = array('id' => 0, 'text' => 'Select');
		$this->db->select("category_id as id,category_name as text");
		
		// $this->db->where('queue_id', $queue_id);
		$this->db->order_by('category_name', 'asc');
		$table = 'tms_queue_category_'.$queue_id;
		$res = $this->db->get($table);
		// print_r($this->db->last_query());die;
		if ($res === false) {
			throw new Exception("Error counting results: " . $this->db->error()['message']);
		}
		
		$ret_data['QUEUE_CAT'] = $res->result_array();
		// array_unshift($ret_data['QUEUE_CAT'], $newElement);
		return $ret_data;
	}
	}
}
