function generateTimings()
{
/* 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());
}
$this->languages = $this->getProperty(DATA_OUTPUT_LANGUAGES);
$this->modes = $this->getProperty(DATA_OUTPUT_MODES);
$this->versions = $this->getProperty(DATA_OUTPUT_VERSIONS);
/* set file names */
$this->setProperty(DATA_OUTPUT_FILENAME_CSV, $this->getProperty(DATA_OUTPUT_FILENAME) . "_timings.csv");
$this->separator = ",";
$_SESSION['PARAMETER_RETRIEVAL'] = PARAMETER_SURVEY_RETRIEVAL;
set_time_limit(0);
// create table
$create = "create table if not exists " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_consolidated_times (\n suid int(11) NOT NULL DEFAULT '1',\n primkey varchar(150) NOT NULL,\n begintime varchar(50) NOT NULL,\n stateid int(11) DEFAULT NULL, \n variable varchar(50) NOT NULL,\n timespent int(11) NOT NULL DEFAULT '0',\n language int(11) NOT NULL DEFAULT '1',\n mode int(11) NOT NULL DEFAULT '1',\n version int(11) NOT NULL DEFAULT '1',\n ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n PRIMARY KEY (suid,primkey,begintime,variable)\n ) ENGINE=MyIsam DEFAULT CHARSET=utf8;";
$this->db->executeQuery($create);
// update
//echo $suid . '----';
//exit;
$query = "delete from table " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_consolidated_times where suid=" . $this->suid;
$this->db->executeQuery($query);
$query = "REPLACE INTO " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_consolidated_times SELECT suid, primkey, begintime, stateid, variable, avg(timespent) as timespent, language, mode, version, ts FROM " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_times where suid=" . $this->suid . " group by primkey, begintime order by primkey asc";
$this->db->executeQuery($query);
// check for filter
$filter = array();
$extra = '';
if ($this->getProperty(DATA_OUTPUT_VARLIST) != "") {
$filter = explode("~", $this->getProperty(DATA_OUTPUT_VARLIST));
$extra = " AND (variable='" . implode("' OR variable='", $filter) . "')";
//echo $extra;
//exit;
}
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) . "'";
}
$cutoff = Config::getTimingCutoff();
//DATA_TIMINGS_CUTOFF; // more than 5 minutes we ignore in calculating total interview time
$data = '';
$select = "select primkey, variable, timespent, language, mode from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_consolidated_times where suid=" . $this->suid . " and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) < " . $this->maxprimkeylength . $extra . " order by primkey asc, ts asc";
//echo $select;
//exit;
$res = $this->db->selectQuery($select);
if ($this->db->getNumberOfRows($res) > 0) {
while ($row = $this->db->getRow($res)) {
//print_r($row);
//if (is_numeric($row["primkey"])) {
/* no match on language, mode and version, then treat as never gotten */
if (!(sizeof($this->languages) == 0 || sizeof($this->languages) > 0 && inArray($row["language"], $this->languages))) {
//
continue;
} else {
if (!(sizeof($this->modes) == 0 || sizeof($this->modes) > 0 && inArray($row["mode"], $this->modes))) {
//
continue;
} else {
if (sizeof($filter) > 0 && !inArray(getBasicName($row["variable"]), $filter)) {
continue;
}
}
}
/* else {
$vd = $this->getVariableDescriptive(getBasicName($row["variable"]));
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;
}
}
} */
$line = '';
if ($this->getProperty(DATA_OUTPUT_PRIMARY_KEY_IN_DATA) != PRIMARYKEY_NO) {
if ($this->getProperty(DATA_OUTPUT_PRIMARY_KEY_ENCRYPTION) != "") {
$line .= getValueForCsv(encryptC($row["primkey"], $this->getProperty(DATA_OUTPUT_PRIMARY_KEY_ENCRYPTION)));
} else {
$line .= getValueForCsv($row["primkey"]);
}
}
$line .= getValueForCsv($row["variable"]);
$line .= getValueForCsv($row["timespent"]);
$line .= getValueForCsv($row["language"]);
$line .= getValueForCsv($row["mode"]);
if (trim($line) != "") {
$data .= trim($line) . "\n";
}
//}
}
}
$data3 = "";
$extralanguagemode = "";
if (sizeof($this->languages) > 0) {
$extralanguagemode .= " AND (language=" . implode(" OR language=", $this->languages) . ")";
}
if (sizeof($this->modes) > 0) {
$extralanguagemode .= " AND (mode=" . implode(" OR mode=", $this->modes) . ")";
}
$select = "select variable, count(*) as cnt, avg(timespent) as average from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_consolidated_times where suid=" . $this->suid . " and timespent < {$cutoff} and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) < " . $this->maxprimkeylength . $extralanguagemode . $extra . " group by variable order by variable asc, ts asc";
$res = $this->db->selectQuery($select);
if ($this->db->getNumberOfRows($res) > 0) {
while ($row = $this->db->getRow($res)) {
/* $vd = $this->getVariableDescriptive(getBasicName($row["variable"]));
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;
}
} */
$line = getValueForCsv($row["variable"]);
$line .= getValueForCsv($row["cnt"]);
$line .= getValueForCsv($row["average"]);
$line .= getValueForCsv($row["average"] / 60);
if (trim($line) != "") {
$data3 .= trim($line) . "\n";
}
}
}
$data2 = "";
$select = "select primkey, sum(timespent) as total, sum(timespent)/60 as total2, avg(timespent) as average, count(*) as cnt, language, mode from " . $this->getProperty(DATA_OUTPUT_MAINDATATABLE) . "_consolidated_times where suid=" . $this->suid . " and timespent < {$cutoff} and length(primkey) >= " . $this->minprimkeylength . " and length(primkey) < " . $this->maxprimkeylength . $extra . " group by primkey order by primkey asc";
$res = $this->db->selectQuery($select);
if ($this->db->getNumberOfRows($res) > 0) {
while ($row = $this->db->getRow($res)) {
//if (is_numeric($row["primkey"])) {
/* no match on language, mode and version, then treat as never gotten */
if (!(sizeof($this->languages) == 0 || sizeof($this->languages) > 0 && inArray($row["language"], $this->languages))) {
//
continue;
} else {
if (!(sizeof($this->modes) == 0 || sizeof($this->modes) > 0 && inArray($row["mode"], $this->modes))) {
//
continue;
}
}
$line = '';
if ($this->getProperty(DATA_OUTPUT_PRIMARY_KEY_IN_DATA) != PRIMARYKEY_NO) {
if ($this->getProperty(DATA_OUTPUT_PRIMARY_KEY_ENCRYPTION) != "") {
$line .= getValueForCsv(encryptC($row["primkey"], $this->getProperty(DATA_OUTPUT_PRIMARY_KEY_ENCRYPTION)));
} else {
$line .= getValueForCsv($row["primkey"]);
}
}
$line .= getValueForCsv($row["total"]);
$line .= getValueForCsv($row["total2"]);
$line .= getValueForCsv($row["cnt"]);
$line .= getValueForCsv($row["average"]);
$line .= getValueForCsv($row["language"]);
$line .= getValueForCsv($row["mode"]);
if (trim($line) != "") {
$data2 .= trim($line) . "\n";
}
//}
}
}
$this->csvhandle = null;
$outdir = sys_get_temp_dir();
if (!endsWith($outdir, DIRECTORY_SEPARATOR)) {
$outdir .= DIRECTORY_SEPARATOR;
}
$this->csvhandle = fopen($outdir . $this->getProperty(DATA_OUTPUT_FILENAME_CSV), "w");
if (!$this->csvhandle) {
/* show error */
return;
}
$this->downloadlocation = $outdir . $this->getProperty(DATA_OUTPUT_FILENAME_CSV);
if ($this->getProperty(DATA_OUTPUT_PRIMARY_KEY_IN_DATA) != PRIMARYKEY_NO) {
$header = 'primkey' . $this->separator . 'variable' . $this->separator . 'timespent' . $this->separator . 'language' . $this->separator . 'mode';
$header2 = 'primkey' . $this->separator . 'total time spent (in seconds)' . $this->separator . 'total time spent (in minutes)' . $this->separator . 'number of screens' . $this->separator . 'average time spent per screen (in seconds)' . $this->separator . 'language' . $this->separator . 'mode';
} else {
$header = 'variable' . $this->separator . 'timespent' . $this->separator . 'language' . $this->separator . 'mode';
$header2 = 'total time spent (in seconds)' . $this->separator . 'total time spent (in minutes)' . $this->separator . 'number of screens' . $this->separator . 'average time spent per screen (in seconds)' . $this->separator . 'language' . $this->separator . 'mode';
}
$header3 = 'variable, number of times on screen, average time spent (in seconds), average time spent (in minutes)';
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
} else {
$data2 = str_replace("\r", "", $data2);
}
// write file
fwrite($this->csvhandle, "{$header2}\n{$data2}\n\n\n{$header3}\n{$data3}\n\n\n{$header}\n{$data}");
/* finish */
$this->finishCSVFile();
$_SESSION['PARAMETER_RETRIEVAL'] = PARAMETER_ADMIN_RETRIEVAL;
}
