/**
* Export the data in PSPP form (may also work with SPSS)
*
* @param int qid The qid to export
*
*/
function export_pspp($qid, $unverified = false)
{
global $db;
$unv = "";
if ($unverified) {
$unv = T_("unverified") . "_";
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: text");
header("Content-Disposition: attachment; filename={$unv}data_{$qid}.sps");
echo "DATA LIST FIXED /";
$sql = "SELECT b.bgid, bg.btid, (CASE WHEN bg.varname = '' THEN CONCAT('Q_',b.bgid) ELSE bg.varname END) as varname, count( b.bid ) as count,bg.width,bg.label\n\t\tFROM boxes as b\n\t\tJOIN boxgroupstype as bg ON (bg.bgid = b.bgid)\n\t\tJOIN pages as p ON (p.pid = b.pid)\n\t\tWHERE p.qid = '{$qid}'\n\t\tAND bg.btid > 0\n\t\tGROUP BY b.bgid\n\t\tORDER BY bg.sortorder";
$cols = $db->GetAll($sql);
$cc = count($cols);
//PSPP variable name cannot start with a number and must be unique - check
$vars = array();
for ($i = 0; $i < $cc; $i++) {
//start numeric
if (is_numeric(substr($cols[$i]['varname'], 0, 1))) {
$cols[$i]['varname'] = "V" . $cols[$i]['varname'];
}
//make unique
$letter = "A";
$checked = false;
$added = false;
while (!$checked) {
if (isset($vars[$cols[$i]['varname']])) {
if ($added) {
$letter = chr(ord($letter) + 1);
$cols[$i]['varname'] = substr($cols[$i]['varname'], 0, -1) . $letter;
} else {
$cols[$i]['varname'] = $cols[$i]['varname'] . $letter;
$added = true;
}
} else {
$checked = true;
$vars[$cols[$i]['varname']] = $cols[$i]['varname'];
}
}
}
$startpos = 1;
$width = 0;
$colsc = 0;
foreach ($cols as $col) {
$varname = $col['varname'];
$length = $col['count'];
$vartype = " ";
if ($col['btid'] == 1) {
$length = max(strlen($col['count']), $col['width']);
//check if any values are non-numeric
$sql = "SELECT count(*) as c\n\t\t\t\tFROM `boxes` \n\t\t\t\tWHERE `bgid` = '{$col['bgid']}'\n\t\t\t\tAND `value` NOT REGEXP '[0-9]+' \n\t\t\t\tAND `value` != ''";
$vt = $db->Getrow($sql);
if (isset($vt['c']) && !empty($vt['c'])) {
$vartype = " (A) ";
$cols[$colsc]['is_string'] = true;
}
}
if ($col['btid'] == 3 || $col['btid'] == 6) {
$vartype = "(A) ";
}
if ($col['btid'] == 6 || $col['btid'] == 5) {
$length = $col['width'];
}
if ($col['btid'] == 2) {
$length = 1;
for ($i = 1; $i <= $col['count']; $i++) {
$nam = $varname . "_{$i}";
$startpos = $startpos + $width;
$width = $length;
$endpos = $startpos + $width - 1;
echo "{$nam} {$startpos}-{$endpos} {$vartype}";
}
} else {
$startpos = $startpos + $width;
$width = $length;
$endpos = $startpos + $width - 1;
echo "{$varname} {$startpos}-{$endpos} {$vartype}";
}
$colsc++;
}
$startpos = $startpos + $width;
$endpos = $startpos + 9;
echo "formid {$startpos}-{$endpos} ";
$startpos = $startpos + 10;
$endpos = $startpos + 9;
echo "rpc_id {$startpos}-{$endpos} ";
echo " .\nVARIABLE LABELS ";
$first = true;
foreach ($cols as $col) {
$vardescription = pspp_escape($col['label']);
$varname = $col['varname'];
if ($first) {
$first = false;
} else {
echo "/";
}
if ($col['btid'] == 2) {
for ($i = 1; $i <= $col['count']; $i++) {
$nam = $varname . "_{$i}";
echo "{$nam} '{$vardescription}' ";
}
} else {
echo "{$varname} '{$vardescription}' ";
}
}
echo "/formid 'queXF Form ID' /rpc_id 'queXF RPC ID' .\n";
echo "VALUE LABELS ";
foreach ($cols as $col) {
$varname = $col['varname'];
if ($col['btid'] == 1 || $col['btid'] == 2) {
$sql = "SELECT value,label\n\t\t\t\tFROM boxes\n\t\t\t\tWHERE bgid = '{$col['bgid']}'";
$rs = $db->GetAll($sql);
if (!empty($rs)) {
echo " /{$varname}";
foreach ($rs as $r) {
if ($r['value'] != "") {
if (!isset($col['is_string'])) {
echo " {$r['value']} '";
} else {
echo " '";
//make label same width
echo str_pad($r['value'], $col['width'], " ", STR_PAD_LEFT);
echo "' '";
}
echo pspp_escape($r['label']) . "'";
}
}
}
}
}
echo " .\nBEGIN DATA.\n";
outputdata($qid, "", false, true, $unverified);
echo "END DATA.\n";
}
/**
* Export the data in PSPP form (may also work with SPSS)
*
* @param int data_id The data id to export
* @param bool include_data Whether or not to include the data
*
*/
function export_pspp($data_id, $include_data = true)
{
global $db;
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: text");
header("Content-Disposition: attachment; filename=data_{$data_id}.sps");
if ($include_data) {
echo "DATA LIST FIXED /";
} else {
echo "DATA LIST FILE=\"data_{$data_id}.txt\" /";
}
//export variables in the format: varname start-end (type)
//Make sure not to include variables with no name as there is no way to identify them
$sql = "SELECT c.*\r\n\t\tFROM `column` as c\r\n\t\tWHERE data_id = '{$data_id}'\r\n\t\tAND c.name IS NOT NULL\r\n\t\tAND c.name != ''\r\n\t\tORDER BY c.in_input DESC , c.startpos ASC , c.sortorder ASC, c.column_id ASC";
$cols = $db->GetAll($sql);
$startpos = 1;
$width = 0;
foreach ($cols as $key => $col) {
$varname = $col['name'];
if ($col['type'] == 0) {
$vartype = ' ';
} else {
$vartype = '(A) ';
}
if (!empty($col['startpos']) && $col['startpos'] > 0) {
$startpos = $col['startpos'];
} else {
$startpos = $startpos + $width;
}
$width = $col['width'];
$endpos = $startpos + $width - 1;
if ($width != 0) {
echo "{$varname} {$startpos}-{$endpos} {$vartype}";
} else {
unset($cols[$key]);
}
}
echo " .\nVARIABLE LABELS ";
$first = true;
foreach ($cols as $col) {
$vardescription = pspp_escape($col['description']);
$varname = $col['name'];
if ($first) {
$first = false;
} else {
echo "/";
}
echo "{$varname} '{$vardescription}' ";
}
echo " .\nVALUE LABELS";
//If there are categories, insert them here
foreach ($cols as $col) {
if (!empty($col['code_level_id'])) {
$varname = $col['name'];
$code_level_id = $col['code_level_id'];
$sql = "SELECT value,label\r\n\t\t\t\tFROM code\r\n\t\t\t\tWHERE code_level_id = '{$code_level_id}'\r\n\t\t\t\tORDER BY code_id ASC";
$codes = $db->GetAll($sql);
if (!empty($codes)) {
echo " /{$varname} ";
if ($col['type'] == 0) {
$surround = "";
} else {
$surround = "'";
}
foreach ($codes as $code) {
echo $surround . $code['value'] . "{$surround} '" . pspp_escape($code['label'], 60) . "' ";
}
}
}
}
echo " .\n";
if ($include_data) {
echo "BEGIN DATA.\n";
export_fixed_width($data_id, false);
echo "END DATA.\n";
}
}
