course_category.lib.php 40 KB

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