UStat::getDepartmentAverageGraphData

static function getDepartmentAverageGraphData($deptId, 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();

	$sectionField = ($section) === null ? 'TOTAL' : $section;

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

		$query->setSelect(array('DEPT_ID', 'DATE' => 'HOUR', 'AVG_ACTIVITY' => $sectionField));

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

		$keyFormat = 'Y-m-d H:00:00';
	}
	elseif ($interval === 'day')
	{
		$query = new EntityQuery(DepartmentDayTable::getEntity());

		$query->setSelect(array(
			'DEPT_ID',
			'DATE' => 'DAY',
			new EntityExpressionField(
				'AVG_ACTIVITY',
				'CASE WHEN %s > 0 THEN ROUND((%s / %s), 0) ELSE 0 END',
				array('ACTIVE_USERS', $sectionField, 'ACTIVE_USERS')
			)
		));

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

		$keyFormat = 'Y-m-d';
	}
	elseif ($interval === 'month')
	{
		$query = new EntityQuery(DepartmentDayTable::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(
			'DEPT_ID',
			'DATE',
			new EntityExpressionField(
				'AVG_ACTIVITY',
				'ROUND(SUM(CASE WHEN %s > 0 THEN %s / %s ELSE 0 END), 0)',
				array('ACTIVE_USERS', $sectionField, 'ACTIVE_USERS')
			)
		));

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

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

		$keyFormat = 'Y-m';
	}

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

	$result = $query->exec();

	while ($row = $result->fetch())
	{
		/** @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;
	}

	if ($interval === 'hour')
	{
		// recount unique users from DAILY stats,
		// because there are empty records for each user

		// at this moment AVG_ACTIVITY is just sum of activity, and we should find average per user

		$dailyActiveUsers = array();

		$query = new EntityQuery(DepartmentDayTable::getEntity());

		$result = $query->addSelect('DAY')
			->addSelect('ACTIVE_USERS')
			->addFilter('=DEPT_ID', $deptId)
			->addFilter('>getTimestamp()), ConvertTimeStamp($dateTo->getTimestamp())
			))->exec();

		while ($row = $result->fetch())
		{
			/** @var TypeDateTime[] $row */
			$dailyActiveUsers[$row['DAY']->format('Y-m-d')] = $row['ACTIVE_USERS'];
		}

		foreach ($data as &$hourlyData)
		{
			/** @var TypeDateTime[] $hourlyData */
			if (!empty($dailyActiveUsers[$hourlyData['DATE']->format('Y-m-d')]))
			{
				$hourlyData['AVG_ACTIVITY'] = round(
					$hourlyData['AVG_ACTIVITY'] / $dailyActiveUsers[$hourlyData['DATE']->format('Y-m-d')]
				);
			}
		}
	}

	return $data;
}