* other free or open source software licenses.
* See COPYRIGHT.php for copyright notices and details.
*
* $Id: importoldresponses.php 8540 2010-03-31 11:37:19Z texens $
*/
//import responses from an old_ survey table into an active survey
include_once "login_check.php";
if (!isset($oldtable)) {
$oldtable = returnglobal('oldtable');
}
if (!isset($surveyid)) {
$surveyid = returnglobal('sid');
}
if (!$subaction == "import") {
// show UI for choosing old table
$query = db_select_tables_like("{$dbprefix}old\\_survey\\_%");
$result = db_execute_num($query) or safe_die("Error:<br />{$query}<br />" . $connect->ErrorMsg());
$optionElements = '';
$queryCheckColumnsActive = "SELECT * FROM {$dbprefix}survey_{$surveyid} ";
$resultActive = db_execute_num($queryCheckColumnsActive) or safe_die("Error:<br />{$query}<br />" . $connect->ErrorMsg());
$countActive = $resultActive->FieldCount();
while ($row = $result->FetchRow()) {
$queryCheckColumnsOld = "SELECT * FROM {$row[0]} ";
$resultOld = db_execute_num($queryCheckColumnsOld) or safe_die("Error:<br />{$query}<br />" . $connect->ErrorMsg());
if ($countActive == $resultOld->FieldCount()) {
$optionElements .= "\t\t\t<option value='{$row[0]}'>{$row[0]}</option>\n";
}
}
//Get the menubar
$importoldresponsesoutput = browsemenubar($clang->gT("Quick Statistics"));
$importoldresponsesoutput .= "\n\t\t<div class='header'>\n\t\t\t" . $clang->gT("Import responses from an deactivated survey table") . "\n\t\t</div>\n <form id='personalsettings' method='post'> \n\t\t<ul>\n\t\t <li><label for='spansurveyid'>" . $clang->gT("Target survey ID:") . "</label>\n\t\t <span id='spansurveyid'> {$surveyid}<input type='hidden' value='{$surveyid}' name='sid'></span>\n\t\t</li>\n <li>\n\t\t <label for='oldtable'>\n\t\t " . $clang->gT("Source table:") . "\n\t\t </label>\n\t\t <select name='oldtable' >\n\t\t {$optionElements}\n\t\t </select>\n\t\t</li>\n </ul>\n\t\t <p><input type='submit' value='" . $clang->gT("Import Responses") . "' onclick='return confirm(\"" . $clang->gT("Are you sure?", "js") . ")'> \n \t \t <input type='hidden' name='subaction' value='import'><br /><br />\n\t\t\t<div class='messagebox'><div class='warningheader'>" . $clang->gT("Warning") . '</div>' . $clang->gT("You can import all old responses with the same amount of columns as in your active survey. YOU have to make sure, that this responses corresponds to the questions in your active survey.") . "</div>\n\t\t</form>\n </div>\n\t\t<br />";
elseif (returnglobal('restoretable') == "Y" && returnglobal('oldtable') && bHasSurveyPermission($surveyid, 'surveyactivation','update'))
{
$query = db_rename_table(returnglobal('oldtable') , db_table_name_nq("tokens_$surveyid"));
$result=$connect->Execute($query) or safe_die("Failed Rename!<br />".$query."<br />".$connect->ErrorMsg());
$tokenoutput .= "\t</div><div class='messagebox ui-corner-all'>\n"
."<div class='header ui-widget-header'>".$clang->gT("Import old tokens")."</div>"
."<br />".$clang->gT("A token table has been created for this survey and the old tokens were imported.")." (\"".$dbprefix."tokens_$surveyid\")<br /><br />\n"
."<input type='submit' value='"
.$clang->gT("Continue")."' onclick=\"window.open('$scriptname?action=tokens&sid=$surveyid', '_top')\" />\n"
."</div>\n";
return;
}
else
{
$query=db_select_tables_like("{$dbprefix}old\_tokens\_".$surveyid."\_%");
$result=db_execute_num($query) or safe_die("Couldn't get old table list<br />".$query."<br />".$connect->ErrorMsg());
$tcount=$result->RecordCount();
if ($tcount > 0)
{
while($rows=$result->FetchRow())
{
$oldlist[]=$rows[0];
}
}
$tokenoutput .= "\t</div><div class='messagebox ui-corner-all'>\n"
."<div class='warningheader'>".$clang->gT("Warning")."</div>\n"
."<br /><strong>".$clang->gT("Tokens have not been initialised for this survey.")."</strong><br /><br />\n";
if (bHasSurveyPermission($surveyid, 'surveyactivation','update'))
{
$tokenoutput .= $clang->gT("If you initialise tokens for this survey then this survey will only be accessible to users who provide a token either manually or by URL.")
function upgrade_survey_tables139()
{
global $modifyoutput, $dbprefix;
$surveyidquery = db_select_tables_like($dbprefix . "survey\\_%");
$surveyidresult = db_execute_num($surveyidquery);
if (!$surveyidresult) {
return "Database Error";
} else {
while ($sv = $surveyidresult->FetchRow()) {
modify_database("", "ALTER TABLE " . $sv[0] . " ADD [lastpage] int");
echo $modifyoutput;
flush();
ob_flush();
}
}
}
$date = date('YmdHis') . rand(1, 1000);
$sOldTable = "survey_{$iSurveyID}";
$sNewTable = "old_survey_{$iSurveyID}_{$date}";
$deactivatequery = db_rename_table(db_table_name_nq($sOldTable), db_table_name_nq($sNewTable));
$deactivateresult = $connect->Execute($deactivatequery) or die("Couldn't make backup of the survey table. Please try again. The database reported the following error:<br />" . htmlspecialchars($connect->ErrorMsg()) . "<br />");
if ($databasetype == 'postgres') {
// If you deactivate a postgres table you have to rename the according sequence too and alter the id field to point to the changed sequence
$deactivatequery = db_rename_table($sOldTable . '_id_seq', $sNewTable . '_id_seq');
$deactivateresult = $connect->Execute($deactivatequery) or die("Couldn't make backup of the survey table. Please try again. The database reported the following error:<br />" . htmlspecialchars($connect->ErrorMsg()) . "<br /><br />Survey was not deactivated either.<br /><br /><a href='{$scriptname}?sid={$postsid}'>" . $clang->gT("Main Admin Screen") . "</a>");
$setsequence = "ALTER TABLE {$sNewTable} ALTER COLUMN id SET DEFAULT nextval('{$sNewTable}_id_seq'::regclass);";
$deactivateresult = $connect->Execute($setsequence) or die("Couldn't make backup of the survey table. Please try again. The database reported the following error:<br />" . htmlspecialchars($connect->ErrorMsg()) . "<br /><br />Survey was not deactivated either.<br /><br /><a href='{$scriptname}?sid={$postsid}'>" . $clang->gT("Main Admin Screen") . "</a>");
}
}
}
/***** Check for activate token tables with missing survey entry **/
$sQuery = db_select_tables_like("{$dbprefix}tokens\\_%");
$aResult = db_execute_num($sQuery) or safe_die("Couldn't get list of token tables from database<br />{$query}<br />" . $connect->ErrorMsg());
while ($aRow = $aResult->FetchRow()) {
$tablename = substr($aRow[0], strlen($dbprefix));
$iSurveyID = substr($tablename, strpos($tablename, '_') + 1);
$qquery = "SELECT sid FROM {$dbprefix}surveys WHERE sid='{$iSurveyID}'";
$qresult = $connect->Execute($qquery) or safe_die("Couldn't check survey table for sid<br />{$qquery}<br />" . $connect->ErrorMsg());
$qcount = $qresult->RecordCount();
if ($qcount == 0) {
$date = date('YmdHis') . rand(1, 1000);
$sOldTable = "tokens_{$iSurveyID}";
$sNewTable = "old_tokens_{$iSurveyID}_{$date}";
$deactivatequery = db_rename_table(db_table_name_nq($sOldTable), db_table_name_nq($sNewTable));
if ($databasetype == 'postgres') {
// If you deactivate a postgres table you have to rename the according sequence too and alter the id field to point to the changed sequence
$sOldTableJur = db_table_name_nq($sOldTable);
/**
* Return a boolean stating if the table(s) exist(s)
* Accepts '%' in names since it uses the 'like' statement
*
* @param mixed $table
*/
function db_tables_exist($table)
{
global $connect;
$surveyHasTokensTblQ = db_select_tables_like("{$table}");
$surveyHasTokensTblResult = db_execute_num($surveyHasTokensTblQ);
//Checked
if ($surveyHasTokensTblResult->RecordCount() >= 1) {
return TRUE;
} else {
return FALSE;
}
}
/**
* Function to activate a survey
* @global $dbprefix $dbprefix
* @global $connect $connect
* @global $clang $clang
* @param int $postsid
* @param int $surveyid
* @return string
*/
function activateSurvey($postsid, $surveyid, $scriptname = 'admin.php')
{
global $dbprefix, $connect, $clang, $databasetype, $databasetabletype, $uploaddir;
$createsurvey = '';
$activateoutput = '';
$createsurveytimings = '';
$createsurveydirectory = false;
//Check for any additional fields for this survey and create necessary fields (token and datestamp)
$pquery = "SELECT anonymized, allowregister, datestamp, ipaddr, refurl, savetimings FROM {$dbprefix}surveys WHERE sid={$postsid}";
$presult = db_execute_assoc($pquery);
$prow = $presult->FetchRow();
if ($prow['allowregister'] == "Y") {
$surveyallowsregistration = "TRUE";
}
if ($prow['savetimings'] == "Y") {
$savetimings = "TRUE";
}
//Get list of questions for the base language
$fieldmap = createFieldMap($surveyid, 'full', true, false, GetBaseLanguageFromSurveyID($surveyid));
// createFieldMap($surveyid, $styl, $force_refresh,$questionid, $sQuestionLanguage);
foreach ($fieldmap as $arow) {
if ($createsurvey != '') {
$createsurvey .= ",\n";
}
$createsurvey .= ' `' . $arow['fieldname'] . '`';
switch ($arow['type']) {
case 'startlanguage':
$createsurvey .= " C(20) NOTNULL";
break;
case 'id':
$createsurvey .= " I NOTNULL AUTO PRIMARY";
$createsurveytimings .= " `{$arow['fieldname']}` I NOTNULL PRIMARY,\n";
break;
case "startdate":
case "datestamp":
$createsurvey .= " T NOTNULL";
break;
case "submitdate":
$createsurvey .= " T";
break;
case "lastpage":
$createsurvey .= " I";
break;
case "N":
//NUMERICAL
$createsurvey .= " F";
break;
case "S":
//SHORT TEXT
if ($databasetype == 'mysql' || $databasetype == 'mysqli') {
$createsurvey .= " X";
} else {
$createsurvey .= " C(255)";
}
break;
case "L":
//LIST (RADIO)
//LIST (RADIO)
case "!":
//LIST (DROPDOWN)
//LIST (DROPDOWN)
case "M":
//Multiple choice
//Multiple choice
case "P":
//Multiple choice with comment
//Multiple choice with comment
case "O":
//DROPDOWN LIST WITH COMMENT
if ($arow['aid'] != 'other' && strpos($arow['aid'], 'comment') === false && strpos($arow['aid'], 'othercomment') === false) {
$createsurvey .= " C(5)";
} else {
$createsurvey .= " X";
}
break;
case "K":
// Multiple Numerical
$createsurvey .= " F";
break;
case "U":
//Huge text
//Huge text
case "Q":
//Multiple short text
//Multiple short text
case "T":
//LONG TEXT
//LONG TEXT
case ";":
//Multi Flexi
//Multi Flexi
case ":":
//Multi Flexi
$createsurvey .= " X";
break;
case "D":
//DATE
if ($databasetype == 'odbc_mssql' || $databasetype == 'odbtp' || $databasetype == 'mssql_n' || $databasetype == 'mssqlnative') {
$createsurvey .= " T";
} else {
$createsurvey .= " D";
}
break;
case "5":
//5 Point Choice
//5 Point Choice
case "G":
//Gender
//Gender
case "Y":
//YesNo
//YesNo
case "X":
//Boilerplate
$createsurvey .= " C(1)";
break;
case "I":
//Language switch
$createsurvey .= " C(20)";
break;
case "|":
$createsurveydirectory = true;
if (strpos($arow['fieldname'], "_")) {
$createsurvey .= " I1";
} else {
$createsurvey .= " X";
}
break;
case "ipaddress":
if ($prow['ipaddr'] == "Y") {
$createsurvey .= " X";
}
break;
case "url":
if ($prow['refurl'] == "Y") {
$createsurvey .= " X";
}
break;
case "token":
if ($prow['anonymized'] == "N") {
$createsurvey .= " C(36)";
}
break;
case '*':
// Equation
$createsurvey .= " X";
// could be anything, from numeric to a long message, so default to text
break;
default:
$createsurvey .= " C(5)";
}
}
$timingsfieldmap = createTimingsFieldMap($surveyid);
$createsurveytimings .= '`' . implode("` F DEFAULT '0',\n`", array_keys($timingsfieldmap)) . "` F DEFAULT '0'";
// If last question is of type MCABCEFHP^QKJR let's get rid of the ending coma in createsurvey
$createsurvey = rtrim($createsurvey, ",\n") . "\n";
// Does nothing if not ending with a comma
$tabname = "{$dbprefix}survey_{$postsid}";
# not using db_table_name as it quotes the table name (as does CreateTableSQL)
$taboptarray = array('mysql' => 'ENGINE=' . $databasetabletype . ' CHARACTER SET utf8 COLLATE utf8_unicode_ci', 'mysqli' => 'ENGINE=' . $databasetabletype . ' CHARACTER SET utf8 COLLATE utf8_unicode_ci');
$dict = NewDataDictionary($connect);
$sqlarray = $dict->CreateTableSQL($tabname, $createsurvey, $taboptarray);
if (isset($savetimings) && $savetimings == "TRUE") {
$tabnametimings = $tabname . '_timings';
$sqlarraytimings = $dict->CreateTableSQL($tabnametimings, $createsurveytimings, $taboptarray);
}
$execresult = $dict->ExecuteSQLArray($sqlarray, 1);
//queXS Addition - add an index on the token
$createtokenindex = $dict->CreateIndexSQL("{$tabname}_idx", $tabname, array('token'));
$dict->ExecuteSQLArray($createtokenindex, false) or safe_die("Failed to create token index<br />{$createtokenindex}<br /><br />" . $connect->ErrorMsg());
if ($execresult == 0 || $execresult == 1) {
$activateoutput .= "<br />\n<div class='messagebox ui-corner-all'>\n" . "<div class='header ui-widget-header'>" . $clang->gT("Activate Survey") . " ({$surveyid})</div>\n" . "<div class='warningheader'>" . $clang->gT("Survey could not be actived.") . "</div>\n" . "<p>" . $clang->gT("Database error:") . "\n <font color='red'>" . $connect->ErrorMsg() . "</font>\n" . "<pre>{$createsurvey}</pre>\n\n <a href='{$scriptname}?sid={$postsid}'>" . $clang->gT("Main Admin Screen") . "</a>\n</div>";
}
if ($execresult != 0 && $execresult != 1) {
$anquery = "SELECT autonumber_start FROM {$dbprefix}surveys WHERE sid={$postsid}";
if ($anresult = db_execute_assoc($anquery)) {
//if there is an autonumber_start field, start auto numbering here
while ($row = $anresult->FetchRow()) {
if ($row['autonumber_start'] > 0) {
if ($databasetype == 'odbc_mssql' || $databasetype == 'odbtp' || $databasetype == 'mssql_n' || $databasetype == 'mssqlnative') {
mssql_drop_primary_index('survey_' . $postsid);
mssql_drop_constraint('id', 'survey_' . $postsid);
$autonumberquery = "alter table {$dbprefix}survey_{$postsid} drop column id ";
$connect->Execute($autonumberquery);
$autonumberquery = "alter table {$dbprefix}survey_{$postsid} add [id] int identity({$row['autonumber_start']},1)";
$connect->Execute($autonumberquery);
} else {
$autonumberquery = "ALTER TABLE {$dbprefix}survey_{$postsid} AUTO_INCREMENT = " . $row['autonumber_start'];
$result = @$connect->Execute($autonumberquery);
}
}
}
if (isset($savetimings) && $savetimings == "TRUE") {
$dict->ExecuteSQLArray($sqlarraytimings, 1);
// create a timings table for this survey
}
}
$activateoutput .= "<br />\n<div class='messagebox ui-corner-all'>\n";
$activateoutput .= "<div class='header ui-widget-header'>" . $clang->gT("Activate Survey") . " ({$surveyid})</div>\n";
$activateoutput .= "<div class='successheader'>" . $clang->gT("Survey has been activated. Results table has been successfully created.") . "</div><br /><br />\n";
// create the survey directory where the uploaded files can be saved
if ($createsurveydirectory) {
if (!file_exists($uploaddir . "/surveys/" . $postsid . "/files")) {
if (!mkdir($uploaddir . "/surveys/" . $postsid . "/files", 0777, true)) {
$activateoutput .= "<div class='warningheader'>" . $clang->gT("The required directory for saving the uploaded files couldn't be created. Please check file premissions on the limesurvey/upload/surveys directory.") . "</div>";
} else {
file_put_contents($uploaddir . "/surveys/" . $postsid . "/files/index.html", '<html><head></head><body></body></html>');
}
}
}
$acquery = "UPDATE {$dbprefix}surveys SET active='Y' WHERE sid=" . $surveyid;
$acresult = $connect->Execute($acquery);
$query = db_select_tables_like("{$dbprefix}old\\_tokens\\_" . $surveyid . "\\_%");
$result = db_execute_num($query) or safe_die("Couldn't get old table list<br />" . $query . "<br />" . $connect->ErrorMsg());
$tcount = $result->RecordCount();
if ($tcount == 0) {
$sTokenActivationLink = "{$scriptname}?action=tokens&sid={$postsid}&createtable=Y";
} else {
$sTokenActivationLink = "{$scriptname}?action=tokens&sid={$postsid}";
}
if (isset($surveyallowsregistration) && $surveyallowsregistration == "TRUE") {
$activateoutput .= $clang->gT("This survey allows public registration. A token table must also be created.") . "<br /><br />\n";
$activateoutput .= "<input type='submit' value='" . $clang->gT("Initialise tokens") . "' onclick=\"" . get2post($sTokenActivationLink) . "\" />\n";
} else {
$activateoutput .= $clang->gT("This survey is now active, and responses can be recorded.") . "<br /><br />\n";
//queXS Removal
// $activateoutput .= "<strong>".$clang->gT("Open-access mode").":</strong> ".$clang->gT("No invitation code is needed to complete the survey.")."<br />".$clang->gT("You can switch to the closed-access mode by initialising a token table with the button below.")."<br /><br />\n";
// $activateoutput .= "<input type='submit' value='".$clang->gT("Switch to closed-access mode")."' onclick=\"".get2post($sTokenActivationLink)."\" />\n";
// $activateoutput .= "<input type='submit' value='".$clang->gT("No, thanks.")."' onclick=\"".get2post("$scriptname?sid={$postsid}")."\" />\n";
}
$activateoutput .= "</div><br /> \n";
$lsrcOutput = true;
}
if ($scriptname == 'lsrc') {
if ($lsrcOutput == true) {
return true;
} else {
return $activateoutput;
}
} else {
return $activateoutput;
}
}
