Issues (3882)

Security Analysis    39 potential vulnerabilities

This project does not seem to handle request data directly as such no vulnerable execution paths were found.

  File Inclusion
File Inclusion enables an attacker to inject custom files into PHP's file loading mechanism, either explicitly passed to include, or for example via PHP's auto-loading mechanism.
  Regex Injection
Regex Injection enables an attacker to execute arbitrary code in your PHP process.
  SQL Injection
SQL Injection enables an attacker to execute arbitrary SQL code on your database server gaining access to user data, or manipulating user data.
  Response Splitting (9)
Response Splitting can be used to send arbitrary responses.
  File Manipulation (2)
File Manipulation enables an attacker to write custom data to files. This potentially leads to injection of arbitrary code on the server.
  Object Injection
Object Injection enables an attacker to inject an object into PHP code, and can lead to arbitrary code execution, file exposure, or file manipulation attacks.
  File Exposure (7)
File Exposure allows an attacker to gain access to local files that he should not be able to access. These files can for example include database credentials, or other configuration files.
  XML Injection
XML Injection enables an attacker to read files on your local filesystem including configuration files, or can be abused to freeze your web-server process.
  Code Injection (13)
Code Injection enables an attacker to execute arbitrary code on the server.
  Variable Injection
Variable Injection enables an attacker to overwrite program variables with custom data, and can lead to further vulnerabilities.
  XPath Injection
XPath Injection enables an attacker to modify the parts of XML document that are read. If that XML document is for example used for authentication, this can lead to further vulnerabilities similar to SQL Injection.
  Other Vulnerability
This category comprises other attack vectors such as manipulating the PHP runtime, loading custom extensions, freezing the runtime, or similar.
  Command Injection
Command Injection enables an attacker to inject a shell command that is execute with the privileges of the web-server. This can be used to expose sensitive data, or gain access of your server.
  LDAP Injection
LDAP Injection enables an attacker to inject LDAP statements potentially granting permission to run unauthorized queries, or modify content inside the LDAP tree.
  Cross-Site Scripting (8)
Cross-Site Scripting enables an attacker to inject code into the response of a web-request that is viewed by other users. It can for example be used to bypass access controls, or even to take over other users' accounts.
  Header Injection
Unfortunately, the security analysis is currently not available for your project. If you are a non-commercial open-source project, please contact support to gain access.

app/Chat.php (2 issues)

1
<?php
2
3
/**
4
 * Chat file.
5
 *
6
 * @package App
7
 *
8
 * @copyright YetiForce S.A.
9
 * @license   YetiForce Public License 6.5 (licenses/LicenseEN.txt or yetiforce.com)
10
 * @author    Arkadiusz Adach <[email protected]>
11
 * @author    Tomasz Poradzewski <[email protected]>
12
 * @author    RadosÅ‚aw Skrzypczak <[email protected]>
13
 */
14
15
namespace App;
16
17
/**
18
 * Chat class.
19
 */
