auth.lib.php 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This file contains a class used like library provides functions for auth tool. It's also used like model to courses_controller (MVC pattern)
  5. * @author Christian Fasanando <christian1827@gmail.com>
  6. * @package chamilo.auth
  7. */
  8. /**
  9. * Code
  10. */
  11. /**
  12. * Auth can be used to instanciate objects or as a library to manage courses
  13. * @package chamilo.auth
  14. */
  15. class Auth {
  16. /**
  17. * Constructor
  18. */
  19. public function __construct() {
  20. }
  21. /**
  22. * retrieves all the courses that the user has already subscribed to
  23. * @param int User id
  24. * @return array an array containing all the information of the courses of the given user
  25. */
  26. public function get_courses_of_user($user_id) {
  27. $TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
  28. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  29. $TABLE_COURSE_FIELD = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  30. $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  31. // get course list auto-register
  32. $sql = "SELECT course_code FROM $TABLE_COURSE_FIELD_VALUE tcfv INNER JOIN $TABLE_COURSE_FIELD tcf ON " .
  33. " tcfv.field_id = tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  34. $special_course_result = Database::query($sql);
  35. if (Database::num_rows($special_course_result) > 0) {
  36. $special_course_list = array();
  37. while ($result_row = Database::fetch_array($special_course_result)) {
  38. $special_course_list[] = '"' . $result_row['course_code'] . '"';
  39. }
  40. }
  41. $without_special_courses = '';
  42. if (!empty($special_course_list)) {
  43. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  44. }
  45. // Secondly we select the courses that are in a category (user_course_cat<>0) and sort these according to the sort of the category
  46. $user_id = intval($user_id);
  47. $sql_select_courses = "SELECT course.code k, course.visual_code vc, course.subscribe subscr, course.unsubscribe unsubscr,
  48. course.title i, course.tutor_name t, course.db_name db, course.directory dir, course_rel_user.status status,
  49. course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
  50. FROM $TABLECOURS course, $TABLECOURSUSER course_rel_user
  51. WHERE course.code = course_rel_user.course_code
  52. AND course_rel_user.relation_type<>" . COURSE_RELATION_TYPE_RRHH . "
  53. AND course_rel_user.user_id = '" . $user_id . "' $without_special_courses
  54. ORDER BY course_rel_user.sort ASC";
  55. $result = Database::query($sql_select_courses);
  56. $courses = array();
  57. while ($row = Database::fetch_array($result)) {
  58. //we only need the database name of the course
  59. $courses[] = array('db' => $row['db'], 'code' => $row['k'], 'visual_code' => $row['vc'], 'title' => $row['i'], 'directory' => $row['dir'], 'status' => $row['status'], 'tutor' => $row['t'], 'subscribe' => $row['subscr'], 'unsubscribe' => $row['unsubscr'], 'sort' => $row['sort'], 'user_course_category' => $row['user_course_cat']);
  60. }
  61. return $courses;
  62. }
  63. /**
  64. * retrieves the user defined course categories
  65. * @return array containing all the IDs of the user defined courses categories, sorted by the "sort" field
  66. */
  67. public function get_user_course_categories() {
  68. $user_id = api_get_user_id();
  69. $table_category = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  70. $sql = "SELECT * FROM " . $table_category . " WHERE user_id=$user_id ORDER BY sort ASC";
  71. $result = Database::query($sql);
  72. $output = array();
  73. while ($row = Database::fetch_array($result)) {
  74. $output[] = $row;
  75. }
  76. return $output;
  77. }
  78. /**
  79. * This function get all the courses in the particular user category;
  80. * @param int User category id
  81. * @return string: the name of the user defined course category
  82. */
  83. public function get_courses_in_category() {
  84. $user_id = api_get_user_id();
  85. // table definitions
  86. $TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
  87. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  88. $TABLE_USER_COURSE_CATEGORY = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  89. $TABLE_COURSE_FIELD = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
  90. $TABLE_COURSE_FIELD_VALUE = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  91. // get course list auto-register
  92. $sql = "SELECT course_code FROM $TABLE_COURSE_FIELD_VALUE tcfv INNER JOIN $TABLE_COURSE_FIELD tcf ON " .
  93. " tcfv.field_id = tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  94. $special_course_result = Database::query($sql);
  95. if (Database::num_rows($special_course_result) > 0) {
  96. $special_course_list = array();
  97. while ($result_row = Database::fetch_array($special_course_result)) {
  98. $special_course_list[] = '"' . $result_row['course_code'] . '"';
  99. }
  100. }
  101. $without_special_courses = '';
  102. if (!empty($special_course_list)) {
  103. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  104. }
  105. $sql_select_courses = "SELECT course.code, course.visual_code, course.subscribe subscr, course.unsubscribe unsubscr,
  106. course.title title, course.tutor_name tutor, course.db_name, course.directory, course_rel_user.status status,
  107. course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
  108. FROM $TABLECOURS course,
  109. $TABLECOURSUSER course_rel_user
  110. WHERE course.code = course_rel_user.course_code
  111. AND course_rel_user.user_id = '" . $user_id . "'
  112. AND course_rel_user.relation_type <> " . COURSE_RELATION_TYPE_RRHH . "
  113. $without_special_courses
  114. ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC";
  115. $result = Database::query($sql_select_courses);
  116. $number_of_courses = Database::num_rows($result);
  117. $data = array();
  118. while ($course = Database::fetch_array($result)) {
  119. $data[$course['user_course_cat']][] = $course;
  120. }
  121. return $data;
  122. }
  123. /**
  124. * stores the changes in a course category (moving a course to a different course category)
  125. * @param string Course code
  126. * @param int Category id
  127. * @return bool True if it success
  128. */
  129. public function store_changecoursecategory($course_code, $newcategory) {
  130. $course_code = Database::escape_string($course_code);
  131. $newcategory = intval($newcategory);
  132. $current_user = api_get_user_id();
  133. $result = false;
  134. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  135. $max_sort_value = api_max_sort_value($newcategory, $current_user); // max_sort_value($newcategory);
  136. Database::query("UPDATE $TABLECOURSUSER SET user_course_cat='" . $newcategory . "', sort='" . ($max_sort_value + 1) . "' WHERE course_code='" . $course_code . "' AND user_id='" . $current_user . "' AND relation_type<>" . COURSE_RELATION_TYPE_RRHH . " ");
  137. if (Database::affected_rows()) {
  138. $result = true;
  139. }
  140. return $result;
  141. }
  142. /**
  143. * moves the course one place up or down
  144. * @param string Direction (up/down)
  145. * @param string Course code
  146. * @param int Category id
  147. * @return bool True if it success
  148. */
  149. public function move_course($direction, $course2move, $category) {
  150. // definition of tables
  151. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  152. $current_user_id = api_get_user_id();
  153. $all_user_courses = $this->get_courses_of_user($current_user_id);
  154. $result = false;
  155. // we need only the courses of the category we are moving in
  156. $user_courses = array();
  157. foreach ($all_user_courses as $key => $course) {
  158. if ($course['user_course_category'] == $category) {
  159. $user_courses[] = $course;
  160. }
  161. }
  162. $target_course = array();
  163. foreach ($user_courses as $count => $course) {
  164. if ($course2move == $course['code']) {
  165. // source_course is the course where we clicked the up or down icon
  166. $source_course = $course;
  167. // target_course is the course before/after the source_course (depending on the up/down icon)
  168. if ($direction == 'up') {
  169. $target_course = $user_courses[$count - 1];
  170. } else {
  171. $target_course = $user_courses[$count + 1];
  172. }
  173. break;
  174. }
  175. }
  176. if (count($target_course) > 0 && count($source_course) > 0) {
  177. $sql_update1 = "UPDATE $TABLECOURSUSER SET sort='" . $target_course['sort'] . "' WHERE course_code='" . $source_course['code'] . "' AND user_id='" . $current_user_id . "' AND relation_type<>" . COURSE_RELATION_TYPE_RRHH . " ";
  178. $sql_update2 = "UPDATE $TABLECOURSUSER SET sort='" . $source_course['sort'] . "' WHERE course_code='" . $target_course['code'] . "' AND user_id='" . $current_user_id . "' AND relation_type<>" . COURSE_RELATION_TYPE_RRHH . " ";
  179. Database::query($sql_update2);
  180. Database::query($sql_update1);
  181. if (Database::affected_rows()) {
  182. $result = true;
  183. }
  184. }
  185. return $result;
  186. }
  187. /**
  188. * Moves the course one place up or down
  189. * @param string Direction up/down
  190. * @param string Category id
  191. * @return bool True If it success
  192. */
  193. public function move_category($direction, $category2move) {
  194. // the database definition of the table that stores the user defined course categories
  195. $table_user_defined_category = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  196. $current_user_id = api_get_user_id();
  197. $user_coursecategories = $this->get_user_course_categories();
  198. $user_course_categories_info = $this->get_user_course_categories_info();
  199. $result = false;
  200. foreach ($user_coursecategories as $key => $category) {
  201. $category_id = $category['id'];
  202. if ($category2move == $category_id) {
  203. // source_course is the course where we clicked the up or down icon
  204. $source_category = $user_course_categories_info[$category2move];
  205. // target_course is the course before/after the source_course (depending on the up/down icon)
  206. if ($direction == 'up') {
  207. $target_category = $user_course_categories_info[$user_coursecategories[$key - 1]['id']];
  208. } else {
  209. $target_category = $user_course_categories_info[$user_coursecategories[$key + 1]['id']];
  210. }
  211. }
  212. }
  213. if (count($target_category) > 0 && count($source_category) > 0) {
  214. $sql_update1 = "UPDATE $table_user_defined_category SET sort='" . Database::escape_string($target_category['sort']) . "' WHERE id='" . intval($source_category['id']) . "' AND user_id='" . $current_user_id . "'";
  215. $sql_update2 = "UPDATE $table_user_defined_category SET sort='" . Database::escape_string($source_category['sort']) . "' WHERE id='" . intval($target_category['id']) . "' AND user_id='" . $current_user_id . "'";
  216. Database::query($sql_update2);
  217. Database::query($sql_update1);
  218. if (Database::affected_rows()) {
  219. $result = true;
  220. }
  221. }
  222. return $result;
  223. }
  224. /**
  225. * Retrieves the user defined course categories and all the info that goes with it
  226. * @return array containing all the info of the user defined courses categories with the id as key of the array
  227. */
  228. public function get_user_course_categories_info() {
  229. $current_user_id = api_get_user_id();
  230. $table_category = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  231. $sql = "SELECT * FROM " . $table_category . " WHERE user_id='" . $current_user_id . "' ORDER BY sort ASC";
  232. $result = Database::query($sql);
  233. while ($row = Database::fetch_array($result)) {
  234. $output[$row['id']] = $row;
  235. }
  236. return $output;
  237. }
  238. /**
  239. * Updates the user course category in the chamilo_user database
  240. * @param string Category title
  241. * @param int Category id
  242. * @return bool True if it success
  243. */
  244. public function store_edit_course_category($title, $category_id) {
  245. // protect data
  246. $title = Database::escape_string($title);
  247. $category_id = intval($category_id);
  248. $result = false;
  249. $tucc = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  250. $sql_update = "UPDATE $tucc SET title='" . api_htmlentities($title, ENT_QUOTES, api_get_system_encoding()) . "' WHERE id='" . $category_id . "'";
  251. Database::query($sql_update);
  252. if (Database::affected_rows()) {
  253. $result = true;
  254. }
  255. return $result;
  256. }
  257. /**
  258. * deletes a course category and moves all the courses that were in this category to main category
  259. * @param int Category id
  260. * @return bool True if it success
  261. */
  262. public function delete_course_category($category_id) {
  263. $current_user_id = api_get_user_id();
  264. $tucc = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  265. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  266. $category_id = intval($category_id);
  267. $result = false;
  268. $sql_delete = "DELETE FROM $tucc WHERE id='" . $category_id . "' and user_id='" . $current_user_id . "'";
  269. Database::query($sql_delete);
  270. if (Database::affected_rows()) {
  271. $result = true;
  272. }
  273. $sql_update = "UPDATE $TABLECOURSUSER SET user_course_cat='0' WHERE user_course_cat='" . $category_id . "' AND user_id='" . $current_user_id . "' AND relation_type<>" . COURSE_RELATION_TYPE_RRHH . " ";
  274. Database::query($sql_update);
  275. return $result;
  276. }
  277. /**
  278. * unsubscribe the user from a given course
  279. * @param string Course code
  280. * @return bool True if it success
  281. */
  282. public function remove_user_from_course($course_code) {
  283. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  284. // protect variables
  285. $current_user_id = api_get_user_id();
  286. $course_code = Database::escape_string($course_code);
  287. $result = true;
  288. // we check (once again) if the user is not course administrator
  289. // because the course administrator cannot unsubscribe himself
  290. // (s)he can only delete the course
  291. $sql_check = "SELECT * FROM $tbl_course_user WHERE user_id='" . $current_user_id . "' AND course_code='" . $course_code . "' AND status='1' ";
  292. $result_check = Database::query($sql_check);
  293. $number_of_rows = Database::num_rows($result_check);
  294. if ($number_of_rows > 0) {
  295. $result = false;
  296. }
  297. CourseManager::unsubscribe_user($current_user_id, $course_code);
  298. return $result;
  299. }
  300. /**
  301. * stores the user course category in the chamilo_user database
  302. * @param string Category title
  303. * @return bool True if it success
  304. */
  305. public function store_course_category($category_title) {
  306. $tucc = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  307. // protect data
  308. $current_user_id = api_get_user_id();
  309. $category_title = Database::escape_string($category_title);
  310. $result = false;
  311. // step 1: we determine the max value of the user defined course categories
  312. $sql = "SELECT sort FROM $tucc WHERE user_id='" . $current_user_id . "' ORDER BY sort DESC";
  313. $rs_sort = Database::query($sql);
  314. $maxsort = Database::fetch_array($rs_sort);
  315. $nextsort = $maxsort['sort'] + 1;
  316. // step 2: we check if there is already a category with this name, if not we store it, else we give an error.
  317. $sql = "SELECT * FROM $tucc WHERE user_id='" . $current_user_id . "' AND title='" . $category_title . "'ORDER BY sort DESC";
  318. $rs = Database::query($sql);
  319. if (Database::num_rows($rs) == 0) {
  320. $sql_insert = "INSERT INTO $tucc (user_id, title,sort) VALUES ('" . $current_user_id . "', '" . api_htmlentities($category_title, ENT_QUOTES, api_get_system_encoding()) . "', '" . $nextsort . "')";
  321. Database::query($sql_insert);
  322. if (Database::affected_rows()) {
  323. $result = true;
  324. }
  325. } else {
  326. $result = false;
  327. }
  328. return $result;
  329. }
  330. /**
  331. * Counts the number of courses in a given course category
  332. * @param string Category code
  333. * @return int Count of courses
  334. */
  335. public function count_courses_in_category($category_code) {
  336. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  337. $TABLE_COURSE_FIELD = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
  338. $TABLE_COURSE_FIELD_VALUE = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  339. // get course list auto-register
  340. $sql = "SELECT course_code FROM $TABLE_COURSE_FIELD_VALUE tcfv INNER JOIN $TABLE_COURSE_FIELD tcf ON " .
  341. " tcfv.field_id = tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  342. $special_course_result = Database::query($sql);
  343. if (Database::num_rows($special_course_result) > 0) {
  344. $special_course_list = array();
  345. while ($result_row = Database::fetch_array($special_course_result)) {
  346. $special_course_list[] = '"' . $result_row['course_code'] . '"';
  347. }
  348. }
  349. $without_special_courses = '';
  350. if (!empty($special_course_list)) {
  351. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  352. }
  353. $sql = "SELECT * FROM $tbl_course WHERE category_code" . (empty($category_code) ? " IS NULL" : "='" . $category_code . "'") . $without_special_courses;
  354. if (api_is_multiple_url_enabled()) {
  355. $url_access_id = api_get_current_access_url_id();
  356. if ($url_access_id != -1) {
  357. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  358. $sql = "SELECT * FROM $tbl_course as course INNER JOIN $tbl_url_rel_course as url_rel_course
  359. ON (url_rel_course.course_code=course.code)
  360. WHERE access_url_id = $url_access_id AND category_code" . (empty($category_code) ? " IS NULL" : "='" . $category_code . "'") . $without_special_courses;
  361. }
  362. }
  363. return Database::num_rows(Database::query($sql));
  364. }
  365. /**
  366. * Get the browsing of the course categories (faculties)
  367. * @return array array containing a list with all the categories and subcategories(if needed)
  368. */
  369. public function browse_course_categories() {
  370. $tbl_courses_nodes = Database::get_main_table(TABLE_MAIN_CATEGORY);
  371. $sql = "SELECT * FROM $tbl_courses_nodes ORDER BY tree_pos ASC";
  372. $result = Database::query($sql);
  373. $categories = array();
  374. while ($row = Database::fetch_array($result)) {
  375. $count_courses = $this->count_courses_in_category($row['code']);
  376. $row['count_courses'] = $count_courses;
  377. if (!isset($row['parent_id'])) {
  378. $categories[0][$row['tree_pos']] = $row;
  379. } else {
  380. $categories[$row['parent_id']][$row['tree_pos']] = $row;
  381. }
  382. }
  383. return $categories;
  384. }
  385. /**
  386. * Display all the courses in the given course category. I could have used a parameter here
  387. * @param string Category code
  388. * @return array Courses data
  389. */
  390. public function browse_courses_in_category($category_code, $random_value = null) {
  391. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  392. $TABLE_COURSE_FIELD = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  393. $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  394. // Get course list auto-register
  395. $sql = "SELECT course_code FROM $TABLE_COURSE_FIELD_VALUE tcfv INNER JOIN $TABLE_COURSE_FIELD tcf ON tcfv.field_id = tcf.id
  396. WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  397. $special_course_result = Database::query($sql);
  398. if (Database::num_rows($special_course_result) > 0) {
  399. $special_course_list = array();
  400. while ($result_row = Database::fetch_array($special_course_result)) {
  401. $special_course_list[] = '"' . $result_row['course_code'] . '"';
  402. }
  403. }
  404. $without_special_courses = '';
  405. if (!empty($special_course_list)) {
  406. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  407. }
  408. if (!empty($random_value)) {
  409. $random_value = intval($random_value);
  410. $sql = "SELECT COUNT(*) FROM $tbl_course";
  411. $result = Database::query($sql);
  412. list($num_records) = Database::fetch_row($result);
  413. if (api_is_multiple_url_enabled()) {
  414. $url_access_id = api_get_current_access_url_id();
  415. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  416. $sql = "SELECT COUNT(*) FROM $tbl_course course INNER JOIN $tbl_url_rel_course as url_rel_course ON (url_rel_course.course_code=course.code)
  417. WHERE access_url_id = $url_access_id ";
  418. $result = Database::query($sql);
  419. list($num_records) = Database::fetch_row($result);
  420. $sql = "SELECT course.id FROM $tbl_course course INNER JOIN $tbl_url_rel_course as url_rel_course
  421. ON (url_rel_course.course_code=course.code)
  422. WHERE access_url_id = $url_access_id AND
  423. RAND()*$num_records< $random_value
  424. $without_special_courses ORDER BY RAND() LIMIT 0, $random_value";
  425. } else {
  426. $sql = "SELECT id FROM $tbl_course course WHERE RAND()*$num_records< $random_value $without_special_courses ORDER BY RAND() LIMIT 0, $random_value";
  427. }
  428. $result = Database::query($sql);
  429. $id_in = null;
  430. while (list($id) = Database::fetch_row($result)) {
  431. if ($id_in) {
  432. $id_in.= ",$id";
  433. } else {
  434. $id_in = "$id";
  435. }
  436. }
  437. $sql = "SELECT * FROM $tbl_course WHERE id IN($id_in)";
  438. } else {
  439. $category_code = Database::escape_string($category_code);
  440. if (empty($category_code)) {
  441. $sql = "SELECT * FROM $tbl_course WHERE 1=1 $without_special_courses ORDER BY title ";
  442. } else {
  443. $sql = "SELECT * FROM $tbl_course WHERE category_code='$category_code' $without_special_courses ORDER BY title ";
  444. }
  445. //showing only the courses of the current Chamilo access_url_id
  446. if (api_is_multiple_url_enabled()) {
  447. $url_access_id = api_get_current_access_url_id();
  448. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  449. $sql = "SELECT * FROM $tbl_course as course INNER JOIN $tbl_url_rel_course as url_rel_course
  450. ON (url_rel_course.course_code=course.code)
  451. WHERE access_url_id = $url_access_id AND category_code='$category_code' $without_special_courses ORDER BY title";
  452. }
  453. }
  454. $result = Database::query($sql);
  455. $courses = array();
  456. while ($row = Database::fetch_array($result)) {
  457. $row['registration_code'] = !empty($row['registration_code']);
  458. $count_users = CourseManager::get_users_count_in_course($row['code']);
  459. $count_connections_last_month = Tracking::get_course_connections_count($row['code'], 0, api_get_utc_datetime(time() - (30 * 86400)));
  460. if ($row['tutor_name'] == '0') {
  461. $row['tutor_name'] = get_lang('NoManager');
  462. }
  463. $point_info = CourseManager::get_course_ranking($row['id'], 0);
  464. $courses[] = array(
  465. 'real_id' => $row['id'],
  466. 'point_info' => $point_info,
  467. 'code' => $row['code'],
  468. 'directory' => $row['directory'],
  469. 'db' => $row['db_name'],
  470. 'visual_code' => $row['visual_code'],
  471. 'title' => $row['title'],
  472. 'tutor' => $row['tutor_name'],
  473. 'subscribe' => $row['subscribe'],
  474. 'unsubscribe' => $row['unsubscribe'],
  475. 'registration_code' => $row['registration_code'],
  476. 'creation_date' => $row['creation_date'],
  477. 'visibility' => $row['visibility'],
  478. 'count_users' => $count_users,
  479. 'count_connections' => $count_connections_last_month
  480. );
  481. }
  482. return $courses;
  483. }
  484. /**
  485. * Search the courses database for a course that matches the search term.
  486. * The search is done on the code, title and tutor field of the course table.
  487. * @param string $search_term: the string that the user submitted, what we are looking for
  488. * @return array an array containing a list of all the courses (the code, directory, dabase, visual_code, title, ... ) matching the the search term.
  489. */
  490. public function search_courses($search_term) {
  491. $TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
  492. $TABLE_COURSE_FIELD = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
  493. $TABLE_COURSE_FIELD_VALUE = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  494. // get course list auto-register
  495. $sql = "SELECT course_code FROM $TABLE_COURSE_FIELD_VALUE tcfv INNER JOIN $TABLE_COURSE_FIELD tcf ON tcfv.field_id = tcf.id
  496. WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  497. $special_course_result = Database::query($sql);
  498. if (Database::num_rows($special_course_result) > 0) {
  499. $special_course_list = array();
  500. while ($result_row = Database::fetch_array($special_course_result)) {
  501. $special_course_list[] = '"' . $result_row['course_code'] . '"';
  502. }
  503. }
  504. $without_special_courses = '';
  505. if (!empty($special_course_list)) {
  506. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  507. }
  508. $search_term_safe = Database::escape_string($search_term);
  509. $sql_find = "SELECT * FROM $TABLECOURS WHERE (code LIKE '%" . $search_term_safe . "%' OR title LIKE '%" . $search_term_safe . "%' OR tutor_name LIKE '%" . $search_term_safe . "%') $without_special_courses ORDER BY title, visual_code ASC";
  510. if (api_is_multiple_url_enabled()) {
  511. $url_access_id = api_get_current_access_url_id();
  512. if ($url_access_id != -1) {
  513. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  514. $sql_find = "SELECT * FROM $TABLECOURS as course INNER JOIN $tbl_url_rel_course as url_rel_course
  515. ON (url_rel_course.course_code=course.code)
  516. WHERE access_url_id = $url_access_id AND (code LIKE '%" . $search_term_safe . "%' OR title LIKE '%" . $search_term_safe . "%' OR tutor_name LIKE '%" . $search_term_safe . "%' ) $without_special_courses ORDER BY title, visual_code ASC ";
  517. }
  518. }
  519. $result_find = Database::query($sql_find);
  520. $courses = array();
  521. while ($row = Database::fetch_array($result_find)) {
  522. $row['registration_code'] = !empty($row['registration_code']);
  523. $count_users = count(CourseManager::get_user_list_from_course_code($row['code']));
  524. $count_connections_last_month = Tracking::get_course_connections_count($row['code'], 0, api_get_utc_datetime(time() - (30 * 86400)));
  525. $courses[] = array(
  526. 'code' => $row['code'],
  527. 'directory' => $row['directory'],
  528. 'db' => $row['db_name'],
  529. 'visual_code' => $row['visual_code'],
  530. 'title' => $row['title'],
  531. 'tutor' => $row['tutor_name'],
  532. 'subscribe' => $row['subscribe'],
  533. 'unsubscribe' => $row['unsubscribe'],
  534. 'registration_code' => $row['registration_code'],
  535. 'creation_date' => $row['creation_date'],
  536. 'visibility' => $row['visibility'],
  537. 'count_users' => $count_users,
  538. 'count_connections' => $count_connections_last_month
  539. );
  540. }
  541. return $courses;
  542. }
  543. /**
  544. * Subscribe the user to a given course
  545. * @param string Course code
  546. * @return string Message about results
  547. */
  548. public function subscribe_user($course_code) {
  549. $user_id = api_get_user_id();
  550. $all_course_information = CourseManager::get_course_information($course_code);
  551. if ($all_course_information['registration_code'] == '' || $_POST['course_registration_code'] == $all_course_information['registration_code']) {
  552. if (api_is_platform_admin()) {
  553. $status_user_in_new_course = COURSEMANAGER;
  554. } else {
  555. $status_user_in_new_course = null;
  556. }
  557. if (CourseManager::add_user_to_course($user_id, $course_code, $status_user_in_new_course)) {
  558. $send = api_get_course_setting('email_alert_to_teacher_on_new_user_in_course', $course_code);
  559. if ($send == 1) {
  560. CourseManager::email_to_tutor($user_id, $course_code, $send_to_tutor_also = false);
  561. } else if ($send == 2) {
  562. CourseManager::email_to_tutor($user_id, $course_code, $send_to_tutor_also = true);
  563. }
  564. $url = Display::url($all_course_information['title'], api_get_course_url($course_code));
  565. $message = sprintf(get_lang('EnrollToCourseXSuccessful'), $url);
  566. } else {
  567. $message = get_lang('ErrorContactPlatformAdmin');
  568. }
  569. return array('message' => $message);
  570. } else {
  571. if (isset($_POST['course_registration_code']) && $_POST['course_registration_code'] != $all_course_information['registration_code']) {
  572. return false;
  573. }
  574. $message = get_lang('CourseRequiresPassword') . '<br />';
  575. $message .= $all_course_information['title'].' ('.$all_course_information['visual_code'].') ';
  576. $action = api_get_path(WEB_CODE_PATH) . "auth/courses.php?action=subscribe_user_with_password&sec_token=" . Security::getCurrentToken();
  577. $form = new FormValidator('subscribe_user_with_password', 'post', $action);
  578. $form->addElement('hidden', 'sec_token', Security::getCurrentToken());
  579. $form->addElement('hidden', 'subscribe_user_with_password', $all_course_information['code']);
  580. $form->addElement('text', 'course_registration_code');
  581. $form->addElement('button', 'submit', get_lang('SubmitRegistrationCode'));
  582. $content = $form->return_form();
  583. return array('message' => $message, 'content' => $content);
  584. }
  585. }
  586. }