subscribe_user.php 35 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856
  1. <?php
  2. /* For licensing terms, see /license.txt*/
  3. use Chamilo\CoreBundle\Entity\ExtraField;
  4. use ExtraField as ExtraFieldModel;
  5. /**
  6. * This script allows teachers to subscribe existing users
  7. * to their course.
  8. *
  9. * @package chamilo.user
  10. */
  11. require_once __DIR__.'/../inc/global.inc.php';
  12. $current_course_tool = TOOL_USER;
  13. // the section (for the tabs)
  14. $this_section = SECTION_COURSES;
  15. // notice for unauthorized people.
  16. api_protect_course_script(true);
  17. if (api_get_setting('allow_user_course_subscription_by_course_admin') === 'false') {
  18. if (!api_is_platform_admin()) {
  19. api_not_allowed(true);
  20. }
  21. }
  22. // Access restriction
  23. if (!api_is_allowed_to_edit()) {
  24. api_not_allowed(true);
  25. }
  26. $tool_name = get_lang("SubscribeUserToCourse");
  27. $type = isset($_REQUEST['type']) ? intval($_REQUEST['type']) : STUDENT;
  28. $keyword = isset($_REQUEST['keyword']) ? Security::remove_XSS($_REQUEST['keyword']) : null;
  29. $courseInfo = api_get_course_info();
  30. if ($type == COURSEMANAGER) {
  31. $tool_name = get_lang("SubscribeUserToCourseAsTeacher");
  32. }
  33. //extra entries in breadcrumb
  34. $interbreadcrumb[] = [
  35. "url" => "user.php?".api_get_cidreq(),
  36. "name" => get_lang("ToolUser"),
  37. ];
  38. if ($keyword) {
  39. $interbreadcrumb[] = [
  40. "url" => "subscribe_user.php?type=".$type.'&'.api_get_cidreq(),
  41. "name" => $tool_name,
  42. ];
  43. $tool_name = get_lang('SearchResults');
  44. }
  45. $sessionId = api_get_session_id();
  46. $list_register_user = '';
  47. $list_not_register_user = '';
  48. if (isset($_REQUEST['register'])) {
  49. $userInfo = api_get_user_info($_REQUEST['user_id']);
  50. $message = $userInfo['complete_name'].' '.get_lang('AddedToCourse');
  51. if ($type === COURSEMANAGER) {
  52. if (!empty($sessionId)) {
  53. $result_simple_sub = SessionManager::set_coach_to_course_session(
  54. $_REQUEST['user_id'],
  55. $sessionId,
  56. $courseInfo['real_id']
  57. );
  58. Display::addFlash(Display::return_message($message));
  59. } else {
  60. $result_simple_sub = CourseManager:: subscribe_user(
  61. $_REQUEST['user_id'],
  62. $courseInfo['code'],
  63. COURSEMANAGER
  64. );
  65. Display::addFlash(Display::return_message($message));
  66. }
  67. } else {
  68. $result_simple_sub = CourseManager:: subscribe_user(
  69. $_REQUEST['user_id'],
  70. $courseInfo['code']
  71. );
  72. Display::addFlash(Display::return_message($message));
  73. }
  74. header('Location:'.api_get_path(WEB_CODE_PATH).'user/user.php?'.api_get_cidreq().'&type='.$type);
  75. exit;
  76. }
  77. if (isset($_POST['action'])) {
  78. switch ($_POST['action']) {
  79. case 'subscribe':
  80. if (is_array($_POST['user'])) {
  81. foreach ($_POST['user'] as $index => $user_id) {
  82. $userInfo = api_get_user_info($user_id);
  83. if ($type === COURSEMANAGER) {
  84. if (!empty($sessionId)) {
  85. $is_suscribe[] = SessionManager::set_coach_to_course_session(
  86. $user_id,
  87. $sessionId,
  88. $courseInfo['real_id']
  89. );
  90. } else {
  91. $is_suscribe[] = CourseManager::subscribe_user(
  92. $user_id,
  93. $courseInfo['code'],
  94. COURSEMANAGER
  95. );
  96. }
  97. } else {
  98. $is_suscribe[] = CourseManager::subscribe_user(
  99. $user_id,
  100. $courseInfo['code']
  101. );
  102. }
  103. $message = $userInfo['complete_name'].' '.get_lang('AddedToCourse');
  104. Display::addFlash(Display::return_message($message));
  105. }
  106. }
  107. header('Location:'.api_get_path(WEB_CODE_PATH).'user/user.php?'.api_get_cidreq().'&type='.$type);
  108. exit;
  109. break;
  110. }
  111. }
  112. $is_western_name_order = api_is_western_name_order();
  113. $sort_by_first_name = api_sort_by_first_name();
  114. // Build table
  115. $table = new SortableTable(
  116. 'subscribe_users',
  117. 'get_number_of_users',
  118. 'get_user_data',
  119. ($is_western_name_order xor $sort_by_first_name) ? 3 : 2
  120. );
  121. $parameters['keyword'] = $keyword;
  122. $parameters['type'] = $type;
  123. $table->set_additional_parameters($parameters);
  124. $col = 0;
  125. $table->set_header($col++, '', false);
  126. $table->set_header($col++, get_lang('OfficialCode'));
  127. if (api_is_western_name_order()) {
  128. $table->set_header($col++, get_lang('FirstName'));
  129. $table->set_header($col++, get_lang('LastName'));
  130. } else {
  131. $table->set_header($col++, get_lang('LastName'));
  132. $table->set_header($col++, get_lang('FirstName'));
  133. }
  134. if (api_get_setting('show_email_addresses') == 'true') {
  135. $table->set_header($col++, get_lang('Email'));
  136. $table->set_column_filter($col - 1, 'email_filter');
  137. }
  138. $table->set_header($col++, get_lang('Active'), false);
  139. $table->set_column_filter($col - 1, 'active_filter');
  140. $table->set_header($col++, get_lang('Actions'), false);
  141. $table->set_column_filter($col - 1, 'reg_filter');
  142. $table->set_form_actions(['subscribe' => get_lang('reg')], 'user');
  143. if (!empty($_POST['keyword'])) {
  144. $keyword_name = Security::remove_XSS($_POST['keyword']);
  145. echo '<br/>'.get_lang('SearchResultsFor').' <span style="font-style: italic ;"> '.$keyword_name.' </span><br>';
  146. }
  147. Display :: display_header($tool_name, "User");
  148. // Build search-form
  149. switch ($type) {
  150. case STUDENT:
  151. $url = api_get_path(WEB_CODE_PATH).'user/user.php?'.api_get_cidreq().'';
  152. break;
  153. case COURSEMANAGER:
  154. $url = api_get_path(WEB_CODE_PATH).'user/user.php?'.api_get_cidreq().'&type='.COURSEMANAGER;
  155. break;
  156. }
  157. $actionsLeft = '';
  158. $actionsLeft = Display::url(
  159. Display::return_icon('back.png', get_lang('Back'), '', ICON_SIZE_MEDIUM),
  160. $url
  161. );
  162. if (isset($_GET['subscribe_user_filter_value']) && !empty($_GET['subscribe_user_filter_value'])) {
  163. $actionsLeft .= '<a href="subscribe_user.php?type='.$type.'">'.
  164. Display::return_icon('clean_group.gif').' '.get_lang('ClearFilterResults').'</a>';
  165. }
  166. $extraForm = '';
  167. if (api_get_setting('ProfilingFilterAddingUsers') === 'true') {
  168. $extraForm = display_extra_profile_fields_filter();
  169. }
  170. // Build search-form
  171. $form = new FormValidator(
  172. 'search_user',
  173. 'get',
  174. api_get_self().'?'.api_get_cidreq(),
  175. '',
  176. null,
  177. FormValidator::LAYOUT_INLINE
  178. );
  179. $form->addText('keyword', '', false);
  180. $form->addElement('hidden', 'type', $type);
  181. $form->addElement('hidden', 'cidReq', api_get_course_id());
  182. $form->addButtonSearch(get_lang('Search'));
  183. echo Display::toolbarAction('toolbar-subscriber', [$actionsLeft, $extraForm, $form->returnForm()], [4, 4, 4]);
  184. $option = $type == COURSEMANAGER ? 2 : 1;
  185. echo UserManager::getUserSubscriptionTab($option);
  186. // Display table
  187. $table->display();
  188. Display::display_footer();
  189. /* SHOW LIST OF USERS */
  190. /**
  191. ** Get the users to display on the current page.
  192. */
  193. function get_number_of_users()
  194. {
  195. // Database table definition
  196. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  197. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  198. $tbl_session_rel_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  199. $table_user_field_values = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  200. $courseCode = api_get_course_id();
  201. $sessionId = api_get_session_id();
  202. if (isset($_REQUEST['type']) && $_REQUEST['type'] === 'teacher') {
  203. if (api_get_session_id() != 0) {
  204. $sql = "SELECT COUNT(u.id)
  205. FROM $user_table u
  206. LEFT JOIN $tbl_session_rel_course_user cu
  207. ON
  208. u.user_id = cu.user_id AND
  209. c_id = '".api_get_course_int_id()."' AND
  210. session_id ='".$sessionId."'
  211. WHERE
  212. cu.user_id IS NULL AND
  213. u.status = 1 AND
  214. (u.official_code <> 'ADMIN' OR u.official_code IS NULL) ";
  215. if (api_is_multiple_url_enabled()) {
  216. $url_access_id = api_get_current_access_url_id();
  217. if ($url_access_id != -1) {
  218. $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  219. $sql = "SELECT COUNT(u.id)
  220. FROM $user_table u
  221. LEFT JOIN $tbl_session_rel_course_user cu
  222. ON
  223. u.user_id = cu.user_id AND cu.c_id = '".api_get_course_int_id()."' AND
  224. session_id ='".$sessionId."'
  225. INNER JOIN $tbl_url_rel_user as url_rel_user
  226. ON (url_rel_user.user_id = u.user_id)
  227. WHERE
  228. cu.user_id IS NULL AND
  229. access_url_id= $url_access_id AND
  230. u.status = 1 AND
  231. (u.official_code <> 'ADMIN' OR u.official_code IS NULL)
  232. ";
  233. }
  234. }
  235. } else {
  236. $sql = "SELECT COUNT(u.id)
  237. FROM $user_table u
  238. LEFT JOIN $course_user_table cu
  239. ON u.user_id = cu.user_id and c_id='".api_get_course_int_id()."'
  240. WHERE cu.user_id IS NULL AND u.status<>".DRH." ";
  241. if (api_is_multiple_url_enabled()) {
  242. $url_access_id = api_get_current_access_url_id();
  243. if ($url_access_id != -1) {
  244. $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  245. $sql = "SELECT COUNT(u.id)
  246. FROM $user_table u
  247. LEFT JOIN $course_user_table cu
  248. ON u.user_id = cu.user_id AND c_id='".api_get_course_int_id()."'
  249. INNER JOIN $tbl_url_rel_user as url_rel_user
  250. ON (url_rel_user.user_id = u.user_id)
  251. WHERE cu.user_id IS NULL AND u.status<>".DRH." AND access_url_id= $url_access_id ";
  252. }
  253. }
  254. }
  255. } else {
  256. // students
  257. if ($sessionId != 0) {
  258. $sql = "SELECT COUNT(u.id)
  259. FROM $user_table u
  260. LEFT JOIN $tbl_session_rel_course_user cu
  261. ON
  262. u.user_id = cu.user_id AND
  263. c_id='".api_get_course_int_id()."' AND
  264. session_id ='".$sessionId."'
  265. WHERE
  266. cu.user_id IS NULL AND
  267. u.status<>".DRH." AND
  268. (u.official_code <> 'ADMIN' OR u.official_code IS NULL) ";
  269. if (api_is_multiple_url_enabled()) {
  270. $url_access_id = api_get_current_access_url_id();
  271. if ($url_access_id != -1) {
  272. $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  273. $sql = "SELECT COUNT(u.id)
  274. FROM $user_table u
  275. LEFT JOIN $tbl_session_rel_course_user cu
  276. ON
  277. u.user_id = cu.user_id AND
  278. c_id='".api_get_course_int_id()."' AND
  279. session_id ='".$sessionId."'
  280. INNER JOIN $tbl_url_rel_user as url_rel_user
  281. ON (url_rel_user.user_id = u.id)
  282. WHERE
  283. cu.user_id IS NULL AND
  284. u.status<>".DRH." AND
  285. access_url_id= $url_access_id AND
  286. (u.official_code <> 'ADMIN' OR u.official_code IS NULL) ";
  287. }
  288. }
  289. } else {
  290. $sql = "SELECT COUNT(u.id)
  291. FROM $user_table u
  292. LEFT JOIN $course_user_table cu
  293. ON u.user_id = cu.user_id AND c_id='".api_get_course_int_id()."'";
  294. // we change the SQL when we have a filter
  295. if (isset($_GET['subscribe_user_filter_value']) &&
  296. !empty($_GET['subscribe_user_filter_value']) &&
  297. api_get_setting('ProfilingFilterAddingUsers') === 'true'
  298. ) {
  299. $field_identification = explode('*', $_GET['subscribe_user_filter_value']);
  300. $sql .= "
  301. LEFT JOIN $table_user_field_values field_values
  302. ON field_values.item_id = u.user_id
  303. WHERE
  304. cu.user_id IS NULL AND
  305. u.status <> ".DRH." AND
  306. field_values.field_id = '".intval($field_identification[0])."' AND
  307. field_values.value = '".Database::escape_string($field_identification[1])."'
  308. ";
  309. } else {
  310. $sql .= "WHERE cu.user_id IS NULL AND u.status <> ".DRH." ";
  311. }
  312. if (api_is_multiple_url_enabled()) {
  313. $url_access_id = api_get_current_access_url_id();
  314. if ($url_access_id != -1) {
  315. $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  316. $sql = "SELECT COUNT(u.id)
  317. FROM $user_table u
  318. LEFT JOIN $course_user_table cu
  319. ON u.user_id = cu.user_id AND c_id='".api_get_course_int_id()."'
  320. INNER JOIN $tbl_url_rel_user as url_rel_user
  321. ON (url_rel_user.user_id = u.id)
  322. WHERE cu.user_id IS NULL AND access_url_id= $url_access_id AND u.status <> ".DRH." ";
  323. }
  324. }
  325. }
  326. }
  327. // when there is a keyword then we are searching and we have to change the SQL statement
  328. if (isset($_GET['keyword']) && !empty($_GET['keyword'])) {
  329. $keyword = Database::escape_string(trim($_REQUEST['keyword']));
  330. $sql .= " AND (
  331. firstname LIKE '%".$keyword."%' OR
  332. lastname LIKE '%".$keyword."%' OR
  333. email LIKE '%".$keyword."%' OR
  334. username LIKE '%".$keyword."%' OR
  335. official_code LIKE '%".$keyword."%'
  336. )";
  337. // we also want to search for users who have something in their profile fields that matches the keyword
  338. if (api_get_setting('ProfilingFilterAddingUsers') === 'true') {
  339. $additional_users = search_additional_profile_fields($keyword);
  340. }
  341. // getting all the users of the course (to make sure that we do not display users that are already in the course)
  342. if (!empty($sessionId)) {
  343. $a_course_users = CourseManager:: get_user_list_from_course_code(
  344. $courseCode,
  345. $sessionId
  346. );
  347. } else {
  348. $a_course_users = CourseManager:: get_user_list_from_course_code(
  349. $courseCode,
  350. 0
  351. );
  352. }
  353. foreach ($a_course_users as $user_id => $course_user) {
  354. $users_of_course[] = $course_user['user_id'];
  355. }
  356. }
  357. $sql .= " AND u.status <> ".ANONYMOUS." ";
  358. $res = Database::query($sql);
  359. $count_user = 0;
  360. if ($res) {
  361. $row = Database::fetch_row($res);
  362. $count_user = $row[0];
  363. }
  364. return $count_user;
  365. }
  366. /**
  367. * Get the users to display on the current page.
  368. */
  369. function get_user_data($from, $number_of_items, $column, $direction)
  370. {
  371. $url_access_id = api_get_current_access_url_id();
  372. $course_code = api_get_course_id();
  373. $sessionId = api_get_session_id();
  374. $courseId = api_get_course_int_id();
  375. // Database table definitions
  376. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  377. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  378. $tbl_session_rel_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  379. $table_user_field_values = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  380. $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  381. // adding teachers
  382. $is_western_name_order = api_is_western_name_order();
  383. if (api_get_setting('show_email_addresses') === 'true') {
  384. $select_fields = "u.id AS col0,
  385. u.official_code AS col1,
  386. ".($is_western_name_order
  387. ? "u.firstname AS col2,
  388. u.lastname AS col3,"
  389. : "u.lastname AS col2,
  390. u.firstname AS col3,")."
  391. u.email AS col4,
  392. u.active AS col5,
  393. u.user_id AS col6";
  394. } else {
  395. $select_fields = "u.user_id AS col0,
  396. u.official_code AS col1,
  397. ".($is_western_name_order
  398. ? "u.firstname AS col2,
  399. u.lastname AS col3,"
  400. : "u.lastname AS col2,
  401. u.firstname AS col3,")."
  402. u.active AS col4,
  403. u.user_id AS col5";
  404. }
  405. if (isset($_REQUEST['type']) && $_REQUEST['type'] == COURSEMANAGER) {
  406. // adding a teacher through a session
  407. if (!empty($sessionId)) {
  408. $sql = "SELECT $select_fields
  409. FROM $user_table u
  410. LEFT JOIN $tbl_session_rel_course_user cu
  411. ON
  412. u.user_id = cu.user_id AND
  413. c_id ='".$courseId."' AND
  414. session_id ='".$sessionId."'
  415. INNER JOIN $tbl_url_rel_user as url_rel_user
  416. ON (url_rel_user.user_id = u.user_id) ";
  417. // applying the filter of the additional user profile fields
  418. if (isset($_GET['subscribe_user_filter_value']) &&
  419. !empty($_GET['subscribe_user_filter_value']) &&
  420. api_get_setting('ProfilingFilterAddingUsers') == 'true'
  421. ) {
  422. $field_identification = explode('*', $_GET['subscribe_user_filter_value']);
  423. $sql .= "
  424. LEFT JOIN $table_user_field_values field_values
  425. ON field_values.item_id = u.user_id
  426. WHERE
  427. cu.user_id IS NULL AND
  428. u.status = 1 AND
  429. (u.official_code <> 'ADMIN' OR u.official_code IS NULL) AND
  430. field_values.field_id = '".intval($field_identification[0])."' AND
  431. field_values.value = '".Database::escape_string($field_identification[1])."'";
  432. } else {
  433. $sql .= "WHERE cu.user_id IS NULL AND u.status=1 AND (u.official_code <> 'ADMIN' OR u.official_code IS NULL) ";
  434. }
  435. $sql .= " AND access_url_id = $url_access_id";
  436. } else {
  437. // adding a teacher NOT through a session
  438. $sql = "SELECT $select_fields
  439. FROM $user_table u
  440. LEFT JOIN $course_user_table cu
  441. ON u.user_id = cu.user_id AND c_id = '".$courseId."'";
  442. // applying the filter of the additional user profile fields
  443. if (isset($_GET['subscribe_user_filter_value']) &&
  444. !empty($_GET['subscribe_user_filter_value']) &&
  445. api_get_setting('ProfilingFilterAddingUsers') == 'true'
  446. ) {
  447. $field_identification = explode('*', $_GET['subscribe_user_filter_value']);
  448. $sql .= "
  449. LEFT JOIN $table_user_field_values field_values
  450. ON field_values.item_id = u.user_id
  451. WHERE
  452. cu.user_id IS NULL AND u.status<>".DRH." AND
  453. field_values.field_id = '".intval($field_identification[0])."' AND
  454. field_values.value = '".Database::escape_string($field_identification[1])."'";
  455. } else {
  456. $sql .= "WHERE cu.user_id IS NULL AND u.status<>".DRH." ";
  457. }
  458. // adding a teacher NOT trough a session on a portal with multiple URLs
  459. if (api_is_multiple_url_enabled()) {
  460. if ($url_access_id != -1) {
  461. $sql = "SELECT $select_fields
  462. FROM $user_table u
  463. LEFT JOIN $course_user_table cu
  464. ON u.user_id = cu.user_id and c_id='".$courseId."'
  465. INNER JOIN $tbl_url_rel_user as url_rel_user
  466. ON (url_rel_user.user_id = u.user_id) ";
  467. // applying the filter of the additional user profile fields
  468. if (isset($_GET['subscribe_user_filter_value']) &&
  469. !empty($_GET['subscribe_user_filter_value']) &&
  470. api_get_setting('ProfilingFilterAddingUsers') == 'true'
  471. ) {
  472. $field_identification = explode('*', $_GET['subscribe_user_filter_value']);
  473. $sql .= "
  474. LEFT JOIN $table_user_field_values field_values
  475. ON field_values.item_id = u.user_id
  476. WHERE
  477. cu.user_id IS NULL AND
  478. u.status<>".DRH." AND
  479. field_values.field_id = '".intval($field_identification[0])."' AND
  480. field_values.value = '".Database::escape_string($field_identification[1])."'";
  481. } else {
  482. $sql .= "WHERE cu.user_id IS NULL AND u.status<>".DRH." AND access_url_id= $url_access_id ";
  483. }
  484. }
  485. }
  486. }
  487. } else {
  488. // adding a student
  489. if (!empty($sessionId)) {
  490. $sql = "SELECT $select_fields
  491. FROM $user_table u
  492. LEFT JOIN $tbl_session_rel_course_user cu
  493. ON
  494. u.user_id = cu.user_id AND
  495. c_id ='".$courseId."' AND
  496. session_id ='".$sessionId."' ";
  497. if (api_is_multiple_url_enabled()) {
  498. $sql .= " INNER JOIN $tbl_url_rel_user as url_rel_user ON (url_rel_user.user_id = u.user_id) ";
  499. }
  500. // applying the filter of the additional user profile fields
  501. if (isset($_GET['subscribe_user_filter_value']) &&
  502. !empty($_GET['subscribe_user_filter_value'])
  503. ) {
  504. $field_identification = explode('*', $_GET['subscribe_user_filter_value']);
  505. $sql .= "
  506. LEFT JOIN $table_user_field_values field_values
  507. ON field_values.item_id = u.user_id
  508. WHERE
  509. cu.user_id IS NULL AND
  510. u.status<>".DRH." AND
  511. (u.official_code <> 'ADMIN' OR u.official_code IS NULL) AND
  512. field_values.field_id = '".intval($field_identification[0])."' AND
  513. field_values.value = '".Database::escape_string($field_identification[1])."'";
  514. } else {
  515. $sql .= "WHERE
  516. cu.user_id IS NULL AND
  517. u.status<>".DRH." AND
  518. (u.official_code <> 'ADMIN' OR u.official_code IS NULL) ";
  519. }
  520. if (api_is_multiple_url_enabled()) {
  521. $sql .= "AND access_url_id = $url_access_id";
  522. }
  523. } else {
  524. $sql = "SELECT $select_fields
  525. FROM $user_table u
  526. LEFT JOIN $course_user_table cu
  527. ON
  528. u.user_id = cu.user_id AND
  529. c_id ='".$courseId."'";
  530. // applying the filter of the additional user profile fields
  531. if (isset($_GET['subscribe_user_filter_value']) && !empty($_GET['subscribe_user_filter_value'])) {
  532. $field_identification = explode('*', $_GET['subscribe_user_filter_value']);
  533. $sql .= "
  534. LEFT JOIN $table_user_field_values field_values
  535. ON field_values.item_id = u.user_id
  536. WHERE
  537. cu.user_id IS NULL AND
  538. u.status<>".DRH." AND
  539. field_values.field_id = '".intval($field_identification[0])."' AND
  540. field_values.value = '".Database::escape_string($field_identification[1])."'";
  541. } else {
  542. $sql .= "WHERE cu.user_id IS NULL AND u.status<>".DRH." ";
  543. }
  544. //showing only the courses of the current Chamilo access_url_id
  545. if (api_is_multiple_url_enabled()) {
  546. if ($url_access_id != -1) {
  547. $sql = "SELECT $select_fields
  548. FROM $user_table u
  549. LEFT JOIN $course_user_table cu
  550. ON u.user_id = cu.user_id AND c_id='".$courseId."'
  551. INNER JOIN $tbl_url_rel_user as url_rel_user
  552. ON (url_rel_user.user_id = u.user_id) ";
  553. // applying the filter of the additional user profile fields
  554. if (isset($_GET['subscribe_user_filter_value']) &&
  555. !empty($_GET['subscribe_user_filter_value']) &&
  556. api_get_setting('ProfilingFilterAddingUsers') == 'true'
  557. ) {
  558. $field_identification = explode('*', $_GET['subscribe_user_filter_value']);
  559. $sql .= "
  560. LEFT JOIN $table_user_field_values field_values
  561. ON field_values.item_id = u.user_id
  562. WHERE
  563. cu.user_id IS NULL AND
  564. u.status<>".DRH." AND
  565. field_values.field_id = '".intval($field_identification[0])."' AND
  566. field_values.value = '".Database::escape_string($field_identification[1])."' AND
  567. access_url_id = $url_access_id
  568. ";
  569. } else {
  570. $sql .= "WHERE cu.user_id IS NULL AND u.status<>".DRH." AND access_url_id= $url_access_id ";
  571. }
  572. }
  573. }
  574. }
  575. }
  576. // adding additional WHERE statements to the SQL for the search functionality
  577. if (isset($_REQUEST['keyword'])) {
  578. $keyword = Database::escape_string(trim($_REQUEST['keyword']));
  579. $sql .= " AND (
  580. firstname LIKE '%".$keyword."%' OR
  581. lastname LIKE '%".$keyword."%' OR
  582. email LIKE '%".$keyword."%' OR
  583. username LIKE '%".$keyword."%' OR
  584. official_code LIKE '%".$keyword."%'
  585. )
  586. ";
  587. if (api_get_setting('ProfilingFilterAddingUsers') === 'true') {
  588. // we also want to search for users who have something in
  589. // their profile fields that matches the keyword
  590. $additional_users = search_additional_profile_fields($keyword);
  591. }
  592. // getting all the users of the course (to make sure that we do not
  593. // display users that are already in the course)
  594. if (!empty($sessionId)) {
  595. $a_course_users = CourseManager :: get_user_list_from_course_code($course_code, $sessionId);
  596. } else {
  597. $a_course_users = CourseManager :: get_user_list_from_course_code($course_code, 0);
  598. }
  599. foreach ($a_course_users as $user_id => $course_user) {
  600. $users_of_course[] = $course_user['user_id'];
  601. }
  602. }
  603. $sql .= " AND u.status != ".ANONYMOUS." ";
  604. // Sorting and pagination (used by the sortable table)
  605. $sql .= " ORDER BY col$column $direction ";
  606. $from = (int) $from;
  607. $number_of_items = (int) $number_of_items;
  608. $sql .= " LIMIT $from, $number_of_items";
  609. $res = Database::query($sql);
  610. $users = [];
  611. while ($user = Database::fetch_row($res)) {
  612. $users[] = $user;
  613. }
  614. return $users;
  615. }
  616. /**
  617. * Returns a mailto-link.
  618. *
  619. * @param string $email An email-address
  620. *
  621. * @return string HTML-code with a mailto-link
  622. */
  623. function email_filter($email)
  624. {
  625. return Display :: encrypted_mailto_link($email, $email);
  626. }
  627. /**
  628. * Build the reg-column of the table.
  629. *
  630. * @param int $user_id The user id
  631. *
  632. * @return string Some HTML-code
  633. */
  634. function reg_filter($user_id)
  635. {
  636. if (isset($_REQUEST['type']) && $_REQUEST['type'] == COURSEMANAGER) {
  637. $type = COURSEMANAGER;
  638. } else {
  639. $type = STUDENT;
  640. }
  641. $user_id = (int) $user_id;
  642. $result = '<a class="btn btn-small btn-primary" href="'.api_get_self().'?'.api_get_cidreq().'&register=yes&type='.$type.'&user_id='.$user_id.'">'.
  643. get_lang("reg").'</a>';
  644. return $result;
  645. }
  646. /**
  647. * Build the active-column of the table to lock or unlock a certain user
  648. * lock = the user can no longer use this account.
  649. *
  650. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  651. *
  652. * @param int $active the current state of the account
  653. * @param string $url_params
  654. *
  655. * @return string Some HTML-code with the lock/unlock button
  656. */
  657. function active_filter($active, $url_params, $row)
  658. {
  659. $_user = api_get_user_info();
  660. if ($active == '1') {
  661. $action = 'AccountActive';
  662. $image = 'accept';
  663. }
  664. if ($active == '0') {
  665. $action = 'AccountInactive';
  666. $image = 'error';
  667. }
  668. $result = '';
  669. if ($row['0'] != $_user['user_id']) {
  670. // you cannot lock yourself out otherwise you could disable all the accounts
  671. // including your own => everybody is locked out and nobody can change it anymore.
  672. $result = Display::return_icon(
  673. $image.'.png',
  674. get_lang(ucfirst($action)),
  675. [],
  676. ICON_SIZE_TINY
  677. );
  678. }
  679. return $result;
  680. }
  681. /**
  682. * Search the additional user profile fields defined by the platform administrator in
  683. * platform administration > profiling for a given keyword.
  684. * We not only search in the predefined options but also in the input fields wherer
  685. * the user can enter some text.
  686. *
  687. * For this we get the additional profile field options that match the (search) keyword,
  688. * then we find all the users who have entered the (search)keyword in a input field of the
  689. * additional profile fields or have chosen one of the matching predefined options
  690. *
  691. * @param string $keyword a keyword we are looking for in the additional profile fields
  692. *
  693. * @return array $additional_users an array with the users who have an additional profile field that matches the keyword
  694. */
  695. function search_additional_profile_fields($keyword)
  696. {
  697. // database table definitions
  698. $table_user_field_options = Database::get_main_table(TABLE_EXTRA_FIELD_OPTIONS);
  699. $table_user_field_values = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  700. $tableExtraField = Database::get_main_table(TABLE_EXTRA_FIELD);
  701. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  702. $keyword = Database::escape_string($keyword);
  703. // getting the field option text that match this keyword (for radio buttons and checkboxes)
  704. $sql = "SELECT * FROM $table_user_field_options
  705. WHERE display_text LIKE '%".$keyword."%'";
  706. $result_profiling = Database::query($sql);
  707. while ($profiling_field_options = Database::fetch_array($result_profiling)) {
  708. $profiling_field_options_exact_values[] = $profiling_field_options;
  709. }
  710. $profiling_field_options_exact_values_sql = '';
  711. foreach ($profiling_field_options_exact_values as $profilingkey => $profilingvalue) {
  712. $profiling_field_options_exact_values_sql .= " OR (field_id = '".$profilingvalue['field_id']."' AND value='".$profilingvalue['option_value']."') ";
  713. }
  714. $extraFieldType = ExtraField::USER_FIELD_TYPE;
  715. // getting all the user ids of the users who have chosen on of the predefined fields that contain the keyword
  716. // or all the users who have entered the keyword in a free-form field
  717. $sql = "SELECT
  718. user.user_id as col0,
  719. user.official_code as col1,
  720. user.lastname as col2,
  721. user.firstname as col3,
  722. user.email as col4,
  723. user.active as col5,
  724. user.user_id as col6
  725. FROM $table_user user, $table_user_field_values user_values, $tableExtraField e
  726. WHERE
  727. user.user_id = user_values.item_id AND
  728. user_values.field_id = e.id AND
  729. e.extra_field_type = $extraFieldType AND
  730. (value LIKE '%".$keyword."%'".$profiling_field_options_exact_values_sql.")";
  731. $result = Database::query($sql);
  732. $additional_users = [];
  733. while ($profiled_users = Database::fetch_array($result)) {
  734. $additional_users[$profiled_users['col0']] = $profiled_users;
  735. }
  736. return $additional_users;
  737. }
  738. /**
  739. * This function displays a dropdown list with all the additional user
  740. * profile fields defined by the platform administrator in
  741. * platform administration > profiling.
  742. * Only the fields that have predefined fields are usefull for such a filter.
  743. */
  744. function display_extra_profile_fields_filter()
  745. {
  746. // getting all the additional user profile fields
  747. $extra = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  748. $return = '<option value="">'.get_lang('SelectFilter').'</option>';
  749. // looping through the additional user profile fields
  750. foreach ($extra as $id => $field_details) {
  751. // $field_details[2] contains the type of the additional user profile field
  752. switch ($field_details[2]) {
  753. // text fields cannot be used as a filter
  754. case ExtraFieldModel::FIELD_TYPE_TEXT:
  755. break;
  756. // text area fields cannot be used as a filter
  757. case ExtraFieldModel::FIELD_TYPE_TEXTAREA:
  758. break;
  759. case ExtraFieldModel::FIELD_TYPE_RADIO:
  760. case ExtraFieldModel::FIELD_TYPE_SELECT:
  761. case ExtraFieldModel::FIELD_TYPE_SELECT_MULTIPLE:
  762. $return .= '<optgroup label="'.$field_details[3].'">';
  763. foreach ($field_details[9] as $option_id => $option_details) {
  764. if (isset($_GET['subscribe_user_filter_value']) &&
  765. $_GET['subscribe_user_filter_value'] == $field_details[0].'*'.$option_details[1]
  766. ) {
  767. $selected = 'selected="selected"';
  768. } else {
  769. $selected = false;
  770. }
  771. $return .= '<option value="'.$field_details[0].'*'.$option_details[1].'" '.$selected.'>'.$option_details[2].'</option>';
  772. }
  773. $return .= '</optgroup>';
  774. break;
  775. }
  776. }
  777. $html = '<form class="form-inline" id="subscribe_user_filter" name="subscribe_user_filter" method="get" action="'.api_get_self().'?'.api_get_cidreq().'">';
  778. $html .= '<input type="hidden" name="type" id="type" value="'.Security::remove_XSS($_REQUEST['type']).'" />';
  779. $html .= '<select name="subscribe_user_filter_value" id="subscribe_user_filter_value">'.$return.'</select>';
  780. $html .= '<button type="submit" name="submit_filter" id="submit_filter" value="" class="search">'.get_lang('Filter').'</button>';
  781. $html .= '</form>';
  782. return $html;
  783. }