20
final class Chat
21
{
22
	/**
23
	 * Information about allowed types of rooms.
24
	 */
25
	const ALLOWED_ROOM_TYPES = ['crm', 'group', 'global', 'private', 'user'];
26
27
	/**
28
	 * Information about the tables of the database.
29
	 */
30
	const TABLE_NAME = [
31
		'message' => [
32
			'crm' => 'u_#__chat_messages_crm',
33
			'group' => 'u_#__chat_messages_group',
34
			'global' => 'u_#__chat_messages_global',
35
			'private' => 'u_#__chat_messages_private',
36
			'user' => 'u_#__chat_messages_user',
37
		],
38
		'room' => [
39
			'crm' => 'u_#__chat_rooms_crm',
40
			'group' => 'u_#__chat_rooms_group',
41
			'global' => 'u_#__chat_rooms_global',
42
			'private' => 'u_#__chat_rooms_private',
43
			'user' => 'u_#__chat_rooms_user',
44
		],
45
		'room_name' => [
46
			'crm' => 'u_#__crmentity_label',
47
			'group' => 'vtiger_groups',
48
			'global' => 'u_#__chat_global',
49
			'private' => 'u_#__chat_private',
50
			'user' => 'u_#__chat_user',
51
		],
52
		'users' => 'vtiger_users',
53
	];
54
55
	/**
56
	 * Information about the columns of the database.
57
	 */
58
	const COLUMN_NAME = [
59
		'message' => [
60
			'crm' => 'crmid',
61
			'group' => 'groupid',
62
			'global' => 'globalid',
63
			'private' => 'privateid',
64
			'user' => 'roomid',
65
		],
66
		'room' => [
67
			'crm' => 'crmid',
68
			'group' => 'groupid',
69
			'global' => 'global_room_id',
70
			'private' => 'private_room_id',
71
			'user' => 'roomid',
72
		],
73
		'room_name' => [
74
			'crm' => 'label',
75
			'group' => 'groupname',
76
			'global' => 'name',
77
			'private' => 'name',
78
			'user' => 'roomid',
79
		],
80
	];
81
82
	/**
83
	 * Type of chat room.
84
	 *
85 2
	 * @var string
86
	 */
87 2
	public $roomType;
88 2
89
	/**
90 2
	 * ID record associated with the chat room.
91
	 *
92
	 * @var int|null
93
	 */
94
	public $recordId;
95
96
	/**
97
	 * @var array|false
98
	 */
99
	private $room = false;
100
101
	/**
102
	 * User ID.
103
	 *
104
	 * @var int
105
	 */
106
	private $userId;
107
108 6
	/**
109
	 * Last message ID.
110 6
	 *
111 6
	 * @var int|null
112 6
	 */
113 2
	private $lastMessageId = 0;
114 4
115 1
	/**
116 3
	 * Set current room ID, type.
117
	 *
118
	 * @param string   $roomType
119 3
	 * @param int|null $recordId
120
	 *
121 6
	 * @throws \App\Exceptions\IllegalValue
122
	 */
123
	public static function setCurrentRoom(?string $roomType, ?int $recordId)
124
	{
125
		$_SESSION['chat'] = [
126
			'roomType' => $roomType, 'recordId' => $recordId,
127
		];
128
	}
129
130
	/**
131
	 * Set default room as current room.
132
	 *
133
	 * @return array|false
134
	 */
135 1
	public static function setCurrentRoomDefault()
136
	{
137 1
		$defaultRoom = static::getDefaultRoom();
138 1
		static::setCurrentRoom($defaultRoom['roomType'], $defaultRoom['recordId']);
139 1
		return $defaultRoom;
140 1
	}
141 1
142 1
	/**
143
	 * Get current room ID, type.
144 1
	 *
145 1
	 * @return array|false
146 1
	 */
147 1
	public static function getCurrentRoom()
148 1
	{
149
		$recordId = $_SESSION['chat']['recordId'] ?? null;
150
		$roomType = $_SESSION['chat']['roomType'] ?? null;
151
		if (!isset($_SESSION['chat'])) {
152
			$result = static::getDefaultRoom();
153
		} elseif ('crm' === $roomType && (!Record::isExists($recordId) || !\Vtiger_Record_Model::getInstanceById($recordId)->isViewable())) {
154
			$result = static::getDefaultRoom();
155
		} elseif ('group' === $roomType && !isset(User::getCurrentUserModel()->getGroupNames()[$recordId])) {
156
			$result = static::getDefaultRoom();
157
		} else {
158
			$result = $_SESSION['chat'];
159
		}
160
		return $result;
161 10
	}
162
163 10
	/**
164 3
	 * Get active room types.
165 3
	 *
166 3
	 * @return array
167 3
	 */
168
	public static function getActiveRoomTypes(): array
169
	{
170 10
		return (new Db\Query())
171
			->select(['type'])
172
			->from(['u_#__chat_rooms'])
173
			->where(['active' => 1])
174
			->orderBy(['sequence' => SORT_ASC])
175
			->column();
176
	}
177
178 1
	/**
179
	 * Create chat room.
180 1
	 *
181 1
	 * @param string $roomType
182 1
	 * @param int    $recordId
183 1
	 *
184 1
	 * @throws \App\Exceptions\IllegalValue
185 1
	 * @throws \yii\db\Exception
186 1
	 *
187 1
	 * @return \App\Chat
188 1
	 */
189 1
	public static function createRoom(string $roomType, int $recordId)
190 1
	{
191 1
		$instance = new self($roomType, $recordId);
192
		$userId = User::getCurrentUserId();
193 1
		$table = static::TABLE_NAME['room'][$roomType];
194 1
		$recordIdName = static::COLUMN_NAME['room'][$roomType];
195 1
		Db::getInstance()->createCommand()->insert($table, [
196 1
			'userid' => $userId,
197 1
			'last_message' => 0,
198 1
			$recordIdName => $recordId,
199 1
		])->execute();
200 1
		$instance->recordId = $recordId;
201 1
		$instance->roomType = $roomType;
202 1
		return $instance;
203 1
	}
204 1
205
	/**
206 1
	 * Get instance \App\Chat.
207 1
	 *
208
	 * @param string|null $roomType
209
	 * @param int|null    $recordId
210
	 *
211
	 * @throws \App\Exceptions\IllegalValue
212
	 *
213
	 * @return \App\Chat
214
	 */
215
	public static function getInstance(?string $roomType = null, ?int $recordId = null): self
216
	{
217 1
		if (empty($roomType) || null === $recordId) {
218
			$currentRoom = static::getCurrentRoom();
219 1
			if (false !== $currentRoom) {
220
				$roomType = $currentRoom['roomType'];
221
				$recordId = $currentRoom['recordId'];
222 1
			}
223 1
		}
224 1
		return new self($roomType, $recordId);
225 1
	}
226 1
227 1
	/**
228 1
	 * List global chat rooms.
229 1
	 *
230 1
	 * @param int $userId
231 1
	 *
232 1
	 * @return array
233
	 */
234 1
	public static function getRoomsGlobal(?int $userId = null): array
235 1
	{
236 1
		if (empty($userId)) {
237 1
			$userId = User::getCurrentUserId();
238 1
		}
239 1
		$roomIdName = static::COLUMN_NAME['room']['global'];
240 1
		$cntQuery = (new Db\Query())
241
			->select([new \yii\db\Expression('COUNT(*)')])
242
			->from(['CM' => static::TABLE_NAME['message']['global']])
243
			->where([
244 1
				'CM.globalid' => new \yii\db\Expression("CR.{$roomIdName}"),
245 1
			])->andWhere(['>', 'CM.id', new \yii\db\Expression('CR.last_message')]);
246 1
		$subQuery = (new Db\Query())
247
			->select([
248
				'CR.*',
249
				'cnt_new_message' => $cntQuery,
250
			])
251
			->from(['CR' => static::TABLE_NAME['room']['global']]);
252 1
		$query = (new Db\Query())
253 1
			->select(['name', 'recordid' => "GL.{$roomIdName}", 'CNT.last_message', 'CNT.cnt_new_message', 'CNT.userid'])
254
			->from(['GL' => static::TABLE_NAME['room_name']['global']])
255
			->leftJoin(['CNT' => $subQuery], "CNT.{$roomIdName} = GL.{$roomIdName}")
256
			->where(['CNT.userid' => $userId]);
257
		$dataReader = $query->createCommand()->query();
258
		$rooms = [];
259
		while ($row = $dataReader->read()) {
260
			$row['name'] = Language::translate($row['name'], 'Chat');
261
			$row['roomType'] = 'global';
262
			$rooms[$row['recordid']] = $row;
263 1
		}
264
		$dataReader->close();
265 1
		return $rooms;
266
	}
267
268 1
	/**
269 1
	 * List unpinned global chat rooms.
270 1
	 *
271 1
	 * @param int $userId
272 1
	 *
273 1
	 * @return array
274 1
	 */
275 1
	public static function getRoomsGlobalUnpinned(?int $userId = null): array
276 1
	{
277 1
		if (empty($userId)) {
278 1
			$userId = User::getCurrentUserId();
279 1
		}
280 1
		$query = self::getRoomsUnpinnedQuery('global', $userId);
281 1
		$dataReader = $query->createCommand()->query();
282 1
		$rooms = [];
283
		while ($row = $dataReader->read()) {
284
			$row['name'] = Language::translate($row['name'], 'Chat');
285
			$row['roomType'] = 'global';
286
			$rooms[$row['recordid']] = $row;
287
		}
288 1
		$dataReader->close();
289 1
		return $rooms;
290 1
	}
291
292 1
	/**
293 1
	 * List of private chat rooms.
294
	 *
295 1
	 * @param int|null $userId
296 1
	 *
297
	 * @return array
298
	 */
299
	public static function getRoomsPrivate(?int $userId = null): array
300
	{
301
		if (empty($userId)) {
302
			$userId = User::getCurrentUserId();
303
		}
304
		$roomIdName = static::COLUMN_NAME['room']['private'];
305
		$cntQuery = (new Db\Query())
306 1
			->select([new \yii\db\Expression('COUNT(*)')])
307
			->from(['CM' => static::TABLE_NAME['message']['private']])
308 1
			->where([
309
				'CM.privateid' => new \yii\db\Expression('CR.private_room_id'),
310
			])->andWhere(['>', 'CM.id', new \yii\db\Expression('CR.last_message')]);
311 1
		$subQuery = (new Db\Query())
312 1
			->select([
313 1
				'CR.*',
314 1
				'cnt_new_message' => $cntQuery,
315 1
			])
316 1
			->from(['CR' => static::TABLE_NAME['room']['private']]);
317 1
		$query = (new Db\Query())
318 1
			->select(['name', 'recordid' => 'GL.private_room_id', 'CNT.last_message', 'CNT.cnt_new_message', 'CNT.userid', 'creatorid', 'created'])
319 1
			->where(['archived' => 0])
320 1
			->from(['GL' => static::TABLE_NAME['room_name']['private']])
321 1
			->rightJoin(['CNT' => $subQuery], "CNT.{$roomIdName} = GL.private_room_id AND CNT.userid = {$userId}");
322 1
		$dataReader = $query->createCommand()->query();
323 1
		$rooms = [];
324 1
		while ($row = $dataReader->read()) {
325 1
			$row['name'] = \App\Purifier::decodeHtml($row['name']);
326 1
			$row['roomType'] = 'private';
327 1
			$rooms[$row['recordid']] = $row;
328 1
		}
329 1
		$dataReader->close();
330 1
		return $rooms;
331 1
	}
332
333
	/**
334 1
	 * List of unpinned private chat rooms.
335 1
	 *
336
	 * @param int|null $userId
337
	 *
338
	 * @return array
339
	 */
340
	public static function getRoomsPrivateUnpinned(?int $userId = null): array
341
	{
342
		if (empty($userId)) {
343
			$userId = User::getCurrentUserId();
344
		}
345
		$query = self::getRoomsUnpinnedQuery('private', $userId);
346 2
		$query->andWhere(['ROOM_SRC.archived' => 0]);
347
		if (!User::getUserModel($userId)->isAdmin()) {
348 2
			$query->andWhere(['creatorid' => $userId]);
349 2
		}
350 2
		$dataReader = $query->createCommand()->query();
351 2
		$rooms = [];
352 2
		while ($row = $dataReader->read()) {
353
			$row['name'] = Language::translate($row['name'], 'Chat');
354
			$row['roomType'] = 'private';
355
			$rooms[$row['recordid']] = $row;
356
		}
357
		$dataReader->close();
358
		return $rooms;
359
	}
360
361
	/**
362 1
	 * List of unpinned users to private room.
363
	 *
364 1
	 * @param int $roomId
365 1
	 *
366
	 * @return array
367 1
	 */
368
	public static function getRoomPrivateUnpinnedUsers(int $roomId): array
369
	{
370
		$userId = User::getCurrentUserId();
371 1
		$roomType = 'private';
372 1
		$pinnedUsersQuery = (new Db\Query())
373 1
			->select(['USER_PINNED.userid'])
374 1
			->from(['USER_PINNED' => static::TABLE_NAME['room'][$roomType]])
375
			->where(['USER_PINNED.private_room_id' => $roomId]);
376 1
		$query = (new Db\Query())
377 1
			->select(['USERS.id', 'USERS.first_name', 'USERS.last_name'])
378
			->from(['USERS' => static::TABLE_NAME['users']])
379
			->where(['and', ['USERS.status' => 'Active'], ['USERS.deleted' => 0], ['not', ['USERS.id' => $userId]]])
380
			->leftJoin(['PINNED' => $pinnedUsersQuery], 'USERS.id = PINNED.userid')
381
			->andWhere(['PINNED.userid' => null]);
382
		$dataReader = $query->createCommand()->query();
383
		$rows = [];
384
		while ($row = $dataReader->read()) {
385
			$row['img'] = User::getImageById($row['id']) ? User::getImageById($row['id'])['url'] : '';
386
			$row['label'] = $row['last_name'] . ' ' . $row['first_name'];
387
			$rows[] = $row;
388
		}
389
		$dataReader->close();
390
		return $rows;
391
	}
392
393
	/**
394
	 * List of chat room groups.
395
	 *
396
	 * @param int|null $userId
397
	 *
398
	 * @return array
399
	 */
400
	public static function getRoomsGroup(?int $userId = null): array
401
	{
402
		if (empty($userId)) {
403
			$userId = User::getCurrentUserId();
404
		}
405
		$subQuery = (new Db\Query())
406
			->select(['CR.groupid', 'CR.userid', 'cnt_new_message' => 'COUNT(*)'])
407
			->from(['CR' => static::TABLE_NAME['room']['group']])
408
			->innerJoin(['CM' => static::TABLE_NAME['message']['group']], 'CM.groupid = CR.groupid')
409
			->where(['>', 'CM.id', new \yii\db\Expression('CR.last_message')])
410 5
			->groupBy(['CR.groupid', 'CR.userid']);
411
		$query = (new Db\Query())
412 5
			->select(['GR.roomid', 'GR.last_message', 'GR.userid', 'recordid' => 'GR.groupid', 'name' => 'VGR.groupname', 'CNT.cnt_new_message'])
413 5
			->from(['GR' => static::TABLE_NAME['room']['group']])
414 5
			->leftJoin(['CNT' => $subQuery], 'CNT.groupid = GR.groupid AND CNT.userid = GR.userid')
415 5
			->where(['GR.userid' => $userId]);
416 5
		$joinArguments = [['VGR' => static::TABLE_NAME['room_name']['group']], 'VGR.groupid = GR.groupid'];
417 5
		$query->rightJoin($joinArguments[0], $joinArguments[1]);
418
		$dataReader = $query->createCommand()->query();
419 1
		$rows = [];
420
		while ($row = $dataReader->read()) {
421
			$row['name'] = \App\Purifier::decodeHtml($row['name']);
422 5
			$row['roomType'] = 'group';
423 5
			$rows[$row['recordid']] = $row;
424 5
		}
425 5
		$dataReader->close();
426
		return $rows;
427
	}
428
429
	/**
430
	 * Get rooms group unpinned.
431
	 *
432
	 * @param int|null $userId
433
	 *
434
	 * @return array
435
	 */
436 1
	public static function getRoomsGroupUnpinned(?int $userId = null): array
437
	{
438 1
		if (empty($userId)) {
439 1
			$userId = User::getCurrentUserId();
440 1
		}
441 1
		$groups = User::getUserModel($userId)->getGroupNames();
442 1
		$pinned = [];
443 1
		$rows = [];
444 1
		$query = (new Db\Query())
445 1
			->select(['recordid' => 'ROOM_PINNED.groupid'])
446 1
			->from(['ROOM_PINNED' => static::TABLE_NAME['room']['group']])
447 1
			->where(['ROOM_PINNED.userid' => $userId]);
448 1
		$dataReader = $query->createCommand()->query();
449 1
		while ($row = $dataReader->read()) {
450 1
			$pinned[] = $row['recordid'];
451 1
		}
452
		$dataReader->close();
453
		foreach ($groups as $id => $groupName) {
454
			if (!\in_array($id, $pinned)) {
455
				$rows[$id] = [
456
					'recordid' => $id,
457
					'name' => $groupName,
458
					'roomType' => 'group',
459
				];
460
			}
461 1
		}
462
		return $rows;
463 1
	}
464 1
465 1
	/**
466 1
	 * Get rooms user unpinned.
467 1
	 *
468 1
	 * @param int|null $userId
469 1
	 *
470 1
	 * @return array
471 1
	 */
472 1
	public static function getRoomsUser(?int $userId = null): array
473 1
	{
474 1
		if (empty($userId)) {
475 1
			$userId = User::getCurrentUserId();
476 1
		}
477
		$roomType = 'user';
478
		$cntQuery = (new Db\Query())
479
			->select([new \yii\db\Expression('COUNT(*)')])
480
			->from(['MESSAGES' => static::TABLE_NAME['message'][$roomType]])
481
			->where([
482
				'MESSAGES.roomid' => new \yii\db\Expression('ROOM_PINNED.roomid'),
483
			])->andWhere(['>', 'MESSAGES.id', new \yii\db\Expression('ROOM_PINNED.last_message')]);
484
		$query = (new Db\Query())
485
			->select(['ROOM_PINNED.last_message', 'ROOM_SRC.userid', 'ROOM_SRC.reluserid', 'recordid' => 'ROOM_SRC.roomid', 'cnt_new_message' => $cntQuery])
486 2
			->from(['ROOM_PINNED' => static::TABLE_NAME['room'][$roomType]])
487
			->where(['ROOM_PINNED.userid' => $userId])
488 2
			->andWhere(['or', ['ROOM_SRC.reluserid' => $userId], ['ROOM_SRC.userid' => $userId]])
489 2
			->leftJoin(['ROOM_SRC' => static::TABLE_NAME['room_name'][$roomType]], 'ROOM_PINNED.roomid = ROOM_SRC.roomid')
490 2
			->leftJoin(['USERS' => 'vtiger_users'], 'USERS.id = ROOM_SRC.userid')
491 2
			->leftJoin(['USERS_REL' => 'vtiger_users'], 'USERS_REL.id = ROOM_SRC.reluserid')
492 2
			->andWhere(['and', ['not', ['USERS.id' => null]], ['USERS.status' => 'Active'], ['not', ['USERS_REL.id' => null]], ['USERS_REL.status' => 'Active']]);
493 2
		$dataReader = $query->createCommand()->query();
494 2
		$rooms = [];
495 2
		while ($row = $dataReader->read()) {
496 2
			$relUser = $row['userid'] === $userId ? $row['reluserid'] : $row['userid'];
497 2
			$roomData = static::getUserInfo($relUser);
498 2
			$roomData['cnt_new_message'] = $row['cnt_new_message'];
499 2
			$roomData['last_message'] = $row['last_message'];
500 2
			$roomData['recordid'] = $row['recordid'];
501
			$roomData['name'] = \App\Purifier::decodeHtml($roomData['user_name']);
502
			$roomData['roomType'] = $roomType;
503
			$rooms[$row['recordid']] = $roomData;
504
		}
505
		$dataReader->close();
506
		return $rooms;
507
	}
508
509
	/**
510 1
	 * Get rooms user unpinned.
511
	 *
512 1
	 * @param int|null $userId
513 1
	 *
514 1
	 * @return array
515 1
	 */
516 1
	public static function getRoomsUserUnpinned(?int $userId = null): array
517 1
	{
518 1
		$rooms = [];
519 1
		$dataReader = static::getRoomsUserUnpinnedQuery($userId)->createCommand()->query();
520 1
		while ($row = $dataReader->read()) {
521 1
			$row['name'] = $row['first_name'] . ' ' . $row['last_name'];
522 1
			$row['roomType'] = 'user';
523 1
			$row['recordid'] = $row['id'];
524 1
			$rooms[$row['id']] = $row;
525
		}
526
		$dataReader->close();
527
		return $rooms;
528
	}
529
530
	/**
531
	 * Get rooms user unpinned query.
532 1
	 *
533
	 * @param int|null $userId
534 1
	 *
535 1
	 * @return object
536 1
	 */
537 1
	public static function getRoomsUserUnpinnedQuery(?int $userId = null): object
538 1
	{
539
		if (empty($userId)) {
540
			$userId = User::getCurrentUserId();
541
		}
542
		$roomType = 'user';
543
		$pinnedUsersQuery = (new Db\Query())
544
			->select(['ROOM_USER.userid', 'ROOM_USER.reluserid'])
545
			->from(['ROOM_PINNED' => static::TABLE_NAME['room'][$roomType]])
546
			->where(['ROOM_PINNED.userid' => $userId])
547
			->leftJoin(['ROOM_USER' => static::TABLE_NAME['room_name'][$roomType]], 'ROOM_PINNED.roomid = ROOM_USER.roomid');
548
		return (new Db\Query())
549 11
			->select(['USERS.id', 'USERS.user_name', 'USERS.first_name', 'USERS.last_name'])
550
			->from(['USERS' => static::TABLE_NAME['users']])
551 11
			->where(['and', ['USERS.status' => 'Active'], ['USERS.deleted' => 0], ['not', ['USERS.id' => $userId]]])
552 11
			->leftJoin(['PINNED' => $pinnedUsersQuery], 'USERS.id = PINNED.userid OR USERS.id = PINNED.reluserid')
553
			->andWhere(['and', ['PINNED.userid' => null], ['PINNED.reluserid' => null]]);
554
	}
555 11
556 11
	/**
557 11
	 * CRM list of chat rooms.
558 11
	 *
559 4
	 * @param int|null $userId
560 4
	 *
561
	 * @return array
562 4
	 */
563
	public static function getRoomsCrm(?int $userId = null): array
564
	{
565
		if (empty($userId)) {
566 11
			$userId = User::getCurrentUserId();
567
		}
568
		$subQuery = (new Db\Query())
569
			->select(['CR.crmid', 'CR.userid', 'cnt_new_message' => 'COUNT(*)'])
570
			->from(['CR' => static::TABLE_NAME['room']['crm']])
571
			->innerJoin(['CM' => static::TABLE_NAME['message']['crm']], 'CM.crmid = CR.crmid')
572
			->where(['>', 'CM.id', new \yii\db\Expression('CR.last_message')])
573 8
			->orWhere(['CR.last_message' => null])
574
			->groupBy(['CR.crmid', 'CR.userid']);
575 8
		$dataReader = (new Db\Query())
576
			->select(['C.roomid', 'C.userid', 'recordid' => 'C.crmid', 'name' => 'CL.label', 'C.last_message', 'CNT.cnt_new_message'])
577
			->from(['C' => static::TABLE_NAME['room']['crm']])
578
			->leftJoin(['CL' => static::TABLE_NAME['room_name']['crm']], 'CL.crmid = C.crmid')
579
			->leftJoin(['CNT' => $subQuery], 'CNT.crmid = C.crmid AND CNT.userid = C.userid')
580
			->where(['C.userid' => $userId])->createCommand()->query();
581
		$rows = [];
582
		while ($row = $dataReader->read()) {
583 2
			$recordModel = \Vtiger_Record_Model::getInstanceById($row['recordid']);
584
			if ($recordModel->isViewable()) {
585 2
				$row['moduleName'] = $recordModel->getModuleName();
586
				$row['roomType'] = 'crm';
587
				$row['name'] = \App\Purifier::decodeHtml($row['name']);
588
				$rows[$row['recordid']] = $row;
589
			}
590
		}
591
		$dataReader->close();
592
		return $rows;
593 9
	}
594
595 9
	/**
596
	 * Create query for unpinned rooms.
597
	 *
598
	 * @param string $roomType
599
	 * @param int    $userId
600
	 *
601
	 * @return object
602
	 */
603 7
	public static function getRoomsUnpinnedQuery(string $roomType, int $userId): object
604
	{
605 7
		$roomIdName = static::COLUMN_NAME['room'][$roomType];
606
		return (object) (new Db\Query())
607
			->select(['ROOM_SRC.*', 'recordid' => "ROOM_SRC.{$roomIdName}"])
608
			->from(['ROOM_SRC' => static::TABLE_NAME['room_name'][$roomType]])
609
			->leftJoin(['ROOM_PINNED' => static::TABLE_NAME['room'][$roomType]], "ROOM_PINNED.{$roomIdName} = ROOM_SRC.{$roomIdName}")
610
			->where(['or', ['not', ['ROOM_PINNED.userid' => $userId]], ['ROOM_PINNED.userid' => null]]);
611
	}
612
613
	/**
614
	 * Get room last message.
615
	 *
616
	 * @param int    $roomId
617
	 * @param string $roomType
618
	 *
619
	 * @return array
620
	 */
621
	public static function getRoomLastMessage(int $roomId, string $roomType): array
622
	{
623
		return (array) (new Db\Query())
624
			->from(static::TABLE_NAME['message'][$roomType])
625
			->where([static::COLUMN_NAME['message'][$roomType] => $roomId])
626
			->orderBy(['id' => SORT_DESC])
627
			->one();
628
	}
629
630
	/**
631
	 * Get room type last message.
632
	 *
633
	 * @param string $roomType
634
	 *
635
	 * @return array
636
	 */
637
	public static function getRoomTypeLastMessage(string $roomType): array
638
	{
639
		return (array) (new Db\Query())
640
			->from(static::TABLE_NAME['message'][$roomType])
641
			->orderBy(['id' => SORT_DESC])
642
			->one();
643
	}
644
645
	/**
646
	 * Get all chat rooms by user.
647
	 *
648
	 * @param int|null $userId
649
	 *
650
	 * @return array
651
	 */
652
	public static function getRoomsByUser(?int $userId = null)
653
	{
654
		$roomsByUser = [];
655
		if (empty($userId)) {
656
			$userId = User::getCurrentUserId();
657
		}
658
		if (Cache::staticHas('ChatGetRoomsByUser', $userId)) {
659
			return Cache::staticGet('ChatGetRoomsByUser', $userId);
660
		}
661
		foreach (self::getActiveRoomTypes() as $roomType) {
662
			$methodName = 'getRooms' . Utils::mbUcfirst($roomType);
663
			$roomsByUser[$roomType] = static::{$methodName}($userId);
664
		}
665
		Cache::staticSave('ChatGetRoomsByUser', $userId);
666
		return $roomsByUser;
667
	}
668
669
	/**
670 6
	 * Rerun the number of new messages.
671
	 *
672 6
	 * @param array|null $roomInfo
673 6
	 *
674 6
	 * @return array
675 6
	 */
676 6
	public static function getNumberOfNewMessages(?array $roomInfo = null): array
677 6
	{
678 6
		$numberOfNewMessages = 0;
679 6
		$roomList = [];
680 6
		$lastMessagesData = [];
681
		if (empty($roomInfo)) {
682
			$roomInfo = static::getRoomsByUser();
683
		}
684
		foreach (array_keys($roomInfo) as $roomType) {
685
			$lastMessageId = 0;
686
			$lastMessageRoomId = 0;
687
			foreach ($roomInfo[$roomType] as $room) {
688
				if (!empty($room['cnt_new_message'])) {
689
					$numberOfNewMessages += $room['cnt_new_message'];
690
					$roomList[$roomType][$room['recordid']]['cnt_new_message'] = $room['cnt_new_message'];
691
					if ($lastMessageId < $room['last_message'] || 0 === $room['last_message']) {
692
						$lastMessageId = $room['last_message'];
693
						$lastMessageRoomId = $room['recordid'];
694
					}
695
				}
696 5
			}
697
			if (0 !== $lastMessageRoomId) {
698 5
				$roomLastMessage = static::getRoomTypeLastMessage($roomType);
699
				$roomLastMessage['roomData'] = $roomInfo[$roomType][$lastMessageRoomId];
700
				$lastMessagesData[] = $roomLastMessage;
701 5
			}
702 5
		}
703 5
704 5
		$lastMessage = 1 === \count($lastMessagesData) ? current($lastMessagesData) : array_reduce($lastMessagesData, fn ($a, $b) => $a['created'] > $b['created'] ? $a : $b);
705 5
		if (!empty($lastMessage)) {
706 5
			$lastMessage['messages'] = static::decodeNoHtmlMessage($lastMessage['messages'], false);
707
			$lastMessage['userData'] = static::getUserInfo($lastMessage['userid']);
708 5
		}
709 5
		return ['roomList' => $roomList, 'amount' => $numberOfNewMessages, 'lastMessage' => $lastMessage];
710 5
	}
711 5
712 5
	/**
713 5
	 * Get user info.
714 5
	 *
715 5
	 * @param int $userId
716
	 *
717
	 * @throws \App\Exceptions\AppException
718 5
	 *
719 5
	 * @return array
720 5
	 */
721
	public static function getUserInfo(int $userId)
722 5
	{
723
		if (User::isExists($userId)) {
724
			$userModel = User::getUserModel($userId);
725
			$image = $userModel->getImage();
726
			$userName = $userModel->getName();
727
			$isAdmin = $userModel->isAdmin();
728
			$userRoleName = Language::translate($userModel->getRoleInstance()->getName());
729
		} else {
730
			$image = $isAdmin = $userName = $userRoleName = null;
731
		}
732
		return [
733 1
			'user_name' => $userName,
734
			'role_name' => $userRoleName,
735 1
			'isAdmin' => $isAdmin,
736 1
			'image' => $image['url'] ?? null,
737 1
		];
738 1
	}
739 1
740 1
	/**
741 1
	 * Is there any new message for global.
742
	 *
743 1
	 * @param int $userId
744 1
	 *
745 1
	 * @return bool
746 1
	 */
747 1
	public static function isNewMessagesForGlobal(int $userId): bool
748 1
	{
749
		$subQueryGlobal = (new Db\Query())
750
			->select([
751 1
				static::COLUMN_NAME['message']['global'],
752 1
				'id' => new \yii\db\Expression('max(id)'),
753 1
			])->from(static::TABLE_NAME['message']['global'])
754 1
			->groupBy([static::COLUMN_NAME['message']['global']]);
755 1
		return (new Db\Query())
756 1
			->select(['CG.name', 'CM.id'])
757 1
			->from(['CG' => 'u_#__chat_global'])
758 1
			->innerJoin(['CM' => $subQueryGlobal], 'CM.globalid = CG.global_room_id')
759 1
			->leftJoin(['GL' => static::TABLE_NAME['room']['global']], "GL.global_room_id = CG.global_room_id AND GL.userid = {$userId}")
760 1
			->where(['or', ['GL.userid' => null], ['GL.userid' => $userId]])
761
			->andWhere(['or', ['GL.last_message' => null], ['<', 'GL.last_message', new \yii\db\Expression('CM.id')]])
762
			->exists();
763 1
	}
764 1
765
	/**
766
	 * Is there any new message for global.
767 1
	 *
768
	 * @param int $userId
769
	 *
770
	 * @return bool
771
	 */
772 1
	public static function isNewMessagesForPrivate(int $userId): bool
773 1
	{
774
		$subQuery = (new Db\Query())
775 1
			->select([
776 1
				static::COLUMN_NAME['message']['private'],
777 1
				'id' => new \yii\db\Expression('max(id)'),
778 1
			])->from(static::TABLE_NAME['message']['private'])
779 1
			->groupBy([static::COLUMN_NAME['message']['private']]);
780 1
		return (new Db\Query())
781
			->select(['CG.name', 'CM.id'])
782 1
			->from(['CG' => 'u_#__chat_private'])
783
			->innerJoin(['CM' => $subQuery], 'CM.privateid = CG.private_room_id')
784
			->innerJoin(['GL' => static::TABLE_NAME['room']['private']], "GL.private_room_id = CG.private_room_id AND GL.userid = {$userId}")
785
			->where(['or', ['GL.userid' => null], ['GL.userid' => $userId]])
786
			->andWhere(['or', ['GL.last_message' => null], ['<', 'GL.last_message', new \yii\db\Expression('CM.id')]])
787
			->exists();
788
	}
789
790 3
	/**
791
	 * Is there any new message for crm.
792 3
	 *
793 3
	 * @param int $userId
794
	 *
795 1
	 * @return bool
796 1
	 */
797 1
	public static function isNewMessagesForCrm(int $userId): bool
798
	{
799 1
		$subQueryCrm = (new Db\Query())
800 1
			->select([
801
				static::COLUMN_NAME['message']['crm'],
802
				'id' => new \yii\db\Expression('max(id)'),
803 1
			])->from(static::TABLE_NAME['message']['crm'])
804 1
			->groupBy([static::COLUMN_NAME['message']['crm']]);
805
		return (new Db\Query())
806
			->select(['CM.id'])
807
			->from(['C' => static::TABLE_NAME['room']['crm']])
808
			->innerJoin(['CM' => $subQueryCrm], 'CM.crmid = C.crmid')
809
			->where(['C.userid' => $userId])
810
			->andWhere(['or', ['C.last_message' => null], ['<', 'C.last_message', new \yii\db\Expression('CM.id')]])
811
			->exists();
812
	}
813
814
	/**
815
	 * Is there any new message for group.
816
	 *
817
	 * @param int $userId
818
	 *
819
	 * @return bool
820
	 */
821
	public static function isNewMessagesForGroup(int $userId): bool
822
	{
823
		$subQueryGroup = (new Db\Query())
824
			->select([
825
				static::COLUMN_NAME['message']['group'],
826
				'id' => new \yii\db\Expression('max(id)'),
827
			])->from(static::TABLE_NAME['message']['group'])
828
			->groupBy([static::COLUMN_NAME['message']['group']]);
829
		return (new Db\Query())
830
			->select(['CM.id'])
831
			->from(['GR' => static::TABLE_NAME['room']['group']])
832
			->innerJoin(['CM' => $subQueryGroup], 'CM.groupid = GR.groupid')
833
			->where(['GR.userid' => $userId])
834
			->andWhere(['or', ['GR.last_message' => null], ['<', 'GR.last_message', new \yii\db\Expression('CM.id')]])
835
			->exists();
836
	}
837
838
	/**
839
	 * Is there any new message.
840
	 *
841
	 * @return bool
842
	 */
843
	public static function isNewMessages(): bool
844
	{
845
		$userId = User::getCurrentUserId();
846
		return static::isNewMessagesForGlobal($userId)
847
			|| static::isNewMessagesForCrm($userId)
848
			|| static::isNewMessagesForGroup($userId)
849
			|| static::isNewMessagesForPrivate($userId);
850
	}
851
852
	/**
853
	 * Chat constructor.
854
	 *
855
	 * @param string|null $roomType
856
	 * @param int|null    $recordId
857
	 *
858
	 * @throws \App\Exceptions\IllegalValue
859
	 */
860
	public function __construct(?string $roomType, ?int $recordId)
861
	{
862
		$this->userId = User::getCurrentUserId();
863
		if (empty($roomType) || null === $recordId) {
864
			return;
865
		}
866
		$this->roomType = $roomType;
867
		$this->recordId = $recordId;
868
		$this->room = $this->getQueryRoom()->one();
869
		if (('crm' === $this->roomType || 'group' === $this->roomType) && !$this->isRoomExists()) {
870
			$this->room = [
871
				'roomid' => null,
872
				'userid' => null,
873
				'record_id' => $recordId,
874
				'last_message' => null,
875
			];
876
		}
877
	}
878
879
	/**
880
	 * Get room type.
881
	 *
882
	 * @return string|null
883
	 */
884
	public function getRoomType(): ?string
885
	{
886
		return $this->roomType;
887
	}
888
889
	/**
890
	 * Get record ID.
891
	 *
892
	 * @return int|null
893
	 */
894
	public function getRecordId(): ?int
895
	{
896
		return $this->recordId;
897
	}
898
899
	/**
900
	 * Check if chat room exists.
901
	 *
902
	 * @return bool
903
	 */
904
	public function isRoomExists(): bool
905
	{
906
		return false !== $this->room;
907
	}
908
909
	/**
910
	 * Is the user assigned to the chat room.
911
	 *
912
	 * @return bool
913
	 */
914
	public function isAssigned()
915
	{
916
		return !empty($this->room['userid']);
917
	}
918
919
	/**
920
	 * Is private room allowed for specified user.
921
	 *
922
	 * @param int $recordId
923
	 * @param int $userId
924
	 *
925
	 * @return bool
926
	 */
927
	public function isPrivateRoomAllowed(int $recordId, ?int $userId = null): bool
928
	{
929
		if (empty($userId)) {
930
			$userId = $this->userId;
931
		}
932
		return (new Db\Query())
933
			->select(['userid', static::COLUMN_NAME['room']['private']])
934
			->from(static::TABLE_NAME['room']['private'])
935
			->where(['and', ['userid' => $userId], [static::COLUMN_NAME['room']['private'] => $recordId]])
936
			->exists();
937
	}
938
939
	/**
940
	 * Is room moderator.
941
	 *
942
	 * @param int $recordId
943
	 *
944
	 * @return bool
945
	 */
946
	public function isRoomModerator(int $recordId): bool
947
	{
948
		if (User::getUserModel($this->userId)->isAdmin()) {
949
			return true;
950
		}
951
		return (new Db\Query())
952
			->select(['creatorid', static::COLUMN_NAME['room']['private']])
953
			->from(static::TABLE_NAME['room_name']['private'])
954
			->where(['and', ['creatorid' => $this->userId], [static::COLUMN_NAME['room']['private'] => $recordId]])
955
			->exists();
956
	}
957
958
	/**
959
	 * Is record owner.
960
	 *
961 1
	 * @return bool
962
	 */
963 1
	public function isRecordOwner(): bool
964
	{
965
		return (new Db\Query())
966 1
			->select(['userid', static::COLUMN_NAME['room']['private']])
967 1
			->from(static::TABLE_NAME['room']['private'])
968 1
			->where(['and', ['userid' => $this->userId], [static::COLUMN_NAME['room'][$this->getRoomType()] => $this->getRecordId()]])
969 1
			->exists();
970 1
	}
971 1
972 1
	/**
973 1
	 * Add new message to chat room.
974 1
	 *
975 1
	 * @param string $message
976 1
	 *
977 1
	 * @throws \yii\db\Exception
978 1
	 *
979 1
	 * @return int
980 1
	 */
981 1
	public function addMessage(string $message): int
982 1
	{
983 1
		if ('user' === $this->roomType) {
984 1
			$this->pinTargetUserRoom();
985 1
		}
986 1
		$table = static::TABLE_NAME['message'][$this->roomType];
987 1
		$db = Db::getInstance();
988 1
		$db->createCommand()->insert($table, [
989
			'userid' => $this->userId,
990
			'messages' => $message,
991 1
			'created' => date('Y-m-d H:i:s'),
992 1
			static::COLUMN_NAME['message'][$this->roomType] => $this->recordId,
993 1
		])->execute();
994 1
		return $this->lastMessageId = (int) $db->getLastInsertID("{$table}_id_seq");
995
	}
996
997 1
	/**
998 1
	 * Pin target user room when is unpinned.
999
	 *
1000
	 * @throws \yii\db\Exception
1001
	 */
1002
	public function pinTargetUserRoom()
1003
	{
1004
		$roomsTable = static::TABLE_NAME['room'][$this->roomType];
1005
		$roomPinned = (new Db\Query())
1006
			->select(['roomid'])
1007
			->from($roomsTable)
1008 1
			->where(['roomid' => $this->recordId])
1009
			->all();
1010 1
		if (2 !== \count($roomPinned)) {
1011 1
			$roomUsers = (new Db\Query())
1012
				->select(['userid', 'reluserid'])
1013 1
				->from(static::TABLE_NAME['room_name'][$this->roomType])
1014 1
				->where(['roomid' => $this->recordId])
1015 1
				->one();
1016
			$targetUserId = $roomUsers['userid'] === $this->userId ? $roomUsers['reluserid'] : $roomUsers['userid'];
1017 1
			$this->addToFavoritesQuery($targetUserId);
1018 1
		}
1019
	}
1020 1
1021
	/**
1022 1
	 * Get entries function.
1023 1
	 *
1024 1
	 * @param int|null $messageId
1025 1
	 * @param string   $condition
1026
	 * @param ?string  $searchVal
1027
	 *
1028
	 * @throws \App\Exceptions\AppException
1029 1
	 * @throws \App\Exceptions\IllegalValue
1030
	 * @throws \yii\db\Exception
1031
	 *
1032
	 * @return array
1033
	 */
1034
	public function getEntries(?int $messageId = 0, string $condition = '>', ?string $searchVal = null)
1035
	{
1036 2
		if (!$this->isRoomExists()) {
1037
			return [];
1038 2
		}
1039 2
		$this->lastMessageId = $messageId;
1040 2
		$rows = [];
1041 2
		$dataReader = $this->getQueryMessage($messageId, $condition, $searchVal)->createCommand()->query();
1042
		while ($row = $dataReader->read()) {
1043 2
			$row['messages'] = static::decodeMessage($row['messages']);
1044 2
			$row['created'] = Fields\DateTime::formatToShort($row['created']);
1045 2
			[
1046
				'user_name' => $row['user_name'],
1047 2
				'role_name' => $row['role_name'],
1048 2
				'image' => $row['image']
1049
			] = static::getUserInfo($row['userid']);
1050 2
			$rows[] = $row;
1051
			$mid = (int) $row['id'];
1052
			if ($this->lastMessageId < $mid) {
1053
				$this->lastMessageId = $mid;
1054
			}
1055
		}
1056
		$dataReader->close();
1057
		if ('>' === $condition) {
1058
			$this->updateRoom();
1059
		}
1060
		return \array_reverse($rows);
1061
	}
1062
1063
	/**
1064
	 * Get history by type.
1065
	 *
1066
	 * @param string   $roomType
1067
	 * @param int|null $messageId
1068
	 *
1069
	 * @return array
1070
	 */
1071
	public function getHistoryByType(string $roomType = 'global', ?int $messageId = null)
1072
	{
1073
		$columnMessage = static::COLUMN_NAME['message'][$roomType];
1074
		$columnRoomName = static::COLUMN_NAME['room_name'][$roomType];
1075
		$roomNameId = 'global' === $roomType || 'private' === $roomType ? static::COLUMN_NAME['room'][$roomType] : $columnMessage;
1076
		$query = (new Db\Query())
1077
			->select([
1078
				'id', 'messages', 'GL.userid', 'GL.created',
1079
				'recordid' => "GL.{$columnMessage}", 'room_name' => "RN.{$columnRoomName}",
1080
			])
1081
			->from(['GL' => static::TABLE_NAME['message'][$roomType]])
1082
			->leftJoin(['RN' => static::TABLE_NAME['room_name'][$roomType]], "RN.{$roomNameId} = GL.{$columnMessage}")
1083
			->where(['GL.userid' => $this->userId])
1084
			->orderBy(['id' => SORT_DESC])
1085
			->limit(\App\Config::module('Chat', 'CHAT_ROWS_LIMIT') + 1);
1086
		if (null !== $messageId) {
1087
			$query->andWhere(['<', 'id', $messageId]);
1088
		}
1089
		$userModel = User::getUserModel($this->userId);
1090
		$groups = $userModel->getGroupNames();
1091
		$userImage = $userModel->getImage()['url'] ?? null;
1092
		$userName = $userModel->getName();
1093
		$userRoleName = $userModel->getRoleInstance()->getName();
1094
		$rows = [];
1095
		$dataReader = $query->createCommand()->query();
1096
		$notPermittedIds = [];
1097
		while ($row = $dataReader->read()) {
1098
			if ('group' === $roomType && !isset($groups[$row['recordid']])) {
1099
				continue;
1100
			}
1101
			if ('crm' === $roomType) {
1102
				if (\in_array($row['recordid'], $notPermittedIds)) {
1103
					continue;
1104
				}
1105
				if (!Record::isExists($row['recordid']) || !\Vtiger_Record_Model::getInstanceById($row['recordid'])->isViewable()) {
1106
					$notPermittedIds[] = $row['recordid'];
1107
					continue;
1108
				}
1109
			}
1110
			if ('global' === $roomType) {
1111
				$row['room_name'] = Language::translate($row['room_name']);
1112
			}
1113
			if ('user' === $roomType) {
1114
				$row['room_name'] = '';
1115
			}
1116
			$row['image'] = $userImage;
1117
			$row['created'] = Fields\DateTime::formatToShort($row['created']);
1118
			$row['user_name'] = $userName;
1119
			$row['role_name'] = $userRoleName;
1120
			$row['messages'] = static::decodeMessage($row['messages']);
1121
			$rows[] = $row;
1122
		}
1123
		return \array_reverse($rows);
1124
	}
1125
1126
	/**
1127
	 * Get default room.
1128
	 *
1129
	 * @return array|false
1130
	 */
1131
	public static function getDefaultRoom()
1132
	{
1133
		if (Cache::has('Chat', 'DefaultRoom')) {
1134 5
			return Cache::get('Chat', 'DefaultRoom');
1135
		}
1136 5
		$room = false;
1137 5
		$row = (new Db\Query())->from('u_#__chat_global')->where(['name' => 'LBL_GENERAL'])->one();
1138 5
		if (false !== $row) {
1139 1
			$room = [
1140 1
				'roomType' => null,
1141 1
				'recordId' => null,
1142 1
			];
1143 1
		}
1144 1
		Cache::save('Chat', 'DefaultRoom', $room);
1145 4
		return $room;
1146 1
	}
1147 1
1148 1
	/**
1149 1
	 * Get query for unread messages.
1150 1
	 *
1151 1
	 * @param string $roomType
1152 3
	 *
1153 3
	 * @return \App\Db\Query
1154 3
	 */
1155 3
	private static function getQueryForUnread(string $roomType = 'global'): Db\Query
1156 3
	{
1157 3
		$userId = User::getCurrentUserId();
1158 3
		$columnRoom = static::COLUMN_NAME['room'][$roomType];
1159
		$columnMessage = static::COLUMN_NAME['message'][$roomType];
1160
		$columnName = static::COLUMN_NAME['room_name'][$roomType];
1161
		$query = (new Db\Query())->from(['M' => static::TABLE_NAME['message'][$roomType]]);
1162
		if ('user' === $roomType) {
1163
			$query->select(['M.*', 'R.last_message', 'recordid' => "M.{$columnMessage}"])
1164
				->innerJoin(
1165
						['R' => static::TABLE_NAME['room'][$roomType]],
1166
						"R.{$columnRoom} = M.{$columnMessage} AND R.userid = {$userId}"
1167
					)
1168
				->innerJoin(['RN' => static::TABLE_NAME['room_name'][$roomType]], "RN.{$columnRoom} = M.{$columnMessage}");
1169 5
		} else {
1170
			$query->select(['M.*', 'name' => "RN.{$columnName}", 'R.last_message', 'recordid' => "M.{$columnMessage}"])
1171
				->innerJoin(['R' => static::TABLE_NAME['room'][$roomType]], "R.{$columnRoom} = M.{$columnMessage} AND R.userid = {$userId}")
1172 5
				->leftJoin(['RN' => static::TABLE_NAME['room_name'][$roomType]], "RN.{$columnRoom} = M.{$columnMessage}");
1173
		}
1174
		return $query->where(['or', ['R.last_message' => null], ['<', 'R.last_message', new \yii\db\Expression('M.id')]])
1175 5
			->orderBy(["M.{$columnMessage}" => SORT_ASC, 'id' => SORT_DESC]);
1176 5
	}
1177
1178 5
	/**
1179
	 * Get last message id.
1180
	 *
1181
	 * @param array $messages
1182
	 *
1183
	 * @return array
1184
	 */
1185
	private static function getLastMessageId($messages = [])
1186 11
	{
1187
		$room = [];
1188 11
		foreach ($messages as $message) {
1189 11
			$id = $message['id'];
1190 3
			$recordId = $message['recordid'];
1191 3
			if (!isset($room[$recordId]['id']) || $room[$recordId]['id'] < $id) {
1192 3
				$room[$recordId] = ['id' => $id, 'last_message' => $message['last_message']];
1193 3
			}
1194 3
		}
1195 8
		return $room;
1196 3
	}
1197 3
1198 3
	/**
1199 3
	 * Mark as read.
1200 3
	 *
1201 5
	 * @param string $roomType
1202 5
	 * @param array  $messages
1203 5
	 *
1204 5
	 * @throws \yii\db\Exception
1205 5
	 */
1206 5
	private static function markAsRead(string $roomType, $messages = [])
1207
	{
1208
		$room = static::getLastMessageId($messages);
1209
		foreach ($room as $id => $lastMessage) {
1210
			if (empty($lastMessage['last_message'])) {
1211
				Db::getInstance()->createCommand()->insert(static::TABLE_NAME['room'][$roomType], [
1212
					'last_message' => $lastMessage['id'],
1213
					static::COLUMN_NAME['room'][$roomType] => $id,
1214
					'userid' => User::getCurrentUserId(),
1215
				])->execute();
1216
			} else {
1217
				Db::getInstance()->createCommand()->update(
1218
					static::TABLE_NAME['room'][$roomType],
1219
					['last_message' => $lastMessage['id']],
1220
					[static::COLUMN_NAME['room'][$roomType] => $id, 'userid' => User::getCurrentUserId()]
1221
				)->execute();
1222
			}
1223
		}
1224
	}
1225 5
1226
	/**
1227 5
	 * Get unread messages.
1228 2
	 *
1229 2
	 * @param string $roomType
1230 2
	 *
1231 2
	 * @throws \App\Exceptions\AppException
1232 2
	 *
1233 2
	 * @return array
1234 2
	 */
1235 2
	public static function getUnreadByType(string $roomType = 'global')
1236 2
	{
1237
		$dataReader = static::getQueryForUnread($roomType)->createCommand()->query();
1238 3
		$rows = [];
1239
		while ($row = $dataReader->read()) {
1240 2
			$userModel = User::getUserModel($row['userid']);
1241 2
			$image = $userModel->getImage();
1242 2
			if ('global' === $roomType) {
1243 2
				$row['name'] = Language::translate($row['name']);
1244 2
			}
1245 2
			if ('user' === $roomType) {
1246 2
				$row['name'] = $userModel->getName();
1247
			}
1248 5
			$rows[] = [
1249
				'id' => $row['id'],
1250
				'userid' => $row['userid'],
1251
				'messages' => static::decodeMessage($row['messages']),
1252
				'created' => Fields\DateTime::formatToShort($row['created']),
1253
				'user_name' => $userModel->getName(),
1254
				'role_name' => Language::translate($userModel->getRoleInstance()->getName()),
1255
				'image' => $image['url'] ?? null,
1256
				'room_name' => $row['name'],
1257 6
				'recordid' => $row['recordid'],
1258
				'last_message' => $row['last_message'],
1259 6
			];
1260
		}
1261
		$dataReader->close();
1262
		static::markAsRead($roomType, $rows);
1263
		return $rows;
1264
	}
1265
1266
	/**
1267
	 * Get getParticipants.
1268
	 *
1269 1
	 * @param int[] $excludedId
1270
	 *
1271 1
	 * @return array
1272
	 */
1273
	public function getParticipants()
1274
	{
1275
		if (empty($this->recordId) || empty($this->roomType)) {
1276
			return [];
1277
		}
1278
		$columnRoom = static::COLUMN_NAME['room'][$this->roomType];
1279
		$allUsersQuery = (new DB\Query())
1280
			->select(['userid'])
1281
			->from(static::TABLE_NAME['room'][$this->roomType])
1282
			->where([$columnRoom => $this->recordId]);
1283
		$subQuery = (new DB\Query())
1284
			->select(['CR.userid', 'last_id' => new \yii\db\Expression('max(id)')])
1285
			->from(['CR' => static::TABLE_NAME['message'][$this->roomType]])
1286
			->where([static::COLUMN_NAME['message'][$this->roomType] => $this->recordId])
1287
			->groupBy(['CR.userid']);
1288
		$query = (new DB\Query())
1289
			->from(['GL' => static::TABLE_NAME['message'][$this->roomType]])
1290
			->innerJoin(['LM' => $subQuery], 'LM.last_id = GL.id');
1291
		$participants = [];
1292
		$dataReader = $allUsersQuery->createCommand()->query();
1293
		while ($row = $dataReader->read()) {
1294
			$user = static::getUserInfo($row['userid']);
1295
			$participants[$row['userid']] = [
1296
				'user_id' => $row['userid'],
1297
				'user_name' => $user['user_name'],
1298
				'role_name' => $user['role_name'],
1299
				'isAdmin' => $user['isAdmin'],
1300
				'image' => $user['image'],
1301
			];
1302
		}
1303
		$dataReader = $query->createCommand()->query();
1304
		while ($row = $dataReader->read()) {
1305
			if (isset($participants[$row['userid']])) {
1306
				$participants[$row['userid']]['message'] = static::decodeNoHtmlMessage($row['messages']);
1307
			}
1308
		}
1309
		$dataReader->close();
1310
		return array_values($participants);
1311
	}
1312
1313
	/**
1314
	 * Remove room from favorites.
1315
	 *
1316
	 * @param ?int $userId
1317
	 *
1318
	 * @throws \yii\db\Exception
1319
	 *
1320
	 * @return bool $success
1321
	 */
1322
	public function removeFromFavorites(?int $userId = null)
1323
	{
1324
		$success = false;
1325
		if (empty($userId)) {
1326
			$userId = $this->userId;
1327
		}
1328
		if (!empty($this->roomType) && !empty($this->recordId)) {
1329
			Db::getInstance()->createCommand()->delete(
1330
				static::TABLE_NAME['room'][$this->roomType],
1331
				[
1332
					'userid' => $userId,
1333
					static::COLUMN_NAME['room'][$this->roomType] => $this->recordId,
1334
				]
1335
  )->execute();
1336
			if ($userId === $this->userId) {
1337
				unset($this->room['userid']);
1338
				$currentRoom = static::getCurrentRoom();
1339
				if ($currentRoom['recordId'] === $this->recordId && $currentRoom['roomType'] === $this->roomType) {
1340
					static::setCurrentRoomDefault();
1341
				}
1342
			}
1343
			$success = true;
1344
		}
1345
		return $success;
1346
	}
1347
1348
	/**
1349
	 * Add room to favorites.
1350
	 *
1351
	 * @throws \yii\db\Exception
1352
	 */
1353
	public function addToFavorites()
1354
	{
1355
		if (!empty($this->roomType) && !empty($this->recordId)) {
1356
			if ('user' === $this->roomType) {
1357
				$this->setUserRoomRecordId();
1358
			}
1359
			$this->addToFavoritesQuery();
1360
			$this->room['userid'] = $this->userId;
1361
		}
1362
	}
1363
1364
	/**
1365
	 * Add room to favorites query.
1366
	 *
1367
	 * @param ?int $userId
1368
	 *
1369
	 * @throws \yii\db\Exception
1370
	 */
1371
	public function addToFavoritesQuery(?int $userId = null)
1372
	{
1373
		$lastMessage = static::getRoomLastMessage($this->recordId, $this->roomType);
1374
		Db::getInstance()->createCommand()->insert(
1375
			static::TABLE_NAME['room'][$this->roomType],
1376
			[
1377
				'last_message' => $lastMessage['id'] ?? 0,
1378
				'userid' => !empty($userId) ? $userId : $this->userId,
1379
				static::COLUMN_NAME['room'][$this->roomType] => $this->recordId,
1380
			]
1381
		)->execute();
1382
	}
1383
1384
	/**
1385
	 * Check if user room is created.
1386
	 *
1387
	 * @param mixed $userId
1388
	 * @param mixed $relUserId
1389
	 *
1390
	 * @throws \yii\db\Exception
1391
	 */
1392
	public static function isUserRoomCreated($userId, $relUserId)
1393
	{
1394
		$roomsTable = static::TABLE_NAME['room_name']['user'];
1395
		return (new Db\Query())
1396
			->select(['roomid'])
1397
			->from($roomsTable)
1398
			->where(['or', ['and', ['userid' => $relUserId], ['reluserid' => $userId]], ['and', ['userid' => $userId], ['reluserid' => $relUserId]]])
1399
			->one();
1400
	}
1401
1402
	/**
1403
	 * Set user room recordId.
1404
	 *
1405
	 * @throws \yii\db\Exception
1406
	 */
1407
	public function setUserRoomRecordId()
1408
	{
1409
		$roomExists = self::isUserRoomCreated($this->userId, $this->recordId);
1410
		$this->recordId = $roomExists ? $roomExists['roomid'] : $this->createUserRoom($this->recordId);
1411
	}
1412
1413
	/**
1414
	 * Create user room.
1415
	 *
1416
	 * @param int $relUserId
1417
	 *
1418
	 * @return int
1419
	 */
1420
	public function createUserRoom(int $relUserId): int
1421
	{
1422
		$roomsTable = static::TABLE_NAME['room_name']['user'];
1423
		Db::getInstance()->createCommand()->insert(
1424
			$roomsTable,
1425
			[
1426
				'userid' => $this->userId,
1427
				'reluserid' => $relUserId,
1428
			]
1429
		)->execute();
1430
		return Db::getInstance()->getLastInsertID("{$roomsTable}_roomid_seq");
0 ignored issues
show
Bug Best Practice introduced by
The expression return App\Db::getInstan...omsTable.'_roomid_seq') returns the type string which is incompatible with the type-hinted return integer.
Loading history...
1431
	}
