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); } }