course_category.lib.php 36 KB

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