function generate() { $_SESSION['PARAMETER_RETRIEVAL'] = PARAMETER_SURVEY_RETRIEVAL; set_time_limit(0); ini_set('memory_limit', Config::dataExportMemoryLimit()); /* set arrays */ if (trim($this->getProperty(DATA_OUTPUT_MODES)) != "") { $this->setProperty(DATA_OUTPUT_MODES, explode("~", $this->getProperty(DATA_OUTPUT_MODES))); } else { $this->setProperty(DATA_OUTPUT_MODES, array()); } if (trim($this->getProperty(DATA_OUTPUT_LANGUAGES)) != "") { $this->setProperty(DATA_OUTPUT_LANGUAGES, explode("~", $this->getProperty(DATA_OUTPUT_LANGUAGES))); } else { $this->setProperty(DATA_OUTPUT_LANGUAGES, array()); } if (trim($this->getProperty(DATA_OUTPUT_VERSIONS)) != "") { $this->setProperty(DATA_OUTPUT_VERSIONS, explode("~", $this->getProperty(DATA_OUTPUT_VERSIONS))); } else { $this->setProperty(DATA_OUTPUT_VERSIONS, array()); } /* set file names */ $this->setProperty(DATA_OUTPUT_FILENAME_STATA, $this->getProperty(DATA_OUTPUT_FILENAME) . ".dta"); $this->setProperty(DATA_OUTPUT_FILENAME_CSV, $this->getProperty(DATA_OUTPUT_FILENAME) . ".csv"); $extracompleted = ""; if ($this->getProperty(DATA_OUTPUT_COMPLETED) == INTERVIEW_COMPLETED) { $extracompleted = " and completed=" . $this->getProperty(DATA_OUTPUT_COMPLETED); } // find any data names in the data $extra = ""; if ($this->getProperty(DATA_OUTPUT_FROM) != "") { $extra .= " and ts > '" . $this->getProperty(DATA_OUTPUT_FROM) . "'"; } if ($this->getProperty(DATA_OUTPUT_TO) != "") { $extra .= " and ts < '" . $this->getProperty(DATA_OUTPUT_TO) . "'"; } $datanames = array(); $this->maxwidths = array(); if ($this->getProperty(DATA_OUTPUT_TYPE) == DATA_OUTPUT_TYPE_DATARECORD_TABLE) { $query = "select distinct datanames from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_datarecords where suid=" . $this->suid . " and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) <= " . $this->maxprimkeylength . $extracompleted . $extra; $res = $this->db->selectQuery($query); if ($res) { if ($this->db->getNumberOfRows($res) == 0) { return 'No records found'; } else { /* go through records */ while ($row = $this->db->getRow($res)) { $datanames = array_unique(array_merge($datanames, explode("~", gzuncompress($row["datanames"])))); $row = null; unset($row); } } $res = null; unset($res); // get max width $decrypt = "answer"; if ($this->survey->getDataEncryptionKey() != "") { $decrypt = "cast(aes_decrypt(answer, '" . $this->survey->getDataEncryptionKey() . "') as char)"; } $query = "SELECT variablename, MAX( LENGTH( " . $decrypt . " )) AS max FROM " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_data WHERE suid = " . $this->suid . " and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) <= " . $this->maxprimkeylength . $extra . $extracompleted . " GROUP BY variablename"; $res = $this->db->selectQuery($query); if ($res) { if ($this->db->getNumberOfRows($res) == 0) { } else { /* go through records */ while ($row = $this->db->getRow($res)) { $this->maxwidths[strtoupper($row["variablename"])] = $row["max"]; $row = null; unset($row); } } $res = null; unset($res); } } } else { $decrypt = ", MAX( LENGTH(answer)) as max"; if ($this->survey->getDataEncryptionKey() != "") { $decrypt = ", MAX( LENGTH( cast(aes_decrypt(answer, '" . $this->survey->getDataEncryptionKey() . "') as char))) AS max"; } $query = "select variablename" . $decrypt . " from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_data where suid=" . $this->suid . " and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) <= " . $this->maxprimkeylength . $extracompleted . $extra . " group by variablename"; //echo $query; //exit; $res = $this->db->selectQuery($query); if ($res) { if ($this->db->getNumberOfRows($res) == 0) { return 'No records found'; } else { /* go through records */ while ($row = $this->db->getRow($res)) { $datanames[] = $row["variablename"]; $this->maxwidths[strtoupper($row["variablename"])] = $row["max"]; $row = null; unset($row); } } $res = null; unset($res); } } // check for filter $filter = array(); if ($this->getProperty(DATA_OUTPUT_VARLIST) != "") { $filter = explode("~", $this->getProperty(DATA_OUTPUT_VARLIST)); //echo $this->getProperty(DATA_OUTPUT_VARLIST); //exit; } /* collect info to sort */ $vars = array(); $this->arrayfields = array(); $this->skipvariables = array(); $this->descriptives = array(); foreach ($datanames as $d) { $vd = $this->getVariableDescriptive(getBasicName($d)); if ($vd->getVsid() != "") { // if info not found, then ignore since we don't know how to handle it // hidden variable if ($this->getProperty(DATA_OUTPUT_HIDDEN) == HIDDEN_YES && $vd->isHidden()) { continue; } else { if (sizeof($filter) > 0 && !inArray($vd->getName(), $filter)) { continue; } } // array and not a specific instance, then store for later so we know to process it differently if ($vd->isArray() && contains($d, "[") == false) { $this->arrayfields[strtoupper($d)] = strtoupper($d); } // create index to track $section = $this->getSection($vd->getSeid()); $key = $vd->getSuid() . $vd->getSeid() . $vd->getVsid(); if (isset($vars[$key])) { $arrtemp = $vars[$key]; $arr = $arrtemp["vars"]; } else { $arr = array(); } //$arr[strtoupper($d)] = $vd; // this needs to work to ensure we are getting the right array and putting it in $vars array $arr[] = strtoupper($d); // this needs to work to ensure we are getting the right array and putting it in $vars array //$vars[$key] = array("sectionposition" => $section->getPosition(), "seid" => $vd->getSeid(), "varposition" => $vd->getPosition(), "vsid" => $vd->getVsid(), "varname" => strtoupper($vd->getName()), "vars" => $arr); $vars[$key] = array("order" => $section->getPosition() . $vd->getSeid() . $vd->getPosition() . $vd->getVsid(), "vars" => $arr); $vd = null; unset($vd); } } $datanames = null; unset($datanames); /* collect variables not found in data */ if ($this->getProperty(DATA_OUTPUT_VARIABLES_WITHOUT_DATA) == VARIABLES_WITHOUT_DATA_YES) { $this->variabledescriptives = $this->survey->getVariableDescriptives(); foreach ($this->variabledescriptives as $vd) { //echo 'going to add: ' . $vd->getName() . "<br/>"; // hidden variable if ($this->getProperty(DATA_OUTPUT_HIDDEN) == DATA_HIDDEN && $vd->isHidden()) { continue; } else { if (inArray($vd->getAnswerType(), array(ANSWER_TYPE_NONE, ANSWER_TYPE_SECTION))) { continue; } else { if (sizeof($filter) > 0 && !inArray($vd->getName(), $filter)) { continue; } } } // array, then store for later so we know to process it differently if ($vd->isArray()) { $this->arrayfields[strtoupper($vd->getName())] = strtoupper($vd->getName()); } $section = $this->getSection($vd->getSeid()); $key = $vd->getSuid() . $vd->getSeid() . $vd->getVsid(); if (isset($vars[$key])) { $arrtemp = $vars[$key]; $arr = $arrtemp["vars"]; } else { $arr = array(); } //$arr[strtoupper($vd->getName())] = $vd; $arr[] = strtoupper($vd->getName()); //$vars[$key] = array("sectionposition" => $section->getPosition(), "seid" => $vd->getSeid(), "varposition" => $vd->getPosition(), "vsid" => $vd->getVsid(), "varname" => strtoupper($vd->getName()), "vars" => $arr); $vars[$key] = array("order" => $section->getPosition() . $vd->getSeid() . $vd->getPosition() . $vd->getVsid(), "vars" => $arr); // no width set, so first time, then no data at all so we set width to 2 if (!isset($this->maxwidths[strtoupper($vd->getName())])) { $this->maxwidths[strtoupper($vd->getName())] = 2; } //$vd = null; //unset($vd); } } /* sort data names by section position, section seid, variable position, variable name */ uasort($vars, 'dataexportSort'); /* retrieve variable information */ foreach ($vars as $key => $subvars) { //echo "<br/>" . $key; $subvars = $subvars["vars"]; sort($subvars, SORT_STRING); // sort by variable name foreach ($subvars as $d) { $this->processVariable($d, $this->getVariableDescriptive(getBasicName($d))); } $vars[$key] = null; } $vars = null; unset($vars); $this->descriptives = null; unset($this->descriptives); /* check for primkey variable presence */ if ($this->getProperty(DATA_OUTPUT_PRIMARY_KEY_IN_DATA) != PRIMARYKEY_NO) { /* TODO */ } /* set number of variables */ $this->variablenumber = sizeof($this->variablenames); /* get number of records */ if ($this->getProperty(DATA_OUTPUT_TYPE) == DATA_OUTPUT_TYPE_DATARECORD_TABLE) { $query = "select count(*) as cnt from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_datarecords where suid=" . $this->suid . " and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) <= " . $this->maxprimkeylength . $extracompleted . $extra; $res = $this->db->selectQuery($query); $row = $this->db->getRow($res); $this->recordcount = $row["cnt"]; } else { $query = "select distinct primkey from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_data where suid=" . $this->suid . " and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) <= " . $this->maxprimkeylength . $extracompleted . $extra; $res = $this->db->selectQuery($query); $this->recordcount = $this->db->getNumberOfRows($res); } /* start writing files */ $outputtype = strtolower($this->getProperty(DATA_OUTPUT_FILETYPE)); $this->asked = sprintf('%.0F', "8.988465625461158E307"); if ($outputtype == FILETYPE_CSV) { $this->startCSVFile(); $this->separator = ","; } else { if ($outputtype == FILETYPE_STATA) { $this->startStataFile(); $this->littleendian = $this->isLittleEndian(); // http://www.stata.com/help.cgi?dta_113 $this->shortempty = 32741; $this->shorterror = 32763; $this->shortdk = 32745; $this->shortna = 32755; $this->shortrf = 32759; $this->shortmarkempty = 32746; $this->doubleempty = sprintf('%.0F', "8.98846567431158E307"); // 2^1013 $this->doubledk = sprintf('%.0F', "8.99724347282165E307"); $this->doublerf = sprintf('%.0F', "9.027965767606894E307"); $this->doublemarkempty = sprintf('%.0F', "8.98846567431158E307") * 1.001220703125; $this->doublena = sprintf('%.0F', "8.98846567431158E307") * 1.00341796875; $this->doubleerror = sprintf('%.0F', "8.98846567431158E307") * 1.00537109375; // floats not used right now $this->floatempty = sprintf('%.0F', "1.7014118E38F"); $this->floatdk = sprintf('%.0F', "1.7030734E38F"); $this->floatna = sprintf('%.0F', "1.7030734E38F"); // TODO $this->floatrf = sprintf('%.0F', "1.7088887E38F"); $this->floatmarkempty = sprintf('%.0F', "1.7030734E38F"); } } // get languages, modes, versions $this->languages = $this->getProperty(DATA_OUTPUT_LANGUAGES); $this->modes = $this->getProperty(DATA_OUTPUT_MODES); $this->versions = $this->getProperty(DATA_OUTPUT_VERSIONS); $this->encoding = $this->getProperty(DATA_OUTPUT_ENCODING); /* go through all records */ if ($this->getProperty(DATA_OUTPUT_TYPE) == DATA_OUTPUT_TYPE_DATARECORD_TABLE) { $decrypt = "data as data_dec"; if ($this->survey->getDataEncryptionKey() != "") { $decrypt = "aes_decrypt(data, '" . $this->survey->getDataEncryptionKey() . "') as data_dec"; } $query = "select primkey, {$decrypt} from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_datarecords where suid=" . $this->suid . " and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) <= " . $this->maxprimkeylength . $extracompleted . $extra . " order by primkey"; $res = $this->db->selectQuery($query); if ($res) { if ($this->db->getNumberOfRows($res) == 0) { return 'No records found'; } else { /* go through records */ while ($row = $this->db->getRow($res)) { //$record = new DataRecord($this->suid, $row["primkey"]); $record = new DataRecord(); $record->setAllData(unserialize(gzuncompress($row["data_dec"]))); if ($outputtype == FILETYPE_CSV) { $this->addCSVRecord($row["primkey"], $record); } else { if ($outputtype == FILETYPE_STATA) { $this->addStataRecord($row["primkey"], $record); } } $record = null; $row = null; unset($record); unset($row); } } } } else { // we already got all distinct primkeys before when we determined the number of records if ($res) { if ($this->db->getNumberOfRows($res) == 0) { return 'No records found'; } else { $decrypt = "answer as data_dec"; if ($this->survey->getDataEncryptionKey() != "") { $decrypt = "aes_decrypt(answer, '" . $this->survey->getDataEncryptionKey() . "') as data_dec"; } /* go through records */ while ($row = $this->db->getRow($res)) { $query = "select primkey, variablename, {$decrypt}, language, mode, version, dirty from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_data where suid=" . $this->suid . " and primkey='" . $row["primkey"] . "'"; $this->currentrecord = array(); //echo $query; //exit; $res2 = $this->db->selectQuery($query); if ($res2) { while ($row2 = $this->db->getRow($res2)) { $this->currentrecord[strtoupper($row2["variablename"])] = array("name" => $row2["variablename"], "dirty" => $row2["dirty"], "answer" => $row2["data_dec"], "language" => $row2["language"], "mode" => $row2["mode"], "version" => $row2["version"]); $row2 = null; unset($row2); } if (sizeof($this->currentrecord) > 0) { if ($outputtype == FILETYPE_CSV) { $this->addCSVRecord($row["primkey"]); } else { if ($outputtype == FILETYPE_STATA) { $this->addStataRecord($row["primkey"]); } } } $this->currentrecord = null; unset($this->currentrecord); } $query = null; unset($query); $res2 = null; unset($res2); $row = null; unset($row); } } } } /* finish */ if ($outputtype == FILETYPE_CSV) { $this->finishCSVFile(); } else { if ($outputtype == FILETYPE_STATA) { $this->addValueLabels(); $this->finishStataFile(); } } $_SESSION['PARAMETER_RETRIEVAL'] = PARAMETER_ADMIN_RETRIEVAL; }
function getAggregrateDataOld($variable) { global $survey, $db; $arr = array(); $decrypt = "data as data_dec"; if ($survey->getDataEncryptionKey() != "") { $decrypt = "aes_decrypt(data, '" . $survey->getDataEncryptionKey() . "') as data_dec"; } if (Config::useDataRecords()) { $query = "select {$decrypt} from " . Config::dbSurveyData() . "_datarecords where suid=" . $survey->getSuid() . $extracompleted . " order by primkey"; } else { $query = "select {$decrypt} from " . Config::dbSurveyData() . "_data where suid=" . $survey->getSuid() . " and variablename='" . VARIABLE_PRIMKEY . "' " . $extracompleted . " order by primkey"; } $res = $db->selectQuery($query); $datanames = array(); if ($res) { if ($db->getNumberOfRows($res) == 0) { return 'No records found'; } else { /* go through records */ while ($row = $db->getRow($res)) { $record = new DataRecord(); $record->setAllData(unserialize(gzuncompress($row["data_dec"]))); $data = $record->getDataForVariable($variable->getName()); foreach ($data as $rec) { $arr[$rec->getAnswer()]++; } } } } return $arr; }