here a VERY VERY VERY BADLY written code, not integrated in the MVC of LimeSurvey - just a quick try.
Columns/questions names are in italian, but should not be an issue.
It a couple MySQL stored proc + PHP code, returning a JSON.
The MYSQL procedure:
Code:
DELIMITER //
DROP PROCEDURE IF EXISTS spLime_SurveyXcode //
CREATE PROCEDURE spLime_SurveyXcode(survey_code INT)
BEGIN
/* return the set of question codes in SGQ format
transcoded with group codes and question codes
*/
select c.column_name
, RIGHT(g.group_name, 5) codice_reato
, q.Title codice_domanda
from information_schema.columns c
left join lime_groups g
ON LOCATE(CONCAT('X', g.gid, 'X') , c.column_name) = 7
left join lime_questions q
ON g.gid = q.gid
and LOCATE(CONCAT('X', q.qid, 'E'), CONCAT(c.column_name, 'E')) > 8
where c.table_name = CONCAT('lime_survey_', CAST(survey_code AS CHAR(8))) #869765'
and left(c.column_name, 7) = CONCAT(CAST(survey_code AS CHAR(8)), 'X'); #'869765X';
END//
CALL `dbLime`.`spLime_SurveyXcode`(869765);
The PHP:
Code:
<?php
error_reporting(E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED);
$config['base_url'] = 'https://localhost/limesurvey/';
$config['db_host'] = 'localhost'; // Database host (e.g. localhost)
$config['db_name'] = 'dbLime'; // Database name
$config['db_username'] = 'root'; // Database username
$config['db_password'] = ''; // Database password
$config['survey_code'] = '869765';
function logMe($msg)
{
// appends logs to a "message.log" file in the Lime's TMP directory
if ($f=fopen('tmp/message.log', 'a'))
{
fputs($f, date("Y-m-d H:i:s")." $msg\n");
fclose($f);
}
}
// Open connection
$mysqli = new mysqli(
$config['db_host']
,$config['db_username']
,$config['db_password']
,$config['db_name']
);
// Check connection
if ($mysqli->connect_errno)
{
logMe("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
die($mysqli->connect_error);
}
// execute xcodeproc
$resx = $mysqli->query(sprintf('call spLime_SurveyXcode(%d)', $config['survey_code']), MYSQLI_STORE_RESULT);
if(!$resx) {
logMe("CALL failed: (" . $mysqli->errno . ") " . $mysqli->error);
die($mysqli->error);
}
// we build the union that traverse every SGQ-code column of the survey
// and pair the results with the actual codes of the group and of the question
while($obj = $resx->fetch_object()) {
list($S, $G, $Q) = split('X', $obj->column_name);
$the_unions[] = sprintf("SELECT '%s' codice_reato \n
, '%s' codice_domanda \n
, %s codice_risposta \n
, a.assessment_value \n
FROM lime_survey_%s AS s \n
LEFT JOIN lime_answers AS a \n
ON a.code = s.%s \n
AND a.qid = '%s' \n"
, $obj->codice_reato
, $obj->codice_domanda
, $obj->column_name
, $config['survey_code']
, $obj->column_name
, $Q);
}
$resx->close();
$mysqli->next_result();
$the_union = implode( "\nUNION ALL\n", $the_unions);
$the_union .= "\nORDER BY codice_reato, codice_domanda, codice_risposta, assessment_value;";
$res = $mysqli->query($the_union );
if($res) {
while($row = $res->fetch_object()) {
$results[] = $row;
}
// Free result set
$res->close();
$mysqli->next_result();
//FINAL OUTPUT
// array_values() removes the original keys and replaces
// with plain consecutive numbers
print json_encode(array_values($results));
}
else {
logMe("UNION failed (" . $mysqli->errno . ") " . $mysqli->error);
}
// Close connection
logMe("Close connection.");
$mysqli->close();
?>
Topic Author
tuned 
