本文整理汇总了PHP中DelDuplicateSort函数的典型用法代码示例。如果您正苦于以下问题：PHP DelDuplicateSort函数的具体用法？PHP DelDuplicateSort怎么用？PHP DelDuplicateSort使用的例子？那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了DelDuplicateSort函数的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: GetList
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;
}示例2: GetList
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__);
}示例3: PrepareSql
//.........这里部分代码省略.........
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
);
}示例4: GetListUni
//.........这里部分代码省略.........
$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;
}示例5: GetList
//.........这里部分代码省略.........
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;
}示例6: PrepareSql
//.........这里部分代码省略.........
$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);
}示例7: GetList
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 .= ",";
//.........这里部分代码省略.........
示例8: GetListEx
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;
}
//.........这里部分代码省略.........
示例9: GetList
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__);
}示例10: GetList
//.........这里部分代码省略.........
}
$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;
}示例11: _GetList
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;
}示例12: PrepareSql
//.........这里部分代码省略.........
$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))示例13: GetListEx
//.........这里部分代码省略.........
$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);
}示例14: GetListEx
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;
}示例15: GetList
//.........这里部分代码省略.........
*
* <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:".$arCertification["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:".$arCertification["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;
}