public function vvexport()
{
$iSurveyID = sanitize_int(Yii::app()->request->getParam('surveyid'));
$subaction = Yii::app()->request->getParam('subaction');
//Exports all responses to a survey in special "Verified Voting" format.
$clang = $this->getController()->lang;
if (!hasSurveyPermission($iSurveyID, 'responses', 'export')) {
return;
}
if ($subaction != "export") {
$selecthide = "";
$selectshow = "";
$selectinc = "";
if (incompleteAnsFilterState() == "incomplete") {
$selectinc = "selected='selected'";
} elseif (incompleteAnsFilterState() == "complete") {
$selecthide = "selected='selected'";
} else {
$selectshow = "selected='selected'";
}
$data['selectinc'] = $selectinc;
$data['selecthide'] = $selecthide;
$data['selectshow'] = $selectshow;
$data['surveyid'] = $iSurveyID;
$data['display']['menu_bars']['browse'] = $clang->gT("Export VV file");
$this->_renderWrappedTemplate('export', 'vv_view', $data);
} elseif (isset($iSurveyID) && $iSurveyID) {
//Export is happening
$extension = sanitize_paranoid_string(returnGlobal('extension'));
$fn = "vvexport_{$iSurveyID}." . $extension;
$this->_addHeaders($fn, "text/comma-separated-values", 0, "cache");
$s = "\t";
$fieldmap = createFieldMap($iSurveyID, 'full', false, false, getBaseLanguageFromSurveyID($iSurveyID));
$surveytable = "{{survey_{$iSurveyID}}}";
Survey::model()->findByPk($iSurveyID)->language;
$fieldnames = Yii::app()->db->schema->getTable($surveytable)->getColumnNames();
//Create the human friendly first line
$firstline = "";
$secondline = "";
foreach ($fieldnames as $field) {
$fielddata = arraySearchByKey($field, $fieldmap, "fieldname", 1);
if (count($fielddata) < 1) {
$firstline .= $field;
} else {
$firstline .= preg_replace('/\\s+/', ' ', strip_tags($fielddata['question']));
}
$firstline .= $s;
$secondline .= $field . $s;
}
$vvoutput = $firstline . "\n";
$vvoutput .= $secondline . "\n";
$query = "SELECT * FROM " . Yii::app()->db->quoteTableName($surveytable);
if (incompleteAnsFilterState() == "incomplete") {
$query .= " WHERE submitdate IS NULL ";
} elseif (incompleteAnsFilterState() == "complete") {
$query .= " WHERE submitdate >= '01/01/1980' ";
}
$result = Yii::app()->db->createCommand($query)->query();
foreach ($result->readAll() as $row) {
foreach ($fieldnames as $field) {
if (is_null($row[$field])) {
$value = '{question_not_shown}';
} else {
$value = trim($row[$field]);
// sunscreen for the value. necessary for the beach.
// careful about the order of these arrays:
// lbrace has to be substituted *first*
$value = str_replace(array("{", "\n", "\r", "\t"), array("{lbrace}", "{newline}", "{cr}", "{tab}"), $value);
}
// one last tweak: excel likes to quote values when it
// exports as tab-delimited (esp if value contains a comma,
// oddly enough). So we're going to encode a leading quote,
// if it occurs, so that we can tell the difference between
// strings that "really are" quoted, and those that excel quotes
// for us.
$value = preg_replace('/^"/', '{quote}', $value);
// yay! that nasty soab won't hurt us now!
if ($field == "submitdate" && !$value) {
$value = "NULL";
}
$sun[] = $value;
}
$beach = implode($s, $sun);
$vvoutput .= $beach;
unset($sun);
$vvoutput .= "\n";
}
echo $vvoutput;
exit;
}
}
<?php
eT("Statistics");
?>
</h3>
</div>
<div class="text-right">
<div class="form-group">
<div style="display:inline-block;position:relative;top:-65px;">
<label for='completionstate' class="control-label"><?php
eT("Include:");
?>
</label>
<?php
echo CHtml::dropDownList('completionstate', incompleteAnsFilterState(), array("all" => gT("All responses", 'unescaped'), "complete" => gT("Complete only", 'unescaped'), "incomplete" => gT("Incomplete only", 'unescaped')), array('class' => 'form-control', 'style' => 'display: inline;width: auto;', 'data-url' => App()->createUrl('/admin/statistics/sa/setIncompleteanswers/')));
?>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-lg-12 content-right">
<input type="hidden" id="showGraphOnPageLoad" />
<div id='statisticsoutput' class='statisticsfilters'>
<?php
echo $output;
?>
dataend.png' alt='<?php
eT("Show last..");
?>
' /></a>
<img src='<?php
echo $sImageURL;
?>
separator.gif' class='separator' alt='' />
<?php
}
$selectshow = '';
$selectinc = '';
$selecthide = '';
if (incompleteAnsFilterState() == "incomplete") {
$selectinc = "selected='selected'";
} elseif (incompleteAnsFilterState() == "complete") {
$selecthide = "selected='selected'";
} else {
$selectshow = "selected='selected'";
}
?>
<?php
echo CHtml::form(array("admin/responses/sa/browse/surveyid/{$surveyid}/"), 'post', array('id' => 'browseresults'));
?>
<img src='<?php
echo $sImageURL;
?>
blank.gif' width='31' height='20' alt='' />
<?php
eT("Records displayed:");
?>
/**
* Constructor
*/
public function run($surveyid = 0, $subaction = null)
{
$surveyid = sanitize_int($surveyid);
//TODO: Convert question types to views
$clang = $this->getController()->lang;
$imageurl = Yii::app()->getConfig("imageurl");
$aData = array('clang' => $clang, 'imageurl' => $imageurl);
$aData['sql'] = '';
/*
* We need this later:
* 1 - Array Dual Scale
* 5 - 5 Point Choice
* A - Array (5 Point Choice)
* B - Array (10 Point Choice)
* C - Array (Yes/No/Uncertain)
* D - Date
* E - Array (Increase, Same, Decrease)
* F - Array (Flexible Labels)
* G - Gender
* H - Array (Flexible Labels) by Column
* I - Language Switch
* K - Multiple Numerical Input
* L - List (Radio)
* M - Multiple choice
* N - Numerical Input
* O - List With Comment
* P - Multiple choice with comments
* Q - Multiple Short Text
* R - Ranking
* S - Short Free Text
* T - Long Free Text
* U - Huge Free Text
* X - Boilerplate Question
* Y - Yes/No
* ! - List (Dropdown)
* : - Array (Flexible Labels) multiple drop down
* ; - Array (Flexible Labels) multiple texts
* | - File Upload
Debugging help:
echo '<script language="javascript" type="text/javascript">alert("HI");</script>';
*/
//split up results to extend statistics -> NOT WORKING YET! DO NOT ENABLE THIS!
$showcombinedresults = 0;
/*
* this variable is used in the function shortencode() which cuts off a question/answer title
* after $maxchars and shows the rest as tooltip
*/
$maxchars = 50;
//we collect all the output within this variable
$statisticsoutput = '';
//output for chosing questions to cross query
$cr_statisticsoutput = '';
// This gets all the 'to be shown questions' from the POST and puts these into an array
$summary = returnGlobal('summary');
$statlang = returnGlobal('statlang');
//if $summary isn't an array we create one
if (isset($summary) && !is_array($summary)) {
$summary = explode("+", $summary);
}
//no survey ID? -> come and get one
if (!isset($surveyid)) {
$surveyid = returnGlobal('sid');
}
//still no survey ID -> error
$aData['surveyid'] = $surveyid;
// Set language for questions and answers to base language of this survey
$language = Survey::model()->findByPk($surveyid)->language;
$aData['language'] = $language;
//Call the javascript file
App()->getClientScript()->registerScriptFile(Yii::app()->getConfig('adminscripts') . 'statistics.js');
App()->getClientScript()->registerScriptFile(Yii::app()->getConfig('adminscripts') . 'json-js/json2.min.js');
$aData['display']['menu_bars']['browse'] = $clang->gT("Quick statistics");
//Select public language file
$row = Survey::model()->find('sid = :sid', array(':sid' => $surveyid));
/*
* check if there is a datestamp available for this survey
* yes -> $datestamp="Y"
* no -> $datestamp="N"
*/
$datestamp = $row->datestamp;
// 1: Get list of questions from survey
/*
* We want to have the following data
* a) "questions" -> all table namens, e.g.
* qid
* sid
* gid
* type
* title
* question
* preg
* help
* other
* mandatory
* lid
* lid1
* question_order
* language
*
* b) "groups" -> group_name + group_order *
*/
//store all the data in $rows
$rows = Question::model()->getQuestionList($surveyid, $language);
//SORT IN NATURAL ORDER!
usort($rows, 'groupOrderThenQuestionOrder');
//put the question information into the filter array
$filters = array();
foreach ($rows as $row) {
//store some column names in $filters array
$filters[] = array($row['qid'], $row['gid'], $row['type'], $row['title'], $row['group_name'], flattenText($row['question']));
}
$aData['filters'] = $filters;
//var_dump($filters);
// SHOW ID FIELD
$grapherror = false;
$error = '';
if (!function_exists("gd_info")) {
$grapherror = true;
$error .= '<br />' . $clang->gT('You do not have the GD Library installed. Showing charts requires the GD library to function properly.');
$error .= '<br />' . $clang->gT('visit http://us2.php.net/manual/en/ref.image.php for more information') . '<br />';
} elseif (!function_exists("imageftbbox")) {
$grapherror = true;
$error .= '<br />' . $clang->gT('You do not have the Freetype Library installed. Showing charts requires the Freetype library to function properly.');
$error .= '<br />' . $clang->gT('visit http://us2.php.net/manual/en/ref.image.php for more information') . '<br />';
}
if ($grapherror) {
unset($_POST['usegraph']);
}
//pre-selection of filter forms
if (incompleteAnsFilterState() == "complete") {
$selecthide = "selected='selected'";
$selectshow = "";
$selectinc = "";
} elseif (incompleteAnsFilterState() == "incomplete") {
$selecthide = "";
$selectshow = "";
$selectinc = "selected='selected'";
} else {
$selecthide = "";
$selectshow = "selected='selected'";
$selectinc = "";
}
$aData['selecthide'] = $selecthide;
$aData['selectshow'] = $selectshow;
$aData['selectinc'] = $selectinc;
$aData['error'] = $error;
$survlangs = Survey::model()->findByPk($surveyid)->additionalLanguages;
$survlangs[] = Survey::model()->findByPk($surveyid)->language;
$aData['survlangs'] = $survlangs;
$aData['datestamp'] = $datestamp;
//if the survey contains timestamps you can filter by timestamp, too
//Output selector
//second row below options -> filter settings headline
$filterchoice_state = returnGlobal('filterchoice_state');
$aData['filterchoice_state'] = $filterchoice_state;
/*
* let's go through the filter array which contains
* ['qid'],
['gid'],
['type'],
['title'],
['group_name'],
['question'],
['lid'],
['lid1']);
*/
$currentgroup = '';
$counter = 0;
foreach ($filters as $key1 => $flt) {
//is there a previous question type set?
/*
* remember: $flt is structured like this
* ['qid'],
['gid'],
['type'],
['title'],
['group_name'],
['question'],
['lid'],
['lid1']);
*/
//SGQ identifier
//full question title
/*
* Check question type: This question types will be used (all others are separated in the if clause)
* 5 - 5 Point Choice
G - Gender
I - Language Switch
L - List (Radio)
M - Multiple choice
N - Numerical Input
| - File Upload
O - List With Comment
P - Multiple choice with comments
Y - Yes/No
! - List (Dropdown) )
*/
/////////////////////////////////////////////////////////////////////////////////////////////////
//This section presents the filter list, in various different ways depending on the question type
/////////////////////////////////////////////////////////////////////////////////////////////////
//let's switch through the question type for each question
switch ($flt[2]) {
case "K":
// Multiple Numerical
//get answers
$result = Question::model()->getQuestionsForStatistics('title as code, question as answer', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1]['key1'] = $result;
break;
case "Q":
// Multiple Short Text
//get subqestions
$result = Question::model()->getQuestionsForStatistics('title as code, question as answer', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
break;
//----------------------- ARRAYS --------------------------
//----------------------- ARRAYS --------------------------
case "A":
// ARRAY OF 5 POINT CHOICE QUESTIONS
//get answers
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
break;
//just like above only a different loop
//just like above only a different loop
case "B":
// ARRAY OF 10 POINT CHOICE QUESTIONS
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
break;
case "C":
// ARRAY OF YES\No\$clang->gT("Uncertain") QUESTIONS
//get answers
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
break;
//similiar to the above one
//similiar to the above one
case "E":
// ARRAY OF Increase/Same/Decrease QUESTIONS
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
break;
case ";":
//ARRAY (Multi Flex) (Text)
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}' AND scale_id = 0", 'question_order');
$aData['result'][$key1] = $result;
foreach ($result as $key => $row) {
$fresult = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}' AND scale_id = 1", 'question_order');
$aData['fresults'][$key1][$key] = $fresult;
}
break;
case ":":
//ARRAY (Multi Flex) (Numbers)
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}' AND scale_id = 0", 'question_order');
$aData['result'][$key1] = $result;
foreach ($result as $row) {
$fresult = Question::model()->getQuestionsForStatistics('*', "parent_qid={$flt['0']} AND language = '{$language}' AND scale_id = 1", 'question_order, title');
$aData['fresults'][$key1] = $fresult;
}
break;
/*
* For question type "F" and "H" you can use labels.
* The only difference is that the labels are applied to column heading
* or rows respectively
*/
/*
* For question type "F" and "H" you can use labels.
* The only difference is that the labels are applied to column heading
* or rows respectively
*/
case "F":
// FlEXIBLE ARRAY
// FlEXIBLE ARRAY
case "H":
// ARRAY (By Column)
//Get answers. We always use the answer code because the label might be too long elsewise
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
//check all the answers
foreach ($result as $row) {
$fresult = Answer::model()->getQuestionsForStatistics('*', "qid={$flt['0']} AND language = '{$language}'", 'sortorder, code');
$aData['fresults'][$key1] = $fresult;
}
//$statisticsoutput .= "\t\t\t\t<td>\n";
$counter = 0;
break;
case "R":
//RANKING
//get some answers
$result = Answer::model()->getQuestionsForStatistics('code, answer', "qid={$flt['0']} AND language = '{$language}'", 'sortorder, answer');
$aData['result'][$key1] = $result;
break;
case "1":
// MULTI SCALE
//get answers
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid={$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
//loop through answers
foreach ($result as $key => $row) {
//check if there is a dualscale_headerA/B
$dshresult = QuestionAttribute::model()->getQuestionsForStatistics('value', "qid={$flt['0']} AND attribute = 'dualscale_headerA'", '');
$aData['dshresults'][$key1][$key] = $dshresult;
$fresult = Answer::model()->getQuestionsForStatistics('*', "qid={$flt['0']} AND language = '{$language}' AND scale_id = 0", 'sortorder, code');
$aData['fresults'][$key1][$key] = $fresult;
$dshresult2 = QuestionAttribute::model()->getQuestionsForStatistics('value', "qid={$flt['0']} AND attribute = 'dualscale_headerB'", '');
$aData['dshresults2'][$key1][$key] = $dshresult2;
}
break;
case "P":
//P - Multiple choice with comments
//P - Multiple choice with comments
case "M":
//M - Multiple choice
//get answers
$result = Question::model()->getQuestionsForStatistics('title, question', "parent_qid = {$flt['0']} AND language = '{$language}'", 'question_order');
$aData['result'][$key1] = $result;
break;
/*
* This question types use the default settings:
* L - List (Radio)
O - List With Comment
P - Multiple choice with comments
! - List (Dropdown)
*/
/*
* This question types use the default settings:
* L - List (Radio)
O - List With Comment
P - Multiple choice with comments
! - List (Dropdown)
*/
default:
//get answers
$result = Answer::model()->getQuestionsForStatistics('code, answer', "qid={$flt['0']} AND language = '{$language}'", 'sortorder, answer');
$aData['result'][$key1] = $result;
break;
}
//end switch -> check question types and create filter forms
$currentgroup = $flt[1];
$counter++;
//temporary save the type of the previous question
//used to adjust linebreaks
$previousquestiontype = $flt[2];
}
// ----------------------------------- END FILTER FORM ---------------------------------------
Yii::app()->loadHelper('admin/statistics');
$showtextinline = isset($_POST['showtextinline']) ? 1 : 0;
$aData['showtextinline'] = $showtextinline;
//Show Summary results
if (isset($summary) && $summary) {
$usegraph = isset($_POST['usegraph']) ? 1 : 0;
$aData['usegraph'] = $usegraph;
$outputType = $_POST['outputtype'];
$helper = new statistics_helper();
switch ($outputType) {
case 'html':
$statisticsoutput .= $helper->generate_statistics($surveyid, $summary, $summary, $usegraph, $outputType, 'DD', $statlang);
break;
case 'pdf':
$helper->generate_statistics($surveyid, $summary, $summary, $usegraph, $outputType, 'I', $statlang);
exit;
break;
case 'xls':
$helper->generate_statistics($surveyid, $summary, $summary, $usegraph, $outputType, 'DD', $statlang);
exit;
break;
default:
break;
}
}
//end if -> show summary results
$aData['sStatisticsLanguage'] = $statlang;
$aData['output'] = $statisticsoutput;
$aData['summary'] = $summary;
$this->_renderWrappedTemplate('export', 'statistics_view', $aData);
}
/**
* Creates a query string with all fields for the export
* @param
* @return CDbCommand
*/
function SPSSGetQuery($iSurveyID, $limit = null, $offset = null)
{
$bDataAnonymized = Survey::model()->findByPk($iSurveyID)->anonymized == 'Y';
$tokensexist = tableExists('tokens_' . $iSurveyID);
#See if tokens are being used
$query = App()->db->createCommand();
$query->from('{{survey_' . $iSurveyID . '}} s');
$columns = array('s.*');
if (isset($tokensexist) && $tokensexist == true && !$bDataAnonymized && Permission::model()->hasSurveyPermission($iSurveyID, 'tokens', 'read')) {
$tokenattributes = array_keys(getTokenFieldsAndNames($iSurveyID, false));
foreach ($tokenattributes as $attributefield) {
//Drop the token field, since it is in the survey too
if ($attributefield != 'token') {
$columns[] = 't.' . $attributefield;
}
}
$query->leftJoin('{{tokens_' . $iSurveyID . '}} t', App()->db->quoteColumnName('s.token') . ' = ' . App()->db->quoteColumnName('t.token'));
//LEFT JOIN {{tokens_$iSurveyID}} t ON ";
}
$query->select($columns);
switch (incompleteAnsFilterState()) {
case 'incomplete':
//Inclomplete answers only
$query->where('s.submitdate IS NULL');
break;
case 'complete':
//Inclomplete answers only
$query->where('s.submitdate IS NOT NULL');
break;
}
if (!empty($limit) & !is_null($offset)) {
$query->limit((int) $limit, (int) $offset);
}
return $query;
}
public function time($iSurveyID)
{
$aData = $this->_getData(array('iSurveyId' => $iSurveyID));
extract($aData);
$aViewUrls = array();
if ($aData['surveyinfo']['savetimings'] != "Y") {
die;
}
if (Yii::app()->request->getPost('deleteanswer') && Yii::app()->request->getPost('deleteanswer') != '' && Yii::app()->request->getPost('deleteanswer') != 'marked' && Permission::model()->hasSurveyPermission($iSurveyID, 'responses', 'delete')) {
$iResponseID = (int) Yii::app()->request->getPost('deleteanswer');
SurveyDynamic::model($iSurveyID)->deleteByPk($iResponseID);
SurveyTimingDynamic::model($iSurveyID)->deleteByPk($iResponseID);
}
if (Yii::app()->request->getPost('markedresponses') && count(Yii::app()->request->getPost('markedresponses')) > 0) {
if (Yii::app()->request->getPost('deleteanswer') && Yii::app()->request->getPost('deleteanswer') === 'marked' && Permission::model()->hasSurveyPermission($iSurveyID, 'responses', 'delete')) {
foreach (Yii::app()->request->getPost('markedresponses') as $iResponseID) {
$iResponseID = (int) $iResponseID;
SurveyDynamic::model($iSurveyID)->deleteByPk($iResponseID);
SurveyTimingDynamic::model($iSurveyID)->deleteByPk($iResponseID);
}
}
}
$fields = createTimingsFieldMap($iSurveyID, 'full', true, false, $aData['language']);
foreach ($fields as $fielddetails) {
// headers for answer id and time data
if ($fielddetails['type'] == 'id') {
$fnames[] = array($fielddetails['fieldname'], $fielddetails['question']);
}
if ($fielddetails['type'] == 'interview_time') {
$fnames[] = array($fielddetails['fieldname'], gT('Total time'));
}
if ($fielddetails['type'] == 'page_time') {
$fnames[] = array($fielddetails['fieldname'], gT('Group') . ": " . $fielddetails['group_name']);
}
if ($fielddetails['type'] == 'answer_time') {
$fnames[] = array($fielddetails['fieldname'], gT('Question') . ": " . $fielddetails['title']);
}
}
$fncount = count($fnames);
//NOW LETS CREATE A TABLE WITH THOSE HEADINGS
foreach ($fnames as $fn) {
if (!isset($currentgroup)) {
$currentgroup = $fn[1];
$gbc = "oddrow";
}
if ($currentgroup != $fn[1]) {
$currentgroup = $fn[1];
if ($gbc == "oddrow") {
$gbc = "evenrow";
} else {
$gbc = "oddrow";
}
}
}
$aData['fnames'] = $fnames;
$start = Yii::app()->request->getParam('start', 0);
$limit = Yii::app()->request->getParam('limit', 50);
if (!$limit) {
$limit = 50;
}
//LETS COUNT THE DATA
$oCriteria = new CdbCriteria();
$oCriteria->select = 'tid';
$oCriteria->join = "INNER JOIN {{survey_{$iSurveyID}}} s ON t.id=s.id";
$oCriteria->condition = 'submitdate IS NOT NULL';
$dtcount = SurveyTimingDynamic::model($iSurveyID)->count($oCriteria);
// or die("Couldn't get response data");
if ($limit > $dtcount) {
$limit = $dtcount;
}
//NOW LETS SHOW THE DATA
$oCriteria = new CdbCriteria();
$oCriteria->join = "INNER JOIN {{survey_{$iSurveyID}}} s ON t.id=s.id";
$oCriteria->condition = 'submitdate IS NOT NULL';
$oCriteria->order = "s.id " . (Yii::app()->request->getParam('order') == 'desc' ? 'desc' : 'asc');
$oCriteria->offset = $start;
$oCriteria->limit = $limit;
$dtresult = SurveyTimingDynamic::model($iSurveyID)->findAllAsArray($oCriteria);
$dtcount2 = count($dtresult);
$cells = $fncount + 1;
//CONTROL MENUBAR
$last = $start - $limit;
$next = $start + $limit;
$end = $dtcount - $limit;
if ($end < 0) {
$end = 0;
}
if ($last < 0) {
$last = 0;
}
if ($next >= $dtcount) {
$next = $dtcount - $limit;
}
if ($end < 0) {
$end = 0;
}
$aData['sCompletionStateValue'] = incompleteAnsFilterState();
$aData['start'] = $start;
$aData['limit'] = $limit;
$aData['last'] = $last;
$aData['next'] = $next;
$aData['end'] = $end;
$aViewUrls[] = 'browsetimeheader_view';
$aData['fncount'] = $fncount;
$bgcc = 'oddrow';
foreach ($dtresult as $dtrow) {
if ($bgcc == "evenrow") {
$bgcc = "oddrow";
} else {
$bgcc = "evenrow";
}
$browsedatafield = array();
for ($i = 0; $i < $fncount; $i++) {
$browsedatafield[$i] = $dtrow[$fnames[$i][0]];
// seconds -> minutes & seconds
if (strtolower(substr($fnames[$i][0], -4)) == "time") {
$minutes = (int) ($browsedatafield[$i] / 60);
$seconds = $browsedatafield[$i] % 60;
$browsedatafield[$i] = '';
if ($minutes > 0) {
$browsedatafield[$i] .= "{$minutes} min ";
}
$browsedatafield[$i] .= "{$seconds} s";
}
}
$aData['browsedatafield'] = $browsedatafield;
$aData['bgcc'] = $bgcc;
$aData['dtrow'] = $dtrow;
$aViewUrls['browsetimerow_view'][] = $aData;
}
//interview Time statistics
$aData['statistics'] = SurveyTimingDynamic::model($iSurveyId)->statistics();
$aData['num_total_answers'] = SurveyDynamic::model($iSurveyID)->count();
$aData['num_completed_answers'] = SurveyDynamic::model($iSurveyID)->count('submitdate IS NOT NULL');
$aViewUrls[] = 'browsetimefooter_view';
$this->_renderWrappedTemplate('', $aViewUrls, $aData);
}
/**
* Returns a simple list of values in a particular column, that meet the requirements of the SQL
*/
function _listcolumn($surveyid, $column, $sortby = "", $sortmethod = "", $sorttype = "")
{
$search['condition'] = Yii::app()->db->quoteColumnName($column) . " != ''";
$sDBDriverName = Yii::app()->db->getDriverName();
if ($sDBDriverName == 'sqlsrv' || $sDBDriverName == 'mssql' || $sDBDriverName == 'dblib') {
$search['condition'] = "CAST(" . Yii::app()->db->quoteColumnName($column) . " as varchar) != ''";
}
//filter incomplete answers if set
if (incompleteAnsFilterState() == "incomplete") {
$search['condition'] .= " AND submitdate is null";
} elseif (incompleteAnsFilterState() == "complete") {
$search['condition'] .= " AND submitdate is not null";
}
//Look for any selects/filters set in the original statistics query, and apply them to the column listing
if (isset(Yii::app()->session['statistics_selects_' . $surveyid]) && is_array(Yii::app()->session['statistics_selects_' . $surveyid])) {
foreach (Yii::app()->session['statistics_selects_' . $surveyid] as $sql) {
$search['condition'] .= " AND {$sql}";
}
}
if ($sortby != '') {
if ($sDBDriverName == 'sqlsrv' || $sDBDriverName == 'mssql' || $sDBDriverName == 'dblib') {
$sortby = "CAST(" . Yii::app()->db->quoteColumnName($sortby) . " as varchar)";
} else {
$sortby = Yii::app()->db->quoteColumnName($sortby);
}
if ($sorttype == 'N') {
$sortby = "({$sortby} * 1)";
}
//Converts text sorting into numerical sorting
$search['order'] = $sortby . ' ' . $sortmethod;
}
$results = SurveyDynamic::model($surveyid)->findAll($search);
$output = array();
foreach ($results as $row) {
$output[] = array("id" => $row['id'], "value" => $row[$column]);
}
return $output;
}
<div class="panel-heading">
<h4 class="panel-title"><?php
eT("Data selection");
?>
</h4>
</div>
<div class="panel-body">
<div class='form-group'>
<label for='completionstate' class="col-sm-4 control-label"><?php
eT("Include:");
?>
</label>
<?php
$this->widget('yiiwheels.widgets.buttongroup.WhButtonGroup', array('name' => 'completionstate', 'value' => incompleteAnsFilterState(), 'selectOptions' => array("all" => gT("All responses", 'unescaped'), "complete" => gT("Complete only", 'unescaped'), "incomplete" => gT("Incomplete only", 'unescaped'))));
?>
</div>
<div class='form-group'>
<label class="col-sm-4 control-label" for='viewsummaryall'><?php
eT("View summary of all available fields:");
?>
</label>
<div class='col-sm-1'>
<?php
$this->widget('yiiwheels.widgets.switch.WhSwitch', array('name' => 'viewsummaryall', 'id' => 'viewsummaryall', 'value' => isset($_POST['viewsummaryall']), 'onLabel' => gT('On'), 'offLabel' => gT('Off')));
?>
</div>
</div>
/**
* Return previous id if previous response exist in database
*
* @param integer $srid : actual save survey id
* @param boolean $usefilterstate
*
* @return integer
*/
public function previous($srid, $usefilterstate = false)
{
$previous = false;
if ($usefilterstate && incompleteAnsFilterState() == 'incomplete') {
$wherefilterstate = 'submitdate IS NULL';
} elseif ($usefilterstate && incompleteAnsFilterState() == 'complete') {
$wherefilterstate = 'submitdate IS NOT NULL';
} else {
$wherefilterstate = '1=1';
}
if (Yii::app()->db->schema->getTable($this->tableName())) {
$data = Yii::app()->db->createCommand()->select("id")->from($this->tableName())->where(array('and', $wherefilterstate, 'id < :id'), array(':id' => $srid))->order('id DESC')->queryRow();
if ($data) {
$previous = $data['id'];
}
}
return $previous;
}
/**
* Creates a query string with all fields for the export
* @param
* @return string
*/
function SPSSGetQuery($iSurveyID)
{
$bDataAnonymized = Survey::model()->findByPk($iSurveyID)->anonymized == 'Y';
$tokensexist = tableExists('tokens_' . $iSurveyID);
#See if tokens are being used
if (isset($tokensexist) && $tokensexist == true && !$bDataAnonymized && hasSurveyPermission($iSurveyID, 'tokens', 'read')) {
$query = "SELECT ";
$tokenattributes = array_keys(getTokenFieldsAndNames($iSurveyID, false));
foreach ($tokenattributes as $attributefield) {
//Drop the token field, since it is in the survey too
if ($attributefield != 'token') {
$query .= Yii::app()->db->quoteColumnName('t.' . $attributefield) . ',';
}
}
$query .= "s.*\n FROM {{survey_{$iSurveyID}}} s\n LEFT JOIN {{tokens_{$iSurveyID}}} t ON s.token = t.token";
} else {
$query = "SELECT s.*\n FROM {{survey_{$iSurveyID}}} s";
}
switch (incompleteAnsFilterState()) {
case 'incomplete':
//Inclomplete answers only
$query .= ' WHERE s.submitdate is null ';
break;
case 'complete':
//Inclomplete answers only
$query .= ' WHERE s.submitdate is not null ';
break;
}
return $query;
}
public function vvexport()
{
$iSurveyId = sanitize_int(Yii::app()->request->getParam('surveyid'));
$subaction = Yii::app()->request->getParam('subaction');
//Exports all responses to a survey in special "Verified Voting" format.
if (!Permission::model()->hasSurveyPermission($iSurveyId, 'responses', 'export')) {
Yii::app()->session['flashmessage'] = gT("You do not have sufficient rights to access this page.");
$this->getController()->redirect($this->getController()->createUrl("/admin/survey/sa/view/surveyid/{$iSurveyId}"));
}
if ($subaction != "export") {
$aData['selectincansstate'] = incompleteAnsFilterState();
$aData['surveyid'] = $iSurveyId;
$aData['display']['menu_bars']['browse'] = gT("Export VV file");
$fieldmap = createFieldMap($iSurveyId, 'full', false, false, getBaseLanguageFromSurveyID($iSurveyId));
Survey::model()->findByPk($iSurveyId)->language;
$surveytable = "{{survey_{$iSurveyId}}}";
// Control if fieldcode are unique
$fieldnames = Yii::app()->db->schema->getTable($surveytable)->getColumnNames();
foreach ($fieldnames as $field) {
$fielddata = arraySearchByKey($field, $fieldmap, "fieldname", 1);
$fieldcode[] = viewHelper::getFieldCode($fielddata, array("LEMcompat" => true));
}
$aData['uniquefieldcode'] = count(array_unique($fieldcode)) == count($fieldcode);
// Did we need more control ?
$aData['vvversionseleted'] = $aData['uniquefieldcode'] ? 2 : 1;
$this->_renderWrappedTemplate('export', 'vv_view', $aData);
} elseif (isset($iSurveyId) && $iSurveyId) {
//Export is happening
$extension = sanitize_paranoid_string(returnGlobal('extension'));
$vvVersion = (int) Yii::app()->request->getPost('vvversion');
$vvVersion = in_array($vvVersion, array(1, 2)) ? $vvVersion : 2;
// Only 2 version actually, default to 2
$fn = "vvexport_{$iSurveyId}." . $extension;
$this->_addHeaders($fn, "text/comma-separated-values", 0, "cache");
$s = "\t";
$fieldmap = createFieldMap($iSurveyId, 'full', false, false, getBaseLanguageFromSurveyID($iSurveyId));
$surveytable = "{{survey_{$iSurveyId}}}";
Survey::model()->findByPk($iSurveyId)->language;
$fieldnames = Yii::app()->db->schema->getTable($surveytable)->getColumnNames();
//Create the human friendly first line
$firstline = "";
$secondline = "";
foreach ($fieldnames as $field) {
$fielddata = arraySearchByKey($field, $fieldmap, "fieldname", 1);
if (count($fielddata) < 1) {
$firstline .= $field;
} else {
$firstline .= preg_replace('/\\s+/', ' ', strip_tags($fielddata['question']));
}
$firstline .= $s;
if ($vvVersion == 2) {
$fieldcode = viewHelper::getFieldCode($fielddata, array("LEMcompat" => true));
$fieldcode = $fieldcode ? $fieldcode : $field;
// $fieldcode is empty for token if there are no token table
} else {
$fieldcode = $field;
}
$secondline .= $fieldcode . $s;
}
$vvoutput = $firstline . "\n";
$vvoutput .= $secondline . "\n";
$query = "SELECT * FROM " . Yii::app()->db->quoteTableName($surveytable);
if (incompleteAnsFilterState() == "incomplete") {
$query .= " WHERE submitdate IS NULL ";
} elseif (incompleteAnsFilterState() == "complete") {
$query .= " WHERE submitdate >= '01/01/1980' ";
}
$result = Yii::app()->db->createCommand($query)->query();
echo $vvoutput;
foreach ($result as $row) {
foreach ($fieldnames as $field) {
if (is_null($row[$field])) {
$value = '{question_not_shown}';
} else {
$value = trim($row[$field]);
// sunscreen for the value. necessary for the beach.
// careful about the order of these arrays:
// lbrace has to be substituted *first*
$value = str_replace(array("{", "\n", "\r", "\t"), array("{lbrace}", "{newline}", "{cr}", "{tab}"), $value);
}
// one last tweak: excel likes to quote values when it
// exports as tab-delimited (esp if value contains a comma,
// oddly enough). So we're going to encode a leading quote,
// if it occurs, so that we can tell the difference between
// strings that "really are" quoted, and those that excel quotes
// for us.
$value = preg_replace('/^"/', '{quote}', $value);
// yay! that nasty soab won't hurt us now!
if ($field == "submitdate" && !$value) {
$value = "NULL";
}
$sun[] = $value;
}
/* it is important here to stream output data, line by line
* in order to avoid huge memory consumption when exporting large
* quantities of answers */
echo implode($s, $sun) . "\n";
unset($sun);
}
exit;
}
}
/**
* Generates statistics
*
* @param int $surveyid The survey id
* @param mixed $allfields
* @param mixed $q2show
* @param mixed $usegraph
* @param string $outputType Optional - Can be xls, html or pdf - Defaults to pdf
* @param string $pdfOutput Sets the target for the PDF output: DD=File download , F=Save file to local disk
* @param string $statlangcode Lamguage for statistics
* @param mixed $browse Show browse buttons
* @return buffer
*/
function generate_statistics($surveyid, $allfields, $q2show = 'all', $usegraph = 0, $outputType = 'pdf', $pdfOutput = 'I', $statlangcode = null, $browse = true)
{
global $pdfdefaultfont, $pdffontsize;
$astatdata = array();
//astatdata generates data for the output page's javascript so it can rebuild graphs on the fly
//load surveytranslator helper
Yii::import('application.helpers.surveytranslator_helper', true);
$statisticsoutput = "";
//This string carries all the actual HTML code to print.
$imagedir = Yii::app()->getConfig("imagedir");
$tempdir = Yii::app()->getConfig("tempdir");
$tempurl = Yii::app()->getConfig("tempurl");
$clang = Yii::app()->lang;
$pdf = array();
//Make sure $pdf exists - it will be replaced with an object if a $pdf is actually being created
// Used for getting coordinates for google maps
$agmapdata = array();
//pick the best font file if font setting is 'auto'
if (is_null($statlangcode)) {
$statlangcode = getBaseLanguageFromSurveyID($surveyid);
} else {
$statlang = new Limesurvey_lang($statlangcode);
}
/*
* this variable is used in the function shortencode() which cuts off a question/answer title
* after $maxchars and shows the rest as tooltip (in html mode)
*/
$maxchars = 13;
//we collect all the html-output within this variable
$statisticsoutput = '';
/**
* $outputType: html || pdf ||
*/
/**
* get/set Survey Details
*/
//no survey ID? -> come and get one
if (!isset($surveyid)) {
$surveyid = returnGlobal('sid');
}
//Get an array of codes of all available languages in this survey
$surveylanguagecodes = Survey::model()->findByPk($surveyid)->additionalLanguages;
$surveylanguagecodes[] = Survey::model()->findByPk($surveyid)->language;
$fieldmap = createFieldMap($surveyid, "full", false, false, $statlang->getlangcode());
// Set language for questions and answers to base language of this survey
$language = $statlangcode;
if ($q2show == 'all') {
$summarySql = " SELECT gid, parent_qid, qid, type " . " FROM {{questions}} WHERE parent_qid=0" . " AND sid={$surveyid} ";
$summaryRs = Yii::app()->db->createCommand($summarySql)->query()->readAll();
foreach ($summaryRs as $field) {
$myField = $surveyid . "X" . $field['gid'] . "X" . $field['qid'];
// Multiple choice get special treatment
if ($field['type'] == "M") {
$myField = "M{$myField}";
}
if ($field['type'] == "P") {
$myField = "P{$myField}";
}
//numerical input will get special treatment (arihtmetic mean, standard derivation, ...)
if ($field['type'] == "N") {
$myField = "N{$myField}";
}
if ($field['type'] == "|") {
$myField = "|{$myField}";
}
if ($field['type'] == "Q") {
$myField = "Q{$myField}";
}
// textfields get special treatment
if ($field['type'] == "S" || $field['type'] == "T" || $field['type'] == "U") {
$myField = "T{$myField}";
}
//statistics for Date questions are not implemented yet.
if ($field['type'] == "D") {
$myField = "D{$myField}";
}
if ($field['type'] == "F" || $field['type'] == "H") {
//Get answers. We always use the answer code because the label might be too long elsewise
$query = "SELECT code, answer FROM {{answers}} WHERE qid='" . $field['qid'] . "' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, answer";
$result = Yii::app()->db->createCommand($query)->query();
$counter2 = 0;
//check all the answers
foreach ($result->readAll() as $row) {
$row = array_values($row);
$myField = "{$myField}{$row[0]}";
}
//$myField = "{$surveyid}X{$flt[1]}X{$flt[0]}{$row[0]}[]";
}
if ($q2show == 'all') {
$summary[] = $myField;
}
//$allfields[]=$myField;
}
} else {
// This gets all the 'to be shown questions' from the POST and puts these into an array
if (!is_array($q2show)) {
$summary = returnGlobal('summary');
} else {
$summary = $q2show;
}
//print_r($_POST);
//if $summary isn't an array we create one
if (isset($summary) && !is_array($summary)) {
$summary = explode("+", $summary);
}
}
/**
* pdf Config
*/
if ($outputType == 'pdf') {
//require_once('classes/tcpdf/config/lang/eng.php');
global $l;
$l['w_page'] = $statlang->gT("Page", 'unescaped');
//require_once('classes/tcpdf/mypdf.php');
Yii::import('application.libraries.admin.pdf', true);
// create new PDF document
$pdf = new Pdf();
$pdf->SetFont($pdfdefaultfont, '', $pdffontsize);
$surveyInfo = getSurveyInfo($surveyid, $language);
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('LimeSurvey');
$pdf->SetTitle('Statistic survey ' . $surveyid);
$pdf->SetSubject($surveyInfo['surveyls_title']);
$pdf->SetKeywords('LimeSurvey, Statistics, Survey ' . $surveyid . '');
$pdf->SetDisplayMode('fullpage', 'two');
// set header and footer fonts
$pdf->setHeaderFont(array($pdfdefaultfont, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(array($pdfdefaultfont, '', PDF_FONT_SIZE_DATA));
// set default header data
$pdf->SetHeaderData("statistics.png", 10, $statlang->gT("Quick statistics", 'unescaped'), $statlang->gT("Survey") . " " . $surveyid . " '" . flattenText($surveyInfo['surveyls_title'], false, true, 'UTF-8') . "'");
// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
//set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
//set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
//set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
//set some language-dependent strings
$pdf->setLanguageArray($l);
}
if ($outputType == 'xls') {
/**
* Initiate the Spreadsheet_Excel_Writer
*/
Yii::import('application.libraries.admin.pear.Spreadsheet.Excel.Xlswriter', true);
if ($pdfOutput == 'F') {
$sFileName = $tempdir . '/statistic-survey' . $surveyid . '.xls';
$workbook = new Xlswriter($sFileName);
} else {
$workbook = new Xlswriter();
}
$workbook->setVersion(8);
// Inform the module that our data will arrive as UTF-8.
// Set the temporary directory to avoid PHP error messages due to open_basedir restrictions and calls to tempnam("", ...)
$workbook->setTempDir($tempdir);
// Inform the module that our data will arrive as UTF-8.
// Set the temporary directory to avoid PHP error messages due to open_basedir restrictions and calls to tempnam("", ...)
if (!empty($tempdir)) {
$workbook->setTempDir($tempdir);
}
if ($pdfOutput != 'F') {
$workbook->send('statistic-survey' . $surveyid . '.xls');
}
// Creating the first worksheet
$sheet =& $workbook->addWorksheet(utf8_decode('results-survey' . $surveyid));
$sheet->setInputEncoding('utf-8');
$sheet->setColumn(0, 20, 20);
$separator = "~|";
/**XXX*/
}
/**
* Start generating
*/
$selects = buildSelects($allfields, $surveyid, $language);
//count number of answers
$query = "SELECT count(*) FROM {{survey_{$surveyid}}}";
//if incompleted answers should be filtert submitdate has to be not null
if (incompleteAnsFilterState() == "inc") {
$query .= " WHERE submitdate is null";
} elseif (incompleteAnsFilterState() == "filter") {
$query .= " WHERE submitdate is not null";
}
$result = Yii::app()->db->createCommand($query)->query();
//$total = total number of answers
$row = $result->read();
$total = reset($row);
//are there any filters that have to be taken care of?
if (isset($selects) && $selects) {
//filter incomplete answers?
if (incompleteAnsFilterState() == "filter" || incompleteAnsFilterState() == "inc") {
$query .= " AND ";
} else {
$query .= " WHERE ";
}
//add filter criteria to SQL
$query .= implode(" AND ", $selects);
}
//get me some data Scotty
$result = Yii::app()->db->createCommand($query)->query();
//put all results into $results
$row = $result->read();
$results = reset($row);
if ($total) {
$percent = sprintf("%01.2f", $results / $total * 100);
}
switch ($outputType) {
case "xls":
$xlsRow = 0;
$sheet->write($xlsRow, 0, $statlang->gT("Number of records in this query:", 'unescaped'));
$sheet->write($xlsRow, 1, $results);
++$xlsRow;
$sheet->write($xlsRow, 0, $statlang->gT("Total records in survey:", 'unescaped'));
$sheet->write($xlsRow, 1, $total);
if ($total) {
++$xlsRow;
$sheet->write($xlsRow, 0, $statlang->gT("Percentage of total:", 'unescaped'));
$sheet->write($xlsRow, 1, $percent . "%");
}
break;
case 'pdf':
// add summary to pdf
$array = array();
//$array[] = array($statlang->gT("Results"),"");
$array[] = array($statlang->gT("Number of records in this query:", 'unescaped'), $results);
$array[] = array($statlang->gT("Total records in survey:", 'unescaped'), $total);
if ($total) {
$array[] = array($statlang->gT("Percentage of total:", 'unescaped'), $percent . "%");
}
$pdf->addPage('P', 'A4');
$pdf->Bookmark($pdf->delete_html($statlang->gT("Results", 'unescaped')), 0, 0);
$pdf->titleintopdf($statlang->gT("Results", 'unescaped'), $statlang->gT("Survey", 'unescaped') . " " . $surveyid);
$pdf->tableintopdf($array);
$pdf->addPage('P', 'A4');
break;
case 'html':
$statisticsoutput .= "<br />\n<table class='statisticssummary' >\n" . "\t<thead><tr><th colspan='2'>" . $statlang->gT("Results") . "</th></tr></thead>\n" . "\t<tr><th >" . $statlang->gT("Number of records in this query:") . '</th>' . "<td>{$results}</td></tr>\n" . "\t<tr><th>" . $statlang->gT("Total records in survey:") . '</th>' . "<td>{$total}</td></tr>\n";
//only calculate percentage if $total is set
if ($total) {
$percent = sprintf("%01.2f", $results / $total * 100);
$statisticsoutput .= "\t<tr><th align='right'>" . $statlang->gT("Percentage of total:") . '</th>' . "<td>{$percent}%</td></tr>\n";
}
$statisticsoutput .= "</table>\n";
break;
default:
break;
}
//put everything from $selects array into a string connected by AND
//This string ($sql) can then be passed on to other functions so you can
//browse these results
if (isset($selects) && $selects) {
$sql = implode(" AND ", $selects);
} elseif (!empty($newsql)) {
$sql = $newsql;
}
if (!isset($sql) || !$sql) {
$sql = "NULL";
}
//only continue if we have something to output
if ($results > 0) {
if ($outputType == 'html' && $browse === true) {
//add a buttons to browse results
$statisticsoutput .= "<form action='" . Yii::app()->getController()->createUrl("admin/responses/index/surveyid/{$surveyid}") . "' method='post' target='_blank'>\n" . "\t\t<p>" . "\t\t\t<input type='submit' value='" . $statlang->gT("Browse") . "' />\n" . "\t\t\t<input type='hidden' name='sid' value='{$surveyid}' />\n" . "\t\t\t<input type='hidden' name='sql' value=\"{$sql}\" />\n" . "\t\t\t<input type='hidden' name='subaction' value='all' />\n" . "\t\t</p>" . "\t\t</form>\n";
}
}
//end if (results > 0)
/* Show Summary results
* The $summary array contains each fieldname that we want to display statistics for
*
* */
if (isset($summary) && $summary) {
//let's run through the survey
$runthrough = $summary;
//START Chop up fieldname and find matching questions
//loop through all selected questions
foreach ($runthrough as $rt) {
//Step 1: Get information about this response field (SGQA) for the summary
$outputs = buildOutputList($rt, $language, $surveyid, $outputType, $sql, $statlang);
$statisticsoutput .= $outputs['statisticsoutput'];
//2. Collect and Display results #######################################################################
if (isset($outputs['alist']) && $outputs['alist']) {
$display = displayResults($outputs, $results, $rt, $outputType, $surveyid, $sql, $usegraph, $browse, $pdf, $statlang);
$statisticsoutput .= $display['statisticsoutput'];
$astatdata = array_merge($astatdata, $display['astatdata']);
}
//end if -> collect and display results
//Delete Build Outputs data
unset($outputs);
unset($display);
}
// end foreach -> loop through all questions
//output
if ($outputType == 'html') {
$statisticsoutput .= "<br /> \n";
}
}
//end if -> show summary results
switch ($outputType) {
case 'xls':
$workbook->close();
if ($pdfOutput == 'F') {
return $sFileName;
} else {
return;
}
break;
case 'pdf':
$pdf->lastPage();
if ($pdfOutput == 'F') {
// This is only used by lsrc to send an E-Mail attachment, so it gives back the filename to send and delete afterwards
$pdf->Output($tempdir . "/" . $statlang->gT('Survey') . '_' . $surveyid . "_" . $surveyInfo['surveyls_title'] . '.pdf', $pdfOutput);
return $tempdir . "/" . $statlang->gT('Survey') . '_' . $surveyid . "_" . $surveyInfo['surveyls_title'] . '.pdf';
} else {
return $pdf->Output($statlang->gT('Survey') . '_' . $surveyid . "_" . $surveyInfo['surveyls_title'] . '.pdf', $pdfOutput);
}
break;
case 'html':
$statisticsoutput .= "<script type=\"text/javascript\" src=\"http://maps.googleapis.com/maps/api/js?sensor=false\"></script>\n" . "<script type=\"text/javascript\">var site_url='" . Yii::app()->baseUrl . "';var temppath='{$tempurl}';var imgpath='" . Yii::app()->getConfig('adminimageurl') . "';var aGMapData=" . ls_json_encode($agmapdata) . ";var aStatData=" . ls_json_encode($astatdata) . "</script>";
return $statisticsoutput;
break;
default:
return $statisticsoutput;
break;
}
}
/**
* Get the quartile using minitab method
*
* L=(1/4)(n+1), U=(3/4)(n+1)
* Minitab linear interpolation between the two
* closest data points. Minitab would let L = 2.5 and find the value half way between the
* 2nd and 3rd data points. In our example, that would be (4+9)/2 =
* 6.5. Similarly, the upper quartile value would be half way between
* the 7th and 8th data points, which would be (49+64)/2 = 56.5. If L
* were 2.25, Minitab would find the value one fourth of the way
* between the 2nd and 3rd data points and if L were 2.75, Minitab
* would find the value three fourths of the way between the 2nd and
* 3rd data points.
*
* @staticvar null $sid
* @staticvar int $recordCount
* @staticvar null $field
* @staticvar null $allRows
* @param 0|1|2|3 $quartile use 0 for return of recordcount, otherwise will return Q1,Q2,Q3
* @param string $fieldname
* @param int $surveyid
* @param string $sql
* @param bool $excludezeros
* @return null|float
*/
protected function getQuartile($quartile, $fieldname, $surveyid, $sql, $excludezeros)
{
static $sid = null;
static $recordCount = 0;
static $field = null;
static $allRows = null;
if ($surveyid !== $sid || $fieldname !== $field) {
//get data
$query = " FROM {{survey_{$surveyid}}} WHERE " . Yii::app()->db->quoteColumnName($fieldname) . " IS NOT null";
//NO ZEROES
if (!$excludezeros) {
$query .= " AND " . Yii::app()->db->quoteColumnName($fieldname) . " != 0";
}
//filtering enabled?
if (incompleteAnsFilterState() == "incomplete") {
$query .= " AND submitdate is null";
} elseif (incompleteAnsFilterState() == "complete") {
$query .= " AND submitdate is not null";
}
//if $sql values have been passed to the statistics script from another script, incorporate them
if ($sql != "NULL") {
$query .= " AND {$sql}";
}
}
if ($surveyid !== $sid) {
$sid = $surveyid;
$recordCount = 0;
$field = null;
// Reset cache
//we just put the total number of records at the beginning of this array
$recordCount = Yii::app()->db->createCommand("SELECT COUNT(" . Yii::app()->db->quoteColumnName($fieldname) . ")" . $query)->queryScalar();
}
if ($fieldname !== $field) {
$field = $fieldname;
$allRows = Yii::app()->db->createCommand("SELECT " . Yii::app()->db->quoteColumnName($fieldname) . $query . ' ORDER BY ' . Yii::app()->db->quoteColumnName($fieldname))->queryAll();
}
// Qx = (x/4) * (n+1) if not integer, interpolate
switch ($quartile) {
case 1:
case 3:
// Need at least 4 records
if ($recordCount < 4) {
return;
}
break;
case 2:
// Need at least 2 records
if ($recordCount < 2) {
return;
}
break;
case 0:
return $recordCount;
default:
return;
break;
}
$q1 = $quartile / 4 * ($recordCount + 1);
$row = $q1 - 1;
// -1 since we start counting at 0
if ($q1 === (int) $q1) {
return $allRows[$row][$fieldname];
} else {
$diff = $q1 - (int) $q1;
return $allRows[$row][$fieldname] + $diff * ($allRows[$row + 1][$fieldname] - $allRows[$row][$fieldname]);
}
}
/**
* Creates a query string with all fields for the export
* @param
* @return string
*/
function SPSSGetQuery($iSurveyID)
{
$bDataAnonymized = Survey::model()->findByPk($iSurveyID)->anonymized == 'Y';
$tokensexist = tableExists('tokens_' . $iSurveyID);
#See if tokens are being used
if (isset($tokensexist) && $tokensexist == true && !$bDataAnonymized) {
$query = "SELECT ";
$tokenattributes = getTokenFieldsAndNames($iSurveyID, false);
foreach ($tokenattributes as $attributefield => $attributedescription) {
//Drop the token field, since it is in the survey too
if ($attributefield != 'token') {
$query .= "t.{$attributefield}, ";
}
}
$query .= "s.*\n FROM {{survey_{$iSurveyID}}} s\n LEFT JOIN {{tokens_{$iSurveyID}}} t ON s.token = t.token";
} else {
$query = "SELECT s.*\n FROM {{survey_{$iSurveyID}}} s";
}
switch (incompleteAnsFilterState()) {
case 'inc':
//Inclomplete answers only
$query .= ' WHERE s.submitdate is null ';
break;
case 'filter':
//Inclomplete answers only
$query .= ' WHERE s.submitdate is not null ';
break;
}
return $query;
}
<a href='<?php echo $this->createUrl("admin/responses/browse/surveyid/$surveyid/start/$next/limit/$limit"); ?>'>
<img src='<?php echo $sImageURL; ?>dataforward.png' alt='<?php $clang->eT("Show next.."); ?>' /></a>
<a href='<?php echo $this->createUrl("admin/responses/browse/surveyid/$surveyid/start/$end/limit/$limit"); ?>'>
<img src='<?php echo $sImageURL; ?>dataend.png' alt='<?php $clang->eT("Show last.."); ?>' /></a>
<img src='<?php echo $sImageURL; ?>separator.gif' class='separator' alt='' />
<?php
}
$selectshow = '';
$selectinc = '';
$selecthide = '';
if (incompleteAnsFilterState() == "inc")
{
$selectinc = "selected='selected'";
}
elseif (incompleteAnsFilterState() == "filter")
{
$selecthide = "selected='selected'";
}
else
{
$selectshow = "selected='selected'";
}
?>
<form action='<?php echo $this->createUrl("admin/responses/browse/surveyid/$surveyid/"); ?>' id='browseresults' method='post'>
<img src='<?php echo $sImageURL; ?>blank.gif' width='31' height='20' alt='' />
<?php $clang->eT("Records displayed:"); ?><input type='text' size='4' value='<?php echo $dtcount2; ?>' name='limit' id='limit' />
<?php $clang->eT("Starting from:"); ?><input type='text' size='4' value='<?php echo $start; ?>' name='start' id='start' />
<input type='submit' value='<?php $clang->eT("Show"); ?>' />
<?php $clang->eT("Display:"); ?>
<?php echo CHtml::dropDownList('completionstate',$sCompletionStateValue,array('all'=> $clang->gT("All responses"),
/**
* @deprecated
* */
function oldbrowse($iSurveyID)
{
$aData = $this->_getData($iSurveyID);
extract($aData);
$aViewUrls = array();
$sBrowseLanguage = $aData['language'];
$tokenRequest = Yii::app()->request->getParam('token', null);
/**
* fnames is used as informational array
* it containts
* $fnames[] = array(<dbfieldname>, <some strange title>, <questiontext>, <group_id>, <questiontype>);
*/
if (Permission::model()->hasSurveyPermission($iSurveyID, 'responses', 'read')) {
if (Yii::app()->request->getPost('sql')) {
$aViewUrls[] = 'browseallfiltered_view';
}
//add token to top of list if survey is not private
if ($aData['surveyinfo']['anonymized'] == "N" && tableExists('tokens_' . $iSurveyID)) {
if (Permission::model()->hasSurveyPermission($iSurveyID, 'tokens', 'read')) {
$fnames[] = array("token", gT("Token ID"), 'code' => 'token');
$fnames[] = array("firstname", gT("First name"), 'code' => 'firstname');
// or token:firstname ?
$fnames[] = array("lastname", gT("Last name"), 'code' => 'lastname');
$fnames[] = array("email", gT("Email"), 'code' => 'email');
}
}
$fnames[] = array("submitdate", gT("Completed"), gT("Completed"), "0", 'D');
$fields = createFieldMap($iSurveyID, 'full', false, false, $aData['language']);
foreach ($fields as $fielddetails) {
if ($fielddetails['fieldname'] == 'lastpage' || $fielddetails['fieldname'] == 'submitdate') {
continue;
}
$question = $fielddetails['question'];
if ($fielddetails['type'] != "|") {
if ($fielddetails['fieldname'] == 'lastpage' || $fielddetails['fieldname'] == 'submitdate' || $fielddetails['fieldname'] == 'token') {
continue;
}
// no headers for time data
if ($fielddetails['type'] == 'interview_time') {
continue;
}
if ($fielddetails['type'] == 'page_time') {
continue;
}
if ($fielddetails['type'] == 'answer_time') {
continue;
}
$fnames[] = array($fielddetails['fieldname'], viewHelper::getFieldText($fielddetails), 'code' => viewHelper::getFieldCode($fielddetails, array('LEMcompat' => true)));
} elseif ($fielddetails['aid'] !== 'filecount') {
$qidattributes = getQuestionAttributeValues($fielddetails['qid']);
for ($i = 0; $i < $qidattributes['max_num_of_files']; $i++) {
$filenum = sprintf(gT("File %s"), $i + 1);
if ($qidattributes['show_title'] == 1) {
$fnames[] = array($fielddetails['fieldname'], "{$filenum} - {$question} (" . gT('Title') . ")", 'code' => viewHelper::getFieldCode($fielddetails) . '(title)', "type" => "|", "metadata" => "title", "index" => $i);
}
if ($qidattributes['show_comment'] == 1) {
$fnames[] = array($fielddetails['fieldname'], "{$filenum} - {$question} (" . gT('Comment') . ")", 'code' => viewHelper::getFieldCode($fielddetails) . '(comment)', "type" => "|", "metadata" => "comment", "index" => $i);
}
$fnames[] = array($fielddetails['fieldname'], "{$filenum} - {$question} (" . gT('File name') . ")", 'code' => viewHelper::getFieldCode($fielddetails) . '(name)', "type" => "|", "metadata" => "name", "index" => $i);
$fnames[] = array($fielddetails['fieldname'], "{$filenum} - {$question} (" . gT('File size') . ")", 'code' => viewHelper::getFieldCode($fielddetails) . '(size)', "type" => "|", "metadata" => "size", "index" => $i);
//$fnames[] = array($fielddetails['fieldname'], "File ".($i+1)." - ".$fielddetails['question']."(extension)", "type"=>"|", "metadata"=>"ext", "index"=>$i);
}
} else {
$fnames[] = array($fielddetails['fieldname'], gT("File count"), 'code' => viewHelper::getFieldCode($fielddetails));
}
}
$fncount = count($fnames);
$start = (int) Yii::app()->request->getParam('start', 0);
$limit = (int) Yii::app()->request->getParam('limit', 50);
$order = Yii::app()->request->getParam('order', 'asc');
if (!$limit) {
$limit = 50;
}
$oCriteria = new CDbCriteria();
//Create the query
if ($aData['surveyinfo']['anonymized'] == "N" && tableExists("{{tokens_{$iSurveyID}}}") && Permission::model()->hasSurveyPermission($iSurveyID, 'tokens', 'read')) {
$oCriteria = SurveyDynamic::model($iSurveyID)->addTokenCriteria($oCriteria);
}
if (incompleteAnsFilterState() == "incomplete") {
$oCriteria->addCondition("submitdate IS NULL");
} elseif (incompleteAnsFilterState() == "complete") {
$oCriteria->addCondition("submitdate IS NOT NULL");
}
$dtcount = SurveyDynamic::model($iSurveyID)->count($oCriteria);
// or die("Couldn't get response data<br />");
if ($limit > $dtcount) {
$limit = $dtcount;
}
//NOW LETS SHOW THE DATA
if (Yii::app()->request->getPost('sql') && stripcslashes(Yii::app()->request->getPost('sql')) !== "" && Yii::app()->request->getPost('sql') != "NULL") {
$oCriteria->addCondition(stripcslashes(Yii::app()->request->getPost('sql')));
}
if (!is_null($tokenRequest)) {
$oCriteria->addCondition('t.token = ' . Yii::app()->db->quoteValue($tokenRequest));
}
$oCriteria->order = 'id ' . ($order == 'desc' ? 'desc' : 'asc');
$oCriteria->offset = $start;
$oCriteria->limit = $limit;
$dtresult = SurveyDynamic::model($iSurveyID)->findAllAsArray($oCriteria);
$dtcount2 = count($dtresult);
// Fix start if order is desc, only if actual start is 0
if ($order == 'desc' && $start == 0) {
$start = $dtcount - count($dtresult);
}
//CONTROL MENUBAR
$last = $start - $limit;
$next = $start + $limit;
$end = $dtcount - $limit;
if ($end < 0) {
$end = 0;
}
if ($last < 0) {
$last = 0;
}
if ($next >= $dtcount) {
$next = $dtcount - $limit;
}
if ($end < 0) {
$end = 0;
}
$aData['dtcount2'] = $dtcount2;
$aData['sCompletionStateValue'] = incompleteAnsFilterState();
$aData['start'] = $start;
$aData['limit'] = $limit;
$aData['last'] = $last;
$aData['next'] = $next;
$aData['end'] = $end;
$aData['fncount'] = $fncount;
$aData['fnames'] = $fnames;
$aData['bHasFileUploadQuestion'] = hasFileUploadQuestion($iSurveyID);
$aViewUrls[] = 'browseallheader_view';
$bgcc = 'even';
foreach ($dtresult as $dtrow) {
if ($bgcc == "even") {
$bgcc = "odd";
} else {
$bgcc = "even";
}
$aData['dtrow'] = $dtrow;
$aData['bgcc'] = $bgcc;
$aData['sBrowseLanguage'] = $sBrowseLanguage;
$aViewUrls['browseallrow_view'][] = $aData;
}
$aViewUrls[] = 'browseallfooter_view';
$this->_renderWrappedTemplate('', $aViewUrls, $aData);
} else {
$aData['surveyid'] = $iSurveyID;
$message['title'] = gT('Access denied!');
$message['message'] = gT('You do not have permission to access this page.');
$message['class'] = "error";
$this->_renderWrappedTemplate('survey', array("message" => $message), $aData);
}
}