PHP CUserTypeSQL::getOrder方法代码示例

//.........这里部分代码省略.........
  *     {
  *         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;
 }

//.........这里部分代码省略.........
         $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;
 }

 /**
  * Get list of existing learning groups
  * 
  * @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, $USER_FIELD_MANAGER;
     $obUserFieldsSql = new CUserTypeSQL();
     $obUserFieldsSql->SetEntity("LEARNING_LGROUPS", "LG.ID");
     $obUserFieldsSql->SetSelect($arSelect);
     $obUserFieldsSql->SetFilter($arFilter);
     $obUserFieldsSql->SetOrder($arOrder);
     $arFields = array('ID' => 'LG.ID', 'TITLE' => 'LG.TITLE', 'ACTIVE' => 'LG.ACTIVE', 'CODE' => 'LG.CODE', 'SORT' => 'LG.SORT', 'ACTIVE_FROM' => $DB->DateToCharFunction('LG.ACTIVE_FROM', 'FULL'), 'ACTIVE_TO' => $DB->DateToCharFunction('LG.ACTIVE_TO', 'FULL'), 'COURSE_LESSON_ID' => 'LG.COURSE_LESSON_ID', 'COURSE_TITLE' => 'LL.NAME', 'MEMBER_ID' => 'LGM.USER_ID');
     $arFieldsSort = $arFields;
     $arFieldsSort["ACTIVE_FROM"] = "LG.ACTIVE_FROM";
     $arFieldsSort["ACTIVE_TO"] = "LG.ACTIVE_TO";
     if (count($arSelect) <= 0 || in_array("*", $arSelect)) {
         $arSelect = array_diff(array_keys($arFields), array('MEMBER_ID'));
     } elseif (!in_array("ID", $arSelect)) {
         $arSelect[] = "ID";
     }
     if (!is_array($arOrder)) {
         $arOrder = array();
     }
     foreach ($arOrder as $by => $order) {
         $by = (string) $by;
         $byUppercase = strtoupper($by);
         $needle = null;
         $order = strtolower($order);
         if ($order != "asc") {
             $order = "desc";
         }
         if (array_key_exists($byUppercase, $arFieldsSort)) {
             $arSqlOrder[] = ' ' . $arFieldsSort[$byUppercase] . ' ' . $order . ' ';
             $needle = $byUppercase;
         } elseif ($s = $obUserFieldsSql->getOrder(strtolower($by))) {
             $arSqlOrder[] = ' ' . $s . ' ' . $order . ' ';
         }
         if ($needle !== null && !in_array($needle, $arSelect, true)) {
             $arSelect[] = $needle;
         }
     }
     if (isset($arFilter['MEMBER_ID']) && !in_array('MEMBER_ID', $arSelect, true)) {
         $arSelect[] = 'MEMBER_ID';
     }
     $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 = 'LG.ID AS ID';
     }
     $arSqlSearch = self::getFilter($arFilter);
     $r = $obUserFieldsSql->GetFilter();
     if (strlen($r) > 0) {
         $arSqlSearch[] = "(" . $r . ")";
     }
     $strSql = "\n\t\t\tSELECT\n\t\t\t\t" . implode(",\n", $arSqlSelect) . "\n\t\t\t\t" . $obUserFieldsSql->GetSelect();
     $strFrom = "\n\t\t\tFROM\n\t\t\t\tb_learn_groups LG\n\t\t\t\t";
     if (in_array('COURSE_TITLE', $arSelect, true)) {
         $strFrom .= "LEFT OUTER JOIN b_learn_lesson LL ON LL.ID = LG.COURSE_LESSON_ID \n";
     }
     if (in_array('MEMBER_ID', $arSelect, true)) {
         $strFrom .= "LEFT JOIN b_learn_groups_member LGM ON LGM.LEARNING_GROUP_ID = LG.ID \n";
     }
     $strFrom .= $obUserFieldsSql->GetJoin("LG.ID") . " " . (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__);
             $res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("LEARNING_LGROUPS"));
         } else {
             $res_cnt = $DB->Query("SELECT COUNT(LG.ID) as C " . $strFrom);
             $res_cnt = $res_cnt->Fetch();
             $res = new CDBResult();
             $res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("LEARNING_LGROUPS"));
             $rc = $res->NavQuery($strSql, $res_cnt["C"], $arNavParams, $bIgnoreErrors = false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
         }
     } else {
//.........这里部分代码省略.........