UStat::getDepartmentGraphData

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

	$data = array();

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

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

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

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

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

		// no company involvement for hourly data
	}
	elseif ($interval === 'day')
	{
		$query = new EntityQuery(DepartmentDayTable::getEntity());

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

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

		$query->setFilter(array(
			'=DEPT_ID' => $departmentId,
			'> 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->registerRuntimeField('TOTAL_SUM', array(
			'data_type' => 'integer',
			'expression' => array('SUM(%s)', 'TOTAL')
		));

		$query->setSelect(array('DATE', 'TOTAL_SUM'));

		foreach (UserHourTable::getSectionNames() as $sectionName)
		{
			$query->registerRuntimeField($sectionName.'_SUM', array(
				'data_type' => 'integer',
				'expression' => array('SUM(%s)', $sectionName)
			));

			$query->addSelect($sectionName.'_SUM');
		}

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

		$query->setGroup('DATE');

		$keyFormat = 'Y-m';

		// company involvement will be attached later
	}

	$query->setOrder('DATE');

	$result = $query->exec();

	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;
	}

	if ($interval === 'month')
	{
		// count involvement
		$invQuery = new EntityQuery(DepartmentDayTable::getEntity());

		$invQuery->setSelect(array('DATE' => 'DAY', 'INVOLVEMENT'));

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

		$invQuery->addOrder('INVOLVEMENT', 'DESC');

		$result = $invQuery->exec();

		$invData = array();

		while ($row = $result->fetch())
		{
			/** @var TypeDateTime[] $row */
			$invData[$row['DATE']->format('Y-m')][$row['DATE']->format('j')] = $row['INVOLVEMENT'];
		}

		// get 70% most involved days
		foreach ($invData as $month => $monthData)
		{
			$bestDays = array_slice($monthData, 0, round(count($monthData)*0.7));

			if (count($bestDays))
			{
				$involvement = round(array_sum($bestDays)/count($bestDays));
				$data[$month]['INVOLVEMENT'] = $involvement;
			}
		}
	}

	return $data;
}