course_category.lib.php 41 KB

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