<?php
ini_set("display_errors", 1);
if (!defined('BASEPATH'))
	exit('No direct script access allowed');


class Md_servicios extends CI_Model {

	public function getDepar()
	{
		$otherdb = $this->load->database('gca', TRUE);
		$otherdb->select('*');
		$otherdb->from('est_group');
		$return = $otherdb->get()->result_array();

		return $return;
	}

	public function getKeysMontechelo($cleanPost)
	{
		//log_message('error', json_encode($cleanPost)); 

		if(isset($cleanPost['msg'])) $word = $cleanPost['msg'];
		else $word = FALSE;
		if(isset($cleanPost['channel'])) $channel = $cleanPost['channel'];
		else $channel = FALSE;
		if(isset($cleanPost['nombre'])) $nombre = $cleanPost['nombre'];
		else $nombre = FALSE;
		if(isset($cleanPost['telefono'])) $telefono = $cleanPost['telefono'];
		else $telefono = FALSE;
		if(isset($cleanPost['correo'])) $correo = $cleanPost['correo'];
		else $correo = FALSE;
		if(isset($cleanPost['id'])) $user = $cleanPost['id'];
		else $user = FALSE;

		$status = 0;

		$otherdb = $this->load->database('montechelo', TRUE);
		$otherdb->select('*');
		$otherdb->from('ticket_etiquetas');
		$keys = $otherdb->get()->result_array();
		$exp=array();
		$chk=array();
		$hooman=array();
		$key = 0;
		$count = 0;
		$return = "";
		$found = FALSE;

			$mensaje = strtolower($word);
			$mensaje = urldecode($mensaje);
			$mensaje = normaliza($mensaje);
			foreach ($keys as $value) {
				$exp[$value['id']]['id'] = $value['id'];
				$exp[$value['id']]['categoria'] = $value['ticket_categoria_id'];
				$exp[$value['id']]['proyecto'] = $value['ticket_proyectos_id'];
				$chk[$value['id']] = strtolower($value['nombre']);
			}
			
			
			foreach ($chk as $value3) {
				if (strpos($mensaje, normaliza($value3)) !== false) {
					$key = array_search($value3, $chk);
					$msg = $value3;
					$hooman[$count]['key'] = $key;
					$hooman[$count]['msg'] = strlen(utf8_decode($msg));
					$found = TRUE;
					$count++;
				}
			}

		if($user && $found) {

			$maxi = max(array_column($hooman, 'msg'));
			$key = array_search($maxi, array_column($hooman, 'msg'));
			if(isset($hooman[$key]['key'])) $key = $hooman[$key]['key'];

			$categoria = $exp[$key]['categoria'];
			$proyecto = $exp[$key]['proyecto'];
			//573173616252 ------2
			//+573162261500 ------1
			if($correo == 573162261500) $proyecto = 1;
			if($correo == 573173616252) $proyecto = 2;
			if($correo == 573173484359) $proyecto = 4;
			if($correo == 573163772136) $proyecto = 3;
			if($correo == 50760657458) $proyecto = 3;
			
				$otherdb = $this->load->database('montechelo', TRUE);
				$otherdb->from('ticket_users');
				$otherdb->where('bot_user', $user);
				$otherdb->where('channel', $channel);
				$test_usr = $otherdb->get()->result_array();

				if (empty($nombre)) $nombre = $telefono;
				
				if (empty($test_usr)) {
					$data2 = array(
						'bot_user' => $user,
						'channel' => $channel,
						'nombre' => $nombre,
						'email' => "",
						'telefono' => $telefono,
						'created' => date("Y-m-d H:i:s", (time())),
						'avatar' => "https://soporte.fogatacrm.com/img/no-user.jpg",
						'created_by' => 1853,
						'cliente' => 4,
						'type' => 1,
					);

					$otherdb->insert('ticket_users', $data2);
					$cliente = $otherdb->insert_id();
				} else if (isset($test_usr[0]['id'])) {
					$cliente = $test_usr[0]['id'];
				}
				
				$otherdb = $this->load->database('montechelo', TRUE);
				$otherdb->from('ticket_email');
				$otherdb->where('cliente', $cliente);
				$otherdb->where('canal', $channel);
				$otherdb->where('status != ', 3);
				$otherdb->where('status != ', 10);
				$otherdb->where('status != ', 4);
				$otherdb->where('status != ', 11);
				$otherdb->where('status != ', 12);
				$otherdb->where('status != ', 14);
				$otherdb->where('status != ', 22);
				$otherdb->where('status != ', 23);
				$otherdb->where('status != ', 24);
				$otherdb->where('status != ', 25);
				$otherdb->where('status != ', 26);
				$otherdb->where('status != ', 27);
				$test = $otherdb->get()->result_array();

				$grupo1 = $proyecto;

				$agente = $otherdb->query('SELECT ss3nu_users.id AS agente, (SELECT count(*) FROM ticket_email WHERE ticket_email.status=2 AND ticket_email.agente = ss3nu_users.id) AS "cantidad" FROM ticket_email RIGHT JOIN ss3nu_users ON ss3nu_users.id = ticket_email.agente WHERE ss3nu_users.params = 1 AND ss3nu_users.grupo LIKE "%'.$grupo1.'%" GROUP BY ss3nu_users.id HAVING cantidad < 20 ORDER BY cantidad, ss3nu_users.id ASC'); 
			
				$agente->result_array();
	
				if (isset($agente->result_array[0]['agente'])) {
					
					$agente = $agente->result_array[0]['agente'];
					if( !isset($test[0]['id'])){
						
						$data2 = array(
							'cliente' => $cliente,
							'copia' => 1,
							'categoria' => $categoria,
							'canal' => $channel,
							'proyecto' => $proyecto,
							'prioridad' => 2,
							'etiquetas' => $key,
							'asunto' => 'Agente',
							'descripcion' => $word,
							'adjunto' => '',
							'fecha_creacion' => date("Y-m-d H:i:s", (time())),
							'fecha_final' => date("Y-m-d H:i:s", (time() + (1*60*60))),
							'creado_por' => 1853,
							'status' => 2,
							'view' =>  0,
							'nivel' => "",
							'agente' => $agente,
						);
						$otherdb->insert('ticket_email', $data2);
						$return = $otherdb->insert_id();

						if($channel == 4) {
		
							$whatsClient = array(
								'bot' => 1,
							);
							$this->db->where('id', $user);
							$this->db->update('bots_whatsapp_user', $whatsClient);
	
						}

						//log_message('error', json_encode($return)); 

						if(!empty($return)) $status = 1;

						$data3 = array(
							'ticket_id' => $return,
							'agente' => $agente,
							'descripcion' => 15,
							'time' => date("Y-m-d H:i:s", (time())),
						);
						$otherdb->insert('ticket_auditoria', $data3);

					}
				} else if( !isset($test[0]['id'])){
						$data2 = array(
							'cliente' => $cliente,
							'copia' => 1,
							'categoria' => $categoria,
							'canal' => $channel,
							'proyecto' => $proyecto,
							'prioridad' => 2,
							'etiquetas' => $key,
							'asunto' => 'Agente',
							'descripcion' => $word,
							'adjunto' => '',
							'fecha_creacion' => date("Y-m-d H:i:s", (time())),
							'fecha_final' => date("Y-m-d H:i:s", (time() + (1*60*60))),
							'creado_por' => 1853,
							'status' => 1,
							'view' =>  0,
							'nivel' => "",
						);
						$otherdb->insert('ticket_email', $data2);
						$return = $otherdb->insert_id();

						if($channel == 4) {
		
							$whatsClient = array(
								'bot' => 1,
							);
							$this->db->where('id', $user);
							$this->db->update('bots_whatsapp_user', $whatsClient);
	
						}

						//log_message('error', json_encode($return)); 

						if(!empty($return)) $status = 2;

						$data3 = array(
							'ticket_id' => $return,
							'agente' => 1853,
							'descripcion' => 5,
							'time' => date("Y-m-d H:i:s", (time())),
						);
						$otherdb->insert('ticket_auditoria', $data3);
				}

				return  $status;


		} else {
			log_message('error', json_encode($cleanPost)); 
			return $status;
		} 

	}

