auth.lib.php 34 KB

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