index.php 48 KB

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