Base::prepareUserFieldQuery

protected function prepareUserFieldQuery($entityClass, array $userField, array $entityGroupField = array())
{
	/**
	 * @var ORM\Data\DataManager $entityClass
	 */
	$ufName = $entityClass::getUfId();
	if (empty($ufName))
	{
		return '';
	}

	$ufType = $userField['USER_TYPE_ID'];
	$isDiskAvailable = self::isModuleAvailable('disk');

	// need to filter by Entity
	$entityQuery = $entityClass::query();
	$entityEntity = $entityClass::getEntity();

	$entityQuery->addSelect('ID');


	// STAGE_SEMANTIC_ID
	if ($entityClass == Crm\QuoteTable::class)
	{
		Volume\Quote::registerStageField($entityQuery, '', 'STAGE_SEMANTIC_ID');
		Volume\Quote::registerStageField($entityQuery, '', 'QUOTE_STAGE_SEMANTIC_ID');
	}
	if ($entityClass == Crm\InvoiceTable::class)
	{
		Volume\Invoice::registerStageField($entityQuery, '', 'STAGE_SEMANTIC_ID');
		Volume\Invoice::registerStageField($entityQuery, '', 'INVOICE_STAGE_SEMANTIC_ID');
	}
	// DATE
	if (
		$entityClass == Crm\CompanyTable::class ||
		$entityClass == Crm\ContactTable::class
	)
	{
		$dayField = new ORM\Fields\ExpressionField(
			'DATE_CREATE_SHORT',
			'DATE(%s)',
			'DATE_CREATE'
		);
		$entityQuery->registerRuntimeField($dayField);
	}
	if (
		$entityClass == Crm\InvoiceTable::class
	)
	{
		$dayField = new ORM\Fields\ExpressionField(
			'DATE_CREATE_SHORT',
			'DATE(%s)',
			'DATE_INSERT'
		);
		$entityQuery->registerRuntimeField($dayField);
	}



	$entityFieldsSql = '';
	$entityFieldsGroupSql = '';
	$entityFields = array();
	foreach ($entityGroupField as $alias => $field)
	{
		$entityQuery->addSelect($field, $alias);
		$entityFields[] = 'entity.'. $alias;
	}

	if ($this->prepareEntityFilter($entityQuery, $entityEntity))
	{
		$entityFilterQuerySql = $entityQuery->getQuery();

		if (count($entityFields) > 0)
		{
			$entityFieldsSql = ', '.implode(', ', $entityFields);
			$entityFieldsGroupSql = 'GROUP BY '.implode(', ', $entityFields);
		}
	}
	else
	{
		// cannot filter this Entity
		return '';
	}


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

		if (isset(self::$tablesInformation[$utmEntityTableName]))
		{
			if (
				$isDiskAvailable &&
				$ufType === Disk\Uf\FileUserType::USER_TYPE_ID
			)
			{
				$querySql = "
					SELECT 
						SUM(f.FILE_SIZE) as FILE_SIZE,
						COUNT(DISTINCT f.ID) as FILE_COUNT,
						SUM(f.FILE_SIZE) as DISK_SIZE,
						COUNT(DISTINCT f.ID) as DISK_COUNT
						{$entityFieldsSql}
					FROM
						{$utmEntityTableName} ufsrc
						INNER JOIN ( {$entityFilterQuerySql} ) entity 
							ON entity.ID = ufsrc.VALUE_ID
						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 = '".Disk\Internals\ObjectTable::TYPE_FILE."'
						INNER JOIN b_file f
							ON f.ID = files.FILE_ID 
					{$entityFieldsGroupSql}  
				";
			}

			elseif (
				$isDiskAvailable &&
				$ufType === Disk\Uf\VersionUserType::USER_TYPE_ID
			)
			{
				$querySql = "
					SELECT DISTINCT
						SUM(f.FILE_SIZE) as FILE_SIZE,
						COUNT(DISTINCT f.ID) as FILE_COUNT,
						SUM(f.FILE_SIZE) as DISK_SIZE,
						COUNT(DISTINCT f.ID) as DISK_COUNT
						{$entityFieldsSql}
					FROM
						{$utmEntityTableName} ufsrc
						INNER JOIN ( {$entityFilterQuerySql} ) entity 
							ON entity.ID = ufsrc.VALUE_ID
						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 = '".Disk\Internals\ObjectTable::TYPE_FILE."'
						INNER JOIN b_file f
							ON f.ID = versions.FILE_ID
					{$entityFieldsGroupSql}
				";
			}

			elseif (
				$ufType === \CUserTypeFile::USER_TYPE_ID
			)
			{
				$querySql = "
					SELECT 
						SUM(f.FILE_SIZE) as FILE_SIZE,
						COUNT(DISTINCT f.ID) as FILE_COUNT,
						0 as DISK_SIZE,
						0 as DISK_COUNT
						{$entityFieldsSql}
					FROM
						{$utmEntityTableName} ufsrc
						INNER JOIN ( {$entityFilterQuerySql} ) entity 
							ON entity.ID = ufsrc.VALUE_ID
						INNER JOIN b_file f
							ON f.ID = ufsrc.VALUE_INT
							AND ufsrc.FIELD_ID = '{$ufId}'
					{$entityFieldsGroupSql}
				";
			}
		}
	}
	else
	{
		$ufEntityTableFieldName = $userField['FIELD_NAME'];
		$utsEntityTableName = 'b_uts_'.mb_strtolower($ufName);

		if (isset(self::$tablesInformation[$utsEntityTableName]))
		{
			if (
				$isDiskAvailable &&
				$ufType === Disk\Uf\FileUserType::USER_TYPE_ID
			)
			{
				$querySql = "
					SELECT 
						SUM(f.FILE_SIZE) as FILE_SIZE,
						COUNT(DISTINCT f.ID) as FILE_COUNT,
						SUM(f.FILE_SIZE) as DISK_SIZE,
						COUNT(DISTINCT f.ID) as DISK_COUNT
						{$entityFieldsSql}
					FROM
						{$utsEntityTableName} ufsrc
						INNER JOIN ( {$entityFilterQuerySql} ) entity 
							ON entity.ID = ufsrc.VALUE_ID
						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 = '".Disk\Internals\ObjectTable::TYPE_FILE."'
						INNER JOIN b_file f
							ON f.ID = files.FILE_ID 
					{$entityFieldsGroupSql}
				";
			}

			elseif (
				$isDiskAvailable &&
				$ufType === Disk\Uf\VersionUserType::USER_TYPE_ID
			)
			{
				$querySql = "
					SELECT 
						SUM(f.FILE_SIZE) as FILE_SIZE,
						COUNT(DISTINCT f.ID) as FILE_COUNT,
						SUM(f.FILE_SIZE) as DISK_SIZE,
						COUNT(DISTINCT f.ID) as DISK_COUNT
						{$entityFieldsSql}
					FROM
						{$utsEntityTableName} ufsrc
						INNER JOIN ( {$entityFilterQuerySql} ) entity 
							ON entity.ID = ufsrc.VALUE_ID
						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 = '".Disk\Internals\ObjectTable::TYPE_FILE."'
						INNER JOIN b_file f
							ON f.ID = versions.FILE_ID 
					{$entityFieldsGroupSql}
				";
			}

			elseif (
				$ufType === \CUserTypeFile::USER_TYPE_ID
			)
			{
				$querySql = "
					SELECT 
						SUM(f.FILE_SIZE) as FILE_SIZE,
						COUNT(DISTINCT f.ID) as FILE_COUNT,
						0 as DISK_SIZE,
						0 as DISK_COUNT
						{$entityFieldsSql}
					FROM
						{$utsEntityTableName} ufsrc
						INNER JOIN ( {$entityFilterQuerySql} ) entity 
							ON entity.ID = ufsrc.VALUE_ID
						INNER JOIN b_file f
							ON f.ID = cast(ufsrc.{$ufEntityTableFieldName} as UNSIGNED)
							and ufsrc.{$ufEntityTableFieldName} REGEXP '^[0-9]+$'
					{$entityFieldsGroupSql}
				";
			}
		}
	}

	return $querySql;
}