UStat::getUsersGraphData

static function getUsersGraphData($userId, TypeDateTime $dateFrom, TypeDateTime $dateTo, $interval, $section = null)
{
	if (!in_array($interval, array('hour', 'day', 'month'), true))
	{
		throw new MainArgumentException('Interval should be the "hour", or "day", or "month".');
	}

	$data = array();

	// rating for TOTAL activity or for an instrument
	$posField = ($section) === null ? 'TOTAL' : $section;

	if ($interval === 'hour')
	{
		$query = new EntityQuery(UserHourTable::getEntity());

		$query->setSelect(array('USER_ID', 'DATE' => 'HOUR', 'TOTAL'));

		foreach (UserHourTable::getSectionNames() as $sectionName)
		{
			$query->addSelect($sectionName);
		}

		$query->setFilter(array(
			'=USER_ID' => $userId,
			'> array(
				ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'),
				ConvertTimeStamp($dateTo->getTimestamp(), 'FULL')
			)
		));

		$keyFormat = 'Y-m-d H:00:00';

		// top position
		$posQuery = new EntityQuery(UserHourTable::getEntity());

		$posQuery->setFilter(array(
			'> array(
				ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'),
				ConvertTimeStamp($dateTo->getTimestamp(), 'FULL')
			),
			'>LEAD_'.$posField => 0,
			'>SUM_'.$posField => 0
		));
	}
	elseif ($interval === 'day')
	{
		$query = new EntityQuery(UserDayTable::getEntity());

		$query->setSelect(array('USER_ID', 'DATE' => 'DAY', 'TOTAL'));

		foreach (UserHourTable::getSectionNames() as $sectionName)
		{
			$query->addSelect($sectionName);
		}

		$query->setFilter(array(
			'=USER_ID' => $userId,
			'> array(
				ConvertTimeStamp($dateFrom->getTimestamp()),
				ConvertTimeStamp($dateTo->getTimestamp())
			)
		));

		$keyFormat = 'Y-m-d';

		// top position
		$posQuery = new EntityQuery(UserDayTable::getEntity());

		$posQuery->setFilter(array(
			'> array(
				ConvertTimeStamp($dateFrom->getTimestamp()),
				ConvertTimeStamp($dateTo->getTimestamp())
			),
			'>LEAD_'.$posField => 0,
			'>SUM_'.$posField => 0
		));
	}
	elseif ($interval === 'month')
	{
		$query = new EntityQuery(UserDayTable::getEntity());
		$sqlHelper = Application::getConnection()->getSqlHelper();

		$monthExpression = array(
			'data_type' => 'string',
			'expression' => array(str_replace(
				$sqlHelper->formatDate('YYYY-MM'), // get db format
				str_replace('%', '%%', $sqlHelper->formatDate('YYYY-MM')), // and quote it for sprintf
				$sqlHelper->formatDate('YYYY-MM', '%1$s') // in main expression
			), 'DAY')
		);

		$query->registerRuntimeField('DATE', $monthExpression);

		$query->setSelect(array(
			'USER_ID',
			'DATE',
			new EntityExpressionField('TOTAL_SUM', 'SUM(%s)', 'TOTAL')
		));

		foreach (UserHourTable::getSectionNames() as $sectionName)
		{
			$query->addSelect(new EntityExpressionField($sectionName.'_SUM', 'SUM(%s)', $sectionName));
		}

		$query->setFilter(array(
			'=USER_ID' => $userId,
			'> array(
				ConvertTimeStamp($dateFrom->getTimestamp()),
				ConvertTimeStamp($dateTo->getTimestamp())
			)
		));

		$query->setGroup(array('USER_ID', 'DATE'));

		$keyFormat = 'Y-m';

		// top position
		$posQuery = new EntityQuery(UserDayTable::getEntity());

		$posQuery->setFilter(array(
			'> array(
				ConvertTimeStamp($dateFrom->getTimestamp()),
				ConvertTimeStamp($dateTo->getTimestamp())
			),
			'>LEAD_'.$posField => 0,
			'>SUM_'.$posField => 0
		));
	}

	// and continue with main data
	$query->setOrder('DATE');

	$result = $query->exec();

	$posTotal = 0;

	while ($row = $result->fetch())
	{
		// back-format keys
		foreach ($row as $k => $v)
		{
			if (mb_substr($k, -4) === '_SUM')
			{
				$row[mb_substr($k, 0, -4)] = $v;
				unset($row[$k]);
			}
		}

		/** @var TypeDateTime[] $row */
		if (!is_object($row['DATE']))
		{
			$key = $row['DATE'];
			$row['DATE'] = new TypeDateTime($row['DATE'], $keyFormat);
		}
		else
		{
			$key = $row['DATE']->format($keyFormat);
		}

		$data[$key] = $row;

		$posTotal += $row[$posField];
	}

	// now get position
	$posQuery->setSelect(array(
		'USER_ID',
		new EntityExpressionField('SUM_'.$posField, 'SUM(%s)', $posField),
		new EntityExpressionField('LEAD_'.$posField, 'CASE WHEN SUM(%s) > '.$posTotal.' THEN 1 ELSE 0 END', $posField)
	));

	$posQuery->registerRuntimeField('MYSELF', array(
		'data_type' => 'integer',
		'expression' => array('CASE WHEN %s = '.(int)$userId.' THEN 1 ELSE 0 END', 'USER_ID')
	));

	$posQuery->addOrder('SUM_'.$posField, 'DESC');
	$posQuery->addOrder('MYSELF', 'DESC');

	// backup query
	$topQuery = clone $posQuery;

	if ($posTotal > 0)
	{
		$result = $posQuery->exec();
		$position = $result->getSelectedRowsCount() + 1;
	}
	else
	{
		$position = 0;
	}

	if ($position < 5)
	{
		//we need all from the top 5
		$filter = $topQuery->getFilter();
		unset($filter['>LEAD_'.$posField]);
		$topQuery->setFilter($filter);

		$topQuery->setLimit(5);

		$result = $topQuery->exec();
	}

	$topUsers = array();

	while (($row = $result->fetch()) && count($topUsers) < 5)
	{
		$topUsers[count($topUsers)+1] = array('USER_ID' => $row['USER_ID'], 'ACTIVITY' => $row['SUM_'.$posField]);
	}

	if ($position >= 5)
	{
		$topUsers[$position] = array('USER_ID' => $userId, 'ACTIVITY' => $posTotal);
	}

	$allQuery = new EntityQuery($query->getEntity());
	$allQuery->setFilter(array_diff_key($query->getFilter(), ['=USER_ID' => null]));
	$allQuery->addSelect(new EntityExpressionField('ALL_ACTIVE_USERS', 'COUNT(DISTINCT %s)', 'USER_ID'));
	$res = $allQuery->exec()->fetch();

	return array('data' => $data, 'rating' => array('top' => $topUsers, 'position' => $position, 'range' => intval($res['ALL_ACTIVE_USERS'])));
}