Esempio n. 1
0
 /**
  * <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"=&gt;"asc", "field2"=&gt;"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&amp;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>&gt;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"=&gt;"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>
  * &lt;?<br>$filter = Array<br>(<br>    "ID"                  =&gt; "1 | 2",<br>    "TIMESTAMP_1"         =&gt; "04.02.2004", // в формате текущего сайта<br>    "TIMESTAMP_2"         =&gt; "04.02.2005",<br>    "LAST_LOGIN_1"        =&gt; "01.02.2004",<br>    "ACTIVE"              =&gt; "Y",<br>    "LOGIN"               =&gt; "nessy | admin",<br>    "NAME"                 =&gt; "Виталий &amp; Соколов",<br>    "EMAIL"               =&gt; "mail@server.com | mail@server.com",<br>    "KEYWORDS"            =&gt; "www.bitrix.ru",<br>    "PERSONAL_PROFESSION" =&gt; "системотехник",<br>    "PERSONAL_GENDER"     =&gt; "M",<br>    "PERSONAL_COUNTRY"    =&gt; "4 | 1", // Беларусь или Россия<br>    "ADMIN_NOTES"         =&gt; "\"UID = 145\"",<br>    "GROUPS_ID"           =&gt; Array(1,4,10)<br>);<br>$rsUsers = <b>CUser::GetList</b>(($by="personal_country"), ($order="desc"), $filter); // выбираем пользователей<br>$is_filtered = $rsUsers-&gt;is_filtered; // отфильтрована ли выборка ?<br>$rsUsers-&gt;NavStart(50); // разбиваем постранично по 50 записей<br>echo $rsUsers-&gt;NavPrint(GetMessage("PAGES")); // печатаем постраничную навигацию<br>while($rsUsers-&gt;NavNext(true, "f_")) :<br>	echo "[".$f_ID."] (".$f_LOGIN.") ".$f_NAME." ".$f_LAST_NAME."&lt;br&gt;";	<br>endwhile;<br>?&gt;
  * 
  * $rsUsers = CUser::GetList(array('sort' =&gt; 'asc'), 'sort');$order = array('sort' =&gt; '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;
 }
Esempio n. 3
0
 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;
 }
Esempio n. 4
0
 /**
  * <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>
  * &lt;?<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)&gt;0) InitFilterEx($FilterArr,"TICKET_LIST","set",true); else InitFilterEx($FilterArr,"TICKET_LIST","get",true);<br>if (strlen($del_filter)&gt;0) DelFilterEx($FilterArr,"TICKET_LIST",true);<br>if (CheckFilter())<br>{<br>	$arFilter = Array(<br>		"ID"                =&gt; $find_id,<br>		"LID"               =&gt; $find_lid,<br>		"LAMP"              =&gt; $find_lamp,<br>		"DATE_CREATE_1"     =&gt; $find_date_create1,<br>		"DATE_CREATE_2"     =&gt; $find_date_create2,<br>		"DATE_TIMESTAMP_1"  =&gt; $find_date_timestamp1,<br>		"DATE_TIMESTAMP_2"  =&gt; $find_date_timestamp2,<br>		"DATE_CLOSE_1"      =&gt; $find_date_close1,<br>		"DATE_CLOSE_2"      =&gt; $find_date_close2,<br>		"CLOSE"             =&gt; $find_close,<br>		"TICKET_TIME_1"     =&gt; $find_ticket_time_1,<br>		"TICKET_TIME_2"     =&gt; $find_ticket_time_2,<br>		"TITLE"             =&gt; $find_title,<br>		"MESSAGES1"         =&gt; $find_messages1,<br>		"MESSAGES2"         =&gt; $find_messages2,<br>		"OWNER"             =&gt; $find_owner,<br>		"CREATED_BY"        =&gt; $find_created_by,<br>		"RESPONSIBLE"       =&gt; $find_responsible,<br>		"RESPONSIBLE_ID"    =&gt; $find_responsible_id,<br>		"CATEGORY"          =&gt; $find_category_id,<br>		"CRITICALITY"       =&gt; $find_criticality_id,<br>		"STATUS"            =&gt; $find_status_id,<br>		"MARK"              =&gt; $find_mark_id,<br>		"SOURCE"            =&gt; $find_source_id,<br>		"MODIFIED_BY"       =&gt; $find_modified_by,<br>		"MESSAGE"           =&gt; $find_message<br>		);<br>}<br>$tickets = <b>CTicket::GetList</b>($by, $order, $arFilter, $is_filtered);<br>?&gt;<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;
 }
Esempio n. 5
0
 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);
 }
Esempio n. 6
0
	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);
	}
Esempio n. 7
0
 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;
 }
Esempio n. 8
0
	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
		);
	}
Esempio n. 9
0
 /**
  * @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;
 }
Esempio n. 10
0
 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;
 }
Esempio n. 11
0
 /**
  * @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;
 }
Esempio n. 12
0
 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;
 }