auth.lib.php 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Class Auth
  5. * Auth can be used to instantiate objects or as a library to manage courses
  6. * This file contains a class used like library provides functions for auth tool.
  7. * It's also used like model to courses_controller (MVC pattern)
  8. * @author Christian Fasanando <christian1827@gmail.com>
  9. *
  10. * @package chamilo.auth
  11. */
  12. class Auth
  13. {
  14. /**
  15. * Constructor
  16. */
  17. public function __construct()
  18. {
  19. }
  20. /**
  21. * retrieves all the courses that the user has already subscribed to
  22. * @param int $user_id
  23. * @return array an array containing all the information of the courses of the given user
  24. */
  25. public function get_courses_of_user($user_id)
  26. {
  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_EXTRA_FIELD);
  30. $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  31. $extraFieldType = \Chamilo\CoreBundle\Entity\ExtraField::COURSE_FIELD_TYPE;
  32. // get course list auto-register
  33. $sql = "SELECT item_id FROM $TABLE_COURSE_FIELD_VALUE tcfv
  34. INNER JOIN $TABLE_COURSE_FIELD tcf
  35. ON tcfv.field_id = tcf.id
  36. WHERE
  37. tcf.extra_field_type = $extraFieldType AND
  38. tcf.variable = 'special_course' AND
  39. tcfv.value = 1
  40. ";
  41. $result = Database::query($sql);
  42. $special_course_list = array();
  43. if (Database::num_rows($result) > 0) {
  44. while ($result_row = Database::fetch_array($result)) {
  45. $special_course_list[] = '"' . $result_row['item_id'] . '"';
  46. }
  47. }
  48. $without_special_courses = '';
  49. if (!empty($special_course_list)) {
  50. $without_special_courses = ' AND course.id NOT IN (' . implode(',', $special_course_list) . ')';
  51. }
  52. // Secondly we select the courses that are in a category (user_course_cat<>0) and sort these according to the sort of the category
  53. $user_id = intval($user_id);
  54. $sql = "SELECT
  55. course.code k,
  56. course.visual_code vc,
  57. course.subscribe subscr,
  58. course.unsubscribe unsubscr,
  59. course.title i,
  60. course.tutor_name t,
  61. course.directory dir,
  62. course_rel_user.status status,
  63. course_rel_user.sort sort,
  64. course_rel_user.user_course_cat user_course_cat
  65. FROM $TABLECOURS course, $TABLECOURSUSER course_rel_user
  66. WHERE
  67. course.id = course_rel_user.c_id AND
  68. course_rel_user.relation_type<>" . COURSE_RELATION_TYPE_RRHH . " AND
  69. course_rel_user.user_id = '" . $user_id . "' $without_special_courses
  70. ORDER BY course_rel_user.sort ASC";
  71. $result = Database::query($sql);
  72. $courses = array();
  73. while ($row = Database::fetch_array($result)) {
  74. //we only need the database name of the course
  75. $courses[] = array(
  76. 'code' => $row['k'],
  77. 'visual_code' => $row['vc'],
  78. 'title' => $row['i'],
  79. 'directory' => $row['dir'],
  80. 'status' => $row['status'],
  81. 'tutor' => $row['t'],
  82. 'subscribe' => $row['subscr'],
  83. 'unsubscribe' => $row['unsubscr'],
  84. 'sort' => $row['sort'],
  85. 'user_course_category' => $row['user_course_cat']
  86. );
  87. }
  88. return $courses;
  89. }
  90. /**
  91. * retrieves the user defined course categories
  92. * @return array containing all the IDs of the user defined courses categories, sorted by the "sort" field
  93. */
  94. public function get_user_course_categories()
  95. {
  96. $user_id = api_get_user_id();
  97. $table_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  98. $sql = "SELECT * FROM " . $table_category . "
  99. WHERE user_id=$user_id
  100. ORDER BY sort ASC";
  101. $result = Database::query($sql);
  102. $output = array();
  103. while ($row = Database::fetch_array($result)) {
  104. $output[] = $row;
  105. }
  106. return $output;
  107. }
  108. /**
  109. * This function get all the courses in the particular user category;
  110. * @return string: the name of the user defined course category
  111. */
  112. public function get_courses_in_category()
  113. {
  114. $user_id = api_get_user_id();
  115. // table definitions
  116. $TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
  117. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  118. $TABLE_COURSE_FIELD = Database::get_main_table(TABLE_EXTRA_FIELD);
  119. $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  120. $extraFieldType = \Chamilo\CoreBundle\Entity\ExtraField::COURSE_FIELD_TYPE;
  121. // get course list auto-register
  122. $sql = "SELECT item_id
  123. FROM $TABLE_COURSE_FIELD_VALUE tcfv
  124. INNER JOIN $TABLE_COURSE_FIELD tcf
  125. ON tcfv.field_id = tcf.id
  126. WHERE
  127. tcf.extra_field_type = $extraFieldType AND
  128. tcf.variable = 'special_course' AND
  129. tcfv.value = 1 ";
  130. $result = Database::query($sql);
  131. $special_course_list = array();
  132. if (Database::num_rows($result) > 0) {
  133. while ($result_row = Database::fetch_array($result)) {
  134. $special_course_list[] = '"' . $result_row['item_id'] . '"';
  135. }
  136. }
  137. $without_special_courses = '';
  138. if (!empty($special_course_list)) {
  139. $without_special_courses = ' AND course.id NOT IN (' . implode(',', $special_course_list) . ')';
  140. }
  141. $sql = "SELECT
  142. course.code, course.visual_code, course.subscribe subscr, course.unsubscribe unsubscr,
  143. course.title title, course.tutor_name tutor, course.directory, course_rel_user.status status,
  144. course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
  145. FROM $TABLECOURS course,
  146. $TABLECOURSUSER course_rel_user
  147. WHERE
  148. course.id = course_rel_user.c_id AND
  149. course_rel_user.user_id = '" . $user_id . "' AND
  150. course_rel_user.relation_type <> " . COURSE_RELATION_TYPE_RRHH . "
  151. $without_special_courses
  152. ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC";
  153. $result = Database::query($sql);
  154. $number_of_courses = Database::num_rows($result);
  155. $data = array();
  156. while ($course = Database::fetch_array($result)) {
  157. $data[$course['user_course_cat']][] = $course;
  158. }
  159. return $data;
  160. }
  161. /**
  162. * stores the changes in a course category
  163. * (moving a course to a different course category)
  164. * @param int $courseId
  165. * @param int Category id
  166. * @return bool True if it success
  167. */
  168. public function updateCourseCategory($courseId, $newcategory)
  169. {
  170. $courseId = intval($courseId);
  171. $newcategory = intval($newcategory);
  172. $current_user = api_get_user_id();
  173. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  174. $max_sort_value = api_max_sort_value($newcategory, $current_user);
  175. $sql = "UPDATE $TABLECOURSUSER SET
  176. user_course_cat='" . $newcategory . "',
  177. sort='" . ($max_sort_value + 1) . "'
  178. WHERE
  179. c_id ='" . $courseId . "' AND
  180. user_id='" . $current_user . "' AND
  181. relation_type<>" . COURSE_RELATION_TYPE_RRHH;
  182. $resultQuery = Database::query($sql);
  183. $result = false;
  184. if (Database::affected_rows($resultQuery)) {
  185. $result = true;
  186. }
  187. return $result;
  188. }
  189. /**
  190. * moves the course one place up or down
  191. * @param string Direction (up/down)
  192. * @param string Course code
  193. * @param int Category id
  194. * @return bool True if it success
  195. */
  196. public function move_course($direction, $course2move, $category)
  197. {
  198. // definition of tables
  199. $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  200. $current_user_id = api_get_user_id();
  201. $all_user_courses = $this->get_courses_of_user($current_user_id);
  202. // we need only the courses of the category we are moving in
  203. $user_courses = array();
  204. foreach ($all_user_courses as $key => $course) {
  205. if ($course['user_course_category'] == $category) {
  206. $user_courses[] = $course;
  207. }
  208. }
  209. $target_course = array();
  210. foreach ($user_courses as $count => $course) {
  211. if ($course2move == $course['code']) {
  212. // source_course is the course where we clicked the up or down icon
  213. $source_course = $course;
  214. // target_course is the course before/after the source_course (depending on the up/down icon)
  215. if ($direction == 'up') {
  216. $target_course = $user_courses[$count - 1];
  217. } else {
  218. $target_course = $user_courses[$count + 1];
  219. }
  220. break;
  221. }
  222. }
  223. $result = false;
  224. if (count($target_course) > 0 && count($source_course) > 0) {
  225. $courseInfo = api_get_course_info($source_course['code']);
  226. $courseId = $courseInfo['real_id'];
  227. $targetCourseInfo = api_get_course_info($target_course['code']);
  228. $targetCourseId = $targetCourseInfo['real_id'];
  229. $sql = "UPDATE $table
  230. SET sort='" . $target_course['sort'] . "'
  231. WHERE
  232. c_id = '" . $courseId . "' AND
  233. user_id = '" . $current_user_id . "' AND
  234. relation_type<>" . COURSE_RELATION_TYPE_RRHH;
  235. $result1 = Database::query($sql);
  236. $sql = "UPDATE $table SET sort='" . $source_course['sort'] . "'
  237. WHERE
  238. c_id ='" . $targetCourseId . "' AND
  239. user_id='" . $current_user_id . "' AND
  240. relation_type<>" . COURSE_RELATION_TYPE_RRHH;
  241. $result2 = Database::query($sql);
  242. if (Database::affected_rows($result1) && Database::affected_rows($result2)) {
  243. $result = true;
  244. }
  245. }
  246. return $result;
  247. }
  248. /**
  249. * Moves the course one place up or down
  250. * @param string Direction up/down
  251. * @param string Category id
  252. * @return bool True If it success
  253. */
  254. public function move_category($direction, $category2move)
  255. {
  256. // the database definition of the table that stores the user defined course categories
  257. $table_user_defined_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  258. $current_user_id = api_get_user_id();
  259. $user_coursecategories = $this->get_user_course_categories();
  260. $user_course_categories_info = $this->get_user_course_categories_info();
  261. $result = false;
  262. foreach ($user_coursecategories as $key => $category) {
  263. $category_id = $category['id'];
  264. if ($category2move == $category_id) {
  265. // source_course is the course where we clicked the up or down icon
  266. $source_category = $user_course_categories_info[$category2move];
  267. // target_course is the course before/after the source_course (depending on the up/down icon)
  268. if ($direction == 'up') {
  269. $target_category = $user_course_categories_info[$user_coursecategories[$key - 1]['id']];
  270. } else {
  271. $target_category = $user_course_categories_info[$user_coursecategories[$key + 1]['id']];
  272. }
  273. }
  274. }
  275. if (count($target_category) > 0 && count($source_category) > 0) {
  276. $sql_update1 = "UPDATE $table_user_defined_category SET sort='" . Database::escape_string($target_category['sort']) . "'
  277. WHERE id='" . intval($source_category['id']) . "' AND user_id='" . $current_user_id . "'";
  278. $sql_update2 = "UPDATE $table_user_defined_category SET sort='" . Database::escape_string($source_category['sort']) . "'
  279. WHERE id='" . intval($target_category['id']) . "' AND user_id='" . $current_user_id . "'";
  280. $result1 = Database::query($sql_update2);
  281. $result2 = Database::query($sql_update1);
  282. if (Database::affected_rows($result1) && Database::affected_rows($result2)) {
  283. $result = true;
  284. }
  285. }
  286. return $result;
  287. }
  288. /**
  289. * Retrieves the user defined course categories and all the info that goes with it
  290. * @return array containing all the info of the user defined courses categories with the id as key of the array
  291. */
  292. public function get_user_course_categories_info()
  293. {
  294. $current_user_id = api_get_user_id();
  295. $table_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  296. $sql = "SELECT * FROM " . $table_category . "
  297. WHERE user_id='" . $current_user_id . "'
  298. ORDER BY sort ASC";
  299. $result = Database::query($sql);
  300. while ($row = Database::fetch_array($result)) {
  301. $output[$row['id']] = $row;
  302. }
  303. return $output;
  304. }
  305. /**
  306. * Updates the user course category in the chamilo_user database
  307. * @param string Category title
  308. * @param int Category id
  309. * @return bool True if it success
  310. */
  311. public function store_edit_course_category($title, $category_id)
  312. {
  313. // protect data
  314. $title = Database::escape_string($title);
  315. $category_id = intval($category_id);
  316. $result = false;
  317. $tucc = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  318. $sql = "UPDATE $tucc
  319. SET title='" . api_htmlentities($title, ENT_QUOTES, api_get_system_encoding()) . "'
  320. WHERE id='" . $category_id . "'";
  321. $resultQuery = Database::query($sql);
  322. if (Database::affected_rows($resultQuery)) {
  323. $result = true;
  324. }
  325. return $result;
  326. }
  327. /**
  328. * deletes a course category and moves all the courses that were in this category to main category
  329. * @param int Category id
  330. * @return bool True if it success
  331. */
  332. public function delete_course_category($category_id)
  333. {
  334. $current_user_id = api_get_user_id();
  335. $tucc = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  336. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  337. $category_id = intval($category_id);
  338. $result = false;
  339. $sql_delete = "DELETE FROM $tucc
  340. WHERE id='" . $category_id . "' and user_id='" . $current_user_id . "'";
  341. $resultQuery = Database::query($sql_delete);
  342. if (Database::affected_rows($resultQuery)) {
  343. $result = true;
  344. }
  345. $sql = "UPDATE $TABLECOURSUSER
  346. SET user_course_cat='0'
  347. WHERE
  348. user_course_cat='" . $category_id . "' AND
  349. user_id='" . $current_user_id . "' AND
  350. relation_type<>" . COURSE_RELATION_TYPE_RRHH . " ";
  351. Database::query($sql);
  352. return $result;
  353. }
  354. /**
  355. * Search the courses database for a course that matches the search term.
  356. * The search is done on the code, title and tutor field of the course table.
  357. * @param string $search_term The string that the user submitted, what we are looking for
  358. * @param array $limit
  359. * @return array An array containing a list of all the courses matching the the search term.
  360. */
  361. public function search_courses($search_term, $limit)
  362. {
  363. $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
  364. $extraFieldTable = Database :: get_main_table(TABLE_EXTRA_FIELD);
  365. $extraFieldValuesTable = Database :: get_main_table(TABLE_EXTRA_FIELD_VALUES);
  366. $limitFilter = getLimitFilterFromArray($limit);
  367. // get course list auto-register
  368. $sql = "SELECT item_id
  369. FROM $extraFieldValuesTable tcfv
  370. INNER JOIN $extraFieldTable tcf ON tcfv.field_id = tcf.id
  371. WHERE
  372. tcf.variable = 'special_course' AND
  373. tcfv.value = 1 ";
  374. $special_course_result = Database::query($sql);
  375. if (Database::num_rows($special_course_result) > 0) {
  376. $special_course_list = array();
  377. while ($result_row = Database::fetch_array($special_course_result)) {
  378. $special_course_list[] = '"' . $result_row['item_id'] . '"';
  379. }
  380. }
  381. $without_special_courses = '';
  382. if (!empty($special_course_list)) {
  383. $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
  384. }
  385. $search_term_safe = Database::escape_string($search_term);
  386. $sql_find = "SELECT * FROM $courseTable
  387. WHERE (
  388. code LIKE '%" . $search_term_safe . "%' OR
  389. title LIKE '%" . $search_term_safe . "%' OR
  390. tutor_name LIKE '%" . $search_term_safe . "%'
  391. )
  392. $without_special_courses
  393. ORDER BY title, visual_code ASC
  394. $limitFilter
  395. ";
  396. if (api_is_multiple_url_enabled()) {
  397. $url_access_id = api_get_current_access_url_id();
  398. if ($url_access_id != -1) {
  399. $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  400. $sql_find = "SELECT *
  401. FROM $courseTable as course
  402. INNER JOIN $tbl_url_rel_course as url_rel_course
  403. ON (url_rel_course.c_id = course.id)
  404. WHERE
  405. access_url_id = $url_access_id AND (
  406. code LIKE '%" . $search_term_safe . "%' OR
  407. title LIKE '%" . $search_term_safe . "%' OR
  408. tutor_name LIKE '%" . $search_term_safe . "%'
  409. )
  410. $without_special_courses
  411. ORDER BY title, visual_code ASC
  412. $limitFilter
  413. ";
  414. }
  415. }
  416. $result_find = Database::query($sql_find);
  417. $courses = array();
  418. while ($row = Database::fetch_array($result_find)) {
  419. $row['registration_code'] = !empty($row['registration_code']);
  420. $count_users = count(CourseManager::get_user_list_from_course_code($row['code']));
  421. $count_connections_last_month = Tracking::get_course_connections_count(
  422. $row['id'], 0, api_get_utc_datetime(time() - (30 * 86400))
  423. );
  424. $point_info = CourseManager::get_course_ranking($row['id'], 0);
  425. $courses[] = array(
  426. 'real_id' => $row['id'],
  427. 'point_info' => $point_info,
  428. 'code' => $row['code'],
  429. 'directory' => $row['directory'],
  430. 'visual_code' => $row['visual_code'],
  431. 'title' => $row['title'],
  432. 'tutor' => $row['tutor_name'],
  433. 'subscribe' => $row['subscribe'],
  434. 'unsubscribe' => $row['unsubscribe'],
  435. 'registration_code' => $row['registration_code'],
  436. 'creation_date' => $row['creation_date'],
  437. 'visibility' => $row['visibility'],
  438. 'count_users' => $count_users,
  439. 'count_connections' => $count_connections_last_month
  440. );
  441. }
  442. return $courses;
  443. }
  444. /**
  445. * unsubscribe the user from a given course
  446. * @param string Course code
  447. * @return bool True if it success
  448. */
  449. public function remove_user_from_course($course_code)
  450. {
  451. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  452. // protect variables
  453. $current_user_id = api_get_user_id();
  454. $course_code = Database::escape_string($course_code);
  455. $result = true;
  456. $courseInfo = api_get_course_info($course_code);
  457. $courseId = $courseInfo['real_id'];
  458. // we check (once again) if the user is not course administrator
  459. // because the course administrator cannot unsubscribe himself
  460. // (s)he can only delete the course
  461. $sql = "SELECT * FROM $tbl_course_user
  462. WHERE
  463. user_id='" . $current_user_id . "' AND
  464. c_id ='" . $courseId . "' AND
  465. status='1' ";
  466. $result_check = Database::query($sql);
  467. $number_of_rows = Database::num_rows($result_check);
  468. if ($number_of_rows > 0) {
  469. $result = false;
  470. }
  471. CourseManager::unsubscribe_user($current_user_id, $course_code);
  472. return $result;
  473. }
  474. /**
  475. * stores the user course category in the chamilo_user database
  476. * @param string Category title
  477. * @return bool True if it success
  478. */
  479. public function store_course_category($category_title)
  480. {
  481. $tucc = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  482. // protect data
  483. $current_user_id = api_get_user_id();
  484. $category_title = Database::escape_string($category_title);
  485. $result = false;
  486. // step 1: we determine the max value of the user defined course categories
  487. $sql = "SELECT sort FROM $tucc WHERE user_id='" . $current_user_id . "' ORDER BY sort DESC";
  488. $rs_sort = Database::query($sql);
  489. $maxsort = Database::fetch_array($rs_sort);
  490. $nextsort = $maxsort['sort'] + 1;
  491. // step 2: we check if there is already a category with this name, if not we store it, else we give an error.
  492. $sql = "SELECT * FROM $tucc WHERE user_id='" . $current_user_id . "' AND title='" . $category_title . "'ORDER BY sort DESC";
  493. $rs = Database::query($sql);
  494. if (Database::num_rows($rs) == 0) {
  495. $sql_insert = "INSERT INTO $tucc (user_id, title,sort)
  496. VALUES ('" . $current_user_id . "', '" . api_htmlentities($category_title, ENT_QUOTES, api_get_system_encoding()) . "', '" . $nextsort . "')";
  497. $resultQuery = Database::query($sql_insert);
  498. if (Database::affected_rows($resultQuery)) {
  499. $result = true;
  500. }
  501. } else {
  502. $result = false;
  503. }
  504. return $result;
  505. }
  506. /**
  507. * Counts the number of courses in a given course category
  508. * @param string $categoryCode Category code
  509. * @param $searchTerm
  510. * @return int Count of courses
  511. */
  512. public function count_courses_in_category($categoryCode, $searchTerm = '')
  513. {
  514. return countCoursesInCategory($categoryCode, $searchTerm);
  515. }
  516. /**
  517. * get the browsing of the course categories (faculties)
  518. * @return array array containing a list with all the categories and subcategories(if needed)
  519. */
  520. public function browse_course_categories()
  521. {
  522. return browseCourseCategories();
  523. }
  524. /**
  525. * Display all the courses in the given course category. I could have used a parameter here
  526. * @param string $categoryCode Category code
  527. * @param int $randomValue
  528. * @param array $limit will be used if $random_value is not set.
  529. * This array should contains 'start' and 'length' keys
  530. * @return array Courses data
  531. */
  532. public function browse_courses_in_category($categoryCode, $randomValue = null, $limit = array())
  533. {
  534. return browseCoursesInCategory($categoryCode, $randomValue, $limit);
  535. }
  536. /**
  537. * Subscribe the user to a given course
  538. * @param string Course code
  539. * @return string Message about results
  540. */
  541. public function subscribe_user($course_code)
  542. {
  543. $user_id = api_get_user_id();
  544. $all_course_information = CourseManager::get_course_information($course_code);
  545. if (
  546. $all_course_information['registration_code'] == '' ||
  547. (
  548. isset($_POST['course_registration_code']) &&
  549. $_POST['course_registration_code'] == $all_course_information['registration_code']
  550. )
  551. ) {
  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, $all_course_information['real_id'], $send_to_tutor_also = false);
  561. } else if ($send == 2) {
  562. CourseManager::email_to_tutor($user_id, $all_course_information['real_id'], $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=" . $_SESSION['sec_token'];
  577. $form = new FormValidator('subscribe_user_with_password', 'post', $action);
  578. $form->addElement('hidden', 'sec_token', $_SESSION['sec_token']);
  579. $form->addElement('hidden', 'subscribe_user_with_password', $all_course_information['code']);
  580. $form->addElement('text', 'course_registration_code');
  581. $form->addButton('submit', get_lang('SubmitRegistrationCode'));
  582. $content = $form->returnForm();
  583. return array('message' => $message, 'content' => $content);
  584. }
  585. }
  586. /**
  587. * List the sessions
  588. * @param string $date (optional) The date of sessions
  589. * @param array $limit
  590. * @return array The session list
  591. */
  592. public function browseSessions($date = null, $limit = array())
  593. {
  594. $em = Database::getManager();
  595. $qb = $em->createQueryBuilder();
  596. $_sessions = $qb->select('s')
  597. ->from('ChamiloCoreBundle:Session', 's');
  598. if (!empty($limit)) {
  599. $_sessions->setFirstResult($limit['start'])
  600. ->setMaxResults($limit['length']);
  601. }
  602. $_sessions->where(
  603. $qb->expr()->gt('s.nbrCourses', 0)
  604. );
  605. if (!is_null($date)) {
  606. $_sessions
  607. ->andWhere(
  608. $qb->expr()->orX(
  609. $qb->expr()->between(':date', 's.accessStartDate', 's.accessEndDate'),
  610. $qb->expr()->isNull('s.accessEndDate'),
  611. $qb->expr()->andX(
  612. $qb->expr()->isNull('s.accessStartDate'),
  613. $qb->expr()->isNotNull('s.accessEndDate'),
  614. $qb->expr()->gt('s.accessEndDate', ':date')
  615. )
  616. )
  617. )
  618. ->setParameter('date', $date);
  619. }
  620. return $_sessions->getQuery()->getResult();
  621. }
  622. /**
  623. * Return a COUNT from Session table
  624. * @param string $date in Y-m-d format
  625. * @return int
  626. */
  627. function countSessions($date = null)
  628. {
  629. $count = 0;
  630. $sessionTable = Database::get_main_table(TABLE_MAIN_SESSION);
  631. $date = Database::escape_string($date);
  632. $dateFilter = '';
  633. if (!empty($date)) {
  634. $dateFilter = <<<SQL
  635. AND ('$date' BETWEEN s.access_start_date AND s.access_end_date)
  636. OR (s.access_end_date IS NULL)
  637. OR (s.access_start_date IS NULL AND
  638. s.access_end_date IS NOT NULL AND s.access_end_date > '$date')
  639. SQL;
  640. }
  641. $sql = "SELECT COUNT(*) FROM $sessionTable s WHERE 1 = 1 $dateFilter";
  642. $res = Database::query($sql);
  643. if ($res !== false && Database::num_rows($res) > 0) {
  644. $count = current(Database::fetch_row($res));
  645. }
  646. return $count;
  647. }
  648. /**
  649. * Search sessions by the tags in their courses
  650. * @param string $termTag Term for search in tags
  651. * @param array $limit Limit info
  652. * @return array The sessions
  653. */
  654. public function browseSessionsByTags($termTag, array $limit)
  655. {
  656. $em = Database::getManager();
  657. $qb = $em->createQueryBuilder();
  658. $sessions = $qb->select('s')
  659. ->distinct(true)
  660. ->from('ChamiloCoreBundle:Session', 's')
  661. ->innerJoin(
  662. 'ChamiloCoreBundle:SessionRelCourse',
  663. 'src',
  664. \Doctrine\ORM\Query\Expr\Join::WITH,
  665. 's.id = src.session'
  666. )
  667. ->innerJoin(
  668. 'ChamiloCoreBundle:ExtraFieldRelTag',
  669. 'frt',
  670. \Doctrine\ORM\Query\Expr\Join::WITH,
  671. 'src.course = frt.itemId'
  672. )
  673. ->innerJoin(
  674. 'ChamiloCoreBundle:Tag',
  675. 't',
  676. \Doctrine\ORM\Query\Expr\Join::WITH,
  677. 'frt.tagId = t.id'
  678. )
  679. ->innerJoin(
  680. 'ChamiloCoreBundle:ExtraField',
  681. 'f',
  682. \Doctrine\ORM\Query\Expr\Join::WITH,
  683. 'frt.fieldId = f.id'
  684. )
  685. ->where(
  686. $qb->expr()->like('t.tag', ":tag")
  687. )
  688. ->andWhere(
  689. $qb->expr()->eq('f.extraFieldType', Chamilo\CoreBundle\Entity\ExtraField::COURSE_FIELD_TYPE)
  690. )
  691. ->setFirstResult($limit['start'])
  692. ->setMaxResults($limit['length'])
  693. ->setParameter('tag', "$termTag%")
  694. ->getQuery()
  695. ->getResult();
  696. $sessionsToBrowse = [];
  697. foreach ($sessions as $session) {
  698. if ($session->getNbrCourses() === 0) {
  699. continue;
  700. }
  701. $sessionsToBrowse[] = $session;
  702. }
  703. return $sessionsToBrowse;
  704. }
  705. /**
  706. * Search sessions by searched term by session name
  707. * @param string $queryTerm Term for search
  708. * @param array $limit Limit info
  709. * @return array The sessions
  710. */
  711. public function browseSessionsBySearch($queryTerm, array $limit)
  712. {
  713. $sessionsToBrowse = [];
  714. $criteria = Doctrine\Common\Collections\Criteria::create()
  715. ->where(
  716. Doctrine\Common\Collections\Criteria::expr()->contains('name', $queryTerm)
  717. )
  718. ->setFirstResult($limit['start'])
  719. ->setMaxResults($limit['length']);
  720. $sessions = Database::getManager()
  721. ->getRepository('ChamiloCoreBundle:Session')
  722. ->matching($criteria);
  723. foreach ($sessions as $session) {
  724. if ($session->getNbrCourses() === 0) {
  725. continue;
  726. }
  727. $sessionsToBrowse[] = $session;
  728. }
  729. return $sessionsToBrowse;
  730. }
  731. }