exams.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Exams script
  5. * @package chamilo.tracking
  6. */
  7. /**
  8. * Code
  9. */
  10. $language_file = array ('registration', 'index', 'tracking', 'exercice','survey');
  11. require_once '../inc/global.inc.php';
  12. require_once api_get_path(LIBRARY_PATH).'course.lib.php';
  13. require_once api_get_path(LIBRARY_PATH).'formvalidator/FormValidator.class.php';
  14. require_once api_get_path(LIBRARY_PATH).'pear/Spreadsheet_Excel_Writer/Writer.php';
  15. $this_section = SECTION_TRACKING;
  16. $is_allowedToTrack = $is_courseAdmin || $is_platformAdmin || $is_courseCoach || $is_sessionAdmin;
  17. if(!$is_allowedToTrack) {
  18. Display :: display_header(null);
  19. api_not_allowed();
  20. Display :: display_footer();
  21. }
  22. $export_to_xls = false;
  23. if (isset($_GET['export'])) {
  24. $export_to_xls = true;
  25. }
  26. $TBL_EXERCICES = Database::get_course_table(TABLE_QUIZ_TEST);
  27. $tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  28. if (api_is_platform_admin() ) {
  29. $global = true;
  30. } else {
  31. $global = false;
  32. }
  33. if ($global) {
  34. $temp_course_list = CourseManager :: get_courses_list();
  35. foreach($temp_course_list as $temp_course_item) {
  36. $course_item = CourseManager ::get_course_information($temp_course_item['code']);
  37. $course_list[]= array('db_name' =>$course_item['db_name'],'code'=>$course_item['code'], 'title'=>$course_item['title']);
  38. }
  39. } else {
  40. $current_course['db_name'] = $_course['dbName'];
  41. $current_course['code'] = $_course['id'];
  42. $course_list = array($current_course);
  43. }
  44. $form = new FormValidator('search_simple','POST','','',null,false);
  45. $form->addElement('text','score',get_lang('Percentage'));
  46. if ($global) {
  47. $form->addElement('hidden','view','admin');
  48. } else {
  49. //Get exam lists
  50. $t_quiz = Database::get_course_table(TABLE_QUIZ_TEST,$_course['db_name']);
  51. $sqlExercices = " SELECT quiz.title,id FROM ".$t_quiz." AS quiz WHERE active='1' ORDER BY quiz.title ASC";
  52. $resultExercices = Database::query($sqlExercices);
  53. $exercise_list[0] = get_lang('All');
  54. while($a_exercices = Database::fetch_array($resultExercices)) {
  55. $exercise_list[$a_exercices['id']] = $a_exercices['title'];
  56. }
  57. $form->addElement('select', 'exercise_id', get_lang('Exercise'), $exercise_list);
  58. }
  59. //$form->addElement('submit','submit',get_lang('Filter'));
  60. $form->addElement('style_submit_button','submit', get_lang('Filter'),'class="search"' );
  61. if (!empty($_REQUEST['score'])) $filter_score = intval($_REQUEST['score']); else $filter_score = 70;
  62. if (!empty($_REQUEST['exercise_id'])) $exercise_id = intval($_REQUEST['exercise_id']); else $exercise_id = 0;
  63. $form->setDefaults(array('score'=>$filter_score));
  64. if (!$export_to_xls) {
  65. Display :: display_header(get_lang('Reporting'));
  66. echo '<div class="actions">';
  67. if ($global) {
  68. echo '<a href="'.api_get_path(WEB_CODE_PATH).'auth/my_progress.php">'.
  69. Display::return_icon('stats.png', get_lang('MyStats'),'','32');
  70. echo '</a>';
  71. echo '<span style="float:right">';
  72. echo '<a href="'.api_get_self().'?export=1&score='.$filter_score.'&exercise_id='.$exercise_id.'">
  73. '.Display::return_icon('export_excel.png',get_lang('ExportAsXLS'),'','32').'</a>';
  74. echo '</span>';
  75. echo '<a href="javascript: void(0);" onclick="javascript: window.print()">
  76. '.Display::return_icon('printer.png',get_lang('Print'),'','32').'</a>';
  77. $menu_items[] = '<a href="'.api_get_path(WEB_CODE_PATH).'mySpace/?view=teacher">'.get_lang('TeacherInterface').'</a>';
  78. if (api_is_platform_admin()) {
  79. $menu_items[] = '<a href="'.api_get_path(WEB_CODE_PATH).'mySpace/?view=admin">'.get_lang('AdminInterface').'</a>';
  80. } else {
  81. $menu_items[] = '<a href="'.api_get_path(WEB_CODE_PATH).'mySpace/?view=coach">'.get_lang('AdminInterface').'</a>';
  82. }
  83. $menu_items[] = get_lang('ExamTracking');
  84. $nb_menu_items = count($menu_items);
  85. if($nb_menu_items>1) {
  86. foreach($menu_items as $key=> $item) {
  87. echo $item;
  88. if($key!=$nb_menu_items-1) {
  89. echo ' | ';
  90. }
  91. }
  92. }
  93. } else {
  94. echo '<a href="courseLog.php?'.api_get_cidreq().'&studentlist=true">'.get_lang('StudentsTracking').'</a>&nbsp;|
  95. <a href="courseLog.php?'.api_get_cidreq().'&studentlist=false">'.get_lang('CourseTracking').'</a>&nbsp;|&nbsp';
  96. echo '<a href="courseLog.php?'.api_get_cidreq().'&studentlist=resources">'.get_lang('ResourcesTracking').'</a>';
  97. echo ' | '.get_lang('ExamTracking').'';
  98. echo '<a href="'.api_get_self().'?export=1&score='.$filter_score.'&exercise_id='.$exercise_id.'"><img align="absbottom" src="../img/excel.gif">&nbsp;'.get_lang('ExportAsXLS').'</a>';
  99. }
  100. echo '</div>';
  101. $form->display();
  102. echo '<h3>'.sprintf(get_lang('FilteringWithScoreX'), $filter_score).'%</h3>';
  103. }
  104. if ($global) {
  105. $html_result .= '<table class="data_table">';
  106. $html_result .= '<tr><th>'.get_lang('Courses').'</th>';
  107. $html_result .= '<th>'.get_lang('Exercises').'</th>';
  108. $html_result .= '<th>'.get_lang('ExamTaken').'</th>';
  109. $html_result .= '<th>'.get_lang('ExamNotTaken').'</th>';
  110. $html_result .= '<th>'.sprintf(get_lang('ExamPassX'), $filter_score).'%</th>';
  111. $html_result .= '<th>'.get_lang('ExamFail').'</th>';
  112. $html_result .= '<th>'.get_lang('TotalStudents').'</th>';
  113. $html_result .= '</tr>';
  114. } else {
  115. $html_result .= '<table class="data_table">';
  116. $html_result .= '<tr><th>'.get_lang('Exercises').'</th>';
  117. $html_result .= '<th>'.get_lang('User').'</th>';
  118. //$html_result .= '<th>'.sprintf(get_lang('ExamPassX'), $filter_score).'</th>';
  119. $html_result .= '<th>'.get_lang('Percentage').' %</th>';
  120. $html_result .= '<th>'.get_lang('Status').'</th>';
  121. $html_result .= '<th>'.get_lang('Attempts').'</th>';
  122. $html_result .= '</tr>';
  123. }
  124. $export_array_global = $export_array = array();
  125. if(!empty($course_list) && is_array($course_list))
  126. foreach($course_list as $current_course ) {
  127. $global_row = $row_not_global = array();
  128. $a_students = CourseManager :: get_student_list_from_course_code($current_course['code'], false);
  129. $total_students = count($a_students);
  130. $t_quiz = Database::get_course_table(TABLE_QUIZ_TEST,$current_course['db_name']);
  131. $sqlExercices = "SELECT count(id) as count FROM ".$t_quiz." AS quiz WHERE active='1' ";
  132. $resultExercices = Database::query($sqlExercices);
  133. $data_exercises = Database::store_result($resultExercices);
  134. $exercise_count = $data_exercises[0]['count'];
  135. if ($global) {
  136. if ($exercise_count == 0) {
  137. $exercise_count = 2;
  138. }
  139. $html_result .= "<tr class='$s_css_class'>
  140. <td rowspan=$exercise_count>";
  141. $html_result .= $current_course['title'];
  142. $html_result .= "</td>";
  143. }
  144. $sql='SELECT visibility FROM '.$current_course['db_name'].'.'.TABLE_TOOL_LIST.' WHERE name="quiz" ';
  145. $resultVisibilityQuizz = Database::query($sql);
  146. if (Database::result($resultVisibilityQuizz, 0 ,'visibility') == 1) {
  147. $sqlExercices = " SELECT quiz.title,id FROM ".$t_quiz." AS quiz WHERE active='1' ORDER BY quiz.title ASC";
  148. //Getting the exam list
  149. if (!$global) {
  150. if (!empty($exercise_id)) {
  151. $sqlExercices = " SELECT quiz.title,id FROM ".$t_quiz." AS quiz WHERE active='1' AND id = $exercise_id ORDER BY quiz.title ASC";
  152. }
  153. }
  154. $resultExercices = Database::query($sqlExercices);
  155. $i = 0;
  156. if (Database::num_rows($resultExercices) > 0) {
  157. while($a_exercices = Database::fetch_array($resultExercices)) {
  158. $global_row[]= $current_course['title'];
  159. if (!$global) {
  160. $html_result .= "<tr class='$s_css_class'>";
  161. }
  162. if (!$global) {
  163. $html_result .= '<td ROWSPAN="'.$total_students.'">';
  164. } else {
  165. $html_result .= '<td>';
  166. }
  167. $html_result .= $a_exercices['title'];
  168. $html_result .= '</td>';
  169. $global_row[]=$a_exercices['title'];
  170. $row_not_global['exercise']= $a_exercices['title'];
  171. $taken = 0;
  172. $total_with_parameter = 0;
  173. $fail = 0;
  174. $not_taken = 0;
  175. $total_with_parameter_score = 0;
  176. $total_with_parameter_porcentage = 0;
  177. $student_result = array();
  178. foreach ($a_students as $student ) {
  179. $current_student_id = $student['user_id'];
  180. $sqlEssais = " SELECT COUNT(ex.exe_id) as essais
  181. FROM $tbl_stats_exercices AS ex
  182. WHERE ex.exe_cours_id = '".$current_course['code']."'
  183. AND ex.exe_exo_id = ".$a_exercices['id']."
  184. AND exe_user_id='".$current_student_id."'";
  185. $resultEssais = Database::query($sqlEssais);
  186. $a_essais = Database::fetch_array($resultEssais);
  187. $sqlScore = "SELECT exe_id, exe_result,exe_weighting
  188. FROM $tbl_stats_exercices
  189. WHERE exe_user_id = ".$current_student_id."
  190. AND exe_cours_id = '".$current_course['code']."'
  191. AND exe_exo_id = ".$a_exercices['id']."
  192. ORDER BY exe_result DESC LIMIT 1"; // we take the higher value
  193. //ORDER BY exe_date DESC LIMIT 1";
  194. $resultScore = Database::query($sqlScore);
  195. $score = 0;
  196. while($a_score = Database::fetch_array($resultScore)) {
  197. $score = $score + $a_score['exe_result'];
  198. $weighting = $weighting + $a_score['exe_weighting'];
  199. $exe_id = $a_score['exe_id'];
  200. }
  201. $pourcentageScore = 0;
  202. if ($weighting!=0) {
  203. $pourcentageScore = round(($score*100)/$weighting);
  204. }
  205. $weighting = 0;
  206. if($i%2==0){
  207. $s_css_class="row_odd";
  208. } else {
  209. $s_css_class="row_even";
  210. }
  211. $i++;
  212. /*echo " <td align='right'>
  213. ";
  214. echo $current_student_id.' ';
  215. echo " </td>";
  216. */
  217. //var_dump($pourcentageScore);
  218. /* echo " <td align='right'>
  219. ";
  220. echo $pourcentageScore.' %';
  221. echo " </td>";
  222. echo "<td align='right'>
  223. ";
  224. /*
  225. echo $a_essais['essais'];
  226. echo " </td>
  227. <td align='center'>
  228. ";*/
  229. if ($a_essais['essais'] > 0 ) {
  230. $taken++;
  231. }
  232. if ($pourcentageScore >= $parameter_porcentage) {
  233. $total_with_parameter_porcentage++;
  234. }
  235. if ($pourcentageScore >= $filter_score) {
  236. $total_with_parameter_score++;
  237. }
  238. /*
  239. $sql_last_attempt='SELECT exe_id FROM '.$tbl_stats_exercices.' WHERE exe_exo_id="'.$a_exercices['id'].'" AND exe_user_id="'.$current_student_id.'" AND exe_cours_id="'.$current_course['code'].'" ORDER BY exe_date DESC LIMIT 1';
  240. $resultLastAttempt = Database::query($sql_last_attempt);
  241. if(Database::num_rows($resultLastAttempt)>0) {
  242. $id_last_attempt=Database::result($resultLastAttempt,0,0);
  243. if($a_essais['essais']>0) {
  244. /// echo '<a href="../exercice/exercise_show.php?id='.$id_last_attempt.'&cidReq='.$current_course['code'].'&student='.$current_student_id.'&origin='.(empty($_GET['origin']) ? 'tracking' : $_GET['origin']).'"> <img src="'.api_get_path(WEB_IMG_PATH).'quiz.gif" border="0"> </a>';
  245. }
  246. }
  247. */
  248. if (!$global) {
  249. $user_info = api_get_user_info($current_student_id);
  250. //User
  251. $user_row = '<td align="center">';
  252. $user_row .= $user_info['firstName'].' '.$user_info['lastName'];
  253. $user_row .= '</td>';
  254. $user_info = $user_info['firstName'].' '.$user_info['lastName'];
  255. //Best result
  256. if (!empty($a_essais['essais'])) {
  257. $user_row .= '<td align="center" >';
  258. $user_row .= $pourcentageScore;
  259. $temp_array [] = $pourcentageScore;
  260. $user_row .= '</td>';
  261. if ($pourcentageScore >= $filter_score ) {
  262. $user_row .= '<td align="center" style="background-color:#DFFFA8">';
  263. $user_row .= get_lang('PassExam').'</td>';
  264. $temp_array [] = get_lang('PassExam');
  265. } else {
  266. $user_row .= '<td align="center" style="background-color:#FC9A9E" >';
  267. $user_row .= get_lang('ExamFail').'</td>';
  268. $temp_array [] = get_lang('ExamFail');
  269. }
  270. $user_row .= '<td align="center">';
  271. $user_row .= $a_essais['essais'];
  272. $temp_array [] = $a_essais['essais'];
  273. $user_row .= '</td>';
  274. } else {
  275. $score = '-';
  276. $user_row .= '<td align="center" >';
  277. $user_row .= '-';
  278. $temp_array [] = '-';
  279. $user_row .= '</td>';
  280. $user_row .= '<td align="center" style="background-color:#FCE89A">';
  281. $user_row .= get_lang('NoAttempt');
  282. $temp_array [] = get_lang('NoAttempt');
  283. $user_row .= '</td>';
  284. $user_row .= '<td align="center">';
  285. $user_row .= 0;
  286. $temp_array [] = 0;
  287. $user_row .= '</td>';
  288. }
  289. $user_row .= '</tr>';
  290. $student_result[$current_student_id] = array('html'=>$user_row,'score'=>$score,'array'=>$temp_array,'user'=>$user_info);
  291. $temp_array = array();
  292. }
  293. }
  294. if (!$global) {
  295. if (!empty($student_result)) {
  296. $student_result_empty = $student_result_content = array();
  297. foreach($student_result as $row) {
  298. if ($row['score'] == '-') {
  299. $student_result_empty[] = $row;
  300. } else {
  301. $student_result_content[] = $row;
  302. }
  303. }
  304. //Sort only users with content
  305. usort($student_result_content, 'sort_user');
  306. $student_result = array_merge($student_result_content, $student_result_empty );
  307. foreach($student_result as $row) {
  308. $html_result .=$row['html'];
  309. $row_not_global['results'][]= $row['array'];
  310. $row_not_global['users'][] = $row['user'];
  311. }
  312. $export_array[] = $row_not_global;
  313. $row_not_global = array();
  314. }
  315. }
  316. if ($global) {
  317. //Exam taken
  318. $html_result .= '<td align="center">';
  319. $html_result .= $taken;
  320. $global_row[]= $taken;
  321. //echo $total.' / '.$total_students;
  322. $html_result .= '</td>';
  323. //Exam NOT taken
  324. $html_result .= '<td align="center">';
  325. $html_result .= $not_taken = $total_students - $taken;
  326. $global_row[]= $not_taken;
  327. $html_result .= '</td>';
  328. //Examn pass
  329. if (!empty($total_with_parameter_score)) {
  330. $html_result .= '<td align="center" style="background-color:#DFFFA8" >';
  331. } else {
  332. $html_result .= '<td align="center" style="background-color:#FCE89A" >';
  333. }
  334. $html_result .= $total_with_parameter_score;
  335. $global_row[]= $total_with_parameter_score;
  336. $html_result .= '</td>';
  337. //Exam fail
  338. $html_result .= '<td align="center">';
  339. $html_result .= $fail = $taken - $total_with_parameter_score;
  340. $global_row[]= $fail;
  341. $html_result .= '</td>';
  342. $html_result .= '<td align="center">';
  343. $html_result .= $total_students;
  344. $global_row[]= $total_students;
  345. $global_counter++;
  346. $html_result .= '</td>';
  347. $html_result .= '</tr>';
  348. $export_array_global[] = $global_row;
  349. $global_row = array();
  350. }
  351. }
  352. } else {
  353. $html_result .= " <tr>
  354. <td colspan='6'>
  355. ".get_lang('NoExercise')."
  356. </td>
  357. </tr>
  358. ";
  359. }
  360. } else {
  361. $html_result .= " <tr>
  362. <td colspan='6'>
  363. ".get_lang('NoExercise')."
  364. </td>
  365. </tr>
  366. ";
  367. }
  368. }
  369. $html_result .= '</table>';
  370. if (!$export_to_xls) {
  371. echo $html_result;
  372. }
  373. $filename = 'exam-reporting-'.date('Y-m-d-h:i:s').'.xls';
  374. if ($export_to_xls) {
  375. if ($global) {
  376. export_complete_report_xls($filename, $export_array_global);
  377. } else {
  378. export_complete_report_xls($filename, $export_array);
  379. }
  380. exit;
  381. }
  382. function sort_user($a, $b) {
  383. if (is_numeric($a['score']) && is_numeric($b['score'])) {
  384. //echo $a['score'].' : '.$b['score']; echo '<br />';
  385. if ($a['score'] < $b['score']) {
  386. return 1;
  387. }
  388. return 0;
  389. }
  390. return 1;
  391. }
  392. function export_complete_report_xls($filename, $array) {
  393. global $charset, $global, $filter_score;
  394. $workbook = new Spreadsheet_Excel_Writer();
  395. $workbook ->setTempDir(api_get_path(SYS_ARCHIVE_PATH));
  396. $workbook->send($filename);
  397. $workbook->setVersion(8); // BIFF8
  398. $worksheet =& $workbook->addWorksheet('Report');
  399. //$worksheet->setInputEncoding(api_get_system_encoding());
  400. $worksheet->setInputEncoding($charset);
  401. $line = 0;
  402. $column = 0; //skip the first column (row titles)
  403. if ($global) {
  404. $worksheet->write($line,$column,get_lang('Courses'));
  405. $column++;
  406. $worksheet->write($line,$column,get_lang('Exercises'));
  407. $column++;
  408. $worksheet->write($line,$column,get_lang('ExamTaken'));
  409. $column++;
  410. $worksheet->write($line,$column,get_lang('ExamNotTaken'));
  411. $column++;
  412. $worksheet->write($line,$column,sprintf(get_lang('ExamPassX'), $filter_score).'%');
  413. $column++;
  414. $worksheet->write($line,$column,get_lang('ExamFail'));
  415. $column++;
  416. $worksheet->write($line,$column,get_lang('TotalStudents'));
  417. $column++;
  418. $line++;
  419. foreach ($array as $row) {
  420. $column = 0;
  421. foreach ($row as $item) {
  422. $worksheet->write($line,$column,html_entity_decode(strip_tags($item)));
  423. $column++;
  424. }
  425. $line++;
  426. }
  427. $line++;
  428. } else {
  429. $worksheet->write($line,$column,get_lang('Exercises'));
  430. $column++;
  431. $worksheet->write($line,$column,get_lang('User'));
  432. $column++;
  433. $worksheet->write($line,$column,get_lang('Percentage'));
  434. $column++;
  435. $worksheet->write($line,$column,get_lang('Status'));
  436. $column++;
  437. $worksheet->write($line,$column,get_lang('Attempts'));
  438. $column++;
  439. $line++;
  440. foreach ($array as $row) {
  441. $column = 0;
  442. $worksheet->write($line,$column,html_entity_decode(strip_tags($row['exercise'])));
  443. $column++;
  444. foreach ($row['users'] as $key=>$user) {
  445. $column = 1;
  446. $worksheet->write($line,$column,html_entity_decode(strip_tags($user)));
  447. $column++;
  448. foreach ($row['results'][$key] as $result_item) {
  449. $worksheet->write($line,$column,html_entity_decode(strip_tags($result_item)));
  450. $column++;
  451. }
  452. $line++;
  453. }
  454. }
  455. $line++;
  456. }
  457. $workbook->close();
  458. exit;
  459. }
  460. Display :: display_footer();