1432
1433
	/**
1434
	 * Create private room.
1435
	 *
1436
	 * @param string $name
1437
	 */
1438
	public function createPrivateRoom(string $name)
1439
	{
1440
		$table = static::TABLE_NAME['room_name']['private'];
1441
		$roomIdColumn = static::COLUMN_NAME['room']['private'];
1442
		Db::getInstance()->createCommand()->insert(
1443
				$table,
1444
				[
1445
					'name' => $name,
1446
					'creatorid' => $this->userId,
1447
					'created' => date('Y-m-d H:i:s'),
1448
				]
1449
			)->execute();
1450
		Db::getInstance()->createCommand()->insert(
1451
				static::TABLE_NAME['room']['private'],
1452
				[
1453
					'userid' => $this->userId,
1454
					'last_message' => 0,
1455
					static::COLUMN_NAME['room']['private'] => Db::getInstance()->getLastInsertID("{$table}_{$roomIdColumn}_seq"),
1456
				]
1457
			)->execute();
1458
	}
1459
1460
	/**
1461
	 * Archive private room.
1462
	 *
1463
	 * @param string $name
1464
	 * @param int    $recordId
1465
	 */
1466
	public function archivePrivateRoom(int $recordId)
1467
	{
1468
		Db::getInstance()->createCommand()
1469
			->update(
1470
			static::TABLE_NAME['room_name']['private'], ['archived' => 1], [static::COLUMN_NAME['room']['private'] => $recordId])
1471
			->execute();
1472
	}
