survey_report.php 16 KB

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