public static function GetList($arOrder = array("ID" => "ASC"), $arFilter = array(), $arAddParams = array())
{
global $DB;
$arFields = array("ID" => array("FIELD" => "BPP.ID", "TYPE" => "int"), "POST_ID" => array("FIELD" => "BPP.POST_ID", "TYPE" => "int"), "USER_ID" => array("FIELD" => "BPP.USER_ID", "TYPE" => "int"), "NAME" => array("FIELD" => "BPP.NAME", "TYPE" => "string"), "VALUE" => array("FIELD" => "BPP.VALUE", "TYPE" => "string"), "RANK" => $arOrder["OWNER_ID"] > 0 ? array("FIELD" => "RV0.RANK", "TYPE" => "int", "FROM" => "\n\tLEFT JOIN (\n\t\t" . "SELECT MAX(RV2.VOTE_WEIGHT) as VOTE_WEIGHT, RV2.ENTITY_ID \n\t\t" . "FROM b_rating_user RV2 \n\t\t" . "GROUP BY RV2.ENTITY_ID) RV ON (RV.ENTITY_ID = BPP.USER_ID)\n\t" . "LEFT JOIN (\n\t\t" . "SELECT RV1.OWNER_ID, SUM(case when RV1.ID is not null then 1 else 0 end) as RANK \n\t\t" . "FROM b_rating_vote RV1 \n\t\t" . "WHERE RV1.USER_ID = " . $arOrder["OWNER_ID"] . "\n\t\t" . "GROUP BY RV1.OWNER_ID) RV0 ON (RV0.OWNER_ID = BPP.USER_ID)") : array("FIELD" => "RV.RANK", "TYPE" => "string", "FROM" => "\n\tLEFT JOIN (" . "\n\t\tSELECT MAX(RV2.VOTE_WEIGHT) as VOTE_WEIGHT, RV2.ENTITY_ID, 0 as RANK " . "\n\t\tFROM b_rating_user RV2" . "\n\t\tGROUP BY RV2.ENTITY_ID) RV ON (RV.ENTITY_ID = BPP.USER_ID)"), "USER_ACTIVE" => array("FIELD" => "U.ACTIVE", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_NAME" => array("FIELD" => "U.NAME", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_LAST_NAME" => array("FIELD" => "U.LAST_NAME", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_SECOND_NAME" => array("FIELD" => "U.SECOND_NAME", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_LOGIN" => array("FIELD" => "U.LOGIN", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_PERSONAL_PHOTO" => array("FIELD" => "U.PERSONAL_PHOTO", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"));
$arSelect = array_diff(array_keys($arFields), array("RANK"));
$arSelect = is_array($arAddParams["SELECT"]) && !empty($arAddParams["SELECT"]) ? array_intersect($arAddParams["SELECT"], $arSelect) : $arSelect;
$arSql = CBlog::PrepareSql($arFields, array(), $arFilter, false, $arSelect);
$arSql["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSql["SELECT"]);
$iCnt = 0;
if ($arAddParams["bCount"] || array_key_exists("bDescPageNumbering", $arAddParams)) {
$strSql = "SELECT COUNT(BPP.ID) AS CNT \n" . "FROM b_blog_post_param BPP " . $arSql["FROM"] . "\n" . (empty($arSql["GROUPBY"]) ? "" : "GROUP BY " . $arSql["GROUPBY"] . "\n") . "WHERE " . (empty($arSql["WHERE"]) ? "1 = 1" : $arSql["WHERE"]);
$db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
if ($arAddParams["bCount"]) {
return $db_res;
}
$iCnt = $db_res && ($res = $db_res->Fetch()) ? intval($res["CNT"]) : 0;
}
// ORDER BY -->
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order) != "ASC" ? "DESC" : "ASK";
if (array_key_exists($by, $arFields) && !array_key_exists($by, $arSqlOrder)) {
if (strtoupper($DB->type) == "ORACLE") {
$order .= $order == "ASC" ? " NULLS FIRST" : " NULLS LAST";
}
if (isset($arFields[$by]["FROM"]) && !empty($arFields[$by]["FROM"]) && strpos($arSql["FROM"], $arFields[$by]["FROM"]) === false) {
$arSql["FROM"] .= " " . $arFields[$by]["FROM"];
}
if ($by == "RANK") {
$arSql["SELECT"] .= ", " . $arFields["RANK"]["FIELD"];
$arSqlOrder[$by] = IsModuleInstalled("intranet") ? "RV.VOTE_WEIGHT " . $order . ", RANK " . $order : "RANK " . $order . ", RV.VOTE_WEIGHT " . $order;
} else {
$arSqlOrder[$by] = (array_key_exists("ORDER", $arFields[$by]) ? $arFields[$by]["ORDER"] : $arFields[$by]["FIELD"]) . " " . $order;
}
}
}
DelDuplicateSort($arSqlOrder);
$arSql["ORDERBY"] = implode(", ", $arSqlOrder);
// <-- ORDER BY
$strSql = "SELECT " . $arSql["SELECT"] . "\n" . "FROM b_blog_post_param BPP" . $arSql["FROM"] . "\n" . "WHERE " . (empty($arSql["WHERE"]) ? "1 = 1" : $arSql["WHERE"]) . (empty($arSql["ORDERBY"]) ? "" : "\nORDER BY " . $arSql["ORDERBY"]);
if (is_set($arAddParams, "bDescPageNumbering")) {
$db_res = new CDBResult();
$db_res->NavQuery($strSql, $iCnt, $arAddParams);
} else {
$db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
return $db_res;
}
public static function GetList($arOrder, $arFilter)
{
global $DB;
$arSqlSearch = CTaskLog::GetFilter($arFilter);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tTL.*,\n\t\t\t\t" . $DB->DateToCharFunction("TL.CREATED_DATE", "FULL") . " AS CREATED_DATE,\n\t\t\t\tU.NAME AS USER_NAME,\n\t\t\t\tU.LAST_NAME AS USER_LAST_NAME,\n\t\t\t\tU.SECOND_NAME AS USER_SECOND_NAME,\n\t\t\t\tU.LOGIN AS USER_LOGIN\n\t\t\tFROM\n\t\t\t\tb_tasks_log TL\n\t\t\tINNER JOIN\n\t\t\t\tb_user U\n\t\t\tON\n\t\t\t\tU.ID = TL.USER_ID\n\t\t\t" . (sizeof($arSqlSearch) ? "WHERE " . implode(" AND ", $arSqlSearch) : "") . "\n\t\t";
if (!is_array($arOrder) || sizeof($arOrder) == 0) {
$arOrder = array("CREATED_DATE" => "ASC");
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "user" || $by == "user_id") {
$arSqlOrder[] = " TL.USER_ID " . $order . " ";
} elseif ($by == "field") {
$arSqlOrder[] = " TL.FIELD " . $order . " ";
} elseif ($by == "task_id") {
$arSqlOrder[] = " TL.TASK_ID " . $order . " ";
} elseif ($by == "rand") {
$arSqlOrder[] = CTasksTools::getRandFunction();
} else {
$arSqlOrder[] = " TL.CREATED_DATE " . $order . " ";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0, $arSqlOrderCnt = count($arSqlOrder); $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
protected static function PrepareSql(&$arFields, $arOrder, &$arFilter, $arGroupBy, $arSelectFields)
{
global $DB;
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy)>0)
{
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct))
{
if (strlen($strSqlGroupBy) > 0)
$strSqlGroupBy .= ", ";
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy)==0)
{
$strSqlSelect = "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT ";
}
else
{
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields)>0 && array_key_exists($arSelectFields, $arFields))
$arSelectFields = array($arSelectFields);
if (!isset($arSelectFields)
|| !is_array($arSelectFields)
|| count($arSelectFields)<=0
|| in_array("*", $arSelectFields))
{
$cntFieldsKeys = count($arFieldsKeys);
for ($i = 0; $i < $cntFieldsKeys; $i++)
{
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"])
&& $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y")
{
continue;
}
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($arFieldsKeys[$i], $arOrder)))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL")." as ".$arFieldsKeys[$i];
}
elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($arFieldsKeys[$i], $arOrder)))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT")." as ".$arFieldsKeys[$i];
}
else
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i];
if (isset($arFields[$arFieldsKeys[$i]]["FROM"])
&& strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0
&& !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
}
else
{
foreach ($arSelectFields as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields))
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if (in_array($key, $arGroupByFunct))
{
$strSqlSelect .= $key."(".$arFields[$val]["FIELD"].") as ".$val;
}
else
{
if ($arFields[$val]["TYPE"] == "datetime")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($val, $arOrder)))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
}
elseif ($arFields[$val]["TYPE"] == "date")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($val, $arOrder)))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT")." as ".$val;
}
else
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val;
}
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if (strlen($strSqlGroupBy) > 0)
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
$strSqlSelect .= "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT";
}
else
$strSqlSelect = "%%_DISTINCT_%% ".$strSqlSelect;
}
// <-- SELECT
// WHERE -->
$arSqlSearch = array();
if (!is_array($arFilter))
$filter_keys = array();
else
$filter_keys = array_keys($arFilter);
$cntFilterKeys = count($filter_keys);
for ($i = 0; $i < $cntFilterKeys; $i++)
{
$vals = $arFilter[$filter_keys[$i]];
if (!is_array($vals))
$vals = array($vals);
else
$vals = array_values($vals);
$key = $filter_keys[$i];
$key_res = self::GetFilterOperation($key);
$key = $key_res["FIELD"];
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$strOrNull = $key_res["OR_NULL"];
if (array_key_exists($key, $arFields))
{
$arSqlSearch_tmp = array();
$cntVals = count($vals);
for ($j = 0; $j < $cntVals; $j++)
{
$val = $vals[$j];
if (isset($arFields[$key]["WHERE"]))
{
$arSqlSearch_tmp1 = call_user_func_array(
$arFields[$key]["WHERE"],
array($val, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], $arFields, $arFilter)
);
if ($arSqlSearch_tmp1 !== false)
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
else
{
if ($arFields[$key]["TYPE"] == "int")
{
if ((IntVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".IntVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "double")
{
$val = str_replace(",", ".", $val);
if ((DoubleVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".DoubleVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
{
if ($strOperation == "QUERY")
{
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, "Y");
}
else
{
if ((strlen($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$DB->Length($arFields[$key]["FIELD"])." <= 0) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
}
}
elseif ($arFields[$key]["TYPE"] == "datetime")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
}
elseif ($arFields[$key]["TYPE"] == "date")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
}
}
}
if (isset($arFields[$key]["FROM"])
&& strlen($arFields[$key]["FROM"]) > 0
&& !in_array($arFields[$key]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$key]["FROM"];
$arAlreadyJoined[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
$cntSqlSearch_tmp = count($arSqlSearch_tmp);
for ($j = 0; $j < $cntSqlSearch_tmp; $j++)
{
if ($j > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arSqlSearch_tmp[$j].")";
}
if ($strOrNull == "Y")
{
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." IS ".($strNegative=="Y" ? "NOT " : "")."NULL)";
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
if ($arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." 0)";
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." '')";
else
$strSqlSearch_tmp .= ($strNegative=="Y" ? " (1=1) " : " (1=0) ");
}
if ($strSqlSearch_tmp != "")
$arSqlSearch[] = "(".$strSqlSearch_tmp.")";
}
}
$cntSqlSearch = count($arSqlSearch);
for ($i = 0; $i < $cntSqlSearch; $i++)
{
if (strlen($strSqlWhere) > 0)
$strSqlWhere .= " AND ";
$strSqlWhere .= "(".$arSqlSearch[$i].")";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = Array();
foreach ($arOrder as $by => $order)
{
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC")
$order = "DESC";
else
$order = "ASC";
if (array_key_exists($by, $arFields))
{
$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$order." ";
if (isset($arFields[$by]["FROM"])
&& strlen($arFields[$by]["FROM"]) > 0
&& !in_array($arFields[$by]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
}
}
DelDuplicateSort($arSqlOrder);
$cntSqlOrder = count($arSqlOrder);
for ($i=0; $i<$cntSqlOrder; $i++)
{
if (strlen($strSqlOrderBy) > 0)
$strSqlOrderBy .= ", ";
if(strtoupper($DB->type)=="ORACLE")
{
if(substr($arSqlOrder[$i], -3)=="ASC")
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS FIRST";
else
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS LAST";
}
else
$strSqlOrderBy .= $arSqlOrder[$i];
}
// <-- ORDER BY
return array(
"SELECT" => $strSqlSelect,
"FROM" => $strSqlFrom,
"WHERE" => $strSqlWhere,
"GROUPBY" => $strSqlGroupBy,
"ORDERBY" => $strSqlOrderBy
);
}
protected static function GetListUni($arOrder = array(), $arFilter = array(), $arSelectFields = array(), $mode = self::GET_LIST_ALL, $lessonId = -1, $arNavParams = array())
{
global $DB, $USER_FIELD_MANAGER;
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity('LEARNING_LESSONS', 'TL.ID');
$obUserFieldsSql->SetSelect($arSelectFields);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$bReplaceCourseId = false;
if (isset($arFilter['#REPLACE_COURSE_ID_TO_ID'])) {
$bReplaceCourseId = true;
unset($arFilter['#REPLACE_COURSE_ID_TO_ID']);
}
$oPermParser = new CLearnParsePermissionsFromFilter($arFilter);
// For ordering
$arMap = array('lesson_id' => 'TL.ID', 'site_id' => 'TL.ID', 'name' => 'TL.NAME', 'code' => 'TL.CODE', 'active' => 'TL.ACTIVE', 'created' => 'TL.DATE_CREATE', 'date_create' => 'TL.DATE_CREATE', 'created_by' => 'TL.CREATED_BY', 'timestamp_x' => 'TL.TIMESTAMP_X', 'course_id' => 'TC.ID', 'course_sort' => 'TC.SORT', 'active_from' => 'TC.ACTIVE_FROM', 'sort' => 'TC.SORT', 'linked_lesson_id' => 'TC.LINKED_LESSON_ID');
$allowedModes = array(self::GET_LIST_ALL, self::GET_LIST_IMMEDIATE_CHILDS_OF, self::GET_LIST_IMMEDIATE_PARENTS_OF, self::GET_LIST_IMMEDIATE_CHILDS_OF | self::GET_LIST_IMMEDIATE_PARENTS_OF);
$argsCheck = is_array($arOrder) && is_array($arSelectFields) && in_array($mode, $allowedModes, true) && self::_EnsureArgsStrictlyCastableToIntegers($lessonId);
if (!$argsCheck) {
throw new LearnException('EA_PARAMS', LearnException::EXC_ERR_ALL_PARAMS);
}
$arFieldsMap = array('LESSON_ID' => 'TL.ID', 'SITE_ID' => 'CASE WHEN (1 > 0) THEN \'no site\' ELSE \'0\' END', 'WAS_CHAPTER_ID' => 'TL.WAS_CHAPTER_ID', 'KEYWORDS' => 'TL.KEYWORDS', 'CHILDS_CNT' => '(SELECT COUNT(*) FROM b_learn_lesson_edges TLES WHERE TLES.SOURCE_NODE = TL.ID)', 'IS_CHILDS' => 'CASE WHEN (SELECT COUNT(*) FROM b_learn_lesson_edges TLES WHERE TLES.SOURCE_NODE = TL.ID) > 0 THEN \'1\' ELSE \'0\' END', 'SORT' => 'TC.SORT', 'TIMESTAMP_X' => $DB->DateToCharFunction('TL.TIMESTAMP_X'), 'DATE_CREATE' => $DB->DateToCharFunction('TL.DATE_CREATE'), 'CREATED_USER_NAME' => $DB->Concat("'('", 'TU.LOGIN', "') '", 'TU.NAME', "' '", 'TU.LAST_NAME'), 'CREATED_BY' => 'TL.CREATED_BY', 'ACTIVE' => 'TL.ACTIVE', 'NAME' => 'TL.NAME', 'PREVIEW_PICTURE' => 'TL.PREVIEW_PICTURE', 'PREVIEW_TEXT' => 'TL.PREVIEW_TEXT', 'PREVIEW_TEXT_TYPE' => 'TL.PREVIEW_TEXT_TYPE', 'DETAIL_TEXT' => 'TL.DETAIL_TEXT', 'DETAIL_PICTURE' => 'TL.DETAIL_PICTURE', 'DETAIL_TEXT_TYPE' => 'TL.DETAIL_TEXT_TYPE', 'LAUNCH' => 'TL.LAUNCH', 'CODE' => 'TL.CODE', 'ACTIVE_FROM' => $DB->DateToCharFunction('TC.ACTIVE_FROM'), 'ACTIVE_TO' => $DB->DateToCharFunction('TC.ACTIVE_TO'), 'RATING' => 'TC.RATING', 'RATING_TYPE' => 'TC.RATING_TYPE', 'SCORM' => 'TC.SCORM', 'LINKED_LESSON_ID' => 'TC.LINKED_LESSON_ID', 'COURSE_ID' => 'TC.ID', 'COURSE_SORT' => 'TC.SORT');
// filter by TIMESTAMP_X by default
if (count($arOrder) == 0) {
$arOrder['TIMESTAMP_X'] = 'DESC';
}
$arSqlSearch = self::GetFilter($arFilter, $mode);
if (isset($arFilter['SITE_ID'])) {
$arLID = array();
if (is_array($arFilter['SITE_ID'])) {
$arLID = $arFilter['SITE_ID'];
} else {
if (strlen($arFilter['SITE_ID']) > 0) {
$arLID[] = $arFilter['SITE_ID'];
}
}
$SqlSearchLang = "''";
foreach ($arLID as $v) {
$SqlSearchLang .= ", '" . $DB->ForSql($v) . "'";
}
}
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$arSqlSearch[] = "(" . $r . ")";
}
$sqlSearch = '';
foreach ($arSqlSearch as $value) {
if (strlen($value) > 0) {
$sqlSearch .= ' AND ' . $value;
}
}
$modeSQL_join = $modeSQL_where = '';
$modeSQL_defaultSortField = "TC.SORT";
// as SORT
// Prepare SQL's joins, if $mode need it
if ($mode & self::GET_LIST_IMMEDIATE_PARENTS_OF) {
$modeSQL_join .= "\nINNER JOIN b_learn_lesson_edges TLE \n\t\t\t\tON TLE.SOURCE_NODE = TL.ID\n";
$modeSQL_where .= "\nAND TLE.TARGET_NODE = " . ($lessonId + 0) . "\n";
$arFieldsMap['EDGE_SORT'] = 'TLE.SORT';
$arFieldsMap['SORT'] = 'TLE.SORT';
}
if ($mode & self::GET_LIST_IMMEDIATE_CHILDS_OF) {
/**
* GROUP BY works for MySQL, MSSQL, Oracle
* select a.id, a.NAME, count(b.USER_ID) as C
* from b_group a, b_user_group b
* where a.id = b.GROUP_ID
* group by a.id, a.NAME
* order by C
*/
$modeSQL_join .= "\nINNER JOIN b_learn_lesson_edges TLE \n\t\t\t\tON TLE.TARGET_NODE = TL.ID\n";
$modeSQL_where .= "\nAND TLE.SOURCE_NODE = " . ($lessonId + 0) . "\n";
$arMap['childs_cnt'] = 'CHILDS_CNT';
$arMap['is_childs'] = 'IS_CHILDS';
$arMap['edge_sort'] = 'TLE.SORT';
// Override default sort
$arMap['sort'] = $arMap['edge_sort'];
$modeSQL_defaultSortField = "TLE.SORT";
// as SORT
$arFieldsMap['EDGE_SORT'] = 'TLE.SORT';
$arFieldsMap['SORT'] = 'TLE.SORT';
}
if ($bReplaceCourseId) {
$arFieldsMap['ID'] = $arFieldsMap['COURSE_ID'];
}
// Select all fields by default
if (count($arSelectFields) == 0) {
$arSelectFields = array_keys($arFieldsMap);
}
// Ensure that all order fields will be selected
foreach ($arOrder as $by => $order) {
$fieldName = strtoupper($by);
if (!in_array($fieldName, $arSelectFields)) {
$arSelectFields[] = $fieldName;
}
}
// Build list of fields to be selected
$strSqlSelect = '';
$bFirstPass = true;
$bDefaultSortFieldSelected = false;
foreach ($arSelectFields as $selectFieldName) {
if (substr($selectFieldName, 0, 3) === 'UF_') {
continue;
}
if (!$bFirstPass) {
$strSqlSelect .= ', ';
} else {
$bFirstPass = false;
}
if (!isset($arFieldsMap[$selectFieldName])) {
throw new LearnException('EA_OTHER: UNKNOWN FIELD: ' . $selectFieldName, LearnException::EXC_ERR_ALL_GIVEUP);
}
$strSqlSelect .= $arFieldsMap[$selectFieldName] . ' AS ' . $selectFieldName;
if ($selectFieldName === 'SORT' && $arFieldsMap[$selectFieldName] === $modeSQL_defaultSortField) {
$bDefaultSortFieldSelected = true;
}
}
if (!$bDefaultSortFieldSelected) {
if ($strSqlSelect !== '') {
$strSqlSelect .= ', ';
}
$strSqlSelect .= $modeSQL_defaultSortField . ' AS SORT';
}
$strSqlSelect .= $obUserFieldsSql->GetSelect();
$sqlLangConstraint = '';
if (strlen($SqlSearchLang) > 2) {
$sqlLangConstraint = "\n\t\t\tAND\n\t\t\tEXISTS\n\t\t\t(\n\t\t\t\tSELECT 'x' FROM b_learn_course_site TCS\n\t\t\t\tWHERE TC.ID = TCS.COURSE_ID AND TCS.SITE_ID IN (" . $SqlSearchLang . ")\n\t\t\t)\n\t\t\t";
}
$strSqlFrom = "FROM b_learn_lesson TL\n\t\t\tLEFT JOIN b_learn_course TC \n\t\t\t\tON TC.LINKED_LESSON_ID = TL.ID\n\t\t\tLEFT JOIN b_user TU \n\t\t\t\tON TU.ID = TL.CREATED_BY " . $modeSQL_join . $obUserFieldsSql->GetJoin("TL.ID") . " WHERE 1 = 1 " . $sqlLangConstraint . $modeSQL_where;
// for getting only parents/childs, if need
if ($oPermParser->IsNeedCheckPerm()) {
$strSqlFrom .= " AND TL.ID IN (" . $oPermParser->SQLForAccessibleLessons() . ") ";
}
$strSqlFrom .= $sqlSearch;
$sql = "SELECT " . $strSqlSelect . " " . $strSqlFrom;
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order !== 'asc') {
$order = 'desc';
}
if ($s = $obUserFieldsSql->getOrder(strtolower($by))) {
$arSqlOrder[] = ' ' . $s . ' ' . $order . ' ';
}
if (substr($by, 0, 3) !== 'UF_') {
if (!isset($arMap[$by])) {
throw new LearnException('EA_PARAMS: unknown order by field: "' . $by . '"', LearnException::EXC_ERR_ALL_PARAMS);
}
}
$arSqlOrder[] = ' ' . $arMap[$by] . ' ' . $order . ' ';
}
// on duplicate first occured FIELD will be used according to function description
DelDuplicateSort($arSqlOrder);
$sql .= ' ORDER BY ' . implode(', ', $arSqlOrder);
if (is_array($arNavParams) && !empty($arNavParams)) {
if (isset($arNavParams['nTopCount']) && (int) $arNavParams['nTopCount'] > 0) {
$sql = $DB->TopSql($sql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($sql, true);
} else {
$res_cnt = $DB->Query("SELECT COUNT(TL.ID) as C " . $strSqlFrom);
$res_cnt = $res_cnt->fetch();
$res = new CDBResult();
$rc = $res->NavQuery($sql, $res_cnt['C'], $arNavParams, true);
if ($rc === false) {
throw new LearnException('EA_SQLERROR', LearnException::EXC_ERR_ALL_GIVEUP);
}
}
} else {
$res = $DB->Query($sql, true);
}
if ($res === false) {
throw new LearnException('EA_SQLERROR', LearnException::EXC_ERR_ALL_GIVEUP);
}
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields('LEARNING_LESSONS'));
return $res;
}
/**
*
* @param $arOrder
* @param $arFilter
* @return __CLDAPServerDBResult
*/
public static function GetList($arOrder = array(), $arFilter = array())
{
global $USER, $DB, $APPLICATION;
$strSql = "SELECT ls.*, " . "\t" . $DB->DateToCharFunction("ls.TIMESTAMP_X") . "\tas TIMESTAMP_X, " . "\t" . $DB->DateToCharFunction("ls.SYNC_LAST") . "\tas SYNC_LAST " . "FROM b_ldap_server ls ";
if (!is_array($arFilter)) {
$arFilter = array();
}
$arSqlSearch = array();
$filter_keys = array_keys($arFilter);
$fkCount = count($filter_keys);
for ($i = 0; $i < $fkCount; $i++) {
$val = $arFilter[$filter_keys[$i]];
$key = $filter_keys[$i];
$res = CLdapUtil::MkOperationFilter($key);
$key = strtoupper($res["FIELD"]);
$cOperationType = $res["OPERATION"];
switch ($key) {
case "ACTIVE":
case "SYNC":
case "CONVERT_UTF8":
case "USER_GROUP_ACCESSORY":
$arSqlSearch[] = CLdapUtil::FilterCreate("ls." . $key, $val, "string_equal", $cOperationType);
break;
case "ID":
case "PORT":
case "MAX_PAX_SIZE":
$arSqlSearch[] = CLdapUtil::FilterCreate("ls." . $key, $val, "number", $cOperationType);
break;
case "TIMESTAMP_X":
$arSqlSearch[] = CLdapUtil::FilterCreate("ls." . $key, $val, "date", $cOperationType);
break;
case "SYNC_LAST":
$arSqlSearch[] = CLdapUtil::FilterCreate("ls." . $key, $val, "date", $cOperationType);
break;
case "CODE":
case "NAME":
case "DESCRIPTION":
case "SERVER":
case "ADMIN_LOGIN":
case "ADMIN_PASSWORD":
case "BASE_DN":
case "GROUP_FILTER":
case "GROUP_ID_ATTR":
case "GROUP_NAME_ATTR":
case "GROUP_MEMBERS_ATTR":
case "USER_FILTER":
case "USER_ID_ATTR":
case "USER_NAME_ATTR":
case "USER_LAST_NAME_ATTR":
case "USER_EMAIL_ATTR":
case "USER_GROUP_ATTR":
$arSqlSearch[] = CldapUtil::FilterCreate("ls." . $key, $val, "string", $cOperationType);
break;
}
}
$is_filtered = false;
$strSqlSearch = "";
for ($i = 0, $ssCount = count($arSqlSearch); $i < $ssCount; $i++) {
if (strlen($arSqlSearch[$i]) > 0) {
$is_filtered = true;
$strSqlSearch .= " AND (" . $arSqlSearch[$i] . ") ";
}
}
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$order = strtolower($order);
if ($order != "asc") {
$order = "desc" . ($DB->type == "ORACLE" ? " NULLS LAST" : "");
} else {
$order = "asc" . ($DB->type == "ORACLE" ? " NULLS FIRST" : "");
}
switch (strtoupper($by)) {
case "ID":
case "NAME":
case "CODE":
case "ACTIVE":
case "CONVERT_UTF8":
case "SERVER":
case "PORT":
case "ADMIN_LOGIN":
case "ADMIN_PASSWORD":
case "BASE_DN":
case "GROUP_FILTER":
case "SYNC":
case "SYNC_LAST":
case "GROUP_ID_ATTR":
case "GROUP_NAME_ATTR":
case "GROUP_MEMBERS_ATTR":
case "USER_FILTER":
case "USER_ID_ATTR":
case "USER_NAME_ATTR":
case "USER_LAST_NAME_ATTR":
case "USER_EMAIL_ATTR":
case "USER_GROUP_ATTR":
case "USER_GROUP_ACCESSORY":
case "MAX_PAX_SIZE":
$arSqlOrder[] = " ls." . $by . " " . $order . " ";
break;
default:
$arSqlOrder[] = " ls.TIMESTAMP_X " . $order . " ";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0; $i < count($arSqlOrder); $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= strtolower($arSqlOrder[$i]);
}
$strSql .= " WHERE 1=1 " . $strSqlSearch . $strSqlOrder;
$res = $DB->Query($strSql);
$res = new __CLDAPServerDBResult($res);
return $res;
}
protected static function PrepareSql(&$arFields, $arOrder, &$arFilter, $arSelectFields = false, $arNavStartParams = false)
{
global $DB;
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$strSqlLimit = "";
$arGroupByFunct = array();
$arAlreadyJoined = array();
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
$arSelectFields = array($arSelectFields);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
foreach ($arFields as $key => $arField) {
if (isset($arField["WHERE_ONLY"]) && $arField["WHERE_ONLY"] == "Y") {
continue;
}
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if ($arField["FIELD_TYPE"] == "datetime") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($key, $arOrder)) {
$strSqlSelect .= $arField["FIELD_NAME"] . " as " . $key . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD_NAME"], "FULL") . " as " . $key;
} elseif ($arField["FIELD_TYPE"] == "date") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($key, $arOrder)) {
$strSqlSelect .= $arField["FIELD_NAME"] . " as " . $key . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD_NAME"], "SHORT") . " as " . $key;
} else {
$strSqlSelect .= $arField["FIELD_NAME"] . " as " . $key;
}
if (isset($arField["FROM"]) && strlen($arField["FROM"]) > 0 && !in_array($arField["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arField["FROM"];
$arAlreadyJoined[] = $arField["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD_NAME"] . ") as " . $val;
} else {
if ($arFields[$val]["FIELD_TYPE"] == "datetime") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD_NAME"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["FIELD_TYPE"] == "date") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD_NAME"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD_NAME"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD_NAME"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- SELECT
// WHERE -->
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$strSqlWhere = $obWhere->GetQuery($arFilter);
// ORDER BY -->
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
} else {
$order = "ASC";
}
if (array_key_exists($by, $arFields)) {
$arSqlOrder[] = " " . $arFields[$by]["FIELD_NAME"] . " " . $order . " ";
if (isset($arFields[$by]["FROM"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
}
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$cnt = count($arSqlOrder);
for ($i = 0; $i < $cnt; $i++) {
if (strlen($strSqlOrderBy) > 0) {
$strSqlOrderBy .= ", ";
}
if (strtoupper($DB->type) == "ORACLE") {
if (substr($arSqlOrder[$i], -3) == "ASC") {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS FIRST";
} else {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS LAST";
}
} else {
$strSqlOrderBy .= $arSqlOrder[$i];
}
}
// <-- ORDER BY
if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) {
$dbType = strtoupper($DB->type);
switch ($dbType) {
case "MSSQL":
$strSqlSelect = "TOP " . $arNavStartParams["nTopCount"] . " " . $strSqlSelect;
break;
case "ORACLE":
if (strlen($strSqlWhere) > 0) {
$strSqlWhere .= " AND ";
}
$strSqlWhere .= "ROWNUM<=" . $arNavStartParams["nTopCount"];
break;
case "MYSQL":
$strSqlLimit .= "LIMIT " . $arNavStartParams["nTopCount"];
}
}
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy, "LIMIT" => $strSqlLimit);
}
function GetList($arOrder=Array(), $arFilter=Array())
{
global $DB;
$strSql =
"SELECT ML.*, MB.NAME as MAILBOX_NAME, ".
" MF.NAME as FILTER_NAME, ".
" MM.SUBJECT as MESSAGE_SUBJECT, ".
" ".$DB->DateToCharFunction("ML.DATE_INSERT")." as DATE_INSERT ".
" ".
"FROM b_mail_log ML ".
" INNER JOIN b_mail_mailbox MB ON MB.ID=ML.MAILBOX_ID ".
" LEFT JOIN b_mail_filter MF ON MF.ID=ML.FILTER_ID ".
" LEFT JOIN b_mail_message MM ON MM.ID=ML.MESSAGE_ID ";
if(!is_array($arFilter))
$arFilter = Array();
$arSqlSearch = Array();
$filter_keys = array_keys($arFilter);
for($i = 0, $n = count($filter_keys); $i < $n; $i++)
{
$val = $arFilter[$filter_keys[$i]];
if (strlen($val)<=0) continue;
$key = strtoupper($filter_keys[$i]);
switch($key)
{
case "ID":
case "MAILBOX_ID":
case "FILTER_ID":
case "MESSAGE_ID":
case "LOG_TYPE":
case "STATUS_GOOD":
$arSqlSearch[] = GetFilterQuery("ML.".$key, $val, "N");
break;
case "MESSAGE":
$arSqlSearch[] = GetFilterQuery("ML.".$key, $val);
break;
case "FILTER_NAME":
$arSqlSearch[] = GetFilterQuery("MF.NAME", $val);
break;
case "MAILBOX_NAME":
$arSqlSearch[] = GetFilterQuery("MB.NAME", $val);
break;
case "MESSAGE_SUBJECT":
$arSqlSearch[] = GetFilterQuery("MM.SUBJECT", $val);
break;
}
}
$is_filtered = false;
$strSqlSearch = "";
for($i = 0, $n = count($arSqlSearch); $i < $n; $i++)
{
if(strlen($arSqlSearch[$i])>0)
{
$strSqlSearch .= " AND (".$arSqlSearch[$i].") ";
$is_filtered = true;
}
}
$arSqlOrder = Array();
foreach($arOrder as $by=>$order)
{
$order = strtolower($order);
if ($order!="asc")
$order = "desc".(strtoupper($DB->type)=="ORACLE"?" NULLS LAST":"");
else
$order = "asc".(strtoupper($DB->type)=="ORACLE"?" NULLS FIRST":"");
switch(strtoupper($by))
{
case "ID":
case "MAILBOX_ID":
case "FILTER_ID":
case "MESSAGE_ID":
case "DATE_INSERT":
case "LOG_TYPE":
case "STATUS_GOOD":
case "MESSAGE":
$arSqlOrder[] = " ML.".$by." ".$order." ";
case "MESSAGE_SUBJECT":
$arSqlOrder[] = " MM.SUBJECT ".$order." ";
case "FILTER_NAME":
$arSqlOrder[] = " MF.NAME ".$order." ";
case "MAILBOX_NAME":
$arSqlOrder[] = " MB.NAME ".$order." ";
default:
$arSqlOrder[] = " ML.ID ".$order." ";
}
}
$strSqlOrder = "";
$arSqlOrder = array_unique($arSqlOrder);
DelDuplicateSort($arSqlOrder);
for ($i = 0, $n = count($arSqlOrder); $i < $n; $i++)
{
if($i==0)
$strSqlOrder = " ORDER BY ";
else
$strSqlOrder .= ",";
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= " WHERE 1=1 ".$strSqlSearch.$strSqlOrder;
$res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
$res = new _CMailLogDBRes($res);
$res->is_filtered = $is_filtered;
return $res;
}
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;
}
function GetList($arOrder = array(), $arFilter = array())
{
global $DB, $USER;
if (!is_array($arFilter)) {
$arFilter = array();
}
$oPermParser = new CLearnParsePermissionsFromFilter($arFilter);
$arSqlSearch = CTest::GetFilter($arFilter);
$strSqlSearch = "";
for ($i = 0; $i < count($arSqlSearch); $i++) {
if (strlen($arSqlSearch[$i]) > 0) {
$strSqlSearch .= " AND " . $arSqlSearch[$i] . " ";
}
}
$strSql = "SELECT DISTINCT T.*, " . $DB->DateToCharFunction("T.TIMESTAMP_X") . " as TIMESTAMP_X " . "FROM b_learn_test T " . "INNER JOIN b_learn_course C ON T.COURSE_ID = C.ID " . "WHERE 1=1 ";
if ($oPermParser->IsNeedCheckPerm()) {
$strSql .= " AND C.LINKED_LESSON_ID IN (" . $oPermParser->SQLForAccessibleLessons() . ") ";
}
$strSql .= $strSqlSearch;
/* was:
$bCheckPerm = ($APPLICATION->GetUserRight("learning") < "W" && !$USER->IsAdmin() && $arFilter["CHECK_PERMISSIONS"] != "N");
$userID = $USER->GetID() ? $USER->GetID() : 0;
$strSql =
"SELECT DISTINCT T.*, ".
$DB->DateToCharFunction("T.TIMESTAMP_X")." as TIMESTAMP_X ".
"FROM b_learn_test T ".
"INNER JOIN b_learn_course C ON T.COURSE_ID = C.ID ".
($bCheckPerm ?
"LEFT JOIN b_learn_course_permission CP ON CP.COURSE_ID = C.ID "
: "").
"WHERE 1=1 ".
($bCheckPerm ?
"AND CP.USER_GROUP_ID IN (".$USER->GetGroups().") ".
"AND CP.PERMISSION >= '".(strlen($arFilter["MIN_PERMISSION"])==1 ? $arFilter["MIN_PERMISSION"] : "R")."' ".
"AND (CP.PERMISSION='X' OR C.ACTIVE='Y') "
:"").
$strSqlSearch;
*/
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[] = " T.ID " . $order . " ";
} elseif ($by == "name") {
$arSqlOrder[] = " T.NAME " . $order . " ";
} elseif ($by == "active") {
$arSqlOrder[] = " T.ACTIVE " . $order . " ";
} elseif ($by == "sort") {
$arSqlOrder[] = " T.SORT " . $order . " ";
} else {
$arSqlOrder[] = " T.TIMESTAMP_X " . $order . " ";
$by = "timestamp_x";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0; $i < count($arSqlOrder); $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
//echo $strSql;
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
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;
}
protected static function _GetList($arOrder = array(), $arFilter = array(), $arSelect = array(), $arCallbackSqlFormer)
{
global $DB, $USER, $APPLICATION, $USER_FIELD_MANAGER;
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("LEARN_ATTEMPT", "A.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$arFields = array("ID" => "A.ID", "TEST_ID" => "A.TEST_ID", "OBY_DATE_END" => "A.DATE_END", "STUDENT_ID" => "A.STUDENT_ID", "DATE_START" => $DB->DateToCharFunction("A.DATE_START", "FULL"), "DATE_END" => $DB->DateToCharFunction("A.DATE_END", "FULL"), "STATUS" => "A.STATUS", "COMPLETED" => "A.COMPLETED", "SCORE" => "A.SCORE", "MAX_SCORE" => "A.MAX_SCORE", "QUESTIONS" => "A.QUESTIONS", "TEST_NAME" => "T.NAME", "USER_NAME" => $DB->Concat("'('", 'U.LOGIN', "') '", "CASE WHEN U.NAME IS NULL THEN '' ELSE U.NAME END", "' '", "CASE WHEN U.LAST_NAME IS NULL THEN '' ELSE U.LAST_NAME END"), "USER_ID" => "U.ID", "MARK" => "TM.MARK", "MESSAGE" => "TM.DESCRIPTION");
if (count($arSelect) <= 0 || in_array("*", $arSelect)) {
$arSelect = array_keys($arFields);
}
$arSqlSelect = array();
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . " AS " . $field;
}
}
$sSelect = implode(",\n", $arSqlSelect);
if (!is_array($arFilter)) {
$arFilter = array();
}
$arSqlSearch = CTestAttempt::GetFilter($arFilter);
$strSqlSearch = "";
$arSqlSearchCnt = count($arSqlSearch);
for ($i = 0; $i < $arSqlSearchCnt; $i++) {
if (strlen($arSqlSearch[$i]) > 0) {
$strSqlSearch .= " AND " . $arSqlSearch[$i] . " ";
}
}
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strSqlSearch .= " AND (" . $r . ") ";
}
$bCheckPerm = 'ORPHANED VAR';
$strSql = call_user_func($arCallbackSqlFormer, $sSelect, $obUserFieldsSql, $bCheckPerm, $USER, $arFilter, $strSqlSearch);
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[] = " A.ID " . $order . " ";
} elseif ($by == "test_id") {
$arSqlOrder[] = " A.TEST_ID " . $order . " ";
} elseif ($by == "student_id") {
$arSqlOrder[] = " A.STUDENT_ID " . $order . " ";
} elseif ($by == "date_start") {
$arSqlOrder[] = " A.DATE_START " . $order . " ";
} elseif ($by == "date_end") {
$arSqlOrder[] = " A.DATE_END " . $order . " ";
} elseif ($by == "status") {
$arSqlOrder[] = " A.STATUS " . $order . " ";
} elseif ($by == "score") {
$arSqlOrder[] = " A.SCORE " . $order . " ";
} elseif ($by == "max_score") {
$arSqlOrder[] = " A.MAX_SCORE " . $order . " ";
} elseif ($by == "completed") {
$arSqlOrder[] = " A.COMPLETED " . $order . " ";
} elseif ($by == "questions") {
$arSqlOrder[] = " A.QUESTIONS " . $order . " ";
} elseif ($by == "user_name") {
$arSqlOrder[] = " USER_NAME " . $order . " ";
} elseif ($by == "test_name") {
$arSqlOrder[] = " TEST_NAME " . $order . " ";
} elseif ($s = $obUserFieldsSql->GetOrder($by)) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
} else {
$arSqlOrder[] = " A.ID " . $order . " ";
$by = "id";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$arSqlOrderCnt = count($arSqlOrder);
for ($i = 0; $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
//echo $strSql;
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("LEARN_ATTEMPT"));
return $res;
}
public static function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arUF = array())
{
global $DB;
$obUserFieldsSql = false;
if (is_array($arUF) && array_key_exists("ENTITY_ID", $arUF))
{
$obUserFieldsSql = new CUserTypeSQL;
$obUserFieldsSql->SetEntity($arUF["ENTITY_ID"], $arFields["ID"]["FIELD"]);
$obUserFieldsSql->SetSelect($arSelectFields);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
}
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy)>0)
{
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct))
{
if (strlen($strSqlGroupBy) > 0)
$strSqlGroupBy .= ", ";
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// WHERE -->
$arSqlSearch = Array();
if (!is_array($arFilter))
$filter_keys = Array();
else
$filter_keys = array_keys($arFilter);
$tmp_count = count($filter_keys);
for ($i = 0; $i < $tmp_count; $i++)
{
$vals = $arFilter[$filter_keys[$i]];
if (!is_array($vals))
$vals = array($vals);
$key = $filter_keys[$i];
$key_res = CSocNetGroup::GetFilterOperation($key);
$key = $key_res["FIELD"];
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$strOrNull = $key_res["OR_NULL"];
if (array_key_exists($key, $arFields))
{
$arSqlSearch_tmp = array();
foreach ($vals as $val)
{
if (isset($arFields[$key]["WHERE"]))
{
$arSqlSearch_tmp1 = call_user_func_array(
$arFields[$key]["WHERE"],
array($val, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], &$arFields, &$arFilter)
);
if ($arSqlSearch_tmp1 !== false)
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
else
{
if ($arFields[$key]["TYPE"] == "int")
{
if ((IntVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".IntVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "double")
{
$val = str_replace(",", ".", $val);
if ((DoubleVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".DoubleVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
{
if ($strOperation == "QUERY")
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, (strpos($val, "%") === false ? "Y" : "N"));
else
{
if ((strlen($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$DB->Length($arFields[$key]["FIELD"])." <= 0) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
}
}
elseif ($arFields[$key]["TYPE"] == "string_or_null" || $arFields[$key]["TYPE"] == "char_or_null")
{
if ($strOperation == "QUERY")
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, (strpos($val, "%") === false ? "Y" : "N"));
else
{
if ((strlen($val) == 0) && (strpos($strOperation, "=") !== False))
{
// future functionality
}
else
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' OR ".$arFields[$key]["FIELD"]." IS NULL)";
}
}
elseif ($arFields[$key]["TYPE"] == "datetime")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
elseif (strtoupper($val) === "NOW")
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->GetNowFunction().")";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
}
elseif ($arFields[$key]["TYPE"] == "date")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
}
}
}
if (isset($arFields[$key]["FROM"])
&& strlen($arFields[$key]["FROM"]) > 0
&& !in_array($arFields[$key]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$key]["FROM"];
$arAlreadyJoined[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
$tmp_count_1 = count($arSqlSearch_tmp);
for ($j = 0; $j < $tmp_count_1; $j++)
{
if ($j > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arSqlSearch_tmp[$j].")";
}
if ($strOrNull == "Y")
{
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." IS ".($strNegative=="Y" ? "NOT " : "")."NULL)";
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
if ($arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." 0)";
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." '')";
}
if ($strSqlSearch_tmp != "")
$arSqlSearch[] = "(".$strSqlSearch_tmp.")";
}
}
$tmp_count = count($arSqlSearch);
for ($i = 0; $i < $tmp_count; $i++)
{
if (strlen($strSqlWhere) > 0)
$strSqlWhere .= " AND ";
$strSqlWhere .= "(".$arSqlSearch[$i].")";
}
if ($obUserFieldsSql)
{
$r = $obUserFieldsSql->GetFilter();
if(strlen($r) > 0)
$strSqlWhere .= (strlen($strSqlWhere) > 0 ? " AND" : "")." (".$r.") ";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = Array();
foreach ($arOrder as $by => $order)
{
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC")
$order = "DESC";
else
$order = "ASC";
if (array_key_exists($by, $arFields))
{
if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date")
{
$arSqlOrder[] = " ".$by."_X1 ".$order." ";
if (!is_array($arSelectFields) || !in_array($by, $arSelectFields))
$arSelectFields[] = $by;
}
else
$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$order." ";
if (isset($arFields[$by]["FROM"])
&& strlen($arFields[$by]["FROM"]) > 0
&& !in_array($arFields[$by]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
}
elseif($obUserFieldsSql && $s = $obUserFieldsSql->GetOrder($by))
$arSqlOrder[$by] = " ".$s." ".$order." ";
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$tmp_count = count($arSqlOrder);
for ($i=0; $i < $tmp_count; $i++)
{
if (strlen($strSqlOrderBy) > 0)
$strSqlOrderBy .= ", ";
if(strtoupper($DB->type)=="ORACLE")
{
if(substr($arSqlOrder[$i], -3)=="ASC")
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS FIRST";
else
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS LAST";
}
else
$strSqlOrderBy .= $arSqlOrder[$i];
}
// <-- ORDER BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy)==0)
$strSqlSelect = "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT ";
else
{
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields)>0 && array_key_exists($arSelectFields, $arFields))
$arSelectFields = array($arSelectFields);
if (!isset($arSelectFields)
|| !is_array($arSelectFields)
|| count($arSelectFields) <= 0
|| in_array("*", $arSelectFields))
{
$tmp_count = count($arFieldsKeys);
for ($i = 0; $i < $tmp_count; $i++)
{
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"])
&& $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y")
continue;
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime")
{
if (array_key_exists($arFieldsKeys[$i], $arOrder))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL")." as ".$arFieldsKeys[$i];
}
elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date")
{
if (array_key_exists($arFieldsKeys[$i], $arOrder))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT")." as ".$arFieldsKeys[$i];
}
else
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i];
if (isset($arFields[$arFieldsKeys[$i]]["FROM"])
&& strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0
&& !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
}
else
{
foreach ($arSelectFields as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields))
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if (in_array($key, $arGroupByFunct))
$strSqlSelect .= $key."(".$arFields[$val]["FIELD"].") as ".$val;
else
{
if ($arFields[$val]["TYPE"] == "datetime")
{
if (array_key_exists($val, $arOrder))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
}
elseif ($arFields[$val]["TYPE"] == "date")
{
if (array_key_exists($val, $arOrder))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT")." as ".$val;
}
else
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val;
}
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if ($obUserFieldsSql)
$strSqlSelect .= (strlen($strSqlSelect) <= 0 ? $arFields["ID"]["FIELD"] : "").$obUserFieldsSql->GetSelect();
if (strlen($strSqlGroupBy) > 0)
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
$strSqlSelect .= "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT";
}
else
$strSqlSelect = "%%_DISTINCT_%% ".$strSqlSelect;
}
// <-- SELECT
if ($obUserFieldsSql)
$strSqlFrom .= " ".$obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
return array(
"SELECT" => $strSqlSelect,
"FROM" => $strSqlFrom,
"WHERE" => $strSqlWhere,
"GROUPBY" => $strSqlGroupBy,
"ORDERBY" => $strSqlOrderBy
);
}
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 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>Возвращает список сертификатов по фильтру <b>arFilter</b>, отсортированный в порядке <b>arOrder</b>. Учитываются права доступа текущего пользователя.</p>
*
*
* @param array $arrayarOrder = Array("ID"=>"DESC") Массив для сортировки результата. Массив вида <i>array("поле
* сортировки"=>"направление сортировки" [, ...])</i>. <br> Поле для
* сортировки может принимать значения: <ul> <li> <b>ID</b> - идентификатор
* сертификата; </li> <li> <b>STUDENT_ID</b> - идентификатор студента ; </li> <li>
* <b>COURSE_ID</b> - идентификатор курса; </li> <li> <b>SUMMARY</b> - cумма баллов,
* набранных за прохождение всех тестов курса; </li> <li> <b>MAX_SUMMARY</b> -
* максимально возможная сумма баллов за прохождение всех тестов
* курса; </li> <li> <b>SORT</b> - индекс сортировки; </li> <li> <b>ACTIVE</b> - фильтр по
* активности (Y|N); </li> <li> <b>FROM_ONLINE</b> - сертификат получен через
* online-обучение (Y/N); </li> <li> <b>PUBLIC_PROFILE</b> - публиковать сертификат в
* профиле (Y/N); </li> <li> <b>DATE_CREATE</b> - дата создания сертификата; </li> <li>
* <b>TIMESTAMP_X</b> - дата изменения студента. </li> </ul> Направление
* сортировки может принимать значения: <ul> <li> <b>asc</b> - по возрастанию;
* </li> <li> <b>desc</b> - по убыванию; </li> </ul> Необязательный. По умолчанию
* фильтруется по убыванию идентификатора сертификата.
*
* @param array $arrayarFilter = Array() Массив вида <i>array("фильтруемое поле"=>"значение фильтра" [, ...])</i>.
* Фильтруемое поле может принимать значения: <ul> <li> <b>ID</b> -
* идентификатор сертификата; </li> <li> <b>STUDENT_ID</b> - идентификатор
* студента ; </li> <li> <b>COURSE_ID</b> - идентификатор курса; </li> <li> <b>SUMMARY</b> -
* cумма баллов, набранных за прохождение всех тестов курса; </li> <li>
* <b>MAX_SUMMARY</b> - максимально возможная сумма баллов за прохождение
* всех тестов курса; </li> <li> <b>SORT</b> - индекс сортировки; </li> <li> <b>ACTIVE</b> -
* фильтр по активности (Y|N); </li> <li> <b>FROM_ONLINE</b> - сертификат получен
* через online-обучение (Y/N); </li> <li> <b>PUBLIC_PROFILE</b> - публиковать сертификат
* в профиле (Y/N); </li> <li> <b>DATE_CREATE</b> - дата создания сертификата; </li> <li>
* <b>TIMESTAMP_X</b> - дата изменения студента. </li> <li> <b>USER</b> - пользователь
* (возможны сложные условия по полям пользователя ID, LOGIN, NAME, LAST_NAME);
* </li> <li> <b>MIN_PERMISSION</b> - минимальный уровень доступа. По умолчанию "R".
* Список прав доступа см. в <a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/ccourse/setpermission.php">CCourse::SetPermission</a>. </li>
* <li> <b>CHECK_PERMISSIONS</b> - проверять уровень доступа. Если установлено
* значение "N" - права доступа не проверяются. </li> </ul> Перед названием
* фильтруемого поля можно указать тип фильтрации: <ul> <li>"!" - не равно
* </li> <li>"<" - меньше </li> <li>"<=" - меньше либо равно </li> <li>">" - больше
* </li> <li>">=" - больше либо равно </li> </ul> <br> "<i>значения фильтра</i>" -
* одиночное значение или массив. <br><br> Необязательный. По умолчанию
* записи не фильтруются.
*
* @return CDBResult <p>Возвращается объект <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>.</p> </h
*
* <h4>Example</h4>
* <pre>
* <?<br>if (CModule::IncludeModule("learning"))<br>{<br> $COURSE_ID = 100;<br> $res = CCertification::GetList(<br> Array("SUMMARY" => "DESC", "SORT"=>"ASC"), <br> Array("ACTIVE" => "Y", "COURSE_ID" => $COURSE_ID)<br> );<br><br> while ($arCertification = $res->GetNext())<br> {<br> echo "User:"******"USER_NAME"].<br> "; Course name: ".$arCertification["COURSE_NAME"]."<br>";<br> }<br>}<br>?><?<br>if (CModule::IncludeModule("learning"))<br>{<br> $COURSE_ID = 100;<br> $res = CCertification::GetList(<br> Array("SUMMARY" => "DESC", "SORT"=>"ASC"), <br> Array("ACTIVE" => "Y", "CHECK_PERMISSIONS" => "N")<br> );<br><br> while ($arCertification = $res->GetNext())<br> {<br> echo "User:"******"USER_NAME"].<br> "; Course name: ".$arCertification["COURSE_NAME"]."<br>";<br> }<br>}<br>?>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li><a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a></li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/ccertification/index.php">CCertification</a>::<a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/ccertification/getbyid.php">GetByID</a> </li> <li><a
* href="http://dev.1c-bitrix.ru/api_help/learning/fields.php">Поля сертификата</a></li> </ul> </ht<a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/learning/classes/ccertification/getlist.php
* @author Bitrix
*/
public static function GetList($arOrder = array(), $arFilter = array(), $arNavParams = array())
{
global $DB;
$oPermParser = new CLearnParsePermissionsFromFilter($arFilter);
$arSqlSearch = CCertification::GetFilter($arFilter);
$strSqlSearch = "";
if (!empty($arSqlSearch)) {
$arSqlSearch = array_filter($arSqlSearch);
if (!empty($arSqlSearch)) {
$strSqlSearch .= ' AND ' . implode(' AND ', $arSqlSearch);
}
}
$strSql = "SELECT CER.*, C.NAME as COURSE_NAME, COURSEOLD.ID as COURSE_ID, " . "COURSEOLD.ACTIVE_FROM as ACTIVE_FROM, COURSEOLD.ACTIVE_TO as ACTIVE_TO, COURSEOLD.RATING as RATING, " . "COURSEOLD.RATING_TYPE as RATING_TYPE, COURSEOLD.SCORM as SCORM, " . $DB->Concat("'('", 'U.LOGIN', "') '", "CASE WHEN U.NAME IS NULL THEN '' ELSE U.NAME END", "' '", "CASE WHEN U.LAST_NAME IS NULL THEN '' ELSE U.LAST_NAME END") . " as USER_NAME, U.ID as USER_ID, " . $DB->DateToCharFunction("CER.TIMESTAMP_X") . " as TIMESTAMP_X, " . $DB->DateToCharFunction("CER.DATE_CREATE") . " as DATE_CREATE ";
$strSqlFrom = "FROM b_learn_certification CER " . "INNER JOIN b_learn_course COURSEOLD ON CER.COURSE_ID = COURSEOLD.ID " . "INNER JOIN b_learn_lesson C ON C.ID = COURSEOLD.LINKED_LESSON_ID " . "INNER JOIN b_user U ON U.ID = CER.STUDENT_ID " . "WHERE 1=1 ";
if ($oPermParser->IsNeedCheckPerm()) {
$strSqlFrom .= " AND C.ID IN (" . $oPermParser->SQLForAccessibleLessons() . ") ";
}
$strSqlFrom .= $strSqlSearch;
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[] = " CER.ID " . $order . " ";
} elseif ($by == "student_id") {
$arSqlOrder[] = " CER.STUDENT_ID " . $order . " ";
} elseif ($by == "course_id") {
$arSqlOrder[] = " CER.COURSE_ID " . $order . " ";
} elseif ($by == "summary") {
$arSqlOrder[] = " CER.SUMMARY " . $order . " ";
} elseif ($by == "sort") {
$arSqlOrder[] = " CER.SORT " . $order . " ";
} elseif ($by == "active") {
$arSqlOrder[] = " CER.ACTIVE " . $order . " ";
} elseif ($by == "from_online") {
$arSqlOrder[] = " CER.FROM_ONLINE " . $order . " ";
} elseif ($by == "public") {
$arSqlOrder[] = " CER.PUBLIC " . $order . " ";
} elseif ($by == "public_profile") {
$arSqlOrder[] = " CER.PUBLIC " . $order . " ";
} elseif ($by == "date_create") {
$arSqlOrder[] = " CER.DATE_CREATE " . $order . " ";
} elseif ($by == "summary") {
$arSqlOrder[] = " CER.SUMMARY " . $order . " ";
} elseif ($by == "max_summary") {
$arSqlOrder[] = " CER.MAX_SUMMARY " . $order . " ";
} elseif ($by == "timestamp_x") {
$arSqlOrder[] = " CER.TIMESTAMP_X " . $order . " ";
} else {
$arSqlOrder[] = " CER.ID " . $order . " ";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
if (!empty($arSqlOrder)) {
$strSqlOrder .= " ORDER BY " . implode(', ', $arSqlOrder);
}
$strSql .= $strSqlFrom . $strSqlOrder;
if (is_array($arNavParams) && !empty($arNavParams)) {
if (isset($arNavParams['nTopCount']) && (int) $arNavParams['nTopCount'] > 0) {
$strSql = $DB->TopSql($strSql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
} else {
$res_cnt = $DB->Query("SELECT COUNT(CER.ID) as CNT " . $strSqlFrom, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res_cnt = $res_cnt->fetch();
$res = new CDBResult();
$rc = $res->NavQuery($strSql, $res_cnt['CNT'], $arNavParams, true);
if ($rc === false) {
throw new LearnException('EA_SQLERROR', LearnException::EXC_ERR_ALL_GIVEUP);
}
}
} else {
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
return $res;
}
/**
* <p>Возвращает список вопросов плана тестирования по фильтру <b>arFilter</b>, отсортированный в порядке <b>arOrder</b>.</p>
*
*
* @param array $arrayarOrder = Array("ID"=>"DESC") Массив для сортировки результата. Массив вида <i>array("поле
* сортировки"=>"направление сортировки" [, ...])</i>.<br>Поле для
* сортировки может принимать значения: <ul> <li> <b>ID</b> - идентификатор
* вопроса в плане тестирования; </li> <li> <b>ATTEMPT_ID</b> - идентификатор
* попытки; </li> <li> <b>QUESTION_ID</b> - идентификатор вопроса; </li> <li> <b>POINT</b> -
* количество баллов; </li> <li> <b>ANSWERED</b> - вопрос отвечен (Y|N); </li> <li>
* <b>CORRECT</b> - вопрос правильно отвечен (Y|N); </li> <li> <b>QUESTION_NAME</b> -
* название вопроса; </li> <li> <b>RAND</b> - случайный порядок. </li>
* </ul>Направление сортировки может принимать значения: <ul> <li> <b>asc</b> -
* по возрастанию; </li> <li> <b>desc</b> - по убыванию; </li> </ul>Необязательный.
* По умолчанию сортируется по убыванию идентификатора вопроса в
* плане тестирования.
*
* @param array $arrayarFilter = Array() Массив вида <i>array("фильтруемое поле"=>"значение фильтра" [, ...])</i>.
* Фильтруемое поле может принимать значения: <ul> <li> <b>ID</b> -
* идентификатор вопроса в плане тестирования; </li> <li> <b>ATTEMPT_ID</b> -
* идентификатор попытки; </li> <li> <b>QUESTION_ID</b> - идентификатор вопроса;
* </li> <li> <b>POINT</b> - количество баллов; </li> <li> <b>RESPONSE</b> - ответ учащегося
* (можно искать по шаблону [%_]); </li> <li> <b>QUESTION_NAME</b> - название вопроса
* (можно искать по шаблону [%_]); </li> <li> <b>ANSWERED</b> - вопрос отвечен (Y|N);
* </li> <li> <b>CORRECT</b> - вопрос правильно отвечен (Y|N). </li> </ul>Перед
* названием фильтруемого поля может указать тип фильтрации: <ul> <li>"!"
* - не равно </li> <li>"<" - меньше </li> <li>"<=" - меньше либо равно </li> <li>">"
* - больше </li> <li>">=" - больше либо равно </li> </ul> <br>"<i>значения
* фильтра</i>" - одиночное значение или массив.<br><br>Необязательный. По
* умолчанию записи не фильтруются.
*
* @return CDBResult <p>Возвращается объект <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>.</p> </h
*
* <h4>Example</h4>
* <pre>
* <?
* if (CModule::IncludeModule("learning"))
* {
* $ATTEMPT_ID = 590;
* $res = CTestResult::GetList(
* Array("ID" => "ASC"),
* Array("ANSWERED" => "N", "ATTEMPT_ID" => $ATTEMPT_ID)
* );
*
* while ($arQuestionPlan = $res->GetNext())
* {
* echo "Question ID: ".$arQuestionPlan["QUESTION_ID"].<br> "; Correct answer: ".$arQuestionPlan["CORRECT"].<br> "; Question name:".$arQuestionPlan["QUESTION_NAME"]."<b
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> </li> <li> <a
* href="index.php">CTestResult</a>::<a href="getbyid.php">GetByID</a> </li> <li><a
* href="../../fields.php#test_result">Поля плана тестирования</a></li> </ul> <a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/learning/classes/ctestresult/getlist.php
* @author Bitrix
*/
public static function GetList($arOrder = array(), $arFilter = array(), $arNavParams = array())
{
global $DB, $USER, $APPLICATION;
if (!is_array($arFilter)) {
$arFilter = array();
}
$oPermParser = new CLearnParsePermissionsFromFilter($arFilter);
$arSqlSearch = CTestResult::GetFilter($arFilter);
// Remove empty strings from array
$arSqlSearch = array_filter($arSqlSearch);
if ($oPermParser->IsNeedCheckPerm()) {
$arSqlSearch[] = " L.ID IN (" . $oPermParser->SQLForAccessibleLessons() . ") ";
}
$strSqlSearch = ' ';
if (!empty($arSqlSearch)) {
$strSqlSearch = ' WHERE ';
$strSqlSearch .= implode(' AND ', $arSqlSearch);
}
$strSqlFrom = "FROM b_learn_test_result TR \n\t\t\tINNER JOIN b_learn_question Q ON TR.QUESTION_ID = Q.ID \n\t\t\tINNER JOIN b_learn_lesson L ON Q.LESSON_ID = L.ID " . $strSqlSearch;
$strSql = "SELECT TR.*, Q.QUESTION_TYPE, Q.NAME as QUESTION_NAME, \n\t\t\tQ.POINT as QUESTION_POINT, Q.LESSON_ID " . $strSqlFrom;
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[] = " TR.ID " . $order . " ";
} elseif ($by == "attempt_id") {
$arSqlOrder[] = " TR.ATTEMPT_ID " . $order . " ";
} elseif ($by == "question_id") {
$arSqlOrder[] = " TR.QUESTION_ID " . $order . " ";
} elseif ($by == "point") {
$arSqlOrder[] = " TR.POINT " . $order . " ";
} elseif ($by == "correct") {
$arSqlOrder[] = " TR.CORRECT " . $order . " ";
} elseif ($by == "answered") {
$arSqlOrder[] = " TR.ANSWERED " . $order . " ";
} elseif ($by == "question_name") {
$arSqlOrder[] = " QUESTION_NAME " . $order . " ";
} elseif ($by == "rand") {
$arSqlOrder[] = CTest::GetRandFunction();
} else {
$arSqlOrder[] = " TR.ID " . $order . " ";
$by = "id";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$arSqlOrderCnt = count($arSqlOrder);
for ($i = 0; $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
if (is_array($arNavParams) && !empty($arNavParams)) {
if (isset($arNavParams['nTopCount']) && (int) $arNavParams['nTopCount'] > 0) {
$strSql = $DB->TopSql($strSql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
} else {
$res_cnt = $DB->Query("SELECT COUNT(TR.ID) as C " . $strSqlFrom);
$res_cnt = $res_cnt->fetch();
$res = new CDBResult();
$res->NavQuery($strSql, $res_cnt['C'], $arNavParams);
}
} else {
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
return $res;
}
function GetListEx($arOrder = array("ID" => "ASC"), $arFilter = array())
{
global $DB;
$arSqlSearch = array();
$strSqlSearch = "";
$arSqlOrder = array();
$strSqlOrder = "";
$arFilter = is_array($arFilter) ? $arFilter : array();
foreach ($arFilter as $key => $val) {
if ($val === "NOT_REF") {
continue;
}
$key_res = VoteGetFilterOperation($key);
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$key = strtoupper($key_res["FIELD"]);
switch ($key) {
case "ID":
case "VOTE_ID":
$str = ($strNegative == "Y" ? "NOT" : "") . "(VQ." . $key . " IS NULL OR VQ." . $key . "<=0)";
if (!empty($val)) {
$str = ($strNegative == "Y" ? " VQ." . $key . " IS NULL OR NOT " : "") . "(VQ." . $key . " " . $strOperation . " " . intVal($val) . ")";
if ($strOperation == "IN") {
$val = array_unique(array_map("intval", is_array($val) ? $val : explode(",", $val)), SORT_NUMERIC);
if (!empty($val)) {
$str = ($strNegative == "Y" ? " NOT " : "") . "(VQ." . $key . " IN (" . implode(",", $val) . "))";
}
}
}
$arSqlSearch[] = $str;
break;
case "CHANNEL_ID":
if (strlen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(V." . $key . " IS NULL OR V." . $key . "<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " V." . $key . " IS NULL OR NOT " : "") . "(V." . $key . " " . $strOperation . " " . intVal($val) . ")";
}
break;
case "ACTIVE":
if (empty($val)) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(VQ." . $key . " IS NULL OR " . ($DB->type == "MSSQL" ? "LEN" : "LENGTH") . "(VQ." . $key . ")<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " VQ." . $key . " IS NULL OR NOT " : "") . "(VQ." . $key . " " . $strOperation . " '" . $DB->ForSql($val) . "')";
}
break;
}
}
if (count($arSqlSearch) > 0) {
$strSqlSearch = " AND (" . implode(") AND (", $arSqlSearch) . ") ";
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
$by = $by == "ACTIVE" ? $by : "ID";
if ($order != "ASC") {
$order = "DESC";
}
if ($by == "ACTIVE") {
$arSqlOrder[] = " VQ.ACTIVE " . $order . " ";
} else {
$arSqlOrder[] = " VQ.ID " . $order . " ";
}
}
DelDuplicateSort($arSqlOrder);
if (count($arSqlOrder) > 0) {
$strSqlOrder = " ORDER BY " . implode(", ", $arSqlOrder);
}
$strSql = "\n\t\t\tSELECT VQ.*\n\t\t\tFROM\n\t\t\t\tb_vote_question VQ, b_vote V\n\t\t\tWHERE VQ.VOTE_ID = V.ID " . $strSqlSearch . "\n\t\t\t" . $strSqlOrder;
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
function GetList($aSort=array(), $aFilter=array())
{
global $DB, $CACHE_MANAGER;
if(CACHED_b_user_field_enum !== false)
{
$cacheId = "b_user_field_enum".md5(serialize($aSort).".".serialize($aFilter));
if($CACHE_MANAGER->Read(CACHED_b_user_field_enum, $cacheId, "b_user_field_enum"))
{
$arResult = $CACHE_MANAGER->Get($cacheId);
$res = new CDBResult;
$res->InitFromArray($arResult);
return $res;
}
}
else
{
$cacheId = '';
}
$bJoinUFTable = false;
$arFilter = array();
foreach($aFilter as $key=>$val)
{
if(is_array($val))
{
if(count($val) <= 0)
continue;
$val = array_map(array($DB, "ForSQL"), $val);
$val = "('".implode("', '", $val)."')";
}
else
{
if(strlen($val) <= 0)
continue;
$val = "('".$DB->ForSql($val)."')";
}
$key = strtoupper($key);
switch($key)
{
case "ID":
case "USER_FIELD_ID":
case "VALUE":
case "DEF":
case "SORT":
case "XML_ID":
$arFilter[] = "UFE.".$key." in ".$val;
break;
case "USER_FIELD_NAME":
$bJoinUFTable = true;
$arFilter[] = "UF.FIELD_NAME in ".$val;
break;
}
}
$arOrder = array();
foreach($aSort as $key=>$val)
{
$key = strtoupper($key);
$ord = (strtoupper($val) <> "ASC"? "DESC": "ASC");
switch($key)
{
case "ID":
case "USER_FIELD_ID":
case "VALUE":
case "DEF":
case "SORT":
case "XML_ID":
$arOrder[] = "UFE.".$key." ".$ord;
break;
}
}
if(count($arOrder) == 0)
{
$arOrder[] = "UFE.SORT asc";
$arOrder[] = "UFE.ID asc";
}
DelDuplicateSort($arOrder);
$sOrder = "\nORDER BY ".implode(", ", $arOrder);
if(count($arFilter) == 0)
$sFilter = "";
else
$sFilter = "\nWHERE ".implode("\nAND ", $arFilter);
$strSql = "
SELECT
UFE.ID
,UFE.USER_FIELD_ID
,UFE.VALUE
,UFE.DEF
,UFE.SORT
,UFE.XML_ID
FROM
b_user_field_enum UFE
".($bJoinUFTable? "INNER JOIN b_user_field UF ON UF.ID = UFE.USER_FIELD_ID": "")."
".$sFilter.$sOrder;
if($cacheId == '')
{
$res = $DB->Query($strSql, false, "FILE: ".__FILE__."<br> LINE: ".__LINE__);
}
else
{
$arResult = array();
$res = $DB->Query($strSql, false, "FILE: ".__FILE__."<br> LINE: ".__LINE__);
while($ar = $res->Fetch())
$arResult[]=$ar;
$CACHE_MANAGER->Set($cacheId, $arResult);
$res = new CDBResult;
$res->InitFromArray($arResult);
}
return $res;
}
/**
* @param $arOrder
* @param $arFilter
* @return bool|CDBResult
*
* @var CDatabase $DB
*/
public static function GetList($arOrder, $arFilter)
{
global $DB;
$arSqlSearch = CTaskFiles::GetFilter($arFilter);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tTF.*\n\t\t\tFROM\n\t\t\t\tb_tasks_file TF\n\t\t\t" . (sizeof($arSqlSearch) ? "WHERE " . implode(" AND ", $arSqlSearch) : "") . "\n\t\t";
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "task") {
$arSqlOrder[] = " TF " . $order . " ";
} elseif ($by == "file") {
$arSqlOrder[] = " TF.FILE_ID " . $order . " ";
} elseif ($by == "rand") {
$arSqlOrder[] = CTasksTools::getRandFunction();
} else {
$arSqlOrder[] = " TF.FILE_ID " . $order . " ";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0, $arSqlOrderCnt = count($arSqlOrder); $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
function PrepareSql(&$arFields, $arOrder, &$arFilter, $arGroupBy, $arSelectFields, $obUserFieldsSql = false, $callback = false, $arOptions = array())
{
global $DB;
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
$strSqlGroupBy = '';
$strSqlFrom = '';
$strSqlSelect = '';
$strSqlWhere = '';
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy) > 0) {
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val) {
$val = ToUpper($val);
$key = ToUpper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct)) {
if ($strSqlGroupBy != '') {
$strSqlGroupBy .= ", ";
}
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if ($strSqlFrom != '') {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy) == 0) {
$strSqlSelect = "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT ";
} else {
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
$arSelectFields = array($arSelectFields);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
$countFieldKey = count($arFieldsKeys);
for ($i = 0; $i < $countFieldKey; $i++) {
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"]) && $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y") {
continue;
}
if ($strSqlSelect != '') {
$strSqlSelect .= ", ";
}
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime") {
if ((ToUpper($DB->type) == "ORACLE" || ToUpper($DB->type) == "MSSQL") && array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL") . " as " . $arFieldsKeys[$i];
} elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date") {
if ((ToUpper($DB->type) == "ORACLE" || ToUpper($DB->type) == "MSSQL") && array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT") . " as " . $arFieldsKeys[$i];
} else {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i];
}
if (isset($arFields[$arFieldsKeys[$i]]["FROM"]) && strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0 && !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = ToUpper($val);
$key = ToUpper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD"] . ") as " . $val;
} else {
if ($arFields[$val]["TYPE"] == "datetime") {
if ((ToUpper($DB->type) == "ORACLE" || ToUpper($DB->type) == "MSSQL") && array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["TYPE"] == "date") {
if ((ToUpper($DB->type) == "ORACLE" || ToUpper($DB->type) == "MSSQL") && array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if (strlen($strSqlGroupBy) > 0) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
$strSqlSelect .= "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT";
} else {
$strSqlSelect = "%%_DISTINCT_%% " . $strSqlSelect;
}
}
// <-- SELECT
// WHERE -->
$arSqlSearch = array();
if (!is_array($arFilter)) {
$filter_keys = array();
} else {
$filter_keys = array_keys($arFilter);
}
$countFilterKey = count($filter_keys);
for ($i = 0; $i < $countFilterKey; $i++) {
$vals = $arFilter[$filter_keys[$i]];
if (!is_array($vals)) {
$vals = array($vals);
} else {
$vals = array_values($vals);
}
$key = $filter_keys[$i];
$key_res = CSaleOrder::GetFilterOperation($key);
$key = $key_res["FIELD"];
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$strOrNull = $key_res["OR_NULL"];
if (array_key_exists($key, $arFields)) {
$arSqlSearch_tmp = array();
if (count($vals) > 0) {
if ($strOperation == "IN") {
if (isset($arFields[$key]["WHERE"])) {
$arSqlSearch_tmp1 = call_user_func_array($arFields[$key]["WHERE"], array($vals, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], $arFields, $arFilter));
if ($arSqlSearch_tmp1 !== false) {
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
} else {
if ($arFields[$key]["TYPE"] == "int") {
array_walk($vals, create_function("&\$item", "\$item=IntVal(\$item);"));
$vals = array_unique($vals);
$val = implode(",", $vals);
if (count($vals) <= 0) {
$arSqlSearch_tmp[] = "(1 = 2)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " NOT " : "") . "(" . $arFields[$key]["FIELD"] . " IN (" . $val . "))";
}
} elseif ($arFields[$key]["TYPE"] == "double") {
array_walk($vals, create_function("&\$item", "\$item=DoubleVal(\$item);"));
$vals = array_unique($vals);
$val = implode(",", $vals);
if (count($vals) <= 0) {
$arSqlSearch_tmp[] = "(1 = 2)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " (" . $val . "))";
}
} elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char") {
array_walk($vals, create_function("&\$item", "\$item=\"'\".\$GLOBALS[\"DB\"]->ForSql(\$item).\"'\";"));
$vals = array_unique($vals);
$val = implode(",", $vals);
if (count($vals) <= 0) {
$arSqlSearch_tmp[] = "(1 = 2)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " (" . $val . "))";
}
} elseif ($arFields[$key]["TYPE"] == "datetime") {
array_walk($vals, create_function("&\$item", "\$item=\"'\".\$GLOBALS[\"DB\"]->CharToDateFunction(\$GLOBALS[\"DB\"]->ForSql(\$item), \"FULL\").\"'\";"));
$vals = array_unique($vals);
$val = implode(",", $vals);
if (count($vals) <= 0) {
$arSqlSearch_tmp[] = "1 = 2";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " (" . $val . "))";
}
} elseif ($arFields[$key]["TYPE"] == "date") {
array_walk($vals, create_function("&\$item", "\$item=\"'\".\$GLOBALS[\"DB\"]->CharToDateFunction(\$GLOBALS[\"DB\"]->ForSql(\$item), \"SHORT\").\"'\";"));
$vals = array_unique($vals);
$val = implode(",", $vals);
if (count($vals) <= 0) {
$arSqlSearch_tmp[] = "1 = 2";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " (" . $val . "))";
}
}
}
} else {
$countVals = count($vals);
for ($j = 0; $j < $countVals; $j++) {
$val = $vals[$j];
if (isset($arFields[$key]["WHERE"])) {
$arSqlSearch_tmp1 = call_user_func_array($arFields[$key]["WHERE"], array($val, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], $arFields, $arFilter));
if ($arSqlSearch_tmp1 !== false) {
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
} else {
if ($arFields[$key]["TYPE"] == "int") {
if (IntVal($val) == 0 && strpos($strOperation, "=") !== False) {
$arSqlSearch_tmp[] = "(" . $arFields[$key]["FIELD"] . " IS " . ($strNegative == "Y" ? "NOT " : "") . "NULL) " . ($strNegative == "Y" ? "AND" : "OR") . " " . ($strNegative == "Y" ? "NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " 0)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . IntVal($val) . " )";
}
} elseif ($arFields[$key]["TYPE"] == "double") {
$val = str_replace(",", ".", $val);
if (DoubleVal($val) == 0 && strpos($strOperation, "=") !== False) {
$arSqlSearch_tmp[] = "(" . $arFields[$key]["FIELD"] . " IS " . ($strNegative == "Y" ? "NOT " : "") . "NULL) " . ($strNegative == "Y" ? "AND" : "OR") . " " . ($strNegative == "Y" ? "NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " 0)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . DoubleVal($val) . " )";
}
} elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char") {
if ($strOperation == "QUERY") {
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, "Y");
} else {
if (strlen($val) == 0 && strpos($strOperation, "=") !== False) {
$arSqlSearch_tmp[] = "(" . $arFields[$key]["FIELD"] . " IS " . ($strNegative == "Y" ? "NOT " : "") . "NULL) " . ($strNegative == "Y" ? "AND NOT" : "OR") . " (" . $DB->Length($arFields[$key]["FIELD"]) . " <= 0) " . ($strNegative == "Y" ? "AND NOT" : "OR") . " (" . $arFields[$key]["FIELD"] . " " . $strOperation . " '" . $DB->ForSql($val) . "' )";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " '" . $DB->ForSql($val) . "' )";
}
}
} elseif ($arFields[$key]["TYPE"] == "datetime") {
if (strlen($val) <= 0) {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? "NOT" : "") . "(" . $arFields[$key]["FIELD"] . " IS NULL)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ")";
}
} elseif ($arFields[$key]["TYPE"] == "date") {
if (strlen($val) <= 0) {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? "NOT" : "") . "(" . $arFields[$key]["FIELD"] . " IS NULL)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "SHORT") . ")";
}
}
}
}
}
}
if (isset($arFields[$key]["FROM"]) && strlen($arFields[$key]["FROM"]) > 0 && !in_array($arFields[$key]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$key]["FROM"];
$arAlreadyJoined[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
$countSqlSearch = count($arSqlSearch_tmp);
for ($j = 0; $j < $countSqlSearch; $j++) {
if ($j > 0) {
$strSqlSearch_tmp .= $strNegative == "Y" ? " AND " : " OR ";
}
$strSqlSearch_tmp .= "(" . $arSqlSearch_tmp[$j] . ")";
}
if ($strOrNull == "Y") {
if (strlen($strSqlSearch_tmp) > 0) {
$strSqlSearch_tmp .= $strNegative == "Y" ? " AND " : " OR ";
}
$strSqlSearch_tmp .= "(" . $arFields[$key]["FIELD"] . " IS " . ($strNegative == "Y" ? "NOT " : "") . "NULL)";
if ($arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double") {
if (strlen($strSqlSearch_tmp) > 0) {
$strSqlSearch_tmp .= $strNegative == "Y" ? " AND " : " OR ";
}
$strSqlSearch_tmp .= "(" . $arFields[$key]["FIELD"] . " " . ($strNegative == "Y" ? "<>" : "=") . " 0)";
} elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char") {
if (strlen($strSqlSearch_tmp) > 0) {
$strSqlSearch_tmp .= $strNegative == "Y" ? " AND " : " OR ";
}
$strSqlSearch_tmp .= "(" . $arFields[$key]["FIELD"] . " " . ($strNegative == "Y" ? "<>" : "=") . " '')";
}
}
if ($strSqlSearch_tmp != "") {
$arSqlSearch[] = "(" . $strSqlSearch_tmp . ")";
}
}
}
// custom subquery callback
if (is_callable($callback)) {
$arSqlSearch[] = call_user_func_array($callback, array($arFields));
}
$countSqlSearch = count($arSqlSearch);
for ($i = 0; $i < $countSqlSearch; $i++) {
if ($strSqlWhere != '') {
$strSqlWhere .= " AND ";
}
$strSqlWhere .= "(" . $arSqlSearch[$i] . ")";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = array();
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = ToUpper($by);
$order = ToUpper($order);
if ($order != "ASC") {
$order = "DESC";
} else {
$order = "ASC";
}
if (is_array($arGroupBy) && count($arGroupBy) > 0 && in_array($by, $arGroupBy)) {
$arSqlOrder[] = " " . $by . " " . $order . " ";
} elseif (array_key_exists($by, $arFields)) {
$arSqlOrder[] = " " . $arFields[$by]["FIELD"] . " " . $order . " ";
if (isset($arFields[$by]["FROM"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
} elseif ($obUserFieldsSql) {
$arSqlOrder[] = " " . $obUserFieldsSql->GetOrder($by) . " " . $order . " ";
}
}
$nullsLast = isset($arOptions['NULLS_LAST']) ? (bool) $arOptions['NULLS_LAST'] : false;
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$countSqlOrder = count($arSqlOrder);
for ($i = 0; $i < $countSqlOrder; $i++) {
if (strlen($strSqlOrderBy) > 0) {
$strSqlOrderBy .= ", ";
}
$order = substr($arSqlOrder[$i], -3) == "ASC" ? "ASC" : "DESC";
if (!$nullsLast) {
if (ToUpper($DB->type) == "ORACLE") {
if ($order === "ASC") {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS FIRST";
} else {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS LAST";
}
} else {
$strSqlOrderBy .= $arSqlOrder[$i];
}
} else {
$field = substr($arSqlOrder[$i], 0, -strlen($order) - 1);
if (ToUpper($DB->type) === "MYSQL") {
if ($order === 'ASC') {
$strSqlOrderBy .= '(CASE WHEN ISNULL(' . $field . ') THEN 1 ELSE 0 END) ' . $order . ', ' . $field . " " . $order;
} else {
$strSqlOrderBy .= $field . " " . $order;
}
} elseif (ToUpper($DB->type) === "MSSQL") {
if ($order === 'ASC') {
$strSqlOrderBy .= '(CASE WHEN ' . $field . ' IS NULL THEN 1 ELSE 0 END) ' . $order . ', ' . $field . " " . $order;
} else {
$strSqlOrderBy .= $field . " " . $order;
}
} elseif (ToUpper($DB->type) === "ORACLE") {
if ($order === 'DESC') {
$strSqlOrderBy .= $field . " " . $order . " NULLS LAST";
} else {
$strSqlOrderBy .= $field . " " . $order;
}
}
}
}
// <-- ORDER BY
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy);
}
/**
* <p>Возвращает список вопросов по фильтру arFilter, отсортированный в порядке arOrder.</p>
*
*
* @param array $arrayarOrder = Array("TIMESTAMP_X"=>"DESC") Массив для сортировки результата. Массив вида <i>array("поле
* сортировки"=>"направление сортировки" [, ...])</i>.<br> Поле для
* сортировки может принимать значения: <ul> <li> <b>ID</b> - идентификатор
* вопроса;</li> <li> <b>NAME</b> - название вопроса;</li> <li> <b>ACTIVE</b> - активность
* вопроса;</li> <li> <b>SORT</b> - индекс сортировки;</li> <li> <b>SELF</b> - вопрос для
* самопроверки;</li> <li> <b>POINT</b> - баллы;</li> <li> <b>TYPE</b> - тип вопроса;</li> <li>
* <b>TIMESTAMP_X</b> - дата изменения вопроса.</li> </ul> Направление сортировки
* может принимать значения: <ul> <li> <b>asc</b> - по возрастанию;</li> <li>
* <b>desc</b> - по убыванию;</li> </ul> Необязательный. По умолчанию
* сортируется по убыванию даты изменения вопроса.
*
* @param array $arrayarFilter = Array() Массив вида <i> array("фильтруемое поле"=>"значение фильтра" [, ...])</i>.
* Фильтруемое поле может принимать значения: <ul> <li> <b>ID</b> -
* идентификатор вопроса;</li> <li> <b>NAME</b> - название вопроса (можно
* искать по шаблону [%_]);</li> <li> <b>SORT</b> - индекс сортировки;</li> <li>
* <b>ACTIVE</b> - фильтр по активности (Y|N);</li> <li> <b>LESSON_ID</b> - идентификатор
* урока;</li> <li> <b>POINT</b> - баллы;</li> <li> <b>COURSE_ID</b> - идентификатор
* курса;</li> <li> <b>QUESTION_TYPE</b> - тип вопроса (S - одиночный выбор, M -
* множественный выбор);</li> <li> <b>SELF</b> - вопрос для самопроверки (Y|N).</li>
* </ul> Перед названием фильтруемого поля может указать тип
* фильтрации: <ul> <li>"!" - не равно</li> <li>"<" - меньше</li> <li>"<=" - меньше
* либо равно</li> <li>">" - больше</li> <li>">=" - больше либо равно</li> </ul> <br>
* "<i>значения фильтра</i>" - одиночное значение или массив.<br><br>
* Необязательный. По умолчанию записи не фильтруются.
*
* @return CDBResult <p>Возвращается объект <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>.</p> </h
*
* <h4>Example</h4>
* <pre>
* <?
*
* if (CModule::IncludeModule("learning"))
* {
* $LESSON_ID = 426;
*
* $res = CLQuestion::GetList(
* Array("TIMESTAMP_X" => "ASC", "SORT"=>"ASC"),
* Array("LESSON_ID" => $LESSON_ID)
* );
*
* while ($arQuestion = $res->GetNext())
* {
* echo "Question name: ".$arQuestion["NAME"]."<br>";
* }
* }
*
* ?>
*
* <?
* if (CModule::IncludeModule("learning"))
* {
* $COURSE_ID = 97;
* $res = CLQuestion::GetList(
* Array("SORT"=>"ASC"),
* Array("ACTIVE" => "Y", "COURSE_ID" => $COURSE_ID)
* );
*
* while ($arQuestion = $res->GetNext())
* {
* echo "Question name: ".$arQuestion["NAME"]."<br>";
* }
* }
*
* ?>
*
* <?
*
* if (CModule::IncludeModule("learning"))
* {
* $res = CLQuestion::GetList(
* Array("SORT"=>"ASC"),
* Array("?NAME" => "Site")
* );
*
* while ($arQuestion = $res->GetNext())
* {
* echo "Question name: ".$arQuestion["NAME"]."<br>";
* }
* }
* ?>
*
* <?
*
* if (CModule::IncludeModule("learning"))
* {
* $COURSE_ID = 97;
*
* $res = CLQuestion::GetList(
* Array("NAME" => "ASC", "SORT"=>"ASC"),
* Array("COURSE_ID" => $COURSE_ID, "!SELF" => "Y")
* );
*
* while ($arQuestion = $res->GetNext())
* {
* echo "Question name: ".$arQuestion["NAME"]."<br>";
* }
* }
*
* ?>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/clquestion/index.php">CLQuestion</a>::<a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/clquestion/getbyid.php">GetByID</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/learning/fields.php#question">Поля вопроса</a> </li> </ul> <a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/learning/classes/clquestion/getlist.php
* @author Bitrix
*/
public static function GetList($arOrder = array(), $arFilter = array(), $bHz = false, $arNavParams = array(), $arSelect = array())
{
global $DB, $USER, $USER_FIELD_MANAGER;
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity('LEARNING_QUESTIONS', "CQ.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$arSqlSearch = array_filter(CLQuestion::GetFilter($arFilter));
$strSqlFrom = "FROM b_learn_question CQ " . "INNER JOIN b_learn_lesson CL ON CQ.LESSON_ID = CL.ID " . $obUserFieldsSql->GetJoin("CQ.ID") . " WHERE ";
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$arSqlSearch[] = "(" . $r . ")";
}
if (!empty($arSqlSearch)) {
$strSqlFrom .= implode(' AND ', $arSqlSearch);
} else {
$strSqlFrom .= ' 1=1 ';
}
$strSql = "SELECT CQ.ID, CQ.ACTIVE, CQ.LESSON_ID, CQ.QUESTION_TYPE,\n\t\t\t\tCQ.NAME, CQ.SORT, CQ.DESCRIPTION, CQ.DESCRIPTION_TYPE,\n\t\t\t\tCQ.COMMENT_TEXT, CQ.FILE_ID, CQ.SELF, CQ.POINT, CQ.DIRECTION,\n\t\t\t\tCQ.CORRECT_REQUIRED, CQ.EMAIL_ANSWER, CQ.INCORRECT_MESSAGE," . $DB->DateToCharFunction("CQ.TIMESTAMP_X") . " as TIMESTAMP_X " . $obUserFieldsSql->GetSelect() . " " . $strSqlFrom;
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[] = " CQ.ID " . $order . " ";
} elseif ($by == "name") {
$arSqlOrder[] = " CQ.NAME " . $order . " ";
} elseif ($by == "sort") {
$arSqlOrder[] = " CQ.SORT " . $order . " ";
} elseif ($by == "point") {
$arSqlOrder[] = " CQ.POINT " . $order . " ";
} elseif ($by == "type") {
$arSqlOrder[] = " CQ.QUESTION_TYPE " . $order . " ";
} elseif ($by == "self") {
$arSqlOrder[] = " CQ.SELF " . $order . " ";
} elseif ($by == "active") {
$arSqlOrder[] = " CQ.ACTIVE " . $order . " ";
} elseif ($by == "correct_required") {
$arSqlOrder[] = " CQ.CORRECT_REQUIRED " . $order . " ";
} elseif ($s = $obUserFieldsSql->getOrder($by)) {
$arSqlOrder[] = ' ' . $s . ' ' . $order . ' ';
} else {
$arSqlOrder[] = " CQ.TIMESTAMP_X " . $order . " ";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$cnt = count($arSqlOrder);
for ($i = 0; $i < $cnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
if (is_array($arNavParams) && !empty($arNavParams)) {
if (isset($arNavParams['nTopCount']) && (int) $arNavParams['nTopCount'] > 0) {
$strSql = $DB->TopSql($strSql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
} else {
$res_cnt = $DB->Query("SELECT COUNT(CQ.ID) as C " . $strSqlFrom);
$res_cnt = $res_cnt->fetch();
$res = new CDBResult();
$res->NavQuery($strSql, $res_cnt['C'], $arNavParams);
}
} else {
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields('LEARNING_QUESTIONS'));
return $res;
}
/**
* <p>Возвращает список вопросов плана тестирования по фильтру <b>arFilter</b>, отсортированный в порядке <b>arOrder</b>.</p>
*
*
*
*
* @param array $arrayarOrder = Array("ID"=>"DESC") Массив для сортировки результата. Массив вида <i>array("поле
* сортировки"=>"направление сортировки" [, ...])</i>.<br>Поле для
* сортировки может принимать значения: <ul> <li> <b>ID</b> - идентификатор
* вопроса в плане тестирования; </li> <li> <b>ATTEMPT_ID</b> - идентификатор
* попытки; </li> <li> <b>QUESTION_ID</b> - идентификатор вопроса; </li> <li> <b>POINT</b> -
* количество баллов; </li> <li> <b>ANSWERED</b> - вопрос отвечен (Y|N); </li> <li>
* <b>CORRECT</b> - вопрос правильно отвечен (Y|N); </li> <li> <b>QUESTION_NAME</b> -
* название вопроса; </li> <li> <b>RAND</b> - случайный порядок. </li>
* </ul>Направление сортировки может принимать значения: <ul> <li> <b>asc</b> -
* по возрастанию; </li> <li> <b>desc</b> - по убыванию; </li> </ul>Необязательный.
* По умолчанию фильтруется по убыванию идентификатора вопроса в
* плане тестирования.
*
*
*
* @param array $arrayarFilter = Array() Массив вида <i>array("фильтруемое поле"=>"значение фильтра" [, ...])</i>.
* Фильтруемое поле может принимать значения: <ul> <li> <b>ID</b> -
* идентификатор вопроса в плане тестирования; </li> <li> <b>ATTEMPT_ID</b> -
* идентификатор попытки; </li> <li> <b>QUESTION_ID</b> - идентификатор вопроса;
* </li> <li> <b>POINT</b> - количество баллов; </li> <li> <b>RESPONSE</b> - ответ учащегося
* (можно искать по шаблону [%_]); </li> <li> <b>QUESTION_NAME</b> - название вопроса
* (можно искать по шаблону [%_]); </li> <li> <b>ANSWERED</b> - вопрос отвечен (Y|N);
* </li> <li> <b>CORRECT</b> - вопрос правильно отвечен (Y|N). </li> </ul>Перед
* названием фильтруемого поля может указать тип фильтрации: <ul> <li>"!"
* - не равно </li> <li>"<" - меньше </li> <li>"<=" - меньше либо равно </li> <li>">"
* - больше </li> <li>">=" - больше либо равно </li> </ul> <br>"<i>значения
* фильтра</i>" - одиночное значение или массив.<br><br>Необязательный. По
* умолчанию записи не фильтруются.
*
*
*
* @return CDBResult <p>Возвращается объект <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>.</p>
*
*
* <h4>Example</h4>
* <pre>
* <?
* if (CModule::IncludeModule("learning"))
* {
* $ATTEMPT_ID = 590;
* $res = CTestResult::GetList(
* Array("ID" => "ASC"),
* Array("ANSWERED" => "N", "ATTEMPT_ID" => $ATTEMPT_ID)
* );
*
* while ($arQuestionPlan = $res->GetNext())
* {
* echo "Question ID: ".$arQuestionPlan["QUESTION_ID"].<br> "; Correct answer: ".$arQuestionPlan["CORRECT"].<br> "; Question name:".$arQuestionPlan["QUESTION_NAME"]."<br>";
* }
* }
* ?>
* </pre>
*
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> </li> <li> <a
* href="index.php">CTestResult</a>::<a href="getbyid.php">GetByID</a> </li> <li><a
* href="../../fields.php#test_result">Поля плана тестирования</a></li> </ul><a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/learning/classes/ctestresult/getlist.php
* @author Bitrix
*/
public static function GetList($arOrder=array(), $arFilter=array())
{
global $DB, $USER, $APPLICATION;
if (!is_array($arFilter))
$arFilter = Array();
$oPermParser = new CLearnParsePermissionsFromFilter ($arFilter);
$arSqlSearch = CTestResult::GetFilter($arFilter);
$strSqlSearch = "";
$arSqlSearchCnt = count($arSqlSearch);
for($i=0; $i<$arSqlSearchCnt; $i++)
if(strlen($arSqlSearch[$i])>0)
$strSqlSearch .= " AND ".$arSqlSearch[$i]." ";
$strSql =
"SELECT TR.*, Q.QUESTION_TYPE, Q.NAME as QUESTION_NAME, Q.POINT as QUESTION_POINT, Q.LESSON_ID
FROM b_learn_test_result TR
INNER JOIN b_learn_question Q ON TR.QUESTION_ID = Q.ID
INNER JOIN b_learn_lesson L ON Q.LESSON_ID = L.ID
WHERE 1=1 ";
if ($oPermParser->IsNeedCheckPerm())
$strSql .= " AND L.ID IN (" . $oPermParser->SQLForAccessibleLessons() . ") ";
$strSql .= $strSqlSearch;
/* was:
$bCheckPerm = ($APPLICATION->GetUserRight("learning") < "W" && !$USER->IsAdmin() && $arFilter["CHECK_PERMISSIONS"] != "N");
$strSql =
"SELECT TR.*, Q.QUESTION_TYPE, Q.NAME as QUESTION_NAME, Q.POINT as QUESTION_POINT, Q.LESSON_ID
FROM b_learn_test_result TR
INNER JOIN b_learn_question Q ON TR.QUESTION_ID = Q.ID
INNER JOIN b_learn_lesson L ON Q.LESSON_ID = L.ID "
"INNER JOIN b_learn_course C ON L.COURSE_ID = C.ID ".
. "WHERE 1=1 "
. ($bCheckPerm ?
"AND EXISTS (SELECT * FROM b_learn_course_permission CP WHERE ".
"CP.USER_GROUP_ID IN (".$USER->GetGroups().") ".
"AND CP.PERMISSION >= '".(strlen($arFilter["MIN_PERMISSION"])==1 ? $arFilter["MIN_PERMISSION"] : "R")."' ".
"AND (CP.PERMISSION='X' OR C.ACTIVE='Y'))"
:"")
. $strSqlSearch;
*/
if (!is_array($arOrder))
$arOrder = Array();
foreach($arOrder as $by=>$order)
{
$by = strtolower($by);
$order = strtolower($order);
if ($order!="asc")
$order = "desc";
if ($by == "id") $arSqlOrder[] = " TR.ID ".$order." ";
elseif ($by == "attempt_id") $arSqlOrder[] = " TR.ATTEMPT_ID ".$order." ";
elseif ($by == "question_id") $arSqlOrder[] = " TR.QUESTION_ID ".$order." ";
elseif ($by == "point") $arSqlOrder[] = " TR.POINT ".$order." ";
elseif ($by == "correct") $arSqlOrder[] = " TR.CORRECT ".$order." ";
elseif ($by == "answered") $arSqlOrder[] = " TR.ANSWERED ".$order." ";
elseif ($by == "question_name") $arSqlOrder[] = " QUESTION_NAME ".$order." ";
elseif ($by == "rand") $arSqlOrder[] = CTest::GetRandFunction();
else
{
$arSqlOrder[] = " TR.ID ".$order." ";
$by = "id";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$arSqlOrderCnt = count($arSqlOrder);
for ($i=0; $i<$arSqlOrderCnt; $i++)
{
if($i==0)
$strSqlOrder = " ORDER BY ";
else
$strSqlOrder .= ",";
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
//echo $strSql;
return $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
}
/**
* <p>Возвращает список учётных записей студентов по фильтру <b>arFilter</b>, отсортированный в порядке <b>arOrder</b>.</p>
*
*
* @param array $arrayarOrder = Array("ID"=>"DESC") Массив для сортировки результата. Массив вида <i>array("поле
* сортировки"=>"направление сортировки" [, ...])</i>.<br>Поле для
* сортировки может принимать значения: <ul> <li> <b>USER_ID</b> - <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cuser/index.php">Код пользователя</a>; </li> <li>
* <b>PUBLIC_PROFILE</b> - профиль доступен публично (Y/N); </li> </ul>Направление
* сортировки может принимать значения: <ul> <li> <b>asc</b> - по возрастанию;
* </li> <li> <b>desc</b> - по убыванию; </li> </ul>Необязательный. По умолчанию
* сортируется по убыванию кода пользователя.
*
* @param array $arrayarFilter = Array() Массив вида <i>array("фильтруемое поле"=>"значение фильтра" [, ...])</i>.
* Фильтруемое поле может принимать значения: <ul> <li> <b>USER_ID</b> - <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cuser/index.php">Код пользователя</a>; </li> <li>
* <b>PUBLIC_PROFILE</b> - профиль доступен публично (Y/N); </li> <li> <b>TRANSCRIPT</b> -
* числовой случайный идентификатор; </li> <li> <b>RESUME</b> - резюме студента
* (можно искать по шаблону [%_]); </li> </ul>Перед названием фильтруемого
* поля можно указать тип фильтрации: <ul> <li>"!" - не равно </li> <li>"<" -
* меньше </li> <li>"<=" - меньше либо равно </li> <li>">" - больше </li> <li>">=" -
* больше либо равно </li> </ul> <br>"<i>значения фильтра</i>" - одиночное
* значение или массив.<br><br>Необязательный. По умолчанию записи не
* фильтруются.
*
* @return CDBResult <p>Возвращается объект <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>.</p> </h
*
* <h4>Example</h4>
* <pre>
* <?
* if (CModule::IncludeModule("learning"))
* {
* $USER_ID = 1; $TRANSCRIPT = 46785643;
* $res = CStudent::GetList(Array(), Array("USER_ID" => $USER_ID, "TRANSCRIPT" => $TRANSCRIPT));
*
* while ($arProfile = $res->GetNext())
* {
* echo $arProfile["RESUME"];
* }
* }
* ?>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/cstudent/index.php">CStudent</a>::<a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/cstudent/getbyid.php">GetByID</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/learning/fields.php#student">Поля студента</a> </li> </ul> <a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/learning/classes/cstudent/getlist.php
* @author Bitrix
*/
public static function GetList($arOrder = array(), $arFilter = array())
{
global $DB, $USER;
$arSqlSearch = CStudent::GetFilter($arFilter);
$strSqlSearch = "";
for ($i = 0; $i < count($arSqlSearch); $i++) {
if (strlen($arSqlSearch[$i]) > 0) {
$strSqlSearch .= " AND " . $arSqlSearch[$i] . " ";
}
}
$strSql = "SELECT S.* " . "FROM b_learn_student S " . "WHERE 1=1 " . $strSqlSearch;
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "user_id") {
$arSqlOrder[] = " S.USER_ID " . $order . " ";
} elseif ($by == "public_profile") {
$arSqlOrder[] = " S.PUBLIC_PROFILE " . $order . " ";
} else {
$arSqlOrder[] = " S.USER_ID " . $order . " ";
$by = "user_id";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0; $i < count($arSqlOrder); $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
//echo $strSql;
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
function GetList($arOrder = array("NAME" => "ASC"), $arFilter = array())
{
global $DB;
$arSqlSearch = array();
if (!is_array($arFilter)) {
$filter_keys = array();
} else {
$filter_keys = array_keys($arFilter);
}
$countFiltersKeys = count($filter_keys);
for ($i = 0; $i < $countFiltersKeys; $i++) {
$val = $DB->ForSql($arFilter[$filter_keys[$i]]);
if (strlen($val) <= 0) {
continue;
}
$key = $filter_keys[$i];
if ($key[0] == "!") {
$key = substr($key, 1);
$bInvert = true;
} else {
$bInvert = false;
}
switch (ToUpper($key)) {
case "ID":
$arSqlSearch[] = "T.ID " . ($bInvert ? "<>" : "=") . " " . IntVal($val) . " ";
break;
case "LID":
$arSqlSearch[] = "T.LID " . ($bInvert ? "<>" : "=") . " '" . $val . "' ";
break;
case "CODE":
$arSqlSearch[] = "T.CODE " . ($bInvert ? "<>" : "=") . " '" . $val . "' ";
break;
}
}
$strSqlSearch = "";
$countSqlSearch = count($arSqlSearch);
for ($i = 0; $i < $countSqlSearch; $i++) {
$strSqlSearch .= " AND ";
$strSqlSearch .= " (" . $arSqlSearch[$i] . ") ";
}
$strSql = "SELECT T.ID, T.LID, T.NAME, T.CODE, T.DESCRIPTION, " . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL") . " as TIMESTAMP_X " . "FROM b_sale_tax T " . "WHERE 1 = 1 " . "\t" . $strSqlSearch . " ";
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = ToUpper($by);
$order = ToUpper($order);
if ($order != "ASC") {
$order = "DESC";
}
if ($by == "ID") {
$arSqlOrder[] = " T.ID " . $order . " ";
} elseif ($by == "LID") {
$arSqlOrder[] = " T.LID " . $order . " ";
} elseif ($by == "CODE") {
$arSqlOrder[] = " T.CODE " . $order . " ";
} elseif ($by == "TIMESTAMP_X") {
$arSqlOrder[] = " T.TIMESTAMP_X " . $order . " ";
} else {
$arSqlOrder[] = " T.NAME " . $order . " ";
$by = "NAME";
}
}
$strSqlOrder = "";
$countSqlOrder = count($arSqlOrder);
DelDuplicateSort($arSqlOrder);
for ($i = 0; $i < $countSqlOrder; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
$db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
return $db_res;
}
function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arUF = array())
{
global $DB;
$obUserFieldsSql = false;
if (is_array($arUF) && array_key_exists("ENTITY_ID", $arUF)) {
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity($arUF["ENTITY_ID"], $arFields["ID"]["FIELD"]);
$obUserFieldsSql->SetSelect($arSelectFields);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
}
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy) > 0) {
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct)) {
if (strlen($strSqlGroupBy) > 0) {
$strSqlGroupBy .= ", ";
}
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// WHERE -->
$arAlreadyJoinedOld = $arAlreadyJoined;
$strSqlWhere .= CSqlUtil::PrepareWhere($arFields, $arFilter, $arAlreadyJoined);
$arAlreadyJoinedDiff = array_diff($arAlreadyJoined, $arAlreadyJoinedOld);
foreach ($arAlreadyJoinedDiff as $from_tmp) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $from_tmp;
}
if ($obUserFieldsSql) {
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strSqlWhere .= (strlen($strSqlWhere) > 0 ? " AND" : "") . " (" . $r . ") ";
}
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
} else {
$order = "ASC";
}
if (array_key_exists($by, $arFields)) {
if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date") {
$arSqlOrder[] = " " . $by . "_X1 " . $order . " ";
if (!is_array($arSelectFields) || !in_array($by, $arSelectFields)) {
$arSelectFields[] = $by;
}
} else {
$arSqlOrder[] = " " . $arFields[$by]["FIELD"] . " " . $order . " ";
}
if (isset($arFields[$by]["FROM"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
} elseif ($obUserFieldsSql && ($s = $obUserFieldsSql->GetOrder($by))) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$tmp_count = count($arSqlOrder);
for ($i = 0; $i < $tmp_count; $i++) {
if (strlen($strSqlOrderBy) > 0) {
$strSqlOrderBy .= ", ";
}
if (strtoupper($DB->type) == "ORACLE") {
if (substr($arSqlOrder[$i], -3) == "ASC") {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS FIRST";
} else {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS LAST";
}
} else {
$strSqlOrderBy .= $arSqlOrder[$i];
}
}
// <-- ORDER BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy) == 0) {
$strSqlSelect = "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT ";
} else {
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
$arSelectFields = array($arSelectFields);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
$tmp_count = count($arFieldsKeys);
for ($i = 0; $i < $tmp_count; $i++) {
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"]) && $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y") {
continue;
}
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL") . " as " . $arFieldsKeys[$i];
} elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT") . " as " . $arFieldsKeys[$i];
} else {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i];
}
if (isset($arFields[$arFieldsKeys[$i]]["FROM"]) && strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0 && !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD"] . ") as " . $val;
} else {
if ($arFields[$val]["TYPE"] == "datetime") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["TYPE"] == "date") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if ($obUserFieldsSql) {
$strSqlSelect .= (strlen($strSqlSelect) <= 0 ? $arFields["ID"]["FIELD"] : "") . $obUserFieldsSql->GetSelect();
}
if (strlen($strSqlGroupBy) > 0) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
$strSqlSelect .= "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT";
} else {
$strSqlSelect = "%%_DISTINCT_%% " . $strSqlSelect;
}
}
// <-- SELECT
if ($obUserFieldsSql) {
$strSqlFrom .= " " . $obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
}
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy);
}
/**
* <p>Возвращает список свойств по фильтру <i>arFilter</i> отсортированные в порядке <i>arOrder</i>. Метод статический.</p>
*
*
* @param array $arrayarOrder = Array() Массив для сортировки, имеющий вид <i>by1=>order1[,by2=>order2 [, ..]]</i>,
* где:<i> by</i> - поле сортировки, может принимать значения: <ul> <li> <b>id</b> -
* код;</li> <li> <b>block_id</b> - код информационного блока, которому
* принадлежит свойство; <b>name</b> - название;</li> <li> <b>name</b> - название;</li>
* <li> <b>active</b> - активность;</li> <li> <b>sort</b> - индекс сортировки;</li> <li>
* <b>timestamp_x</b> - дата последнего изменения</li> <li> <b>searchable</b> - по
* признаку участия значения свойства в поиске; </li> <li> <b>filtrable</b> - по
* признаку участия свойства в фильре на странице списка элементов;
* </li> <li> <b>order</b> - порядок сортировки, может принимать значения: <ul> <li>
* <b>asc</b> - по возрастанию;</li> <li> <b>desc</b> - по убыванию;</li> </ul> </li> </ul>
*
* @param array $arrayarFilter = Array() Массив вида <i> array("фильтруемое поле"=>"значение" [, ...])</i>. Ни один
* параметр фильтра не принимает массивы. <i>Фильтруемое поле</i>
* может принимать значения: <ul> <li> <b>ACTIVE</b> - фильтр по активности
* (Y|N);</li> <li> <b>ID</b> - по ID свойства, только число;</li> <li> <b>CODE</b> - по
* символьному коду (по шаблону [%_]), только строка;</li> <li> <b>NAME</b> - по
* названию свойства (по шаблону [%_]);</li> <li> <b>EXTERNAL_ID</b> или <b>XML_ID </b>- по
* внешнему коду(по шаблону [%_]). Возможно использование отрицания,
* указав <i>!EXTERNAL_ID</i>;</li> <li> <b>PROPERTY_TYPE</b> - по типу свойства: <ul> <li> <b>S</b> -
* строка</li> <li> <b>N</b> - число</li> <li> <b>L</b> - список</li> <li> <b>F</b> - файл</li> <li>
* <b>G</b> - привязка к разделу</li> <li> <b>E</b> - привязка к элементу</li> </ul> </li>
* <li> <b>USER_TYPE</b> - по пользовательскому типу свойства; <ul> <li> <b>UserID</b> -
* Привязка к пользователю</li> <li> <b>DateTime</b> - Дата/Время</li> <li> <b>EList</b> -
* Привязка к элементам в виде списка</li> <li> <b>FileMan</b> - Привязка к
* файлу (на сервере)</li> <li> <b>map_yandex</b> - Привязка к Яndex.Карте</li> <li>
* <b>HTML</b> - HTML/текст</li> <li> <b>map_google</b> - Привязка к карте Google Maps</li> <li>
* <b>ElementXmlID</b> - Привязка к элементам по XML_ID</li> <li> <b>Sequence</b> - Счетчик</li>
* <li> <b>EAutocomplete</b> - Привязка к элементам с автозаполнением</li> <li>
* <b>SKU</b> - Привязка к товарам (SKU)</li> <li> <b>video</b> - Видео</li> <li> <b>TopicID</b> -
* Привязка к теме форума</li> </ul> Кроме того, можно использовать
* идентификаторы пользовательских типов свойств инфоблока,
* добавленных сторонними модулями. </li> <li> <b>SEARCHABLE</b> - по признаку
* участия значения свойства в поиске (Y|N); </li> <li> <b>FILTRABLE</b> - по
* признаку участия свойства в фильре на странице списка элементов
* (Y|N);</li> <li> <b>VERSION</b> - по флагу хранения значений свойств элементов
* инфоблока;</li> <li> <b>MIN_PERMISSION</b> - фильтр по правам доступа, по
* умолчанию принимает <i>R</i> (уровень доступа <i>Чтение</i>);</li> <li>
* <b>CHECK_PERMISSIONS</b> - если установлено значение "N", то проверки прав не
* происходит; <br> </li> <li> <b>IBLOCK_ID</b> - по коду информационного блока,
* которому принадлежит свойство;</li> <li> <b>IBLOCK_CODE</b> - по символьному
* коду информационного блока, которому принадлежит свойство;</li> <li>
* <b>LINK_IBLOCK_ID</b> - по коду связанного информационного блока (может
* быть указан для свойств типа "E" и "G");</li> <li> <b>MULTIPLE</b> - Проверка на
* множественность. по умолчанию N.</li> </ul> Необязательное. По
* умолчанию записи не фильтруются.
*
* @return CDBResult <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>
*
* <h4>Example</h4>
* <pre>
* <?<br>$IBLOCK_ID = 11;<br>$properties = CIBlockProperty::GetList(Array("sort"=>"asc", "name"=>"asc"), Array("ACTIVE"=>"Y", "IBLOCK_ID"=>$IBLOCK_ID));<br>while ($prop_fields = $properties->GetNext())<br>{<br> echo $prop_fields["ID"]." - ".$prop_fields["NAME"]."<br>";<br>}<br>?><br>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/iblock/fields.php#fproperty">Поля свойства</a> </li> </ul> <a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/iblock/classes/ciblockproperty/getlist.php
* @author Bitrix
*/
public static function GetList($arOrder = array(), $arFilter = array())
{
global $DB;
$strSql = "\n\t\t\tSELECT BP.*\n\t\t\tFROM b_iblock_property BP\n\t\t";
$bJoinIBlock = false;
$arSqlSearch = "";
foreach ($arFilter as $key => $val) {
$val = $DB->ForSql($val);
$key = strtoupper($key);
switch ($key) {
case "ACTIVE":
case "SEARCHABLE":
case "FILTRABLE":
case "IS_REQUIRED":
case "MULTIPLE":
if ($val == "Y" || $val == "N") {
$arSqlSearch[] = "BP." . $key . " = '" . $val . "'";
}
break;
case "?CODE":
case "?NAME":
$arSqlSearch[] = CIBlock::FilterCreate("BP." . substr($key, 1), $val, "string", "E");
break;
case "CODE":
case "NAME":
$arSqlSearch[] = "UPPER(BP." . $key . ") LIKE UPPER('" . $val . "')";
break;
case "XML_ID":
case "EXTERNAL_ID":
$arSqlSearch[] = "BP.XML_ID LIKE '" . $val . "'";
break;
case "!XML_ID":
case "!EXTERNAL_ID":
$arSqlSearch[] = "(BP.XML_ID IS NULL OR NOT (BP.XML_ID LIKE '" . $val . "'))";
break;
case "TMP_ID":
$arSqlSearch[] = "BP.TMP_ID LIKE '" . $val . "'";
break;
case "!TMP_ID":
$arSqlSearch[] = "(BP.TMP_ID IS NULL OR NOT (BP.TMP_ID LIKE '" . $val . "'))";
break;
case "PROPERTY_TYPE":
$ar = explode(":", $val);
if (count($ar) == 2) {
$val = $ar[0];
$arSqlSearch[] = "BP.USER_TYPE = '" . $ar[1] . "'";
}
$arSqlSearch[] = "BP." . $key . " = '" . $val . "'";
break;
case "USER_TYPE":
$arSqlSearch[] = "BP." . $key . " = '" . $val . "'";
break;
case "ID":
case "IBLOCK_ID":
case "LINK_IBLOCK_ID":
case "VERSION":
$arSqlSearch[] = "BP." . $key . " = " . (int) $val;
break;
case "IBLOCK_CODE":
$arSqlSearch[] = "UPPER(B.CODE) = UPPER('" . $val . "')";
$bJoinIBlock = true;
break;
}
}
if ($bJoinIBlock) {
$strSql .= "\n\t\t\t\tINNER JOIN b_iblock B ON B.ID = BP.IBLOCK_ID\n\t\t\t";
}
if (!empty($arSqlSearch)) {
$strSql .= "\n\t\t\t\tWHERE " . implode("\n\t\t\t\tAND ", $arSqlSearch) . "\n\t\t\t";
}
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order) == "ASC" ? "ASC" : "DESC";
if ($by === "ID" || $by === "IBLOCK_ID" || $by === "NAME" || $by === "ACTIVE" || $by === "SORT" || $by === "FILTRABLE" || $by === "SEARCHABLE") {
$arSqlOrder[] = " BP." . $by . " " . $order;
} else {
$arSqlOrder[] = " BP.TIMESTAMP_X " . $order;
}
}
DelDuplicateSort($arSqlOrder);
if (!empty($arSqlOrder)) {
$strSql .= "\n\t\t\t\tORDER BY " . implode(", ", $arSqlOrder) . "\n\t\t\t";
}
$res = $DB->Query($strSql, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__);
$res = new CIBlockPropertyResult($res);
return $res;
}
/**
* Get list of existing learning group <-> lesson pairs
*
* @param array $arOrder
* @param array $arFilter
* @param array $arSelect
* @param array $arNavParams
*
* @return CDBResult
*/
public static function getList($arOrder, $arFilter, $arSelect = array(), $arNavParams = array())
{
global $DB, $USER;
$arFields = array('LEARNING_GROUP_ID' => 'LGL.LEARNING_GROUP_ID', 'LESSON_ID' => 'LGL.LESSON_ID', 'DELAY' => 'LGL.DELAY');
if (count($arSelect) <= 0 || in_array("*", $arSelect)) {
$arSelect = array_keys($arFields);
}
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = (string) $by;
$needle = null;
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if (array_key_exists($by, $arFields)) {
$arSqlOrder[] = ' ' . $by . ' ' . $order . ' ';
$needle = $by;
}
if ($needle !== null && !in_array($needle, $arSelect, true)) {
$arSelect[] = $needle;
}
}
$arSqlSelect = array();
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . ' AS ' . $field;
}
}
if (!sizeof($arSqlSelect)) {
$arSqlSelect = 'LGL.LESSON_ID AS LESSON_ID';
}
$arSqlSearch = self::getFilter($arFilter);
$strSql = "\n\t\t\tSELECT \n\t\t\t\t" . implode(",\n", $arSqlSelect);
$strFrom = "\n\t\t\tFROM\n\t\t\t\tb_learn_groups_lesson LGL\n\t\t\t\t" . (sizeof($arSqlSearch) ? " WHERE " . implode(" AND ", $arSqlSearch) : "") . " ";
$strSql .= $strFrom;
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0, $arSqlOrderCnt = count($arSqlOrder); $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
if (count($arNavParams)) {
if (isset($arNavParams['nTopCount'])) {
$strSql = $DB->TopSql($strSql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($strSql, $bIgnoreErrors = false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
} else {
$res_cnt = $DB->Query("SELECT COUNT(LGL.ID) as C " . $strFrom);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$rc = $res->NavQuery($strSql, $res_cnt["C"], $arNavParams, $bIgnoreErrors = false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
} else {
$res = $DB->Query($strSql, $bIgnoreErrors = false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
return $res;
}
public static function GetListEx($arOrder=array("SORT"=>"ASC"), $arFilter=array())
{
return false;
global $DB, $USER;
$arSqlSearch = CIBlockElement::MkFilter($arFilter);
$bSections = false;
if($arSqlSearch["SECTION"]=="Y")
{
$bSections = true;
unset($arSqlSearch["SECTION"]);
}
$strSqlSearch = "";
for ($i = 0, $intCount = count($arSqlSearch); $i < $intCount; $i++)
$strSqlSearch .= " AND (".$arSqlSearch[$i].") ";
$MAX_LOCK = intval(COption::GetOptionString("workflow", "MAX_LOCK_TIME", "60"));
$uid = intval($USER->GetID());
$db_groups = CCatalogGroup::GetList(array("SORT" => "ASC"));
$strSelectPart = "";
$strFromPart = "";
$i = -1;
while ($groups = $db_groups->Fetch())
{
$i++;
$strSelectPart .= ", P".$i.".PRICE as PRICE".$i.", P".$i.".CURRENCY as CURRENCY".$i.", P".$i.".CATALOG_GROUP_ID as CATALOG_GROUP_ID".$i.", P".$i.".ID as PRICE_ID".$i." ";
$strFromPart .= " LEFT JOIN b_catalog_price P".$i." ON (P".$i.".PRODUCT_ID = BE.ID AND P".$i.".CATALOG_GROUP_ID = ".$groups["ID"].") ";
}
$maxInd = $i;
if (!$USER->IsAdmin())
{
$strSql =
"SELECT DISTINCT BE.*, ".
" ".$DB->DateToCharFunction("BE.TIMESTAMP_X")." as TIMESTAMP_X, ".
" ".$DB->DateToCharFunction("BE.ACTIVE_FROM", "SHORT")." as ACTIVE_FROM, ".
" ".$DB->DateToCharFunction("BE.ACTIVE_TO", "SHORT")." as ACTIVE_TO, ".
" ".$DB->DateToCharFunction("BE.WF_DATE_LOCK")." as WF_DATE_LOCK, ".
" L.DIR as LANG_DIR, B.DETAIL_PAGE_URL, B.LIST_PAGE_URL, ".
" CAP.QUANTITY, CAP.QUANTITY_RESERVED, IF (CAP.QUANTITY_TRACE = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','default_quantity_trace','N'))."', CAP.QUANTITY_TRACE) as QUANTITY_TRACE, CAP.WEIGHT, ".
" IF (CAP.CAN_BUY_ZERO = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','default_can_buy_zero','N'))."', CAP.CAN_BUY_ZERO) as CAN_BUY_ZERO, ".
" IF (CAP.NEGATIVE_AMOUNT_TRACE = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','allow_negative_amount','N'))."', CAP.NEGATIVE_AMOUNT_TRACE) as NEGATIVE_AMOUNT_TRACE, ".
" CAP.VAT_ID, CAP.VAT_INCLUDED, ".
" CAP.PRICE_TYPE, CAP.RECUR_SCHEME_TYPE, CAP.RECUR_SCHEME_LENGTH, CAP.TRIAL_PRICE_ID, ".
" CAP.WITHOUT_ORDER, CAP.SELECT_BEST_PRICE, CAP.PURCHASING_PRICE, CAP.PURCHASING_CURRENCY, CAP.BARCODE_MULTI, ".
" CAP.TMP_ID ".
" ".$strSelectPart." ".
"FROM b_iblock_element BE, b_lang L, ".
($bSections?"b_iblock_section_element BSE,":"").
" b_iblock B ".
" LEFT JOIN b_iblock_group IBG ON IBG.IBLOCK_ID = B.ID ".
" LEFT JOIN b_catalog_product CAP ON BE.ID = CAP.ID ".
" ".$strFromPart." ".
"WHERE BE.IBLOCK_ID = B.ID ".
" AND B.LID = L.LID ".
($bSections?" AND BSE.IBLOCK_ELEMENT_ID = BE.ID ":"").
" AND IBG.GROUP_ID IN (".$USER->GetGroups().") ".
" ".CIBlockElement::WF_GetSqlLimit("BE.", $SHOW_NEW)." ".
" AND IBG.PERMISSION>='".(strlen($arFilter["MIN_PERMISSION"])==1 ? $arFilter["MIN_PERMISSION"] : "R")."' ".
" AND (IBG.PERMISSION='X' OR B.ACTIVE='Y') ".
" ".$strSqlSearch." ";
}
else
{
$strSql =
"SELECT BE.*, ".
" ".$DB->DateToCharFunction("BE.TIMESTAMP_X")." as TIMESTAMP_X, ".
" ".$DB->DateToCharFunction("BE.ACTIVE_FROM", "SHORT")." as ACTIVE_FROM, ".
" ".$DB->DateToCharFunction("BE.ACTIVE_TO", "SHORT")." as ACTIVE_TO, ".
" ".$DB->DateToCharFunction("BE.WF_DATE_LOCK")." as WF_DATE_LOCK, ".
" L.DIR as LANG_DIR, B.DETAIL_PAGE_URL, B.LIST_PAGE_URL, ".
" CAP.QUANTITY, CAP.QUANTITY_RESERVED, IF (CAP.QUANTITY_TRACE = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','default_quantity_trace','N'))."', CAP.QUANTITY_TRACE) as QUANTITY_TRACE, CAP.WEIGHT, ".
" IF (CAP.CAN_BUY_ZERO = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','default_can_buy_zero','N'))."', CAP.CAN_BUY_ZERO) as CAN_BUY_ZERO, ".
" IF (CAP.NEGATIVE_AMOUNT_TRACE = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','allow_negative_amount','N'))."', CAP.NEGATIVE_AMOUNT_TRACE) as NEGATIVE_AMOUNT_TRACE, ".
" CAP.VAT_ID, CAP.VAT_INCLUDED, ".
" CAP.PRICE_TYPE, CAP.RECUR_SCHEME_TYPE, CAP.RECUR_SCHEME_LENGTH, CAP.TRIAL_PRICE_ID, ".
" CAP.WITHOUT_ORDER, CAP.SELECT_BEST_PRICE, CAP.PURCHASING_PRICE, CAP.PURCHASING_CURRENCY, CAP.BARCODE_MULTI, ".
" CAP.TMP_ID ".
" ".$strSelectPart." ".
"FROM b_iblock B, b_lang L, ".
($bSections?"b_iblock_section_element BSE,":"").
" b_iblock_element BE ".
" LEFT JOIN b_catalog_product CAP ON BE.ID = CAP.ID ".
" ".$strFromPart." ".
"WHERE BE.IBLOCK_ID = B.ID ".
($bSections?" AND BSE.IBLOCK_ELEMENT_ID = BE.ID ":"").
" ".CIBlockElement::WF_GetSqlLimit("BE.",$SHOW_NEW)." ".
" AND B.LID = L.LID ".
" ".$strSqlSearch." ";
}
$arSqlOrder = array();
foreach($arOrder as $by=>$order)
{
$by = strtoupper($by);
$order = strtoupper($order);
if ($order!="ASC") $order = "DESC";
if ($by == "ID") $arSqlOrder[] = " BE.ID ".$order." ";
elseif ($by == "SECTION") $arSqlOrder[] = " BE.IBLOCK_SECTION_ID ".$order." ";
elseif ($by == "NAME") $arSqlOrder[] = " BE.NAME ".$order." ";
elseif ($by == "STATUS") $arSqlOrder[] = " BE.WF_STATUS_ID ".$order." ";
elseif ($by == "MODIFIED_BY") $arSqlOrder[] = " BE.MODIFIED_BY ".$order." ";
elseif ($by == "ACTIVE") $arSqlOrder[] = " BE.ACTIVE ".$order." ";
elseif ($by == "ACTIVE_FROM") $arSqlOrder[] = " BE.ACTIVE_FROM ".$order." ";
elseif ($by == "ACTIVE_TO") $arSqlOrder[] = " BE.ACTIVE_TO ".$order." ";
elseif ($by == "SORT") $arSqlOrder[] = " BE.SORT ".$order." ";
elseif (substr($by, 0, 5) == "PRICE" && intval(substr($by, 5))<=$maxInd)
{
$indx = intval(substr($by, 5));
$arSqlOrder[] = " P".$indx.".PRICE ".$order." ";
}
elseif (substr($by, 0, 8) == "CURRENCY" && intval(substr($by, 8))<=$maxInd)
{
$indx = intval(substr($by, 8));
$arSqlOrder[] = " P".$indx.".CURRENCY ".$order." ";
}
else
{
$arSqlOrder[] = " BE.ID ".$order." ";
$by = "ID";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0, $intCount = count($arSqlOrder); $i < $intCount; $i++)
{
if($i==0)
$strSqlOrder = " ORDER BY ";
else
$strSqlOrder .= ",";
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
$res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
return $res;
}
public static function GetList($arOrder, $arFilter)
{
global $DB;
$arSqlSearch = array_filter(CTaskMembers::GetFilter($arFilter));
$strSqlSearch = "";
$arSqlSearchCnt = count($arSqlSearch);
for ($i = 0; $i < $arSqlSearchCnt; $i++) {
if (strlen($arSqlSearch[$i]) > 0) {
$strSqlSearch .= " AND " . $arSqlSearch[$i] . " ";
}
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\tTM.*\n\t\t\tFROM\n\t\t\t\tb_tasks_member TM\n\t\t\t" . (sizeof($arSqlSearch) ? "WHERE " . implode(" AND ", $arSqlSearch) : "") . "\n\t\t";
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by === 'task' || $by === 'task_id') {
$arSqlOrder[] = " TM.TASK_ID " . $order . " ";
} elseif ($by === 'user' || $by === 'user_id') {
$arSqlOrder[] = " TM.USER_ID " . $order . " ";
} elseif ($by === 'type') {
$arSqlOrder[] = " TM.TYPE " . $order . " ";
} elseif ($by == "rand") {
$arSqlOrder[] = CTasksTools::getRandFunction();
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$arSqlOrderCnt = count($arSqlOrder);
for ($i = 0; $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
