auth.lib.php 32 KB

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