1<?php 2/* 3 * LimeSurvey 4 * Copyright (C) 2007-2011 The LimeSurvey Project Team / Carsten Schmitz 5 * All rights reserved. 6 * License: GNU/GPL License v2 or later, see LICENSE.php 7 * LimeSurvey is free software. This version may have been modified pursuant 8 * to the GNU General Public License, and as distributed it includes or 9 * is derivative of works licensed under the GNU General Public License or 10 * other free or open source software licenses. 11 * See COPYRIGHT.php for copyright notices and details. 12 * 13 */ 14 15/* 16 * Created 12-2008 by Maziminke (maziminke@web.de) 17 * 18 * This file handles the "Show results to users" option: 19 * Survey Settings -> Presentation & navigation -> "Public statistics?" 20 * 21 * The admin can set a question attribute "public_statistics" for each question 22 * to determine whether the results of a certain question should be shown to the user 23 * after he/she has submitted the survey. 24 * 25 */ 26 27class Statistics_userController extends SurveyController 28{ 29 30 /** 31 * @var int 32 */ 33 public $iSurveyID; 34 35 /** 36 * @var string 37 */ 38 public $sLanguage; 39 40 /** 41 * @param mixed $method 42 * @param array $params 43 * @return array 44 */ 45 public function _remap($method, $params = array()) 46 { 47 array_unshift($params, $method); 48 return call_user_func_array(array($this, "action"), $params); 49 } 50 51 /** 52 * @param int $surveyid 53 * @param string $language 54 * @throws CHttpException 55 */ 56 public function actionAction($surveyid, $language = null) 57 { 58 $sLanguage = $language; 59 $survey = Survey::model()->findByPk($surveyid); 60 61 $this->sLanguage = $language; 62 63 $iSurveyID = (int) $survey->sid; 64 $this->iSurveyID = $survey->sid; 65 66 //$postlang = returnglobal('lang'); 67 //~ Yii::import('application.libraries.admin.progressbar',true); 68 Yii::app()->loadHelper("userstatistics"); 69 Yii::app()->loadHelper('database'); 70 Yii::app()->loadHelper('surveytranslator'); 71 $data = array(); 72 73 if (!isset($iSurveyID)) { 74 $iSurveyID = returnGlobal('sid'); 75 } else { 76 $iSurveyID = (int) $iSurveyID; 77 } 78 if (!$iSurveyID) { 79 //This next line ensures that the $iSurveyID value is never anything but a number. 80 throw new CHttpException(404, 'You have to provide a valid survey ID.'); 81 } 82 83 84 $actresult = Survey::model()->findAll('sid = :sid AND active = :active', array(':sid' => $iSurveyID, ':active' => 'Y')); //Checked 85 if (count($actresult) == 0) { 86 throw new CHttpException(404, 'You have to provide a valid survey ID.'); 87 } else { 88 $surveyinfo = getSurveyInfo($iSurveyID); 89 // CHANGE JSW_NZ - let's get the survey title for display 90 $thisSurveyTitle = $surveyinfo["name"]; 91 // CHANGE JSW_NZ - let's get css from individual template.css - so define path 92 $thisSurveyCssPath = getTemplateURL($surveyinfo["template"]); 93 if ($surveyinfo['publicstatistics'] != 'Y') { 94 throw new CHttpException(404, 'The public statistics for this survey are deactivated.'); 95 } 96 97 //check if graphs should be shown for this survey 98 if ($survey->isPublicGraphs) { 99 $publicgraphs = 1; 100 } else { 101 $publicgraphs = 0; 102 } 103 } 104 105 //we collect all the output within this variable 106 $statisticsoutput = ''; 107 108 //for creating graphs we need some more scripts which are included here 109 //True -> include 110 //False -> forget about charts 111 if (isset($publicgraphs) && $publicgraphs == 1) { 112 require_once(APPPATH.'third_party/pchart/pChart.class.php'); 113 require_once(APPPATH.'third_party/pchart/pData.class.php'); 114 require_once(APPPATH.'third_party/pchart/pCache.class.php'); 115 116 $MyCache = new pCache(Yii::app()->getConfig("tempdir").DIRECTORY_SEPARATOR); 117 //$currentuser is created as prefix for pchart files 118 if (isset($_SERVER['REDIRECT_REMOTE_USER'])) { 119 $currentuser = $_SERVER['REDIRECT_REMOTE_USER']; 120 } else if (session_id()) { 121 $currentuser = substr(session_id(), 0, 15); 122 } else { 123 $currentuser = "standard"; 124 } 125 } 126 // Set language for questions and labels to base language of this survey 127 if ($sLanguage == null || !in_array($sLanguage, Survey::model()->findByPk($iSurveyID)->getAllLanguages())) { 128 $sLanguage = Survey::model()->findByPk($iSurveyID)->language; 129 } else { 130 $sLanguage = sanitize_languagecode($sLanguage); 131 } 132 //set survey language for translations 133 SetSurveyLanguage($iSurveyID, $sLanguage); 134 //Create header 135 $condition = false; 136 $sitename = Yii::app()->getConfig("sitename"); 137 138 $data['surveylanguage'] = $sLanguage; 139 $data['sitename'] = $sitename; 140 $data['condition'] = $condition; 141 $data['thisSurveyCssPath'] = $thisSurveyCssPath; 142 143 /* 144 * only show questions where question attribute "public_statistics" is set to "1" 145 */ 146 147 $quotedGroups = Yii::app()->db->quoteTableName('{{groups}}'); 148 $query = "SELECT q.* , group_name, group_order FROM {{questions}} q, $quotedGroups g, {{question_attributes}} qa 149 WHERE g.gid = q.gid AND g.language = :lang1 AND q.language = :lang2 AND q.sid = :surveyid AND q.qid = qa.qid AND q.parent_qid = 0 AND qa.attribute = 'public_statistics'"; 150 $databasetype = Yii::app()->db->getDriverName(); 151 if ($databasetype == 'mssql' || $databasetype == "sqlsrv" || $databasetype == "dblib") { 152 $query .= " AND CAST(CAST(qa.value as varchar) as int)='1'\n"; 153 } else { 154 $query .= " AND qa.value='1'\n"; 155 } 156 157 //execute query 158 $result = Yii::app()->db->createCommand($query)->bindParam(":lang1", $sLanguage, PDO::PARAM_STR)->bindParam(":lang2", $sLanguage, PDO::PARAM_STR)->bindParam(":surveyid", $iSurveyID, PDO::PARAM_INT)->queryAll(); 159 160 //store all the data in $rows 161 $rows = $result; 162 163 164 //SORT IN NATURAL ORDER! 165 usort($rows, 'groupOrderThenQuestionOrder'); 166 167 //put the question information into the filter array 168 $filters = array(); 169 foreach ($rows as $row) { 170 //store some column names in $filters array 171 $filters[] = array($row['qid'], 172 $row['gid'], 173 $row['type'], 174 $row['title'], 175 $row['group_name'], 176 flattenText($row['question'])); 177 } 178 179 //number of records for this survey 180 $totalrecords = 0; 181 182 //count number of answers 183 $query = "SELECT count(*) FROM ".$survey->responsesTableName; 184 185 // Usually you don't want to see incomplete resposnes 186 //this setting is taken from config-defaults.php 187 if (Yii::app()->getConfig("filterout_incomplete_answers") == 'complete') { 188 $query .= " WHERE ".$survey->responsesTableName.".submitdate is not null"; 189 } 190 $result = Yii::app()->db->createCommand($query)->queryAll(); 191 192 //$totalrecords = total number of answers 193 foreach ($result as $row) { 194 $totalrecords = reset($row); 195 } 196 197 //...while this is the array from copy/paste which we don't want to replace because this is a nasty source of error 198 $allfields = array(); 199 200 //---------- CREATE SGQA OF ALL QUESTIONS WHICH USE "PUBLIC_STATISTICS" ---------- 201 202 /* 203 * let's go through the filter array which contains 204 * ['qid'], 205 ['gid'], 206 ['type'], 207 ['title'], 208 ['group_name'], 209 ['question']; 210 */ 211 212 $currentgroup = ''; 213 // use to check if there are any question with public statistics 214 if (isset($filters)) { 215 $allfields = $this->createSGQA($filters); 216 }// end if -> for removing the error message in case there are no filters 217 218 $summary = $allfields; 219 220 // Get the survey inforamtion 221 $thissurvey = getSurveyInfo($surveyid, $sLanguage); 222 223 //SET THE TEMPLATE DIRECTORY 224 //---------- CREATE STATISTICS ---------- 225 226 227 228 //some progress bar stuff 229 230 // Create progress bar which is shown while creating the results 231 //~ $prb = new ProgressBar(); 232 //~ $prb->pedding = 2; // Bar Pedding 233 //~ $prb->brd_color = "#404040 #dfdfdf #dfdfdf #404040"; // Bar Border Color 234 235 //~ $prb->setFrame(); // set ProgressBar Frame 236 //~ $prb->frame['left'] = 50; // Frame position from left 237 //~ $prb->frame['top'] = 80; // Frame position from top 238 //~ $prb->addLabel('text','txt1',gT("Please wait ...")); // add Text as Label 'txt1' and value 'Please wait' 239 //~ $prb->addLabel('percent','pct1'); // add Percent as Label 'pct1' 240 //~ $prb->addButton('btn1',gT('Go back'),'?action=statistics&sid='.$iSurveyID); // add Button as Label 'btn1' and action '?restart=1' 241 242 //~ $prb->show(); // show the ProgressBar 243 244 //~ // 1: Get list of questions with answers chosen 245 //~ //"Getting Questions and Answer ..." is shown above the bar 246 //~ $prb->setLabelValue('txt1',gT('Getting questions and answers ...')); 247 //~ $prb->moveStep(5); 248 249 // creates array of post variable names 250 $postvars = array(); 251 for (reset($_POST); $key = key($_POST); next($_POST)) { 252 $postvars[] = $key; 253 } 254 $data['thisSurveyTitle'] = $thisSurveyTitle; 255 $data['totalrecords'] = $totalrecords; 256 $data['summary'] = $summary; 257 //show some main data at the beginnung 258 // CHANGE JSW_NZ - let's allow html formatted questions to show 259 260 261 //push progress bar from 35 to 40 262 $process_status = 40; 263 264 //Show Summary results 265 if (isset($summary) && !empty($summary)) { 266 //"Generating Summaries ..." is shown above the progress bar 267 //~ $prb->setLabelValue('txt1',gT('Generating summaries ...')); 268 //~ $prb->moveStep($process_status); 269 270 //let's run through the survey // Fixed bug 3053 with array_unique 271 $runthrough = array_unique($summary); 272 273 //loop through all selected questions 274 foreach ($runthrough as $rt) { 275 276 //update progress bar 277 if ($process_status < 100) { 278 $process_status++; 279 } 280 //~ $prb->moveStep($process_status); 281 282 } // end foreach -> loop through all questions 283 284 $helper = new userstatistics_helper(); 285 $statisticsoutput .= $helper->generate_statistics($iSurveyID, $summary, $summary, $publicgraphs, 'html', null, $sLanguage, false); 286 287 } //end if -> show summary results 288 289 $data['statisticsoutput'] = $statisticsoutput; 290 //done! set progress bar to 100% 291 if (isset($prb)) { 292 //~ $prb->setLabelValue('txt1',gT('Completed')); 293 //~ $prb->moveStep(100); 294 //~ $prb->hide(); 295 } 296 297 Yii::app()->getClientScript()->registerScriptFile(Yii::app()->getConfig('generalscripts').'statistics_user.js'); 298 $this->layout = "public"; 299 $this->render('/statistics_user_view', $data); 300 301 //Delete all Session Data 302 Yii::app()->session['finished'] = true; 303 } 304 305 /** 306 * Create SGQA of all questions which use "public_statistics" 307 * Assumes this->sLanguage and this->iSurveyID is set. 308 * @param array $filters 309 * @return array 310 */ 311 public function createSGQA(array $filters) 312 { 313 $allfields = array(); 314 315 foreach ($filters as $flt) { 316 //SGQ identifier 317 $myfield = $this->iSurveyID.'X'.$flt[1].'X'.$flt[0]; 318 319 //let's switch through the question type for each question 320 switch ($flt[2]) { 321 case "K": // Multiple Numerical 322 case "Q": // Multiple Short Text 323 //get answers 324 $query = "SELECT title as code, question as answer FROM {{questions}} WHERE parent_qid=:flt_0 AND language = :lang ORDER BY question_order"; 325 $result = Yii::app()->db->createCommand($query)->bindParam(":flt_0", $flt[0], PDO::PARAM_INT)->bindParam(":lang", $this->sLanguage, PDO::PARAM_STR)->queryAll(); 326 327 //go through all the (multiple) answers 328 foreach ($result as $row) { 329 $myfield2 = $flt[2].$myfield.reset($row); 330 $allfields[] = $myfield2; 331 } 332 break; 333 case "A": // ARRAY OF 5 POINT CHOICE QUESTIONS 334 case "B": // ARRAY OF 10 POINT CHOICE QUESTIONS 335 case "C": // ARRAY OF YES\No\gT("Uncertain") QUESTIONS 336 case "E": // ARRAY OF Increase/Same/Decrease QUESTIONS 337 case "F": // FlEXIBLE ARRAY 338 case "H": // ARRAY (By Column) 339 //get answers 340 $query = "SELECT title as code, question as answer FROM {{questions}} WHERE parent_qid=:flt_0 AND language = :lang ORDER BY question_order"; 341 $result = Yii::app()->db->createCommand($query)->bindParam(":flt_0", $flt[0], PDO::PARAM_INT)->bindParam(":lang", $this->sLanguage, PDO::PARAM_STR)->queryAll(); 342 343 //go through all the (multiple) answers 344 foreach ($result as $row) { 345 $myfield2 = $myfield.reset($row); 346 $allfields[] = $myfield2; 347 } 348 break; 349 // all "free text" types (T, U, S) get the same prefix ("T") 350 case "T": // Long free text 351 case "U": // Huge free text 352 case "S": // Short free text 353 $myfield = "T".$myfield; 354 $allfields[] = $myfield; 355 break; 356 case ";": //ARRAY (Multi Flex) (Text) 357 case ":": //ARRAY (Multi Flex) (Numbers) 358 $query = "SELECT title, question FROM {{questions}} WHERE parent_qid=:flt_0 AND language=:lang AND scale_id = 0 ORDER BY question_order"; 359 $result = Yii::app()->db->createCommand($query)->bindParam(":flt_0", $flt[0], PDO::PARAM_INT)->bindParam(":lang", $this->sLanguage, PDO::PARAM_STR)->queryAll(); 360 foreach ($result as $row) { 361 $fquery = "SELECT * FROM {{questions}} WHERE parent_qid = :flt_0 AND language = :lang AND scale_id = 1 ORDER BY question_order, title"; 362 $fresult = Yii::app()->db->createCommand($fquery)->bindParam(":flt_0", $flt[0], PDO::PARAM_INT)->bindParam(":lang", $this->sLanguage, PDO::PARAM_STR)->queryAll(); 363 foreach ($fresult as $frow) { 364 $myfield2 = $myfield.reset($row)."_".$frow['title']; 365 $allfields[] = $myfield2; 366 } 367 } 368 break; 369 case "R": //RANKING 370 //get some answers 371 $query = "SELECT code, answer FROM {{answers}} WHERE qid = :flt_0 AND language = :lang ORDER BY sortorder, answer"; 372 $result = Yii::app()->db->createCommand($query)->bindParam(":flt_0", $flt[0], PDO::PARAM_INT)->bindParam(":lang", $this->sLanguage, PDO::PARAM_STR)->queryAll(); 373 374 //get number of answers 375 $count = count($result); 376 377 //loop through all answers. if there are 3 items to rate there will be 3 statistics 378 for ($i = 1; $i <= $count; $i++) { 379 $myfield2 = "R".$myfield.$i."-".strlen($i); 380 $allfields[] = $myfield2; 381 } 382 break; 383 //Boilerplate questions are only used to put some text between other questions -> no analysis needed 384 case "X": //This is a boilerplate question and it has no business in this script 385 break; 386 case "1": // MULTI SCALE 387 //get answers 388 $query = "SELECT title, question FROM {{questions}} WHERE parent_qid = :flt_0 AND language = :lang ORDER BY question_order"; 389 $result = Yii::app()->db->createCommand($query)->bindParam(":flt_0", $flt[0], PDO::PARAM_INT)->bindParam(":lang", $this->sLanguage, PDO::PARAM_STR)->queryAll(); 390 391 //loop through answers 392 foreach ($result as $row) { 393 //----------------- LABEL 1 --------------------- 394 $myfield2 = $myfield.$row['title']."#0"; 395 $allfields[] = $myfield2; 396 //----------------- LABEL 2 --------------------- 397 $myfield2 = $myfield.$row['title']."#1"; 398 $allfields[] = $myfield2; 399 } //end WHILE -> loop through all answers 400 break; 401 402 case "P": //P - Multiple choice with comments 403 case "M": //M - Multiple choice 404 case "N": //N - Numerical input 405 case "D": //D - Date 406 $myfield2 = $flt[2].$myfield; 407 $allfields[] = $myfield2; 408 break; 409 default: //Default settings 410 $allfields[] = $myfield; 411 break; 412 413 } //end switch -> check question types and create filter forms 414 } 415 416 return $allfields; 417 } 418} 419