404

D7net
:/ var / www / html / chatweb02 / application / models /
Filename : Conversa_model.php
back
<?php

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

Class Conversa_model extends CI_Model
{
	function __construct()
	{
		// Call the Model constructor
		parent::__construct();
		@$this->load->database();
	}

    // ---------- chat ------------------
    function getContatosConversa($datas)
	{
		$sql = "SELECT
				    con.cht_con_id,
				    con.cht_con_nome,
				    con.cht_con_imagem,
				    con.cht_con_descricao,
				    con.cht_con_status,
				    con.cht_con_tipo,
				    con.cht_usu_id_1,
				    con.cht_usu_id_2,
				    usu2.cht_usu_id,
				    usu2.cht_usu_nome,
				    usu2.cht_usu_imagem,
				    '' img_outro,
				    '' nome_outro,
				    0 id_outro,
				    '' status_outro,
				    usu2.cht_usu_online online,
				    usu2.cht_usu_status,
				    usu2.cht_usu_online,
				    CONVERT_TZ(ifnull((SELECT max(men.cht_men_dhentrada) FROM cht_conversa_mensagem men WHERE men.cht_con_id = con.cht_con_id),con.cht_con_dhcriacao),'+00:00','-3:00') dtmensagem, 
					(SELECT IFNULL(cht_cmc_qntd,0) cht_cmc_qntd FROM cht_conversa_mensagem_count cmc WHERE cmc.cht_usu_id = {$datas['usuario1']}   and cmc.cht_con_id  = con.cht_con_id) cht_cmc_qntd
				FROM
				    cht_conversa con
				INNER JOIN cht_grupo_usuario gus1 ON gus1.cht_con_id = con.cht_con_id
				INNER JOIN cht_usuarios usu2 ON usu2.cht_usu_id = gus1.cht_usu_id
				WHERE 1=1
						AND IFNULL(con.cht_con_status,'A') <> 'I'
						and gus1.cht_usu_id != {$datas['usuario1']}
						and usu2.cht_usu_status != 'I'
						AND con.cht_con_tipo = 'T'
				";

		if(!empty($datas['usuario1'])){
		$sql .= " AND (
						con.cht_usu_id_1 = {$datas['usuario1']}
						OR  con.cht_usu_id_2 = {$datas['usuario1']}
						OR con.cht_con_id IN	(SELECT cht_con_id
													FROM cht_grupo_usuario gr
													WHERE gr.cht_usu_id =  {$datas['usuario1']}  )
					)";
		}
		if(!empty($datas['contato'])){
		$sql .= " AND ((usu2.cht_usu_nome LIKE '%{$datas['contato']}%' AND con.cht_con_tipo != 'G')
				OR con.cht_con_nome like '%{$datas['contato']}%'
				OR con.cht_con_descricao like '%{$datas['contato']}%'
				)";
		}
		if(!empty($datas['conversa'])){
			$sql .=" AND con.cht_con_id = {$datas['conversa']}";
		}
		$sql.="
		UNION ALL
			SELECT
			    con.cht_con_id,
			    con.cht_con_nome,
			    con.cht_con_imagem,
			    con.cht_con_descricao,
			    con.cht_con_status,
			    con.cht_con_tipo,
			    con.cht_usu_id_1,
			    con.cht_usu_id_2,
			    0 cht_usu_id,
			    '' cht_usu_nome,
			    '' cht_usu_imagem,
			    '' img_outro,
			    '' nome_outro,
			    0 as id_outro,
			    '' as status_outro,
			    '' as online,
			    '' cht_usu_status,
			    '' cht_usu_online,
			    CONVERT_TZ(ifnull((SELECT max(men.cht_men_dhentrada) FROM cht_conversa_mensagem men WHERE men.cht_con_id = con.cht_con_id),con.cht_con_dhcriacao),'+00:00','-3:00') dtmensagem, 
				(SELECT IFNULL(cht_cmc_qntd,0) cht_cmc_qntd FROM cht_conversa_mensagem_count cmc WHERE cmc.cht_usu_id = {$datas['usuario1']}   and cmc.cht_con_id  = con.cht_con_id) cht_cmc_qntd
			FROM
			    cht_conversa con

			WHERE
			    1 = 1
			        AND IFNULL(con.cht_con_status, 'A') <> 'I'
			      and con.cht_con_id IN (   SELECT gus.cht_con_id  from cht_grupo_usuario gus
			        INNER JOIN cht_usuarios usu1 ON usu1.cht_usu_id = gus.cht_usu_id AND usu1.cht_usu_status != 'I'
			        where  gus.cht_usu_id = {$datas['usuario1']} )
			     AND con.cht_con_tipo = 'G' ";
			if(!empty($datas['conversa'])){
			$sql .=" AND con.cht_con_id = {$datas['conversa']}";
		}
			if(!empty($datas['contato'])){
					$sql.= " AND (con.cht_con_nome LIKE '%{$datas['contato']}%')";
					}
