/** * 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"; } }