static function GetSqlByFilter($arFilter, $userID, $sAliasPrefix, $bGetZombie,
$bMembersTableJoined = false, $params = [])
{
global $DB;
$bFullJoin = null;
if (!is_array($arFilter))
{
throw new TasksException(
'GetSqlByFilter: expected array, but something other given: ' . var_export($arFilter, true)
);
}
if (
array_key_exists('ONLY_ROOT_TASKS', $arFilter)
&& $arFilter['ONLY_ROOT_TASKS'] === 'Y'
&& (
array_key_exists('FULL_SEARCH_INDEX', $arFilter)
|| array_key_exists('COMMENT_SEARCH_INDEX', $arFilter)
)
)
{
unset($arFilter['ONLY_ROOT_TASKS']);
}
$logicStr = ' AND ';
if (isset($arFilter['::LOGIC']))
{
switch ($arFilter['::LOGIC'])
{
case 'AND':
$logicStr = ' AND ';
break;
case 'OR':
$logicStr = ' OR ';
break;
default:
throw new TasksException('Unknown logic in filter');
}
}
$arSqlSearch = [];
$targetUserId = isset($params['TARGET_USER_ID']) ? $params['TARGET_USER_ID'] : $userID;
foreach ($arFilter as $key => $val)
{
// Skip meta-key
if ($key === '::LOGIC')
{
continue;
}
// Skip markers
if ($key === '::MARKERS')
{
continue;
}
// Subfilter?
if (static::isSubFilterKey($key))
{
$arSqlSearch[] = self::GetSqlByFilter($val, $userID, $sAliasPrefix, $bGetZombie, $bMembersTableJoined,
$params);
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')
{
$arSqlSearch = array_merge(
$arSqlSearch,
self::getSqlForTimestamps($key, $val, $userID, $sAliasPrefix, $bGetZombie)
);
continue;
}
$res = CTasks::MkOperationFilter($key);
$key = $res["FIELD"];
$cOperationType = $res["OPERATION"];
$key = mb_strtoupper($key);
switch ($key)
{
case 'META::ID_OR_NAME':
$arSqlSearch[] = " (" .
$sAliasPrefix .
"T.ID = '" .
intval($val) .
"' OR (UPPER(" .
$sAliasPrefix .
"T.TITLE) LIKE UPPER('%" .
$DB->ForSqlLike($val) .
"%')) ) ";
break;
//case "DURATION_PLAN": // temporal
case "PARENT_ID":
case "GROUP_ID":
case "STATUS_CHANGED_BY":
case "FORUM_TOPIC_ID":
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
$val,
"number",
$bFullJoin,
$cOperationType
);
break;
case "ID":
case "PRIORITY":
case "CREATED_BY":
case "RESPONSIBLE_ID":
case "STAGE_ID":
case 'TIME_ESTIMATE':
case 'FORKED_BY_TEMPLATE_ID':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
$val,
"number_wo_nulls",
$bFullJoin,
$cOperationType
);
break;
case "REFERENCE:RESPONSIBLE_ID":
$arSqlSearch[] = CTasks::FilterCreate(
"{$sAliasPrefix}T.RESPONSIBLE_ID",
"{$sAliasPrefix}T.{$val}",
'reference',
$bFullJoin,
$cOperationType
);
break;
case "REFERENCE:START_DATE_PLAN":
$key = 'START_DATE_PLAN';
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
$val,
'reference',
$bFullJoin,
$cOperationType
);
break;
case 'META:GROUP_ID_IS_NULL_OR_ZERO':
$key = 'GROUP_ID';
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
$val,
"null_or_zero",
$bFullJoin,
$cOperationType,
false
);
break;
case 'META:PARENT_ID_OR_NULL':
if ((array)$val)
{
$arSqlSearch[] = '(T.PARENT_ID IN ('
. join(', ', array_map('intval', (array)$val))
. ') OR T.PARENT_ID IS NULL)';
}
break;
case "CHANGED_BY":
$arSqlSearch[] = CTasks::FilterCreate(
"CASE WHEN " .
$sAliasPrefix .
"T." .
$key .
" IS NULL THEN " .
$sAliasPrefix .
"T.CREATED_BY ELSE " .
$sAliasPrefix .
"T." .
$key .
" END",
$val,
"number",
$bFullJoin,
$cOperationType
);
break;
case 'GUID':
case 'TITLE':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
$val,
"string",
$bFullJoin,
$cOperationType
);
break;
case 'FULL_SEARCH_INDEX':
case 'COMMENT_SEARCH_INDEX':
$isComment = $key === 'COMMENT_SEARCH_INDEX';
$tableName = SearchIndexTable::getTableName();
$tableAlias = $sAliasPrefix . ($isComment ? 'TSIC' : 'TSIF');
$columnName = "{$tableAlias}.SEARCH_INDEX";
$where = self::FilterCreate($columnName, $val, 'fulltext', $bFullJoin, $cOperationType);
$filterParams = ($params['FILTER_PARAMS'] ?? null);
$searchTaskOnly =
isset($filterParams['SEARCH_TASK_ONLY'])
&& $filterParams['SEARCH_TASK_ONLY'] === 'Y'
;
$searchCommentOnly =
isset($filterParams['SEARCH_COMMENT_ONLY'])
&& $filterParams['SEARCH_COMMENT_ONLY'] === 'Y'
;
$join = "";
if ($searchTaskOnly)
{
$join = "AND {$tableAlias}.MESSAGE_ID = 0";
}
elseif ($isComment || $searchCommentOnly)
{
$join = "AND {$tableAlias}.MESSAGE_ID != 0";
}
$innerQuery = "
"
. "SELECT {$sAliasPrefix}ST.ID"
. "
"
. "FROM b_tasks {$sAliasPrefix}ST"
. "
"
. "INNER JOIN {$tableName} {$tableAlias} ON {$tableAlias}.TASK_ID = {$sAliasPrefix}ST.ID {$join}"
. "
"
. "WHERE {$where}";
$arSqlSearch[] = "({$sAliasPrefix}T.ID IN ({$innerQuery}))";
break;
case 'TAG':
if (!is_array($val))
{
$val = [$val];
}
$tags = array_filter(
array_map(
static function ($tag) use ($DB) {
return ($tag ? $DB->ForSql($tag) : false);
},
$val
)
);
$tagsCount = count($tags);
if ($tagsCount)
{
$tags = "('" . implode("','", $tags) . "')";
$arSqlSearch[] = trim("
{$sAliasPrefix}T.ID IN (
SELECT TT.TASK_ID
FROM (
SELECT TASK_ID, COUNT(TASK_ID) AS CNT
FROM b_tasks_label
INNER JOIN b_tasks_task_tag bttt on bttt.TAG_ID = b_tasks_label.ID
WHERE NAME IN {$tags}
GROUP BY TASK_ID
HAVING CNT = {$tagsCount}
) TT
)
");
}
break;
case 'TAG_ID':
if (!is_array($val))
{
$val = [$val];
}
$tags = array_filter(
array_map(
static function ($tag) use ($DB) {
return ($tag ? $DB->ForSql($tag) : false);
},
$val
)
);
$tagsCount = count($tags);
if ($tagsCount)
{
$tags = "('" . implode("','", $tags) . "')";
$arSqlSearch[] = "
{$sAliasPrefix}T.ID IN (
SELECT TASK_TAGS.ID FROM b_tasks AS TASK_TAGS
INNER JOIN b_tasks_task_tag BTT on BTT.TASK_ID = TASK_TAGS.ID
WHERE BTT.TAG_ID IN $tags
)
";
}
break;
case 'REAL_STATUS':
$val = self::removeStatusValueForActiveSprint($val);
$realStatusFilter = CTasks::FilterCreate(
$sAliasPrefix . "T.STATUS",
$val,
"number",
$bFullJoin,
$cOperationType
);
if (self::containCompletedInActiveSprintStatus($arFilter))
{
$realStatusFilter = $realStatusFilter
? $realStatusFilter . " OR ({$sAliasPrefix}TSI.ID IS NOT NULL AND (T.STATUS = '5'))"
: "({$sAliasPrefix}TSI.ID IS NOT NULL AND (T.STATUS = '5'))";
}
$arSqlSearch[] = $realStatusFilter;
break;
case 'DEADLINE_COUNTED':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T.DEADLINE_COUNTED",
$val,
"number_wo_nulls",
$bFullJoin,
$cOperationType
);
break;
case 'VIEWED':
$arSqlSearch[] = CTasks::FilterCreate(
"
CASE
WHEN
" . $sAliasPrefix . "TV.USER_ID IS NULL
AND
(" . $sAliasPrefix . "T.STATUS = 1 OR " . $sAliasPrefix . "T.STATUS = 2)
THEN
'0'
ELSE
'1'
END
",
$val,
"number",
$bFullJoin,
$cOperationType
);
break;
case "STATUS_EXPIRED": // expired: deadline in past and
$arSqlSearch[] = ($cOperationType == 'N' ? 'not' : '') .
"(" .
$sAliasPrefix .
"T.DEADLINE < " .
$DB->CurrentTimeFunction() .
" AND " .
$sAliasPrefix .
"T.STATUS != '4' AND " .
$sAliasPrefix .
"T.STATUS != '5' AND (" .
$sAliasPrefix .
"T.STATUS != '7' OR " .
$sAliasPrefix .
"T.RESPONSIBLE_ID != " .
$userID .
"))";
break;
case "STATUS_NEW": // viewed by a specified user + status is either new or pending
$arSqlSearch[] = ($cOperationType == 'N' ? 'not' : '') . "(
" . $sAliasPrefix . "TV.USER_ID IS NULL
AND
" . $sAliasPrefix . "T.CREATED_BY != " . $userID . "
AND
(" . $sAliasPrefix . "T.STATUS = 1 OR " . $sAliasPrefix . "T.STATUS = 2)
)";
$bFullJoin = true; // join TV
break;
case "STATUS":
$arSqlSearch[] = CTasks::FilterCreate(
"
CASE
WHEN
" .
$sAliasPrefix .
"T.DEADLINE < DATE_ADD(" .
$DB->CurrentTimeFunction() .
", INTERVAL " .
CounterDeadline::getDeadlineTimeLimit() .
" SECOND)
AND " .
$sAliasPrefix .
"T.DEADLINE >= " .
$DB->CurrentTimeFunction() .
"
AND " .
$sAliasPrefix .
"T.STATUS != '4'
AND " .
$sAliasPrefix .
"T.STATUS != '5'
AND (
" .
$sAliasPrefix .
"T.STATUS != '7'
OR " .
$sAliasPrefix .
"T.RESPONSIBLE_ID != " .
intval($userID) .
"
)
THEN
'-3'
WHEN
" .
$sAliasPrefix .
"T.DEADLINE < " .
$DB->CurrentTimeFunction() .
" AND " .
$sAliasPrefix .
"T.STATUS != '4' AND " .
$sAliasPrefix .
"T.STATUS != '5' AND (" .
$sAliasPrefix .
"T.STATUS != '7' OR " .
$sAliasPrefix .
"T.RESPONSIBLE_ID != " .
$userID .
")
THEN
'-1'
WHEN
" .
$sAliasPrefix .
"TV.USER_ID IS NULL
AND
" .
$sAliasPrefix .
"T.CREATED_BY != " .
$userID .
"
AND
(" .
$sAliasPrefix .
"T.STATUS = 1 OR " .
$sAliasPrefix .
"T.STATUS = 2)
THEN
'-2'
ELSE
" .
$sAliasPrefix .
"T.STATUS
END
",
$val,
"number",
$bFullJoin,
$cOperationType
);
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':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
$val,
"string_equal",
$bFullJoin,
$cOperationType
);
break;
case "END_DATE_PLAN":
case "START_DATE_PLAN":
case "DATE_START":
case "DEADLINE":
case "CREATED_DATE":
case "CLOSED_DATE":
if (($val === false) || ($val === ''))
{
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
$val,
"date",
$bFullJoin,
$cOperationType,
$bSkipEmpty = false
);
}
else
{
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "T." . $key,
Db::charToDateFunction($val),
"date",
$bFullJoin,
$cOperationType
);
}
break;
case "CHANGED_DATE":
case "ACTIVITY_DATE":
$fname = "CASE WHEN {$sAliasPrefix}T.{$key} IS NULL"
. " THEN {$sAliasPrefix}T.CREATED_DATE"
. " ELSE {$sAliasPrefix}T.{$key} END";
$arSqlSearch[] = CTasks::FilterCreate(
$fname,
Db::charToDateFunction($val),
"date",
$bFullJoin,
$cOperationType
);
break;
case "ACCOMPLICE":
if (!is_array($val))
{
$val = [$val];
}
$val = array_filter($val);
$arConds = [];
if ($bMembersTableJoined)
{
if ($cOperationType !== 'N')
{
foreach ($val as $id)
{
$arConds[] = "(" . $sAliasPrefix . "TM.USER_ID = '" . intval($id) . "')";
}
if (!empty($arConds))
{
$arSqlSearch[] = '('
. $sAliasPrefix
. "TM.TYPE = 'A' AND ("
. implode(" OR ", $arConds)
. '))';
}
}
else
{
foreach ($val as $id)
{
$arConds[] = "(" . $sAliasPrefix . "TM.USER_ID != '" . intval($id) . "')";
}
if (!empty($arConds))
{
$arSqlSearch[] = '(' .
$sAliasPrefix .
"TM.TYPE = 'A' AND (" .
implode(" AND ", $arConds) .
'))';
}
}
}
else
{
foreach ($val as $id)
{
$arConds[] = "(" . $sAliasPrefix . "TM.USER_ID = '" . intval($id) . "')";
}
if (!empty($arConds))
{
$arSqlSearch[] = ($cOperationType !== 'N' ? 'EXISTS' : 'NOT EXISTS') . "(
SELECT
'x'
FROM
b_tasks_member " . $sAliasPrefix . "TM
WHERE
(" . implode(" OR ", $arConds) . ")
AND
" . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID
AND
" . $sAliasPrefix . "TM.TYPE = 'A'
)";
}
}
break;
case "PERIOD":
case "ACTIVE":
if (is_array($val) && ($val["START"] || $val["END"]))
{
$strDateStart = $strDateEnd = false;
if (MakeTimeStamp($val['START']) > 0)
{
$strDateStart = Db::charToDateFunction(
$DB->ForSql(
CDatabase::FormatDate(
$val['START'],
FORMAT_DATETIME
)
)
);
}
if (MakeTimeStamp($val['END']))
{
$strDateEnd = Db::charToDateFunction(
$DB->ForSql(
CDatabase::FormatDate(
$val['END'],
FORMAT_DATETIME
)
)
);
}
if (($strDateStart !== false) && ($strDateEnd !== false))
{
$arSqlSearch[] = "(
(T.CREATED_DATE >= $strDateStart AND T.CLOSED_DATE <= $strDateEnd)
OR
(T.CHANGED_DATE >= $strDateStart AND T.CHANGED_DATE <= $strDateEnd)
OR
(T.CREATED_DATE <= $strDateStart AND T.CLOSED_DATE IS NULL)
)";
}
elseif (($strDateStart !== false) && ($strDateEnd === false))
{
$arSqlSearch[] = "(
(T.CREATED_DATE >= $strDateStart)
OR
(T.CHANGED_DATE >= $strDateStart)
)";
}
elseif (($strDateStart === false) && ($strDateEnd !== false))
{
$arSqlSearch[] = "(
(T.CLOSED_DATE <= $strDateEnd)
(T.CHANGED_DATE <= $strDateEnd)
)";
}
}
break;
case "AUDITOR":
if (!is_array($val))
{
$val = [$val];
}
$val = array_filter($val);
$arConds = [];
if ($bMembersTableJoined)
{
if ($cOperationType !== 'N')
{
foreach ($val as $id)
{
$arConds[] = "(" . $sAliasPrefix . "TM.USER_ID = '" . intval($id) . "')";
}
if (!empty($arConds))
{
$arSqlSearch[] = '('
. $sAliasPrefix
. "TM.TYPE = 'U' AND ("
. implode(" OR ", $arConds)
. '))';
}
}
else
{
foreach ($val as $id)
{
$arConds[] = "(" . $sAliasPrefix . "TM.USER_ID != '" . intval($id) . "')";
}
if (!empty($arConds))
{
$arSqlSearch[] = '(' .
$sAliasPrefix .
"TM.TYPE = 'U' AND (" .
implode(" AND ", $arConds) .
'))';
}
}
}
else
{
foreach ($val as $id)
{
$arConds[] = "(" . $sAliasPrefix . "TM.USER_ID = '" . intval($id) . "')";
}
if (!empty($arConds))
{
$arSqlSearch[] = ($cOperationType !== 'N' ? 'EXISTS' : 'NOT EXISTS') . "(
SELECT
'x'
FROM
b_tasks_member " . $sAliasPrefix . "TM
WHERE
(" . implode(" OR ", $arConds) . ")
AND
" . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID
AND
" . $sAliasPrefix . "TM.TYPE = 'U'
)";
}
}
break;
case "DOER":
$val = intval($val);
$arSqlSearch[] = "(
" . $sAliasPrefix . "T.RESPONSIBLE_ID = " . $val . "
OR
EXISTS(
SELECT 'x'
FROM
b_tasks_member " . $sAliasPrefix . "TM
WHERE
" . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID
AND
" . $sAliasPrefix . "TM.USER_ID = '" . $val . "'
AND
" . $sAliasPrefix . "TM.TYPE = 'A'
)
)";
break;
case "MEMBER":
$val = intval($val);
$arSqlSearch[] = "(
" . $sAliasPrefix . "T.CREATED_BY = " . intval($val) . "
OR
" . $sAliasPrefix . "T.RESPONSIBLE_ID = " . intval($val) . "
OR
EXISTS(
SELECT 'x' FROM b_tasks_member " . $sAliasPrefix . "TM
WHERE
" . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID
AND
" . $sAliasPrefix . "TM.USER_ID = '" . $val . "'
)
)";
break;
case "DEPENDS_ON":
if (!is_array($val))
{
$val = [$val];
}
$arConds = [];
foreach ($val as $id)
{
if ($id)
{
$arConds[] = "(" . $sAliasPrefix . "TD.TASK_ID = '" . intval($id) . "')";
}
}
if (sizeof($arConds))
{
$arSqlSearch[] = "EXISTS(
SELECT
'x'
FROM
b_tasks_dependence " . $sAliasPrefix . "TD
WHERE
(" . implode(" OR ", $arConds) . ")
AND
" . $sAliasPrefix . "TD.DEPENDS_ON_ID = " . $sAliasPrefix . "T.ID
)";
}
break;
case "ONLY_ROOT_TASKS":
if ($val === 'Y')
{
$arSqlSearch[] = "("
. "{$sAliasPrefix}T.PARENT_ID IS NULL OR "
. "{$sAliasPrefix}T.PARENT_ID = '0' OR "
. "{$sAliasPrefix}T.PARENT_ID NOT IN ("
. CTasks::GetRootSubQuery($arFilter, $sAliasPrefix, $params)
. "))";
}
break;
case "SUBORDINATE_TASKS":
if ($val == "Y")
{
$arSubSqlSearch = [
$sAliasPrefix . "T.CREATED_BY = " . $targetUserId,
$sAliasPrefix . "T.RESPONSIBLE_ID = " . $targetUserId,
"EXISTS(
SELECT 'x'
FROM
b_tasks_member " . $sAliasPrefix . "TM
WHERE
" . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID
AND
" . $sAliasPrefix . "TM.USER_ID = " . $targetUserId . "
)",
];
// subordinate check
if ($strSql = CTasks::GetSubordinateSql($sAliasPrefix, ['USER_ID' => $targetUserId]))
{
$arSubSqlSearch[] = "EXISTS(" . $strSql . ")";
}
$arSqlSearch[] = "(" . implode(" OR ", $arSubSqlSearch) . ")";
}
break;
case "OVERDUED":
if ($val == "Y")
{
$arSqlSearch[] = $sAliasPrefix .
"T.CLOSED_DATE IS NOT NULL AND " .
$sAliasPrefix .
"T.DEADLINE IS NOT NULL AND " .
$sAliasPrefix .
"T.DEADLINE < CLOSED_DATE";
}
break;
case "SAME_GROUP_PARENT":
if ($val == "Y" && !array_key_exists("ONLY_ROOT_TASKS", $arFilter))
{
$arSqlSearch[] = "EXISTS(
SELECT
'x'
FROM
b_tasks " . $sAliasPrefix . "PT
WHERE
" . $sAliasPrefix . "T.PARENT_ID = " . $sAliasPrefix . "PT.ID
AND
(" . $sAliasPrefix . "PT.GROUP_ID = " . $sAliasPrefix . "T.GROUP_ID
OR (" . $sAliasPrefix . "PT.GROUP_ID IS NULL AND " . $sAliasPrefix . "T.GROUP_ID IS NULL)
OR (" . $sAliasPrefix . "PT.GROUP_ID = 0 AND " . $sAliasPrefix . "T.GROUP_ID IS NULL)
OR (" . $sAliasPrefix . "PT.GROUP_ID IS NULL AND " . $sAliasPrefix . "T.GROUP_ID = 0)
)
)";
}
break;
case "DEPARTMENT_ID":
if ($strSql = CTasks::GetDeparmentSql($val, $sAliasPrefix))
{
$arSqlSearch[] = "EXISTS(" . $strSql . ")";
}
break;
case 'CHECK_PERMISSIONS':
break;
case 'FAVORITE':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "FVT.TASK_ID",
$val,
"left_existence",
$bFullJoin,
$cOperationType,
false
);
break;
case 'SORTING':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "SRT.TASK_ID",
$val,
"left_existence",
$bFullJoin,
$cOperationType,
false
);
break;
case 'STAGES_ID':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "STG.STAGE_ID",
$val,
"number",
$bFullJoin,
$cOperationType,
false
);
break;
case 'PROJECT_EXPIRED':
$typesIn = array_merge(
[CounterCounterDictionary::COUNTER_GROUP_EXPIRED],
CounterCounterDictionary::MAP_MUTED_EXPIRED
);
$typesIn = "('" . implode("', '", $typesIn) . "')";
$typesEx = "('" . implode("', '", CounterCounterDictionary::MAP_EXPIRED) . "')";
$arSqlSearch[] = "
{$sAliasPrefix}TSC.ID IS NOT NULL
AND {$sAliasPrefix}TSC.TYPE IN {$typesIn}
AND NOT EXISTS (
SELECT 1
FROM b_tasks_scorer
WHERE
GROUP_ID = {$sAliasPrefix}T.GROUP_ID
AND TASK_ID = {$sAliasPrefix}T.ID
AND USER_ID = {$userID}
AND TYPE IN {$typesEx}
)
";
break;
case 'PROJECT_NEW_COMMENTS':
$typesIn = array_merge(
[CounterCounterDictionary::COUNTER_GROUP_COMMENTS],
CounterCounterDictionary::MAP_MUTED_COMMENTS
);
$typesIn = "('" . implode("', '", $typesIn) . "')";
$typesEx = "('" . implode("', '", CounterCounterDictionary::MAP_COMMENTS) . "')";
$arSqlSearch[] = "
{$sAliasPrefix}TSC.ID IS NOT NULL
AND {$sAliasPrefix}TSC.TYPE IN {$typesIn}
AND NOT EXISTS (
SELECT 1
FROM b_tasks_scorer
WHERE
GROUP_ID = {$sAliasPrefix}T.GROUP_ID
AND TASK_ID = {$sAliasPrefix}T.ID
AND USER_ID = {$userID}
AND TYPE IN {$typesEx}
)
";
break;
case 'WITH_COMMENT_COUNTERS':
$types = array_merge(
array_values(CounterCounterDictionary::MAP_COMMENTS),
array_values(CounterCounterDictionary::MAP_MUTED_COMMENTS)
);
$types = "('" . implode("', '", $types) . "')";
$arSqlSearch[] = "{$sAliasPrefix}TSC.ID IS NOT NULL AND {$sAliasPrefix}TSC.TYPE IN {$types}";
break;
case 'WITH_NEW_COMMENTS':
$expiredCommentType = Comment::TYPE_EXPIRED;
$expiredSoonCommentType = Comment::TYPE_EXPIRED_SOON;
$qr = "
(
({$sAliasPrefix}TV.VIEWED_DATE IS NOT NULL AND {$sAliasPrefix}FM.POST_DATE > {$sAliasPrefix}TV.VIEWED_DATE)
OR ({$sAliasPrefix}TV.VIEWED_DATE IS NULL AND {$sAliasPrefix}FM.POST_DATE >= {$sAliasPrefix}T.CREATED_DATE)
)
AND {$sAliasPrefix}FM.NEW_TOPIC = 'N'
AND (
(
{$sAliasPrefix}FM.AUTHOR_ID != {$targetUserId}
AND (
{$sAliasPrefix}BUF_FM.UF_TASK_COMMENT_TYPE IS NULL
OR {$sAliasPrefix}BUF_FM.UF_TASK_COMMENT_TYPE != {$expiredCommentType}
)
)
OR {$sAliasPrefix}BUF_FM.UF_TASK_COMMENT_TYPE = {$expiredSoonCommentType}
)
";
$startCounterDate = COption::GetOptionString("tasks", "tasksDropCommentCounters", null);
if ($startCounterDate)
{
$qr .= " AND {$sAliasPrefix}FM.POST_DATE > '{$startCounterDate}'";
}
$arSqlSearch[] = $qr;
break;
case 'IS_MUTED':
case 'IS_PINNED':
case 'IS_PINNED_IN_GROUP':
$optionMap = [
'IS_MUTED' => UserOptionOption::MUTED,
'IS_PINNED' => UserOptionOption::PINNED,
'IS_PINNED_IN_GROUP' => UserOptionOption::PINNED_IN_GROUP,
];
$arSqlSearch[] = " {$sAliasPrefix}T.ID " . ($val === 'N' ? 'NOT ' : '')
. UserOption::getFilterSql($targetUserId, $optionMap[$key], $sAliasPrefix);
break;
case 'SCENARIO_NAME':
$arSqlSearch[] = CTasks::FilterCreate(
$sAliasPrefix . "SCR.SCENARIO",
$val,
"string_equal",
$bFullJoin,
$cOperationType,
false
);
break;
default:
if ((mb_strlen($key) >= 3) && (mb_substr($key, 0, 3) === 'UF_'))
{
; // It's OK, this fields will be processed by UserFieldManager
}
else
{
$extraData = '';
if (isset($_POST['action']) && ($_POST['action'] === 'group_action'))
{
$extraData = '; Extra data: ' .
serialize([$_POST['arFilter'], $_POST['action'], $arFilter]) .
'';
}
else
{
$extraData = '; Extra data: ' . serialize($arFilter) . '';
}
//CTaskAssert::logError('[0x6024749e] unexpected field in filter: ' . $key . $extraData);
//throw new TasksException('Bad filter argument: '.$key, TasksException::TE_WRONG_ARGUMENTS);
}
break;
}
}
$sql = implode(
$logicStr,
array_filter(
$arSqlSearch
)
);
if ($sql == '')
{
$sql = '1=1';
}
return ('(' . $sql . ')');
}