course_category.lib.php 34 KB

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