1473
1474
	/**
1475
	 * Add participant to private room.
1476
	 *
1477
	 * @param int $userId
1478
	 *
1479
	 * @return bool $alreadyInvited
1480
	 */
1481
	public function addParticipantToPrivate(int $userId): bool
1482
	{
1483
		$privateRoomsTable = static::TABLE_NAME['room']['private'];
1484
		$privateRoomsIdColumn = static::COLUMN_NAME['room']['private'];
1485
		$alreadyInvited = (new Db\Query())
1486
			->select(['userid', $privateRoomsIdColumn])
1487
			->from($privateRoomsTable)
1488
			->where(['and', [$privateRoomsIdColumn => $this->recordId], ['userid' => $userId]])
1489
			->exists();
1490
		if (!$alreadyInvited) {
1491
			$this->addToFavoritesQuery($userId);
1492
		}
1493
		return $alreadyInvited;
1494
	}
1495
1496
	/**
1497
	 * Get a query for chat messages.
1498
	 *
1499
	 * @param int|null $messageId
1500
	 * @param string   $condition
1501
	 * @param bool     $isLimit
1502
	 * @param ?string  $searchVal
1503
	 *
1504
	 * @throws \App\Exceptions\IllegalValue
1505
	 *
1506
	 * @return \App\Db\Query
1507
	 */
