auth.lib.php 32 KB

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