/modules/forum/classes/mysql/user.php
PHP | 854 lines | 807 code | 40 blank | 7 comment | 145 complexity | 26db845cbc86eb6773f39a30e54a355a MD5 | raw file
- <?
- 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;
- $arSqlSearch = array();
- $arSqlOrder = array();
- $strSqlSearch = "";
- $strSqlOrder = "";
- $arFilter = (is_array($arFilter) ? $arFilter : array());
- $arAddParams = (is_array($arAddParams) ? $arAddParams : array($arAddParams));
- if (is_set($arAddParams, "nameTemplate"))
- $arAddParams["sNameTemplate"] = $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 ")."(DATE_FORMAT(U.PERSONAL_BIRTHDAY, '%m-%d') ".$strOperation." '".$DB->ForSql($val)."')";
- $db_sub_res = $DB->Query($subQuery, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- $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 ":"")."(DATE_FORMAT(U.PERSONAL_BIRTHDAY, '%m-%d') ".$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 (is_array($arAddParams) && (intval($arAddParams["nTopCount"])>0))
- $strSql .= " LIMIT 0,".intval($arAddParams["nTopCount"]);
- if (is_array($arAddParams) && is_set($arAddParams, "bDescPageNumbering") && (intval($arAddParams["nTopCount"])<=0))
- {
- $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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- 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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- }
- return $db_res;
- }
- public static function GetListEx($arOrder = Array("ID"=>"ASC"), $arFilter = Array())
- {
- global $DB;
- $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 ":"")."(DATE_FORMAT(U.PERSONAL_BIRTHDAY, '%m-%d') ".$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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- 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 ($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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- $iCnt = ($db_res && ($res = $db_res->Fetch()) ? intval($res["CNT"]) : 0);
- if ($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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- }
- return $db_res;
- }
- /**
- * Converts name template fields from Bitrix name template to SQL query fields
- *
- * @param string $sNameTemplate Bitrix name template (ex: #LAST_NAME# #NAME#). Uses site name template if empty @see CSite::GetNameTemplates
- * @return string (ex: U.LAST_NAME, U.NAME)
- */
- public static function GetNameFieldsForQuery($sNameTemplate, $userTablePrefix = "U.")
- {
- global $DB;
- $sNameTemplate = (empty($sNameTemplate) ? CSite::GetDefaultNameFormat() : $sNameTemplate);
- if (!preg_match("/(#NAME#)|(#LAST_NAME#\,)|(#LAST_NAME#)|(#SECOND_NAME#)|(#NAME_SHORT#)|(#SECOND_NAME_SHORT#)/".BX_UTF_PCRE_MODIFIER, $sNameTemplate, $matches))
- $sNameTemplate = CSite::GetDefaultNameFormat();
- if (mb_strpos($sNameTemplate, "#NOBR#") !== false)
- $sNameTemplate = preg_replace("/\#NOBR\#(.+?)\#\/NOBR\#/".BX_UTF_PCRE_MODIFIER, "\\1", $sNameTemplate);
- preg_match_all("/(#NAME#)|(#LAST_NAME#\,)|(#LAST_NAME#)|(#SECOND_NAME#)|(#NAME_SHORT#)|(#SECOND_NAME_SHORT#)/".BX_UTF_PCRE_MODIFIER, $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",
- "IF (LENGTH(TRIM(".$userTablePrefix."LAST_NAME)) <= 0, '', CONCAT(".$userTablePrefix."LAST_NAME, ','))",
- $userTablePrefix."LAST_NAME",
- $userTablePrefix."SECOND_NAME",
- "IF (LENGTH(TRIM(".$userTablePrefix."NAME)) <= 0,'',CONCAT(SUBSTRING(".$userTablePrefix."NAME,1,1),'.'))",
- "IF (LENGTH(TRIM(".$userTablePrefix."SECOND_NAME)) <= 0,'',CONCAT(SUBSTRING(".$userTablePrefix."SECOND_NAME,1,1),'.'))"
- ),
- $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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- $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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- }
- return $ID;
- }
- }
- class CForumStat extends CALLForumStat
- {
- public static function GetListEx($arOrder = Array("ID"=>"ASC"), $arFilter = Array(), $arAddParams = array())
- {
- global $DB;
- $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 ":"").
- "(FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - ".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;
- 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, false, "File: ".__FILE__."<br>Line: ".__LINE__);
- return $db_res;
- }
- }