course_category.lib.php 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. function isMultipleUrlSupport()
  4. {
  5. global $_configuration;
  6. if (isset($_configuration['enable_multiple_url_support_for_course_category'])) {
  7. return $_configuration['enable_multiple_url_support_for_course_category'];
  8. }
  9. return false;
  10. }
  11. /**
  12. * @param int $categoryId
  13. * @return array
  14. */
  15. function getCategoryById($categoryId)
  16. {
  17. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  18. $categoryId = Database::escape_string($categoryId);
  19. $sql = "SELECT * FROM $tbl_category WHERE id = '$categoryId'";
  20. $result = Database::query($sql);
  21. if (Database::num_rows($result)) {
  22. return Database::fetch_array($result, 'ASSOC');
  23. }
  24. return array();
  25. }
  26. /**
  27. * @param string $category
  28. * @return array
  29. */
  30. function getCategory($category)
  31. {
  32. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  33. $category = Database::escape_string($category);
  34. $sql = "SELECT * FROM $tbl_category WHERE code ='$category'";
  35. $result = Database::query($sql);
  36. if (Database::num_rows($result)) {
  37. return Database::fetch_array($result, 'ASSOC');
  38. }
  39. return array();
  40. }
  41. /**
  42. * @param string $category
  43. * @return array
  44. */
  45. function getCategories($category)
  46. {
  47. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  48. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  49. $category = Database::escape_string($category);
  50. $conditions = null;
  51. $whereCondition = null;
  52. if (isMultipleUrlSupport()) {
  53. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  54. $conditions = " INNER JOIN $table a ON (t1.id = a.course_category_id)";
  55. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  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 ON t1.code=t2.parent_id
  67. LEFT JOIN $tbl_course t3 ON t3.category_code=t1.code
  68. WHERE
  69. t1.parent_id " . (empty($category) ? "IS NULL" : "='$category'") . "
  70. $whereCondition
  71. GROUP BY t1.name,
  72. t1.code,
  73. t1.parent_id,
  74. t1.tree_pos,
  75. t1.children_count
  76. ORDER BY t1.tree_pos";
  77. $result = Database::query($sql);
  78. return Database::store_result($result);
  79. }
  80. /**
  81. * @param string $code
  82. * @param string $name
  83. * @param string $canHaveCourses
  84. * @param int $parent_id
  85. * @return bool
  86. */
  87. function addNode($code, $name, $canHaveCourses, $parent_id)
  88. {
  89. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  90. $code = trim(Database::escape_string($code));
  91. $name = trim(Database::escape_string($name));
  92. $parent_id = Database::escape_string($parent_id);
  93. $canHaveCourses = Database::escape_string($canHaveCourses);
  94. $code = generate_course_code($code);
  95. $result = Database::query("SELECT 1 FROM $tbl_category WHERE code='$code'");
  96. if (Database::num_rows($result)) {
  97. return false;
  98. }
  99. $result = Database::query("SELECT MAX(tree_pos) AS maxTreePos FROM $tbl_category");
  100. $row = Database::fetch_array($result);
  101. $tree_pos = $row['maxTreePos'] + 1;
  102. $sql = "INSERT INTO $tbl_category(name, code, parent_id, tree_pos, children_count, auth_course_child)
  103. VALUES('$name','$code'," .(empty($parent_id) ? "NULL" : "'$parent_id'") . ",'$tree_pos','0','$canHaveCourses')";
  104. Database::query($sql);
  105. $categoryId = Database::insert_id();
  106. updateCategoryChildren($parent_id);
  107. if (isMultipleUrlSupport()) {
  108. addToUrl($categoryId);
  109. }
  110. return $categoryId;
  111. }
  112. /**
  113. * @param string $category
  114. */
  115. function updateCategoryChildren($category)
  116. {
  117. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  118. $category = Database::escape_string($category);
  119. $result = Database::query("SELECT parent_id FROM $tbl_category WHERE code='$category'");
  120. if ($row = Database::fetch_array($result)) {
  121. updateCategoryChildren($row['parent_id']);
  122. }
  123. $children_count = compterFils($category, 0) - 1;
  124. Database::query("UPDATE $tbl_category SET children_count='$children_count' WHERE code='$category'");
  125. }
  126. /**
  127. * @param string $node
  128. */
  129. function deleteNode($node)
  130. {
  131. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  132. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  133. $node = Database::escape_string($node);
  134. $result = Database::query("SELECT parent_id, tree_pos FROM $tbl_category WHERE code='$node'");
  135. if ($row = Database::fetch_array($result)) {
  136. if (!empty($row['parent_id'])) {
  137. Database::query("UPDATE $tbl_course SET category_code = '".$row['parent_id']."' WHERE category_code='$node'");
  138. Database::query("UPDATE $tbl_category SET parent_id='" . $row['parent_id'] . "' WHERE parent_id='$node'");
  139. } else {
  140. Database::query("UPDATE $tbl_course SET category_code='' WHERE category_code='$node'");
  141. Database::query("UPDATE $tbl_category SET parent_id=NULL WHERE parent_id='$node'");
  142. }
  143. Database::query("UPDATE $tbl_category SET tree_pos=tree_pos-1 WHERE tree_pos > '" . $row['tree_pos'] . "'");
  144. Database::query("DELETE FROM $tbl_category WHERE code='$node'");
  145. if (!empty($row['parent_id'])) {
  146. updateCategoryChildren($row['parent_id']);
  147. }
  148. }
  149. }
  150. /**
  151. * @param string $code
  152. * @param string $name
  153. * @param string $canHaveCourses
  154. * @param string $old_code
  155. * @return bool
  156. */
  157. function editNode($code, $name, $canHaveCourses, $old_code)
  158. {
  159. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  160. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  161. $code = trim(Database::escape_string($code));
  162. $name = trim(Database::escape_string($name));
  163. $old_code = Database::escape_string($old_code);
  164. $canHaveCourses = Database::escape_string($canHaveCourses);
  165. $code = generate_course_code($code);
  166. // Updating category
  167. $sql = "UPDATE $tbl_category SET name='$name', code='$code', auth_course_child = '$canHaveCourses'
  168. WHERE code = '$old_code'";
  169. Database::query($sql);
  170. // Updating children
  171. $sql = "UPDATE $tbl_category SET parent_id = '$code'
  172. WHERE parent_id = '$old_code'";
  173. Database::query($sql);
  174. // Updating course category
  175. $sql = "UPDATE $tbl_course SET category_code = '$code' WHERE category_code = '$old_code' ";
  176. Database::query($sql);
  177. return true;
  178. }
  179. /**
  180. * @param string $code
  181. * @param string $tree_pos
  182. * @param int $parent_id
  183. * @return bool
  184. */
  185. function moveNodeUp($code, $tree_pos, $parent_id)
  186. {
  187. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  188. $code = Database::escape_string($code);
  189. $tree_pos = Database::escape_string($tree_pos);
  190. $parent_id = Database::escape_string($parent_id);
  191. $sql = "SELECT code,tree_pos
  192. FROM $tbl_category
  193. WHERE parent_id " . (empty($parent_id) ? "IS NULL" : "='$parent_id'") . " AND tree_pos<'$tree_pos'
  194. ORDER BY tree_pos DESC LIMIT 0,1";
  195. $result = Database::query($sql);
  196. if (!$row = Database::fetch_array($result)) {
  197. $sql = "SELECT code,tree_pos FROM $tbl_category
  198. WHERE parent_id " . (empty($parent_id) ? "IS NULL" : "='$parent_id'") . " AND tree_pos>'$tree_pos'
  199. ORDER BY tree_pos DESC LIMIT 0,1";
  200. $result2 = Database::query($sql);
  201. if (!$row2 = Database::fetch_array($result2)) {
  202. return false;
  203. }
  204. }
  205. Database::query("UPDATE $tbl_category SET tree_pos='" . $row['tree_pos'] . "' WHERE code='$code'");
  206. Database::query("UPDATE $tbl_category SET tree_pos='$tree_pos' WHERE code='$row[code]'");
  207. }
  208. /**
  209. * @param $pere
  210. * @param $cpt
  211. * @return mixed
  212. */
  213. function compterFils($pere, $cpt)
  214. {
  215. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  216. $pere = Database::escape_string($pere);
  217. $result = Database::query("SELECT code FROM $tbl_category WHERE parent_id='$pere'");
  218. while ($row = Database::fetch_array($result)) {
  219. $cpt = compterFils($row['code'], $cpt);
  220. }
  221. return ($cpt + 1);
  222. }
  223. /**
  224. * @param string $categoryCode
  225. * @return array
  226. */
  227. function getChildren($categoryCode)
  228. {
  229. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  230. $categoryCode = Database::escape_string($categoryCode);
  231. $result = Database::query("SELECT code, id FROM $tbl_category WHERE parent_id = '$categoryCode'");
  232. $children = array();
  233. while ($row = Database::fetch_array($result, 'ASSOC')) {
  234. $children[] = $row;
  235. $subChildren = getChildren($row['code']);
  236. $children = array_merge($children, $subChildren);
  237. }
  238. return $children;
  239. }
  240. function getParents($categoryCode)
  241. {
  242. if (empty($categoryCode)) {
  243. return array();
  244. }
  245. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  246. $categoryCode = Database::escape_string($categoryCode);
  247. $sql = "SELECT code, parent_id FROM $tbl_category WHERE code = '$categoryCode'";
  248. $result = Database::query($sql);
  249. $children = array();
  250. while ($row = Database::fetch_array($result, 'ASSOC')) {
  251. $parent = getCategory($row['parent_id']);
  252. $children[] = $row;
  253. $subChildren = getParents($parent['code']);
  254. $children = array_merge($children, $subChildren);
  255. }
  256. return $children;
  257. }
  258. function getParentsToString($categoryCode)
  259. {
  260. $parents = getParents($categoryCode);
  261. if (!empty($parents)) {
  262. $parents = array_reverse($parents);
  263. $categories = array();
  264. foreach ($parents as $category) {
  265. $categories[] = $category['code'];
  266. }
  267. $categoriesInString = implode(' > ', $categories).' > ';
  268. return $categoriesInString;
  269. }
  270. return null;
  271. }
  272. /**
  273. * @param string $categorySource
  274. * @return string
  275. */
  276. function listCategories($categorySource)
  277. {
  278. $categorySource = isset($categorySource) ? $categorySource : null;
  279. $categories = getCategories($categorySource);
  280. if (count($categories) > 0) {
  281. $table = new HTML_Table(array('class' => 'data_table'));
  282. $column = 0;
  283. $row = 0;
  284. $headers = array(
  285. get_lang('Category'), get_lang('CategoriesNumber'), get_lang('Courses'), get_lang('Actions')
  286. );
  287. foreach ($headers as $header) {
  288. $table->setHeaderContents($row, $column, $header);
  289. $column++;
  290. }
  291. $row++;
  292. $mainUrl = api_get_path(WEB_CODE_PATH).'admin/course_category.php?category='.$categorySource;
  293. $editIcon = Display::return_icon('edit.png', get_lang('EditNode'), null, ICON_SIZE_SMALL);
  294. $deleteIcon = Display::return_icon('delete.png', get_lang('DeleteNode'), null, ICON_SIZE_SMALL);
  295. $moveIcon = Display::return_icon('up.png', get_lang('UpInSameLevel'), null, ICON_SIZE_SMALL);
  296. foreach ($categories as $category) {
  297. $editUrl = $mainUrl.'&id='.$category['code'].'&action=edit';
  298. $moveUrl = $mainUrl.'&id='.$category['code'].'&action=moveUp&tree_pos='.$category['tree_pos'];
  299. $deleteUrl = $mainUrl.'&id='.$category['code'].'&action=delete';
  300. $actions = Display::url($editIcon, $editUrl).Display::url($moveIcon, $moveUrl).Display::url($deleteIcon, $deleteUrl);
  301. $url = api_get_path(WEB_CODE_PATH).'admin/course_category.php?category='.$category['code'];
  302. $title = Display::url(
  303. Display::return_icon('folder_document.gif', get_lang('OpenNode'), null, ICON_SIZE_SMALL).' '.$category['name'],
  304. $url
  305. );
  306. $content = array(
  307. $title,
  308. $category['children_count'],
  309. $category['nbr_courses'],
  310. $actions
  311. );
  312. $column = 0;
  313. foreach ($content as $value) {
  314. $table->setCellContents($row, $column, $value);
  315. $column++;
  316. }
  317. $row++;
  318. }
  319. return $table->toHtml();
  320. } else {
  321. return Display::return_message(get_lang("NoCategories"), 'warning');
  322. }
  323. }
  324. /**
  325. * @return array
  326. */
  327. function getCategoriesToDisplayInHomePage()
  328. {
  329. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  330. $sql = "SELECT name FROM $tbl_category WHERE parent_id IS NULL ORDER BY tree_pos";
  331. return Database::store_result(Database::query($sql));
  332. }
  333. /**
  334. * @param int $id
  335. * @return bool
  336. */
  337. function addToUrl($id)
  338. {
  339. if (!isMultipleUrlSupport()) {
  340. return false;
  341. }
  342. UrlManager::addCourseCategoryListToUrl(array($id), array(api_get_current_access_url_id()));
  343. }
  344. /**
  345. * @param string $categoryCode
  346. * @return array
  347. */
  348. function getCategoriesCanBeAddedInCourse($categoryCode)
  349. {
  350. $conditions = null;
  351. $whereCondition = null;
  352. if (isMultipleUrlSupport()) {
  353. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  354. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  355. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  356. }
  357. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  358. $sql = "SELECT code, name
  359. FROM $tbl_category c
  360. $conditions
  361. WHERE (auth_course_child = 'TRUE' OR code = '".Database::escape_string($categoryCode)."')
  362. $whereCondition
  363. ORDER BY tree_pos";
  364. $res = Database::query($sql);
  365. $categories[''] = '-';
  366. while ($cat = Database::fetch_array($res)) {
  367. $categories[$cat['code']] = '('.$cat['code'].') '.$cat['name'];
  368. ksort($categories);
  369. }
  370. return $categories;
  371. }
  372. /**
  373. * @return array
  374. */
  375. function browseCourseCategories()
  376. {
  377. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  378. $conditions = null;
  379. $whereCondition = null;
  380. if (isMultipleUrlSupport()) {
  381. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  382. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  383. $whereCondition = " WHERE a.access_url_id = ".api_get_current_access_url_id();
  384. }
  385. $sql = "SELECT c.* FROM $tbl_category c
  386. $conditions
  387. $whereCondition
  388. ORDER BY tree_pos ASC";
  389. $result = Database::query($sql);
  390. $url_access_id = 1;
  391. if (api_is_multiple_url_enabled()) {
  392. $url_access_id = api_get_current_access_url_id();
  393. }
  394. $countCourses = CourseManager :: countAvailableCourses($url_access_id);
  395. $categories = array();
  396. $categories[0][0] = array(
  397. 'id' => 0,
  398. 'name' => get_lang('DisplayAll'),
  399. 'code' => 'ALL',
  400. 'parent_id' => null,
  401. 'tree_pos' => 0,
  402. 'count_courses' => $countCourses
  403. );
  404. while ($row = Database::fetch_array($result)) {
  405. $count_courses = countCoursesInCategory($row['code']);
  406. $row['count_courses'] = $count_courses;
  407. if (!isset($row['parent_id'])) {
  408. $categories[0][$row['tree_pos']] = $row;
  409. } else {
  410. $categories[$row['parent_id']][$row['tree_pos']] = $row;
  411. }
  412. }
  413. $count_courses = countCoursesInCategory();
  414. $categories[0][count($categories[0])+1] = array(
  415. 'id' =>0,
  416. 'name' => get_lang('None'),
  417. 'code' => 'NONE',
  418. 'parent_id' => null,
  419. 'tree_pos' => $row['tree_pos']+1,
  420. 'children_count' => 0,
  421. 'auth_course_child' => true,
  422. 'auth_cat_child' => true,
  423. 'count_courses' => $count_courses
  424. );
  425. return $categories;
  426. }
  427. /**
  428. * @param string $category_code
  429. * @return int
  430. */
  431. function countCoursesInCategory($category_code="")
  432. {
  433. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  434. $TABLE_COURSE_FIELD = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
  435. $TABLE_COURSE_FIELD_VALUE = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  436. // get course list auto-register
  437. $sql = "SELECT course_code
  438. FROM $TABLE_COURSE_FIELD_VALUE tcfv
  439. INNER JOIN $TABLE_COURSE_FIELD tcf ON tcfv.field_id = tcf.id
  440. WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  441. $special_course_result = Database::query($sql);
  442. if (Database::num_rows($special_course_result) > 0) {
  443. $special_course_list = array();
  444. while ($result_row = Database::fetch_array($special_course_result)) {
  445. $special_course_list[] = '"' . $result_row['course_code'] . '"';
  446. }
  447. }
  448. $without_special_courses = '';
  449. if (!empty($special_course_list)) {
  450. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  451. }
  452. $sql = "SELECT * FROM $tbl_course
  453. WHERE visibility != '0' AND visibility != '4' AND category_code" . "='" . $category_code . "'" . $without_special_courses;
  454. // Showing only the courses of the current portal access_url_id.
  455. if (api_is_multiple_url_enabled()) {
  456. $url_access_id = api_get_current_access_url_id();
  457. if ($url_access_id != -1) {
  458. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  459. $sql = "SELECT * FROM $tbl_course as course
  460. INNER JOIN $tbl_url_rel_course as url_rel_course
  461. ON (url_rel_course.course_code=course.code)
  462. WHERE access_url_id = $url_access_id AND course.visibility != '0' AND course.visibility != '4' AND category_code" . "='" . $category_code . "'" . $without_special_courses;
  463. }
  464. }
  465. return Database::num_rows(Database::query($sql));
  466. }
  467. /**
  468. * @param string $category_code
  469. * @param string $random_value
  470. * @return array
  471. */
  472. function browseCoursesInCategory($category_code, $random_value = null)
  473. {
  474. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  475. $TABLE_COURSE_FIELD = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  476. $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  477. // Get course list auto-register
  478. $sql = "SELECT course_code
  479. FROM $TABLE_COURSE_FIELD_VALUE tcfv
  480. INNER JOIN $TABLE_COURSE_FIELD tcf ON tcfv.field_id = tcf.id
  481. WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  482. $special_course_result = Database::query($sql);
  483. if (Database::num_rows($special_course_result) > 0) {
  484. $special_course_list = array();
  485. while ($result_row = Database::fetch_array($special_course_result)) {
  486. $special_course_list[] = '"' . $result_row['course_code'] . '"';
  487. }
  488. }
  489. $without_special_courses = '';
  490. if (!empty($special_course_list)) {
  491. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  492. }
  493. if (!empty($random_value)) {
  494. $random_value = intval($random_value);
  495. $sql = "SELECT COUNT(*) FROM $tbl_course";
  496. $result = Database::query($sql);
  497. list($num_records) = Database::fetch_row($result);
  498. if (api_is_multiple_url_enabled()) {
  499. $url_access_id = api_get_current_access_url_id();
  500. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  501. $sql = "SELECT COUNT(*) FROM $tbl_course course
  502. INNER JOIN $tbl_url_rel_course as url_rel_course ON (url_rel_course.course_code=course.code)
  503. WHERE access_url_id = $url_access_id ";
  504. $result = Database::query($sql);
  505. list($num_records) = Database::fetch_row($result);
  506. $sql = "SELECT course.id FROM $tbl_course course INNER JOIN $tbl_url_rel_course as url_rel_course
  507. ON (url_rel_course.course_code=course.code)
  508. WHERE access_url_id = $url_access_id AND
  509. RAND()*$num_records< $random_value
  510. $without_special_courses
  511. ORDER BY RAND() LIMIT 0, $random_value";
  512. } else {
  513. $sql = "SELECT id FROM $tbl_course course
  514. WHERE RAND()*$num_records< $random_value $without_special_courses
  515. ORDER BY RAND() LIMIT 0, $random_value";
  516. }
  517. $result = Database::query($sql);
  518. $id_in = null;
  519. while (list($id) = Database::fetch_row($result)) {
  520. if ($id_in) {
  521. $id_in.=",$id";
  522. } else {
  523. $id_in = "$id";
  524. }
  525. }
  526. $sql = "SELECT * FROM $tbl_course WHERE id IN($id_in)";
  527. } else {
  528. $category_code = Database::escape_string($category_code);
  529. if (empty($category_code) || $category_code == "ALL") {
  530. $sql = "SELECT * FROM $tbl_course WHERE 1=1 $without_special_courses ORDER BY title ";
  531. } else {
  532. if ($category_code == 'NONE') {
  533. $category_code = '';
  534. }
  535. $sql = "SELECT * FROM $tbl_course WHERE category_code='$category_code' $without_special_courses ORDER BY title ";
  536. }
  537. //showing only the courses of the current Chamilo access_url_id
  538. if (api_is_multiple_url_enabled()) {
  539. $url_access_id = api_get_current_access_url_id();
  540. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  541. if ($category_code != "ALL") {
  542. $sql = "SELECT * FROM $tbl_course as course INNER JOIN $tbl_url_rel_course as url_rel_course
  543. ON (url_rel_course.course_code=course.code)
  544. WHERE access_url_id = $url_access_id AND category_code='$category_code' $without_special_courses
  545. ORDER BY title";
  546. } else{
  547. $sql = "SELECT * FROM $tbl_course as course INNER JOIN $tbl_url_rel_course as url_rel_course
  548. ON (url_rel_course.course_code=course.code)
  549. WHERE access_url_id = $url_access_id $without_special_courses
  550. ORDER BY title";
  551. }
  552. }
  553. }
  554. $result = Database::query($sql);
  555. $courses = array();
  556. while ($row = Database::fetch_array($result)) {
  557. $row['registration_code'] = !empty($row['registration_code']);
  558. $count_users = CourseManager::get_users_count_in_course($row['code']);
  559. $count_connections_last_month = Tracking::get_course_connections_count($row['code'], 0, api_get_utc_datetime(time() - (30 * 86400)));
  560. if ($row['tutor_name'] == '0') {
  561. $row['tutor_name'] = get_lang('NoManager');
  562. }
  563. $point_info = CourseManager::get_course_ranking($row['id'], 0);
  564. $courses[] = array(
  565. 'real_id' => $row['id'],
  566. 'point_info' => $point_info,
  567. 'code' => $row['code'],
  568. 'directory' => $row['directory'],
  569. 'db' => $row['db_name'],
  570. 'visual_code' => $row['visual_code'],
  571. 'title' => $row['title'],
  572. 'tutor' => $row['tutor_name'],
  573. 'subscribe' => $row['subscribe'],
  574. 'unsubscribe' => $row['unsubscribe'],
  575. 'registration_code' => $row['registration_code'],
  576. 'creation_date' => $row['creation_date'],
  577. 'visibility' => $row['visibility'],
  578. 'count_users' => $count_users,
  579. 'count_connections' => $count_connections_last_month
  580. );
  581. }
  582. return $courses;
  583. }
  584. /**
  585. * create recursively all categories as option of the select passed in parameter.
  586. *
  587. * @param HTML_QuickForm_Element $element
  588. * @param string $defaultCode the option value to select by default (used mainly for edition of courses)
  589. * @param string $parentCode the parent category of the categories added (default=null for root category)
  590. * @param string $padding the indent param (you shouldn't indicate something here)
  591. */
  592. function setCategoriesInForm($element, $defaultCode = null, $parentCode = null, $padding = null)
  593. {
  594. $tbl_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  595. $conditions = null;
  596. $whereCondition = null;
  597. if (isMultipleUrlSupport()) {
  598. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  599. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  600. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  601. }
  602. $sql = "SELECT code, name, auth_course_child, auth_cat_child
  603. FROM ".$tbl_category." c
  604. $conditions
  605. WHERE parent_id ".(empty($parentCode) ? "IS NULL" : "='".Database::escape_string($parentCode)."'")."
  606. $whereCondition
  607. ORDER BY name, code";
  608. $res = Database::query($sql);
  609. while ($cat = Database::fetch_array($res, 'ASSOC')) {
  610. $params = $cat['auth_course_child'] == 'TRUE' ? '' : 'disabled';
  611. $params .= ($cat['code'] == $defaultCode) ? ' selected' : '';
  612. $option = $padding.' '.$cat['name'].' ('.$cat['code'].')';
  613. $element->addOption($option, $cat['code'], $params);
  614. if ($cat['auth_cat_child'] == 'TRUE') {
  615. setCategoriesInForm($element, $defaultCode, $cat['code'], $padding.' - ');
  616. }
  617. }
  618. }
  619. /**
  620. * @param array $list
  621. * @return array
  622. */
  623. function getCourseCategoryNotInList($list)
  624. {
  625. $table = Database::get_main_table(TABLE_MAIN_CATEGORY);
  626. if (empty($list)) {
  627. return array();
  628. }
  629. $list = array_map('intval', $list);
  630. $listToString = implode("','", $list);
  631. $sql = "SELECT * FROM $table WHERE id NOT IN ('$listToString') AND (parent_id IS NULL) ";
  632. $result = Database::query($sql);
  633. return Database::store_result($result, 'ASSOC');
  634. }
  635. /**
  636. * @param string $keyword
  637. * @return array|null
  638. */
  639. function searchCategoryByKeyword($keyword)
  640. {
  641. if (empty($keyword)) {
  642. return null;
  643. }
  644. $tableCategory = Database::get_main_table(TABLE_MAIN_CATEGORY);
  645. $conditions = null;
  646. $whereCondition = null;
  647. if (isMultipleUrlSupport()) {
  648. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  649. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  650. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  651. }
  652. $keyword = Database::escape_string($keyword);
  653. $sql = "SELECT c.*, c.name as text FROM $tableCategory c $conditions
  654. WHERE (c.code LIKE '%$keyword%' or name LIKE '%$keyword%') AND auth_course_child = 'TRUE' $whereCondition ";
  655. $result = Database::query($sql);
  656. return Database::store_result($result, 'ASSOC');
  657. }
  658. /**
  659. * @param array $list
  660. * @return array
  661. */
  662. function searchCategoryById($list)
  663. {
  664. if (empty($list)) {
  665. return array();
  666. } else {
  667. $list = array_map('intval', $list);
  668. $list = implode("','", $list);
  669. }
  670. $tableCategory = Database::get_main_table(TABLE_MAIN_CATEGORY);
  671. $conditions = null;
  672. $whereCondition = null;
  673. if (isMultipleUrlSupport()) {
  674. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  675. $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";
  676. $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();
  677. }
  678. $sql = "SELECT c.*, c.name as text FROM $tableCategory c $conditions
  679. WHERE c.id IN $list $whereCondition";
  680. $result = Database::query($sql);
  681. return Database::store_result($result, 'ASSOC');
  682. }
  683. /**
  684. CREATE TABLE IF NOT EXISTS access_url_rel_course_category (access_url_id int unsigned NOT NULL, course_category_id int unsigned NOT NULL, PRIMARY KEY (access_url_id, course_category_id));
  685. */