survey_report.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503
  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 des Palais 44 Paleizenstraat
  13. B-1030 Brussels - Belgium
  14. Tel. +32 (2) 211 34 56
  15. */
  16. /**
  17. * @package dokeos.survey
  18. * @author
  19. * @version $Id: survey_report.php 10705 2007-01-12 22:40:01Z pcool $
  20. */
  21. /*
  22. ==============================================================================
  23. INIT SECTION
  24. ==============================================================================
  25. */
  26. // name of the language file that needs to be included
  27. $language_file = 'survey';
  28. // including the global dokeos file
  29. require_once ('../inc/global.inc.php');
  30. // including additional libraries
  31. /** @todo check if these are all needed */
  32. /** @todo check if the starting / is needed. api_get_path probably ends with an / */
  33. require_once (api_get_path(LIBRARY_PATH)."/course.lib.php");
  34. require_once (api_get_path(LIBRARY_PATH)."/groupmanager.lib.php");
  35. require_once (api_get_path(LIBRARY_PATH)."/surveymanager.lib.php");
  36. $table_survey = Database :: get_course_table(TABLE_SURVEY);
  37. $surveyid=$_REQUEST['surveyid'];
  38. if($_SESSION['status']==5)
  39. {
  40. api_protect_admin_script();
  41. }
  42. $screen = isset($_POST['screen']) ? $_POST['screen'] : '1' ;
  43. $tool_name = get_lang('SurveyReporting');
  44. $interbreadcrumb[] = array ("url" => "survey_list.php", "name" => get_lang('Survey'));
  45. Display::display_header($tool_name);
  46. switch($screen) {
  47. // first screen : base
  48. case '1' :
  49. echo '<h1>'.get_lang('SelectDataYouWantToCompare').'</h1>';
  50. echo get_lang('HelpComparative');
  51. $questions = SurveyManager::listQuestions($surveyid);
  52. $menu_left = '<select name="left[]" size="20" multiple style="width:500px">';
  53. $menu_left .= '
  54. <option value="name">'.get_lang('Name').'</option>
  55. <option value="email">'.get_lang('EmailAddress').'</option>
  56. <option value="organisation">'.get_lang('Organisation').'</option>
  57. ';
  58. foreach ($questions as $key=>$question) {
  59. //if($question['qtype']!='Numbered' && $question['qtype']!='Open Answer')
  60. $menu_left .= '<option value="'.$question['qid'].'">Q'.($key+1).' - '.substr(stripslashes(strip_tags($question['caption'])),0,80).'...</option>';
  61. }
  62. $menu_left .= '</select>';
  63. /*
  64. $menu_right = '<select name="right[]" size="10" multiple style="width:250px">';
  65. $menu_right .= '
  66. <option value="name">'.get_lang('Name').'</option>
  67. <option value="email">'.get_lang('EmailAddress').'</option>
  68. <option value="organisation">'.get_lang('Organisation').'</option>
  69. ';
  70. foreach ($questions as $key=>$question) {
  71. $menu_right .= '<option value="'.$question['qid'].'">Q'.($key+1).' - '.substr(stripslashes(strip_tags($question['caption'])),0,50).'...</option>';
  72. }
  73. $menu_right .= '</select>';
  74. */
  75. echo ' <form method="POST">
  76. <table>
  77. <tr>
  78. <td>'.$menu_left.'</td>
  79. </tr>
  80. <tr>
  81. <td align="center">
  82. <input type="hidden" name="screen" value="2" />
  83. <input type="submit" value="'.get_lang('Ok').'"
  84. </td>
  85. </table>
  86. </form>';
  87. break;
  88. //second screen : details
  89. case '2' :
  90. echo '<h1>'.get_lang('PreciseWhatYouWantToCompare').'</h1>';
  91. $left_questions = $_POST['left'];
  92. $right_questions = $_POST['right'];
  93. // prepare the left menu
  94. $menu_left = '<select id="leftmenu" name="left[]" size="20" multiple style="width:500px">';
  95. $users = SurveyManager :: listUsers($surveyid, $db_name);
  96. $keyname = array_search('name', $left_questions);
  97. if(is_int($keyname)){
  98. $menu_left .= '<option value="name" disabled onclick="selectAllItems(\'name\')">'.get_lang('Name').'</option>';
  99. foreach($users as $user) {
  100. $menu_left .= '<option value="name|'.$user['lastname'].' '.$user['firstname'].'">---- '.$user['lastname'].' '.$user['firstname'].'</option>';
  101. }
  102. unset($left_questions[$keyname]);
  103. }
  104. $keyorg = array_search('organisation', $left_questions);
  105. if(is_int($keyorg)){
  106. $menu_left .= '<option value="organisation" disabled onclick="selectAllItems(\'organisation\')">'.get_lang('Organisation').'</option>';
  107. foreach($users as $user) {
  108. $menu_left .= '<option value="organisation|'.$user['organization'].'">---- '.$user['organization'].'</option>';
  109. }
  110. unset($left_questions[$keyorg]);
  111. }
  112. $keymail = array_search('email', $left_questions);
  113. if(is_int($keymail)){
  114. $menu_left .= '<option value="email" disabled onclick="selectAllItems(\'email\')">'.get_lang('EmailAddress').'</option>';
  115. foreach($users as $user) {
  116. $menu_left .= '<option value="email|'.$user['email'].'">---- '.$user['email'].'</option>';
  117. }
  118. unset($left_questions[$keymail]);
  119. }
  120. foreach($left_questions as $question){
  121. $question = SurveyManager::get_question_data($question, $db_name);
  122. $menu_left .= '<option value="question|'.$question->qid.'" onclick="selectAllAnswers(\''.$question->qid.'\')">Q'.($question->sortby+1).' - '.substr(stripslashes(strip_tags($question->caption)),0,50).'...</option>';
  123. if($question->qtype!='Open Answer' && $question->qtype!='Numbered'){
  124. foreach($question as $key=>$reponse) {
  125. if(substr($key,0,1)=='a' && $key!='alignment' && !empty($reponse)){
  126. $menu_left .= '<option value="answer|'.$question->qid.'|'.stripslashes(strip_tags($key)).'">---- '.substr(stripslashes(strip_tags($reponse)),0,50).'</option>';
  127. }
  128. }
  129. }
  130. }
  131. $menu_left .= '</select>';
  132. // prepare the right menu
  133. /*
  134. $menu_right = '<select name="right[]" size="10" multiple style="width:250px">';
  135. $keyname = array_search('name', $right_questions);
  136. if(is_int($keyname)){
  137. $menu_right .= '<option value="name" disabled>'.get_lang('Name').'</option>';
  138. foreach($users as $user) {
  139. $menu_right .= '<option value="name|'.$user['lastname'].' '.$user['firstname'].'">---- '.$user['lastname'].' '.$user['firstname'].'</option>';
  140. }
  141. unset($right_questions[$keyname]);
  142. }
  143. $keyorg = array_search('organisation', $right_questions);
  144. if(is_int($keyorg)){
  145. $menu_right .= '<option value="organisation" disabled>'.get_lang('Organisation').'</option>';
  146. foreach($users as $user) {
  147. $menu_right .= '<option value="organisation|'.$user['organization'].'">---- '.$user['organization'].'</option>';
  148. }
  149. unset($right_questions[$keyorg]);
  150. }
  151. $keymail = array_search('email', $right_questions);
  152. if(is_int($keymail)){
  153. $menu_right .= '<option value="email" disabled>'.get_lang('EmailAddress').'</option>';
  154. foreach($users as $user) {
  155. $menu_right .= '<option value="email|'.$user['email'].'">---- '.$user['email'].'</option>';
  156. }
  157. unset($right_questions[$keymail]);
  158. }
  159. foreach($right_questions as $question){
  160. $question = SurveyManager::get_question_data($question, $db_name);
  161. $menu_right .= '<option value="question|'.$question->qid.'" selected>Q'.($question->sortby+1).' - '.substr(stripslashes(strip_tags($question->caption)),0,50).'...</option>';
  162. /*
  163. foreach($question as $key=>$reponse) {
  164. if(substr($key,0,1)=='a' && !empty($reponse)){
  165. $menu_right .= '<option value="answer|'.$question->qid.'|'.stripslashes(strip_tags($reponse)).'">---- '.substr(stripslashes(strip_tags($reponse)),0,50).'</option>';
  166. }
  167. }
  168. /*
  169. $list_answers = SurveyManager::listAnswers($question->qid);
  170. foreach($list_answers as $answer){
  171. }
  172. }
  173. $menu_right .= '</select>';
  174. */
  175. echo ' <form method="POST">
  176. <table>
  177. <tr>
  178. <td>'.$menu_left.'</td>
  179. </tr>
  180. <tr>
  181. <td align="center">
  182. <input type="hidden" name="screen" value="3" />
  183. <input type="submit" value="'.get_lang('Ok').'"
  184. </td>
  185. </table>
  186. </form>';
  187. break;
  188. // screen 3 : results
  189. case '3' :
  190. echo '<h1>'.get_lang('ComparativeResults').'</h1>';
  191. $params = array_merge($_POST['left'] , $_POST['right']);
  192. $tbl_user_survey = Database::get_main_table(TABLE_MAIN_SURVEY_USER);
  193. $table_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION);
  194. $tbl_answers = Database::get_course_table(TABLE_SURVEY_REPORT);
  195. //$which_answers = 'WHERE qid';
  196. //$which_questions = 'AND qid IN ()';
  197. $names = $emails = $organisations = $questions = array();
  198. $excel_file_name = 'export_survey-'.$surveyid.$db_name.'.csv';
  199. echo '<a href="../course_info/download.php?archive='.$excel_file_name.'"><img border="0" src="../img/xls.gif" align="middle"/>'.get_lang('ExportInExcel').'</a>';
  200. echo '<table border="1" class="data_table"><td class="cell_header"></td>';
  201. $with_answers = false;
  202. $answers_required = $header_order = array();
  203. $i=0;
  204. $nbColumns = 0;
  205. foreach($params as $param){
  206. $param = explode('|',$param);
  207. switch ($param[0]){ // which type ?
  208. case 'name' :
  209. $names[]='"'.$param[1].'"';
  210. break;
  211. case 'email' :
  212. $emails[]='"'.$param[1].'"';
  213. break;
  214. case 'organisation' :
  215. $organisations[]='"'.$param[1].'"';
  216. break;
  217. case 'question' :
  218. $questions[]='"'.$param[1].'"';
  219. $sql = 'SELECT caption FROM '.$table_survey_question.' WHERE qid='.$param[1];
  220. $rs = api_sql_query($sql);
  221. if($row = mysql_fetch_array($rs)){
  222. $row[0] = eregi_replace('^<p[^>]*>(.*)</p>','\\1', $row[0]);
  223. $row[0] = eregi_replace('(<[^ ]*) (style=."."[^>]*)(>)','\\1\\3', $row[0]);
  224. $row[0] = eregi_replace('(<[^ ]*) (style=.""[^>]*)(>)','\\1\\3', $row[0]);
  225. $row[0] = eregi_replace('(<[^ ]*)( style=."[^"]*")([^>]*)(>)','\\1\\2\\4', $row[0]);
  226. $html_questions .= '<td class="cell_header">'.stripslashes($row[0]).'</td>';
  227. $excel_questions .= stripslashes($row[0]).';';
  228. }
  229. $header_order[$param[1]] = $i;
  230. $i++;
  231. break;
  232. case 'answer' :
  233. $answers_required[] = $param;
  234. $sql = 'SELECT caption FROM '.$table_survey_question.' as questions
  235. INNER JOIN '.$tbl_answers.' as answers
  236. ON answers.qid = questions.qid
  237. AND answers.qid='.$param[1];
  238. $rs = api_sql_query($sql);
  239. if($row = mysql_fetch_array($rs)){
  240. if(!in_array('"'.$param[1].'"',$questions)){
  241. $row[0] = eregi_replace('^<p[^>]*>(.*)</p>','\\1', $row[0]);
  242. $row[0] = eregi_replace('(<[^ ]*) (style=."."[^>]*)(>)','\\1\\3', $row[0]);
  243. $row[0] = eregi_replace('(<[^ ]*) (style=.""[^>]*)(>)','\\1\\3', $row[0]);
  244. $row[0] = eregi_replace('(<[^ ]*)( style=."[^"]*")([^>]*)(>)','\\1\\2\\4', $row[0]);
  245. $html_questions .= '<td class="cell_header">'.stripslashes($row[0]).'</td>';
  246. $excel_questions .= stripslashes($row[0]).';';
  247. $questions[]='"'.$param[1].'"';
  248. $header_order[$param[1]] = $i;
  249. $i++;
  250. }
  251. }
  252. break;
  253. }
  254. }
  255. $select = array();
  256. $excel = '';
  257. if(count($names)){
  258. $nbColumns++;
  259. $select[] = 'CONCAT(lastname," ",firstname) as name';
  260. $which_names = ' AND CONCAT(lastname," ",firstname) IN ('.implode(',',$names).')';
  261. echo '<td class="cell_header">'.get_lang('Name').'</td>';
  262. $excel .= get_lang('Name').';';
  263. }
  264. if(count($emails)) {
  265. $nbColumns++;
  266. $select[] = 'email';
  267. $which_emails = ' AND email IN ('.implode(',',$emails).')';
  268. echo '<td class="cell_header">'.get_lang('EmailAddress').'</td>';
  269. $excel .= get_lang('EmailAddress').';';
  270. }
  271. if(count($organisations)) {
  272. $nbColumns++;
  273. $select[] = 'organization';
  274. $which_organisations = ' AND organization IN ('.implode(',',$organisations).')';
  275. echo '<td class="cell_header">'.get_lang('Organisation').'</td>';
  276. $excel .= get_lang('Organisation').';';
  277. }
  278. if(count($questions)){
  279. $nbColumns += count($questions);
  280. $select[] = 'questions.qid, caption, answer';
  281. $which_questions = ' AND questions.qid IN ('.implode(',',$questions).')';
  282. echo $html_questions;
  283. }
  284. $excel .= $excel_questions;
  285. echo '</tr>';
  286. $excel .="\r\n";
  287. $sql = 'SELECT user_survey.id as id_user_survey, '.implode(',',$select).'
  288. FROM '.$tbl_answers.' as answers,'.$tbl_user_survey.' as user_survey,'.$table_survey_question.' as questions
  289. WHERE answers.qid = questions.qid
  290. AND answers.user_id = user_survey.user_id
  291. AND user_survey.survey_id='.$surveyid.'
  292. AND user_survey.db_name="'.$db_name.'" ';
  293. $sql .= $which_names.' '.$which_emails.' '.$which_organisations.' '.$which_questions;
  294. $rs = api_sql_query($sql, __FILE__, __LINE__);
  295. $answers_to_keep = $user_survey_done = array();
  296. while($row = mysql_fetch_array($rs)) {
  297. $ok = false;
  298. if(count($answers_required)==0){
  299. $ok=true;
  300. }
  301. else {
  302. foreach($answers_required as $param){
  303. $key = $param[1];
  304. $answer = $param[2];
  305. $sql = 'SELECT 1 FROM '.$tbl_answers.' as answers
  306. INNER JOIN '.$tbl_user_survey.' as user_survey
  307. ON user_survey.id = '.$row['id_user_survey'].'
  308. AND user_survey.survey_id='.$surveyid.'
  309. AND user_survey.db_name="'.$db_name.'"
  310. WHERE answers.qid='.$key.'
  311. AND answers.answer = "'.$answer.'"';
  312. $rs2 = api_sql_query($sql);
  313. if(mysql_num_rows($rs2)>0){
  314. $ok = true;
  315. }
  316. }
  317. }
  318. if($ok){
  319. $answers_to_keep[] = $row;
  320. }
  321. }
  322. $stats = array();
  323. $users_survey = array();
  324. foreach($answers_to_keep as $answer) {
  325. if(in_array($answer['answer'],array('a1','a2','a3','a4','a5','a6','a7','a8','a9','a10',))){
  326. $sql = 'SELECT '.$answer['answer'].'
  327. FROM '.$table_survey_question.'
  328. WHERE survey_id = '.$surveyid.'
  329. AND questions.qid='.$answer['qid'];
  330. $rsAnswer = api_sql_query($sql, __FILE__,__LINE__);
  331. $answer['answer'] = mysql_result($rsAnswer,0);
  332. }
  333. $i=0;
  334. $users_survey[$answer['id_user_survey']][0]['answer'] = $answer['id_user_survey'];
  335. $i++;
  336. if(count($names)){
  337. $users_survey[$answer['id_user_survey']][$i]['answer'] = $answer['name'];
  338. $i++;
  339. }
  340. if(count($emails)){
  341. $users_survey[$answer['id_user_survey']][$i]['answer'] = $answer['email'];
  342. $i++;
  343. }
  344. if(count($organisations)){
  345. $users_survey[$answer['id_user_survey']][$i]['answer'] = $answer['organization'];
  346. $i++;
  347. }
  348. if(count($questions)){
  349. $users_survey[$answer['id_user_survey']][$i+$header_order[$answer['qid']]]['answer'] = $answer['answer'];
  350. $stats[$i+$header_order[$answer['qid']]][] = $answer['answer'];
  351. }
  352. }
  353. foreach($users_survey as $user_survey){
  354. echo '<tr>';
  355. for($i=0; $i<=$nbColumns; $i++){
  356. echo '<td>'.(!empty($user_survey[$i]['answer']) ? $user_survey[$i]['answer'] : '-').'</td>';
  357. if($i!=0)
  358. $excel .= str_replace(array("\r","\n"),"",$user_survey[$i]['answer'].';');
  359. }
  360. $excel .= "\r\n";
  361. echo '</tr>';
  362. }
  363. $max = max(array_keys($stats));
  364. echo '<tr style="background-color:yellow">';
  365. echo '<td>Stats</td>';
  366. for($i=1 ; $i<$max+1; $i++){
  367. echo '<td>';
  368. $valeurs = array_count_values($stats[$i]);
  369. foreach($valeurs as $key=>$value){
  370. if(!empty($key))
  371. echo $key.' : '.$value.' votes<br />';
  372. }
  373. echo '</td>';
  374. }
  375. echo '</tr>';
  376. echo '</table>';
  377. $archivePath=api_get_path(SYS_PATH).$archiveDirName.'/';
  378. $handle = fopen($archivePath.$excel_file_name, 'w');
  379. fwrite($handle, $excel);
  380. fclose($handle);
  381. chmod($archivePath.$excel_file_name, 0755);
  382. break;
  383. }
  384. ?>
  385. <script type="text/javascript">
  386. function selectAllItems(type){
  387. myselect = document.getElementById('leftmenu');
  388. for(i=0 ; i<myselect.options.length ; i++){
  389. if(myselect.options[i].value.indexOf(type)!=-1){
  390. myselect.options[i].selected = true;
  391. }
  392. }
  393. }
  394. function selectAllAnswers(qid){
  395. myselect = document.getElementById('leftmenu');
  396. for(i=0 ; i<myselect.options.length ; i++){
  397. if(myselect.options[i].value.indexOf('|'+qid+'|')!=-1){
  398. myselect.options[i].selected = true;
  399. }
  400. }
  401. }
  402. </script>