UStat::getSectionInvolvement

static function getSectionInvolvement($departmentId, $section, TypeDateTime $dateFrom, TypeDateTime $dateTo, $interval)
{
	// at this moment departmentId doesn't work, data will be counted for a whole company

	if (!in_array($interval, array('hour', 'day', 'month'), true))
	{
		throw new MainArgumentException('Interval should be the "hour", or "day", or "month".');
	}

	if (!in_array($section, UserHourTable::getSectionNames(), true))
	{
		throw new MainArgumentException(sprintf('Unknown section "%s"', $section));
	}

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

		$query->setSelect(array(
			'DATE' => 'HOUR',
			new EntityExpressionField('TOTAL_USERS', 'COUNT(1)'),
			new EntityExpressionField($section.'_USAGE', 'SUM(CASE WHEN %s > 0 THEN 1 ELSE 0 END)', $section)
		));

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

		$query->setGroup('HOUR');

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

		// need to rewrite TOTAL_USERS according to daily stats
	}
	elseif ($interval === 'day')
	{
		$query = new EntityQuery(UserDayTable::getEntity());

		$query->setSelect(array(
			'DATE' => 'DAY',
			new EntityExpressionField('TOTAL_USERS', 'COUNT(1)'),
			new EntityExpressionField($section.'_USAGE', 'SUM(CASE WHEN %s > 0 THEN 1 ELSE 0 END)', $section)
		));

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

		$keyFormat = 'Y-m-d';

		$query->setGroup('DAY');
	}
	elseif ($interval === 'month')
	{
		$subQuery = 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')
		);

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

		$subQuery->setSelect(array(
			'USER_ID',
			'DATE',
			new EntityExpressionField($section.'_SUM', 'SUM(%s)', $section)
		));

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

		$query = new EntityQuery($subQuery);

		$query->setSelect(array(
			'DATE',
			new EntityExpressionField($section.'_USAGE', 'SUM(CASE WHEN %s > 5 THEN 1 ELSE 0 END)', $section.'_SUM'),
			new EntityExpressionField('TOTAL_USERS', 'COUNT(1)')
		));

		$query->setGroup('DATE');

		$keyFormat = 'Y-m';
	}

	$query->addOrder('DATE');

	$result = $query->exec();

	$data = array();

	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
		$dailyActiveUsers = array();

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

		$result = $query->addSelect('DAY')
			->addSelect('ACTIVE_USERS')
			->addFilter('=DEPT_ID', 0)
			->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 */
			$hourlyData['TOTAL_USERS'] = $dailyActiveUsers[$hourlyData['DATE']->format('Y-m-d')];
		}
	}

	return $data;
}