CAllCrmDeal::getClientUFSqlData

static function getClientUFSqlData(
	array $order,
	array $filter,
	int $entityTypeId
): array
{
	$entities = [
		CCrmOwnerType::Contact => [
			'FIELD_PREFIX' => 'CONTACT_UF_',
			'DEAL_FIELD_NAME' => 'CONTACT_ID',
			'TABLE' => CCrmContact::TABLE_NAME,
			'TABLE_ALIAS' => 'C',
			'UF_ENTITY_ID' => CCrmContact::GetUserFieldEntityID(),
		],
		CCrmOwnerType::Company => [
			'FIELD_PREFIX' => 'COMPANY_UF_',
			'DEAL_FIELD_NAME' => 'COMPANY_ID',
			'TABLE' => CCrmCompany::TABLE_NAME,
			'TABLE_ALIAS' => 'CO',
			'UF_ENTITY_ID' => CCrmCompany::GetUserFieldEntityID(),
		],
	];
	if (!isset($entities[$entityTypeId]))
	{
		throw new \Bitrix\Main\NotSupportedException();
	}
	$entity = $entities[$entityTypeId];

	$fieldPrefix = $entity['FIELD_PREFIX'];
	$dealTableFieldName = self::TABLE_ALIAS . '.' .$entity['DEAL_FIELD_NAME'];
	$clientTableName = $entity['TABLE'];
	$clientTableAlias = $entity['TABLE_ALIAS'];
	$clientUFEntityId = $entity['UF_ENTITY_ID'];

	$ufFilterSql = null;
	$ufFilter = [];
	$sqlWhere = new CSQLWhere();
	foreach ($filter as $filterKey => $filterValue)
	{
		$filterFieldName = $sqlWhere->MakeOperation($filterKey)['FIELD'];
		if (mb_strpos($filterFieldName, $fieldPrefix) !== 0)
		{
			continue;
		}
		$filterFieldNamePos = mb_strpos($filterKey, $fieldPrefix);
		$filterKey =
			($filterFieldNamePos > 0 ? mb_substr($filterKey, 0, $filterFieldNamePos) : '')
			. 'UF_'
			. mb_substr($filterKey, $filterFieldNamePos + mb_strlen($fieldPrefix));

		//Adapt nested filters for UserTypeSQL
		if (mb_strpos($filterKey, '__INNER_FILTER') === 0)
		{
			$ufFilter[] = $filterValue;
		}
		else
		{
			$ufFilter[$filterKey] = $filterValue;
		}
	}
	if (!empty($ufFilter))
	{
		$userType = new CCrmUserType($GLOBALS['USER_FIELD_MANAGER'], $clientUFEntityId);
		$userType->ListPrepareFilter($ufFilter);

		$ufFilterSql = new CUserTypeSQL();
		$ufFilterSql->SetEntity($clientUFEntityId, $clientTableAlias . '.ID');
		$ufFilterSql->SetFilter($ufFilter);
	}

	$ufOrderSql = null;
	$ufOrder = [];
	foreach ($order as $orderField => $orderDirection)
	{
		if (mb_strpos($orderField, $fieldPrefix) !== 0)
		{
			continue;
		}
		$orderField = 'UF_' . mb_substr($orderField, mb_strlen($fieldPrefix));

		$ufOrder[$orderField] = $orderDirection;
	}
	if (!empty($ufOrder))
	{
		$ufOrderSql = new CUserTypeSQL();
		$ufOrderSql->SetEntity($clientUFEntityId, $clientTableAlias . '.ID');
		$ufOrderSql->SetOrder($ufOrder);
		$ufOrderSql->table_alias .= $clientTableAlias;
	}

	$result = [];
	if ($ufFilterSql)
	{
		// Adding user fields to WHERE
		$ufWhere = $ufFilterSql->GetFilter();
		if ($ufWhere !== '')
		{
			$ufSql = $dealTableFieldName . ' IN (SELECT '
				. $clientTableAlias . '.ID FROM ' . $clientTableName . ' ' . $clientTableAlias . ' '
				. $ufFilterSql->GetJoin($clientTableAlias . '.ID') . ' WHERE ' . $ufWhere . ')';

			// Adding user fields to joins
			$result['WHERE'][] = $ufSql;
		}
	}

	if ($ufOrderSql)
	{
		$ufOrderFound = false;

		foreach ($ufOrder as $orderField => $orderDirection)
		{
			$orderDirection = mb_strtoupper($orderDirection);
			if (!in_array($orderDirection, ['ASC', 'DESC'], true))
			{
				$orderDirection = 'ASC';
			}
			$ufOrderField = $ufOrderSql->GetOrder($orderField);
			if ($ufOrderField !== '')
			{
				$result['ORDERBY'][] = $ufOrderSql->GetOrder($orderField) . ' ' . $orderDirection;
				$ufOrderFound =- true;
			}
		}
		if ($ufOrderFound)
		{
			$result['FROM'][] = $ufOrderSql->GetJoin($dealTableFieldName);
		}
	}

	return $result;
}