myStudents.php 34 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061
  1. <?php
  2. /*
  3. * Created on 20 juil. 2006 by Elixir Interactive http://www.elixir-interactive.com
  4. */
  5. // name of the language file that needs to be included
  6. $language_file = array ('registration', 'index', 'tracking', 'exercice');
  7. $cidReset=true;
  8. include ('../inc/global.inc.php');
  9. include_once(api_get_path(LIBRARY_PATH).'tracking.lib.php');
  10. include_once(api_get_path(LIBRARY_PATH).'export.lib.inc.php');
  11. include_once(api_get_path(LIBRARY_PATH).'usermanager.lib.php');
  12. include_once(api_get_path(LIBRARY_PATH).'course.lib.php');
  13. include_once('../newscorm/learnpath.class.php');
  14. $export_csv = isset($_GET['export']) && $_GET['export'] == 'csv' ? true : false;
  15. if($export_csv)
  16. {
  17. ob_start();
  18. }
  19. $csv_content = array();
  20. $this_section = "session_my_space";
  21. $nameTools=get_lang("StudentDetails");
  22. if(isset($_GET['details']))
  23. {
  24. if(!empty($_GET['origin']) && $_GET['origin'] == 'user_course')
  25. {
  26. $course_infos = CourseManager :: get_course_information($_GET['course']);
  27. if(empty($cidReq))
  28. $interbreadcrumb[] = array ("url" => api_get_path(WEB_COURSE_PATH).$course_infos['directory'], 'name' => $course_infos['title']);
  29. $interbreadcrumb[] = array ("url" => "../user/user.php?cidReq=".$_GET['course'], "name" => get_lang("Users"));
  30. }
  31. else if(!empty($_GET['origin']) && $_GET['origin'] == 'tracking_course')
  32. {
  33. $course_infos = CourseManager :: get_course_information($_GET['course']);
  34. if(empty($cidReq))
  35. $interbreadcrumb[] = array ("url" => api_get_path(WEB_COURSE_PATH).$course_infos['directory'], 'name' => $course_infos['title']);
  36. $interbreadcrumb[] = array ("url" => "../tracking/courseLog.php?cidReq=".$_GET['course'].'&studentlist=true&id_session='.$_SESSION['id_session'], "name" => get_lang("Tracking"));
  37. }
  38. else
  39. {
  40. $interbreadcrumb[] = array ("url" => "index.php", "name" => get_lang('MySpace'));
  41. if(isset($_GET['id_coach']) && intval($_GET['id_coach'])!=0){
  42. $interbreadcrumb[] = array ("url" => "student.php?id_coach=".$_GET['id_coach'], "name" => get_lang("CoachStudents"));
  43. $interbreadcrumb[] = array ("url" => "myStudents.php?student=".$_GET['student'].'&id_coach='.$_GET['id_coach'], "name" => get_lang("StudentDetails"));
  44. }
  45. else{
  46. $interbreadcrumb[] = array ("url" => "student.php", "name" => get_lang("MyStudents"));
  47. $interbreadcrumb[] = array ("url" => "myStudents.php?student=".$_GET['student'], "name" => get_lang("StudentDetails"));
  48. }
  49. }
  50. $nameTools=get_lang("DetailsStudentInCourse");
  51. }
  52. else
  53. {
  54. $interbreadcrumb[] = array ("url" => "index.php", "name" => get_lang('MySpace'));
  55. if(isset($_GET['id_coach']) && intval($_GET['id_coach'])!=0){
  56. if(isset($_GET['id_session']) && intval($_GET['id_session'])!=0){
  57. $interbreadcrumb[] = array ("url" => "student.php?id_coach=".$_GET['id_coach']."&id_session=".$_GET['id_session'], "name" => get_lang("CoachStudents"));
  58. }
  59. else{
  60. $interbreadcrumb[] = array ("url" => "student.php?id_coach=".$_GET['id_coach'], "name" => get_lang("CoachStudents"));
  61. }
  62. }
  63. else{
  64. $interbreadcrumb[] = array ("url" => "student.php", "name" => get_lang("MyStudents"));
  65. }
  66. }
  67. api_block_anonymous_users();
  68. if(empty($_SESSION['is_allowedCreateCourse']) && !api_is_coach() && $_user['status']!=DRH){
  69. api_not_allowed(true);
  70. }
  71. Display :: display_header($nameTools);
  72. /*
  73. * ======================================================================================
  74. * FUNCTIONS
  75. * ======================================================================================
  76. */
  77. function calculHours($seconds)
  78. {
  79. //How many hours ?
  80. $hours = floor($seconds / 3600);
  81. //How many minutes ?
  82. $min = floor(($seconds - ($hours * 3600)) / 60);
  83. if ($min < 10)
  84. $min = "0".$min;
  85. //How many seconds
  86. $sec = $seconds - ($hours * 3600) - ($min * 60);
  87. if ($sec < 10)
  88. $sec = "0".$sec;
  89. return $hours."h".$min."m".$sec."s" ;
  90. }
  91. function is_teacher($course_code){
  92. global $_user;
  93. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  94. $sql="SELECT 1 FROM $tbl_course_user WHERE user_id='".$_user["user_id"]."' AND course_code='".$course_code."' AND status='1'";
  95. $result=api_sql_query($sql);
  96. if(mysql_result($result)!=1){
  97. return true;
  98. }
  99. else{
  100. return false;
  101. }
  102. }
  103. /*
  104. *===============================================================================
  105. * MAIN CODE
  106. *===============================================================================
  107. */
  108. // Database Table Definitions
  109. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  110. $tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  111. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  112. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  113. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  114. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  115. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  116. $tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  117. $tbl_stats_exercices_attempts = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  118. //$tbl_course_lp_view = Database :: get_course_table('lp_view');
  119. //$tbl_course_lp_view_item = Database :: get_course_table('lp_item_view');
  120. //$tbl_course_lp_item = Database :: get_course_table('lp_item');
  121. $tbl_course_lp_view = 'lp_view';
  122. $tbl_course_lp_view_item = 'lp_item_view';
  123. $tbl_course_lp_item = 'lp_item';
  124. $tbl_course_lp = 'lp';
  125. $tbl_course_quiz = 'quiz';
  126. $course_quiz_question = 'quiz_question';
  127. $course_quiz_rel_question = 'quiz_rel_question';
  128. $course_quiz_answer = 'quiz_answer';
  129. $course_student_publication = Database::get_course_table(TABLE_STUDENT_PUBLICATION);
  130. if(isset($_GET["user_id"]) && $_GET["user_id"]!="")
  131. {
  132. $i_user_id=$_GET["user_id"];
  133. }
  134. else
  135. {
  136. $i_user_id = $_user['user_id'];
  137. }
  138. if(!empty($_GET['student']))
  139. {
  140. $student_id = intval($_GET['student']);
  141. // infos about user
  142. $a_infosUser = UserManager::get_user_info_by_id($student_id);
  143. if($_user['status']==DRH && $a_infosUser['hr_dept_id']!=$_user['user_id'])
  144. {
  145. api_not_allowed();
  146. }
  147. $a_infosUser['name'] = $a_infosUser['firstname'].' '.$a_infosUser['lastname'];
  148. echo '<div align="right">
  149. <a href="#" onclick="window.print()"><img align="absbottom" src="../img/printmgr.gif">&nbsp;'.get_lang('Print').'</a>
  150. <a href="'.api_get_self().'?'.$_SERVER['QUERY_STRING'].'&export=csv"><img align="absbottom" src="../img/excel.gif">&nbsp;'.get_lang('ExportAsCSV').'</a>
  151. </div>';
  152. // is the user online ?
  153. $statistics_database = Database :: get_statistic_database();
  154. $a_usersOnline = WhoIsOnline($_GET['student'], $statistics_database, 30);
  155. foreach($a_usersOnline as $a_online)
  156. {
  157. if(in_array($_GET['student'],$a_online))
  158. {
  159. $online = get_lang('Yes');
  160. break;
  161. }
  162. else
  163. {
  164. $online = get_lang('No');
  165. }
  166. }
  167. $avg_student_progress = $avg_student_score = $nb_courses = 0;
  168. $sql = 'SELECT course_code FROM '.$tbl_course_user.' WHERE user_id='.$a_infosUser['user_id'];
  169. $rs = api_sql_query($sql, __FILE__, __LINE__);
  170. $a_courses = array();
  171. while($row = Database :: fetch_array($rs))
  172. {
  173. $a_courses[$row['course_code']] = $row['course_code'];
  174. }
  175. // get the list of sessions where the user is subscribed as student
  176. $sql = 'SELECT DISTINCT course_code FROM '.Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER).' WHERE id_user='.intval($a_infosUser['user_id']);
  177. $rs = api_sql_query($sql, __FILE__, __LINE__);
  178. while($row = Database :: fetch_array($rs))
  179. {
  180. $a_courses[$row['course_code']] = $row['course_code'];
  181. }
  182. foreach ($a_courses as $key=>$course_code)
  183. {
  184. if(!CourseManager::is_user_subscribed_in_course($a_infosUser['user_id'], $course_code, true))
  185. {
  186. unset($a_courses[$key]);
  187. }
  188. else
  189. {
  190. $nb_courses++;
  191. $avg_student_progress += Tracking :: get_avg_student_progress($a_infosUser['user_id'],$course_code);
  192. $avg_student_score += Tracking :: get_avg_student_score($a_infosUser['user_id'],$course_code);
  193. }
  194. }
  195. $avg_student_progress = round($avg_student_progress / $nb_courses,2);
  196. $avg_student_score = round($avg_student_score / $nb_courses,2);
  197. $first_connection_date = Tracking::get_first_connection_date($a_infosUser['user_id']);
  198. if($first_connection_date==''){
  199. $first_connection_date=get_lang('NoConnexion');
  200. }
  201. $last_connection_date = Tracking::get_last_connection_date($a_infosUser['user_id'],true);
  202. if($last_connection_date==''){
  203. $last_connection_date=get_lang('NoConnexion');
  204. }
  205. $time_spent_on_the_platform = api_time_to_hms(Tracking::get_time_spent_on_the_platform($a_infosUser['user_id']));
  206. // cvs informations
  207. $csv_content[] = array(get_lang('Informations'));
  208. $csv_content[] = array(get_lang('Name'), get_lang('Email'), get_lang('Tel'));
  209. $csv_content[] = array($a_infosUser['name'], $a_infosUser['email'],$a_infosUser['phone']);
  210. $csv_content[] = array();
  211. // csv tracking
  212. $csv_content[] = array(get_lang('Tracking'));
  213. $csv_content[] = array(get_lang('FirstLogin'),get_lang('LatestLogin'), get_lang('TimeSpentOnThePlatform'), get_lang('Progress'), get_lang('Score'));
  214. $csv_content[] = array(strip_tags($first_connection_date),strip_tags($last_connection_date), $time_spent_on_the_platform , $avg_student_progress.' %',$avg_student_score.' %');
  215. ?>
  216. <a name="infosStudent"></a>
  217. <table class="data_table">
  218. <tr>
  219. <td class="border">
  220. <table width="100%" border="0" >
  221. <tr>
  222. <?php
  223. $image_array=UserManager::get_user_picture_path_by_id($a_infosUser['user_id'],'web',false, true);
  224. echo '<td class="borderRight" width="10%" valign="top">';
  225. echo '<img src="'.$image_array['dir'].$image_array['file'].'" border="1">';
  226. echo '</td>';
  227. ?>
  228. <td class="none" width="40%" valign="top">
  229. <table width="100%">
  230. <tr>
  231. <th>
  232. <?php echo get_lang('Informations'); ?>
  233. </th>
  234. </tr>
  235. <tr>
  236. <td class="none">
  237. <?php
  238. echo get_lang('Name').' : ';
  239. echo $a_infosUser['name'];
  240. ?>
  241. </td>
  242. </tr>
  243. <tr>
  244. <td class="none">
  245. <?php
  246. echo get_lang('Email').' : ';
  247. if(!empty($a_infosUser['email']))
  248. {
  249. echo '<a href="mailto:'.$a_infosUser['email'].'">'.$a_infosUser['email'].'</a>';
  250. }
  251. else
  252. {
  253. echo get_lang('NoEmail');
  254. }
  255. ?>
  256. </td>
  257. </tr>
  258. <tr>
  259. <td class="none">
  260. <?php
  261. echo get_lang('Tel').'. ';
  262. if(!empty($a_infosUser['phone']))
  263. {
  264. echo $a_infosUser['phone'];
  265. }
  266. else
  267. {
  268. echo get_lang('NoTel');
  269. }
  270. ?>
  271. </td>
  272. </tr>
  273. <tr>
  274. <td class="none">
  275. <?php
  276. echo get_lang('OnLine').' : ';
  277. echo $online;
  278. ?>
  279. </td>
  280. </tr>
  281. </table>
  282. </td>
  283. <td class="borderLeft" width="35%" valign="top">
  284. <table width="100%">
  285. <tr>
  286. <th>
  287. <?php echo get_lang('Tracking'); ?>
  288. </th>
  289. </tr>
  290. <tr>
  291. <td>
  292. <table>
  293. <tr>
  294. <td class="none" align="right">
  295. <?php echo get_lang('FirstLogin') ?>
  296. </td>
  297. <td class="none" align="left">
  298. <?php echo $first_connection_date ?>
  299. </td>
  300. </tr>
  301. <tr>
  302. <td class="none" align="right">
  303. <?php echo get_lang('LatestLogin') ?>
  304. </td>
  305. <td class="none" align="left">
  306. <?php echo $last_connection_date ?>
  307. </td>
  308. </tr>
  309. <tr>
  310. <td class="none" align="right">
  311. <?php echo get_lang('TimeSpentOnThePlatform') ?>
  312. </td>
  313. <td class="none" align="left">
  314. <?php echo $time_spent_on_the_platform ?>
  315. </td>
  316. </tr>
  317. <tr>
  318. <td class="none" align="right">
  319. <?php echo get_lang('Progress') ?>
  320. </td>
  321. <td class="none" align="left">
  322. <?php echo $avg_student_progress.' %' ?>
  323. </td>
  324. </tr>
  325. <tr>
  326. <td class="none" align="right">
  327. <?php echo get_lang('Score') ?>
  328. </td>
  329. <td class="none" align="left">
  330. <?php echo $avg_student_score.' %' ?>
  331. </td>
  332. </tr>
  333. </table>
  334. </td>
  335. </tr>
  336. </table>
  337. </td>
  338. <?php
  339. $sendMail = Display::encrypted_mailto_link($a_infosUser['email'], ' '.get_lang('SendMail'));
  340. ?>
  341. <td class="borderLeft" width="15%" valign="top">
  342. <table width="100%">
  343. <tr>
  344. <th>
  345. <?php echo get_lang('Actions'); ?>
  346. </th>
  347. </tr>
  348. <tr>
  349. <?php
  350. if(!empty($a_infosUser['email']))
  351. {
  352. echo "<td class='none'>";
  353. echo '<img align="absbottom" src="../img/send_mail.gif">&nbsp;'.$sendMail;
  354. echo "</td>";
  355. }
  356. else
  357. {
  358. echo "<td class='noLink none'>";
  359. echo '<img align="absbottom" src="../img/send_mail.gif">&nbsp; <strong> > '.get_lang('SendMail').'</strong>';
  360. echo "</td>";
  361. }
  362. ?>
  363. </tr>
  364. </table>
  365. </td>
  366. </tr>
  367. </table>
  368. </td>
  369. </tr>
  370. </table>
  371. <table class="data_table">
  372. <tr>
  373. <td colspan="5" style="border-width: 0px;">&nbsp;</td>
  374. </tr>
  375. <?php
  376. if(!empty($_GET['details']))
  377. {
  378. $a_infosCours = CourseManager :: get_course_information($_GET['course']);
  379. //get coach and session_name if there is one and if session_mode is activated
  380. if(api_get_setting('use_session_mode')=='true')
  381. {
  382. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  383. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  384. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  385. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  386. $sql = 'SELECT id_session
  387. FROM '.$tbl_session_course_user.' session_course_user
  388. WHERE session_course_user.id_user = '.intval($a_infosUser['user_id']).'
  389. AND session_course_user.course_code = "'.Database::escape_string($_GET['course']).'"
  390. ORDER BY id_session DESC';
  391. $rs = api_sql_query($sql,__FILE__,__LINE__);
  392. $le_session_id = intval(mysql_result($rs,0,0));
  393. if($le_session_id>0)
  394. {
  395. // get session name and coach of the session
  396. $sql = 'SELECT name, id_coach FROM '.$tbl_session.'
  397. WHERE id='.$le_session_id;
  398. $rs = api_sql_query($sql,__FILE__,__LINE__);
  399. $session_name = mysql_result($rs,0,'name');
  400. $session_coach_id = intval(mysql_result($rs,0,'id_coach'));
  401. // get coach of the course in the session
  402. $sql = 'SELECT id_coach FROM '.$tbl_session_course.'
  403. WHERE id_session='.$le_session_id.'
  404. AND course_code = "'.Database::escape_string($_GET['course']).'"';
  405. $rs = api_sql_query($sql,__FILE__,__LINE__);
  406. $session_course_coach_id = intval(mysql_result($rs,0,0));
  407. if($session_course_coach_id!=0)
  408. {
  409. $coach_infos = UserManager :: get_user_info_by_id($session_course_coach_id);
  410. $a_infosCours['tutor_name'] = $coach_infos['firstname'].' '.$coach_infos['lastname'];
  411. }
  412. else if($session_coach_id!=0)
  413. {
  414. $coach_infos = UserManager :: get_user_info_by_id($session_coach_id);
  415. $a_infosCours['tutor_name'] = $coach_infos['firstname'].' '.$coach_infos['lastname'];
  416. }
  417. }
  418. } // end if(api_get_setting('use_session_mode')=='true')
  419. $a_date_start = explode('-',$a_infosCours['date_start']);
  420. $date_start = $a_date_start[2].'/'.$a_date_start[1].'/'.$a_date_start[0];
  421. $a_date_end = explode('-',$a_infosCours['date_end']);
  422. $date_end = $a_date_end[2].'/'.$a_date_end[1].'/'.$a_date_end[0];
  423. $dateSession = get_lang('From').' '.$date_start.' '.get_lang('To').' '.$date_end;
  424. $nb_login = Tracking :: count_login_per_student($a_infosUser['user_id'], $_GET['course']);
  425. $tableTitle = $a_infosCours['title'].'&nbsp;|&nbsp;'.get_lang('CountToolAccess').' : '.$nb_login.'&nbsp; | &nbsp;'.get_lang('Tutor').' : '.stripslashes($a_infosCours['tutor_name']).((!empty($session_name)) ? ' | '.get_lang('Session').' : '.$session_name : '');
  426. $csv_content[] = array();
  427. $csv_content[] = array(str_replace('&nbsp;','',$tableTitle));
  428. ?>
  429. <tr class="tableName">
  430. <td colspan="6">
  431. <strong><?php echo $tableTitle; ?></strong>
  432. </td>
  433. </tr>
  434. <tr> <!-- line about learnpaths -->
  435. <td>
  436. <table class="data_table">
  437. <tr>
  438. <th>
  439. <?php echo get_lang('Learnpaths'); ?>
  440. </th>
  441. <th>
  442. <?php echo get_lang('Time'); ?>
  443. </th>
  444. <th>
  445. <?php echo get_lang('Score'); ?>
  446. </th>
  447. <th>
  448. <?php echo get_lang('Progress'); ?>
  449. </th>
  450. <th>
  451. <?php echo get_lang('LastConnexion'); ?>
  452. </th>
  453. <th>
  454. <?php echo get_lang('Details'); ?>
  455. </th>
  456. </tr>
  457. <?php
  458. $a_headerLearnpath = array(get_lang('Learnpath'),get_lang('Time'),get_lang('Progress'),get_lang('LastConnexion'));
  459. $sqlLearnpath = " SELECT lp.name,lp.id
  460. FROM ".$a_infosCours['db_name'].".".$tbl_course_lp." AS lp ORDER BY lp.name ASC
  461. ";
  462. $resultLearnpath = api_sql_query($sqlLearnpath);
  463. $csv_content[] = array();
  464. $csv_content[] = array(get_lang('Learnpath'),get_lang('Time'),get_lang('Score'),get_lang('Progress'),get_lang('LastConnexion'));
  465. if(mysql_num_rows($resultLearnpath)>0)
  466. {
  467. $i = 0;
  468. while($a_learnpath = mysql_fetch_array($resultLearnpath))
  469. {
  470. $progress = learnpath :: get_db_progress($a_learnpath['id'],$student_id, '%',$a_infosCours['db_name']);
  471. // calculates time
  472. $sql = 'SELECT SUM(total_time)
  473. FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view_item.' AS item_view
  474. INNER JOIN '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view.' AS view
  475. ON item_view.lp_view_id = view.id
  476. AND view.lp_id = '.$a_learnpath['id'].'
  477. AND view.user_id = '.intval($_GET['student']);
  478. $rs = api_sql_query($sql, __FILE__, __LINE__);
  479. $total_time = mysql_result($rs, 0, 0);
  480. // calculates last connection time
  481. $sql = 'SELECT MAX(start_time)
  482. FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view_item.' AS item_view
  483. INNER JOIN '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view.' AS view
  484. ON item_view.lp_view_id = view.id
  485. AND view.lp_id = '.$a_learnpath['id'].'
  486. AND view.user_id = '.intval($_GET['student']);
  487. $rs = api_sql_query($sql, __FILE__, __LINE__);
  488. $start_time = mysql_result($rs, 0, 0);
  489. //QUIZZ IN LP
  490. $sql = 'SELECT id as item_id, max_score
  491. FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_item.' AS lp_item
  492. WHERE lp_id='.$a_learnpath['id'].'
  493. AND item_type="quiz"';
  494. $rsItems = api_sql_query($sql, __FILE__, __LINE__);
  495. //We get the last view id of this LP
  496. $sql='SELECT max(id) as id FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($_GET['student']).'"';
  497. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  498. $lp_view_id = mysql_result($rs_last_lp_view_id,0,'id');
  499. $total_score = $total_weighting = 0;
  500. while($item = Database :: fetch_array($rsItems, 'ASSOC'))
  501. {
  502. $sql = 'SELECT score as student_score
  503. FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view_item.' as lp_view_item
  504. WHERE lp_view_item.lp_item_id = '.$item['item_id'].'
  505. AND lp_view_id = "'.$lp_view_id.'"
  506. ';
  507. $rsScores = api_sql_query($sql, __FILE__, __LINE__);
  508. $total_score += mysql_result($rsScores, 0, 0);
  509. $total_weighting += $item['max_score'];
  510. }
  511. $sql = 'SELECT id, max_score
  512. FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_item.' AS lp_item
  513. WHERE lp_id='.$a_learnpath['id'].'
  514. AND item_type="sco" LIMIT 1';
  515. $rs_lp_item_id_scorm = api_sql_query($sql, __FILE__, __LINE__);
  516. if(mysql_num_rows($rs_lp_item_id_scorm)>0){
  517. $lp_item_id = mysql_result($rs_lp_item_id_scorm,0,'id');
  518. $lp_item__max_score = mysql_result($rs_lp_item_id_scorm,0,'max_score');
  519. $total_weighting+=$lp_item__max_score;
  520. //We get the last view id of this LP
  521. $sql='SELECT max(id) as id FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($_GET['student']).'"';
  522. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  523. $lp_view_id = mysql_result($rs_last_lp_view_id,0,'id');
  524. $sql='SELECT SUM(score)/count(lp_item_id) as score FROM '.$a_infosCours['db_name'].'.'.$tbl_course_lp_view_item.' WHERE lp_view_id="'.$lp_view_id.'" GROUP BY lp_view_id';
  525. $rs_score = api_sql_query($sql, __FILE__, __LINE__);
  526. $lp_scorm_score = mysql_result($rs_score,0,'score');
  527. $total_score+=$lp_scorm_score;
  528. }
  529. $score = 0;
  530. if($total_weighting != 0)
  531. {
  532. $score = round($total_score / $total_weighting * 100,2);
  533. }
  534. if($i%2==0){
  535. $s_css_class="row_odd";
  536. }
  537. else{
  538. $s_css_class="row_even";
  539. }
  540. $i++;
  541. $csv_content[] = array(stripslashes($a_learnpath['name']),api_time_to_hms($total_time),$score.'%',$progress,date('Y-m-d',$start_time));
  542. ?>
  543. <tr class="<?php echo $s_css_class;?>">
  544. <td>
  545. <?php echo stripslashes($a_learnpath['name']); ?>
  546. </td>
  547. <td align="right">
  548. <?php echo api_time_to_hms($total_time) ?>
  549. </td>
  550. <td align="right">
  551. <?php echo $total_weighting>0 ? $score.'%' : '-' ?>
  552. </td>
  553. <td align="right">
  554. <?php echo $progress ?>
  555. </td>
  556. <td align="center">
  557. <?php if($start_time!='' && $start_time>0) echo format_locale_date(get_lang('DateFormatLongWithoutDay'),$start_time); else echo '-'; ?>
  558. </td>
  559. <td align="center">
  560. <?php
  561. if($progress > 0 || $score > 0)
  562. {
  563. ?>
  564. <a href="lp_tracking.php?course=<?php echo $_GET['course'] ?>&origin=<?php echo $_GET['origin'] ?>&lp_id=<?php echo $a_learnpath['id']?>&student_id=<?php echo $a_infosUser['user_id'] ?>">
  565. <img src="../img/2rightarrow.gif" border="0" />
  566. </a>
  567. <?php
  568. }
  569. ?>
  570. </td>
  571. </tr>
  572. <?php
  573. $dataLearnpath[$i][] = $a_learnpath['name'];
  574. $dataLearnpath[$i][] = $progress.'%';
  575. $i++;
  576. }
  577. }
  578. else
  579. {
  580. echo " <tr>
  581. <td colspan='6'>
  582. ".get_lang('NoLearnpath')."
  583. </td>
  584. </tr>
  585. ";
  586. }
  587. ?>
  588. </table>
  589. </td>
  590. </tr>
  591. <tr> <!-- line about exercises -->
  592. <td>
  593. <table class="data_table">
  594. <tr>
  595. <th>
  596. <?php echo get_lang('Exercices'); ?>
  597. </th>
  598. <th>
  599. <?php echo get_lang('Score') ?>
  600. </th>
  601. <th>
  602. <?php echo get_lang('Attempts'); ?>
  603. </th>
  604. <th>
  605. <?php echo get_lang('CorrectTest'); ?>
  606. </th>
  607. </tr>
  608. <?php
  609. $csv_content[] = array();
  610. $csv_content[] = array(get_lang('Exercices'),get_lang('Score'),get_lang('Attempts'));
  611. $a_infosCours = CourseManager :: get_course_information($_GET['course']);
  612. $sql='SELECT visibility FROM '.$a_infosCours['db_name'].'.'.TABLE_TOOL_LIST.' WHERE name="quiz"';
  613. $resultVisibilityQuizz = api_sql_query($sql);
  614. if(mysql_result($resultVisibilityQuizz,0,'visibility')==1){
  615. $sqlExercices = " SELECT quiz.title,id
  616. FROM ".$a_infosCours['db_name'].".".$tbl_course_quiz." AS quiz
  617. WHERE active='1' ORDER BY quiz.title ASC
  618. ";
  619. $resultExercices = api_sql_query($sqlExercices);
  620. $i = 0;
  621. if(mysql_num_rows($resultExercices)>0)
  622. {
  623. while($a_exercices = mysql_fetch_array($resultExercices))
  624. {
  625. $sqlEssais = " SELECT COUNT(ex.exe_id) as essais
  626. FROM $tbl_stats_exercices AS ex
  627. WHERE ex.exe_cours_id = '".$a_infosCours['code']."'
  628. AND ex.exe_exo_id = ".$a_exercices['id']."
  629. AND exe_user_id='".$_GET["student"]."'"
  630. ;
  631. $resultEssais = api_sql_query($sqlEssais);
  632. $a_essais = mysql_fetch_array($resultEssais);
  633. $sqlScore = "SELECT exe_id, exe_result,exe_weighting
  634. FROM $tbl_stats_exercices
  635. WHERE exe_user_id = ".$_GET['student']."
  636. AND exe_cours_id = '".$a_infosCours['code']."'
  637. AND exe_exo_id = ".$a_exercices['id']."
  638. ORDER BY exe_date DESC LIMIT 1";
  639. $resultScore = api_sql_query($sqlScore);
  640. $score = 0;
  641. while($a_score = mysql_fetch_array($resultScore))
  642. {
  643. $score = $score + $a_score['exe_result'];
  644. $weighting = $weighting + $a_score['exe_weighting'];
  645. $exe_id = $a_score['exe_id'];
  646. }
  647. $pourcentageScore = 0;
  648. if($weighting!=0)
  649. {
  650. $pourcentageScore = round(($score*100)/$weighting);
  651. }
  652. $weighting = 0;
  653. $csv_content[] = array($a_exercices['title'], $pourcentageScore.' %', $a_essais['essais']);
  654. if($i%2==0){
  655. $s_css_class="row_odd";
  656. }
  657. else{
  658. $s_css_class="row_even";
  659. }
  660. $i++;
  661. echo "<tr class='$s_css_class'>
  662. <td>
  663. ";
  664. echo $a_exercices['title'];
  665. echo " </td>
  666. ";
  667. echo " <td align='right'>
  668. ";
  669. echo $pourcentageScore.' %';
  670. echo " </td>
  671. <td align='right'>
  672. ";
  673. echo $a_essais['essais'];
  674. echo " </td>
  675. <td align='center'>
  676. ";
  677. $sql_last_attempt='SELECT exe_id FROM '.$tbl_stats_exercices.' WHERE exe_exo_id="'.$a_exercices['id'].'" AND exe_user_id="'.$_GET['student'].'" AND exe_cours_id="'.$a_infosCours['code'].'" ORDER BY exe_date DESC LIMIT 1';
  678. $resultLastAttempt = api_sql_query($sql_last_attempt);
  679. if(Database::num_rows($resultLastAttempt)>0)
  680. {
  681. $id_last_attempt=mysql_result($resultLastAttempt,0,0);
  682. if($a_essais['essais']>0)
  683. echo '<a href="../exercice/exercise_show.php?id='.$id_last_attempt.'&cidReq='.$a_infosCours['code'].'&student='.$_GET['student'].'&origin='.(empty($_GET['origin']) ? 'tracking' : $_GET['origin']).'"> <img src="'.api_get_path(WEB_IMG_PATH).'quiz.gif" border="0"> </a>';
  684. }
  685. echo " </td>
  686. </tr>
  687. ";
  688. $dataExercices[$i][] = $a_exercices['title'];
  689. $dataExercices[$i][] = $pourcentageScore.'%';
  690. $dataExercices[$i][] = $a_essais['essais'];
  691. //$dataExercices[$i][] = corrections;
  692. $i++;
  693. }
  694. }
  695. else
  696. {
  697. echo " <tr>
  698. <td colspan='6'>
  699. ".get_lang('NoExercise')."
  700. </td>
  701. </tr>
  702. ";
  703. }
  704. }
  705. else
  706. {
  707. echo " <tr>
  708. <td colspan='6'>
  709. ".get_lang('NoExercise')."
  710. </td>
  711. </tr>
  712. ";
  713. }
  714. ?>
  715. </table>
  716. </td>
  717. </tr>
  718. <tr><!-- line about other tools -->
  719. <td>
  720. <table class="data_table">
  721. <?php
  722. $csv_content[] = array();
  723. $nb_assignments = Tracking :: count_student_assignments($a_infosUser['user_id'], $a_infosCours['code']);
  724. $messages = Tracking :: count_student_messages($a_infosUser['user_id'], $a_infosCours['code']);
  725. $links = Tracking :: count_student_visited_links($a_infosUser['user_id'], $a_infosCours['code']);
  726. $documents = Tracking :: count_student_downloaded_documents($a_infosUser['user_id'], $a_infosCours['code']);
  727. $csv_content[] = array(get_lang('Student_publication'), $nb_assignments);
  728. $csv_content[] = array(get_lang('Messages'), $messages);
  729. $csv_content[] = array(get_lang('LinksDetails'), $links);
  730. $csv_content[] = array(get_lang('DocumentsDetails'), $documents);
  731. ?>
  732. <tr>
  733. <th colspan="2">
  734. <?php echo get_lang('OtherTools'); ?>
  735. </th>
  736. </tr>
  737. <tr><!-- assignments -->
  738. <td width="40%">
  739. <?php echo get_lang('Student_publication') ?>
  740. </td>
  741. <td>
  742. <?php echo $nb_assignments ?>
  743. </td>
  744. </tr>
  745. <tr><!-- messages -->
  746. <td>
  747. <?php echo get_lang('Messages') ?>
  748. </td>
  749. <td>
  750. <?php echo $messages ?>
  751. </td>
  752. </tr>
  753. <tr><!-- links -->
  754. <td>
  755. <?php echo get_lang('LinksDetails') ?>
  756. </td>
  757. <td>
  758. <?php echo $links ?>
  759. </td>
  760. </tr>
  761. <tr><!-- documents -->
  762. <td>
  763. <?php echo get_lang('DocumentsDetails') ?>
  764. </td>
  765. <td>
  766. <?php echo $documents ?>
  767. </td>
  768. </tr>
  769. </table>
  770. </td>
  771. </tr>
  772. </table>
  773. <?php
  774. }
  775. else
  776. {
  777. ?>
  778. <tr>
  779. <th>
  780. <?php echo get_lang('Course'); ?>
  781. </th>
  782. <th>
  783. <?php echo get_lang('Time'); ?>
  784. </th>
  785. <th>
  786. <?php echo get_lang('Progress'); ?>
  787. </th>
  788. <th>
  789. <?php echo get_lang('Score'); ?>
  790. </th>
  791. <th>
  792. <?php echo get_lang('Details'); ?>
  793. </th>
  794. </tr>
  795. <?php
  796. if(!api_is_platform_admin() && $_user['status']!=DRH){
  797. // courses followed by user where we are coach
  798. if(!isset($_GET['id_coach'])){
  799. $a_courses = Tracking :: get_courses_followed_by_coach($_user['user_id']);
  800. }
  801. else{
  802. $a_courses = Tracking :: get_courses_followed_by_coach($_GET['id_coach']);
  803. }
  804. }
  805. if(count($a_courses)>0)
  806. {
  807. $csv_content[] = array();
  808. $csv_content[] = array(get_lang('Course'),get_lang('Time'),get_lang('Progress'),get_lang('Score'));
  809. foreach($a_courses as $course_code)
  810. {
  811. if(CourseManager :: is_user_subscribed_in_course($student_id,$course_code, true)){
  812. $course_infos = CourseManager :: get_course_information($course_code);
  813. $time_spent_on_course = api_time_to_hms(Tracking :: get_time_spent_on_the_course($a_infosUser['user_id'], $course_code));
  814. $progress = Tracking :: get_avg_student_progress($a_infosUser['user_id'], $course_code).' %';
  815. $score = Tracking :: get_avg_student_score($a_infosUser['user_id'], $course_code).' %';
  816. $csv_content[] = array($course_infos['title'], $time_spent_on_course, $progress, $score);
  817. echo '
  818. <tr>
  819. <td align="right">
  820. '.$course_infos['title'].'
  821. </td>
  822. <td align="right">
  823. '.$time_spent_on_course.'
  824. </td>
  825. <td align="right">
  826. '.$progress.'
  827. </td>
  828. <td align="right">
  829. '.$score.'
  830. </td>';
  831. if(isset($_GET['id_coach']) && intval($_GET['id_coach'])!=0){
  832. echo '<td align="center" width="10">
  833. <a href="'.api_get_self().'?student='.$a_infosUser['user_id'].'&details=true&course='.$course_infos['code'].'&id_coach='.$_GET['id_coach'].'#infosStudent"><img src="'.api_get_path(WEB_IMG_PATH).'2rightarrow.gif" border="0" /></a>
  834. </td>';
  835. }
  836. else{
  837. echo '<td align="center" width="10">
  838. <a href="'.api_get_self().'?student='.$a_infosUser['user_id'].'&details=true&course='.$course_infos['code'].'#infosStudent"><img src="'.api_get_path(WEB_IMG_PATH).'2rightarrow.gif" border="0" /></a>
  839. </td>';
  840. }
  841. echo '</tr>';
  842. }
  843. }
  844. }
  845. else
  846. {
  847. echo "<tr>
  848. <td colspan='5'>
  849. ".get_lang('NoCourse')."
  850. </td>
  851. </tr>
  852. ";
  853. }
  854. }//end of else !empty($details)
  855. ?>
  856. </table>
  857. <br />
  858. <?php
  859. if(!empty($_GET['details']) && $_GET['origin'] != 'tracking_course' && $_GET['origin'] != 'user_course')
  860. {
  861. ?>
  862. <br /><br />
  863. <?php
  864. }
  865. if(!empty($_GET['exe_id']))
  866. {
  867. $sqlExerciceDetails = " SELECT qq.question, qq.ponderation, qq.id
  868. FROM ".$a_infosCours['db_name'].".".$course_quiz_question." as qq
  869. INNER JOIN ".$a_infosCours['db_name'].".".$course_quiz_rel_question." as qrq
  870. ON qrq.question_id = qq.id
  871. AND qrq.exercice_id = ".$_GET['exe_id']
  872. ;
  873. $resultExerciceDetails = api_sql_query($sqlExerciceDetails);
  874. $sqlExName = " SELECT quiz.title
  875. FROM ".$a_infosCours['db_name'].".".$tbl_course_quiz." AS quiz
  876. WHERE quiz.id = ".$_GET['exe_id']
  877. ;
  878. $resultExName = api_sql_query($sqlExName);
  879. $a_exName = mysql_fetch_array($resultExName);
  880. echo "<table class='data_table'>
  881. <tr>
  882. <th colspan='2'>
  883. ".$a_exName['title']."
  884. </th>
  885. </tr>
  886. ";
  887. while($a_exerciceDetails = mysql_fetch_array($resultExerciceDetails))
  888. {
  889. $sqlAnswer = " SELECT qa.comment, qa.answer
  890. FROM ".$a_infosCours['db_name'].".".$course_quiz_answer." as qa
  891. WHERE qa.question_id = ".$a_exerciceDetails['id']
  892. ;
  893. $resultAnswer = api_sql_query($sqlAnswer);
  894. echo "<a name='infosExe'></a>";
  895. echo"
  896. <tr>
  897. <td colspan='2'>
  898. <strong>".$a_exerciceDetails['question'].' /'.$a_exerciceDetails['ponderation']."</strong>
  899. </td>
  900. </tr>
  901. ";
  902. while($a_answer = mysql_fetch_array($resultAnswer))
  903. {
  904. echo"
  905. <tr>
  906. <td>
  907. ".$a_answer['answer']."
  908. </td>
  909. <td>
  910. ";
  911. if(!empty($a_answer['comment']))
  912. echo $a_answer['comment'];
  913. else
  914. echo get_lang('NoComment');
  915. echo "
  916. </td>
  917. </tr>
  918. ";
  919. }
  920. }
  921. echo "</table>";
  922. }
  923. //YW - commented out because it doesn't seem to be used
  924. //$a_header = array_merge($a_headerLearnpath,$a_headerExercices,$a_headerProductions);
  925. }
  926. if($export_csv)
  927. {
  928. ob_end_clean();
  929. Export :: export_table_csv($csv_content, 'reporting_student');
  930. }
  931. /*
  932. ==============================================================================
  933. FOOTER
  934. ==============================================================================
  935. */
  936. Display::display_footer();
  937. ?>