One Hat Cyber Team

Your IP: 216.73.216.63
Server IP: 217.76.53.76
Server: Linux vmi1397931.contaboserver.net 5.4.0-105-generic #119-Ubuntu SMP Mon Mar 7 18:49:24 UTC 2022 x86_64
Server Software: Apache
PHP Version: 8.2.27
Buat File | Buat Folder

Dir : ~/www/wwwroot/spread.com.ng/core/apps/chat/sql/

View File Name : fetch_chats.sql

SELECT 

	user.`id` as user_id,
	user.`username`,
	CONCAT(user.`fname`,' ',user.`lname`) as name,
	user.`avatar`,
	user.`verified`,
	user.`is_online`,
	chat.`id` as chat_id, 
	chat.`time`, 

	/* Select last message from  chat conversation */

		(SELECT  message.`message`  FROM `<?php echo($data['t_msgs']); ?>` message  WHERE 

			(message.`sent_by` = chat.`user_one` AND message.`sent_to` = chat.`user_two` AND message.`deleted_fs1` = 'N') OR

			(message.`sent_by` = chat.`user_two` AND message.`sent_to` = chat.`user_one` AND message.`deleted_fs2` = 'N')

		ORDER BY message.`id` DESC LIMIT 1) AS last_message,


	/* Select_ unseen messages total from_ user conversation */

		(SELECT  COUNT(m.`id`) FROM `<?php echo($data['t_msgs']); ?>` m WHERE (m.`sent_to` = <?php echo($data['user_id']); ?> AND m.`sent_by` = chat.`user_two`) AND m.`seen` = 0) AS new_messages


FROM `<?php echo($data['t_chats']); ?>` chat INNER JOIN `<?php echo($data['t_users']); ?>` user ON chat.`user_two` = user.`id`

	WHERE chat.`user_one` = <?php echo($data['user_id']); ?>

	AND chat.`user_two` NOT IN (SELECT b1.`profile_id` FROM `<?php echo($data['t_blocks']); ?>` b1 WHERE b1.`user_id` = <?php echo($data['user_id']); ?>)

	AND chat.`user_two` NOT IN (SELECT b2.`user_id` FROM `<?php echo($data['t_blocks']); ?>` b2 WHERE b2.`profile_id` = <?php echo($data['user_id']); ?>)

	<?php if($data['offset']): ?>
		AND chat.`id` < <?php echo($data['offset']); ?>
	<?php endif; ?>

  	ORDER BY chat.`time` DESC 

<?php if($data['limit']): ?>
	LIMIT <?php echo($data['limit']); ?>
<?php endif; ?>