/**
* 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";
    }
}
Exemple #2
0
 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;
     }
 }
Exemple #3
0
 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;
     }
 }