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 *
17 *  Generate a chart for a question
18 *  @param int $iQuestionID      ID of the question
19 *  @param int $iSurveyID        ID of the survey
20 *  @param mixed $type           Type of the chart to be created - null produces bar chart, any other value produces pie chart
21 *  @param array $lbl            An array containing the labels for the chart items
22 *  @param mixed $gdata          An array containing the percentages for the chart items
23 *  @param mixed $grawdata       An array containing the raw count for the chart items
24 *  @param pCache $cache          An object containing [Hashkey] and [CacheFolder]
25 *  @param mixed $sLanguageCode  Language Code
26 *  @param string $sQuestionType The question type
27 *  @return                false|string
28 */
29function createChart($iQuestionID, $iSurveyID, $type = null, $lbl, $gdata, $grawdata, $cache, $sLanguageCode, $sQuestionType)
30{
31    /* This is a lazy solution to bug #6389. A better solution would be to find out how
32    the "T" gets passed to this function from the statistics.js file in the first place! */
33    if (substr($iSurveyID, 0, 1) == "T") {$iSurveyID = substr($iSurveyID, 1); }
34    static $bErrorGenerate = false;
35
36    if ($bErrorGenerate) {
37        return false;
38    }
39    $rootdir = Yii::app()->getConfig("rootdir");
40    $homedir = Yii::app()->getConfig("homedir");
41    $admintheme = Yii::app()->getConfig("admintheme");
42    $chartfontfile = Yii::app()->getConfig("chartfontfile");
43    $chartfontsize = Yii::app()->getConfig("chartfontsize");
44    $alternatechartfontfile = Yii::app()->getConfig("alternatechartfontfile");
45    $cachefilename = "";
46
47    /* Set the fonts for the chart */
48    if ($chartfontfile == 'auto') {
49        // Tested with ar,be,el,fa,hu,he,is,lt,mt,sr, and en (english)
50        // Not working for hi, si, zh, th, ko, ja : see $config['alternatechartfontfile'] to add some specific language font
51        $chartfontfile = 'DejaVuSans.ttf';
52        if (array_key_exists($sLanguageCode, $alternatechartfontfile)) {
53            $neededfontfile = $alternatechartfontfile[$sLanguageCode];
54            if (is_file($rootdir."/assets/fonts/".$neededfontfile)) {
55                $chartfontfile = $neededfontfile;
56            } else {
57                Yii::app()->setFlashMessage(sprintf(gT('The fonts file %s was not found in <limesurvey root folder>/fonts directory. Please, see the txt file for your language in fonts directory to generate the charts.'), $neededfontfile), 'error');
58                $bErrorGenerate = true; // Don't do a graph again.
59                return false;
60            }
61        }
62    }
63    if (count($lbl) > 72) {
64        $DataSet = array(1=>array(1=>1));
65        if ($cache->IsInCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet) && Yii::app()->getConfig('debug') < 2) {
66            $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet));
67        } else {
68            $graph = new pChart(690, 200);
69            $graph->loadColorPalette(Yii::app()->getConfig('styledir').DIRECTORY_SEPARATOR.$admintheme.DIRECTORY_SEPARATOR.'images/limesurvey.pal');
70            $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
71            $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
72            $graph->drawTitle(0, 0, gT('Sorry, but this question has too many answer options to be shown properly in a graph.', 'unescaped'), 30, 30, 30, 690, 200);
73            $cache->WriteToCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet, $graph);
74            $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet));
75            unset($graph);
76        }
77        return  $cachefilename;
78    }
79    if (array_sum($gdata) == 0) {
80        $DataSet = array(1=>array(1=>1));
81        if ($cache->IsInCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet) && Yii::app()->getConfig('debug') < 2) {
82            $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet));
83        } else {
84            $graph = new pChart(690, 200);
85            $graph->loadColorPalette(Yii::app()->getConfig('styledir').DIRECTORY_SEPARATOR.$admintheme.DIRECTORY_SEPARATOR.'images/limesurvey.pal');
86            $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
87            $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
88            $graph->drawTitle(0, 0, gT('Sorry, but this question has no responses yet so a graph cannot be shown.', 'unescaped'), 30, 30, 30, 690, 200);
89            $cache->WriteToCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet, $graph);
90            $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet));
91            unset($graph);
92        }
93        return  $cachefilename;
94    }
95
96    if (array_sum($gdata) > 0) {
97//Make sure that the percentages add up to more than 0
98        $i = 0;
99        foreach ($gdata as $data) {
100            if ($data != 0) {
101                $i++;
102            }
103        }
104
105        /* Totatllines is the number of entries to show in the key and we need to reduce the font
106        and increase the size of the chart if there are lots of them (ie more than 15) */
107        $totallines = $i;
108        if ($totallines > 15) {
109            $gheight = 320 + (6.7 * ($totallines - 15));
110        } else {
111            $gheight = 320;
112        }
113
114        if (!$type) {
115// Bar chart
116            $DataSet = new pData;
117            $counter = 0;
118            $maxyvalue = 0;
119            foreach ($grawdata as $datapoint) {
120                $DataSet->AddPoint(array($datapoint), "Serie".$counter);
121                $DataSet->AddSerie("Serie".$counter);
122
123                $counter++;
124                if ($datapoint > $maxyvalue) {
125                    $maxyvalue = $datapoint;
126                }
127            }
128
129
130
131            if ($sLanguageCode == 'ar') {
132                if (!class_exists('I18N_Arabic_Glyphs', false)) {
133                    $Arabic = new I18N_Arabic('Glyphs');
134                } else {
135                    $Arabic = new I18N_Arabic_Glyphs();
136                }
137
138                foreach ($lbl as $kkey => $kval) {
139                    if (preg_match("^[A-Za-z]^", $kkey)) {
140//auto detect if english
141                        $lblout[] = $kkey.' ('.$kval.')';
142                    } else {
143                        $lblout[] = $Arabic->utf8Glyphs($kkey.' )'.$kval.'(');
144                    }
145                }
146            } elseif (getLanguageRTL($sLanguageCode)) {
147                foreach ($lbl as $kkey => $kval) {
148                    $lblout[] = UTF8Strrev($kkey.' )'.$kval.'(');
149                }
150            } else {
151                foreach ($lbl as $kkey => $kval) {
152                    $lblout[] = $kkey.' ('.$kval.')';
153                }
154            }
155
156            $counter = 0;
157            foreach ($lblout as $sLabelName) {
158                $DataSet->SetSerieName(html_entity_decode($sLabelName, null, 'UTF-8'), "Serie".$counter);
159                $counter++;
160            }
161
162            if ($cache->IsInCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData()) && Yii::app()->getConfig('debug') < 2) {
163                $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData()));
164            } else {
165                $graph = new pChart(1, 1);
166                $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
167                $legendsize = $graph->getLegendBoxSize($DataSet->GetDataDescription());
168
169                if ($legendsize[1] < 320) {
170                    $gheight = 420;
171                } else {
172                    $gheight = $legendsize[1] + 100;
173                }
174                $graph = new pChart(690 + $legendsize[0], $gheight);
175                $graph->drawFilledRectangle(0, 0, 690 + $legendsize[0], $gheight, 254, 254, 254, false);
176                $graph->loadColorPalette(Yii::app()->getConfig('styledir').DIRECTORY_SEPARATOR.$admintheme.DIRECTORY_SEPARATOR.'images/limesurvey.pal');
177
178                $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
179                $graph->setGraphArea(50, 30, 500, $gheight - 60);
180                $graph->drawFilledRoundedRectangle(7, 7, 523 + $legendsize[0], $gheight - 7, 5, 254, 255, 254);
181                $graph->drawRoundedRectangle(5, 5, 525 + $legendsize[0], $gheight - 5, 5, 230, 230, 230);
182                $graph->drawGraphArea(254, 254, 254, true);
183                $graph->drawScale($DataSet->GetData(), $DataSet->GetDataDescription(), SCALE_START0, 150, 150, 150, true, 90, 0, true, 5, false);
184                $graph->drawGrid(4, true, 230, 230, 230, 50);
185                // Draw the 0 line
186                $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
187                $graph->drawTreshold(0, 143, 55, 72, true, true);
188
189                // Draw the bar graph
190                $graph->drawBarGraph($DataSet->GetData(), $DataSet->GetDataDescription(), false);
191                //$Test->setLabel($DataSet->GetData(),$DataSet->GetDataDescription(),"Serie4","1","Important point!");
192                // Finish the graph
193                $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
194                $graph->drawLegend(510, 30, $DataSet->GetDataDescription(), 250, 250, 250);
195
196                $cache->WriteToCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData(), $graph);
197                $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData()));
198                unset($graph);
199            }
200        }    //end if (bar chart)
201
202        //Pie Chart
203        else {
204            // this block is to remove the items with value == 0
205            // and an inelegant way to remove comments from List with Comments questions
206            $i = 0;
207            $j = 0;
208            $labelTmp = array();
209            while (isset ($gdata[$i])) {
210                $aHelperArray = array_keys($lbl);
211                if ($gdata[$i] == 0 || ($sQuestionType == "O" && substr($aHelperArray[$i], 0, strlen($sLanguageCode->gT("Comments"))) == $sLanguageCode->gT("Comments"))) {
212                    array_splice($gdata, $i, 1);
213                } else {
214                    $i++;
215                    $labelTmp = $labelTmp + array_slice($lbl, $j, 1, true); // Preserve numeric keys for the labels!
216                }
217                $j++;
218            }
219            $lbl = $labelTmp;
220
221            if ($sLanguageCode == 'ar') {
222                if (!class_exists('I18N_Arabic_Glyphs', false)) {
223                    $Arabic = new I18N_Arabic('Glyphs');
224                } else {
225                    $Arabic = new I18N_Arabic_Glyphs();
226                }
227
228                foreach ($lbl as $kkey => $kval) {
229                    if (preg_match("^[A-Za-z]^", $kkey)) {
230//auto detect if english
231                        $lblout[] = $kkey.' ('.$kval.')';
232                    } else {
233                        $lblout[] = $Arabic->utf8Glyphs($kkey.' )'.$kval.'(');
234                    }
235                }
236            } elseif (getLanguageRTL($sLanguageCode)) {
237                foreach ($lbl as $kkey => $kval) {
238                    $lblout[] = UTF8Strrev(html_entity_decode($kkey, null, 'UTF-8').' )'.$kval.'(');
239                }
240            } else {
241                foreach ($lbl as $kkey => $kval) {
242                    $lblout[] = html_entity_decode($kkey, null, 'UTF-8').' ('.$kval.')';
243                }
244            }
245
246
247            //create new 3D pie chart
248            $DataSet = new pData;
249            $DataSet->AddPoint($gdata, "Serie1");
250            $DataSet->AddPoint($lblout, "Serie2");
251            $DataSet->AddAllSeries();
252            $DataSet->SetAbsciseLabelSerie("Serie2");
253
254            if ($cache->IsInCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData()) && Yii::app()->getConfig('debug') < 2) {
255                $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData()));
256            } else {
257
258                $gheight = ceil($gheight);
259                $graph = new pChart(690, $gheight);
260                $graph->drawFilledRectangle(0, 0, 690, $gheight, 254, 254, 254, false);
261                $graph->loadColorPalette($homedir.'/assets/styles/'.$admintheme.'/images/limesurvey.pal');
262                $graph->drawFilledRoundedRectangle(7, 7, 687, $gheight - 3, 5, 254, 255, 254);
263                $graph->drawRoundedRectangle(5, 5, 689, $gheight - 1, 5, 230, 230, 230);
264
265                // Draw the pie chart
266                $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
267                $graph->drawPieGraph($DataSet->GetData(), $DataSet->GetDataDescription(), 225, round($gheight / 2), 170, PIE_PERCENTAGE, true, 50, 20, 5);
268                $graph->setFontProperties($rootdir.DIRECTORY_SEPARATOR.'assets'.DIRECTORY_SEPARATOR.'fonts'.DIRECTORY_SEPARATOR.$chartfontfile, $chartfontsize);
269                $graph->drawPieLegend(430, 12, $DataSet->GetData(), $DataSet->GetDataDescription(), 250, 250, 250);
270                $cache->WriteToCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData(), $graph);
271                $cachefilename = basename($cache->GetFileFromCache("graph".$iSurveyID.$sLanguageCode.$iQuestionID, $DataSet->GetData()));
272                unset($graph);
273            }
274        }    //end else -> pie charts
275    }
276
277    return $cachefilename;
278}
279
280
281/**
282* Return data to populate a Google Map
283* @param string$sField    Field name
284* @param $qsid             Survey id
285* @param string $sField
286* @return array
287*/
288function getQuestionMapData($sField, $qsid)
289{
290    $aresult = SurveyDynamic::model($qsid)->findAll();
291
292    $d = array();
293
294    //loop through question data
295    foreach ($aresult as $arow) {
296        $alocation = explode(";", $arow->$sField);
297        if (count($alocation) >= 2) {
298            $d[] = "{$alocation[0]} {$alocation[1]}";
299        }
300    }
301    return $d;
302}
303
304/** Builds the list of addon SQL select statements
305 *   that builds the query result set
306 *
307 *   @param array    $allfields   An array containing the names of the fields/answers we want to display in the statistics summary
308 *   @param integer  $surveyid
309 *   @param string   $language    The language to use
310 *
311 *   @return array $selects array of individual select statements that can be added/appended to
312 *                          the 'where' portion of a SQL statement to restrict the result set
313 *                          ie: array("`FIELDNAME`='Y'", "`FIELDNAME2`='Hello'");
314 *
315 */
316function buildSelects($allfields, $surveyid, $language)
317{
318
319    //Create required variables
320    $selects = array();
321    $aQuestionMap = array();
322    $survey = Survey::model()->findByPk($surveyid);
323
324    $fieldmap = createFieldMap($survey, "full", false, false, $language);
325    foreach ($fieldmap as $field) {
326        if (isset($field['qid']) && $field['qid'] != '') {
327                    $aQuestionMap[] = $field['sid'].'X'.$field['gid'].'X'.$field['qid'];
328        }
329    }
330
331    // creates array of post variable names
332    for (reset($_POST); $key = key($_POST); next($_POST)) { $postvars[] = $key; }
333
334    /*
335    * Iterate through postvars to create "nice" data for SQL later.
336    *
337    * Remember there might be some filters applied which have to be put into an SQL statement
338    *
339    * This foreach iterates through the name ($key) of each post value and builds a SELECT
340    * statement out of it. It returns an array called $selects[] which will have a select query
341    * for each filter chosen. ie: $select[0]="`74X71X428EXP` ='Y'";
342    *
343    * This array is used later to build the overall query used to limit the number of responses
344    *
345    */
346    if (isset($postvars)) {
347            foreach ($postvars as $pv) {
348            //Only do this if there is actually a value for the $pv
349
350            if (
351                in_array($pv, $allfields) || in_array(substr($pv, 1), $aQuestionMap) || in_array($pv, $aQuestionMap)
352                || (
353                    (
354                        $pv[0] == 'D' || $pv[0] == 'N' || $pv[0] == 'K'
355                    )
356                    && (in_array(substr($pv, 1, strlen($pv) - 2), $aQuestionMap) || in_array(substr($pv, 1, strlen($pv) - 3), $aQuestionMap) || in_array(substr($pv, 1, strlen($pv) - 5), $aQuestionMap))
357                )
358                ) {
359                    $firstletter = substr($pv, 0, 1);
360    }
361                    /*
362                    * these question types WON'T be handled here:
363                    * M = Multiple choice
364                    * T - Long Free Text
365                    * Q - Multiple Short Text
366                    * D - Date
367                    * N - Numerical Input
368                    * | - File Upload
369                    * K - Multiple Numerical Input
370                    */
371                    if ($pv != "sid" && $pv != "display" && $firstletter != "M" && $firstletter != "P" && $firstletter != "T" &&
372                    $firstletter != "Q" && $firstletter != "D" && $firstletter != "N" && $firstletter != "K" && $firstletter != "|" &&
373                    $pv != "summary" && substr($pv, 0, 2) != "id" && substr($pv, 0, 9) != "datestamp") {
374//pull out just the fieldnames
375                        //put together some SQL here
376                        $thisquestion = Yii::app()->db->quoteColumnName($pv)." IN (";
377
378                        foreach ($_POST[$pv] as $condition) {
379                            $thisquestion .= "'$condition', ";
380                        }
381
382                        $thisquestion = substr($thisquestion, 0, -2)
383                        . ")";
384
385                        //we collect all the to be selected data in this array
386                        $selects[] = $thisquestion;
387                    }
388
389                    //M - Multiple choice
390                    //P - Multiple choice with comments
391                    elseif ($firstletter == "M" || $firstletter == "P") {
392                        $mselects = array();
393                        //create a list out of the $pv array
394                        list($lsid, $lgid, $lqid) = explode("X", $pv);
395
396                        $aresult = Question::model()->findAll(array('order'=>'question_order', 'condition'=>'parent_qid=:parent_qid AND scale_id=0', 'params'=>array(":parent_qid"=>$lqid)));
397                        foreach ($aresult as $arow) {
398                            // only add condition if answer has been chosen
399                            if (in_array($arow['title'], $_POST[$pv])) {
400                                $mselects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv)).$arow['title'])." = 'Y'";
401                            }
402                        }
403                        /* If there are mutliple conditions generated from this multiple choice question, join them using the boolean "OR" */
404                        if ($mselects) {
405                            $thismulti = implode(" OR ", $mselects);
406                            $selects[] = "($thismulti)";
407                            unset($mselects);
408                        }
409                    }
410
411                    //N - Numerical Input
412                    //K - Multiple Numerical Input
413                    elseif ($firstletter == "N" || $firstletter == "K") {
414                        //value greater than
415                        if (substr($pv, strlen($pv) - 1, 1) == "G" && $_POST[$pv] != "") {
416                            $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1))." > ".sanitize_int($_POST[$pv]);
417                        }
418
419                        //value less than
420                        if (substr($pv, strlen($pv) - 1, 1) == "L" && $_POST[$pv] != "") {
421                            $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1))." < ".sanitize_int($_POST[$pv]);
422                        }
423                    }
424
425                    //| - File Upload Question Type
426                    else if ($firstletter == "|") {
427                        // no. of files greater than
428                        if (substr($pv, strlen($pv) - 1, 1) == "G" && $_POST[$pv] != "") {
429                                                    $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1)."_filecount")." > ".sanitize_int($_POST[$pv]);
430                        }
431
432                        // no. of files less than
433                        if (substr($pv, strlen($pv) - 1, 1) == "L" && $_POST[$pv] != "") {
434                                                    $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1)."_filecount")." < ".sanitize_int($_POST[$pv]);
435                        }
436                    }
437
438                    //"id" is a built in field, the unique database id key of each response row
439                    elseif (substr($pv, 0, 2) == "id") {
440                        if (substr($pv, strlen($pv) - 1, 1) == "G" && $_POST[$pv] != "") {
441                            $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 0, -1))." > ".sanitize_int($_POST[$pv]);
442                        }
443                        if (substr($pv, strlen($pv) - 1, 1) == "L" && $_POST[$pv] != "") {
444                            $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 0, -1))." < ".sanitize_int($_POST[$pv]);
445                        }
446                    }
447
448                    //T - Long Free Text
449                    //Q - Multiple Short Text
450                    elseif (($firstletter == "T" || $firstletter == "Q") && $_POST[$pv] != "") {
451                        $selectSubs = array();
452                        //We intepret and * and % as wildcard matches, and use ' OR ' and , as the separators
453                        $pvParts = explode(",", str_replace('*', '%', str_replace(' OR ', ',', $_POST[$pv])));
454                        if (is_array($pvParts) AND count($pvParts)) {
455                            foreach ($pvParts AS $pvPart) {
456                                $selectSubs[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv)))." LIKE '".trim($pvPart)."'";
457                            }
458                            if (count($selectSubs)) {
459                                $selects[] = ' ('.implode(' OR ', $selectSubs).') ';
460                            }
461                        }
462                    }
463
464                    //D - Date
465                    elseif ($firstletter == "D" && $_POST[$pv] != "") {
466                        //Date equals
467                        if (substr($pv, -2) == "eq") {
468                            $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv) - 3))." = ".App()->db->quoteValue($_POST[$pv]);
469                        } else {
470                            //date less than
471                            if (substr($pv, -4) == "less") {
472                                $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv) - 5))." >= ".App()->db->quoteValue($_POST[$pv]);
473                            }
474
475                            //date greater than
476                            if (substr($pv, -4) == "more") {
477                                $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv) - 5))." <= ".App()->db->quoteValue($_POST[$pv]);
478                            }
479                        }
480                    }
481
482                    //check for datestamp of given answer
483                    elseif (substr($pv, 0, 9) == "datestamp") {
484                        //timestamp equals
485                        $formatdata = getDateFormatData(Yii::app()->session['dateformat']);
486                        if (substr($pv, -1, 1) == "E" && !empty($_POST[$pv])) {
487                            $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'].' H:i');
488                            $sDateValue = $datetimeobj->convert("Y-m-d");
489
490                            $selects[] = Yii::app()->db->quoteColumnName('datestamp')." >= ".App()->db->quoteValue($sDateValue." 00:00:00")." and ".Yii::app()->db->quoteColumnName('datestamp')." <= ".App()->db->quoteValue($sDateValue." 23:59:59");
491                        } else {
492                            //timestamp less than
493                            if (substr($pv, -1, 1) == "L" && !empty($_POST[$pv])) {
494                                $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'].' H:i');
495                                $sDateValue = $datetimeobj->convert("Y-m-d H:i:s");
496                                $selects[] = Yii::app()->db->quoteColumnName('datestamp')." < ".App()->db->quoteValue($sDateValue);
497                            }
498
499                            //timestamp greater than
500                            if (substr($pv, -1, 1) == "G" && !empty($_POST[$pv])) {
501                                $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'].' H:i');
502                                $sDateValue = $datetimeobj->convert("Y-m-d H:i:s");
503                                $selects[] = Yii::app()->db->quoteColumnName('datestamp')." > ".App()->db->quoteValue($sDateValue);
504                            }
505                        }
506                    }
507            }
508    }    //end foreach -> loop through filter options to create SQL
509
510    return $selects;
511}
512
513/**
514* Simple function to square a value
515*
516* @param mixed $number Value to square
517*/
518function square($number)
519{
520    if ($number == 0) {
521        $squarenumber = 0;
522    } else {
523        $squarenumber = $number * $number;
524    }
525    return $squarenumber;
526}
527
528class userstatistics_helper
529{
530    /**
531     * @var pdf
532     */
533    protected $pdf;
534
535    /**
536     * The Excel worksheet we are working on
537     *
538     * @var Spreadsheet_Excel_Writer_Worksheet
539     */
540    protected $sheet;
541
542    protected $xlsPercents;
543
544    protected $formatBold;
545    /**
546     * The current Excel workbook we are working on
547     *
548     * @var Writer
549     */
550    protected $workbook;
551
552    /**
553     * Keeps track of the current row in Excel sheet
554     *
555     * @var int
556     */
557    protected $xlsRow = 0;
558
559    /**
560     * Builds an array containing information about this particular question/answer combination
561     *
562     * @param string $rt The code passed from the statistics form listing the field/answer (SGQA) combination to be displayed
563     * @param mixed $language The language to present output in
564     * @param mixed $surveyid The survey id
565     * @param string $outputType
566     * @param boolean $browse
567     *
568     * @output array $output An array containing "alist"=>A list of answers to the question in the form of an array ($alist array
569     *                       contains an array for every field to be displayed - with the Actual Question Code/Title, The text (flattened)
570     *                       of the question, and the fieldname where the data is stored.
571     *                       "qtitle"=>The title of the question,
572     *                       "qquestion"=>The description of the question,
573     *                       "qtype"=>The question type code
574     */
575    protected function buildOutputList($rt, $language, $surveyid, $outputType, $sql, $oLanguage, $browse = true)
576    {
577
578        //Set up required variables
579        $survey = Survey::model()->findByPk($surveyid);
580        $alist = array();
581        $qtitle = "";
582        $qquestion = "";
583        $qtype = "";
584        $firstletter = substr($rt, 0, 1);
585        $fieldmap = createFieldMap($survey, "full", false, false, $language);
586        $sDatabaseType = Yii::app()->db->getDriverName();
587        $statisticsoutput = "";
588        $qqid = "";
589
590        /* Some variable depend on output type, actually : only line feed */
591        $linefeed = $this->getLinefeed($outputType);
592
593        //M - Multiple choice, therefore multiple fields - one for each answer
594        if ($firstletter == "M" || $firstletter == "P") {
595            //get SGQ data
596            list($qsid, $qgid, $qqid) = explode("X", substr($rt, 1, strlen($rt)), 3);
597
598            //select details for this question
599            $nresult = Question::model()->find('language=:language AND parent_qid=0 AND qid=:qid', array(':language'=>$language, ':qid'=>$qqid));
600            $qtitle = $nresult->title;
601            $qtype = $nresult->type;
602            $qquestion = flattenText($nresult->question);
603            $qlid = $nresult->parent_qid;
604            $qother = $nresult->other;
605
606            //1. Get list of answers
607            $result = Question::model()->findAll(array('order'=>'question_order',
608                'condition'=>'language=:language AND parent_qid=:qid AND scale_id=0',
609                'params'=>array(':language'=>$language, ':qid'=>$qqid)
610            ));
611            foreach ($result as $row) {
612                $mfield = substr($rt, 1, strlen($rt)).$row['title'];
613                $alist[] = array($row['title'], flattenText($row['question']), $mfield);
614            }
615
616            //Add the "other" answer if it exists
617            if ($qother == "Y") {
618                $mfield = substr($rt, 1, strlen($rt))."other";
619                $alist[] = array(gT("Other"), gT("Other"), $mfield);
620            }
621        }
622
623        //S - Short Free Text and T - Long Free Text
624        elseif ($firstletter == "T" || $firstletter == "S") {
625            //Short and long text
626            //search for key
627            $fld = substr($rt, 1, strlen($rt));
628            $fielddata = $fieldmap[$fld];
629
630
631            //get question data
632            $nresult = Question::model()->find('language=:language AND parent_qid=0 AND qid=:qid', array(':language'=>$language, ':qid'=>$fielddata['qid']));
633            $qtitle = $nresult->title;
634            $qtype = $nresult->type;
635            $qquestion = flattenText($nresult->question);
636
637            $mfield = substr($rt, 1, strlen($rt));
638
639            //Text questions either have an answer, or they don't. There's no other way of quantising the results.
640            // So, instead of building an array of predefined answers like we do with lists & other types,
641            // we instead create two "types" of possible answer - either there is a response.. or there isn't.
642            // This question type then can provide a % of the question answered in the summary.
643            $alist[] = array("Answer", gT("Answer"), $mfield);
644            $alist[] = array("NoAnswer", gT("No answer"), $mfield);
645            if ($qtype == ";"){
646                $qqid = $fielddata['qid']; // setting $qqid variable to parent qid enables graph for Array Text to be shown
647            }
648        }
649
650        //Q - Multiple short text
651        elseif ($firstletter == "Q") {
652            //Build an array of legitimate qid's for testing later
653            $aQuestionInfo = $fieldmap[substr($rt, 1)];
654            $qqid = $aQuestionInfo['qid'];
655            $qaid = $aQuestionInfo['aid'];
656
657            //get question data
658            $nresult = Question::model()->find('language=:language AND parent_qid=0 AND qid=:qid', array(':language'=>$language, ':qid'=>$qqid));
659            $qtitle = $nresult->title;
660            $qtype = $nresult->type;
661            $qquestion = flattenText($nresult->question);
662
663
664            //get answers / subquestion text
665            $nresult = Question::model()->find(array('order'=>'question_order',
666                'condition'=>'language=:language AND parent_qid=:parent_qid AND title=:title',
667                'params'=>array(':language'=>$language, ':parent_qid'=>$qqid, ':title'=>$qaid)
668            ));
669            $atext = flattenText($nresult->question);
670            //add this to the question title
671            $qtitle .= " [$atext]";
672
673            //even more substrings...
674            $mfield = substr($rt, 1, strlen($rt));
675
676            //Text questions either have an answer, or they don't. There's no other way of quantising the results.
677            // So, instead of building an array of predefined answers like we do with lists & other types,
678            // we instead create two "types" of possible answer - either there is a response.. or there isn't.
679            // This question type then can provide a % of the question answered in the summary.
680            $alist[] = array("Answer", gT("Answer"), $mfield);
681            $alist[] = array("NoAnswer", gT("No answer"), $mfield);
682        }
683
684        //RANKING OPTION
685        elseif ($firstletter == "R") {
686            //getting the needed IDs somehow
687            $lengthofnumeral = substr($rt, strpos($rt, "-") + 1, 1);
688            list($qsid, $qgid, $qqid) = explode("X", substr($rt, 1, strpos($rt, "-") - ($lengthofnumeral + 1)), 3);
689
690            //get question data
691            $nquery = "SELECT title, type, question FROM {{questions}} WHERE parent_qid=0 AND qid='$qqid' AND language='{$language}'";
692            $nresult = Yii::app()->db->createCommand($nquery)->query();
693
694            //loop through question data
695            foreach ($nresult->readAll() as $nrow) {
696                $nrow = array_values($nrow);
697                $qtitle = flattenText($nrow[0])." [".substr($rt, strpos($rt, "-") - ($lengthofnumeral), $lengthofnumeral)."]";
698                $qtype = $nrow[1];
699                $qquestion = flattenText($nrow[2])."[".gT("Ranking")." ".substr($rt, strpos($rt, "-") - ($lengthofnumeral), $lengthofnumeral)."]";
700            }
701
702            //get answers
703            $query = "SELECT code, answer FROM {{answers}} WHERE qid='$qqid' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, answer";
704            $result = Yii::app()->db->createCommand($query)->query();
705
706            //loop through answers
707            foreach ($result->readAll() as $row) {
708                $row = array_values($row);
709                //create an array containing answer code, answer and fieldname(??)
710                $mfield = substr($rt, 1, strpos($rt, "-") - 1);
711                $alist[] = array("$row[0]", flattenText($row[1]), $mfield);
712            }
713        } else if ($firstletter == "|") {
714// File Upload
715
716            //get SGQ data
717            list($qsid, $qgid, $qqid) = explode("X", substr($rt, 1, strlen($rt)), 3);
718
719            //select details for this question
720            /**
721              FIXME $iQuestionIDlength not defined!!
722             */
723            $nresult = Question::model()->find('language=:language AND parent_qid=0 AND qid=:qid', array(':language'=>$language, ':qid'=>substr($qqid, 0, $iQuestionIDlength)));
724            $qtitle = $nresult->title;
725            $qtype = $nresult->type;
726            $qquestion = flattenText($nresult->question);
727            /*
728            4)      Average size of file per respondent
729            5)      Average no. of files
730            5)      Summary/count of file types (ie: 37 jpg, 65 gif, 12 png)
731            6)      Total size of all files (useful if you re about to download them all)
732            7)      You could also add things like  smallest file size, largest file size, median file size
733            8)      no. of files corresponding to each extension
734            9)      max file size
735            10)     min file size
736            */
737
738            // 1) Total number of files uploaded
739            // 2)      Number of respondents who uploaded at least one file (with the inverse being the number of respondents who didn t upload any)
740            $fieldname = substr($rt, 1, strlen($rt));
741            $query = "SELECT SUM(".Yii::app()->db->quoteColumnName($fieldname.'_filecount').") as sum, AVG(".Yii::app()->db->quoteColumnName($fieldname.'_filecount').") as avg FROM {{survey_$surveyid}}";
742            $result = Yii::app()->db->createCommand($query)->query();
743
744            $showem = array();
745
746            foreach ($result->readAll() as $row) {
747                $showem[] = array(gT("Total number of files"), $row['sum']);
748                $showem[] = array(gT("Average no. of files per respondent"), $row['avg']);
749            }
750
751
752            $query = "SELECT ".$fieldname." as json FROM {{survey_$surveyid}}";
753            $result = Yii::app()->db->createCommand($query)->query();
754
755            $responsecount = 0;
756            $filecount = 0;
757            $size = 0;
758
759            foreach ($result->readAll() as $row) {
760                $json = $row['json'];
761                $phparray = json_decode($json);
762
763                foreach ($phparray as $metadata) {
764                    $size += (int) $metadata->size;
765                    $filecount++;
766                }
767                $responsecount++;
768            }
769            $showem[] = array(gT("Total size of files"), $size." KB");
770            $showem[] = array(gT("Average file size"), $size / $filecount." KB");
771            $showem[] = array(gT("Average size per respondent"), $size / $responsecount." KB");
772
773            /*              $query="SELECT title, question FROM {{questions}} WHERE parent_qid='$qqid' AND language='{$language}' ORDER BY question_order";
774            $result=db_execute_num($query) or safeDie("Couldn't get list of subquestions for multitype<br />$query<br />");
775
776            //loop through multiple answers
777            while ($row=$result->FetchRow())
778            {
779            $mfield=substr($rt, 1, strlen($rt))."$row[0]";
780
781            //create an array containing answer code, answer and fieldname(??)
782            $alist[]=array("$row[0]", flattenText($row[1]), $mfield);
783            }
784
785            */
786            //outputting
787            switch ($outputType) {
788                case 'xls':
789                    $xlsTitle = sprintf(gT("Summary for %s"), html_entity_decode($qtitle, ENT_QUOTES, 'UTF-8'));
790                    $xlsDesc = html_entity_decode($qquestion, ENT_QUOTES, 'UTF-8');
791                    $this->xlsRow++;
792                    $this->xlsRow++;
793                    $this->xlsRow++;
794                    $this->sheet->write($this->xlsRow, 0, $xlsTitle);
795                    $this->xlsRow++;
796                    $this->sheet->write($this->xlsRow, 0, $xlsDesc);
797                    $this->xlsRow++;
798                    $this->sheet->write($this->xlsRow, 0, gT("Calculation"));
799                    $this->sheet->write($this->xlsRow, 1, gT("Result"));
800                    break;
801
802                case 'pdf':
803                    $headPDF = array();
804                    $headPDF[] = array(gT("Calculation"), gT("Result"));
805
806                    break;
807
808                case 'html':
809                    $statisticsoutput .= "\n<table class='table table-striped statisticstable' >\n"
810                    ."\t<thead><tr><th colspan='2' class='text-center'><strong>".sprintf(gT("Summary for %s"), $qtitle).":</strong>"
811                    ."</th></tr>\n"
812                    ."\t<tr><th colspan='2' class='text-left'><strong>$qquestion</strong></th></tr>\n"
813                    ."\t<tr>\n\t\t<th width='50%' class='text-right'><strong>"
814                    .gT("Calculation")."</strong></th>\n"
815                    ."\t\t<th width='50%' class='text-right'><strong>"
816                    .gT("Result")."</strong></th>\n"
817                    ."\t</tr></thead>\n";
818
819                    foreach ($showem as $res) {
820                                            $statisticsoutput .= "<tr><td>".$res[0]."</td><td>".$res[1]."</td></tr>";
821                    }
822                    break;
823
824                default:
825                    break;
826            }
827        }
828
829        //N = numerical input
830        //K = multiple numerical input
831        elseif ($firstletter == "N" || $firstletter == "K") {
832//NUMERICAL TYPE
833            //Zero handling
834            if (!isset($excludezeros)) {
835//If this hasn't been set, set it to on as default:
836                $excludezeros = 1;
837            }
838            //check last character, greater/less/equals don't need special treatment
839            if (substr($rt, -1) == "G" || substr($rt, -1) == "L" || substr($rt, -1) == "=") {
840                //DO NOTHING
841            } else {
842                $showem = array();
843                $fld = substr($rt, 1, strlen($rt));
844                $fielddata = $fieldmap[$fld];
845
846                $qtitle = flattenText($fielddata['title']);
847                $qtype = $fielddata['type'];
848                $qquestion = $fielddata['question'];
849
850                //Get answer texts for multiple numerical
851                if (substr($rt, 0, 1) == "K") {
852                    //put single items in brackets at output
853                    $qtitle .= " [".$fielddata['subquestion']."]";
854                }
855
856                //outputting
857                switch ($outputType) {
858                    case 'xls':
859                        $xlsTitle = sprintf(gT("Summary for %s"), html_entity_decode($qtitle, ENT_QUOTES, 'UTF-8'));
860                        $xlsDesc = html_entity_decode($qquestion, ENT_QUOTES, 'UTF-8');
861                        $this->xlsRow++;
862                        $this->xlsRow++;
863
864                        $this->xlsRow++;
865                        $this->sheet->write($this->xlsRow, 0, $xlsTitle);
866                        $this->xlsRow++;
867                        $this->sheet->write($this->xlsRow, 0, $xlsDesc);
868                        $this->xlsRow++;
869                        $this->sheet->write($this->xlsRow, 0, gT("Calculation"));
870                        $this->sheet->write($this->xlsRow, 1, gT("Result"));
871                        break;
872
873                    case 'pdf':
874
875                        $headPDF = array();
876                        $tablePDF = array();
877                        $footPDF = array();
878
879                        $pdfTitle = sprintf(gT("Summary for %s"), html_entity_decode($qtitle, ENT_QUOTES, 'UTF-8'));
880                        $titleDesc = html_entity_decode($qquestion, ENT_QUOTES, 'UTF-8');
881
882                        $headPDF[] = array(gT("Calculation"), gT("Result"));
883
884                        break;
885                    case 'html':
886
887                        // Multiple numerical and numerical field summary
888                        $statisticsoutput .= "\n<div class='well'><table class='table table-striped statisticstable' >\n"
889                        ."\t<thead><tr><th colspan='2' class='text-center'><strong>".sprintf(gT("Summary for %s"), $qtitle).":</strong>"
890                        ."</th></tr>\n"
891                        ."\t<tr><th colspan='2' class='text-center'><strong>$qquestion</strong></th></tr>\n"
892                        ."\t<tr>\n\t\t<th width='50%' style='text-align:left'><strong>"
893                        .gT("Calculation")."</strong></th>\n"
894                        ."\t\t<th width='50%' style='text-align:right'><strong>"
895                        .gT("Result")."</strong></th>\n"
896                        ."\t</tr></thead>\n";
897
898                        break;
899                    default:
900
901
902                        break;
903                }
904
905                //this field is queried using mathematical functions
906                $fieldname = substr($rt, 1, strlen($rt));
907
908                //special treatment for MS SQL databases
909                if ($sDatabaseType == 'mssql' || $sDatabaseType == 'sqlsrv' || $sDatabaseType == 'dblib') {
910                    //standard deviation
911                    $query = "SELECT STDEVP(".Yii::app()->db->quoteColumnName($fieldname)."*1) as stdev";
912                }
913
914                //other databases (MySQL, Postgres)
915                else {
916                    //standard deviation
917                    $query = "SELECT STDDEV(".Yii::app()->db->quoteColumnName($fieldname).") as stdev";
918                }
919
920                //sum
921                $query .= ", SUM(".Yii::app()->db->quoteColumnName($fieldname)."*1) as sum";
922
923                //average
924                $query .= ", AVG(".Yii::app()->db->quoteColumnName($fieldname)."*1) as average";
925
926                //min
927                $query .= ", MIN(".Yii::app()->db->quoteColumnName($fieldname)."*1) as minimum";
928
929                //max
930                $query .= ", MAX(".Yii::app()->db->quoteColumnName($fieldname)."*1) as maximum";
931                //Only select responses where there is an actual number response, ignore nulls and empties (if these are included, they are treated as zeroes, and distort the deviation/mean calculations)
932
933                //special treatment for MS SQL databases
934                if ($sDatabaseType == 'mssql' || $sDatabaseType == 'sqlsrv' || $sDatabaseType == 'dblib') {
935                    //no NULL/empty values please
936                    $query .= " FROM {{survey_$surveyid}} WHERE ".Yii::app()->db->quoteColumnName($fieldname)." IS NOT NULL";
937                    if (!$excludezeros) {
938                        //NO ZERO VALUES
939                        $query .= " AND (".Yii::app()->db->quoteColumnName($fieldname)." <> 0)";
940                    }
941                }
942
943                //other databases (MySQL, Postgres)
944                else {
945                    //no NULL/empty values please
946                    $query .= " FROM {{survey_$surveyid}} WHERE ".Yii::app()->db->quoteColumnName($fieldname)." IS NOT NULL";
947                    if (!$excludezeros) {
948                        //NO ZERO VALUES
949                        $query .= " AND (".Yii::app()->db->quoteColumnName($fieldname)." != 0)";
950                    }
951                }
952
953                //filter incomplete answers if set
954                if (incompleteAnsFilterState() == "incomplete") {$query .= " AND submitdate is null"; } elseif (incompleteAnsFilterState() == "complete") {$query .= " AND submitdate is not null"; }
955
956                //$sql was set somewhere before
957                if (!empty($sql)) {$query .= " AND $sql"; }
958
959                //execute query
960                $result = Yii::app()->db->createCommand($query)->queryAll();
961
962                //get calculated data
963                foreach ($result as $row) {
964                    //put translation of mean and calculated data into $showem array
965                    $showem[] = array(gT("Sum"), $row['sum']);
966                    $showem[] = array(gT("Standard deviation"), round($row['stdev'], 2));
967                    $showem[] = array(gT("Average"), round($row['average'], 2));
968                    $showem[] = array(gT("Minimum"), $row['minimum']);
969
970                    //Display the maximum and minimum figures after the quartiles for neatness
971                    $maximum = $row['maximum'];
972                }
973
974
975                //CALCULATE QUARTILES
976                $medcount = $this->getQuartile(0, $fieldname, $surveyid, $sql, $excludezeros); // Get the recordcount
977                $quartiles = array();
978                $quartiles[1] = $this->getQuartile(1, $fieldname, $surveyid, $sql, $excludezeros);
979                $quartiles[2] = $this->getQuartile(2, $fieldname, $surveyid, $sql, $excludezeros);
980                $quartiles[3] = $this->getQuartile(3, $fieldname, $surveyid, $sql, $excludezeros);
981
982                //we just put the total number of records at the beginning of this array
983                array_unshift($showem, array(gT("Count"), $medcount));
984
985                /* IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT */
986                /* IF YOU DON'T UNDERSTAND WHAT QUARTILES ARE DO NOT MODIFY THIS CODE */
987                /* Quartiles and Median values are NOT related to average, and the sum is irrelevent */
988
989                if (isset($quartiles[1])) {
990                    $showem[] = array(gT("1st quartile (Q1)"), $quartiles[1]);
991                }
992                if (isset($quartiles[2])) {
993                    $showem[] = array(gT("2nd quartile (Median)"), $quartiles[2]);
994                }
995                if (isset($quartiles[3])) {
996                    $showem[] = array(gT("3rd quartile (Q3)"), $quartiles[3]);
997                }
998                $showem[] = array(gT("Maximum"), $maximum);
999
1000                //output results
1001                foreach ($showem as $shw) {
1002                    switch ($outputType) {
1003                        case 'xls':
1004
1005                            $this->xlsRow++;
1006                            $this->sheet->write($this->xlsRow, 0, html_entity_decode($shw[0], ENT_QUOTES, 'UTF-8'));
1007                            $this->sheet->write($this->xlsRow, 1, html_entity_decode($shw[1], ENT_QUOTES, 'UTF-8'));
1008
1009                            break;
1010                        case 'pdf':
1011
1012                            $tablePDF[] = array(html_entity_decode($shw[0], ENT_QUOTES, 'UTF-8'), html_entity_decode($shw[1], ENT_QUOTES, 'UTF-8'));
1013
1014                            break;
1015                        case 'html':
1016
1017                            $statisticsoutput .= "\t<tr>\n"
1018                            ."\t\t<td style='text-align:left'>$shw[0]</td>\n"
1019                            ."\t\t<td style='text-align:right'>$shw[1]</td>\n"
1020                            ."\t</tr>\n";
1021
1022                            break;
1023                        default:
1024
1025
1026                        break;
1027                    }
1028                }
1029                switch ($outputType) {
1030                    case 'xls':
1031
1032                        $this->xlsRow++;
1033                        $this->sheet->write($this->xlsRow, 0, gT("Null values are ignored in calculations"));
1034                        $this->xlsRow++;
1035                        $this->sheet->write($this->xlsRow, 0, sprintf(gT("Q1 and Q3 calculated using %s"), gT("minitab method")));
1036
1037                        $footXLS[] = array(gT("Null values are ignored in calculations"));
1038                        $footXLS[] = array(sprintf(gT("Q1 and Q3 calculated using %s"), gT("minitab method")));
1039
1040                        break;
1041                    case 'pdf':
1042
1043                        $footPDF[] = array(gT("Null values are ignored in calculations"));
1044                        $footPDF[] = array(sprintf(gT("Q1 and Q3 calculated using %s"), "<a href='http://mathforum.org/library/drmath/view/60969.html' target='_blank'>".gT("minitab method")."</a>"));
1045                        $this->pdf->AddPage('P', 'A4');
1046                        $this->pdf->Bookmark($this->pdf->delete_html($qquestion), 1, 0);
1047                        $this->pdf->titleintopdf($pdfTitle, $titleDesc);
1048
1049                        $this->pdf->headTable($headPDF, $tablePDF);
1050
1051                        $this->pdf->tablehead($footPDF);
1052
1053                        break;
1054                    case 'html':
1055
1056                        //footer of question type "N"
1057                        $statisticsoutput .= "\t<tr>\n"
1058                        ."\t\t<td colspan='4' class='text-center'>\n"
1059                        ."\t\t\t<font size='1'>".gT("Null values are ignored in calculations")."<br />\n"
1060                        ."\t\t\t".sprintf(gT("Q1 and Q3 calculated using %s"), "<a href='http://mathforum.org/library/drmath/view/60969.html' target='_blank'>".gT("minitab method")."</a>")
1061                        ."</font>\n"
1062                        ."\t\t</td>\n"
1063                        ."\t</tr>\n";
1064                        if ($browse) {
1065                            $statisticsoutput .= "\t<tr>\n"
1066                            ."\t\t<td class='text-center' colspan='4'>
1067                            <input type='button' class='btn btn-default statisticsbrowsebutton numericalbrowse' value='"
1068                            .gT("Browse")."' id='$fieldname' /></td>\n</tr>";
1069                            $statisticsoutput .= "<tr><td class='statisticsbrowsecolumn' colspan='3' style='display: none'>
1070                            <div class='statisticsbrowsecolumn' id='columnlist_{$fieldname}'></div></td></tr>";
1071                        }
1072                        $statisticsoutput .= "</table></div>\n";
1073
1074                        break;
1075                    default:
1076
1077
1078                        break;
1079                }
1080
1081                //clean up
1082                unset($showem);
1083
1084
1085                //not enough (<1) results for calculation
1086                if ($medcount < 1) {
1087                    switch ($outputType) {
1088                        case 'xls':
1089                            $this->xlsRow++;
1090                            $this->sheet->write($this->xlsRow, 0, gT("Not enough values for calculation"));
1091                            break;
1092
1093                        case 'pdf':
1094                            $tablePDF = array();
1095                            $tablePDF[] = array(gT("Not enough values for calculation"));
1096                            $this->pdf->AddPage('P', 'A4');
1097                            $this->pdf->Bookmark($this->pdf->delete_html($qquestion), 1, 0);
1098                            $this->pdf->titleintopdf($pdfTitle, $titleDesc);
1099                            $this->pdf->equalTable($tablePDF);
1100                            break;
1101
1102                        case 'html':
1103
1104                            //output
1105                            $statisticsoutput .= "\t<tr>\n"
1106                            ."\t\t<td class='text-center' colspan='4'>".gT("Not enough values for calculation")."</td>\n"
1107                            ."\t</tr>\n</table><br />\n";
1108
1109                            break;
1110                        default:
1111
1112
1113                            break;
1114                    }
1115
1116                    unset($showem);
1117
1118                }
1119
1120            }    //end else -> check last character, greater/less/equals don't need special treatment
1121
1122        }    //end else-if -> multiple numerical types
1123
1124        //is there some "id", "datestamp" or "D" within the type?
1125        elseif (substr($rt, 0, 2) == "id" || substr($rt, 0, 9) == "datestamp" || ($firstletter == "D")) {
1126            /*
1127            * DON'T show anything for date questions
1128            * because there aren't any statistics implemented yet!
1129            *
1130            * See bug report #2539 and
1131            * feature request #2620
1132            */
1133        }
1134
1135        // NICE SIMPLE SINGLE OPTION ANSWERS
1136        else {
1137            //search for key
1138            $fielddata = $fieldmap[$rt];
1139            //get SGQA IDs
1140            $qqid = $fielddata['qid'];
1141            $qanswer = $fielddata['aid'];
1142            $qtype = $fielddata['type'];
1143            //question string
1144            $qastring = $fielddata['question'];
1145            //question ID
1146            $rqid = $qqid;
1147
1148            //get question data
1149            $nquery = "SELECT title, type, question, qid, parent_qid, other FROM {{questions}} WHERE qid='{$rqid}' AND parent_qid=0 and language='{$language}'";
1150            $nresult = Yii::app()->db->createCommand($nquery)->query();
1151
1152            //loop though question data
1153            foreach ($nresult->readAll() as $nrow) {
1154                $nrow = array_values($nrow);
1155                $qtitle = flattenText($nrow[0]);
1156                $qtype = $nrow[1];
1157                $qquestion = flattenText($nrow[2]);
1158                $qiqid = $nrow[3];
1159                $qparentqid = $nrow[4];
1160                $qother = $nrow[5];
1161            }
1162
1163            //check question types
1164            switch ($qtype) {
1165                //Array of 5 point choices (several items to rank!)
1166                case "A":
1167
1168                    //get data
1169                    $qquery = "SELECT title, question FROM {{questions}} WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order";
1170                    $qresult = Yii::app()->db->createCommand($qquery)->query();
1171
1172                    //loop through results
1173                    foreach ($qresult->readAll() as $qrow) {
1174                        $qrow = array_values($qrow);
1175                        //5-point array
1176                        for ($i = 1; $i <= 5; $i++) {
1177                            //add data
1178                            $alist[] = array("$i", "$i");
1179                        }
1180                        //add counter
1181                        $atext = flattenText($qrow[1]);
1182                    }
1183
1184                    //list IDs and answer codes in brackets
1185                    $qquestion .= $linefeed;
1186                    $qtitle .= "($qanswer)"."[".$atext."]";
1187                    break;
1188
1189
1190
1191                    //Array of 10 point choices
1192                    //same as above just with 10 items
1193                case "B":
1194                    $qquery = "SELECT title, question FROM {{questions}} WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order";
1195                    $qresult = Yii::app()->db->createCommand($qquery)->query();
1196                    foreach ($qresult->readAll() as $qrow) {
1197                        $qrow = array_values($qrow);
1198                        for ($i = 1; $i <= 10; $i++) {
1199                            $alist[] = array("$i", "$i");
1200                        }
1201                        $atext = flattenText($qrow[1]);
1202                    }
1203
1204                    $qquestion .= $linefeed;
1205                    $qtitle .= "($qanswer)"."[".$atext."]";;
1206                    break;
1207
1208
1209
1210                    //Array of Yes/No/gT("Uncertain")
1211                case "C":
1212                    $qquery = "SELECT title, question FROM {{questions}} WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order";
1213                    $qresult = Yii::app()->db->createCommand($qquery)->query();
1214
1215                    //loop thorugh results
1216                    foreach ($qresult->readAll() as $qrow) {
1217                        $qrow = array_values($qrow);
1218                        //add results
1219                        $alist[] = array("Y", gT("Yes"));
1220                        $alist[] = array("N", gT("No"));
1221                        $alist[] = array("U", gT("Uncertain"));
1222                        $atext = flattenText($qrow[1]);
1223                    }
1224                    //output
1225                    $qquestion .= $linefeed;
1226                    $qtitle .= "($qanswer)"."[".$atext."]";
1227                    break;
1228
1229
1230
1231                    //Array of Yes/No/gT("Uncertain")
1232                    //same as above
1233                case "E":
1234                    $qquery = "SELECT title, question FROM {{questions}} WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order";
1235                    $qresult = Yii::app()->db->createCommand($qquery)->query();
1236                    foreach ($qresult->readAll() as $qrow) {
1237                        $qrow = array_values($qrow);
1238                        $alist[] = array("I", gT("Increase"));
1239                        $alist[] = array("S", gT("Same"));
1240                        $alist[] = array("D", gT("Decrease"));
1241                        $atext = flattenText($qrow[1]);
1242                    }
1243                    $qquestion .= $linefeed;
1244                    $qtitle .= "($qanswer)"."[".$atext."]";
1245                    break;
1246
1247
1248                case ";": //Array (Multi Flexi) (Text)
1249                    list($qacode, $licode) = explode("_", $qanswer);
1250
1251                    $qquery = "SELECT title, question FROM {{questions}} WHERE parent_qid='$qiqid' AND title='$qacode' AND language='{$language}' ORDER BY question_order";
1252                    $qresult = Yii::app()->db->createCommand($qquery)->query();
1253
1254                    foreach ($qresult->readAll() as $qrow) {
1255                        $qrow = array_values($qrow);
1256                        $fquery = "SELECT * FROM {{answers}} WHERE qid='{$qiqid}' AND scale_id=0 AND code = '{$licode}' AND language='{$language}'ORDER BY sortorder, code";
1257                        $fresult = Yii::app()->db->createCommand($fquery)->query();
1258                        foreach ($fresult->readAll() as $frow) {
1259                            $alist[] = array($frow['code'], $frow['answer']);
1260                            $ltext = $frow['answer'];
1261                        }
1262                        $atext = flattenText($qrow[1]);
1263                    }
1264
1265                    $qquestion .= $linefeed;
1266                    $qtitle .= "($qanswer)"."[".$atext."] [".$ltext."]";;
1267                    break;
1268
1269                case ":": //Array (Multiple Flexi) (Numbers)
1270                    $aQuestionAttributes = QuestionAttribute::model()->getQuestionAttributes($qiqid);
1271                    if (trim($aQuestionAttributes['multiflexible_max']) != '') {
1272                        $maxvalue = $aQuestionAttributes['multiflexible_max'];
1273                    } else {
1274                        $maxvalue = 10;
1275                    }
1276
1277                    if (trim($aQuestionAttributes['multiflexible_min']) != '') {
1278                        $minvalue = $aQuestionAttributes['multiflexible_min'];
1279                    } else {
1280                        $minvalue = 1;
1281                    }
1282
1283                    if (trim($aQuestionAttributes['multiflexible_step']) != '') {
1284                        $stepvalue = $aQuestionAttributes['multiflexible_step'];
1285                    } else {
1286                        $stepvalue = 1;
1287                    }
1288
1289                    if ($aQuestionAttributes['multiflexible_checkbox'] != 0) {
1290                        $minvalue = 0;
1291                        $maxvalue = 1;
1292                        $stepvalue = 1;
1293                    }
1294
1295                    for ($i = $minvalue; $i <= $maxvalue; $i += $stepvalue) {
1296                        $alist[] = array($i, $i);
1297                    }
1298
1299                    $qquestion .= $linefeed."[".$fielddata['subquestion1']."] [".$fielddata['subquestion2']."]";
1300                    list($myans, $mylabel) = explode("_", $qanswer);
1301                    $qtitle .= "[$myans][$mylabel]";
1302                    break;
1303
1304                case "F": //Array of Flexible
1305                case "H": //Array of Flexible by Column
1306                    $qquery = "SELECT title, question FROM {{questions}} WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order";
1307                    $qresult = Yii::app()->db->createCommand($qquery)->query();
1308
1309                    //loop through answers
1310                    foreach ($qresult->readAll() as $qrow) {
1311                        $qrow = array_values($qrow);
1312
1313                        //this question type uses its own labels
1314                        $fquery = "SELECT * FROM {{answers}} WHERE qid='{$qiqid}' AND scale_id=0 AND language='{$language}'ORDER BY sortorder, code";
1315                        $fresult = Yii::app()->db->createCommand($fquery)->query();
1316
1317                        //add code and title to results for outputting them later
1318                        foreach ($fresult->readAll() as $frow) {
1319                            $alist[] = array($frow['code'], flattenText($frow['answer']));
1320                        }
1321
1322                        //counter
1323                        $atext = flattenText($qrow[1]);
1324                    }
1325
1326                    //output
1327                    $qquestion .= $linefeed;
1328                    $qtitle .= "($qanswer)"."[".$atext."]";
1329                    break;
1330
1331
1332
1333                case "G": //Gender
1334                    $alist[] = array("F", gT("Female"));
1335                    $alist[] = array("M", gT("Male"));
1336                    break;
1337
1338
1339
1340                case "Y": //Yes\No
1341                    $alist[] = array("Y", gT("Yes"));
1342                    $alist[] = array("N", gT("No"));
1343                    break;
1344
1345
1346
1347                case "I": //Language
1348                    foreach (Survey::model()->findByPk($surveyid)->getAllLanguages() as $availlang) {
1349                        $alist[] = array($availlang, getLanguageNameFromCode($availlang, false));
1350                    }
1351                    break;
1352
1353
1354                case "5": //5 Point (just 1 item to rank!)
1355                    for ($i = 1; $i <= 5; $i++) {
1356                        $alist[] = array("$i", "$i");
1357                    }
1358                    break;
1359
1360
1361                case "1":    //array (dual scale)
1362
1363
1364                    $sSubquestionQuery = "SELECT  question FROM {{questions}} WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order";
1365                    $questionDesc = Yii::app()->db->createCommand($sSubquestionQuery)->query()->read();
1366                    $sSubquestion = flattenText($questionDesc['question']);
1367
1368                    //get question attributes
1369                    $aQuestionAttributes = QuestionAttribute::model()->getQuestionAttributes($qqid);
1370
1371
1372                    //check last character -> label 1
1373                    if (substr($rt, -1, 1) == 0) {
1374                        //get label 1
1375                        $fquery = "SELECT * FROM {{answers}} WHERE qid='{$qqid}' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, code";
1376
1377                        //header available?
1378                        if (trim($aQuestionAttributes['dualscale_headerA'][$language]) != '') {
1379                            //output
1380                            $labelheader = "[".$aQuestionAttributes['dualscale_headerA'][$language]."]";
1381                        }
1382
1383                        //no header
1384                        else {
1385                            $labelheader = '';
1386                        }
1387
1388                        //output
1389                        $labelno = sprintf(gT('Label %s'), '1');
1390                    }
1391
1392                    //label 2
1393                    else {
1394                        //get label 2
1395                        $fquery = "SELECT * FROM {{answers}} WHERE qid='{$qqid}' AND scale_id=1 AND language='{$language}' ORDER BY sortorder, code";
1396
1397                        //header available?
1398                        if (trim($aQuestionAttributes['dualscale_headerB'][$language]) != '') {
1399                            //output
1400                            $labelheader = "[".$aQuestionAttributes['dualscale_headerB'][$language]."]";
1401                        }
1402
1403                        //no header
1404                        else {
1405                            $labelheader = '';
1406                        }
1407
1408                        //output
1409                        $labelno = sprintf(gT('Label %s'), '2');
1410                    }
1411
1412                    //get data
1413                    $fresult = Yii::app()->db->createCommand($fquery)->query();
1414
1415                    //put label code and label title into array
1416                    foreach ($fresult->readAll() as $frow) {
1417                        $alist[] = array($frow['code'], flattenText($frow['answer']));
1418                    }
1419
1420                    //adapt title and question
1421                    $qtitle = $qtitle." [".$sSubquestion."][".$labelno."]";
1422                    $qquestion = $qastring.$labelheader;
1423                    break;
1424
1425
1426
1427
1428                default:    //default handling
1429
1430                    //get answer code and title
1431                    $qquery = "SELECT code, answer FROM {{answers}} WHERE qid='$qqid' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, answer";
1432                    $qresult = Yii::app()->db->createCommand($qquery)->query();
1433
1434                    //put answer code and title into array
1435                    foreach ($qresult->readAll() as $qrow) {
1436                        $qrow = array_values($qrow);
1437                        $alist[] = array("$qrow[0]", flattenText($qrow[1]));
1438                    }
1439
1440                    //handling for "other" field for list radio or list drowpdown
1441                    if ((($qtype == "L" || $qtype == "!") && $qother == "Y")) {
1442                        //add "other"
1443                        $alist[] = array(gT("Other"), gT("Other"), $fielddata['fieldname'].'other');
1444                    }
1445                    if ($qtype == "O") {
1446                        //add "comment"
1447                        $alist[] = array(gT("Comments"), gT("Comments"), $fielddata['fieldname'].'comment');
1448                    }
1449
1450            }    //end switch question type
1451
1452            //moved because it's better to have "no answer" at the end of the list instead of the beginning
1453            //put data into array
1454            $alist[] = array("", gT("No answer"));
1455
1456        }
1457
1458        return array("alist"=>$alist, "qtitle"=>$qtitle, "qquestion"=>$qquestion, "qtype"=>$qtype, "statisticsoutput"=>$statisticsoutput, "parentqid"=>$qqid);
1459    }
1460
1461    /**
1462     * displayResults builds html output to display the actual results from a survey
1463     *
1464     * @param mixed $outputs
1465     * @param INT $results The number of results being displayed overall
1466     * @param mixed $rt
1467     * @param string $outputType
1468     * @param mixed $surveyid
1469     * @param mixed $sql
1470     * @param integer $usegraph
1471     * @param boolean $browse
1472     * @return array
1473     */
1474    protected function displayResults($outputs, $results, $rt, $outputType, $surveyid, $sql, $usegraph, $browse, $sLanguage)
1475    {
1476
1477        /* Set up required variables */
1478        $TotalCompleted = 0; //Count of actually completed answers
1479        $statisticsoutput = "";
1480        $sDatabaseType = Yii::app()->db->getDriverName();
1481        $tempdir = Yii::app()->getConfig("tempdir");
1482        $tempurl = Yii::app()->getConfig("tempurl");
1483        $astatdata = array();
1484        $sColumnName = null;
1485        if ($usegraph == 1) {
1486            //for creating graphs we need some more scripts which are included here
1487            require_once(APPPATH.'/third_party/pchart/pChart.class.php');
1488            require_once(APPPATH.'/third_party/pchart/pData.class.php');
1489            require_once(APPPATH.'/third_party/pchart/pCache.class.php');
1490            $MyCache = new pCache($tempdir.'/');
1491        }
1492
1493        switch ($outputType) {
1494            case 'xls':
1495
1496                $xlsTitle = sprintf(gT("Summary for %s"), html_entity_decode($outputs['qtitle'], ENT_QUOTES, 'UTF-8'));
1497                $xlsDesc = html_entity_decode($outputs['qquestion'], ENT_QUOTES, 'UTF-8');
1498
1499                $this->xlsRow++;
1500                $this->xlsRow++;
1501
1502                $this->xlsRow++;
1503                $this->sheet->write($this->xlsRow, 0, $xlsTitle);
1504                $this->xlsRow++;
1505                $this->sheet->write($this->xlsRow, 0, $xlsDesc);
1506                $footXLS = array();
1507
1508                break;
1509            case 'pdf':
1510
1511                $sPDFQuestion = flattenText($outputs['qquestion'], false, true);
1512                $pdfTitle = $this->pdf->delete_html(sprintf(gT("Summary for %s"), html_entity_decode($outputs['qtitle'], ENT_QUOTES, 'UTF-8')));
1513                $titleDesc = $sPDFQuestion;
1514
1515                $this->pdf->AddPage('P', 'A4');
1516                $this->pdf->Bookmark($sPDFQuestion, 1, 0);
1517                $this->pdf->titleintopdf($pdfTitle, $sPDFQuestion);
1518                $tablePDF = array();
1519                $footPDF = array();
1520
1521                break;
1522            case 'html':
1523                //output
1524                $statisticsoutput .= "<div class='well'><table class='table table-striped statisticstable'>\n"
1525                ."\t<thead><tr><th colspan='4' class='text-center'><strong>"
1526
1527                //headline
1528                .sprintf(gT("Summary for %s"), $outputs['qtitle'])."</strong>"
1529                ."</th></tr>\n"
1530                ."\t<tr><th colspan='4' class='text-center'><strong>"
1531
1532                //question title
1533                .$outputs['qquestion']."</strong></th></tr>\n"
1534                ."\t<tr>\n\t\t<th width='50%' class='text-left'>";
1535                break;
1536            default:
1537
1538
1539                break;
1540        }
1541        //loop though the array which contains all answer data
1542        $ColumnName_RM = array();
1543        foreach ($outputs['alist'] as $al) {
1544            //picks out answer list ($outputs['alist']/$al)) that come from the multiple list above
1545            if (isset($al[2]) && $al[2]) {
1546
1547                //handling for "other" option
1548                if ($al[0] == gT("Other")) {
1549                    if ($outputs['qtype'] == '!' || $outputs['qtype'] == 'L') {
1550                        // It is better for single choice question types to filter on the number of '-oth-' entries, than to
1551                        // just count the number of 'other' values - that way with failing Javascript the statistics don't get messed up
1552                        /* This query selects a count of responses where "other" has been selected */
1553                        $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ".Yii::app()->db->quoteColumnName(substr($al[2], 0, strlen($al[2]) - 5))."='-oth-'";
1554                    } else {
1555                        //get data - select a count of responses where no answer is provided
1556                        $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ";
1557                        $query .= ($sDatabaseType == "mysql") ?  Yii::app()->db->quoteColumnName($al[2])." != ''" : "NOT (".Yii::app()->db->quoteColumnName($al[2])." LIKE '')";
1558                    }
1559                }
1560
1561                /*
1562                * text questions:
1563                *
1564                * U = huge free text
1565                * T = long free text
1566                * S = short free text
1567                * Q = multiple short text
1568                */
1569                elseif ($outputs['qtype'] == "U" || $outputs['qtype'] == "T" || $outputs['qtype'] == "S" || $outputs['qtype'] == "Q" || $outputs['qtype'] == ";") {
1570                    $sDatabaseType = Yii::app()->db->getDriverName();
1571
1572                    //free text answers
1573                    if ($al[0] == "Answer") {
1574                        $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ";
1575                        $query .= ($sDatabaseType == "mysql") ?  Yii::app()->db->quoteColumnName($al[2])." != ''" : "NOT (".Yii::app()->db->quoteColumnName($al[2])." LIKE '')";
1576                    }
1577                    //"no answer" handling
1578                    elseif ($al[0] == "NoAnswer") {
1579                        $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ( ";
1580                        $query .= ($sDatabaseType == "mysql") ?  Yii::app()->db->quoteColumnName($al[2])." = '')" : " (".Yii::app()->db->quoteColumnName($al[2])." LIKE ''))";
1581                    }
1582                } elseif ($outputs['qtype'] == "O") {
1583                    $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ( ";
1584                    $query .= ($sDatabaseType == "mysql") ?  Yii::app()->db->quoteColumnName($al[2])." <> '')" : " (".Yii::app()->db->quoteColumnName($al[2])." NOT LIKE ''))";
1585                    // all other question types
1586                } else {
1587                    $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ".Yii::app()->db->quoteColumnName($al[2])." =";
1588
1589                    //ranking question?
1590                    if (substr($rt, 0, 1) == "R") {
1591                        $query .= " '$al[0]'";
1592                    } else {
1593                        $query .= " 'Y'";
1594                    }
1595                }
1596            }    //end if -> alist set
1597
1598            else {
1599                if ($al[0] != "") {
1600                    //get more data
1601                    $sDatabaseType = Yii::app()->db->getDriverName();
1602                    if ($sDatabaseType == 'mssql' || $sDatabaseType == 'sqlsrv' || $sDatabaseType == 'dblib') {
1603                        // mssql cannot compare text blobs so we have to cast here
1604                        $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE cast(".Yii::app()->db->quoteColumnName($rt)." as varchar)= '$al[0]'";
1605                    } else {
1606                                            $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ".Yii::app()->db->quoteColumnName($rt)." = '$al[0]'";
1607                    }
1608                } else {
1609// This is for the 'NoAnswer' case
1610                    // We need to take into account several possibilities
1611                    // * NoAnswer cause the participant clicked the NoAnswer radio
1612                    //  ==> in this case value is '' or ' '
1613                    // * NoAnswer in text field
1614                    //  ==> value is ''
1615                    // * NoAnswer due to conditions, or a page not displayed
1616                    //  ==> value is NULL
1617                    if ($sDatabaseType == 'mssql' || $sDatabaseType == 'sqlsrv' || $sDatabaseType == 'dblib') {
1618                        // mssql cannot compare text blobs so we have to cast here
1619                        //$query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE (".sanitize_int($rt)." IS NULL "
1620                        $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ( "
1621                        //                                    . "OR cast(".sanitize_int($rt)." as varchar) = '' "
1622                        . "cast(".Yii::app()->db->quoteColumnName($rt)." as varchar) = '' "
1623                        . "OR cast(".Yii::app()->db->quoteColumnName($rt)." as varchar) = ' ' )";
1624                    } else {
1625                        $query = "SELECT count(*) FROM {{survey_$surveyid}} WHERE ( "
1626                        . " ".Yii::app()->db->quoteColumnName($rt)." = '' "
1627                        . "OR ".Yii::app()->db->quoteColumnName($rt)." = ' ') ";
1628                    }
1629                }
1630
1631            }
1632
1633            //check filter option
1634            if (incompleteAnsFilterState() == "incomplete") {$query .= " AND submitdate is null"; } elseif (incompleteAnsFilterState() == "complete") {$query .= " AND submitdate is not null"; }
1635
1636            //check for any "sql" that has been passed from another script
1637            if (!empty($sql)) {$query .= " AND $sql"; }
1638
1639            //get data
1640            $row = Yii::app()->db->createCommand($query)->queryScalar();
1641
1642            // $statisticsoutput .= "\n<!-- ($sql): $query -->\n\n";
1643
1644            //store temporarily value of answer count of question type '5' and 'A'.
1645            $tempcount = -1; //count can't be less han zero
1646
1647            //increase counter
1648            $TotalCompleted += $row;
1649
1650            //"no answer" handling
1651            if ($al[0] === "")
1652                {$fname = gT("No answer"); }
1653
1654            //"other" handling
1655            //"Answer" means that we show an option to list answer to "other" text field
1656            elseif (($al[0] === gT("Other") || $al[0] === "Answer" || ($outputs['qtype'] === "O" && $al[0] === gT("Comments")) || $outputs['qtype'] === "P") && count($al) > 2) {
1657                if ($outputs['qtype'] == "P") {
1658                    $sColumnName = $al[2]."comment";
1659                } else {
1660                    $sColumnName = $al[2];
1661                }
1662                $ColumnName_RM[] = $sColumnName;
1663                if ($outputs['qtype'] == 'O') {
1664                    $TotalCompleted -= $row;
1665                }
1666                $fname = "$al[1]";
1667                if ($browse === true) {
1668                    $fname .= " <input type='button' class='btn btn-default statisticsbrowsebutton' value='"
1669                    .gT("Browse")."' id='$sColumnName' />";
1670                }
1671
1672                if ($browse === true && isset($_POST['showtextinline']) && $outputType == 'pdf') {
1673                    $headPDF2 = array();
1674                    $headPDF2[] = array(gT("ID"), gT("Response"));
1675                    $tablePDF2 = array();
1676                    $result2 = $this->_listcolumn($surveyid, $sColumnName);
1677
1678                    foreach ($result2 as $row2) {
1679                        $tablePDF2[] = array($row2['id'], $row2['value']);
1680                    }
1681                }
1682
1683                if ($browse === true && isset($_POST['showtextinline']) && $outputType == 'xls') {
1684                    $headXLS = array();
1685                    $tableXLS = array();
1686                    $headXLS[] = array(gT("ID"), gT("Response"));
1687
1688                    $result2 = $this->_listcolumn($surveyid, $sColumnName);
1689
1690                    foreach ($result2 as $row2) {
1691                        $tableXLS[] = array($row2['id'], $row2['value']);
1692                    }
1693
1694                }
1695
1696
1697            }
1698
1699            /*
1700            * text questions:
1701            *
1702            * U = huge free text
1703            * T = long free text
1704            * S = short free text
1705            * Q = multiple short text
1706            */
1707            elseif ($outputs['qtype'] == "S" || $outputs['qtype'] == "U" || $outputs['qtype'] == "T" || $outputs['qtype'] == "Q") {
1708                $headPDF = array();
1709                $headPDF[] = array(gT("Answer"), gT("Count"), gT("Percentage"));
1710
1711                //show free text answers
1712                if ($al[0] == "Answer") {
1713                    $fname = "$al[1]";
1714                    if ($browse === true) {
1715                        $fname .= " <input type='button'  class='btn btn-default statisticsbrowsebutton' value='"
1716                        . gT("Browse")."' id='$sColumnName' />";
1717                    }
1718                } elseif ($al[0] == "NoAnswer") {
1719                    $fname = "$al[1]";
1720                }
1721
1722                $statisticsoutput .= "</th>\n"
1723                ."\t\t<th width='25%' class='text-right'>"
1724                ."<strong>".gT("Count")."</strong></th>\n"
1725                ."\t\t<th width='25%'class='text-right'>"
1726                ."<strong>".gT("Percentage")."</strong></th>\n"
1727                ."\t</tr></thead>\n";
1728
1729                if ($browse === true && isset($_POST['showtextinline']) && $outputType == 'pdf') {
1730                    $headPDF2 = array();
1731                    $headPDF2[] = array(gT("ID"), gT("Response"));
1732                    $tablePDF2 = array();
1733                    $result2 = $this->_listcolumn($surveyid, $sColumnName);
1734
1735                    foreach ($result2 as $row2) {
1736                        $tablePDF2[] = array($row2['id'], $row2['value']);
1737                    }
1738                }
1739            }
1740
1741
1742            //check if aggregated results should be shown
1743            elseif (Yii::app()->getConfig('showaggregateddata') == 1) {
1744                if (!isset($showheadline) || $showheadline != false) {
1745                    if ($outputs['qtype'] == "5" || $outputs['qtype'] == "A") {
1746                        switch ($outputType) {
1747                            case 'xls':
1748                                $this->xlsRow++;
1749                                $this->sheet->write($this->xlsRow, 0, gT("Answer"));
1750                                $this->sheet->write($this->xlsRow, 1, gT("Count"));
1751                                $this->sheet->write($this->xlsRow, 2, gT("Percentage"));
1752                                $this->sheet->write($this->xlsRow, 3, gT("Sum"));
1753                                break;
1754
1755                            case 'pdf':
1756
1757                                $headPDF = array();
1758                                $headPDF[] = array(gT("Answer"), gT("Count"), gT("Percentage"), gT("Sum"));
1759
1760                                break;
1761                            case 'html':
1762                                //four columns
1763                                $statisticsoutput .= "<strong>".gT("Answer")."</strong></th>\n"
1764                                ."\t\t<th width='15%' class='text-right'>"
1765                                ."<strong>".gT("Count")."</strong></th>\n"
1766                                ."\t\t<th width='20%'class='text-right'>"
1767                                ."<strong>".gT("Percentage")."</strong></th>\n"
1768                                ."\t\t<th width='15%'class='text-right'>"
1769                                ."<strong>".gT("Sum")."</strong></th>\n"
1770                                ."\t</tr></thead>\n";
1771                                break;
1772                            default:
1773
1774
1775                                break;
1776                        }
1777
1778                        $showheadline = false;
1779                    } else {
1780                        switch ($outputType) {
1781                            case 'xls':
1782                                $this->xlsRow++;
1783                                $this->sheet->write($this->xlsRow, 0, gT("Answer"));
1784                                $this->sheet->write($this->xlsRow, 1, gT("Count"));
1785                                $this->sheet->write($this->xlsRow, 2, gT("Percentage"));
1786                                break;
1787
1788                            case 'pdf':
1789
1790                                $headPDF = array();
1791                                $headPDF[] = array(gT("Answer"), gT("Count"), gT("Percentage"));
1792
1793                                break;
1794                            case 'html':
1795                                //three columns
1796                                $statisticsoutput .= "<strong>".gT("Answer")."</strong></td>\n"
1797                                ."\t\t<th width='25%'class='text-right'>"
1798                                ."<strong>".gT("Count")."</strong></th>\n"
1799                                ."\t\t<th width='25%'class='text-right'>"
1800                                ."<strong>".gT("Percentage")."</strong></th>\n"
1801                                ."\t</tr></thead>\n";
1802                                break;
1803                            default:
1804
1805                                break;
1806                        }
1807
1808                        $showheadline = false;
1809                    }
1810
1811                }
1812
1813                //text for answer column is always needed
1814                $fname = "$al[1] ($al[0])";
1815
1816            }    //end if -> show aggregated data
1817
1818            //handling what's left
1819            else {
1820                if (!isset($showheadline) || $showheadline != false) {
1821                    switch ($outputType) {
1822                        case 'xls':
1823                            $this->xlsRow++;
1824                            $this->sheet->write($this->xlsRow, 0, gT("Answer"));
1825                            $this->sheet->write($this->xlsRow, 1, gT("Count"));
1826                            $this->sheet->write($this->xlsRow, 2, gT("Percentage"));
1827                            break;
1828
1829                        case 'pdf':
1830
1831                            $headPDF = array();
1832                            $headPDF[] = array(gT("Answer"), gT("Count"), gT("Percentage"));
1833
1834                            break;
1835                        case 'html':
1836                            //three columns
1837                            $statisticsoutput .= "<strong>".gT("Answer")."</strong></th>\n"
1838                            ."\t\t<th width='25%'class='text-right'>"
1839                            ."<strong>".gT("Count")."</strong></th>\n"
1840                            ."\t\t<th width='25%'class='text-right'>"
1841                            ."<strong>".gT("Percentage")."</strong></th>\n"
1842                            ."\t</tr></thead>\n";
1843                            break;
1844                        default:
1845
1846
1847                            break;
1848                    }
1849
1850                    $showheadline = false;
1851
1852                }
1853                //answer text
1854                $fname = "$al[1] ($al[0])";
1855            }
1856
1857            //are there some results to play with?
1858            if ($results > 0) {
1859                //calculate percentage
1860                $gdata[] = ($row / $results) * 100;
1861            }
1862            //no results
1863            else {
1864                //no data!
1865                $gdata[] = "N/A";
1866            }
1867
1868            //put absolute data into array
1869            $grawdata[] = $row;
1870
1871            //put question title and code into array
1872            $label[] = $fname;
1873
1874            //put only the code into the array
1875            $justcode[] = $al[0];
1876
1877            //edit labels and put them into antoher array
1878
1879            //first check if $tempcount is > 0. If yes, $row has been modified and $tempcount has the original count.
1880            if ($tempcount > -1) {
1881                $lbl[wordwrap(FlattenText("$al[1]"), 25, "\n")] = $tempcount;
1882            } else {
1883                // Duplicate labels can exist.
1884                // TODO: Support three or more duplicates.
1885                $flatLabel = wordwrap(FlattenText("$al[1]"), 25, "\n");
1886                if (isset($lbl[$flatLabel])) {
1887                    $lbl[$flatLabel.' (2)'] = $row;
1888                } else {
1889                    $lbl[$flatLabel] = $row;
1890                }
1891
1892            }
1893
1894
1895        }    //end foreach -> loop through answer data
1896
1897        //no filtering of incomplete answers and NO multiple option questions
1898        //if ((incompleteAnsFilterState() != "complete") and ($outputs['qtype'] != "M") and ($outputs['qtype'] != "P"))
1899        //error_log("TIBO ".print_r($showaggregated_indice_table,true));
1900        if (($outputs['qtype'] != "M") and ($outputs['qtype'] != "P")) {
1901            //is the checkbox "Don't consider NON completed responses (only works when Filter incomplete answers is Disable)" checked?
1902            //if (isset($_POST[''noncompleted']) and ($_POST['noncompleted'] == "on") && (isset(Yii::app()->getConfig('showaggregateddata')) && Yii::app()->getConfig('showaggregateddata') == 0))
1903            // TIBO: TODO WE MUST SKIP THE FOLLOWING SECTION FOR TYPE A and 5 when
1904            // showaggreagated data is set and set to 1
1905            if (isset($_POST['noncompleted']) and ($_POST['noncompleted'] == "on")) {
1906                //counter
1907                $i = 0;
1908
1909                while (isset($gdata[$i])) {
1910                    if (isset($showaggregated_indice_table[$i]) && $showaggregated_indice_table[$i] == "aggregated") {
1911// do nothing, we don't rewrite aggregated results
1912                        // or at least I don't know how !!! (lemeur)
1913                    } else {
1914                        //we want to have some "real" data here
1915                        if ($gdata[$i] != "N/A") {
1916                            //calculate percentage
1917                            $gdata[$i] = ($grawdata[$i] / $TotalCompleted) * 100;
1918                        }
1919                    }
1920
1921                    //increase counter
1922                    $i++;
1923
1924                }    //end while (data available)
1925
1926            }    //end if -> noncompleted checked
1927
1928            //noncompleted is NOT checked
1929            else {
1930                //calculate total number of incompleted records
1931                $TotalIncomplete = $results - $TotalCompleted;
1932
1933                //output
1934                if ((incompleteAnsFilterState() != "complete")) {
1935                    $fname = gT("Not completed or Not displayed");
1936                } else {
1937                    $fname = gT("Not displayed");
1938                }
1939
1940                //we need some data
1941                if ($results > 0) {
1942                    //calculate percentage
1943                    $gdata[] = ($TotalIncomplete / $results) * 100;
1944                }
1945
1946                //no data :(
1947                else {
1948                    $gdata[] = "N/A";
1949                }
1950
1951                //put data of incompleted records into array
1952                $grawdata[] = $TotalIncomplete;
1953
1954                //put question title ("Not completed") into array
1955                $label[] = $fname;
1956
1957                //put the code ("Not completed") into the array
1958                $justcode[] = $fname;
1959
1960                //edit labels and put them into another array
1961                if ((incompleteAnsFilterState() != "complete")) {
1962                    $lbl[gT("Not completed or Not displayed")] = $TotalIncomplete;
1963                } else {
1964                    $lbl[gT("Not displayed")] = $TotalIncomplete;
1965                }
1966            }    //end else -> noncompleted NOT checked
1967        }
1968
1969        // For multiple choice question type, we have to check non completed with ALL sub question set to NULL
1970        if (($outputs['qtype'] == "M") or ($outputs['qtype'] == "P")) {
1971            $criteria = new CDbCriteria;
1972            foreach ($outputs['alist'] as $al) {
1973                $criteria->addCondition(Yii::app()->db->quoteColumnName($al[2])." IS NULL");
1974            }
1975            if (incompleteAnsFilterState() == "incomplete") {$criteria->addCondition("submitdate IS NULL"); } elseif (incompleteAnsFilterState() == "complete") {$criteria->addCondition("submitdate IS NOT NULL"); }
1976            $multiNotDisplayed = SurveyDynamic::model($surveyid)->count($criteria);
1977            if (isset($_POST['noncompleted']) and ($_POST['noncompleted'] == "on")) {
1978                //counter
1979                $i = 0;
1980                while (isset($gdata[$i])) {
1981                    //we want to have some "real" data here
1982                    if ($gdata[$i] != "N/A") {
1983                        //calculate percentage
1984                        if ($results > $multiNotDisplayed) {
1985                            $gdata[$i] = ($grawdata[$i] / ($results - $multiNotDisplayed)) * 100;
1986                        } else {
1987                            $gdata[$i] = "N/A";
1988                        }
1989                    }
1990                    $i++;
1991                }
1992            } else {
1993// Add a line with not displayed %
1994                if ($multiNotDisplayed > 0) {
1995                    if ((incompleteAnsFilterState() != "complete")) {
1996                        $fname = gT("Not completed or Not displayed");
1997                    } else {
1998                        $fname = gT("Not displayed");
1999                    }
2000                    $label[] = $fname;
2001                    $lbl[$fname] = $multiNotDisplayed;
2002                    //we need some data
2003                    if ($results > 0) {
2004                        //calculate percentage
2005                        $gdata[] = ($multiNotDisplayed / $results) * 100;
2006                    }
2007                    //no data :(
2008                    else {
2009                        $gdata[] = "N/A";
2010                    }
2011                    //put data of incompleted records into array
2012                    $grawdata[] = $multiNotDisplayed;
2013                }
2014            }
2015        }
2016
2017
2018        //counter
2019        $i = 0;
2020
2021        //we need to know which item we are editing
2022        $itemcounter = 1;
2023
2024        //loop through all available answers
2025        while (isset($gdata[$i])) {
2026            //repeat header (answer, count, ...) for each new question
2027            unset($showheadline);
2028
2029
2030            /*
2031            * there are 3 colums:
2032            *
2033            * 1 (50%) = answer (title and code in brackets)
2034            * 2 (25%) = count (absolute)
2035            * 3 (25%) = percentage
2036            */
2037            $statisticsoutput .= "\t<tr>\n\t\t<td class='text-left'>".$label[$i]."\n"
2038            ."\t\t</td>\n";
2039            /*
2040            * If there is a "browse" button in this label, let's make sure there's an extra row afterwards
2041            * to store the columnlist
2042            *
2043            * */
2044            if (strpos($label[$i], "class='statisticsbrowsebutton'")) {
2045                $extraline = "<tr><td class='statisticsbrowsecolumn' colspan='3' style='display: none'>";
2046                if ($outputs['qtype'] == 'P') {
2047                    $extraline .= "<div class='statisticsbrowsecolumn' id='columnlist_{$ColumnName_RM[$i]}'></div></td></tr>\n";
2048                } else {
2049                    $extraline .= "<div class='statisticsbrowsecolumn' id='columnlist_{$sColumnName}'></div></td></tr>\n";
2050                }
2051            }
2052
2053            //output absolute number of records
2054            $statisticsoutput .= "\t\t<td class='text-right'>".$grawdata[$i]."\n</td>";
2055
2056
2057            //no data
2058            if ($gdata[$i] === "N/A") {
2059                switch ($outputType) {
2060                    case 'xls':
2061                        $label[$i] = flattenText($label[$i]);
2062                        $this->xlsRow++;
2063                        $this->sheet->write($this->xlsRow, 0, $label[$i]);
2064                        $this->sheet->writeNumber($this->xlsRow, 1, $grawdata[$i]);
2065                        $this->sheet->writeNumber($this->xlsRow, 2, $gdata[$i] / 100, $this->xlsPercents);
2066                        break;
2067
2068                    case 'pdf':
2069
2070                        $tablePDF[] = array(flattenText($label[$i]), $grawdata[$i], sprintf("%01.2f", $gdata[$i])."%", "");
2071
2072                        break;
2073                    case 'html':
2074                        //output when having no data
2075                        $statisticsoutput .= "\t\t<td  class='text-right'>";
2076
2077                        //percentage = 0
2078                        $statisticsoutput .= sprintf("%01.2f", $gdata[$i])."%";
2079                        $gdata[$i] = 0;
2080
2081                        //check if we have to adjust ouput due to Yii::app()->getConfig('showaggregateddata') setting
2082                        if (Yii::app()->getConfig('showaggregateddata') == 1 && ($outputs['qtype'] == "5" || $outputs['qtype'] == "A")) {
2083                            $statisticsoutput .= "\t\t</td>";
2084                        } elseif ($outputs['qtype'] == "S" || $outputs['qtype'] == "U" || $outputs['qtype'] == "T" || $outputs['qtype'] == "Q") {
2085                            $statisticsoutput .= "</td>\n\t";
2086                        }
2087                        $statisticsoutput .= "</tr>\n"; //Close the row
2088                        if (isset($extraline)) {$statisticsoutput .= $extraline; }
2089                        break;
2090                    default:
2091
2092
2093                        break;
2094                }
2095
2096            }
2097
2098            //data available
2099            else {
2100                //check if data should be aggregated
2101                if (Yii::app()->getConfig('showaggregateddata') == 1 && ($outputs['qtype'] == "5" || $outputs['qtype'] == "A")) {
2102
2103                    if (($results - $grawdata[5]) > 0) {
2104                        $percentage = $grawdata[$i] / ($results - $grawdata[5]) * 100; // Only answered
2105                    } else {
2106                        $percentage = 0;
2107                    }
2108
2109                    switch ($itemcounter) {
2110                        case 1:
2111                            if (($results - $grawdata[5]) > 0) {
2112                                $aggregatedPercentage = ($grawdata[0] + $grawdata[1]) / ($results - $grawdata[5]) * 100;
2113                            } else {
2114                                $aggregatedPercentage = 0;
2115                            }
2116                            break;
2117
2118                        case 3:
2119                            $aggregatedPercentage = $percentage;
2120                            break;
2121
2122                        case 5:
2123                            if (($results - $grawdata[5]) > 0) {
2124                                $aggregatedPercentage = ($grawdata[3] + $grawdata[4]) / ($results - $grawdata[5]) * 100;
2125                            } else {
2126                                $aggregatedPercentage = 0;
2127                            }
2128                            break;
2129
2130                        case 6:
2131                        case 7:
2132                            if (($results - $grawdata[5]) > 0) {
2133                                $percentage = $grawdata[$i] / $results * 100; // All results
2134                            } else {
2135                                $percentage = 0;
2136                            }
2137                            break;
2138
2139                        default:
2140                            $aggregatedPercentage = 'na';
2141                            break;
2142                    }
2143
2144
2145                    switch ($outputType) {
2146                        case 'xls':
2147                            $label[$i] = flattenText($label[$i]);
2148                            $this->xlsRow++;
2149                            $this->sheet->write($this->xlsRow, 0, $label[$i]);
2150                            $this->sheet->writeNumber($this->xlsRow, 1, $grawdata[$i]);
2151                            $this->sheet->writeNumber($this->xlsRow, 2, $percentage / 100, $this->xlsPercents);
2152                            if ($aggregatedPercentage !== 'na') {
2153                                $this->sheet->writeNumber($this->xlsRow, 3, $aggregatedPercentage / 100, $this->xlsPercents);
2154                            }
2155                            break;
2156
2157                        case 'pdf':
2158                            $label[$i] = flattenText($label[$i]);
2159                            if ($aggregatedPercentage !== 'na') {
2160                                $tablePDF[] = array($label[$i], $grawdata[$i], sprintf("%01.2f", $percentage)."%", sprintf("%01.2f", $aggregatedPercentage)."%");
2161                            } else {
2162                                $tablePDF[] = array($label[$i], $grawdata[$i], sprintf("%01.2f", $percentage)."%", "");
2163                            }
2164                            break;
2165
2166                        case 'html':
2167                            //output percentage
2168                            $statisticsoutput .= "\t\t<td class='text-right'>";
2169                            $statisticsoutput .= sprintf("%01.2f", $percentage)."%</td>";
2170
2171                            $statisticsoutput .= "\t\t<td class='text-right'>";
2172                            if ($aggregatedPercentage !== 'na') {
2173                                $statisticsoutput .= sprintf("%01.2f", $aggregatedPercentage)."%";
2174                            } else {
2175                                $statisticsoutput .= '&nbsp;';
2176                            }
2177                            $statisticsoutput .= "</td>\t\t";
2178                            break;
2179
2180                        default:
2181                            break;
2182                    }
2183
2184                    if ($itemcounter == 5) {
2185                        // create new row "sum"
2186                        //calculate sum of items 1-5
2187                        $sumitems = $grawdata[0]
2188                        + $grawdata[1]
2189                        + $grawdata[2]
2190                        + $grawdata[3]
2191                        + $grawdata[4];
2192
2193                        //special treatment for zero values
2194                        if ($sumitems > 0) {
2195                            $sumpercentage = "100.00";
2196                        } else {
2197                            $sumpercentage = "0";
2198                        }
2199                        //special treatment for zero values
2200                        if ($TotalCompleted > 0) {
2201                            $casepercentage = "100.00";
2202                        } else {
2203                            $casepercentage = "0";
2204                        }
2205                        switch ($outputType) {
2206                            case 'xls':
2207
2208
2209                                $footXLS[] = array(gT("Sum")." (".gT("Answers").")", $sumitems, $sumpercentage."%", $sumpercentage."%");
2210                                $footXLS[] = array(gT("Number of cases"), $TotalCompleted, $casepercentage."%", "");
2211
2212                                $this->xlsRow++;
2213                                $this->sheet->write($this->xlsRow, 0, gT("Sum")." (".gT("Answers").")");
2214                                $this->sheet->writeNumber($this->xlsRow, 1, $sumitems);
2215                                $this->sheet->writeNumber($this->xlsRow, 2, $sumpercentage / 100, $this->xlsPercents);
2216                                $this->sheet->writeNumber($this->xlsRow, 3, $sumpercentage / 100, $this->xlsPercents);
2217                                $this->xlsRow++;
2218                                $this->sheet->write($this->xlsRow, 0, gT("Number of cases"));
2219                                $this->sheet->writeNumber($this->xlsRow, 1, $TotalCompleted);
2220                                $this->sheet->writeNumber($this->xlsRow, 2, $casepercentage / 100, $this->xlsPercents);
2221
2222                                break;
2223                            case 'pdf':
2224
2225                                $footPDF[] = array(gT("Sum")." (".gT("Answers").")", $sumitems, $sumpercentage."%", $sumpercentage."%");
2226                                $footPDF[] = array(gT("Number of cases"), $TotalCompleted, $casepercentage."%", "");
2227
2228                                break;
2229                            case 'html':
2230                                $statisticsoutput .= "\t\t&nbsp;\n\t</tr>\n";
2231                                $statisticsoutput .= "<tr><td class='text-right'><strong>".gT("Sum")." (".gT("Answers").")</strong></td>";
2232                                $statisticsoutput .= "<td class='text-right'><strong>".$sumitems."</strong></td>";
2233                                $statisticsoutput .= "<td class='text-right'><strong>$sumpercentage%</strong></td>";
2234                                $statisticsoutput .= "<td class='text-right'><strong>$sumpercentage%</strong></td>";
2235                                $statisticsoutput .= "\t\t&nbsp;\n\t</tr>\n";
2236
2237                                $statisticsoutput .= "<tr><td class='text-right'>".gT("Number of cases")."</td>"; //German: "Fallzahl"
2238                                $statisticsoutput .= "<td class='text-right'>".$TotalCompleted."</td>";
2239                                $statisticsoutput .= "<td class='text-right'>$casepercentage%</td>";
2240                                //there has to be a whitespace within the table cell to display correctly
2241                                $statisticsoutput .= "<td class='text-right'>&nbsp;</td></tr>";
2242                                break;
2243                            default:
2244
2245
2246                                break;
2247                        }
2248
2249                    }
2250
2251                }    //end if -> show aggregated data
2252
2253                //don't show aggregated data
2254                else {
2255                    switch ($outputType) {
2256                        case 'xls':
2257                            $label[$i] = flattenText($label[$i]);
2258                            $this->xlsRow++;
2259                            $this->sheet->write($this->xlsRow, 0, $label[$i]);
2260                            $this->sheet->writeNumber($this->xlsRow, 1, $grawdata[$i]);
2261                            $this->sheet->writeNumber($this->xlsRow, 2, $gdata[$i] / 100, $this->xlsPercents);
2262                            break;
2263
2264                        case 'pdf':
2265                            $label[$i] = flattenText($label[$i]);
2266                            $tablePDF[] = array($label[$i], $grawdata[$i], sprintf("%01.2f", $gdata[$i])."%", "");
2267
2268                            break;
2269                        case 'html':
2270                            //output percentage
2271                            $statisticsoutput .= "\t\t<td class='text-right'>";
2272                            $statisticsoutput .= sprintf("%01.2f", $gdata[$i])."%";
2273                            $statisticsoutput .= "\t\t";
2274                            //end output per line. there has to be a whitespace within the table cell to display correctly
2275                            $statisticsoutput .= "\t\t&nbsp;</td>\n\t</tr>\n";
2276                            if (isset($extraline)) {$statisticsoutput .= $extraline; }
2277                            break;
2278                        default:
2279
2280
2281                            break;
2282                    }
2283
2284                }
2285
2286            }    //end else -> $gdata[$i] != "N/A"
2287
2288
2289
2290            //increase counter
2291            $i++;
2292
2293            $itemcounter++;
2294
2295            //Clear extraline
2296            unset($extraline);
2297
2298        }    //end while
2299
2300        //only show additional values when this setting is enabled
2301        if (Yii::app()->getConfig('showaggregateddata') == 1) {
2302            //it's only useful to calculate standard deviation and arithmetic means for question types
2303            //5 = 5 Point Scale
2304            //A = Array (5 Point Choice)
2305            if ($outputs['qtype'] == "5" || $outputs['qtype'] == "A") {
2306                $stddev = 0;
2307                $stddevarray = array_slice($grawdata, 0, 5, true);
2308                $am = 0;
2309
2310                //calculate arithmetic mean
2311                if (isset($sumitems) && $sumitems > 0) {
2312
2313
2314                    //calculate and round results
2315                    //there are always 5 items
2316                    for ($x = 0; $x < 5; $x++) {
2317                        //create product of item * value
2318                        $am += (($x + 1) * $stddevarray[$x]);
2319                    }
2320
2321                    //prevent division by zero
2322                    if (isset($stddevarray) && array_sum($stddevarray) > 0) {
2323                        $am = round($am / array_sum($stddevarray), 2);
2324                    } else {
2325                        $am = 0;
2326                    }
2327
2328                    //calculate standard deviation -> loop through all data
2329                    /*
2330                    * four steps to calculate the standard deviation
2331                    * 1 = calculate difference between item and arithmetic mean and multiply with the number of elements
2332                    * 2 = create sqaure value of difference
2333                    * 3 = sum up square values
2334                    * 4 = multiply result with 1 / (number of items)
2335                    * 5 = get root
2336                    */
2337
2338
2339
2340                    for ($j = 0; $j < 5; $j++) {
2341                        //1 = calculate difference between item and arithmetic mean
2342                        $diff = (($j + 1) - $am);
2343
2344                        //2 = create square value of difference
2345                        $squarevalue = square($diff);
2346
2347                        //3 = sum up square values and multiply them with the occurence
2348                        //prevent divison by zero
2349                        if ($squarevalue != 0 && $stddevarray[$j] != 0) {
2350                            $stddev += $squarevalue * $stddevarray[$j];
2351                        }
2352
2353                    }
2354
2355                    //4 = multiply result with 1 / (number of items (=5))
2356                    //There are two different formulas to calculate standard derivation
2357                    //$stddev = $stddev / array_sum($stddevarray);        //formula source: http://de.wikipedia.org/wiki/Standardabweichung
2358
2359                    //prevent division by zero
2360                    if ((array_sum($stddevarray) - 1) != 0 && $stddev != 0) {
2361                        $stddev = $stddev / (array_sum($stddevarray) - 1); //formula source: http://de.wikipedia.org/wiki/Empirische_Varianz
2362                    } else {
2363                        $stddev = 0;
2364                    }
2365
2366                    //5 = get root
2367                    $stddev = sqrt($stddev);
2368                    $stddev = round($stddev, 2);
2369                }
2370                switch ($outputType) {
2371                    case 'xls':
2372                        $this->xlsRow++;
2373                        $this->sheet->write($this->xlsRow, 0, gT("Arithmetic mean"));
2374                        $this->sheet->writeNumber($this->xlsRow, 1, $am);
2375                        $this->xlsRow++;
2376                        $this->sheet->write($this->xlsRow, 0, gT("Standard deviation"));
2377                        $this->sheet->writeNumber($this->xlsRow, 1, $stddev);
2378                        break;
2379
2380                    case 'pdf':
2381
2382                        $tablePDF[] = array(gT("Arithmetic mean"), $am, '', '');
2383                        $tablePDF[] = array(gT("Standard deviation"), $stddev, '', '');
2384
2385                        break;
2386                    case 'html':
2387                        //calculate standard deviation
2388                        $statisticsoutput .= "<tr><td class='text-right'>".gT("Arithmetic mean")."</td>"; //German: "Fallzahl"
2389                        $statisticsoutput .= "<td>&nbsp;</td><td class='text-right'> $am</td><td>&nbsp;</td></tr>";
2390                        $statisticsoutput .= "<tr><td class='text-right'>".gT("Standard deviation")."</td>"; //German: "Fallzahl"
2391                        $statisticsoutput .= "<td>&nbsp;</td><td class='text-right'>$stddev</td><td>&nbsp;</td></tr>";
2392
2393                        break;
2394                    default:
2395
2396
2397                        break;
2398                }
2399            }
2400        }
2401
2402        if ($outputType == 'pdf') {
2403            $tablePDF = array_merge_recursive($tablePDF, $footPDF);
2404            $this->pdf->headTable($headPDF, $tablePDF);
2405            if (isset($headPDF2)) {
2406                $this->pdf->headTable($headPDF2, $tablePDF2);
2407            }
2408        }
2409
2410        if ($outputType == 'xls' && (isset($headXLS) || isset($tableXLS))) {
2411            if (isset($headXLS)) {
2412                $this->xlsRow++;
2413                $this->xlsRow++;
2414                foreach ($headXLS as $aRow) {
2415                    $this->xlsRow++;
2416                    $iColumn = 0;
2417                    foreach ($aRow as $sValue) {
2418                        $this->sheet->write($this->xlsRow, $iColumn, $sValue, $this->formatBold);
2419                        $iColumn++;
2420                    }
2421                }
2422            }
2423            if (isset($tableXLS)) {
2424                foreach ($tableXLS as $aRow) {
2425                    $this->xlsRow++;
2426                    $iColumn = 0;
2427                    foreach ($aRow as $sValue) {
2428                        $this->sheet->write($this->xlsRow, $iColumn, $sValue);
2429                        $iColumn++;
2430                    }
2431                }
2432
2433            }
2434        }
2435
2436        if ($outputType == 'html') {
2437            $statisticsoutput .= "<tr><td colspan='4' style=\"text-align:center\" id='statzone_$rt'>";
2438        }
2439
2440
2441
2442        //-------------------------- PCHART OUTPUT ----------------------------
2443        list($qsid, $qgid, $qqid) = explode("X", $rt, 3);
2444        $qsid = $surveyid;
2445        $aattr = QuestionAttribute::model()->getQuestionAttributes($outputs['parentqid']);
2446
2447        //PCHART has to be enabled and we need some data
2448        if ($usegraph == 1) {
2449            $bShowGraph = $aattr["statistics_showgraph"] == "1";
2450            $bAllowPieChart = ($outputs['qtype'] != "M" && $outputs['qtype'] != "P");
2451            $bAllowMap = (isset($aattr["location_mapservice"]) && $aattr["location_mapservice"] == "1");
2452            $bShowMap = ($bAllowMap && $aattr["statistics_showmap"] == "1");
2453            $bShowPieChart = ($bAllowPieChart && (isset($aattr["statistics_graphtype"]) && $aattr["statistics_graphtype"] == "1"));
2454
2455            $astatdata[$rt] = array(
2456                'id' => $rt,
2457                'sg' => $bShowGraph,
2458                'ap' => $bAllowPieChart,
2459                'am' => $bAllowMap,
2460                'sm' => $bShowMap,
2461                'sp' => $bShowPieChart
2462            );
2463
2464            $stats = Yii::app()->session['stats'];
2465            $stats[$rt] = array(
2466                'lbl' => $lbl,
2467                'gdata' => $gdata,
2468                'grawdata' => $grawdata
2469            );
2470            Yii::app()->session['stats'] = $stats;
2471
2472            if ($bShowGraph == true) {
2473                $cachefilename = createChart($qqid, $qsid, $bShowPieChart, $lbl, $gdata, $grawdata, $MyCache, $sLanguage, $outputs['qtype']);
2474                if ($cachefilename) {
2475// Add the image only if constructed
2476                    //introduce new counter
2477                    if (!isset($ci)) {$ci = 0; }
2478
2479                    switch ($outputType) {
2480                        case 'xls':
2481
2482                            /**
2483                             * No Image for Excel...
2484                             */
2485
2486                            break;
2487                        case 'pdf':
2488
2489                            $this->pdf->AddPage('P', 'A4');
2490
2491                            $this->pdf->titleintopdf($pdfTitle, $titleDesc);
2492                            $this->pdf->Image($tempdir."/".$cachefilename, 0, 70, 180, 0, '', Yii::app()->getController()->createUrl("admin/survey/sa/view/surveyid/".$surveyid), 'B', true, 150, 'C', false, false, 0, true);
2493
2494                            break;
2495                        case 'html':
2496                            $statisticsoutput .= "<img src=\"$tempurl/".$cachefilename."\" border='1' />";
2497
2498                            $aattr = QuestionAttribute::model()->getQuestionAttributes($qqid);
2499                            if ($bShowMap) {
2500                                $statisticsoutput .= "<div id=\"statisticsmap_$rt\" class=\"statisticsmap\"></div>";
2501
2502                                $agmapdata[$rt] = array(
2503                                    "coord" => getQuestionMapData(substr($rt, 1), $qsid),
2504                                    "zoom" => $aattr['location_mapzoom'],
2505                                    "width" => $aattr['location_mapwidth'],
2506                                    "height" => $aattr['location_mapheight']
2507                                );
2508                            }
2509                            break;
2510                        default:
2511                            break;
2512                    }
2513                }
2514            }
2515        }
2516
2517        //close table/output
2518        if ($outputType == 'html') {
2519            // show this block only when we show graphs and are not in the public statics controller
2520            // this is because the links don't work from that controller
2521            if ($usegraph == 1 && get_class(Yii::app()->getController()) !== 'Statistics_userController') {
2522                $sImgUrl = Yii::app()->getConfig('adminimageurl');
2523
2524                $statisticsoutput .= "</td></tr><tr><td colspan='4'><div id='stats_$rt' class='graphdisplay' style=\"text-align:center\">"
2525                ."<img class='stats-hidegraph' src='$sImgUrl/chart_disabled.png' title='".gT("Disable chart")."' />"
2526                ."<img class='stats-showgraph' src='$sImgUrl/chart.png' title='".gT("Enable chart")."' />"
2527                ."<img class='stats-showbar' src='$sImgUrl/chart_bar.png' title='".gT("Display as bar chart")."' />"
2528                ."<img class='stats-showpie' src='$sImgUrl/chart_pie.png' title='".gT("Display as pie chart")."' />"
2529                ."<img class='stats-showmap' src='$sImgUrl/map_disabled.png' title='".gT("Disable map display")."' />"
2530                ."<img class='stats-hidemap' src='$sImgUrl/map.png' title='".gT("Enable map display")."' />"
2531                ."</div></td></tr>";
2532
2533            }
2534            $statisticsoutput .= "</td></tr></table></div><br /> \n";
2535        }
2536
2537        return array("statisticsoutput"=>$statisticsoutput, "pdf"=>$this->pdf, "astatdata"=>$astatdata);
2538
2539    }
2540
2541    /**
2542     * Generates statistics
2543     *
2544     * @param int $surveyid The survey id
2545     * @param mixed $allfields
2546     * @param mixed $q2show
2547     * @param integer $usegraph
2548     * @param string $outputType Optional - Can be xls, html or pdf - Defaults to pdf
2549     * @param string $pdfOutput Sets the target for the PDF output: DD=File download , F=Save file to local disk
2550     * @param boolean $browse  Show browse buttons
2551     * @return string|null
2552     */
2553    public function generate_statistics($surveyid, $allfields, $q2show = 'all', $usegraph = 0, $outputType = 'pdf', $pdfOutput = 'I', $sLanguageCode = null, $browse = true)
2554    {
2555        $survey = Survey::model()->findByPk($surveyid);
2556
2557        $aStatisticsData = array(); //astatdata generates data for the output page's javascript so it can rebuild graphs on the fly
2558        //load surveytranslator helper
2559        Yii::import('application.helpers.surveytranslator_helper', true);
2560        Yii::import('application.third_party.ar-php.Arabic', true);
2561
2562        $sTempDir = Yii::app()->getConfig("tempdir");
2563
2564        $this->pdf = array(); //Make sure $this->pdf exists - it will be replaced with an object if a $this->pdf is actually being created
2565
2566        //pick the best font file if font setting is 'auto'
2567        if (is_null($sLanguageCode)) {
2568            $sLanguageCode = $survey->language;
2569        }
2570        Yii::app()->setLanguage($sLanguageCode);
2571
2572        //we collect all the html-output within this variable
2573        $sOutputHTML = '';
2574        /**
2575         * $outputType: html || pdf ||
2576         */
2577        /**
2578         * get/set Survey Details
2579         */
2580
2581        //no survey ID? -> come and get one
2582        if (!isset($surveyid)) {$surveyid = returnGlobal('sid'); }
2583
2584        // Set language for questions and answers to base language of this survey
2585        $language = $sLanguageCode;
2586
2587        if ($q2show == 'all') {
2588            $summarySql = " SELECT gid, parent_qid, qid, type "
2589            ." FROM {{questions}} WHERE parent_qid=0"
2590            ." AND sid=$surveyid ";
2591
2592            $summaryRs = Yii::app()->db->createCommand($summarySql)->query()->readAll();
2593
2594            foreach ($summaryRs as $field) {
2595                $myField = $surveyid."X".$field['gid']."X".$field['qid'];
2596
2597                // Multiple choice get special treatment
2598                if ($field['type'] == "M") {$myField = "M".$myField; }
2599                if ($field['type'] == "P") {$myField = "P".$myField; }
2600                //numerical input will get special treatment (arihtmetic mean, standard derivation, ...)
2601                if ($field['type'] == "N") {$myField = "N".$myField; }
2602
2603                if ($field['type'] == "|") {$myField = "|".$myField; }
2604
2605                if ($field['type'] == "Q") {$myField = "Q".$myField; }
2606                // textfields get special treatment
2607                if ($field['type'] == "S" || $field['type'] == "T" || $field['type'] == "U") {$myField = "T".$myField; }
2608                //statistics for Date questions are not implemented yet.
2609                if ($field['type'] == "D") {$myField = "D".$myField; }
2610                if ($field['type'] == "F" || $field['type'] == "H") {
2611                    //Get answers. We always use the answer code because the label might be too long elsewise
2612                    $query = "SELECT code, answer FROM {{answers}} WHERE qid='".$field['qid']."' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, answer";
2613                    $result = Yii::app()->db->createCommand($query)->query();
2614
2615                    //check all the answers
2616                    foreach ($result->readAll() as $row) {
2617                        $row = array_values($row);
2618                        $myField = "$myField{$row[0]}";
2619                    }
2620                    //$myField = "{$surveyid}X{$flt[1]}X{$flt[0]}{$row[0]}[]";
2621
2622
2623                }
2624                if ($q2show == 'all') {
2625                                    $summary[] = $myField;
2626                }
2627
2628                //$allfields[]=$myField;
2629            }
2630        } else {
2631            // This gets all the 'to be shown questions' from the POST and puts these into an array
2632            if (!is_array($q2show)) {
2633                            $summary = returnGlobal('summary');
2634            } else {
2635                            $summary = $q2show;
2636            }
2637
2638            //print_r($_POST);
2639            //if $summary isn't an array we create one
2640            if (isset($summary) && !is_array($summary)) {
2641                $summary = explode("+", $summary);
2642            }
2643        }
2644
2645        /**
2646         * pdf Config
2647         */
2648        if ($outputType == 'pdf') {
2649            //require_once('classes/tcpdf/mypdf.php');
2650            Yii::import('application.libraries.admin.pdf', true);
2651            Yii::import('application.helpers.pdfHelper');
2652            $aPdfLanguageSettings = pdfHelper::getPdfLanguageSettings($language);
2653
2654            // create new PDF document
2655            $this->pdf = new pdf();
2656
2657            $surveyInfo = getSurveyInfo($surveyid, $language);
2658
2659            // set document information
2660            $this->pdf->SetCreator(PDF_CREATOR);
2661            $this->pdf->SetAuthor('LimeSurvey');
2662            $this->pdf->SetTitle(sprintf(gT("Statistics survey %s"), $surveyid));
2663            $this->pdf->SetSubject($surveyInfo['surveyls_title']);
2664            $this->pdf->SetKeywords('LimeSurvey,'.gT("Statistics").', '.sprintf(gT("Survey %s"), $surveyid));
2665            $this->pdf->SetDisplayMode('fullpage', 'two');
2666            $this->pdf->setLanguageArray($aPdfLanguageSettings['lg']);
2667
2668            // set header and footer fonts
2669            $this->pdf->setHeaderFont(Array($aPdfLanguageSettings['pdffont'], '', PDF_FONT_SIZE_MAIN));
2670            $this->pdf->setFooterFont(Array($aPdfLanguageSettings['pdffont'], '', PDF_FONT_SIZE_DATA));
2671
2672            // set default header data
2673            // Since png crashes some servers (and we can not try/catch that) we use .gif (or .jpg) instead
2674            $headerlogo = 'statistics.gif';
2675            $this->pdf->SetHeaderData($headerlogo, 10, gT("Quick statistics", 'unescaped'), gT("Survey")." ".$surveyid." '".flattenText($surveyInfo['surveyls_title'], false, true, 'UTF-8')."'");
2676            $this->pdf->SetFont($aPdfLanguageSettings['pdffont'], '', $aPdfLanguageSettings['pdffontsize']);
2677            // set default monospaced font
2678            $this->pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
2679        }
2680        if ($outputType == 'xls') {
2681            /**
2682             * Initiate the Spreadsheet_Excel_Writer
2683             */
2684            require_once(APPPATH.'/third_party/pear/Spreadsheet/Excel/Writer.php');
2685
2686            if ($pdfOutput == 'F') {
2687                $sFileName = $sTempDir.'/statistic-survey'.$surveyid.'.xls';
2688                $this->workbook = new Spreadsheet_Excel_Writer($sFileName);
2689            } else {
2690                            $this->workbook = new Spreadsheet_Excel_Writer();
2691            }
2692
2693            $this->workbook->setVersion(8);
2694            // Inform the module that our data will arrive as UTF-8.
2695            // Set the temporary directory to avoid PHP error messages due to open_basedir restrictions and calls to tempnam("", ...)
2696            $this->workbook->setTempDir($sTempDir);
2697
2698            // Inform the module that our data will arrive as UTF-8.
2699            // Set the temporary directory to avoid PHP error messages due to open_basedir restrictions and calls to tempnam("", ...)
2700            if (!empty($sTempDir)) {
2701                $this->workbook->setTempDir($sTempDir);
2702            }
2703            if ($pdfOutput != 'F') {
2704                            $this->workbook->send('statistic-survey'.$surveyid.'.xls');
2705            }
2706
2707            // Creating the first worksheet
2708            $this->sheet = $this->workbook->addWorksheet(utf8_decode('results-survey'.$surveyid));
2709            $this->xlsPercents = &$this->workbook->addFormat();
2710            $this->xlsPercents->setNumFormat('0.00%');
2711            $this->formatBold = &$this->workbook->addFormat(array('Bold'=>1));
2712            $this->sheet->setInputEncoding('utf-8');
2713            $this->sheet->setColumn(0, 20, 20);
2714            /**XXX*/
2715        }
2716        /**
2717         * Start generating
2718         */
2719
2720
2721
2722        $selects = buildSelects($allfields, $surveyid, $language);
2723
2724        //count number of answers
2725        $query = "SELECT count(*) FROM {{survey_$surveyid}}";
2726
2727        //if incompleted answers should be filtert submitdate has to be not null
2728        if (incompleteAnsFilterState() == "incomplete") {$query .= " WHERE submitdate is null"; } elseif (incompleteAnsFilterState() == "complete") {$query .= " WHERE submitdate is not null"; }
2729        $total = Yii::app()->db->createCommand($query)->queryScalar();
2730
2731        //are there any filters that have to be taken care of?
2732        if (isset($selects) && $selects) {
2733            //Save the filters to session for use in browsing text & other features (statistics.php function listcolumn())
2734            Yii::app()->session['statistics_selects_'.$surveyid] = $selects;
2735            //filter incomplete answers?
2736            if (incompleteAnsFilterState() == "complete" || incompleteAnsFilterState() == "incomplete") {$query .= " AND "; } else {$query .= " WHERE "; }
2737
2738            //add filter criteria to SQL
2739            $query .= implode(" AND ", $selects);
2740        }
2741
2742
2743        //get me some data Scotty
2744        $results = Yii::app()->db->createCommand($query)->queryScalar();
2745
2746        if ($total) {
2747            $percent = sprintf("%01.2f", ($results / $total) * 100);
2748
2749        }
2750        switch ($outputType) {
2751            case "xls":
2752                $this->xlsRow = 0;
2753                $this->sheet->write($this->xlsRow, 0, gT("Number of records in this query:", 'unescaped'));
2754                $this->sheet->writeNumber($this->xlsRow, 1, $results);
2755                $this->xlsRow++;
2756                $this->sheet->write($this->xlsRow, 0, gT("Total records in survey:", 'unescaped'));
2757                $this->sheet->writeNumber($this->xlsRow, 1, $total);
2758
2759                if ($total) {
2760                    $this->xlsRow++;
2761                    $this->sheet->write($this->xlsRow, 0, gT("Percentage of total:", 'unescaped'));
2762                    $this->sheet->writeNumber($this->xlsRow, 1, $results / $total, $this->xlsPercents);
2763                }
2764
2765                break;
2766
2767            case 'pdf':
2768                // add summary to pdf
2769                $array = array(
2770                    array(gT("Number of records in this query:", 'unescaped'), $results),
2771                    array(gT("Total records in survey:", 'unescaped'), $total)
2772                );
2773                if ($total) {
2774                    $array[] = array(gT("Percentage of total:", 'unescaped'), $percent."%");
2775                }
2776                $this->pdf->AddPage('P', ' A4');
2777                $this->pdf->Bookmark(gT("Results", 'unescaped'), 0, 0);
2778                $this->pdf->titleintopdf(gT("Results", 'unescaped'), gT("Survey", 'unescaped')." ".$surveyid);
2779                $this->pdf->tableintopdf($array);
2780                break;
2781
2782            case 'html':
2783
2784                $sOutputHTML .= "<br />\n<div class='well'><table class='table table-striped statisticssummary' >\n"
2785                ."\t<thead><tr><th class='text-center' colspan='2'>".gT("Results")."</th></tr></thead>\n"
2786                ."\t<tr><th style='text-align:left'>".gT("Number of records in this query:").'</th>'
2787                ."<td style='text-align:right'>$results</td></tr>\n"
2788                ."\t<tr><th style='text-align:left'>".gT("Total records in survey:").'</th>'
2789                ."<td style='text-align:right'>$total</td></tr>\n";
2790
2791                //only calculate percentage if $total is set
2792                if ($total) {
2793                    $percent = sprintf("%01.2f", ($results / $total) * 100);
2794                    $sOutputHTML .= "\t<tr><th style='text-align:left'>".gT("Percentage of total:").'</th>'
2795                    ."<td style='text-align:right'>$percent%</td></tr>\n";
2796                }
2797                $sOutputHTML .= "<tr><td>&nbsp</td></tr>";
2798                if ($outputType == 'html' && $browse === true && Permission::model()->hasSurveyPermission($surveyid, 'responses', 'read')) {
2799                    //add a buttons to browse results
2800                    $sOutputHTML .= "<tr><td clospan='2' style='text-align:center'>";
2801                    $sOutputHTML .= CHtml::link(gT("Browse"), array("admin/responses", "sa"=>'browse', 'surveyid'=>$surveyid, 'statfilter'=>1), array('class'=>'button btn-link'));
2802                    $sOutputHTML .= CHtml::link(gT("Export"), array("admin/export", "sa"=>'exportresults', 'surveyid'=>$surveyid, 'statfilter'=>1), array('class'=>'button btn-link'));
2803                    $sOutputHTML .= "</td></tr>";
2804
2805                }
2806                $sOutputHTML .= "</table></div>\n";
2807
2808                break;
2809            default:
2810
2811
2812                break;
2813        }
2814
2815        //put everything from $selects array into a string connected by AND
2816        //This string ($sql) can then be passed on to other functions so you can
2817        //browse these results
2818        if (isset ($selects) && $selects) {$sql = implode(" AND ", $selects); } elseif (!empty($newsql)) {$sql = $newsql; }
2819
2820        if (!isset($sql) || !$sql) {
2821            $sql = null;
2822        }
2823
2824        /* Show Summary results
2825        * The $summary array contains each fieldname that we want to display statistics for
2826        *
2827        * */
2828
2829        if (isset($summary) && $summary) {
2830            //let's run through the survey
2831            $runthrough = $summary;
2832
2833            //START Chop up fieldname and find matching questions
2834
2835            //loop through all selected questions
2836            foreach ($runthrough as $rt) {
2837
2838                //Step 1: Get information about this response field (SGQA) for the summary
2839                $outputs = $this->buildOutputList($rt, $language, $surveyid, $outputType, $sql, $sLanguageCode);
2840                $sOutputHTML .= $outputs['statisticsoutput'];
2841                //2. Collect and Display results #######################################################################
2842                if (isset($outputs['alist']) && $outputs['alist']) {
2843//Make sure there really is an answerlist, and if so:
2844                    $display = $this->displayResults($outputs, $results, $rt, $outputType, $surveyid, $sql, $usegraph, $browse, $sLanguageCode);
2845                    $sOutputHTML .= $display['statisticsoutput'];
2846                    $aStatisticsData = array_merge($aStatisticsData, $display['astatdata']);
2847                }    //end if -> collect and display results
2848
2849
2850                //Delete Build Outputs data
2851                unset($outputs);
2852                unset($display);
2853            }    // end foreach -> loop through all questions
2854
2855            //output
2856            if ($outputType == 'html') {
2857                        $sOutputHTML .= "<br />&nbsp;\n";
2858            }
2859
2860        }    //end if -> show summary results
2861
2862        switch ($outputType) {
2863            case 'xls':
2864
2865                $this->workbook->close();
2866
2867                if ($pdfOutput == 'F') {
2868                    return $sFileName;
2869                } else {
2870                    return null;
2871                }
2872                break;
2873
2874            case 'pdf':
2875                $this->pdf->lastPage();
2876
2877                if ($pdfOutput == 'F') {
2878// This is only used by lsrc to send an E-Mail attachment, so it gives back the filename to send and delete afterwards
2879                    $tempfilename = $sTempDir."/Survey_".$surveyid.".pdf";
2880                    $this->pdf->Output($tempfilename, $pdfOutput);
2881                    return $tempfilename;
2882                } else {
2883                                    return $this->pdf->Output(gT('Survey').'_'.$surveyid."_".$surveyInfo['surveyls_title'].'.pdf', $pdfOutput);
2884                }
2885
2886                break;
2887            case 'html':
2888                $sGoogleMapsAPIKey = trim(Yii::app()->getConfig("googleMapsAPIKey"));
2889                if ($sGoogleMapsAPIKey != '') {
2890                    $sGoogleMapsAPIKey = '&key='.$sGoogleMapsAPIKey;
2891                }
2892                $sSSL = '';
2893                if (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != "off") {
2894                    $sSSL = 's';
2895                }
2896                $sOutputHTML .= "<script type=\"text/javascript\" src=\"http{$sSSL}://maps.googleapis.com/maps/api/js?sensor=false$sGoogleMapsAPIKey\"></script>\n"
2897                ."<script type=\"text/javascript\">var site_url='".Yii::app()->baseUrl."';var temppath='".Yii::app()->getConfig("tempurl")."';var imgpath='".Yii::app()->getConfig('adminimageurl')."';var aStatData=".ls_json_encode($aStatisticsData)."</script>";
2898                return $sOutputHTML;
2899
2900                break;
2901            default:
2902                return $sOutputHTML;
2903
2904                break;
2905        }
2906
2907    }
2908
2909    /**
2910     * Get the quartile using minitab method
2911     *
2912     * L=(1/4)(n+1), U=(3/4)(n+1)
2913     * Minitab linear interpolation between the two
2914     * closest data points. Minitab would let L = 2.5 and find the value half way between the
2915     * 2nd and 3rd data points. In our example, that would be (4+9)/2 =
2916     * 6.5. Similarly, the upper quartile value would be half way between
2917     * the 7th and 8th data points, which would be (49+64)/2 = 56.5. If L
2918     * were 2.25, Minitab would find the value one fourth of the way
2919     * between the 2nd and 3rd data points and if L were 2.75, Minitab
2920     * would find the value three fourths of the way between the 2nd and
2921     * 3rd data points.
2922     *
2923     * @staticvar null $sid
2924     * @staticvar int $recordCount
2925     * @staticvar null $field
2926     * @staticvar null $allRows
2927     * @param integer $quartile use 0 for return of recordcount, otherwise will return Q1,Q2,Q3
2928     * @param string $fieldname
2929     * @param int $surveyid
2930     * @param string $sql
2931     * @param bool $excludezeros
2932     * @return null|float
2933     */
2934    protected function getQuartile($quartile, $fieldname, $surveyid, $sql, $excludezeros)
2935    {
2936        static $sid = null;
2937        static $recordCount = 0;
2938        static $field = null;
2939        static $allRows = null;
2940
2941        if ($surveyid !== $sid || $fieldname !== $field) {
2942            //get data
2943            $query = " FROM {{survey_$surveyid}} WHERE ".Yii::app()->db->quoteColumnName($fieldname)." IS NOT null";
2944            //NO ZEROES
2945            if (!$excludezeros) {
2946                $query .= " AND ".Yii::app()->db->quoteColumnName($fieldname)." != 0";
2947            }
2948
2949            //filtering enabled?
2950            if (incompleteAnsFilterState() == "incomplete") {
2951                $query .= " AND submitdate is null";
2952            } elseif (incompleteAnsFilterState() == "complete") {
2953                $query .= " AND submitdate is not null";
2954            }
2955
2956            //if $sql values have been passed to the statistics script from another script, incorporate them
2957            if (!empty($sql)) {$query .= " AND $sql"; }
2958        }
2959
2960        if ($surveyid !== $sid) {
2961            $sid = $surveyid;
2962            $recordCount = 0;
2963            $field = null; // Reset cache
2964        }
2965
2966        if ($fieldname !== $field) {
2967            $field = $fieldname;
2968            $allRows = Yii::app()->db->createCommand("SELECT ".Yii::app()->db->quoteColumnName($fieldname).$query.' ORDER BY '.Yii::app()->db->quoteColumnName($fieldname))->queryAll();
2969            $recordCount = Yii::app()->db->createCommand("SELECT COUNT(".Yii::app()->db->quoteColumnName($fieldname).")".$query)->queryScalar(); // Record count for THIS $fieldname
2970        }
2971
2972        // Qx = (x/4) * (n+1) if not integer, interpolate
2973        switch ($quartile) {
2974            case 1:
2975            case 3:
2976                // Need at least 4 records
2977                if ($recordCount < 4) {
2978                    return null;
2979                }
2980                break;
2981            case 2:
2982                // Need at least 2 records
2983                if ($recordCount < 2) {
2984                    return null;
2985                }
2986                break;
2987
2988            case 0:
2989                return $recordCount;
2990
2991            default:
2992                return null;
2993                break;
2994        }
2995
2996        $q1 = $quartile / 4 * ($recordCount + 1);
2997        $row = $q1 - 1; // -1 since we start counting at 0
2998        if ($q1 === (int) $q1) {
2999            return $allRows[$row][$fieldname];
3000        } else {
3001            $diff = ($q1 - (int) $q1);
3002            return $allRows[$row][$fieldname] + $diff * ($allRows[$row + 1][$fieldname] - $allRows[$row][$fieldname]);
3003        }
3004    }
3005
3006    /**
3007     *  Returns a simple list of values in a particular column, that meet the requirements of the SQL
3008     */
3009    function _listcolumn($surveyid, $column, $sortby = "", $sortmethod = "", $sorttype = "")
3010    {
3011        $search['condition'] = Yii::app()->db->quoteColumnName($column)." != ''";
3012        $sDBDriverName = Yii::app()->db->getDriverName();
3013        if ($sDBDriverName == 'sqlsrv' || $sDBDriverName == 'mssql' || $sDBDriverName == 'dblib') {
3014            $search['condition'] = "CAST(".Yii::app()->db->quoteColumnName($column)." as varchar) != ''";
3015        }
3016
3017        //filter incomplete answers if set
3018        if (incompleteAnsFilterState() == "incomplete") {$search['condition'] .= " AND submitdate is null"; } elseif (incompleteAnsFilterState() == "complete") {$search['condition'] .= " AND submitdate is not null"; }
3019
3020        //Look for any selects/filters set in the original statistics query, and apply them to the column listing
3021        if (isset(Yii::app()->session['statistics_selects_'.$surveyid]) && is_array(Yii::app()->session['statistics_selects_'.$surveyid])) {
3022            foreach (Yii::app()->session['statistics_selects_'.$surveyid] as $sql) {
3023                $search['condition'] .= " AND $sql";
3024            }
3025        }
3026
3027        if ($sortby != '') {
3028            if ($sDBDriverName == 'sqlsrv' || $sDBDriverName == 'mssql' || $sDBDriverName == 'dblib') {
3029                $sortby = "CAST(".Yii::app()->db->quoteColumnName($sortby)." as varchar)";
3030            } else {
3031                $sortby = Yii::app()->db->quoteColumnName($sortby);
3032            }
3033
3034            if ($sorttype == 'N') {$sortby = "($sortby * 1)"; } //Converts text sorting into numerical sorting
3035            $search['order'] = $sortby.' '.$sortmethod;
3036        }
3037        $results = SurveyDynamic::model($surveyid)->findAll($search);
3038        $output = array();
3039        foreach ($results as $row) {
3040            $output[] = array("id"=>$row['id'], "value"=>$row[$column]);
3041        }
3042        return $output;
3043    }
3044
3045    /**
3046     * @param string $outputType
3047     * @return string
3048     */
3049    private function getLinefeed($outputType)
3050    {
3051        switch ($outputType) {
3052            case 'xls':
3053            case 'pdf':
3054                $linefeed = "\n";
3055                break;
3056            case 'html':
3057                $linefeed = "<br />\n";
3058                break;
3059            default:
3060                throw new \CInvalidArgumentException('Unknown output type: '.$outputType);
3061                break;
3062        }
3063
3064        return $linefeed;
3065    }
3066
3067}
3068