static function GetList($arOrder = Array("ID"=>"ASC"), $arFilter = Array(), $bCount = false, $iNum = 0, $arAddParams = array())
{
global $DB, $USER_FIELD_MANAGER;
$arSqlSearch = array();
$arSqlOrder = array();
$strSqlSearch = "";
$strSqlOrder = "";
$arFilter = (is_array($arFilter) ? $arFilter : array());
$arAddParams = (is_array($arAddParams) ? $arAddParams : array());
$arAddParams['nTopCount'] = ($arAddParams['nTopCount'] ?? 0);
$obUserFieldsSql = new CUserTypeSQL;
$obUserFieldsSql->SetEntity("FORUM_MESSAGE", "FM.ID");
$obUserFieldsSql->SetSelect($arAddParams["SELECT"] ?? null);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
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 "PARAM1":
case "AUTHOR_NAME":
case "POST_MESSAGE_CHECK":
case "APPROVED":
case "NEW_TOPIC":
if ($val == '')
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FM.".$key." IS NULL OR LENGTH(FM.".$key.")<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" FM.".$key." IS NULL OR NOT ":"")."(FM.".$key." ".$strOperation." '".$DB->ForSql($val)."' )";
break;
case "PARAM2":
case "ID":
case "AUTHOR_ID":
case "FORUM_ID":
case "TOPIC_ID":
case "ATTACH_IMG":
if ( ($strOperation == "IN") && (!is_array($val)) && (mb_strpos($val, ",") > 0) )
$val = explode(",", $val);
if (($strOperation!="IN") && (intval($val) > 0))
$arSqlSearch[] = ($strNegative=="Y"?" FM.".$key." IS NULL OR NOT ":"")."(FM.".$key." ".$strOperation." ".intval($val)." )";
elseif (($strOperation =="IN") && ((is_array($val) && sizeof($val)>0 && (array_sum($val) > 0)) || ($val <> '') ))
{
if (is_array($val))
{
$val_int = array();
foreach ($val as $v)
$val_int[] = intval($v);
$val = implode(", ", $val_int);
}
else
{
$val = intval($val);
}
$arSqlSearch[] = ($strNegative=="Y"?" NOT ":"")."(FM.".$key." IN (".$DB->ForSql($val).") )";
}
else
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(FM.".$key." IS NULL OR FM.".$key."<=0)";
break;
case "EDIT_DATE":
case "POST_DATE":
if ($val == '')
$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 = mb_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 ($v == '')
$str[] = ($strNegative=="Y"?"NOT":"")."(FM.APPROVED IS NULL OR LENGTH(FM.APPROVED)<=0)";
else
$str[] = ($strNegative=="Y"?" FM.APPROVED IS NULL OR NOT ":"")."FM.APPROVED ".$strOperation." '".$DB->ForSql($v)."' ";
break;
}
}
$return[] = implode(" AND ", $str);
}
if (count($return)>0)
$arSqlSearch[] = "(".implode(") OR (", $return).")";
}
break;
}
}
$r = $obUserFieldsSql->GetFilter();
if (!empty($r))
$arSqlSearch[] = $r;
if (!empty($arSqlSearch))
$strSqlSearch = " AND (".implode(") AND (", $arSqlSearch).") ";
if ($bCount || (is_set($arAddParams, "bDescPageNumbering") && $arAddParams["nTopCount"] <= 0))
{
$strFrom = "FROM b_forum_message FMn".$obUserFieldsSql->GetJoin("FM.ID")."nWHERE 1 = 1 ".$strSqlSearch;
if($obUserFieldsSql->GetDistinct())
$strFrom = "FROM b_forum_message FMntINNER JOIN (SELECT DISTINCT FM.ID ".$strFrom.") FM2 ON (FM2.ID=FM.ID)";
// This code was changed because of http://bugs.mysql.com/bug.php?id=64002
if ($bCount === "cnt_not_approved")
{
$ar_res = false;
$strSql =
"SELECT COUNT(FM.ID) as CNT, MAX(FM.ID) AS ABS_LAST_MESSAGE_ID, MIN(FM.ID) AS ABS_FIRST_MESSAGE_ID, nt".
"MIN(CASE WHEN FM.NEW_TOPIC='Y' THEN FM.ID ELSE NULL END) AS FIRST_MESSAGE_ID, nt".
"SUM(CASE WHEN FM.APPROVED!='Y' THEN 1 ELSE 0 END) as CNT_NOT_APPROVED,nt".
"MAX(CASE WHEN FM.APPROVED='Y' THEN FM.ID ELSE 0 END) AS LAST_MESSAGE_ID n".
$strFrom;
if (array_intersect_key($arFilter, array("FORUM_ID" => null)) == $arFilter && $arFilter["FORUM_ID"] > 0) // High-usage
{
$db_res = $DB->Query($strSql . "nGROUP BY FM.FORUM_ID", false, "File: ".__FILE__."
Line: ".__LINE__);
$ar_res = $db_res->Fetch();
}
else if (array_intersect_key($arFilter, array("TOPIC_ID" => null)) == $arFilter && $arFilter["TOPIC_ID"] > 0) // High-usage
{
$db_res = $DB->Query($strSql . "nGROUP BY FM.TOPIC_ID", false, "File: ".__FILE__."
Line: ".__LINE__);
$ar_res = $db_res->Fetch();
}
else
{
$strSql = "SELECT COUNT(FM.ID) as CNT ".$strFrom;
$db_res = $DB->Query($strSql , false, "File: ".__FILE__."
Line: ".__LINE__);
if ($db_res && $ar_res = $db_res->Fetch())
{
$strSql =
"SELECT MAX(FM.ID) AS ABS_LAST_MESSAGE_ID, MIN(FM.ID) AS ABS_FIRST_MESSAGE_ID, nt".
"MIN(CASE WHEN FM.NEW_TOPIC='Y' THEN FM.ID ELSE NULL END) AS FIRST_MESSAGE_ID, nt".
"SUM(CASE WHEN FM.APPROVED!='Y' THEN 1 ELSE 0 END) as CNT_NOT_APPROVED,nt".
"MAX(CASE WHEN FM.APPROVED='Y' THEN FM.ID ELSE 0 END) AS LAST_MESSAGE_ID n".
$strFrom;
$db_res = $DB->Query($strSql , false, "File: ".__FILE__."
Line: ".__LINE__);
if ($db_res && $ar_res1 = $db_res->Fetch())
{
$ar_res = array_merge($ar_res1, $ar_res);
}
}
}
return $ar_res;
}
else if ($bCount === "cnt_and_last_mid")
{
$ar_res = array();
if (array_intersect_key($arFilter, array("AUTHOR_ID" => null, "APPROVED" => null)) == $arFilter && $arFilter["AUTHOR_ID"] > 0) // High-usage
{
$strSql = "SELECT COUNT(FM.ID) as CNT, MAX(FM.ID) as LAST_MESSAGE_ID n ".$strFrom." nGROUP BY FM.AUTHOR_ID"; // explain the same as without "GROUP BY"
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
if ($db_res)
$ar_res = $db_res->Fetch();
}
else
{
$strSql = "SELECT COUNT(FM.ID) as CNT n ".$strFrom;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
if ($db_res && $ar_res = $db_res->Fetch())
{
$strSql = "SELECT MAX(FM.ID) as LAST_MESSAGE_ID n ".$strFrom;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
if ($db_res && $ar_res1 = $db_res->Fetch())
{
$ar_res["LAST_MESSAGE_ID"] = $ar_res1["LAST_MESSAGE_ID"];
}
}
}
return $ar_res;
}
else
{
$strSql = "SELECT COUNT(FM.ID) as CNT n ".$strFrom;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
$iCnt = 0;
if ($db_res && $ar_res = $db_res->Fetch())
$iCnt = intval($ar_res["CNT"]);
if ($bCount)
return $iCnt;
}
// /This code was changed because of http://bugs.mysql.com/bug.php?id=64002
}
foreach ($arOrder as $by=>$order)
{
$by = mb_strtoupper($by);
$order = mb_strtoupper($order);
if ($order!="ASC") $order = "DESC";
if ($by == "AUTHOR_NAME") $arSqlOrder[] = " FM.AUTHOR_NAME ".$order." ";
elseif ($by == "EDIT_DATE") $arSqlOrder[] = " FM.EDIT_DATE ".$order." ";
elseif ($by == "POST_DATE") $arSqlOrder[] = " FM.POST_DATE ".$order." ";
elseif ($by == "FORUM_ID") $arSqlOrder[] = " FM.FORUM_ID ".$order." ";
elseif ($by == "TOPIC_ID") $arSqlOrder[] = " FM.TOPIC_ID ".$order." ";
elseif ($by == "NEW_TOPIC") $arSqlOrder[] = " FM.NEW_TOPIC ".$order." ";
elseif ($by == "APPROVED") $arSqlOrder[] = " FM.APPROVED ".$order." ";
else
{
$r = $obUserFieldsSql->GetOrder($by);
if (!!$r)
{
$arSqlOrder[] = " ".$r." ".$order." ";
}
else
{
$arSqlOrder[] = " FM.ID ".$order." ";
$by = "ID";
}
}
}
$arSqlOrder = array_unique($arSqlOrder);
DelDuplicateSort($arSqlOrder);
if(!empty($arSqlOrder))
$strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
$strSqlUserFieldJoin = $obUserFieldsSql->GetJoin("FM.ID");
if ($obUserFieldsSql->GetDistinct())
{
$obUserFieldsSqlSelect = new CUserTypeSQL;
$obUserFieldsSqlSelect->SetEntity("FORUM_MESSAGE", "FM.ID");
$obUserFieldsSqlSelect->SetSelect($arAddParams["SELECT"]);
$obUserFieldsSqlSelect->SetOrder($arOrder);
$strSqlUserFieldJoin =
$obUserFieldsSqlSelect->GetJoin("FM.ID")."
INNER JOIN (
SELECT DISTINCT FM.ID
FROM b_forum_message FMn".
$obUserFieldsSql->GetJoin("FM.ID")."n".
"WHERE 1 = 1 ".$strSqlSearch.") FM2 ON (FM2.ID = FM.ID) ";
$strSqlSearch = "";
}
$select = "FM.ID,
FM.AUTHOR_ID, FM.AUTHOR_NAME, FM.AUTHOR_EMAIL, FM.AUTHOR_IP,
FM.USE_SMILES, FM.POST_MESSAGE, FM.POST_MESSAGE_HTML, FM.POST_MESSAGE_FILTER,
FM.FORUM_ID, FM.TOPIC_ID, FM.NEW_TOPIC,
FM.APPROVED, FM.SOURCE_ID, FM.POST_MESSAGE_CHECK, FM.GUEST_ID, FM.AUTHOR_REAL_IP, FM.ATTACH_IMG, FM.XML_ID,
" . $DB->DateToCharFunction("FM.POST_DATE", "FULL") . " as POST_DATE,
FM.EDITOR_ID, FM.EDITOR_NAME, FM.EDITOR_EMAIL, FM.EDIT_REASON,
FU.SHOW_NAME, U.LOGIN, U.NAME, U.SECOND_NAME, U.LAST_NAME, U.PERSONAL_PHOTO,
" . $DB->DateToCharFunction("FM.EDIT_DATE", "FULL") . " as EDIT_DATE, FM.PARAM1, FM.PARAM2, FM.HTML, FM.MAIL_HEADER, FM.SERVICE_TYPE, FM.SERVICE_DATA " .
$obUserFieldsSql->GetSelect() .
(!empty($arAddParams["sNameTemplate"]) ?
",nt".CForumUser::GetFormattedNameFieldsForSelect(array_merge(
$arAddParams, array(
"sUserTablePrefix" => "U.",
"sForumUserTablePrefix" => "FU.",
"sFieldName" => "AUTHOR_NAME_FRMT")), false) : "");
$strSql =
"SELECT " . $select . "
FROM b_forum_message FM
LEFT JOIN b_forum_user FU ON (FM.AUTHOR_ID = FU.USER_ID)
LEFT JOIN b_user U ON (FM.AUTHOR_ID = U.ID)" .
$strSqlUserFieldJoin . "
WHERE 1 = 1 " . $strSqlSearch . "
" . $strSqlOrder;
$limit = intval($iNum ?? $arAddParams["nTopCount"]);
if ($limit > 0)
{
$strSql .= " LIMIT 0,".$limit;
}
if (!$iNum && is_set($arAddParams, "bDescPageNumbering") && $arAddParams["nTopCount"] <= 0)
{
$db_res = new CDBResult();
$db_res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("FORUM_MESSAGE"));
$db_res->NavQuery($strSql, $iCnt, $arAddParams);
}
else
{
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
$db_res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("FORUM_MESSAGE"));
}
return new _CMessageDBResult($db_res, $arAddParams);
}