course_category.lib.php 40 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Class CourseCategory
  5. */
  6. class CourseCategory
  7. {
  8. /**
  9. * Returns the category fields from the database from an int ID
  10. * @param int $categoryId The category ID
  11. * @return array
  12. */
  13. public static function getCategoryById($categoryId)
  14. {
  15. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  16. $categoryId = intval($categoryId);
  17. $sql = "SELECT * FROM $tbl_category WHERE id = $categoryId";
  18. $result = Database::query($sql);
  19. if (Database::num_rows($result)) {
  20. return Database::fetch_array($result, 'ASSOC');
  21. }
  22. return [];
  23. }
  24. /**
  25. * Get category details from a simple category code
  26. * @param string $category The literal category code
  27. * @return array
  28. */
  29. public static function getCategory($category)
  30. {
  31. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  32. $category = Database::escape_string($category);
  33. $sql = "SELECT * FROM $tbl_category WHERE code ='$category'";
  34. $result = Database::query($sql);
  35. if (Database::num_rows($result)) {
  36. return Database::fetch_array($result, 'ASSOC');
  37. }
  38. return [];
  39. }
  40. /**
  41. * @param string $category
  42. *
  43. * @return array
  44. */
  45. public static function getCategories($category)
  46. {
  47. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  48. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  49. $category = Database::escape_string($category);
  50. $conditions = null;
  51. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  52. $conditions = " INNER JOIN $table a ON (t1.id = a.course_category_id)";
  53. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  54. $parentIdCondition = " AND (t1.parent_id IS NULL OR t1.parent_id = '' )";
  55. if (!empty($category)) {
  56. $parentIdCondition = " AND t1.parent_id = '$category' ";
  57. }
  58. $sql = "SELECT
  59. t1.name,
  60. t1.code,
  61. t1.parent_id,
  62. t1.tree_pos,
  63. t1.children_count,
  64. COUNT(DISTINCT t3.code) AS nbr_courses
  65. FROM $tbl_category t1
  66. $conditions
  67. LEFT JOIN $tbl_category t2
  68. ON t1.code = t2.parent_id
  69. LEFT JOIN $tbl_course t3
  70. ON t3.category_code=t1.code
  71. WHERE
  72. 1 = 1
  73. $parentIdCondition
  74. $whereCondition
  75. GROUP BY t1.name,
  76. t1.code,
  77. t1.parent_id,
  78. t1.tree_pos,
  79. t1.children_count
  80. ORDER BY t1.tree_pos";
  81. $result = Database::query($sql);
  82. $categories = Database::store_result($result);
  83. return $categories;
  84. }
  85. /**
  86. * @param string $code
  87. * @param string $name
  88. * @param string $canHaveCourses
  89. * @param int $parent_id
  90. *
  91. * @return bool
  92. */
  93. public static function addNode($code, $name, $canHaveCourses, $parent_id)
  94. {
  95. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  96. $code = trim($code);
  97. $name = trim($name);
  98. $parent_id = trim($parent_id);
  99. $code = CourseManager::generate_course_code($code);
  100. $sql = "SELECT 1 FROM $tbl_category
  101. WHERE code = '".Database::escape_string($code)."'";
  102. $result = Database::query($sql);
  103. if (Database::num_rows($result)) {
  104. return false;
  105. }
  106. $result = Database::query("SELECT MAX(tree_pos) AS maxTreePos FROM $tbl_category");
  107. $row = Database::fetch_array($result);
  108. $tree_pos = $row['maxTreePos'] + 1;
  109. $params = [
  110. 'name' => $name,
  111. 'code' => $code,
  112. 'parent_id' => empty($parent_id) ? null : $parent_id,
  113. 'tree_pos' => $tree_pos,
  114. 'children_count' => 0,
  115. 'auth_course_child' => $canHaveCourses,
  116. 'auth_cat_child' => 'TRUE'
  117. ];
  118. $categoryId = Database::insert($tbl_category, $params);
  119. self::updateParentCategoryChildrenCount($parent_id, 1);
  120. self::addToUrl($categoryId);
  121. return $categoryId;
  122. }
  123. /**
  124. * Recursive function that updates the count of children in the parent
  125. * @param string $categoryId Category ID
  126. * @param int $delta The number to add or delete (1 to add one, -1 to remove one)
  127. */
  128. public static function updateParentCategoryChildrenCount($categoryId, $delta = 1)
  129. {
  130. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  131. $categoryId = Database::escape_string($categoryId);
  132. $delta = intval($delta);
  133. // First get to the highest level possible in the tree
  134. $result = Database::query("SELECT parent_id FROM $tbl_category WHERE code = '$categoryId'");
  135. $row = Database::fetch_array($result);
  136. if ($row !== false and $row['parent_id'] != 0) {
  137. // if a parent was found, enter there to see if he's got one more parent
  138. self::updateParentCategoryChildrenCount($row['parent_id'], $delta);
  139. }
  140. // Now we're at the top, get back down to update each child
  141. //$children_count = courseCategoryChildrenCount($categoryId);
  142. if ($delta >= 0) {
  143. $sql = "UPDATE $tbl_category SET children_count = (children_count + $delta)
  144. WHERE code = '$categoryId'";
  145. } else {
  146. $sql = "UPDATE $tbl_category SET children_count = (children_count - ".abs($delta).")
  147. WHERE code = '$categoryId'";
  148. }
  149. Database::query($sql);
  150. }
  151. /**
  152. * @param string $node
  153. */
  154. public static function deleteNode($node)
  155. {
  156. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  157. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  158. $node = Database::escape_string($node);
  159. $result = Database::query("SELECT parent_id, tree_pos FROM $tbl_category WHERE code='$node'");
  160. if ($row = Database::fetch_array($result)) {
  161. if (!empty($row['parent_id'])) {
  162. Database::query(
  163. "UPDATE $tbl_course SET category_code = '".$row['parent_id']."' WHERE category_code='$node'"
  164. );
  165. Database::query("UPDATE $tbl_category SET parent_id='".$row['parent_id']."' WHERE parent_id='$node'");
  166. } else {
  167. Database::query("UPDATE $tbl_course SET category_code='' WHERE category_code='$node'");
  168. Database::query("UPDATE $tbl_category SET parent_id=NULL WHERE parent_id='$node'");
  169. }
  170. Database::query("UPDATE $tbl_category SET tree_pos=tree_pos-1 WHERE tree_pos > '".$row['tree_pos']."'");
  171. Database::query("DELETE FROM $tbl_category WHERE code='$node'");
  172. if (!empty($row['parent_id'])) {
  173. self::updateParentCategoryChildrenCount($row['parent_id'], -1);
  174. }
  175. }
  176. }
  177. /**
  178. * @param string $code
  179. * @param string $name
  180. * @param string $canHaveCourses
  181. * @param string $old_code
  182. * @return bool
  183. */
  184. public static function editNode($code, $name, $canHaveCourses, $old_code)
  185. {
  186. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  187. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  188. $code = trim(Database::escape_string($code));
  189. $name = trim(Database::escape_string($name));
  190. $old_code = Database::escape_string($old_code);
  191. $canHaveCourses = Database::escape_string($canHaveCourses);
  192. $code = CourseManager::generate_course_code($code);
  193. // Updating category
  194. $sql = "UPDATE $tbl_category SET
  195. name='$name',
  196. code='$code',
  197. auth_course_child = '$canHaveCourses'
  198. WHERE code = '$old_code'";
  199. Database::query($sql);
  200. // Updating children
  201. $sql = "UPDATE $tbl_category SET parent_id = '$code'
  202. WHERE parent_id = '$old_code'";
  203. Database::query($sql);
  204. // Updating course category
  205. $sql = "UPDATE $tbl_course SET category_code = '$code'
  206. WHERE category_code = '$old_code' ";
  207. Database::query($sql);
  208. return true;
  209. }
  210. /**
  211. * Move a node up on display
  212. * @param string $code
  213. * @param int $tree_pos
  214. * @param string $parent_id
  215. *
  216. * @return bool
  217. */
  218. public static function moveNodeUp($code, $tree_pos, $parent_id)
  219. {
  220. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  221. $code = Database::escape_string($code);
  222. $tree_pos = intval($tree_pos);
  223. $parent_id = Database::escape_string($parent_id);
  224. $parentIdCondition = " AND (parent_id IS NULL OR parent_id = '' )";
  225. if (!empty($parent_id)) {
  226. $parentIdCondition = " AND parent_id = '$parent_id' ";
  227. }
  228. $sql = "SELECT code,tree_pos
  229. FROM $tbl_category
  230. WHERE
  231. tree_pos < $tree_pos
  232. $parentIdCondition
  233. ORDER BY tree_pos DESC
  234. LIMIT 0,1";
  235. $result = Database::query($sql);
  236. if (!$row = Database::fetch_array($result)) {
  237. $sql = "SELECT code, tree_pos
  238. FROM $tbl_category
  239. WHERE
  240. tree_pos > $tree_pos
  241. $parentIdCondition
  242. ORDER BY tree_pos DESC
  243. LIMIT 0,1";
  244. $result2 = Database::query($sql);
  245. if (!$row = Database::fetch_array($result2)) {
  246. return false;
  247. }
  248. }
  249. $sql = "UPDATE $tbl_category
  250. SET tree_pos ='".$row['tree_pos']."'
  251. WHERE code='$code'";
  252. Database::query($sql);
  253. $sql = "UPDATE $tbl_category
  254. SET tree_pos = '$tree_pos'
  255. WHERE code= '".$row['code']."'";
  256. Database::query($sql);
  257. return true;
  258. }
  259. /**
  260. * Counts the number of children categories a category has
  261. * @param int $categoryId The ID of the category of which we want to count the children
  262. *
  263. * @return mixed The number of subcategories this category has
  264. */
  265. public static function courseCategoryChildrenCount($categoryId)
  266. {
  267. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  268. $categoryId = intval($categoryId);
  269. $count = 0;
  270. if (empty($categoryId)) {
  271. return 0;
  272. }
  273. $sql = "SELECT id, code FROM $tbl_category
  274. WHERE parent_id = $categoryId";
  275. $result = Database::query($sql);
  276. while ($row = Database::fetch_array($result)) {
  277. $count += self::courseCategoryChildrenCount($row['id']);
  278. }
  279. $sql = "UPDATE $tbl_category SET
  280. children_count = $count
  281. WHERE id = $categoryId";
  282. Database::query($sql);
  283. return $count + 1;
  284. }
  285. /**
  286. * @param string $categoryCode
  287. *
  288. * @return array
  289. */
  290. public static function getChildren($categoryCode)
  291. {
  292. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  293. $categoryCode = Database::escape_string($categoryCode);
  294. $sql = "SELECT code, id FROM $tbl_category
  295. WHERE parent_id = '$categoryCode'";
  296. $result = Database::query($sql);
  297. $children = [];
  298. while ($row = Database::fetch_array($result, 'ASSOC')) {
  299. $children[] = $row;
  300. $subChildren = self::getChildren($row['code']);
  301. $children = array_merge($children, $subChildren);
  302. }
  303. return $children;
  304. }
  305. /**
  306. * @param string $categoryCode
  307. *
  308. * @return array
  309. */
  310. public static function getParents($categoryCode)
  311. {
  312. if (empty($categoryCode)) {
  313. return [];
  314. }
  315. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  316. $categoryCode = Database::escape_string($categoryCode);
  317. $sql = "SELECT code, parent_id FROM $tbl_category
  318. WHERE code = '$categoryCode'";
  319. $result = Database::query($sql);
  320. $children = [];
  321. while ($row = Database::fetch_array($result, 'ASSOC')) {
  322. $parent = self::getCategory($row['parent_id']);
  323. $children[] = $row;
  324. $subChildren = self::getParents($parent ? $parent['code'] : null);
  325. $children = array_merge($children, $subChildren);
  326. }
  327. return $children;
  328. }
  329. /**
  330. * @param string $categoryCode
  331. * @return null|string
  332. */
  333. public static function getParentsToString($categoryCode)
  334. {
  335. $parents = self::getParents($categoryCode);
  336. if (!empty($parents)) {
  337. $parents = array_reverse($parents);
  338. $categories = [];
  339. foreach ($parents as $category) {
  340. $categories[] = $category['code'];
  341. }
  342. $categoriesInString = implode(' > ', $categories).' > ';
  343. return $categoriesInString;
  344. }
  345. return null;
  346. }
  347. /**
  348. * @param string $categorySource
  349. *
  350. * @return string
  351. */
  352. public static function listCategories($categorySource)
  353. {
  354. $categorySource = isset($categorySource) ? $categorySource : null;
  355. $categories = self::getCategories($categorySource);
  356. if (count($categories) > 0) {
  357. $table = new HTML_Table(array('class' => 'data_table'));
  358. $column = 0;
  359. $row = 0;
  360. $headers = array(
  361. get_lang('Category'),
  362. get_lang('SubCat'),
  363. get_lang('Courses'),
  364. get_lang('Actions')
  365. );
  366. foreach ($headers as $header) {
  367. $table->setHeaderContents($row, $column, $header);
  368. $column++;
  369. }
  370. $row++;
  371. $mainUrl = api_get_path(WEB_CODE_PATH).'admin/course_category.php?category='.$categorySource;
  372. $editIcon = Display::return_icon(
  373. 'edit.png',
  374. get_lang('EditNode'),
  375. null,
  376. ICON_SIZE_SMALL
  377. );
  378. $deleteIcon = Display::return_icon(
  379. 'delete.png',
  380. get_lang('DeleteNode'),
  381. null,
  382. ICON_SIZE_SMALL
  383. );
  384. $moveIcon = Display::return_icon(
  385. 'up.png',
  386. get_lang('UpInSameLevel'),
  387. null,
  388. ICON_SIZE_SMALL
  389. );
  390. foreach ($categories as $category) {
  391. $editUrl = $mainUrl.'&id='.$category['code'].'&action=edit';
  392. $moveUrl = $mainUrl.'&id='.$category['code'].'&action=moveUp&tree_pos='.$category['tree_pos'];
  393. $deleteUrl = $mainUrl.'&id='.$category['code'].'&action=delete';
  394. $actions = Display::url($editIcon, $editUrl).
  395. Display::url($moveIcon, $moveUrl).
  396. Display::url($deleteIcon, $deleteUrl);
  397. $url = api_get_path(WEB_CODE_PATH).'admin/course_category.php?category='.$category['code'];
  398. $title = Display::url(
  399. Display::return_icon(
  400. 'folder_document.gif',
  401. get_lang('OpenNode'),
  402. null,
  403. ICON_SIZE_SMALL
  404. ).' '.$category['name'],
  405. $url
  406. );
  407. $content = array(
  408. $title,
  409. $category['children_count'],
  410. $category['nbr_courses'],
  411. $actions
  412. );
  413. $column = 0;
  414. foreach ($content as $value) {
  415. $table->setCellContents($row, $column, $value);
  416. $column++;
  417. }
  418. $row++;
  419. }
  420. return $table->toHtml();
  421. } else {
  422. return Display::return_message(get_lang("NoCategories"), 'warning');
  423. }
  424. }
  425. /**
  426. * @return array
  427. */
  428. public static function getCategoriesToDisplayInHomePage()
  429. {
  430. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  431. $sql = "SELECT name FROM $tbl_category
  432. WHERE parent_id IS NULL
  433. ORDER BY tree_pos";
  434. return Database::store_result(Database::query($sql));
  435. }
  436. /**
  437. * @param int $id
  438. *
  439. * @return bool
  440. */
  441. public static function addToUrl($id)
  442. {
  443. UrlManager::addCourseCategoryListToUrl(
  444. array($id),
  445. array(api_get_current_access_url_id())
  446. );
  447. }
  448. /**
  449. * @param string $categoryCode
  450. *
  451. * @return array
  452. */
  453. public static function getCategoriesCanBeAddedInCourse($categoryCode)
  454. {
  455. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  456. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  457. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  458. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  459. $sql = "SELECT code, name
  460. FROM $tbl_category c
  461. $conditions
  462. WHERE (auth_course_child = 'TRUE' OR code = '".Database::escape_string($categoryCode)."')
  463. $whereCondition
  464. ORDER BY tree_pos";
  465. $res = Database::query($sql);
  466. $categories[''] = '-';
  467. while ($cat = Database::fetch_array($res)) {
  468. $categories[$cat['code']] = '('.$cat['code'].') '.$cat['name'];
  469. ksort($categories);
  470. }
  471. return $categories;
  472. }
  473. /**
  474. * @return array
  475. */
  476. public static function browseCourseCategories()
  477. {
  478. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  479. $conditions = null;
  480. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  481. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  482. $whereCondition = " WHERE a.access_url_id = ".api_get_current_access_url_id();
  483. $sql = "SELECT c.* FROM $tbl_category c
  484. $conditions
  485. $whereCondition
  486. ORDER BY tree_pos ASC";
  487. $result = Database::query($sql);
  488. $url_access_id = 1;
  489. if (api_is_multiple_url_enabled()) {
  490. $url_access_id = api_get_current_access_url_id();
  491. }
  492. $countCourses = CourseManager::countAvailableCourses($url_access_id);
  493. $categories = [];
  494. $categories[0][0] = array(
  495. 'id' => 0,
  496. 'name' => get_lang('DisplayAll'),
  497. 'code' => 'ALL',
  498. 'parent_id' => null,
  499. 'tree_pos' => 0,
  500. 'count_courses' => $countCourses
  501. );
  502. while ($row = Database::fetch_array($result)) {
  503. $count_courses = self::countCoursesInCategory($row['code']);
  504. $row['count_courses'] = $count_courses;
  505. if (!isset($row['parent_id'])) {
  506. $categories[0][$row['tree_pos']] = $row;
  507. } else {
  508. $categories[$row['parent_id']][$row['tree_pos']] = $row;
  509. }
  510. }
  511. $count_courses = self::countCoursesInCategory();
  512. $categories[0][count($categories[0]) + 1] = array(
  513. 'id' => 0,
  514. 'name' => get_lang('None'),
  515. 'code' => 'NONE',
  516. 'parent_id' => null,
  517. 'tree_pos' => $row['tree_pos'] + 1,
  518. 'children_count' => 0,
  519. 'auth_course_child' => true,
  520. 'auth_cat_child' => true,
  521. 'count_courses' => $count_courses
  522. );
  523. return $categories;
  524. }
  525. /**
  526. * @param string $category_code
  527. * @param string $searchTerm
  528. * @return int
  529. */
  530. public static function countCoursesInCategory($category_code = '', $searchTerm = '')
  531. {
  532. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  533. $categoryCode = Database::escape_string($category_code);
  534. $searchTerm = Database::escape_string($searchTerm);
  535. $specialCourseList = CourseManager::get_special_course_list();
  536. $without_special_courses = '';
  537. if (!empty($specialCourseList)) {
  538. $without_special_courses = ' AND course.id NOT IN ("'.implode('","', $specialCourseList).'")';
  539. }
  540. $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
  541. $categoryFilter = '';
  542. if ($categoryCode === 'ALL') {
  543. // Nothing to do
  544. } elseif ($categoryCode === 'NONE') {
  545. $categoryFilter = ' AND category_code = "" ';
  546. } else {
  547. $categoryFilter = ' AND category_code = "'.$categoryCode.'" ';
  548. }
  549. $searchFilter = '';
  550. if (!empty($searchTerm)) {
  551. $searchFilter = ' AND (code LIKE "%'.$searchTerm.'%"
  552. OR title LIKE "%'.$searchTerm.'%"
  553. OR tutor_name LIKE "%'.$searchTerm.'%") ';
  554. }
  555. $sql = "SELECT * FROM $tbl_course
  556. WHERE
  557. visibility != '0' AND
  558. visibility != '4'
  559. $categoryFilter
  560. $searchFilter
  561. $without_special_courses
  562. $visibilityCondition
  563. ";
  564. // Showing only the courses of the current portal access_url_id.
  565. if (api_is_multiple_url_enabled()) {
  566. $url_access_id = api_get_current_access_url_id();
  567. if ($url_access_id != -1) {
  568. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  569. $sql = "SELECT * FROM $tbl_course as course
  570. INNER JOIN $tbl_url_rel_course as url_rel_course
  571. ON (url_rel_course.c_id = course.id)
  572. WHERE
  573. access_url_id = $url_access_id AND
  574. course.visibility != '0' AND
  575. course.visibility != '4' AND
  576. category_code = '$category_code'
  577. $searchFilter
  578. $without_special_courses
  579. $visibilityCondition
  580. ";
  581. }
  582. }
  583. return Database::num_rows(Database::query($sql));
  584. }
  585. /**
  586. * @param string $category_code
  587. * @param int $random_value
  588. * @param array $limit will be used if $random_value is not set.
  589. * This array should contains 'start' and 'length' keys
  590. * @return array
  591. */
  592. public static function browseCoursesInCategory($category_code, $random_value = null, $limit = [])
  593. {
  594. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  595. $specialCourseList = CourseManager::get_special_course_list();
  596. $without_special_courses = '';
  597. if (!empty($specialCourseList)) {
  598. $without_special_courses = ' AND course.id NOT IN ("'.implode('","', $specialCourseList).'")';
  599. }
  600. $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
  601. if (!empty($random_value)) {
  602. $random_value = intval($random_value);
  603. $sql = "SELECT COUNT(*) FROM $tbl_course";
  604. $result = Database::query($sql);
  605. list($num_records) = Database::fetch_row($result);
  606. if (api_is_multiple_url_enabled()) {
  607. $url_access_id = api_get_current_access_url_id();
  608. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  609. $sql = "SELECT COUNT(*) FROM $tbl_course course
  610. INNER JOIN $tbl_url_rel_course as url_rel_course
  611. ON (url_rel_course.c_id = course.id)
  612. WHERE access_url_id = $url_access_id ";
  613. $result = Database::query($sql);
  614. list($num_records) = Database::fetch_row($result);
  615. $sql = "SELECT course.id FROM $tbl_course course
  616. INNER JOIN $tbl_url_rel_course as url_rel_course
  617. ON (url_rel_course.c_id = course.id)
  618. WHERE
  619. access_url_id = $url_access_id AND
  620. RAND()*$num_records< $random_value
  621. $without_special_courses
  622. $visibilityCondition
  623. ORDER BY RAND()
  624. LIMIT 0, $random_value";
  625. } else {
  626. $sql = "SELECT id FROM $tbl_course course
  627. WHERE
  628. RAND()*$num_records< $random_value
  629. $without_special_courses
  630. $visibilityCondition
  631. ORDER BY RAND()
  632. LIMIT 0, $random_value";
  633. }
  634. $result = Database::query($sql);
  635. $id_in = null;
  636. while (list($id) = Database::fetch_row($result)) {
  637. if ($id_in) {
  638. $id_in .= ",$id";
  639. } else {
  640. $id_in = "$id";
  641. }
  642. }
  643. if ($id_in === null) {
  644. return [];
  645. }
  646. $sql = "SELECT * FROM $tbl_course WHERE id IN($id_in)";
  647. } else {
  648. $limitFilter = self::getLimitFilterFromArray($limit);
  649. $category_code = Database::escape_string($category_code);
  650. if (empty($category_code) || $category_code == "ALL") {
  651. $sql = "SELECT * FROM $tbl_course
  652. WHERE
  653. 1=1
  654. $without_special_courses
  655. $visibilityCondition
  656. ORDER BY title $limitFilter ";
  657. } else {
  658. if ($category_code == 'NONE') {
  659. $category_code = '';
  660. }
  661. $sql = "SELECT * FROM $tbl_course
  662. WHERE
  663. category_code='$category_code'
  664. $without_special_courses
  665. $visibilityCondition
  666. ORDER BY title $limitFilter ";
  667. }
  668. // Showing only the courses of the current Chamilo access_url_id
  669. if (api_is_multiple_url_enabled()) {
  670. $url_access_id = api_get_current_access_url_id();
  671. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  672. if ($category_code != "ALL") {
  673. $sql = "SELECT * FROM $tbl_course as course
  674. INNER JOIN $tbl_url_rel_course as url_rel_course
  675. ON (url_rel_course.c_id = course.id)
  676. WHERE
  677. access_url_id = $url_access_id AND
  678. category_code='$category_code'
  679. $without_special_courses
  680. $visibilityCondition
  681. ORDER BY title $limitFilter";
  682. } else {
  683. $sql = "SELECT * FROM $tbl_course as course
  684. INNER JOIN $tbl_url_rel_course as url_rel_course
  685. ON (url_rel_course.c_id = course.id)
  686. WHERE
  687. access_url_id = $url_access_id
  688. $without_special_courses
  689. $visibilityCondition
  690. ORDER BY title $limitFilter";
  691. }
  692. }
  693. }
  694. $result = Database::query($sql);
  695. $courses = [];
  696. while ($row = Database::fetch_array($result)) {
  697. $row['registration_code'] = !empty($row['registration_code']);
  698. $count_users = CourseManager::get_users_count_in_course($row['code']);
  699. $count_connections_last_month = Tracking::get_course_connections_count(
  700. $row['id'],
  701. 0,
  702. api_get_utc_datetime(time() - (30 * 86400))
  703. );
  704. if ($row['tutor_name'] == '0') {
  705. $row['tutor_name'] = get_lang('NoManager');
  706. }
  707. $point_info = CourseManager::get_course_ranking($row['id'], 0);
  708. $courses[] = array(
  709. 'real_id' => $row['id'],
  710. 'point_info' => $point_info,
  711. 'code' => $row['code'],
  712. 'directory' => $row['directory'],
  713. 'visual_code' => $row['visual_code'],
  714. 'title' => $row['title'],
  715. 'tutor' => $row['tutor_name'],
  716. 'subscribe' => $row['subscribe'],
  717. 'unsubscribe' => $row['unsubscribe'],
  718. 'registration_code' => $row['registration_code'],
  719. 'creation_date' => $row['creation_date'],
  720. 'visibility' => $row['visibility'],
  721. 'category' => $row['category_code'],
  722. 'count_users' => $count_users,
  723. 'count_connections' => $count_connections_last_month
  724. );
  725. }
  726. return $courses;
  727. }
  728. /**
  729. * create recursively all categories as option of the select passed in parameter.
  730. *
  731. * @param HTML_QuickForm_Element $element
  732. * @param string $defaultCode the option value to select by default (used mainly for edition of courses)
  733. * @param string $parentCode the parent category of the categories added (default=null for root category)
  734. * @param string $padding the indent param (you shouldn't indicate something here)
  735. */
  736. public static function setCategoriesInForm($element, $defaultCode = null, $parentCode = null, $padding = null)
  737. {
  738. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  739. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  740. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  741. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  742. $sql = "SELECT code, name, auth_course_child, auth_cat_child
  743. FROM $tbl_category c
  744. $conditions
  745. WHERE parent_id ".(empty($parentCode) ? "IS NULL" : "='".Database::escape_string($parentCode)."'")."
  746. $whereCondition
  747. ORDER BY name, code";
  748. $res = Database::query($sql);
  749. while ($cat = Database::fetch_array($res, 'ASSOC')) {
  750. $params = $cat['auth_course_child'] == 'TRUE' ? '' : 'disabled';
  751. $params .= ($cat['code'] == $defaultCode) ? ' selected' : '';
  752. $option = $padding.' '.$cat['name'].' ('.$cat['code'].')';
  753. $element->addOption($option, $cat['code'], $params);
  754. if ($cat['auth_cat_child'] == 'TRUE') {
  755. self::setCategoriesInForm($element, $defaultCode, $cat['code'], $padding.' - ');
  756. }
  757. }
  758. }
  759. /**
  760. * @param array $list
  761. * @return array
  762. */
  763. public static function getCourseCategoryNotInList($list)
  764. {
  765. $table = Database::get_main_table(TABLE_MAIN_CATEGORY);
  766. if (empty($list)) {
  767. $sql = "SELECT * FROM $table
  768. WHERE (parent_id IS NULL) ";
  769. $result = Database::query($sql);
  770. return Database::store_result($result, 'ASSOC');
  771. }
  772. $list = array_map('intval', $list);
  773. $listToString = implode("','", $list);
  774. $sql = "SELECT * FROM $table
  775. WHERE id NOT IN ('$listToString') AND (parent_id IS NULL) ";
  776. $result = Database::query($sql);
  777. return Database::store_result($result, 'ASSOC');
  778. }
  779. /**
  780. * @param string $keyword
  781. * @return array|null
  782. */
  783. public static function searchCategoryByKeyword($keyword)
  784. {
  785. if (empty($keyword)) {
  786. return null;
  787. }
  788. $tableCategory = Database::get_main_table(TABLE_MAIN_CATEGORY);
  789. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  790. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  791. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  792. $keyword = Database::escape_string($keyword);
  793. $sql = "SELECT c.*, c.name as text
  794. FROM $tableCategory c $conditions
  795. WHERE
  796. (
  797. c.code LIKE '%$keyword%' OR name LIKE '%$keyword%'
  798. ) AND auth_course_child = 'TRUE'
  799. $whereCondition ";
  800. $result = Database::query($sql);
  801. return Database::store_result($result, 'ASSOC');
  802. }
  803. /**
  804. * @param array $list
  805. * @return array
  806. */
  807. public static function searchCategoryById($list)
  808. {
  809. if (empty($list)) {
  810. return [];
  811. } else {
  812. $list = array_map('intval', $list);
  813. $list = implode("','", $list);
  814. }
  815. $tableCategory = Database::get_main_table(TABLE_MAIN_CATEGORY);
  816. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  817. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  818. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  819. $sql = "SELECT c.*, c.name as text FROM $tableCategory c $conditions
  820. WHERE c.id IN $list $whereCondition";
  821. $result = Database::query($sql);
  822. return Database::store_result($result, 'ASSOC');
  823. }
  824. /**
  825. * @return array
  826. */
  827. public static function getLimitArray()
  828. {
  829. $pageCurrent = isset($_REQUEST['pageCurrent']) ? intval($_GET['pageCurrent']) : 1;
  830. $pageLength = isset($_REQUEST['pageLength']) ? intval($_GET['pageLength']) : 12;
  831. return array(
  832. 'start' => ($pageCurrent - 1) * $pageLength,
  833. 'current' => $pageCurrent,
  834. 'length' => $pageLength
  835. );
  836. }
  837. /**
  838. * Return LIMIT to filter SQL query
  839. * @param array $limit
  840. * @return string
  841. */
  842. public static function getLimitFilterFromArray($limit)
  843. {
  844. $limitFilter = '';
  845. if (!empty($limit) && is_array($limit)) {
  846. $limitStart = isset($limit['start']) ? $limit['start'] : 0;
  847. $limitLength = isset($limit['length']) ? $limit['length'] : 12;
  848. $limitFilter = 'LIMIT '.$limitStart.', '.$limitLength;
  849. }
  850. return $limitFilter;
  851. }
  852. /**
  853. * Get Pagination HTML div
  854. * @param $pageCurrent
  855. * @param $pageLength
  856. * @param $pageTotal
  857. * @return string
  858. */
  859. public static function getCatalogPagination($pageCurrent, $pageLength, $pageTotal)
  860. {
  861. // Start empty html
  862. $pageDiv = '';
  863. $html = '';
  864. $pageBottom = max(1, $pageCurrent - 3);
  865. $pageTop = min($pageTotal, $pageCurrent + 3);
  866. if ($pageBottom > 1) {
  867. $pageDiv .= self::getPageNumberItem(1, $pageLength);
  868. if ($pageBottom > 2) {
  869. $pageDiv .= self::getPageNumberItem($pageBottom - 1, $pageLength, null, '...');
  870. }
  871. }
  872. // For each page add its page button to html
  873. for ($i = $pageBottom; $i <= $pageTop; $i++) {
  874. if ($i === $pageCurrent) {
  875. $pageItemAttributes = array('class' => 'active');
  876. } else {
  877. $pageItemAttributes = [];
  878. }
  879. $pageDiv .= self::getPageNumberItem($i, $pageLength, $pageItemAttributes);
  880. }
  881. // Check if current page is the last page
  882. if ($pageTop < $pageTotal) {
  883. if ($pageTop < ($pageTotal - 1)) {
  884. $pageDiv .= self::getPageNumberItem($pageTop + 1, $pageLength, null, '...');
  885. }
  886. $pageDiv .= self::getPageNumberItem($pageTotal, $pageLength);
  887. }
  888. // Complete pagination html
  889. $pageDiv = Display::tag('ul', $pageDiv, array('class' => 'pagination'));
  890. $html .= '<nav>'.$pageDiv.'</nav>';
  891. return $html;
  892. }
  893. /**
  894. * Return URL to course catalog
  895. * @param int $pageCurrent
  896. * @param int $pageLength
  897. * @param string $categoryCode
  898. * @param int $hiddenLinks
  899. * @param string $action
  900. * @return string
  901. */
  902. public static function getCourseCategoryUrl(
  903. $pageCurrent,
  904. $pageLength,
  905. $categoryCode = null,
  906. $hiddenLinks = null,
  907. $action = null
  908. ) {
  909. $requestAction = isset($_REQUEST['action']) ? Security::remove_XSS($_REQUEST['action']) : null;
  910. $action = isset($action) ? Security::remove_XSS($action) : $requestAction;
  911. $searchTerm = isset($_REQUEST['search_term']) ? Security::remove_XSS($_REQUEST['search_term']) : null;
  912. if ($action === 'subscribe_user_with_password') {
  913. $action = 'subscribe';
  914. }
  915. $categoryCodeRequest = isset($_REQUEST['category_code']) ? Security::remove_XSS($_REQUEST['category_code']) : null;
  916. $categoryCode = isset($categoryCode) ? Security::remove_XSS($categoryCode) : $categoryCodeRequest;
  917. $hiddenLinksRequest = isset($_REQUEST['hidden_links']) ? Security::remove_XSS($_REQUEST['hidden_links']) : null;
  918. $hiddenLinks = isset($hiddenLinks) ? Security::remove_XSS($hiddenLinksRequest) : $categoryCodeRequest;
  919. // Start URL with params
  920. $pageUrl = api_get_self().
  921. '?action='.$action.
  922. '&category_code='.$categoryCode.
  923. '&hidden_links='.$hiddenLinks.
  924. '&pageCurrent='.$pageCurrent.
  925. '&pageLength='.$pageLength;
  926. switch ($action) {
  927. case 'subscribe':
  928. // for search
  929. $pageUrl .=
  930. '&search_term='.$searchTerm.
  931. '&search_course=1'.
  932. '&sec_token='.$_SESSION['sec_token'];
  933. break;
  934. case 'display_courses':
  935. // No break
  936. default:
  937. break;
  938. }
  939. return $pageUrl;
  940. }
  941. /**
  942. * Get li HTML of page number
  943. * @param $pageNumber
  944. * @param $pageLength
  945. * @param array $liAttributes
  946. * @param string $content
  947. * @return string
  948. */
  949. public static function getPageNumberItem(
  950. $pageNumber,
  951. $pageLength,
  952. $liAttributes = [],
  953. $content = ''
  954. ) {
  955. // Get page URL
  956. $url = self::getCourseCategoryUrl(
  957. $pageNumber,
  958. $pageLength
  959. );
  960. // If is current page ('active' class) clear URL
  961. if (isset($liAttributes) && is_array($liAttributes) && isset($liAttributes['class'])) {
  962. if (strpos('active', $liAttributes['class']) !== false) {
  963. $url = '';
  964. }
  965. }
  966. $content = !empty($content) ? $content : $pageNumber;
  967. return Display::tag(
  968. 'li',
  969. Display::url(
  970. $content,
  971. $url
  972. ),
  973. $liAttributes
  974. );
  975. }
  976. /**
  977. * Return the name tool by action
  978. * @param string $action
  979. * @return string
  980. */
  981. public static function getCourseCatalogNameTools($action)
  982. {
  983. $nameTools = get_lang('SortMyCourses');
  984. if (empty($action)) {
  985. return $nameTools; //should never happen
  986. }
  987. switch ($action) {
  988. case 'createcoursecategory':
  989. $nameTools = get_lang('CreateCourseCategory');
  990. break;
  991. case 'subscribe':
  992. $nameTools = get_lang('CourseManagement');
  993. break;
  994. case 'subscribe_user_with_password':
  995. $nameTools = get_lang('CourseManagement');
  996. break;
  997. case 'display_random_courses':
  998. // No break
  999. case 'display_courses':
  1000. $nameTools = get_lang('CourseManagement');
  1001. break;
  1002. case 'display_sessions':
  1003. $nameTools = get_lang('Sessions');
  1004. break;
  1005. default:
  1006. // Nothing to do
  1007. break;
  1008. }
  1009. return $nameTools;
  1010. }
  1011. }