/**
* Exports CSV response data for SPSS and R
*
* @param mixed $iSurveyID The survey ID
* @param mixed $iLength Maximum text lenght data, usually 255 for SPSS <v16 and 16384 for SPSS 16 and later
* @param mixed $na Value for N/A data
* @param sep Quote separator. Use '\'' for SPSS, '"' for R
* @param logical $header If TRUE, adds SQGA code as column headings (used by export to R)
*/
function SPSSExportData($iSurveyID, $iLength, $na = '', $q = '\'', $header = FALSE)
{
// Build array that has to be returned
$fields = SPSSFieldMap($iSurveyID);
// Now see if we have parameters for from (offset) & num (limit)
$limit = App()->getRequest()->getParam('limit');
$offset = App()->getRequest()->getParam('offset');
//Now get the query string with all fields to export
$query = SPSSGetQuery($iSurveyID, $limit, $offset);
$result = $query->query();
$rownr = 0;
foreach ($result as $row) {
$rownr++;
if ($rownr == 1) {
$num_fields = count($row);
//This shouldn't occur, but just to be safe:
if (count($fields) != $num_fields) {
safeDie("Database inconsistency error");
}
// Add column headers (used by R export)
if ($header == TRUE) {
$i = 1;
foreach ($fields as $field) {
if (!$field['hide']) {
echo $q . strtoupper($field['sql_name']) . $q;
}
if ($i < $num_fields && !$field['hide']) {
echo ',';
}
$i++;
}
echo "\n";
}
}
$row = array_change_key_case($row, CASE_UPPER);
//$row = $result->GetRowAssoc(true); //Get assoc array, use uppercase
reset($fields);
//Jump to the first element in the field array
$i = 1;
foreach ($fields as $field) {
$fieldno = strtoupper($field['sql_name']);
if ($field['SPSStype'] == 'DATETIME23.2') {
#convert mysql datestamp (yyyy-mm-dd hh:mm:ss) to SPSS datetime (dd-mmm-yyyy hh:mm:ss) format
if (isset($row[$fieldno])) {
list($year, $month, $day, $hour, $minute, $second) = preg_split('([^0-9])', $row[$fieldno]);
if ($year != '' && (int) $year >= 1900) {
echo $q . date('d-m-Y H:i:s', mktime($hour, $minute, $second, $month, $day, $year)) . $q;
} else {
echo $na;
}
} else {
echo $na;
}
} else {
if ($field['LStype'] == 'Y') {
if ($row[$fieldno] == 'Y') {
echo $q . 1 . $q;
} else {
if ($row[$fieldno] == 'N') {
echo $q . 2 . $q;
} else {
echo $na;
}
}
} else {
if ($field['LStype'] == 'G') {
if ($row[$fieldno] == 'F') {
echo $q . 1 . $q;
} else {
if ($row[$fieldno] == 'M') {
echo $q . 2 . $q;
} else {
echo $na;
}
}
} else {
if ($field['LStype'] == 'C') {
if ($row[$fieldno] == 'Y') {
echo $q . 1 . $q;
} else {
if ($row[$fieldno] == 'N') {
echo $q . 2 . $q;
} else {
if ($row[$fieldno] == 'U') {
echo $q . 3 . $q;
} else {
echo $na;
}
}
}
} else {
if ($field['LStype'] == 'E') {
if ($row[$fieldno] == 'I') {
echo $q . 1 . $q;
} else {
if ($row[$fieldno] == 'S') {
echo $q . 2 . $q;
} else {
if ($row[$fieldno] == 'D') {
echo $q . 3 . $q;
} else {
echo $na;
}
}
}
} elseif (($field['LStype'] == 'P' || $field['LStype'] == 'M') && (substr($field['code'], -7) != 'comment' && substr($field['code'], -5) != 'other')) {
if ($row[$fieldno] == 'Y') {
echo $q . 1 . $q;
} else {
echo $q . 0 . $q;
}
} elseif (!$field['hide']) {
$strTmp = mb_substr(stripTagsFull($row[$fieldno]), 0, $iLength);
if (trim($strTmp) != '') {
if ($q == '\'') {
$strTemp = str_replace(array("'", "\n", "\r"), array("''", ' ', ' '), trim($strTmp));
}
if ($q == '"') {
$strTemp = str_replace(array('"', "\n", "\r"), array('""', ' ', ' '), trim($strTmp));
}
/*
* Temp quick fix for replacing decimal dots with comma's
if (isNumericExtended($strTemp)) {
$strTemp = str_replace('.',',',$strTemp);
}
*/
echo $q . $strTemp . $q;
} else {
echo $na;
}
}
}
}
}
}
if ($i < $num_fields && !$field['hide']) {
echo ',';
}
$i++;
}
echo "\n";
}
}
public function exportr()
{
global $length_vallabel;
$iSurveyID = sanitize_int(Yii::app()->request->getParam('sid'));
$subaction = Yii::app()->request->getParam('subaction');
$clang = $this->getController()->lang;
//for scale 1=nominal, 2=ordinal, 3=scale
//$typeMap = $this->_getTypeMap();
// $length_vallabel = '120'; // Set the max text length of Value Labels
$iLength = '25500';
// Set the max text length of Text Data
$length_varlabel = '25500';
// Set the max text length of Variable Labels
$headerComment = '';
// $tempFile = '';
if (!isset($iSurveyID)) {
$iSurveyID = returnGlobal('sid');
}
$filterstate = incompleteAnsFilterState();
$headerComment = '#$Rev: 10193 $' . " {$filterstate}.\n";
if (isset($_POST['dldata'])) {
$subaction = "dldata";
}
if (isset($_POST['dlstructure'])) {
$subaction = "dlstructure";
}
if (!isset($subaction)) {
$selecthide = "";
$selectshow = "";
$selectinc = "";
switch ($filterstate) {
case "incomplete":
$selectinc = "selected='selected'";
break;
case "complete":
$selecthide = "selected='selected'";
break;
default:
$selectshow = "selected='selected'";
}
$data['selectinc'] = $selectinc;
$data['selecthide'] = $selecthide;
$data['selectshow'] = $selectshow;
$data['filename'] = "survey_" . $iSurveyID . "_R_syntax_file.R";
$data['surveyid'] = $iSurveyID;
$data['display']['menu_bars']['browse'] = $clang->gT("Export results");
$this->_renderWrappedTemplate('export', 'r_view', $data);
} else {
Yii::app()->loadHelper("admin/exportresults");
}
if ($subaction == 'dldata') {
header("Content-Disposition: attachment; filename=survey_" . $iSurveyID . "_R_data_file.csv");
header("Content-type: text/comma-separated-values; charset=UTF-8");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: public");
$na = "";
//change to empty string instead of two double quotes to fix warnings on NA
SPSSExportData($iSurveyID, $iLength, $na = '', $q = '"', $header = TRUE);
exit;
}
if ($subaction == 'dlstructure') {
header("Content-Disposition: attachment; filename=survey_" . $iSurveyID . "_R_syntax_file.R");
header("Content-type: application/download; charset=UTF-8");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: public");
echo $headerComment;
echo 'data <- read.csv("survey_' . $iSurveyID . '_R_data_file.csv", na.strings=c(",", "\\"\\""), stringsAsFactors=FALSE)';
echo "\n\n";
// Build array that has to be returned
$fields = SPSSFieldMap($iSurveyID, "V");
//Now get the query string with all fields to export
$query = SPSSGetQuery($iSurveyID);
$result = Yii::app()->db->createCommand($query)->query();
//Checked
$num_fields = 0;
//Now we check if we need to adjust the size of the field or the type of the field
foreach ($result as $row) {
if ($num_fields == 0) {
$num_fields = count($row);
}
$row = array_values($row);
$fieldno = 0;
while ($fieldno < $num_fields) {
//Performance improvement, don't recheck fields that have valuelabels
if (!isset($fields[$fieldno]['answers'])) {
$strTmp = mb_substr(stripTagsFull($row[$fieldno]), 0, $iLength);
$len = mb_strlen($strTmp);
if ($len > $fields[$fieldno]['size']) {
$fields[$fieldno]['size'] = $len;
}
if (trim($strTmp) != '') {
if ($fields[$fieldno]['SPSStype'] == 'F' && (isNumericExtended($strTmp) === FALSE || $fields[$fieldno]['size'] > 16)) {
$fields[$fieldno]['SPSStype'] = 'A';
}
}
}
$fieldno++;
}
}
$result->close();
$errors = "";
$i = 1;
foreach ($fields as $field) {
if ($field['SPSStype'] == 'DATETIME23.2') {
$field['size'] = '';
}
if ($field['LStype'] == 'N' || $field['LStype'] == 'K') {
$field['size'] .= '.' . ($field['size'] - 1);
}
switch ($field['SPSStype']) {
case 'F':
$type = "numeric";
break;
case 'A':
$type = "character";
break;
case 'DATETIME23.2':
case 'SDATE':
$type = "character";
//@TODO set $type to format for date
break;
}
if (!$field['hide']) {
echo "data[, " . $i . "] <- " . "as.{$type}(data[, " . $i . "])\n";
echo 'attributes(data)$variable.labels[' . $i . '] <- "' . addslashes(htmlspecialchars_decode(mb_substr(stripTagsFull($field['VariableLabel']), 0, $length_varlabel))) . '"' . "\n";
// Create the value Labels!
if (isset($field['answers'])) {
$answers = $field['answers'];
//print out the value labels!
echo 'data[, ' . $i . '] <- factor(data[, ' . $i . '], levels=c(';
$str = "";
foreach ($answers as $answer) {
if ($field['SPSStype'] == "F" && isNumericExtended($answer['code'])) {
$str .= ",{$answer['code']}";
} else {
$str .= ",\"{$answer['code']}\"";
}
}
$str = mb_substr($str, 1);
echo $str . '),labels=c(';
$str = "";
foreach ($answers as $answer) {
$str .= ",\"{$answer['value']}\"";
}
$str = mb_substr($str, 1);
if ($field['scale'] !== '' && $field['scale'] == 2) {
$scale = ",ordered=TRUE";
} else {
$scale = "";
}
echo "{$str}){$scale})\n";
}
//Rename the Variables (in case somethings goes wrong, we still have the OLD values
if (isset($field['sql_name'])) {
$ftitle = $field['title'];
if (!preg_match("/^([a-z]|[A-Z])+.*\$/", $ftitle)) {
$ftitle = "q_" . $ftitle;
}
$ftitle = str_replace(array("-", ":", ";", "!"), array("_hyph_", "_dd_", "_dc_", "_excl_"), $ftitle);
if (!$field['hide']) {
if ($ftitle != $field['title']) {
$errors .= "# Variable name was incorrect and was changed from {$field['title']} to {$ftitle} .\n";
}
echo "names(data)[" . $i . "] <- " . "\"" . $ftitle . "\"\n";
// <AdV> added \n
}
$i++;
} else {
echo "#sql_name not set\n";
}
} else {
echo "#Field hidden\n";
}
echo "\n";
}
// end foreach
echo $errors;
exit;
}
}
public function exportspss()
{
global $length_vallabel;
$iSurveyID = sanitize_int(Yii::app()->request->getParam('sid'));
//for scale 1=nominal, 2=ordinal, 3=scale
// $typeMap = $this->_getTypeMap();
$filterstate = incompleteAnsFilterState();
$spssver = returnGlobal('spssver');
if (is_null($spssver)) {
if (!Yii::app()->session['spssversion']) {
Yii::app()->session['spssversion'] = 2;
//Set default to 2, version 16 or up
}
$spssver = Yii::app()->session['spssversion'];
} else {
Yii::app()->session['spssversion'] = $spssver;
}
$length_varlabel = '231';
// Set the max text length of Variable Labels
$length_vallabel = '120';
// Set the max text length of Value Labels
switch ($spssver) {
case 1:
//<16
$iLength = '255';
// Set the max text length of the Value
break;
case 2:
//>=16
$iLength = '16384';
// Set the max text length of the Value
break;
default:
$iLength = '16384';
// Set the max text length of the Value
}
$headerComment = '*$Rev: 121017 $' . " {$filterstate} {$spssver}.\n";
if (isset($_POST['dldata'])) {
$subaction = "dldata";
}
if (isset($_POST['dlstructure'])) {
$subaction = "dlstructure";
}
if (!isset($subaction)) {
$selecthide = "";
$selectshow = "";
$selectinc = "";
switch ($filterstate) {
case "incomplete":
$selectinc = "selected='selected'";
break;
case "complete":
$selecthide = "selected='selected'";
break;
default:
$selectshow = "selected='selected'";
}
$data['selectinc'] = $selectinc;
$data['selecthide'] = $selecthide;
$data['selectshow'] = $selectshow;
$data['spssver'] = $spssver;
$data['surveyid'] = $iSurveyID;
$data['display']['menu_bars']['browse'] = gT('Export results');
$this->_renderWrappedTemplate('export', 'spss_view', $data);
return;
}
// Get Base language:
$language = Survey::model()->findByPk($iSurveyID)->language;
App()->setLanguage($language);
Yii::app()->loadHelper("admin/exportresults");
viewHelper::disableHtmlLogging();
if ($subaction == 'dldata') {
header("Content-Disposition: attachment; filename=survey_" . $iSurveyID . "_SPSS_data_file.dat");
header("Content-type: text/comma-separated-values; charset=UTF-8");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: public");
if ($spssver == 2) {
echo "";
}
SPSSExportData($iSurveyID, $iLength);
exit;
}
if ($subaction == 'dlstructure') {
header("Content-Disposition: attachment; filename=survey_" . $iSurveyID . "_SPSS_syntax_file.sps");
header("Content-type: application/download; charset=UTF-8");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: public");
// Build array that has to be returned
$fields = SPSSFieldMap($iSurveyID);
//Now get the query string with all fields to export
$query = SPSSGetQuery($iSurveyID, 500, 0);
// Sample first 500 responses for adjusting fieldmap
$result = $query->queryAll();
$num_fields = 0;
//Now we check if we need to adjust the size of the field or the type of the field
foreach ($result as $row) {
foreach ($fields as $iIndex => $aField) {
//Performance improvement, don't recheck fields that have valuelabels
if (!isset($aField['answers'])) {
$strTmp = mb_substr(stripTagsFull($row[$aField['sql_name']]), 0, $iLength);
$len = mb_strlen($strTmp);
if ($len > $fields[$iIndex]['size']) {
$fields[$iIndex]['size'] = $len;
}
if (trim($strTmp) != '') {
if ($fields[$iIndex]['SPSStype'] == 'F' && (isNumericExtended($strTmp) === FALSE || $fields[$iIndex]['size'] > 16)) {
$fields[$iIndex]['SPSStype'] = 'A';
}
}
}
}
}
/**
* End of DATA print out
*
* Now $fields contains accurate length data, and the DATA LIST can be rendered -- then the contents of the temp file can
* be sent to the client.
*/
if ($spssver == 2) {
echo "";
}
echo $headerComment;
if ($spssver == 2) {
echo "SET UNICODE=ON.\n";
}
echo "SHOW LOCALE.\n";
echo "PRESERVE LOCALE.\n";
echo "SET LOCALE='en_UK'.\n";
echo "GET DATA\n" . " /TYPE=TXT\n" . " /FILE='survey_" . $iSurveyID . "_SPSS_data_file.dat'\n" . " /DELCASE=LINE\n" . " /DELIMITERS=\",\"\n" . " /QUALIFIER=\"'\"\n" . " /ARRANGEMENT=DELIMITED\n" . " /FIRSTCASE=1\n" . " /IMPORTCASE=ALL\n" . " /VARIABLES=";
foreach ($fields as $field) {
if ($field['SPSStype'] == 'DATETIME23.2') {
$field['size'] = '';
}
if ($field['SPSStype'] == 'F' && ($field['LStype'] == 'N' || $field['LStype'] == 'K')) {
$field['size'] .= '.' . ($field['size'] - 1);
}
if (!$field['hide']) {
echo "\n {$field['id']} {$field['SPSStype']}{$field['size']}";
}
}
echo ".\nCACHE.\n" . "EXECUTE.\n";
//Create the variable labels:
echo "*Define Variable Properties.\n";
foreach ($fields as $field) {
if (!$field['hide']) {
$label_parts = strSplitUnicode(str_replace('"', '""', stripTagsFull($field['VariableLabel'])), $length_varlabel - strlen($field['id']));
//if replaced quotes are splitted by, we need to mve the first quote to the next row
foreach ($label_parts as $idx => $label_part) {
if ($idx != count($label_parts) && substr($label_part, -1) == '"' && substr($label_part, -2) != '"') {
$label_parts[$idx] = rtrim($label_part, '"');
$label_parts[$idx + 1] = '"' . $label_parts[$idx + 1];
}
}
echo "VARIABLE LABELS " . $field['id'] . " \"" . implode("\"+\n\"", $label_parts) . "\".\n";
}
}
// Create our Value Labels!
echo "*Define Value labels.\n";
foreach ($fields as $field) {
if (isset($field['answers'])) {
$answers = $field['answers'];
//print out the value labels!
echo "VALUE LABELS {$field['id']}\n";
$i = 0;
foreach ($answers as $answer) {
$i++;
if ($field['SPSStype'] == "F" && isNumericExtended($answer['code'])) {
$str = "{$answer['code']}";
} else {
$str = "\"{$answer['code']}\"";
}
if ($i < count($answers)) {
echo " {$str} \"{$answer['value']}\"\n";
} else {
echo " {$str} \"{$answer['value']}\".\n";
}
}
}
}
foreach ($fields as $field) {
if ($field['scale'] !== '') {
switch ($field['scale']) {
case 2:
echo "VARIABLE LEVEL {$field['id']}(ORDINAL).\n";
break;
case 3:
echo "VARIABLE LEVEL {$field['id']}(SCALE).\n";
}
}
}
//Rename the Variables (in case somethings goes wrong, we still have the OLD values
foreach ($fields as $field) {
if (isset($field['sql_name']) && $field['hide'] === 0) {
$ftitle = $field['title'];
if (!preg_match("/^([a-z]|[A-Z])+.*\$/", $ftitle)) {
$ftitle = "q_" . $ftitle;
}
$ftitle = str_replace(array(" ", "-", ":", ";", "!", "/", "\\", "'"), array("_", "_hyph_", "_dd_", "_dc_", "_excl_", "_fs_", "_bs_", '_qu_'), $ftitle);
if ($ftitle != $field['title']) {
echo "* Variable name was incorrect and was changed from {$field['title']} to {$ftitle} .\n";
}
echo "RENAME VARIABLE ( " . $field['id'] . ' = ' . $ftitle . " ).\n";
}
}
echo "RESTORE LOCALE.\n";
exit;
}
}