subscribe_user.php 31 KB

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