	public function getKeysGca($cleanPost)
	{
		//log_message('error', json_encode($cleanPost)); 

		if(isset($cleanPost['msg'])) $word = $cleanPost['msg'];
		else $word = FALSE;
		if(isset($cleanPost['channel'])) $channel = $cleanPost['channel'];
		else $channel = FALSE;
		if(isset($cleanPost['nombre'])) $nombre = $cleanPost['nombre'];
		else $nombre = FALSE;
		if(isset($cleanPost['telefono'])) $telefono = $cleanPost['telefono'];
		else $telefono = FALSE;
		if(isset($cleanPost['correo'])) $correo = $cleanPost['correo'];
		else $correo = FALSE;
		if(isset($cleanPost['id'])) $user = $cleanPost['id'];
		else $user = FALSE;

		$status = 0;

		$otherdb = $this->load->database('gca', TRUE);
		$otherdb->select('*');
		$otherdb->from('ticket_etiquetas');
		$keys = $otherdb->get()->result_array();
		$exp=array();
		$chk=array();
		$hooman=array();
		$key = 0;
		$count = 0;
		$return = "";
		$found = FALSE;

			$mensaje = strtolower($word);
			$mensaje = urldecode($mensaje);
			$mensaje = normaliza($mensaje);
			foreach ($keys as $value) {
				$exp[$value['id']]['id'] = $value['id'];
				$exp[$value['id']]['categoria'] = $value['ticket_categoria_id'];
				$exp[$value['id']]['proyecto'] = $value['ticket_proyectos_id'];
				$chk[$value['id']] = strtolower($value['nombre']);
			}
			
			
			foreach ($chk as $value3) {
				if (strpos($mensaje, normaliza($value3)) !== false) {
					$key = array_search($value3, $chk);
					$msg = $value3;
					$hooman[$count]['key'] = $key;
					$hooman[$count]['msg'] = strlen(utf8_decode($msg));
					$found = TRUE;
					$count++;
				}
			}

		if($user && $found) {

			$maxi = max(array_column($hooman, 'msg'));
			$key = array_search($maxi, array_column($hooman, 'msg'));
			if(isset($hooman[$key]['key'])) $key = $hooman[$key]['key'];

			$categoria = $exp[$key]['categoria'];
			$proyecto = $exp[$key]['proyecto'];
			
			
				$otherdb = $this->load->database('gca', TRUE);
				$otherdb->from('ticket_users');
				$otherdb->where('bot_user', $user);
				$otherdb->where('channel', $channel);
				$test_usr = $otherdb->get()->result_array();

				if (empty($nombre)) $nombre = $telefono;
				
				if (empty($test_usr)) {
					$data2 = array(
						'bot_user' => $user,
						'channel' => $channel,
						'nombre' => $nombre,
						'email' => "",
						'telefono' => $telefono,
						'created' => date("Y-m-d H:i:s", (time())),
						'avatar' => "https://soporte.fogatacrm.com/img/no-user.jpg",
						'created_by' => 1853,
						'cliente' => 4,
						'type' => 1,
					);

					$otherdb->insert('ticket_users', $data2);
					$cliente = $otherdb->insert_id();
				} else if (isset($test_usr[0]['id'])) {
					$cliente = $test_usr[0]['id'];
				}
				
				$otherdb = $this->load->database('gca', TRUE);
				$otherdb->from('ticket_email');
				$otherdb->where('cliente', $cliente);
				$otherdb->where('canal', $channel);
				$otherdb->where('status != ', 3);
				$otherdb->where('status != ', 10);
				$otherdb->where('status != ', 4);
				$otherdb->where('status != ', 11);
				$otherdb->where('status != ', 12);
				$otherdb->where('status != ', 14);
				$otherdb->where('status != ', 22);
				$otherdb->where('status != ', 23);
				$otherdb->where('status != ', 24);
				$otherdb->where('status != ', 25);
				$otherdb->where('status != ', 26);
				$otherdb->where('status != ', 27);
				$test = $otherdb->get()->result_array();

				$grupo1 = $proyecto;

				$agente = $otherdb->query('SELECT ss3nu_users.id AS agente, (SELECT count(*) FROM ticket_email WHERE ticket_email.status=2 AND ticket_email.agente = ss3nu_users.id) AS "cantidad" FROM ticket_email RIGHT JOIN ss3nu_users ON ss3nu_users.id = ticket_email.agente WHERE ss3nu_users.params = 1 AND ss3nu_users.grupo LIKE "%'.$grupo1.'%" GROUP BY ss3nu_users.id HAVING cantidad < 20 ORDER BY cantidad, ss3nu_users.id ASC'); 
			
				$agente->result_array();
	
				if (isset($agente->result_array[0]['agente'])) {
					
					$agente = $agente->result_array[0]['agente'];
					if( !isset($test[0]['id'])){
						
						$data2 = array(
							'cliente' => $cliente,
							'copia' => 1,
							'categoria' => $categoria,
							'canal' => $channel,
							'proyecto' => $proyecto,
							'prioridad' => 2,
							'etiquetas' => $key,
							'asunto' => 'Agente',
							'descripcion' => $word,
							'adjunto' => '',
							'fecha_creacion' => date("Y-m-d H:i:s", (time())),
							'fecha_final' => date("Y-m-d H:i:s", (time() + (1*60*60))),
							'creado_por' => 1853,
							'status' => 2,
							'view' =>  0,
							'nivel' => "",
							'agente' => $agente,
						);
						$otherdb->insert('ticket_email', $data2);
						$return = $otherdb->insert_id();

						if($channel == 4) {
		
							$whatsClient = array(
								'bot' => 1,
							);
							$this->db->where('id', $user);
							$this->db->update('bots_whatsapp_user', $whatsClient);
	
						}

						//log_message('error', json_encode($return)); 

						if(!empty($return)) $status = 1;

						$data3 = array(
							'ticket_id' => $return,
							'agente' => $agente,
							'descripcion' => 15,
							'time' => date("Y-m-d H:i:s", (time())),
						);
						$otherdb->insert('ticket_auditoria', $data3);

					}
				} else if( !isset($test[0]['id'])){
						$data2 = array(
							'cliente' => $cliente,
							'copia' => 1,
							'categoria' => $categoria,
							'canal' => $channel,
							'proyecto' => $proyecto,
							'prioridad' => 2,
							'etiquetas' => $key,
							'asunto' => 'Agente',
							'descripcion' => $word,
							'adjunto' => '',
							'fecha_creacion' => date("Y-m-d H:i:s", (time())),
							'fecha_final' => date("Y-m-d H:i:s", (time() + (1*60*60))),
							'creado_por' => 1853,
							'status' => 1,
							'view' =>  0,
							'nivel' => "",
						);
						$otherdb->insert('ticket_email', $data2);
						$return = $otherdb->insert_id();

						if($channel == 4) {
		
							$whatsClient = array(
								'bot' => 1,
							);
							$this->db->where('id', $user);
							$this->db->update('bots_whatsapp_user', $whatsClient);
	
						}

						//log_message('error', json_encode($return)); 

						if(!empty($return)) $status = 2;

						$data3 = array(
							'ticket_id' => $return,
							'agente' => 1853,
							'descripcion' => 5,
							'time' => date("Y-m-d H:i:s", (time())),
						);
						$otherdb->insert('ticket_auditoria', $data3);
				}

				return  $status;


		} else {
			log_message('error', json_encode($cleanPost)); 
			return $status;
		} 

	}

