subscribe_user.php 34 KB

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