MissedReaction::getBaseQuery

public function getBaseQuery($startDate, $finishDate, $filterParameters)
{
	$query = StatisticMissedTable::query();

	$query->addSelect('PORTAL_USER_ID');
	$query->addSelect('CALL_MISSED');
	$query->addSelect('UNANSWERED');
	$query->addSelect('AVG_RESPONSE_TIME');

	$this->addToQueryFilterCase($query, $filterParameters);
	$query->whereBetween('CALL_START_DATE', $startDate, $finishDate);
	$query->addGroup('PORTAL_USER_ID');

	$query->registerRuntimeField(new ExpressionField(
		'CALL_MISSED',
		'count(%s)',
		['ID']
	));

	$query->registerRuntimeField(new ExpressionField(
		'UNANSWERED',
		'(sum(if(DATE_SUB(%s, INTERVAL 24 HOUR) <= %s, 0, 1)))',
		['CALLBACK_CALL_START_DATE', 'CALL_START_DATE'])
	);

	$query->registerRuntimeField(new ExpressionField(
		'AVG_RESPONSE_TIME',
		'round(sum(if(%s is not null, TIMESTAMPDIFF(MINUTE, %s, %s), 0)) / (sum(if(%s is not null, if(DATE_SUB(%s, INTERVAL 24 HOUR) <= %s, 1, 0), 0))))',
		[
			'CALLBACK_CALL_START_DATE',
			'CALL_START_DATE',
			'CALLBACK_CALL_START_DATE',
			'CALLBACK_CALL_START_DATE',
			'CALLBACK_CALL_START_DATE',
			'CALL_START_DATE',
		])
	);

	return $query;
}