Beispiel #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;
 }
 function GetList($arOrder = array(), $arFilter = array(), $arSelect = array(), $arOptions = array())
 {
     global $DB, $USER_FIELD_MANAGER;
     $bEmptySelect = !is_array($arSelect) || empty($arSelect);
     if (is_array($arOrder) && is_array($arSelect)) {
         foreach ($arOrder as $k => $v) {
             $arSelect[] = $k;
         }
     }
     $obUserFieldsSql = new CUserTypeSQL();
     $obUserFieldsSql->SetEntity("CONTROLLER_MEMBER", "M.ID");
     $obUserFieldsSql->SetSelect($arSelect);
     $obUserFieldsSql->SetFilter($arFilter);
     $obUserFieldsSql->SetOrder($arOrder);
     static $arFields = array("ID" => array("FIELD_NAME" => "M.ID", "FIELD_TYPE" => "int"), "MEMBER_ID" => array("FIELD_NAME" => "M.MEMBER_ID", "FIELD_TYPE" => "string"), "SECRET_ID" => array("FIELD_NAME" => "M.SECRET_ID", "FIELD_TYPE" => "string"), "NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "EMAIL" => array("FIELD_NAME" => "M.EMAIL", "FIELD_TYPE" => "string"), "CONTACT_PERSON" => array("FIELD_NAME" => "M.CONTACT_PERSON", "FIELD_TYPE" => "string"), "URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"), "CONTROLLER_GROUP_ID" => array("FIELD_NAME" => "M.CONTROLLER_GROUP_ID", "FIELD_TYPE" => "int"), "CONTROLLER_GROUP_NAME" => array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string", "TABLE_ALIAS" => "G", "JOIN" => "INNER JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID"), "IN_GROUP_FROM" => array("FIELD_NAME" => "M.IN_GROUP_FROM", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "SHARED_KERNEL" => array("FIELD_NAME" => "M.SHARED_KERNEL", "FIELD_TYPE" => "string"), "ACTIVE" => array("FIELD_NAME" => "M.ACTIVE", "FIELD_TYPE" => "string"), "SITE_ACTIVE" => array("FIELD_NAME" => "M.SITE_ACTIVE", "FIELD_TYPE" => "string"), "DISCONNECTED" => array("FIELD_NAME" => "M.DISCONNECTED", "FIELD_TYPE" => "string"), "DATE_ACTIVE_FROM" => array("FIELD_NAME" => "M.DATE_ACTIVE_FROM", "FIELD_TYPE" => "datetime", "FORMAT" => "SHORT"), "DATE_ACTIVE_TO" => array("FIELD_NAME" => "M.DATE_ACTIVE_TO", "FIELD_TYPE" => "datetime", "FORMAT" => "SHORT"), "TIMESTAMP_X" => array("FIELD_NAME" => "M.TIMESTAMP_X", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "MODIFIED_BY" => array("FIELD_NAME" => "M.MODIFIED_BY", "FIELD_TYPE" => "int"), "MODIFIED_BY_USER" => array("FIELD_TYPE" => "string"), "DATE_CREATE" => array("FIELD_NAME" => "M.DATE_CREATE", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "CREATED_BY" => array("FIELD_NAME" => "M.CREATED_BY", "FIELD_TYPE" => "int"), "CREATED_BY_USER" => array("FIELD_TYPE" => "string"), "COUNTER_FREE_SPACE" => array("FIELD_NAME" => "M.COUNTER_FREE_SPACE", "FIELD_TYPE" => "int"), "COUNTER_SITES" => array("FIELD_NAME" => "M.COUNTER_SITES", "FIELD_TYPE" => "int"), "COUNTER_USERS" => array("FIELD_NAME" => "M.COUNTER_USERS", "FIELD_TYPE" => "int"), "COUNTER_LAST_AUTH" => array("FIELD_NAME" => "M.COUNTER_LAST_AUTH", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "COUNTERS_UPDATED" => array("FIELD_NAME" => "M.COUNTERS_UPDATED", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "NOTES" => array("FIELD_NAME" => "M.NOTES"));
     $arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UM.LOGIN", "') '", "UM.NAME", "' '", "UM.LAST_NAME");
     $arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UC.LOGIN", "') '", "UC.NAME", "' '", "UC.LAST_NAME");
     $rsCounters = CControllerCounter::GetList();
     while ($arCounter = $rsCounters->Fetch()) {
         $arFields["COUNTER_" . $arCounter["ID"]] = array("FIELD_NAME" => "CCV_" . $arCounter["ID"] . "." . CControllerCounter::GetTypeColumn($arCounter["COUNTER_TYPE"]), "FIELD_TYPE" => CControllerCounter::GetTypeUserType($arCounter["COUNTER_TYPE"]), "TABLE_ALIAS" => "CCV_" . $arCounter["ID"], "JOIN" => "INNER JOIN b_controller_counter_value CCV_" . $arCounter["ID"] . " ON CCV_" . $arCounter["ID"] . ".CONTROLLER_COUNTER_ID = " . $arCounter["ID"] . " AND CCV_" . $arCounter["ID"] . ".CONTROLLER_MEMBER_ID = M.ID", "LEFT_JOIN" => "LEFT JOIN b_controller_counter_value CCV_" . $arCounter["ID"] . " ON CCV_" . $arCounter["ID"] . ".CONTROLLER_COUNTER_ID = " . $arCounter["ID"] . " AND CCV_" . $arCounter["ID"] . ".CONTROLLER_MEMBER_ID = M.ID");
     }
     $obWhere = new CSQLWhere();
     $obWhere->SetFields($arFields);
     $arDateFields = array();
     foreach ($arFields as $code => $arField) {
         if ($arField["FIELD_TYPE"] == "datetime") {
             $arDateFields[] = $code;
         }
     }
     $date_field = "/(" . implode("|", $arDateFields) . ")\$/";
     $arFilterNew = array();
     if (is_array($arFilter)) {
         foreach ($arFilter as $k => $value) {
             if (is_array($value)) {
                 if (!empty($value)) {
                     $arFilterNew[$k] = $value;
                 }
             } elseif ($value === false) {
                 $arFilterNew[$k] = $value;
             } elseif (strlen($value) > 0) {
                 if (array_key_exists("date_format", $arOptions) && preg_match($date_field, $k)) {
                     $arFilterNew[$k] = ConvertTimeStamp(MakeTimeStamp($value, $arOptions["date_format"]), "FULL");
                 } else {
                     $arFilterNew[$k] = $value;
                 }
             }
         }
     }
     $strWhere = "1 = 1";
     $r = $obWhere->GetQuery($arFilterNew);
     if (strlen($r) > 0) {
         $strWhere .= " AND (" . $r . ") ";
     }
     $r = $obUserFieldsSql->GetFilter();
     if (strlen($r) > 0) {
         $strWhere .= " AND (" . $r . ") ";
     }
     if (is_array($arOrder)) {
         foreach ($arOrder as $key => $value) {
             $key = strtoupper($key);
             if (array_key_exists($key, $arFields) && isset($arFields[$key]["LEFT_JOIN"])) {
                 $obWhere->c_joins[$key]++;
             }
         }
     }
     if ($bEmptySelect) {
         $arSelectAdd = array("ID", "MEMBER_ID", "SECRET_ID", "NAME", "URL", "EMAIL", "CONTACT_PERSON", "CONTROLLER_GROUP_ID", "DISCONNECTED", "SHARED_KERNEL", "ACTIVE", "DATE_ACTIVE_FROM", "DATE_ACTIVE_TO", "SITE_ACTIVE", "TIMESTAMP_X", "MODIFIED_BY", "DATE_CREATE", "CREATED_BY", "IN_GROUP_FROM", "NOTES", "COUNTER_FREE_SPACE", "COUNTER_SITES", "COUNTER_USERS", "COUNTER_LAST_AUTH", "COUNTERS_UPDATED", "MODIFIED_BY_USER", "CREATED_BY_USER");
         if (is_array($arSelect)) {
             $arSelect = array_merge($arSelect, $arSelectAdd);
         } else {
             $arSelect = $arSelectAdd;
         }
     }
     $duplicates = array("ID" => 1);
     $strSelect = "M.ID AS ID\n";
     foreach ($arSelect as $key) {
         $key = strtoupper($key);
         if (array_key_exists($key, $arFields) && !array_key_exists($key, $duplicates)) {
             $duplicates[$key]++;
             if (isset($arFields[$key]["LEFT_JOIN"])) {
                 $obWhere->c_joins[$key]++;
             }
             if ($arFields[$key]["FIELD_TYPE"] == "datetime") {
                 if (array_key_exists("date_format", $arOptions)) {
                     $strSelect .= "," . $DB->DateFormatToDB($arOptions["date_format"], $arFields[$key]["FIELD_NAME"]) . " AS " . $key . "\n";
                 } else {
                     $strSelect .= "," . $arFields[$key]["FIELD_NAME"] . " AS " . $key . "_TMP," . $DB->DateToCharFunction($arFields[$key]["FIELD_NAME"], $arFields[$key]["FORMAT"]) . " AS " . $key . "\n";
                 }
             } else {
                 $strSelect .= "," . $arFields[$key]["FIELD_NAME"] . " AS " . $key . "\n";
             }
         }
     }
     $bUseSubQuery = false;
     if ($DB->type == "ORACLE" && $obUserFieldsSql->GetDistinct()) {
         $bUseSubQuery = true;
     }
     if ($bUseSubQuery) {
         $ob = new CUserTypeSQL();
         $ob->SetEntity("CONTROLLER_MEMBER", "M.ID");
         $ob->SetSelect($arSelect);
         $ob->SetOrder($arOrder);
         $strSql = "\n\t\t\t\tSELECT " . $strSelect . $ob->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $ob->GetJoin("M.ID") . "\n\t\t\t\tWHERE M.ID IN (\n\t\t\t\t\tSELECT M.ID\n\t\t\t\t\tFROM b_controller_member M\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t)\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $ob) . "\n\t\t\t";
     } else {
         $strSql = "\n\t\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " " . $strSelect . $obUserFieldsSql->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t\t";
     }
     $dbr = $DB->Query($strSql);
     $dbr->is_filtered = $strWhere != "1 = 1";
     $dbr->SetUserFields($USER_FIELD_MANAGER->GetUserFields("CONTROLLER_MEMBER"));
     return $dbr;
 }
Beispiel #3
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);
	}
 public static function GetList($arOrder = array(), $arFilter = array(), $arSelect = array())
 {
     global $DB, $USER_FIELD_MANAGER;
     $obUserFieldsSql = new CUserTypeSQL();
     $obUserFieldsSql->SetEntity("CONTROLLER_GROUP", "G.ID");
     $obUserFieldsSql->SetSelect($arSelect);
     $obUserFieldsSql->SetFilter($arFilter);
     $obUserFieldsSql->SetOrder($arOrder);
     static $arFields = array("ID" => array("FIELD_NAME" => "G.ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string"), "TIMESTAMP_X" => array("FIELD_NAME" => "G.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "MODIFIED_BY" => array("FIELD_NAME" => "G.MODIFIED_BY", "FIELD_TYPE" => "int"), "UPDATE_PERIOD" => array("FIELD_NAME" => "G.UPDATE_PERIOD", "FIELD_TYPE" => "int"), "MODIFIED_BY_USER" => array("FIELD_TYPE" => "string"), "DATE_CREATE" => array("FIELD_NAME" => "G.DATE_CREATE", "FIELD_TYPE" => "datetime"), "CREATED_BY" => array("FIELD_NAME" => "G.CREATED_BY", "FIELD_TYPE" => "int"), "CREATED_BY_USER" => array("FIELD_TYPE" => "string"), "TRIAL_PERIOD" => array("FIELD_NAME" => "G.TRIAL_PERIOD", "FIELD_TYPE" => "int"), "COUNTER_UPDATE_PERIOD" => array("FIELD_NAME" => "G.COUNTER_UPDATE_PERIOD", "FIELD_TYPE" => "int"), "CHECK_COUNTER_FREE_SPACE" => array("FIELD_NAME" => "G.CHECK_COUNTER_FREE_SPACE", "FIELD_TYPE" => "string"), "CHECK_COUNTER_SITES" => array("FIELD_NAME" => "G.CHECK_COUNTER_SITES", "FIELD_TYPE" => "string"), "CHECK_COUNTER_USERS" => array("FIELD_NAME" => "G.CHECK_COUNTER_USERS", "FIELD_TYPE" => "string"), "CHECK_COUNTER_LAST_AUTH" => array("FIELD_NAME" => "G.CHECK_COUNTER_LAST_AUTH", "FIELD_TYPE" => "string"));
     $arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UM.LOGIN", "UM.NAME", "UM.LAST_NAME");
     $arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UC.LOGIN", "UC.NAME", "UC.LAST_NAME");
     $obWhere = new CSQLWhere();
     $obWhere->SetFields($arFields);
     $arFilterNew = array();
     foreach ($arFilter as $k => $value) {
         if (strlen($value) > 0 || $value === false) {
             $arFilterNew[$k] = $value;
         }
     }
     $strWhere = "1 = 1";
     $r = $obWhere->GetQuery($arFilterNew);
     if (strlen($r) > 0) {
         $strWhere .= " AND (" . $r . ") ";
     }
     $r = $obUserFieldsSql->GetFilter();
     if (strlen($r) > 0) {
         $strWhere .= " AND (" . $r . ") ";
     }
     $strSql = "\n\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " G.*\n\t\t\t\t,UC.LOGIN as CREATED_BY_LOGIN\n\t\t\t\t,UC.NAME as CREATED_BY_NAME\n\t\t\t\t,UC.LAST_NAME as CREATED_BY_LAST_NAME\n\t\t\t\t,UM.LOGIN as MODIFIED_BY_LOGIN\n\t\t\t\t,UM.NAME as MODIFIED_BY_NAME\n\t\t\t\t,UM.LAST_NAME as MODIFIED_BY_LAST_NAME\n\t\t\t\t," . $DB->DateToCharFunction("G.TIMESTAMP_X") . " as TIMESTAMP_X\n\t\t\t\t," . $DB->DateToCharFunction("G.DATE_CREATE") . " as DATE_CREATE\n\t\t\t\t" . $obUserFieldsSql->GetSelect() . "\n\t\t\tFROM b_controller_group G\n\t\t\t\tLEFT JOIN b_user UC ON UC.ID=G.CREATED_BY\n\t\t\t\tLEFT JOIN b_user UM ON UM.ID=G.MODIFIED_BY\n\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t" . $obUserFieldsSql->GetJoin("G.ID") . "\n\t\t\tWHERE " . $strWhere . "\n\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t";
     $dbr = $DB->Query($strSql);
     $dbr->is_filtered = strlen($strWhere) > 0;
     return $dbr;
 }