/**
* Function to activate a survey
* @param int $iSurveyID The Survey ID
* @param bool $simulate
* @return string
*/
function activateSurvey($iSurveyID, $simulate = false)
{
$createsurvey = '';
$activateoutput = '';
$createsurveytimings = '';
$fieldstiming = array();
$createsurveydirectory = false;
//Check for any additional fields for this survey and create necessary fields (token and datestamp)
$prow = Survey::model()->findByAttributes(array('sid' => $iSurveyID));
//Get list of questions for the base language
$fieldmap = createFieldMap($iSurveyID, 'full', true, false, getBaseLanguageFromSurveyID($iSurveyID));
$createsurvey = array();
foreach ($fieldmap as $j => $arow) {
switch ($arow['type']) {
case 'startlanguage':
$createsurvey[$arow['fieldname']] = "string(20) NOT NULL";
break;
case 'id':
$createsurvey[$arow['fieldname']] = "pk";
break;
case "startdate":
case "datestamp":
$createsurvey[$arow['fieldname']] = "datetime NOT NULL";
break;
case "submitdate":
$createsurvey[$arow['fieldname']] = "datetime";
break;
case "lastpage":
$createsurvey[$arow['fieldname']] = "integer";
break;
case "N":
//Numerical
//Numerical
case "K":
//Multiple Numerical
$createsurvey[$arow['fieldname']] = "decimal (30,10)";
break;
case "S":
//SHORT TEXT
$createsurvey[$arow['fieldname']] = "text";
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[$arow['fieldname']] = "string(5)";
} else {
$createsurvey[$arow['fieldname']] = "text";
}
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[$arow['fieldname']] = "text";
break;
case "D":
//DATE
$createsurvey[$arow['fieldname']] = "datetime";
break;
case "5":
//5 Point Choice
//5 Point Choice
case "G":
//Gender
//Gender
case "Y":
//YesNo
//YesNo
case "X":
//Boilerplate
$createsurvey[$arow['fieldname']] = "string(1)";
break;
case "I":
//Language switch
$createsurvey[$arow['fieldname']] = "string(20)";
break;
case "|":
$createsurveydirectory = true;
if (strpos($arow['fieldname'], "_")) {
$createsurvey[$arow['fieldname']] = "integer";
} else {
$createsurvey[$arow['fieldname']] = "text";
}
break;
case "ipaddress":
if ($prow->ipaddr == "Y") {
$createsurvey[$arow['fieldname']] = "string";
}
break;
case "url":
if ($prow->refurl == "Y") {
$createsurvey[$arow['fieldname']] = "string";
}
break;
case "token":
// Specify case sensitive collations for the token
$sCollation = '';
if (Yii::app()->db->driverName == 'mysqli' | Yii::app()->db->driverName == 'mysqli') {
$sCollation = " COLLATE 'utf8_bin'";
}
if (Yii::app()->db->driverName == 'sqlsrv' | Yii::app()->db->driverName == 'dblib' | Yii::app()->db->driverName == 'mssql') {
$sCollation = " COLLATE SQL_Latin1_General_CP1_CS_AS";
}
$createsurvey[$arow['fieldname']] = 'string(35)' . $sCollation;
break;
case '*':
// Equation
$createsurvey[$arow['fieldname']] = "text";
break;
default:
$createsurvey[$arow['fieldname']] = "string(5)";
}
if ($prow->anonymized == 'N' && !array_key_exists('token', $createsurvey)) {
$createsurvey['token'] = "string(36)";
}
if ($simulate) {
$tempTrim = trim($createsurvey);
$brackets = strpos($tempTrim, "(");
if ($brackets === false) {
$type = substr($tempTrim, 0, 2);
} else {
$type = substr($tempTrim, 0, 2);
}
$arrSim[] = array($type);
}
}
if ($simulate) {
return array('dbengine' => $CI->db->databasetabletype, 'dbtype' => Yii::app()->db->driverName, 'fields' => $arrSim);
}
// 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 = "{{survey_{$iSurveyID}}}";
Yii::app()->loadHelper("database");
try {
$execresult = Yii::app()->db->createCommand()->createTable($tabname, $createsurvey);
Yii::app()->db->schema->getTable($tabname, true);
// Refresh schema cache just in case the table existed in the past
} catch (CDbException $e) {
return array('error' => 'surveytablecreation');
}
try {
if (isset($createsurvey['token'])) {
Yii::app()->db->createCommand()->createIndex("idx_survey_token_{$iSurveyID}_" . rand(1, 50000), $tabname, 'token');
}
} catch (CDbException $e) {
}
$anquery = "SELECT autonumber_start FROM {{surveys}} WHERE sid={$iSurveyID}";
$iAutoNumberStart = Yii::app()->db->createCommand($anquery)->queryScalar();
//if there is an autonumber_start field, start auto numbering here
if ($iAutoNumberStart !== false && $iAutoNumberStart > 0) {
if (Yii::app()->db->driverName == 'mssql' || Yii::app()->db->driverName == 'sqlsrv' || Yii::app()->db->driverName == 'dblib') {
mssql_drop_primary_index('survey_' . $iSurveyID);
mssql_drop_constraint('id', 'survey_' . $iSurveyID);
$sQuery = "ALTER TABLE {{survey_{$iSurveyID}}} drop column id ";
Yii::app()->db->createCommand($sQuery)->execute();
$sQuery = "ALTER TABLE {{survey_{$iSurveyID}}} ADD [id] int identity({$iAutoNumberStart},1)";
Yii::app()->db->createCommand($sQuery)->execute();
// Add back the primaryKey
Yii::app()->db->createCommand()->addPrimaryKey('PRIMARY', '{{survey_' . $iSurveyID . '}}', 'id');
} elseif (Yii::app()->db->driverName == 'pgsql') {
$sQuery = "SELECT setval(pg_get_serial_sequence('{{survey_{$iSurveyID}}}', 'id'),{$iAutoNumberStart},false);";
$result = @Yii::app()->db->createCommand($sQuery)->execute();
} else {
$sQuery = "ALTER TABLE {{survey_{$iSurveyID}}} AUTO_INCREMENT = {$iAutoNumberStart}";
$result = @Yii::app()->db->createCommand($sQuery)->execute();
}
}
if ($prow->savetimings == "Y") {
$timingsfieldmap = createTimingsFieldMap($iSurveyID, "full", false, false, getBaseLanguageFromSurveyID($iSurveyID));
$column = array();
$column['id'] = $createsurvey['id'];
foreach ($timingsfieldmap as $field => $fielddata) {
$column[$field] = 'FLOAT';
}
$tabname = "{{survey_{$iSurveyID}_timings}}";
try {
$execresult = Yii::app()->db->createCommand()->createTable($tabname, $column);
Yii::app()->db->schema->getTable($tabname, true);
// Refresh schema cache just in case the table existed in the past
} catch (CDbException $e) {
return array('error' => 'timingstablecreation');
}
}
$aResult = array('status' => 'OK');
// create the survey directory where the uploaded files can be saved
if ($createsurveydirectory) {
if (!file_exists(Yii::app()->getConfig('uploaddir') . "/surveys/" . $iSurveyID . "/files")) {
if (!mkdir(Yii::app()->getConfig('uploaddir') . "/surveys/" . $iSurveyID . "/files", 0777, true)) {
$aResult['warning'] = 'nouploadsurveydir';
} else {
file_put_contents(Yii::app()->getConfig('uploaddir') . "/surveys/" . $iSurveyID . "/files/index.html", '<html><head></head><body></body></html>');
}
}
}
$acquery = "UPDATE {{surveys}} SET active='Y' WHERE sid=" . $iSurveyID;
$acresult = Yii::app()->db->createCommand($acquery)->query();
return $aResult;
}
/**
* Function to activate a survey
* @param int $iSurveyID The Survey ID
* @param bool $simulate
* @return string
*/
function activateSurvey($iSurveyID, $simulate = false)
{
$createsurvey = '';
$activateoutput = '';
$createsurveytimings = '';
$fieldstiming = array();
$createsurveydirectory = false;
//Check for any additional fields for this survey and create necessary fields (token and datestamp)
$prow = Survey::model()->findByAttributes(array('sid' => $iSurveyID));
//Get list of questions for the base language
$fieldmap = createFieldMap($iSurveyID, 'full', true, false, getBaseLanguageFromSurveyID($iSurveyID));
$createsurvey = array();
foreach ($fieldmap as $j => $arow) {
switch ($arow['type']) {
case 'startlanguage':
$createsurvey[$arow['fieldname']] = "VARCHAR(20) NOT NULL";
break;
case 'id':
$createsurvey[$arow['fieldname']] = "pk";
break;
case "startdate":
case "datestamp":
$createsurvey[$arow['fieldname']] = "datetime NOT NULL";
break;
case "submitdate":
$createsurvey[$arow['fieldname']] = "datetime";
break;
case "lastpage":
$createsurvey[$arow['fieldname']] = "integer";
break;
case "N":
//NUMERICAL
$createsurvey[$arow['fieldname']] = "decimal (30,10)";
break;
case "S":
//SHORT TEXT
if (Yii::app()->db->driverName == 'mysql' || Yii::app()->db->driverName == 'mysqli') {
$createsurvey[$arow['fieldname']] = "text";
} else {
$createsurvey[$arow['fieldname']] = "string";
}
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[$arow['fieldname']] = "VARCHAR(5)";
} else {
$createsurvey[$arow['fieldname']] = "text";
}
break;
case "K":
// Multiple Numerical
$createsurvey[$arow['fieldname']] = "float";
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[$arow['fieldname']] = "text";
break;
case "D":
//DATE
$createsurvey[$arow['fieldname']] = "datetime";
break;
case "5":
//5 Point Choice
//5 Point Choice
case "G":
//Gender
//Gender
case "Y":
//YesNo
//YesNo
case "X":
//Boilerplate
$createsurvey[$arow['fieldname']] = "VARCHAR(1)";
break;
case "I":
//Language switch
$createsurvey[$arow['fieldname']] = "VARCHAR(20)";
break;
case "|":
$createsurveydirectory = true;
if (strpos($arow['fieldname'], "_")) {
$createsurvey[$arow['fieldname']] = "integer";
} else {
$createsurvey[$arow['fieldname']] = "text";
}
break;
case "ipaddress":
if ($prow->ipaddr == "Y") {
$createsurvey[$arow['fieldname']] = "text";
}
break;
case "url":
if ($prow->refurl == "Y") {
$createsurvey[$arow['fieldname']] = "text";
}
break;
case "token":
if ($prow->anonymized == "N") {
$createsurvey[$arow['fieldname']] = "VARCHAR(36)";
}
break;
case '*':
// Equation
$createsurvey[$arow['fieldname']] = "text";
break;
default:
$createsurvey[$arow['fieldname']] = "VARCHAR(5)";
}
if ($simulate) {
$tempTrim = trim($createsurvey);
$brackets = strpos($tempTrim, "(");
if ($brackets === false) {
$type = substr($tempTrim, 0, 2);
} else {
$type = substr($tempTrim, 0, 2);
}
$arrSim[] = array($type);
}
}
if ($simulate) {
return array('dbengine' => $CI->db->databasetabletype, 'dbtype' => Yii::app()->db->driverName, 'fields' => $arrSim);
}
// 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 = "{{survey_{$iSurveyID}}}";
$command = new CDbCommand(Yii::app()->db);
try {
$execresult = $command->createTable($tabname, $createsurvey);
} catch (CDbException $e) {
return array('error' => 'surveytablecreation');
}
$anquery = "SELECT autonumber_start FROM {{surveys}} WHERE sid={$iSurveyID}";
if ($anresult = Yii::app()->db->createCommand($anquery)->query()->readAll()) {
//if there is an autonumber_start field, start auto numbering here
foreach ($anresult as $row) {
if ($row['autonumber_start'] > 0) {
if (Yii::app()->db->driverName == 'mssql' || Yii::app()->db->driverName == 'sqlsrv') {
mssql_drop_primary_index('survey_' . $iSurveyID);
mssql_drop_constraint('id', 'survey_' . $iSurveyID);
$autonumberquery = "alter table {{survey_{$iSurveyID}}} drop column id ";
Yii::app()->db->createCommand($autonumberquery)->execute();
$autonumberquery = "alter table {{survey_{$iSurveyID}}} add [id] int identity({$row['autonumber_start']},1)";
Yii::app()->db->createCommand($autonumberquery)->execute();
} elseif (Yii::app()->db->driverName == 'pgsql') {
} else {
$autonumberquery = "ALTER TABLE {{survey_{$iSurveyID}}} AUTO_INCREMENT = " . $row['autonumber_start'];
$result = @Yii::app()->db->createCommand($autonumberquery)->execute();
}
}
}
}
if ($prow->savetimings == "Y") {
$timingsfieldmap = createTimingsFieldMap($iSurveyID, "full", false, false, getBaseLanguageFromSurveyID($iSurveyID));
$column = array();
$column['id'] = $createsurvey['id'];
foreach ($timingsfieldmap as $field => $fielddata) {
$column[$field] = 'FLOAT';
}
$command = new CDbCommand(Yii::app()->db);
$tabname = "{{survey_{$iSurveyID}}}_timings";
try {
$execresult = $command->createTable($tabname, $column);
} catch (CDbException $e) {
return array('error' => 'timingstablecreation');
}
}
$aResult = array('status' => 'OK');
// create the survey directory where the uploaded files can be saved
if ($createsurveydirectory) {
if (!file_exists(Yii::app()->getConfig('uploaddir') . "/surveys/" . $iSurveyID . "/files")) {
if (!mkdir(Yii::app()->getConfig('uploaddir') . "/surveys/" . $iSurveyID . "/files", 0777, true)) {
$aResult['warning'] = 'nouploadsurveydir';
} else {
file_put_contents(Yii::app()->getConfig('uploaddir') . "/surveys/" . $iSurveyID . "/files/index.html", '<html><head></head><body></body></html>');
}
}
}
$acquery = "UPDATE {{surveys}} SET active='Y' WHERE sid=" . $iSurveyID;
$acresult = Yii::app()->db->createCommand($acquery)->query();
return $aResult;
}
function upgrade_survey_tables133a()
{
global $dbprefix, $connect, $modifyoutput;
mssql_drop_primary_index('assessments');
// add the new primary key
modify_database("", "ALTER TABLE [prefix_assessments] ADD CONSTRAINT pk_assessments_id_lang PRIMARY KEY ([id],[language])");
echo $modifyoutput;
flush();
ob_flush();
$surveyidquery = "SELECT sid,additional_languages FROM " . db_table_name('surveys');
$surveyidresult = db_execute_num($surveyidquery);
while ($sv = $surveyidresult->FetchRow()) {
FixLanguageConsistency($sv[0], $sv[1]);
}
}
function db_upgrade($oldversion)
{
/// This function does anything necessary to upgrade
/// older versions to match current functionality
global $modifyoutput, $dbprefix, $clang;
if ($oldversion < 111) {
// Language upgrades from version 110 to 111 since the language names did change
$oldnewlanguages = array('german_informal' => 'german-informal', 'cns' => 'cn-Hans', 'cnt' => 'cn-Hant', 'pt_br' => 'pt-BR', 'gr' => 'el', 'jp' => 'ja', 'si' => 'sl', 'se' => 'sv', 'vn' => 'vi');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update [prefix_answers] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_questions] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_groups] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_labels] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_language]='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_users] set [lang]='{$newlang} where lang='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update [prefix_labelsets] set [languages`='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
ob_flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update [prefix_surveys] set [additional_languages`='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
ob_flush();
}
modify_database("", "update [prefix_settings_global] set [stg_value]='111' where stg_name='DBVersion'");
echo $modifyoutput;
}
if ($oldversion < 112) {
//The size of the users_name field is now 64 char (20 char before version 112)
modify_database("", "ALTER TABLE [prefix_users] ALTER COLUMN [users_name] VARCHAR( 64 ) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='112' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 113) {
//No action needed
modify_database("", "update [prefix_settings_global] set [stg_value]='113' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 114) {
modify_database("", "ALTER TABLE [prefix_saved_control] ALTER COLUMN [email] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [adminemail] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ALTER COLUMN [email] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", 'INSERT INTO [prefix_settings_global] VALUES (\'SessionName\', \'$sessionname\');');
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='114' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 126) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [printanswers] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [listpublic] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
upgrade_survey_tables117();
upgrade_survey_tables118();
//119
modify_database("", "CREATE TABLE [prefix_quota] (\r\n\t\t\t\t\t\t [id] int NOT NULL IDENTITY (1,1),\r\n\t\t\t\t\t\t [sid] int,\r\n\t\t\t\t\t\t [name] varchar(255) ,\r\n\t\t\t\t\t\t [qlimit] int ,\r\n\t\t\t\t\t\t [action] int ,\r\n\t\t\t\t\t\t [active] int NOT NULL default '1',\r\n\t\t\t\t\t\t PRIMARY KEY ([id])\r\n\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_quota_members] (\r\n\t\t\t\t\t\t [id] int NOT NULL IDENTITY (1,1),\r\n\t\t\t\t\t\t [sid] int ,\r\n\t\t\t\t\t\t [qid] int ,\r\n\t\t\t\t\t\t [quota_id] int ,\r\n\t\t\t\t\t\t [code] varchar(5) ,\r\n\t\t\t\t\t\t PRIMARY KEY ([id])\r\n\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
ob_flush();
// Rename Norwegian language code from NO to NB
$oldnewlanguages = array('no' => 'nb');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update [prefix_answers] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_questions] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_groups] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_labels] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_language]='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_users] set [lang]='{$newlang}' where lang='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update [prefix_labelsets] set [languages]='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
ob_flush();
}
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update [prefix_surveys] set [additional_languages]='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
ob_flush();
}
}
modify_database("", "ALTER TABLE [prefix_surveys] ADD [htmlemail] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usecaptcha] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [tokenanswerspersistence] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [htmleditormode] CHAR(7) DEFAULT 'default'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_templates_rights] (\r\n\t\t\t\t\t\t [uid] int NOT NULL,\r\n\t\t\t\t\t\t [folder] varchar(255) NOT NULL,\r\n\t\t\t\t\t\t [use] int NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY ([uid],[folder])\r\n\t\t\t\t\t\t );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_templates] (\r\n\t\t\t\t\t\t [folder] varchar(255) NOT NULL,\r\n\t\t\t\t\t\t [creator] int NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY ([folder])\r\n\t\t\t\t\t\t );");
echo $modifyoutput;
flush();
ob_flush();
//123
modify_database("", "ALTER TABLE [prefix_conditions] ALTER COLUMN [value] VARCHAR(255)");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('title', 'labels');
modify_database("", "ALTER TABLE [prefix_labels] ALTER COLUMN [title] varchar(4000)");
echo $modifyoutput;
flush();
ob_flush();
//124
modify_database("", "ALTER TABLE [prefix_surveys] ADD [bounce_email] text");
echo $modifyoutput;
flush();
ob_flush();
//125
upgrade_token_tables125();
modify_database("", "EXEC sp_rename 'prefix_users.move_user','superadmin'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_users] SET [superadmin]=1 where ([create_survey]=1 AND [create_user]=1 AND [delete_user]=1 AND [configurator]=1)");
echo $modifyoutput;
flush();
ob_flush();
//126
modify_database("", "ALTER TABLE [prefix_questions] ADD [lid1] int NOT NULL DEFAULT '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_conditions] SET [method]='==' where ( [method] is null) or [method]='' or [method]='0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='126' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 127) {
modify_database("", "create index [answers_idx2] on [prefix_answers] ([sortorder])");
echo $modifyoutput;
modify_database("", "create index [assessments_idx2] on [prefix_assessments] ([sid])");
echo $modifyoutput;
modify_database("", "create index [assessments_idx3] on [prefix_assessments] ([gid])");
echo $modifyoutput;
modify_database("", "create index [conditions_idx2] on [prefix_conditions] ([qid])");
echo $modifyoutput;
modify_database("", "create index [conditions_idx3] on [prefix_conditions] ([cqid])");
echo $modifyoutput;
modify_database("", "create index [groups_idx2] on [prefix_groups] ([sid])");
echo $modifyoutput;
modify_database("", "create index [question_attributes_idx2] on [prefix_question_attributes] ([qid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx2] on [prefix_questions] ([sid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx3] on [prefix_questions] ([gid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx4] on [prefix_questions] ([type])");
echo $modifyoutput;
modify_database("", "create index [quota_idx2] on [prefix_quota] ([sid])");
echo $modifyoutput;
modify_database("", "create index [saved_control_idx2] on [prefix_saved_control] ([sid])");
echo $modifyoutput;
modify_database("", "create index [user_in_groups_idx1] on [prefix_user_in_groups] ([ugid], [uid])");
echo $modifyoutput;
modify_database("", "update [prefix_settings_global] set [stg_value]='127' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 128) {
upgrade_token_tables128();
modify_database("", "update [prefix_settings_global] set [stg_value]='128' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 129) {
//128
modify_database("", "ALTER TABLE [prefix_surveys] ADD [startdate] DATETIME");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usestartdate] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='129' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 130) {
modify_database("", "ALTER TABLE [prefix_conditions] ADD [scenario] int NOT NULL DEFAULT '1'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_conditions] SET [scenario]=1 where ( [scenario] is null) or [scenario]='' or [scenario]=0");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='130' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 131) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [publicstatistics] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='131' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 132) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [publicgraphs] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='132' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 133) {
modify_database("", "ALTER TABLE [prefix_users] ADD [one_time_pw] text");
echo $modifyoutput;
flush();
ob_flush();
// Add new assessment setting
modify_database("", "ALTER TABLE [prefix_surveys] ADD [assessments] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
// add new assessment value fields to answers & labels
modify_database("", "ALTER TABLE [prefix_answers] ADD [assessment_value] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_labels] ADD [assessment_value] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
// copy any valid codes from code field to assessment field
modify_database("", "update [prefix_answers] set [assessment_value]=CAST([code] as int)");
// no output here is intended
modify_database("", "update [prefix_labels] set [assessment_value]=CAST([code] as int)");
// no output here is intended
// activate assessment where assesment rules exist
modify_database("", "update [prefix_surveys] set [assessments]='Y' where [sid] in (SELECT [sid] FROM [prefix_assessments] group by [sid])");
echo $modifyoutput;
flush();
ob_flush();
// add language field to assessment table
modify_database("", "ALTER TABLE [prefix_assessments] ADD [language] varchar(20) NOT NULL default 'en'");
echo $modifyoutput;
flush();
ob_flush();
// update language field with default language of that particular survey
modify_database("", "update [prefix_assessments] set [language]=(select [language] from [prefix_surveys] where [sid]=[prefix_assessments].[sid])");
echo $modifyoutput;
flush();
ob_flush();
// copy assessment link to message since from now on we will have HTML assignment messages
modify_database("", "update [prefix_assessments] set [message]=cast([message] as varchar) +'<br /><a href=\"'+[link]+'\">'+[link]+'</a>'");
echo $modifyoutput;
flush();
ob_flush();
// drop the old link field
modify_database("", "ALTER TABLE [prefix_assessments] DROP COLUMN [link]");
echo $modifyoutput;
flush();
ob_flush();
// change the primary index to include language
mssql_drop_primary_index('assessments');
// add the new primary key
modify_database("", "ALTER TABLE [prefix_assessments] ADD CONSTRAINT pk_assessments_id_lang PRIMARY KEY ([id],[language])");
echo $modifyoutput;
flush();
ob_flush();
// Add new fields to survey language settings
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_url] varchar(255)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_endtext] text");
echo $modifyoutput;
flush();
ob_flush();
// copy old URL fields ot language specific entries
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_url]=(select [url] from [prefix_surveys] where [sid]=[prefix_surveys_languagesettings].[surveyls_survey_id])");
echo $modifyoutput;
flush();
ob_flush();
// drop old URL field
mssql_drop_constraint('url', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [url]");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='133' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 134) {
// Add new assessment setting
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usetokens] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('attribute1', 'surveys');
mssql_drop_constraint('attribute2', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] ADD [attributedescriptions] TEXT;");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [attribute1]");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [attribute2]");
echo $modifyoutput;
flush();
ob_flush();
upgrade_token_tables134();
modify_database("", "update [prefix_settings_global] set [stg_value]='134' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 135) {
mssql_drop_constraint('value', 'question_attributes');
modify_database("", "ALTER TABLE [prefix_question_attributes] ALTER COLUMN [value] text");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_answers] ALTER COLUMN [answer] varchar(8000)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='135' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 136) {
modify_database("", "ALTER TABLE[prefix_quota] ADD [autoload_url] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_quota_languagesettings] (\r\n \t\t\t\t\t\t\t[quotals_id] int NOT NULL IDENTITY (1,1),\r\n\t\t\t\t\t\t\t[quotals_quota_id] int,\r\n \t\t\t\t\t\t\t[quotals_language] varchar(45) NOT NULL default 'en',\r\n \t\t\t\t\t\t\t[quotals_name] varchar(255),\r\n \t\t\t\t\t\t\t[quotals_message] text,\r\n \t\t\t\t\t\t\t[quotals_url] varchar(255),\r\n \t\t\t\t\t\t\t[quotals_urldescrip] varchar(255),\r\n \t\t\t\t\t\t\tPRIMARY KEY ([quotals_id])\r\n\t\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='136' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 137) {
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_dateformat] int NOT NULL default '1'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [dateformat] int NOT NULL default '1'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set startdate=null where usestartdate='N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set expires=null where useexpiry='N'");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('usestartdate', 'surveys');
mssql_drop_constraint('useexpiry', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN usestartdate");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN useexpiry");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='137' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 138) {
modify_database("", "ALTER TABLE [prefix_quota_members] ALTER COLUMN [code] VARCHAR(11) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='138' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 139) {
upgrade_survey_tables139();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='139' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 140) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [emailresponseto] text");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='140' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 141) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [tokenlength] tinyint NOT NULL default '15'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='141' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 142) {
upgrade_question_attributes142();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [startdate] datetime NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [expires] datetime NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_question_attributes] SET [value]='0' WHERE cast([value] as varchar)='false'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_question_attributes] SET [value]='1' WHERE cast([value] as varchar)='true'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='142' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 143) {
modify_database("", "ALTER TABLE [prefix_questions] ADD parent_qid integer NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_answers] ADD scale_id tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_questions] ADD scale_id tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_questions] ADD same_default tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_primary_index('answers');
modify_database("", "ALTER TABLE [prefix_answers] ADD CONSTRAINT pk_answers_qcls PRIMARY KEY ([qid],[code],[language],[scale_id])");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_defaultvalues] (\r\n [qid] integer NOT NULL default '0',\r\n [scale_id] tinyint NOT NULL default '0',\r\n [sqid] integer NOT NULL default '0',\r\n [language] varchar(20) NOT NULL,\r\n [specialtype] varchar(20) NOT NULL default '',\r\n [defaultvalue] text,\r\n CONSTRAINT pk_defaultvalues_qlss PRIMARY KEY ([qid] , [scale_id], [language], [specialtype], [sqid]))");
echo $modifyoutput;
flush();
ob_flush();
// -Move all 'answers' that are subquestions to the questions table
// -Move all 'labels' that are answers to the answers table
// -Transscribe the default values where applicable
// -Move default values from answers to questions
upgrade_tables143();
mssql_drop_constraint('default_value', 'answers');
modify_database("", "ALTER TABLE [prefix_answers] DROP COLUMN [default_value]");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('lid', 'questions');
modify_database("", "ALTER TABLE [prefix_questions] DROP COLUMN lid");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('lid1', 'questions');
modify_database("", "ALTER TABLE [prefix_questions] DROP COLUMN lid1");
echo $modifyoutput;
flush();
ob_flush();
// add field for timings and table for extended conditions
modify_database("", "ALTER TABLE [prefix_surveys] ADD savetimings char(1) default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_sessions(\r\n sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',\r\n expiry DATETIME NOT NULL ,\r\n expireref VARCHAR( 250 ) DEFAULT '',\r\n created DATETIME NOT NULL ,\r\n modified DATETIME NOT NULL ,\r\n sessdata text,\r\n CONSTRAINT pk_sessions_sesskey PRIMARY KEY ( [sesskey] ))");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "create index [idx_expiry] on [prefix_sessions] ([expiry])");
echo $modifyoutput;
modify_database("", "create index [idx_expireref] on [prefix_sessions] ([expireref])");
echo $modifyoutput;
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='143' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 145) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD showxquestions CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showgroupinfo CHAR(1) NULL default 'B'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD shownoanswer CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showqnumcode CHAR(1) NULL default 'X'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bouncetime BIGINT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceprocessing VARCHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccounttype VARCHAR(4) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccounthost VARCHAR(200) NULL ");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccountpass VARCHAR(100) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccountencryption VARCHAR(3) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccountuser VARCHAR(200) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showwelcome CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showprogress CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD allowjumps CHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD navigationdelay tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD nokeyboard CHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD alloweditaftercompletion CHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_survey_permissions] (\r\n [sid] INT NOT NULL,\r\n [uid] INT NOT NULL,\r\n [permission] VARCHAR(20) NOT NULL,\r\n [create_p] TINYINT NOT NULL default '0',\r\n [read_p] TINYINT NOT NULL default '0',\r\n [update_p] TINYINT NOT NULL default '0',\r\n [delete_p] TINYINT NOT NULL default '0',\r\n [import_p] TINYINT NOT NULL default '0',\r\n [export_p] TINYINT NOT NULL default '0',\r\n PRIMARY KEY ([sid], [uid],[permission])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
upgrade_surveypermissions_table145();
modify_database("", "DROP TABLE [prefix_surveys_rights]");
echo $modifyoutput;
flush();
ob_flush();
// Add new fields for email templates
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD\r\n email_admin_notification_subj VARCHAR(255) NULL,\r\n email_admin_notification TEXT NULL,\r\n email_admin_responses_subj VARCHAR(255) NULL,\r\n email_admin_responses TEXT NULL");
//Add index to questions table to speed up subquestions
modify_database("", "create index [parent_qid_idx] on [prefix_questions] ([parent_qid])");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD emailnotificationto text DEFAULT NULL");
echo $modifyoutput;
flush();
ob_flush();
upgrade_survey_table145();
mssql_drop_constraint('notification', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [notification]");
echo $modifyoutput;
flush();
ob_flush();
// modify length of method in conditions
modify_database("", "ALTER TABLE [prefix_conditions] ALTER COLUMN [method] CHAR( 5 ) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
//Add index to questions table to speed up subquestions
modify_database("", "create index [parent_qid] on [prefix_questions] ([parent_qid])");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE prefix_surveys set [private]='N' where [private] is NULL;");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "EXEC sp_rename 'prefix_surveys.private','anonymized'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [anonymized] char(1) NOT NULL;");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('anonymized', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] ADD CONSTRAINT DF_surveys_anonymized DEFAULT 'N' FOR [anonymized];");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_failed_login_attempts] (\r\n [id] INT NOT NULL IDENTITY (1,1) PRIMARY KEY,\r\n [ip] varchar(37) NOT NULL,\r\n [last_attempt] varchar(20) NOT NULL,\r\n [number_attempts] int NOT NULL );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_numberformat] INT default 0 NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
upgrade_token_tables145();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='145' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 146) {
upgrade_timing_tables146();
modify_database("", "INSERT into [prefix_survey_permissions] (sid,uid,permission,read_p,update_p) SELECT sid,owner_id,'translations','1','1' from [prefix_surveys]");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='146' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 147) {
modify_database("", "ALTER TABLE [prefix_users] ADD templateeditormode VARCHAR(7) NOT NULL default 'default'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD questionselectormode VARCHAR(7) NOT NULL default 'default'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='147' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 148) {
modify_database("", "CREATE TABLE [prefix_participants] (\r\n [participant_id] varchar(50) NOT NULL,\r\n [firstname] varchar(40) NOT NULL,\r\n [lastname] varchar(40) NOT NULL,\r\n [email] varchar(80) NOT NULL,\r\n [language] varchar(2) NOT NULL,\r\n [blacklisted] varchar(1) NOT NULL,\r\n [owner_uid] int(20) NOT NULL,\r\n PRIMARY KEY ([participant_id])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_participant_attribute] (\r\n [participant_id] varchar(50) NOT NULL,\r\n [attribute_id] int(11) NOT NULL,\r\n [value] varchar(50) NOT NULL,\r\n PRIMARY KEY ([participant_id],[attribute_id])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_participant_attribute_names] (\r\n [attribute_id] int(11) NOT NULL AUTO_INCREMENT,\r\n [attribute_type] varchar(4) NOT NULL,\r\n [visible] char(5) NOT NULL,\r\n PRIMARY KEY ([attribute_id],[attribute_type])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_participant_attribute_names_lang] (\r\n [attribute_id] int(11) NOT NULL,\r\n [attribute_name] varchar(30) NOT NULL,\r\n [lang] varchar(20) NOT NULL,\r\n PRIMARY KEY ([attribute_id],[lang])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_participant_attribute_values] (\r\n [attribute_id] int(11) NOT NULL,\r\n [value_id] int(11) NOT NULL AUTO_INCREMENT,\r\n [value] varchar(20) NOT NULL,\r\n PRIMARY KEY ([value_id])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_participant_shares] (\r\n [participant_id] varchar(50) NOT NULL,\r\n [share_uid] int(11) NOT NULL,\r\n [date_added] datetime,\r\n [can_edit] text NOT NULL,\r\n PRIMARY KEY ([participant_id],[share_uid])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_survey_links] (\r\n [participant_id] varchar(50) NOT NULL,\r\n [token_id] int(11) NOT NULL,\r\n [survey_id] int(11) NOT NULL,\r\n [date_created] datetime,\r\n PRIMARY KEY ([participant_id],[token_id],[survey_id])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [participant_panel] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
// Add language field to question_attributes table
modify_database("", "ALTER TABLE [prefix_question_attributes] ADD [language] varchar(20)");
echo $modifyoutput;
flush();
ob_flush();
upgrade_question_attributes148();
fixSubquestions();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='148' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 149) {
modify_database("", "CREATE TABLE [prefix_survey_url_parameters] (\r\n [id] int(9) NOT NULL AUTO_INCREMENT,\r\n [sid] int(10) NOT NULL,\r\n [parameter] varchar(50) NOT NULL,\r\n [targetqid] int(10) NULL,\r\n [targetsqid] int(10) NULL,\r\n PRIMARY KEY ([id])\r\n );");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='149' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 150) {
modify_database("", "ALTER TABLE [prefix_questions] ADD [relevance] TEXT;");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='150' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 151) {
modify_database("", "ALTER TABLE [prefix_groups] ADD [randomization_group] VARCHAR(20) NOT NULL DEFAULT '';");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='151' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 152) {
modify_database("", "CREATE INDEX [question_attributes_idx3] ON [prefix_question_attributes] ([attribute]);");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='152' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 153) {
modify_database("", "CREATE TABLE [prefix_expression_errors] (\r\n [id] integer NOT NULL AUTO_INCREMENT,\r\n [errortime] varchar(50) DEFAULT NULL,\r\n [sid] integer DEFAULT NULL,\r\n [gid] integer DEFAULT NULL,\r\n [qid] integer DEFAULT NULL,\r\n [gseq] integer DEFAULT NULL,\r\n [qseq] integer DEFAULT NULL,\r\n [type] varchar(50) ,\r\n [eqn] text,\r\n [prettyprint] text,\r\n PRIMARY KEY ([id])\r\n );");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='153' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 154) {
modify_database("", "ALTER TABLE [prefix_groups] ADD [grelevance] text DEFAULT NULL;");
echo $modifyoutput;
flush();
@ob_flush();
LimeExpressionManager::UpgradeConditionsToRelevance();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='154' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 155) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [googleanalyticsstyle] char(1) DEFAULT NULL;");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [googleanalyticsapikey] varchar(25) DEFAULT NULL;");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "EXEC sp_rename 'prefix_surveys.showXquestions','showxquestions'");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='155' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
echo '<br /><br />' . sprintf($clang->gT('Database update finished (%s)'), date('Y-m-d H:i:s')) . '<br />';
return true;
}
/**
* 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";
}
//strip trailing comma and new line feed (if any)
$createsurvey = rtrim($createsurvey, ",\n");
//strip trailing comma and new line feed (if any)
$createsurvey = rtrim($createsurvey, ",\n");
//Get list of questions for the base language
$fieldmap = createFieldMap($surveyid);
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
$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;
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);
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);
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("{$scriptname}?action=tokens&sid={$postsid}&createtable=Y") . "\" />\n";
} else {
$activateoutput .= $clang->gT("This survey is now active, and responses can be recorded.") . "<br /><br />\n";
$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("{$scriptname}?action=tokens&sid={$postsid}&createtable=Y") . "\" />\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;
}
}
# 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);
$execresult = $dict->ExecuteSQLArray($sqlarray, 1);
if ($execresult == 0 || $execresult == 1) {
$activateoutput .= "<br />\n<div class='messagebox'>\n" . "<div class='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);
}
}
}
}
$activateoutput .= "<br />\n<div class='messagebox'>\n";
$activateoutput .= "<div class='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";
