Chat::getRelation

static function getRelation($chatId, $params = [])
{
	$chatId = intval($chatId);
	if ($chatId <= 0)
	{
		return false;
	}

	$connection = BitrixMainApplication::getInstance()->getConnection();

	$selectFields = '';
	if (isset($params['SELECT']))
	{
		$params['SELECT'][] = 'ID';
		$params['SELECT'][] = 'USER_ID';
		$map = BitrixImModelRelationTable::getMap();
		foreach ($params['SELECT'] as $key => $value)
		{
			if (is_int($key) && isset($map[$value]))
			{
				$selectFields .= "R.{$value}, ";
				unset($map[$value]);
			}
			else if (!is_int($key) && isset($map[$key]))
			{
				$value = (string)$value;
				$selectFields .= "R.{$key} '{$connection->getSqlHelper()->forSql($value)}', ";
				unset($map[$value]);
			}
		}
	}
	if (!$selectFields)
	{
		$selectFields = 'R.*, ';
	}

	$withUserFields = false;
	if (isset($params['USER_DATA']) && $params['USER_DATA'] == 'Y')
	{
		$withUserFields = true;
		$list = Array('ACTIVE', 'EXTERNAL_AUTH_ID');
		foreach ($list as $key)
		{
			$selectFields .= "U.{$key} USER_DATA_{$key}, ";
		}
	}
	$skipUsers = false;
	$skipUserInactiveSql = '';
	if (isset($params['SKIP_INACTIVE_USER']) && $params['SKIP_INACTIVE_USER'] === 'Y')
	{
		$skipUsers = true;
		$skipUserInactiveSql = "AND U.ACTIVE = 'Y'";
	}

	$skipUserTypes = $params['SKIP_USER_TYPES'] ?? [];
	if (isset($params['SKIP_CONNECTOR']) && $params['SKIP_CONNECTOR'] === 'Y')
	{
		$skipUserTypes[] = 'imconnector';
	}

	$skipUserTypesSql = '';
	if (!empty($skipUserTypes))
	{
		$skipUsers = true;
		if (count($skipUserTypes) === 1)
		{
			$skipUserTypesSql = "AND (U.EXTERNAL_AUTH_ID != '".$connection->getSqlHelper()->forSql($skipUserTypes[0])."' OR U.EXTERNAL_AUTH_ID IS NULL)";
		}
		else
		{
			$skipUserTypes = array_map(function($type) use ($connection) {
				return $connection->getSqlHelper()->forSql($type);
			}, $skipUserTypes);

			$skipUserTypesSql = "AND (U.EXTERNAL_AUTH_ID NOT IN ('".implode("','", $skipUserTypes)."') OR U.EXTERNAL_AUTH_ID IS NULL)";
		}
	}

	$whereFields = '';
	if (isset($params['FILTER']))
	{
		$map = BitrixImModelRelationTable::getMap();
		foreach ($params['FILTER'] as $key => $value)
		{
			if (!isset($map[$key]))
			{
				continue;
			}

			if (is_int($value))
			{
			}
			else if (is_bool($value))
			{
				$value = $value? "'Y'": "'N'";
			}
			else if (is_string($value))
			{
				$value = "'{$connection->getSqlHelper()->forSql($value)}'";
			}
			else
			{
				continue;
			}

			$whereFields .= " AND R.{$key} = {$value}";
		}
	}

	/*$skipUnmodifiedRecords = false;
	if (isset($params['SKIP_RELATION_WITH_UNMODIFIED_COUNTERS']) && $params['SKIP_RELATION_WITH_UNMODIFIED_COUNTERS'] == 'Y')
	{
		$skipUnmodifiedRecords = true;
	}*/

	/*$sqlSelectCounter = 'R.LAST_ID, R.COUNTER, R.COUNTER PREVIOUS_COUNTER';

	$customCounter = false;
	$customMaxId = 0;
	$customMinId = 0;
	$counters = [];

	if (isset($params['REAL_COUNTERS']) && $params['REAL_COUNTERS'] != 'N' || $skipUnmodifiedRecords)
	{
		if (is_array($params['REAL_COUNTERS']) && isset($params['REAL_COUNTERS']['LAST_ID']))
		{
			$sqlSelectCounter = "R.COUNTER PREVIOUS_COUNTER, (
				SELECT COUNT(1) FROM b_im_message M WHERE M.CHAT_ID = R.CHAT_ID AND M.ID > ".intval($params['REAL_COUNTERS']['LAST_ID'])."
			) COUNTER";
		}
		else
		{
			$customCounter = true;
			$query = $connection->query("
				SELECT ID FROM b_im_message
				WHERE CHAT_ID = {$chatId}
				ORDER BY DATE_CREATE DESC, ID DESC
				LIMIT 100
			");
			$messageCounter = 0;
			while ($row = $query->fetch())
			{
				if (!$customMaxId)
				{
					$customMaxId = $row['ID'];
				}
				$counters[$row['ID']] = $messageCounter++;
				$customMinId = $row['ID'];
			}
		}
	}*/

	$limit = '';
	if (isset($params['LIMIT']))
	{
		$limit = 'LIMIT '.(int)$params['LIMIT'];
	}

	$offset = '';
	if (isset($params['OFFSET']))
	{
		$offset = 'OFFSET '.(int)$params['OFFSET'];
	}

	$orderField = 'R.ID';

	if (isset($params['LAST_USER_ID']) && (int)$params['LAST_USER_ID'] >= 0)
	{
		$lastUserId = (int)$params['LAST_USER_ID'];
		$whereFields .= " AND R.USER_ID > {$lastUserId}";
		$orderField = 'R.USER_ID';
	}

	$selectFields = rtrim($selectFields, ', ');
	$sql = "
		SELECT {$selectFields}
		FROM b_im_relation R
		".($withUserFields && !$skipUsers? "LEFT JOIN b_user U ON R.USER_ID = U.ID": "")."
		".($skipUsers? "INNER JOIN b_user U ON R.USER_ID = U.ID {$skipUserInactiveSql} {$skipUserTypesSql}": "")."
		WHERE R.CHAT_ID = {$chatId} {$whereFields} 
		ORDER BY {$orderField} ASC
		{$limit} {$offset}
	";
	$relations = array();
	$query = $connection->query($sql);
	while ($row = $query->fetch())
	{
		/*if ($customCounter)
		{
			if (isset($counters[$row['LAST_ID']]))
			{
				$row['COUNTER'] = $counters[$row['LAST_ID']];
			}
			else if ($row['LAST_ID'] < $customMinId)
			{
				$row['COUNTER'] = count($counters);
			}
			else if ($row['LAST_ID'] > $customMaxId)
			{
				$row['COUNTER'] = 0;
			}
		}
		else
		{
			$row['COUNTER'] = $row['COUNTER'] > 99? 100: (int)$row['COUNTER'];
		}

		$row['PREVIOUS_COUNTER'] = (int)$row['PREVIOUS_COUNTER'];

		if ($skipUnmodifiedRecords && $row['COUNTER'] == $row['PREVIOUS_COUNTER'])
		{
			continue;
		}*/

		foreach ($row as $key => $value)
		{
			if (mb_strpos($key, 'USER_DATA_') === 0)
			{
				$row['USER_DATA'][mb_substr($key, 10)] = $value;
				unset($row[$key]);
			}
		}

		$relations[$row['USER_ID']] = $row;
	}

	// region New counter
	// todo: select counter only if it's need
	if (!isset($params['WITHOUT_COUNTERS']) || $params['WITHOUT_COUNTERS'] !== 'Y')
	{
		$userIds = array_keys($relations);
		$readService = new ReadService();
		$counters = $readService->getCounterService()->getByChatForEachUsers($chatId, $userIds);
		$lastIdInChat = $readService->getViewedService()->getLastMessageIdInChat($chatId) ?? 0;
		$lastReads = $readService->getViewedService()->getDateViewedByMessageIdForEachUser($lastIdInChat, $userIds);
		foreach ($relations as $userId => $relation)
		{
			$counter = $counters[$userId] ?? 0;
			$counter = $counter > 99 ? 100 : $counter;
			$relations[$userId]['COUNTER'] = $counter;
			$relations[$userId]['LAST_READ'] = $lastReads[$userId] ?? null;
		}
	}
	// endregion

	return $relations;
}