1508
	private function getQueryMessage(?int $messageId, string $condition = '>', ?string $searchVal = null, bool $isLimit = true): Db\Query
1509
	{
1510
		$query = null;
1511
		switch ($this->roomType) {
1512
			case 'crm':
1513
				$query = (new Db\Query())
1514
					->select(['C.*', 'U.user_name', 'U.last_name'])
1515
					->from(['C' => 'u_#__chat_messages_crm'])
1516
					->leftJoin(['U' => static::TABLE_NAME['users']], 'U.id = C.userid')
1517
					->where(['crmid' => $this->recordId]);
1518
				break;
1519
			case 'group':
1520
				$query = (new Db\Query())
1521
					->select(['C.*', 'U.user_name', 'U.last_name'])
1522
					->from(['C' => 'u_#__chat_messages_group'])
1523
					->leftJoin(['U' => static::TABLE_NAME['users']], 'U.id = C.userid')
1524
					->where(['groupid' => $this->recordId]);
1525
				break;
1526
			case 'global':
1527
				$query = (new Db\Query())
1528
					->select(['C.*', 'U.user_name', 'U.last_name'])
1529
					->from(['C' => 'u_#__chat_messages_global'])
1530
					->leftJoin(['U' => static::TABLE_NAME['users']], 'U.id = C.userid')
1531
					->where(['globalid' => $this->recordId]);
1532
				break;
1533
			case 'private':
1534
				$query = (new Db\Query())
1535
					->select(['C.*', 'U.user_name', 'U.last_name'])
1536
					->from(['C' => 'u_#__chat_messages_private'])
1537
					->leftJoin(['U' => static::TABLE_NAME['users']], 'U.id = C.userid')
1538
					->where(['privateid' => $this->recordId]);
1539
				break;
1540
			case 'user':
1541
				$query = (new Db\Query())
1542
					->select(['C.*', 'U.user_name', 'U.last_name'])
1543
					->from(['C' => 'u_#__chat_messages_user'])
1544
					->leftJoin(['U' => static::TABLE_NAME['users']], 'U.id = C.userid')
1545
					->where(['roomid' => $this->recordId]);
1546
				break;
1547
			default:
1548
				throw new Exceptions\IllegalValue("ERR_NOT_ALLOWED_VALUE||$this->roomType", 406);
1549
		}
1550
		if (!empty($messageId)) {
1551
			$query->andWhere([$condition, 'C.id', $messageId]);
1552
		}
1553
		if (!empty($searchVal)) {
1554
			$query->andWhere(['LIKE', 'C.messages', $searchVal]);
1555
		}
1556
		if ($isLimit) {
1557
			$query->limit(\App\Config::module('Chat', 'CHAT_ROWS_LIMIT') + 1);
1558
		}
1559
		return $query->orderBy(['id' => SORT_DESC]);
1560
	}
