hotpotatoes_exercise_result.class.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Class HotpotatoesExerciseResult
  5. * Allows you to export exercises results in multiple presentation forms
  6. * @package chamilo.exercise
  7. */
  8. class HotpotatoesExerciseResult
  9. {
  10. //stores the list of exercises
  11. private $exercises_list = array();
  12. //stores the results
  13. private $results = array();
  14. /**
  15. * Gets the results of all students (or just one student if access is limited)
  16. * @param string The document path (for HotPotatoes retrieval)
  17. * @param integer User ID. Optional. If no user ID is provided, we take all the results. Defauts to null
  18. * @param string $document_path
  19. * @return bool
  20. */
  21. public function getExercisesReporting($document_path, $hotpotato_name)
  22. {
  23. $return = array();
  24. $TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
  25. $TBL_TRACK_HOTPOTATOES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
  26. $cid = api_get_course_id();
  27. $course_id = api_get_course_int_id();
  28. //$user_id = intval($user_id);
  29. $user_id = null;
  30. $session_id_and = ' AND te.session_id = '.api_get_session_id().' ';
  31. $hotpotato_name = Database::escape_string($hotpotato_name);
  32. if (!empty($exercise_id)) {
  33. $session_id_and .= " AND exe_exo_id = $exercise_id ";
  34. }
  35. if (empty($user_id)) {
  36. $sql = "SELECT firstname as userpart1, lastname as userpart2 ,
  37. email,
  38. tth.exe_name,
  39. tth.exe_result,
  40. tth.exe_weighting,
  41. tth.exe_date
  42. FROM $TBL_TRACK_HOTPOTATOES tth, $TBL_USER tu
  43. WHERE tu.user_id=tth.exe_user_id AND
  44. tth.c_id = $course_id AND
  45. tth.exe_name = '$hotpotato_name'
  46. ORDER BY tth.c_id ASC, tth.exe_date ASC";
  47. } else {
  48. // get only this user's results
  49. $sql = "SELECT '', exe_name, exe_result , exe_weighting, exe_date
  50. FROM $TBL_TRACK_HOTPOTATOES
  51. WHERE
  52. exe_user_id = '".$user_id."' AND
  53. c_id = $course_id AND
  54. tth.exe_name = '$hotpotato_name'
  55. ORDER BY c_id ASC, exe_date ASC";
  56. }
  57. $results = array();
  58. $resx = Database::query($sql);
  59. while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
  60. $results[] = $rowx;
  61. }
  62. $hpresults = array();
  63. $resx = Database::query($sql);
  64. while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
  65. $hpresults[] = $rowx;
  66. }
  67. // Print the Result of Hotpotatoes Tests
  68. if (is_array($hpresults)) {
  69. for ($i = 0; $i < sizeof($hpresults); $i++) {
  70. $return[$i] = array();
  71. $title = GetQuizName($hpresults[$i]['exe_name'], $document_path);
  72. if ($title == '') {
  73. $title = basename($hpresults[$i]['exe_name']);
  74. }
  75. if (empty($user_id)) {
  76. $return[$i]['email'] = $hpresults[$i]['email'];
  77. $return[$i]['first_name'] = $hpresults[$i]['userpart1'];
  78. $return[$i]['last_name'] = $hpresults[$i]['userpart2'];
  79. }
  80. $return[$i]['title'] = $title;
  81. $return[$i]['exe_date'] = $hpresults[$i]['exe_date'];
  82. $return[$i]['result'] = $hpresults[$i]['exe_result'];
  83. $return[$i]['max'] = $hpresults[$i]['exe_weighting'];
  84. }
  85. }
  86. $this->results = $return;
  87. return true;
  88. }
  89. /**
  90. * Exports the complete report as a CSV file
  91. * @param string $document_path Document path inside the document tool
  92. * @param string $hotpotato_name
  93. * @return boolean False on error
  94. */
  95. public function exportCompleteReportCSV($document_path = '', $hotpotato_name = '')
  96. {
  97. global $charset;
  98. $this->getExercisesReporting($document_path, $hotpotato_name);
  99. $filename = 'exercise_results_'.date('YmdGis').'.csv';
  100. if (!empty($user_id)) {
  101. $filename = 'exercise_results_user_'.$user_id.'_'.date('YmdGis').'.csv';
  102. }
  103. $data = '';
  104. if (api_is_western_name_order()) {
  105. if (!empty($this->results[0]['first_name'])) {
  106. $data .= get_lang('FirstName').';';
  107. }
  108. if (!empty($this->results[0]['last_name'])) {
  109. $data .= get_lang('LastName').';';
  110. }
  111. } else {
  112. if (!empty($this->results[0]['last_name'])) {
  113. $data .= get_lang('LastName').';';
  114. }
  115. if (!empty($this->results[0]['first_name'])) {
  116. $data .= get_lang('FirstName').';';
  117. }
  118. }
  119. $data .= get_lang('Email').';';
  120. $data .= get_lang('Title').';';
  121. $data .= get_lang('StartDate').';';
  122. $data .= get_lang('Score').';';
  123. $data .= get_lang('Total').';';
  124. $data .= "\n";
  125. // Results
  126. foreach ($this->results as $row) {
  127. if (api_is_western_name_order()) {
  128. $data .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['first_name']), ENT_QUOTES, $charset)).';';
  129. $data .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['last_name']), ENT_QUOTES, $charset)).';';
  130. } else {
  131. $data .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['last_name']), ENT_QUOTES, $charset)).';';
  132. $data .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['first_name']), ENT_QUOTES, $charset)).';';
  133. }
  134. $data .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['email']), ENT_QUOTES, $charset)).';';
  135. $data .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['title']), ENT_QUOTES, $charset)).';';
  136. $data .= str_replace("\r\n", ' ', $row['exe_date']).';';
  137. $data .= str_replace("\r\n", ' ', $row['result']).';';
  138. $data .= str_replace("\r\n", ' ', $row['max']).';';
  139. $data .= "\n";
  140. }
  141. //output the results
  142. $len = strlen($data);
  143. header('Content-type: application/octet-stream');
  144. header('Content-Type: application/force-download');
  145. header('Content-length: '.$len);
  146. if (preg_match("/MSIE 5.5/", $_SERVER['HTTP_USER_AGENT'])) {
  147. header('Content-Disposition: filename= '.$filename);
  148. } else {
  149. header('Content-Disposition: attachment; filename= '.$filename);
  150. }
  151. if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')) {
  152. header('Pragma: ');
  153. header('Cache-Control: ');
  154. header('Cache-Control: public'); // IE cannot download from sessions without a cache
  155. }
  156. header('Content-Description: '.$filename);
  157. header('Content-transfer-encoding: binary');
  158. // @todo add this utf-8 header for all csv files
  159. echo "\xEF\xBB\xBF"; // force utf-8 header of csv file
  160. echo $data;
  161. return true;
  162. }
  163. /**
  164. * Exports the complete report as an XLS file
  165. * @param string $document_path
  166. * @param null $user_id
  167. * @param bool $export_user_fields
  168. * @param int $export_filter
  169. * @param int $exercise_id
  170. * @param null $hotpotato_name
  171. * @return bool
  172. */
  173. public function exportCompleteReportXLS(
  174. $document_path = '',
  175. $user_id = null,
  176. $export_user_fields = false,
  177. $export_filter = 0,
  178. $exercise_id = 0,
  179. $hotpotato_name = null
  180. ) {
  181. global $charset;
  182. $this->getExercisesReporting(
  183. $document_path,
  184. $user_id,
  185. $export_filter,
  186. $exercise_id,
  187. $hotpotato_name
  188. );
  189. $filename = 'exercise_results_'.api_get_local_time().'.xls';
  190. if (!empty($user_id)) {
  191. $filename = 'exercise_results_user_'.$user_id.'_'.api_get_local_time().'.xls';
  192. }
  193. $spreadsheet = new PHPExcel();
  194. $spreadsheet->setActiveSheetIndex(0);
  195. $worksheet = $spreadsheet->getActiveSheet();
  196. $line = 0;
  197. $column = 0; //skip the first column (row titles)
  198. // check if exists column 'user'
  199. $with_column_user = false;
  200. foreach ($this->results as $result) {
  201. if (!empty($result['last_name']) && !empty($result['first_name'])) {
  202. $with_column_user = true;
  203. break;
  204. }
  205. }
  206. if ($with_column_user) {
  207. $worksheet->setCellValueByColumnAndRow(
  208. $column,
  209. $line,
  210. get_lang('Email')
  211. );
  212. $column++;
  213. if (api_is_western_name_order()) {
  214. $worksheet->setCellValueByColumnAndRow(
  215. $column,
  216. $line,
  217. get_lang('FirstName')
  218. );
  219. $column++;
  220. $worksheet->setCellValueByColumnAndRow(
  221. $column,
  222. $line,
  223. get_lang('LastName')
  224. );
  225. $column++;
  226. } else {
  227. $worksheet->setCellValueByColumnAndRow(
  228. $column,
  229. $line,
  230. get_lang('LastName')
  231. );
  232. $column++;
  233. $worksheet->setCellValueByColumnAndRow(
  234. $column,
  235. $line,
  236. get_lang('FirstName')
  237. );
  238. $column++;
  239. }
  240. }
  241. if ($export_user_fields) {
  242. //show user fields section with a big th colspan that spans over all fields
  243. $extra_user_fields = UserManager::get_extra_fields(
  244. 0,
  245. 1000,
  246. 5,
  247. 'ASC',
  248. false,
  249. 1
  250. );
  251. //show the fields names for user fields
  252. foreach ($extra_user_fields as $field) {
  253. $worksheet->setCellValueByColumnAndRow(
  254. $column,
  255. $line,
  256. api_html_entity_decode(
  257. strip_tags($field[3]),
  258. ENT_QUOTES,
  259. $charset
  260. )
  261. );
  262. $column++;
  263. }
  264. }
  265. $worksheet->setCellValueByColumnAndRow(
  266. $column,
  267. $line,
  268. get_lang('Title')
  269. );
  270. $column++;
  271. $worksheet->setCellValueByColumnAndRow(
  272. $column,
  273. $line,
  274. get_lang('StartDate')
  275. );
  276. $column++;
  277. $worksheet->setCellValueByColumnAndRow(
  278. $column,
  279. $line,
  280. get_lang('EndDate')
  281. );
  282. $column++;
  283. $worksheet->setCellValueByColumnAndRow(
  284. $column,
  285. $line,
  286. get_lang('Duration').' ('.get_lang('MinMinutes').')'
  287. );
  288. $column++;
  289. $worksheet->setCellValueByColumnAndRow(
  290. $column,
  291. $line,
  292. get_lang('Score')
  293. );
  294. $column++;
  295. $worksheet->setCellValueByColumnAndRow(
  296. $column,
  297. $line,
  298. get_lang('Total')
  299. );
  300. $column++;
  301. $worksheet->setCellValueByColumnAndRow(
  302. $column,
  303. $line,
  304. get_lang('Status')
  305. );
  306. $line++;
  307. foreach ($this->results as $row) {
  308. $column = 0;
  309. if ($with_column_user) {
  310. $worksheet->setCellValueByColumnAndRow(
  311. $column,
  312. $line,
  313. api_html_entity_decode(
  314. strip_tags($row['email']),
  315. ENT_QUOTES,
  316. $charset
  317. )
  318. );
  319. $column++;
  320. if (api_is_western_name_order()) {
  321. $worksheet->setCellValueByColumnAndRow(
  322. $column,
  323. $line,
  324. api_html_entity_decode(
  325. strip_tags($row['first_name']),
  326. ENT_QUOTES,
  327. $charset
  328. )
  329. );
  330. $column++;
  331. $worksheet->setCellValueByColumnAndRow(
  332. $column,
  333. $line,
  334. api_html_entity_decode(
  335. strip_tags($row['last_name']),
  336. ENT_QUOTES,
  337. $charset
  338. )
  339. );
  340. $column++;
  341. } else {
  342. $worksheet->setCellValueByColumnAndRow(
  343. $column,
  344. $line,
  345. api_html_entity_decode(
  346. strip_tags($row['last_name']),
  347. ENT_QUOTES,
  348. $charset
  349. )
  350. );
  351. $column++;
  352. $worksheet->setCellValueByColumnAndRow(
  353. $column,
  354. $line,
  355. api_html_entity_decode(
  356. strip_tags($row['first_name']),
  357. ENT_QUOTES,
  358. $charset
  359. )
  360. );
  361. $column++;
  362. }
  363. }
  364. if ($export_user_fields) {
  365. //show user fields data, if any, for this user
  366. $user_fields_values = UserManager::get_extra_user_data(
  367. $row['user_id'],
  368. false,
  369. false,
  370. false,
  371. true
  372. );
  373. foreach ($user_fields_values as $value) {
  374. $worksheet->setCellValueByColumnAndRow($column, $line, api_html_entity_decode(strip_tags($value), ENT_QUOTES, $charset));
  375. $column++;
  376. }
  377. }
  378. $worksheet->setCellValueByColumnAndRow($column, $line, api_html_entity_decode(strip_tags($row['title']), ENT_QUOTES, $charset));
  379. $column++;
  380. $worksheet->setCellValueByColumnAndRow($column, $line, $row['start_date']);
  381. $column++;
  382. $worksheet->setCellValueByColumnAndRow($column, $line, $row['end_date']);
  383. $column++;
  384. $worksheet->setCellValueByColumnAndRow($column, $line, $row['duration']);
  385. $column++;
  386. $worksheet->setCellValueByColumnAndRow($column, $line, $row['result']);
  387. $column++;
  388. $worksheet->setCellValueByColumnAndRow($column, $line, $row['max']);
  389. $column++;
  390. $worksheet->setCellValueByColumnAndRow($column, $line, $row['status']);
  391. $line++;
  392. }
  393. $file = api_get_path(SYS_ARCHIVE_PATH).api_replace_dangerous_char($filename);
  394. $writer = new PHPExcel_Writer_Excel2007($spreadsheet);
  395. $writer->save($file);
  396. DocumentManager::file_send_for_download($file, true, $filename);
  397. return true;
  398. }
  399. }