	public function getKeys($cleanPost)
	{
		//log_message('error', json_encode($cleanPost)); 

		if(isset($cleanPost['msg'])) $word = $cleanPost['msg'];
		else $word = FALSE;
		if(isset($cleanPost['channel'])) $channel = $cleanPost['channel'];
		else $channel = FALSE;
		if(isset($cleanPost['nombre'])) $nombre = $cleanPost['nombre'];
		else $nombre = FALSE;
		if(isset($cleanPost['telefono'])) $telefono = $cleanPost['telefono'];
		else $telefono = FALSE;
		if(isset($cleanPost['correo'])) $correo = $cleanPost['correo'];
		else $correo = FALSE;
		if(isset($cleanPost['id'])) $user = $cleanPost['id'];
		else $user = FALSE;

		$status = 0;

		if($user) {
			
				$key=41;
			
				$otherdb = $this->load->database('zapata', TRUE);
				$otherdb->from('ticket_users');
				$otherdb->where('bot_user', $user);
				$otherdb->where('channel', $channel);
				$test_usr = $otherdb->get()->result_array();

				if (empty($nombre)) $nombre = $telefono;
				
				if (empty($test_usr)) {
					$data2 = array(
						'bot_user' => $user,
						'channel' => $channel,
						'nombre' => $nombre,
						'email' => "",
						'telefono' => $telefono,
						'created' => date("Y-m-d H:i:s", (time())),
						'avatar' => "https://soporte.fogatacrm.com/img/no-user.jpg",
						'created_by' => 1853,
						'cliente' => 4,
						'type' => 1,
					);

					$otherdb->insert('ticket_users', $data2);
					$cliente = $otherdb->insert_id();
				} else if (isset($test_usr[0]['id'])) {
					$cliente = $test_usr[0]['id'];
				}
				
				$otherdb = $this->load->database('zapata', TRUE);
				$otherdb->from('ticket_email');
				$otherdb->where('cliente', $cliente);
				$otherdb->where('canal', $channel);
				$otherdb->where('status != ', 3);
				$otherdb->where('status != ', 10);
				$otherdb->where('status != ', 4);
				$otherdb->where('status != ', 11);
				$otherdb->where('status != ', 12);
				$otherdb->where('status != ', 14);
				$test = $otherdb->get()->result_array();


				$proyecto = 18;
				$grupo1 = 17;
				


				$agente = $otherdb->query('SELECT ss3nu_users.id AS agente, (SELECT count(*) FROM ticket_email WHERE ticket_email.status=2 AND ticket_email.agente = ss3nu_users.id) AS "cantidad" FROM ticket_email RIGHT JOIN ss3nu_users ON ss3nu_users.id = ticket_email.agente WHERE ss3nu_users.params = 1 AND ss3nu_users.grupo LIKE "%'.$grupo1.'%" GROUP BY ss3nu_users.id HAVING cantidad < 20 ORDER BY cantidad, ss3nu_users.id ASC'); 
			
				$agente->result_array();
	
				if (isset($agente->result_array[0]['agente'])) {
					
					$agente = $agente->result_array[0]['agente'];
					if( !isset($test[0]['id'])){
						
						$data2 = array(
							'cliente' => $cliente,
							'copia' => 1,
							'categoria' => 1,
							'canal' => $channel,
							'proyecto' => $proyecto,
							'prioridad' => 2,
							'etiquetas' => $key,
							'asunto' => 'Agente',
							'descripcion' => $word,
							'adjunto' => '',
							'fecha_creacion' => date("Y-m-d H:i:s", (time())),
							'fecha_final' => date("Y-m-d H:i:s", (time() + (1*60*60))),
							'creado_por' => 1853,
							'status' => 2,
							'view' =>  0,
							'nivel' => "",
							'agente' => $agente,
						);
						$otherdb->insert('ticket_email', $data2);
						$return = $otherdb->insert_id();

						//log_message('error', json_encode($return)); 

						if(!empty($return)) $status = 1;

						$data3 = array(
							'ticket_id' => $return,
							'agente' => $agente,
							'descripcion' => 15,
							'time' => date("Y-m-d H:i:s", (time())),
						);
						$otherdb->insert('ticket_auditoria', $data3);

					}
				} else if( !isset($test[0]['id'])){
						$data2 = array(
							'cliente' => $cliente,
							'copia' => 1,
							'categoria' => 1,
							'canal' => $channel,
							'proyecto' => $proyecto,
							'prioridad' => 2,
							'etiquetas' => $key,
							'asunto' => 'Agente',
							'descripcion' => $word,
							'adjunto' => '',
							'fecha_creacion' => date("Y-m-d H:i:s", (time())),
							'fecha_final' => date("Y-m-d H:i:s", (time() + (1*60*60))),
							'creado_por' => 1853,
							'status' => 1,
							'view' =>  0,
							'nivel' => "",
						);
						$otherdb->insert('ticket_email', $data2);
						$return = $otherdb->insert_id();

						//log_message('error', json_encode($return)); 

						if(!empty($return)) $status = 2;

						$data3 = array(
							'ticket_id' => $return,
							'agente' => 1853,
							'descripcion' => 5,
							'time' => date("Y-m-d H:i:s", (time())),
						);
						$otherdb->insert('ticket_auditoria', $data3);
				}

				return  $return;


		} else {
			log_message('error', json_encode($cleanPost)); 
			return $status;
		} 

	}

