vikbooking / admin / helpers / src / chat / storage / database.php

database.php in VikBooking Hotel Booking Engine & PMS trunk, at admin/helpers/src/chat/storage/database.php

250 lines 8.4 KB

Raw Download Zip

1	<?php
2	/**
3	* @package VikBooking
4	* @subpackage core
5	* @author E4J s.r.l.
6	* @copyright Copyright (C) 2026 E4J s.r.l. All Rights Reserved.
7	* @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
8	* @link https://vikwp.com
9	*/
10
11	// No direct access
12	defined('ABSPATH') or die('No script kiddies please!');
13
14	/**
15	* Chat messages input/output database handler.
16	*
17	* @since 1.8
18	*/
19	class VBOChatStorageDatabase implements VBOChatStorage
20	{
21	/** @var JDatabaseDriver */
22	protected $db;
23
24	/**
25	* Class constructor.
26	*
27	* @param object\|null $db The database driver.
28	*/
29	public function __construct($db = null)
30	{
31	$this->db = $db ?: JFactory::getDbo();
32	}
33
34	/**
35	* @inheritDoc
36	*/
37	public function getMessages(VBOChatSearch $search)
38	{
39	$query = $this->db->getQuery(true);
40
41	$query->select('m.*');
42	$query->from($this->db->qn('#__vikbooking_chat_messages', 'm'));
43
44	if ($search->hasReader()) {
45	// join with the table holding the notifications to read
46	$query->select('IF(' . $this->db->qn('r.id') . ' IS NULL, 1, 0) AS ' . $this->db->qn('read'));
47	$query->leftjoin($this->db->qn('#__vikbooking_chat_messages_unread', 'r')
48	. ' ON ' . $this->db->qn('r.id_message') . ' = ' . $this->db->qn('m.id')
49	. ' AND ' . $this->db->qn('r.id_sender') . ' = ' . (int) $search->getReader());
50	} else {
51	// treat "read" column as unavailable
52	$query->select('NULL AS ' . $this->db->qn('read'));
53	}
54
55	if ($search->hasUnread()) {
56	// take only the unread messages
57	$query->having($this->db->qn('read') . ' = 0');
58	}
59
60	if ($search->hasMessage()) {
61	$msg = $search->getMessage();
62
63	// filter by message ID
64	$query->where($this->db->qn('m.id') . ' ' . $msg->operator . ' ' . (int) $msg->id);
65	}
66
67	if ($search->hasSender()) {
68	$sender = $search->getSender();
69
70	// filter by sender ID
71	$query->where($this->db->qn('m.id_sender') . ' ' . $sender->operator . ' ' . (int) $sender->id);
72	}
73
74	if ($search->hasDate()) {
75	$date = $search->getDate();
76
77	// filter by date
78	$query->where($this->db->qn('m.createdon') . ' ' . $date->operator . ' ' . $this->db->q($date->utc));
79	}
80
81	if ($search->hasContext()) {
82	// filter by context
83	$query->where($this->db->qn('m.context') . ' = ' . $this->db->q($search->getContext()->getAlias()));
84
85	// filter by context ID only if provided
86	if ($search->getContext()->getID() > 0) {
87	$query->where($this->db->qn('m.id_context') . ' = ' . (int) $search->getContext()->getID());
88	}
89	}
90
91	if ($search->hasCategories()) {
92	// filter by supported contexts
93	$query->where($this->db->qn('m.context') . ' IN (' . implode(',', array_map([$this->db, 'q'], $search->getCategories())) . ')');
94	}
95
96	if ($search->hasRefId()) {
97	$refId = $search->getRefId();
98
99	// filter by reference ID
100	$query->where($this->db->qn('m.ref_id') . ' = ' . $this->db->q($refId));
101	}
102
103	if ($search->hasAggregate()) {
104	$inner = $this->db->getQuery(true);
105
106	// create inner query to fetch the latest message for each "thread"
107	$inner->select($this->db->qn('t.id_context'));
108	$inner->select($this->db->qn('t.context'));
109	// $inner->select('MAX(' . $this->db->qn('t.createdon') . ') AS ' . $this->db->qn('maxdate'));
110	$inner->select('MAX(' . $this->db->qn('t.id') . ') AS ' . $this->db->qn('id'));
111
112	$inner->from($this->db->qn('#__vikbooking_chat_messages', 't'));
113
114	$inner->group($this->db->qn('t.id_context'));
115	$inner->group($this->db->qn('t.context'));
116
117	// aggregate messages by "thread"
118	$query->innerjoin(
119	'(' . $inner . ') AS ' . $this->db->qn('latest')
120	. ' ON ' . $this->db->qn('latest.id_context') . ' = ' . $this->db->qn('m.id_context')
121	. ' AND ' . $this->db->qn('latest.context') . ' = ' . $this->db->qn('m.context')
122	. ' AND ' . $this->db->qn('latest.id') . ' = ' . $this->db->qn('m.id')
123	);
124	}
125
126	// always use a descending order (from the newest one to the oldest one)
127	$query->order($this->db->qn('m.createdon') . ' DESC');
128	$query->order($this->db->qn('m.id') . ' DESC');
129
130	// apply the pagination bounds
131	$this->db->setQuery($query, $search->getStart(), $search->getLimit());
132	$messages = $this->db->loadObjectList();
133
134	foreach ($messages as $message) {
135	// unserialize attachments for each message
136	$message->attachments = (array) @unserialize($message->attachments);
137	}
138
139	return $messages;
140	}
141
142	/**
143	* @inheritDoc
144	*/
145	public function getMessage(int $messageId, VBOChatContext $context)
146	{
147	// defines message search
148	$search = (new VBOChatSearch)
149	->message($messageId)
150	->withContext($context)
151	->limit(1);
152
153	// obtain all the matching messages
154	$messages = $this->getMessages($search);
155
156	// return the first one available, null in case of no matches
157	return array_shift($messages);
158	}
159
160	/**
161	* @inheritDoc
162	*/
163	public function saveMessage(VBOChatMessage $message)
164	{
165	$isNew = !$message->getID();
166
167	// prepare record to save
168	$data = (object) $message->jsonSerialize();
169	$data->attachments = serialize($data->attachments);
170
171	// unset read information
172	unset($data->read);
173
174	if ($isNew) {
175	// insert a new message
176	$result = $this->db->insertObject('#__vikbooking_chat_messages', $data, 'id');
177	} else {
178	// update the existing message
179	$result = $this->db->updateObject('#__vikbooking_chat_messages', $data, 'id');
180	}
181
182	if (!$result \|\| empty($data->id)) {
183	// query failed or message ID empty
184	throw new \UnexpectedValueException('Unable to save the specified chat message.', 500);
185	}
186
187	if ($isNew) {
188	// inject ID within the message properties
189	$message->bind(['id' => (int) $data->id]);
190
191	// iterate all the users that should receive a notification
192	foreach ($message->getContext()->getRecipients() as $recipient) {
193	if ($message->getSenderID() == $recipient->getID()) {
194	// do not notify myself
195	continue;
196	}
197
198	if ($recipient->getID() == -2) {
199	// skip also in case the recipient is the AI
200	continue;
201	}
202
203	// register unread notification
204	$unreadMessage = new stdClass;
205	$unreadMessage->id_message = $message->getID();
206	$unreadMessage->id_sender = (int) $recipient->getID();
207	$this->db->insertObject('#__vikbooking_chat_messages_unread', $unreadMessage, 'id');
208	}
209	}
210	}
211
212	/**
213	* @inheritDoc
214	*/
215	public function deleteMessage(VBOChatMessage $message)
216	{
217	// delete message record from the database
218	$query = $this->db->getQuery(true)
219	->delete($this->db->qn('#__vikbooking_chat_messages'))
220	->where($this->db->qn('id') . ' = ' . (int) $message->getID());
221
222	$this->db->setQuery($query);
223	$this->db->execute();
224
225	// delete notification record from the database
226	$query = $this->db->getQuery(true)
227	->delete($this->db->qn('#__vikbooking_chat_messages_unread'))
228	->where($this->db->qn('id_message') . ' = ' . (int) $message->getID());
229
230	$this->db->setQuery($query);
231	$this->db->execute();
232	}
233
234	/**
235	* @inheritDoc
236	*/
237	public function readMessage(int $messageId, int $userId = 0)
238	{
239	$query = $this->db->getQuery(true);
240
241	// delete notification record from the database
242	$query->delete($this->db->qn('#__vikbooking_chat_messages_unread'));
243	$query->where($this->db->qn('id_message') . ' = ' . $messageId);
244	$query->where($this->db->qn('id_sender') . ' = ' . $userId);
245
246	$this->db->setQuery($query);
247	$this->db->execute();
248	}
249	}
250