private function translateFilter(array $filter): ?ConditionTree
{
$connection = BitrixMainApplication::getConnection();
$helper = $connection->getSqlHelper();
$filterFields = $this->getFilteredFields($filter);
if (
array_key_exists('ONLY_ROOT_TASKS', $filter)
&& $filter['ONLY_ROOT_TASKS'] === 'Y'
&&
(
in_array('FULL_SEARCH_INDEX', $filterFields)
|| in_array('COMMENT_SEARCH_INDEX', $filterFields)
)
)
{
unset($filter['ONLY_ROOT_TASKS']);
}
if (
array_key_exists('SAME_GROUP_PARENT_EX', $filter)
&& $filter['SAME_GROUP_PARENT_EX'] === 'Y'
)
{
unset($filter['SAME_GROUP_PARENT']);
}
$conditionTree = Query::filter();
$filterCount = count($filter);
$logicOr = false;
if (isset($filter['::LOGIC']))
{
$filterCount = $filterCount - 1;
switch ($filter['::LOGIC'])
{
case 'AND':
$conditionTree->logic('and');
break;
case 'OR':
$conditionTree->logic('or');
$logicOr = true;
break;
default:
throw new InvalidFilterException('Unknown logic in filter');
}
}
foreach ($filter as $key => $val)
{
// Skip meta-key
if ($key === '::LOGIC')
{
continue;
}
// Skip markers
if ($key === '::MARKERS')
{
continue;
}
if ($this->isSubFilter($key))
{
$subFilter = $this->translateFilter($val);
$subFilter && $conditionTree->where($subFilter);
continue;
}
$key = ltrim($key);
// This type of operations should be processed in special way
// Fields like "META:DEADLINE_TS" will be replaced to "DEADLINE"
if (mb_substr($key, -3) === '_TS')
{
$subFilter = $this->translateFilterTs($key, $val);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
continue;
}
$operation = $this->parseOperation($key);
$field = $key;
if ($operation !== self::OPERATION_DEFAULT)
{
$field = mb_substr($key, mb_strlen($operation));
}
switch ($field)
{
case "META::ID_OR_NAME":
if (empty($val))
{
break;
}
$subFilter = Query::filter();
$subFilter
->logic('or')
->where('ID', (int) $val);
$val = strtoupper((string)$val);
if ($this->query->needTitleEscape())
{
$val = $this->escapeStencilCharacters($val);
}
$subFilter->whereLike('TITLE', '%'. $val .'%');
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "PARENT_ID":
case "GROUP_ID":
case "STATUS_CHANGED_BY":
case "FORUM_TOPIC_ID":
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_NUMBER);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "ID":
case "PRIORITY":
case "CREATED_BY":
case "RESPONSIBLE_ID":
case "STAGE_ID":
case "TIME_ESTIMATE":
case "FORKED_BY_TEMPLATE_ID":
case "DEADLINE_COUNTED":
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_NUMBER_WO_NULLS);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "REFERENCE:RESPONSIBLE_ID":
$subFilter = $this->createSubfilter('RESPONSIBLE_ID', $val, $operation, self::CAST_REFERENCE);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "REFERENCE:START_DATE_PLAN":
$subFilter = $this->createSubfilter('START_DATE_PLAN', $val, $operation, self::CAST_REFERENCE);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case 'META:GROUP_ID_IS_NULL_OR_ZERO':
$subFilter = $this->createSubfilter('GROUP_ID', $val, $operation, self::CAST_NULL_OR_ZERO);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "CHANGED_BY":
$field = new ExpressionField(
'CHANGED_BY'.mt_rand(1000, 9999),
'CASE WHEN %1$s IS NULL THEN %2$s ELSE %1$s END',
["CHANGED_BY", "CREATED_BY"]
);
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_NUMBER);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case 'GUID':
case 'TITLE':
if ($this->query->needTitleEscape())
{
$val = $this->escapeStencilCharacters($val);
}
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_STRING);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case 'FULL_SEARCH_INDEX':
case 'COMMENT_SEARCH_INDEX':
$subFilter = $this->createSubfilter(TaskQueryBuilder::ALIAS_SEARCH_FULL.'.SEARCH_INDEX', $val, $operation, self::CAST_FULLTEXT);
if (!$subFilter)
{
break;
}
$join = Join::on('this.ID', 'ref.TASK_ID');
if ($this->query->getParam('SEARCH_TASK_ONLY'))
{
$join->where('ref.MESSAGE_ID', 0);
}
else if (
$field === 'COMMENT_SEARCH_INDEX'
|| $this->query->getParam('SEARCH_COMMENT_ONLY')
)
{
$join->where('ref.MESSAGE_ID', '!=', 0);
}
$subQuery = (new Query(TaskTable::getEntity()));
$subQuery->setSelect(['ID']);
$subQuery->where($subFilter);
$subQuery->registerRuntimeField(
TaskQueryBuilder::ALIAS_SEARCH_FULL,
(new ReferenceField(
TaskQueryBuilder::ALIAS_SEARCH_FULL,
SearchIndexTable::getEntity(),
$join
))->configureJoinType('inner')
);
$subSql = $subQuery->getQuery();
$conditionTree->whereIn('ID', new SqlExpression($subSql));
break;
case 'TAG':
if (
empty($val)
)
{
break;
}
if (!is_array($val))
{
$val = [$val];
}
$tags = array_filter(
array_map(
function (string $tag) {
return ($tag ? trim($tag) : false);
},
$val
)
);
$tagsCount = count($tags);
$subQuery = TaskQueryBuilder::createQuery(
TaskQueryBuilder::ALIAS_TASK_TAG,
LabelTable::getEntity()
);
$join = Join::on('this.ID', 'ref.TAG_ID');
$subQuery->setSelect([
TaskQueryBuilder::ALIAS_TASK_TAG . '.TASK_ID',
]);
$subQuery->registerRuntimeField(
TaskQueryBuilder::ALIAS_TASK_TAG,
(new ReferenceField(
TaskQueryBuilder::ALIAS_TASK_TAG,
TaskTagTable::getEntity(),
$join
))->configureJoinType('inner')
);
$subQuery->registerRuntimeField(
'CNT',
new ExpressionField(
'CNT',
'COUNT(%s)',
TaskQueryBuilder::ALIAS_TASK_TAG . '.TASK_ID')
);
$subQuery->whereIn('NAME', $tags);
$subQuery->addGroup(TaskQueryBuilder::ALIAS_TASK_TAG . '.TASK_ID');
$subQuery->having('CNT', $tagsCount);
$conditionTree->whereIn('ID', new SqlExpression($subQuery->getQuery()));
break;
case 'TAG_ID':
if ((int)$val < 0)
{
break;
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_TAG);
$conditionTree->where(TaskQueryBuilder::ALIAS_TASK_TAG.'.TAG_ID', $val);
break;
case 'REAL_STATUS':
$containCompletedSprint =
is_array($val) && in_array(EntityForm::STATE_COMPLETED_IN_ACTIVE_SPRINT, $val, true)
;
$val = $this->removeStatusForActiveSprint($val);
$subFilter = $this->createSubfilter('STATUS', $val, $operation, self::CAST_NUMBER);
if ($containCompletedSprint)
{
$scrumFilter = Query::filter()
->logic('and')
->whereNotNull(TaskQueryBuilder::ALIAS_SCRUM_ITEM.'.ID')
->where('STATUS', Status::COMPLETED);
if ($subFilter)
{
$subFilter = Query::filter()
->logic('or')
->where($subFilter)
->where($scrumFilter);
}
else
{
$subFilter = $scrumFilter;
}
}
if ($subFilter)
{
$conditionTree->where($subFilter);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_SCRUM_ITEM);
}
break;
case 'VIEWED':
$field = new ExpressionField(
'VIEWED_'.mt_rand(1000, 9999),
'
CASE
WHEN
%1$s IS NULL
AND
(%2$s = '.Status::NEW.' OR %2$s = '.Status::PENDING.')
THEN
0
ELSE
1
END
',
[TaskQueryBuilder::ALIAS_TASK_VIEW.'.USER_ID', 'STATUS']
);
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_NUMBER);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_VIEW);
break;
case "STATUS_EXPIRED":
$subFilter = Query::filter()
->logic('and')
->where('DEADLINE', '<', new SqlExpression('NOW()'))
->whereNot('STATUS', Status::SUPPOSEDLY_COMPLETED)
->whereNot('STATUS', Status::COMPLETED)
->where(
Query::filter()
->logic('or')
->whereNot('STATUS', Status::DECLINED)
->whereNot('RESPONSIBLE_ID', $this->behalfUserId)
);
if ($operation === self::OPERATION_NOT)
{
$conditionTree->whereNot($subFilter);
}
else
{
$conditionTree->where($subFilter);
}
break;
case "STATUS_NEW": // viewed by a specified user + status is either new or pending
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_VIEW);
$subFilter = Query::filter()
->logic('and')
->whereNull(TaskQueryBuilder::ALIAS_TASK_VIEW.'.USER_ID')
->whereNot('CREATED_BY', $this->behalfUserId)
->where(
Query::filter()
->logic('or')
->where('STATUS', Status::NEW)
->where('STATUS', Status::PENDING)
);
if ($operation === self::OPERATION_NOT)
{
$conditionTree->whereNot($subFilter);
}
else
{
$conditionTree->where($subFilter);
}
break;
case "STATUS":
$field = new ExpressionField(
"STATUS_".mt_rand(1000, 9999),
'
CASE WHEN
%1$s < ' . $helper->addSecondsToDateTime(Deadline::getDeadlineTimeLimit()) . '
AND %1$s >= NOW()
AND %2$s != '.Status::SUPPOSEDLY_COMPLETED.'
AND %2$s != '.Status::COMPLETED.'
AND (
%2$s != '.Status::DECLINED.'
OR %3$s != '.$this->behalfUserId.'
)
THEN
'.MetaStatus::EXPIRED_SOON.'
WHEN
%1$s < NOW()
AND %2$s != '.Status::SUPPOSEDLY_COMPLETED.'
AND %2$s != '.Status::COMPLETED.'
AND (
%2$s != '.Status::DECLINED.'
OR %3$s != '.$this->behalfUserId.'
)
THEN
'.MetaStatus::EXPIRED.'
WHEN
%5$s IS NULL
AND %4$s != '.$this->behalfUserId.'
AND (
%2$s = '.Status::NEW.'
OR %2$s = '.Status::PENDING.'
)
THEN
'.MetaStatus::UNSEEN.'
ELSE
%2$s
END
',
["DEADLINE", "STATUS", "RESPONSIBLE_ID", "CREATED_BY", TaskQueryBuilder::ALIAS_TASK_VIEW.".USER_ID"]
);
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_NUMBER);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_VIEW);
break;
case 'MARK':
case 'XML_ID':
case 'SITE_ID':
case 'ADD_IN_REPORT':
case 'ALLOW_TIME_TRACKING':
case 'ALLOW_CHANGE_DEADLINE':
case 'MATCH_WORK_TIME':
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_STRING_EQ);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "END_DATE_PLAN":
case "START_DATE_PLAN":
case "DATE_START":
case "DEADLINE":
case "CREATED_DATE":
case "CLOSED_DATE":
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_DATE);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "CHANGED_DATE":
case "ACTIVITY_DATE":
$field = new ExpressionField(
$field."_".mt_rand(1000, 9999),
'
CASE
WHEN %1$s IS NULL
THEN %2$s
ELSE %1$s
END
',
[$field, "CREATED_DATE"]
);
$subFilter = $this->createSubfilter($field, $val, $operation, self::CAST_DATE_STRING);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
break;
case "ACCOMPLICE":
case "AUDITOR":
if (!is_array($val))
{
$val = [$val];
}
$val = array_filter($val);
if (empty($val))
{
break;
}
$memberType = ($field === 'ACCOMPLICE')
? MemberTable::MEMBER_TYPE_ACCOMPLICE
: MemberTable::MEMBER_TYPE_AUDITOR;
$alias = ($field === 'ACCOMPLICE')
? TaskQueryBuilder::ALIAS_TASK_MEMBER_ACCOMPLICE
: TaskQueryBuilder::ALIAS_TASK_MEMBER_AUDITOR;
if ($operation === self::OPERATION_NOT)
{
$conditionTree->where(
Query::filter()
->logic('and')
->where($alias . '.TYPE', $memberType)
->whereNotIn($alias . '.USER_ID', $val)
);
}
else
{
$conditionTree->where(
Query::filter()
->logic('and')
->where($alias . '.TYPE', $memberType)
->whereIn($alias . '.USER_ID', $val)
);
}
$this->registerRuntimeField($alias);
break;
case "PERIOD":
case "ACTIVE":
if (
!$val['START']
&& !$val['END']
)
{
break;
}
$dateStart = null;
if ($val['START'])
{
$dateStart = new DateTime($val['START']);
}
$dateEnd = null;
if ($val['END'])
{
$dateEnd = new DateTime($val['END']);
}
if (
$dateStart
&& $dateEnd
)
{
$subFilter = Query::filter()
->logic('or')
->where(
Query::filter()
->logic('and')
->where('CREATED_DATE', '>=', $dateStart)
->where('CLOSED_DATE', '<=', $dateEnd)
)
->where(
Query::filter()
->logic('and')
->where('CHANGED_DATE', '>=', $dateStart)
->where('CHANGED_DATE', '<=', $dateEnd)
)
->where(
Query::filter()
->logic('and')
->where('CREATED_DATE', '<=', $dateStart)
->whereNull('CLOSED_DATE')
);
}
elseif ($dateStart)
{
$subFilter = Query::filter()
->logic('or')
->where('CREATED_DATE', '>=', $dateStart)
->where('CHANGED_DATE', '>=', $dateStart);
}
elseif ($dateEnd)
{
$subFilter = Query::filter()
->logic('and')
->where('CLOSED_DATE', '<=', $dateStart)
->where('CHANGED_DATE', '<=', $dateEnd);
}
$conditionTree->where($subFilter);
break;
case 'DOER':
$conditionTree->where(
Query::filter()
->logic('or')
->where('RESPONSIBLE_ID', $val)
->where(
Query::filter()
->logic('and')
->where(TaskQueryBuilder::ALIAS_TASK_MEMBER.'.USER_ID', $val)
->where(TaskQueryBuilder::ALIAS_TASK_MEMBER.'.TYPE', MemberTable::MEMBER_TYPE_ACCOMPLICE)
)
);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_MEMBER);
break;
case 'MEMBER':
$conditionTree->where(
Query::filter()
->logic('or')
->where('CREATED_BY', $val)
->where('RESPONSIBLE_ID', $val)
->where(
Query::filter()
->logic('and')
->where(TaskQueryBuilder::ALIAS_TASK_MEMBER.'.USER_ID', $val)
)
);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_MEMBER);
break;
case 'DEPENDS_ON':
if (empty($val))
{
break;
}
if (!is_array($val))
{
$val = [$val];
}
$subQuery = TaskQueryBuilder::createQuery(
TaskQueryBuilder::ALIAS_TASK_DEPENDS,
RelatedTable::getEntity()
);
$subQuery->setSelect(['*']);
$subQuery->whereIn('TASK_ID', $val);
$subQuery->where('DEPENDS_ON_ID', new SqlExpression('%s'));
$subFilter = Query::filter();
$subFilter->whereExpr("EXISTS({$subQuery->getQuery()})", ['ID']);
$conditionTree->where($subFilter);
break;
case 'ONLY_ROOT_TASKS':
if ($val !== 'Y')
{
break;
}
$where = $this->prepareForRootQuery($filter);
unset($where['ONLY_ROOT_TASKS']);
unset($where['SAME_GROUP_PARENT']);
if (
array_key_exists('SAME_GROUP_PARENT', $filter)
&& $filter['SAME_GROUP_PARENT']
)
{
$where['SAME_GROUP_PARENT_EX'] = 'Y';
}
$taskQuery = clone $this->query;
$taskQuery
->skipUfEscape()
->skipTitleEscape()
->setSelect(['ID'])
->setWhere($where)
->setLimit(0)
->setOrder([])
->setGroupBy([])
;
$subQuery = TaskQueryBuilder::build($taskQuery);
$subSql = $subQuery->getQuery();
$subFilter = Query::filter()
->logic('or')
->whereNull('PARENT_ID')
->where('PARENT_ID', 0)
->whereExpr("%2$s NOT IN ({$subSql})", ["GROUP_ID", "PARENT_ID"]);
$conditionTree->where($subFilter);
break;
case 'SUBORDINATE_TASKS':
$subQuery = TaskQueryBuilder::createQuery(TaskQueryBuilder::ALIAS_TASK_MEMBER, MemberTable::getEntity());
$subQuery
->where('TASK_ID', '%s')
->where('USER_ID', $this->behalfUserId);
$subFieldMember = (new ExpressionField(
"SUBORDINATE_TASKS_MEMBER_SUB",
"EXISTS({$subQuery->getQuery()})",
['ID']
))->configureValueType(BooleanField::class);
$this->registerRuntimeField("SUBORDINATE_TASKS_MEMBER_SUB", $subFieldMember);
$subFilter = Query::filter()
->logic('or')
->where('CREATED_BY', $this->behalfUserId)
->where('RESPONSIBLE_ID', $this->behalfUserId)
->where('SUBORDINATE_TASKS_MEMBER_SUB', true);
$subQuerySubordinate = CTasks::GetSubordinateSql('', ['USER_ID' => $this->behalfUserId], ['USE_PLACEHOLDERS' => true]);
if ($subQuerySubordinate)
{
$subFieldSubordinate = (new ExpressionField(
"SUBORDINATE_TASKS_SUB",
"EXISTS({$subQuerySubordinate})",
["RESPONSIBLE_ID", "CREATED_BY", "ID"]
))->configureValueType(BooleanField::class);
$this->registerRuntimeField("SUBORDINATE_TASKS_SUB", $subFieldSubordinate);
$subFilter->where('SUBORDINATE_TASKS_SUB', true);
}
$conditionTree->where($subFilter);
break;
case 'OVERDUED':
if ($val !== 'Y')
{
break;
}
$conditionTree->where(
Query::filter()
->logic('and')
->whereNotNull('CLOSED_DATE')
->whereNotNull('DEADLINE')
->whereColumn('DEADLINE', '<', 'CLOSED_DATE')
);
break;
case 'SAME_GROUP_PARENT':
if (
$val !== 'Y'
|| array_key_exists("ONLY_ROOT_TASKS", $filter)
)
{
break;
}
$subQuery = TaskQueryBuilder::createQuery(TaskQueryBuilder::ALIAS_TASK, TaskTable::getEntity());
$subQuery
->where('ID', new SqlExpression('%1$s'))
->where(
Query::filter()
->logic('or')
->where('GROUP_ID', new SqlExpression('%2$s'))
->where(
Query::filter()
->logic('and')
->whereNull('GROUP_ID')
->whereExpr('%%2$s is null', [])
)
->where(
Query::filter()
->logic('and')
->whereNull('GROUP_ID')
->whereExpr('%%2$s = 0', [])
)
->where(
Query::filter()
->logic('and')
->where('GROUP_ID', 0)
->whereExpr('%%2$s is null', [])
)
);
$conditionTree->whereExpr("EXISTS({$subQuery->getQuery()})", ['PARENT_ID', 'GROUP_ID']);
break;
case 'SAME_GROUP_PARENT_EX':
if ($val !== 'Y')
{
break;
}
$subFilter = Query::filter()
->logic('OR')
->where('GROUP_ID', new SqlExpression('%1$s'))
->where(
Query::filter()
->logic('and')
->whereNull('GROUP_ID')
->whereExpr('%%1$s is null', [])
)
->where(
Query::filter()
->logic('and')
->whereNull('GROUP_ID')
->whereExpr('%%1$s = 0', [])
)
->where(
Query::filter()
->logic('and')
->where('GROUP_ID', 0)
->whereExpr('%%1$s is null', [])
);
$conditionTree->where($subFilter);
break;
case 'DEPARTMENT_ID':
$subSql = CTasks::GetDeparmentSql($val, "", [], ['USE_PLACEHOLDERS' => true]);
$conditionTree->whereExpr("EXISTS({$subSql})", ["RESPONSIBLE_ID", "CREATED_BY", "ID"]);
break;
case 'CHECK_PERMISSIONS':
break;
case 'FAVORITE':
$subFilter = $this->createSubfilter(TaskQueryBuilder::ALIAS_TASK_FAVORITE.".TASK_ID", $val, $operation, self::CAST_LEFT_EXIST);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_FAVORITE);
break;
case 'SORTING':
$subFilter = $this->createSubfilter(TaskQueryBuilder::ALIAS_TASK_SORT.".TASK_ID", $val, $operation, self::CAST_LEFT_EXIST);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_SORT);
break;
case 'STAGES_ID':
$subFilter = $this->createSubfilter(TaskQueryBuilder::ALIAS_TASK_STAGES.".STAGE_ID", $val, $operation, self::CAST_NUMBER);
if ($subFilter)
{
$conditionTree->where($subFilter);
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_STAGES);
break;
case 'PROJECT_NEW_COMMENTS':
case 'PROJECT_EXPIRED':
$subQuery = TaskQueryBuilder::createQuery(TaskQueryBuilder::ALIAS_TASK_COUNTERS, CounterTable::getEntity());
$subQuery
->where('GROUP_ID', new SqlExpression('%1$s'))
->where('TASK_ID', new SqlExpression('%2$s'))
->where('USER_ID', $this->behalfUserId);
if ($field === 'PROJECT_NEW_COMMENTS')
{
$typesIn = array_merge(
[CounterDictionary::COUNTER_GROUP_COMMENTS],
CounterDictionary::MAP_MUTED_COMMENTS
);
$subQuery->whereIn('TYPE', CounterDictionary::MAP_COMMENTS);
}
else
{
$typesIn = array_merge(
[CounterDictionary::COUNTER_GROUP_EXPIRED],
CounterDictionary::MAP_MUTED_EXPIRED
);
$subQuery->whereIn('TYPE', CounterDictionary::MAP_EXPIRED);
}
$subFilter = Query::filter()
->logic('and')
->whereNotNull(TaskQueryBuilder::ALIAS_TASK_COUNTERS.'.ID')
->whereIn(TaskQueryBuilder::ALIAS_TASK_COUNTERS.'.TYPE', $typesIn)
->whereExpr("NOT EXISTS({$subQuery->getQuery()})", ["GROUP_ID", "ID"]);
$conditionTree->where($subFilter);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_COUNTERS);
break;
case 'WITH_COMMENT_COUNTERS':
$types = array_values(CounterDictionary::MAP_COMMENTS);
$conditionTree->where(
Query::filter()
->whereNotNull(TaskQueryBuilder::ALIAS_TASK_COUNTERS.'.ID')
->whereIn(TaskQueryBuilder::ALIAS_TASK_COUNTERS.'.TYPE', $types)
);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_COUNTERS);
break;
case 'WITH_NEW_COMMENTS':
if (!Loader::includeModule('forum'))
{
throw new LoadModuleException();
}
$subFilter = Query::filter()
->logic('and')
->where(
Query::filter()
->logic('or')
->where(
Query::filter()
->logic('and')
->whereNotNull(TaskQueryBuilder::ALIAS_TASK_VIEW.'.VIEWED_DATE')
->whereColumn(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.POST_DATE', '>', TaskQueryBuilder::ALIAS_TASK_VIEW.'.VIEWED_DATE')
)
->where(
Query::filter()
->logic('and')
->whereNull(TaskQueryBuilder::ALIAS_TASK_VIEW.'.VIEWED_DATE')
->whereColumn(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.POST_DATE', '>=', 'CREATED_DATE')
)
)
->where(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.NEW_TOPIC', 'N')
->where(
Query::filter()
->logic('or')
->where(
Query::filter()
->logic('and')
->whereNot(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.AUTHOR_ID', $this->behalfUserId)
->where(
Query::filter()
->logic('or')
->whereNull(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.UF_TASK_COMMENT_TYPE')
->whereNot(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.UF_TASK_COMMENT_TYPE', Comment::TYPE_EXPIRED)
)
)
->where(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.UF_TASK_COMMENT_TYPE', Comment::TYPE_EXPIRED_SOON)
);
$startCounterDate = COption::GetOptionString("tasks", "tasksDropCommentCounters", null);
if ($startCounterDate)
{
$subFilter->where(TaskQueryBuilder::ALIAS_FORUM_MESSAGE.'.POST_DATE', '>', DateTime::tryParse($startCounterDate, 'Y-m-d H:i:s'));
}
$conditionTree->where($subFilter);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_FORUM_MESSAGE);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_VIEW);
break;
case 'IS_MUTED':
case 'IS_PINNED':
case 'IS_PINNED_IN_GROUP':
$optionMap = [
'IS_MUTED' => Option::MUTED,
'IS_PINNED' => Option::PINNED,
'IS_PINNED_IN_GROUP' => Option::PINNED_IN_GROUP,
];
$subQuery = TaskQueryBuilder::createQuery(TaskQueryBuilder::ALIAS_TASK_OPTION, UserOptionTable::getEntity());
$subQuery->addSelect('TASK_ID');
$subQuery->where('OPTION_CODE', $optionMap[$key]);
$subQuery->where('TASK_ID', new SqlExpression('%1$s'));
$subQuery->where('USER_ID', $this->behalfUserId);
if ($val === 'N')
{
$conditionTree->whereExpr('%1$s NOT IN ('. $subQuery->getQuery() .')', ["ID"]);
}
else
{
$conditionTree->whereExpr('%1$s IN ('. $subQuery->getQuery() .')', ["ID"]);
}
break;
case 'SCRUM_TASKS':
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_SCRUM_ITEM_B);
break;
case 'STORY_POINTS':
if ($val === 'Y')
{
$subSql = "NULLIF(%s, '') IS NOT NULL";
}
else
{
$subSql = "NULLIF(%s, '') IS NULL";
}
$conditionTree->whereExpr($subSql, [TaskQueryBuilder::ALIAS_SCRUM_ITEM_C.".STORY_POINTS"]);
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_SCRUM_ITEM_C);
break;
case 'EPIC':
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_SCRUM_ITEM_D);
break;
case 'SCENARIO_NAME':
$filter = $this->query->getWhere();
$scenario = (array_key_exists('SCENARIO_NAME', $filter))
? $filter['SCENARIO_NAME']
: null;
if ($scenario === null)
{
break;
}
// filter by valid values
if (is_array($scenario))
{
$scenario = ScenarioTable::filterByValidScenarios($scenario);
if (empty($scenario))
{
break;
}
}
else
{
if (!ScenarioTable::isValidScenario($scenario))
{
break;
}
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_TASK_SCENARIO);
if (is_array($scenario))
{
$conditionTree->whereIn(TaskQueryBuilder::ALIAS_TASK_SCENARIO.'.SCENARIO', $scenario);
}
else
{
$conditionTree->where(TaskQueryBuilder::ALIAS_TASK_SCENARIO.'.SCENARIO', $scenario);
}
break;
case 'IM_CHAT_ID':
case 'IM_CHAT_CHAT_ID':
if (!Loader::includeModule('im'))
{
break;
}
$fieldMap = [
'IM_CHAT_ID' => TaskQueryBuilder::ALIAS_CHAT_TASK.'.ID',
'IM_CHAT_CHAT_ID' => TaskQueryBuilder::ALIAS_CHAT_TASK.'.CHAT_ID',
];
$subFilter = $this->createSubfilter($fieldMap[$field], $val, $operation, self::CAST_NUMBER);
if (!$subFilter)
{
break;
}
$this->registerRuntimeField(TaskQueryBuilder::ALIAS_CHAT_TASK);
$conditionTree->where($subFilter);
break;
default:
if (preg_match('/^UF_/', $field))
{
$conditions = $this->translateUfFilter($field, $operation, $val, $this->query->needUfEscape());
$subFilter = Query::filter();
if (count($conditions) > 1)
{
$subFilter->logic('or');
}
foreach ($conditions as $condition)
{
$subFilter->addCondition($condition);
}
$conditionTree->where($subFilter);
}
}
}
if (!$conditionTree->hasConditions())
{
return null;
}
$conditions = $conditionTree->getConditions();
if (
$logicOr
&& count($conditions) < $filterCount
)
{
// we've got OR logic and one or more empty filters
return null;
}
return $conditionTree;
}