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 : [];
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 ")."(DATE_FORMAT(U.PERSONAL_BIRTHDAY, '%m-%d') ".$strOperation." '".$DB->ForSql($val)."')";
$db_sub_res = $DB->Query($subQuery, false, "File: ".__FILE__."
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 (!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, false, "File: ".__FILE__."
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__."
Line: ".__LINE__);
}
return $db_res;
}