1561
1562
	/**
1563
	 * Get a query for chat room.
1564
	 *
1565
	 * @return \App\Db\Query
1566
	 */
1567
	public function getQueryRoom(): Db\Query
1568
	{
1569
		switch ($this->roomType) {
1570
			case 'crm':
1571
				return (new Db\Query())
1572
					->select(['CR.roomid', 'CR.userid', 'record_id' => 'CR.crmid', 'CR.last_message'])
1573
					->from(['CR' => 'u_#__chat_rooms_crm'])
1574
					->where(['CR.crmid' => $this->recordId])
1575
					->andWhere(['CR.userid' => $this->userId]);
1576
			case 'group':
1577
				return (new Db\Query())
1578
					->select(['CR.roomid', 'CR.userid', 'record_id' => 'CR.groupid', 'CR.last_message'])
1579
					->from(['CR' => 'u_#__chat_rooms_group'])
1580
					->where(['CR.groupid' => $this->recordId])
1581
					->andWhere(['CR.userid' => $this->userId]);
1582
			case 'global':
1583
				return (new Db\Query())
1584
					->select(['CG.*', 'CR.userid', 'record_id' => 'CR.global_room_id', 'CR.last_message'])
1585
					->from(['CG' => 'u_#__chat_global'])
1586
					->leftJoin(['CR' => 'u_#__chat_rooms_global'], "CR.global_room_id = CG.global_room_id AND CR.userid = {$this->userId}")
1587
					->where(['CG.global_room_id' => $this->recordId]);
1588
			case 'private':
1589
				return (new Db\Query())
1590
					->select(['CG.*', 'CR.userid', 'record_id' => 'CR.private_room_id', 'CR.last_message'])
1591
					->from(['CG' => 'u_#__chat_private'])
1592
					->leftJoin(['CR' => 'u_#__chat_rooms_private'], "CR.private_room_id = CG.private_room_id AND CR.userid = {$this->userId}")
1593
					->where(['CG.private_room_id' => $this->recordId]);
1594
			case 'user':
1595
				return (new Db\Query())
1596
					->select(['CG.*', 'CR.userid', 'record_id' => 'CR.roomid', 'CR.last_message'])
1597
					->from(['CG' => 'u_#__chat_user'])
1598
					->leftJoin(['CR' => 'u_#__chat_rooms_user'], "CR.roomid = CG.roomid AND CR.userid = {$this->userId}")
1599
					->where(['CG.roomid' => $this->recordId]);
1600
			default:
1601
				throw new Exceptions\IllegalValue("ERR_NOT_ALLOWED_VALUE||$this->roomType", 406);
1602
				break;
1603
		}
1604
	}
