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', // hack for compatibility with courses in shared lists
'name' => 'TL.NAME',
'code' => 'TL.CODE',
'active' => 'TL.ACTIVE',
'created' => 'TL.DATE_CREATE', // 'created' was in previous code, perhaps for back compatibility
'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',
// ! This will be overrided below to TLE.SORT in case of self::GET_LIST_IMMEDIATE_CHILDS_OF
'sort' => 'TC.SORT',
'linked_lesson_id' => 'TC.LINKED_LESSON_ID'
// This element is dynamically added below for case of self::GET_LIST_IMMEDIATE_CHILDS_OF
// 'edge_sort' => 'TLE.SORT'
);
$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', // hack for compatibility with courses in shared lists
'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);
$SqlSearchLang = '';
if (isset($arFilter['SITE_ID']))
{
$arLID = array();
if (is_array($arFilter['SITE_ID']))
$arLID = $arFilter['SITE_ID'];
else
{
if ($arFilter['SITE_ID'] <> '')
$arLID[] = $arFilter['SITE_ID'];
}
$SqlSearchLang = "''";
foreach ($arLID as $v)
$SqlSearchLang .= ", '" . $DB->ForSql($v) . "'";
}
$r = $obUserFieldsSql->GetFilter();
if ($r <> '')
$arSqlSearch[] = "(".$r.")";
$sqlSearch = '';
foreach ($arSqlSearch as $value)
{
if ($value <> '')
$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
ON TLE.SOURCE_NODE = TL.IDn";
$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
ON TLE.TARGET_NODE = TL.IDn";
$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 = mb_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 (mb_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 (mb_strlen($SqlSearchLang) > 2)
{
$sqlLangConstraint = "
AND
EXISTS
(
SELECT 'x' FROM b_learn_course_site TCS
WHERE TC.ID = TCS.COURSE_ID AND TCS.SITE_ID IN (" . $SqlSearchLang . ")
)
";
}
$strSqlFrom = "FROM b_learn_lesson TL
LEFT JOIN b_learn_course TC
ON TC.LINKED_LESSON_ID = TL.ID
LEFT JOIN b_user TU
ON TU.ID = TL.CREATED_BY "
. $modeSQL_join // for getting only parents/childs, if need
. $obUserFieldsSql->GetJoin("TL.ID")
. " WHERE 1 = 1 "
. $sqlLangConstraint // filter by site IDs
. $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 = mb_strtolower($by);
$order = mb_strtolower($order);
if ($order !== 'asc')
$order = 'desc';
if ($s = $obUserFieldsSql->getOrder(mb_strtolower($by)))
$arSqlOrder[] = ' ' . $s . ' ' . $order . ' ';
if (mb_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);
}