course_category.lib.php 34 KB

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