1605
1606
	/**
1607
	 * Update last message ID.
1608
	 *
1609
	 * @throws \App\Exceptions\IllegalValue
1610
	 * @throws \yii\db\Exception
1611
	 */
1612
	private function updateRoom()
1613
	{
1614
		if ('global' === $this->roomType && !$this->isAssigned()) {
1615
			Db::getInstance()->createCommand()
1616
				->insert(static::TABLE_NAME['room'][$this->roomType], [
1617
					static::COLUMN_NAME['room'][$this->roomType] => $this->recordId,
1618
					'last_message' => $this->lastMessageId,
1619
					'userid' => $this->userId,
1620
				])->execute();
1621
			$this->room['last_message'] = $this->lastMessageId;
1622
			$this->room['record_id'] = $this->recordId;
1623
			$this->room['userid'] = $this->userId;
1624
		} elseif (
1625
			\is_array($this->room) && $this->isAssigned() && (empty($this->room['last_message']) || $this->lastMessageId > (int) $this->room['last_message'])
1626
		) {
1627
			Db::getInstance()
1628
				->createCommand()
1629
				->update(static::TABLE_NAME['room'][$this->roomType],
1630
				['last_message' => $this->lastMessageId],
1631
				['and', [
1632
					static::COLUMN_NAME['room'][$this->roomType] => $this->recordId,
1633
					'userid' => $this->userId,
1634
				],
1635
				])->execute();
1636
			$this->room['last_message'] = $this->lastMessageId;
1637
		}
1638
	}
