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 = 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) . "'";
}
}
$val = !empty($res) ? implode(", ", $res) : "";
$strOperation = !empty($res) ? "IN" : $strOperation;
} else {
$val = "'" . $DB->ForSql($val) . "'";
}
if (strlen($val) <= 0) {
$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 (strLen($val) <= 0) {
$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 (strLen($val) <= 0) {
$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 (strLen($val) <= 0) {
$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 = strtoupper($by);
$order = 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" . "\t" . $DB->DateToCharFunction("FSTAT.LAST_VISIT", "FULL") . " AS LAST_VISIT, \n" . "\tFSTAT.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" . "\t" . $DB->DateToCharFunction("FSTAT.LAST_VISIT", "FULL") . " AS LAST_VISIT, \n" . "\tFSTAT.FORUM_ID, FSTAT.TOPIC_ID, \n" . "\tU.LOGIN, U.NAME, U.SECOND_NAME, U.LAST_NAME, \n" . "\t" . (!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;
}
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 = "";
$arSqlSelectConst = array("FSTAT.USER_ID" => "FSTAT.USER_ID", "FSTAT.IPADDRES" => "FSTAT.IPADDRES", "FSTAT.PHPSESSID" => "FSTAT.PHPSESSID", "LAST_VISIT" => $DB->DateToCharFunction("FSTAT.LAST_VISIT", "FULL"), "FSTAT.FORUM_ID" => "FSTAT.FORUM_ID", "FSTAT.TOPIC_ID" => "FSTAT.TOPIC_ID");
$arSqlSelect = $arSqlSelectConst;
$arFilter = is_array($arFilter) ? $arFilter : array();
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 "TOPIC_ID":
case "FORUM_ID":
case "USER_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 "LAST_VISIT":
if (strLen($val) <= 0) {
$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 "HIDE_FROM_ONLINE":
$arSqlFrom["FU"] = "LEFT JOIN b_forum_user FU ON FSTAT.USER_ID=FU.USER_ID";
if (strLen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(FU." . $key . " IS NULL OR " . ($DB->type == "MSSQL" ? "LEN" : "LENGTH") . "(FU." . $key . ")<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " FU." . $key . " IS NULL OR NOT " : "") . "(FU." . $key . " " . $strOperation . " '" . $DB->ForSql($val) . "' )";
}
break;
break;
case "COUNT_GUEST":
$arSqlSelect = array("FSTAT.USER_ID" => "FSTAT.USER_ID", "FSTAT.SHOW_NAME" => "FSTAT.SHOW_NAME", "COUNT_USER" => "COUNT(FSTAT.PHPSESSID) AS COUNT_USER");
$arSqlGroup["FSTAT.USER_ID"] = "FSTAT.USER_ID";
$arSqlGroup["FSTAT.SHOW_NAME"] = "FSTAT.SHOW_NAME";
break;
}
}
if (count($arSqlSearch) > 0) {
$strSqlSearch = " AND (" . implode(") AND (", $arSqlSearch) . ") ";
}
if (count($arSqlSelect) > 0) {
$strSqlSelect = implode(", ", $arSqlSelect);
}
if (count($arSqlFrom) > 0) {
$strSqlFrom = implode("\t", $arSqlFrom);
}
if (count($arSqlGroup) > 0) {
$strSqlGroup = " GROUP BY " . implode(", ", $arSqlGroup);
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = 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 " . $strSqlSelect . "\n\t\t\tFROM b_forum_stat FSTAT\n\t\t\t" . $strSqlFrom . "\n\t\t\tWHERE 1=1\n\t\t\t" . $strSqlSearch . "\n\t\t\t" . $strSqlGroup . "\n\t\t\t" . $strSqlOrder;
$db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
return $db_res;
}
function GetList($arOrder = Array("ID"=>"ASC"), $arFilter = Array(), $iNum = 0, $arAddParams = array())
{
global $DB;
$arSqlSearch = array();
$arSqlOrder = array();
$strSqlSearch = "";
$strSqlOrder = "";
$iCnt = 0;
$iNum = intVal($iNum);
$arFilter = (is_array($arFilter) ? $arFilter : array());
$arAddParams = (is_array($arAddParams) ? $arAddParams : array());
if (intVal($arAddParams["nTopCount"]) > 0)
unset($arAddParams["bDescPageNumbering"]);
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":
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;
// to table b_forum_message
case "APPROVED_AND_MINE":
if ($val >= 0)
$arSqlSearch[] = "(FM.APPROVED='Y' OR (FM.APPROVED != 'Y' AND FM.AUTHOR_ID=".intval($val)."))";
else
$arSqlSearch[] = "(FM.APPROVED='Y')";
break;
case "PARAM2":
case "FORUM_ID":
case "TOPIC_ID":
case "AUTHOR_ID":
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) && array_sum($val) > 0 || is_string($val) && strlen($val) > 0))
{
if (is_array($val))
{
$val_int = array();
foreach ($val as $v)
$val_int[] = intVal($v);
$val = implode(", ", $val_int);
}
$arSqlSearch[] = ($strNegative=="Y"?" NOT ":"")."(FM.".$key." IN (".$DB->ForSql($val).") )";
}
else
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FM.".$key." IS NULL OR FM.".$key."<=0)";
break;
// to table b_forum_file
case "FILE_FORUM_ID":
case "FILE_TOPIC_ID":
case "FILE_MESSAGE_ID":
$key = substr($key, 5);
if ($strOperation != "IN" && intVal($val) > 0)
{
$res = ($strNegative=="Y"?" FF.".$key." IS NULL OR NOT ":"")."(FF.".$key." ".$strOperation." ".intVal($val)." ) OR ".
"".($strNegative=="Y"?"NOT":"")."(FF.".$key." IS NULL OR FF.".$key."<=0)";
$arSqlSearch[] = $res;
break;
}
elseif ($strOperation == "IN" && (is_array($val) && array_sum($val) > 0 || is_string($val) && strlen($val) > 0))
{
$val = (!is_array($val) ? explode(",", $val) : $val);
$val_int = array();
foreach ($val as $k => $v):
$val_int[] = intVal($v);
endforeach;
$val = implode(",", $val_int);
if (strLen($val) > 0)
{
$arSqlSearch[] = ($strNegative=="Y"?" NOT ":"")."(FF.".$key." IN (".$DB->ForSql($val).") )";
break;
}
}
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FF.".$key." IS NULL OR FF.".$key."<=0)";
break;
case "FILE_ID":
case "MESSAGE_ID":
case "USER_ID":
if (($strOperation!="IN") && (intVal($val) > 0 || $val === 0))
{
$arSqlSearch[] = ($strNegative=="Y"?" FF.".$key." IS NULL OR NOT ":"")."(FF.".$key." ".$strOperation." ".intVal($val)." )";
break;
}
elseif ($strOperation =="IN" && (is_array($val) && array_sum($val) > 0 || strlen($val) > 0))
{
$val = (!is_array($val) ? explode(",", $val) : $val);
$val_int = array();
foreach ($val as $k => $v):
$val_int[] = intVal($v);
endforeach;
$val = implode(",", $val_int);
if (strLen($val) > 0)
{
$arSqlSearch[] = ($strNegative=="Y"?" NOT ":"")."(FF.".$key." IN (".$DB->ForSql($val).") )";
break;
}
}
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FF.".$key." IS NULL OR FF.".$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;
}
}
if (count($arSqlSearch) > 0)
$strSqlSearch = " AND (".implode(") AND (", $arSqlSearch).") ";
foreach ($arOrder as $by=>$order)
{
$by = strtoupper($by); $order = strtoupper($order);
if ($order!="ASC") $order = "DESC";
if ($by == "FILE_ID") $arSqlOrder[] = " FF.FILE_ID ".$order." ";
elseif ($by == "FORUM_ID") $arSqlOrder[] = " FF.FORUM_ID ".$order." ";
elseif ($by == "TOPIC_ID") $arSqlOrder[] = " FF.TOPIC_ID ".$order." ";
elseif ($by == "MESSAGE_ID") $arSqlOrder[] = " FF.MESSAGE_ID ".$order." ";
else
{
$arSqlOrder[] = " FF.FILE_ID ".$order." ";
$by = "FILE_ID";
}
}
DelDuplicateSort($arSqlOrder);
if(count($arSqlOrder) > 0)
$strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
$strSql =
"SELECT BF.ID, BF.HEIGHT, BF.WIDTH, BF.FILE_SIZE, BF.CONTENT_TYPE, BF.SUBDIR, BF.FILE_NAME,
BF.ORIGINAL_NAME, FF.FILE_ID, FF.FORUM_ID, FF.TOPIC_ID, FF.MESSAGE_ID, FF.USER_ID, FF.HITS,
".$DB->DateToCharFunction("FF.TIMESTAMP_X", "FULL")." as TIMESTAMP_X, BF.HANDLER_ID
FROM b_forum_file FF
INNER JOIN b_file BF ON (BF.ID = FF.FILE_ID)
LEFT JOIN b_forum_message FM ON (FM.ID=FF.MESSAGE_ID)
WHERE 1 = 1
".$strSqlSearch."
".$strSqlOrder;
if ($iNum > 0 || intVal($arAddParams["nTopCount"]) > 0)
{
$iNum = ($iNum > 0) ? $iNum : intVal($arAddParams["nTopCount"]);
$strSql = "SELECT * FROM(".$strSql.") WHERE ROWNUM<=".$iNum;
}
elseif (is_set($arAddParams, "bDescPageNumbering"))
{
$iCnt = 0;
$strSql1 = "SELECT COUNT(FM.ID) as CNT FROM b_forum_message FM WHERE 1 = 1 ".$strSqlSearch;
$db_res = $DB->Query($strSql1, false, "File: ".__FILE__."<br>Line: ".__LINE__);
if ($ar_res = $db_res->Fetch())
$iCnt = intVal($ar_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;
}
function GetListEx($arOrder = array("SORT" => "ASC"), $arFilter = array(), $bCount = false, $iNum = 0, $arAddParams = array())
{
global $DB, $USER;
$arOrder = is_array($arOrder) ? $arOrder : array();
$arFilter = is_array($arFilter) ? $arFilter : array();
$arSqlSearch = array();
$arSqlFrom = array();
$arSqlSelect = array();
$arSqlGroup = array();
$arSqlOrder = array();
$strSqlSearch = "";
$strSqlFrom = "";
$strSqlSelect = "";
$strSqlGroup = "";
$strSqlOrder = "";
$UseGroup = false;
$arAddParams = is_array($arAddParams) ? $arAddParams : array($arAddParams);
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 "STATE":
case "XML_ID":
case "APPROVED":
if (strlen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(FT." . $key . " IS NULL OR " . ($DB->type == "MSSQL" ? "LEN" : "LENGTH") . "(FT." . $key . ")<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " FT." . $key . " IS NULL OR NOT " : "") . "(FT." . $key . " " . $strOperation . " '" . $DB->ForSql($val) . "' )";
}
break;
case "ID":
case "FORUM_ID":
case "SOCNET_GROUP_ID":
case "OWNER_ID":
case "USER_START_ID":
case "SORT":
case "POSTS":
case "TOPICS":
if ($strOperation != "IN" && intVal($val) > 0) {
$arSqlSearch[] = ($strNegative == "Y" ? " FT." . $key . " IS NULL OR NOT " : "") . "(FT." . $key . " " . $strOperation . " " . intVal($val) . " )";
} elseif ($strOperation == "IN" && (is_array($val) && array_sum($val) > 0 || strlen($val) > 0)) {
if (!is_array($val)) {
$val = explode(',', $val);
}
$val_int = array();
foreach ($val as $v) {
$val_int[] = intVal($v);
}
$val = implode(", ", $val_int);
$arSqlSearch[] = ($strNegative == "Y" ? " NOT " : "") . "(FT." . $key . " IN (" . $DB->ForSql($val) . ") )";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(FT." . $key . " IS NULL OR FT." . $key . "<=0)";
}
break;
case "TITLE_ALL":
$arSqlSearch[] = GetFilterQuery("FT.TITLE, FT.DESCRIPTION", $val);
break;
case "TITLE":
case "DESCRIPTION":
$arSqlSearch[] = GetFilterQuery("FT." . $key, $val);
$arSqlSearch[] = GetFilterQuery("FT." . $key, $val);
break;
case "START_DATE":
case "LAST_POST_DATE":
case "ABS_LAST_POST_DATE":
if (strlen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(FT." . $key . " IS NULL)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " FT." . $key . " IS NULL OR NOT " : "") . "(FT." . $key . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ")";
}
break;
case "USER_ID":
$arSqlSelect["LAST_VISIT"] = $DB->DateToCharFunction("FUT.LAST_VISIT", "FULL");
$arSqlFrom["FUT"] = "LEFT JOIN b_forum_user_topic FUT ON (" . (strlen($val) <= 0 ? ($strNegative == "Y" ? "NOT" : "") . "(FUT.USER_ID IS NULL)" : "FUT.USER_ID=" . intVal($val)) . " AND FUT.FORUM_ID = FT.FORUM_ID AND FUT.TOPIC_ID = FT.ID)";
break;
case "RENEW_TOPIC":
if (strlen($val) > 0 && array_key_exists("FUT", $arSqlFrom)) {
$arSqlSearch[] = "((FT.LAST_POST_DATE " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ") AND\n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t(LAST_VISIT IS NULL) OR\n\t\t\t\t\t\t\t\t\t\t(LAST_VISIT < " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ")\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t((FT.LAST_POST_DATE > FUT.LAST_VISIT) AND \n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t(LAST_VISIT IS NOT NULL) AND\n\t\t\t\t\t\t\t\t\t\t(LAST_VISIT > " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ")\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t)";
}
break;
case "PERMISSION":
if (!is_array($val)) {
$val = explode(',', $val);
}
if (empty($val)) {
$val = $GLOBALS["USER"]->GetGroups();
} elseif (is_array($val)) {
$val_int = array();
foreach ($val as $v) {
$val_int[] = intVal($v);
}
$val = implode(", ", $val_int);
}
$arSqlFrom["FPP"] = " INNER JOIN ( \n" . "\tSELECT FPP.FORUM_ID, MAX(FPP.PERMISSION) AS PERMISSION \n" . "\tFROM b_forum_perms FPP \n" . "\tWHERE FPP.GROUP_ID IN (" . $DB->ForSql($val) . ") AND FPP.PERMISSION > 'A' \n" . "\tGROUP BY FPP.FORUM_ID) FPP ON (FPP.FORUM_ID = FT.FORUM_ID) ";
$arSqlSelect[] = "FPP.PERMISSION AS PERMISSION";
break;
case "RENEW":
$val = is_array($val) ? $val : array("USER_ID" => $val);
$val["USER_ID"] = intVal($val["USER_ID"]);
if ($val["USER_ID"] <= 0) {
continue;
}
$perms = "NOT_CHECK";
$arUserGroups = $GLOBALS["USER"]->GetGroups();
if (is_set($arFilter, "PERMISSION")) {
$perms = "NORMAL";
} elseif (is_set($arFilter, "APPROVED") && $arFilter["APPROVED"] == "Y") {
$perms = "ONLY_APPROVED";
}
$arSqlFrom["FUT"] = "LEFT JOIN b_forum_user_topic FUT ON (FUT.USER_ID=" . intVal($val["USER_ID"]) . " AND FUT.FORUM_ID = FT.FORUM_ID AND FUT.TOPIC_ID = FT.ID)";
$arSqlFrom["FUF"] = "LEFT JOIN b_forum_user_forum FUF ON (FUF.USER_ID=" . $val["USER_ID"] . " AND FUF.FORUM_ID = FT.FORUM_ID)";
$arSqlFrom["FUF_ALL"] = "LEFT JOIN b_forum_user_forum FUF_ALL ON (FUF_ALL.USER_ID=" . $val["USER_ID"] . " AND FUF_ALL.FORUM_ID = 0)";
$arSqlSearch[] = "FT.STATE != 'L'";
$arSqlSearch[] = "\n\t\t\t\t\t(\n\t\t\t\t\t\tFUT.LAST_VISIT IS NULL \n\t\t\t\t\t\tAND \n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(FUF_ALL.LAST_VISIT IS NULL AND FUF.LAST_VISIT IS NULL)\n\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tFUF.LAST_VISIT IS NOT NULL\n\t\t\t\t\t\t\t\tAND \n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t" . ($perms == "NORMAL" ? "\n\t\t\t\t\t\t\t\t\t(FPP.PERMISSION >= 'Q' AND FUF.LAST_VISIT < FT.ABS_LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t\t\t(FT.APPROVED = 'Y' AND FUF.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t" : ($perms == "NOT_CHECK" ? "\n\t\t\t\t\t\t\t\t\t(FUF.LAST_VISIT < FT.ABS_LAST_POST_DATE OR FUF.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t" : "\n\t\t\t\t\t\t\t\t\t(FT.APPROVED = 'Y' AND FUF.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t")) . "\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tFUF.LAST_VISIT IS NULL AND FUF_ALL.LAST_VISIT IS NOT NULL \n\t\t\t\t\t\t\t\tAND \n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t" . ($perms == "NORMAL" ? "\n\t\t\t\t\t\t\t\t\t(FPP.PERMISSION >= 'Q' AND FUF_ALL.LAST_VISIT < FT.ABS_LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t\t\t(FT.APPROVED = 'Y' AND FUF_ALL.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t" : ($perms == "NOT_CHECK" ? "\n\t\t\t\t\t\t\t\t\t(FUF_ALL.LAST_VISIT < FT.ABS_LAST_POST_DATE OR FUF_ALL.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t" : "\n\t\t\t\t\t\t\t\t\t(FT.APPROVED = 'Y' AND FUF_ALL.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t")) . "\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\t\t\t\t\tOR\n\t\t\t\t\t(\n\t\t\t\t\t\tFUT.LAST_VISIT IS NOT NULL \n\t\t\t\t\t\tAND \n\t\t\t\t\t\t(\n\t\t\t\t\t" . ($perms == "NORMAL" ? "\n\t\t\t\t\t\t\t\t\t(FPP.PERMISSION >= 'Q' AND FUT.LAST_VISIT < FT.ABS_LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t\t\t(FT.APPROVED = 'Y' AND FUT.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t" : ($perms == "NOT_CHECK" ? "\n\t\t\t\t\t\t\t\t\t(FUT.LAST_VISIT < FT.ABS_LAST_POST_DATE OR FUT.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t" : "\n\t\t\t\t\t\t\t\t\t(FT.APPROVED = 'Y' AND FUT.LAST_VISIT < FT.LAST_POST_DATE)\n\t\t\t\t\t\t\t\t\t")) . "\t)\n\t\t\t\t\t)";
break;
case "PERMISSION_STRONG":
$arSqlFrom["FP"] = "LEFT JOIN b_forum_perms FP ON (FP.FORUM_ID=FT.FORUM_ID)";
$arSqlSearch[] = "FP.GROUP_ID IN (" . $DB->ForSql($USER->GetGroups()) . ") AND (FP.PERMISSION IN ('Q','U','Y'))";
$UseGroup = true;
break;
}
}
if (count($arSqlSearch) > 0) {
$strSqlSearch = " AND (" . implode(") AND (", $arSqlSearch) . ")";
}
if (count($arSqlSelect) > 0) {
$res = array();
foreach ($arSqlSelect as $key => $val) {
if (substr($key, 0, 1) == "!") {
$key = substr($key, 1);
}
if ($key != $val) {
$res[] = $val . " AS " . $key;
} else {
$res[] = $val;
}
}
$strSqlSelect = ", " . implode(", ", $res);
}
if (count($arSqlFrom) > 0) {
$strSqlFrom = implode("\n", $arSqlFrom);
}
if ($UseGroup) {
foreach ($arSqlSelect as $key => $val) {
if (substr($key, 0, 1) != "!") {
$arSqlGroup[$key] = $val;
}
}
if (!empty($arSqlGroup)) {
$strSqlGroup = ", " . implode(", ", $arSqlGroup);
}
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
}
if (in_array($by, array("ID", "FORUM_ID", "TOPIC_ID", "TITLE", "TAGS", "DESCRIPTION", "ICON_ID", "STATE", "APPROVED", "SORT", "VIEWS", "USER_START_ID", "USER_START_NAME", "START_DATE", "POSTS", "LAST_POSTER_ID", "LAST_POSTER_NAME", "LAST_POST_DATE", "LAST_MESSAGE_ID", "POSTS_UNAPPROVED", "ABS_LAST_POSTER_ID", "ABS_LAST_POSTER_NAME", "ABS_LAST_POST_DATE", "ABS_LAST_MESSAGE_ID", "SOCNET_GROUP_ID", "OWNER_ID", "HTML", "XML_ID"))) {
$arSqlOrder[] = "FT." . $by . " " . $order;
} elseif ($by == "FORUM_NAME") {
$arSqlOrder[] = "F.NAME " . $order;
} else {
$arSqlOrder[] = "FT.SORT " . $order;
$by = "SORT";
}
}
$arSqlOrder = array_unique($arSqlOrder);
DelDuplicateSort($arSqlOrder);
if (count($arSqlOrder) > 0) {
$strSqlOrder = " ORDER BY " . implode(", ", $arSqlOrder);
}
if ($bCount || is_set($arAddParams, "bDescPageNumbering") && intVal($arAddParams["nTopCount"]) <= 0) {
$strSql = "SELECT COUNT(FT.ID) as CNT FROM b_forum_topic FT ";
$arCountSqlFrom = $arSqlFrom;
if (isset($arSqlFrom['FUT']) && strpos($strSqlSearch, "FUT.") === false) {
unset($arCountSqlFrom['FUT']);
}
$strSqlCountFrom = implode("\n", $arCountSqlFrom);
$strSql .= $strSqlCountFrom . " WHERE 1 = 1 " . $strSqlSearch;
$db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$iCnt = 0;
if ($ar_res = $db_res->Fetch()) {
$iCnt = intVal($ar_res["CNT"]);
}
if ($bCount) {
return $iCnt;
}
}
$arSQL = array("select" => "", "join" => "");
if (!empty($arAddParams["sNameTemplate"])) {
$arSQL = array_merge_recursive(CForumUser::GetFormattedNameFieldsForSelect(array_merge($arAddParams, array("sUserTablePrefix" => "U_START.", "sForumUserTablePrefix" => "FU_START.", "sFieldName" => "USER_START_NAME_FRMT", "sUserIDFieldName" => "FT.USER_START_ID"))), CForumUser::GetFormattedNameFieldsForSelect(array_merge($arAddParams, array("sUserTablePrefix" => "U_LAST.", "sForumUserTablePrefix" => "FU_LAST.", "sFieldName" => "LAST_POSTER_NAME_FRMT", "sUserIDFieldName" => "FT.LAST_POSTER_ID"))), CForumUser::GetFormattedNameFieldsForSelect(array_merge($arAddParams, array("sUserTablePrefix" => "U_ABS_LAST.", "sForumUserTablePrefix" => "FU_ABS_LAST.", "sFieldName" => "ABS_LAST_POSTER_NAME_FRMT", "sUserIDFieldName" => "FT.ABS_LAST_POSTER_ID"))));
$arSQL["select"] = ",\n\t" . implode(",\n\t", $arSQL["select"]);
$arSQL["join"] = "\n" . implode("\n", $arSQL["join"]);
}
if ($UseGroup) {
$strSql = " SELECT F_T.*, FT.FORUM_ID, FT.TOPIC_ID, FT.TITLE, FT.TAGS, FT.DESCRIPTION, FT.ICON_ID, \n" . "\tFT.STATE, FT.APPROVED, FT.SORT, FT.VIEWS, FT.USER_START_ID, FT.USER_START_NAME, \n" . "\t" . CForumNew::Concat("-", array("FT.ID", "FT.TITLE_SEO")) . " as TITLE_SEO, \n" . "\t" . $DB->DateToCharFunction("FT.START_DATE", "FULL") . " as START_DATE, \n" . "\tFT.POSTS, FT.LAST_POSTER_ID, FT.LAST_POSTER_NAME, \n" . "\t" . $DB->DateToCharFunction("FT.LAST_POST_DATE", "FULL") . " as LAST_POST_DATE, \n" . "\tFT.LAST_POST_DATE AS LAST_POST_DATE_ORIGINAL, FT.LAST_MESSAGE_ID, \n" . "\tFT.POSTS_UNAPPROVED, FT.ABS_LAST_POSTER_ID, FT.ABS_LAST_POSTER_NAME, \n" . "\t" . $DB->DateToCharFunction("FT.ABS_LAST_POST_DATE", "FULL") . " as ABS_LAST_POST_DATE, \n" . "\tFT.ABS_LAST_POST_DATE AS ABS_LAST_POST_DATE_ORIGINAL, FT.ABS_LAST_MESSAGE_ID, \n" . "\tFT.SOCNET_GROUP_ID, FT.OWNER_ID, FT.HTML, FT.XML_ID, \n" . "\tF.NAME as FORUM_NAME, \n" . "\tFS.IMAGE, '' as IMAGE_DESCR " . $arSQL["select"] . " \n" . " FROM \n" . "\t( \n" . "\t\tSELECT FT.ID" . $strSqlSelect . " \n" . "\t\tFROM b_forum_topic FT \n" . "\t\t\tLEFT JOIN b_forum F ON (FT.FORUM_ID = F.ID) \n" . "\t\t\t" . $strSqlFrom . " \n" . "\t\tWHERE 1 = 1 " . $strSqlSearch . " \n" . "\t\tGROUP BY FT.ID" . $strSqlGroup . " \n" . "\t) F_T \n" . " INNER JOIN b_forum_topic FT ON (F_T.ID = FT.ID) \n" . " LEFT JOIN b_forum F ON (FT.FORUM_ID = F.ID) \n" . " LEFT JOIN b_forum_smile FS ON (FT.ICON_ID = FS.ID) " . $arSQL["join"] . " \n" . $strSqlOrder;
} else {
$strSql = " SELECT FT.ID, FT.FORUM_ID, FT.TOPIC_ID, FT.TITLE, FT.TAGS, FT.DESCRIPTION, FT.ICON_ID, \n" . "\tFT.STATE, FT.APPROVED, FT.SORT, FT.VIEWS, FT.USER_START_ID, FT.USER_START_NAME, \n" . "\t" . CForumNew::Concat("-", array("FT.ID", "FT.TITLE_SEO")) . " as TITLE_SEO, \n" . "\t" . $DB->DateToCharFunction("FT.START_DATE", "FULL") . " as START_DATE, \n" . "\tFT.POSTS, FT.LAST_POSTER_ID, FT.LAST_POSTER_NAME, \n" . "\t" . $DB->DateToCharFunction("FT.LAST_POST_DATE", "FULL") . " as LAST_POST_DATE, \n" . "\tFT.LAST_POST_DATE AS LAST_POST_DATE_ORIGINAL, FT.LAST_MESSAGE_ID, \n" . "\tFT.POSTS_UNAPPROVED, FT.ABS_LAST_POSTER_ID, FT.ABS_LAST_POSTER_NAME, \n" . "\t" . $DB->DateToCharFunction("FT.ABS_LAST_POST_DATE", "FULL") . " as ABS_LAST_POST_DATE, \n" . "\tFT.ABS_LAST_POST_DATE AS ABS_LAST_POST_DATE_ORIGINAL, FT.ABS_LAST_MESSAGE_ID, \n" . "\tFT.SOCNET_GROUP_ID, FT.OWNER_ID, FT.HTML, FT.XML_ID, \n" . "\tF.NAME as FORUM_NAME, \n" . "\tFS.IMAGE, '' as IMAGE_DESCR" . $strSqlSelect . $arSQL["select"] . " \n" . " FROM b_forum_topic FT \n" . "\tLEFT JOIN b_forum F ON (FT.FORUM_ID = F.ID) \n" . "\tLEFT JOIN b_forum_smile FS ON (FT.ICON_ID = FS.ID) \n" . "\t" . $strSqlFrom . $arSQL["join"] . " \n" . " WHERE 1 = 1 " . $strSqlSearch . " \n" . $strSqlOrder;
}
$iNum = intVal($iNum);
if ($iNum > 0 || intVal($arAddParams["nTopCount"]) > 0) {
$iNum = $iNum > 0 ? $iNum : intVal($arAddParams["nTopCount"]);
$strSql .= "\nLIMIT 0," . $iNum;
}
if (!$iNum && is_set($arAddParams, "bDescPageNumbering") && intVal($arAddParams["nTopCount"]) <= 0) {
$db_res = new CDBResult();
$db_res->NavQuery($strSql, $iCnt, $arAddParams);
} else {
$db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
if (is_set($arAddParams, 'NoFilter') && $arAddParams['NoFilter'] == true) {
return $db_res;
}
return new _CTopicDBResult($db_res, $arAddParams);
}
function GetListEx($arOrder = array("SORT" => "ASC"), $arFilter = array())
{
global $DB;
$arSqlSearch = array();
$strSqlSearch = "";
$arSqlOrder = array();
$strSqlOrder = "";
$arFilter = is_array($arFilter) ? $arFilter : array();
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 "ID":
case "SORT":
case "PARENT_ID":
case "LEFT_MARGIN":
case "RIGHT_MARGIN":
case "DEPTH_LEVEL":
if (intVal($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(FR." . $key . " IS NULL OR FR." . $key . "<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " FR." . $key . " IS NULL OR NOT " : "") . "(FR." . $key . " " . $strOperation . " " . intVal($val) . " )";
}
break;
case "LID":
if (strlen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(FRL.LID IS NULL OR " . ($DB->type == "MSSQL" ? "LEN" : "LENGTH") . "(FRL.LID)<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " FRL.LID IS NULL OR NOT " : "") . "(FRL.LID " . $strOperation . " '" . $DB->ForSql($val) . "' )";
}
break;
}
}
if (!empty($arSqlSearch)) {
$strSqlSearch = " WHERE (" . implode(") AND (", $arSqlSearch) . ") ";
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
}
if ($by == "ID") {
$arSqlOrder[] = " FR.ID " . $order . " ";
} elseif ($by == "LID") {
$arSqlOrder[] = " FRL.LID " . $order . " ";
} elseif ($by == "NAME") {
$arSqlOrder[] = " FRL.NAME " . $order . " ";
} elseif ($by == "LEFT_MARGIN") {
$arSqlOrder[] = " FR.LEFT_MARGIN " . $order . " ";
} else {
$arSqlOrder[] = " FR.SORT " . $order . " ";
$by = "SORT";
}
}
DelDuplicateSort($arSqlOrder);
if (!empty($arSqlOrder)) {
$strSqlOrder = "ORDER BY " . implode(", ", $arSqlOrder);
}
$strSql = "SELECT FR.ID, FR.SORT, FR.PARENT_ID, FR.LEFT_MARGIN, FR.RIGHT_MARGIN, FR.DEPTH_LEVEL, FRL.LID, FRL.NAME, FRL.DESCRIPTION " . "FROM b_forum_group FR " . "\tLEFT JOIN b_forum_group_lang FRL ON FR.ID = FRL.FORUM_GROUP_ID " . $strSqlSearch . " " . $strSqlOrder . " ";
$db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
return $db_res;
}
/**
* <p>Возвращает голосования за посетителей форума, которые удовлетворяют фильтру arFilter, упорядоченные в соответствии с порядком arOrder.</p>
*
*
*
*
* @param array $arrayarOrder = array("TO_USER_ID"=>"ASC") <p>Порядок сортировки записей; представляет собой ассоциативный
* массив, в котором ключами являются названия параметров
* голосования, а значениями - направления сортировки.</p>
* <p>Допустимые параметры голосования для сортировки: <br> FROM_USER_ID -
* код голосовавшего посетителя <br> TO_USER_ID - код посетителя, за
* которого голосовали <br> POINTS - количество отданных голосов <br>
* DATE_UPDATE - дата последнего изменения записи </p>
*
*
*
* @param array $arrayarFilter = array() <p>Фильтр на возвращаемые голосования; представляет собой
* ассоциативный массив, в котором ключами являются названия
* параметров голосования, а значениями - условия на эти
* параметры.</p> <p>Допустимые параметры голосования для фильтрации:
* <br> FROM_USER_ID - код голосовавшего посетителя <br> TO_USER_ID - код
* посетителя, за которого голосовали</p>
*
*
*
* @return CDBResult <p>Возвращяется объект класса CDBResult, каждая запись которого
* представляет собой массив с ключами.</p><table class="tnormal" width="100%"><tbody> <tr>
* <th width="15%">Ключ</th> <th>Значение</th> </tr> <tr> <td>FROM_USER_ID</td> <td>Код
* пользователя, который отдал голос.</td> </tr> <tr> <td>TO_USER_ID</td> <td>Код
* пользователя, за которого отдали голос.</td> </tr> <tr> <td>POINTS</td>
* <td>Количество отданных голосов.</td> </tr> <tr> <td>DATE_UPDATE</td> <td>Дата
* последнего изменения записи.</td> </tr> </tbody></table>
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/forum/developer/cforumuserpoints/cforumuserpoints__getlist.b43c7704.php
* @author Bitrix
*/
public static function GetList($arOrder = array("TO_USER_ID"=>"ASC"), $arFilter = array())
{
global $DB;
$arSqlSearch = Array();
$strSqlSearch = "";
$arSqlOrder = Array();
$strSqlOrder = "";
$arFilter = (is_array($arFilter) ? $arFilter : array());
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 "FROM_USER_ID":
case "TO_USER_ID":
if (IntVal($val)<=0)
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FR.".$key." IS NULL OR FR.".$key."<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" FR.".$key." IS NULL OR NOT ":"")."(FR.".$key." ".$strOperation." ".IntVal($val)." )";
break;
}
}
if (!empty($arSqlSearch))
$strSqlSearch = " WHERE (".implode(") AND (", $arSqlSearch).") ";
foreach ($arOrder as $by=>$order)
{
$by = strtoupper($by); $order = strtoupper($order);
if ($order!="ASC") $order = "DESC";
if ($by == "FROM_USER_ID") $arSqlOrder[] = " FR.FROM_USER_ID ".$order." ";
elseif ($by == "POINTS") $arSqlOrder[] = " FR.POINTS ".$order." ";
elseif ($by == "DATE_UPDATE") $arSqlOrder[] = " FR.DATE_UPDATE ".$order." ";
else
{
$arSqlOrder[] = " FR.TO_USER_ID ".$order." ";
$by = "TO_USER_ID";
}
}
DelDuplicateSort($arSqlOrder);
if (!empty($arSqlOrder))
$strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
$strSql =
"SELECT FR.FROM_USER_ID, FR.TO_USER_ID, FR.POINTS, FR.DATE_UPDATE ".
"FROM b_forum_user_points FR ".
$strSqlSearch." ".
$strSqlOrder;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
return $db_res;
}
function GetListEx($arOrder = array("SORT"=>"ASC"), $arFilter = array())
{
global $DB;
$arSqlSearch = Array();
$strSqlSearch = "";
$arSqlOrder = Array();
$strSqlOrder = "";
$arFilter = (is_array($arFilter) ? $arFilter : array());
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 "ID":
case "SORT":
if (intVal($val)<=0)
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FR.".$key." IS NULL OR FR.".$key."<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" FR.".$key." IS NULL OR NOT ":"")."(FR.".$key." ".$strOperation." ".intVal($val)." )";
break;
case "TYPE":
if (strlen($val)<=0)
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FR.TYPE IS NULL OR ".($DB->type == "MSSQL" ? "LEN" : "LENGTH")."(FR.TYPE)<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" FR.TYPE IS NULL OR NOT ":"")."(FR.TYPE ".$strOperation." '".$DB->ForSql($val)."' )";
break;
case "LID":
if (strlen($val)<=0)
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FRL.LID IS NULL OR ".($DB->type == "MSSQL" ? "LEN" : "LENGTH")."(FRL.LID)<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" FRL.LID IS NULL OR NOT ":"")."(FRL.LID ".$strOperation." '".$DB->ForSql($val)."' )";
break;
}
}
if (!empty($arSqlSearch))
$strSqlSearch = " WHERE (".implode(") AND (", $arSqlSearch).") ";
foreach ($arOrder as $by=>$order)
{
$by = strtoupper($by); $order = strtoupper($order);
$order = ($order == "ASC" ? "ASC" : "DESC");
if ($by == "ID") $arSqlOrder[] = " FR.ID ".$order." ";
elseif ($by == "LID") $arSqlOrder[] = " FRL.LID ".$order." ";
elseif ($by == "NAME") $arSqlOrder[] = " FRL.NAME ".$order." ";
elseif ($by == "TYPE") $arSqlOrder[] = " FR.TYPE ".$order." ";
else
{
$arSqlOrder[] = " FR.SORT ".$order." ";
$by = "SORT";
}
}
DelDuplicateSort($arSqlOrder);
if (!empty($arSqlOrder))
$strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
$strSql =
"SELECT FR.ID, FR.SORT, FR.TYPE, FR.TYPING, FR.IMAGE, FR.CLICKABLE, ".
" FRL.LID, FRL.NAME, FR.DESCRIPTION, FR.IMAGE_WIDTH, FR.IMAGE_HEIGHT ".
"FROM b_forum_smile FR ".
" LEFT JOIN b_forum_smile_lang FRL ON FR.ID = FRL.SMILE_ID ".
$strSqlSearch." ".
$strSqlOrder;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
return $db_res;
}
public static function GetListEx($arOrder = array("ID" => "ASC"), $arFilter = array(), $bCount = false, $iNum = 0, $arAddParams = array())
{
global $DB;
$arSqlSearch = array();
$arSqlOrder = array();
$strSqlSearch = "";
$strSqlOrder = "";
$strSqlFrom = "";
$arFilter = is_array($arFilter) ? $arFilter : array();
if (is_array($bCount) && empty($arAddParams)) {
$arAddParams = $bCount;
$bCount = false;
$iNum = 0;
}
$arAddParams = is_array($arAddParams) ? $arAddParams : array($arAddParams);
if (is_set($arAddParams, "nameTemplate")) {
$arAddParams["sNameTemplate"] = $arAddParams["nameTemplate"];
}
$arAddParams["bCount"] = !!$bCount || !!$arAddParams["bCount"];
$arAddParams["nTopCount"] = $iNum > 0 ? $iNum : ($arAddParams["nTopCount"] > 0 ? $arAddParams["nTopCount"] : 0);
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 "ID":
case "FOLDER_ID":
case "AUTHOR_ID":
case "RECIPIENT_ID":
case "USER_ID":
if (IntVal($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(PM." . $key . " IS NULL OR PM." . $key . "<=0)";
} elseif (strToUpper($strOperation) == "IN") {
$arSqlSearch[] = ($strNegative == "Y" ? " PM." . $key . " IS NULL OR NOT " : "") . "(PM." . $key . " " . $strOperation . " (" . IntVal($val) . ") )";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " PM." . $key . " IS NULL OR NOT " : "") . "(PM." . $key . " " . $strOperation . " " . IntVal($val) . " )";
}
break;
case "OWNER_ID":
if (COption::GetOptionString("forum", "UsePMVersion", "2") == 2) {
$user_id = 0;
if (is_array($val) && intVal($val["USER_ID"]) > 0) {
$user_id = intVal($val["USER_ID"]);
} else {
$user_id = intVal($val);
}
$arSqlSearch[] = "(PM.USER_ID=" . $user_id . " AND ((PM.FOLDER_ID=2) OR (PM.FOLDER_ID=3)))";
} else {
$arSqlSearch[] = "((PM.AUTHOR_ID=" . intVal($val) . ") AND (PM.IS_READ='N')) OR (PM.USER_ID=" . intVal($val) . " AND (PM.FOLDER_ID=2))";
}
break;
case "POST_SUBJ":
case "POST_MESSAGE":
case "USE_SMILES":
case "IS_READ":
if (strlen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(PM." . $key . " IS NULL OR LENGTH(PM." . $key . ")<=0)";
} elseif (strToUpper($strOperation) == "IN") {
$arSqlSearch[] = ($strNegative == "Y" ? " PM." . $key . " IS NULL OR NOT " : "") . "(PM." . $key . " " . $strOperation . " ('" . $DB->ForSql($val) . "') )";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " PM." . $key . " IS NULL OR NOT " : "") . "(PM." . $key . " " . $strOperation . " '" . $DB->ForSql($val) . "' )";
}
break;
case "POST_DATE":
if (strlen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(PM." . $key . " IS NULL OR LENGTH(PM." . $key . ")<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " PM." . $key . " IS NULL OR NOT " : "") . "(PM." . $key . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . " )";
}
break;
}
}
if (!empty($arSqlSearch)) {
$strSqlSearch = " AND (" . implode(") AND (", $arSqlSearch) . ") ";
}
$iCnt = 0;
if ($arAddParams["bCount"] || is_set($arAddParams, "bDescPageNumbering")) {
$strSql = "SELECT COUNT(PM.ID) AS CNT FROM b_forum_private_message PM WHERE (1=1) " . $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;
}
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
}
if ($by == "AUTHOR_NAME") {
$arSqlOrder[] = " AUTHOR_NAME " . $order . " ";
} elseif ($by == "RECIPIENT_NAME") {
$arSqlOrder[] = " RECIPIENT_NAME " . $order . " ";
} elseif ($by == "AUTHOR_ID") {
$arSqlOrder[] = " PM.AUTHOR_ID " . $order . " ";
} elseif ($by == "RECIPIENT_ID") {
$arSqlOrder[] = " PM.RECIPIENT_ID " . $order . " ";
} elseif ($by == "POST_DATE") {
$arSqlOrder[] = " PM.POST_DATE " . $order . " ";
} elseif ($by == "POST_SUBJ") {
$arSqlOrder[] = " PM.POST_SUBJ " . $order . " ";
} elseif ($by == "POST_MESSAGE") {
$arSqlOrder[] = " PM.POST_MESSAGE " . $order . " ";
} elseif ($by == "IS_READ") {
$arSqlOrder[] = " PM.IS_READ " . $order . " ";
} elseif ($by == "USE_SMILES") {
$arSqlOrder[] = " PM.USE_SMILES " . $order . " ";
} else {
$arSqlOrder[] = " PM.POST_DATE " . $order . " ";
$by = "POST_DATE";
}
}
DelDuplicateSort($arSqlOrder);
if (!empty($arSqlOrder)) {
$strSqlOrder = " ORDER BY " . implode(", ", $arSqlOrder);
}
$strSql = "SELECT \n\t\t\t\tPM.ID, PM.POST_SUBJ, PM.POST_MESSAGE, PM.FOLDER_ID, PM.IS_READ, PM.USE_SMILES, PM.REQUEST_IS_READ, \n\t\t\t\t" . $DB->DateToCharFunction("PM.POST_DATE", "FULL") . " as POST_DATE, \n\t\t\t\tPM.USER_ID,\n\t\t\t\t\n\t\t\t\tPM.AUTHOR_ID, U.EMAIL AS AUTHOR_EMAIL, U.LOGIN AS AUTHOR_LOGIN, \n\t\t\t\tCASE\n\t\t\t\t\tWHEN ((FU.SHOW_NAME='Y') AND (LENGTH(TRIM(CONCAT_WS(''," . CForumUser::GetNameFieldsForQuery($arAddParams["sNameTemplate"]) . ")))>0))\n\t\t\t\t\t\tTHEN TRIM(REPLACE(CONCAT_WS(' '," . CForumUser::GetNameFieldsForQuery($arAddParams["sNameTemplate"]) . "), ' ', ' '))\n\t\t\t\t\t\tELSE U.LOGIN\n\t\t\t\t\tEND AS AUTHOR_NAME, \n\t\t\t\tPM.RECIPIENT_ID, UU.EMAIL AS RECIPIENT_EMAIL, UU.LOGIN AS RECIPIENT_LOGIN,\n\t\t\t\tCASE\n\t\t\t\t\tWHEN ((FUU.SHOW_NAME='Y') AND (LENGTH(TRIM(CONCAT_WS(''," . CForumUser::GetNameFieldsForQuery($arAddParams["sNameTemplate"], "UU.") . ")))>0))\n\t\t\t\t\tTHEN TRIM(REPLACE(CONCAT_WS(' '," . CForumUser::GetNameFieldsForQuery($arAddParams["sNameTemplate"], "UU.") . "), ' ', ' '))\n\t\t\t\t\tELSE UU.LOGIN\n\t\t\t\tEND AS RECIPIENT_NAME\n\t\t\tFROM b_forum_private_message PM\n\t\t\t\tLEFT JOIN b_forum_user FU ON (PM.AUTHOR_ID = FU.USER_ID)\n\t\t\t\tLEFT JOIN b_forum_user FUU ON (PM.RECIPIENT_ID = FUU.USER_ID)\n\t\t\t\tLEFT JOIN b_user U ON (PM.AUTHOR_ID = U.ID)\n\t\t\t\tLEFT JOIN b_user UU ON (PM.RECIPIENT_ID = UU.ID)\n\t\t\tWHERE 1=1 " . $strSqlSearch . "\n\t\t\t" . $strSqlOrder;
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;
}
