course_category.lib.php 41 KB

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