DealActivityStatistics::getList

public function getList(array $params)
{
	/** @var Filter $filter */
	$filter = isset($params['filter']) ? $params['filter'] : null;
	if(!($filter instanceof Filter))
	{
		throw new Main\ObjectNotFoundException("The 'filter' is not found in params.");
	}

	$this->applyFilterContext($filter);

	$categoryID = (int)$filter->getExtraParam('dealCategoryID', -1);
	$semanticID = $filter->getExtraParam('semanticID', PhaseSemantics::UNDEFINED);

	$group = isset($params['group'])? mb_strtoupper($params['group']) : '';
	if($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE)
	{
		$group = '';
	}

	/** @var array $select */
	$select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array();
	$name = '';
	$aggregate = '';
	if(!empty($select))
	{
		$selectItem = $select[0];
		if(isset($selectItem['name']))
		{
			$name = $selectItem['name'];
		}
		if(isset($selectItem['aggregate']))
		{
			$aggregate = mb_strtoupper($selectItem['aggregate']);
		}
	}

	if($name === '')
	{
		$name = 'CALL_QTY';
	}

	if($aggregate !== '' && !in_array($aggregate, array('SUM', 'COUNT', 'MAX', 'MIN')))
	{
		$aggregate = '';
	}

	$permissionSql = '';
	if($this->enablePermissionCheck)
	{
		$permissionSql = $this->preparePermissionSql();
		if($permissionSql === false)
		{
			//Access denied;
			return array();
		}
	}

	$period = $filter->getPeriod();
	$periodStartDate = $period['START'];
	$periodEndDate = $period['END'];

	$query = new Query(DealActivityStatisticsTable::getEntity());

	$nameAlias = $name;
	if($aggregate !== '')
	{
		if($aggregate === 'COUNT')
		{
			$query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)"));
		}
		else
		{
			$nameAlias = "{$nameAlias}_R";
			$query->registerRuntimeField('', new ExpressionField($nameAlias, "{$aggregate}(%s)", $name));
		}
	}
	$query->addSelect($nameAlias);

	$recentOnly = (bool)$filter->getExtraParam('recentOnly', false);
	if($recentOnly)
	{
		$query->setTableAliasPostfix('_s2');

		$subQuery = new Query(DealActivityStatisticsTable::getEntity());
		$subQuery->setTableAliasPostfix('_s1');
		$subQuery->addSelect('OWNER_ID');

		$subQuery->addFilter('>=DEADLINE_DATE', $periodStartDate);
		$subQuery->addFilter('<=DEADLINE_DATE', $periodEndDate);

		if($semanticID !== PhaseSemantics::UNDEFINED)
		{
			$subQuery->addFilter('=STAGE_SEMANTIC_ID', $semanticID);
		}

		if($categoryID >= 0)
		{
			//HACK: use SqlExpression to avoid filter sql like (CATEGORY_ID IS NULL OR CATEGORY_ID = 0), that cause the filesort.
			$subQuery->addFilter('=CATEGORY_ID', new Main\DB\SqlExpression('?i', $categoryID));
		}

		if($this->enablePermissionCheck && is_string($permissionSql) && $permissionSql !== '')
		{
			$subQuery->addFilter('@OWNER_ID', new SqlExpression($permissionSql));
		}

		$responsibleIDs = $filter->getResponsibleIDs();
		if(!empty($responsibleIDs))
		{
			$subQuery->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
		}

		$subQuery->addGroup('OWNER_ID');
		$subQuery->addSelect('MAX_CREATED_DATE');
		$subQuery->registerRuntimeField('', new ExpressionField('MAX_DEADLINE_DATE', 'MAX(DEADLINE_DATE)'));
		$subQuery->addFilter('<=DEADLINE_DATE', $periodEndDate);

		$query->registerRuntimeField('',
			new ReferenceField('M',
				Base::getInstanceByQuery($subQuery),
				array('=this.OWNER_ID' => 'ref.OWNER_ID', '=this.DEADLINE_DATE' => 'ref.MAX_DEADLINE_DATE'),
				array('join_type' => 'INNER')
			)
		);
	}
	else
	{
		$query->addFilter('>=DEADLINE_DATE', $periodStartDate);
		$query->addFilter('<=DEADLINE_DATE', $periodEndDate);

		if($semanticID !== PhaseSemantics::UNDEFINED)
		{
			$query->addFilter('=STAGE_SEMANTIC_ID', $semanticID);
		}

		if($categoryID >= 0)
		{
			//HACK: use SqlExpression to avoid filter sql like (CATEGORY_ID IS NULL OR CATEGORY_ID = 0), that cause the filesort.
			$query->addFilter('=CATEGORY_ID', new Main\DB\SqlExpression('?i', $categoryID));
		}

		if($this->enablePermissionCheck && is_string($permissionSql) && $permissionSql !== '')
		{
			$query->addFilter('@OWNER_ID', new SqlExpression($permissionSql));
		}

		$responsibleIDs = $filter->getResponsibleIDs();
		if(!empty($responsibleIDs))
		{
			$query->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
		}
	}

	$sort = isset($params['sort']) && is_array($params['sort']) && !empty($params['sort']) ? $params['sort'] : null;
	if($sort)
	{
		foreach($sort as $sortItem)
		{
			if(isset($sortItem['name']))
			{
				$sortName = $sortItem['name'];
				if($sortName === $name)
				{
					$sortName = $nameAlias;
				}
				$query->addOrder($sortName, isset($sortItem['order']) ? $sortItem['order'] : 'ASC');
			}
		}
	}

	if($group !== '')
	{
		if($group === self::GROUP_BY_USER)
		{
			$query->addSelect('RESPONSIBLE_ID');
			$query->addGroup('RESPONSIBLE_ID');
		}
		else //if($groupBy === self::GROUP_BY_DATE)
		{
			$query->addSelect('DEADLINE_DATE');
			$query->addGroup('DEADLINE_DATE');
			if(!$sort)
			{
				$query->addOrder('DEADLINE_DATE', 'ASC');
			}
		}
	}

	$dbResult = $query->exec();
	$result = array();
	$useAlias = $nameAlias !== $name;
	if($group === self::GROUP_BY_DATE)
	{
		while($ary = $dbResult->fetch())
		{
			if($useAlias && isset($ary[$nameAlias]))
			{
				$ary[$name] = $ary[$nameAlias];
				unset($ary[$nameAlias]);
			}

			$ary['DATE'] = $this->getLocalDate($ary['DEADLINE_DATE'], 'Y-m-d');
			unset($ary['DEADLINE_DATE']);

			if($ary['DATE'] === '9999-12-31')
			{
				//Skip empty dates
				continue;
			}

			$result[] = $ary;
		}
	}
	elseif($group === self::GROUP_BY_USER)
	{
		while($ary = $dbResult->fetch())
		{
			if($useAlias && isset($ary[$nameAlias]))
			{
				$ary[$name] = $ary[$nameAlias];
				unset($ary[$nameAlias]);
			}
			$result[] = $ary;
		}
		self::parseUserInfo($result, ['RESPONSIBLE_ID' => 'USER']);
	}
	else
	{
		while($ary = $dbResult->fetch())
		{
			if($useAlias && isset($ary[$nameAlias]))
			{
				$ary[$name] = $ary[$nameAlias];
				unset($ary[$nameAlias]);
			}

			$result[] = $ary;
		}
	}

	return $result;
}