1639
1640
	/**
1641
	 * Decode message.
1642
	 *
1643
	 * @param string $message
1644
	 *
1645
	 * @return string
1646
	 */
1647
	private static function decodeMessage(string $message): string
1648
	{
1649
		return nl2br(\App\Utils\Completions::decode(\App\Purifier::purifyHtml(\App\Purifier::decodeHtml($message))));
1650
	}
1651
1652
	/**
1653
	 * Decode message without html except completions.
1654
	 *
1655
	 * @param string $message
1656
	 * @param bool   $linksAllowed
1657
	 *
1658
	 * @return string
1659
	 */
1660
	private static function decodeNoHtmlMessage(string $message, ?bool $linksAllowed = true): string
1661
	{
1662
		$format = $linksAllowed ? 'HTML' : 'Text';
1663
		$tagsToReplace = ['<br >', '<br>', '<br/>', '<br />', '</div><div>'];
1664
		$message = (string) str_ireplace($tagsToReplace, "\r\n", $message);
1665
		$message = \App\Utils\Completions::decode(\App\Purifier::purifyHtml(strip_tags($message)), $format);
1666
		return (string) str_ireplace($tagsToReplace, '', $message);
1667
	}
1668
1669
	/**
1670
	 * Get chat modules.
1671
	 *
1672
	 * @return array
1673
	 */
1674
	public static function getChatModules(): array
1675
	{
1676
		$activeModules = [];
1677
		$userPrivilegesModel = \Users_Privileges_Model::getCurrentUserPrivilegesModel();
1678
		foreach (array_keys(ModuleHierarchy::getModulesHierarchy()) as $moduleName) {
1679
			if ($userPrivilegesModel->hasModulePermission($moduleName)) {
1680
				$activeModules[] = [
1681
					'id' => $moduleName,
1682
					'label' => Language::translate($moduleName, $moduleName),
1683
				];
1684
			}
1685
		}
1686
		return $activeModules;
1687
	}
1688
1689
	/**
1690
	 * Get chat users.
1691
	 *
1692
	 * @return array
1693
	 */
1694
	public static function getChatUsers(): array
1695
	{
1696
		$owner = Fields\Owner::getInstance();
1697
		$data = [];
1698
		if ($users = $owner->getAccessibleUsers('private', 'owner')) {
1699
			foreach ($users as $key => $value) {
1700
				if (\Users_Privileges_Model::getInstanceById($key)->hasModulePermission('Chat')) {
0 ignored issues
show
'Chat' of type string is incompatible with the type integer expected by parameter $mixed of Users_Privileges_Model::hasModulePermission(). ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

1700
				if (\Users_Privileges_Model::getInstanceById($key)->hasModulePermission(/** @scrutinizer ignore-type */ 'Chat')) {
Loading history...
1701
					$data[] = [
1702
						'id' => $key,
1703
						'label' => $value,
1704
						'img' => User::getImageById($key) ? User::getImageById($key)['url'] : '',
1705
					];
1706
				}
1707
			}
1708
		}
1709
		return $data;
1710
	}
1711
1712
	/**
1713
	 * Pin all users.
1714
	 *
1715
	 * @param int $userId
1716
	 */
1717
	public static function pinAllUsers($userId)
1718
	{
1719
		$dataReader = static::getRoomsUserUnpinnedQuery($userId)->createCommand()->query();
1720
		while ($row = $dataReader->read()) {
1721
			$chat = self::getInstance('user', $row['id']);
1722
			$chat->addToFavorites();
1723
		}
1724
		$dataReader->close();
1725
	}
1726
}
1727