	public function getKeysKrea($cleanPost)
	{
		error_log("krea 1" . json_encode($cleanPost));
		if(isset($cleanPost['msg'])) $word = $cleanPost['msg'];
		else $word = FALSE;
		if(isset($cleanPost['channel'])) $channel = $cleanPost['channel'];
		else $channel = FALSE;
		if(isset($cleanPost['nombre'])) $nombre = $cleanPost['nombre'];
		else $nombre = FALSE;
		if(isset($cleanPost['telefono'])) $telefono = $cleanPost['telefono'];
		else $telefono = FALSE;
		if(isset($cleanPost['bot'])) $bot = $cleanPost['bot'];
		else $bot = FALSE;
		if(isset($cleanPost['id'])) $user = $cleanPost['id'];
		else $user = FALSE;
	

		$otherdb = $this->load->database('krea', TRUE);
		$otherdb->select('*');
		$otherdb->from('ticket_etiquetas');
		$keys = $otherdb->get()->result_array();
		$exp=array();
		$chk=array();
		$hooman=array();
		$key = 0;
		$count = 0;
		$return = "";
		$found = FALSE;

			$mensaje = strtolower($word);
			$mensaje = urldecode($mensaje);
			$mensaje = normaliza($mensaje);
			foreach ($keys as $value) {
				$exp[$value['id']]['id'] = $value['id'];
				$exp[$value['id']]['categoria'] = $value['ticket_categoria_id'];
				$exp[$value['id']]['proyecto'] = $value['ticket_proyectos_id'];
				$chk[$value['id']] = strtolower($value['nombre']);
			}
			
			foreach ($chk as $value3) {
				if (strpos($mensaje, normaliza($value3)) !== false) {
					$key = array_search($value3, $chk);
					$msg = $value3;
					$hooman[$count]['key'] = $key;
					$hooman[$count]['msg'] = strlen(utf8_decode($msg));
					$found = TRUE;
					$count++;
				}
			}
			
				if($found && $user > 0) {

					$maxi = max(array_column($hooman, 'msg'));
					$key = array_search($maxi, array_column($hooman, 'msg'));
					if(isset($hooman[$key]['key'])) $key = $hooman[$key]['key'];

					$categoria = $exp[$key]['categoria'];
					$proyecto = $exp[$key]['proyecto'];

					if($bot == 1723102124418163) { //Centro San Lucas
						$group = 25;
						$proyecto = 25;
					};
					if($bot == 1462297100570092) { //Centro San Juan
						$group = 26;
						$proyecto = 26;
					};
					if($bot == 220703664982486) { //La Mar
						$group = 27;
						$proyecto = 27;
					};
					if($bot == 106359710844912) { //Domos
						$group = 28;
						$proyecto = 28;
					};
	
					$otherdb = $this->load->database('krea', TRUE);
					$otherdb->from('ticket_users');
					$otherdb->where('bot_user', $user);
					$otherdb->where('channel', $channel);
					$test_usr = $otherdb->get()->result_array();
	
					if (empty($nombre)) $nombre = $telefono;
	
					if (empty($test_usr)) {
						$data2 = array(
							'bot_user' => $user,
							'channel' => $channel,
							'nombre' => $nombre,
							'email' => "",
							'telefono' => $telefono,
							'created' => date("Y-m-d H:i:s", (time())),
							'avatar' => "https://soporte.fogatacrm.com/img/no-user.jpg",
							'created_by' => 1853,
							'cliente' => 4,
						);
						$otherdb->insert('ticket_users', $data2);
						$cliente = $otherdb->insert_id();
					} else if (isset($test_usr[0]['id'])) {
						$cliente = $test_usr[0]['id'];
					}
	
					$otherdb = $this->load->database('krea', TRUE);
					$otherdb->from('ticket_email');
					$otherdb->where('cliente', $cliente);
					$otherdb->where('canal', $channel);
					$otherdb->where('status != ', 3);
					$otherdb->where('status != ', 4);
					$otherdb->where('status != ', 10);
					$otherdb->where('status != ', 11);
					$otherdb->where('status != ', 12);
					$otherdb->where('status != ', 13);
					$otherdb->where('status != ', 14);
					$otherdb->where('status != ', 15);
					$otherdb->where('status != ', 22);
					$ticket_usr = $otherdb->get()->result_array();
					error_log(json_encode($ticket_usr));


					$agente = $otherdb->query('SELECT ss3nu_users.id AS agente, (SELECT count(*) FROM ticket_email WHERE ticket_email.status=2 AND ticket_email.agente = ss3nu_users.id) AS "cantidad" FROM ticket_email RIGHT JOIN ss3nu_users ON ss3nu_users.id = ticket_email.agente WHERE ss3nu_users.params = 1 AND ss3nu_users.grupo LIKE "%'.$group.'%" GROUP BY ss3nu_users.id HAVING cantidad < 20 ORDER BY cantidad, ss3nu_users.id ASC'); 
			
					$agente->result_array();
					error_log(json_encode($agente->result_array));

					if (isset($agente->result_array[0]['agente'])) {
						
						$agente = $agente->result_array[0]['agente'];
						if( !isset($ticket_usr[0]['id'])){
							error_log("krea 3".json_encode($ticket_usr));

							$data2 = array(
								'cliente' => $cliente,
								'copia' => 1,
								'categoria' => $categoria,
								'canal' => $channel,
								'proyecto' => $proyecto,
								'prioridad' => 4,
								'etiquetas' => $key,
								'asunto' => $chk[$key],
								'descripcion' => $word,
								'adjunto' => '',
								'fecha_creacion' => date("Y-m-d H:i:s", (time())),
								'fecha_final' => date("Y-m-d H:i:s", (time() + (48*60*60))),
								'creado_por' => 1853,
								'status' => 2,
								'view' =>  0,
								'nivel' => "",
								'agente' => $agente,
							);
							$otherdb->insert('ticket_email', $data2);
							$return = $otherdb->insert_id();
	
							if($channel == 4) {
			
								$whatsClient = array(
									'bot' => 1,
								);
								$this->db->where('id', $user);
								$this->db->update('bots_whatsapp_user', $whatsClient);
		
							}

							if($channel == 2) {
			
								$whatsClient = array(
									'active' => 1,
								);
								$this->db->where('id', $user);
								$this->db->update('bots_manager', $whatsClient);
		
							}

							if($channel == 1) {
			
								$whatsClient = array(
									'bot' => 1,
									'live' => 1,
								);
								$this->db->where('id', $user);
								$this->db->update('chatbot_web_user', $whatsClient);
		
							}
	
							//log_message('error', json_encode($return)); 
	
							if(!empty($return)) $status = 1;
	
							$data3 = array(
								'ticket_id' => $return,
								'agente' => $agente,
								'descripcion' => 15,
								'time' => date("Y-m-d H:i:s", (time())),
							);
							$otherdb->insert('ticket_auditoria', $data3);
	
						}
					} else if( !isset($ticket_usr[0]['id'])){
						error_log( "krea 4" . json_encode( $ticket_usr ) );

							$data2 = array(
								'cliente' => $cliente,
								'copia' => 1,
								'categoria' => $categoria,
								'canal' => $channel,
								'proyecto' => $proyecto,
								'prioridad' => 4,
								'etiquetas' => $key,
								'asunto' => $chk[$key],
								'descripcion' => $word,
								'adjunto' => '',
								'fecha_creacion' => date("Y-m-d H:i:s", (time())),
								'fecha_final' => date("Y-m-d H:i:s", (time() + (48*60*60))),
								'creado_por' => 1853,
								'status' => 1,
								'view' =>  0,
								'nivel' => "",
							);
							$otherdb->insert('ticket_email', $data2);
							$return = $otherdb->insert_id();
	
							if($channel == 4) {
			
								$whatsClient = array(
									'bot' => 1,
								);
								$this->db->where('id', $user);
								$this->db->update('bots_whatsapp_user', $whatsClient);
		
							}

							if($channel == 2) {
			
								$whatsClient = array(
									'active' => 1,
								);
								$this->db->where('id', $user);
								$this->db->update('bots_manager', $whatsClient);
		
							}

							if($channel == 1) {
			
								$whatsClient = array(
									'bot' => 1,
									'live' => 1,
								);
								$this->db->where('id', $user);
								$this->db->update('chatbot_web_user', $whatsClient);
		
							}

							//log_message('error', json_encode($return)); 
	
							if(!empty($return)) $status = 2;
	
							$data3 = array(
								'ticket_id' => $return,
								'agente' => 1853,
								'descripcion' => 5,
								'time' => date("Y-m-d H:i:s", (time())),
							);
							$otherdb->insert('ticket_auditoria', $data3);
					}
	
					/*if( !isset($ticket_usr[0]['id'])) {

							$data2 = array(
								'cliente' => $cliente,
								'copia' => 1,
								'categoria' => $categoria,
								'canal' => $channel,
								'proyecto' => $proyecto,
								'prioridad' => 4,
								'etiquetas' => $key,
								'asunto' => $chk[$key],
								'descripcion' => $word,
								'adjunto' => '',
								'fecha_creacion' => date("Y-m-d H:i:s", time()),
								'fecha_final' => date("Y-m-d H:i:s", (time() + (48*60*60))),
								'creado_por' => 1853,
								'status' => 1,
								'view' =>  0,
								'nivel' => "",
							);
							$otherdb->insert('ticket_email', $data2);
							$return = $otherdb->insert_id();

							$data3 = array(
								'ticket_id' => $return,
								'agente' => 1853,
								'descripcion' => 5,
								'time' => date("Y-m-d H:i:s", time()),
							);
							$otherdb->insert('ticket_auditoria', $data3);
					}*/

				}
				
				return  $return;
		
	}

