reporting.php 44 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335
  1. <?php
  2. /*
  3. DOKEOS - elearning and course management software
  4. For a full list of contributors, see documentation/credits.html
  5. This program is free software; you can redistribute it and/or
  6. modify it under the terms of the GNU General Public License
  7. as published by the Free Software Foundation; either version 2
  8. of the License, or (at your option) any later version.
  9. See "documentation/licence.html" more details.
  10. Contact:
  11. Dokeos
  12. Rue du Corbeau, 108
  13. B-1030 Brussels - Belgium
  14. */
  15. /**
  16. * @package dokeos.survey
  17. * @author unknown, the initial survey that did not make it in 1.8 because of bad code
  18. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University: cleanup, refactoring and rewriting large parts of the code
  19. * @version $Id: reporting.php 14636 2008-03-17 22:24:08Z yannoo $
  20. *
  21. * @todo The question has to be more clearly indicated (same style as when filling the survey)
  22. */
  23. // name of the language file that needs to be included
  24. $language_file = 'survey';
  25. // including the global dokeos file
  26. require ('../inc/global.inc.php');
  27. // export
  28. /**
  29. * @todo use export_table_csv($data, $filename = 'export')
  30. */
  31. if ($_POST['export_report'])
  32. {
  33. if($_POST['export_xls'])
  34. {
  35. $data = export_complete_report();
  36. $filename = 'fileexport.csv';
  37. echo $data;
  38. exit;
  39. }
  40. else
  41. {
  42. $data = export_complete_report();
  43. $filename = 'fileexport.csv';
  44. header('Content-type: application/octet-stream');
  45. header('Content-Type: application/force-download');
  46. header('Content-length: '.$len);
  47. if (preg_match("/MSIE 5.5/", $_SERVER['HTTP_USER_AGENT']))
  48. {
  49. header('Content-Disposition: filename= '.$filename);
  50. }
  51. else
  52. {
  53. header('Content-Disposition: attachment; filename= '.$filename);
  54. }
  55. if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE'))
  56. {
  57. header('Pragma: ');
  58. header('Cache-Control: ');
  59. header('Cache-Control: public'); // IE cannot download from sessions without a cache
  60. }
  61. header('Content-Description: '.$filename);
  62. header('Content-transfer-encoding: binary');
  63. echo $data;
  64. exit;
  65. }
  66. }
  67. // including additional libraries
  68. //require_once (api_get_path(LIBRARY_PATH)."/survey.lib.php");
  69. require_once('survey.lib.php');
  70. require_once (api_get_path(LIBRARY_PATH)."/course.lib.php");
  71. // Checking the parameters
  72. check_parameters();
  73. /** @todo this has to be moved to a more appropriate place (after the display_header of the code)*/
  74. if (!api_is_allowed_to_edit())
  75. {
  76. Display :: display_header();
  77. Display :: display_error_message(get_lang('NotAllowed'), false);
  78. Display :: display_footer();
  79. exit;
  80. }
  81. // Database table definitions
  82. $table_survey = Database :: get_course_table(TABLE_SURVEY);
  83. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  84. $table_survey_question_option = Database :: get_course_table(TABLE_SURVEY_QUESTION_OPTION);
  85. $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  86. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  87. $user_info = Database :: get_main_table(TABLE_MAIN_SURVEY_REMINDER);
  88. // getting the survey information
  89. $survey_data = survey_manager::get_survey($_GET['survey_id']);
  90. $urlname = substr(strip_tags($survey_data['title']), 0, 40);
  91. if (strlen(strip_tags($survey_data['title'])) > 40)
  92. {
  93. $urlname .= '...';
  94. }
  95. // breadcrumbs
  96. $interbreadcrumb[] = array ("url" => "survey_list.php", "name" => get_lang('SurveyList'));
  97. $interbreadcrumb[] = array ('url' => 'survey.php?survey_id='.$_GET['survey_id'], 'name' => $urlname);
  98. if (!$_GET['action'] OR $_GET['action'] == 'overview')
  99. {
  100. $tool_name = get_lang('Reporting');
  101. }
  102. else
  103. {
  104. $interbreadcrumb[] = array ("url" => "reporting.php?survey_id=".$_GET['survey_id'], "name" => get_lang('Reporting'));
  105. switch ($_GET['action'])
  106. {
  107. case 'questionreport':
  108. $tool_name = get_lang('DetailedReportByQuestion');
  109. break;
  110. case 'userreport':
  111. $tool_name = get_lang('DetailedReportByUser');
  112. break;
  113. case 'comparativereport':
  114. $tool_name = get_lang('ComparativeReport');
  115. break;
  116. case 'completereport':
  117. $tool_name = get_lang('CompleteReport');
  118. break;
  119. }
  120. }
  121. // Displaying the header
  122. Display::display_header($tool_name);
  123. // Action handling
  124. handle_reporting_actions();
  125. if (!$_GET['action'] OR $_GET['action'] == 'overview')
  126. {
  127. echo '<b><a href="reporting.php?action=questionreport&amp;survey_id='.$_GET['survey_id'].'">'.get_lang('DetailedReportByQuestion').'</a></b> <br />'.get_lang('DetailedReportByQuestionDetail').' <br /><br />';
  128. echo '<b><a href="reporting.php?action=userreport&amp;survey_id='.$_GET['survey_id'].'">'.get_lang('DetailedReportByUser').'</a></b><br />'.get_lang('DetailedReportByUserDetail').'.<br /><br />';
  129. echo '<b><a href="reporting.php?action=comparativereport&amp;survey_id='.$_GET['survey_id'].'">'.get_lang('ComparativeReport').'</a></b><br />'.get_lang('ComparativeReportDetail').'.<br /><br />';
  130. echo '<b><a href="reporting.php?action=completereport&amp;survey_id='.$_GET['survey_id'].'">'.get_lang('CompleteReport').'</a></b><br />'.get_lang('CompleteReportDetail').'<br /><br />';
  131. }
  132. // Footer
  133. Display :: display_footer();
  134. /**
  135. * This function checks the parameters that are used in this page
  136. *
  137. * @return the header, an error and the footer if any parameter fails, else it returns true
  138. *
  139. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  140. * @version February 2007
  141. */
  142. function check_parameters()
  143. {
  144. $error = false;
  145. // getting the survey data
  146. $survey_data = survey_manager::get_survey($_GET['survey_id']);
  147. // $_GET['survey_id'] has to be numeric
  148. if (!is_numeric($_GET['survey_id']))
  149. {
  150. $error = get_lang('IllegalSurveyId');
  151. }
  152. // $_GET['action']
  153. $allowed_actions = array('overview', 'questionreport', 'userreport', 'comparativereport', 'completereport');
  154. if (isset($_GET['action']) AND !in_array($_GET['action'], $allowed_actions))
  155. {
  156. $error = get_lang('ActionNotAllowed');
  157. }
  158. // user report
  159. if ($_GET['action'] == 'userreport')
  160. {
  161. global $people_filled;
  162. if ($survey_data['anonymous'] == 0)
  163. {
  164. $people_filled_full_data = true;
  165. }
  166. else
  167. {
  168. $people_filled_full_data = false;
  169. }
  170. $people_filled = survey_manager::get_people_who_filled_survey($_GET['survey_id'], $people_filled_full_data);
  171. if ($survey_data['anonymous'] == 0)
  172. {
  173. foreach ($people_filled as $key=>$value)
  174. {
  175. $people_filled_userids[]=$value['invited_user'];
  176. }
  177. }
  178. else
  179. {
  180. $people_filled_userids = $people_filled;
  181. }
  182. if (isset($_GET['user']) AND !in_array($_GET['user'], $people_filled_userids))
  183. {
  184. $error = get_lang('UnknowUser');
  185. }
  186. }
  187. // question report
  188. if ($_GET['action'] == 'questionreport')
  189. {
  190. if (isset($_GET['question'])AND !is_numeric($_GET['question']))
  191. {
  192. $error = get_lang('UnknowQuestion');
  193. }
  194. }
  195. if ($error)
  196. {
  197. $tool_name = get_lang('Reporting');
  198. Display::display_header($tool_name);
  199. Display::display_error_message(get_lang('Error').': '.$error, false);
  200. Display::display_footer();
  201. exit;
  202. }
  203. else
  204. {
  205. return true;
  206. }
  207. }
  208. /**
  209. * This function deals with the action handling
  210. *
  211. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  212. * @version February 2007
  213. */
  214. function handle_reporting_actions()
  215. {
  216. // getting the number of question
  217. $temp_questions_data = survey_manager::get_questions($_GET['survey_id']);
  218. // sorting like they should be displayed and removing the non-answer question types (comment and pagebreak)
  219. foreach ($temp_questions_data as $key=>$value)
  220. {
  221. if ($value['type'] <> 'comment' AND $value['type']<>'pagebreak')
  222. {
  223. $questions_data[$value['sort']]=$value;
  224. }
  225. }
  226. // counting the number of questions that are relevant for the reporting
  227. $survey_data['number_of_questions'] = count($questions_data);
  228. if ($_GET['action'] == 'questionreport')
  229. {
  230. display_question_report($survey_data);
  231. }
  232. if ($_GET['action'] == 'userreport')
  233. {
  234. display_user_report();
  235. }
  236. if ($_GET['action'] == 'comparativereport')
  237. {
  238. display_comparative_report();
  239. }
  240. if ($_GET['action'] == 'completereport')
  241. {
  242. display_complete_report();
  243. }
  244. }
  245. /**
  246. * This function displays the user report which is basically nothing more than a one-page display of all the questions
  247. * of the survey that is filled with the answers of the person who filled the survey.
  248. *
  249. * @return html code of the one-page survey with the answers of the selected user
  250. *
  251. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  252. * @version February 2007
  253. */
  254. function display_user_report()
  255. {
  256. global $people_filled, $survey_data;
  257. // Database table definitions
  258. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  259. $table_survey_question_option = Database :: get_course_table(TABLE_SURVEY_QUESTION_OPTION);
  260. $table_survey_answer = Database :: get_course_table(TABLE_SURVEY_ANSWER);
  261. // step 1: selection of the user
  262. echo "<script language=\"JavaScript\" type=\"text/JavaScript\">
  263. <!--
  264. function jumpMenu(targ,selObj,restore)
  265. {
  266. eval(targ+\".location='\"+selObj.options[selObj.selectedIndex].value+\"'\");
  267. if (restore) selObj.selectedIndex=0;
  268. }
  269. //-->
  270. </script>
  271. ";
  272. echo get_lang('SelectUserWhoFilledSurvey').'<br />';
  273. echo '<select name="user" onchange="jumpMenu(\'parent\',this,0)">';
  274. echo '<option value="reporting.php?action='.$_GET['action'].'&amp;survey_id='.$_GET['survey_id'].'">'.get_lang('SelectUser').'</option>';
  275. foreach ($people_filled as $key=>$person)
  276. {
  277. if ($survey_data['anonymous'] == 0)
  278. {
  279. $name = $person['firstname'].' '.$person['lastname'];
  280. $id = $person['user_id'];
  281. }
  282. else
  283. {
  284. $name = $key+1;
  285. $id = $person;
  286. }
  287. echo '<option value="reporting.php?action='.$_GET['action'].'&amp;survey_id='.$_GET['survey_id'].'&amp;user='.$id.'" ';
  288. if ($_GET['user'] == $person)
  289. {
  290. echo 'selected="selected"';
  291. }
  292. echo '>'.$name.'</option>';
  293. }
  294. echo '</select>';
  295. // step 2: displaying the survey and the answer of the selected users
  296. if (isset($_GET['user']))
  297. {
  298. Display::display_normal_message(get_lang('AllQuestionsOnOnePage'), false);
  299. // getting all the questions and options
  300. $sql = "SELECT survey_question.question_id, survey_question.survey_id, survey_question.survey_question, survey_question.display, survey_question.max_value, survey_question.sort, survey_question.type,
  301. survey_question_option.question_option_id, survey_question_option.option_text, survey_question_option.sort as option_sort
  302. FROM $table_survey_question survey_question
  303. LEFT JOIN $table_survey_question_option survey_question_option
  304. ON survey_question.question_id = survey_question_option.question_id
  305. WHERE survey_question.survey_id = '".Database::escape_string($_GET['survey_id'])."'
  306. ORDER BY survey_question.sort ASC";
  307. $result = api_sql_query($sql, __FILE__, __LINE__);
  308. while ($row = mysql_fetch_assoc($result))
  309. {
  310. if($row['type'] <> 'pagebreak')
  311. {
  312. $questions[$row['sort']]['question_id'] = $row['question_id'];
  313. $questions[$row['sort']]['survey_id'] = $row['survey_id'];
  314. $questions[$row['sort']]['survey_question'] = $row['survey_question'];
  315. $questions[$row['sort']]['display'] = $row['display'];
  316. $questions[$row['sort']]['type'] = $row['type'];
  317. $questions[$row['sort']]['maximum_score'] = $row['max_value'];
  318. $questions[$row['sort']]['options'][$row['question_option_id']] = $row['option_text'];
  319. }
  320. }
  321. // getting all the answers of the user
  322. $sql = "SELECT * FROM $table_survey_answer WHERE survey_id = '".Database::escape_string($_GET['survey_id'])."' AND user = '".Database::escape_string($_GET['user'])."'";
  323. $result = api_sql_query($sql, __FILE__, __LINE__);
  324. while ($row = mysql_fetch_assoc($result))
  325. {
  326. $answers[$row['question_id']][] = $row['option_id'];
  327. $all_answers[$row['question_id']][] = $row;
  328. }
  329. /*
  330. echo '<pre>';
  331. print_r($all_answers);
  332. echo '</pre>';
  333. */
  334. // displaying all the questions
  335. foreach ($questions as $key=>$question)
  336. {
  337. // if the question type is a scoring then we have to format the answers differently
  338. if ($question['type'] == 'score')
  339. {
  340. foreach($all_answers[$question['question_id']] as $key=>$answer_array)
  341. {
  342. $second_parameter[$answer_array['option_id']] = $answer_array['value'];
  343. }
  344. }
  345. else
  346. {
  347. $second_parameter = $answers[$question['question_id']];
  348. if ($question['type'] == 'open')
  349. {
  350. $second_parameter = array();
  351. $second_parameter[] = $all_answers[$question['question_id']][0]['option_id'];
  352. }
  353. }
  354. $display = new $question['type'];
  355. $display->render_question($question, $second_parameter);
  356. // echo '<pre>';
  357. // print_r($answers[$question['question_id']]);
  358. // echo '</pre>';
  359. }
  360. }
  361. }
  362. /**
  363. * This function displays the report by question.
  364. * It displays a table with all the options of the question and the number of users who have answered positively on the option.
  365. * The number of users who answered positive on a given option is expressed in an absolute number, in a percentage of the total
  366. * and graphically using bars
  367. * By clicking on the absolute number you get a list with the persons who have answered this.
  368. * You can then click on the name of the person and you will then go to the report by user where you see all the
  369. * answers of that user.
  370. *
  371. * @param array $survey_data all the data of the survey
  372. *
  373. * @return html code that displays the report by question
  374. *
  375. * @todo allow switching between horizontal and vertical.
  376. * @todo multiple response: percentage are probably not OK
  377. * @todo the question and option text have to be shortened and should expand when the user clicks on it.
  378. * @todo the pagebreak and comment question types should not be shown => removed from $survey_data before
  379. *
  380. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  381. * @version February 2007
  382. */
  383. function display_question_report($survey_data)
  384. {
  385. // Database table definitions
  386. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  387. $table_survey_question_option = Database :: get_course_table(TABLE_SURVEY_QUESTION_OPTION);
  388. $table_survey_answer = Database :: get_course_table(TABLE_SURVEY_ANSWER);
  389. // determining the offset of the sql statement (the n-th question of the survey)
  390. if (!isset($_GET['question']))
  391. {
  392. $offset = 0;
  393. }
  394. else
  395. {
  396. $offset = $_GET['question'];
  397. }
  398. echo '<div id="question_report_questionnumbers">';
  399. for($i=1; $i<=($survey_data['number_of_questions']); $i++ )
  400. {
  401. if ($offset <> $i-1)
  402. {
  403. echo '<a href="reporting.php?action=questionreport&amp;survey_id='.(int)$_GET['survey_id'].'&amp;question='.($i-1).'">'.$i.'</a>';
  404. }
  405. else
  406. {
  407. echo $i;
  408. }
  409. if ($i < $survey_data['number_of_questions'])
  410. {
  411. echo ' | ';
  412. }
  413. }
  414. echo '</div>';
  415. // getting the question information
  416. $sql = "SELECT * FROM $table_survey_question WHERE survey_id='".Database::escape_string($_GET['survey_id'])."' AND type<>'pagebreak' AND type<>'comment' ORDER BY sort ASC LIMIT ".$offset.",1";
  417. $result = api_sql_query($sql, __FILE__, __LINE__);
  418. $question = mysql_fetch_assoc($result);
  419. // navigate through the questions (next and previous)
  420. if ($_GET['question'] <> 0)
  421. {
  422. echo '<a href="reporting.php?action='.$_GET['action'].'&amp;survey_id='.$_GET['survey_id'].'&amp;question='.($offset-1).'"> &lt;&lt; '.get_lang('PreviousQuestion').'</a> ';
  423. }
  424. else
  425. {
  426. echo '&lt;&lt;'.get_lang('PreviousQuestion').' ';
  427. }
  428. echo ' | ';
  429. if ($_GET['question'] < ($survey_data['number_of_questions']-1))
  430. {
  431. echo '<a href="reporting.php?action='.$_GET['action'].'&amp;survey_id='.$_GET['survey_id'].'&amp;question='.($offset+1).'">'.get_lang('NextQuestion').'&gt;&gt; </a>';
  432. }
  433. else
  434. {
  435. echo get_lang('NextQuestion'). '&gt;&gt;';
  436. }
  437. echo '<br />';
  438. echo $question['survey_question'];
  439. echo '<br />';
  440. if ($question['type'] == 'score')
  441. {
  442. /** @todo this function should return the options as this is needed further in the code */
  443. $options = display_question_report_score($survey_data, $question, $offset);
  444. }
  445. elseif ($question['type'] == 'open')
  446. {
  447. /** @todo also get the user who has answered this */
  448. $sql = "SELECT * FROM $table_survey_answer WHERE survey_id='".Database::escape_string($_GET['survey_id'])."'
  449. AND question_id = '".Database::escape_string($question['question_id'])."'";
  450. $result = api_sql_query($sql, __FILE__, __LINE__);
  451. while ($row = mysql_fetch_assoc($result))
  452. {
  453. echo $row['option_id'].'<hr noshade="noshade" size="1" />';
  454. }
  455. }
  456. else
  457. {
  458. // getting the options
  459. $sql = "SELECT * FROM $table_survey_question_option
  460. WHERE survey_id='".Database::escape_string($_GET['survey_id'])."'
  461. AND question_id = '".Database::escape_string($question['question_id'])."'
  462. ORDER BY sort ASC";
  463. $result = api_sql_query($sql, __FILE__, __LINE__);
  464. while ($row = mysql_fetch_assoc($result))
  465. {
  466. $options[$row['question_option_id']] = $row;
  467. }
  468. //echo '<pre>';
  469. //print_r($options);
  470. //echo '<pre>';
  471. // getting the answers
  472. $sql = "SELECT *, count(answer_id) as total FROM $table_survey_answer
  473. WHERE survey_id='".Database::escape_string($_GET['survey_id'])."'
  474. AND question_id = '".Database::escape_string($question['question_id'])."'
  475. GROUP BY option_id, value";
  476. $result = api_sql_query($sql, __FILE__, __LINE__);
  477. while ($row = mysql_fetch_assoc($result))
  478. {
  479. $number_of_answers += $row['total'];
  480. $data[$row['option_id']] = $row;
  481. }
  482. //echo '<pre>';
  483. //print_r($data);
  484. //echo '<pre>';
  485. // displaying the table: headers
  486. echo '<table>';
  487. echo ' <tr>';
  488. echo ' <th>&nbsp;</th>';
  489. echo ' <th>'.get_lang('AbsoluteTotal').'</th>';
  490. echo ' <th>'.get_lang('Percentage').'</th>';
  491. echo ' <th>'.get_lang('VisualRepresentation').'</th>';
  492. echo ' <tr>';
  493. // displaying the table: the content
  494. foreach ($options as $key=>$value)
  495. {
  496. $absolute_number = $data[$value['question_option_id']]['total'];
  497. echo ' <tr>';
  498. echo ' <td>'.$value['option_text'].'</td>';
  499. echo ' <td><a href="reporting.php?action='.$_GET['action'].'&amp;survey_id='.$_GET['survey_id'].'&amp;question='.$offset.'&amp;viewoption='.$value['question_option_id'].'">'.$absolute_number.'</a></td>';
  500. echo ' <td>'.round($absolute_number/$number_of_answers*100, 2).' %</td>';
  501. echo ' <td>';
  502. $size = $absolute_number/$number_of_answers*100*2;
  503. if ($size > 0)
  504. {
  505. echo '<div style="border:1px solid #264269; background-color:#aecaf4; height:10px; width:'.$size.'px">&nbsp;</div>';
  506. }
  507. echo ' </td>';
  508. echo ' </tr>';
  509. }
  510. // displaying the table: footer (totals)
  511. echo ' <tr>';
  512. echo ' <td style="border-top:1px solid black"><b>'.get_lang('Total').'</b></td>';
  513. echo ' <td style="border-top:1px solid black"><b>'.$number_of_answers.'</b></td>';
  514. echo ' <td style="border-top:1px solid black">&nbsp;</td>';
  515. echo ' <td style="border-top:1px solid black">&nbsp;</td>';
  516. echo ' </tr>';
  517. echo '</table>';
  518. }
  519. if (isset($_GET['viewoption']))
  520. {
  521. echo get_lang('PeopleWhoAnswered').': '.$options[$_GET['viewoption']]['option_text'].'<br />';
  522. if (is_numeric($_GET['value']))
  523. {
  524. $sql_restriction = "AND value='".Database::escape_string($_GET['value'])."'";
  525. }
  526. $sql = "SELECT user FROM $table_survey_answer WHERE option_id = '".Database::escape_string($_GET['viewoption'])."' $sql_restriction";
  527. $result = api_sql_query($sql, __FILE__, __LINE__);
  528. while ($row = mysql_fetch_assoc($result))
  529. {
  530. echo '<a href="reporting.php?action=userreport&survey_id='.$_GET['survey_id'].'&user='.$row['user'].'">'.$row['user'].'</a><br />';
  531. }
  532. }
  533. }
  534. function display_question_report_score($survey_data, $question, $offset)
  535. {
  536. // Database table definitions
  537. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  538. $table_survey_question_option = Database :: get_course_table(TABLE_SURVEY_QUESTION_OPTION);
  539. $table_survey_answer = Database :: get_course_table(TABLE_SURVEY_ANSWER);
  540. // getting the options
  541. $sql = "SELECT * FROM $table_survey_question_option
  542. WHERE survey_id='".Database::escape_string($_GET['survey_id'])."'
  543. AND question_id = '".Database::escape_string($question['question_id'])."'
  544. ORDER BY sort ASC";
  545. $result = api_sql_query($sql, __FILE__, __LINE__);
  546. while ($row = mysql_fetch_assoc($result))
  547. {
  548. $options[$row['question_option_id']] = $row;
  549. }
  550. // getting the answers
  551. $sql = "SELECT *, count(answer_id) as total FROM $table_survey_answer
  552. WHERE survey_id='".Database::escape_string($_GET['survey_id'])."'
  553. AND question_id = '".Database::escape_string($question['question_id'])."'
  554. GROUP BY option_id, value";
  555. $result = api_sql_query($sql, __FILE__, __LINE__);
  556. while ($row = mysql_fetch_assoc($result))
  557. {
  558. $number_of_answers += $row['total'];
  559. $data[$row['option_id']][$row['value']] = $row;
  560. }
  561. /*
  562. echo '<pre>';
  563. print_r($data);
  564. echo '</pre>';
  565. */
  566. // displaying the table: headers
  567. echo '<table>';
  568. echo ' <tr>';
  569. echo ' <th>&nbsp;</th>';
  570. echo ' <th>'.get_lang('Score').'</th>';
  571. echo ' <th>'.get_lang('AbsoluteTotal').'</th>';
  572. echo ' <th>'.get_lang('Percentage').'</th>';
  573. echo ' <th>'.get_lang('VisualRepresentation').'</th>';
  574. echo ' <tr>';
  575. // displaying the table: the content
  576. foreach ($options as $key=>$value)
  577. {
  578. for ($i=1; $i<=$question['max_value']; $i++)
  579. {
  580. $absolute_number = $data[$value['question_option_id']][$i]['total'];
  581. echo ' <tr>';
  582. echo ' <td>'.$value['option_text'].'</td>';
  583. echo ' <td>'.$i.'</td>';
  584. echo ' <td><a href="reporting.php?action='.$_GET['action'].'&amp;survey_id='.$_GET['survey_id'].'&amp;question='.$offset.'&amp;viewoption='.$value['question_option_id'].'&amp;value='.$i.'">'.$absolute_number.'</a></td>';
  585. echo ' <td>'.round($absolute_number/$number_of_answers*100, 2).' %</td>';
  586. echo ' <td>';
  587. $size = ($absolute_number/$number_of_answers*100*2);
  588. if ($size > 0)
  589. {
  590. echo ' <div style="border:1px solid #264269; background-color:#aecaf4; height:10px; width:'.$size.'px">&nbsp;</div>';
  591. }
  592. echo ' </td>';
  593. echo ' </tr>';
  594. }
  595. }
  596. // displaying the table: footer (totals)
  597. echo ' <tr>';
  598. echo ' <td style="border-top:1px solid black"><b>'.get_lang('Total').'</b></td>';
  599. echo ' <td style="border-top:1px solid black">&nbsp;</td>';
  600. echo ' <td style="border-top:1px solid black"><b>'.$number_of_answers.'</b></td>';
  601. echo ' <td style="border-top:1px solid black">&nbsp;</td>';
  602. echo ' <td style="border-top:1px solid black">&nbsp;</td>';
  603. echo ' </tr>';
  604. echo '</table>';
  605. }
  606. /**
  607. * This functions displays the complete reporting
  608. *
  609. * @return html code
  610. *
  611. * @todo open questions are not in the complete report yet.
  612. *
  613. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  614. * @version February 2007
  615. */
  616. function display_complete_report()
  617. {
  618. // Database table definitions
  619. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  620. $table_survey_question_option = Database :: get_course_table(TABLE_SURVEY_QUESTION_OPTION);
  621. $table_survey_answer = Database :: get_course_table(TABLE_SURVEY_ANSWER);
  622. // the form
  623. echo '<form id="form1" name="form1" method="post" action="'.api_get_self().'?action='.$_GET['action'].'&survey_id='.$_GET['survey_id'].'">';
  624. /*// the export button
  625. echo '<input type="submit" name="export_report" value="'.get_lang('ExportCurrentReport').'" />';*/
  626. echo '<input type="hidden" name="export_report" value="export_report">';
  627. echo '</form>';
  628. echo '<form id="form2" name="form2" method="post" action="'.api_get_self().'?action='.$_GET['action'].'&survey_id='.$_GET['survey_id'].'">';
  629. echo '<a href="#" onclick="document.form1.submit();"><img align="absbottom" src="'.api_get_path(WEB_IMG_PATH).'excel.gif">&nbsp;'.get_lang('ExportCurrentReport').'</a>';
  630. // the table
  631. echo '<table class="data_table" border="1">';
  632. // getting the number of options per question
  633. echo ' <tr>';
  634. echo ' <th>';
  635. if ($_POST['submit_question_filter'] OR $_POST['export_report'])
  636. {
  637. echo ' <input type="submit" name="reset_question_filter" value="'.get_lang('ResetQuestionFilter').'" />';
  638. }
  639. echo ' <input type="submit" name="submit_question_filter" value="'.get_lang('SubmitQuestionFilter').'" />';
  640. echo '</th>';
  641. $sql = "SELECT questions.question_id, questions.type, questions.survey_question, count(options.question_option_id) as number_of_options
  642. FROM $table_survey_question questions LEFT JOIN $table_survey_question_option options
  643. ON questions.question_id = options.question_id
  644. WHERE questions.question_id = options.question_id
  645. AND questions.survey_id = '".Database::escape_string($_GET['survey_id'])."'
  646. GROUP BY questions.question_id";
  647. $result = api_sql_query($sql, __FILE__, __LINE__);
  648. while ($row = mysql_fetch_assoc($result))
  649. {
  650. // we show the questions if
  651. // 1. there is no question filter and the export button has not been clicked
  652. // 2. there is a quesiton filter but the question is selected for display
  653. if (!($_POST['submit_question_filter'] OR $_POST['export_report']) OR in_array($row['question_id'], $_POST['questions_filter']))
  654. {
  655. // we do not show comment and pagebreak question types
  656. if ($row['type'] <> 'comment' AND $row['type'] <> 'pagebreak')
  657. {
  658. echo ' <th';
  659. if ($row['number_of_options'] >0)
  660. {
  661. echo ' colspan="'.$row['number_of_options'].'"';
  662. }
  663. echo '>';
  664. echo '<label><input type="checkbox" name="questions_filter[]" value="'.$row['question_id'].'" checked="checked"/> ';
  665. echo $row['survey_question'];
  666. echo '</label>';
  667. echo '</th>';
  668. }
  669. }
  670. $questions[$row['question_id']] = $row;
  671. }
  672. echo ' </tr>';
  673. // getting all the questions and options
  674. echo ' <tr>';
  675. echo ' <th>&nbsp;</th>'; // the user column
  676. $sql = "SELECT survey_question.question_id, survey_question.survey_id, survey_question.survey_question, survey_question.display, survey_question.sort, survey_question.type,
  677. survey_question_option.question_option_id, survey_question_option.option_text, survey_question_option.sort as option_sort
  678. FROM $table_survey_question survey_question
  679. LEFT JOIN $table_survey_question_option survey_question_option
  680. ON survey_question.question_id = survey_question_option.question_id
  681. WHERE survey_question.survey_id = '".Database::escape_string($_GET['survey_id'])."'
  682. ORDER BY survey_question.sort ASC";
  683. $result = api_sql_query($sql, __FILE__, __LINE__);
  684. while ($row = mysql_fetch_assoc($result))
  685. {
  686. // we show the options if
  687. // 1. there is no question filter and the export button has not been clicked
  688. // 2. there is a quesiton filter but the question is selected for display
  689. if (!($_POST['submit_question_filter'] OR $_POST['export_report']) OR in_array($row['question_id'], $_POST['questions_filter']))
  690. {
  691. // we do not show comment and pagebreak question types
  692. if ($row['type'] <> 'comment' AND $row['type'] <> 'pagebreak')
  693. {
  694. echo ' <th>';
  695. echo $row['option_text'];
  696. echo '</th>';
  697. $possible_answers[$row['question_id']][$row['question_option_id']] =$row['question_option_id'];
  698. }
  699. }
  700. }
  701. echo ' </tr>';
  702. // getting all the answers of the users
  703. $old_user='';
  704. $answers_of_user = array();
  705. $sql = "SELECT * FROM $table_survey_answer WHERE survey_id='".Database::escape_string($_GET['survey_id'])."' ORDER BY user ASC";
  706. $result = api_sql_query($sql, __FILE__, __LINE__);
  707. while ($row = mysql_fetch_assoc($result))
  708. {
  709. if ($old_user <> $row['user'] AND $old_user<>'')
  710. {
  711. display_complete_report_row($possible_answers, $answers_of_user, $old_user, $questions);
  712. $answers_of_user=array();
  713. }
  714. if ($questions[$row['question_id']]['type']<> 'open')
  715. {
  716. $answers_of_user[$row['question_id']][$row['option_id']] = $row;
  717. }
  718. else
  719. {
  720. $answers_of_user[$row['question_id']][0] = $row;
  721. }
  722. $old_user = $row['user'];
  723. }
  724. display_complete_report_row($possible_answers, $answers_of_user, $old_user, $questions); // this is to display the last user
  725. echo '</table>';
  726. echo '</form>';
  727. }
  728. /**
  729. * This function displays a row (= a user and his/her answers) in the table of the complete report.
  730. *
  731. * @param array $possible_answers all the possible options
  732. * @param array $answers_of_user the answers of the user
  733. * @param string $user the user
  734. *
  735. * @todo rename $possible_answers to $possible_options ?
  736. *
  737. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  738. * @version February 2007
  739. */
  740. function display_complete_report_row($possible_answers, $answers_of_user, $user, $questions)
  741. {
  742. global $survey_data;
  743. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  744. echo '<tr>';
  745. if ($survey_data['anonymous'] == 0)
  746. {
  747. if(intval($user)!==0)
  748. {
  749. $sql = 'SELECT firstname, lastname FROM '.Database::get_main_table(TABLE_MAIN_USER).' WHERE user_id='.intval($user);
  750. $rs = api_sql_query($sql, __FILE__, __LINE__);
  751. if($row = mysql_fetch_array($rs, MYSQL_ASSOC))
  752. {
  753. $user_displayed = $row['lastname'].' '.$row['firstname'];
  754. }
  755. else
  756. {
  757. $user_displayed = '-';
  758. }
  759. echo ' <th><a href="'.api_get_self().'?action=userreport&survey_id='.$_GET['survey_id'].'&user='.$user.'">'.$user_displayed.'</a></th>'; // the user column
  760. }
  761. else
  762. {
  763. echo ' <th>'.$user.'</th>'; // the user column
  764. }
  765. }
  766. else
  767. {
  768. echo '<th>-</th>';
  769. }
  770. foreach ($possible_answers as $question_id=>$possible_option)
  771. {
  772. if ($questions[$question_id]['type'] == 'open')
  773. {
  774. echo '<td align="center">';
  775. echo $answers_of_user[$question_id]['0']['option_id'];
  776. echo '</td>';
  777. }
  778. else
  779. {
  780. foreach ($possible_option as $option_id=>$value)
  781. {
  782. echo '<td align="center">';
  783. if (!empty($answers_of_user[$question_id][$option_id]))
  784. {
  785. if ($answers_of_user[$question_id][$option_id]['value']<>0)
  786. {
  787. echo $answers_of_user[$question_id][$option_id]['value'];
  788. }
  789. else
  790. {
  791. echo 'v';
  792. }
  793. }
  794. }
  795. }
  796. }
  797. echo '</tr>';
  798. }
  799. /**
  800. * the function is quite similar to display_complete_report and return a html string that can be used in a csv file
  801. *
  802. * @todo consider merging this function with display_complete_report
  803. *
  804. * @return string $return the content of a csv file
  805. *
  806. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  807. * @version February 2007
  808. */
  809. function export_complete_report()
  810. {
  811. // Database table definitions
  812. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  813. $table_survey_question_option = Database :: get_course_table(TABLE_SURVEY_QUESTION_OPTION);
  814. $table_survey_answer = Database :: get_course_table(TABLE_SURVEY_ANSWER);
  815. // the first column
  816. $return = ';';
  817. $sql = "SELECT questions.question_id, questions.type, questions.survey_question, count(options.question_option_id) as number_of_options
  818. FROM $table_survey_question questions LEFT JOIN $table_survey_question_option options
  819. ON questions.question_id = options.question_id "
  820. /*WHERE questions.question_id = options.question_id
  821. AND questions.survey_id = '".Database::escape_string($_GET['survey_id'])."'*/
  822. ." AND questions.survey_id = '".Database::escape_string($_GET['survey_id'])."'
  823. GROUP BY questions.question_id";
  824. $result = api_sql_query($sql, __FILE__, __LINE__);
  825. while ($row = mysql_fetch_assoc($result))
  826. {
  827. // we show the questions if
  828. // 1. there is no question filter and the export button has not been clicked
  829. // 2. there is a quesiton filter but the question is selected for display
  830. if (!($_POST['submit_question_filter']) OR (is_array($_POST['questions_filter']) && in_array($row['question_id'], $_POST['questions_filter'])))
  831. {
  832. // we do not show comment and pagebreak question types
  833. if ($row['type'] <> 'comment' AND $row['type'] <> 'pagebreak')
  834. {
  835. if($row['number_of_options'] == 0 && $row['type']=='open')
  836. {
  837. $return .= str_replace("\r\n",' ',html_entity_decode(strip_tags($row['survey_question']))).';';
  838. }
  839. else
  840. {
  841. for ($ii = 0; $ii < $row['number_of_options']; $ii ++)
  842. {
  843. $return .= str_replace("\r\n",' ',html_entity_decode(strip_tags($row['survey_question']))).';';
  844. }
  845. }
  846. }
  847. }
  848. }
  849. $return .= "\n";
  850. // getting all the questions and options
  851. $return .= ';';
  852. $sql = "SELECT survey_question.question_id, survey_question.survey_id, survey_question.survey_question, survey_question.display, survey_question.sort, survey_question.type,
  853. survey_question_option.question_option_id, survey_question_option.option_text, survey_question_option.sort as option_sort
  854. FROM $table_survey_question survey_question
  855. LEFT JOIN $table_survey_question_option survey_question_option
  856. ON survey_question.question_id = survey_question_option.question_id
  857. WHERE survey_question.survey_id = '".Database::escape_string($_GET['survey_id'])."'
  858. ORDER BY survey_question.sort ASC";
  859. $result = api_sql_query($sql, __FILE__, __LINE__);
  860. $possible_answers = array();
  861. $possible_answers_type = array();
  862. while ($row = mysql_fetch_assoc($result))
  863. {
  864. // we show the options if
  865. // 1. there is no question filter and the export button has not been clicked
  866. // 2. there is a quesiton filter but the question is selected for display
  867. if (!($_POST['submit_question_filter']) OR (is_array($_POST['questions_filter']) && in_array($row['question_id'], $_POST['questions_filter'])))
  868. {
  869. // we do not show comment and pagebreak question types
  870. if ($row['type'] <> 'comment' AND $row['type'] <> 'pagebreak')
  871. {
  872. $return .= html_entity_decode(strip_tags($row['option_text'])).';';
  873. $possible_answers[$row['question_id']][$row['question_option_id']] =$row['question_option_id'];
  874. $possible_answers_type[$row['question_id']] = $row['type'];
  875. }
  876. }
  877. }
  878. $return .= "\n";
  879. // getting all the answers of the users
  880. $old_user='';
  881. $answers_of_user = array();
  882. $sql = "SELECT * FROM $table_survey_answer WHERE survey_id='".Database::escape_string($_GET['survey_id'])."' ORDER BY user ASC";
  883. $open_question_iterator = 1;
  884. $result = api_sql_query($sql, __FILE__, __LINE__);
  885. while ($row = mysql_fetch_assoc($result))
  886. {
  887. if ($old_user <> $row['user'] AND $old_user <> '')
  888. {
  889. $return .= export_complete_report_row($possible_answers, $answers_of_user, $old_user);
  890. $answers_of_user=array();
  891. }
  892. if($possible_answers_type[$row['question_id']] == 'open')
  893. {
  894. $temp_id = 'open'.$open_question_iterator;
  895. $answers_of_user[$row['question_id']][$temp_id] = $row;
  896. $open_question_iterator++;
  897. }
  898. else
  899. {
  900. $answers_of_user[$row['question_id']][$row['option_id']] = $row;
  901. }
  902. $old_user = $row['user'];
  903. }
  904. $return .= export_complete_report_row($possible_answers, $answers_of_user, $old_user); // this is to display the last user
  905. return $return;
  906. }
  907. /**
  908. * add a line to the csv file
  909. *
  910. * @param array $possible_answers all the possible answers
  911. * @param array $answers_of_user the answers of the user
  912. * @param string $user the user
  913. *
  914. * @return string $return line of the csv file
  915. *
  916. * @todo rename $possible_answers to $possible_options ?
  917. *
  918. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  919. * @version February 2007
  920. */
  921. function export_complete_report_row($possible_answers, $answers_of_user, $user)
  922. {
  923. $return = $user.';'; // the user column
  924. if(is_array($possible_answers))
  925. {
  926. foreach ($possible_answers as $question_id=>$possible_option)
  927. {
  928. if(is_array($possible_option) && count($possible_option)>0)
  929. {
  930. foreach ($possible_option as $option_id=>$value)
  931. {
  932. $key = array_keys($answers_of_user[$question_id]);
  933. if(substr($key[0],0,4)=='open')
  934. {
  935. $return .= '"'.str_replace('"','""',html_entity_decode(strip_tags($answers_of_user[$question_id][$key[0]]['option_id']))).'"';
  936. }
  937. elseif (!empty($answers_of_user[$question_id][$option_id]))
  938. {
  939. $return .= 'v';
  940. }
  941. $return .= ';';
  942. }
  943. }
  944. }
  945. }
  946. $return .= "\n";
  947. return $return;
  948. }
  949. /**
  950. * This function displays the comparative report which allows you to compare two questions
  951. * A comparative report creates a table where one question is on the x axis and a second question is on the y axis.
  952. * In the intersection is the number of people who have answerd positive on both options.
  953. *
  954. * @return html code
  955. *
  956. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  957. * @version February 2007
  958. */
  959. function display_comparative_report()
  960. {
  961. // allowed question types for comparative report
  962. $allowed_question_types = array('yesno', 'multiplechoice', 'multipleresponse', 'dropdown', 'percentage', 'score');
  963. // getting all the questions
  964. $questions = survey_manager::get_questions($_GET['survey_id']);
  965. // displaying an information message that only the questions with predefined answers can be used in a comparative report
  966. Display::display_normal_message(get_lang('OnlyQuestionsWithPredefinedAnswers'), false);
  967. // The form for selecting the axis of the table
  968. echo '<form id="form1" name="form1" method="get" action="'.api_get_self().'?action='.$_GET['action'].'&survey_id='.$_GET['survey_id'].'&xaxis='.$_GET['xaxis'].'&y='.$_GET['yaxis'].'">';
  969. // survey_id
  970. echo '<input type="hidden" name="action" value="'.$_GET['action'].'"/>';
  971. echo '<input type="hidden" name="survey_id" value="'.(int)$_GET['survey_id'].'"/>';
  972. // X axis
  973. echo get_lang('SelectXAxis').': ';
  974. echo '<select name="xaxis">';
  975. echo '<option value="">---</option>';
  976. foreach ($questions as $key=>$question)
  977. {
  978. if (in_array($question['type'], $allowed_question_types))
  979. {
  980. echo '<option value="'.$question['question_id'].'"';
  981. if ($_GET['xaxis'] == $question['question_id'])
  982. {
  983. echo ' selected="selected"';
  984. }
  985. echo '">'.substr(strip_tags($question['question']), 0, 50).'</option>';
  986. }
  987. }
  988. echo '</select><br /><br />';
  989. // Y axis
  990. echo get_lang('SelectYAxis').': ';
  991. echo '<select name="yaxis">';
  992. echo '<option value="">---</option>';
  993. foreach ($questions as $key=>$question)
  994. {
  995. if (in_array($question['type'], $allowed_question_types))
  996. {
  997. echo '<option value="'.$question['question_id'].'"';
  998. if ($_GET['yaxis'] == $question['question_id'])
  999. {
  1000. echo ' selected="selected"';
  1001. }
  1002. echo '">'.substr(strip_tags($question['question']), 0, 50).'</option>';
  1003. }
  1004. }
  1005. echo '</select><br /><br />';
  1006. echo '<input type="submit" name="Submit" value="Submit" />';
  1007. echo '</form>';
  1008. // getting all the information of the x axis
  1009. if (isset($_GET['xaxis']) AND is_numeric($_GET['xaxis']))
  1010. {
  1011. $question_x = survey_manager::get_question($_GET['xaxis']);
  1012. }
  1013. // getting all the information of the y axis
  1014. if (isset($_GET['yaxis']) AND is_numeric($_GET['yaxis']))
  1015. {
  1016. $question_y = survey_manager::get_question($_GET['yaxis']);
  1017. }
  1018. if (isset($_GET['xaxis']) AND is_numeric($_GET['xaxis']) AND isset($_GET['yaxis']) AND is_numeric($_GET['yaxis']))
  1019. {
  1020. // getting the answers of the two questions
  1021. $answers_x = get_answers_of_question_by_user($_GET['survey_id'], $_GET['xaxis']);
  1022. $answers_y = get_answers_of_question_by_user($_GET['survey_id'], $_GET['yaxis']);
  1023. // displaying the table
  1024. echo '<table border="1" class="data_table">';
  1025. // the header
  1026. echo ' <tr>';
  1027. for ($ii=0; $ii<=count($question_x['answers']); $ii++)
  1028. {
  1029. if ($ii == 0)
  1030. {
  1031. echo ' <th>&nbsp;</th>';
  1032. }
  1033. else
  1034. {
  1035. if ($question_x['type']=='score')
  1036. {
  1037. for($x=1; $x<=$question_x['maximum_score']; $x++)
  1038. {
  1039. echo ' <th>'.$question_x['answers'][($ii-1)].'<br />'.$x.'</th>';
  1040. }
  1041. $x='';
  1042. }
  1043. else
  1044. {
  1045. echo ' <th>'.$question_x['answers'][($ii-1)].'</th>';
  1046. }
  1047. }
  1048. }
  1049. echo ' </tr>';
  1050. // the main part
  1051. for ($ij=0; $ij<count($question_y['answers']); $ij++)
  1052. {
  1053. // The Y axis is a scoring question type so we have more rows than the options (actually options * maximum score)
  1054. if ($question_y['type'] == 'score')
  1055. {
  1056. for($y=1; $y<=$question_y['maximum_score']; $y++)
  1057. {
  1058. echo ' <tr>';
  1059. for ($ii=0; $ii<=count($question_x['answers']); $ii++)
  1060. {
  1061. if ($question_x['type']=='score')
  1062. {
  1063. for($x=1; $x<=$question_x['maximum_score']; $x++)
  1064. {
  1065. if ($ii == 0)
  1066. {
  1067. echo ' <th>'.$question_y['answers'][($ij)].' '.$y.'</th>';
  1068. break;
  1069. }
  1070. else
  1071. {
  1072. echo ' <td align="center">';
  1073. echo comparative_check($answers_x, $answers_y, $question_x['answersid'][($ii-1)], $question_y['answersid'][($ij)], $x, $y);
  1074. echo '</td>';
  1075. }
  1076. }
  1077. }
  1078. else
  1079. {
  1080. if ($ii == 0)
  1081. {
  1082. echo ' <th>'.$question_y['answers'][($ij)].' '.$y.'</th>';
  1083. }
  1084. else
  1085. {
  1086. echo ' <td align="center">';
  1087. echo comparative_check($answers_x, $answers_y, $question_x['answersid'][($ii-1)], $question_y['answersid'][($ij)], 0, $y);
  1088. echo '</td>';
  1089. }
  1090. }
  1091. }
  1092. echo ' </tr>';
  1093. }
  1094. }
  1095. // The Y axis is NOT a score question type so the number of rows = the number of options
  1096. else
  1097. {
  1098. echo ' <tr>';
  1099. for ($ii=0; $ii<=count($question_x['answers']); $ii++)
  1100. {
  1101. if ($question_x['type']=='score')
  1102. {
  1103. for($x=1; $x<=$question_x['maximum_score']; $x++)
  1104. {
  1105. if ($ii == 0)
  1106. {
  1107. echo ' <th>'.$question_y['answers'][($ij)].'</th>';
  1108. break;
  1109. }
  1110. else
  1111. {
  1112. echo ' <td align="center">';
  1113. echo comparative_check($answers_x, $answers_y, $question_x['answersid'][($ii-1)], $question_y['answersid'][($ij)], $x, 0);
  1114. echo '</td>';
  1115. }
  1116. }
  1117. }
  1118. else
  1119. {
  1120. if ($ii == 0)
  1121. {
  1122. echo ' <th>'.$question_y['answers'][($ij)].'</th>';
  1123. }
  1124. else
  1125. {
  1126. echo ' <td align="center">';
  1127. echo comparative_check($answers_x, $answers_y, $question_x['answersid'][($ii-1)], $question_y['answersid'][($ij)]);
  1128. echo '</td>';
  1129. }
  1130. }
  1131. }
  1132. echo ' </tr>';
  1133. }
  1134. }
  1135. echo '</table>';
  1136. }
  1137. }
  1138. /**
  1139. * get all the answers of a question grouped by user
  1140. *
  1141. * @param integer $survey_id the id of the survey
  1142. * @param integer $question_id the id of the question
  1143. * @return array $return an array countaining all the answers of all the users grouped by user
  1144. *
  1145. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  1146. * @version February 2007
  1147. */
  1148. function get_answers_of_question_by_user($survey_id, $question_id)
  1149. {
  1150. // Database table definitions
  1151. $table_survey_question = Database :: get_course_table(TABLE_SURVEY_QUESTION);
  1152. $table_survey_question_option = Database :: get_course_table(TABLE_SURVEY_QUESTION_OPTION);
  1153. $table_survey_answer = Database :: get_course_table(TABLE_SURVEY_ANSWER);
  1154. $sql = "SELECT * FROM $table_survey_answer
  1155. WHERE survey_id='".Database::escape_string($survey_id)."'
  1156. AND question_id='".Database::escape_string($question_id)."'
  1157. ORDER BY USER ASC";
  1158. $result = api_sql_query($sql, __FILE__, __LINE__);
  1159. while ($row = mysql_fetch_assoc($result))
  1160. {
  1161. if ($row['value'] == 0)
  1162. {
  1163. $return[$row['user']][] = $row['option_id'];
  1164. }
  1165. else
  1166. {
  1167. $return[$row['user']][] = $row['option_id'].'*'.$row['value'];
  1168. }
  1169. }
  1170. return $return;
  1171. }
  1172. /**
  1173. * count the number of users who answer positively on both options
  1174. *
  1175. * @param array $answers_x all the answers of the x axis
  1176. * @param array $answers_y all the answers of the y axis
  1177. * @param integer $option_x the x axis value (= the option_id of the first question)
  1178. * @param integer $option_y the y axis value (= the option_id of the second question)
  1179. * @return integer the number of users who have answered positively on both options
  1180. *
  1181. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  1182. * @version February 2007
  1183. */
  1184. function comparative_check($answers_x, $answers_y, $option_x, $option_y, $value_x=0, $value_y=0)
  1185. {
  1186. if ($value_x==0)
  1187. {
  1188. $check_x = $option_x;
  1189. }
  1190. else
  1191. {
  1192. $check_x = $option_x.'*'.$value_x;
  1193. }
  1194. if ($value_y==0)
  1195. {
  1196. $check_y = $option_y;
  1197. }
  1198. else
  1199. {
  1200. $check_y = $option_y.'*'.$value_y;
  1201. }
  1202. $counter = 0;
  1203. foreach ($answers_x as $user => $answers)
  1204. {
  1205. // check if the user has given $option_x as answer
  1206. if (in_array($check_x, $answers))
  1207. {
  1208. // check if the user has given $option_y as an answer
  1209. if (in_array($check_y, $answers_y[$user]))
  1210. {
  1211. $counter++;
  1212. }
  1213. }
  1214. }
  1215. return $counter;
  1216. }
  1217. ?>