user.php

См. документацию.

<?php
require_once($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/forum/classes/general/user.php");
 
class CForumUser extends CAllForumUser
{
    public static function GetList($arOrder = Array("ID"=>"ASC"), $arFilter = Array(), $arAddParams = array())
    {
        global $DB;
        $sqlHelper = \Bitrix\Main\Application::getConnection()->getSqlHelper();
        $arSqlSearch = array();
        $arSqlOrder = array();
        $strSqlSearch = "";
        $strSqlOrder = "";
        $arFilter = (is_array($arFilter) ? $arFilter : array());
        $arAddParams = is_array($arAddParams) ? $arAddParams : [];
        if (isset($arAddParams["nameTemplate"]))
        {
            $arAddParams["sNameTemplate"] = $arAddParams["nameTemplate"];
            unset($arAddParams["nameTemplate"]);
        }
 
        if (isset($arFilter['PERSONAL_BIRTHDAY_DATE']))
        {
            $subQuery = "SELECT U.ID FROM b_user U WHERE ";
            $key_res = CForumNew::GetFilterOperation($arFilter['PERSONAL_BIRTHDAY_DATE']);
            $key = mb_strtoupper($key_res["FIELD"]);
            $val = $arFilter['PERSONAL_BIRTHDAY_DATE'];
            $strNegative = $key_res["NEGATIVE"];
            $strOperation = $key_res["OPERATION"];
            $subQuery .= ( $strNegative === "Y"
                    ? " U.PERSONAL_BIRTHDAY IS NULL OR NOT "
                    : " U.PERSONAL_BIRTHDAY IS NOT NULL AND ")
                        . "(" . $sqlHelper->formatDate('MM-DD', 'U.PERSONAL_BIRTHDAY')
                        . $strOperation . " '".$DB->ForSql($val)."')";
            $db_sub_res = $DB->Query($subQuery);
            $arUserID = array();
            if ($db_sub_res)
            {
                while($ar_sub_res = $db_sub_res->Fetch())
                    $arUserID[] = $ar_sub_res['ID'];
            }
            if (sizeof($arUserID) > 0)
            {
                if (sizeof($arUserID) > 50)
                    $arUserID = array_slice($arUserID, 0, 50);
 
                unset($arFilter['PERSONAL_BIRTHDAY_DATE']);
                $arFilter['@USER_ID'] = $arUserID;
            }
        }
 
        foreach ($arFilter as $key => $val)
        {
            $key_res = CForumNew::GetFilterOperation($key);
            $key = mb_strtoupper($key_res["FIELD"]);
            $strNegative = $key_res["NEGATIVE"];
            $strOperation = $key_res["OPERATION"];
 
            switch ($key)
            {
                case "USER_ID":
                    $userID = intval($val);
                    if (is_array($val) && $strOperation == 'IN')
                    {
                        $userID = array();
                        foreach($val as $valI)
                            $userID[] = intval($valI);
                        $userID = array_unique($userID);
                        if (empty($userID))
                            $val = $userID = 0;
                        else
                            $userID = '(' . implode(', ', $userID). ')';
                    }
                    if (!is_array($val) && intval($userID)<=0)
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.ID IS NULL OR U.ID<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.ID IS NULL OR NOT ":"")."(U.ID ".$strOperation." ".$userID." )";
                    break;
                case "ID":
                case "RANK_ID":
                case "NUM_POSTS":
                case "AVATAR":
                    if (intval($val)<=0)
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FU.".$key." IS NULL OR FU.".$key."<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FU.".$key." IS NULL OR NOT ":"")."(FU.".$key." ".$strOperation." ".intval($val)." )";
                    break;
                case "SHOW_NAME":
                case "HIDE_FROM_ONLINE":
                case "SUBSC_GROUP_MESSAGE":
                case "SUBSC_GET_MY_MESSAGE":
                case "ALLOW_POST":
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FU.".$key." IS NULL OR LENGTH(FU.".$key.")<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FU.".$key." IS NULL OR NOT ":"")."(FU.".$key." ".$strOperation." '".$DB->ForSql($val)."' )";
                    break;
                case "ACTIVE":
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.".$key." IS NULL OR LEN(U.".$key.")<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.".$key." IS NULL OR NOT ":"")."(U.".$key." ".$strOperation." '".$DB->ForSql($val)."' )";
                    break;
                case "PERSONAL_BIRTHDATE":
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.PERSONAL_BIRTHDATE IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.PERSONAL_BIRTHDATE IS NULL OR NOT ":"")."(U.PERSONAL_BIRTHDATE ".$strOperation." '".$DB->ForSql($val)."')";
                    break;
                case "PERSONAL_BIRTHDAY":
                    if($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.PERSONAL_BIRTHDAY IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.PERSONAL_BIRTHDAY IS NULL OR NOT ":"")."(U.PERSONAL_BIRTHDAY ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
                    break;
                case "PERSONAL_BIRTHDAY_DATE":
                    $arSqlSearch[] = ($strNegative=="Y"?" U.PERSONAL_BIRTHDAY IS NULL OR NOT ":"")."(" . $sqlHelper->formatDate('MM-DD', 'U.PERSONAL_BIRTHDAY') . $strOperation." '".$DB->ForSql($val)."')";
                    break;
                case "LAST_VISIT":
                    if($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FU.LAST_VISIT IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FU.LAST_VISIT IS NULL OR NOT ":"")."(FU.LAST_VISIT ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
                    break;
                case "SHOW_ABC":
                    $val = trim($val);
                    if (!empty($val) && $val != "Y")
                    {
                        $arSqlSearch[] =
                            "(
                                (
                                    FU.SHOW_NAME = 'Y'
                                    AND
                                    LENGTH(TRIM(CONCAT_WS('',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"])."))) > 0
                                    AND
                                    (REPLACE(CONCAT_WS(' ',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"])."), '  ', ' ') LIKE '%".$DB->ForSql($val)."%')
                                )
                                OR
                                (
                                    (
                                        FU.SHOW_NAME != 'Y'
                                        OR
                                        FU.SHOW_NAME IS NULL
                                        OR
                                        (
                                            FU.SHOW_NAME = 'Y'
                                            AND
                                            LENGTH(TRIM(CONCAT_WS('',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"])."))) <= 0
                                        )
                                    )
                                    AND
                                    (
                                        U.LOGIN LIKE '%".$DB->ForSql($val)."%'
                                    )
                                )
                            )";
                    }
                    break;
            }
        }
        if (count($arSqlSearch) > 0)
            $strSqlSearch = " AND (".implode(") AND (", $arSqlSearch).") ";
 
        foreach ($arOrder as $by=>$order)
        {
            $by = mb_strtoupper($by);
            $order = mb_strtoupper($order);
 
            if ($order!="ASC") $order = "DESC";
 
            if ($by == "USER_ID") $arSqlOrder[] = " U.ID ".$order." ";
            elseif ($by == "SHOW_NAME") $arSqlOrder[] = " FU.SHOW_NAME ".$order." ";
            elseif ($by == "HIDE_FROM_ONLINE") $arSqlOrder[] = " FU.HIDE_FROM_ONLINE ".$order." ";
            elseif ($by == "SUBSC_GROUP_MESSAGE") $arSqlOrder[] = " FU.SUBSC_GROUP_MESSAGE ".$order." ";
            elseif ($by == "SUBSC_GET_MY_MESSAGE") $arSqlOrder[] = " FU.SUBSC_GET_MY_MESSAGE ".$order." ";
            elseif ($by == "NUM_POSTS") $arSqlOrder[] = " FU.NUM_POSTS ".$order." ";
            elseif ($by == "LAST_POST") $arSqlOrder[] = " FU.LAST_POST ".$order." ";
            elseif ($by == "POINTS") $arSqlOrder[] = " FU.POINTS ".$order." ";
            elseif ($by == "NAME") $arSqlOrder[] = " U.NAME ".$order." ";
            elseif ($by == "LAST_NAME") $arSqlOrder[] = " U.LAST_NAME ".$order." ";
            elseif ($by == "LOGIN") $arSqlOrder[] = " U.LOGIN ".$order." ";
            elseif ($by == "LAST_VISIT") $arSqlOrder[] = " FU.LAST_VISIT ".$order." ";
            elseif ($by == "DATE_REGISTER") $arSqlOrder[] = " U.DATE_REGISTER ".$order." ";
            elseif ($by == "SHOW_ABC") $arSqlOrder[] = " SHOW_ABC ".$order." ";
            else
            {
                $arSqlOrder[] = " FU.ID ".$order." ";
                $by = "ID";
            }
        }
        DelDuplicateSort($arSqlOrder);
        if (count($arSqlOrder) > 0)
            $strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
 
        $strSql =
            "SELECT FU.ID, U.ID as USER_ID, FU.SHOW_NAME, FU.DESCRIPTION, FU.IP_ADDRESS,
                FU.REAL_IP_ADDRESS, FU.AVATAR, FU.NUM_POSTS, FU.POINTS as NUM_POINTS,
                FU.INTERESTS, FU.SUBSC_GROUP_MESSAGE, FU.SUBSC_GET_MY_MESSAGE,
                FU.LAST_POST, FU.ALLOW_POST, FU.SIGNATURE, FU.RANK_ID,
                U.EMAIL, U.NAME, U.SECOND_NAME, U.LAST_NAME, U.LOGIN, U.PERSONAL_BIRTHDATE,
                ".$DB->DateToCharFunction("FU.DATE_REG", "SHORT")." as DATE_REG,
                ".$DB->DateToCharFunction("FU.LAST_VISIT", "FULL")." as LAST_VISIT,
                ".$DB->DateToCharFunction("FU.LAST_VISIT", "SHORT")." as LAST_VISIT_SHORT,
                ".$DB->DateToCharFunction("U.DATE_REGISTER", "SHORT")." as DATE_REGISTER_SHORT,
                U.PERSONAL_ICQ, U.PERSONAL_WWW, U.PERSONAL_PROFESSION, U.DATE_REGISTER,
                U.PERSONAL_CITY, U.PERSONAL_COUNTRY, U.EXTERNAL_AUTH_ID, U.PERSONAL_PHOTO,
                U.PERSONAL_GENDER, FU.POINTS, FU.HIDE_FROM_ONLINE,
                ".$DB->DateToCharFunction("U.PERSONAL_BIRTHDAY", "SHORT")." as PERSONAL_BIRTHDAY ".
                (array_key_exists("SHOW_ABC", $arFilter) || array_key_exists("sNameTemplate", $arAddParams) ?
                    ", \n".self::GetFormattedNameFieldsForSelect(
                        array_merge(
                            $arAddParams,
                            array(
                                "sUserTablePrefix" => "U.",
                                "sForumUserTablePrefix" => "FU.",
                                "sFieldName" => "SHOW_ABC")
                        ),
                        false
                    )
                    :
                    ""
                ).
                ((isset($arFilter['USER_ID']) || isset($arFilter['@USER_ID'])) ?
                    " FROM b_user U LEFT JOIN b_forum_user FU ON (FU.USER_ID = U.ID)"
                    :
                    " FROM b_forum_user FU LEFT JOIN b_user U ON (FU.USER_ID = U.ID)"
                ).
                " WHERE 1 = 1 ".$strSqlSearch." \n".
                $strSqlOrder;
 
        if (!empty($arAddParams["nTopCount"]))
            $strSql .= " LIMIT 0," . intval($arAddParams["nTopCount"]);
        if (isset($arAddParams["bDescPageNumbering"]) && empty($arAddParams["nTopCount"]))
        {
            $iCnt = 0;
            $strSqlCount =
                "SELECT COUNT('x') as CNT ".
                ((isset($arFilter['USER_ID']) || isset($arFilter['@USER_ID'])) ?
                    " FROM b_user U LEFT JOIN b_forum_user FU ON (FU.USER_ID = U.ID)"
                    :
                    " FROM b_forum_user FU LEFT JOIN b_user U ON (FU.USER_ID = U.ID)"
                ).
                " WHERE 1 = 1 ".$strSqlSearch;
            $db_res = $DB->Query($strSqlCount);
            if ($db_res && ($res = $db_res->Fetch()))
                $iCnt = $res["CNT"];
 
            $db_res =  new CDBResult();
            $db_res->NavQuery($strSql, $iCnt, $arAddParams);
        }
        else
        {
            $db_res = $DB->Query($strSql);
        }
        return $db_res;
    }
 
    public static function GetListEx($arOrder = Array("ID"=>"ASC"), $arFilter = Array())
    {
        global $DB;
        $sqlHelper = \Bitrix\Main\Application::getConnection()->getSqlHelper();
        $arSqlSearch = array();
        $arSqlSelect = array();
        $arSqlFrom = array();
        $arSqlGroup = array();
        $arSqlOrder = array();
        $arSql = array();
        $strSqlSearch = "";
        $strSqlSelect = "";
        $strSqlFrom = "";
        $strSqlGroup = "";
        $strSqlOrder = "";
        $strSql = "";
        $tmp = array();
        $arFilter = (is_array($arFilter) ? $arFilter : array());
 
        $arMainUserFields = array("LOGIN"=>"S", "NAME"=>"S", "LAST_NAME"=>"S", "SECOND_NAME"=>"S",
            "PERSONAL_PROFESSION"=>"S", "PERSONAL_WWW"=>"S", "PERSONAL_ICQ"=>"S", "PERSONAL_GENDER"=>"E",
            "PERSONAL_PHONE"=>"S", "PERSONAL_FAX"=>"S", "PERSONAL_MOBILE"=>"S", "PERSONAL_PAGER"=>"S",
            "PERSONAL_STREET"=>"S", "PERSONAL_MAILBOX"=>"S", "PERSONAL_CITY"=>"S", "PERSONAL_STATE"=>"S",
            "PERSONAL_ZIP"=>"S", "PERSONAL_COUNTRY"=>"I", "EXTERNAL_AUTH_ID"=>"S", "PERSONAL_NOTES"=>"S", "WORK_COMPANY"=>"S",
            "WORK_DEPARTMENT"=>"S", "WORK_POSITION"=>"S", "WORK_WWW"=>"S", "WORK_PHONE"=>"S", "WORK_FAX"=>"S",
            "WORK_PAGER"=>"S", "WORK_STREET"=>"S", "WORK_MAILBOX"=>"S", "WORK_CITY"=>"S", "WORK_STATE"=>"S",
            "WORK_ZIP"=>"S", "WORK_COUNTRY"=>"I", "WORK_PROFILE"=>"S", "WORK_NOTES"=>"S");
        $arSqlSelectConst = array(
            "FU.ID" => "FU.ID",
            "USER_ID" => "U.ID",
            "FU.SHOW_NAME" => "FU.SHOW_NAME",
            "FU.DESCRIPTION" => "FU.DESCRIPTION",
            "FU.IP_ADDRESS" => "FU.IP_ADDRESS",
            "FU.REAL_IP_ADDRESS" => "FU.REAL_IP_ADDRESS",
            "FU.AVATAR" => "FU.AVATAR",
            "FU.NUM_POSTS" => "FU.NUM_POSTS",
            "NUM_POINTS" => "FU.POINTS",
            "FU.INTERESTS" => "FU.INTERESTS",
            "FU.SUBSC_GROUP_MESSAGE" => "FU.SUBSC_GROUP_MESSAGE",
            "FU.SUBSC_GET_MY_MESSAGE" => "FU.SUBSC_GET_MY_MESSAGE",
            "FU.LAST_POST" => "FU.LAST_POST",
            "FU.ALLOW_POST" => "FU.ALLOW_POST",
            "FU.SIGNATURE" => "FU.SIGNATURE",
            "FU.RANK_ID" => "FU.RANK_ID",
            "FU.POINTS" => "FU.POINTS",
            "FU.HIDE_FROM_ONLINE" => "FU.HIDE_FROM_ONLINE",
            "U.DATE_REGISTER" => "U.DATE_REGISTER",
            "U.EMAIL" => "U.EMAIL",
            "U.NAME" => "U.NAME",
            "U.SECOND_NAME" => "U.SECOND_NAME",
            "U.LAST_NAME" => "U.LAST_NAME",
            "U.LOGIN" => "U.LOGIN",
            "U.PERSONAL_BIRTHDATE" => "U.PERSONAL_BIRTHDATE",
            "U.PERSONAL_ICQ" => "U.PERSONAL_ICQ",
            "U.PERSONAL_WWW" => "U.PERSONAL_WWW",
            "U.PERSONAL_PROFESSION" => "U.PERSONAL_PROFESSION",
            "U.PERSONAL_CITY" => "U.PERSONAL_CITY",
            "U.PERSONAL_COUNTRY" => "U.PERSONAL_COUNTRY",
            "U.EXTERNAL_AUTH_ID" => "U.EXTERNAL_AUTH_ID",
            "U.PERSONAL_PHOTO" => "U.PERSONAL_PHOTO",
            "U.PERSONAL_GENDER" => "U.PERSONAL_GENDER",
            "DATE_REG" => $DB->DateToCharFunction("FU.DATE_REG", "SHORT"),
            "LAST_VISIT" => $DB->DateToCharFunction("FU.LAST_VISIT", "FULL"),
            "PERSONAL_BIRTHDAY" => $DB->DateToCharFunction("U.PERSONAL_BIRTHDAY", "SHORT"),
            "U.WORK_POSITION" => "U.WORK_POSITION",
            "U.WORK_COMPANY" => "U.WORK_COMPANY"
            );
 
        foreach ($arFilter as $key => $val)
        {
            $key_res = CForumNew::GetFilterOperation($key);
            $key = mb_strtoupper($key_res["FIELD"]);
            $strNegative = $key_res["NEGATIVE"];
            $strOperation = $key_res["OPERATION"];
 
            switch ($key)
            {
                case "USER_ID":
                    if (intval($val)<=0)
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.ID IS NULL OR U.ID<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.ID IS NULL OR NOT ":"")."(U.ID ".$strOperation." ".intval($val)." )";
                    break;
                case "ID":
                case "RANK_ID":
                case "NUM_POSTS":
                    if (intval($val)<=0)
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FU.".$key." IS NULL OR FU.".$key."<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FU.".$key." IS NULL OR NOT ":"")."(FU.".$key." ".$strOperation." ".intval($val)." )";
                    break;
                case "SHOW_NAME":
                case "HIDE_FROM_ONLINE":
                case "SUBSC_GROUP_MESSAGE":
                case "SUBSC_GET_MY_MESSAGE":
                case "ALLOW_POST":
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FU.".$key." IS NULL OR LENGTH(FU.".$key.")<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FU.".$key." IS NULL OR NOT ":"")."(FU.".$key." ".$strOperation." '".$DB->ForSql($val)."' )";
                    break;
                case "ACTIVE":
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.".$key." IS NULL OR LEN(U.".$key.")<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.".$key." IS NULL OR NOT ":"")."(U.".$key." ".$strOperation." '".$DB->ForSql($val)."' )";
                    break;
                case "PERSONAL_BIRTHDATE":
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.PERSONAL_BIRTHDATE IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.PERSONAL_BIRTHDATE IS NULL OR NOT ":"")."(U.PERSONAL_BIRTHDATE ".$strOperation." '".$DB->ForSql($val)."')";
                    break;
                case "PERSONAL_BIRTHDAY":
                    if($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(U.PERSONAL_BIRTHDAY IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" U.PERSONAL_BIRTHDAY IS NULL OR NOT ":"")."(U.PERSONAL_BIRTHDAY ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
                    break;
                case "PERSONAL_BIRTHDAY_DATE":
                    $arSqlSearch[] = ($strNegative=="Y"?" U.PERSONAL_BIRTHDAY IS NULL OR NOT ":"")."( ". $sqlHelper->formatDate('MM-DD', 'U.PERSONAL_BIRTHDAY') .") ".$strOperation." '".$DB->ForSql($val)."')";
                    break;
                case "LAST_VISIT":
                    if($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FU.LAST_VISIT IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FU.LAST_VISIT IS NULL OR NOT ":"")."(FU.LAST_VISIT ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
                    break;
                case "LOGIN":
                case "EMAIL":
                    $arSqlSearch[] = GetFilterQuery("U.".$key, $val);
                    break;
                case "NAME":
                    $arSqlSearch[] = GetFilterQuery("U.NAME, U.LAST_NAME, U.SECOND_NAME", $val);
                    break;
                case"SUBSC_NEW_TOPIC_ONLY":
                    $key = "NEW_TOPIC_ONLY";
                    $arSqlFrom["FS"] = "INNER JOIN b_forum_subscribe FS ON (FU.USER_ID = FS.USER_ID)";
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FS.".$key." IS NULL OR LENGTH(FS.".$key.")<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FS.".$key." IS NULL OR NOT ":"")."(FS.".$key." ".$strOperation." '".$DB->ForSql($val)."' )";
                    break;
                case "SUBSC_START_DATE":
                    $key = "START_DATE";
                    $arSqlFrom["FS"] = "INNER JOIN b_forum_subscribe FS ON (FU.USER_ID = FS.USER_ID)";
                    if($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FS.".$key." IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FS.".$key." IS NULL OR NOT ":"")."(FS.".$key." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
                    break;
                case "SUBSC_FORUM_ID":
                case "SUBSC_TOPIC_ID":
                case "SUBSC":
                    $arSqlFrom["FS"] = "INNER JOIN b_forum_subscribe FS ON (FU.USER_ID = FS.USER_ID)";
                    unset($arSqlSelectConst["FU.INTERESTS"]);
                    $arSqlSelect = $arSqlSelectConst;
                    $arSqlSelect["SUBSC_COUNT"] = "COUNT(FS.ID)";
                    $arSqlSelect["SUBSC_START_DATE"] = $DB->DateToCharFunction("MIN(FS.START_DATE)", "FULL");
                    $arSqlGroup = array_merge($arSqlSelectConst, $arSqlGroup);
                    if ($key != "SUBSC")
                    {
                        $key = mb_substr($key, mb_strlen("SUBSC_"));
                        if (intval($val)<=0)
                            $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FS.".$key." IS NULL OR FS.".$key."<=0)";
                        else
                            $arSqlSearch[] = ($strNegative=="Y"?" FS.".$key." IS NULL OR NOT ":"")."(FS.".$key." ".$strOperation." ".intval($val)." )";
                    }
                    break;
                default:
                    if (mb_substr($key, 0, mb_strlen("USER_")) == "USER_")
                    {
                        $strUserKey = mb_substr($key, mb_strlen("USER_"));
                        if (array_key_exists($strUserKey, $arMainUserFields))
                        {
                            if ($arMainUserFields[$strUserKey]=="I")
                                $arSqlSearch[] = ($strNegative=="Y"?" U.".$strUserKey." IS NULL OR NOT ":"")."(U.".$strUserKey." ".$strOperation." ".intval($val)." )";
                            elseif ($arMainUserFields[$strUserKey]=="E")
                                $arSqlSearch[] = ($strNegative=="Y"?" U.".$strUserKey." IS NULL OR NOT ":"")."(U.".$strUserKey." ".$strOperation." '".$DB->ForSql($val)."' )";
                            else
                                $arSqlSearch[] = GetFilterQuery("U.".$strUserKey, $val);
                        }
                    }
            }
        }
        if (count($arSqlSearch) > 0)
            $strSqlSearch = " AND (".implode(") AND (", $arSqlSearch).") ";
        if (count($arSqlSelect) <= 0)
            $arSqlSelect = $arSqlSelectConst;
        foreach ($arSqlSelect as $key => $val)
        {
            if ($val != $key)
                $tmp[] = $val." AS ".$key;
            else
                $tmp[] = $val;
        }
        $strSqlSelect = implode(", ", $tmp);
        if (count($arSqlFrom) > 0)
            $strSqlFrom = implode(" ", $arSqlFrom);
        if (count($arSqlGroup) > 0)
            $strSqlGroup = " GROUP BY ".implode(", ", $arSqlGroup);
 
        foreach ($arOrder as $by=>$order)
        {
            $by = mb_strtoupper($by);
            $order = mb_strtoupper($order);
            if ($order!="ASC")
                $order = "DESC";
 
            if ($by == "USER_ID") $arSqlOrder[] = " U.ID ".$order." ";
            elseif ($by == "SHOW_NAME") $arSqlOrder[] = " FU.SHOW_NAME ".$order." ";
            elseif ($by == "HIDE_FROM_ONLINE") $arSqlOrder[] = " FU.HIDE_FROM_ONLINE ".$order." ";
            elseif ($by == "SUBSC_GROUP_MESSAGE") $arSqlOrder[] = " FU.SUBSC_GROUP_MESSAGE ".$order." ";
            elseif ($by == "SUBSC_GET_MY_MESSAGE") $arSqlOrder[] = " FU.SUBSC_GET_MY_MESSAGE ".$order." ";
            elseif ($by == "NUM_POSTS") $arSqlOrder[] = " FU.NUM_POSTS ".$order." ";
            elseif ($by == "LAST_POST") $arSqlOrder[] = " FU.LAST_POST ".$order." ";
            elseif ($by == "POINTS") $arSqlOrder[] = " FU.POINTS ".$order." ";
            elseif ($by == "NAME") $arSqlOrder[] = " U.NAME ".$order." ";
            elseif ($by == "LAST_NAME") $arSqlOrder[] = " U.LAST_NAME ".$order." ";
            elseif ($by == "EMAIL") $arSqlOrder[] = " U.EMAIL ".$order." ";
            elseif ($by == "LOGIN") $arSqlOrder[] = " U.LOGIN ".$order." ";
            elseif ($by == "LAST_VISIT") $arSqlOrder[] = " FU.LAST_VISIT ".$order." ";
            elseif ($by == "DATE_REGISTER") $arSqlOrder[] = " U.DATE_REGISTER ".$order." ";
            elseif ($by == "ID") $arSqlOrder[] = " FU.ID ".$order." ";
            elseif (($by == "SUBSC_COUNT") && array_key_exists("FS", $arSqlFrom)) $arSqlOrder[] = " SUBSC_COUNT ".$order." ";
            elseif (($by == "SUBSC_START_DATE") && array_key_exists("FS", $arSqlFrom)) $arSqlOrder[] = " FS.START_DATE ".$order." ";
            elseif (mb_substr($by, 0, mb_strlen("USER_")) == "USER_")
            {
                $strUserBy = mb_substr($by, mb_strlen("USER_"));
                if (array_key_exists($strUserBy, $arMainUserFields))
                {
                    $arSqlOrder[] = " U.".$strUserBy." ".$order." ";
                }
            }
            else
            {
                $arSqlOrder[] = " FU.ID ".$order." ";
                $by = "ID";
            }
        }
 
        DelDuplicateSort($arSqlOrder);
        if (count($arSqlOrder) > 0)
            $strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
 
            $strSql = "SELECT ".$strSqlSelect." 
                FROM b_forum_user FU
                    INNER JOIN b_user U ON (FU.USER_ID = U.ID) 
                    ".$strSqlFrom."
                WHERE 1 = 1 
                    ".$strSqlSearch."
                    ".$strSqlGroup."
                    ".$strSqlOrder;
        $db_res = $DB->Query($strSql);
        return $db_res;
    }
 
    public static function SearchUser($template, $arAddParams = array())
    {
        global $DB;
        $template = $DB->ForSql(str_replace("*", "%", $template));
        $arAddParams = (is_array($arAddParams) ? $arAddParams : array($arAddParams));
        $arAddParams["sNameTemplate"] = (is_set($arAddParams, "nameTemplate") ? $arAddParams["nameTemplate"] : $arAddParams["sNameTemplate"]);
 
        $strSqlSearch =
            "(
                F.SHOW_NAME = 'Y' AND LENGTH(U.NAME) > 0 AND U.NAME LIKE '".$template."'
            )
            OR
            (
                F.SHOW_NAME = 'Y' AND LENGTH(U.NAME) <= 0
                AND
                LENGTH(U.LAST_NAME) > 0 AND U.LAST_NAME LIKE '".$template."'
            )
            OR
            (
                (
                    F.SHOW_NAME = 'N' OR F.SHOW_NAME = '' OR (F.SHOW_NAME IS NULL)
                    OR
                    (
                        F.SHOW_NAME = 'Y'
                        AND
                        LENGTH(TRIM(CONCAT_WS('',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"]).")))<=0
                    )
                )
                AND
                U.LOGIN LIKE '".$template."'
            )";
        if (mb_substr($template, 0, 1) == '%')
            $strSqlSearch =
            "(
                (
                    F.SHOW_NAME = 'Y'
                    AND
                    LENGTH(TRIM(CONCAT_WS('',U.NAME,U.LAST_NAME))) > 0
                    AND
                    REPLACE(CONCAT_WS(' ',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"])."), '  ', ' ') LIKE '".$template."'
                )
                OR
                (
                    (
                        F.SHOW_NAME = 'N' OR F.SHOW_NAME = '' OR (F.SHOW_NAME IS NULL)
                        OR
                        (
                            F.SHOW_NAME = 'Y'
                            AND
                            LENGTH(TRIM(CONCAT_WS('',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"])."))) <= 0
                        )
                    )
                    AND
                    U.LOGIN LIKE '".$template."'
                )
            )";
 
        $iCnt = 0;
        if ((isset($arAddParams["bCount"]) && $arAddParams["bCount"]) || is_set($arAddParams, "bDescPageNumbering"))
        {
            $strSql = "SELECT COUNT(U.ID) AS CNT FROM b_user U LEFT JOIN b_forum_user F ON (F.USER_ID = U.ID) WHERE ".$strSqlSearch;
            $db_res = $DB->Query($strSql);
            $iCnt = ($db_res && ($res = $db_res->Fetch()) ? intval($res["CNT"]) : 0);
            if (isset($arAddParams["bCount"]) && $arAddParams["bCount"])
                return $iCnt;
        }
 
        $strSql =
            "SELECT U.ID, U.NAME, U.SECOND_NAME, U.LAST_NAME, U.LOGIN, F.SHOW_NAME,
                CASE
                    WHEN (F.SHOW_NAME = 'Y' AND LENGTH(TRIM(CONCAT_WS('',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"])."))) > 0)
                    THEN TRIM(REPLACE(CONCAT_WS(' ',".self::GetNameFieldsForQuery($arAddParams["sNameTemplate"])."), '  ', ' '))
                    ELSE U.LOGIN
                END AS SHOW_ABC
            FROM b_user U
                LEFT JOIN b_forum_user F ON (F.USER_ID = U.ID)
            WHERE ".$strSqlSearch."\n"."ORDER BY SHOW_ABC";
        if (is_set($arAddParams, "bDescPageNumbering")) {
            $db_res =  new CDBResult();
            $db_res->NavQuery($strSql, $iCnt, $arAddParams);
        } else {
            if ($arAddParams["nTopCount"] > 0)
                $strSql .= " LIMIT 0,".$arAddParams["nTopCount"];
            $db_res = $DB->Query($strSql);
        }
 
        return $db_res;
    }

    public static function GetNameFieldsForQuery($sNameTemplate, $userTablePrefix = "U.")
    {
        global $DB;
        $sqlHelper = \Bitrix\Main\Application::getConnection()->getSqlHelper();
        $sNameTemplate = (empty($sNameTemplate) ? CSite::GetDefaultNameFormat() : $sNameTemplate);
        if (!preg_match("/(#NAME#)|(#LAST_NAME#\,)|(#LAST_NAME#)|(#SECOND_NAME#)|(#NAME_SHORT#)|(#SECOND_NAME_SHORT#)/u", $sNameTemplate, $matches))
            $sNameTemplate = CSite::GetDefaultNameFormat();
        if (mb_strpos($sNameTemplate, "#NOBR#") !== false)
            $sNameTemplate = preg_replace("/\#NOBR\#(.+?)\#\/NOBR\#/u", "\\1", $sNameTemplate);
 
        preg_match_all("/(#NAME#)|(#LAST_NAME#\,)|(#LAST_NAME#)|(#SECOND_NAME#)|(#NAME_SHORT#)|(#SECOND_NAME_SHORT#)/u", $sNameTemplate, $matches);
 
        $tmp = array();
        foreach($matches[0] as $val) {
            $pos = mb_strpos($sNameTemplate, $val);
            if ($pos > 0) {
                $tmp[] = "'".$DB->ForSql(mb_substr($sNameTemplate, 0, $pos))."'";
            }
            $tmp[] = str_replace(
                array(
                    "#NAME#",
                    "#LAST_NAME#,",
                    "#LAST_NAME#",
                    "#SECOND_NAME#",
                    "#NAME_SHORT#",
                    "#SECOND_NAME_SHORT#"
                ),
                array(
                    $userTablePrefix."NAME",
                    "case when LENGTH(TRIM(".$userTablePrefix."LAST_NAME)) <= 0 then '' else " . $sqlHelper->getConcatFunction($userTablePrefix.'LAST_NAME', "','") . " END",
                    $userTablePrefix."LAST_NAME",
                    $userTablePrefix."SECOND_NAME",
                    "case when LENGTH(TRIM(".$userTablePrefix."NAME)) <= 0 then '' else " . $sqlHelper->getConcatFunction("SUBSTRING(".$userTablePrefix."NAME,1,1)", "'.'") . " END",
                    "case when LENGTH(TRIM(".$userTablePrefix."SECOND_NAME)) <= 0 then '' else " . $sqlHelper->getConcatFunction("SUBSTRING(".$userTablePrefix."SECOND_NAME,1,1)", "'.'") . " END"
                ),
                $val
            );
            $sNameTemplate = mb_substr($sNameTemplate, ($pos + mb_strlen($val)));
        }
        if (!empty($sNameTemplate))
            $tmp[] = "'".$DB->ForSql($sNameTemplate)."'";
        $res = implode(",", $tmp);
        return (!empty($res) ? $res : "''");
    }
 
    public static function GetFormattedNameFieldsForSelect($arParams = array(), $bReturnAll = true)
    {
        $arParams = (is_array($arParams) ? $arParams : array($arParams));
        $arParams["sNameTemplate"] = trim($arParams["sNameTemplate"]);
        $arParams["sUserTablePrefix"] = rtrim((!empty($arParams["sUserTablePrefix"]) ? $arParams["sUserTablePrefix"] : "U"), ".").".";
        $arParams["sForumUserTablePrefix"] = rtrim((!empty($arParams["sForumUserTablePrefix"]) ? $arParams["sForumUserTablePrefix"] : "FU"), ".").".";
        $arParams["sFieldName"] = (!empty($arParams["sFieldName"]) ? $arParams["sFieldName"] : "AUTHOR_NAME_FRMT");
        $arParams["sUserIDFieldName"] = (!empty($arParams["sUserIDFieldName"]) ? $arParams["sUserIDFieldName"] : "F.LAST_POSTER_ID");
        $res = array(
            "select" =>
                "CASE ".
                    " WHEN (".
                        $arParams["sForumUserTablePrefix"]."USER_ID > 0 ".
                        " AND ".
                        $arParams["sForumUserTablePrefix"]."SHOW_NAME = 'Y' ".
                        " AND ".
                        "LENGTH(TRIM(CONCAT_WS('',".
                            CForumUser::GetNameFieldsForQuery(
                                $arParams["sNameTemplate"],
                                $arParams["sUserTablePrefix"])."))) > 0".
                    ") ".
                    " THEN TRIM(REPLACE(CONCAT_WS(' ',".
                        CForumUser::GetNameFieldsForQuery(
                            $arParams["sNameTemplate"],
                            $arParams["sUserTablePrefix"])."), '  ', ' '))".
                    " ELSE ".$arParams["sUserTablePrefix"]."LOGIN ".
                " END AS ".$arParams["sFieldName"],
            "join" =>
                "LEFT JOIN b_forum_user ".rtrim($arParams["sForumUserTablePrefix"], ".").
                    " ON (".$arParams["sUserIDFieldName"]."=".$arParams["sForumUserTablePrefix"]."USER_ID) ".
                "LEFT JOIN b_user ".rtrim($arParams["sUserTablePrefix"], ".").
                    " ON (".$arParams["sUserIDFieldName"]."=".$arParams["sUserTablePrefix"]."ID) "
        );
        if ($bReturnAll)
            return $res;
        return $res["select"];
    }
}
 
class CForumSubscribe extends CAllForumSubscribe
{
}
 
class CForumRank extends CAllForumRank
{
    // Tekuwie statusy posetitelej srazu ne pereschityvayutsya. Tol'ko postepenno v processe raboty.
    public static function Add($arFields)
    {
        global $DB;
 
        if (!CForumRank::CheckFields("ADD", $arFields))
            return false;
 
        $arInsert = $DB->PrepareInsert("b_forum_rank", $arFields);
        $strSql = "INSERT INTO b_forum_rank(".$arInsert[0].") VALUES(".$arInsert[1].")";
        $DB->Query($strSql);
        $ID = intval($DB->LastID());
        foreach ($arFields["LANG"] as $i => $val)
        {
            $arInsert = $DB->PrepareInsert("b_forum_rank_lang", $arFields["LANG"][$i]);
            $strSql = "INSERT INTO b_forum_rank_lang(RANK_ID, ".$arInsert[0].") VALUES(".$ID.", ".$arInsert[1].")";
            $DB->Query($strSql);
        }
        return $ID;
    }
}
 
class CForumStat extends CALLForumStat
{
    public static function GetListEx($arOrder = Array("ID"=>"ASC"), $arFilter = Array(), $arAddParams = array())
    {
        global $DB;
        $sqlHelper = \Bitrix\Main\Application::getConnection()->getSqlHelper();
        $arSqlSearch = array();
        $arSqlFrom = array();
        $arSqlOrder = array();
        $strSqlSearch = "";
        $strSqlFrom = "";
        $strSqlOrder = "";
        $arFilter = (is_array($arFilter) ? $arFilter : array());
 
        foreach ($arFilter as $key => $val)
        {
            $key_res = CForumNew::GetFilterOperation($key);
            $key = mb_strtoupper($key_res["FIELD"]);
            $strNegative = $key_res["NEGATIVE"];
            $strOperation = $key_res["OPERATION"];
            switch ($key)
            {
                case "TOPIC_ID":
                case "FORUM_ID":
                    if (intval($val)<=0)
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FSTAT.".$key." IS NULL OR FSTAT.".$key."<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FSTAT.".$key." IS NULL OR NOT ":"")."(FSTAT.".$key." ".$strOperation." ".intval($val).")";
                    break;
                case "SITE_ID":
                    $bOrNull = false;
                    if (is_array($val)):
                        $res = array();
                        foreach ($val as $v):
                            $v = trim($v);
                            if ($v == "NULL")
                                $bOrNull = true;
                            elseif (!empty($v))
                                $res[] = "'".$DB->ForSql($v)."'";
                        endforeach;
                        $val = (!empty($res) ? implode(", ", $res) : "");
                        $strOperation = (!empty($res) ? "IN" : $strOperation);
                    else:
                        $val = "'".$DB->ForSql($val)."'";
                    endif;
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FSTAT.".$key." IS NULL OR LENGTH(FSTAT.".$key.")<=0)";
                    elseif ($strOperation == "IN")
                        $arSqlSearch[] = ($strNegative=="Y"?" FSTAT.".$key." IS NULL OR NOT ":"")."(FSTAT.".$key." IN (".$val.")".(
                            $bOrNull ? " OR (FSTAT.".$key." IS NULL OR LENGTH(FSTAT.".$key.")<=0)" : "").")";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FSTAT.".$key." IS NULL OR NOT ":"")."(FSTAT.".$key." ".$strOperation." ".$val.")";
                    break;
                case "LAST_VISIT":
                    if($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FSTAT.".$key." IS NULL)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FSTAT.".$key." IS NULL OR NOT ":"")."(FSTAT.".$key." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
                    break;
                case "PERIOD":
                    if($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FSTAT.LAST_VISIT IS NULL)";
                    else
                    {
                        $arSqlSearch[] = ($strNegative == "Y" ? " FSTAT.LAST_VISIT IS NULL OR NOT " : "") .
                            '(' . $sqlHelper->addSecondsToDateTime(-intval($val)) . ' ' . $strOperation . "  FSTAT.LAST_VISIT)";
                    }
                    break;
                case "HIDE_FROM_ONLINE":
                    $arSqlFrom["FU"] = "LEFT JOIN b_forum_user FU ON (FSTAT.USER_ID=FU.USER_ID)";
                    if ($val == '')
                        $arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FU.".$key." IS NULL OR LENGTH(FU.".$key.")<=0)";
                    else
                        $arSqlSearch[] = ($strNegative=="Y"?" FU.".$key." IS NULL OR NOT ":"")."(((FU.".$key." ".$strOperation." '".$DB->ForSql($val)."' ) AND (FSTAT.USER_ID > 0)) OR (FSTAT.USER_ID <= 0))";
                    break;
                case "ACTIVE":
                        $arSqlFrom["U"] = "LEFT JOIN b_user U ON (FSTAT.USER_ID=U.ID)";
                        $arSqlSearch[] = ($strNegative=="Y"?" U.".$key." IS NULL OR NOT ":"")."(FSTAT.USER_ID = 0 OR U.ACTIVE = 'Y')";
                    break;
            }
        }
        if (!empty($arSqlSearch))
            $strSqlSearch = " AND ".implode(" AND ", $arSqlSearch)." ";
 
        if (!empty($arSqlFrom))
            $strSqlFrom = implode("\n", $arSqlFrom);
 
        foreach ($arOrder as $by=>$order)
        {
            $by = mb_strtoupper($by);
            $order = mb_strtoupper($order);
            $order = $order!="ASC" ? $order = "DESC" : "ASC";
 
            if ($by == "USER_ID") $arSqlOrder[] = " FSTAT.USER_ID ".$order." ";
        }
 
        DelDuplicateSort($arSqlOrder);
        if (count($arSqlOrder) > 0)
            $strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
 
        $strSql =
            "SELECT FSTAT.USER_ID, FSTAT.IP_ADDRESS, FSTAT.PHPSESSID, \n".
            "   ".$DB->DateToCharFunction("FSTAT.LAST_VISIT", "FULL")." AS LAST_VISIT, \n".
            "   FSTAT.FORUM_ID, FSTAT.TOPIC_ID \n".
            "FROM b_forum_stat FSTAT ".$strSqlFrom. "\n".
            "WHERE 1=1 ".$strSqlSearch."\n".
            $strSqlOrder;
 
        if (is_set($arFilter, "COUNT_GUEST"))
        {
            $strSql =
                "SELECT FST.*, FU.*, FSTAT.IP_ADDRESS, FSTAT.PHPSESSID, \n".
                "   ".$DB->DateToCharFunction("FSTAT.LAST_VISIT", "FULL")." AS LAST_VISIT, \n".
                "   FSTAT.FORUM_ID, FSTAT.TOPIC_ID, \n".
                "   U.LOGIN, U.NAME, U.SECOND_NAME, U.LAST_NAME, \n".
                "   ".
                (!empty($arAddParams["sNameTemplate"]) ?
                    CForumUser::GetFormattedNameFieldsForSelect(
                        array_merge(
                            $arAddParams,
                            array(
                                "sUserTablePrefix" => "U.",
                                "sForumUserTablePrefix" => "FU.",
                                "sFieldName" => "SHOW_NAME")
                        ),
                        false
                    ) :
                    "FSTAT.SHOW_NAME"
                )."\n ".
            " FROM ( ".
                " SELECT FSTAT.USER_ID, MAX(FSTAT.ID) FST_ID, COUNT(FSTAT.PHPSESSID) COUNT_USER ".
                " FROM b_forum_stat FSTAT ".
                $strSqlFrom.
                " WHERE 1=1 ".$strSqlSearch.
                " GROUP BY FSTAT.USER_ID".
            ") FST ".
            "LEFT JOIN b_forum_stat FSTAT ON (FST.FST_ID = FSTAT.ID) ".
            "LEFT JOIN b_forum_user FU ON (FST.USER_ID = FU.USER_ID) ".
            "LEFT JOIN b_user U ON (FST.USER_ID = U.ID) ".
            $strSqlOrder;
        }
        $db_res = $DB->Query($strSql);
        return $db_res;
    }
}