/**
* <p>Возвращает список пользователей в виде объекта класса <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>.</p>
*
*
*
*
* @param mixed &$by = "timestamp_x" ссылка на переменную с полем для сортировки, может принимать
* значения: <ul> <li> <b>id</b> - ID пользователя </li> <li> <b>active</b> - активность
* </li> <li> <b>last_login</b> - дата последней авторизации </li> <li> <b>login</b> - имя
* входа </li> <li> <b>email</b> - E-Mail адрес </li> <li> <b>name</b> - имя </li> <li> <b>ntopcount</b> -
* параметр постраничной навигации, ограничивающий количество
* возвращаемых элементов </li> <li> <b>last_name</b> - фамилия </li> <li> <b>timestamp_x</b> -
* дата изменения </li> <li> <b>date_register</b> - дата регистрации </li> <li>
* <b>personal_profession</b> - профессия </li> <li> <b>personal_www</b> - WWW-страница </li> <li>
* <b>personal_icq</b> - номер ICQ </li> <li> <b>personal_gender</b> - пол ("M" - мужской; "F" -
* женский) </li> <li> <b>personal_birthday</b> - день рождения </li> <li> <b>personal_photo</b> - ID
* файла-фотографии </li> <li> <b>personal_phone</b> - номер телефона </li> <li>
* <b>personal_fax</b> - номер факса </li> <li> <b>personal_mobile</b> - номер мобильного </li>
* <li> <b>personal_pager</b> - номер пейджера </li> <li> <b>personal_street</b> - улица </li> <li>
* <b>personal_mailbox</b> - почтовый ящик </li> <li> <b>personal_city</b> - город </li> <li>
* <b>personal_state</b> - область / край </li> <li> <b>personal_zip</b> - почтовый индекс </li>
* <li> <b>personal_country</b> - код страны </li> <li> <b>personal_notes</b> - дополнительные
* заметки </li> <li> <b>work_company</b> - наименования компании </li> <li>
* <b>work_department</b> - отдел </li> <li> <b>work_position</b> - должность </li> <li> <b>work_www</b> -
* WWW-страница компании </li> <li> <b>work_phone</b> - рабочий телефон </li> <li>
* <b>work_fax</b> - рабочий факс </li> <li> <b>work_pager</b> - рабочий пейджер </li> <li>
* <b>work_street</b> - улица компании </li> <li> <b>work_mailbox</b> - почтовый ящик
* компании </li> <li> <b>work_city</b> - город компании </li> <li> <b>work_state</b> - область
* / край компании </li> <li> <b>work_zip</b> - почтовый индекс компании </li> <li>
* <b>work_country</b> - код страны компании </li> <li> <b>work_profile</b> - направление
* деятельности компании </li> <li> <b>work_notes</b> - дополнительные заметки
* касаемо места работы </li> <li> <b>admin_notes</b> - комментарий
* администратора </li> </ul> <p>Начиная с версии ядра 11.0.13 в параметре
* можно передавать массив вида array("field1"=>"asc", "field2"=>"desc") для
* множественной сортировки. Значения ключей массива совпадают с
* перечисленными выше.</p>
*
*
*
* @param string &$order = "desc" Ссылка на переменную с порядком сортировки, может принимать
* значения: <br><ul> <li> <b>asc</b> - по возрастанию </li> <li> <b>desc</b> - по убыванию
* </li> </ul> <p>При использовании массива в параметре <em>by</em> данный
* параметр игнорируется. Значения c <i>nulls</i> не работают, например:
* <i>desc,nulls</i>.</p>
*
*
*
* @param array $filter = array() Массив для фильтрации пользователей. (<a
* href="http://dev.1c-bitrix.ru/learning/course/index.php?COURSE_ID=43&LESSON_ID=2683#types" >Типы
* фильтрации</a>) В массиве допустимы следующие индексы: <ul> <li>
* <sup>1</sup><b>ID</b> - по ID пользователя </li> <li> <b>XML_ID</b> - по XML_ID пользователя
* </li> <li> <b>TIMESTAMP_1</b> - дата изменения профайла пользователя "с" </li> <li>
* <b>TIMESTAMP_2</b> - дата изменения профайла пользователя "по" </li> <li>
* <b>LAST_LOGIN_1</b> - дата последнего логина пользователя "с" </li> <li>
* <b>LAST_LOGIN_2</b> - дата последнего логина пользователя "по" </li> <li>
* <b>LAST_ACTIVITY</b> - интервал в секундах </li> <li> <b>ACTIVE</b> - фильтр по
* активности (Y|N) </li> <li> <sup>1</sup><b>LOGIN_EQUAL</b> - по имени входа (ищет прямое
* совпадение с логином) </li> <li> <sup>1</sup><b>LOGIN</b> - по имени входа (ищет
* подстроку в логине) </li> <li> <sup>1</sup><b>NAME</b> - по имени и фамилии </li> <li>
* <sup>1</sup><b>EMAIL</b> - по E-Mail адресу </li> <li> <sup>1</sup><b>COUNTRY_ID</b> - по коду
* страны </li> <li> <b>GROUPS_ID</b> - по группам (массив с кодами групп
* пользователей) </li> <li> <b>PERSONAL_BIRTHDAY_1</b> - день рождения "с" </li> <li>
* <b>PERSONAL_BIRTHDAY_2</b> - день рождения "по" </li> <li> <sup>1</sup><b>KEYWORDS</b> - по
* нижеследующим полям профайла помеченных символом - * </li> <li>
* <sup>1</sup>* <b>PERSONAL_PROFESSION</b> - профессия </li> <li> <sup>1</sup>* <b>PERSONAL_WWW</b> -
* WWW-страница </li> <li> <sup>1</sup>* <b>PERSONAL_ICQ</b> - номер ICQ </li> <li> <sup>1</sup>*
* <b>PERSONAL_GENDER</b> - пол ("M" - мужской; "F" - женский) </li> <li> <sup>1</sup>*
* <b>PERSONAL_PHOTO</b> - ID файла - фотографии (таблица b_file) </li> <li> <sup>1</sup>*
* <b>PERSONAL_PHONE</b> - номер телефона </li> <li> <sup>1</sup>* <b>PERSONAL_FAX</b> - номер факса
* </li> <li> <sup>1</sup>* <b>PERSONAL_MOBILE</b> - номер мобильного </li> <li> <sup>1</sup>*
* <b>PERSONAL_PAGER</b> - номер пейджера </li> <li> <sup>1</sup>* <b>PERSONAL_STREET</b> - улица </li>
* <li> <sup>1</sup>* <b>PERSONAL_MAILBOX</b> - почтовый ящик </li> <li> <sup>1</sup>* <b>PERSONAL_CITY</b> -
* город </li> <li> <sup>1</sup>* <b>PERSONAL_STATE</b> - область / край </li> <li> <sup>1</sup>*
* <b>PERSONAL_ZIP</b> - почтовый индекс </li> <li> <sup>1</sup>* <b>PERSONAL_COUNTRY</b> - код
* страны (хранится в файлах \bitrix\modules\main\lang\ru\tools.php,
* \bitrix\modules\main\lang\en\tools.php) </li> <li> <sup>1</sup>* <b>PERSONAL_NOTES</b> - дополнительные
* заметки </li> <li> <sup>1</sup>* <b>WORK_COMPANY</b> - наименования компании </li> <li>
* <sup>1</sup>* <b>WORK_DEPARTMENT</b> - отдел </li> <li> <sup>1</sup>* <b>WORK_POSITION</b> - должность
* </li> <li> <sup>1</sup>* <b>WORK_WWW</b> - WWW-страница компании </li> <li> <sup>1</sup>*
* <b>WORK_PHONE</b> - рабочий телефон </li> <li> <sup>1</sup>* <b>WORK_FAX</b> - рабочий факс
* </li> <li> <sup>1</sup>* <b>WORK_PAGER</b> - рабочий пейджер </li> <li> <sup>1</sup>* <b>WORK_STREET</b> -
* улица компании </li> <li> <sup>1</sup>* <b>WORK_MAILBOX</b> - почтовый ящик компании
* </li> <li> <sup>1</sup>* <b>WORK_CITY</b> - город компании </li> <li> <sup>1</sup>* <b>WORK_STATE</b> -
* область / край компании </li> <li> <sup>1</sup>* <b>WORK_ZIP</b> - почтовый индекс
* компании </li> <li> <sup>1</sup>* <b>WORK_COUNTRY</b> - код страны компании (хранится
* в файлах \bitrix\modules\main\lang\ru\tools.php, \bitrix\modules\main\lang\en\tools.php) </li> <li> <sup>1</sup>*
* <b>WORK_PROFILE</b> - направление деятельности компании </li> <li> <sup>1</sup>*
* <b>WORK_NOTES</b> - дополнительные заметки касаемо места работы </li> <li>
* <sup>1</sup>* <b>ADMIN_NOTES</b> - комментарий администратора (доступен для
* просмотра и редактирования только администратору сайта) </li> </ul>
* <sup>1</sup> - в данных полях допускается <a
* href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложные условия</a>. Сложные
* условия для данного поля работают только при указании: <b>ID</b>. При
* указании <b>!ID</b> и <b>>ID</b>, сложные условия работать не будут. <br> * -
* поиск по "KEYWORDS" по сути является поиском по полям отмеченных
* символом "*"
*
*
*
* @param array $arParams = array() Массив с дополнительными параметрами функции. Может содержать
* ключи: <br><p><strong>SELECT</strong> - массив с идентификаторами
* пользовательских полей для их выборки в результат, например
* array("UF_TEXT_1", "UF_STRUCTURE"). Для указания выборки всех полей используйте
* маску: array("UF_*").</p> <p><strong>NAV_PARAMS</strong> - массив с параметрами
* навигации, может использоваться для ограничения размера выборки.
* Например: array("nPageSize"=>"20"). При указании NAV_PARAMS строится
* ограниченный по размеру список результатов, учитывающий номер
* страницы в постраничной навигации (для mysql выборка производится с
* указанием limit). С версии ядра 11.0.14 в массиве можно указать параметр
* "nTopCount" для ограничения выборки по количеству записей.</p>
* <p><strong>FIELDS</strong> (с версии ядра 11.0.13) - массив с идентификаторами
* полей для выборки. Если не указан или пустой, то выбираются все
* поля. Возможные значения:</p> <table height="0" cellspacing="0" cellpadding="0" bgcolor=""
* width="100%"><tbody> <tr> <td>ID</td> <td>PERSONAL_WWW</td> <td>PERSONAL_ZIP</td> <td>IS_ONLINE</td> </tr> <tr>
* <td>ACTIVE</td> <td>PERSONAL_ICQ</td> <td>PERSONAL_COUNTRY</td> <td>WORK_CITY</td> </tr> <tr> <td>LAST_LOGIN</td>
* <td>PERSONAL_GENDER</td> <td>PERSONAL_NOTES</td> <td>WORK_STATE</td> </tr> <tr> <td>LOGIN</td> <td>PERSONAL_PHOTO</td>
* <td>WORK_COMPANY</td> <td>WORK_ZIP</td> </tr> <tr> <td>EMAIL</td> <td>PERSONAL_PHONE</td> <td>WORK_DEPARTMENT</td>
* <td>WORK_COUNTRY</td> </tr> <tr> <td>NAME</td> <td>PERSONAL_FAX</td> <td>WORK_POSITION</td> <td>WORK_PROFILE</td> </tr>
* <tr> <td>LAST_NAME</td> <td>PERSONAL_MOBILE</td> <td>WORK_WWW</td> <td>WORK_NOTES</td> </tr> <tr> <td>SECOND_NAME</td>
* <td>PERSONAL_PAGER</td> <td>WORK_PHONE</td> <td>ADMIN_NOTES</td> </tr> <tr> <td>TIMESTAMP_X</td>
* <td>PERSONAL_STREET</td> <td>WORK_FAX</td> <td>XML_ID</td> </tr> <tr> <td>PERSONAL_BIRTHDAY</td>
* <td>PERSONAL_MAILBOX</td> <td>WORK_PAGER</td> <td>LAST_NAME</td> </tr> <tr> <td>DATE_REGISTER</td>
* <td>PERSONAL_CITY</td> <td>WORK_STREET</td> <td>SECOND_NAME</td> </tr> <tr> <td>PERSONAL_PROFESSION</td>
* <td>PERSONAL_STATE</td> <td>WORK_MAILBOX</td> <td>STORED_HASH</td> </tr> <tr> <td>CHECKWORD_TIME</td>
* <td>EXTERNAL_AUTH_ID</td> <td>CONFIRM_CODE</td> <td>LOGIN_ATTEMPTS</td> </tr> <tr> <td>LAST_ACTIVITY_DATE</td>
* <td>AUTO_TIME_ZONE</td> <td>TIME_ZONE</td> <td>PASSWORD</td> </tr> <tr> <td>CHECKWORD</td> <td>LID</td> <td></td>
* <td></td> </tr> </tbody></table>
*
*
*
* @return CDBResult
*
*
* <h4>Example</h4>
* <pre>
* <?<br>$filter = Array<br>(<br> "ID" => "1 | 2",<br> "TIMESTAMP_1" => "04.02.2004", // в формате текущего сайта<br> "TIMESTAMP_2" => "04.02.2005",<br> "LAST_LOGIN_1" => "01.02.2004",<br> "ACTIVE" => "Y",<br> "LOGIN" => "nessy | admin",<br> "NAME" => "Виталий & Соколов",<br> "EMAIL" => "mail@server.com | mail@server.com",<br> "KEYWORDS" => "www.bitrix.ru",<br> "PERSONAL_PROFESSION" => "системотехник",<br> "PERSONAL_GENDER" => "M",<br> "PERSONAL_COUNTRY" => "4 | 1", // Беларусь или Россия<br> "ADMIN_NOTES" => "\"UID = 145\"",<br> "GROUPS_ID" => Array(1,4,10)<br>);<br>$rsUsers = <b>CUser::GetList</b>(($by="personal_country"), ($order="desc"), $filter); // выбираем пользователей<br>$is_filtered = $rsUsers->is_filtered; // отфильтрована ли выборка ?<br>$rsUsers->NavStart(50); // разбиваем постранично по 50 записей<br>echo $rsUsers->NavPrint(GetMessage("PAGES")); // печатаем постраничную навигацию<br>while($rsUsers->NavNext(true, "f_")) :<br> echo "[".$f_ID."] (".$f_LOGIN.") ".$f_NAME." ".$f_LAST_NAME."<br>"; <br>endwhile;<br>?>
*
* $rsUsers = CUser::GetList(array('sort' => 'asc'), 'sort');$order = array('sort' => 'asc');
* $tmp = 'sort'; // параметр проигнорируется методом, но обязан быть
* $rsUsers = CUser::GetList($order, $tmp);
* </pre>
*
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cuser/index.php">Поля CUser</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cuser/getbyid.php">CUser::GetByID</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cuser/getbylogin.php">CUser::GetByLogin</a> </li> </ul></b<a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/main/reference/cuser/getlist.php
* @author Bitrix
*/
public static function GetList(&$by, &$order, $arFilter = array(), $arParams = array())
{
/** @global CUserTypeManager $USER_FIELD_MANAGER */
global $DB, $USER_FIELD_MANAGER;
$err_mess = CUser::err_mess() . "<br>Function: GetList<br>Line: ";
if (is_array($by)) {
$bSingleBy = false;
$arOrder = $by;
} else {
$bSingleBy = true;
$arOrder = array($by => $order);
}
static $obUserFieldsSql;
if (!isset($obUserFieldsSql)) {
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("USER", "U.ID");
$obUserFieldsSql->obWhere->AddFields(array("F_LAST_NAME" => array("TABLE_ALIAS" => "U", "FIELD_NAME" => "U.LAST_NAME", "MULTIPLE" => "N", "FIELD_TYPE" => "string", "JOIN" => false)));
}
$obUserFieldsSql->SetSelect($arParams["SELECT"]);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$arFields_m = array("ID", "ACTIVE", "LAST_LOGIN", "LOGIN", "EMAIL", "NAME", "LAST_NAME", "SECOND_NAME", "TIMESTAMP_X", "PERSONAL_BIRTHDAY", "IS_ONLINE");
$arFields = array("DATE_REGISTER", "PERSONAL_PROFESSION", "PERSONAL_WWW", "PERSONAL_ICQ", "PERSONAL_GENDER", "PERSONAL_PHOTO", "PERSONAL_PHONE", "PERSONAL_FAX", "PERSONAL_MOBILE", "PERSONAL_PAGER", "PERSONAL_STREET", "PERSONAL_MAILBOX", "PERSONAL_CITY", "PERSONAL_STATE", "PERSONAL_ZIP", "PERSONAL_COUNTRY", "PERSONAL_NOTES", "WORK_COMPANY", "WORK_DEPARTMENT", "WORK_POSITION", "WORK_WWW", "WORK_PHONE", "WORK_FAX", "WORK_PAGER", "WORK_STREET", "WORK_MAILBOX", "WORK_CITY", "WORK_STATE", "WORK_ZIP", "WORK_COUNTRY", "WORK_PROFILE", "WORK_NOTES", "ADMIN_NOTES", "XML_ID", "LAST_NAME", "SECOND_NAME", "STORED_HASH", "CHECKWORD_TIME", "EXTERNAL_AUTH_ID", "CONFIRM_CODE", "LOGIN_ATTEMPTS", "LAST_ACTIVITY_DATE", "AUTO_TIME_ZONE", "TIME_ZONE", "TIME_ZONE_OFFSET", "PASSWORD", "CHECKWORD", "LID", "TITLE");
$arFields_all = array_merge($arFields_m, $arFields);
$arSelectFields = array();
$online_interval = array_key_exists("ONLINE_INTERVAL", $arParams) && intval($arParams["ONLINE_INTERVAL"]) > 0 ? $arParams["ONLINE_INTERVAL"] : 120;
if (isset($arParams['FIELDS']) && is_array($arParams['FIELDS']) && count($arParams['FIELDS']) > 0 && !in_array("*", $arParams['FIELDS'])) {
foreach ($arParams['FIELDS'] as $field) {
$field = strtoupper($field);
if ($field == 'TIMESTAMP_X') {
$arSelectFields[$field] = $DB->DateToCharFunction("U.TIMESTAMP_X") . " TIMESTAMP_X";
} elseif ($field == 'IS_ONLINE') {
$arSelectFields[$field] = "IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . $online_interval . " SECOND), 'Y', 'N') IS_ONLINE";
} elseif ($field == 'DATE_REGISTER') {
$arSelectFields[$field] = $DB->DateToCharFunction("U.DATE_REGISTER") . " DATE_REGISTER";
} elseif ($field == 'LAST_LOGIN') {
$arSelectFields[$field] = $DB->DateToCharFunction("U.LAST_LOGIN") . " LAST_LOGIN";
} elseif ($field == 'PERSONAL_BIRTHDAY') {
$arSelectFields[$field] = $DB->DateToCharFunction("U.PERSONAL_BIRTHDAY", "SHORT") . " PERSONAL_BIRTHDAY";
} elseif (in_array($field, $arFields_all)) {
$arSelectFields[$field] = 'U.' . $field;
}
}
}
if (empty($arSelectFields)) {
$arSelectFields[] = 'U.*';
$arSelectFields['TIMESTAMP_X'] = $DB->DateToCharFunction("U.TIMESTAMP_X") . " TIMESTAMP_X";
$arSelectFields['IS_ONLINE'] = "IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . $online_interval . " SECOND), 'Y', 'N') IS_ONLINE";
$arSelectFields['DATE_REGISTER'] = $DB->DateToCharFunction("U.DATE_REGISTER") . " DATE_REGISTER";
$arSelectFields['LAST_LOGIN'] = $DB->DateToCharFunction("U.LAST_LOGIN") . " LAST_LOGIN";
$arSelectFields['PERSONAL_BIRTHDAY'] = $DB->DateToCharFunction("U.PERSONAL_BIRTHDAY", "SHORT") . " PERSONAL_BIRTHDAY";
}
$arSqlSearch = array();
$strJoin = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
$key = strtoupper($key);
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} elseif ($key != "LOGIN_EQUAL_EXACT" && $key != "CONFIRM_CODE" && $key != "!CONFIRM_CODE" && $key != "LAST_ACTIVITY" && $key != "!LAST_ACTIVITY" && $key != "LAST_LOGIN" && $key != "!LAST_LOGIN" && $key != "EXTERNAL_AUTH_ID") {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
switch ($key) {
case "ID":
$arSqlSearch[] = GetFilterQuery("U.ID", $val, "N");
break;
case ">ID":
$arSqlSearch[] = "U.ID > " . intval($val);
break;
case "!ID":
$arSqlSearch[] = "U.ID <> " . intval($val);
break;
case "ID_EQUAL_EXACT":
$arSqlSearch[] = "U.ID='" . intval($val) . "'";
break;
case "TIMESTAMP_1":
$arSqlSearch[] = "U.TIMESTAMP_X >= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y"), "d.m.Y") . "')";
break;
case "TIMESTAMP_2":
$arSqlSearch[] = "U.TIMESTAMP_X <= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y") . " 23:59:59", "d.m.Y") . "')";
break;
case "TIMESTAMP_X_1":
$arSqlSearch[] = "U.TIMESTAMP_X >= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "DD.MM.YYYY HH:MI:SS"), "d.m.Y H:i:s") . "')";
break;
case "TIMESTAMP_X_2":
$arSqlSearch[] = "U.TIMESTAMP_X <= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "DD.MM.YYYY HH:MI:SS"), "d.m.Y H:i:s") . "')";
break;
case "LAST_LOGIN_1":
$arSqlSearch[] = "U.LAST_LOGIN >= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y"), "d.m.Y") . "')";
break;
case "LAST_LOGIN_2":
$arSqlSearch[] = "U.LAST_LOGIN <= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y") . " 23:59:59", "d.m.Y") . "')";
break;
case "LAST_LOGIN":
if ($val === false) {
$arSqlSearch[] = "U.LAST_LOGIN IS NULL";
}
break;
case "!LAST_LOGIN":
if ($val === false) {
$arSqlSearch[] = "U.LAST_LOGIN IS NOT NULL";
}
break;
case "DATE_REGISTER_1":
$arSqlSearch[] = "U.DATE_REGISTER >= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y"), "d.m.Y") . "')";
break;
case "DATE_REGISTER_2":
$arSqlSearch[] = "U.DATE_REGISTER <= FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y") . " 23:59:59", "d.m.Y") . "')";
break;
case "ACTIVE":
$arSqlSearch[] = $val == "Y" ? "U.ACTIVE='Y'" : "U.ACTIVE='N'";
break;
case "LOGIN_EQUAL":
$arSqlSearch[] = GetFilterQuery("U.LOGIN", $val, "N");
break;
case "LOGIN":
$arSqlSearch[] = GetFilterQuery("U.LOGIN", $val);
break;
case "EXTERNAL_AUTH_ID":
if ($val != '') {
$arSqlSearch[] = "U.EXTERNAL_AUTH_ID='" . $DB->ForSQL($val, 255) . "'";
} else {
$arSqlSearch[] = "(U.EXTERNAL_AUTH_ID IS NULL OR U.EXTERNAL_AUTH_ID='')";
}
break;
case "LOGIN_EQUAL_EXACT":
$arSqlSearch[] = "U.LOGIN='******'";
break;
case "XML_ID":
$arSqlSearch[] = "U.XML_ID='" . $DB->ForSql($val) . "'";
break;
case "CONFIRM_CODE":
if ($val != '') {
$arSqlSearch[] = "U.CONFIRM_CODE='" . $DB->ForSql($val) . "'";
} else {
$arSqlSearch[] = "(U.CONFIRM_CODE IS NULL OR LENGTH(U.CONFIRM_CODE) <= 0)";
}
break;
case "!CONFIRM_CODE":
if ($val != '') {
$arSqlSearch[] = "U.CONFIRM_CODE <> '" . $DB->ForSql($val) . "'";
} else {
$arSqlSearch[] = "(U.CONFIRM_CODE IS NOT NULL AND LENGTH(U.CONFIRM_CODE) > 0)";
}
break;
case "COUNTRY_ID":
case "WORK_COUNTRY":
$arSqlSearch[] = "U.WORK_COUNTRY=" . intval($val);
break;
case "PERSONAL_COUNTRY":
$arSqlSearch[] = "U.PERSONAL_COUNTRY=" . intval($val);
break;
case "NAME":
$arSqlSearch[] = GetFilterQuery("U.NAME, U.LAST_NAME, U.SECOND_NAME", $val);
break;
case "NAME_SEARCH":
$arSqlSearch[] = GetFilterQuery("U.NAME, U.LAST_NAME, U.SECOND_NAME, U.EMAIL, U.LOGIN", $val);
break;
case "EMAIL":
$arSqlSearch[] = GetFilterQuery("U.EMAIL", $val, "Y", array("@", "_", ".", "-"));
break;
case "=EMAIL":
$arSqlSearch[] = "U.EMAIL = '" . $DB->ForSQL(trim($val)) . "'";
break;
case "GROUP_MULTI":
case "GROUPS_ID":
if (is_numeric($val) && intval($val) > 0) {
$val = array($val);
}
if (is_array($val) && count($val) > 0) {
$ar = array();
foreach ($val as $id) {
$ar[intval($id)] = intval($id);
}
$strJoin .= " INNER JOIN (SELECT DISTINCT UG.USER_ID FROM b_user_group UG\n\t\t\t\t\t\t\tWHERE UG.GROUP_ID in (" . implode(",", $ar) . ")\n\t\t\t\t\t\t\t\tand (UG.DATE_ACTIVE_FROM is null or\tUG.DATE_ACTIVE_FROM <= " . $DB->CurrentTimeFunction() . ")\n\t\t\t\t\t\t\t\tand (UG.DATE_ACTIVE_TO is null or UG.DATE_ACTIVE_TO >= " . $DB->CurrentTimeFunction() . ")\n\t\t\t\t\t\t\t) UG ON UG.USER_ID=U.ID ";
}
break;
case "PERSONAL_BIRTHDATE_1":
$arSqlSearch[] = "U.PERSONAL_BIRTHDATE>=" . $DB->CharToDateFunction($val);
break;
case "PERSONAL_BIRTHDATE_2":
$arSqlSearch[] = "U.PERSONAL_BIRTHDATE<=" . $DB->CharToDateFunction($val . " 23:59:59");
break;
case "PERSONAL_BIRTHDAY_1":
$arSqlSearch[] = "U.PERSONAL_BIRTHDAY>=" . $DB->CharToDateFunction($DB->ForSql($val), "SHORT");
break;
case "PERSONAL_BIRTHDAY_2":
$arSqlSearch[] = "U.PERSONAL_BIRTHDAY<=" . $DB->CharToDateFunction($DB->ForSql($val), "SHORT");
break;
case "PERSONAL_BIRTHDAY_DATE":
$arSqlSearch[] = "DATE_FORMAT(U.PERSONAL_BIRTHDAY, '%m-%d') = '" . $DB->ForSql($val) . "'";
break;
case "KEYWORDS":
$arSqlSearch[] = GetFilterQuery(implode(",", $arFields), $val);
break;
case "CHECK_SUBORDINATE":
if (is_array($val)) {
$strSubord = "0";
foreach ($val as $grp) {
$strSubord .= "," . intval($grp);
}
if (intval($arFilter["CHECK_SUBORDINATE_AND_OWN"]) > 0) {
$arSqlSearch[] = "(U.ID=" . intval($arFilter["CHECK_SUBORDINATE_AND_OWN"]) . " OR NOT EXISTS(SELECT 'x' FROM b_user_group UGS WHERE UGS.USER_ID=U.ID AND UGS.GROUP_ID NOT IN (" . $strSubord . ")))";
} else {
$arSqlSearch[] = "NOT EXISTS(SELECT 'x' FROM b_user_group UGS WHERE UGS.USER_ID=U.ID AND UGS.GROUP_ID NOT IN (" . $strSubord . "))";
}
}
break;
case "NOT_ADMIN":
if ($val !== true) {
break;
}
$arSqlSearch[] = "not exists (SELECT * FROM b_user_group UGNA WHERE UGNA.USER_ID=U.ID AND UGNA.GROUP_ID = 1)";
break;
case "LAST_ACTIVITY":
if ($val === false) {
$arSqlSearch[] = "U.LAST_ACTIVITY_DATE IS NULL";
} elseif (intval($val) > 0) {
$arSqlSearch[] = "U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . intval($val) . " SECOND)";
}
break;
case "!LAST_ACTIVITY":
if ($val === false) {
$arSqlSearch[] = "U.LAST_ACTIVITY_DATE IS NOT NULL";
}
break;
case "INTRANET_USERS":
$arSqlSearch[] = "U.ACTIVE = 'Y' AND U.LAST_LOGIN IS NOT NULL AND EXISTS(SELECT 'x' FROM b_utm_user UF1, b_user_field F1 WHERE F1.ENTITY_ID = 'USER' AND F1.FIELD_NAME = 'UF_DEPARTMENT' AND UF1.FIELD_ID = F1.ID AND UF1.VALUE_ID = U.ID AND UF1.VALUE_INT IS NOT NULL AND UF1.VALUE_INT <> 0)";
break;
default:
if (in_array($key, $arFields)) {
$arSqlSearch[] = GetFilterQuery('U.' . $key, $val, $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y");
}
}
}
}
$arSqlOrder = array();
foreach ($arOrder as $field => $dir) {
$field = strtoupper($field);
if (strtolower($dir) != "asc") {
$dir = "desc";
if ($bSingleBy) {
$order = "desc";
}
}
if ($field == "CURRENT_BIRTHDAY") {
$cur_year = intval(date('Y'));
$arSqlOrder[$field] = "IF(ISNULL(PERSONAL_BIRTHDAY), '9999-99-99', IF (\n\t\t\t\t\tDATE_FORMAT(U.PERSONAL_BIRTHDAY, '" . $cur_year . "-%m-%d') < DATE_FORMAT(DATE_ADD(" . $DB->CurrentTimeFunction() . ", INTERVAL " . CTimeZone::GetOffset() . " SECOND), '%Y-%m-%d'),\n\t\t\t\t\tDATE_FORMAT(U.PERSONAL_BIRTHDAY, '" . ($cur_year + 1) . "-%m-%d'),\n\t\t\t\t\tDATE_FORMAT(U.PERSONAL_BIRTHDAY, '" . $cur_year . "-%m-%d')\n\t\t\t\t)) " . $dir;
} elseif ($field == "IS_ONLINE") {
$arSelectFields[$field] = "IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . $online_interval . " SECOND), 'Y', 'N') IS_ONLINE";
$arSqlOrder[$field] = "IS_ONLINE " . $dir;
} elseif (in_array($field, $arFields_all)) {
$arSqlOrder[$field] = "U." . $field . " " . $dir;
} elseif ($s = $obUserFieldsSql->GetOrder($field)) {
$arSqlOrder[$field] = strtoupper($s) . " " . $dir;
} elseif (preg_match('/^RATING_(\\d+)$/i', $field, $matches)) {
$ratingId = intval($matches[1]);
if ($ratingId > 0) {
$arSqlOrder[$field] = $field . "_ISNULL ASC, " . $field . " " . $dir;
$arParams['SELECT'][] = $field;
} else {
$field = "TIMESTAMP_X";
$arSqlOrder[$field] = "U." . $field . " " . $dir;
if ($bSingleBy) {
$by = strtolower($field);
}
}
} else {
$field = "TIMESTAMP_X";
$arSqlOrder[$field] = "U." . $field . " " . $dir;
if ($bSingleBy) {
$by = strtolower($field);
}
}
}
$userFieldsSelect = $obUserFieldsSql->GetSelect();
$arSqlSearch[] = $obUserFieldsSql->GetFilter();
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$sSelect = ($obUserFieldsSql->GetDistinct() ? "DISTINCT " : "") . implode(', ', $arSelectFields) . "\n\t\t\t" . $userFieldsSelect . "\n\t\t";
if (is_array($arParams['SELECT'])) {
$arRatingInSelect = array();
foreach ($arParams['SELECT'] as $column) {
if (preg_match('/^RATING_(\\d+)$/i', $column, $matches)) {
$ratingId = intval($matches[1]);
if ($ratingId > 0 && !in_array($ratingId, $arRatingInSelect)) {
$sSelect .= ", RR" . $ratingId . ".CURRENT_POSITION IS NULL as RATING_" . $ratingId . "_ISNULL";
$sSelect .= ", RR" . $ratingId . ".CURRENT_VALUE as RATING_" . $ratingId;
$sSelect .= ", RR" . $ratingId . ".CURRENT_VALUE as RATING_" . $ratingId . "_CURRENT_VALUE";
$sSelect .= ", RR" . $ratingId . ".PREVIOUS_VALUE as RATING_" . $ratingId . "_PREVIOUS_VALUE";
$sSelect .= ", RR" . $ratingId . ".CURRENT_POSITION as RATING_" . $ratingId . "_CURRENT_POSITION";
$sSelect .= ", RR" . $ratingId . ".PREVIOUS_POSITION as RATING_" . $ratingId . "_PREVIOUS_POSITION";
$strJoin .= " LEFT JOIN b_rating_results RR" . $ratingId . "\n\t\t\t\t\t\t\tON RR" . $ratingId . ".RATING_ID=" . $ratingId . "\n\t\t\t\t\t\t\tand RR" . $ratingId . ".ENTITY_TYPE_ID = 'USER'\n\t\t\t\t\t\t\tand RR" . $ratingId . ".ENTITY_ID = U.ID ";
$arRatingInSelect[] = $ratingId;
}
}
}
}
$strFrom = "\n\t\t\tFROM\n\t\t\t\tb_user U\n\t\t\t\t" . $obUserFieldsSql->GetJoin("U.ID") . "\n\t\t\t\t" . $strJoin . "\n\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t";
$strSqlOrder = '';
if (!empty($arSqlOrder)) {
$strSqlOrder = 'ORDER BY ' . implode(', ', $arSqlOrder);
}
$strSql = "SELECT " . $sSelect . $strFrom . $strSqlOrder;
if (array_key_exists("NAV_PARAMS", $arParams) && is_array($arParams["NAV_PARAMS"])) {
$nTopCount = intval($arParams['NAV_PARAMS']['nTopCount']);
if ($nTopCount > 0) {
$strSql = $DB->TopSql($strSql, $nTopCount);
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
if ($userFieldsSelect != '') {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("USER"));
}
} else {
$res_cnt = $DB->Query("SELECT COUNT(" . ($obUserFieldsSql->GetDistinct() ? "DISTINCT " : "") . "U.ID) as C " . $strFrom);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
if ($userFieldsSelect != '') {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("USER"));
}
$res->NavQuery($strSql, $res_cnt["C"], $arParams["NAV_PARAMS"]);
}
} else {
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
if ($userFieldsSelect != '') {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("USER"));
}
}
$res->is_filtered = IsFiltered($strSqlSearch);
return $res;
}
public function Prepare($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array(), $arOptions = array())
{
global $DB;
if (!is_array($arOrder)) {
$arOrder = array();
}
if (!is_array($arFilter)) {
$arFilter = array();
}
// ID must present in select (If select is empty it will be filled by CSqlUtil::PrepareSql)
if (!is_array($arSelectFields)) {
$arSelectFields = array();
}
if (count($arSelectFields) > 0 && !in_array('*', $arSelectFields, true) && !in_array('ID', $arSelectFields, true)) {
$arSelectFields[] = 'ID';
}
if (!is_array($arOptions)) {
$arOptions = array();
}
$arOptions['DB_TYPE'] = $this->dbType;
$isExternalContext = isset($arOptions['IS_EXTERNAL_CONTEXT']) && ($arOptions['IS_EXTERNAL_CONTEXT'] === true || $arOptions['IS_EXTERNAL_CONTEXT'] === 'Y');
if ($isExternalContext) {
// Sanitizing of filter data
if (isset($arFilter['__JOINS'])) {
unset($arFilter['__JOINS']);
}
if (isset($arFilter['CHECK_PERMISSIONS'])) {
unset($arFilter['CHECK_PERMISSIONS']);
}
}
// Processing of special fields
if ($this->fmEntityID !== '' && isset($arFilter['FM'])) {
CCrmFieldMulti::PrepareExternalFilter($arFilter, array('ENTITY_ID' => $this->fmEntityID, 'MASTER_ALIAS' => $this->tableAlias, 'MASTER_IDENTITY' => 'ID'));
}
// Processing user fields
$ufSelectSql = null;
$ufFilterSql = null;
if ($this->ufEntityID !== '') {
$ufSelectSql = new CUserTypeSQL();
$ufSelectSql->SetEntity($this->ufEntityID, $this->tableAlias . '.ID');
$ufSelectSql->SetSelect($arSelectFields);
$ufSelectSql->SetOrder($arOrder);
$ufFilterSql = new CUserTypeSQL();
$ufFilterSql->SetEntity($this->ufEntityID, $this->tableAlias . '.ID');
$ufFilterSql->SetFilter($arFilter);
$userType = new CCrmUserType($GLOBALS['USER_FIELD_MANAGER'], $this->ufEntityID);
$userType->ListPrepareFilter($arFilter);
}
$this->sqlData = CSqlUtil::PrepareSql($this->fields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arOptions);
$this->sqlData['SELECT'] = str_replace('%%_DISTINCT_%% ', '', $this->sqlData['SELECT']);
// 'Joins' implement custom filter logic
$joins = array();
if (isset($arFilter['__JOINS'])) {
if (is_array($arFilter['__JOINS'])) {
$joins = $arFilter['__JOINS'];
}
unset($arFilter['__JOINS']);
}
if (count($joins) > 0) {
foreach ($joins as &$join) {
// INNER JOINs will be added tostart
$this->Add2SqlData($join['SQL'], 'FROM', !isset($join['TYPE']) || $join['TYPE'] === 'INNER', isset($join['REPLACE']) ? $join['REPLACE'] : '');
}
unset($join);
}
// Apply user permission logic
if (count($this->permissionCallback) > 0) {
if ((!array_key_exists('CHECK_PERMISSIONS', $arFilter) || $arFilter['CHECK_PERMISSIONS'] !== 'N') && !CCrmPerms::IsAdmin()) {
$arPermType = !isset($arFilter['PERMISSION']) ? 'READ' : (is_array($arFilter['PERMISSION']) ? $arFilter['PERMISSION'] : array($arFilter['PERMISSION']));
$permissionSql = call_user_func_array($this->permissionCallback, array($this->tableAlias, $arPermType, $arOptions));
if (is_bool($permissionSql) && !$permissionSql) {
$CDBResult = new CDBResult();
$CDBResult->InitFromArray(array());
return $CDBResult;
}
if ($permissionSql !== '') {
$sqlType = isset($arOptions['PERMISSION_SQL_TYPE']) && $arOptions['PERMISSION_SQL_TYPE'] === 'FROM' ? 'FROM' : 'WHERE';
$this->Add2SqlData($permissionSql, $sqlType, $sqlType === 'FROM');
}
}
}
// Apply custom SQL logic
if (count($this->afterPrepareSqlCallback) > 0) {
$arUserSql = call_user_func_array($this->afterPrepareSqlCallback, array($this, $arOrder, $arFilter, $arGroupBy, $arSelectFields));
if (is_array($arUserSql)) {
if (isset($arUserSql['FROM'])) {
$this->Add2SqlData($arUserSql['FROM'], 'FROM');
}
if (isset($arUserSql['WHERE'])) {
$this->Add2SqlData($arUserSql['WHERE'], 'WHERE');
}
}
}
if ($ufSelectSql) {
// Adding user fields to SELECT
$this->Add2SqlData($ufSelectSql->GetSelect(), 'SELECT');
// Adding user fields to ORDER BY
if (is_array($arOrder)) {
foreach ($arOrder as $orderKey => $order) {
$orderSql = $ufSelectSql->GetOrder($orderKey);
if (!is_string($orderSql) || $orderSql === '') {
continue;
}
$order = strtoupper($order);
if ($order !== 'ASC' && $order !== 'DESC') {
$order = 'ASC';
}
$this->Add2SqlData("{$orderSql} {$order}", 'ORDERBY');
}
}
// Adding user fields to joins
$this->Add2SqlData($ufSelectSql->GetJoin($this->tableAlias . '.ID'), 'FROM');
}
if ($ufFilterSql) {
// Adding user fields to WHERE
$ufWhere = $ufFilterSql->GetFilter();
if ($ufWhere !== '') {
$ufSql = $this->tableAlias . '.ID IN (SELECT ' . $this->tableAlias . '.ID FROM ' . $this->tableName . ' ' . $this->tableAlias . ' ' . $ufFilterSql->GetJoin($this->tableAlias . '.ID') . ' WHERE ' . $ufWhere . ')';
// Adding user fields to joins
$this->Add2SqlData($ufSql, 'WHERE');
}
}
//Get count only
if (is_array($arGroupBy) && count($arGroupBy) == 0) {
return $this->GetRowCount(RestrictionManager::getSqlRestriction()->getRowCountThreshold());
}
$sql = 'SELECT ' . $this->sqlData['SELECT'] . ' FROM ' . $this->tableName . ' ' . $this->tableAlias;
if (isset($this->sqlData['FROM'][0])) {
$sql .= ' ' . $this->sqlData['FROM'];
}
if (isset($this->sqlData['WHERE'][0])) {
$sql .= ' WHERE ' . $this->sqlData['WHERE'];
}
if (isset($this->sqlData['GROUPBY'][0])) {
$sql .= ' GROUP BY ' . $this->sqlData['GROUPBY'];
}
if (isset($this->sqlData['ORDERBY'][0])) {
$sql .= ' ORDER BY ' . $this->sqlData['ORDERBY'];
}
$enableNavigation = is_array($arNavStartParams);
$top = $enableNavigation && isset($arNavStartParams['nTopCount']) ? intval($arNavStartParams['nTopCount']) : 0;
if ($enableNavigation && $top <= 0) {
$dbRes = new CDBResult();
if ($this->ufEntityID !== '') {
$dbRes->SetUserFields($GLOBALS['USER_FIELD_MANAGER']->GetUserFields($this->ufEntityID));
} elseif (!empty($this->userFields)) {
$dbRes->SetUserFields($this->userFields);
}
//Trace('CCrmEntityListBuilder::Prepare, SQL', $sql, 1);
$cnt = $this->GetRowCount(RestrictionManager::getSqlRestriction()->getRowCountThreshold());
$dbRes->NavQuery($sql, $cnt, $arNavStartParams);
} else {
$limit = $top;
$offset = 0;
if (isset($arOptions['QUERY_OPTIONS']) && is_array($arOptions['QUERY_OPTIONS'])) {
$queryOptions = $arOptions['QUERY_OPTIONS'];
$limit = isset($queryOptions['LIMIT']) ? (int) $queryOptions['LIMIT'] : 0;
$offset = isset($queryOptions['OFFSET']) ? (int) $queryOptions['OFFSET'] : 0;
}
$threshold = RestrictionManager::getSqlRestriction()->getRowCountThreshold();
if ($threshold > 0 && $threshold < $limit + $offset) {
$delta = $threshold - $offset;
if ($delta <= 0) {
$obRes = new CDBResult();
$obRes->InitFromArray(array());
return $obRes;
}
$limit = $delta;
}
if ($limit > 0) {
$sql = Main\Application::getConnection()->getSqlHelper()->getTopSql($sql, $limit, $offset);
}
//Trace('CCrmEntityListBuilder::Prepare, SQL', $sql, 1);
$dbRes = $DB->Query($sql, false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__);
if ($this->ufEntityID !== '') {
$dbRes->SetUserFields($GLOBALS['USER_FIELD_MANAGER']->GetUserFields($this->ufEntityID));
} elseif (!empty($this->userFields)) {
$dbRes->SetUserFields($this->userFields);
}
}
return $dbRes;
}
function GetList(&$by, &$order, $arFilter = array(), &$isFiltered, $checkRights = "Y", $getUserName = "******", $getExtraNames = "Y", $siteID = false, $arParams = array())
{
$err_mess = CTicket::err_mess() . "<br>Function: GetList<br>Line: ";
global $DB, $USER, $USER_FIELD_MANAGER;
$bAdmin = 'N';
$bSupportTeam = 'N';
$bSupportClient = 'N';
$bDemo = 'N';
$messJoin = "";
if ($checkRights == 'Y') {
$bAdmin = CTicket::IsAdmin() ? 'Y' : 'N';
$bSupportTeam = CTicket::IsSupportTeam() ? 'Y' : 'N';
$bSupportClient = CTicket::IsSupportClient() ? 'Y' : 'N';
$bDemo = CTicket::IsDemo() ? 'Y' : 'N';
$uid = intval($USER->GetID());
} else {
$bAdmin = 'Y';
$bSupportTeam = 'Y';
$bSupportClient = 'Y';
$bDemo = 'Y';
if (is_object($USER)) {
$uid = intval($USER->GetID());
} else {
$uid = -1;
}
}
if ($bAdmin != 'Y' && $bSupportTeam != 'Y' && $bSupportClient != 'Y' && $bDemo != 'Y') {
return false;
}
if ($bSupportTeam == 'Y' || $bAdmin == 'Y' || $bDemo == 'Y') {
$lamp = "\n\t\t\t\tif(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',\n\t\t\t\t\tif(ifnull(T.LAST_MESSAGE_USER_ID,0)='{$uid}', 'green',\n\t\t\t\t\t\tif(ifnull(T.OWNER_USER_ID,0)='{$uid}', 'red',\n\t\t\t\t\t\t\tif(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y','green_s',\n\t\t\t\t\t\t\t\tif(ifnull(T.RESPONSIBLE_USER_ID,0)='{$uid}', 'red',\n\t\t\t\t\t\t\t\t\t'yellow')))))\n\t\t\t\t";
} else {
$lamp = "\n\t\t\t\tif(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',\n\t\t\t\t\tif(ifnull(T.LAST_MESSAGE_USER_ID,0)='{$uid}', 'green', 'red'))\n\t\t\t\t";
}
$bJoinSupportTeamTbl = $bJoinClientTbl = false;
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
$filterKeys = array_keys($arFilter);
for ($i = 0; $i < count($filterKeys); $i++) {
$key = $filterKeys[$i];
$val = $arFilter[$filterKeys[$i]];
if (is_array($val) && count($val) <= 0 || !is_array($val) && (strlen($val) <= 0 || $val === 'NOT_REF')) {
continue;
}
$matchValueSet = in_array($key . "_EXACT_MATCH", $filterKeys) ? true : false;
$key = strtoupper($key);
switch ($key) {
case "ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T.ID", $val, $match);
break;
case "HOLD_ON":
$arSqlSearch[] = $val == "Y" ? "T.HOLD_ON='Y'" : "T.HOLD_ON = 'N'";
break;
case "LID":
case "SITE":
case "SITE_ID":
if (is_array($val)) {
$val = implode(" | ", $val);
}
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T.SITE_ID", $val, $match);
break;
case "LAMP":
if (is_array($val)) {
if (count($val) > 0) {
foreach ($val as $value) {
$str .= ", '" . $DB->ForSQL($value) . "'";
}
$str = TrimEx($str, ",");
$arSqlSearch[] = " " . $lamp . " in (" . $str . ")";
}
} elseif (strlen($val) > 0) {
$arSqlSearch[] = " " . $lamp . " = '" . $DB->ForSQL($val) . "'";
}
break;
case "DATE_CREATE_1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CREATE>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE_CREATE_2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CREATE<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
case "DATE_TIMESTAMP_1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.TIMESTAMP_X>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE_TIMESTAMP_2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.TIMESTAMP_X<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
case "DATE_CLOSE_1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CLOSE>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE_CLOSE_2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CLOSE<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
case "CLOSE":
$arSqlSearch[] = $val == "Y" ? "T.DATE_CLOSE is not null" : "T.DATE_CLOSE is null";
break;
case "AUTO_CLOSE_DAYS1":
$arSqlSearch[] = "T.AUTO_CLOSE_DAYS>='" . intval($val) . "'";
break;
case "AUTO_CLOSE_DAYS2":
$arSqlSearch[] = "T.AUTO_CLOSE_DAYS<='" . intval($val) . "'";
break;
case "TICKET_TIME_1":
$arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)>='" . intval($val) * 86400 . "'";
break;
case "TICKET_TIME_2":
$arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)<='" . intval($val) * 86400 . "'";
break;
case "TITLE":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.TITLE", $val, $match);
break;
case "MESSAGES1":
$arSqlSearch[] = "T.MESSAGES>='" . intval($val) . "'";
break;
case "MESSAGES2":
$arSqlSearch[] = "T.MESSAGES<='" . intval($val) . "'";
break;
case "PROBLEM_TIME1":
$arSqlSearch[] = "T.PROBLEM_TIME>='" . intval($val) . "'";
break;
case "PROBLEM_TIME2":
$arSqlSearch[] = "T.PROBLEM_TIME<='" . intval($val) . "'";
break;
case "OVERDUE_MESSAGES1":
$arSqlSearch[] = "T.OVERDUE_MESSAGES>='" . intval($val) . "'";
break;
case "OVERDUE_MESSAGES2":
$arSqlSearch[] = "T.OVERDUE_MESSAGES<='" . intval($val) . "'";
break;
case "AUTO_CLOSE_DAYS_LEFT1":
$arSqlSearch[] = "TO_DAYS(ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY))-TO_DAYS(now())>='" . intval($val) . "'";
break;
case "AUTO_CLOSE_DAYS_LEFT2":
$arSqlSearch[] = "TO_DAYS(ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY))-TO_DAYS(now())<='" . intval($val) . "'";
break;
case "OWNER":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.OWNER_USER_ID, UO.LOGIN, UO.LAST_NAME, UO.NAME, T.OWNER_SID", $val, $match, array("@", "."));
break;
case "OWNER_USER_ID":
case "OWNER_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T." . $key, $val, $match);
break;
case "SLA_ID":
case "SLA":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match);
break;
case "CREATED_BY":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.CREATED_USER_ID, UC.LOGIN, UC.LAST_NAME, UC.NAME, T.CREATED_MODULE_NAME", $val, $match);
break;
case "RESPONSIBLE":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.RESPONSIBLE_USER_ID, UR.LOGIN, UR.LAST_NAME, UR.NAME", $val, $match);
break;
case "RESPONSIBLE_ID":
if (intval($val) > 0) {
$arSqlSearch[] = "T.RESPONSIBLE_USER_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.RESPONSIBLE_USER_ID is null or T.RESPONSIBLE_USER_ID=0)";
}
break;
case "CATEGORY_ID":
case "CATEGORY":
if (intval($val) > 0) {
$arSqlSearch[] = "T.CATEGORY_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)";
}
break;
case "CATEGORY_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DC.SID", $val, $match);
break;
case "CRITICALITY_ID":
case "CRITICALITY":
if (intval($val) > 0) {
$arSqlSearch[] = "T.CRITICALITY_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)";
}
break;
case "CRITICALITY_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DK.SID", $val, $match);
break;
case "STATUS_ID":
case "STATUS":
if (intval($val) > 0) {
$arSqlSearch[] = "T.STATUS_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)";
}
break;
case "STATUS_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DS.SID", $val, $match);
break;
case "MARK_ID":
case "MARK":
if (intval($val) > 0) {
$arSqlSearch[] = "T.MARK_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)";
}
break;
case "MARK_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DM.SID", $val, $match);
break;
case "SOURCE_ID":
case "SOURCE":
if (intval($val) > 0) {
$arSqlSearch[] = "T.SOURCE_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)";
}
break;
case "SOURCE_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DSR.SID", $val, $match);
break;
case "DIFFICULTY_ID":
case "DIFFICULTY":
if (intval($val) > 0) {
$arSqlSearch[] = "T.DIFFICULTY_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)";
}
break;
case "DIFFICULTY_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DD.SID", $val, $match);
break;
case "MODIFIED_BY":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.MODIFIED_USER_ID, T.MODIFIED_MODULE_NAME, UM.LOGIN, UM.LAST_NAME, UM.NAME", $val, $match);
break;
case "MESSAGE":
global $strError;
if (strlen($val) <= 0) {
break;
}
if ($bSupportTeam == "Y" || $bAdmin == "Y" || $bDemo == "Y") {
$messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID)";
} else {
$messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID and M.IS_HIDDEN='N' and M.IS_LOG='N')";
}
if (CSupportSearch::checkModule()) {
$arSqlSearch[] = CSupportSearch::GetFilterQuery($val, "M.ID", "T.TITLE", "M.MESSAGE", $strError);
} else {
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$f = new CFilterQuery("OR", "yes", $match, array(), "N", "Y", "N");
$query = $f->GetQueryString("T.TITLE,M.MESSAGE_SEARCH", $val);
$error = $f->error;
if (strlen(trim($error)) > 0) {
$strError .= $error . "<br>";
$query = "0";
} else {
$arSqlSearch[] = $query;
}
}
break;
case "LAST_MESSAGE_USER_ID":
case "LAST_MESSAGE_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T." . $key, $val, $match);
break;
case "SUPPORT_COMMENTS":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.SUPPORT_COMMENTS", $val, $match);
break;
case "IS_SPAM":
$arSqlSearch[] = $val == "Y" ? "T.IS_SPAM ='Y'" : "(T.IS_SPAM = 'N' or T.IS_SPAM is null)";
break;
case "IS_OVERDUE":
$arSqlSearch[] = $val == "Y" ? "T.IS_OVERDUE ='Y'" : "(T.IS_OVERDUE = 'N' or T.IS_OVERDUE is null)";
break;
case "IS_SPAM_MAYBE":
$arSqlSearch[] = $val == "Y" ? "T.IS_SPAM='N'" : "(T.IS_SPAM='Y' or T.IS_SPAM is null)";
break;
case 'SUPPORTTEAM_GROUP_ID':
case 'CLIENT_GROUP_ID':
if ($key == 'SUPPORTTEAM_GROUP_ID') {
$table = 'UGS';
$bJoinSupportTeamTbl = true;
} else {
$table = 'UGC';
$bJoinClientTbl = true;
}
if (is_array($val)) {
$val = array_map('intval', $val);
$val = array_unique($val);
$val = array_filter($val);
if (count($val) > 0) {
$arSqlSearch[] = '(' . $table . '.GROUP_ID IS NOT NULL AND ' . $table . '.GROUP_ID IN (' . implode(',', $val) . '))';
}
} else {
$val = intval($val);
if ($val > 0) {
$arSqlSearch[] = '(' . $table . '.GROUP_ID IS NOT NULL AND ' . $table . '.GROUP_ID=\'' . $val . '\')';
}
}
break;
case 'COUPON':
$match = $matchValueSet && $arFilter[$key . "_EXACT_MATCH"] != "Y" ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T." . $key, $val, $match);
break;
}
}
}
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("SUPPORT", "T.ID");
$obUserFieldsSql->SetSelect($arParams["SELECT"]);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder(array($by => $order));
if ($by == "s_id") {
$strSqlOrder = "ORDER BY T.ID";
} elseif ($by == "s_last_message_date") {
$strSqlOrder = "ORDER BY T.LAST_MESSAGE_DATE";
} elseif ($by == "s_site_id" || $by == "s_lid") {
$strSqlOrder = "ORDER BY T.SITE_ID";
} elseif ($by == "s_lamp") {
$strSqlOrder = "ORDER BY LAMP";
} elseif ($by == "s_is_overdue") {
$strSqlOrder = "ORDER BY T.IS_OVERDUE";
} elseif ($by == "s_is_notified") {
$strSqlOrder = "ORDER BY T.IS_NOTIFIED";
} elseif ($by == "s_date_create") {
$strSqlOrder = "ORDER BY T.DATE_CREATE";
} elseif ($by == "s_timestamp") {
$strSqlOrder = "ORDER BY T.TIMESTAMP_X";
} elseif ($by == "s_date_close") {
$strSqlOrder = "ORDER BY T.DATE_CLOSE";
} elseif ($by == "s_owner") {
$strSqlOrder = "ORDER BY T.OWNER_USER_ID";
} elseif ($by == "s_modified_by") {
$strSqlOrder = "ORDER BY T.MODIFIED_USER_ID";
} elseif ($by == "s_title") {
$strSqlOrder = "ORDER BY T.TITLE ";
} elseif ($by == "s_responsible") {
$strSqlOrder = "ORDER BY T.RESPONSIBLE_USER_ID";
} elseif ($by == "s_messages") {
$strSqlOrder = "ORDER BY T.MESSAGES";
} elseif ($by == "s_category") {
$strSqlOrder = "ORDER BY T.CATEGORY_ID";
} elseif ($by == "s_criticality") {
$strSqlOrder = "ORDER BY T.CRITICALITY_ID";
} elseif ($by == "s_sla") {
$strSqlOrder = "ORDER BY T.SLA_ID";
} elseif ($by == "s_status") {
$strSqlOrder = "ORDER BY T.STATUS_ID";
} elseif ($by == "s_difficulty") {
$strSqlOrder = "ORDER BY T.DIFFICULTY_ID";
} elseif ($by == "s_problem_time") {
$strSqlOrder = "ORDER BY T.PROBLEM_TIME";
} elseif ($by == "s_mark") {
$strSqlOrder = "ORDER BY T.MARK_ID";
} elseif ($by == "s_online") {
$strSqlOrder = "ORDER BY USERS_ONLINE";
} elseif ($by == "s_support_comments") {
$strSqlOrder = "ORDER BY T.SUPPORT_COMMENTS";
} elseif ($by == "s_auto_close_days_left") {
$strSqlOrder = "ORDER BY AUTO_CLOSE_DAYS_LEFT";
} elseif ($by == 's_coupon') {
$strSqlOrder = 'ORDER BY T.COUPON';
} elseif ($by == 's_deadline') {
$strSqlOrder = 'ORDER BY T.SUPPORT_DEADLINE';
} elseif ($s = $obUserFieldsSql->GetOrder($by)) {
$strSqlOrder = "ORDER BY " . strtoupper($s);
} else {
$by = "s_default";
$strSqlOrder = "ORDER BY IS_SUPER_TICKET DESC, T.IS_OVERDUE DESC, T.IS_NOTIFIED DESC, T.LAST_MESSAGE_DATE";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$arSqlSearch[] = $obUserFieldsSql->GetFilter();
if ($getUserName == "Y") {
$u_select = "\n\t\t\t\t,\n\t\t\t\tUO.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_LOGIN,\n\t\t\t\tUO.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_EMAIL,\n\t\t\t\tconcat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,''))\t\tOWNER_NAME,\n\t\t\t\tUR.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_LOGIN,\n\t\t\t\tUR.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_EMAIL,\n\t\t\t\tconcat(ifnull(UR.NAME,''),' ',ifnull(UR.LAST_NAME,''))\t\tRESPONSIBLE_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_BY_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_NAME,\n\t\t\t\tUL.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_LOGIN,\n\t\t\t\tUL.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_EMAIL,\n\t\t\t\tconcat(ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,''))\t\tLAST_MESSAGE_NAME,\n\t\t\t\tUC.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_LOGIN,\n\t\t\t\tUC.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_EMAIL,\n\t\t\t\tconcat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,''))\t\tCREATED_NAME\n\t\t\t";
$u_join = "\n\t\t\tLEFT JOIN b_user UO ON (UO.ID = T.OWNER_USER_ID)\n\t\t\tLEFT JOIN b_user UR ON (UR.ID = T.RESPONSIBLE_USER_ID)\n\t\t\tLEFT JOIN b_user UM ON (UM.ID = T.MODIFIED_USER_ID)\n\t\t\tLEFT JOIN b_user UL ON (UL.ID = T.LAST_MESSAGE_USER_ID)\n\t\t\tLEFT JOIN b_user UC ON (UC.ID = T.CREATED_USER_ID)\n\t\t\t";
}
if ($getExtraNames == "Y") {
$d_select = "\n\t\t\t\t,\n\t\t\t\tDC.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_NAME,\n\t\t\t\tDC.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_DESC,\n\t\t\t\tDC.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_SID,\n\t\t\t\tDK.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_NAME,\n\t\t\t\tDK.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_DESC,\n\t\t\t\tDK.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_SID,\n\t\t\t\tDS.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_NAME,\n\t\t\t\tDS.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_DESC,\n\t\t\t\tDS.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_SID,\n\t\t\t\tDM.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_NAME,\n\t\t\t\tDM.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_DESC,\n\t\t\t\tDM.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_SID,\n\t\t\t\tDSR.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_NAME,\n\t\t\t\tDSR.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_DESC,\n\t\t\t\tDSR.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_SID,\n\t\t\t\tDD.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_NAME,\n\t\t\t\tDD.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_DESC,\n\t\t\t\tDD.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_SID,\n\t\t\t\tSLA.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSLA_NAME\n\t\t\t";
$d_join = "\n\t\t\tLEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')\n\t\t\tLEFT JOIN b_ticket_dictionary DK ON (DK.ID = T.CRITICALITY_ID and DK.C_TYPE = 'K')\n\t\t\tLEFT JOIN b_ticket_dictionary DS ON (DS.ID = T.STATUS_ID and DS.C_TYPE = 'S')\n\t\t\tLEFT JOIN b_ticket_dictionary DM ON (DM.ID = T.MARK_ID and DM.C_TYPE = 'M')\n\t\t\tLEFT JOIN b_ticket_dictionary DSR ON (DSR.ID = T.SOURCE_ID and DSR.C_TYPE = 'SR')\n\t\t\tLEFT JOIN b_ticket_dictionary DD ON (DD.ID = T.DIFFICULTY_ID and DD.C_TYPE = 'D')\n\t\t\tLEFT JOIN b_ticket_sla SLA ON (SLA.ID = T.SLA_ID)\n\t\t\t";
}
if (strlen($siteID) > 0) {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL", $siteID, true) . "\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL", $siteID, true) . "\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL", $siteID, true) . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL", $siteID, true) . "\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT", $siteID, true) . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT", $siteID, true) . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT", $siteID, true) . "\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL", $siteID, true) . "\tSUPPORT_DEADLINE,\n\t\t\t\t" . $DB->DateToCharFunction("ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL", $siteID, true) . "\tAUTO_CLOSE_DATE\n\t\t\t";
} else {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL") . "\t\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL") . "\t\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL") . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL") . "\t\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT") . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT") . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT") . "\t\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL") . "\tSUPPORT_DEADLINE,\n\t\t\t\t" . $DB->DateToCharFunction("ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL") . "\tAUTO_CLOSE_DATE\n\t\t\t";
}
$ugroupJoin = '';
$strSqlSearchUser = '';
if ($bJoinSupportTeamTbl || !($bAdmin == 'Y' || $bDemo == 'Y') && $bSupportTeam == 'Y') {
$ugroupJoin .= "\n\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGS ON (UGS.USER_ID = T.RESPONSIBLE_USER_ID) ";
}
if ($bJoinClientTbl || !($bAdmin == 'Y' || $bDemo == 'Y') && $bSupportClient == 'Y') {
$ugroupJoin .= "\n\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGC ON (UGC.USER_ID = T.OWNER_USER_ID) ";
}
if (!($bAdmin == 'Y' || $bDemo == 'Y')) {
$strSqlSearchUser = "******";
if ($bSupportTeam == 'Y') {
$strSqlSearchUser .= " OR (UGS2.USER_ID IS NOT NULL AND UGS2.USER_ID='{$uid}' AND UUS.IS_TEAM_GROUP IS NOT NULL AND UUS.IS_TEAM_GROUP='Y')";
$ugroupJoin .= "\n\t\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGS2 ON (UGS2.GROUP_ID = UGS.GROUP_ID AND UGS2.CAN_VIEW_GROUP_MESSAGES = 'Y')\n\t\t\t\t\tLEFT JOIN b_ticket_ugroups UUS ON (UUS.ID = UGS.GROUP_ID) ";
} elseif ($bSupportClient == 'Y') {
$strSqlSearchUser .= " OR (UGC2.USER_ID IS NOT NULL AND UGC2.USER_ID='{$uid}' AND UUC.IS_TEAM_GROUP IS NOT NULL AND UUC.IS_TEAM_GROUP<>'Y')";
$ugroupJoin .= "\n\t\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGC2 ON (UGC2.GROUP_ID = UGC.GROUP_ID AND UGC2.CAN_VIEW_GROUP_MESSAGES = 'Y')\n\t\t\t\t\tLEFT JOIN b_ticket_ugroups UUC ON (UUC.ID = UGC.GROUP_ID) ";
}
$strSqlSearchUser .= ')';
$arSqlSearch[] = $strSqlSearchUser;
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL"));
$strSql = "\n\t\t\tSELECT\n\t\t\t\tT.*,\n\t\t\t\tT.SITE_ID,\n\t\t\t\tT.SITE_ID\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLID,\n\t\t\t\t{$dates_select},\n\t\t\t\tUNIX_TIMESTAMP(T.DATE_CLOSE)-UNIX_TIMESTAMP(T.DATE_CREATE)\t\t\t\t\t\t\tTICKET_TIME,\n\t\t\t\tTO_DAYS(ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY))-TO_DAYS(now())\t\tAUTO_CLOSE_DAYS_LEFT,\n\t\t\t\tcount(distinct TN.USER_ID)\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tUSERS_ONLINE,\n\t\t\t\tif(T.COUPON IS NOT NULL, 1, 0)\t\t\t\t\t\t\t\t\t\t\t\t\t\tIS_SUPER_TICKET,\n\t\t\t\t{$lamp}\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLAMP\n\t\t\t\t{$d_select}\n\t\t\t\t{$u_select}\n\t\t\t\t" . $obUserFieldsSql->GetSelect() . "\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\tLEFT JOIN b_ticket_online TN ON (TN.TICKET_ID = T.ID and TN.TIMESTAMP_X >= DATE_ADD(now(), INTERVAL - {$onlineInterval} SECOND))\n\t\t\t{$u_join}\n\t\t\t{$d_join}\n\t\t\t{$messJoin}\n\t\t\t{$ugroupJoin}\n\t\t\t\t" . $obUserFieldsSql->GetJoin("T.ID") . "\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tGROUP BY\n\t\t\t\tT.ID\n\t\t\t{$strSqlOrder}\n\t\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
$isFiltered = IsFiltered($strSqlSearch);
return $res;
}
/**
* <p>Функция предназначена для получения списка обращений в техподдержку.</p>
*
*
*
*
* @param varchar &$by Идентификатор, позволяющий задать имя поля для сортировки.
* Допустимы следующие значения: <ul> <li>s_id - по ID </li> <li>s_lid - по сайту,
* для которого было создано обращение </li> <li>s_lamp - по индикатору </li>
* <li>s_date_create - по дате создания </li> <li>s_timestamp - по дате изменения </li>
* <li>s_date_close - по дате закрытия </li> <li>s_owner - по автору </li> <li>s_modified_by - по
* ID пользователя - изменившего обращение </li> <li>s_title - по заголовку
* </li> <li>s_responsible - по ID ответственного </li> <li>s_messages - по количеству
* сообщений </li> <li>s_category - по ID категории </li> <li>s_criticality - по ID
* критичности </li> <li>s_status - по ID статуса </li> <li>s_mark - по ID оценки </li>
* <li>s_online - по количеству пользователей </li> </ul>
*
*
*
* @param varchar &$order Порядок сортировки. Допустимы следующие значения: <ul> <li>desc - по
* убыванию (значение по умолчанию) </li> <li>asc - по возрастанию </li> </ul>
*
*
*
* @param array $arFilter = array() Массив для фильтрации значений. Необязательный параметр. В
* массиве допустимы следующие индексы: <ul> <li>ID - ID обращения
* (допускается сложная логика) </li> <li>ID_EXACT_MATCH - "Y" - при фильтрации по
* ID обращения будет искаться точное совпадение (по умолчанию); "N" - в
* противном случае будет искаться вхождение </li> <li>LID - ID сайта </li>
* <li>LID_EXACT_MATCH - "Y" - при фильтрации по ID языка будет искаться точное
* совпадение (по умолчанию); "N" - в противном случае будет искаться
* вхождение </li> <li>LAMP - массив индикаторов: "red", "yellow", "green", "green_s", "grey"
* </li> <li>DATE_CREATE_1 - левая часть интервала для даты создания обращения
* </li> <li>DATE_CREATE_2 - правая часть интервала для даты создания обращения
* </li> <li>DATE_TIMESTAMP_1 - левая часть интервала для даты модификации
* обращения </li> <li>DATE_TIMESTAMP_2 - правая часть интервала для даты
* модификации обращения </li> <li>DATE_CLOSE_1 - левая часть интервала для
* даты закрытия обращения </li> <li>DATE_CLOSE_2 - правая часть интервала для
* даты закрытия обращения </li> <li>AUTO_CLOSE_DAYS1 - левая часть интервала
* для количества дней по истечении которых обращение будет
* автоматически закрыто при отсутствии ответа от автора </li>
* <li>AUTO_CLOSE_DAYS2 - правая часть интервала для количества дней по
* истечении которых обращение будет автоматически закрыто при
* отсутствии ответа от автора </li> <li>TICKET_TIME_1 - левая часть интервала
* для количества дней прошедших с даты создания до даты закрытия
* обращения </li> <li>TICKET_TIME_2 - правая часть интервала для количества
* дней прошедших с даты создания до даты закрытия обращения </li>
* <li>TITLE - заголовок обращения (допускается сложная логика) </li>
* <li>TITLE_EXACT_MATCH - "Y" - при фильтрации по заголовку обращения будет
* искаться точное совпадение (по умолчанию); "N" - в противном случае
* будет искаться вхождение </li> <li>MESSAGES1 - левая часть интервала для
* количества сообщений в обращении </li> <li>MESSAGES2 - правая часть
* интервала для количества сообщений в обращении </li> <li>OWNER - автор
* обращения, поиск осуществляется по ID пользователя, логину, имени,
* фамилии, символному коду (допускается сложная логика) </li>
* <li>OWNER_EXACT_MATCH - "Y" - при фильтрации по автору обращения будет
* искаться точное совпадение (по умолчанию); "N" - в противном случае
* будет искаться вхождение </li> <li>CREATED_BY - кем было создано обращение,
* поиск осуществляется по ID пользователя, логину, имени, фамилии,
* названии модуля из которого было создано обращение (допускается
* сложная логика) </li> <li>CREATED_BY_EXACT_MATCH - "Y" - при фильтрации по
* создателю обращения будет искаться точное совпадение (по
* умолчанию); "N" - в противном случае будет искаться вхождение </li>
* <li>MODIFIED_BY - изменивший обращение, поиск осуществляется по ID
* пользователя, логину, имени, фамилии, названии модуля из которого
* было изменено обращение (допускается сложная логика) </li>
* <li>MODIFIED_BY_EXACT_MATCH - "Y" - при фильтрации по символьному коду статуса
* будет искаться точное совпадение (по умолчанию); "N" - в противном
* случае будет искаться вхождение </li> <li>RESPONSIBLE - ответственный за
* обращение, поиск осуществляется по ID пользователя, логину, имени,
* фамилии (допускается сложная логика) </li> <li>RESPONSIBLE_EXACT_MATCH - "Y" - при
* фильтрации по ответственному за обращение будет искаться точное
* совпадение (по умолчанию); "N" - в противном случае будет искаться
* вхождение </li> <li>RESPONSIBLE_ID - ID пользователя ответственного за
* обращение, при задании "0" будут выбраны только те обращения у
* которых нет ответственного </li> <li>CATEGORY - ID категории обращения (0 -
* без категории) </li> <li>CATEGORY_SID - символьный код категории обращения
* (допускается сложная логика) </li> <li>CATEGORY_SID_EXACT_MATCH - "Y" - при
* фильтрации по символьному коду категории будет искаться точное
* совпадение (по умолчанию); "N" - в противном случае будет искаться
* вхождение </li> <li>CRITICALITY - ID критичности обращения (0 - без
* критичности) </li> <li>CRITICALITY_SID - символьный код критичности
* обращения (допускается сложная логика) </li> <li>CRITICALITY_SID_EXACT_MATCH - "Y" -
* при фильтрации по символьному коду критичности будет искаться
* точное совпадение (по умолчанию); "N" - в противном случае будет
* искаться вхождение </li> <li>STATUS - ID статуса обращения (0 - без статуса)
* </li> <li>STATUS_SID - символьный код статуса обращения (допускается
* сложная логика) </li> <li>STATUS_SID_EXACT_MATCH - "Y" - при фильтрации по
* символьному коду статуса будет искаться точное совпадение (по
* умолчанию); "N" - в противном случае будет искаться вхождение </li>
* <li>STATUS_SID_EXACT_MATCH - "Y" - при фильтрации по символьному коду оценки
* ответа будет искаться точное совпадение (по умолчанию); "N" - в
* противном случае будет искаться вхождение </li> <li>MARK - ID оценки
* ответов (0 - без оценки) </li> <li>MARK_SID - символьный код оценки ответов
* (допускается сложная логика) </li> <li>SOURCE - ID источника (0 - источник
* "web") </li> <li>SOURCE_SID - символьный код источника (допускается сложная
* логика) </li> <li>SOURCE_SID_EXACT_MATCH - "Y" - при фильтрации по символьному коду
* источника будет искаться точное совпадение (по умолчанию); "N" - в
* противном случае будет искаться вхождение </li> <li>MESSAGE - текст
* сообщения (допускается сложная логика) </li> <li>MESSAGE_EXACT_MATCH - "Y" - при
* фильтрации по сообщению будет искаться точное совпадение (по
* умолчанию); "N" - в противном случае будет искаться вхождение </li>
* <li>LAST_MESSAGE_USER_ID - ID пользователя написавшего последнее сообщение в
* обращении (допускается сложная логика) </li> <li>LAST_MESSAGE_USER_ID_EXACT_MATCH - "Y"
* - при фильтрации по ID пользователя написавшего последнее
* сообщение в обращении будет искаться точное совпадение (по
* умолчанию); "N" - в противном случае будет искаться вхождение </li>
* <li>LAST_MESSAGE_SID - символьный идентификатор написавшего последнее
* сообщение в обращении (если источник "email", то это может быть email,
* если источник "телефон", то это может быть номер телефона)
* (допускается сложная логика) </li> <li>LAST_MESSAGE_SID_EXACT_MATCH - "Y" - при
* фильтрации по символьному идентификатору пользователя
* написавшего последнее сообщение в обращении будет искаться
* точное совпадение (по умолчанию); "N" - в противном случае будет
* искаться вхождение </li> <li>SUPPORT_COMMENTS - комментарий техподдержки,
* видимый в стандартных формах только для пользователей входящих в
* группу техподдержки </li> <li>SUPPORT_COMMENTS_EXACT_MATCH - "Y" - при фильтрации по ID
* пользователя написавшего последнее сообщение в обращении будет
* искаться точное совпадение (по умолчанию); "N" - в противном случае
* будет искаться вхождение </li> <li>SITE_ID - идентификатор сайта</li> <li>CLOSE
* - Y\N - закрыт тикет, или нет</li> <li>S_SPAM - Y\N - флаг, является ли тикет
* спамом</li> <li>IS_OVERDUE - Y\N - флаг, просрочен ли тикет.</li> <li>SLA_ID - по ID
* уровня техподдержки (допускается сложная логика).</li>
* <li>SUPPORTTEAM_GROUP_ID - по ID группы техподдержки (число или массив
* чисел).</li> <li>CLIENT_GROUP_ID - Y\N - по ID группы клиентов (число или массив
* чисел).</li> </ul>
*
*
*
* @param boolean &$is_filtered Переменная возвращающая true в том случае если список результатов
* отфильтрован по какому либо критерию; либо false в противном случае.
* Изменен на <b>isFiltered</b> c 12.0.0
*
*
*
* @param char(1) $CHECK_RIGHTS = "Y" Необязательный параметр. "Y" - будут выбраны только те обращения
* которые текущий пользователь может просматривать (по умолчанию);
* "N" - выбирать все обращения независимо от прав текущего
* пользователя. Изменен на <b>checkRights</b> c 12.0.0
*
*
*
* @param char(1) $get_user_name = "Y" Необязательный параметр. "Y" - при выборке обращений будут также
* выбраны такие поля как OWNER_LOGIN, OWNER_NAME, RESPONSIBLE_LOGIN, RESPONSIBLE_NAME, MODIFIED_LOGIN,
* MODIFIED_NAME, LAST_MESSAGE_LOGIN, LAST_MESSAGE_NAME, CREATED_LOGIN, CREATED_EMAIL, CREATED_NAME, описывающие
* параметры пользователей имевших отношение к данному обращению
* (по умолчанию); "N" - вышеперечисленные поля не будут выбраны, но
* зато это ускорит работу функции. Изменен на <b>getUserName</b> c 12.0.0
*
*
*
* @param char(1) $get_dictionary_name = "Y" Необязательный параметр. "Y" - при выборке обращений будут также
* выбраны такие поля как CATEGORY_NAME, CATEGORY_SID, CRITICALITY_NAME, CRITICALITY_SID, STATUS_NAME,
* STATUS_SID, MARK_NAME, MARK_SID, SOURCE_NAME, SOURCE_SID, описывающие поля из справочника
* техподдержки (по умолчанию); "N" - вышеперечисленные поля не будут
* выбраны, но зато это ускорит работу функции. Удален с 4.0.6
*
*
*
* @param char(2) $lang = LANG Двухсимвольный код языка в формате которого необходимо выбрать
* временные параметры обращения (время создания, изменения,
* закрытия); необязательный параметр, по умолчанию - код текущего
* сайта. Удален с 4.0.0
*
*
*
* @return record
*
*
* <h4>Example</h4>
* <pre>
* <?<br>$FilterArr = Array(<br> "find_id",<br> "find_lid",<br> "find_lamp",<br> "find_date_create1",<br> "find_date_create2",<br> "find_date_timestamp1",<br> "find_date_timestamp2",<br> "find_date_close1",<br> "find_date_close2",<br> "find_close",<br> "find_ticket_time_1",<br> "find_ticket_time_2",<br> "find_title",<br> "find_messages1",<br> "find_messages2",<br> "find_owner",<br> "find_created_by",<br> "find_responsible",<br> "find_responsible_id",<br> "find_category_id",<br> "find_criticality_id",<br> "find_status_id",<br> "find_mark_id",<br> "find_source_id",<br> "find_modified_by",<br> "find_message"<br> );<br>if (strlen($set_filter)>0) InitFilterEx($FilterArr,"TICKET_LIST","set",true); else InitFilterEx($FilterArr,"TICKET_LIST","get",true);<br>if (strlen($del_filter)>0) DelFilterEx($FilterArr,"TICKET_LIST",true);<br>if (CheckFilter())<br>{<br> $arFilter = Array(<br> "ID" => $find_id,<br> "LID" => $find_lid,<br> "LAMP" => $find_lamp,<br> "DATE_CREATE_1" => $find_date_create1,<br> "DATE_CREATE_2" => $find_date_create2,<br> "DATE_TIMESTAMP_1" => $find_date_timestamp1,<br> "DATE_TIMESTAMP_2" => $find_date_timestamp2,<br> "DATE_CLOSE_1" => $find_date_close1,<br> "DATE_CLOSE_2" => $find_date_close2,<br> "CLOSE" => $find_close,<br> "TICKET_TIME_1" => $find_ticket_time_1,<br> "TICKET_TIME_2" => $find_ticket_time_2,<br> "TITLE" => $find_title,<br> "MESSAGES1" => $find_messages1,<br> "MESSAGES2" => $find_messages2,<br> "OWNER" => $find_owner,<br> "CREATED_BY" => $find_created_by,<br> "RESPONSIBLE" => $find_responsible,<br> "RESPONSIBLE_ID" => $find_responsible_id,<br> "CATEGORY" => $find_category_id,<br> "CRITICALITY" => $find_criticality_id,<br> "STATUS" => $find_status_id,<br> "MARK" => $find_mark_id,<br> "SOURCE" => $find_source_id,<br> "MODIFIED_BY" => $find_modified_by,<br> "MESSAGE" => $find_message<br> );<br>}<br>$tickets = <b>CTicket::GetList</b>($by, $order, $arFilter, $is_filtered);<br>?><br>
* </pre>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/support/classes/cticket/getlist.php
* @author Bitrix
*/
public static function GetList(&$by, &$order, $arFilter = array(), &$isFiltered, $checkRights = "Y", $getUserName = "******", $getExtraNames = "Y", $siteID = false, $arParams = array())
{
$err_mess = CTicket::err_mess() . "<br>Function: GetList<br>Line: ";
global $DB, $USER, $USER_FIELD_MANAGER;
/** @var string $d_join Dictionary join */
$d_join = "";
$bAdmin = 'N';
$bSupportTeam = 'N';
$bSupportClient = 'N';
$bDemo = 'N';
/** @var string $messJoin Messages join */
$messJoin = "";
/** @var string $searchJoin Search table join */
$searchJoin = '';
$need_group = false;
$arSqlHaving = array();
if ($checkRights == 'Y') {
$bAdmin = CTicket::IsAdmin() ? 'Y' : 'N';
$bSupportTeam = CTicket::IsSupportTeam() ? 'Y' : 'N';
$bSupportClient = CTicket::IsSupportClient() ? 'Y' : 'N';
$bDemo = CTicket::IsDemo() ? 'Y' : 'N';
$uid = intval($USER->GetID());
} else {
$bAdmin = 'Y';
$bSupportTeam = 'Y';
$bSupportClient = 'Y';
$bDemo = 'Y';
if (is_object($USER)) {
$uid = intval($USER->GetID());
} else {
$uid = -1;
}
}
if ($bAdmin != 'Y' && $bSupportTeam != 'Y' && $bSupportClient != 'Y' && $bDemo != 'Y') {
return false;
}
if ($bSupportTeam == 'Y' || $bAdmin == 'Y' || $bDemo == 'Y') {
$lamp = "\n\t\t\t\tif(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',\n\t\t\t\t\tif(ifnull(T.LAST_MESSAGE_USER_ID,0)='{$uid}', 'green',\n\t\t\t\t\t\tif(ifnull(T.OWNER_USER_ID,0)='{$uid}', 'red',\n\t\t\t\t\t\t\tif(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y','green_s',\n\t\t\t\t\t\t\t\tif(ifnull(T.RESPONSIBLE_USER_ID,0)='{$uid}', 'red',\n\t\t\t\t\t\t\t\t\t'yellow')))))\n\t\t\t\t";
} else {
$lamp = "\n\t\t\t\tif(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',\n\t\t\t\t\tif(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y', 'red', 'green'))\n\t\t\t\t";
}
$bJoinSupportTeamTbl = $bJoinClientTbl = false;
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
$filterKeys = array_keys($arFilter);
$filterKeysCount = count($filterKeys);
for ($i = 0; $i < $filterKeysCount; $i++) {
$key = $filterKeys[$i];
$val = $arFilter[$filterKeys[$i]];
if (is_array($val) && count($val) <= 0 || !is_array($val) && (strlen($val) <= 0 || $val === 'NOT_REF')) {
continue;
}
$matchValueSet = in_array($key . "_EXACT_MATCH", $filterKeys) ? true : false;
$key = strtoupper($key);
switch ($key) {
case "ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T.ID", $val, $match);
break;
case "HOLD_ON":
$arSqlSearch[] = $val == "Y" ? "T.HOLD_ON='Y'" : "T.HOLD_ON = 'N'";
break;
case "LID":
case "SITE":
case "SITE_ID":
if (is_array($val)) {
$val = implode(" | ", $val);
}
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T.SITE_ID", $val, $match);
break;
case "LAMP":
if (is_array($val)) {
if (count($val) > 0) {
$str = "";
foreach ($val as $value) {
$str .= ", '" . $DB->ForSQL($value) . "'";
}
$str = TrimEx($str, ",");
$arSqlSearch[] = " " . $lamp . " in (" . $str . ")";
}
} elseif (strlen($val) > 0) {
$arSqlSearch[] = " " . $lamp . " = '" . $DB->ForSQL($val) . "'";
}
break;
case "DATE_CREATE_1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CREATE>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE_CREATE_2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CREATE<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
case "DATE_TIMESTAMP_1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.TIMESTAMP_X>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE_TIMESTAMP_2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.TIMESTAMP_X<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
case "DATE_CLOSE_1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CLOSE>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE_CLOSE_2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "T.DATE_CLOSE<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
case "CLOSE":
$arSqlSearch[] = $val == "Y" ? "T.DATE_CLOSE is not null" : "T.DATE_CLOSE is null";
break;
case "AUTO_CLOSE_DAYS1":
$arSqlSearch[] = "T.AUTO_CLOSE_DAYS>='" . intval($val) . "'";
break;
case "AUTO_CLOSE_DAYS2":
$arSqlSearch[] = "T.AUTO_CLOSE_DAYS<='" . intval($val) . "'";
break;
case "TICKET_TIME_1":
$arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)>='" . intval($val) * 86400 . "'";
break;
case "TICKET_TIME_2":
$arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)<='" . intval($val) * 86400 . "'";
break;
case "TITLE":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.TITLE", $val, $match);
break;
case "MESSAGES1":
$arSqlSearch[] = "T.MESSAGES>='" . intval($val) . "'";
break;
case "MESSAGES2":
$arSqlSearch[] = "T.MESSAGES<='" . intval($val) . "'";
break;
case "PROBLEM_TIME1":
$arSqlSearch[] = "T.PROBLEM_TIME>='" . intval($val) . "'";
break;
case "PROBLEM_TIME2":
$arSqlSearch[] = "T.PROBLEM_TIME<='" . intval($val) . "'";
break;
case "OVERDUE_MESSAGES1":
$arSqlSearch[] = "T.OVERDUE_MESSAGES>='" . intval($val) . "'";
break;
case "OVERDUE_MESSAGES2":
$arSqlSearch[] = "T.OVERDUE_MESSAGES<='" . intval($val) . "'";
break;
case "AUTO_CLOSE_DAYS_LEFT1":
$arSqlSearch[] = "CASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN\n\t\t\t\t\t\t\tTO_DAYS(ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)) - TO_DAYS(now()) ELSE -1 END >='" . intval($val) . "'";
break;
case "AUTO_CLOSE_DAYS_LEFT2":
$arSqlSearch[] = "CASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN\n\t\t\t\t\t\t\tTO_DAYS(ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY))-TO_DAYS(now()) ELSE 999 END <='" . intval($val) . "'";
break;
case "OWNER":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("UO.ID, UO.LOGIN, UO.LAST_NAME, UO.NAME", $val, $match, array("@", "."));
//T.OWNER_USER_ID,
break;
case "OWNER_USER_ID":
case "OWNER_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T." . $key, $val, $match);
break;
case "SLA_ID":
case "SLA":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match);
break;
case "CREATED_BY":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.CREATED_USER_ID, UC.LOGIN, UC.LAST_NAME, UC.NAME, T.CREATED_MODULE_NAME", $val, $match);
break;
case "RESPONSIBLE":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.RESPONSIBLE_USER_ID, UR.LOGIN, UR.LAST_NAME, UR.NAME", $val, $match);
break;
case "RESPONSIBLE_ID":
if (intval($val) > 0) {
$arSqlSearch[] = "T.RESPONSIBLE_USER_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.RESPONSIBLE_USER_ID is null or T.RESPONSIBLE_USER_ID=0)";
}
break;
case "CATEGORY_ID":
case "CATEGORY":
if (intval($val) > 0) {
$arSqlSearch[] = "T.CATEGORY_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)";
}
break;
case "CATEGORY_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DC.SID", $val, $match);
$d_join = "\n\t\t\tLEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')";
break;
case "CRITICALITY_ID":
case "CRITICALITY":
if (intval($val) > 0) {
$arSqlSearch[] = "T.CRITICALITY_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)";
}
break;
case "CRITICALITY_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DK.SID", $val, $match);
break;
case "STATUS_ID":
case "STATUS":
if (intval($val) > 0) {
$arSqlSearch[] = "T.STATUS_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)";
}
break;
case "STATUS_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DS.SID", $val, $match);
break;
case "MARK_ID":
case "MARK":
if (intval($val) > 0) {
$arSqlSearch[] = "T.MARK_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)";
}
break;
case "MARK_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DM.SID", $val, $match);
break;
case "SOURCE_ID":
case "SOURCE":
if (intval($val) > 0) {
$arSqlSearch[] = "T.SOURCE_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)";
}
break;
case "SOURCE_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DSR.SID", $val, $match);
break;
case "DIFFICULTY_ID":
case "DIFFICULTY":
if (intval($val) > 0) {
$arSqlSearch[] = "T.DIFFICULTY_ID = '" . intval($val) . "'";
} elseif ($val == 0) {
$arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)";
}
break;
case "DIFFICULTY_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("DD.SID", $val, $match);
break;
case "MODIFIED_BY":
$getUserName = "******";
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.MODIFIED_USER_ID, T.MODIFIED_MODULE_NAME, UM.LOGIN, UM.LAST_NAME, UM.NAME", $val, $match);
break;
case "MESSAGE":
global $strError;
if (strlen($val) <= 0) {
break;
}
if (CSupportSearch::CheckModule() && CSupportSearch::isIndexExists()) {
// new indexed search
$searchSqlParams = CSupportSearch::getSql($val);
$searchOn = $searchSqlParams['WHERE'];
$searchHaving = $searchSqlParams['HAVING'];
if ($searchOn) {
$searchJoin = 'INNER JOIN b_ticket_search TS ON TS.TICKET_ID = T.ID AND ' . $searchOn;
if (!empty($searchHaving)) {
// 2 or more search words
$arSqlHaving[] = $searchHaving;
$need_group = true;
}
}
} else {
if ($bSupportTeam == "Y" || $bAdmin == "Y" || $bDemo == "Y") {
$messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID)";
} else {
$messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID and M.IS_HIDDEN='N' and M.IS_LOG='N')";
}
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$f = new CFilterQuery("OR", "yes", $match, array(), "N", "Y", "N");
$query = $f->GetQueryString("T.TITLE,M.MESSAGE_SEARCH", $val);
$error = $f->error;
if (strlen(trim($error)) > 0) {
$strError .= $error . "<br>";
$query = "0";
} else {
$arSqlSearch[] = $query;
}
}
break;
case "LAST_MESSAGE_USER_ID":
case "LAST_MESSAGE_SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $matchValueSet ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T." . $key, $val, $match);
break;
case "LAST_MESSAGE_BY_SUPPORT_TEAM":
$arSqlSearch[] = "T.LAST_MESSAGE_BY_SUPPORT_TEAM= '" . ($val == 'Y' ? 'Y' : 'N') . "'";
break;
case "SUPPORT_COMMENTS":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $matchValueSet ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T.SUPPORT_COMMENTS", $val, $match);
break;
case "IS_SPAM":
$arSqlSearch[] = $val == "Y" ? "T.IS_SPAM ='Y'" : "(T.IS_SPAM = 'N' or T.IS_SPAM is null)";
break;
case "IS_OVERDUE":
$arSqlSearch[] = $val == "Y" ? "T.IS_OVERDUE ='Y'" : "(T.IS_OVERDUE = 'N' or T.IS_OVERDUE is null)";
break;
case "IS_SPAM_MAYBE":
$arSqlSearch[] = $val == "Y" ? "T.IS_SPAM='N'" : "(T.IS_SPAM='Y' or T.IS_SPAM is null)";
break;
case 'SUPPORTTEAM_GROUP_ID':
case 'CLIENT_GROUP_ID':
if ($key == 'SUPPORTTEAM_GROUP_ID') {
$table = 'UGS';
$bJoinSupportTeamTbl = true;
} else {
$table = 'UGC';
$bJoinClientTbl = true;
}
if (is_array($val)) {
$val = array_map('intval', $val);
$val = array_unique($val);
$val = array_filter($val);
if (count($val) > 0) {
$arSqlSearch[] = '(' . $table . '.GROUP_ID IS NOT NULL AND ' . $table . '.GROUP_ID IN (' . implode(',', $val) . '))';
}
} else {
$val = intval($val);
if ($val > 0) {
$arSqlSearch[] = '(' . $table . '.GROUP_ID IS NOT NULL AND ' . $table . '.GROUP_ID=\'' . $val . '\')';
}
}
break;
case 'COUPON':
$match = $matchValueSet && $arFilter[$key . "_EXACT_MATCH"] != "Y" ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T." . $key, $val, $match);
break;
}
}
}
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("SUPPORT", "T.ID");
$obUserFieldsSql->SetSelect($arParams["SELECT"]);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder(array($by => $order));
if ($by == "s_id") {
$strSqlOrder = "ORDER BY T.ID";
} elseif ($by == "s_last_message_date") {
$strSqlOrder = "ORDER BY T.LAST_MESSAGE_DATE";
} elseif ($by == "s_site_id" || $by == "s_lid") {
$strSqlOrder = "ORDER BY T.SITE_ID";
} elseif ($by == "s_lamp") {
$strSqlOrder = "ORDER BY LAMP";
} elseif ($by == "s_is_overdue") {
$strSqlOrder = "ORDER BY T.IS_OVERDUE";
} elseif ($by == "s_is_notified") {
$strSqlOrder = "ORDER BY T.IS_NOTIFIED";
} elseif ($by == "s_date_create") {
$strSqlOrder = "ORDER BY T.DATE_CREATE";
} elseif ($by == "s_timestamp" || $by == "s_timestamp_x") {
$strSqlOrder = "ORDER BY T.TIMESTAMP_X";
} elseif ($by == "s_date_close") {
$strSqlOrder = "ORDER BY T.DATE_CLOSE";
} elseif ($by == "s_owner") {
$strSqlOrder = "ORDER BY T.OWNER_USER_ID";
} elseif ($by == "s_modified_by") {
$strSqlOrder = "ORDER BY T.MODIFIED_USER_ID";
} elseif ($by == "s_title") {
$strSqlOrder = "ORDER BY T.TITLE ";
} elseif ($by == "s_responsible") {
$strSqlOrder = "ORDER BY T.RESPONSIBLE_USER_ID";
} elseif ($by == "s_messages") {
$strSqlOrder = "ORDER BY T.MESSAGES";
} elseif ($by == "s_category") {
$strSqlOrder = "ORDER BY T.CATEGORY_ID";
} elseif ($by == "s_criticality") {
$strSqlOrder = "ORDER BY T.CRITICALITY_ID";
} elseif ($by == "s_sla") {
$strSqlOrder = "ORDER BY T.SLA_ID";
} elseif ($by == "s_status") {
$strSqlOrder = "ORDER BY T.STATUS_ID";
} elseif ($by == "s_difficulty") {
$strSqlOrder = "ORDER BY T.DIFFICULTY_ID";
} elseif ($by == "s_problem_time") {
$strSqlOrder = "ORDER BY T.PROBLEM_TIME";
} elseif ($by == "s_mark") {
$strSqlOrder = "ORDER BY T.MARK_ID";
} elseif ($by == "s_online") {
$strSqlOrder = "ORDER BY USERS_ONLINE";
} elseif ($by == "s_support_comments") {
$strSqlOrder = "ORDER BY T.SUPPORT_COMMENTS";
} elseif ($by == "s_auto_close_days_left") {
$strSqlOrder = "ORDER BY AUTO_CLOSE_DAYS_LEFT";
} elseif ($by == 's_coupon') {
$strSqlOrder = 'ORDER BY T.COUPON';
} elseif ($by == 's_deadline') {
$strSqlOrder = 'ORDER BY T.SUPPORT_DEADLINE';
} elseif ($s = $obUserFieldsSql->GetOrder($by)) {
$strSqlOrder = "ORDER BY " . strtoupper($s);
} else {
$by = "s_default";
$strSqlOrder = "ORDER BY IS_SUPER_TICKET DESC, T.IS_OVERDUE DESC, T.IS_NOTIFIED DESC, T.LAST_MESSAGE_DATE";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$arSqlSearch[] = $obUserFieldsSql->GetFilter();
if ($getUserName == "Y") {
$u_select = "\n\t\t\t\t,\n\t\t\t\tUO.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_LOGIN,\n\t\t\t\tUO.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_EMAIL,\n\t\t\t\tconcat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,''))\t\tOWNER_NAME,\n\t\t\t\tUR.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_LOGIN,\n\t\t\t\tUR.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_EMAIL,\n\t\t\t\tconcat(ifnull(UR.NAME,''),' ',ifnull(UR.LAST_NAME,''))\t\tRESPONSIBLE_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_BY_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_NAME,\n\t\t\t\tUL.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_LOGIN,\n\t\t\t\tUL.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_EMAIL,\n\t\t\t\tconcat(ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,''))\t\tLAST_MESSAGE_NAME,\n\t\t\t\tUC.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_LOGIN,\n\t\t\t\tUC.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_EMAIL,\n\t\t\t\tconcat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,''))\t\tCREATED_NAME\n\t\t\t";
$u_join = "\n\t\t\tLEFT JOIN b_user UO ON (UO.ID = T.OWNER_USER_ID)\n\t\t\tLEFT JOIN b_user UR ON (UR.ID = T.RESPONSIBLE_USER_ID)\n\t\t\tLEFT JOIN b_user UM ON (UM.ID = T.MODIFIED_USER_ID)\n\t\t\tLEFT JOIN b_user UL ON (UL.ID = T.LAST_MESSAGE_USER_ID)\n\t\t\tLEFT JOIN b_user UC ON (UC.ID = T.CREATED_USER_ID)\n\t\t\t";
}
if ($getExtraNames == "Y") {
$d_select = "\n\t\t\t\t,\n\t\t\t\tDC.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_NAME,\n\t\t\t\tDC.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_DESC,\n\t\t\t\tDC.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_SID,\n\t\t\t\tDK.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_NAME,\n\t\t\t\tDK.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_DESC,\n\t\t\t\tDK.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_SID,\n\t\t\t\tDS.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_NAME,\n\t\t\t\tDS.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_DESC,\n\t\t\t\tDS.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_SID,\n\t\t\t\tDM.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_NAME,\n\t\t\t\tDM.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_DESC,\n\t\t\t\tDM.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_SID,\n\t\t\t\tDSR.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_NAME,\n\t\t\t\tDSR.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_DESC,\n\t\t\t\tDSR.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_SID,\n\t\t\t\tDD.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_NAME,\n\t\t\t\tDD.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_DESC,\n\t\t\t\tDD.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_SID,\n\t\t\t\tSLA.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSLA_NAME\n\t\t\t";
$d_join = "\n\t\t\tLEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')\n\t\t\tLEFT JOIN b_ticket_dictionary DK ON (DK.ID = T.CRITICALITY_ID and DK.C_TYPE = 'K')\n\t\t\tLEFT JOIN b_ticket_dictionary DS ON (DS.ID = T.STATUS_ID and DS.C_TYPE = 'S')\n\t\t\tLEFT JOIN b_ticket_dictionary DM ON (DM.ID = T.MARK_ID and DM.C_TYPE = 'M')\n\t\t\tLEFT JOIN b_ticket_dictionary DSR ON (DSR.ID = T.SOURCE_ID and DSR.C_TYPE = 'SR')\n\t\t\tLEFT JOIN b_ticket_dictionary DD ON (DD.ID = T.DIFFICULTY_ID and DD.C_TYPE = 'D')\n\t\t\tLEFT JOIN b_ticket_sla SLA ON (SLA.ID = T.SLA_ID)\n\t\t\t";
}
if (strlen($siteID) > 0) {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL", $siteID, true) . "\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL", $siteID, true) . "\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL", $siteID, true) . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL", $siteID, true) . "\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT", $siteID, true) . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT", $siteID, true) . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT", $siteID, true) . "\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL", $siteID, true) . "\tSUPPORT_DEADLINE,\n\t\t\t\tCASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN " . $DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL", $siteID, true) . " ELSE NULL END AUTO_CLOSE_DATE\n\t\t\t";
} else {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL") . "\t\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL") . "\t\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL") . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL") . "\t\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT") . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT") . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT") . "\t\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL") . "\tSUPPORT_DEADLINE,\n\t\t\t\tCASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN " . $DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL") . " ELSE NULL END AUTO_CLOSE_DATE\n\t\t\t";
}
$ugroupJoin = '';
if ($bJoinSupportTeamTbl) {
$ugroupJoin .= "\n\t\t\tLEFT JOIN b_ticket_user_ugroup UGS ON (UGS.USER_ID = T.RESPONSIBLE_USER_ID) ";
$need_group = true;
}
if ($bJoinClientTbl) {
$ugroupJoin .= "\n\t\t\tLEFT JOIN b_ticket_user_ugroup UGC ON (UGC.USER_ID = T.OWNER_USER_ID) ";
$need_group = true;
}
// add permissions check
if (!($bAdmin == 'Y' || $bDemo == 'Y')) {
// a list of users who own or are responsible for tickets, which we can show to our current user
$ticketUsers = array($uid);
// check if user has groups
$result = $DB->Query('SELECT GROUP_ID FROM b_ticket_user_ugroup WHERE USER_ID = ' . $uid . ' AND CAN_VIEW_GROUP_MESSAGES = \'Y\'');
if ($result) {
// collect members of these groups
$uGroups = array();
while ($row = $result->Fetch()) {
$uGroups[] = $row['GROUP_ID'];
}
if (!empty($uGroups)) {
$result = $DB->Query('SELECT USER_ID FROM b_ticket_user_ugroup WHERE GROUP_ID IN (' . join(',', $uGroups) . ')');
if ($result) {
while ($row = $result->Fetch()) {
$ticketUsers[] = $row['USER_ID'];
}
}
}
}
// build sql
$strSqlSearchUser = "";
if ($bSupportTeam == 'Y') {
$strSqlSearchUser = '******' . join(',', $ticketUsers) . ')';
} elseif ($bSupportClient == 'Y') {
$strSqlSearchUser = '******' . join(',', $ticketUsers) . ')';
}
$arSqlSearch[] = $strSqlSearchUser;
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL"));
$strSqlSelect = "\n\t\t\tSELECT\n\t\t\t\tT.*,\n\t\t\t\tT.SITE_ID,\n\t\t\t\tT.SITE_ID\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLID,\n\t\t\t\t{$dates_select},\n\t\t\t\tUNIX_TIMESTAMP(T.DATE_CLOSE)-UNIX_TIMESTAMP(T.DATE_CREATE)\t\t\t\t\t\t\tTICKET_TIME,\n\t\t\t\tCASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN\n\t\t\t\t\tTO_DAYS(\n\t\t\t\t\t\tADDDATE(\n\t\t\t\t\t\t\tT.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY\n\t\t\t\t\t\t)\n\t\t\t\t\t) - TO_DAYS(now())\n\t\t\t\tELSE -1 END AUTO_CLOSE_DAYS_LEFT,\n\t\t\t\t(SELECT COUNT(DISTINCT USER_ID) FROM b_ticket_online WHERE TICKET_ID = T.ID AND TIMESTAMP_X >= DATE_ADD(now(), INTERVAL - " . $onlineInterval . " SECOND)) USERS_ONLINE,\n\t\t\t\tif(T.COUPON IS NOT NULL, 1, 0)\t\t\t\t\t\t\t\t\t\t\t\t\t\tIS_SUPER_TICKET,\n\t\t\t\t{$lamp}\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLAMP\n\t\t\t\t{$d_select}\n\t\t\t\t{$u_select}\n\t\t\t\t" . $obUserFieldsSql->GetSelect();
$strSqlFrom = "\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\t{$u_join}\n\t\t\t{$d_join}\n\t\t\t{$messJoin}\n\t\t\t{$searchJoin}\n\t\t\t{$ugroupJoin}\n\t\t\t\t" . $obUserFieldsSql->GetJoin("T.ID");
$strSqlWhere = "\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t";
$strSqlGroup = $need_group ? ' GROUP BY T.ID ' : '';
$strSqlHaving = $arSqlHaving ? ' HAVING ' . join(' AND ', $arSqlHaving) . ' ' : '';
$strSql = $strSqlSelect . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving . $strSqlOrder;
if (is_array($arParams) && isset($arParams["NAV_PARAMS"]) && is_array($arParams["NAV_PARAMS"])) {
$nTopCount = isset($arParams['NAV_PARAMS']['nTopCount']) ? intval($arParams['NAV_PARAMS']['nTopCount']) : 0;
if ($nTopCount > 0) {
$strSql = $DB->TopSql($strSql, $nTopCount);
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
} else {
$cntSql = "SELECT COUNT(T.ID) as C " . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving;
if (!empty($strSqlGroup)) {
$cntSql = 'SELECT COUNT(1) AS C FROM (' . $cntSql . ') tt';
}
$res_cnt = $DB->Query($cntSql);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
$res->NavQuery($strSql, $res_cnt["C"], $arParams["NAV_PARAMS"]);
}
} else {
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
}
$isFiltered = IsFiltered($strSqlSearch);
return $res;
}
function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arUF = array())
{
global $DB;
$obUserFieldsSql = false;
if (is_array($arUF) && array_key_exists("ENTITY_ID", $arUF)) {
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity($arUF["ENTITY_ID"], $arFields["ID"]["FIELD"]);
$obUserFieldsSql->SetSelect($arSelectFields);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
}
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy) > 0) {
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct)) {
if (strlen($strSqlGroupBy) > 0) {
$strSqlGroupBy .= ", ";
}
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// WHERE -->
$arAlreadyJoinedOld = $arAlreadyJoined;
$strSqlWhere .= CSqlUtil::PrepareWhere($arFields, $arFilter, $arAlreadyJoined);
$arAlreadyJoinedDiff = array_diff($arAlreadyJoined, $arAlreadyJoinedOld);
foreach ($arAlreadyJoinedDiff as $from_tmp) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $from_tmp;
}
if ($obUserFieldsSql) {
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strSqlWhere .= (strlen($strSqlWhere) > 0 ? " AND" : "") . " (" . $r . ") ";
}
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
} else {
$order = "ASC";
}
if (array_key_exists($by, $arFields)) {
if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date") {
$arSqlOrder[] = " " . $by . "_X1 " . $order . " ";
if (!is_array($arSelectFields) || !in_array($by, $arSelectFields)) {
$arSelectFields[] = $by;
}
} else {
$arSqlOrder[] = " " . $arFields[$by]["FIELD"] . " " . $order . " ";
}
if (isset($arFields[$by]["FROM"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
} elseif ($obUserFieldsSql && ($s = $obUserFieldsSql->GetOrder($by))) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$tmp_count = count($arSqlOrder);
for ($i = 0; $i < $tmp_count; $i++) {
if (strlen($strSqlOrderBy) > 0) {
$strSqlOrderBy .= ", ";
}
if (strtoupper($DB->type) == "ORACLE") {
if (substr($arSqlOrder[$i], -3) == "ASC") {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS FIRST";
} else {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS LAST";
}
} else {
$strSqlOrderBy .= $arSqlOrder[$i];
}
}
// <-- ORDER BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy) == 0) {
$strSqlSelect = "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT ";
} else {
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
$arSelectFields = array($arSelectFields);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
$tmp_count = count($arFieldsKeys);
for ($i = 0; $i < $tmp_count; $i++) {
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"]) && $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y") {
continue;
}
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL") . " as " . $arFieldsKeys[$i];
} elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT") . " as " . $arFieldsKeys[$i];
} else {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i];
}
if (isset($arFields[$arFieldsKeys[$i]]["FROM"]) && strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0 && !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD"] . ") as " . $val;
} else {
if ($arFields[$val]["TYPE"] == "datetime") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["TYPE"] == "date") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if ($obUserFieldsSql) {
$strSqlSelect .= (strlen($strSqlSelect) <= 0 ? $arFields["ID"]["FIELD"] : "") . $obUserFieldsSql->GetSelect();
}
if (strlen($strSqlGroupBy) > 0) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
$strSqlSelect .= "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT";
} else {
$strSqlSelect = "%%_DISTINCT_%% " . $strSqlSelect;
}
}
// <-- SELECT
if ($obUserFieldsSql) {
$strSqlFrom .= " " . $obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
}
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy);
}
function GetList($arOrder = Array("ID"=>"ASC"), $arFilter = Array(), $bCount = false, $iNum = 0, $arAddParams = array())
{
global $DB;
$arSqlSearch = array();
$arSqlOrder = array();
$strSqlSearch = "";
$strSqlOrder = "";
$arFilter = (is_array($arFilter) ? $arFilter : array());
$arAddParams = (is_array($arAddParams) ? $arAddParams : array());
$obUserFieldsSql = new CUserTypeSQL;
$obUserFieldsSql->SetEntity("FORUM_MESSAGE", "FM.ID");
$obUserFieldsSql->SetSelect($arAddParams["SELECT"]);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
foreach ($arFilter as $key => $val)
{
$key_res = CForumNew::GetFilterOperation($key);
$key = strtoupper($key_res["FIELD"]);
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
switch ($key)
{
case "PARAM1":
case "AUTHOR_NAME":
case "POST_MESSAGE_CHECK":
case "APPROVED":
case "NEW_TOPIC":
if (strlen($val)<=0)
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FM.".$key." IS NULL OR LENGTH(FM.".$key.")<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" FM.".$key." IS NULL OR NOT ":"")."(FM.".$key." ".$strOperation." '".$DB->ForSql($val)."' )";
break;
case "PARAM2":
case "ID":
case "AUTHOR_ID":
case "FORUM_ID":
case "TOPIC_ID":
case "ATTACH_IMG":
if ( ($strOperation == "IN") && (!is_array($val)) && (strpos($val,",")>0) )
$val = explode(",", $val);
if (($strOperation!="IN") && (intVal($val) > 0))
$arSqlSearch[] = ($strNegative=="Y"?" FM.".$key." IS NULL OR NOT ":"")."(FM.".$key." ".$strOperation." ".intVal($val)." )";
elseif (($strOperation =="IN") && ((is_array($val) && sizeof($val)>0 && (array_sum($val) > 0)) || (strlen($val) > 0) ))
{
if (is_array($val))
{
$val_int = array();
foreach ($val as $v)
$val_int[] = intVal($v);
$val = implode(", ", $val_int);
}
else
{
$val = intval($val);
}
$arSqlSearch[] = ($strNegative=="Y"?" NOT ":"")."(FM.".$key." IN (".$DB->ForSql($val).") )";
}
else
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FM.".$key." IS NULL OR FM.".$key."<=0)";
break;
case "EDIT_DATE":
case "POST_DATE":
if (strlen($val)<=0)
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FM.".$key." IS NULL OR LENGTH(FM.".$key.")<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" FM.".$key." IS NULL OR NOT ":"")."(FM.".$key." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL")." )";
break;
case "PERMISSION":
if ((is_array($val)) && (count($val)>0))
{
$return = array();
foreach ($val as $value)
{
$str = array();
foreach ($value as $k => $v)
{
$k_res = CForumNew::GetFilterOperation($k);
$k = strToUpper($k_res["FIELD"]);
$strNegative = $k_res["NEGATIVE"];
$strOperation = $k_res["OPERATION"];
switch ($k)
{
case "TOPIC_ID":
case "FORUM_ID":
if (intVal($v)<=0)
$str[] = ($strNegative=="Y"?"NOT":"")."(FM.".$k." IS NULL OR FM.".$k."<=0)";
else
$str[] = ($strNegative=="Y"?" FM.".$k." IS NULL OR NOT ":"")."(FM.".$k." ".$strOperation." ".intVal($v)." )";
break;
case "APPROVED":
if (strlen($v)<=0)
$str[] = ($strNegative=="Y"?"NOT":"")."(FM.APPROVED IS NULL OR LENGTH(FM.APPROVED)<=0)";
else
$str[] = ($strNegative=="Y"?" FM.APPROVED IS NULL OR NOT ":"")."FM.APPROVED ".$strOperation." '".$DB->ForSql($v)."' ";
break;
}
}
$return[] = implode(" AND ", $str);
}
if (count($return)>0)
$arSqlSearch[] = "(".implode(") OR (", $return).")";
}
break;
}
}
$r = $obUserFieldsSql->GetFilter();
if (!empty($r))
$arSqlSearch[] = $r;
if (!empty($arSqlSearch))
$strSqlSearch = " AND (".implode(") AND (", $arSqlSearch).") ";
if ($bCount || (is_array($arAddParams) && is_set($arAddParams, "bDescPageNumbering") && (intVal($arAddParams["nTopCount"])<=0)))
{
$strFrom = "FROM b_forum_message FM\n".$obUserFieldsSql->GetJoin("FM.ID")."\nWHERE 1 = 1 ".$strSqlSearch;
if($obUserFieldsSql->GetDistinct())
$strFrom = "FROM b_forum_message FM\n\tINNER JOIN (SELECT DISTINCT FM.ID ".$strFrom.") FM2 ON (FM2.ID=FM.ID)";
// This code was changed because of http://bugs.mysql.com/bug.php?id=64002
if ($bCount === "cnt_not_approved")
{
$ar_res = false;
$strSql =
"SELECT COUNT(FM.ID) as CNT, MAX(FM.ID) AS ABS_LAST_MESSAGE_ID, MIN(FM.ID) AS ABS_FIRST_MESSAGE_ID, \n\t".
"MIN(CASE WHEN FM.NEW_TOPIC='Y' THEN FM.ID ELSE NULL END) AS FIRST_MESSAGE_ID, \n\t".
"SUM(CASE WHEN FM.APPROVED!='Y' THEN 1 ELSE 0 END) as CNT_NOT_APPROVED,\n\t".
"MAX(CASE WHEN FM.APPROVED='Y' THEN FM.ID ELSE 0 END) AS LAST_MESSAGE_ID \n".
$strFrom;
if (array_intersect_key($arFilter, array("FORUM_ID" => null)) == $arFilter && $arFilter["FORUM_ID"] > 0) // High-usage
{
$db_res = $DB->Query($strSql . "\nGROUP BY FM.FORUM_ID", false, "File: ".__FILE__."<br>Line: ".__LINE__);
$ar_res = $db_res->Fetch();
}
else if (array_intersect_key($arFilter, array("TOPIC_ID" => null)) == $arFilter && $arFilter["TOPIC_ID"] > 0) // High-usage
{
$db_res = $DB->Query($strSql . "\nGROUP BY FM.TOPIC_ID", false, "File: ".__FILE__."<br>Line: ".__LINE__);
$ar_res = $db_res->Fetch();
}
else
{
$strSql = "SELECT COUNT(FM.ID) as CNT ".$strFrom;
$db_res = $DB->Query($strSql , false, "File: ".__FILE__."<br>Line: ".__LINE__);
if ($db_res && $ar_res = $db_res->Fetch())
{
$strSql =
"SELECT MAX(FM.ID) AS ABS_LAST_MESSAGE_ID, MIN(FM.ID) AS ABS_FIRST_MESSAGE_ID, \n\t".
"MIN(CASE WHEN FM.NEW_TOPIC='Y' THEN FM.ID ELSE NULL END) AS FIRST_MESSAGE_ID, \n\t".
"SUM(CASE WHEN FM.APPROVED!='Y' THEN 1 ELSE 0 END) as CNT_NOT_APPROVED,\n\t".
"MAX(CASE WHEN FM.APPROVED='Y' THEN FM.ID ELSE 0 END) AS LAST_MESSAGE_ID \n".
$strFrom;
$db_res = $DB->Query($strSql , false, "File: ".__FILE__."<br>Line: ".__LINE__);
if ($db_res && $ar_res1 = $db_res->Fetch())
{
$ar_res = array_merge($ar_res1, $ar_res);
}
}
}
return $ar_res;
}
else if ($bCount === "cnt_and_last_mid")
{
$ar_res = array();
if (array_intersect_key($arFilter, array("AUTHOR_ID" => null, "APPROVED" => null)) == $arFilter && $arFilter["AUTHOR_ID"] > 0) // High-usage
{
$strSql = "SELECT COUNT(FM.ID) as CNT, MAX(FM.ID) as LAST_MESSAGE_ID \n ".$strFrom." \nGROUP BY FM.AUTHOR_ID"; // explain the same as without "GROUP BY"
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
if ($db_res)
$ar_res = $db_res->Fetch();
}
else
{
$strSql = "SELECT COUNT(FM.ID) as CNT \n ".$strFrom;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
if ($db_res && $ar_res = $db_res->Fetch())
{
$strSql = "SELECT MAX(FM.ID) as LAST_MESSAGE_ID \n ".$strFrom;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
if ($db_res && $ar_res1 = $db_res->Fetch())
{
$ar_res["LAST_MESSAGE_ID"] = $ar_res1["LAST_MESSAGE_ID"];
}
}
}
return $ar_res;
}
else
{
$strSql = "SELECT COUNT(FM.ID) as CNT \n ".$strFrom;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
$iCnt = 0;
if ($db_res && $ar_res = $db_res->Fetch())
$iCnt = intVal($ar_res["CNT"]);
if ($bCount)
return $iCnt;
}
// /This code was changed because of http://bugs.mysql.com/bug.php?id=64002
}
foreach ($arOrder as $by=>$order)
{
$by = strtoupper($by); $order = strtoupper($order);
if ($order!="ASC") $order = "DESC";
if ($by == "AUTHOR_NAME") $arSqlOrder[] = " FM.AUTHOR_NAME ".$order." ";
elseif ($by == "EDIT_DATE") $arSqlOrder[] = " FM.EDIT_DATE ".$order." ";
elseif ($by == "POST_DATE") $arSqlOrder[] = " FM.POST_DATE ".$order." ";
elseif ($by == "FORUM_ID") $arSqlOrder[] = " FM.FORUM_ID ".$order." ";
elseif ($by == "TOPIC_ID") $arSqlOrder[] = " FM.TOPIC_ID ".$order." ";
elseif ($by == "NEW_TOPIC") $arSqlOrder[] = " FM.NEW_TOPIC ".$order." ";
elseif ($by == "APPROVED") $arSqlOrder[] = " FM.APPROVED ".$order." ";
else
{
$r = $obUserFieldsSql->GetOrder($by);
if (!!$r)
{
$arSqlOrder[] = " ".$r." ".$order." ";
}
else
{
$arSqlOrder[] = " FM.ID ".$order." ";
$by = "ID";
}
}
}
$arSqlOrder = array_unique($arSqlOrder);
DelDuplicateSort($arSqlOrder);
if(!empty($arSqlOrder))
$strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
$strSqlUserFieldJoin = $obUserFieldsSql->GetJoin("FM.ID");
if ($obUserFieldsSql->GetDistinct())
{
$obUserFieldsSqlSelect = new CUserTypeSQL;
$obUserFieldsSqlSelect->SetEntity("FORUM_MESSAGE", "FM.ID");
$obUserFieldsSqlSelect->SetSelect($arAddParams["SELECT"]);
$obUserFieldsSqlSelect->SetOrder($arOrder);
$strSqlUserFieldJoin =
$obUserFieldsSqlSelect->GetJoin("FM.ID")."
INNER JOIN (
SELECT DISTINCT FM.ID
FROM b_forum_message FM\n".
$obUserFieldsSql->GetJoin("FM.ID")."\n".
"WHERE 1 = 1 ".$strSqlSearch.") FM2 ON (FM2.ID = FM.ID) ";
$strSqlSearch = "";
}
$strSql =
"SELECT FM.ID,
FM.AUTHOR_ID, FM.AUTHOR_NAME, FM.AUTHOR_EMAIL, FM.AUTHOR_IP,
FM.USE_SMILES, FM.POST_MESSAGE, FM.POST_MESSAGE_HTML, FM.POST_MESSAGE_FILTER,
FM.FORUM_ID, FM.TOPIC_ID, FM.NEW_TOPIC,
FM.APPROVED, FM.SOURCE_ID, FM.POST_MESSAGE_CHECK, FM.GUEST_ID, FM.AUTHOR_REAL_IP, FM.ATTACH_IMG, FM.XML_ID,
".$DB->DateToCharFunction("FM.POST_DATE", "FULL")." as POST_DATE,
FM.EDITOR_ID, FM.EDITOR_NAME, FM.EDITOR_EMAIL, FM.EDIT_REASON,
FU.SHOW_NAME, U.LOGIN, U.NAME, U.SECOND_NAME, U.LAST_NAME, U.PERSONAL_PHOTO,
".$DB->DateToCharFunction("FM.EDIT_DATE", "FULL")." as EDIT_DATE, FM.PARAM1, FM.PARAM2, FM.HTML, FM.MAIL_HEADER".
$obUserFieldsSql->GetSelect().
(!empty($arAddParams["sNameTemplate"]) ?
",\n\t".CForumUser::GetFormattedNameFieldsForSelect(array_merge(
$arAddParams, array(
"sUserTablePrefix" => "U.",
"sForumUserTablePrefix" => "FU.",
"sFieldName" => "AUTHOR_NAME_FRMT")), false) : "")."
FROM b_forum_message FM
LEFT JOIN b_forum_user FU ON (FM.AUTHOR_ID = FU.USER_ID)
LEFT JOIN b_user U ON (FM.AUTHOR_ID = U.ID)".
$strSqlUserFieldJoin."
WHERE 1 = 1 ".$strSqlSearch."
".$strSqlOrder;
$iNum = intVal($iNum);
if (($iNum>0) || (is_array($arAddParams) && (intVal($arAddParams["nTopCount"])>0)))
{
$iNum = ($iNum > 0) ? $iNum : intVal($arAddParams["nTopCount"]);
$strSql .= " LIMIT 0,".$iNum;
}
if (!$iNum && is_array($arAddParams) && is_set($arAddParams, "bDescPageNumbering") && (intVal($arAddParams["nTopCount"])<=0))
{
$db_res = new CDBResult();
$db_res->SetUserFields($GLOBALS["USER_FIELD_MANAGER"]->GetUserFields("FORUM_MESSAGE"));
$db_res->NavQuery($strSql, $iCnt, $arAddParams);
}
else
{
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
$db_res->SetUserFields($GLOBALS["USER_FIELD_MANAGER"]->GetUserFields("FORUM_MESSAGE"));
}
return new _CMessageDBResult($db_res, $arAddParams);
}
public static function GetList($arOrder = array("SORT" => "ASC"), $arFilter = array(), $bIncCnt = false, $arSelect = array(), $arNavStartParams = false)
{
global $DB, $USER, $USER_FIELD_MANAGER;
if (!is_array($arOrder)) {
$arOrder = array();
}
if (isset($arFilter["IBLOCK_ID"]) && $arFilter["IBLOCK_ID"] > 0) {
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("IBLOCK_" . $arFilter["IBLOCK_ID"] . "_SECTION", "BS.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
} else {
foreach ($arFilter as $key => $val) {
$res = CIBlock::MkOperationFilter($key);
if (preg_match("/^UF_/", $res["FIELD"])) {
trigger_error("arFilter parameter of the CIBlockSection::GetList contains user fields, but has no IBLOCK_ID field.", E_USER_WARNING);
break;
}
}
}
$arJoinProps = array();
$bJoinFlatProp = false;
$arSqlSearch = CIBlockSection::GetFilter($arFilter);
$bCheckPermissions = !array_key_exists("CHECK_PERMISSIONS", $arFilter) || $arFilter["CHECK_PERMISSIONS"] !== "N";
$bIsAdmin = is_object($USER) && $USER->IsAdmin();
if ($bCheckPermissions && !$bIsAdmin) {
$arSqlSearch[] = CIBlockSection::_check_rights_sql($arFilter["MIN_PERMISSION"]);
}
if (array_key_exists("PROPERTY", $arFilter)) {
$val = $arFilter["PROPERTY"];
foreach ($val as $propID => $propVAL) {
$res = CIBlock::MkOperationFilter($propID);
$propID = $res["FIELD"];
$cOperationType = $res["OPERATION"];
if ($db_prop = CIBlockProperty::GetPropertyArray($propID, CIBlock::_MergeIBArrays($arFilter["IBLOCK_ID"], $arFilter["IBLOCK_CODE"]))) {
$bSave = false;
if (array_key_exists($db_prop["ID"], $arJoinProps)) {
$iPropCnt = $arJoinProps[$db_prop["ID"]];
} elseif ($db_prop["VERSION"] != 2 || $db_prop["MULTIPLE"] == "Y") {
$bSave = true;
$iPropCnt = count($arJoinProps);
}
if (!is_array($propVAL)) {
$propVAL = array($propVAL);
}
if ($db_prop["PROPERTY_TYPE"] == "N" || $db_prop["PROPERTY_TYPE"] == "G" || $db_prop["PROPERTY_TYPE"] == "E") {
if ($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N") {
$r = CIBlock::FilterCreate("FPS.PROPERTY_" . $db_prop["ORIG_ID"], $propVAL, "number", $cOperationType);
$bJoinFlatProp = $db_prop["IBLOCK_ID"];
} else {
$r = CIBlock::FilterCreate("FPV" . $iPropCnt . ".VALUE_NUM", $propVAL, "number", $cOperationType);
}
} else {
if ($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N") {
$r = CIBlock::FilterCreate("FPS.PROPERTY_" . $db_prop["ORIG_ID"], $propVAL, "string", $cOperationType);
$bJoinFlatProp = $db_prop["IBLOCK_ID"];
} else {
$r = CIBlock::FilterCreate("FPV" . $iPropCnt . ".VALUE", $propVAL, "string", $cOperationType);
}
}
if (strlen($r) > 0) {
if ($bSave) {
$db_prop["iPropCnt"] = $iPropCnt;
$arJoinProps[$db_prop["ID"]] = $db_prop;
}
$arSqlSearch[] = $r;
}
}
}
}
$strSqlSearch = "";
foreach ($arSqlSearch as $r) {
if (strlen($r) > 0) {
$strSqlSearch .= "\n\t\t\t\tAND (" . $r . ") ";
}
}
if (isset($obUserFieldsSql)) {
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strSqlSearch .= "\n\t\t\t\tAND (" . $r . ") ";
}
}
$strProp1 = "";
foreach ($arJoinProps as $propID => $db_prop) {
if ($db_prop["VERSION"] == 2) {
$strTable = "b_iblock_element_prop_m" . $db_prop["IBLOCK_ID"];
} else {
$strTable = "b_iblock_element_property";
}
$i = $db_prop["iPropCnt"];
$strProp1 .= "\n\t\t\t\tLEFT JOIN b_iblock_property FP" . $i . " ON FP" . $i . ".IBLOCK_ID=B.ID AND\n\t\t\t\t" . (IntVal($propID) > 0 ? " FP" . $i . ".ID=" . IntVal($propID) . " " : " FP" . $i . ".CODE='" . $DB->ForSQL($propID, 200) . "' ") . "\n\t\t\t\tLEFT JOIN " . $strTable . " FPV" . $i . " ON FP" . $i . ".ID=FPV" . $i . ".IBLOCK_PROPERTY_ID AND FPV" . $i . ".IBLOCK_ELEMENT_ID=BE.ID ";
}
if ($bJoinFlatProp) {
$strProp1 .= "\n\t\t\t\tLEFT JOIN b_iblock_element_prop_s" . $bJoinFlatProp . " FPS ON FPS.IBLOCK_ELEMENT_ID = BE.ID\n\t\t\t";
}
$arFields = array("ID" => "BS.ID", "CODE" => "BS.CODE", "XML_ID" => "BS.XML_ID", "EXTERNAL_ID" => "BS.XML_ID", "IBLOCK_ID" => "BS.IBLOCK_ID", "IBLOCK_SECTION_ID" => "BS.IBLOCK_SECTION_ID", "TIMESTAMP_X" => $DB->DateToCharFunction("BS.TIMESTAMP_X"), "TIMESTAMP_X_UNIX" => 'UNIX_TIMESTAMP(BS.TIMESTAMP_X)', "SORT" => "BS.SORT", "NAME" => "BS.NAME", "ACTIVE" => "BS.ACTIVE", "GLOBAL_ACTIVE" => "BS.GLOBAL_ACTIVE", "PICTURE" => "BS.PICTURE", "DESCRIPTION" => "BS.DESCRIPTION", "DESCRIPTION_TYPE" => "BS.DESCRIPTION_TYPE", "LEFT_MARGIN" => "BS.LEFT_MARGIN", "RIGHT_MARGIN" => "BS.RIGHT_MARGIN", "DEPTH_LEVEL" => "BS.DEPTH_LEVEL", "SEARCHABLE_CONTENT" => "BS.SEARCHABLE_CONTENT", "MODIFIED_BY" => "BS.MODIFIED_BY", "DATE_CREATE" => $DB->DateToCharFunction("BS.DATE_CREATE"), "DATE_CREATE_UNIX" => 'UNIX_TIMESTAMP(BS.DATE_CREATE)', "CREATED_BY" => "BS.CREATED_BY", "DETAIL_PICTURE" => "BS.DETAIL_PICTURE", "TMP_ID" => "BS.TMP_ID", "LIST_PAGE_URL" => "B.LIST_PAGE_URL", "SECTION_PAGE_URL" => "B.SECTION_PAGE_URL", "IBLOCK_TYPE_ID" => "B.IBLOCK_TYPE_ID", "IBLOCK_CODE" => "B.CODE", "IBLOCK_EXTERNAL_ID" => "B.XML_ID", "SOCNET_GROUP_ID" => "BS.SOCNET_GROUP_ID");
$arSqlSelect = array();
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . " AS " . $field;
}
}
if (array_key_exists("DESCRIPTION", $arSqlSelect)) {
$arSqlSelect["DESCRIPTION_TYPE"] = $arFields["DESCRIPTION_TYPE"] . " AS DESCRIPTION_TYPE";
}
if (array_key_exists("LIST_PAGE_URL", $arSqlSelect) || array_key_exists("SECTION_PAGE_URL", $arSqlSelect)) {
$arSqlSelect["ID"] = $arFields["ID"] . " AS ID";
$arSqlSelect["CODE"] = $arFields["CODE"] . " AS CODE";
$arSqlSelect["EXTERNAL_ID"] = $arFields["EXTERNAL_ID"] . " AS EXTERNAL_ID";
$arSqlSelect["IBLOCK_TYPE_ID"] = $arFields["IBLOCK_TYPE_ID"] . " AS IBLOCK_TYPE_ID";
$arSqlSelect["IBLOCK_ID"] = $arFields["IBLOCK_ID"] . " AS IBLOCK_ID";
$arSqlSelect["IBLOCK_CODE"] = $arFields["IBLOCK_CODE"] . " AS IBLOCK_CODE";
$arSqlSelect["IBLOCK_EXTERNAL_ID"] = $arFields["IBLOCK_EXTERNAL_ID"] . " AS IBLOCK_EXTERNAL_ID";
$arSqlSelect["GLOBAL_ACTIVE"] = $arFields["GLOBAL_ACTIVE"] . " AS GLOBAL_ACTIVE";
//$arr["LANG_DIR"],
}
if (count($arSqlSelect)) {
$sSelect = implode(",\n", $arSqlSelect);
} else {
$sSelect = "\n\t\t\t\tBS.*,\n\t\t\t\tB.LIST_PAGE_URL,\n\t\t\t\tB.SECTION_PAGE_URL,\n\t\t\t\tB.IBLOCK_TYPE_ID,\n\t\t\t\tB.CODE as IBLOCK_CODE,\n\t\t\t\tB.XML_ID as IBLOCK_EXTERNAL_ID,\n\t\t\t\tBS.XML_ID as EXTERNAL_ID,\n\t\t\t\t" . $DB->DateToCharFunction("BS.TIMESTAMP_X") . " as TIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("BS.DATE_CREATE") . " as DATE_CREATE\n\t\t\t";
}
if (!$bIncCnt) {
$strSelect = $sSelect . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetSelect() : "");
$strSql = "\n\t\t\t\tFROM b_iblock_section BS\n\t\t\t\t\tINNER JOIN b_iblock B ON BS.IBLOCK_ID = B.ID\n\t\t\t\t\t" . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetJoin("BS.ID") : "") . "\n\t\t\t\t" . (strlen($strProp1) > 0 ? "\tINNER JOIN b_iblock_section BSTEMP ON BSTEMP.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t\tLEFT JOIN b_iblock_section_element BSE ON BSE.IBLOCK_SECTION_ID=BSTEMP.ID\n\t\t\t\t\t\tLEFT JOIN b_iblock_element BE ON (BSE.IBLOCK_ELEMENT_ID=BE.ID\n\t\t\t\t\t\t\tAND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL )\n\t\t\t\t\t\t\tAND BE.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t" . ($arFilter["CNT_ALL"] == "Y" ? " OR BE.WF_NEW='Y' " : "") . ")\n\t\t\t\t\t" . ($arFilter["CNT_ACTIVE"] == "Y" ? " AND BE.ACTIVE='Y'\n\t\t\t\t\t\tAND (BE.ACTIVE_TO >= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_TO IS NULL)\n\t\t\t\t\t\tAND (BE.ACTIVE_FROM <= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_FROM IS NULL)" : "") . ")\n\t\t\t\t\t\t" . $strProp1 . " " : "") . "\n\t\t\t\tWHERE 1=1\n\t\t\t\t" . (strlen($strProp1) > 0 ? "\tAND BSTEMP.LEFT_MARGIN >= BS.LEFT_MARGIN\n\t\t\t\t\t\tAND BSTEMP.RIGHT_MARGIN <= BS.RIGHT_MARGIN " : "") . "\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t";
$strGroupBy = "";
} else {
$strSelect = $sSelect . ",COUNT(DISTINCT BE.ID) as ELEMENT_CNT" . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetSelect() : "");
$strSql = "\n\t\t\t\tFROM b_iblock_section BS\n\t\t\t\t\tINNER JOIN b_iblock B ON BS.IBLOCK_ID = B.ID\n\t\t\t\t\t" . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetJoin("BS.ID") : "") . "\n\t\t\t\t" . ($arFilter["ELEMENT_SUBSECTIONS"] == "N" ? "\tLEFT JOIN b_iblock_section_element BSE ON BSE.IBLOCK_SECTION_ID=BS.ID " : "\tINNER JOIN b_iblock_section BSTEMP ON BSTEMP.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t\tLEFT JOIN b_iblock_section_element BSE ON BSE.IBLOCK_SECTION_ID=BSTEMP.ID ") . "\n\t\t\t\t\tLEFT JOIN b_iblock_element BE ON (BSE.IBLOCK_ELEMENT_ID=BE.ID\n\t\t\t\t\t\tAND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL )\n\t\t\t\t\t\tAND BE.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t" . ($arFilter["CNT_ALL"] == "Y" ? " OR BE.WF_NEW='Y' " : "") . ")\n\t\t\t\t" . ($arFilter["CNT_ACTIVE"] == "Y" ? " AND BE.ACTIVE='Y'\n\t\t\t\t\tAND (BE.ACTIVE_TO >= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_TO IS NULL)\n\t\t\t\t\tAND (BE.ACTIVE_FROM <= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_FROM IS NULL)" : "") . ")\n\t\t\t\t\t" . $strProp1 . "\n\t\t\t\tWHERE 1=1\n\t\t\t\t" . ($arFilter["ELEMENT_SUBSECTIONS"] == "N" ? "\t" : "\tAND BSTEMP.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t\tAND BSTEMP.LEFT_MARGIN >= BS.LEFT_MARGIN\n\t\t\t\t\t\tAND BSTEMP.RIGHT_MARGIN <= BS.RIGHT_MARGIN ") . "\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t";
$strGroupBy = "GROUP BY BS.ID, B.ID";
}
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
if (isset($arSqlOrder[$by])) {
continue;
}
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[$by] = " BS.ID " . $order . " ";
} elseif ($by == "section") {
$arSqlOrder[$by] = " BS.IBLOCK_SECTION_ID " . $order . " ";
} elseif ($by == "name") {
$arSqlOrder[$by] = " BS.NAME " . $order . " ";
} elseif ($by == "code") {
$arSqlOrder[$by] = " BS.CODE " . $order . " ";
} elseif ($by == "active") {
$arSqlOrder[$by] = " BS.ACTIVE " . $order . " ";
} elseif ($by == "left_margin") {
$arSqlOrder[$by] = " BS.LEFT_MARGIN " . $order . " ";
} elseif ($by == "depth_level") {
$arSqlOrder[$by] = " BS.DEPTH_LEVEL " . $order . " ";
} elseif ($by == "sort") {
$arSqlOrder[$by] = " BS.SORT " . $order . " ";
} elseif ($by == "created") {
$arSqlOrder[$by] = " BS.DATE_CREATE " . $order . " ";
} elseif ($by == "created_by") {
$arSqlOrder[$by] = " BS.CREATED_BY " . $order . " ";
} elseif ($by == "modified_by") {
$arSqlOrder[$by] = " BS.MODIFIED_BY " . $order . " ";
} elseif ($bIncCnt && $by == "element_cnt") {
$arSqlOrder[$by] = " ELEMENT_CNT " . $order . " ";
} elseif (isset($obUserFieldsSql) && ($s = $obUserFieldsSql->GetOrder($by))) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
} else {
$by = "timestamp_x";
$arSqlOrder[$by] = " BS.TIMESTAMP_X " . $order . " ";
}
}
if (count($arSqlOrder) > 0) {
$strSqlOrder = "\n\t\t\t\tORDER BY " . implode(", ", $arSqlOrder);
} else {
$strSqlOrder = "";
}
if (is_array($arNavStartParams)) {
$nTopCount = intval($arNavStartParams["nTopCount"]);
if ($nTopCount > 0) {
$res = $DB->Query($DB->TopSql("SELECT DISTINCT " . $strSelect . $strSql . $strGroupBy . $strSqlOrder, $nTopCount));
} else {
$res_cnt = $DB->Query("SELECT COUNT(DISTINCT BS.ID) as C " . $strSql);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$res->NavQuery("SELECT DISTINCT " . $strSelect . $strSql . $strGroupBy . $strSqlOrder, $res_cnt["C"], $arNavStartParams);
}
} else {
$res = $DB->Query("SELECT DISTINCT " . $strSelect . $strSql . $strGroupBy . $strSqlOrder, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__);
}
$res = new CIBlockResult($res);
if (isset($arFilter["IBLOCK_ID"]) && $arFilter["IBLOCK_ID"] > 0) {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("IBLOCK_" . $arFilter["IBLOCK_ID"] . "_SECTION"));
$res->SetIBlockTag($arFilter["IBLOCK_ID"]);
}
return $res;
}
public static function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arUF = array())
{
global $DB;
$obUserFieldsSql = false;
if (is_array($arUF) && array_key_exists("ENTITY_ID", $arUF))
{
$obUserFieldsSql = new CUserTypeSQL;
$obUserFieldsSql->SetEntity($arUF["ENTITY_ID"], $arFields["ID"]["FIELD"]);
$obUserFieldsSql->SetSelect($arSelectFields);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
}
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy)>0)
{
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct))
{
if (strlen($strSqlGroupBy) > 0)
$strSqlGroupBy .= ", ";
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// WHERE -->
$arSqlSearch = Array();
if (!is_array($arFilter))
$filter_keys = Array();
else
$filter_keys = array_keys($arFilter);
$tmp_count = count($filter_keys);
for ($i = 0; $i < $tmp_count; $i++)
{
$vals = $arFilter[$filter_keys[$i]];
if (!is_array($vals))
$vals = array($vals);
$key = $filter_keys[$i];
$key_res = CSocNetGroup::GetFilterOperation($key);
$key = $key_res["FIELD"];
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$strOrNull = $key_res["OR_NULL"];
if (array_key_exists($key, $arFields))
{
$arSqlSearch_tmp = array();
foreach ($vals as $val)
{
if (isset($arFields[$key]["WHERE"]))
{
$arSqlSearch_tmp1 = call_user_func_array(
$arFields[$key]["WHERE"],
array($val, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], &$arFields, &$arFilter)
);
if ($arSqlSearch_tmp1 !== false)
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
else
{
if ($arFields[$key]["TYPE"] == "int")
{
if ((IntVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".IntVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "double")
{
$val = str_replace(",", ".", $val);
if ((DoubleVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".DoubleVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
{
if ($strOperation == "QUERY")
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, (strpos($val, "%") === false ? "Y" : "N"));
else
{
if ((strlen($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$DB->Length($arFields[$key]["FIELD"])." <= 0) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
}
}
elseif ($arFields[$key]["TYPE"] == "string_or_null" || $arFields[$key]["TYPE"] == "char_or_null")
{
if ($strOperation == "QUERY")
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, (strpos($val, "%") === false ? "Y" : "N"));
else
{
if ((strlen($val) == 0) && (strpos($strOperation, "=") !== False))
{
// future functionality
}
else
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' OR ".$arFields[$key]["FIELD"]." IS NULL)";
}
}
elseif ($arFields[$key]["TYPE"] == "datetime")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
elseif (strtoupper($val) === "NOW")
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->GetNowFunction().")";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
}
elseif ($arFields[$key]["TYPE"] == "date")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
}
}
}
if (isset($arFields[$key]["FROM"])
&& strlen($arFields[$key]["FROM"]) > 0
&& !in_array($arFields[$key]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$key]["FROM"];
$arAlreadyJoined[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
$tmp_count_1 = count($arSqlSearch_tmp);
for ($j = 0; $j < $tmp_count_1; $j++)
{
if ($j > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arSqlSearch_tmp[$j].")";
}
if ($strOrNull == "Y")
{
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." IS ".($strNegative=="Y" ? "NOT " : "")."NULL)";
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
if ($arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." 0)";
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." '')";
}
if ($strSqlSearch_tmp != "")
$arSqlSearch[] = "(".$strSqlSearch_tmp.")";
}
}
$tmp_count = count($arSqlSearch);
for ($i = 0; $i < $tmp_count; $i++)
{
if (strlen($strSqlWhere) > 0)
$strSqlWhere .= " AND ";
$strSqlWhere .= "(".$arSqlSearch[$i].")";
}
if ($obUserFieldsSql)
{
$r = $obUserFieldsSql->GetFilter();
if(strlen($r) > 0)
$strSqlWhere .= (strlen($strSqlWhere) > 0 ? " AND" : "")." (".$r.") ";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = Array();
foreach ($arOrder as $by => $order)
{
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC")
$order = "DESC";
else
$order = "ASC";
if (array_key_exists($by, $arFields))
{
if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date")
{
$arSqlOrder[] = " ".$by."_X1 ".$order." ";
if (!is_array($arSelectFields) || !in_array($by, $arSelectFields))
$arSelectFields[] = $by;
}
else
$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$order." ";
if (isset($arFields[$by]["FROM"])
&& strlen($arFields[$by]["FROM"]) > 0
&& !in_array($arFields[$by]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
}
elseif($obUserFieldsSql && $s = $obUserFieldsSql->GetOrder($by))
$arSqlOrder[$by] = " ".$s." ".$order." ";
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$tmp_count = count($arSqlOrder);
for ($i=0; $i < $tmp_count; $i++)
{
if (strlen($strSqlOrderBy) > 0)
$strSqlOrderBy .= ", ";
if(strtoupper($DB->type)=="ORACLE")
{
if(substr($arSqlOrder[$i], -3)=="ASC")
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS FIRST";
else
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS LAST";
}
else
$strSqlOrderBy .= $arSqlOrder[$i];
}
// <-- ORDER BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy)==0)
$strSqlSelect = "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT ";
else
{
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields)>0 && array_key_exists($arSelectFields, $arFields))
$arSelectFields = array($arSelectFields);
if (!isset($arSelectFields)
|| !is_array($arSelectFields)
|| count($arSelectFields) <= 0
|| in_array("*", $arSelectFields))
{
$tmp_count = count($arFieldsKeys);
for ($i = 0; $i < $tmp_count; $i++)
{
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"])
&& $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y")
continue;
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime")
{
if (array_key_exists($arFieldsKeys[$i], $arOrder))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL")." as ".$arFieldsKeys[$i];
}
elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date")
{
if (array_key_exists($arFieldsKeys[$i], $arOrder))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT")." as ".$arFieldsKeys[$i];
}
else
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i];
if (isset($arFields[$arFieldsKeys[$i]]["FROM"])
&& strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0
&& !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
}
else
{
foreach ($arSelectFields as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields))
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if (in_array($key, $arGroupByFunct))
$strSqlSelect .= $key."(".$arFields[$val]["FIELD"].") as ".$val;
else
{
if ($arFields[$val]["TYPE"] == "datetime")
{
if (array_key_exists($val, $arOrder))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
}
elseif ($arFields[$val]["TYPE"] == "date")
{
if (array_key_exists($val, $arOrder))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT")." as ".$val;
}
else
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val;
}
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if ($obUserFieldsSql)
$strSqlSelect .= (strlen($strSqlSelect) <= 0 ? $arFields["ID"]["FIELD"] : "").$obUserFieldsSql->GetSelect();
if (strlen($strSqlGroupBy) > 0)
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
$strSqlSelect .= "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT";
}
else
$strSqlSelect = "%%_DISTINCT_%% ".$strSqlSelect;
}
// <-- SELECT
if ($obUserFieldsSql)
$strSqlFrom .= " ".$obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
return array(
"SELECT" => $strSqlSelect,
"FROM" => $strSqlFrom,
"WHERE" => $strSqlWhere,
"GROUPBY" => $strSqlGroupBy,
"ORDERBY" => $strSqlOrderBy
);
}
/**
* @param array $arOrder
* @param array $arFilter
* @param array $arSelect
* @return CDBResult
* Obsolete. Always select all record from database. Please use GetListEx instead.
*/
public static function GetList($arOrder = array('DATE_CREATE' => 'DESC'), $arFilter = array(), $arSelect = array(), $nPageTop = false)
{
global $DB, $USER_FIELD_MANAGER;
// fields
$arFields = array('ID' => 'L.ID', 'COMMENTS' => 'L.COMMENTS', 'ADDITIONAL_INFO' => 'L.ADDITIONAL_INFO', 'LOCATION_ID' => 'L.LOCATION_ID', 'TITLE' => 'L.TITLE', 'LEAD_ID' => 'L.LEAD_ID', 'COMPANY_ID' => 'L.COMPANY_ID', 'COMPANY_TITLE' => 'C.TITLE', 'CONTACT_ID' => 'L.CONTACT_ID', 'CONTACT_FULL_NAME' => 'CT.FULL_NAME', 'STAGE_ID' => 'L.STAGE_ID', 'CLOSED' => 'L.CLOSED', 'TYPE_ID' => 'L.TYPE_ID', 'PRODUCT_ID' => 'L.PRODUCT_ID', 'PROBABILITY' => 'L.PROBABILITY', 'OPPORTUNITY' => 'L.OPPORTUNITY', 'TAX_VALUE' => 'L.TAX_VALUE', 'CURRENCY_ID' => 'L.CURRENCY_ID', 'OPPORTUNITY_ACCOUNT' => 'L.OPPORTUNITY_ACCOUNT', 'TAX_VALUE_ACCOUNT' => 'L.TAX_VALUE_ACCOUNT', 'ACCOUNT_CURRENCY_ID' => 'L.ACCOUNT_CURRENCY_ID', 'BEGINDATE' => $DB->DateToCharFunction('L.BEGINDATE'), 'CLOSEDATE' => $DB->DateToCharFunction('L.CLOSEDATE'), 'EVENT_ID' => 'L.EVENT_ID', 'EVENT_DATE' => $DB->DateToCharFunction('L.EVENT_DATE'), 'EVENT_DESCRIPTION' => 'L.EVENT_DESCRIPTION', 'ASSIGNED_BY' => 'L.ASSIGNED_BY_ID', 'ASSIGNED_BY_ID' => 'L.ASSIGNED_BY_ID', 'CREATED_BY' => 'L.CREATED_BY_ID', 'CREATED_BY_ID' => 'L.CREATED_BY_ID', 'MODIFY_BY' => 'L.MODIFY_BY_ID', 'MODIFY_BY_ID' => 'L.MODIFY_BY_ID', 'DATE_CREATE' => $DB->DateToCharFunction('L.DATE_CREATE'), 'DATE_MODIFY' => $DB->DateToCharFunction('L.DATE_MODIFY'), 'OPENED' => 'L.OPENED', 'EXCH_RATE' => 'L.EXCH_RATE', 'ORIGINATOR_ID' => 'L.ORIGINATOR_ID', 'ORIGIN_ID' => 'L.ORIGIN_ID', 'ASSIGNED_BY_LOGIN' => 'U.LOGIN', 'ASSIGNED_BY_NAME' => 'U.NAME', 'ASSIGNED_BY_LAST_NAME' => 'U.LAST_NAME', 'ASSIGNED_BY_SECOND_NAME' => 'U.SECOND_NAME', 'CREATED_BY_LOGIN' => 'U2.LOGIN', 'CREATED_BY_NAME' => 'U2.NAME', 'CREATED_BY_LAST_NAME' => 'U2.LAST_NAME', 'CREATED_BY_SECOND_NAME' => 'U2.SECOND_NAME', 'MODIFY_BY_LOGIN' => 'U3.LOGIN', 'MODIFY_BY_NAME' => 'U3.NAME', 'MODIFY_BY_LAST_NAME' => 'U3.LAST_NAME', 'MODIFY_BY_SECOND_NAME' => 'U3.SECOND_NAME');
$arSqlSelect = array();
$sSqlJoin = '';
if (count($arSelect) == 0) {
$arSelect = array_merge(array_keys($arFields), array('UF_*'));
}
$obQueryWhere = new CSQLWhere();
$arFilterField = $arSelect;
foreach ($arFilter as $sKey => $sValue) {
$arField = $obQueryWhere->MakeOperation($sKey);
$arFilterField[] = $arField['FIELD'];
}
if (in_array('ASSIGNED_BY_LOGIN', $arFilterField) || in_array('ASSIGNED_BY', $arFilterField)) {
$arSelect[] = 'ASSIGNED_BY_LOGIN';
$arSelect[] = 'ASSIGNED_BY_NAME';
$arSelect[] = 'ASSIGNED_BY_LAST_NAME';
$arSelect[] = 'ASSIGNED_BY_SECOND_NAME';
$sSqlJoin .= ' LEFT JOIN b_user U ON L.ASSIGNED_BY_ID = U.ID ';
}
if (in_array('CREATED_BY_LOGIN', $arFilterField)) {
$arSelect[] = 'CREATED_BY';
$arSelect[] = 'CREATED_BY_LOGIN';
$arSelect[] = 'CREATED_BY_NAME';
$arSelect[] = 'CREATED_BY_LAST_NAME';
$arSelect[] = 'CREATED_BY_SECOND_NAME';
$sSqlJoin .= ' LEFT JOIN b_user U2 ON L.CREATED_BY_ID = U2.ID ';
}
if (in_array('MODIFY_BY_LOGIN', $arFilterField)) {
$arSelect[] = 'MODIFY_BY';
$arSelect[] = 'MODIFY_BY_LOGIN';
$arSelect[] = 'MODIFY_BY_NAME';
$arSelect[] = 'MODIFY_BY_LAST_NAME';
$arSelect[] = 'MODIFY_BY_SECOND_NAME';
$sSqlJoin .= ' LEFT JOIN b_user U3 ON L.MODIFY_BY_ID = U3.ID ';
}
if (in_array('COMPANY_ID', $arFilterField) || in_array('COMPANY_TITLE', $arFilterField)) {
$arSelect[] = 'COMPANY_ID';
$arSelect[] = 'COMPANY_TITLE';
$sSqlJoin .= ' LEFT JOIN b_crm_company C ON L.COMPANY_ID = C.ID ';
}
if (in_array('CONTACT_ID', $arFilterField) || in_array('CONTACT_FULL_NAME', $arFilterField)) {
$arSelect[] = 'CONTACT_ID';
$arSelect[] = 'CONTACT_FULL_NAME';
$sSqlJoin .= ' LEFT JOIN b_crm_contact CT ON L.CONTACT_ID = CT.ID ';
}
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . ($field != '*' ? ' AS ' . $field : '');
}
}
if (!isset($arSqlSelect['ID'])) {
$arSqlSelect['ID'] = $arFields['ID'];
}
$sSqlSelect = implode(",\n", $arSqlSelect);
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity(self::$sUFEntityID, 'L.ID');
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$arSqlSearch = array();
// check permissions
$sSqlPerm = '';
if (!CCrmPerms::IsAdmin() && (!array_key_exists('CHECK_PERMISSIONS', $arFilter) || $arFilter['CHECK_PERMISSIONS'] !== 'N')) {
$arPermType = array();
if (!isset($arFilter['PERMISSION'])) {
$arPermType[] = 'READ';
} else {
$arPermType = is_array($arFilter['PERMISSION']) ? $arFilter['PERMISSION'] : array($arFilter['PERMISSION']);
}
$sSqlPerm = self::BuildPermSql('L', $arPermType);
if ($sSqlPerm === false) {
$CDBResult = new CDBResult();
$CDBResult->InitFromArray(array());
return $CDBResult;
}
if (strlen($sSqlPerm) > 0) {
$sSqlPerm = ' AND ' . $sSqlPerm;
}
}
// where
$arWhereFields = array('ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'LEAD_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LEAD_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CONTACT_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_FULL_NAME' => array('TABLE_ALIAS' => 'CT', 'FIELD_NAME' => 'CT.FULL_NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMPANY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMPANY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'COMPANY_TITLE' => array('TABLE_ALIAS' => 'C', 'FIELD_NAME' => 'C.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STATE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STATE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STAGE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STAGE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'TYPE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TYPE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'PRODUCT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PRODUCT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ACCOUNT_CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ACCOUNT_CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TITLE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CLOSED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMMENTS' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMMENTS', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ADDITIONAL_INFO' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ADDITIONAL_INFO', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'LOCATION_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LOCATION_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'DATE_CREATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_CREATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'BEGINDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.BEGINDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'CLOSEDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSEDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'EVENT_DATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_DATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'DATE_MODIFY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_MODIFY', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'PROBABILITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PROBABILITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EVENT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CREATED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CREATED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ASSIGNED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ASSIGNED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'OPENED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPENED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'MODIFY_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.MODIFY_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EXCH_RATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EXCH_RATE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ORIGINATOR_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGINATOR_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ORIGIN_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGIN_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false));
$obQueryWhere->SetFields($arWhereFields);
if (!is_array($arFilter)) {
$arFilter = array();
}
$sQueryWhereFields = $obQueryWhere->GetQuery($arFilter);
$sSqlSearch = '';
foreach ($arSqlSearch as $r) {
if (strlen($r) > 0) {
$sSqlSearch .= "\n\t\t\t\tAND ({$r}) ";
}
}
$CCrmUserType = new CCrmUserType($GLOBALS['USER_FIELD_MANAGER'], self::$sUFEntityID);
$CCrmUserType->ListPrepareFilter($arFilter);
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$sSqlSearch .= "\n\t\t\t\tAND ({$r}) ";
}
if (!empty($sQueryWhereFields)) {
$sSqlSearch .= "\n\t\t\t\tAND ({$sQueryWhereFields}) ";
}
$arFieldsOrder = array('ASSIGNED_BY' => 'L.ASSIGNED_BY_ID', 'CREATED_BY' => 'L.CREATED_BY_ID', 'MODIFY_BY' => 'L.MODIFY_BY_ID', 'DATE_CREATE' => 'L.DATE_CREATE', 'DATE_MODIFY' => 'L.DATE_MODIFY');
// order
$arSqlOrder = array();
if (!is_array($arOrder)) {
$arOrder = array('DATE_CREATE' => 'DESC');
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtolower($order);
if ($order != 'asc') {
$order = 'desc';
}
if (isset($arFieldsOrder[$by])) {
$arSqlOrder[$by] = " {$arFieldsOrder[$by]} {$order} ";
} else {
if (isset($arFields[$by]) && $by != 'ADDRESS') {
$arSqlOrder[$by] = " L.{$by} {$order} ";
} else {
if ($s = $obUserFieldsSql->GetOrder($by)) {
$arSqlOrder[$by] = " {$s} {$order} ";
} else {
$by = 'date_create';
$arSqlOrder[$by] = " L.DATE_CREATE {$order} ";
}
}
}
}
if (count($arSqlOrder) > 0) {
$sSqlOrder = "\n\t\t\t\tORDER BY " . implode(', ', $arSqlOrder);
} else {
$sSqlOrder = '';
}
$sSql = "\n\t\t\tSELECT\n\t\t\t\t{$sSqlSelect}\n\t\t\t\t{$obUserFieldsSql->GetSelect()}\n\t\t\tFROM\n\t\t\t\tb_crm_deal L {$sSqlJoin}\n\t\t\t\t{$obUserFieldsSql->GetJoin('L.ID')}\n\t\t\tWHERE\n\t\t\t\t1=1 {$sSqlSearch}\n\t\t\t\t{$sSqlPerm}\n\t\t\t{$sSqlOrder}";
if ($nPageTop !== false) {
$nPageTop = (int) $nPageTop;
$sSql = $DB->TopSql($sSql, $nPageTop);
}
$obRes = $DB->Query($sSql, false, 'FILE: ' . __FILE__ . '<br /> LINE: ' . __LINE__);
$obRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields(self::$sUFEntityID));
return $obRes;
}
static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array())
{
global $DB;
if (empty($arSelectFields)) {
$arSelectFields = array("ID", "ACTIVE", "TITLE", "PHONE", "SCHEDULE", "ADDRESS", "DESCRIPTION", "GPS_N", "GPS_S", "IMAGE_ID", "DATE_CREATE", "DATE_MODIFY", "USER_ID", "XML_ID", "SORT", "EMAIL", "ISSUING_CENTER", "SHIPPING_CENTER", "SITE_ID");
}
$keyForDelete = array_search("PRODUCT_AMOUNT", $arSelectFields);
if (!isset($arFilter["PRODUCT_ID"]) && $keyForDelete !== false) {
unset($arSelectFields[$keyForDelete]);
}
if ($keyForDelete == false) {
$keyForDelete = array_search("ELEMENT_ID", $arSelectFields);
if ($keyForDelete !== false) {
unset($arSelectFields[$keyForDelete]);
}
}
$productID = '(';
if (is_array($arFilter["PRODUCT_ID"])) {
foreach ($arFilter["PRODUCT_ID"] as $id) {
$productID .= intval($id) . ',';
}
$productID = rtrim($productID, ',') . ')';
} else {
$productID .= intval($arFilter["PRODUCT_ID"]) . ')';
}
$arFields = array("ID" => array("FIELD" => "CS.ID", "TYPE" => "int"), "ACTIVE" => array("FIELD" => "CS.ACTIVE", "TYPE" => "string"), "TITLE" => array("FIELD" => "CS.TITLE", "TYPE" => "string"), "PHONE" => array("FIELD" => "CS.PHONE", "TYPE" => "string"), "SCHEDULE" => array("FIELD" => "CS.SCHEDULE", "TYPE" => "string"), "ADDRESS" => array("FIELD" => "CS.ADDRESS", "TYPE" => "string"), "DESCRIPTION" => array("FIELD" => "CS.DESCRIPTION", "TYPE" => "string"), "GPS_N" => array("FIELD" => "CS.GPS_N", "TYPE" => "string"), "GPS_S" => array("FIELD" => "CS.GPS_S", "TYPE" => "string"), "IMAGE_ID" => array("FIELD" => "CS.IMAGE_ID", "TYPE" => "int"), "LOCATION_ID" => array("FIELD" => "CS.LOCATION_ID", "TYPE" => "int"), "DATE_CREATE" => array("FIELD" => "CS.DATE_CREATE", "TYPE" => "datetime"), "DATE_MODIFY" => array("FIELD" => "CS.DATE_MODIFY", "TYPE" => "datetime"), "USER_ID" => array("FIELD" => "CS.USER_ID", "TYPE" => "int"), "MODIFIED_BY" => array("FIELD" => "CS.MODIFIED_BY", "TYPE" => "int"), "XML_ID" => array("FIELD" => "CS.XML_ID", "TYPE" => "string"), "SORT" => array("FIELD" => "CS.SORT", "TYPE" => "int"), "EMAIL" => array("FIELD" => "CS.EMAIL", "TYPE" => "string"), "ISSUING_CENTER" => array("FIELD" => "CS.ISSUING_CENTER", "TYPE" => "char"), "SHIPPING_CENTER" => array("FIELD" => "CS.SHIPPING_CENTER", "TYPE" => "char"), "SITE_ID" => array("FIELD" => "CS.SITE_ID", "TYPE" => "string"), "PRODUCT_AMOUNT" => array("FIELD" => "CP.AMOUNT", "TYPE" => "double", "FROM" => "LEFT JOIN b_catalog_store_product CP ON (CS.ID = CP.STORE_ID AND CP.PRODUCT_ID IN " . $productID . ")"), "ELEMENT_ID" => array("FIELD" => "CP.PRODUCT_ID", "TYPE" => "int"));
$userField = new CUserTypeSQL();
$userField->SetEntity("CAT_STORE", "CS.ID");
$userField->SetSelect($arSelectFields);
$userField->SetFilter($arFilter);
$userField->SetOrder($arOrder);
$strUfFilter = $userField->GetFilter();
$strSqlUfFilter = strlen($strUfFilter) > 0 ? " (" . $strUfFilter . ") " : "";
$strSqlUfOrder = "";
foreach ($arOrder as $field => $by) {
$field = $userField->GetOrder($field);
if (empty($field)) {
continue;
}
if (strlen($strSqlUfOrder) > 0) {
$strSqlUfOrder .= ', ';
}
$strSqlUfOrder .= $field . " " . $by;
}
$arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields);
$arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]);
if (empty($arGroupBy) && is_array($arGroupBy)) {
$strSql = "SELECT " . $arSqls["SELECT"] . " " . $userField->GetSelect() . " FROM b_catalog_store CS " . $arSqls["FROM"] . " " . $userField->GetJoin("CS.ID");
if (!empty($arSqls["WHERE"])) {
$strSql .= " WHERE " . $arSqls["WHERE"] . " ";
}
if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " AND " . $strSqlUfFilter . " ";
} elseif (strlen($arSqls["WHERE"]) == 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " WHERE " . $strSqlUfFilter . " ";
}
if (!empty($arSqls["GROUPBY"])) {
$strSql .= " GROUP BY " . $arSqls["GROUPBY"];
}
$dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
if ($arRes = $dbRes->Fetch()) {
return $arRes["CNT"];
} else {
return false;
}
}
$strSql = "SELECT " . $arSqls["SELECT"] . " " . $userField->GetSelect() . " FROM b_catalog_store CS " . $arSqls["FROM"] . " " . $userField->GetJoin("CS.ID");
if (!empty($arSqls["WHERE"])) {
$strSql .= " WHERE " . $arSqls["WHERE"] . " ";
}
if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " AND " . $strSqlUfFilter . " ";
} elseif (strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " WHERE " . $strSqlUfFilter . " ";
}
if (!empty($arSqls["GROUPBY"])) {
$strSql .= " GROUP BY " . $arSqls["GROUPBY"];
}
if (!empty($arSqls["ORDERBY"])) {
$strSql .= " ORDER BY " . $arSqls["ORDERBY"];
} elseif (strlen($arSqls["ORDERBY"]) <= 0 && strlen($strSqlUfOrder) > 0) {
$strSql .= " ORDER BY " . $strSqlUfOrder;
}
$intTopCount = 0;
$boolNavStartParams = !empty($arNavStartParams) && is_array($arNavStartParams);
if ($boolNavStartParams && array_key_exists('nTopCount', $arNavStartParams)) {
$intTopCount = intval($arNavStartParams["nTopCount"]);
}
if ($boolNavStartParams && 0 >= $intTopCount) {
$strSql_tmp = "SELECT COUNT('x') as CNT FROM b_catalog_store CS " . $arSqls["FROM"] . " " . $userField->GetJoin("CS.ID");
if (!empty($arSqls["WHERE"])) {
$strSql_tmp .= " WHERE " . $arSqls["WHERE"];
}
if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUfFilter) > 0) {
$strSql_tmp .= " AND " . $strSqlUfFilter . " ";
} elseif (strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUfFilter) > 0) {
$strSql_tmp .= " WHERE " . $strSqlUfFilter . " ";
}
if (!empty($arSqls["GROUPBY"])) {
$strSql_tmp .= " GROUP BY " . $arSqls["GROUPBY"];
}
$dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$cnt = 0;
if (empty($arSqls["GROUPBY"])) {
if ($arRes = $dbRes->Fetch()) {
$cnt = $arRes["CNT"];
} else {
$cnt = $dbRes->SelectedRowsCount();
}
}
$dbRes = new CDBResult();
$dbRes->NavQuery($strSql, $cnt, $arNavStartParams);
} else {
if ($boolNavStartParams && 0 < $intTopCount) {
$strSql .= " LIMIT " . $intTopCount;
}
$dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
return $dbRes;
}
/**
* @param $arOrder
* @param $arFilter
* @param array $arNavParams
* @param array $arParams
* @param array $arSelect
* @return bool|CDBResult
*
* @global $DB CDatabase
* @global $DBType string
*/
public static function GetList($arOrder, $arFilter, $arNavParams = array(), $arParams = array(), $arSelect = array())
{
global $DB, $DBType, $USER_FIELD_MANAGER;
$arSqlSearch = CTaskTemplates::GetFilter($arFilter, $arParams);
// check permissions
if (isset($arParams['USER_ID'])) {
$executiveUserId = (int) $arParams['USER_ID'];
$isAdmin = CTasksTools::IsAdmin($executiveUserId) || CTasksTools::IsPortalB24Admin($executiveUserId);
if (!$isAdmin) {
$sql = 'TT.CREATED_BY = ' . (int) $executiveUserId;
if ($executiveUserId && ($arDepsIDs = CTasks::GetSubordinateDeps($executiveUserId))) {
if (!is_array($arDepsIDs)) {
$arDepsIDs = array(intval($arDepsIDs));
}
/** @noinspection PhpDynamicAsStaticMethodCallInspection */
$rsDepartmentField = CUserTypeEntity::GetList(array(), array("ENTITY_ID" => "USER", "FIELD_NAME" => "UF_DEPARTMENT"));
$cntOfDepartments = count($arDepsIDs);
if ($cntOfDepartments && ($arDepartmentField = $rsDepartmentField->Fetch())) {
if (strtolower($DBType) === 'oracle' && ($valuesLimit = 1000) && $cntOfDepartments > $valuesLimit) {
$arConstraints = array();
$sliceIndex = 0;
while ($sliceIndex < $cntOfDepartments) {
$arConstraints[] = 'BUF1.VALUE_INT IN (' . implode(',', array_slice($arDepsIDs, $sliceIndex, $valuesLimit)) . ')';
$sliceIndex += $valuesLimit;
}
$strConstraint = '(' . implode(' OR ', $arConstraints) . ')';
} else {
$strConstraint = "BUF1.VALUE_INT IN (" . implode(",", $arDepsIDs) . ")";
}
$sql .= "\n\t\t\t\t\t\t\tOR 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_utm_user BUF1\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tBUF1.FIELD_ID = " . $arDepartmentField["ID"] . "\n\t\t\t\t\t\t\t\tAND BUF1.VALUE_ID = TT.CREATED_BY\n\t\t\t\t\t\t\t\tAND " . $strConstraint . "\n\t\t\t\t\t\t\t) ";
}
}
$arSqlSearch[] = ' (' . $sql . ') ';
}
}
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("TASKS_TASK_TEMPLATE", "TT.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$arSqlSearch[] = "(" . $r . ")";
}
$arFields = array('ID' => array('FIELD' => 'TT.ID', 'DEFAULT' => true), 'TITLE' => array('FIELD' => 'TT.TITLE', 'DEFAULT' => true), 'DESCRIPTION' => array('FIELD' => 'TT.DESCRIPTION', 'DEFAULT' => true), 'DESCRIPTION_IN_BBCODE' => array('FIELD' => 'TT.DESCRIPTION_IN_BBCODE', 'DEFAULT' => true), 'PRIORITY' => array('FIELD' => 'TT.PRIORITY', 'DEFAULT' => true), 'STATUS' => array('FIELD' => 'TT.STATUS', 'DEFAULT' => true), 'RESPONSIBLE_ID' => array('FIELD' => 'TT.RESPONSIBLE_ID', 'DEFAULT' => true), 'DEADLINE_AFTER' => array('FIELD' => 'TT.DEADLINE_AFTER', 'DEFAULT' => true), 'REPLICATE' => array('FIELD' => 'TT.REPLICATE', 'DEFAULT' => true), 'REPLICATE_PARAMS' => array('FIELD' => 'TT.REPLICATE_PARAMS', 'DEFAULT' => true), 'CREATED_BY' => array('FIELD' => 'TT.CREATED_BY', 'DEFAULT' => true), 'XML_ID' => array('FIELD' => 'TT.XML_ID', 'DEFAULT' => true), 'ALLOW_CHANGE_DEADLINE' => array('FIELD' => 'TT.ALLOW_CHANGE_DEADLINE', 'DEFAULT' => true), 'ALLOW_TIME_TRACKING' => array('FIELD' => 'TT.ALLOW_TIME_TRACKING', 'DEFAULT' => true), 'TASK_CONTROL' => array('FIELD' => 'TT.TASK_CONTROL', 'DEFAULT' => true), 'ADD_IN_REPORT' => array('FIELD' => 'TT.ADD_IN_REPORT', 'DEFAULT' => true), 'GROUP_ID' => array('FIELD' => 'TT.GROUP_ID', 'DEFAULT' => true), 'PARENT_ID' => array('FIELD' => 'TT.PARENT_ID', 'DEFAULT' => true), 'MULTITASK' => array('FIELD' => 'TT.MULTITASK', 'DEFAULT' => true), 'SITE_ID' => array('FIELD' => 'TT.SITE_ID', 'DEFAULT' => true), 'ACCOMPLICES' => array('FIELD' => 'TT.ACCOMPLICES', 'DEFAULT' => true), 'AUDITORS' => array('FIELD' => 'TT.AUDITORS', 'DEFAULT' => true), 'RESPONSIBLES' => array('FIELD' => 'TT.RESPONSIBLES', 'DEFAULT' => true), 'FILES' => array('FIELD' => 'TT.FILES', 'DEFAULT' => true), 'TAGS' => array('FIELD' => 'TT.TAGS', 'DEFAULT' => true), 'DEPENDS_ON' => array('FIELD' => 'TT.DEPENDS_ON', 'DEFAULT' => true), 'TASK_ID' => array('FIELD' => 'TT.TASK_ID', 'DEFAULT' => true), 'TPARAM_TYPE' => array('FIELD' => 'TT.TPARAM_TYPE', 'DEFAULT' => true), 'BASE_TEMPLATE_ID' => array('FIELD' => 'CASE WHEN TDD.' . Template\DependencyTable::getPARENTIDColumnName() . ' IS NULL THEN 0 ELSE TDD.' . Template\DependencyTable::getPARENTIDColumnName() . ' END', 'DEFAULT' => false), 'TEMPLATE_CHILDREN_COUNT' => array('FIELD' => 'CASE WHEN TEMPLATE_CHILDREN_COUNT IS NULL THEN 0 ELSE TEMPLATE_CHILDREN_COUNT END', 'DEFAULT' => false), 'CREATED_BY_NAME' => array('FIELD' => 'CU.NAME', 'DEFAULT' => true, 'ALWAYS' => true), 'CREATED_BY_LAST_NAME' => array('FIELD' => 'CU.LAST_NAME ', 'DEFAULT' => true, 'ALWAYS' => true), 'CREATED_BY_SECOND_NAME' => array('FIELD' => 'CU.SECOND_NAME', 'DEFAULT' => true, 'ALWAYS' => true), 'CREATED_BY_LOGIN' => array('FIELD' => 'CU.LOGIN', 'DEFAULT' => true, 'ALWAYS' => true), 'CREATED_BY_WORK_POSITION' => array('FIELD' => 'CU.WORK_POSITION', 'DEFAULT' => true, 'ALWAYS' => true), 'CREATED_BY_PHOTO' => array('FIELD' => 'CU.PERSONAL_PHOTO', 'DEFAULT' => true, 'ALWAYS' => true), 'RESPONSIBLE_NAME' => array('FIELD' => 'RU.NAME', 'DEFAULT' => true, 'ALWAYS' => true), 'RESPONSIBLE_LAST_NAME' => array('FIELD' => 'RU.LAST_NAME', 'DEFAULT' => true, 'ALWAYS' => true), 'RESPONSIBLE_SECOND_NAME' => array('FIELD' => 'RU.SECOND_NAME', 'DEFAULT' => true, 'ALWAYS' => true), 'RESPONSIBLE_LOGIN' => array('FIELD' => 'RU.LOGIN', 'DEFAULT' => true, 'ALWAYS' => true), 'RESPONSIBLE_WORK_POSITION' => array('FIELD' => 'RU.WORK_POSITION', 'DEFAULT' => true, 'ALWAYS' => true), 'RESPONSIBLE_PHOTO' => array('FIELD' => 'RU.PERSONAL_PHOTO', 'DEFAULT' => true, 'ALWAYS' => true));
$filterByBaseTemplate = false;
$selectBaseTemplateId = false;
$useChildrenCount = false;
if (!is_array($arSelect)) {
$arSelect = array();
}
$defaultSelect = array();
$alwaysSelect = array();
foreach ($arFields as $field => $rule) {
if ($rule['DEFAULT']) {
$defaultSelect[] = $field;
}
if ($rule['ALWAYS']) {
$alwaysSelect[] = $field;
}
}
if (count($arSelect) <= 0) {
$arSelect = $defaultSelect;
} elseif (in_array("*", $arSelect)) {
$arSelect = array_diff(array_merge($defaultSelect, $arSelect), array("*"));
}
$arSelect = array_merge($arSelect, $alwaysSelect);
$selectBaseTemplateId = in_array('BASE_TEMPLATE_ID', $arSelect);
$useChildrenCount = in_array('TEMPLATE_CHILDREN_COUNT', $arSelect);
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $field => $direction) {
if ($field == 'BASE_TEMPLATE_ID') {
$selectBaseTemplateId = true;
}
if ($field == 'TEMPLATE_CHILDREN_COUNT') {
$useChildrenCount = true;
}
}
if (!is_array($arFilter)) {
$arFilter = array();
}
if (!is_array($arParams)) {
$arParams = array();
}
foreach ($arFilter as $key => $value) {
$keyParsed = CTasks::MkOperationFilter($key);
if ($keyParsed['FIELD'] == 'BASE_TEMPLATE_ID') {
$filterByBaseTemplate = true;
}
if ($keyParsed['FIELD'] == 'TEMPLATE_CHILDREN_COUNT') {
$useChildrenCount = true;
}
}
$includeSubtree = $arParams['INCLUDE_TEMPLATE_SUBTREE'] === true || $arParams['INCLUDE_TEMPLATE_SUBTREE'] === 'Y';
$excludeSubtree = $arParams['EXCLUDE_TEMPLATE_SUBTREE'] === true || $arParams['EXCLUDE_TEMPLATE_SUBTREE'] === 'Y';
$treeJoin = '';
if ($excludeSubtree) {
$treeJoin = "";
} else {
$treeJoin = "LEFT JOIN " . Template\DependencyTable::getTableName() . " TD on TT.ID = TD.TEMPLATE_ID" . ($includeSubtree ? "" : " AND TD.DIRECT = '1'");
}
$temporalTableName = \Bitrix\Tasks\DB\Helper::getTemporaryTableNameSql();
$strFrom = "FROM\n\t\t\t\tb_tasks_template TT\n\n\t\t\t" . $treeJoin . "\n\n\t\t\t" . ($selectBaseTemplateId ? "\n\t\t\tLEFT JOIN\n\t\t\t\t" . Template\DependencyTable::getTableName() . " TDD ON TT.ID = TDD.TEMPLATE_ID AND TDD.DIRECT = '1'\n\t\t\t" : "\n\t\t\t") . "\n\n\t\t\t" . ($useChildrenCount ? "\n\t\t\t\tLEFT JOIN (\n\t\t\t\t\tSELECT TTI.ID, COUNT(TDDC.TEMPLATE_ID) AS TEMPLATE_CHILDREN_COUNT\n\t\t\t\t\tfrom\n\t\t\t\t\t\tb_tasks_template TTI\n\t\t\t\t\t\tINNER JOIN " . Template\DependencyTable::getTableName() . " TDDC ON TTI.ID = TDDC.PARENT_TEMPLATE_ID AND TDDC.DIRECT = '1'\n\t\t\t\t\tGROUP BY TTI.ID\n\t\t\t\t) " . $temporalTableName . " on " . $temporalTableName . ".ID = TT.ID\n\t\t\t" : "\n\t\t\t") . "\n\n\t\t\tLEFT JOIN\n\t\t\t\tb_user CU ON CU.ID = TT.CREATED_BY\n\t\t\tLEFT JOIN\n\t\t\t\tb_user RU ON RU.ID = TT.RESPONSIBLE_ID\n\t\t\t\n\t\t\t" . $obUserFieldsSql->GetJoin("TT.ID") . "\n\n\t\t\t" . (sizeof($arSqlSearch) ? "WHERE " . implode(" AND ", $arSqlSearch) : "") . " ";
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "task") {
$arSqlOrder[] = " TT " . $order . " ";
} elseif ($by == "title") {
$arSqlOrder[] = " TT.TITLE " . $order . " ";
} elseif ($by == "depends_on") {
$arSqlOrder[] = " TT.DEPENDS_ON " . $order . " ";
} elseif ($by == "rand") {
$arSqlOrder[] = CTasksTools::getRandFunction();
} elseif ($by === 'responsible_last_name') {
$arSqlOrder[] = " RU.LAST_NAME " . $order . " ";
} elseif ($by === 'tparam_type') {
$arSqlOrder[] = " TT.TPARAM_TYPE " . $order . " ";
} elseif ($by === 'template_children_count') {
$arSqlOrder[] = " TEMPLATE_CHILDREN_COUNT " . $order . " ";
} elseif ($by === 'base_template_id') {
$arSqlOrder[] = " BASE_TEMPLATE_ID " . $order . " ";
} elseif (substr($by, 0, 3) === 'uf_') {
if ($s = $obUserFieldsSql->GetOrder($by)) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
} else {
$arSqlOrder[] = " TT.ID " . $order . " ";
$by = "id";
}
if ($by !== 'rand' && !in_array(strtoupper($by), $arSelect)) {
$arSelect[] = strtoupper($by);
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$arSqlOrderCnt = count($arSqlOrder);
for ($i = 0; $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
if (!in_array("ID", $arSelect)) {
$arSelect[] = "ID";
}
$arSqlSelect = array();
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = \Bitrix\Tasks\DB\Helper::wrapColumnWithFunction($arFields[$field]['FIELD'], $arFields[$field]['WRAP']) . " AS " . $field;
}
}
if (!sizeof($arSqlSelect)) {
$arSqlSelect = "TT.ID AS ID";
} else {
$arSqlSelect = implode(",\n", $arSqlSelect);
}
$ufSelect = $obUserFieldsSql->GetSelect();
if (strlen($ufSelect)) {
$arSqlSelect .= $ufSelect;
}
$strSql = "\n\t\t\tSELECT \n\t\t\t\t" . $arSqlSelect . "\n\t\t\t\t" . $strFrom . "\n\t\t\t\t" . $strSqlOrder;
if (isset($arNavParams["NAV_PARAMS"]) && is_array($arNavParams["NAV_PARAMS"])) {
$nTopCount = (int) $arNavParams['NAV_PARAMS']['nTopCount'];
if ($nTopCount > 0) {
$strSql = $DB->TopSql($strSql, $nTopCount);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK_TEMPLATE"));
} else {
$res_cnt = $DB->Query("SELECT COUNT(TT.ID) as C " . $strFrom);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK_TEMPLATE"));
$res->NavQuery($strSql, $res_cnt["C"], $arNavParams["NAV_PARAMS"]);
}
} else {
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK_TEMPLATE"));
}
return $res;
}
public static function GetList($arOrder = array(), $arFilter = array(), $arSelect = array(), $arParams = array())
{
global $DB, $USER, $USER_FIELD_MANAGER;
$bIgnoreErrors = false;
$nPageTop = false;
$bGetZombie = false;
if (!is_array($arParams)) {
$nPageTop = $arParams;
$arParams = false;
} else {
if (isset($arParams['nPageTop'])) {
$nPageTop = $arParams['nPageTop'];
}
if (isset($arParams['bIgnoreErrors'])) {
$bIgnoreErrors = (bool) $arParams['bIgnoreErrors'];
}
if (isset($arParams['bGetZombie'])) {
$bGetZombie = (bool) $arParams['bGetZombie'];
}
}
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("TASKS_TASK", "T.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
if (is_array($arParams) && array_key_exists('USER_ID', $arParams) && $arParams['USER_ID'] > 0) {
$userID = (int) $arParams['USER_ID'];
} else {
$userID = is_object($USER) ? intval($USER->GetID()) : 0;
}
$arFields = array("ID" => "T.ID", "TITLE" => "T.TITLE", "DESCRIPTION" => "T.DESCRIPTION", "DESCRIPTION_IN_BBCODE" => "T.DESCRIPTION_IN_BBCODE", "DECLINE_REASON" => "T.DECLINE_REASON", "PRIORITY" => "T.PRIORITY", "STATUS" => "\n\t\t\t\tCASE\n\t\t\t\t\tWHEN\n\t\t\t\t\t\tT.DEADLINE < " . $DB->CurrentTimeFunction() . " AND T.STATUS != '4' AND T.STATUS != '5' AND (T.STATUS != '7' OR T.RESPONSIBLE_ID != " . $userID . ")\n\t\t\t\t\tTHEN\n\t\t\t\t\t\t'-1'\n\t\t\t\t\tWHEN\n\t\t\t\t\t\tTV.USER_ID IS NULL\n\t\t\t\t\t\tAND\n\t\t\t\t\t\tT.CREATED_BY != " . $userID . "\n\t\t\t\t\t\tAND\n\t\t\t\t\t\t(T.STATUS = 1 OR T.STATUS = 2)\n\t\t\t\t\tTHEN\n\t\t\t\t\t\t'-2'\n\t\t\t\t\tELSE\n\t\t\t\t\t\tT.STATUS\n\t\t\t\tEND\n\t\t\t", "STATUS_COMPLETE" => "\n\t\t\t\tCASE\n\t\t\t\t\tWHEN\n\t\t\t\t\t\tT.STATUS = '5'\n\t\t\t\t\tTHEN\n\t\t\t\t\t\t'2'\n\t\t\t\t\tELSE\n\t\t\t\t\t\t'1'\n\t\t\t\t\tEND\n\t\t\t", "REAL_STATUS" => "T.STATUS", "MULTITASK" => "T.MULTITASK", "RESPONSIBLE_ID" => "T.RESPONSIBLE_ID", "RESPONSIBLE_NAME" => "RU.NAME", "RESPONSIBLE_LAST_NAME" => "RU.LAST_NAME", "RESPONSIBLE_SECOND_NAME" => "RU.SECOND_NAME", "RESPONSIBLE_LOGIN" => "RU.LOGIN", "RESPONSIBLE_WORK_POSITION" => "RU.WORK_POSITION", "RESPONSIBLE_PHOTO" => "RU.PERSONAL_PHOTO", "DATE_START" => $DB->DateToCharFunction("T.DATE_START", "FULL"), "DURATION_PLAN" => "T.DURATION_PLAN", "DURATION_TYPE" => "T.DURATION_TYPE", "DURATION_FACT" => "(SELECT SUM(TE.MINUTES) FROM b_tasks_elapsed_time TE WHERE TE.TASK_ID = T.ID GROUP BY TE.TASK_ID)", "TIME_ESTIMATE" => "T.TIME_ESTIMATE", "TIME_SPENT_IN_LOGS" => "(SELECT SUM(TE.SECONDS) FROM b_tasks_elapsed_time TE WHERE TE.TASK_ID = T.ID GROUP BY TE.TASK_ID)", "REPLICATE" => "T.REPLICATE", "DEADLINE" => $DB->DateToCharFunction("T.DEADLINE", "FULL"), "DEADLINE_ORIG" => "T.DEADLINE", "START_DATE_PLAN" => $DB->DateToCharFunction("T.START_DATE_PLAN", "FULL"), "END_DATE_PLAN" => $DB->DateToCharFunction("T.END_DATE_PLAN", "FULL"), "CREATED_BY" => "T.CREATED_BY", "CREATED_BY_NAME" => "CU.NAME", "CREATED_BY_LAST_NAME" => "CU.LAST_NAME", "CREATED_BY_SECOND_NAME" => "CU.SECOND_NAME", "CREATED_BY_LOGIN" => "CU.LOGIN", "CREATED_BY_WORK_POSITION" => "CU.WORK_POSITION", "CREATED_BY_PHOTO" => "CU.PERSONAL_PHOTO", "CREATED_DATE" => $DB->DateToCharFunction("T.CREATED_DATE", "FULL"), "CHANGED_BY" => "T.CHANGED_BY", "CHANGED_DATE" => $DB->DateToCharFunction("T.CHANGED_DATE", "FULL"), "STATUS_CHANGED_BY" => "T.CHANGED_BY", "STATUS_CHANGED_DATE" => 'CASE WHEN T.STATUS_CHANGED_DATE IS NULL THEN ' . $DB->DateToCharFunction("T.CHANGED_DATE", "FULL") . ' ELSE ' . $DB->DateToCharFunction("T.STATUS_CHANGED_DATE", "FULL") . ' END ', "CLOSED_BY" => "T.CLOSED_BY", "CLOSED_DATE" => $DB->DateToCharFunction("T.CLOSED_DATE", "FULL"), 'GUID' => 'T.GUID', "XML_ID" => "T.XML_ID", "MARK" => "T.MARK", "ALLOW_CHANGE_DEADLINE" => "T.ALLOW_CHANGE_DEADLINE", 'ALLOW_TIME_TRACKING' => 'T.ALLOW_TIME_TRACKING', "TASK_CONTROL" => "T.TASK_CONTROL", "ADD_IN_REPORT" => "T.ADD_IN_REPORT", "GROUP_ID" => "CASE WHEN T.GROUP_ID IS NULL THEN 0 ELSE T.GROUP_ID END", "FORUM_TOPIC_ID" => "T.FORUM_TOPIC_ID", "PARENT_ID" => "T.PARENT_ID", "COMMENTS_COUNT" => "FT.POSTS", "FORUM_ID" => "FT.FORUM_ID", "SITE_ID" => "T.SITE_ID", "SUBORDINATE" => ($strSql = CTasks::GetSubordinateSql('', $arParams)) ? "CASE WHEN EXISTS(" . $strSql . ") THEN 'Y' ELSE 'N' END" : "'N'", "EXCHANGE_MODIFIED" => "T.EXCHANGE_MODIFIED", "EXCHANGE_ID" => "T.EXCHANGE_ID", "OUTLOOK_VERSION" => "T.OUTLOOK_VERSION", "VIEWED_DATE" => $DB->DateToCharFunction("TV.VIEWED_DATE", "FULL"), 'DEADLINE_COUNTED' => 'T.DEADLINE_COUNTED', 'FORKED_BY_TEMPLATE_ID' => 'T.FORKED_BY_TEMPLATE_ID');
if ($bGetZombie) {
$arFields['ZOMBIE'] = 'T.ZOMBIE';
}
if (count($arSelect) <= 0 || in_array("*", $arSelect)) {
$arSelect = array_keys($arFields);
} elseif (!in_array("ID", $arSelect)) {
$arSelect[] = "ID";
}
// If DESCRIPTION selected, than BBCODE flag must be selected too
if (in_array('DESCRIPTION', $arSelect) && !in_array('DESCRIPTION_IN_BBCODE', $arSelect)) {
$arSelect[] = 'DESCRIPTION_IN_BBCODE';
}
if (!is_array($arOrder)) {
$arOrder = array();
}
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$needle = null;
$by = strtolower($by);
$order = strtolower($order);
if ($by === 'deadline') {
if (!in_array($order, array('asc', 'desc', 'asc,nulls', 'desc,nulls'), true)) {
$order = 'asc,nulls';
}
} else {
if ($order !== 'asc') {
$order = 'desc';
}
}
switch ($by) {
case 'id':
$arSqlOrder[] = " ID " . $order . " ";
break;
case 'title':
$arSqlOrder[] = " TITLE " . $order . " ";
$needle = 'TITLE';
break;
case 'date_start':
$arSqlOrder[] = " T.DATE_START " . $order . " ";
$needle = 'DATE_START';
break;
case 'created_date':
$arSqlOrder[] = " T.CREATED_DATE " . $order . " ";
$needle = 'CREATED_DATE';
break;
case 'changed_date':
$arSqlOrder[] = " T.CHANGED_DATE " . $order . " ";
$needle = 'CHANGED_DATE';
break;
case 'closed_date':
$arSqlOrder[] = " T.CLOSED_DATE " . $order . " ";
$needle = 'CLOSED_DATE';
break;
case 'start_date_plan':
$arSqlOrder[] = " T.START_DATE_PLAN " . $order . " ";
$needle = 'START_DATE_PLAN';
break;
case 'deadline':
$orderClause = self::getOrderSql('T.DEADLINE', $order, $default_order = 'asc,nulls', $nullable = true);
$needle = 'DEADLINE_ORIG';
if (!is_array($orderClause)) {
$arSqlOrder[] = $orderClause;
} else {
// COLUMN ALIAS COLUMN EXPRESSION
$arFields[$orderClause[1]] = $orderClause[0];
if (!in_array($orderClause[1], $arSelect)) {
$arSelect[] = $orderClause[1];
}
$arSqlOrder[] = $orderClause[2];
// order expression
}
break;
case 'status':
$arSqlOrder[] = " STATUS " . $order . " ";
$needle = 'STATUS';
break;
case 'status_complete':
$arSqlOrder[] = " STATUS_COMPLETE " . $order . " ";
$needle = 'STATUS_COMPLETE';
break;
case 'priority':
$arSqlOrder[] = " PRIORITY " . $order . " ";
$needle = 'PRIORITY';
break;
case 'mark':
$arSqlOrder[] = " MARK " . $order . " ";
$needle = 'MARK';
break;
case 'created_by':
$arSqlOrder[] = " CREATED_BY_LAST_NAME " . $order . " ";
$needle = 'CREATED_BY_LAST_NAME';
break;
case 'responsible_id':
$arSqlOrder[] = " RESPONSIBLE_LAST_NAME " . $order . " ";
$needle = 'RESPONSIBLE_LAST_NAME';
break;
case 'group_id':
$arSqlOrder[] = " GROUP_ID " . $order . " ";
$needle = 'GROUP_ID';
break;
case 'time_estimate':
$arSqlOrder[] = " TIME_ESTIMATE " . $order . " ";
$needle = 'TIME_ESTIMATE';
break;
case 'allow_change_deadline':
$arSqlOrder[] = " ALLOW_CHANGE_DEADLINE " . $order . " ";
$needle = 'ALLOW_CHANGE_DEADLINE';
break;
case 'allow_time_tracking':
$arSqlOrder[] = " ALLOW_TIME_TRACKING " . $order . " ";
$needle = 'ALLOW_TIME_TRACKING';
break;
default:
if (substr($by, 0, 3) === 'uf_') {
if ($s = $obUserFieldsSql->GetOrder($by)) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
} else {
CTaskAssert::logWarning('[0x9a92cf7d] invalid sort by field requested: ' . $by);
}
break;
}
if ($needle !== null && !in_array($needle, $arSelect)) {
$arSelect[] = $needle;
}
}
$arSqlSelect = array();
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . " AS " . $field;
}
}
if (!sizeof($arSqlSelect)) {
$arSqlSelect = "T.ID AS ID";
}
// First level logic MUST be 'AND', because of backward compatibility
// and some requests for checking rights, attached at first level of filter.
// Situtation when there is OR-logic at first level cannot be resolved
// in general case.
// So if there is OR-logic, it is FATAL error caused by programmer.
// But, if you want to use OR-logic at the first level of filter, you
// can do this by putting all your filter conditions to the ::SUBFILTER-xxx,
// except CHECK_PERMISSIONS, SUBORDINATE_TASKS (if you don't know exactly,
// what are consequences of this fields in OR-logic of subfilters).
if (isset($arFilter['::LOGIC'])) {
CTaskAssert::assert($arFilter['::LOGIC'] === 'AND');
}
$arSqlSearch = CTasks::GetFilter($arFilter, '', $arParams);
if (!$bGetZombie) {
$arSqlSearch[] = " T.ZOMBIE = 'N' ";
}
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$arSqlSearch[] = "(" . $r . ")";
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\t" . implode(",\n", $arSqlSelect) . "\n\t\t\t\t" . $obUserFieldsSql->GetSelect();
if (in_array('COMMENTS_COUNT', $arSelect, true) || in_array('FORUM_ID', $arSelect, true)) {
$bNeedJoinForumsTable = true;
} else {
$bNeedJoinForumsTable = false;
}
$strFrom = "\n\t\t\tFROM\n\t\t\t\tb_tasks T\n\t\t\tINNER JOIN b_user CU ON CU.ID = T.CREATED_BY \n\t\t\tINNER JOIN b_user RU ON RU.ID = T.RESPONSIBLE_ID \n\t\t\tLEFT JOIN b_tasks_viewed TV ON TV.TASK_ID = T.ID \n\t\t\t\tAND TV.USER_ID = " . $userID . " " . ($bNeedJoinForumsTable ? " LEFT JOIN b_forum_topic FT ON FT.ID = T.FORUM_TOPIC_ID " : "") . $obUserFieldsSql->GetJoin("T.ID") . " " . (sizeof($arSqlSearch) ? " WHERE " . implode(" AND ", $arSqlSearch) : "") . " ";
$strSql .= $strFrom;
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0, $arSqlOrderCnt = count($arSqlOrder); $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
if ($nPageTop !== false && is_numeric($nPageTop)) {
$strSql = $DB->TopSql($strSql, intval($nPageTop));
}
if (is_array($arParams) && array_key_exists("NAV_PARAMS", $arParams) && is_array($arParams["NAV_PARAMS"])) {
$nTopCount = intval($arParams['NAV_PARAMS']['nTopCount']);
if ($nTopCount > 0) {
$strSql = $DB->TopSql($strSql, $nTopCount);
$res = $DB->Query($strSql, $bIgnoreErrors, "File: " . __FILE__ . "<br>Line: " . __LINE__);
if ($res === false) {
throw new TasksException('', TasksException::TE_SQL_ERROR);
}
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK"));
} else {
$res_cnt = $DB->Query("SELECT COUNT(T.ID) as C " . $strFrom);
$res_cnt = $res_cnt->Fetch();
$totalTasksCount = (int) $res_cnt["C"];
// unknown by default
// Sync counters in case of mistiming
CTaskCountersProcessorHomeostasis::onTaskGetList($arFilter, $totalTasksCount);
$res = new CDBResult();
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK"));
$rc = $res->NavQuery($strSql, $totalTasksCount, $arParams["NAV_PARAMS"], $bIgnoreErrors);
if ($bIgnoreErrors && $rc === false) {
throw new TasksException('', TasksException::TE_SQL_ERROR);
}
}
} else {
$res = $DB->Query($strSql, $bIgnoreErrors, "File: " . __FILE__ . "<br>Line: " . __LINE__);
if ($res === false) {
throw new TasksException('', TasksException::TE_SQL_ERROR);
}
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK"));
}
return $res;
}