$sql.="
		 UNION ALL SELECT  0 cht_con_id,
							'' cht_con_nome,
							'' cht_con_imagem,
						'' cht_con_descricao,
						'' cht_con_status,
						'T' cht_con_tipo ,
							0 cht_usu_id_1,
							0 cht_usu_id_2,
							usu.cht_usu_id,
							usu.cht_usu_nome ,
							usu.cht_usu_imagem,
					''  img_outro,
						''  nome_outro,
						0 id_outro,
						'' status_outro,
						usu.cht_usu_online online,
						usu.cht_usu_status,
						usu.cht_usu_online,
						'1969-01-01 00:00:00' dtmensagem,
						0 cht_cmc_qntd
					FROM cht_usuarios usu
					WHERE usu.cht_usu_id != {$datas['usuario1']}
					AND usu.cht_usu_id not in (
							SELECT distinct b.cht_usu_id from cht_grupo_usuario b where b.cht_con_id in (
							SELECT a.cht_con_id FROM cht_grupo_usuario a where a.cht_usu_id = {$datas['usuario1']} )
					  )
					AND usu.cht_usu_status != 'I'

				";
				if(!empty($datas['contato'])){
					$sql.= " AND (usu.cht_usu_nome LIKE '%{$datas['contato']}%')";
					}
			$sql.= " ORDER BY dtmensagem DESC ";
	//	echo $sql;
		$query = $this->db->query($sql);
		return $query->result();
	}

	function getConversaMensagens($datas)
	{
		$sql = "SELECT
				men.*,
				usu.cht_usu_email,
				usu.cht_usu_id,
				usu.cht_usu_imagem,
				usu.cht_usu_login,
				usu.cht_usu_nome,
				usu.cht_usu_online,
				usu.cht_usu_perfil,
				usu.cht_usu_status,
				DATE_FORMAT(men.cht_men_dhentrada,'%Y-%m-%d %H:%i:%s') dhapresentacao,
				(CASE WHEN con.cht_con_tipo = 'G' THEN   (SELECT gru.cht_gru_funcao FROM cht_grupo_usuario gru WHERE gru.cht_con_id  =con.cht_con_id  AND gru.cht_usu_id = usu.cht_usu_id  ) ELSE 'T' END) funcao
				FROM  cht_conversa con
				INNER JOIN cht_conversa_mensagem men ON men.cht_con_id = con.cht_con_id
				INNER JOIN cht_usuarios usu ON men.cht_usu_id = usu.cht_usu_id
				WHERE con.cht_con_id = {$datas['conversa']}
				";
		if(!empty($datas['mensagem'])) {
			$sql .=" AND men.cht_men_texto LIKE '%{$datas['mensagem']}%'";
		}
		if(!empty($datas['mensagem_limit'])){
			$sql .=" AND DATE_FORMAT(men.cht_men_dhentrada,'%Y-%m-%d') BETWEEN (SELECT max(DATE_SUB(m2.cht_men_dhentrada, INTERVAL {$_ENV['TEMPOMENSAGEM']} DAY) ) FROM cht_conversa_mensagem m2 WHERE m2.cht_con_id = men.cht_con_id )    AND  (SELECT DATE_FORMAT(max(m2.cht_men_dhentrada),'%Y-%m-%d') FROM cht_conversa_mensagem m2 WHERE m2.cht_con_id = men.cht_con_id ) ";
		}
		$sql .= " ORDER BY men.cht_men_dhentrada  ";
		//echo $sql;
		$query = $this->db->query($sql);
		return $query->result();
	}

	function getTotalMensagensConversa($datas)
	{
		$sql = "SELECT
					round(count(men.cht_men_uuid)/10) total
				FROM  cht_conversa con
				INNER JOIN cht_conversa_mensagem men ON men.cht_con_id = con.cht_con_id
				INNER JOIN cht_usuarios usu ON men.cht_usu_id = usu.cht_usu_id
				WHERE con.cht_con_id = {$datas['conversa']}
				";
		if(!empty($datas['mensagem'])) {
			$sql .=" AND men.cht_men_texto LIKE '%{$datas['mensagem']}%'";
		}
		if(!empty($datas['mensagem_limit'])){
			$sql .=" AND DATE_FORMAT(men.cht_men_dhentrada,'%Y-%m-%d') < (SELECT DATE_FORMAT(max(m2.cht_men_dhentrada),'%Y-%m-%d') FROM cht_conversa_mensagem m2 WHERE m2.cht_con_id = men.cht_con_id )";
		}
		$query = $this->db->query($sql);
		return $query->result();
	}

	function getConversaMensagensMore($datas)
	{
		$sql = "SELECT * FROM( SELECT
				men.cht_men_dhentrada,
				men.cht_men_uuid,
				men.cht_men_texto,
				men.cht_men_id,
				men.cht_men_lido,
				men.cht_men_tipo,
				usu.cht_usu_email,
				usu.cht_usu_id,
				usu.cht_usu_imagem,
				usu.cht_usu_login,
				usu.cht_usu_nome,
				usu.cht_usu_online,
				usu.cht_usu_perfil,
				usu.cht_usu_status,
				DATE_FORMAT(men.cht_men_dhentrada,'%Y-%m-%d %H:%i:%s') dhapresentacao,
				(CASE WHEN con.cht_con_tipo = 'G' THEN   (SELECT gru.cht_gru_funcao FROM cht_grupo_usuario gru WHERE gru.cht_con_id  =con.cht_con_id  AND gru.cht_usu_id = usu.cht_usu_id  ) ELSE 'T' END) funcao
				FROM  cht_conversa con
				INNER JOIN cht_conversa_mensagem men ON men.cht_con_id = con.cht_con_id
				INNER JOIN cht_usuarios usu ON men.cht_usu_id = usu.cht_usu_id
				WHERE con.cht_con_id = {$datas['conversa']}
				";
		$sql .= " AND DATE_FORMAT(men.cht_men_dhentrada,'%Y-%m-%d') < (SELECT DATE_FORMAT(max(m2.cht_men_dhentrada),'%Y-%m-%d') FROM cht_conversa_mensagem m2 WHERE m2.cht_con_id = men.cht_con_id )";
		$sql .= " ORDER BY cht_men_dhentrada desc ,cht_men_id DESC ";
		$sql .= " LIMIT {$datas['limite']},10 ) messs
		ORDER BY messs.cht_men_dhentrada desc ,messs.cht_men_id desc
		";
		//echo $sql;
		$query = $this->db->query($sql);
		return $query->result();
	}

	function getMensagensRecebidas($datas)
	{
		$sql = "SELECT
					COUNT(men.cht_men_id) totmensagem,
				con.cht_con_id
				FROM  cht_conversa con
				INNER JOIN cht_conversa_mensagem men ON men.cht_con_id = con.cht_con_id
				WHERE men.cht_men_lido is null  AND men.cht_usu_id != {$datas['usuario1']}   AND  (con.cht_usu_id_1 = {$datas['usuario1']}
				OR  con.cht_usu_id_2 = {$datas['usuario1']}
				OR con.cht_con_id IN	(SELECT cht_con_id
												FROM cht_grupo_usuario gr
											WHERE gr.cht_usu_id =  {$datas['usuario1']}  )
				)
				GROUP BY con.cht_con_id
				";
		$query = $this->db->query($sql);
		return $query->result();
	}

	function setConversaMensagens($datas)
	{

		$this->db->insert('cht_conversa_mensagem', $datas);
		return $this->db->insert_id();
	}

	function atulizamensagem($data)
	{
		$sql = "UPDATE cht_conversa_mensagem
				   SET cht_men_lido = 'S'
				WHERE cht_con_id = {$data['cht_con_id']}
				AND cht_usu_id != {$data['cht_usu_id']}

		";
		$query = $this->db->query($sql);
	}

	function consultar($data){
			$sql = "SELECT con.cht_con_tipo,
						   con.cht_con_nome,
						   usu.cht_usu_nome,
						   usu.cht_usu_id,
						   men.cht_men_dhentrada,
						   men.cht_men_texto,
						   men.cht_men_id,
						   DATE_FORMAT(men.cht_men_dhentrada,'%d/%m/%Y %H:%i:%s') dhapresentacao
					FROM `cht_conversa` con
			INNER JOIN cht_conversa_mensagem men ON men.cht_con_id = con.cht_con_id
			INNER JOIN cht_usuarios usu ON usu.cht_usu_id  = men.cht_usu_id
			WHERE 1=1 ";
			if(!empty($data['mensagem'])){
				$sql.=" AND men.cht_men_texto LIKE '%{$data['mensagem']}%' " ;
			}
			if(!empty($data['datainicial'])){
				$sql.=" and men.`cht_men_dhentrada` >= '{$data['datainicial']}' " ;
			}
			if(!empty($data['datafinal'])){
				$sql.=" and men.`cht_men_dhentrada` <= '{$data['datafinal']}' " ;
			}
			if(!empty($data['usuario']) && is_int($data['usuario'])){
				$sql.=" AND usu.cht_usu_id = {$data['usuario']}" ;
			}
			if(!empty($data['limit'])){
				$sql.=" limit {$data['limit']}, 5";
			}
			$query = $this->db->query($sql);
			return $query->result();
	}

	function conversaapresenta($data)
	{
		$sql = "SELECT * FROM cht_conversa_mensagem men
		INNER JOIN cht_conversa con ON con.cht_con_id = men.cht_con_id
		INNER JOIN cht_usuarios usu ON usu.cht_usu_id = men.cht_usu_id
		WHERE men.cht_men_id = {$data['mensagem']}
		 ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	function conversaapresentaproximas($data)
	{
		$sql = "SELECT * FROM cht_conversa con
		INNER JOIN cht_conversa_mensagem men  ON con.cht_con_id = men.cht_con_id
		INNER JOIN cht_usuarios usu ON usu.cht_usu_id = men.cht_usu_id
		WHERE con.cht_con_id IN ( SELECT men1.cht_con_id FROM cht_conversa_mensagem men1  WHERE  men1.cht_men_id = {$data['mensagem']}   )
		AND men.cht_men_id >{$data['mensagem']}
		limit {$data['pagina']},3
		 ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	function conversaapresentaanteriores($data)
	{
		$sql = "SELECT * FROM cht_conversa con
				INNER JOIN cht_conversa_mensagem men  ON con.cht_con_id = men.cht_con_id
				INNER JOIN cht_usuarios usu ON usu.cht_usu_id = men.cht_usu_id
				WHERE con.cht_con_id IN ( SELECT men1.cht_con_id FROM cht_conversa_mensagem men1  WHERE  men1.cht_men_id = {$data['mensagem']}    )
				AND men.cht_men_id < {$data['mensagem']}
				limit {$data['pagina']},3
		 ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	function deletarmensagem($uuid)
	{
		$sql = "DELETE FROM cht_conversa_mensagem WHERE cht_men_uuid = '{$uuid}'";
		$query = $this->db->query($sql);
	}

	function getGrupoUsuarioV($usu1, $usu2){
		$sql = "Select DISTINCT g1.cht_con_id from cht_grupo_usuario g1 
		INNER JOIN cht_grupo_usuario g2 ON g1.cht_con_id  = g2.cht_con_id
		INNER JOIN cht_conversa cc ON cc.cht_con_id  = g1.cht_con_id  
		where g1.cht_usu_id  = {$usu2} and g2.cht_usu_id = {$usu1}
		and cc.cht_con_tipo != 'G'"; 
		$query = $this->db->query($sql);
		return $query->result();
	}
}