| 1 |
<?php |
| 2 |
/** |
| 3 |
* @package VikBooking |
| 4 |
* @subpackage core |
| 5 |
* @author E4J s.r.l. |
| 6 |
* @copyright Copyright (C) 2021 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 |
$query->where($this->db->qn('m.id_context') . ' = ' . (int) $search->getContext()->getID()); |
| 85 |
} |
| 86 |
|
| 87 |
if ($search->hasAggregate()) { |
| 88 |
$inner = $this->db->getQuery(true); |
| 89 |
|
| 90 |
// create inner query to fetch the latest message for each "thread" |
| 91 |
$inner->select($this->db->qn('t.id_context')); |
| 92 |
$inner->select($this->db->qn('t.context')); |
| 93 |
// $inner->select('MAX(' . $this->db->qn('t.createdon') . ') AS ' . $this->db->qn('maxdate')); |
| 94 |
$inner->select('MAX(' . $this->db->qn('t.id') . ') AS ' . $this->db->qn('id')); |
| 95 |
|
| 96 |
$inner->from($this->db->qn('#__vikbooking_chat_messages', 't')); |
| 97 |
|
| 98 |
$inner->group($this->db->qn('t.id_context')); |
| 99 |
$inner->group($this->db->qn('t.context')); |
| 100 |
|
| 101 |
// aggregate messages by "thread" |
| 102 |
$query->innerjoin( |
| 103 |
'(' . $inner . ') AS ' . $this->db->qn('latest') |
| 104 |
. ' ON ' . $this->db->qn('latest.id_context') . ' = ' . $this->db->qn('m.id_context') |
| 105 |
. ' AND ' . $this->db->qn('latest.context') . ' = ' . $this->db->qn('m.context') |
| 106 |
. ' AND ' . $this->db->qn('latest.id') . ' = ' . $this->db->qn('m.id') |
| 107 |
); |
| 108 |
} |
| 109 |
|
| 110 |
// always use a descending order (from the newest one to the oldest one) |
| 111 |
$query->order($this->db->qn('m.createdon') . ' DESC'); |
| 112 |
$query->order($this->db->qn('m.id') . ' DESC'); |
| 113 |
|
| 114 |
// apply the pagination bounds |
| 115 |
$this->db->setQuery($query, $search->getStart(), $search->getLimit()); |
| 116 |
$messages = $this->db->loadObjectList(); |
| 117 |
|
| 118 |
foreach ($messages as $message) { |
| 119 |
// unserialize attachments for each message |
| 120 |
$message->attachments = (array) @unserialize($message->attachments); |
| 121 |
} |
| 122 |
|
| 123 |
return $messages; |
| 124 |
} |
| 125 |
|
| 126 |
/** |
| 127 |
* @inheritDoc |
| 128 |
*/ |
| 129 |
public function getMessage(int $messageId, VBOChatContext $context) |
| 130 |
{ |
| 131 |
// defines message search |
| 132 |
$search = (new VBOChatSearch) |
| 133 |
->message($messageId) |
| 134 |
->withContext($context) |
| 135 |
->limit(1); |
| 136 |
|
| 137 |
// obtain all the matching messages |
| 138 |
$messages = $this->getMessages($search); |
| 139 |
|
| 140 |
// return the first one available, null in case of no matches |
| 141 |
return array_shift($messages); |
| 142 |
} |
| 143 |
|
| 144 |
/** |
| 145 |
* @inheritDoc |
| 146 |
*/ |
| 147 |
public function saveMessage(VBOChatMessage $message) |
| 148 |
{ |
| 149 |
$isNew = !$message->getID(); |
| 150 |
|
| 151 |
// prepare record to save |
| 152 |
$data = (object) $message->jsonSerialize(); |
| 153 |
$data->attachments = serialize($data->attachments); |
| 154 |
|
| 155 |
// unset read information |
| 156 |
unset($data->read); |
| 157 |
|
| 158 |
if ($isNew) { |
| 159 |
// insert a new message |
| 160 |
$result = $this->db->insertObject('#__vikbooking_chat_messages', $data, 'id'); |
| 161 |
} else { |
| 162 |
// update the existing message |
| 163 |
$result = $this->db->updateObject('#__vikbooking_chat_messages', $data, 'id'); |
| 164 |
} |
| 165 |
|
| 166 |
if (!$result || empty($data->id)) { |
| 167 |
// query failed or message ID empty |
| 168 |
throw new \UnexpectedValueException('Unable to save the specified chat message.', 500); |
| 169 |
} |
| 170 |
|
| 171 |
if ($isNew) { |
| 172 |
// inject ID within the message properties |
| 173 |
$message->bind(['id' => (int) $data->id]); |
| 174 |
|
| 175 |
// iterate all the users that should receive a notification |
| 176 |
foreach ($message->getContext()->getRecipients() as $recipient) { |
| 177 |
if ($message->getSenderID() == $recipient->getID()) { |
| 178 |
// do not notify myself |
| 179 |
continue; |
| 180 |
} |
| 181 |
|
| 182 |
// register unread notification |
| 183 |
$unreadMessage = new stdClass; |
| 184 |
$unreadMessage->id_message = $message->getID(); |
| 185 |
$unreadMessage->id_sender = (int) $recipient->getID(); |
| 186 |
$this->db->insertObject('#__vikbooking_chat_messages_unread', $unreadMessage, 'id'); |
| 187 |
} |
| 188 |
} |
| 189 |
} |
| 190 |
|
| 191 |
/** |
| 192 |
* @inheritDoc |
| 193 |
*/ |
| 194 |
public function readMessage(int $messageId, int $userId = 0) |
| 195 |
{ |
| 196 |
$query = $this->db->getQuery(true); |
| 197 |
|
| 198 |
// delete notification record from the database |
| 199 |
$query->delete($this->db->qn('#__vikbooking_chat_messages_unread')); |
| 200 |
$query->where($this->db->qn('id_message') . ' = ' . $messageId); |
| 201 |
$query->where($this->db->qn('id_sender') . ' = ' . $userId); |
| 202 |
|
| 203 |
$this->db->setQuery($query); |
| 204 |
$this->db->execute(); |
| 205 |
} |
| 206 |
} |
| 207 |
|
