protected static function GetSqlByFilter($arFilter, $userID, $sAliasPrefix, $bGetZombie, $bMembersTableJoined = false) { global $DB; $bFullJoin = null; if (!is_array($arFilter)) { throw new TasksException('GetSqlByFilter: expected array, but something other given: ' . var_export($arFilter, true)); } $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'); break; } } $arSqlSearch = array(); foreach ($arFilter as $key => $val) { // Skip meta-key if ($key === '::LOGIC') { continue; } // Skip markers if ($key === '::MARKERS') { continue; } // Subfilter? if (substr($key, 0, 12) === '::SUBFILTER-') { $arSqlSearch[] = self::GetSqlByFilter($val, $userID, $sAliasPrefix, $bGetZombie, $bMembersTableJoined); 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 (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 = strtoupper($key); switch ($key) { case 'META::ID_OR_NAME': if (strtoupper($DB->type) == "ORACLE") { $arSqlSearch[] = " (" . $sAliasPrefix . "T.ID = '" . intval($val) . "' OR (UPPER(" . $sAliasPrefix . "T.TITLE) UPPER('%" . $DB->ForSqlLike($val) . "%')) ) "; } else { $arSqlSearch[] = " (" . $sAliasPrefix . "T.ID = '" . intval($val) . "' OR (UPPER(" . $sAliasPrefix . "T.TITLE) LIKE UPPER('%" . $DB->ForSqlLike($val) . "%')) ) "; } break; 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 'TIME_ESTIMATE': $arSqlSearch[] = CTasks::FilterCreate($sAliasPrefix . "T." . $key, $val, "number_wo_nulls", $bFullJoin, $cOperationType); break; case "REFERENCE:RESPONSIBLE_ID": $key = 'RESPONSIBLE_ID'; $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 "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 "TAG": if (!is_array($val)) { $val = array($val); } $arConds = array(); foreach ($val as $tag) { if ($tag) { $arConds[] = "(" . $sAliasPrefix . "TT.NAME = '" . $DB->ForSql($tag) . "')"; } } if (sizeof($arConds)) { $arSqlSearch[] = "EXISTS(\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t'x'\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tb_tasks_tag " . $sAliasPrefix . "TT\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t(" . implode(" OR ", $arConds) . ")\n\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TT.TASK_ID = " . $sAliasPrefix . "T.ID\n\t\t\t\t\t\t)"; } break; case 'REAL_STATUS': $arSqlSearch[] = CTasks::FilterCreate($sAliasPrefix . "T.STATUS", $val, "number", $bFullJoin, $cOperationType); break; case 'DEADLINE_COUNTED': $arSqlSearch[] = CTasks::FilterCreate($sAliasPrefix . "T.DEADLINE_COUNTED", $val, "number_wo_nulls", $bFullJoin, $cOperationType); break; case 'VIEWED': $arSqlSearch[] = CTasks::FilterCreate("\n\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\tWHEN\n\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TV.USER_ID IS NULL\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t(" . $sAliasPrefix . "T.STATUS = 1 OR " . $sAliasPrefix . "T.STATUS = 2)\n\t\t\t\t\t\t\tTHEN\n\t\t\t\t\t\t\t\t'0'\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\t'1'\n\t\t\t\t\t\tEND\n\t\t\t\t\t", $val, "number", $bFullJoin, $cOperationType); break; case "STATUS": $arSqlSearch[] = CTasks::FilterCreate("\n\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\tWHEN\n\t\t\t\t\t\t\t\t" . $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 . ")\n\t\t\t\t\t\t\tTHEN\n\t\t\t\t\t\t\t\t'-1'\n\t\t\t\t\t\t\tWHEN\n\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TV.USER_ID IS NULL\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t" . $sAliasPrefix . "T.CREATED_BY != " . $userID . "\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t(" . $sAliasPrefix . "T.STATUS = 1 OR " . $sAliasPrefix . "T.STATUS = 2)\n\t\t\t\t\t\t\tTHEN\n\t\t\t\t\t\t\t\t'-2'\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\t" . $sAliasPrefix . "T.STATUS\n\t\t\t\t\t\tEND\n\t\t\t\t\t", $val, "number", $bFullJoin, $cOperationType); break; case 'MARK': case 'XML_ID': case 'SITE_ID': case 'ZOMBIE': case 'ADD_IN_REPORT': case 'ALLOW_TIME_TRACKING': $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": $arSqlSearch[] = CTasks::FilterCreate("CASE WHEN " . $sAliasPrefix . "T." . $key . " IS NULL THEN " . $sAliasPrefix . "T.CREATED_DATE ELSE " . $sAliasPrefix . "T." . $key . " END", $DB->CharToDateFunction($val), "date", $bFullJoin, $cOperationType); break; case "ACCOMPLICE": if (!is_array($val)) { $val = array($val); } $val = array_filter($val); $arConds = array(); 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') . "(\n\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t'x'\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tb_tasks_member " . $sAliasPrefix . "TM\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t(" . implode(" OR ", $arConds) . ")\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TM.TYPE = 'A'\n\t\t\t\t\t\t\t)"; } } break; case "PERIOD": case "ACTIVE": if ($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[] = "(\n\t\t\t\t\t\t\t\t\t(T.CREATED_DATE >= {$strDateStart} AND T.CREATED_DATE <= {$strDateEnd})\n\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(T.CLOSED_DATE >= {$strDateStart} AND T.CLOSED_DATE <= {$strDateEnd})\n\t\t\t\t\t\t\t\t)"; } elseif ($strDateStart !== false && $strDateEnd === false) { $arSqlSearch[] = "(\n\t\t\t\t\t\t\t\t\t(T.CREATED_DATE >= {$strDateStart})\n\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(T.CLOSED_DATE >= {$strDateStart})\n\t\t\t\t\t\t\t\t)"; } elseif ($strDateStart === false && $strDateEnd !== false) { $arSqlSearch[] = "(\n\t\t\t\t\t\t\t\t\t(T.CREATED_DATE <= {$strDateEnd})\n\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(T.CLOSED_DATE <= {$strDateEnd})\n\t\t\t\t\t\t\t\t)"; } } break; case "AUDITOR": if (!is_array($val)) { $val = array($val); } $val = array_filter($val); $arConds = array(); 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') . "(\n\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t'x'\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tb_tasks_member " . $sAliasPrefix . "TM\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t(" . implode(" OR ", $arConds) . ")\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TM.TYPE = 'U'\n\t\t\t\t\t\t\t)"; } } 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 = array($val); } $arConds = array(); foreach ($val as $id) { if ($id) { $arConds[] = "(" . $sAliasPrefix . "TD.TASK_ID = '" . intval($id) . "')"; } } if (sizeof($arConds)) { $arSqlSearch[] = "EXISTS(\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t'x'\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tb_tasks_dependence " . $sAliasPrefix . "TD\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t(" . implode(" OR ", $arConds) . ")\n\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t" . $sAliasPrefix . "TD.DEPENDS_ON_ID = " . $sAliasPrefix . "T.ID\n\t\t\t\t\t\t)"; } break; case "ONLY_ROOT_TASKS": if ($val == "Y") { $arSqlSearch[] = "(" . $sAliasPrefix . "T.PARENT_ID IS NULL OR NOT EXISTS (" . CTasks::GetRootSubquery($arFilter, $bGetZombie) . "))"; } break; case "SUBORDINATE_TASKS": if ($val == "Y") { $arSubSqlSearch = array($sAliasPrefix . "T.CREATED_BY = " . $userID, $sAliasPrefix . "T.RESPONSIBLE_ID = " . $userID, "EXISTS(SELECT 'x' FROM b_tasks_member " . $sAliasPrefix . "TM WHERE " . $sAliasPrefix . "TM.TASK_ID = " . $sAliasPrefix . "T.ID AND " . $sAliasPrefix . "TM.USER_ID = " . $userID . ")"); // subordinate check if ($strSql = CTasks::GetSubordinateSql($sAliasPrefix, array('USER_ID' => $userID))) { $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(\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t'x'\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tb_tasks " . $sAliasPrefix . "PT\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t" . $sAliasPrefix . "T.PARENT_ID = " . $sAliasPrefix . "PT.ID\n\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t(" . $sAliasPrefix . "PT.GROUP_ID = " . $sAliasPrefix . "T.GROUP_ID \n\t\t\t\t\t\t\t\tOR (" . $sAliasPrefix . "PT.GROUP_ID IS NULL AND " . $sAliasPrefix . "T.GROUP_ID IS NULL)\n\t\t\t\t\t\t\t\tOR (" . $sAliasPrefix . "PT.GROUP_ID = 0 AND " . $sAliasPrefix . "T.GROUP_ID IS NULL)\n\t\t\t\t\t\t\t\tOR (" . $sAliasPrefix . "PT.GROUP_ID IS NULL AND " . $sAliasPrefix . "T.GROUP_ID = 0)\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t" . ($bGetZombie ? "" : " AND " . $sAliasPrefix . "PT.ZOMBIE = 'N' ") . "\n\t\t\t\t\t\t)"; } break; case "DEPARTMENT_ID": if ($strSql = CTasks::GetDeparmentSql($val, $sAliasPrefix)) { $arSqlSearch[] = "EXISTS(" . $strSql . ")"; } break; case 'CHECK_PERMISSIONS': break; default: if (strlen($key) >= 3 && 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: <data0>' . serialize(array($_POST['arFilter'], $_POST['action'], $arFilter)) . '</data0>'; } else { $extraData = '; Extra data: <data1>' . serialize($arFilter) . '</data1>'; } CTaskAssert::logError('[0x6024749e] unexpected field in filter: ' . $key . $extraData); throw new TasksException('', TasksException::TE_WRONG_ARGUMENTS); } break; } } $sql = implode($logicStr, array_filter($arSqlSearch)); if ($sql == '') { $sql = '1=1'; } return '(' . $sql . ')'; }