Event::getEventFileMeasureQuery

public function getEventFileMeasureQuery($indicator = '')
{
	if (!class_exists('\\Bitrix\\Crm\\Volume\\EventFileReferenceTable'))
	{
		$connection = Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$eventTable = $helper->quote(Crm\EventTable::getTableName());

		for ($i = 2, $auxiliaries = ['1 as n']; $i <= 50; $i++)
		{
			$auxiliaries[] = $i;
		}
		$auxiliarySql = implode(' union select ', $auxiliaries);

		// analise b_crm_event with non empty field FILES
		$querySql = "(
			select  
				src.ID AS EVENT_ID,
				CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(src.fids, ' ', NS.n), ' ', -1) AS UNSIGNED) as FILE_ID
			from (
				select {$auxiliarySql}
			) NS
			inner join
			(
				select
					@xml := replace(replace(replace(replace(e.FILES,'a:',''),';}',''),':{i:',''),';i:','') as xml,
					CAST(ExtractValue(@xml, '/a/len') AS UNSIGNED) as len,
					ExtractValue(@xml, '/a/i[position() mod 2 = 0]') as fids,
					e.ID
				from 
					{$eventTable} e
				where 
					e.FILES IS NOT NULL
			) src 
			ON NS.n <= src.len
		)";

		Main\ORM\Entity::compileEntity(
			'EventFileReference',
			array(
				'EVENT_ID' => array('data_type' => 'integer'),
				'FILE_ID' => array('data_type' => 'integer'),
				new ORM\Fields\Relations\Reference(
					'FILE',
					Main\FileTable::class,
					array('=this.FILE_ID' => 'ref.ID'),
					array('join_type' => 'INNER')
				),
			),
			array('table_name' => $querySql, 'namespace' => __NAMESPACE__)
		);
	}

	/** @var Main\ORM\Query\Query $query */
	$query = $this->prepareQuery($indicator);
	$this->prepareFilter($query);


	$fileRef = new ORM\Fields\Relations\Reference(
		'FILEREF',
		Crm\Volume\EventFileReferenceTable::class,
		ORM\Query\Join::on('this.ID', 'ref.EVENT_ID'),
		array('join_type' => 'INNER')
	);
	$query->registerRuntimeField($fileRef);

	$file = new ORM\Fields\Relations\Reference(
		'FILE',
		Main\FileTable::class,
		ORM\Query\Join::on('this.FILEREF.FILE_ID', 'ref.ID'),
		array('join_type' => 'INNER')
	);
	$query->registerRuntimeField($file);


	$fileSize = new ORM\Fields\ExpressionField('FILE_SIZE', 'SUM(IFNULL(%s, 0))', 'FILEREF.FILE.FILE_SIZE');
	$fileCount = new ORM\Fields\ExpressionField('FILE_COUNT', 'COUNT(%s)', 'FILEREF.FILE.ID');
	$query
		->registerRuntimeField($fileSize)
		->addSelect('FILE_SIZE')
		->registerRuntimeField($fileCount)
		->addSelect('FILE_COUNT');

	return $query;
}