ClearUnregisteredLogo::prepareUserFieldQuery

static function prepareUserFieldQuery()
{
	$diskAvailable = \Bitrix\Main\ModuleManager::isModuleInstalled('disk') &&
					 \Bitrix\Main\Loader::includeModule('disk');

	$userTypeField = array(\CUserTypeFile::USER_TYPE_ID);
	if ($diskAvailable)
	{
		$userTypeField[] = \Bitrix\Disk\Uf\FileUserType::USER_TYPE_ID;
		$userTypeField[] = \Bitrix\Disk\Uf\VersionUserType::USER_TYPE_ID;
	}

	$userFieldList = \Bitrix\Main\UserFieldTable::getList(array(
		'filter' => array(
			'ENTITY_ID' => 'CRM_%',
			'=USER_TYPE_ID' => $userTypeField,
		),
		'select' => array(
			'ID',
			'ENTITY_ID',
			'USER_TYPE_ID',
			'FIELD_NAME',
			'MULTIPLE',
		),
	));
	$userFieldInformation[] = array();
	if ($userFieldList->getSelectedRowsCount() > 0)
	{
		foreach ($userFieldList as $userField)
		{
			$userFieldInformation[] = $userField;
		}
	}
	if (empty($userFieldInformation))
	{
		return [];
	}

	$connection = \Bitrix\Main\Application::getConnection();

	$tablesInformation = array();
	$result = $connection->query("
		SELECT TABLE_NAME
		FROM information_schema.TABLES 
		WHERE 
			TABLE_SCHEMA = '".$connection->getDatabase()."'
			AND (
				TABLE_NAME LIKE 'b_uts_crm_%' OR
				TABLE_NAME LIKE 'b_utm_crm_%' OR
				TABLE_NAME LIKE 'b_uts_order' OR
				TABLE_NAME LIKE 'b_utm_order'
			)
	");
	while ($row = $result->fetch())
	{
		$tablesInformation[] = $row['TABLE_NAME'];
	}
	if (empty($tablesInformation))
	{
		return [];
	}


	$querySql = [];

	foreach ($userFieldInformation as $userField)
	{
		$ufName = $userField['ENTITY_ID'];
		$ufType = $userField['USER_TYPE_ID'];

		if ($userField['MULTIPLE'] === 'Y')
		{
			$ufId = $userField['ID'];
			$utmEntityTableName = 'b_utm_'.mb_strtolower($ufName);

			if (in_array($utmEntityTableName, $tablesInformation))
			{
				if (
					$diskAvailable &&
					$ufType === \Bitrix\Disk\Uf\FileUserType::USER_TYPE_ID
				)
				{
					$querySql[] = "
						SELECT files.FILE_ID as FILE_ID
						FROM
							{$utmEntityTableName} ufsrc
							INNER JOIN b_disk_attached_object attached
								ON attached.ID = ufsrc.VALUE_INT
								AND ufsrc.FIELD_ID = '{$ufId}'
							INNER JOIN b_disk_object files
								ON files.ID = attached.OBJECT_ID 
								AND files.ID = files.REAL_OBJECT_ID
								AND files.TYPE = '".\Bitrix\Disk\Internals\ObjectTable::TYPE_FILE."'
					";
				}

				elseif (
					$diskAvailable &&
					$ufType === \Bitrix\Disk\Uf\VersionUserType::USER_TYPE_ID
				)
				{
					$querySql[] = "
						SELECT versions.FILE_ID as FILE_ID
						FROM
							{$utmEntityTableName} ufsrc
							INNER JOIN b_disk_attached_object attached
								ON attached.ID = ufsrc.VALUE_INT
								AND ufsrc.FIELD_ID = '{$ufId}'
							INNER JOIN b_disk_version versions
								ON versions.ID = attached.VERSION_ID 
							INNER JOIN b_disk_object files
								ON files.ID = versions.OBJECT_ID
								AND files.ID = attached.OBJECT_ID 
								AND files.ID = files.REAL_OBJECT_ID
								AND files.TYPE = '".\Bitrix\Disk\Internals\ObjectTable::TYPE_FILE."'
					";
				}

				elseif (
					$ufType === \CUserTypeFile::USER_TYPE_ID
				)
				{
					$querySql[] = "
						SELECT ufsrc.VALUE_INT as FILE_ID
						FROM {$utmEntityTableName} ufsrc
						WHERE ufsrc.FIELD_ID = '{$ufId}' AND ufsrc.VALUE_INT IS NOT NULL 
					";
				}
			}
		}
		else
		{
			$ufEntityTableFieldName = $userField['FIELD_NAME'];
			$utsEntityTableName = 'b_uts_'.mb_strtolower($ufName);

			if (in_array($utsEntityTableName, $tablesInformation))
			{
				if (
					$diskAvailable &&
					$ufType === \Bitrix\Disk\Uf\FileUserType::USER_TYPE_ID
				)
				{
					$querySql[] = "
						SELECT files.FILE_ID as FILE_ID
						FROM
							{$utsEntityTableName} ufsrc
							INNER JOIN b_disk_attached_object attached
								ON attached.ID = cast(ufsrc.{$ufEntityTableFieldName} as UNSIGNED)
								and ufsrc.{$ufEntityTableFieldName} REGEXP '^[0-9]+$'
							INNER JOIN b_disk_object files
								ON files.ID = attached.OBJECT_ID 
								AND files.ID = files.REAL_OBJECT_ID
								AND files.TYPE = '".\Bitrix\Disk\Internals\ObjectTable::TYPE_FILE."'
					";
				}

				elseif (
					$diskAvailable &&
					$ufType === \Bitrix\Disk\Uf\VersionUserType::USER_TYPE_ID
				)
				{
					$querySql[] = "
						SELECT versions.FILE_ID as FILE_ID
						FROM
							{$utsEntityTableName} ufsrc
							INNER JOIN b_disk_attached_object attached
								ON attached.ID = cast(ufsrc.{$ufEntityTableFieldName} as UNSIGNED)
								and ufsrc.{$ufEntityTableFieldName} REGEXP '^[0-9]+$'
							INNER JOIN b_disk_version versions
								ON versions.ID = attached.VERSION_ID 
							INNER JOIN b_disk_object files
								ON files.ID = versions.OBJECT_ID
								AND files.ID = attached.OBJECT_ID
								AND files.ID = files.REAL_OBJECT_ID
								AND files.TYPE = '".\Bitrix\Disk\Internals\ObjectTable::TYPE_FILE."'
					";
				}

				elseif (
					$ufType === \CUserTypeFile::USER_TYPE_ID
				)
				{
					$querySql[] = "
						SELECT CAST(ufsrc.{$ufEntityTableFieldName} as UNSIGNED) as FILE_ID
						FROM {$utsEntityTableName} ufsrc 
						WHERE ufsrc.{$ufEntityTableFieldName} IS NOT NULL AND ufsrc.{$ufEntityTableFieldName} REGEXP '^[0-9]+$'
					";
				}
			}
		}
	}

	return $querySql;
}