index.php 48 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232
  1. <?php // $Id: index.php 16620 2008-10-25 20:03:54Z yannoo $
  2. /* For licensing terms, see /chamilo_license.txt */
  3. /**
  4. * @todo use constant for $this_section
  5. */
  6. // name of the language file that needs to be included
  7. $language_file = array ('registration', 'index', 'tracking');
  8. // resetting the course id
  9. $cidReset = true;
  10. // including the global Dokeos file
  11. require_once '../inc/global.inc.php';
  12. // including additional libraries
  13. require_once api_get_path(LIBRARY_PATH).'tracking.lib.php';
  14. require_once api_get_path(LIBRARY_PATH).'course.lib.php';
  15. require_once api_get_path(LIBRARY_PATH).'usermanager.lib.php';
  16. require_once api_get_path(LIBRARY_PATH).'export.lib.inc.php';
  17. // the section (for the tabs)
  18. $this_section = 'session_my_space';
  19. ob_start();
  20. $export_csv = isset($_GET['export']) && $_GET['export'] == 'csv' ? true : false;
  21. $csv_content = array();
  22. $nameTools = get_lang('MySpace');
  23. // access control
  24. api_block_anonymous_users();
  25. if (!$export_csv) {
  26. Display :: display_header($nameTools);
  27. } else {
  28. if ($_GET['view'] == 'admin' AND $_GET['display'] == 'useroverview') {
  29. export_tracking_user_overview();
  30. exit;
  31. }
  32. }
  33. // Database table definitions
  34. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  35. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  36. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  37. $tbl_class = Database :: get_main_table(TABLE_MAIN_CLASS);
  38. $tbl_sessions = Database :: get_main_table(TABLE_MAIN_SESSION);
  39. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  40. $tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  41. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  42. $tbl_admin = Database :: get_main_table(TABLE_MAIN_ADMIN);
  43. $tbl_track_cours_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  44. /********************
  45. * FUNCTIONS
  46. ********************/
  47. function count_teacher_courses() {
  48. global $nb_teacher_courses;
  49. return $nb_teacher_courses;
  50. }
  51. function count_coaches() {
  52. global $total_no_coaches;
  53. return $total_no_coaches;
  54. }
  55. function sort_users($a, $b) {
  56. return api_strcmp(trim(api_strtolower($a[$_SESSION['tracking_column']])), trim(api_strtolower($b[$_SESSION['tracking_column']])));
  57. }
  58. function rsort_users($a, $b) {
  59. return api_strcmp(trim(api_strtolower($b[$_SESSION['tracking_column']])), trim(api_strtolower($a[$_SESSION['tracking_column']])));
  60. }
  61. /**************************
  62. * MAIN CODE
  63. ***************************/
  64. $is_coach = api_is_coach();
  65. $is_platform_admin = api_is_platform_admin();
  66. $view = isset($_GET['view']) ? $_GET['view'] : 'teacher';
  67. $menu_items = array();
  68. global $_configuration;
  69. if (api_is_allowed_to_create_course()) {
  70. $sql_nb_cours = "SELECT course_rel_user.course_code, course.title
  71. FROM $tbl_course_user as course_rel_user
  72. INNER JOIN $tbl_course as course
  73. ON course.code = course_rel_user.course_code
  74. WHERE course_rel_user.user_id='".$_user['user_id']."' AND course_rel_user.status='1'
  75. ORDER BY course.title";
  76. if ($_configuration['multiple_access_urls'] == true) {
  77. $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  78. $access_url_id = api_get_current_access_url_id();
  79. if ($access_url_id != -1) {
  80. $sql_nb_cours = " SELECT course_rel_user.course_code, course.title
  81. FROM $tbl_course_user as course_rel_user
  82. INNER JOIN $tbl_course as course
  83. ON course.code = course_rel_user.course_code
  84. INNER JOIN $tbl_course_rel_access_url course_rel_url
  85. ON (course_rel_url.course_code= course.code)
  86. WHERE access_url_id = $access_url_id AND course_rel_user.user_id='".$_user['user_id']."' AND course_rel_user.status='1'
  87. ORDER BY course.title";
  88. }
  89. }
  90. $result_nb_cours = Database::query($sql_nb_cours);
  91. $courses = Database::store_result($result_nb_cours);
  92. $nb_teacher_courses = count($courses);
  93. if ($nb_teacher_courses) {
  94. if (!$is_coach && !$is_platform_admin) {
  95. $view = 'teacher';
  96. }
  97. if ($view == 'teacher') {
  98. $menu_items[] = get_lang('TeacherInterface');
  99. $title = get_lang('YourCourseList');
  100. } else {
  101. $menu_items[] = '<a href="'.api_get_self().'?view=teacher">'.get_lang('TeacherInterface').'</a>';
  102. }
  103. }
  104. }
  105. if ($is_coach) {
  106. if ($nb_teacher_courses == 0 && !$is_platform_admin) {
  107. $view = 'coach';
  108. }
  109. if ($view == 'coach') {
  110. $menu_items[] = get_lang('CoachInterface');
  111. $title = get_lang('YourStatistics');
  112. } else {
  113. $menu_items[] = '<a href="'.api_get_self().'?view=coach">'.get_lang('CoachInterface').'</a>';
  114. }
  115. }
  116. if ($is_platform_admin) {
  117. if (!$is_coach && $nb_teacher_courses == 0) {
  118. $view = 'admin';
  119. }
  120. if ($view == 'admin') {
  121. $menu_items[] = get_lang('AdminInterface');
  122. $title = get_lang('CoachList');
  123. } else {
  124. $menu_items[] = '<a href="'.api_get_self().'?view=admin">'.get_lang('AdminInterface').'</a>';
  125. }
  126. }
  127. if (api_is_drh()) {
  128. $view = 'drh_students';
  129. $menu_items[] = get_lang('Learners');
  130. $menu_items[] = '<a href="teachers.php">'.get_lang('Trainers').'</a>';
  131. $menu_items[] = '<a href="course.php">'.get_lang('Trainings').'</a>';
  132. $menu_items[] = '<a href="session.php">'.get_lang('Sessions').'</a>';
  133. }
  134. echo '<div class="actions-title" style ="font-size:10pt;">';
  135. $nb_menu_items = count($menu_items);
  136. if ($nb_menu_items > 1) {
  137. foreach ($menu_items as $key => $item) {
  138. echo $item;
  139. if ($key != $nb_menu_items - 1) {
  140. echo '&nbsp;|&nbsp;';
  141. }
  142. }
  143. }
  144. echo '&nbsp;&nbsp;<a href="javascript: void(0);" onclick="javascript: window.print()"><img align="absbottom" src="../img/printmgr.gif">&nbsp;'.get_lang('Print').'</a> ';
  145. if ($view == 'admin') {
  146. echo (isset($_GET['display']) && $_GET['display'] == 'useroverview')? '<a href="'.api_get_self().'?display=useroverview&export=csv&view='.$view.'"><img align="absbottom" src="../img/csv.gif">&nbsp;'.get_lang('ExportAsCSV').'</a>' : '';
  147. } else {
  148. echo (isset($_GET['display']) && $_GET['display'] == 'useroverview')? '' : '<a href="'.api_get_self().'?export=csv&view='.$view.'"><img align="absbottom" src="../img/csv.gif">&nbsp;'.get_lang('ExportAsCSV').'</a>';
  149. }
  150. echo '</div>';
  151. echo '<h4>'.$title.'</h4>';
  152. if (api_is_drh() && $view == 'drh_students') {
  153. // get data for human resources manager
  154. $students = array_keys(UserManager::get_users_followed_by_drh($_user['user_id'], STUDENT));
  155. $courses_of_the_platform = CourseManager :: get_real_course_list();
  156. foreach ($courses_of_the_platform as $course) {
  157. $courses[$course['code']] = $course['code'];
  158. }
  159. }
  160. if ($is_coach && $view == 'coach') {
  161. $students = Tracking :: get_student_followed_by_coach($_user['user_id']);
  162. $courses = Tracking :: get_courses_followed_by_coach($_user['user_id']);
  163. }
  164. if ($view == 'coach' || $view == 'drh_students') {
  165. $nb_students = count($students);
  166. $total_time_spent = 0;
  167. $total_courses = 0;
  168. $avg_total_progress = 0;
  169. $avg_results_to_exercises = 0;
  170. $nb_inactive_students = 0;
  171. $nb_posts = $nb_assignments = 0;
  172. foreach ($students as $student_id) {
  173. // inactive students
  174. $last_connection_date = Tracking :: get_last_connection_date($student_id, true, true);
  175. if ($last_connection_date != false) {
  176. /*
  177. list($last_connection_date, $last_connection_hour) = explode(' ', $last_connection_date);
  178. $last_connection_date = explode('-', $last_connection_date);
  179. $last_connection_hour = explode(':', $last_connection_hour);
  180. $last_connection_hour[0];
  181. $last_connection_time = mktime($last_connection_hour[0], $last_connection_hour[1], $last_connection_hour[2], $last_connection_date[1], $last_connection_date[2], $last_connection_date[0]);
  182. */
  183. if (time() - (3600 * 24 * 7) > $last_connection_time) {
  184. $nb_inactive_students++;
  185. }
  186. } else {
  187. $nb_inactive_students++;
  188. }
  189. $total_time_spent += Tracking :: get_time_spent_on_the_platform($student_id);
  190. $total_courses += Tracking :: count_course_per_student($student_id);
  191. $avg_student_progress = $avg_student_score = 0;
  192. $nb_courses_student = 0;
  193. foreach ($courses as $course_code) {
  194. if (CourseManager :: is_user_subscribed_in_course($student_id, $course_code, true)) {
  195. $nb_courses_student++;
  196. $nb_posts += Tracking :: count_student_messages($student_id, $course_code);
  197. $nb_assignments += Tracking :: count_student_assignments($student_id, $course_code);
  198. $avg_student_progress += Tracking :: get_avg_student_progress($student_id, $course_code);
  199. $avg_student_score += Tracking :: get_avg_student_score($student_id, $course_code);
  200. if ($nb_posts !== null && $nb_assignments !== null && $avg_student_progress !== null && $avg_student_score !== null) {
  201. //if one of these scores is null, it means that we had a problem connecting to the right database, so don't count it in
  202. $nb_courses_student++;
  203. }
  204. }
  205. }
  206. // average progress of the student
  207. $avg_student_progress = $nb_courses_student ?$avg_student_progress / $nb_courses_student:0;
  208. $avg_total_progress += $avg_student_progress;
  209. // average test results of the student
  210. $avg_student_score = $avg_student_score?$avg_student_score / $nb_courses_student:0;
  211. $avg_results_to_exercises += $avg_student_score;
  212. }
  213. if ($nb_students > 0) {
  214. // average progress
  215. $avg_total_progress = $avg_total_progress / $nb_students;
  216. // average results to the tests
  217. $avg_results_to_exercises = $avg_results_to_exercises / $nb_students;
  218. // average courses by student
  219. $avg_courses_per_student = round($total_courses / $nb_students, 2);
  220. // average time spent on the platform
  221. $avg_time_spent = $total_time_spent / $nb_students;
  222. // average assignments
  223. $nb_assignments = $nb_assignments / $nb_students;
  224. // average posts
  225. $nb_posts = $nb_posts / $nb_students;
  226. } else {
  227. $avg_total_progress = null;
  228. $avg_results_to_exercises = null;
  229. $avg_courses_per_student = null;
  230. $avg_time_spent = null;
  231. $nb_assignments = null;
  232. $nb_posts = null;
  233. }
  234. if ($export_csv) {
  235. //csv part
  236. $csv_content[] = array(get_lang('Probationers', ''));
  237. $csv_content[] = array(get_lang('InactivesStudents', ''), $nb_inactive_students );
  238. $csv_content[] = array(get_lang('AverageTimeSpentOnThePlatform', ''), $avg_time_spent);
  239. $csv_content[] = array(get_lang('AverageCoursePerStudent', ''), $avg_courses_per_student);
  240. $csv_content[] = array(get_lang('AverageProgressInLearnpath', ''), is_null($avg_total_progress) ? null : round($avg_total_progress, 2).'%');
  241. $csv_content[] = array(get_lang('AverageResultsToTheExercices', ''), is_null($avg_results_to_exercises) ? null : round($avg_results_to_exercises, 2).'%');
  242. $csv_content[] = array(get_lang('AveragePostsInForum', ''), $nb_posts);
  243. $csv_content[] = array(get_lang('AverageAssignments', ''), $nb_assignments);
  244. $csv_content[] = array();
  245. } else {
  246. // html part
  247. echo '
  248. <div class="report_section">
  249. <h4>
  250. <a href="student.php"><img src="'.api_get_path(WEB_IMG_PATH).'students.gif">&nbsp;'.get_lang('Probationers').' ('.$nb_students.')'.'</a>
  251. </h4>
  252. <table class="data_table">
  253. <tr>
  254. <td>
  255. '.get_lang('InactivesStudents').'
  256. </td>
  257. <td align="right">
  258. '.$nb_inactive_students.'
  259. </td>
  260. </tr>
  261. <tr>
  262. <td>
  263. '.get_lang('AverageTimeSpentOnThePlatform').'
  264. </td>
  265. <td align="right">
  266. '.(is_null($avg_time_spent) ? '' : api_time_to_hms($avg_time_spent)).'
  267. </td>
  268. </tr>
  269. <tr>
  270. <td>
  271. '.get_lang('AverageCoursePerStudent').'
  272. </td>
  273. <td align="right">
  274. '.(is_null($avg_courses_per_student) ? '' : $avg_courses_per_student).'
  275. </td>
  276. </tr>
  277. <tr>
  278. <td>
  279. '.get_lang('AverageProgressInLearnpath').'
  280. </td>
  281. <td align="right">
  282. '.(is_null($avg_total_progress) ? '' : round($avg_total_progress, 2).'%').'
  283. </td>
  284. </tr>
  285. <tr>
  286. <td>
  287. '.get_lang('AverageResultsToTheExercices').'
  288. </td>
  289. <td align="right">
  290. '.(is_null($avg_results_to_exercises) ? '' : round($avg_results_to_exercises, 2).'%').'
  291. </td>
  292. </tr>
  293. <tr>
  294. <td>
  295. '.get_lang('AveragePostsInForum').'
  296. </td>
  297. <td align="right">
  298. '.(is_null($nb_posts) ? '' : round($nb_posts, 2)).'
  299. </td>
  300. </tr>
  301. <tr>
  302. <td>
  303. '.get_lang('AverageAssignments').'
  304. </td>
  305. <td align="right">
  306. '.(is_null($nb_assignments) ? '' : round($nb_assignments, 2)).'
  307. </td>
  308. </tr>
  309. </table><br />
  310. <a href="student.php">'.get_lang('SeeStudentList').'</a>
  311. </div>';
  312. }
  313. }
  314. if ($view == 'coach') {
  315. /****************************************
  316. * Infos about sessions of the coach
  317. ****************************************/
  318. $sessions = Tracking :: get_sessions_coached_by_user($_user['user_id']);
  319. $nb_sessions = count($sessions);
  320. $nb_sessions_past = $nb_sessions_future = $nb_sessions_current = 0;
  321. $courses = array();
  322. foreach ($sessions as $session) {
  323. if ($session['date_start'] == '0000-00-00') {
  324. $nb_sessions_current ++;
  325. } else {
  326. $date_start = explode('-', $session['date_start']);
  327. $time_start = mktime(0, 0, 0, $date_start[1], $date_start[2], $date_start[0]);
  328. $date_end = explode('-', $session['date_end']);
  329. $time_end = mktime(0, 0, 0, $date_end[1], $date_end[2], $date_end[0]);
  330. if ($time_start < time() && time() < $time_end) {
  331. $nb_sessions_current++;
  332. }
  333. elseif (time() < $time_start) {
  334. $nb_sessions_future++;
  335. }
  336. elseif (time() > $time_end) {
  337. $nb_sessions_past++;
  338. }
  339. }
  340. $courses = array_merge($courses, Tracking::get_courses_list_from_session($session['id']));
  341. }
  342. if ($nb_sessions > 0) {
  343. $nb_courses_per_session = round(count($courses) / $nb_sessions, 2);
  344. $nb_students_per_session = round($nb_students / $nb_sessions, 2);
  345. } else {
  346. $nb_courses_per_session = null;
  347. $nb_students_per_session = null;
  348. }
  349. if ($export_csv) {
  350. //csv part
  351. $csv_content[] = array(get_lang('Sessions', ''));
  352. $csv_content[] = array(get_lang('NbActiveSessions', '').';'.$nb_sessions_current);
  353. $csv_content[] = array(get_lang('NbPastSessions', '').';'.$nb_sessions_past);
  354. $csv_content[] = array(get_lang('NbFutureSessions', '').';'.$nb_sessions_future);
  355. $csv_content[] = array(get_lang('NbStudentPerSession', '').';'.$nb_students_per_session);
  356. $csv_content[] = array(get_lang('NbCoursesPerSession', '').';'.$nb_courses_per_session);
  357. $csv_content[] = array();
  358. } else {
  359. // html part
  360. echo '
  361. <div class="report_section">
  362. <h4>
  363. <a href="session.php"><img src="'.api_get_path(WEB_IMG_PATH).'sessions.gif">&nbsp;'.get_lang('Sessions').' ('.$nb_sessions.')'.'</a>
  364. </h4>
  365. <table class="data_table">
  366. <tr>
  367. <td>
  368. '.get_lang('NbActiveSessions').'
  369. </td>
  370. <td align="right">
  371. '.$nb_sessions_current.'
  372. </td>
  373. </tr>
  374. <tr>
  375. <td>
  376. '.get_lang('NbPastSessions').'
  377. </td>
  378. <td align="right">
  379. '.$nb_sessions_past.'
  380. </td>
  381. </tr>
  382. <tr>
  383. <td>
  384. '.get_lang('NbFutureSessions').'
  385. </td>
  386. <td align="right">
  387. '.$nb_sessions_future.'
  388. </td>
  389. </tr>
  390. <tr>
  391. <td>
  392. '.get_lang('NbStudentPerSession').'
  393. </td>
  394. <td align="right">
  395. '.(is_null($nb_students_per_session) ? '' : $nb_students_per_session).'
  396. </td>
  397. </tr>
  398. <tr>
  399. <td>
  400. '.get_lang('NbCoursesPerSession').'
  401. </td>
  402. <td align="right">
  403. '.(is_null($nb_courses_per_session) ? '' : $nb_courses_per_session).'
  404. </td>
  405. </tr>
  406. </table>
  407. <a href="session.php">'.get_lang('SeeSessionList').'</a>
  408. </div>';
  409. }
  410. }
  411. echo '<div class="clear">&nbsp;</div>';
  412. if (api_is_allowed_to_create_course() && $view == 'teacher') {
  413. if ($nb_teacher_courses) {
  414. $table = new SortableTable('courses', 'get_number_of_courses' ,'get_course_data');
  415. $parameters['view'] = 'teacher';
  416. $parameters['class'] = 'data_table';
  417. $table->set_additional_parameters($parameters);
  418. $table -> set_header(0, get_lang('CourseTitle'), false, 'align="center"');
  419. $table -> set_header(1, get_lang('NbStudents'), false);
  420. $table -> set_header(2, get_lang('AvgTimeSpentInTheCourse').Display :: return_icon('info3.gif', get_lang('TimeOfActiveByTraining'), array('align' => 'absmiddle', 'hspace' => '3px')), false);
  421. $table -> set_header(3, get_lang('AvgStudentsProgress').Display :: return_icon('info3.gif', get_lang('AvgAllUsersInAllCourses'), array('align' => 'absmiddle', 'hspace' => '3px')), false);
  422. $table -> set_header(4, get_lang('AvgCourseScore').Display :: return_icon('info3.gif', get_lang('AvgAllUsersInAllCourses'), array('align' => 'absmiddle', 'hspace' => '3px')), false);
  423. $table -> set_header(5, get_lang('AvgExercisesScore').Display :: return_icon('info3.gif', get_lang('AvgAllUsersInAllCourses'), array('align' => 'absmiddle', 'hspace' => '3px')), false);
  424. $table -> set_header(6, get_lang('AvgMessages'), false);
  425. $table -> set_header(7, get_lang('AvgAssignments'), false);
  426. $table -> set_header(8, get_lang('Details'), false);
  427. $csv_content[] = array (
  428. get_lang('CourseTitle', ''),
  429. get_lang('NbStudents', ''),
  430. get_lang('AvgTimeSpentInTheCourse', ''),
  431. get_lang('AvgStudentsProgress', ''),
  432. get_lang('AvgCourseScore', ''),
  433. get_lang('AvgExercisesScore', ''),
  434. get_lang('AvgMessages', ''),
  435. get_lang('AvgAssignments', '')
  436. );
  437. $table->display();
  438. }
  439. }
  440. if ($is_platform_admin && $view == 'admin') {
  441. echo '<a href="'.api_get_self().'?view=admin&amp;display=coaches">'.get_lang('DisplayCoaches').'</a> | ';
  442. echo '<a href="'.api_get_self().'?view=admin&amp;display=useroverview">'.get_lang('DisplayUserOverview').'</a>';
  443. if ($_GET['display'] == 'useroverview') {
  444. echo ' | <a href="'.api_get_self().'?view=admin&amp;display=useroverview&amp;export=options">'.get_lang('ExportUserOverviewOptions').'</a>';
  445. }
  446. echo '<br /><br />';
  447. if ($_GET['display'] === 'useroverview') {
  448. display_tracking_user_overview();
  449. } else {
  450. if ($export_csv) {
  451. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  452. } else {
  453. $is_western_name_order = api_is_western_name_order();
  454. }
  455. $sort_by_first_name = api_sort_by_first_name();
  456. $tracking_column = isset($_GET['tracking_list_coaches_column']) ? $_GET['tracking_list_coaches_column'] : ($is_western_name_order xor $sort_by_first_name) ? 1 : 0;
  457. $tracking_direction = (isset($_GET['tracking_list_coaches_direction']) && in_array(strtoupper($_GET['tracking_list_coaches_direction']), array('ASC', 'DESC', 'ASCENDING', 'DESCENDING', '0', '1'))) ? $_GET['tracking_list_coaches_direction'] : 'DESC';
  458. // Prepare array for column order - when impossible, use some of user names.
  459. if ($is_western_name_order) {
  460. $order = array(0 => 'firstname', 1 => 'lastname', 2 => ($sort_by_first_name ? 'firstname' : 'lastname'), 3 => 'login_date', 4 => ($sort_by_first_name ? 'firstname' : 'lastname'), 5 => ($sort_by_first_name ? 'firstname' : 'lastname'));
  461. } else {
  462. $order = array(0 => 'lastname', 1 => 'firstname', 2 => ($sort_by_first_name ? 'firstname' : 'lastname'), 3 => 'login_date', 4 => ($sort_by_first_name ? 'firstname' : 'lastname'), 5 => ($sort_by_first_name ? 'firstname' : 'lastname'));
  463. }
  464. $table = new SortableTable('tracking_list_coaches', 'count_coaches', null, ($is_western_name_order xor $sort_by_first_name) ? 1 : 0);
  465. $parameters['view'] = 'admin';
  466. $table->set_additional_parameters($parameters);
  467. if ($is_western_name_order) {
  468. $table -> set_header(0, get_lang('FirstName'), true, 'align="center"');
  469. $table -> set_header(1, get_lang('LastName'), true, 'align="center"');
  470. } else {
  471. $table -> set_header(0, get_lang('LastName'), true, 'align="center"');
  472. $table -> set_header(1, get_lang('FirstName'), true, 'align="center"');
  473. }
  474. $table -> set_header(2, get_lang('TimeSpentOnThePlatform'), false);
  475. $table -> set_header(3, get_lang('LastConnexion'), true, 'align="center"');
  476. $table -> set_header(4, get_lang('NbStudents'), false);
  477. $table -> set_header(5, get_lang('CountCours'), false);
  478. $table -> set_header(6, get_lang('NumberOfSessions'), false);
  479. $table -> set_header(7, get_lang('Sessions'), false, 'align="center"');
  480. if ($is_western_name_order) {
  481. $csv_header[] = array (
  482. get_lang('FirstName', ''),
  483. get_lang('LastName', ''),
  484. get_lang('TimeSpentOnThePlatform', ''),
  485. get_lang('LastConnexion', ''),
  486. get_lang('NbStudents', ''),
  487. get_lang('CountCours', ''),
  488. get_lang('NumberOfSessions', '')
  489. );
  490. } else {
  491. $csv_header[] = array (
  492. get_lang('LastName', ''),
  493. get_lang('FirstName', ''),
  494. get_lang('TimeSpentOnThePlatform', ''),
  495. get_lang('LastConnexion', ''),
  496. get_lang('NbStudents', ''),
  497. get_lang('CountCours', ''),
  498. get_lang('NumberOfSessions', '')
  499. );
  500. }
  501. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  502. $sqlCoachs = "SELECT DISTINCT scu.id_user as id_coach, user_id, lastname, firstname, MAX(login_date) as login_date
  503. FROM $tbl_user, $tbl_session_course_user scu, $tbl_track_login
  504. WHERE scu.id_user=user_id AND scu.status=2 AND login_user_id=user_id
  505. GROUP BY user_id ";
  506. if ($_configuration['multiple_access_urls'] == true) {
  507. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  508. $access_url_id = api_get_current_access_url_id();
  509. if ($access_url_id != -1) {
  510. $sqlCoachs = "SELECT DISTINCT scu.id_user as id_coach, user_id, lastname, firstname, MAX(login_date) as login_date
  511. FROM $tbl_user, $tbl_session_course_user scu, $tbl_track_login , $tbl_session_rel_access_url session_rel_url
  512. WHERE scu.id_user=user_id AND scu.status=2 AND login_user_id=user_id AND access_url_id = $access_url_id AND session_rel_url.session_id=id_session
  513. GROUP BY user_id ";
  514. }
  515. }
  516. if (!empty($order[$tracking_column])) {
  517. $sqlCoachs .= "ORDER BY ".$order[$tracking_column]." ".$tracking_direction;
  518. }
  519. $result_coaches = Database::query($sqlCoachs);
  520. $total_no_coaches = Database::num_rows($result_coaches);
  521. $global_coaches = array();
  522. while ($coach = Database::fetch_array($result_coaches)) {
  523. $global_coaches[$coach['user_id']] = $coach;
  524. }
  525. $sql_session_coach = 'SELECT session.id_coach, user_id, lastname, firstname, MAX(login_date) as login_date
  526. FROM '.$tbl_user.','.$tbl_sessions.' as session,'.$tbl_track_login.'
  527. WHERE id_coach=user_id AND login_user_id=user_id
  528. GROUP BY user_id
  529. ORDER BY login_date '.$tracking_direction;
  530. if ($_configuration['multiple_access_urls'] == true) {
  531. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  532. $access_url_id = api_get_current_access_url_id();
  533. if ($access_url_id != -1) {
  534. $sql_session_coach = 'SELECT session.id_coach, user_id, lastname, firstname, MAX(login_date) as login_date
  535. FROM '.$tbl_user.','.$tbl_sessions.' as session,'.$tbl_track_login.' , '.$tbl_session_rel_access_url.' as session_rel_url
  536. WHERE id_coach=user_id AND login_user_id=user_id AND access_url_id = '.$access_url_id.' AND session_rel_url.session_id=session.id
  537. GROUP BY user_id
  538. ORDER BY login_date '.$tracking_direction;
  539. }
  540. }
  541. $result_sessions_coach = Database::query($sql_session_coach);
  542. $total_no_coaches += Database::num_rows($result_sessions_coach);
  543. while ($coach = Database::fetch_array($result_sessions_coach)) {
  544. $global_coaches[$coach['user_id']] = $coach;
  545. }
  546. $all_datas = array();
  547. foreach ($global_coaches as $id_coach => $coaches) {
  548. $time_on_platform = api_time_to_hms(Tracking :: get_time_spent_on_the_platform($coaches['user_id']));
  549. $last_connection = Tracking :: get_last_connection_date($coaches['user_id']);
  550. $nb_students = count(Tracking :: get_student_followed_by_coach($coaches['user_id']));
  551. $nb_courses = count(Tracking :: get_courses_followed_by_coach($coaches['user_id']));
  552. $nb_sessions = count(Tracking :: get_sessions_coached_by_user($coaches['user_id']));
  553. $table_row = array();
  554. if ($is_western_name_order) {
  555. $table_row[] = $coaches['firstname'];
  556. $table_row[] = $coaches['lastname'];
  557. } else {
  558. $table_row[] = $coaches['lastname'];
  559. $table_row[] = $coaches['firstname'];
  560. }
  561. $table_row[] = $time_on_platform;
  562. $table_row[] = $last_connection;
  563. $table_row[] = $nb_students;
  564. $table_row[] = $nb_courses;
  565. $table_row[] = $nb_sessions;
  566. $table_row[] = '<center><a href="session.php?id_coach='.$coaches['user_id'].'"><img src="'.api_get_path(WEB_IMG_PATH).'2rightarrow.gif" border="0" /></a></center>';
  567. $all_datas[] = $table_row;
  568. if ($is_western_name_order) {
  569. $csv_content[] = array(
  570. api_html_entity_decode($coaches['firstname'], ENT_QUOTES, $charset),
  571. api_html_entity_decode($coaches['lastname'], ENT_QUOTES, $charset),
  572. $time_on_platform,
  573. $last_connection,
  574. $nb_students,
  575. $nb_courses,
  576. $nb_sessions
  577. );
  578. } else {
  579. $csv_content[] = array(
  580. api_html_entity_decode($coaches['lastname'], ENT_QUOTES, $charset),
  581. api_html_entity_decode($coaches['firstname'], ENT_QUOTES, $charset),
  582. $time_on_platform,
  583. $last_connection,
  584. $nb_students,
  585. $nb_courses,
  586. $nb_sessions
  587. );
  588. }
  589. }
  590. if ($tracking_column != 3) {
  591. if ($tracking_direction == 'DESC') {
  592. usort($all_datas, 'rsort_users');
  593. } else {
  594. usort($all_datas, 'sort_users');
  595. }
  596. }
  597. if ($export_csv && $tracking_column != 3) {
  598. usort($csv_content, 'sort_users');
  599. }
  600. if ($export_csv) {
  601. $csv_content = array_merge($csv_header, $csv_content);
  602. }
  603. foreach ($all_datas as $row) {
  604. $table -> addRow($row, 'align="right"');
  605. }
  606. $table -> updateColAttributes(0, array('align' => 'left'));
  607. $table -> updateColAttributes(1, array('align' => 'left'));
  608. $table -> updateColAttributes(3, array('align' => 'left'));
  609. $table -> updateColAttributes(7, array('align' => 'center'));
  610. $table -> display();
  611. }
  612. }
  613. // send the csv file if asked
  614. if ($export_csv) {
  615. ob_end_clean();
  616. Export :: export_table_csv($csv_content, 'reporting_index');
  617. exit;
  618. }
  619. //footer
  620. if (!$export_csv) {
  621. Display::display_footer();
  622. }
  623. /**
  624. * This function exports the table that we see in display_tracking_user_overview()
  625. *
  626. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  627. * @version Dokeos 1.8.6
  628. * @since October 2008
  629. */
  630. function export_tracking_user_overview() {
  631. // database table definitions
  632. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  633. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  634. $sort_by_first_name = api_sort_by_first_name();
  635. // the values of the sortable table
  636. if ($_GET['tracking_user_overview_page_nr']) {
  637. $from = $_GET['tracking_user_overview_page_nr'];
  638. } else {
  639. $from = 0;
  640. }
  641. if ($_GET['tracking_user_overview_column']) {
  642. $orderby = $_GET['tracking_user_overview_column'];
  643. } else {
  644. $orderby = 0;
  645. }
  646. if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
  647. // Swapping the sorting column if name order for export is different than the common name order.
  648. $orderby = 3 - $orderby;
  649. }
  650. if ($_GET['tracking_user_overview_direction']) {
  651. $direction = $_GET['tracking_user_overview_direction'];
  652. } else {
  653. $direction = 'ASC';
  654. }
  655. $user_data = get_user_data_tracking_overview($from, 1000, $orderby, $direction);
  656. // the first line of the csv file with the column headers
  657. $csv_row = array();
  658. $csv_row[] = get_lang('OfficialCode');
  659. if ($is_western_name_order) {
  660. $csv_row[] = get_lang('FirstName', '');
  661. $csv_row[] = get_lang('LastName', '');
  662. } else {
  663. $csv_row[] = get_lang('LastName', '');
  664. $csv_row[] = get_lang('FirstName', '');
  665. }
  666. $csv_row[] = get_lang('LoginName');
  667. $csv_row[] = get_lang('CourseCode');
  668. // the additional user defined fields (only those that were selected to be exported)
  669. require_once (api_get_path(LIBRARY_PATH).'usermanager.lib.php');
  670. $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  671. if (is_array($_SESSION['additional_export_fields'])) {
  672. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  673. $csv_row[] = $fields[$extra_field_export][3];
  674. $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1];
  675. }
  676. }
  677. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  678. $csv_row[] = get_lang('AvgStudentsProgress', '');
  679. $csv_row[] = get_lang('AvgCourseScore', '');
  680. $csv_row[] = get_lang('AvgExercisesScore', '');
  681. $csv_row[] = get_lang('AvgMessages', '');
  682. $csv_row[] = get_lang('AvgAssignments', '');
  683. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  684. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  685. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  686. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  687. $csv_row[] = get_lang('FirstLogin', '');
  688. $csv_row[] = get_lang('LatestLogin', '');
  689. $csv_content[] = $csv_row;
  690. // the other lines (the data)
  691. foreach ($user_data as $key => $user) {
  692. // getting all the courses of the user
  693. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user[4])."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  694. $result = Database::query($sql);
  695. while ($row = Database::fetch_row($result)) {
  696. $csv_row = array();
  697. // user official code
  698. $csv_row[] = $user[0];
  699. // user first|last name
  700. $csv_row[] = $user[1];
  701. // user last|first name
  702. $csv_row[] = $user[2];
  703. // user login name
  704. $csv_row[] = $user[3];
  705. // course code
  706. $csv_row[] = $row[0];
  707. // the additional defined user fields
  708. $extra_fields = get_user_overview_export_extra_fields($user[4]);
  709. if (is_array($field_names_to_be_exported)) {
  710. foreach ($field_names_to_be_exported as $key => $extra_field_export) {
  711. $csv_row[] = $extra_fields[$extra_field_export];
  712. }
  713. }
  714. // time spent in the course
  715. $csv_row[] = api_time_to_hms(Tracking :: get_time_spent_on_the_course ($user[4], $row[0]));
  716. // student progress in course
  717. $csv_row[] = round(Tracking :: get_avg_student_progress ($user[4], $row[0]), 2);
  718. // student score
  719. $csv_row[] = round(Tracking :: get_avg_student_score ($user[4], $row[0]), 2);
  720. // student tes score
  721. $csv_row[] = round(Tracking :: get_avg_student_exercise_score ($user[4], $row[0]), 2);
  722. // student messages
  723. $csv_row[] = Tracking :: count_student_messages ($user[4], $row[0]);
  724. // student assignments
  725. $csv_row[] = Tracking :: count_student_assignments ($user[4], $row[0]);
  726. // student exercises results
  727. $exercises_results = exercises_results($user[4], $row[0]);
  728. $csv_row[] = $exercises_results['score_obtained'];
  729. $csv_row[] = $exercises_results['score_possible'];
  730. $csv_row[] = $exercises_results['questions_answered'];
  731. $csv_row[] = $exercises_results['percentage'];
  732. // first connection
  733. $csv_row[] = Tracking :: get_first_connection_date_on_the_course ($user[4], $row[0]);
  734. // last connection
  735. $csv_row[] = strip_tags(Tracking :: get_last_connection_date_on_the_course ($user[4], $row[0]));
  736. $csv_content[] = $csv_row;
  737. }
  738. }
  739. Export :: export_table_csv($csv_content, 'reporting_user_overview');
  740. exit;
  741. }
  742. /**
  743. * Display a sortable table that contains an overview off all the reporting progress of all users and all courses the user is subscribed to
  744. *
  745. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  746. * @version Dokeos 1.8.6
  747. * @since October 2008
  748. */
  749. function display_tracking_user_overview() {
  750. display_user_overview_export_options();
  751. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  752. $t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  753. $t_head .= '<tr>';
  754. $t_head .= ' <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  755. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  756. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  757. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  758. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  759. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgMessages'), 6, true).'</span></th>';
  760. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgAssignments'), 6, true).'</span></th>';
  761. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  762. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  763. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  764. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  765. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  766. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  767. $t_head .= ' </tr></table>';
  768. $addparams = array('view' => 'admin', 'display' => 'useroverview');
  769. $table = new SortableTable('tracking_user_overview', 'get_number_of_users_tracking_overview', 'get_user_data_tracking_overview', 0);
  770. $table->additional_parameters = $addparams;
  771. $table->set_header(0, get_lang('OfficialCode'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  772. if (api_is_western_name_order()) {
  773. $table->set_header(1, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  774. $table->set_header(2, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  775. } else {
  776. $table->set_header(1, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  777. $table->set_header(2, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  778. }
  779. $table->set_header(3, get_lang('LoginName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  780. $table->set_header(4, $t_head, false, array('style' => 'width:90%;border:0;padding:0;font-size:7.5pt;'), array('style' => 'width:90%;padding:0;font-size:7.5pt;'));
  781. $table->set_column_filter(4, 'course_info_tracking_filter');
  782. $table->display();
  783. }
  784. /**
  785. * get the numer of users of the platform
  786. *
  787. * @return integer
  788. *
  789. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  790. * @version Dokeos 1.8.6
  791. * @since October 2008
  792. */
  793. function get_number_of_users_tracking_overview() {
  794. // database table definition
  795. $main_user_table = Database :: get_main_table(TABLE_MAIN_USER);
  796. // query
  797. $sql = 'SELECT user_id FROM '.$main_user_table;
  798. $result = Database::query($sql);
  799. // return the number of results
  800. return Database::num_rows($result);
  801. }
  802. /**
  803. * get all the data for the sortable table of the reporting progress of all users and all the courses the user is subscribed to.
  804. *
  805. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  806. * @version Dokeos 1.8.6
  807. * @since October 2008
  808. */
  809. function get_user_data_tracking_overview($from, $number_of_items, $column, $direction) {
  810. // database table definition
  811. $main_user_table = Database :: get_main_table(TABLE_MAIN_USER);
  812. global $export_csv;
  813. if ($export_csv) {
  814. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  815. } else {
  816. $is_western_name_order = api_is_western_name_order();
  817. }
  818. $sql = "SELECT
  819. official_code AS col0,
  820. ".($is_western_name_order ? "
  821. firstname AS col1,
  822. lastname AS col2,
  823. " : "
  824. lastname AS col1,
  825. firstname AS col2,
  826. ").
  827. "username AS col3,
  828. user_id AS col4
  829. FROM
  830. $main_user_table
  831. ";
  832. $sql .= " ORDER BY col$column $direction ";
  833. $sql .= " LIMIT $from,$number_of_items";
  834. $result = Database::query($sql);
  835. $return = array ();
  836. while ($user = Database::fetch_row($result)) {
  837. $return[] = $user;
  838. }
  839. return $return;
  840. }
  841. /**
  842. * Creates a small table in the last column of the table with the user overview
  843. *
  844. * @param integer $user_id the id of the user
  845. * @param array $url_params additonal url parameters
  846. * @param array $row the row information (the other columns)
  847. * @return html code
  848. *
  849. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  850. * @version Dokeos 1.8.6
  851. * @since October 2008
  852. */
  853. function course_info_tracking_filter($user_id, $url_params, $row) {
  854. // the table header
  855. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  856. /*$return .= ' <tr>';
  857. $return .= ' <th>'.get_lang('Course').'</th>';
  858. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  859. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  860. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  861. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  862. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  863. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  864. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  865. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  866. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  867. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  868. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  869. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  870. $return .= ' </tr>';*/
  871. // database table definition
  872. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  873. // getting all the courses of the user
  874. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user_id)."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  875. $result = Database::query($sql);
  876. while ($row = Database::fetch_row($result)) {
  877. $return .= '<tr>';
  878. // course code
  879. $return .= ' <td width="157px" >'.cut($row[0], 20, true).'</td>';
  880. // time spent in the course
  881. $return .= ' <td><div>'.api_time_to_hms(Tracking :: get_time_spent_on_the_course($user_id, $row[0])).'</div></td>';
  882. // student progress in course
  883. $return .= ' <td><div>'.round(Tracking :: get_avg_student_progress($user_id, $row[0]), 2).'</div></td>';
  884. // student score
  885. $return .= ' <td><div>'.round(Tracking :: get_avg_student_score($user_id, $row[0]), 2).'</div></td>';
  886. // student tes score
  887. //$return .= ' <td><div style="width:40px">'.round(Tracking :: get_avg_student_exercise_score ($user_id, $row[0]),2).'%</div></td>';
  888. // student messages
  889. $return .= ' <td><div>'.Tracking :: count_student_messages($user_id, $row[0]).'</div></td>';
  890. // student assignments
  891. $return .= ' <td><div>'.Tracking :: count_student_assignments($user_id, $row[0]).'</div></td>';
  892. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  893. $exercises_results = exercises_results($user_id, $row[0]);
  894. $return .= ' <td width="105px"><div>'.(is_null($exercises_results['percentage']) ? '' : $exercises_results['score_obtained'].'/'.$exercises_results['score_possible'].' ( '.$exercises_results['percentage'].'% )').'</div></td>';
  895. //$return .= ' <td><div>'.$exercises_results['score_possible'].'</div></td>';
  896. $return .= ' <td><div>'.$exercises_results['questions_answered'].'</div></td>';
  897. //$return .= ' <td><div>'.$exercises_results['percentage'].'% </div></td>';
  898. // first connection
  899. //$return .= ' <td width="60px">'.Tracking :: get_first_connection_date_on_the_course ($user_id, $row[0]).'</td>';
  900. // last connection
  901. $return .= ' <td><div>'.Tracking :: get_last_connection_date_on_the_course ($user_id, $row[0]).'</div></td>';
  902. $return .= '<tr>';
  903. }
  904. $return .= '</table>';
  905. return $return;
  906. }
  907. /**
  908. * Get general information about the exercise performance of the user
  909. * the total obtained score (all the score on all the questions)
  910. * the maximum score that could be obtained
  911. * the number of questions answered
  912. * the success percentage
  913. *
  914. * @param integer $user_id the id of the user
  915. * @param string $course_code the course code
  916. *
  917. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  918. * @version Dokeos 1.8.6
  919. * @since November 2008
  920. */
  921. function exercises_results($user_id, $course_code) {
  922. $questions_answered = 0;
  923. $sql = 'SELECT exe_result , exe_weighting
  924. FROM '.Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES)."
  925. WHERE exe_cours_id = '".Database::escape_string($course_code)."'
  926. AND exe_user_id = '".Database::escape_string($user_id)."'";
  927. $result = Database::query($sql);
  928. $score_obtained = 0;
  929. $score_possible = 0;
  930. $questions_answered = 0;
  931. while ($row = Database::fetch_array($result)) {
  932. $score_obtained += $row['exe_result'];
  933. $score_possible += $row['exe_weighting'];
  934. $questions_answered ++;
  935. }
  936. if ($score_possible != 0) {
  937. $percentage = round(($score_obtained / $score_possible * 100), 2);
  938. } else {
  939. $percentage = null;
  940. }
  941. return array('score_obtained' => $score_obtained, 'score_possible' => $score_possible, 'questions_answered' => $questions_answered, 'percentage' => $percentage);
  942. }
  943. /**
  944. * Displays a form with all the additionally defined user fields of the profile
  945. * and give you the opportunity to include these in the CSV export
  946. *
  947. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  948. * @version Dokeos 1.8.6
  949. * @since November 2008
  950. */
  951. function display_user_overview_export_options() {
  952. // include the user manager and formvalidator library
  953. require_once api_get_path(LIBRARY_PATH).'usermanager.lib.php';
  954. require_once api_get_path(LIBRARY_PATH).'formvalidator/FormValidator.class.php';
  955. if ($_GET['export'] == 'options') {
  956. // get all the defined extra fields
  957. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC', false);
  958. // creating the form with all the defined extra fields
  959. $form = new FormValidator('exportextrafields', 'post', api_get_self()."?view=".Security::remove_XSS($_GET['view']).'&display='.Security::remove_XSS($_GET['display']).'&export='.Security::remove_XSS($_GET['export']));
  960. if (is_array($extrafields) && count($extrafields) > 0) {
  961. foreach ($extrafields as $key => $extra) {
  962. $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]);
  963. }
  964. $form->addElement('style_submit_button','submit', get_lang('Ok'),'class="save"' );
  965. // setting the default values for the form that contains all the extra fields
  966. if (is_array($_SESSION['additional_export_fields'])) {
  967. foreach ($_SESSION['additional_export_fields'] as $key => $value) {
  968. $defaults['extra_export_field'.$value] = 1;
  969. }
  970. }
  971. $form->setDefaults($defaults);
  972. } else {
  973. $form->addElement('html', Display::display_warning_message(get_lang('ThereAreNotExtrafieldsAvailable')));
  974. }
  975. if ($form->validate()) {
  976. // exporting the form values
  977. $values = $form->exportValues();
  978. // re-initialising the session that contains the additional fields that need to be exported
  979. $_SESSION['additional_export_fields'] = array();
  980. // adding the fields that are checked to the session
  981. $message = '';
  982. foreach ($values as $field_ids => $value) {
  983. if ($value == 1 && strstr($field_ids,'extra_export_field')) {
  984. $_SESSION['additional_export_fields'][] = str_replace('extra_export_field', '', $field_ids);
  985. }
  986. }
  987. // adding the fields that will be also exported to a message string
  988. if (is_array($_SESSION['additional_export_fields'])) {
  989. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  990. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  991. }
  992. }
  993. // Displaying a feedback message
  994. if (!empty($_SESSION['additional_export_fields'])) {
  995. Display::display_confirmation_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  996. } else {
  997. Display::display_confirmation_message(get_lang('NoAdditionalFieldsWillBeExported'), false);
  998. }
  999. $message = '';
  1000. } else {
  1001. $form->display();
  1002. }
  1003. } else {
  1004. if (!empty($_SESSION['additional_export_fields'])) {
  1005. // get all the defined extra fields
  1006. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  1007. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  1008. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  1009. }
  1010. Display::display_normal_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  1011. $message = '';
  1012. }
  1013. }
  1014. }
  1015. /**
  1016. * Get all information that the user with user_id = $user_data has
  1017. * entered in the additionally defined profile fields
  1018. *
  1019. * @param integer $user_id the id of the user
  1020. *
  1021. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1022. * @version Dokeos 1.8.6
  1023. * @since November 2008
  1024. */
  1025. function get_user_overview_export_extra_fields($user_id) {
  1026. // include the user manager
  1027. require_once api_get_path(LIBRARY_PATH).'usermanager.lib.php';
  1028. $extra_data = UserManager::get_extra_user_data($user_id, true);
  1029. return $extra_data;
  1030. }
  1031. /**
  1032. * Get number of courses for sortable with pagination
  1033. * @return int
  1034. */
  1035. function get_number_of_courses() {
  1036. global $courses;
  1037. return count($courses);
  1038. }
  1039. /**
  1040. * Get data for courses list in sortable with pagination
  1041. * @return array
  1042. */
  1043. function get_course_data($from, $number_of_items, $column, $direction) {
  1044. global $courses, $csv_content, $charset ;
  1045. global $tbl_course, $tbl_course_user, $tbl_track_cours_access, $tbl_session_course_user;
  1046. $a_course_students = array();
  1047. $course_data = array();
  1048. $arr_course = $courses;
  1049. foreach ($arr_course as &$cours) {
  1050. $cours = "'{$cours[course_code]}'";
  1051. }
  1052. // get all courses with limit
  1053. $sql = "SELECT course.code as col1, course.title as col2
  1054. FROM $tbl_course course
  1055. WHERE course.code IN (".implode(',',$arr_course).")";
  1056. if (!in_array($direction, array('ASC','DESC'))) $direction = 'ASC';
  1057. $column = intval($column);
  1058. $from = intval($from);
  1059. $number_of_items = intval($number_of_items);
  1060. $sql .= " ORDER BY col$column $direction ";
  1061. $sql .= " LIMIT $from,$number_of_items";
  1062. $res = Database::query($sql);
  1063. while ($row_course = Database::fetch_row($res)) {
  1064. $course_code = $row_course[0];
  1065. $course_info = api_get_course_info($course_code);
  1066. $avg_assignments_in_course = $avg_messages_in_course = $nb_students_in_course = $avg_progress_in_course = $avg_score_in_course = $avg_time_spent_in_course = $avg_score_in_exercise = 0;
  1067. $tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $course_info['dbName']);
  1068. $tbl_forum_post = Database :: get_course_table(TABLE_FORUM_POST, $course_info['dbName']);
  1069. $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $course_info['dbName']);
  1070. $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $course_info['dbName']);
  1071. // students directly subscribed to the course
  1072. $sql = "SELECT user_id FROM $tbl_course_user as course_rel_user WHERE course_rel_user.status='5' AND course_rel_user.course_code='$course_code'
  1073. UNION DISTINCT SELECT id_user as user_id FROM $tbl_session_course_user srcu WHERE srcu. course_code='$course_code'";
  1074. $rs = Database::query($sql);
  1075. $users = array();
  1076. while ($row = Database::fetch_array($rs)) {
  1077. $users[] = $row['user_id'];
  1078. }
  1079. if (count($users) > 0) {
  1080. $nb_students_in_course = count($users);
  1081. $avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code);
  1082. $avg_messages_in_course = Tracking::count_student_messages($users, $course_code);
  1083. $avg_time_spent_in_course = Tracking::get_time_spent_on_the_course($users, $course_code);
  1084. $avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code);
  1085. $avg_score_in_course = Tracking :: get_avg_student_score($users, $course_code);
  1086. $avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code);
  1087. $avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
  1088. $avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
  1089. $avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
  1090. $avg_score_in_exercise = round($avg_score_in_exercise / $nb_students_in_course, 2);
  1091. } else {
  1092. $avg_time_spent_in_course = null;
  1093. $avg_progress_in_course = null;
  1094. $avg_score_in_course = null;
  1095. $avg_score_in_exercise = null;
  1096. $avg_messages_in_course = null;
  1097. $avg_assignments_in_course = null;
  1098. }
  1099. $table_row = array();
  1100. $table_row[] = $row_course[1];
  1101. $table_row[] = $nb_students_in_course;
  1102. $table_row[] = $avg_time_spent_in_course;
  1103. $table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course.'%';
  1104. $table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course.'%';
  1105. $table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise.'%';
  1106. $table_row[] = $avg_messages_in_course;
  1107. $table_row[] = $avg_assignments_in_course;
  1108. //set the "from" value to know if I access the Reporting by the Dokeos tab or the course link
  1109. $table_row[] = '<center><a href="../tracking/courseLog.php?cidReq='.$course_code.'&studentlist=true&from=myspace"><img src="'.api_get_path(WEB_IMG_PATH).'2rightarrow.gif" border="0" /></a></center>';
  1110. $csv_content[] = array(
  1111. api_html_entity_decode($row_course[1], ENT_QUOTES, $charset),
  1112. $nb_students_in_course,
  1113. $avg_time_spent_in_course,
  1114. is_null($avg_progress_in_course) ? null : $avg_progress_in_course.'%',
  1115. is_null($avg_score_in_course) ? null : $avg_score_in_course.'%',
  1116. is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise.'%',
  1117. $avg_messages_in_course,
  1118. $avg_assignments_in_course,
  1119. );
  1120. $course_data[] = $table_row;
  1121. }
  1122. return $course_data;
  1123. }