CoursesAndSessionsCatalog.class.php 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use Chamilo\CoreBundle\Entity\ExtraField;
  4. use Doctrine\ORM\Query\Expr\Join;
  5. /**
  6. * @todo change class name
  7. * Class CoursesAndSessionsCatalog
  8. */
  9. class CoursesAndSessionsCatalog
  10. {
  11. public const PAGE_LENGTH = 12;
  12. /**
  13. * Check the configuration for the courses and sessions catalog.
  14. *
  15. * @global array $_configuration Configuration
  16. *
  17. * @param int $value The value to check
  18. *
  19. * @return bool Whether the configuration is $value
  20. */
  21. public static function is($value = CATALOG_COURSES)
  22. {
  23. $showCoursesSessions = (int) api_get_setting('catalog_show_courses_sessions');
  24. if ($showCoursesSessions == $value) {
  25. return true;
  26. }
  27. return false;
  28. }
  29. /**
  30. * Check whether to display the sessions list.
  31. *
  32. * @global array $_configuration Configuration
  33. *
  34. * @return bool whether to display
  35. */
  36. public static function showSessions()
  37. {
  38. $catalogShow = (int) api_get_setting('catalog_show_courses_sessions');
  39. if ($catalogShow == CATALOG_SESSIONS || $catalogShow == CATALOG_COURSES_SESSIONS) {
  40. return true;
  41. }
  42. return false;
  43. }
  44. /**
  45. * Check whether to display the courses list.
  46. *
  47. * @global array $_configuration Configuration
  48. *
  49. * @return bool whether to display
  50. */
  51. public static function showCourses()
  52. {
  53. $catalogShow = (int) api_get_setting('catalog_show_courses_sessions');
  54. if ($catalogShow == CATALOG_COURSES || $catalogShow == CATALOG_COURSES_SESSIONS) {
  55. return true;
  56. }
  57. return false;
  58. }
  59. /**
  60. * @return array
  61. */
  62. public static function getCoursesToAvoid()
  63. {
  64. $TABLE_COURSE_FIELD = Database::get_main_table(TABLE_EXTRA_FIELD);
  65. $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  66. // Check special courses
  67. $courseListToAvoid = CourseManager::get_special_course_list();
  68. // Checks "hide_from_catalog" extra field
  69. $extraFieldType = ExtraField::COURSE_FIELD_TYPE;
  70. $sql = "SELECT item_id FROM $TABLE_COURSE_FIELD_VALUE tcfv
  71. INNER JOIN $TABLE_COURSE_FIELD tcf
  72. ON tcfv.field_id = tcf.id
  73. WHERE
  74. tcf.extra_field_type = $extraFieldType AND
  75. tcf.variable = 'hide_from_catalog' AND
  76. tcfv.value = 1
  77. ";
  78. $result = Database::query($sql);
  79. if (Database::num_rows($result) > 0) {
  80. while ($row = Database::fetch_array($result)) {
  81. $courseListToAvoid[] = $row['item_id'];
  82. }
  83. }
  84. return $courseListToAvoid;
  85. }
  86. /**
  87. * @return string
  88. */
  89. public static function getAvoidCourseCondition()
  90. {
  91. $courseListToAvoid = self::getCoursesToAvoid();
  92. $condition = '';
  93. if (!empty($courseListToAvoid)) {
  94. $courses = [];
  95. foreach ($courseListToAvoid as $courseId) {
  96. $courses[] = '"'.$courseId.'"';
  97. }
  98. $condition = ' AND course.id NOT IN ('.implode(',', $courses).')';
  99. }
  100. return $condition;
  101. }
  102. /**
  103. * Get available le courses count.
  104. *
  105. * @param int $accessUrlId (optional)
  106. *
  107. * @return int Number of courses
  108. */
  109. public static function countAvailableCoursesToShowInCatalog($accessUrlId = 1)
  110. {
  111. $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
  112. $tableCourseRelAccessUrl = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  113. $courseToAvoidCondition = self::getAvoidCourseCondition();
  114. $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
  115. $accessUrlId = (int) $accessUrlId;
  116. if (empty($accessUrlId)) {
  117. $accessUrlId = 1;
  118. }
  119. $sql = "SELECT count(course.id)
  120. FROM $tableCourse course
  121. INNER JOIN $tableCourseRelAccessUrl u
  122. ON (course.id = u.c_id)
  123. WHERE
  124. u.access_url_id = $accessUrlId AND
  125. course.visibility != 0 AND
  126. course.visibility != 4
  127. $courseToAvoidCondition
  128. $visibilityCondition
  129. ";
  130. $res = Database::query($sql);
  131. $row = Database::fetch_row($res);
  132. return $row[0];
  133. }
  134. public static function getCourseCategoriesTree()
  135. {
  136. $urlId = 1;
  137. if (api_is_multiple_url_enabled()) {
  138. $urlId = api_get_current_access_url_id();
  139. }
  140. $countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
  141. $categories = [];
  142. $list = [];
  143. $categories['ALL'] = [
  144. 'id' => 0,
  145. 'name' => get_lang('Display all'),
  146. 'code' => 'ALL',
  147. 'parent_id' => null,
  148. 'tree_pos' => 0,
  149. 'number_courses' => $countCourses,
  150. 'level' => 0,
  151. ];
  152. $allCategories = CourseCategory::getAllCategories();
  153. foreach ($allCategories as $category) {
  154. if (empty($category['parent_id'])) {
  155. $list[$category['code']] = $category;
  156. $list[$category['code']]['level'] = 0;
  157. list($subList, $childrenCount) = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
  158. foreach ($subList as $item) {
  159. $list[$item['code']] = $item;
  160. }
  161. // Real course count
  162. $countCourses = CourseCategory::countCoursesInCategory($category['code']);
  163. $list[$category['code']]['number_courses'] = $childrenCount + $countCourses;
  164. }
  165. }
  166. // count courses that are in no category
  167. $countCourses = CourseCategory::countCoursesInCategory();
  168. $categories['NONE'] = [
  169. 'id' => 0,
  170. 'name' => get_lang('Without category'),
  171. 'code' => 'NONE',
  172. 'parent_id' => null,
  173. 'tree_pos' => 0,
  174. 'children_count' => 0,
  175. 'auth_course_child' => true,
  176. 'auth_cat_child' => true,
  177. 'number_courses' => $countCourses,
  178. 'level' => 0,
  179. ];
  180. $result = array_merge($list, $categories);
  181. return $result;
  182. }
  183. /**
  184. * @return array
  185. */
  186. public static function getCourseCategories()
  187. {
  188. $urlId = 1;
  189. if (api_is_multiple_url_enabled()) {
  190. $urlId = api_get_current_access_url_id();
  191. }
  192. $countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
  193. $categories = [];
  194. $categories[0][0] = [
  195. 'id' => 0,
  196. 'name' => get_lang('Display all'),
  197. 'code' => 'ALL',
  198. 'parent_id' => null,
  199. 'tree_pos' => 0,
  200. 'count_courses' => $countCourses,
  201. ];
  202. $categoriesFromDatabase = CourseCategory::getCategories();
  203. foreach ($categoriesFromDatabase as $row) {
  204. $countCourses = CourseCategory::countCoursesInCategory($row['code']);
  205. $row['count_courses'] = $countCourses;
  206. if (empty($row['parent_id'])) {
  207. $categories[0][$row['tree_pos']] = $row;
  208. } else {
  209. $categories[$row['parent_id']][$row['tree_pos']] = $row;
  210. }
  211. }
  212. // count courses that are in no category
  213. $countCourses = CourseCategory::countCoursesInCategory();
  214. $categories[0][count($categories[0]) + 1] = [
  215. 'id' => 0,
  216. 'name' => get_lang('none'),
  217. 'code' => 'NONE',
  218. 'parent_id' => null,
  219. 'tree_pos' => $row['tree_pos'] + 1,
  220. 'children_count' => 0,
  221. 'auth_course_child' => true,
  222. 'auth_cat_child' => true,
  223. 'count_courses' => $countCourses,
  224. ];
  225. return $categories;
  226. }
  227. /**
  228. * Return LIMIT to filter SQL query.
  229. *
  230. * @param array $limit
  231. *
  232. * @return string
  233. */
  234. public static function getLimitFilterFromArray($limit)
  235. {
  236. $limitFilter = '';
  237. if (!empty($limit) && is_array($limit)) {
  238. $limitStart = isset($limit['start']) ? (int) $limit['start'] : 0;
  239. $limitLength = isset($limit['length']) ? (int) $limit['length'] : 12;
  240. $limitFilter = 'LIMIT '.$limitStart.', '.$limitLength;
  241. }
  242. return $limitFilter;
  243. }
  244. /**
  245. * @param string $category_code
  246. * @param int $random_value
  247. * @param array $limit will be used if $random_value is not set.
  248. * This array should contains 'start' and 'length' keys
  249. *
  250. * @return array
  251. */
  252. public static function getCoursesInCategory($category_code, $random_value = null, $limit = [])
  253. {
  254. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  255. $avoidCoursesCondition = self::getAvoidCourseCondition();
  256. $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
  257. if (!empty($random_value)) {
  258. $random_value = (int) $random_value;
  259. $sql = "SELECT COUNT(*) FROM $tbl_course";
  260. $result = Database::query($sql);
  261. list($num_records) = Database::fetch_row($result);
  262. if (api_is_multiple_url_enabled()) {
  263. $urlId = api_get_current_access_url_id();
  264. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  265. $urlCondition = ' access_url_id = '.$urlId.' ';
  266. $allowBaseCategories = api_get_configuration_value('allow_base_course_category');
  267. if ($allowBaseCategories) {
  268. $urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1) ';
  269. }
  270. $sql = "SELECT COUNT(*) FROM $tbl_course course
  271. INNER JOIN $tbl_url_rel_course as url_rel_course
  272. ON (url_rel_course.c_id = course.id)
  273. WHERE access_url_id = '.$urlId.' ";
  274. $result = Database::query($sql);
  275. list($num_records) = Database::fetch_row($result);
  276. $sql = "SELECT course.id, course.id as real_id
  277. FROM $tbl_course course
  278. INNER JOIN $tbl_url_rel_course as url_rel_course
  279. ON (url_rel_course.c_id = course.id)
  280. WHERE
  281. $urlCondition AND
  282. RAND()*$num_records< $random_value
  283. $avoidCoursesCondition
  284. $visibilityCondition
  285. ORDER BY RAND()
  286. LIMIT 0, $random_value";
  287. } else {
  288. $sql = "SELECT id, id as real_id FROM $tbl_course course
  289. WHERE
  290. RAND()*$num_records< $random_value
  291. $avoidCoursesCondition
  292. $visibilityCondition
  293. ORDER BY RAND()
  294. LIMIT 0, $random_value";
  295. }
  296. $result = Database::query($sql);
  297. $id_in = null;
  298. while (list($id) = Database::fetch_row($result)) {
  299. if ($id_in) {
  300. $id_in .= ",$id";
  301. } else {
  302. $id_in = "$id";
  303. }
  304. }
  305. if ($id_in === null) {
  306. return [];
  307. }
  308. $sql = "SELECT *, id as real_id FROM $tbl_course WHERE id IN($id_in)";
  309. } else {
  310. $limitFilter = self::getLimitFilterFromArray($limit);
  311. $category_code = Database::escape_string($category_code);
  312. $listCode = self::childrenCategories($category_code);
  313. $conditionCode = ' ';
  314. if (empty($listCode)) {
  315. if ($category_code === 'NONE') {
  316. $conditionCode .= " category_code='' ";
  317. } else {
  318. $conditionCode .= " category_code='$category_code' ";
  319. }
  320. } else {
  321. foreach ($listCode as $code) {
  322. $conditionCode .= " category_code='$code' OR ";
  323. }
  324. $conditionCode .= " category_code='$category_code' ";
  325. }
  326. if (empty($category_code) || $category_code == 'ALL') {
  327. $sql = "SELECT *, id as real_id
  328. FROM $tbl_course course
  329. WHERE
  330. 1=1
  331. $avoidCoursesCondition
  332. $visibilityCondition
  333. ORDER BY title $limitFilter ";
  334. } else {
  335. $sql = "SELECT *, id as real_id FROM $tbl_course course
  336. WHERE
  337. $conditionCode
  338. $avoidCoursesCondition
  339. $visibilityCondition
  340. ORDER BY title $limitFilter ";
  341. }
  342. // Showing only the courses of the current Chamilo access_url_id
  343. if (api_is_multiple_url_enabled()) {
  344. $urlId = api_get_current_access_url_id();
  345. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  346. $urlCondition = ' access_url_id = '.$urlId.' ';
  347. if ($category_code != 'ALL') {
  348. $sql = "SELECT *, course.id real_id FROM $tbl_course as course
  349. INNER JOIN $tbl_url_rel_course as url_rel_course
  350. ON (url_rel_course.c_id = course.id)
  351. WHERE
  352. $urlCondition AND
  353. $conditionCode
  354. $avoidCoursesCondition
  355. $visibilityCondition
  356. ORDER BY title $limitFilter";
  357. } else {
  358. $sql = "SELECT *, course.id real_id FROM $tbl_course as course
  359. INNER JOIN $tbl_url_rel_course as url_rel_course
  360. ON (url_rel_course.c_id = course.id)
  361. WHERE
  362. $urlCondition
  363. $avoidCoursesCondition
  364. $visibilityCondition
  365. ORDER BY title $limitFilter";
  366. }
  367. }
  368. }
  369. $result = Database::query($sql);
  370. $courses = [];
  371. while ($row = Database::fetch_array($result)) {
  372. $row['registration_code'] = !empty($row['registration_code']);
  373. $count_users = CourseManager::get_users_count_in_course($row['code']);
  374. $count_connections_last_month = Tracking::get_course_connections_count(
  375. $row['id'],
  376. 0,
  377. api_get_utc_datetime(time() - (30 * 86400))
  378. );
  379. if ($row['tutor_name'] == '0') {
  380. $row['tutor_name'] = get_lang('No administrator');
  381. }
  382. $point_info = CourseManager::get_course_ranking($row['id'], 0);
  383. $courses[] = [
  384. 'real_id' => $row['real_id'],
  385. 'point_info' => $point_info,
  386. 'code' => $row['code'],
  387. 'directory' => $row['directory'],
  388. 'visual_code' => $row['visual_code'],
  389. 'title' => $row['title'],
  390. 'tutor' => $row['tutor_name'],
  391. 'subscribe' => $row['subscribe'],
  392. 'unsubscribe' => $row['unsubscribe'],
  393. 'registration_code' => $row['registration_code'],
  394. 'creation_date' => $row['creation_date'],
  395. 'visibility' => $row['visibility'],
  396. 'category' => $row['category_code'],
  397. 'count_users' => $count_users,
  398. 'count_connections' => $count_connections_last_month,
  399. ];
  400. }
  401. return $courses;
  402. }
  403. /**
  404. * Search the courses database for a course that matches the search term.
  405. * The search is done on the code, title and tutor field of the course table.
  406. *
  407. * @param string $search_term The string that the user submitted, what we are looking for
  408. * @param array $limit
  409. * @param bool $justVisible search only on visible courses in the catalogue
  410. *
  411. * @return array an array containing a list of all the courses matching the the search term
  412. */
  413. public static function search_courses($search_term, $limit, $justVisible = false)
  414. {
  415. $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
  416. $limitFilter = self::getLimitFilterFromArray($limit);
  417. $avoidCoursesCondition = self::getAvoidCourseCondition();
  418. $visibilityCondition = $justVisible ? CourseManager::getCourseVisibilitySQLCondition('course', true) : '';
  419. $search_term_safe = Database::escape_string($search_term);
  420. $sql = "SELECT * FROM $courseTable course
  421. WHERE (
  422. course.code LIKE '%".$search_term_safe."%' OR
  423. course.title LIKE '%".$search_term_safe."%' OR
  424. course.tutor_name LIKE '%".$search_term_safe."%'
  425. )
  426. $avoidCoursesCondition
  427. $visibilityCondition
  428. ORDER BY title, visual_code ASC
  429. $limitFilter
  430. ";
  431. if (api_is_multiple_url_enabled()) {
  432. $urlId = api_get_current_access_url_id();
  433. if ($urlId != -1) {
  434. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  435. $urlCondition = ' access_url_id = '.$urlId.' AND';
  436. $allowBaseCategories = api_get_configuration_value('allow_base_course_category');
  437. if ($allowBaseCategories) {
  438. $urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1) AND ';
  439. }
  440. $sql = "SELECT course.*
  441. FROM $courseTable as course
  442. INNER JOIN $tbl_url_rel_course as url_rel_course
  443. ON (url_rel_course.c_id = course.id)
  444. WHERE
  445. access_url_id = $urlId AND
  446. (
  447. code LIKE '%".$search_term_safe."%' OR
  448. title LIKE '%".$search_term_safe."%' OR
  449. tutor_name LIKE '%".$search_term_safe."%'
  450. )
  451. $avoidCoursesCondition
  452. $visibilityCondition
  453. ORDER BY title, visual_code ASC
  454. $limitFilter
  455. ";
  456. }
  457. }
  458. $result_find = Database::query($sql);
  459. $courses = [];
  460. while ($row = Database::fetch_array($result_find)) {
  461. $row['registration_code'] = !empty($row['registration_code']);
  462. $countUsers = CourseManager::get_user_list_from_course_code(
  463. $row['code'],
  464. 0,
  465. null,
  466. null,
  467. null,
  468. true
  469. );
  470. $connectionsLastMonth = Tracking::get_course_connections_count(
  471. $row['id'],
  472. 0,
  473. api_get_utc_datetime(time() - (30 * 86400))
  474. );
  475. $point_info = CourseManager::get_course_ranking($row['id'], 0);
  476. $courses[] = [
  477. 'real_id' => $row['id'],
  478. 'point_info' => $point_info,
  479. 'code' => $row['code'],
  480. 'directory' => $row['directory'],
  481. 'visual_code' => $row['visual_code'],
  482. 'title' => $row['title'],
  483. 'tutor' => $row['tutor_name'],
  484. 'subscribe' => $row['subscribe'],
  485. 'unsubscribe' => $row['unsubscribe'],
  486. 'registration_code' => $row['registration_code'],
  487. 'creation_date' => $row['creation_date'],
  488. 'visibility' => $row['visibility'],
  489. 'count_users' => $countUsers,
  490. 'count_connections' => $connectionsLastMonth,
  491. ];
  492. }
  493. return $courses;
  494. }
  495. /**
  496. * List the sessions.
  497. *
  498. * @param string $date
  499. * @param array $limit
  500. * @param bool $returnQueryBuilder
  501. * @param bool $getCount
  502. *
  503. * @return array|\Doctrine\ORM\Query The session list
  504. */
  505. public static function browseSessions($date = null, $limit = [], $returnQueryBuilder = false, $getCount = false)
  506. {
  507. $urlId = api_get_current_access_url_id();
  508. $select = 's';
  509. if ($getCount) {
  510. $select = 'count(s) ';
  511. }
  512. $dql = "SELECT $select
  513. FROM ChamiloCoreBundle:Session s
  514. WHERE EXISTS
  515. (
  516. SELECT url.sessionId FROM ChamiloCoreBundle:AccessUrlRelSession url
  517. WHERE url.sessionId = s.id AND url.accessUrlId = $urlId
  518. ) AND
  519. s.nbrCourses > 0
  520. ";
  521. if (!is_null($date)) {
  522. $date = Database::escape_string($date);
  523. $dql .= "
  524. AND (
  525. (s.accessEndDate IS NULL)
  526. OR
  527. (
  528. s.accessStartDate IS NOT NULL AND
  529. s.accessEndDate IS NOT NULL AND
  530. s.accessStartDate >= '$date' AND s.accessEndDate <= '$date')
  531. OR
  532. (
  533. s.accessStartDate IS NULL AND
  534. s.accessEndDate IS NOT NULL AND
  535. s.accessStartDate >= '$date'
  536. )
  537. )
  538. ";
  539. }
  540. $qb = Database::getManager()->createQuery($dql);
  541. if (!empty($limit)) {
  542. $qb
  543. ->setFirstResult($limit['start'])
  544. ->setMaxResults($limit['length'])
  545. ;
  546. }
  547. if ($returnQueryBuilder) {
  548. return $qb;
  549. }
  550. if ($getCount) {
  551. return $qb->getSingleScalarResult();
  552. }
  553. return $qb->getResult();
  554. }
  555. /**
  556. * Search sessions by the tags in their courses.
  557. *
  558. * @param string $termTag Term for search in tags
  559. * @param array $limit Limit info
  560. *
  561. * @return array The sessions
  562. */
  563. public static function browseSessionsByTags($termTag, array $limit)
  564. {
  565. $em = Database::getManager();
  566. $qb = $em->createQueryBuilder();
  567. $urlId = api_get_current_access_url_id();
  568. $sessions = $qb->select('s')
  569. ->distinct()
  570. ->from('ChamiloCoreBundle:Session', 's')
  571. ->innerJoin(
  572. 'ChamiloCoreBundle:SessionRelCourse',
  573. 'src',
  574. Join::WITH,
  575. 's.id = src.session'
  576. )
  577. ->innerJoin(
  578. 'ChamiloCoreBundle:AccessUrlRelSession',
  579. 'url',
  580. Join::WITH,
  581. 'url.sessionId = s.id'
  582. )
  583. ->innerJoin(
  584. 'ChamiloCoreBundle:ExtraFieldRelTag',
  585. 'frt',
  586. Join::WITH,
  587. 'src.course = frt.itemId'
  588. )
  589. ->innerJoin(
  590. 'ChamiloCoreBundle:Tag',
  591. 't',
  592. Join::WITH,
  593. 'frt.tagId = t.id'
  594. )
  595. ->innerJoin(
  596. 'ChamiloCoreBundle:ExtraField',
  597. 'f',
  598. Join::WITH,
  599. 'frt.fieldId = f.id'
  600. )
  601. ->where(
  602. $qb->expr()->like('t.tag', ':tag')
  603. )
  604. ->andWhere(
  605. $qb->expr()->eq('f.extraFieldType', ExtraField::COURSE_FIELD_TYPE)
  606. )->andWhere(
  607. $qb->expr()->eq('url.accessUrlId', $urlId)
  608. )
  609. ->setFirstResult($limit['start'])
  610. ->setMaxResults($limit['length'])
  611. ->setParameter('tag', "$termTag%")
  612. ->getQuery()
  613. ->getResult();
  614. $sessionsToBrowse = [];
  615. foreach ($sessions as $session) {
  616. if ($session->getNbrCourses() === 0) {
  617. continue;
  618. }
  619. $sessionsToBrowse[] = $session;
  620. }
  621. return $sessionsToBrowse;
  622. }
  623. /**
  624. * Build a recursive tree of course categories.
  625. *
  626. * @param array $categories
  627. * @param int $parentId
  628. * @param int $level
  629. *
  630. * @return array
  631. */
  632. public static function buildCourseCategoryTree($categories, $parentId = 0, $level = 0)
  633. {
  634. $list = [];
  635. $count = 0;
  636. $level++;
  637. foreach ($categories as $category) {
  638. if (empty($category['parent_id'])) {
  639. continue;
  640. }
  641. if ($category['parent_id'] == $parentId) {
  642. $list[$category['code']] = $category;
  643. $count += $category['number_courses'];
  644. $list[$category['code']]['level'] = $level;
  645. list($subList, $childrenCount) = self::buildCourseCategoryTree(
  646. $categories,
  647. $category['code'],
  648. $level
  649. );
  650. $list[$category['code']]['number_courses'] += $childrenCount;
  651. foreach ($subList as $item) {
  652. $list[$item['code']] = $item;
  653. }
  654. $count += $childrenCount;
  655. }
  656. }
  657. return [$list, $count];
  658. }
  659. /**
  660. * List Code Search Category.
  661. *
  662. * @param string $code
  663. *
  664. * @return array
  665. */
  666. public static function childrenCategories($code)
  667. {
  668. $allCategories = CourseCategory::getAllCategories();
  669. $list = [];
  670. $row = [];
  671. if ($code != 'ALL' and $code != 'NONE') {
  672. foreach ($allCategories as $category) {
  673. if ($category['code'] === $code) {
  674. $list = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
  675. }
  676. }
  677. foreach ($list[0] as $item) {
  678. $row[] = $item['code'];
  679. }
  680. }
  681. return $row;
  682. }
  683. }