	public function getKeysContacta($cleanPost)
	{

		if(isset($cleanPost['msg'])) $word = $cleanPost['msg'];
		else $word = FALSE;
		if(isset($cleanPost['channel'])) $channel = $cleanPost['channel'];
		else $channel = FALSE;
		if(isset($cleanPost['nombre'])) $nombre = $cleanPost['nombre'];
		else $nombre = FALSE;
		if(isset($cleanPost['telefono'])) $telefono = $cleanPost['telefono'];
		else $telefono = FALSE;
		if(isset($cleanPost['bot'])) $bot = $cleanPost['bot'];
		else $bot = FALSE;
		if(isset($cleanPost['id'])) $user = $cleanPost['id'];
		else $user = FALSE;
	

		$otherdb = $this->load->database('contacta', TRUE);
		$otherdb->select('*');
		$otherdb->from('ticket_etiquetas');
		$keys = $otherdb->get()->result_array();
		$exp=array();
		$chk=array();
		$hooman=array();
		$key = 0;
		$count = 0;
		$return = "";
		$found = FALSE;
		$status = 0;

			$mensaje = strtolower($word);
			$mensaje = urldecode($mensaje);
			$mensaje = normaliza($mensaje);
			foreach ($keys as $value) {
				$exp[$value['id']]['id'] = $value['id'];
				$exp[$value['id']]['categoria'] = $value['ticket_categoria_id'];
				$exp[$value['id']]['proyecto'] = $value['ticket_proyectos_id'];
				$exp[$value['id']]['prioridad'] = $value['est_prioridad_id'];
				$chk[$value['id']] = strtolower($value['nombre']);
			}
			
			foreach ($chk as $value3) {
				if (strpos($mensaje, normaliza($value3)) !== false) {
					$key = array_search($value3, $chk);
					$msg = $value3;
					$hooman[$count]['key'] = $key;
					$hooman[$count]['msg'] = strlen(utf8_decode($msg));
					$found = TRUE;
					$count++;
				}
			}
			
				if($found && $user > 0) {

					$maxi = max(array_column($hooman, 'msg'));
					$key = array_search($maxi, array_column($hooman, 'msg'));
					if(isset($hooman[$key]['key'])) $key = $hooman[$key]['key'];

					$categoria = $exp[$key]['categoria'];
					$proyecto = $exp[$key]['proyecto'];
					$prioridad = $exp[$key]['prioridad'];

					if($bot == 100965411559018) { //telefix
						$categoria = 1;
						$proyecto = 1;
					};
					if($bot == 115763473596923) { //contacta
						$categoria = 35;
						$proyecto = 2;
					};
	
					$otherdb = $this->load->database('contacta', TRUE);
					$otherdb->from('ticket_users');
					$otherdb->where('bot_user', $user);
					$otherdb->where('channel', $channel);
					$test_usr = $otherdb->get()->result_array();
	
					if (empty($nombre)) $nombre = $telefono;
	
					if (empty($test_usr)) {
						$data2 = array(
							'bot_user' => $user,
							'channel' => $channel,
							'nombre' => $nombre,
							'email' => "",
							'telefono' => $telefono,
							'created' => date("Y-m-d H:i:s", (time())),
							'avatar' => "https://soporte.fogatacrm.com/img/no-user.jpg",
							'created_by' => 1853,
							'cliente' => 4,
						);
						$otherdb->insert('ticket_users', $data2);
						$cliente = $otherdb->insert_id();
					} else if (isset($test_usr[0]['id'])) {
						$cliente = $test_usr[0]['id'];
					}
	
					$otherdb = $this->load->database('contacta', TRUE);
					$otherdb->from('ticket_email');
					$otherdb->where('cliente', $cliente);
					$otherdb->where('canal', $channel);
					$otherdb->where('status != ', 3);
					$otherdb->where('status != ', 4);
					$otherdb->where('status != ', 10);
					$otherdb->where('status != ', 11);
					$otherdb->where('status != ', 12);
					$otherdb->where('status != ', 13);
					$otherdb->where('status != ', 14);
					$otherdb->where('status != ', 15);
					$otherdb->where('status != ', 22);
					$ticket_usr = $otherdb->get()->result_array();
	
	
					if( !isset($ticket_usr[0]['id'])) {

							$data2 = array(
								'cliente' => $cliente,
								'copia' => 1,
								'categoria' => $categoria,
								'canal' => $channel,
								'proyecto' => $proyecto,
								'prioridad' => $prioridad,
								'etiquetas' => $key,
								'asunto' => $chk[$key],
								'descripcion' => $word,
								'adjunto' => '',
								'fecha_creacion' => date("Y-m-d H:i:s", time()),
								'fecha_final' => date("Y-m-d H:i:s", (time() + (48*60*60))),
								'creado_por' => 1853,
								'status' => 1,
								'view' =>  0,
								'nivel' => "",
							);
							$otherdb->insert('ticket_email', $data2);
							$return = $otherdb->insert_id();
							$status = 1;

							$data3 = array(
								'ticket_id' => $return,
								'agente' => 1853,
								'descripcion' => 5,
								'time' => date("Y-m-d H:i:s", time()),
							);
							$otherdb->insert('ticket_auditoria', $data3);
					}

				}
				
				return  $status;
		
	}

