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; }