	public function getKeysPazko($cleanPost)
	{

		if(isset($cleanPost['msg'])) $word = $cleanPost['msg'];
		else $word = FALSE;
		if(isset($cleanPost['channel'])) $channel = $cleanPost['channel'];
		else $channel = FALSE;
		if(isset($cleanPost['nombre'])) $nombre = $cleanPost['nombre'];
		else $nombre = FALSE;
		if(isset($cleanPost['telefono'])) $telefono = $cleanPost['telefono'];
		else $telefono = FALSE;
		if(isset($cleanPost['bot'])) $bot = $cleanPost['bot'];
		else $bot = FALSE;
		if(isset($cleanPost['id'])) $user = $cleanPost['id'];
		else $user = FALSE;
	

		$otherdb = $this->load->database('pazko', TRUE);
		$otherdb->select('*');
		$otherdb->from('ticket_etiquetas');
		$keys = $otherdb->get()->result_array();
		$exp=array();
		$chk=array();
		$hooman=array();
		$key = 0;
		$count = 0;
		$return = "";
		$found = FALSE;

			$mensaje = strtolower($word);
			$mensaje = urldecode($mensaje);
			$mensaje = normaliza($mensaje);
			foreach ($keys as $value) {
				$exp[$value['id']]['id'] = $value['id'];
				$exp[$value['id']]['categoria'] = $value['ticket_categoria_id'];
				$exp[$value['id']]['proyecto'] = $value['ticket_proyectos_id'];
				$chk[$value['id']] = strtolower($value['nombre']);
			}
			
			
			foreach ($chk as $value3) {
				if (strpos($mensaje, normaliza($value3)) !== false) {
					$key = array_search($value3, $chk);
					$msg = $value3;
					$hooman[$count]['key'] = $key;
					$hooman[$count]['msg'] = strlen(utf8_decode($msg));
					$found = TRUE;
					$count++;
				}
			}
			
				if($found && $user > 0) {

					$maxi = max(array_column($hooman, 'msg'));
					$key = array_search($maxi, array_column($hooman, 'msg'));
					if(isset($hooman[$key]['key'])) $key = $hooman[$key]['key'];

					$categoria = $exp[$key]['categoria'];
					$proyecto = $exp[$key]['proyecto'];
	
					$otherdb = $this->load->database('pazko', TRUE);
					$otherdb->from('ticket_users');
					$otherdb->where('bot_user', $user);
					$otherdb->where('channel', $channel);
					$test_usr = $otherdb->get()->result_array();
	
					if (empty($nombre)) $nombre = $telefono;
	
					if (empty($test_usr)) {
						$data2 = array(
							'bot_user' => $user,
							'channel' => $channel,
							'nombre' => $nombre,
							'email' => "",
							'telefono' => $telefono,
							'created' => date("Y-m-d H:i:s", (time())),
							'avatar' => "https://soporte.fogatacrm.com/img/no-user.jpg",
							'created_by' => 1853,
							'cliente' => 4,
						);
						$otherdb->insert('ticket_users', $data2);
						$cliente = $otherdb->insert_id();
					} else if (isset($test_usr[0]['id'])) {
						$cliente = $test_usr[0]['id'];
					}
	
					$otherdb = $this->load->database('pazko', TRUE);
					$otherdb->from('ticket_email');
					$otherdb->where('cliente', $cliente);
					$otherdb->where('canal', $channel);
					$otherdb->where('status != ', 3);
					$otherdb->where('status != ', 4);
					$otherdb->where('status != ', 10);
					$otherdb->where('status != ', 11);
					$otherdb->where('status != ', 12);
					$otherdb->where('status != ', 13);
					$otherdb->where('status != ', 14);
					$otherdb->where('status != ', 15);
					$otherdb->where('status != ', 22);
					$test_usr = $otherdb->get()->result_array();
	
					if( !isset($test_usr[0]['id'])) {
							$data2 = array(
								'cliente' => $cliente,
								'copia' => 1,
								'categoria' => $categoria,
								'canal' => $channel,
								'proyecto' => $proyecto,
								'prioridad' => 4,
								'etiquetas' => $key,
								'asunto' => $chk[$key],
								'descripcion' => $word,
								'adjunto' => '',
								'fecha_creacion' => date("Y-m-d H:i:s", time()),
								'fecha_final' => date("Y-m-d H:i:s", (time() + (7*24*60*60))),
								'creado_por' => 1853,
								'status' => 1,
								'view' =>  0,
								'nivel' => "",
							);
							$otherdb->insert('ticket_email', $data2);
							$return = $otherdb->insert_id();

							/*if($channel == 4) {
		
								$whatsClient = array(
									'bot' => 1,
								);
								$this->db->where('id', $user);
								$this->db->update('bots_whatsapp_user', $whatsClient);
		
							}*/

							$data3 = array(
								'ticket_id' => $return,
								'agente' => 1853,
								'descripcion' => 5,
								'time' => date("Y-m-d H:i:s", time()),
							);
							$otherdb->insert('ticket_auditoria', $data3);
					}


				}
				
				return  $return;
		
	}

	public function closePazko($cleanPost)
	{

		if(isset($cleanPost['msg'])) $word = $cleanPost['msg'];
		else $word = FALSE;
		if(isset($cleanPost['channel'])) $channel = $cleanPost['channel'];
		else $channel = FALSE;
		if(isset($cleanPost['nombre'])) $nombre = $cleanPost['nombre'];
		else $nombre = FALSE;
		if(isset($cleanPost['telefono'])) $telefono = $cleanPost['telefono'];
		else $telefono = FALSE;
		if(isset($cleanPost['bot'])) $bot = $cleanPost['bot'];
		else $bot = FALSE;
		if(isset($cleanPost['id'])) $user = $cleanPost['id'];
		else $user = FALSE;

		$exp=array();
		$chk=array();
		$hooman=array();
		$key = 0;
		$count = 0;
		$return = "";

			$mensaje = strtolower($word);
			$mensaje = urldecode($mensaje);
			$mensaje = normaliza($mensaje);

			
				if($user > 0) {

					$otherdb = $this->load->database('pazko', TRUE);
					$otherdb->from('ticket_users');
					$otherdb->where('bot_user', $user);
					$otherdb->where('channel', $channel);
					$test_usr = $otherdb->get()->result_array();
	
					if (empty($nombre)) $nombre = $telefono;
	
					if (empty($test_usr)) {
						$data2 = array(
							'bot_user' => $user,
							'channel' => $channel,
							'nombre' => $nombre,
							'email' => "",
							'telefono' => $telefono,
							'created' => date("Y-m-d H:i:s", (time())),
							'avatar' => "https://soporte.fogatacrm.com/img/no-user.jpg",
							'created_by' => 1853,
							'cliente' => 4,
						);
						$otherdb->insert('ticket_users', $data2);
						$cliente = $otherdb->insert_id();
					} else if (isset($test_usr[0]['id'])) {
						$cliente = $test_usr[0]['id'];
					}
	
					$otherdb = $this->load->database('pazko', TRUE);
					$otherdb->from('ticket_email');
					$otherdb->where('cliente', $cliente);
					$otherdb->where('canal', $channel);
					$otherdb->where('status != ', 3);
					$otherdb->where('status != ', 4);
					$otherdb->where('status != ', 10);
					$otherdb->where('status != ', 11);
					$otherdb->where('status != ', 12);
					$otherdb->where('status != ', 13);
					$otherdb->where('status != ', 14);
					$otherdb->where('status != ', 15);
					$otherdb->where('status != ', 22);
					$test_usr = $otherdb->get()->result_array();
	
					if( isset($test_usr[0]['id'])) {

							$data3 = array(
								'agente' => 1853,
								'cerrado_por' => 1853,
								'status' => 3,
								'view' => $test_usr[0]['id'],
								'fecha_cierre' => date("Y-m-d H:i:s", time()),
							);
							$otherdb->where('id', $test_usr[0]['id']);
							$otherdb->update('ticket_email', $data3);

							$data3 = array(
								'ticket_id' => $return,
								'agente' => 1853,
								'descripcion' => 4,
								'time' => date("Y-m-d H:i:s", time()),
							);
							$otherdb->insert('ticket_auditoria', $data3);
					}

				}
				
				return  $return;
		
	}

}
