/**
* An initialization method that implementing classes can override to gain access
* to any information about the survey, language, or formatting options they
* may need for setup.
*
* @param Survey $oSurvey
* @param mixed $sLanguageCode
* @param FormattingOptions $oOptions
*/
public function init(SurveyObj $oSurvey, $sLanguageCode, FormattingOptions $oOptions)
{
$this->languageCode = $sLanguageCode;
$this->translator = new Translator();
$this->filename = Yii::app()->getConfig("tempdir") . DIRECTORY_SEPARATOR . randomChars(40);
$this->webfilename = 'results-survey' . $oSurvey->id;
}
/**
* An initialization method that implementing classes can override to gain access
* to any information about the survey, language, or formatting options they
* may need for setup.
*
* @param Survey $oSurvey
* @param mixed $sLanguageCode
* @param FormattingOptions $oOptions
*/
public function init(SurveyObj $oSurvey, $sLanguageCode, FormattingOptions $oOptions)
{
$this->languageCode = $sLanguageCode;
$this->translator = new Translator();
if ($oOptions->output == 'file') {
$sRandomFileName = Yii::app()->getConfig("tempdir") . DIRECTORY_SEPARATOR . randomChars(40);
$this->filename = $sRandomFileName;
}
}
/**
* questiongroup::import()
* Function responsible to import a question group.
*
* @access public
* @return void
*/
function import()
{
$action = $_POST['action'];
$iSurveyID = $surveyid = $aData['surveyid'] = (int) $_POST['sid'];
if (!Permission::model()->hasSurveyPermission($surveyid, 'surveycontent', 'import')) {
Yii::app()->user->setFlash('error', gT("Access denied"));
$this->getController()->redirect(array('admin/survey/sa/listquestiongroups/surveyid/' . $surveyid));
}
if ($action == 'importgroup') {
$importgroup = "\n";
$importgroup .= "\n";
$sFullFilepath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . randomChars(20);
$aPathInfo = pathinfo($_FILES['the_file']['name']);
$sExtension = $aPathInfo['extension'];
if ($_FILES['the_file']['error'] == 1 || $_FILES['the_file']['error'] == 2) {
$fatalerror = sprintf(gT("Sorry, this file is too large. Only files up to %01.2f MB are allowed."), getMaximumFileUploadSize() / 1024 / 1024) . '<br>';
} elseif (!@move_uploaded_file($_FILES['the_file']['tmp_name'], $sFullFilepath)) {
$fatalerror = gT("An error occurred uploading your file. This may be caused by incorrect permissions for the application /tmp folder.");
}
// validate that we have a SID
if (!returnGlobal('sid')) {
$fatalerror .= gT("No SID (Survey) has been provided. Cannot import question.");
}
if (isset($fatalerror)) {
@unlink($sFullFilepath);
Yii::app()->user->setFlash('error', $fatalerror);
$this->getController()->redirect(array('admin/questiongroups/sa/importview/surveyid/' . $surveyid));
}
Yii::app()->loadHelper('admin/import');
// IF WE GOT THIS FAR, THEN THE FILE HAS BEEN UPLOADED SUCCESFULLY
if (strtolower($sExtension) == 'lsg') {
$aImportResults = XMLImportGroup($sFullFilepath, $iSurveyID);
} else {
Yii::app()->user->setFlash('error', gT("Unknown file extension"));
$this->getController()->redirect(array('admin/questiongroups/sa/importview/surveyid/' . $surveyid));
}
LimeExpressionManager::SetDirtyFlag();
// so refreshes syntax highlighting
fixLanguageConsistency($iSurveyID);
if (isset($aImportResults['fatalerror'])) {
unlink($sFullFilepath);
Yii::app()->user->setFlash('error', $aImportResults['fatalerror']);
$this->getController()->redirect(array('admin/questiongroups/sa/importview/surveyid/' . $surveyid));
}
unlink($sFullFilepath);
$aData['display'] = $importgroup;
$aData['surveyid'] = $iSurveyID;
$aData['aImportResults'] = $aImportResults;
$aData['sExtension'] = $sExtension;
//$aData['display']['menu_bars']['surveysummary'] = 'importgroup';
$aData['sidemenu']['state'] = false;
$surveyinfo = Survey::model()->findByPk($iSurveyID)->surveyinfo;
$aData['title_bar']['title'] = $surveyinfo['surveyls_title'] . "(" . gT("ID") . ":" . $iSurveyID . ")";
$this->_renderWrappedTemplate('survey/QuestionGroups', 'import_view', $aData);
}
}
public function generateToken()
{
$length = $this->survey->tokenlength;
$this->token = randomChars($length);
$counter = 0;
while (!$this->validate('token')) {
$this->token = randomChars($length);
$counter++;
// This is extremely unlikely.
if ($counter > 10) {
throw new CHttpException(500, 'Failed to create unique token in 10 attempts.');
}
}
}
/**
* Function responsible to import a question.
*
* @access public
* @return void
*/
public function import()
{
$action = returnGlobal('action');
$surveyid = returnGlobal('sid');
$gid = returnGlobal('gid');
$clang = $this->getController()->lang;
$aViewUrls = array();
$aData['display']['menu_bars']['surveysummary'] = 'viewquestion';
$aData['display']['menu_bars']['gid_action'] = 'viewgroup';
if ($action == 'importquestion') {
$sFullFilepath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . randomChars(20);
$sExtension = pathinfo($_FILES['the_file']['name'], PATHINFO_EXTENSION);
if (!@move_uploaded_file($_FILES['the_file']['tmp_name'], $sFullFilepath)) {
$fatalerror = sprintf($clang->gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), Yii::app()->getConfig('tempdir'));
}
// validate that we have a SID and GID
if (!$surveyid) {
$fatalerror .= $clang->gT("No SID (Survey) has been provided. Cannot import question.");
}
if (!$gid) {
$fatalerror .= $clang->gT("No GID (Group) has been provided. Cannot import question");
}
if (isset($fatalerror)) {
unlink($sFullFilepath);
$this->getController()->error($fatalerror);
}
// IF WE GOT THIS FAR, THEN THE FILE HAS BEEN UPLOADED SUCCESFULLY
Yii::app()->loadHelper('admin/import');
if (strtolower($sExtension) == 'csv') {
$aImportResults = CSVImportQuestion($sFullFilepath, $surveyid, $gid);
} elseif (strtolower($sExtension) == 'lsq') {
$aImportResults = XMLImportQuestion($sFullFilepath, $surveyid, $gid);
} else {
$this->getController()->error($clang->gT('Unknown file extension'));
}
fixLanguageConsistency($surveyid);
if (isset($aImportResults['fatalerror'])) {
unlink($sFullFilepath);
$this->getController()->error($aImportResults['fatalerror']);
}
unlink($sFullFilepath);
$aData['aImportResults'] = $aImportResults;
$aData['surveyid'] = $surveyid;
$aData['gid'] = $gid;
$aData['sExtension'] = $sExtension;
$aViewUrls[] = 'import_view';
}
$this->_renderWrappedTemplate('survey/Question', $aViewUrls, $aData);
}
function PrepareEditorScript($load = false, $controller = null)
{
$clang = Yii::app()->lang;
$data['clang'] = $clang;
$data['sKCFinderCSRFToken'] = $_SESSION['kcfinder_csrftoken'] = randomChars(128);
App()->getClientScript()->registerCoreScript('ckeditor');
if ($controller == null) {
$controller = Yii::app()->getController();
}
if ($load == false) {
echo $controller->renderPartial('/admin/survey/prepareEditorScript_view', $data, true);
} else {
return $controller->renderPartial('/admin/survey/prepareEditorScript_view', $data);
}
}
/**
* questiongroup::import()
* Function responsible to import a question group.
*
* @access public
* @return void
*/
function import()
{
$action = $_POST['action'];
$surveyid = $_POST['sid'];
$clang = $this->getController()->lang;
if ($action == 'importgroup') {
$importgroup = "\n";
$importgroup .= "\n";
$sFullFilepath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . randomChars(20);
$aPathInfo = pathinfo($_FILES['the_file']['name']);
$sExtension = $aPathInfo['extension'];
if (!@move_uploaded_file($_FILES['the_file']['tmp_name'], $sFullFilepath)) {
$fatalerror = sprintf($clang->gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), $this->config->item('tempdir'));
}
// validate that we have a SID
if (!returnGlobal('sid')) {
$fatalerror .= $clang->gT("No SID (Survey) has been provided. Cannot import question.");
}
if (isset($fatalerror)) {
@unlink($sFullFilepath);
$this->getController()->error($fatalerror);
}
Yii::app()->loadHelper('admin/import');
// IF WE GOT THIS FAR, THEN THE FILE HAS BEEN UPLOADED SUCCESFULLY
if (strtolower($sExtension) == 'csv') {
$aImportResults = CSVImportGroup($sFullFilepath, $surveyid);
} elseif (strtolower($sExtension) == 'lsg') {
$aImportResults = XMLImportGroup($sFullFilepath, $surveyid);
} else {
$this->getController()->error('Unknown file extension');
}
LimeExpressionManager::SetDirtyFlag();
// so refreshes syntax highlighting
fixLanguageConsistency($surveyid);
if (isset($aImportResults['fatalerror'])) {
unlink($sFullFilepath);
$this->getController()->error($aImportResults['fatalerror']);
}
unlink($sFullFilepath);
$aData['display'] = $importgroup;
$aData['surveyid'] = $surveyid;
$aData['aImportResults'] = $aImportResults;
$aData['sExtension'] = $sExtension;
//$aData['display']['menu_bars']['surveysummary'] = 'importgroup';
$this->_renderWrappedTemplate('survey/QuestionGroups', 'import_view', $aData);
// TMSW Condition->Relevance: call LEM->ConvertConditionsToRelevance() after import
}
}
/**
* @param string $sTablename
*/
function addPrimaryKey($sTablename, $aColumns)
{
return Yii::app()->db->createCommand()->addPrimaryKey('PK_' . $sTablename . '_' . randomChars(12, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'), '{{' . $sTablename . '}}', $aColumns);
}
/**
* register::index()
* Process register form data and take appropriate action
* @return
*/
function actionIndex($surveyid = null)
{
Yii::app()->loadHelper('database');
Yii::app()->loadHelper('replacements');
$postlang = Yii::app()->request->getPost('lang');
if ($surveyid == null) {
$surveyid = Yii::app()->request->getPost('sid');
}
if (!$surveyid) {
Yii::app()->request->redirect(Yii::app()->baseUrl);
}
// Get passed language from form, so that we dont loose this!
if (!isset($postlang) || $postlang == "" || !$postlang) {
$baselang = Survey::model()->findByPk($surveyid)->language;
Yii::import('application.libraries.Limesurvey_lang');
Yii::app()->lang = new Limesurvey_lang($baselang);
$clang = Yii::app()->lang;
} else {
Yii::import('application.libraries.Limesurvey_lang');
Yii::app()->lang = new Limesurvey_lang($postlang);
$clang = Yii::app()->lang;
$baselang = $postlang;
}
$thissurvey = getSurveyInfo($surveyid, $baselang);
$register_errormsg = "";
// Check the security question's answer
if (function_exists("ImageCreate") && isCaptchaEnabled('registrationscreen', $thissurvey['usecaptcha'])) {
if (!isset($_POST['loadsecurity']) || !isset($_SESSION['survey_' . $surveyid]['secanswer']) || Yii::app()->request->getPost('loadsecurity') != $_SESSION['survey_' . $surveyid]['secanswer']) {
$register_errormsg .= $clang->gT("The answer to the security question is incorrect.") . "<br />\n";
}
}
//Check that the email is a valid style address
if (!validateEmailAddress(Yii::app()->request->getPost('register_email'))) {
$register_errormsg .= $clang->gT("The email you used is not valid. Please try again.");
}
// Check for additional fields
$attributeinsertdata = array();
foreach (GetParticipantAttributes($surveyid) as $field => $data) {
if (empty($data['show_register']) || $data['show_register'] != 'Y') {
continue;
}
$value = sanitize_xss_string(Yii::app()->request->getPost('register_' . $field));
if (trim($value) == '' && $data['mandatory'] == 'Y') {
$register_errormsg .= sprintf($clang->gT("%s cannot be left empty"), $thissurvey['attributecaptions'][$field]);
}
$attributeinsertdata[$field] = $value;
}
if ($register_errormsg != "") {
$_SESSION['survey_' . $surveyid]['register_errormsg'] = $register_errormsg;
Yii::app()->request->redirect(Yii::app()->createUrl('survey/index/sid/' . $surveyid));
}
//Check if this email already exists in token database
$query = "SELECT email FROM {{tokens_{$surveyid}}}\n" . "WHERE email = '" . sanitize_email(Yii::app()->request->getPost('register_email')) . "'";
$usrow = Yii::app()->db->createCommand($query)->queryRow();
if ($usrow) {
$register_errormsg = $clang->gT("The email you used has already been registered.");
$_SESSION['survey_' . $surveyid]['register_errormsg'] = $register_errormsg;
Yii::app()->request->redirect(Yii::app()->createUrl('survey/index/sid/' . $surveyid));
//include "index.php";
//exit;
}
$mayinsert = false;
// Get the survey settings for token length
//$this->load->model("surveys_model");
$tlresult = Survey::model()->findAllByAttributes(array("sid" => $surveyid));
if (isset($tlresult[0])) {
$tlrow = $tlresult[0];
} else {
$tlrow = $tlresult;
}
$tokenlength = $tlrow['tokenlength'];
//if tokenlength is not set or there are other problems use the default value (15)
if (!isset($tokenlength) || $tokenlength == '') {
$tokenlength = 15;
}
while ($mayinsert != true) {
$newtoken = randomChars($tokenlength);
$ntquery = "SELECT * FROM {{tokens_{$surveyid}}} WHERE token='{$newtoken}'";
$usrow = Yii::app()->db->createCommand($ntquery)->queryRow();
if (!$usrow) {
$mayinsert = true;
}
}
$postfirstname = sanitize_xss_string(strip_tags(Yii::app()->request->getPost('register_firstname')));
$postlastname = sanitize_xss_string(strip_tags(Yii::app()->request->getPost('register_lastname')));
$starttime = sanitize_xss_string(Yii::app()->request->getPost('startdate'));
$endtime = sanitize_xss_string(Yii::app()->request->getPost('enddate'));
/*$postattribute1=sanitize_xss_string(strip_tags(returnGlobal('register_attribute1')));
$postattribute2=sanitize_xss_string(strip_tags(returnGlobal('register_attribute2'))); */
// Insert new entry into tokens db
Tokens_dynamic::sid($thissurvey['sid']);
$token = new Tokens_dynamic();
$token->firstname = $postfirstname;
$token->lastname = $postlastname;
$token->email = Yii::app()->request->getPost('register_email');
$token->emailstatus = 'OK';
$token->token = $newtoken;
if ($starttime && $endtime) {
$token->validfrom = $starttime;
$token->validuntil = $endtime;
}
foreach ($attributeinsertdata as $k => $v) {
$token->{$k} = $v;
}
$result = $token->save();
/**
$result = $connect->Execute($query, array($postfirstname,
$postlastname,
returnGlobal('register_email'),
'OK',
$newtoken)
// $postattribute1, $postattribute2)
) or safeDie ($query."<br />".$connect->ErrorMsg()); //Checked - According to adodb docs the bound variables are quoted automatically
*/
$tid = getLastInsertID($token->tableName());
$fieldsarray["{ADMINNAME}"] = $thissurvey['adminname'];
$fieldsarray["{ADMINEMAIL}"] = $thissurvey['adminemail'];
$fieldsarray["{SURVEYNAME}"] = $thissurvey['name'];
$fieldsarray["{SURVEYDESCRIPTION}"] = $thissurvey['description'];
$fieldsarray["{FIRSTNAME}"] = $postfirstname;
$fieldsarray["{LASTNAME}"] = $postlastname;
$fieldsarray["{EXPIRY}"] = $thissurvey["expiry"];
$message = $thissurvey['email_register'];
$subject = $thissurvey['email_register_subj'];
$from = "{$thissurvey['adminname']} <{$thissurvey['adminemail']}>";
if (getEmailFormat($surveyid) == 'html') {
$useHtmlEmail = true;
$surveylink = $this->createAbsoluteUrl($surveyid . '/lang-' . $baselang . '/tk-' . $newtoken);
$optoutlink = $this->createAbsoluteUrl('optout/local/' . $surveyid . '/' . $baselang . '/' . $newtoken);
$optinlink = $this->createAbsoluteUrl('optin/local/' . $surveyid . '/' . $baselang . '/' . $newtoken);
$fieldsarray["{SURVEYURL}"] = "<a href='{$surveylink}'>" . $surveylink . "</a>";
$fieldsarray["{OPTOUTURL}"] = "<a href='{$optoutlink}'>" . $optoutlink . "</a>";
$fieldsarray["{OPTINURL}"] = "<a href='{$optinlink}'>" . $optinlink . "</a>";
} else {
$useHtmlEmail = false;
$fieldsarray["{SURVEYURL}"] = $this->createAbsoluteUrl('' . $surveyid . '/lang-' . $baselang . '/tk-' . $newtoken);
$fieldsarray["{OPTOUTURL}"] = $this->createAbsoluteUrl('optout/local/' . $surveyid . '/' . $baselang . '/' . $newtoken);
$fieldsarray["{OPTINURL}"] = $this->createAbsoluteUrl('optin/local/' . $surveyid . '/' . $baselang . '/' . $newtoken);
}
$message = ReplaceFields($message, $fieldsarray);
$subject = ReplaceFields($subject, $fieldsarray);
$html = "";
//Set variable
$sitename = Yii::app()->getConfig('sitename');
if (SendEmailMessage($message, $subject, Yii::app()->request->getPost('register_email'), $from, $sitename, $useHtmlEmail, getBounceEmail($surveyid))) {
// TLR change to put date into sent
$today = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i", Yii::app()->getConfig('timeadjust'));
$query = "UPDATE {{tokens_{$surveyid}}}\n" . "SET sent='{$today}' WHERE tid={$tid}";
$result = dbExecuteAssoc($query) or show_error("Unable to execute this query : {$query}<br />");
//Checked
$html = "<center>" . $clang->gT("Thank you for registering to participate in this survey.") . "<br /><br />\n" . $clang->gT("An email has been sent to the address you provided with access details for this survey. Please follow the link in that email to proceed.") . "<br /><br />\n" . $clang->gT("Survey administrator") . " {ADMINNAME} ({ADMINEMAIL})";
$html = ReplaceFields($html, $fieldsarray);
$html .= "<br /><br /></center>\n";
} else {
$html = "Email Error";
}
//PRINT COMPLETED PAGE
if (!$thissurvey['template']) {
$thistpl = getTemplatePath(validateTemplateDir('default'));
} else {
$thistpl = getTemplatePath(validateTemplateDir($thissurvey['template']));
}
sendCacheHeaders();
doHeader();
Yii::app()->lang = $clang;
// fetch the defined variables and pass it to the header footer templates.
$redata = compact(array_keys(get_defined_vars()));
$this->_printTemplateContent($thistpl . '/startpage.pstpl', $redata, __LINE__);
$this->_printTemplateContent($thistpl . '/survey.pstpl', $redata, __LINE__);
echo $html;
$this->_printTemplateContent($thistpl . '/endpage.pstpl', $redata, __LINE__);
doFooter();
}
function attributeMapCSV()
{
$clang = $this->getController()->lang;
$sRandomFileName = randomChars(20);
$sFilePath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . $sRandomFileName;
$aPathinfo = pathinfo($_FILES['the_file']['name']);
$sExtension = $aPathinfo['extension'];
if (strtolower($sExtension) == 'csv') {
$bMoveFileResult = @move_uploaded_file($_FILES['the_file']['tmp_name'], $sFilePath);
$errorinupload = '';
$filterblankemails = Yii::app()->request->getPost('filterbea');
} else {
$templateData['error_msg'] = sprintf($clang->gT("This is not a .csv file."), Yii::app()->getConfig('tempdir'));
$errorinupload = array('error' => $this->upload->display_errors());
Yii::app()->session['summary'] = array('errorinupload' => $errorinupload);
$this->_renderWrappedTemplate('participants', array('participantsPanel', 'uploadSummary'), array('aAttributes' => ParticipantAttributeName::model()->getAllAttributes()));
}
if (!$bMoveFileResult) {
$templateData['error_msg'] = sprintf($clang->gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), Yii::app()->getConfig('tempdir'));
$errorinupload = array('error' => $this->upload->display_errors());
Yii::app()->session['summary'] = array('errorinupload' => $errorinupload);
$this->_renderWrappedTemplate('participants', array('participantsPanel', 'uploadSummary'), array('aAttributes' => ParticipantAttributeName::model()->getAllAttributes()));
} else {
$aData = array('upload_data' => $_FILES['the_file']);
$sFileName = $_FILES['the_file']['name'];
$regularfields = array('firstname', 'participant_id', 'lastname', 'email', 'language', 'blacklisted', 'owner_uid');
$csvread = fopen($sFilePath, 'r');
$separator = Yii::app()->request->getPost('separatorused');
$firstline = fgetcsv($csvread, 1000, ',');
$selectedcsvfields = array();
foreach ($firstline as $key => $value) {
$testvalue = preg_replace('/[^(\\x20-\\x7F)]*/', '', $value);
//Remove invalid characters from string
if (!in_array(strtolower($testvalue), $regularfields)) {
array_push($selectedcsvfields, $value);
}
$fieldlist[] = $value;
}
$linecount = count(file($sFilePath));
$attributes = ParticipantAttributeName::model()->model()->getCPDBAttributes();
$aData = array('attributes' => $attributes, 'firstline' => $selectedcsvfields, 'fullfilepath' => $sRandomFileName, 'linecount' => $linecount - 1, 'filterbea' => $filterblankemails, 'participant_id_exists' => in_array('participant_id', $fieldlist));
App()->getClientScript()->registerCssFile(Yii::app()->getConfig('adminstyleurl') . "attributeMapCSV.css");
App()->getClientScript()->registerPackage('qTip2');
App()->getClientScript()->registerPackage('jquery-nestedSortable');
App()->getClientScript()->registerScriptFile(Yii::app()->getConfig('adminscripts') . "attributeMapCSV.js");
$sAttributeMapJS = "var copyUrl = '" . App()->createUrl("admin/participants/sa/uploadCSV") . "';\n" . "var displayParticipants = '" . App()->createUrl("admin/participants/sa/displayParticipants") . "';\n" . "var mapCSVcancelled = '" . App()->createUrl("admin/participants/sa/mapCSVcancelled") . "';\n" . "var characterset = '" . sanitize_paranoid_string($_POST['characterset']) . "';\n" . "var okBtn = '" . $clang->gT("OK") . "';\n" . "var processed = '" . $clang->gT("Summary") . "';\n" . "var summary = '" . $clang->gT("Upload summary") . "';\n" . "var notPairedErrorTxt = '" . $clang->gT("You have to pair this field with an existing attribute.") . "';\n" . "var onlyOnePairedErrorTxt = '" . $clang->gT("Only one CSV attribute is mapped with central attribute.") . "';\n" . "var cannotAcceptErrorTxt='" . $clang->gT("This list cannot accept token attributes.") . "';\n" . "var separator = '" . sanitize_paranoid_string($_POST['separatorused']) . "';\n" . "var thefilepath = '" . $sRandomFileName . "';\n" . "var filterblankemails = '" . $filterblankemails . "';\n";
App()->getClientScript()->registerScript("sAttributeMapJS", $sAttributeMapJS, CClientScript::POS_BEGIN);
$this->_renderWrappedTemplate('participants', 'attributeMapCSV', $aData);
}
}
/**
* Creates tokens for all token records that have empty token fields and returns the number
* of tokens created
*
* @param int $iSurveyID
* @return array ( int number of created tokens, int number to be created tokens)
*/
function createTokens($iSurveyID)
{
$tkresult = $this->selectEmptyTokens($iSurveyID);
//Exit early if there are not empty tokens
if (count($tkresult) === 0) {
return array(0, 0);
}
//get token length from survey settings
$tlrow = Survey::model()->findByAttributes(array("sid" => $iSurveyID));
$iTokenLength = $tlrow->tokenlength;
//if tokenlength is not set or there are other problems use the default value (15)
if (empty($iTokenLength)) {
$iTokenLength = 15;
}
//Add some criteria to select only the token field
$criteria = $this->getDbCriteria();
$criteria->select = 'token';
$ntresult = $this->findAllAsArray($criteria);
//Use AsArray to skip active record creation
// select all existing tokens
foreach ($ntresult as $tkrow) {
$existingtokens[$tkrow['token']] = true;
}
$newtokencount = 0;
$invalidtokencount = 0;
foreach ($tkresult as $tkrow) {
$bIsValidToken = false;
while ($bIsValidToken == false && $invalidtokencount < 50) {
$newtoken = randomChars($iTokenLength);
if (!isset($existingtokens[$newtoken])) {
$existingtokens[$newtoken] = true;
$bIsValidToken = true;
$invalidtokencount = 0;
} else {
$invalidtokencount++;
}
}
if ($bIsValidToken) {
$itresult = $this->updateToken($tkrow['tid'], $newtoken);
$newtokencount++;
} else {
break;
}
}
return array($newtokencount, count($tkresult));
}
function step3()
{
$clang = $this->getController()->lang;
$buildnumber = Yii::app()->getConfig("buildnumber");
$tempdir = Yii::app()->getConfig("tempdir");
$updatebuild = getGlobalSetting("updatebuild");
//$_POST=$this->input->post();
$rootdir = Yii::app()->getConfig("rootdir");
$publicdir = Yii::app()->getConfig("publicdir");
$tempdir = Yii::app()->getConfig("tempdir");
$aDatabasetype = Yii::app()->db->getDriverName();
$aData = array('clang' => $clang);
// Request the list with changed files from the server
if (!isset(Yii::app()->session['updateinfo'])) {
if ($updateinfo['error'] == 1) {
}
} else {
$updateinfo = Yii::app()->session['updateinfo'];
}
$aData['updateinfo'] = $updateinfo;
// okay, updateinfo now contains all necessary updateinformation
// Create DB and file backups now
$basefilename = dateShift(date("Y-m-d H:i:s"), "Y-m-d", Yii::app()->getConfig('timeadjust')) . '_' . md5(uniqid(rand(), true));
//Now create a backup of the files to be delete or modified
$filestozip = array();
foreach ($updateinfo['files'] as $file) {
if (is_file($publicdir . $file['file']) === true) {
$filestozip[] = $publicdir . $file['file'];
}
}
Yii::app()->loadLibrary("admin/pclzip");
$archive = new PclZip($tempdir . DIRECTORY_SEPARATOR . 'LimeSurvey_files_backup_' . $basefilename . '.zip');
$v_list = $archive->add($filestozip, PCLZIP_OPT_REMOVE_PATH, $publicdir);
if ($v_list == 0) {
$aFileBackup = array('class' => 'error', 'text' => sprintf($clang->gT("Error on file backup: %s"), $archive->errorInfo(true)));
} else {
$aFileBackup = array('class' => 'success', 'text' => sprintf($clang->gT("File backup created: %s"), $tempdir . DIRECTORY_SEPARATOR . 'LimeSurvey_files_backup_' . $basefilename . '.zip'));
}
$aData['aFileBackup'] = $aFileBackup;
$aData['databasetype'] = $aDatabasetype;
//TODO: Yii provides no function to backup the database. To be done after dumpdb is ported
if (in_array($aDatabasetype, array('mysql', 'mysqli'))) {
if (in_array($aDatabasetype, array('mysql', 'mysqli')) && Yii::app()->getConfig('demoMode') != true) {
Yii::app()->loadHelper("admin/backupdb");
$sfilename = $tempdir . DIRECTORY_SEPARATOR . "backup_db_" . randomChars(20) . "_" . dateShift(date("Y-m-d H:i:s"), "Y-m-d", Yii::app()->getConfig('timeadjust')) . ".sql";
$dfilename = $tempdir . DIRECTORY_SEPARATOR . "LimeSurvey_database_backup_" . $basefilename . ".zip";
outputDatabase('', false, $sfilename);
// Before try to zip: test size of file
if (is_file($sfilename) && filesize($sfilename)) {
$archive = new PclZip($dfilename);
$v_list = $archive->add(array($sfilename), PCLZIP_OPT_REMOVE_PATH, $tempdir, PCLZIP_OPT_ADD_TEMP_FILE_ON);
unlink($sfilename);
if ($v_list == 0) {
// Unknow reason because backup of DB work ?
$aSQLBackup = array('class' => 'warning', 'text' => $clang->gT("Unable to backup your database for unknow reason. Before proceeding please backup your database using a backup tool!"));
} else {
$aSQLBackup = array('class' => 'success', 'text' => sprintf($clang->gT('DB backup created: %s'), htmlspecialchars($dfilename)));
}
} else {
$aSQLBackup = array('class' => 'warning', 'text' => $clang->gT("Unable to backup your database for unknow reason. Before proceeding please backup your database using a backup tool!"));
}
}
} else {
$aSQLBackup = array('class' => 'warning', 'text' => $clang->gT('Database backup functionality is currently not available for your database type. Before proceeding please backup your database using a backup tool!'));
}
$aData['aSQLBackup'] = $aSQLBackup;
if ($aFileBackup['class'] == "success" && $aSQLBackup['class'] == "success") {
$aData['result'] = "success";
} elseif ($aFileBackup['class'] == "error" || $aSQLBackup['class'] == "error") {
$aData['result'] = "error";
} else {
$aData['result'] = "warning";
}
$this->_renderWrappedTemplate('update', 'step3', $aData);
}
function attributeMapCSV()
{
$clang = $this->getController()->lang;
$sRandomFileName = randomChars(20);
$sFilePath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . $sRandomFileName;
$aPathinfo = pathinfo($_FILES['the_file']['name']);
$sExtension = $aPathinfo['extension'];
if (strtolower($sExtension) == 'csv') {
$bMoveFileResult = @move_uploaded_file($_FILES['the_file']['tmp_name'], $sFilePath);
$errorinupload = '';
$filterblankemails = Yii::app()->request->getPost('filterbea');
} else {
$templateData['error_msg'] = sprintf($clang->gT("This is not a .csv file."), Yii::app()->getConfig('tempdir'));
$errorinupload = array('error' => $this->upload->display_errors());
Yii::app()->session['summary'] = array('errorinupload' => $errorinupload);
$this->_renderWrappedTemplate('participants', array('participantsPanel', 'uploadSummary'));
}
if (!$bMoveFileResult) {
$templateData['error_msg'] = sprintf($clang->gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), Yii::app()->getConfig('tempdir'));
$errorinupload = array('error' => $this->upload->display_errors());
Yii::app()->session['summary'] = array('errorinupload' => $errorinupload);
$this->_renderWrappedTemplate('participants', array('participantsPanel', 'uploadSummary'));
} else {
$aData = array('upload_data' => $_FILES['the_file']);
$sFileName = $_FILES['the_file']['name'];
$regularfields = array('firstname', 'participant_id', 'lastname', 'email', 'language', 'blacklisted', 'owner_uid');
$csvread = fopen($sFilePath, 'r');
$seperator = Yii::app()->request->getPost('seperatorused');
$firstline = fgetcsv($csvread, 1000, ',');
$selectedcsvfields = array();
foreach ($firstline as $key => $value) {
$testvalue = preg_replace('/[^(\\x20-\\x7F)]*/', '', $value);
//Remove invalid characters from string
if (!in_array(strtolower($testvalue), $regularfields)) {
array_push($selectedcsvfields, $value);
}
$fieldlist[] = $value;
}
$linecount = count(file($sFilePath));
$attributes = ParticipantAttributeNames::model()->model()->getAttributes();
$aData = array('attributes' => $attributes, 'firstline' => $selectedcsvfields, 'fullfilepath' => $sRandomFileName, 'linecount' => $linecount - 1, 'filterbea' => $filterblankemails, 'participant_id_exists' => in_array('participant_id', $fieldlist));
$this->_renderWrappedTemplate('participants', 'attributeMapCSV', $aData);
}
}
/**
* This function returns a new random sid if the existing one is taken,
* otherwise it returns the old one.
*
* @param mixed $iOldSID
*/
function GetNewSurveyID($iOldSID)
{
Yii::app()->loadHelper('database');
$query = "SELECT sid FROM {{surveys}} WHERE sid={$iOldSID}";
$aRow = Yii::app()->db->createCommand($query)->queryRow();
//if (!is_null($isresult))
if ($aRow !== false) {
// Get new random ids until one is found that is not used
do {
$iNewSID = randomChars(5, '123456789');
$query = "SELECT sid FROM {{surveys}} WHERE sid={$iNewSID}";
$aRow = Yii::app()->db->createCommand($query)->queryRow();
} while ($aRow !== false);
return $iNewSID;
} else {
return $iOldSID;
}
}
function run($actionID)
{
$surveyid = Yii::app()->session['LEMsid'];
$oSurvey = Survey::model()->findByPk($surveyid);
if (!$oSurvey) {
throw new CHttpException(400);
}
// See for debug > 1
$sLanguage = isset(Yii::app()->session['survey_' . $surveyid]['s_lang']) ? Yii::app()->session['survey_' . $surveyid]['s_lang'] : "";
$uploaddir = Yii::app()->getConfig("uploaddir");
$tempdir = Yii::app()->getConfig("tempdir");
Yii::app()->loadHelper("database");
// Fill needed var
$sFileGetContent = Yii::app()->request->getParam('filegetcontents', '');
// The file to view fu_ or fu_tmp
$bDelete = Yii::app()->request->getParam('delete');
$sFieldName = Yii::app()->request->getParam('fieldname');
$sFileName = Yii::app()->request->getParam('filename', '');
// The file to delete fu_ or fu_tmp
$sOriginalFileName = Yii::app()->request->getParam('name', '');
// Used for javascript return only
$sMode = Yii::app()->request->getParam('mode');
$sPreview = Yii::app()->request->getParam('preview', 0);
// Validate and filter and throw error if problems
// Using 'futmp_'.randomChars(15).'_'.$pathinfo['extension'] for filename, then remove all other characters
$sFileGetContentFiltered = preg_replace('/[^a-zA-Z0-9_]/', '', $sFileGetContent);
$sFileNameFiltered = preg_replace('/[^a-zA-Z0-9_]/', '', $sFileName);
$sFieldNameFiltered = preg_replace('/[^X0-9]/', '', $sFieldName);
if ($sFileGetContent != $sFileGetContentFiltered || $sFileName != $sFileNameFiltered || $sFieldName != $sFieldNameFiltered) {
// If one seems to be a hack: Bad request
throw new CHttpException(400);
// See for debug > 1
}
if ($sFileGetContent) {
if (substr($sFileGetContent, 0, 6) == 'futmp_') {
$sFileDir = $tempdir . '/upload/';
} elseif (substr($sFileGetContent, 0, 3) == 'fu_') {
// Need to validate $_SESSION['srid'], and this file is from this srid !
$sFileDir = "{$uploaddir}/surveys/{$surveyid}/files/";
} else {
throw new CHttpException(400);
// See for debug > 1
}
if (is_file($sFileDir . $sFileGetContent)) {
header('Content-Type: ' . CFileHelper::getMimeType($sFileDir . $sFileGetContent));
readfile($sFileDir . $sFileGetContent);
Yii::app()->end();
} else {
Yii::app()->end();
}
} elseif ($bDelete) {
if (substr($sFileName, 0, 6) == 'futmp_') {
$sFileDir = $tempdir . '/upload/';
} elseif (substr($sFileName, 0, 3) == 'fu_') {
// Need to validate $_SESSION['srid'], and this file is from this srid !
$sFileDir = "{$uploaddir}/surveys/{$surveyid}/files/";
} else {
throw new CHttpException(400);
// See for debug > 1
}
if (isset($_SESSION[$sFieldName])) {
// We already have $sFieldName ?
$sJSON = $_SESSION[$sFieldName];
$aFiles = json_decode(stripslashes($sJSON), true);
if (substr($sFileName, 0, 3) == 'fu_') {
$iFileIndex = 0;
$found = false;
foreach ($aFiles as $aFile) {
if ($aFile['filename'] == $sFileName) {
$found = true;
break;
}
$iFileIndex++;
}
if ($found == true) {
unset($aFiles[$iFileIndex]);
}
$_SESSION[$sFieldName] = ls_json_encode($aFiles);
}
}
//var_dump($sFileDir.$sFilename);
// Return some json to do a beautiful text
if (@unlink($sFileDir . $sFileName)) {
echo sprintf(gT('File %s deleted'), $sOriginalFileName);
} else {
echo gT('Oops, There was an error deleting the file');
}
Yii::app()->end();
}
if ($sMode == "upload") {
$sTempUploadDir = $tempdir . '/upload/';
// Check if exists and is writable
if (!file_exists($sTempUploadDir)) {
// Try to create
mkdir($sTempUploadDir);
}
$filename = $_FILES['uploadfile']['name'];
// Do we filter file name ? It's used on displaying only , but not save like that.
//$filename = sanitize_filename($_FILES['uploadfile']['name']);// This remove all non alpha numeric characters and replaced by _ . Leave only one dot .
$size = 0.001 * $_FILES['uploadfile']['size'];
$preview = Yii::app()->session['preview'];
$aFieldMap = createFieldMap($surveyid, 'short', false, false, $sLanguage);
if (!isset($aFieldMap[$sFieldName])) {
throw new CHttpException(400);
// See for debug > 1
}
$aAttributes = getQuestionAttributeValues($aFieldMap[$sFieldName]['qid']);
$maxfilesize = (int) $aAttributes['max_filesize'];
$valid_extensions_array = explode(",", $aAttributes['allowed_filetypes']);
$valid_extensions_array = array_map('trim', $valid_extensions_array);
$pathinfo = pathinfo($_FILES['uploadfile']['name']);
$ext = strtolower($pathinfo['extension']);
$randfilename = 'futmp_' . randomChars(15) . '_' . $pathinfo['extension'];
$randfileloc = $sTempUploadDir . $randfilename;
// check to see that this file type is allowed
// it is also checked at the client side, but jst double checking
if (!in_array($ext, $valid_extensions_array)) {
$return = array("success" => false, "msg" => sprintf(gT("Sorry, this file extension (%s) is not allowed!"), $ext));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
}
// If this is just a preview, don't save the file
if ($preview) {
if ($size > $maxfilesize) {
$return = array("success" => false, "msg" => sprintf(gT("Sorry, this file is too large. Only files upto %s KB are allowed."), $maxfilesize));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
} else {
if (move_uploaded_file($_FILES['uploadfile']['tmp_name'], $randfileloc)) {
$return = array("success" => true, "file_index" => $filecount, "size" => $size, "name" => rawurlencode(basename($filename)), "ext" => $ext, "filename" => $randfilename, "msg" => gT("The file has been successfuly uploaded."));
// TODO : unlink this file since this is just a preview. But we can do it only if it's not needed, and still needed to have the file content
// Maybe use a javascript 'onunload' on preview question/group
// unlink($randfileloc)
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
}
}
} else {
// if everything went fine and the file was uploaded successfuly,
// send the file related info back to the client
$iFileUploadTotalSpaceMB = Yii::app()->getConfig("iFileUploadTotalSpaceMB");
if ($size > $maxfilesize) {
$return = array("success" => false, "msg" => sprintf(gT("Sorry, this file is too large. Only files up to %s KB are allowed.", 'unescaped'), $maxfilesize));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
} elseif ($iFileUploadTotalSpaceMB > 0 && calculateTotalFileUploadUsage() + $size / 1024 / 1024 > $iFileUploadTotalSpaceMB) {
$return = array("success" => false, "msg" => gT("We are sorry but there was a system error and your file was not saved. An email has been dispatched to notify the survey administrator.", 'unescaped'));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
} elseif (move_uploaded_file($_FILES['uploadfile']['tmp_name'], $randfileloc)) {
$return = array("success" => true, "size" => $size, "name" => rawurlencode(basename($filename)), "ext" => $ext, "filename" => $randfilename, "msg" => gT("The file has been successfuly uploaded."));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
} else {
// check for upload error
if ($_FILES['uploadfile']['error'] > 2) {
$return = array("success" => false, "msg" => gT("Sorry, there was an error uploading your file"));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
} else {
if ($_FILES['uploadfile']['error'] == 1 || $_FILES['uploadfile']['error'] == 2 || $size > $maxfilesize) {
$return = array("success" => false, "msg" => sprintf(gT("Sorry, this file is too large. Only files upto %s KB are allowed."), $maxfilesize));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
} else {
$return = array("success" => false, "msg" => gT("Unknown error"));
//header('Content-Type: application/json');
echo ls_json_encode($return);
Yii::app()->end();
}
}
}
}
return;
}
$meta = '';
App()->getClientScript()->registerPackage('jqueryui');
App()->getClientScript()->registerPackage('jquery-superfish');
$sNeededScriptVar = '
var uploadurl = "' . $this->createUrl('/uploader/index/mode/upload/') . '";
var imageurl = "' . Yii::app()->getConfig('imageurl') . '/";
var surveyid = "' . $surveyid . '";
var fieldname = "' . $sFieldName . '";
var questgrppreview = ' . $sPreview . ';
csrfToken = ' . ls_json_encode(Yii::app()->request->csrfToken) . ';
showpopups="' . Yii::app()->getConfig("showpopups") . '";
';
$sLangScriptVar = "\n uploadLang = {\n titleFld: '" . gT('Title', 'js') . "',\n commentFld: '" . gT('Comment', 'js') . "',\n errorNoMoreFiles: '" . gT('Sorry, no more files can be uploaded!', 'js') . "',\n errorOnlyAllowed: '" . gT('Sorry, only %s files can be uploaded for this question!', 'js') . "',\n uploading: '" . gT('Uploading', 'js') . "',\n selectfile: '" . gT('Select file', 'js') . "',\n errorNeedMore: '" . gT('Please upload %s more file(s).', 'js') . "',\n errorMoreAllowed: '" . gT('If you wish, you may upload %s more file(s); else you may return back to survey.', 'js') . "',\n errorMaxReached: '" . gT('The maximum number of files has been uploaded. You may return back to survey.', 'js') . "',\n errorTooMuch: '" . gT('The maximum number of files has been uploaded. You may return back to survey.', 'js') . "',\n errorNeedMoreConfirm: '" . gT("You need to upload %s more files for this question.\nAre you sure you want to exit?", 'js') . "',\n deleteFile : '" . gt('Delete', 'js') . "',\n editFile : '" . gt('Edit', 'js') . "',\n };\n ";
$aSurveyInfo = getSurveyInfo($surveyid, $sLanguage);
$oEvent = new PluginEvent('beforeSurveyPage');
$oEvent->set('surveyId', $surveyid);
App()->getPluginManager()->dispatchEvent($oEvent);
if (!is_null($oEvent->get('template'))) {
$aSurveyInfo['templatedir'] = $event->get('template');
}
$sTemplateDir = getTemplatePath($aSurveyInfo['template']);
$sTemplateUrl = getTemplateURL($aSurveyInfo['template']) . "/";
App()->clientScript->registerScript('sNeededScriptVar', $sNeededScriptVar, CClientScript::POS_HEAD);
App()->clientScript->registerScript('sLangScriptVar', $sLangScriptVar, CClientScript::POS_HEAD);
App()->getClientScript()->registerScriptFile(Yii::app()->getConfig("generalscripts") . 'ajaxupload.js');
App()->getClientScript()->registerScriptFile(Yii::app()->getConfig("generalscripts") . 'uploader.js');
App()->getClientScript()->registerScriptFile("{$sTemplateUrl}template.js");
App()->clientScript->registerCssFile(Yii::app()->getConfig("publicstyleurl") . "uploader.css");
App()->getClientScript()->registerCssFile(Yii::app()->getConfig('publicstyleurl') . "uploader-files.css");
if (file_exists($sTemplateDir . DIRECTORY_SEPARATOR . 'jquery-ui-custom.css')) {
Yii::app()->getClientScript()->registerCssFile("{$sTemplateUrl}jquery-ui-custom.css");
} elseif (file_exists($sTemplateDir . DIRECTORY_SEPARATOR . 'jquery-ui.css')) {
Yii::app()->getClientScript()->registerCssFile("{$sTemplateUrl}jquery-ui.css");
} else {
Yii::app()->getClientScript()->registerCssFile(Yii::app()->getConfig('publicstyleurl') . "jquery-ui.css");
}
App()->clientScript->registerCssFile("{$sTemplateUrl}template.css");
$header = getHeader($meta);
echo $header;
$fn = $sFieldName;
$qid = (int) Yii::app()->request->getParam('qid');
$minfiles = (int) Yii::app()->request->getParam('minfiles');
$maxfiles = (int) Yii::app()->request->getParam('maxfiles');
$qidattributes = getQuestionAttributeValues($qid);
$qidattributes['max_filesize'] = floor(min($qidattributes['max_filesize'] * 1024, getMaximumFileUploadSize()) / 1024);
$body = '</head><body class="uploader">
<div id="notice"></div>
<input type="hidden" id="ia" value="' . $fn . '" />
<input type="hidden" id="' . $fn . '_minfiles" value="' . $minfiles . '" />
<input type="hidden" id="' . $fn . '_maxfiles" value="' . $maxfiles . '" />
<input type="hidden" id="' . $fn . '_maxfilesize" value="' . $qidattributes['max_filesize'] . '" />
<input type="hidden" id="' . $fn . '_allowed_filetypes" value="' . $qidattributes['allowed_filetypes'] . '" />
<input type="hidden" id="preview" value="' . Yii::app()->session['preview'] . '" />
<input type="hidden" id="' . $fn . '_show_comment" value="' . $qidattributes['show_comment'] . '" />
<input type="hidden" id="' . $fn . '_show_title" value="' . $qidattributes['show_title'] . '" />
<input type="hidden" id="' . $fn . '_licount" value="0" />
<input type="hidden" id="' . $fn . '_filecount" value="0" />
<!-- The upload button -->
<div class="upload-div">
<button id="button1" class="button upload-button" type="button" >' . gT("Select file") . '</button>
</div>
<p class="uploadmsg">' . sprintf(gT("You can upload %s under %s KB each."), $qidattributes['allowed_filetypes'], $qidattributes['max_filesize']) . '</p>
<div class="uploadstatus" id="uploadstatus"></div>
<!-- The list of uploaded files -->
</body>
</html>';
App()->getClientScript()->render($body);
echo $body;
}
/**
* Function responsible to import/copy a survey based on $action.
*
* @access public
* @return void
*/
public function copy()
{
$importsurvey = "";
$action = Yii::app()->request->getParam('action');
$iSurveyID = sanitize_int(Yii::app()->request->getParam('sid'));
if ($action == "importsurvey" || $action == "copysurvey") {
// Start the HTML
if ($action == 'importsurvey') {
$aData['sHeader'] = gT("Import survey data");
$aData['sSummaryHeader'] = gT("Survey structure import summary");
$importingfrom = "http";
$aPathInfo = pathinfo($_FILES['the_file']['name']);
if (isset($aPathInfo['extension'])) {
$sExtension = $aPathInfo['extension'];
} else {
$sExtension = "";
}
} elseif ($action == 'copysurvey') {
$aData['sHeader'] = gT("Copy survey");
$aData['sSummaryHeader'] = gT("Survey copy summary");
}
// Start traitment and messagebox
$aData['bFailed'] = false;
// Put a var for continue
if ($action == 'importsurvey') {
$sFullFilepath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . randomChars(20) . '.' . $sExtension;
if (!@move_uploaded_file($_FILES['the_file']['tmp_name'], $sFullFilepath)) {
$aData['sErrorMessage'] = sprintf(gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), Yii::app()->getConfig('tempdir'));
$aData['bFailed'] = true;
}
if (!$aData['bFailed'] && (strtolower($sExtension) != 'csv' && strtolower($sExtension) != 'lss' && strtolower($sExtension) != 'txt' && strtolower($sExtension) != 'lsa')) {
$aData['sErrorMessage'] = sprintf(gT("Import failed. You specified an invalid file type '%s'."), $sExtension);
$aData['bFailed'] = true;
}
} elseif ($action == 'copysurvey') {
$iSurveyID = sanitize_int(Yii::app()->request->getParam('copysurveylist'));
$aExcludes = array();
$sNewSurveyName = Yii::app()->request->getPost('copysurveyname');
if (Yii::app()->request->getPost('copysurveyexcludequotas') == "on") {
$aExcludes['quotas'] = true;
}
if (Yii::app()->request->getPost('copysurveyexcludepermissions') == "on") {
$aExcludes['permissions'] = true;
}
if (Yii::app()->request->getPost('copysurveyexcludeanswers') == "on") {
$aExcludes['answers'] = true;
}
if (Yii::app()->request->getPost('copysurveyresetconditions') == "on") {
$aExcludes['conditions'] = true;
}
if (Yii::app()->request->getPost('copysurveyresetstartenddate') == "on") {
$aExcludes['dates'] = true;
}
if (!$iSurveyID) {
$aData['sErrorMessage'] = gT("No survey ID has been provided. Cannot copy survey");
$aData['bFailed'] = true;
} elseif (!Survey::model()->findByPk($iSurveyID)) {
$aData['sErrorMessage'] = gT("Invalid survey ID");
$aData['bFailed'] = true;
} elseif (!Permission::model()->hasSurveyPermission($iSurveyID, 'surveycontent', 'export') && !Permission::model()->hasSurveyPermission($iSurveyID, 'surveycontent', 'export')) {
$aData['sErrorMessage'] = gT("You don't have sufficient permissions.");
$aData['bFailed'] = true;
} else {
Yii::app()->loadHelper('export');
$copysurveydata = surveyGetXMLData($iSurveyID, $aExcludes);
}
}
// Now, we have the survey : start importing
Yii::app()->loadHelper('admin/import');
if ($action == 'importsurvey' && !$aData['bFailed']) {
$aImportResults = importSurveyFile($sFullFilepath, isset($_POST['translinksfields']));
if (is_null($aImportResults) || !empty($aImportResults['error'])) {
$aData['sErrorMessage'] = isset($aImportResults['error']) ? $aImportResults['error'] : gt("Unknow error.");
$aData['bFailed'] = true;
}
} elseif ($action == 'copysurvey' && !$aData['bFailed']) {
$aImportResults = XMLImportSurvey('', $copysurveydata, $sNewSurveyName, sanitize_int(App()->request->getParam('copysurveyid')), isset($_POST['translinksfields']));
if (isset($aExcludes['conditions'])) {
Question::model()->updateAll(array('relevance' => '1'), 'sid=' . $aImportResults['newsid']);
QuestionGroup::model()->updateAll(array('grelevance' => '1'), 'sid=' . $aImportResults['newsid']);
}
if (!isset($aExcludes['permissions'])) {
Permission::model()->copySurveyPermissions($iSurveyID, $aImportResults['newsid']);
}
} else {
$aData['bFailed'] = true;
}
if ($action == 'importsurvey' && isset($sFullFilepath)) {
unlink($sFullFilepath);
}
if (!$aData['bFailed']) {
$aData['action'] = $action;
$aData['sLink'] = $this->getController()->createUrl('admin/survey/sa/view/surveyid/' . $aImportResults['newsid']);
$aData['aImportResults'] = $aImportResults;
}
}
$this->_renderWrappedTemplate('survey', 'importSurvey_view', $aData);
}
/**
* import from csv
*/
function import($iSurveyId)
{
$clang = $this->getController()->lang;
$iSurveyId = (int) $iSurveyId;
if (!Permission::model()->hasSurveyPermission($iSurveyId, 'tokens', 'import')) {
Yii::app()->session['flashmessage'] = $clang->gT("You do not have sufficient rights to access this page.");
$this->getController()->redirect(array("/admin/survey/sa/view/surveyid/{$iSurveyId}"));
}
// CHECK TO SEE IF A TOKEN TABLE EXISTS FOR THIS SURVEY
$bTokenExists = tableExists('{{tokens_' . $iSurveyId . '}}');
if (!$bTokenExists) {
self::_newtokentable($iSurveyId);
}
App()->getClientScript()->registerScriptFile(Yii::app()->getConfig('adminscripts') . 'tokensimport.js');
$aEncodings = aEncodingsArray();
if (Yii::app()->request->getPost('submit')) {
if (Yii::app()->request->getPost('csvcharset') && Yii::app()->request->getPost('csvcharset')) {
$uploadcharset = Yii::app()->request->getPost('csvcharset');
if (!array_key_exists($uploadcharset, $aEncodings)) {
$uploadcharset = 'auto';
}
$filterduplicatetoken = Yii::app()->request->getPost('filterduplicatetoken') && Yii::app()->request->getPost('filterduplicatetoken') == 'on';
$filterblankemail = Yii::app()->request->getPost('filterblankemail') && Yii::app()->request->getPost('filterblankemail') == 'on';
}
$attrfieldnames = getAttributeFieldNames($iSurveyId);
$duplicatelist = array();
$invalidemaillist = array();
$invalidformatlist = array();
$firstline = array();
$sPath = Yii::app()->getConfig('tempdir');
$sFileTmpName = $_FILES['the_file']['tmp_name'];
$sFilePath = $sPath . '/' . randomChars(20);
if (!@move_uploaded_file($sFileTmpName, $sFilePath)) {
$aData['sError'] = $clang->gT("Upload file not found. Check your permissions and path ({$sFilePath}) for the upload directory");
$aData['aEncodings'] = $aEncodings;
$aData['iSurveyId'] = $aData['surveyid'] = $iSurveyId;
$aData['thissurvey'] = getSurveyInfo($iSurveyId);
$this->_renderWrappedTemplate('token', array('tokenbar', 'csvupload'), $aData);
} else {
$xz = 0;
$recordcount = 0;
$xv = 0;
// This allows to read file with MAC line endings too
@ini_set('auto_detect_line_endings', true);
// open it and trim the ednings
$tokenlistarray = file($sFilePath);
$sBaseLanguage = Survey::model()->findByPk($iSurveyId)->language;
if (!Yii::app()->request->getPost('filterduplicatefields') || Yii::app()->request->getPost('filterduplicatefields') && count(Yii::app()->request->getPost('filterduplicatefields')) == 0) {
$filterduplicatefields = array('firstname', 'lastname', 'email');
} else {
$filterduplicatefields = Yii::app()->request->getPost('filterduplicatefields');
}
$separator = returnGlobal('separator');
foreach ($tokenlistarray as $buffer) {
$buffer = @mb_convert_encoding($buffer, "UTF-8", $uploadcharset);
if ($recordcount == 0) {
// Parse first line (header) from CSV
$buffer = removeBOM($buffer);
// We alow all field except tid because this one is really not needed.
$allowedfieldnames = array('participant_id', 'firstname', 'lastname', 'email', 'emailstatus', 'token', 'language', 'blacklisted', 'sent', 'remindersent', 'remindercount', 'validfrom', 'validuntil', 'completed', 'usesleft');
$allowedfieldnames = array_merge($attrfieldnames, $allowedfieldnames);
// Some header don't have same column name
$aReplacedFields = array('invited' => 'sent');
switch ($separator) {
case 'comma':
$separator = ',';
break;
case 'semicolon':
$separator = ';';
break;
default:
$comma = substr_count($buffer, ',');
$semicolon = substr_count($buffer, ';');
if ($semicolon > $comma) {
$separator = ';';
} else {
$separator = ',';
}
}
$firstline = str_getcsv($buffer, $separator, '"');
$firstline = array_map('trim', $firstline);
$ignoredcolumns = array();
// Now check the first line for invalid fields
foreach ($firstline as $index => $fieldname) {
$firstline[$index] = preg_replace("/(.*) <[^,]*>\$/", "\$1", $fieldname);
$fieldname = $firstline[$index];
if (!in_array($fieldname, $allowedfieldnames)) {
$ignoredcolumns[] = $fieldname;
}
if (array_key_exists($fieldname, $aReplacedFields)) {
$firstline[$index] = $aReplacedFields[$fieldname];
}
}
if (!in_array('firstname', $firstline) || !in_array('lastname', $firstline) || !in_array('email', $firstline)) {
$recordcount = count($tokenlistarray);
break;
}
} else {
$line = str_getcsv($buffer, $separator, '"');
if (count($firstline) != count($line)) {
$invalidformatlist[] = $recordcount;
$recordcount++;
continue;
}
$writearray = array_combine($firstline, $line);
//kick out ignored columns
foreach ($ignoredcolumns as $column) {
unset($writearray[$column]);
}
$dupfound = false;
$invalidemail = false;
if ($filterduplicatetoken != false) {
$dupquery = "SELECT count(tid) from {{tokens_" . intval($iSurveyId) . "}} where 1=1";
foreach ($filterduplicatefields as $field) {
if (isset($writearray[$field])) {
$dupquery .= " and " . Yii::app()->db->quoteColumnName($field) . " = " . Yii::app()->db->quoteValue($writearray[$field]);
}
}
$dupresult = Yii::app()->db->createCommand($dupquery)->queryScalar();
if ($dupresult > 0) {
$dupfound = true;
$duplicatelist[] = Yii::app()->db->quoteValue($writearray['firstname']) . " " . Yii::app()->db->quoteValue($writearray['lastname']) . " (" . Yii::app()->db->quoteValue($writearray['email']) . ")";
}
}
$writearray['email'] = trim($writearray['email']);
//treat blank emails
if ($filterblankemail && $writearray['email'] == '') {
$invalidemail = true;
$invalidemaillist[] = $line[0] . " " . $line[1] . " ( )";
}
if ($writearray['email'] != '') {
$aEmailAddresses = explode(';', $writearray['email']);
foreach ($aEmailAddresses as $sEmailaddress) {
if (!validateEmailAddress($sEmailaddress)) {
$invalidemail = true;
$invalidemaillist[] = $line[0] . " " . $line[1] . " (" . $line[2] . ")";
}
}
}
if (isset($writearray['token'])) {
$writearray['token'] = sanitize_token($writearray['token']);
}
if (!$dupfound && !$invalidemail) {
// unset all empty value
foreach ($writearray as $key => $value) {
if ($writearray[$key] == "") {
unset($writearray[$key]);
}
if (substr($value, 0, 1) == '"' && substr($value, -1) == '"') {
// Fix CSV quote
$value = substr($value, 1, -1);
}
}
// Some default value : to be moved to Token model rules in future release ?
// But think we have to accept invalid email etc ... then use specific scenario
$writearray['emailstatus'] = isset($writearray['emailstatus']) ? $writearray['emailstatus'] : "OK";
$writearray['language'] = isset($writearray['language']) ? $writearray['language'] : $sBaseLanguage;
$oToken = Token::create($iSurveyId);
foreach ($writearray as $key => $value) {
//if(in_array($key,$oToken->attributes)) Not needed because we filter attributes before
$oToken->{$key} = $value;
}
$ir = $oToken->save();
if (!$ir) {
$duplicatelist[] = $writearray['firstname'] . " " . $writearray['lastname'] . " (" . $writearray['email'] . ")";
} else {
$xz++;
}
}
$xv++;
}
$recordcount++;
}
$recordcount = $recordcount - 1;
unlink($sFilePath);
$aData['tokenlistarray'] = $tokenlistarray;
$aData['xz'] = $xz;
$aData['xv'] = $xv;
$aData['recordcount'] = $recordcount;
$aData['firstline'] = $firstline;
$aData['duplicatelist'] = $duplicatelist;
$aData['invalidformatlist'] = $invalidformatlist;
$aData['invalidemaillist'] = $invalidemaillist;
$aData['thissurvey'] = getSurveyInfo($iSurveyId);
$aData['iSurveyId'] = $aData['surveyid'] = $iSurveyId;
$this->_renderWrappedTemplate('token', array('tokenbar', 'csvpost'), $aData);
}
} else {
$aData['aEncodings'] = $aEncodings;
$aData['iSurveyId'] = $iSurveyId;
$aData['thissurvey'] = getSurveyInfo($iSurveyId);
$aData['surveyid'] = $iSurveyId;
$aTokenTableFields = getTokenFieldsAndNames($iSurveyId);
unset($aTokenTableFields['sent']);
unset($aTokenTableFields['remindersent']);
unset($aTokenTableFields['remindercount']);
unset($aTokenTableFields['usesleft']);
foreach ($aTokenTableFields as $sKey => $sValue) {
if ($sValue['description'] != $sKey) {
$sValue['description'] .= ' - ' . $sKey;
}
$aNewTokenTableFields[$sKey] = $sValue['description'];
}
$aData['aTokenTableFields'] = $aNewTokenTableFields;
$this->_renderWrappedTemplate('token', array('tokenbar', 'csvupload'), $aData);
}
}
/**
* Sets the session data
* @param CActiveRecord $user
*/
private function _setSessionData($user)
{
Yii::app()->session['loginID'] = (int) $user->uid;
Yii::app()->session['user'] = $user->users_name;
Yii::app()->session['full_name'] = $user->full_name;
Yii::app()->session['htmleditormode'] = $user->htmleditormode;
Yii::app()->session['templateeditormode'] = $user->templateeditormode;
Yii::app()->session['questionselectormode'] = $user->questionselectormode;
Yii::app()->session['dateformat'] = $user->dateformat;
Yii::app()->session['checksessionpost'] = randomChars(10);
Yii::app()->session['session_hash'] = hash('sha256', getGlobalSetting('SessionName') . $user->users_name . $user->uid);
}
/**
* Show the drag-n-drop form for CSV attributes
*/
public function attributeMapCSV()
{
if (!Permission::model()->hasGlobalPermission('participantpanel', 'import')) {
die('No permission');
}
if ($_FILES['the_file']['name'] == '') {
Yii::app()->setFlashMessage(gT('Please select a file to import!'), 'error');
Yii::app()->getController()->redirect(array('admin/participants/sa/importCSV'));
}
$sRandomFileName = randomChars(20);
$sFilePath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . $sRandomFileName;
$aPathinfo = pathinfo($_FILES['the_file']['name']);
$sExtension = $aPathinfo['extension'];
if ($_FILES['the_file']['error'] == 1 || $_FILES['the_file']['error'] == 2) {
Yii::app()->setFlashMessage(sprintf(gT("Sorry, this file is too large. Only files up to %01.2f MB are allowed."), getMaximumFileUploadSize() / 1024 / 1024), 'error');
Yii::app()->getController()->redirect(array('admin/participants/sa/importCSV'));
exit;
} elseif (strtolower($sExtension) == 'csv') {
$bMoveFileResult = @move_uploaded_file($_FILES['the_file']['tmp_name'], $sFilePath);
$filterblankemails = Yii::app()->request->getPost('filterbea');
} else {
Yii::app()->setFlashMessage(gT("This is not a .csv file."), 'error');
Yii::app()->getController()->redirect(array('admin/participants/sa/importCSV'));
exit;
}
if (!$bMoveFileResult) {
Yii::app()->setFlashMessage(sprintf(gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), Yii::app()->getConfig('tempdir')), 'error');
Yii::app()->getController()->redirect(array('admin/participants/sa/importCSV'));
exit;
} else {
$regularfields = array('firstname', 'participant_id', 'lastname', 'email', 'language', 'blacklisted', 'owner_uid');
$oCSVFile = fopen($sFilePath, 'r');
$aFirstLine = fgets($oCSVFile);
rewind($oCSVFile);
$sSeparator = Yii::app()->request->getPost('separatorused');
if ($sSeparator == 'auto') {
$aCount = array();
$aCount[','] = substr_count($aFirstLine, ',');
$aCount[';'] = substr_count($aFirstLine, ';');
$aCount['|'] = substr_count($aFirstLine, '|');
$aResult = array_keys($aCount, max($aCount));
$sSeparator = $aResult[0];
}
$firstline = fgetcsv($oCSVFile, 1000, $sSeparator[0]);
$selectedcsvfields = array();
$fieldlist = array();
foreach ($firstline as $key => $value) {
$testvalue = preg_replace('/[^(\\x20-\\x7F)]*/', '', $value);
//Remove invalid characters from string
if (!in_array(strtolower($testvalue), $regularfields)) {
array_push($selectedcsvfields, $value);
}
$fieldlist[] = $value;
}
$iLineCount = count(array_filter(array_filter(file($sFilePath), 'trim')));
$attributes = ParticipantAttributeName::model()->model()->getCPDBAttributes();
$aData = array('attributes' => $attributes, 'firstline' => $selectedcsvfields, 'fullfilepath' => $sRandomFileName, 'linecount' => $iLineCount - 1, 'filterbea' => $filterblankemails, 'participant_id_exists' => in_array('participant_id', $fieldlist));
App()->getClientScript()->registerPackage('qTip2');
App()->getClientScript()->registerPackage('jquery-nestedSortable');
$this->registerScriptFile('ADMIN_SCRIPT_PATH', 'attributeMapCSV.js');
$sAttributeMapJS = "var copyUrl = '" . App()->createUrl("admin/participants/sa/uploadCSV") . "';\n" . "var displayParticipants = '" . App()->createUrl("admin/participants/sa/displayParticipants") . "';\n" . "var mapCSVcancelled = '" . App()->createUrl("admin/participants/sa/mapCSVcancelled") . "';\n" . "var characterset = '" . sanitize_paranoid_string($_POST['characterset']) . "';\n" . "var okBtn = '" . gT("OK") . "';\n" . "var processed = '" . gT("Summary") . "';\n" . "var summary = '" . gT("Upload summary") . "';\n" . "var notPairedErrorTxt = '" . gT("You have to pair this field with an existing attribute.") . "';\n" . "var onlyOnePairedErrorTxt = '" . gT("Only one CSV attribute is mapped with central attribute.") . "';\n" . "var cannotAcceptErrorTxt='" . gT("This list cannot accept token attributes.") . "';\n" . "var separator = '" . sanitize_paranoid_string($_POST['separatorused']) . "';\n" . "var thefilepath = '" . $sRandomFileName . "';\n" . "var filterblankemails = '" . sanitize_paranoid_string($filterblankemails) . "';\n";
App()->getClientScript()->registerScript("sAttributeMapJS", $sAttributeMapJS, CClientScript::POS_BEGIN);
$this->_renderWrappedTemplate('participants', 'attributeMapCSV', $aData);
}
}
/**
* Function responsible to import a question.
*
* @access public
* @return void
*/
public function import()
{
$action = returnGlobal('action');
$surveyid = $iSurveyID = returnGlobal('sid');
$gid = returnGlobal('gid');
$aViewUrls = array();
$aData['display']['menu_bars']['surveysummary'] = 'viewquestion';
$aData['display']['menu_bars']['gid_action'] = 'viewgroup';
if ($action == 'importquestion') {
$sFullFilepath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . randomChars(20);
$sExtension = pathinfo($_FILES['the_file']['name'], PATHINFO_EXTENSION);
if (!@move_uploaded_file($_FILES['the_file']['tmp_name'], $sFullFilepath)) {
$fatalerror = sprintf(gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), Yii::app()->getConfig('tempdir'));
}
// validate that we have a SID and GID
if (!$surveyid) {
$fatalerror .= gT("No SID (Survey) has been provided. Cannot import question.");
}
if (!$gid) {
$fatalerror .= gT("No GID (Group) has been provided. Cannot import question");
}
if (isset($fatalerror)) {
unlink($sFullFilepath);
$message = $fatalerror;
$message .= '<p>
<a class="btn btn-default btn-lg"
href="' . $this->getController()->createUrl('admin/survey/sa/listquestions/surveyid/') . '/' . $surveyid . '">' . gT("Return to question list") . '</a></p>';
$this->_renderWrappedTemplate('super', 'messagebox', array('title' => gT('Error'), 'message' => $message));
die;
}
// IF WE GOT THIS FAR, THEN THE FILE HAS BEEN UPLOADED SUCCESFULLY
Yii::app()->loadHelper('admin/import');
if (strtolower($sExtension) == 'lsq') {
$aImportResults = XMLImportQuestion($sFullFilepath, $surveyid, $gid);
} else {
$this->getController()->error(gT('Unknown file extension'));
}
fixLanguageConsistency($surveyid);
if (isset($aImportResults['fatalerror'])) {
//echo htmlentities($aImportResults['fatalerror']); die();
$message = $aImportResults['fatalerror'];
$message .= '<p>
<a class="btn btn-default btn-lg"
href="' . $this->getController()->createUrl('admin/survey/sa/listquestions/surveyid/') . '/' . $surveyid . '">' . gT("Return to question list") . '</a></p>';
$this->_renderWrappedTemplate('super', 'messagebox', array('title' => gT('Error'), 'message' => $message));
die;
}
unlink($sFullFilepath);
$aData['aImportResults'] = $aImportResults;
$aData['surveyid'] = $surveyid;
$aData['gid'] = $gid;
$aData['sExtension'] = $sExtension;
$aViewUrls[] = 'import_view';
}
/////
$aData['sidemenu']['state'] = false;
$aData['surveyid'] = $iSurveyID;
$surveyinfo = Survey::model()->findByPk($iSurveyID)->surveyinfo;
$aData['title_bar']['title'] = $surveyinfo['surveyls_title'] . "(" . gT("ID") . ":" . $iSurveyID . ")";
$this->_renderWrappedTemplate('survey/Question', $aViewUrls, $aData);
}
/**
* Function to import a label set
*
* @access public
* @return void
*/
public function import()
{
$action = returnGlobal('action');
$aViewUrls = array();
if ($action == 'importlabels') {
Yii::app()->loadHelper('admin/import');
$sFullFilepath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . randomChars(20);
$aPathInfo = pathinfo($_FILES['the_file']['name']);
$sExtension = !empty($aPathInfo['extension']) ? $aPathInfo['extension'] : '';
if (!@move_uploaded_file($_FILES['the_file']['tmp_name'], $sFullFilepath)) {
$this->getController()->error(sprintf(gT("An error occurred uploading your file. This may be caused by incorrect permissions in your %s folder."), Yii::app()->getConfig('tempdir')));
}
$options['checkforduplicates'] = 'off';
if (isset($_POST['checkforduplicates'])) {
$options['checkforduplicates'] = $_POST['checkforduplicates'];
}
if (strtolower($sExtension) == 'lsl') {
$aImportResults = XMLImportLabelsets($sFullFilepath, $options);
} else {
$this->getController()->error(gT("Uploaded label set file needs to have an .lsl extension."));
}
unlink($sFullFilepath);
$aViewUrls['import_view'][] = array('aImportResults' => $aImportResults);
}
$this->_renderWrappedTemplate('labels', $aViewUrls);
}
/**
* import from csv
*/
public function import($iSurveyId)
{
$aData = array();
$iSurveyId = (int) $iSurveyId;
if (!Permission::model()->hasSurveyPermission($iSurveyId, 'tokens', 'import')) {
Yii::app()->session['flashmessage'] = gT("You do not have permission to access this page.");
$this->getController()->redirect(array("/admin/survey/sa/view/surveyid/{$iSurveyId}"));
}
// CHECK TO SEE IF A TOKEN TABLE EXISTS FOR THIS SURVEY
$bTokenExists = tableExists('{{tokens_' . $iSurveyId . '}}');
if (!$bTokenExists) {
self::_newtokentable($iSurveyId);
}
$surveyinfo = Survey::model()->findByPk($iSurveyId)->surveyinfo;
$aData['sidemenu']['state'] = false;
$aData["surveyinfo"] = $surveyinfo;
$aData['title_bar']['title'] = $surveyinfo['surveyls_title'] . "(" . gT("ID") . ":" . $iSurveyId . ")";
$aData['sidemenu']["token_menu"] = TRUE;
$aData['token_bar']['closebutton']['url'] = 'admin/tokens/sa/index/surveyid/' . $iSurveyId;
$this->registerScriptFile('ADMIN_SCRIPT_PATH', 'tokensimport.js');
$aEncodings = aEncodingsArray();
if (Yii::app()->request->isPostRequest) {
$sUploadCharset = Yii::app()->request->getPost('csvcharset');
if (!array_key_exists($sUploadCharset, $aEncodings)) {
$sUploadCharset = 'auto';
}
$bFilterDuplicateToken = Yii::app()->request->getPost('filterduplicatetoken');
$bFilterBlankEmail = Yii::app()->request->getPost('filterblankemail');
$bAllowInvalidEmail = Yii::app()->request->getPost('allowinvalidemail');
$aAttrFieldNames = getAttributeFieldNames($iSurveyId);
$aDuplicateList = array();
$aInvalidTokenList = array();
$aInvalidEmailList = array();
$aInvalidFormatList = array();
$aModelErrorList = array();
$aFirstLine = array();
$oFile = CUploadedFile::getInstanceByName("the_file");
$sPath = Yii::app()->getConfig('tempdir');
$sFileName = $sPath . '/' . randomChars(20);
if ($_FILES['the_file']['error'] == 1 || $_FILES['the_file']['error'] == 2) {
Yii::app()->setFlashMessage(sprintf(gT("Sorry, this file is too large. Only files up to %01.2f MB are allowed."), getMaximumFileUploadSize() / 1024 / 1024), 'error');
} elseif (strtolower($oFile->getExtensionName()) != 'csv') {
Yii::app()->setFlashMessage(gT("Only CSV files are allowed."), 'error');
} elseif (!@$oFile->saveAs($sFileName)) {
Yii::app()->setFlashMessage(sprintf(gT("Upload file not found. Check your permissions and path (%s) for the upload directory"), $sPath), 'error');
} else {
$iRecordImported = 0;
$iRecordCount = 0;
$iRecordOk = 0;
$iInvalidEmailCount = 0;
// Count invalid email imported
// This allows to read file with MAC line endings too
@ini_set('auto_detect_line_endings', true);
// open it and trim the ednings
$aTokenListArray = file($sFileName);
$sBaseLanguage = Survey::model()->findByPk($iSurveyId)->language;
if (!Yii::app()->request->getPost('filterduplicatefields') || Yii::app()->request->getPost('filterduplicatefields') && count(Yii::app()->request->getPost('filterduplicatefields')) == 0) {
$aFilterDuplicateFields = array('firstname', 'lastname', 'email');
} else {
$aFilterDuplicateFields = Yii::app()->request->getPost('filterduplicatefields');
}
$sSeparator = Yii::app()->request->getPost('separator');
$aMissingAttrFieldName = $aInvalideAttrFieldName = array();
foreach ($aTokenListArray as $buffer) {
$buffer = @mb_convert_encoding($buffer, "UTF-8", $sUploadCharset);
if ($iRecordCount == 0) {
// Parse first line (header) from CSV
$buffer = removeBOM($buffer);
// We alow all field except tid because this one is really not needed.
$aAllowedFieldNames = Token::model($iSurveyId)->tableSchema->getColumnNames();
if (($kTid = array_search('tid', $aAllowedFieldNames)) !== false) {
unset($aAllowedFieldNames[$kTid]);
}
// Some header don't have same column name
$aReplacedFields = array('invited' => 'sent', 'reminded' => 'remindersent');
switch ($sSeparator) {
case 'comma':
$sSeparator = ',';
break;
case 'semicolon':
$sSeparator = ';';
break;
default:
$comma = substr_count($buffer, ',');
$semicolon = substr_count($buffer, ';');
if ($semicolon > $comma) {
$sSeparator = ';';
} else {
$sSeparator = ',';
}
}
$aFirstLine = str_getcsv($buffer, $sSeparator, '"');
$aFirstLine = array_map('trim', $aFirstLine);
$aIgnoredColumns = array();
// Now check the first line for invalid fields
foreach ($aFirstLine as $index => $sFieldname) {
$aFirstLine[$index] = preg_replace("/(.*) <[^,]*>\$/", "\$1", $sFieldname);
$sFieldname = $aFirstLine[$index];
if (!in_array($sFieldname, $aAllowedFieldNames)) {
$aIgnoredColumns[] = $sFieldname;
}
if (array_key_exists($sFieldname, $aReplacedFields)) {
$aFirstLine[$index] = $aReplacedFields[$sFieldname];
}
// Attribute not in list
if (strpos($aFirstLine[$index], 'attribute_') !== false and !in_array($aFirstLine[$index], $aAttrFieldNames) and Yii::app()->request->getPost('showwarningtoken')) {
$aInvalideAttrFieldName[] = $aFirstLine[$index];
}
}
//compare attributes with source csv
if (Yii::app()->request->getPost('showwarningtoken')) {
$aMissingAttrFieldName = array_diff($aAttrFieldNames, $aFirstLine);
// get list of mandatory attributes
$allAttrFieldNames = GetParticipantAttributes($iSurveyId);
//if it isn't mandantory field we don't need to show in warning
if (!empty($aAttrFieldNames)) {
if (!empty($aMissingAttrFieldName)) {
foreach ($aMissingAttrFieldName as $index => $AttrFieldName) {
if (isset($allAttrFieldNames[$AttrFieldName]) and strtolower($allAttrFieldNames[$AttrFieldName]["mandatory"]) != "y") {
unset($aMissingAttrFieldName[$index]);
}
}
}
if (isset($aInvalideAttrFieldName) and !empty($aInvalideAttrFieldName)) {
foreach ($aInvalideAttrFieldName as $index => $AttrFieldName) {
if (isset($allAttrFieldNames[$AttrFieldName]) and strtolower($allAttrFieldNames[$AttrFieldName]["mandatory"]) != "y") {
unset($aInvalideAttrFieldName[$index]);
}
}
}
}
}
} else {
$line = str_getcsv($buffer, $sSeparator, '"');
if (count($aFirstLine) != count($line)) {
$aInvalidFormatList[] = sprintf(gT("Line %s"), $iRecordCount);
$iRecordCount++;
continue;
}
$aWriteArray = array_combine($aFirstLine, $line);
//kick out ignored columns
foreach ($aIgnoredColumns as $column) {
unset($aWriteArray[$column]);
}
$bDuplicateFound = false;
$bInvalidEmail = false;
$bInvalidToken = false;
$aWriteArray['email'] = isset($aWriteArray['email']) ? trim($aWriteArray['email']) : "";
$aWriteArray['firstname'] = isset($aWriteArray['firstname']) ? $aWriteArray['firstname'] : "";
$aWriteArray['lastname'] = isset($aWriteArray['lastname']) ? $aWriteArray['lastname'] : "";
$aWriteArray['language'] = isset($aWriteArray['language']) ? $aWriteArray['language'] : $sBaseLanguage;
if ($bFilterDuplicateToken) {
$aParams = array();
$oCriteria = new CDbCriteria();
$oCriteria->condition = "";
foreach ($aFilterDuplicateFields as $field) {
if (isset($aWriteArray[$field])) {
$oCriteria->addCondition("{$field} = :{$field}");
$aParams[":{$field}"] = $aWriteArray[$field];
}
}
if (!empty($aParams)) {
$oCriteria->params = $aParams;
}
$dupresult = TokenDynamic::model($iSurveyId)->count($oCriteria);
if ($dupresult > 0) {
$bDuplicateFound = true;
$aDuplicateList[] = sprintf(gT("Line %s : %s %s (%s)"), $iRecordCount, $aWriteArray['firstname'], $aWriteArray['lastname'], $aWriteArray['email']);
}
}
//treat blank emails
if (!$bDuplicateFound && $bFilterBlankEmail && $aWriteArray['email'] == '') {
$bInvalidEmail = true;
$aInvalidEmailList[] = sprintf(gT("Line %s : %s %s"), $iRecordCount, CHtml::encode($aWriteArray['firstname']), CHtml::encode($aWriteArray['lastname']));
}
if (!$bDuplicateFound && $aWriteArray['email'] != '') {
$aEmailAddresses = preg_split("/(,|;)/", $aWriteArray['email']);
foreach ($aEmailAddresses as $sEmailaddress) {
if (!validateEmailAddress($sEmailaddress)) {
if ($bAllowInvalidEmail) {
$iInvalidEmailCount++;
if (empty($aWriteArray['emailstatus']) || strtoupper($aWriteArray['emailstatus'] == "OK")) {
$aWriteArray['emailstatus'] = "invalid";
}
} else {
$bInvalidEmail = true;
$aInvalidEmailList[] = sprintf(gT("Line %s : %s %s (%s)"), $iRecordCount, CHtml::encode($aWriteArray['firstname']), CHtml::encode($aWriteArray['lastname']), CHtml::encode($aWriteArray['email']));
}
}
}
}
if (!$bDuplicateFound && !$bInvalidEmail && isset($aWriteArray['token']) && trim($aWriteArray['token']) != '') {
if (trim($aWriteArray['token']) != sanitize_token($aWriteArray['token'])) {
$aInvalidTokenList[] = sprintf(gT("Line %s : %s %s (%s) - token : %s"), $iRecordCount, CHtml::encode($aWriteArray['firstname']), CHtml::encode($aWriteArray['lastname']), CHtml::encode($aWriteArray['email']), CHtml::encode($aWriteArray['token']));
$bInvalidToken = true;
}
// We allways search for duplicate token (it's in model. Allow to reset or update token ?
if (Token::model($iSurveyId)->count("token=:token", array(":token" => $aWriteArray['token']))) {
$bDuplicateFound = true;
$aDuplicateList[] = sprintf(gT("Line %s : %s %s (%s) - token : %s"), $iRecordCount, CHtml::encode($aWriteArray['firstname']), CHtml::encode($aWriteArray['lastname']), CHtml::encode($aWriteArray['email']), CHtml::encode($aWriteArray['token']));
}
}
if (!$bDuplicateFound && !$bInvalidEmail && !$bInvalidToken) {
// unset all empty value
foreach ($aWriteArray as $key => $value) {
if ($aWriteArray[$key] == "") {
unset($aWriteArray[$key]);
}
if (substr($value, 0, 1) == '"' && substr($value, -1) == '"') {
// Fix CSV quote
$value = substr($value, 1, -1);
}
}
// Some default value : to be moved to Token model rules in future release ?
// But think we have to accept invalid email etc ... then use specific scenario
$oToken = Token::create($iSurveyId);
if ($bAllowInvalidEmail) {
$oToken->scenario = 'allowinvalidemail';
}
foreach ($aWriteArray as $key => $value) {
$oToken->{$key} = $value;
}
if (!$oToken->save()) {
$errors = $oToken->getErrors();
$aModelErrorList[] = sprintf(gT("Line %s : %s"), $iRecordCount, print_r($errors, true));
} else {
$iRecordImported++;
}
}
$iRecordOk++;
}
$iRecordCount++;
}
$iRecordCount = $iRecordCount - 1;
unlink($sFileName);
$aData['aTokenListArray'] = $aTokenListArray;
// Big array in memory, just for success ?
$aData['iRecordImported'] = $iRecordImported;
$aData['iRecordOk'] = $iRecordOk;
$aData['iRecordCount'] = $iRecordCount;
$aData['aFirstLine'] = $aFirstLine;
// Seem not needed
$aData['aDuplicateList'] = $aDuplicateList;
$aData['aInvalidTokenList'] = $aInvalidTokenList;
$aData['aInvalidFormatList'] = $aInvalidFormatList;
$aData['aInvalidEmailList'] = $aInvalidEmailList;
$aData['aModelErrorList'] = $aModelErrorList;
$aData['iInvalidEmailCount'] = $iInvalidEmailCount;
$aData['thissurvey'] = getSurveyInfo($iSurveyId);
$aData['iSurveyId'] = $aData['surveyid'] = $iSurveyId;
$aData['aInvalideAttrFieldName'] = $aInvalideAttrFieldName;
$aData['aMissingAttrFieldName'] = $aMissingAttrFieldName;
$this->_renderWrappedTemplate('token', array('csvimportresult'), $aData);
Yii::app()->end();
}
}
// If there are error with file : show the form
$aData['aEncodings'] = $aEncodings;
asort($aData['aEncodings']);
$aData['iSurveyId'] = $iSurveyId;
$aData['thissurvey'] = getSurveyInfo($iSurveyId);
$aData['surveyid'] = $iSurveyId;
$aTokenTableFields = getTokenFieldsAndNames($iSurveyId);
unset($aTokenTableFields['sent']);
unset($aTokenTableFields['remindersent']);
unset($aTokenTableFields['remindercount']);
unset($aTokenTableFields['usesleft']);
foreach ($aTokenTableFields as $sKey => $sValue) {
if ($sValue['description'] != $sKey) {
$sValue['description'] .= ' - ' . $sKey;
}
$aNewTokenTableFields[$sKey] = $sValue['description'];
}
$aData['aTokenTableFields'] = $aNewTokenTableFields;
// Get default character set from global settings
$thischaracterset = getGlobalSetting('characterset');
// If no encoding was set yet, use the old "auto" default
if ($thischaracterset == "") {
$thischaracterset = "auto";
}
$aData['thischaracterset'] = $thischaracterset;
$this->_renderWrappedTemplate('token', array('csvupload'), $aData);
}
/**
* RPC Routine to import a question - imports lsq,csv.
*
* @access public
* @param string $sSessionKey
* @param int $iSurveyID The id of the survey that the question will belong
* @param int $iGroupID The id of the group that the question will belong
* @param string $sImportData String containing the BASE 64 encoded data of a lsg,csv
* @param string $sImportDataType lsq,csv
* @param string $sMandatory Optional Mandatory question option (default to No)
* @param string $sNewQuestionTitle Optional new title for the question
* @param string $sNewqQuestion An optional new question
* @param string $sNewQuestionHelp An optional new question help text
* @return array|integer iQuestionID - ID of the new question - Or status
*/
public function import_question($sSessionKey, $iSurveyID, $iGroupID, $sImportData, $sImportDataType, $sMandatory = 'N', $sNewQuestionTitle = NULL, $sNewqQuestion = NULL, $sNewQuestionHelp = NULL)
{
if ($this->_checkSessionKey($sSessionKey)) {
$oSurvey = Survey::model()->findByPk($iSurveyID);
if (!isset($oSurvey)) {
return array('status' => 'Error: Invalid survey ID');
}
if (Permission::model()->hasSurveyPermission($iSurveyID, 'survey', 'update')) {
if ($oSurvey->getAttribute('active') == 'Y') {
return array('status' => 'Error:Survey is Active and not editable');
}
$oGroup = QuestionGroup::model()->findByAttributes(array('gid' => $iGroupID));
if (!isset($oGroup)) {
return array('status' => 'Error: Invalid group ID');
}
$sGroupSurveyID = $oGroup['sid'];
if ($sGroupSurveyID != $iSurveyID) {
return array('status' => 'Error: Missmatch in surveyid and groupid');
}
if (!in_array($sImportDataType, array('csv', 'lsq'))) {
return array('status' => 'Invalid extension');
}
libxml_use_internal_errors(true);
Yii::app()->loadHelper('admin/import');
// First save the data to a temporary file
$sFullFilePath = Yii::app()->getConfig('tempdir') . DIRECTORY_SEPARATOR . randomChars(40) . '.' . $sImportDataType;
file_put_contents($sFullFilePath, base64_decode(chunk_split($sImportData)));
if (strtolower($sImportDataType) == 'lsq') {
$sXMLdata = file_get_contents($sFullFilePath);
$xml = @simplexml_load_string($sXMLdata, 'SimpleXMLElement', LIBXML_NONET);
if (!$xml) {
unlink($sFullFilePath);
return array('status' => 'Error: Invalid LimeSurvey question structure XML ');
}
$aImportResults = XMLImportQuestion($sFullFilePath, $iSurveyID, $iGroupID);
} else {
return array('status' => 'Really Invalid extension');
}
//just for symmetry!
unlink($sFullFilePath);
if (isset($aImportResults['fatalerror'])) {
return array('status' => 'Error: ' . $aImportResults['fatalerror']);
} else {
fixLanguageConsistency($iSurveyID);
$iNewqid = $aImportResults['newqid'];
$oQuestion = Question::model()->findByAttributes(array('sid' => $iSurveyID, 'gid' => $iGroupID, 'qid' => $iNewqid));
if ($sNewQuestionTitle != NULL) {
$oQuestion->setAttribute('title', $sNewQuestionTitle);
}
if ($sNewqQuestion != '') {
$oQuestion->setAttribute('question', $sNewqQuestion);
}
if ($sNewQuestionHelp != '') {
$oQuestion->setAttribute('help', $sNewQuestionHelp);
}
if (in_array($sMandatory, array('Y', 'N'))) {
$oQuestion->setAttribute('mandatory', $sMandatory);
} else {
$oQuestion->setAttribute('mandatory', 'N');
}
try {
$oQuestion->save();
} catch (Exception $e) {
// no need to throw exception
}
return (int) $aImportResults['newqid'];
}
} else {
return array('status' => 'No permission');
}
} else {
return array('status' => 'Invalid session key');
}
}
/**
* Run an arbitrary sequence of semicolon-delimited SQL commands
*
* Assumes that the input text (file or string) consists of
* a number of SQL statements ENDING WITH SEMICOLONS. The
* semicolons MUST be the last character in a line.
* Lines that are blank or that start with "#" or "--" (postgres) are ignored.
* Only tested with mysql dump files (mysqldump -p -d limesurvey)
* Function kindly borrowed by Moodle
* @param string $sqlfile The path where a file with sql commands can be found on the server.
* @param string $sqlstring If no path is supplied then a string with semicolon delimited sql
* commands can be supplied in this argument.
* @return bool Returns true if database was modified successfully.
*/
function modifyDatabase($sqlfile = '', $sqlstring = '')
{
Yii::app()->loadHelper('database');
$clang = Yii::app()->lang;
global $siteadminemail;
global $siteadminname;
global $codeString;
global $modifyoutput;
$success = true;
// Let's be optimistic
$modifyoutput = '';
if (!empty($sqlfile)) {
if (!is_readable($sqlfile)) {
$success = false;
echo '<p>Tried to modify database, but "' . $sqlfile . '" doesn\'t exist!</p>';
return $success;
} else {
$lines = file($sqlfile);
}
} else {
$sqlstring = trim($sqlstring);
if ($sqlstring[strlen($sqlstring) - 1] != ";") {
$sqlstring .= ";";
// add it in if it's not there.
}
$lines[] = $sqlstring;
}
$command = '';
foreach ($lines as $line) {
$line = rtrim($line);
$length = strlen($line);
if ($length and $line[0] != '#' and substr($line, 0, 2) != '--') {
if (substr($line, $length - 1, 1) == ';') {
$line = substr($line, 0, $length - 1);
// strip ;
$command .= $line;
$command = str_replace('prefix_', Yii::app()->db->tablePrefix, $command);
// Table prefixes
$command = str_replace('$defaultuser', Yii::app()->getConfig('defaultuser'), $command);
$command = str_replace('$defaultpass', hash('sha256', Yii::app()->getConfig('defaultpass')), $command);
$command = str_replace('$siteadminname', $siteadminname, $command);
$command = str_replace('$siteadminemail', $siteadminemail, $command);
$command = str_replace('$defaultlang', Yii::app()->getConfig('defaultlang'), $command);
$command = str_replace('$sessionname', 'ls' . randomChars(20, '123456789'), $command);
$command = str_replace('$databasetabletype', Yii::app()->db->getDriverName(), $command);
try {
Yii::app()->db->createCommand($command)->query();
//Checked
$command = htmlspecialchars($command);
$modifyoutput .= ". ";
} catch (CDbException $e) {
$command = htmlspecialchars($command);
$modifyoutput .= "<br />" . sprintf($clang->gT("SQL command failed: %s"), "<span style='font-size:10px;'>" . $command . "</span>", "<span style='color:#ee0000;font-size:10px;'></span><br/>");
$success = false;
}
$command = '';
} else {
$command .= $line;
}
}
}
return $success;
}
/**
* Create a backup of the DataBase
* @return array result of backup
*/
private function _createDbBackup()
{
Yii::app()->loadHelper("admin/backupdb");
$backupDb = new stdClass();
$basefilename = dateShift(date("Y-m-d H:i:s"), "Y-m-d", Yii::app()->getConfig('timeadjust')) . '_' . md5(uniqid(rand(), true));
$sfilename = $this->tempdir . DIRECTORY_SEPARATOR . "backup_db_" . randomChars(20) . "_" . dateShift(date("Y-m-d H:i:s"), "Y-m-d", Yii::app()->getConfig('timeadjust')) . ".sql";
$dfilename = $this->tempdir . DIRECTORY_SEPARATOR . "LimeSurvey_database_backup_" . $basefilename . ".zip";
outputDatabase('', false, $sfilename);
if (is_file($sfilename) && filesize($sfilename)) {
$archive = new PclZip($dfilename);
$v_list = $archive->add(array($sfilename), PCLZIP_OPT_REMOVE_PATH, $this->tempdir, PCLZIP_OPT_ADD_TEMP_FILE_ON);
unlink($sfilename);
if ($v_list == 0) {
$backupDb->result = FALSE;
$backupDb->message = 'db_backup_zip_failed';
} else {
$backupDb->result = TRUE;
$backupDb->message = htmlspecialchars($dfilename);
}
} else {
$backupDb->result = FALSE;
$backupDb->message = htmlspecialchars(db_backup_failed);
}
return $backupDb;
}
/**
* Creates a new survey - does some basic checks of the suppplied data
*
* @param array $aData Array with fieldname=>fieldcontents data
* @return integer The new survey id
*/
public function insertNewSurvey($aData)
{
do {
if (isset($aData['wishSID'])) {
$aData['sid'] = $aData['wishSID'];
unset($aData['wishSID']);
} else {
$aData['sid'] = randomChars(6, '123456789');
}
$isresult = self::model()->findByPk($aData['sid']);
} while (!is_null($isresult));
$survey = new self();
foreach ($aData as $k => $v) {
$survey->{$k} = $v;
}
$sResult = $survey->save();
if (!$sResult) {
tracevar($survey->getErrors());
return false;
} else {
return $aData['sid'];
}
}
/**
* Exports a archive (ZIP) of the current survey (structure, responses, timings, tokens)
*
* @param integer $iSurveyID The ID of the survey to export
* @param boolean $bSendToBrowser If TRUE (default) then the ZIP file is sent to the browser
* @return string Full path of the ZIP filename if $bSendToBrowser is set to TRUE, otherwise no return value
*/
private function _exportarchive($iSurveyID, $bSendToBrowser = TRUE)
{
$aSurveyInfo = getSurveyInfo($iSurveyID);
$sTempDir = Yii::app()->getConfig("tempdir");
$aZIPFileName = $sTempDir . DIRECTORY_SEPARATOR . randomChars(30);
$sLSSFileName = $sTempDir . DIRECTORY_SEPARATOR . randomChars(30);
$sLSRFileName = $sTempDir . DIRECTORY_SEPARATOR . randomChars(30);
$sLSTFileName = $sTempDir . DIRECTORY_SEPARATOR . randomChars(30);
$sLSIFileName = $sTempDir . DIRECTORY_SEPARATOR . randomChars(30);
Yii::import('application.libraries.admin.pclzip.pclzip', TRUE);
$zip = new PclZip($aZIPFileName);
file_put_contents($sLSSFileName, surveyGetXMLData($iSurveyID));
$this->_addToZip($zip, $sLSSFileName, 'survey_' . $iSurveyID . '.lss');
unlink($sLSSFileName);
if ($aSurveyInfo['active'] == 'Y') {
getXMLDataSingleTable($iSurveyID, 'survey_' . $iSurveyID, 'Responses', 'responses', $sLSRFileName, FALSE);
$this->_addToZip($zip, $sLSRFileName, 'survey_' . $iSurveyID . '_responses.lsr');
unlink($sLSRFileName);
}
if (Yii::app()->db->schema->getTable('{{tokens_' . $iSurveyID . '}}')) {
getXMLDataSingleTable($iSurveyID, 'tokens_' . $iSurveyID, 'Tokens', 'tokens', $sLSTFileName);
$this->_addToZip($zip, $sLSTFileName, 'survey_' . $iSurveyID . '_tokens.lst');
unlink($sLSTFileName);
}
if (Yii::app()->db->schema->getTable('{{survey_' . $iSurveyID . '_timings}}')) {
getXMLDataSingleTable($iSurveyID, 'survey_' . $iSurveyID . '_timings', 'Timings', 'timings', $sLSIFileName);
$this->_addToZip($zip, $sLSIFileName, 'survey_' . $iSurveyID . '_timings.lsi');
unlink($sLSIFileName);
}
if (is_file($aZIPFileName)) {
if ($bSendToBrowser) {
$fn = "survey_archive_{$iSurveyID}.lsa";
//Send the file for download!
$this->_addHeaders($fn, "application/force-download", 0);
@readfile($aZIPFileName);
//Delete the temporary file
unlink($aZIPFileName);
return;
} else {
return $aZIPFileName;
}
}
}
/**
* Cleanse the $_POSTed data and update $_SESSION variables accordingly
*/
static function ProcessCurrentResponses()
{
$LEM =& LimeExpressionManager::singleton();
if (!isset($LEM->currentQset)) {
return array();
}
$updatedValues = array();
$radixchange = $LEM->surveyOptions['radix'] == ',' ? true : false;
foreach ($LEM->currentQset as $qinfo) {
$relevant = false;
$qid = $qinfo['info']['qid'];
$gseq = $qinfo['info']['gseq'];
$relevant = isset($_POST['relevance' . $qid]) ? $_POST['relevance' . $qid] == 1 : false;
$grelevant = isset($_POST['relevanceG' . $gseq]) ? $_POST['relevanceG' . $gseq] == 1 : false;
$_SESSION[$LEM->sessid]['relevanceStatus'][$qid] = $relevant;
$_SESSION[$LEM->sessid]['relevanceStatus']['G' . $gseq] = $grelevant;
foreach (explode('|', $qinfo['sgqa']) as $sq) {
$sqrelevant = true;
if (isset($LEM->subQrelInfo[$qid][$sq]['rowdivid'])) {
$rowdivid = $LEM->subQrelInfo[$qid][$sq]['rowdivid'];
if ($rowdivid != '' && isset($_POST['relevance' . $rowdivid])) {
$sqrelevant = $_POST['relevance' . $rowdivid] == 1;
$_SESSION[$LEM->sessid]['relevanceStatus'][$rowdivid] = $sqrelevant;
}
}
$type = $qinfo['info']['type'];
if ($relevant && $grelevant && $sqrelevant || !$LEM->surveyOptions['deletenonvalues']) {
if ($qinfo['info']['hidden'] && !isset($_POST[$sq])) {
$value = isset($_SESSION[$LEM->sessid][$sq]) ? $_SESSION[$LEM->sessid][$sq] : '';
// if always hidden, use the default value, if any
} else {
$value = isset($_POST[$sq]) ? $_POST[$sq] : '';
}
// Check for and adjust ',' and '.' in numbers
$isOnlyNum = isset($LEM->knownVars[$sq]['onlynum']) && $LEM->knownVars[$sq]['onlynum'] == '1';
if ($radixchange && $isOnlyNum) {
// Convert from comma back to decimal
// Also make sure to be able to convert numbers like 1.100,10
$value = preg_replace('|\\.|', '', $value);
$value = preg_replace('|\\,|', '.', $value);
} elseif (!$radixchange && $isOnlyNum) {
// Still have to remove all ',' introduced by the thousand separator
$value = preg_replace('|\\,|', '', $value);
}
switch ($type) {
case 'D':
//DATE
$value = trim($value);
if ($value != "" && $value != "INVALID") {
$aAttributes = $LEM->getQuestionAttributesForEM($LEM->sid, $qid, $_SESSION['LEMlang']);
if (!isset($aAttributes[$qid])) {
$aAttributes[$qid] = array();
}
$aDateFormatData = getDateFormatDataForQID($aAttributes[$qid], $LEM->surveyOptions);
// We don't really validate date here : if date is invalid : return 1999-12-01 00:00
$oDateTimeConverter = new Date_Time_Converter(trim($value), $aDateFormatData['phpdate']);
$newValue = $oDateTimeConverter->convert("Y-m-d H:i");
$oDateTimeConverter = new Date_Time_Converter($newValue, "Y-m-d H:i");
if ($value == $oDateTimeConverter->convert($aDateFormatData['phpdate'])) {
$value = $newValue;
} else {
$value = "";
// Or $value="INVALID" ? : dropdown is OK with this not default.
}
}
break;
# case 'N': //NUMERICAL QUESTION TYPE
# case 'K': //MULTIPLE NUMERICAL QUESTION
# if (trim($value)=="") {
# $value = "";
# }
# else {
# $value = sanitize_float($value);
# }
break;
case '|':
//File Upload
if (!preg_match('/_filecount$/', $sq)) {
$json = $value;
$phparray = json_decode(stripslashes($json));
// if the files have not been saved already,
// move the files from tmp to the files folder
$tmp = $LEM->surveyOptions['tempdir'] . 'upload' . DIRECTORY_SEPARATOR;
if (!is_null($phparray) && count($phparray) > 0) {
// Move the (unmoved, temp) files from temp to files directory.
// Check all possible file uploads
for ($i = 0; $i < count($phparray); $i++) {
if (file_exists($tmp . $phparray[$i]->filename)) {
$sDestinationFileName = 'fu_' . randomChars(15);
if (!is_dir($LEM->surveyOptions['target'])) {
mkdir($LEM->surveyOptions['target'], 0777, true);
}
if (!rename($tmp . $phparray[$i]->filename, $LEM->surveyOptions['target'] . $sDestinationFileName)) {
echo "Error moving file to target destination";
}
$phparray[$i]->filename = $sDestinationFileName;
}
}
$value = ls_json_encode($phparray);
// so that EM doesn't try to parse it.
}
}
break;
}
$_SESSION[$LEM->sessid][$sq] = $value;
$_update = array('type' => $type, 'value' => $value);
$updatedValues[$sq] = $_update;
$LEM->updatedValues[$sq] = $_update;
} else {
// irrelevant, so database will be NULLed separately
// Must unset the value, rather than setting to '', so that EM can re-use the default value as needed.
unset($_SESSION[$LEM->sessid][$sq]);
$_update = array('type' => $type, 'value' => NULL);
$updatedValues[$sq] = $_update;
$LEM->updatedValues[$sq] = $_update;
}
}
}
if (isset($_POST['timerquestion'])) {
$_SESSION[$LEM->sessid][$_POST['timerquestion']] = sanitize_float($_POST[$_POST['timerquestion']]);
}
return $updatedValues;
}
/**
* dataentry::insert()
* insert new dataentry
* @return
*/
public function insert()
{
$clang = Yii::app()->lang;
$subaction = Yii::app()->request->getPost('subaction');
$surveyid = Yii::app()->request->getPost('sid');
$lang = isset($_POST['lang']) ? Yii::app()->request->getPost('lang') : NULL;
$aData = array('surveyid' => $surveyid, 'lang' => $lang, 'clang' => $clang);
if (hasSurveyPermission($surveyid, 'responses', 'read')) {
if ($subaction == "insert" && hasSurveyPermission($surveyid, 'responses', 'create')) {
$surveytable = "{{survey_{$surveyid}}}";
$thissurvey = getSurveyInfo($surveyid);
$errormsg = "";
Yii::app()->loadHelper("database");
$aViewUrls['display']['menu_bars']['browse'] = $clang->gT("Data entry");
$aDataentryoutput = '';
$aDataentrymsgs = array();
$hiddenfields = '';
$lastanswfortoken = '';
// check if a previous answer has been submitted or saved
$rlanguage = '';
if (isset($_POST['token'])) {
$tokencompleted = "";
$tcquery = "SELECT completed from {{tokens_{$surveyid}}} WHERE token='{$_POST['token']}'";
//dbQuoteAll($_POST['token'],true);
$tcresult = dbExecuteAssoc($tcquery);
$tcresult = $tcresult->readAll();
$tccount = count($tcresult);
foreach ($tcresult as $tcrow) {
$tokencompleted = $tcrow['completed'];
}
if ($tccount < 1) {
// token doesn't exist in token table
$lastanswfortoken = 'UnknownToken';
} elseif ($thissurvey['anonymized'] == "Y") {
// token exist but survey is anonymous, check completed state
if ($tokencompleted != "" && $tokencompleted != "N") {
// token is completed
$lastanswfortoken = 'PrivacyProtected';
}
} else {
// token is valid, survey not anonymous, try to get last recorded response id
$aquery = "SELECT id,startlanguage FROM {$surveytable} WHERE token='" . $_POST['token'] . "'";
//dbQuoteAll($_POST['token'],true);
$aresult = dbExecuteAssoc($aquery);
foreach ($aresult->readAll() as $arow) {
if ($tokencompleted != "N") {
$lastanswfortoken = $arow['id'];
}
$rlanguage = $arow['startlanguage'];
}
}
}
// First Check if the survey uses tokens and if a token has been provided
if (tableExists('{{tokens_' . $thissurvey['sid'] . '}}') && !$_POST['token']) {
$errormsg = CHtml::tag('div', array('class' => 'warningheader'), $clang->gT("Error"));
$errormsg .= CHtml::tag('p', array(), $clang->gT("This is a closed-access survey, so you must supply a valid token. Please contact the administrator for assistance."));
} elseif (tableExists('{{tokens_' . $thissurvey['sid'] . '}}') && $lastanswfortoken == 'UnknownToken') {
$errormsg = CHtml::tag('div', array('class' => 'warningheader'), $clang->gT("Error"));
$errormsg .= CHtml::tag('p', array(), $clang->gT("The token you have provided is not valid or has already been used."));
} elseif (tableExists('{{tokens_' . $thissurvey['sid'] . '}}') && $lastanswfortoken != '') {
$errormsg = CHtml::tag('div', array('class' => 'warningheader'), $clang->gT("Error"));
$errormsg .= CHtml::tag('p', array(), $clang->gT("There is already a recorded answer for this token"));
if ($lastanswfortoken != 'PrivacyProtected') {
$errormsg .= "<br /><br />" . $clang->gT("Follow the following link to update it") . ":\n";
$errormsg .= CHtml::link("[id:{$lastanswfortoken}]", Yii::app()->baseUrl . ('/admin/dataentry/editdata/subaction/edit/id/' . $lastanswfortoken . '/surveyid/' . $surveyid . '/lang/' . $rlanguage), array('title' => $clang->gT("Edit this entry")));
} else {
$errormsg .= "<br /><br />" . $clang->gT("This surveys uses anonymized responses, so you can't update your response.") . "\n";
}
} else {
$last_db_id = 0;
if (isset($_POST['save']) && $_POST['save'] == "on") {
$aData['save'] = TRUE;
$saver['identifier'] = $_POST['save_identifier'];
$saver['language'] = $_POST['save_language'];
$saver['password'] = $_POST['save_password'];
$saver['passwordconfirm'] = $_POST['save_confirmpassword'];
$saver['email'] = $_POST['save_email'];
if (!returnGlobal('redo')) {
$password = md5($saver['password']);
} else {
$password = $saver['password'];
}
$errormsg = "";
if (!$saver['identifier']) {
$errormsg .= $clang->gT("Error") . ": " . $clang->gT("You must supply a name for this saved session.");
}
if (!$saver['password']) {
$errormsg .= $clang->gT("Error") . ": " . $clang->gT("You must supply a password for this saved session.");
}
if ($saver['password'] != $saver['passwordconfirm']) {
$errormsg .= $clang->gT("Error") . ": " . $clang->gT("Your passwords do not match.");
}
$aData['errormsg'] = $errormsg;
if ($errormsg) {
foreach ($_POST as $key => $val) {
if (substr($key, 0, 4) != "save" && $key != "action" && $key != "sid" && $key != "datestamp" && $key != "ipaddr") {
$hiddenfields .= CHtml::hiddenField($key, $val);
//$aDataentryoutput .= "<input type='hidden' name='$key' value='$val' />\n";
}
}
}
}
//BUILD THE SQL TO INSERT RESPONSES
$baselang = Survey::model()->findByPk($surveyid)->language;
$fieldmap = createFieldMap($surveyid, 'full', false, false, getBaseLanguageFromSurveyID($surveyid));
$insert_data = array();
$_POST['startlanguage'] = $baselang;
if ($thissurvey['datestamp'] == "Y") {
$_POST['startdate'] = $_POST['datestamp'];
}
if (isset($_POST['closerecord'])) {
if ($thissurvey['datestamp'] == "Y") {
$_POST['submitdate'] = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", Yii::app()->getConfig('timeadjust'));
} else {
$_POST['submitdate'] = date("Y-m-d H:i:s", mktime(0, 0, 0, 1, 1, 1980));
}
}
foreach ($fieldmap as $irow) {
$fieldname = $irow['fieldname'];
if (isset($_POST[$fieldname])) {
if ($_POST[$fieldname] == "" && ($irow['type'] == 'D' || $irow['type'] == 'N' || $irow['type'] == 'K')) {
// can't add '' in Date column
// Do nothing
} else {
if ($irow['type'] == '|') {
if (!strpos($irow['fieldname'], "_filecount")) {
$json = $_POST[$fieldname];
$phparray = json_decode(stripslashes($json));
$filecount = 0;
for ($i = 0; $filecount < count($phparray); $i++) {
if ($_FILES[$fieldname . "_file_" . $i]['error'] != 4) {
$target = Yii::app()->getConfig('uploaddir') . "/surveys/" . $thissurvey['sid'] . "/files/" . randomChars(20);
$size = 0.001 * $_FILES[$fieldname . "_file_" . $i]['size'];
$name = rawurlencode($_FILES[$fieldname . "_file_" . $i]['name']);
if (move_uploaded_file($_FILES[$fieldname . "_file_" . $i]['tmp_name'], $target)) {
$phparray[$filecount]->filename = basename($target);
$phparray[$filecount]->name = $name;
$phparray[$filecount]->size = $size;
$pathinfo = pathinfo($_FILES[$fieldname . "_file_" . $i]['name']);
$phparray[$filecount]->ext = $pathinfo['extension'];
$filecount++;
}
}
}
$insert_data[$fieldname] = ls_json_encode($phparray);
} else {
$insert_data[$fieldname] = count($phparray);
}
} elseif ($irow['type'] == 'D') {
Yii::app()->loadLibrary('Date_Time_Converter');
$qidattributes = getQuestionAttributeValues($irow['qid'], $irow['type']);
$dateformatdetails = getDateFormatDataForQID($qidattributes, $thissurvey);
$datetimeobj = new Date_Time_Converter($_POST[$fieldname], $dateformatdetails['phpdate']);
$insert_data[$fieldname] = $datetimeobj->convert("Y-m-d H:i:s");
} else {
$insert_data[$fieldname] = $_POST[$fieldname];
}
}
}
}
Survey_dynamic::sid($surveyid);
$new_response = new Survey_dynamic();
foreach ($insert_data as $column => $value) {
$new_response->{$column} = $value;
}
$new_response->save();
$last_db_id = $new_response->getPrimaryKey();
if (isset($_POST['closerecord']) && isset($_POST['token']) && $_POST['token'] != '') {
// get submit date
if (isset($_POST['closedate'])) {
$submitdate = $_POST['closedate'];
} else {
$submitdate = dateShift(date("Y-m-d H:i:s"), "Y-m-d", $timeadjust);
}
// check how many uses the token has left
$usesquery = "SELECT usesleft FROM {{tokens_}}{$surveyid} WHERE token='" . $_POST['token'] . "'";
$usesresult = dbExecuteAssoc($usesquery);
$usesrow = $usesresult->readAll();
//$usesresult->row_array()
if (isset($usesrow)) {
$usesleft = $usesrow[0]['usesleft'];
}
// query for updating tokens
$utquery = "UPDATE {{tokens_{$surveyid}}}\n";
if (isTokenCompletedDatestamped($thissurvey)) {
if (isset($usesleft) && $usesleft <= 1) {
$utquery .= "SET usesleft=usesleft-1, completed='{$submitdate}'\n";
} else {
$utquery .= "SET usesleft=usesleft-1\n";
}
} else {
if (isset($usesleft) && $usesleft <= 1) {
$utquery .= "SET usesleft=usesleft-1, completed='Y'\n";
} else {
$utquery .= "SET usesleft=usesleft-1\n";
}
}
$utquery .= "WHERE token='" . $_POST['token'] . "'";
$utresult = dbExecuteAssoc($utquery);
//Yii::app()->db->Execute($utquery) or safeDie ("Couldn't update tokens table!<br />\n$utquery<br />\n".Yii::app()->db->ErrorMsg());
// save submitdate into survey table
$srid = Yii::app()->db->getLastInsertID();
// Yii::app()->db->getLastInsertID();
$sdquery = "UPDATE {{survey_{$surveyid}}} SET submitdate='" . $submitdate . "' WHERE id={$srid}\n";
$sdresult = dbExecuteAssoc($sdquery) or safeDie("Couldn't set submitdate response in survey table!<br />\n{$sdquery}<br />\n");
$last_db_id = Yii::app()->db->getLastInsertID();
}
if (isset($_POST['save']) && $_POST['save'] == "on") {
$srid = Yii::app()->db->getLastInsertID();
//Yii::app()->db->getLastInsertID();
$aUserData = Yii::app()->session;
//CREATE ENTRY INTO "saved_control"
$saved_control_table = '{{saved_control}}';
$columns = array("sid", "srid", "identifier", "access_code", "email", "ip", "refurl", 'saved_thisstep', "status", "saved_date");
$values = array("'" . $surveyid . "'", "'" . $srid . "'", "'" . $saver['identifier'] . "'", "'" . $password . "'", "'" . $saver['email'] . "'", "'" . $aUserData['ip_address'] . "'", "'" . getenv("HTTP_REFERER") . "'", 0, "'" . "S" . "'", "'" . dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", "'" . Yii::app()->getConfig('timeadjust')) . "'");
$SQL = "INSERT INTO {$saved_control_table}\n (" . implode(',', $columns) . ")\n VALUES\n (" . implode(',', $values) . ")";
/*$scdata = array("sid"=>$surveyid,
"srid"=>$srid,
"identifier"=>$saver['identifier'],
"access_code"=>$password,
"email"=>$saver['email'],
"ip"=>$aUserData['ip_address'],
"refurl"=>getenv("HTTP_REFERER"),
'saved_thisstep' => 0,
"status"=>"S",
"saved_date"=>dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", Yii::app()->getConfig('timeadjust')));
$this->load->model('saved_control_model');*/
if (dbExecuteAssoc($SQL)) {
$scid = Yii::app()->db->getLastInsertID();
// Yii::app()->db->getLastInsertID("{{saved_control}}","scid");
$aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), $clang->gT("Your survey responses have been saved successfully. You will be sent a confirmation e-mail. Please make sure to save your password, since we will not be able to retrieve it for you."));
//$aDataentryoutput .= "<font class='successtitle'></font><br />\n";
$tokens_table = "{{tokens_{$surveyid}}}";
$last_db_id = Yii::app()->db->getLastInsertID();
if (tableExists($tokens_table)) {
$tkquery = "SELECT * FROM {$tokens_table}";
$tkresult = dbExecuteAssoc($tkquery);
/*$tokendata = array (
"firstname"=> $saver['identifier'],
"lastname"=> $saver['identifier'],
"email"=>$saver['email'],
"token"=>randomChars(15),
"language"=>$saver['language'],
"sent"=>dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i", $timeadjust),
"completed"=>"N");*/
$columns = array("firstname", "lastname", "email", "token", "language", "sent", "completed");
$values = array("'" . $saver['identifier'] . "'", "'" . $saver['identifier'] . "'", "'" . $saver['email'] . "'", "'" . $password . "'", "'" . randomChars(15) . "'", "'" . $saver['language'] . "'", "'" . "N" . "'");
$SQL = "INSERT INTO {$token_table}\n (" . implode(',', $columns) . ")\n VALUES\n (" . implode(',', $values) . ")";
//$this->tokens_dynamic_model->insertToken($surveyid,$tokendata);
dbExecuteAssoc($SQL);
//Yii::app()->db->AutoExecute(db_table_name("tokens_".$surveyid), $tokendata,'INSERT');
$aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), $clang->gT("A token entry for the saved survey has been created too."));
//$aDataentryoutput .= "<font class='successtitle'></font><br />\n";
$last_db_id = Yii::app()->db->getLastInsertID();
}
if ($saver['email']) {
//Send email
if (validateEmailAddress($saver['email']) && !returnGlobal('redo')) {
$subject = $clang->gT("Saved Survey Details");
$message = $clang->gT("Thank you for saving your survey in progress. The following details can be used to return to this survey and continue where you left off. Please keep this e-mail for your reference - we cannot retrieve the password for you.");
$message .= "\n\n" . $thissurvey['name'] . "\n\n";
$message .= $clang->gT("Name") . ": " . $saver['identifier'] . "\n";
$message .= $clang->gT("Password") . ": " . $saver['password'] . "\n\n";
$message .= $clang->gT("Reload your survey by clicking on the following link (or pasting it into your browser):") . ":\n";
$message .= Yii::app()->getConfig('publicurl') . "/index.php?sid={$surveyid}&loadall=reload&scid=" . $scid . "&lang=" . urlencode($saver['language']) . "&loadname=" . urlencode($saver['identifier']) . "&loadpass="******"&token=" . $tokendata['token'];
}
$from = $thissurvey['adminemail'];
if (SendEmailMessage($message, $subject, $saver['email'], $from, $sitename, false, getBounceEmail($surveyid))) {
$emailsent = "Y";
$aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), $clang->gT("An email has been sent with details about your saved survey"));
}
}
}
} else {
safeDie("Unable to insert record into saved_control table.<br /><br />");
}
}
$aData['thisid'] = $last_db_id;
}
$aData['errormsg'] = $errormsg;
$aData['dataentrymsgs'] = $aDataentrymsgs;
$this->_renderWrappedTemplate('dataentry', 'insert', $aData);
}
}
}
function db_upgrade_all($iOldDBVersion)
{
/// This function does anything necessary to upgrade
/// older versions to match current functionality
global $modifyoutput;
Yii::app()->loadHelper('database');
$sUserTemplateRootDir = Yii::app()->getConfig('usertemplaterootdir');
$sStandardTemplateRootDir = Yii::app()->getConfig('standardtemplaterootdir');
echo str_pad(gT('The LimeSurvey database is being upgraded') . ' (' . date('Y-m-d H:i:s') . ')', 14096) . ".<br /><br />" . gT('Please be patient...') . "<br /><br />\n";
$oDB = Yii::app()->getDb();
$oDB->schemaCachingDuration = 0;
// Deactivate schema caching
$oTransaction = $oDB->beginTransaction();
try {
if ($iOldDBVersion < 111) {
// Language upgrades from version 110 to 111 because the language names did change
$aOldNewLanguages = 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 ($aOldNewLanguages as $sOldLanguageCode => $sNewLanguageCode) {
alterLanguageCode($sOldLanguageCode, $sNewLanguageCode);
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 111), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 112) {
// New size of the username field (it was previously 20 chars wide)
$oDB->createCommand()->alterColumn('{{users}}', 'users_name', "string(64) NOT NULL");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 112), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 113) {
//Fixes the collation for the complete DB, tables and columns
if (Yii::app()->db->driverName == 'mysql') {
$sDatabaseName = getDBConnectionStringProperty('dbname');
fixMySQLCollations();
modifyDatabase("", "ALTER DATABASE `{$sDatabaseName}` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
@ob_flush();
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 113), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 114) {
$oDB->createCommand()->alterColumn('{{saved_control}}', 'email', "string(320) NOT NULL");
$oDB->createCommand()->alterColumn('{{surveys}}', 'adminemail', "string(320) NOT NULL");
$oDB->createCommand()->alterColumn('{{users}}', 'email', "string(320) NOT NULL");
$oDB->createCommand()->insert('{{settings_global}}', array('stg_name' => 'SessionName', 'stg_value' => randomChars(64, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ!"$%&/()=?`+*~#",;.:abcdefghijklmnopqrstuvwxyz123456789')));
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 114), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 126) {
addColumn('{{surveys}}', 'printanswers', "string(1) default 'N'");
addColumn('{{surveys}}', 'listpublic', "string(1) default 'N'");
upgradeSurveyTables126();
upgradeTokenTables126();
// Create quota table
$oDB->createCommand()->createTable('{{quota}}', array('id' => 'pk', 'sid' => 'integer', 'qlimit' => 'integer', 'name' => 'string', 'action' => 'integer', 'active' => 'integer NOT NULL DEFAULT 1'));
// Create quota_members table
$oDB->createCommand()->createTable('{{quota_members}}', array('id' => 'pk', 'sid' => 'integer', 'qid' => 'integer', 'quota_id' => 'integer', 'code' => 'string(5)'));
$oDB->createCommand()->createIndex('sid', '{{quota_members}}', 'sid,qid,quota_id,code', true);
// Create templates_rights table
$oDB->createCommand()->createTable('{{templates_rights}}', array('uid' => 'integer NOT NULL', 'folder' => 'string NOT NULL', 'use' => 'integer', 'PRIMARY KEY (uid, folder)'));
// Create templates table
$oDB->createCommand()->createTable('{{templates}}', array('folder' => 'string NOT NULL', 'creator' => 'integer NOT NULL', 'PRIMARY KEY (folder)'));
// Rename Norwegian language codes
alterLanguageCode('no', 'nb');
addColumn('{{surveys}}', 'htmlemail', "string(1) default 'N'");
addColumn('{{surveys}}', 'tokenanswerspersistence', "string(1) default 'N'");
addColumn('{{surveys}}', 'usecaptcha', "string(1) default 'N'");
addColumn('{{surveys}}', 'bounce_email', 'text');
addColumn('{{users}}', 'htmleditormode', "string(7) default 'default'");
addColumn('{{users}}', 'superadmin', "integer NOT NULL default '0'");
addColumn('{{questions}}', 'lid1', "integer NOT NULL default '0'");
alterColumn('{{conditions}}', 'value', "string", false, '');
alterColumn('{{labels}}', 'title', "text");
$oDB->createCommand()->update('{{users}}', array('superadmin' => 1), "create_survey=1 AND create_user=1 AND move_user=1 AND delete_user=1 AND configurator=1");
$oDB->createCommand()->update('{{conditions}}', array('method' => '=='), "(method is null) or method='' or method='0'");
dropColumn('{{users}}', 'move_user');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 126), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 127) {
modifyDatabase("", "create index answers_idx2 on {{answers}} (sortorder)");
echo $modifyoutput;
modifyDatabase("", "create index assessments_idx2 on {{assessments}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index assessments_idx3 on {{assessments}} (gid)");
echo $modifyoutput;
modifyDatabase("", "create index conditions_idx2 on {{conditions}} (qid)");
echo $modifyoutput;
modifyDatabase("", "create index conditions_idx3 on {{conditions}} (cqid)");
echo $modifyoutput;
modifyDatabase("", "create index groups_idx2 on {{groups}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index question_attributes_idx2 on {{question_attributes}} (qid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx2 on {{questions}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx3 on {{questions}} (gid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx4 on {{questions}} (type)");
echo $modifyoutput;
modifyDatabase("", "create index quota_idx2 on {{quota}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index saved_control_idx2 on {{saved_control}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index user_in_groups_idx1 on {{user_in_groups}} (ugid, uid)");
echo $modifyoutput;
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 127), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 128) {
upgradeTokens128();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 128), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 129) {
addColumn('{{surveys}}', 'startdate', "datetime");
addColumn('{{surveys}}', 'usestartdate', "string(1) NOT NULL default 'N'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 129), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 130) {
addColumn('{{conditions}}', 'scenario', "integer NOT NULL default '1'");
$oDB->createCommand()->update('{{conditions}}', array('scenario' => '1'), "(scenario is null) or scenario=0");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 130), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 131) {
addColumn('{{surveys}}', 'publicstatistics', "string(1) NOT NULL default 'N'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 131), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 132) {
addColumn('{{surveys}}', 'publicgraphs', "string(1) NOT NULL default 'N'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 132), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 133) {
addColumn('{{users}}', 'one_time_pw', 'binary');
// Add new assessment setting
addColumn('{{surveys}}', 'assessments', "string(1) NOT NULL default 'N'");
// add new assessment value fields to answers & labels
addColumn('{{answers}}', 'assessment_value', "integer NOT NULL default '0'");
addColumn('{{labels}}', 'assessment_value', "integer NOT NULL default '0'");
// copy any valid codes from code field to assessment field
switch (Yii::app()->db->driverName) {
case 'mysql':
case 'mysqli':
$oDB->createCommand("UPDATE {{answers}} SET assessment_value=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'")->execute();
$oDB->createCommand("UPDATE {{labels}} SET assessment_value=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'")->execute();
// copy assessment link to message since from now on we will have HTML assignment messages
$oDB->createCommand("UPDATE {{assessments}} set message=concat(replace(message,'/''',''''),'<br /><a href=\"',link,'\">',link,'</a>')")->execute();
break;
case 'sqlsrv':
case 'dblib':
case 'mssql':
try {
$oDB->createCommand("UPDATE {{answers}} SET assessment_value=CAST([code] as int) WHERE ISNUMERIC([code])=1")->execute();
$oDB->createCommand("UPDATE {{labels}} SET assessment_value=CAST([code] as int) WHERE ISNUMERIC([code])=1")->execute();
} catch (Exception $e) {
}
// copy assessment link to message since from now on we will have HTML assignment messages
alterColumn('{{assessments}}', 'link', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
$oDB->createCommand("UPDATE {{assessments}} set message=replace(message,'/''','''')+'<br /><a href=\"'+link+'\">'+link+'</a>'")->execute();
break;
case 'pgsql':
$oDB->createCommand("UPDATE {{answers}} SET assessment_value=CAST(code as integer) where code ~ '^[0-9]+'")->execute();
$oDB->createCommand("UPDATE {{labels}} SET assessment_value=CAST(code as integer) where code ~ '^[0-9]+'")->execute();
// copy assessment link to message since from now on we will have HTML assignment messages
$oDB->createCommand("UPDATE {{assessments}} set message=replace(message,'/''','''')||'<br /><a href=\"'||link||'\">'||link||'</a>'")->execute();
break;
default:
die('Unknown database type');
}
// activate assessment where assessment rules exist
$oDB->createCommand("UPDATE {{surveys}} SET assessments='Y' where sid in (SELECT sid FROM {{assessments}} group by sid)")->execute();
// add language field to assessment table
addColumn('{{assessments}}', 'language', "string(20) NOT NULL default 'en'");
// update language field with default language of that particular survey
$oDB->createCommand("UPDATE {{assessments}} SET language=(select language from {{surveys}} where sid={{assessments}}.sid)")->execute();
// drop the old link field
dropColumn('{{assessments}}', 'link');
// Add new fields to survey language settings
addColumn('{{surveys_languagesettings}}', 'surveyls_url', "string");
addColumn('{{surveys_languagesettings}}', 'surveyls_endtext', 'text');
// copy old URL fields ot language specific entries
$oDB->createCommand("UPDATE {{surveys_languagesettings}} set surveyls_url=(select url from {{surveys}} where sid={{surveys_languagesettings}}.surveyls_survey_id)")->execute();
// drop old URL field
dropColumn('{{surveys}}', 'url');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 133), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 134) {
// Add new tokens setting
addColumn('{{surveys}}', 'usetokens', "string(1) NOT NULL default 'N'");
addColumn('{{surveys}}', 'attributedescriptions', 'text');
dropColumn('{{surveys}}', 'attribute1');
dropColumn('{{surveys}}', 'attribute2');
upgradeTokenTables134();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 134), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 135) {
alterColumn('{{question_attributes}}', 'value', 'text');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 135), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 136) {
addColumn('{{quota}}', 'autoload_url', "integer NOT NULL default 0");
// Create quota table
$aFields = array('quotals_id' => 'pk', 'quotals_quota_id' => 'integer NOT NULL DEFAULT 0', 'quotals_language' => "string(45) NOT NULL default 'en'", 'quotals_name' => 'string', 'quotals_message' => 'text NOT NULL', 'quotals_url' => 'string', 'quotals_urldescrip' => 'string');
$oDB->createCommand()->createTable('{{quota_languagesettings}}', $aFields);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 136), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 137) {
addColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', "integer NOT NULL default 1");
addColumn('{{users}}', 'dateformat', "integer NOT NULL default 1");
$oDB->createCommand()->update('{{surveys}}', array('startdate' => NULL), "usestartdate='N'");
$oDB->createCommand()->update('{{surveys}}', array('expires' => NULL), "useexpiry='N'");
dropColumn('{{surveys}}', 'useexpiry');
dropColumn('{{surveys}}', 'usestartdate');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 137), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 138) {
alterColumn('{{quota_members}}', 'code', "string(11)");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 138), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 139) {
upgradeSurveyTables139();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 139), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 140) {
addColumn('{{surveys}}', 'emailresponseto', 'text');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 140), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 141) {
addColumn('{{surveys}}', 'tokenlength', 'integer NOT NULL default 15');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 141), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 142) {
upgradeQuestionAttributes142();
$oDB->createCommand()->alterColumn('{{surveys}}', 'expires', "datetime");
$oDB->createCommand()->alterColumn('{{surveys}}', 'startdate', "datetime");
$oDB->createCommand()->update('{{question_attributes}}', array('value' => 0), "value='false'");
$oDB->createCommand()->update('{{question_attributes}}', array('value' => 1), "value='true'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 142), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 143) {
addColumn('{{questions}}', 'parent_qid', 'integer NOT NULL default 0');
addColumn('{{answers}}', 'scale_id', 'integer NOT NULL default 0');
addColumn('{{questions}}', 'scale_id', 'integer NOT NULL default 0');
addColumn('{{questions}}', 'same_default', 'integer NOT NULL default 0');
dropPrimaryKey('answers');
addPrimaryKey('answers', array('qid', 'code', 'language', 'scale_id'));
$aFields = array('qid' => "integer NOT NULL default 0", 'scale_id' => 'integer NOT NULL default 0', 'sqid' => 'integer NOT NULL default 0', 'language' => 'string(20) NOT NULL', 'specialtype' => "string(20) NOT NULL default ''", 'defaultvalue' => 'text');
$oDB->createCommand()->createTable('{{defaultvalues}}', $aFields);
addPrimaryKey('defaultvalues', array('qid', 'specialtype', 'language', 'scale_id', 'sqid'));
// -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
upgradeTables143();
dropColumn('{{answers}}', 'default_value');
dropColumn('{{questions}}', 'lid');
dropColumn('{{questions}}', 'lid1');
$aFields = array('sesskey' => "string(64) NOT NULL DEFAULT ''", 'expiry' => "datetime NOT NULL", 'expireref' => "string(250) DEFAULT ''", 'created' => "datetime NOT NULL", 'modified' => "datetime NOT NULL", 'sessdata' => 'text');
$oDB->createCommand()->createTable('{{sessions}}', $aFields);
addPrimaryKey('sessions', array('sesskey'));
$oDB->createCommand()->createIndex('sess2_expiry', '{{sessions}}', 'expiry');
$oDB->createCommand()->createIndex('sess2_expireref', '{{sessions}}', 'expireref');
// Move all user templates to the new user template directory
echo "<br>" . sprintf(gT("Moving user templates to new location at %s..."), $sUserTemplateRootDir) . "<br />";
$hTemplateDirectory = opendir($sStandardTemplateRootDir);
$aFailedTemplates = array();
// get each entry
while ($entryName = readdir($hTemplateDirectory)) {
if (!in_array($entryName, array('.', '..', '.svn')) && is_dir($sStandardTemplateRootDir . DIRECTORY_SEPARATOR . $entryName) && !isStandardTemplate($entryName)) {
if (!rename($sStandardTemplateRootDir . DIRECTORY_SEPARATOR . $entryName, $sUserTemplateRootDir . DIRECTORY_SEPARATOR . $entryName)) {
$aFailedTemplates[] = $entryName;
}
}
}
if (count($aFailedTemplates) > 0) {
echo "The following templates at {$sStandardTemplateRootDir} could not be moved to the new location at {$sUserTemplateRootDir}:<br /><ul>";
foreach ($aFailedTemplates as $sFailedTemplate) {
echo "<li>{$sFailedTemplate}</li>";
}
echo "</ul>Please move these templates manually after the upgrade has finished.<br />";
}
// close directory
closedir($hTemplateDirectory);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 143), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 145) {
addColumn('{{surveys}}', 'savetimings', "string(1) NULL default 'N'");
addColumn('{{surveys}}', 'showXquestions', "string(1) NULL default 'Y'");
addColumn('{{surveys}}', 'showgroupinfo', "string(1) NULL default 'B'");
addColumn('{{surveys}}', 'shownoanswer', "string(1) NULL default 'Y'");
addColumn('{{surveys}}', 'showqnumcode', "string(1) NULL default 'X'");
addColumn('{{surveys}}', 'bouncetime', 'integer');
addColumn('{{surveys}}', 'bounceprocessing', "string(1) NULL default 'N'");
addColumn('{{surveys}}', 'bounceaccounttype', "string(4)");
addColumn('{{surveys}}', 'bounceaccounthost', "string(200)");
addColumn('{{surveys}}', 'bounceaccountpass', "string(100)");
addColumn('{{surveys}}', 'bounceaccountencryption', "string(3)");
addColumn('{{surveys}}', 'bounceaccountuser', "string(200)");
addColumn('{{surveys}}', 'showwelcome', "string(1) default 'Y'");
addColumn('{{surveys}}', 'showprogress', "string(1) default 'Y'");
addColumn('{{surveys}}', 'allowjumps', "string(1) default 'N'");
addColumn('{{surveys}}', 'navigationdelay', "integer default 0");
addColumn('{{surveys}}', 'nokeyboard', "string(1) default 'N'");
addColumn('{{surveys}}', 'alloweditaftercompletion', "string(1) default 'N'");
$aFields = array('sid' => "integer NOT NULL", 'uid' => "integer NOT NULL", 'permission' => 'string(20) NOT NULL', 'create_p' => "integer NOT NULL default 0", 'read_p' => "integer NOT NULL default 0", 'update_p' => "integer NOT NULL default 0", 'delete_p' => "integer NOT NULL default 0", 'import_p' => "integer NOT NULL default 0", 'export_p' => "integer NOT NULL default 0");
$oDB->createCommand()->createTable('{{survey_permissions}}', $aFields);
addPrimaryKey('survey_permissions', array('sid', 'uid', 'permission'));
upgradeSurveyPermissions145();
// drop the old survey rights table
$oDB->createCommand()->dropTable('{{surveys_rights}}');
// Add new fields for email templates
addColumn('{{surveys_languagesettings}}', 'email_admin_notification_subj', "string");
addColumn('{{surveys_languagesettings}}', 'email_admin_responses_subj', "string");
addColumn('{{surveys_languagesettings}}', 'email_admin_notification', "text");
addColumn('{{surveys_languagesettings}}', 'email_admin_responses', "text");
//Add index to questions table to speed up subquestions
$oDB->createCommand()->createIndex('parent_qid_idx', '{{questions}}', 'parent_qid');
addColumn('{{surveys}}', 'emailnotificationto', "text");
upgradeSurveys145();
dropColumn('{{surveys}}', 'notification');
alterColumn('{{conditions}}', 'method', "string(5)", false, '');
$oDB->createCommand()->renameColumn('{{surveys}}', 'private', 'anonymized');
$oDB->createCommand()->update('{{surveys}}', array('anonymized' => 'N'), "anonymized is NULL");
alterColumn('{{surveys}}', 'anonymized', "string(1)", false, 'N');
//now we clean up things that were not properly set in previous DB upgrades
$oDB->createCommand()->update('{{answers}}', array('answer' => ''), "answer is NULL");
$oDB->createCommand()->update('{{assessments}}', array('scope' => ''), "scope is NULL");
$oDB->createCommand()->update('{{assessments}}', array('name' => ''), "name is NULL");
$oDB->createCommand()->update('{{assessments}}', array('message' => ''), "message is NULL");
$oDB->createCommand()->update('{{assessments}}', array('minimum' => ''), "minimum is NULL");
$oDB->createCommand()->update('{{assessments}}', array('maximum' => ''), "maximum is NULL");
$oDB->createCommand()->update('{{groups}}', array('group_name' => ''), "group_name is NULL");
$oDB->createCommand()->update('{{labels}}', array('code' => ''), "code is NULL");
$oDB->createCommand()->update('{{labelsets}}', array('label_name' => ''), "label_name is NULL");
$oDB->createCommand()->update('{{questions}}', array('type' => 'T'), "type is NULL");
$oDB->createCommand()->update('{{questions}}', array('title' => ''), "title is NULL");
$oDB->createCommand()->update('{{questions}}', array('question' => ''), "question is NULL");
$oDB->createCommand()->update('{{questions}}', array('other' => 'N'), "other is NULL");
alterColumn('{{answers}}', 'answer', "text", false);
alterColumn('{{answers}}', 'assessment_value', 'integer', false, '0');
alterColumn('{{assessments}}', 'scope', "string(5)", false, '');
alterColumn('{{assessments}}', 'name', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
alterColumn('{{assessments}}', 'minimum', "string(50)", false, '');
alterColumn('{{assessments}}', 'maximum', "string(50)", false, '');
// change the primary index to include language
if (Yii::app()->db->driverName == 'mysql') {
modifyPrimaryKey('assessments', array('id', 'language'));
} else {
dropPrimaryKey('assessments');
addPrimaryKey('assessments', array('id', 'language'));
}
alterColumn('{{conditions}}', 'cfieldname', "string(50)", false, '');
dropPrimaryKey('defaultvalues');
alterColumn('{{defaultvalues}}', 'specialtype', "string(20)", false, '');
addPrimaryKey('defaultvalues', array('qid', 'specialtype', 'language', 'scale_id', 'sqid'));
alterColumn('{{groups}}', 'group_name', "string(100)", false, '');
alterColumn('{{labels}}', 'code', "string(5)", false, '');
dropPrimaryKey('labels');
alterColumn('{{labels}}', 'language', "string(20)", false, 'en');
addPrimaryKey('labels', array('lid', 'sortorder', 'language'));
alterColumn('{{labelsets}}', 'label_name', "string(100)", false, '');
alterColumn('{{questions}}', 'parent_qid', 'integer', false, '0');
alterColumn('{{questions}}', 'title', "string(20)", false, '');
alterColumn('{{questions}}', 'question', "text", false);
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('questions_idx4', '{{questions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{questions}}', 'type', "string(1)", false, 'T');
try {
$oDB->createCommand()->createIndex('questions_idx4', '{{questions}}', 'type');
} catch (Exception $e) {
}
alterColumn('{{questions}}', 'other', "string(1)", false, 'N');
alterColumn('{{questions}}', 'mandatory', "string(1)");
alterColumn('{{question_attributes}}', 'attribute', "string(50)");
alterColumn('{{quota}}', 'qlimit', 'integer');
$oDB->createCommand()->update('{{saved_control}}', array('identifier' => ''), "identifier is NULL");
alterColumn('{{saved_control}}', 'identifier', "text", false);
$oDB->createCommand()->update('{{saved_control}}', array('access_code' => ''), "access_code is NULL");
alterColumn('{{saved_control}}', 'access_code', "text", false);
alterColumn('{{saved_control}}', 'email', "string(320)");
$oDB->createCommand()->update('{{saved_control}}', array('ip' => ''), "ip is NULL");
alterColumn('{{saved_control}}', 'ip', "text", false);
$oDB->createCommand()->update('{{saved_control}}', array('saved_thisstep' => ''), "saved_thisstep is NULL");
alterColumn('{{saved_control}}', 'saved_thisstep', "text", false);
$oDB->createCommand()->update('{{saved_control}}', array('status' => ''), "status is NULL");
alterColumn('{{saved_control}}', 'status', "string(1)", false, '');
$oDB->createCommand()->update('{{saved_control}}', array('saved_date' => '1980-01-01 00:00:00'), "saved_date is NULL");
alterColumn('{{saved_control}}', 'saved_date', "datetime", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => ''), "stg_value is NULL");
alterColumn('{{settings_global}}', 'stg_value', "string", false, '');
alterColumn('{{surveys}}', 'admin', "string(50)");
$oDB->createCommand()->update('{{surveys}}', array('active' => 'N'), "active is NULL");
alterColumn('{{surveys}}', 'active', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'startdate', "datetime");
alterColumn('{{surveys}}', 'adminemail', "string(320)");
alterColumn('{{surveys}}', 'anonymized', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'faxto', "string(20)");
alterColumn('{{surveys}}', 'format', "string(1)");
alterColumn('{{surveys}}', 'language', "string(50)");
alterColumn('{{surveys}}', 'additional_languages', "string");
alterColumn('{{surveys}}', 'printanswers', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'publicstatistics', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'publicgraphs', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'assessments', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'usetokens', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'bounce_email', "string(320)");
alterColumn('{{surveys}}', 'tokenlength', 'integer', true, 15);
$oDB->createCommand()->update('{{surveys_languagesettings}}', array('surveyls_title' => ''), "surveyls_title is NULL");
alterColumn('{{surveys_languagesettings}}', 'surveyls_title', "string(200)", false);
alterColumn('{{surveys_languagesettings}}', 'surveyls_endtext', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_url', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_urldescription', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_invite_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_remind_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_register_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_confirm_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', 'integer', false, 1);
$oDB->createCommand()->update('{{users}}', array('users_name' => ''), "users_name is NULL");
$oDB->createCommand()->update('{{users}}', array('full_name' => ''), "full_name is NULL");
alterColumn('{{users}}', 'users_name', "string(64)", false, '');
alterColumn('{{users}}', 'full_name', "string(50)", false);
alterColumn('{{users}}', 'lang', "string(20)");
alterColumn('{{users}}', 'email', "string(320)");
alterColumn('{{users}}', 'superadmin', 'integer', false, 0);
alterColumn('{{users}}', 'htmleditormode', "string(7)", true, 'default');
alterColumn('{{users}}', 'dateformat', 'integer', false, 1);
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('email', '{{users}}');
} catch (Exception $e) {
// do nothing
rollBackToTransactionBookmark();
}
$oDB->createCommand()->update('{{user_groups}}', array('name' => ''), "name is NULL");
$oDB->createCommand()->update('{{user_groups}}', array('description' => ''), "description is NULL");
alterColumn('{{user_groups}}', 'name', "string(20)", false);
alterColumn('{{user_groups}}', 'description', "text", false);
try {
$oDB->createCommand()->dropIndex('user_in_groups_idx1', '{{user_in_groups}}');
} catch (Exception $e) {
}
try {
addPrimaryKey('user_in_groups', array('ugid', 'uid'));
} catch (Exception $e) {
}
addColumn('{{surveys_languagesettings}}', 'surveyls_numberformat', "integer NOT NULL DEFAULT 0");
$oDB->createCommand()->createTable('{{failed_login_attempts}}', array('id' => "pk", 'ip' => 'string(37) NOT NULL', 'last_attempt' => 'string(20) NOT NULL', 'number_attempts' => "integer NOT NULL"));
upgradeTokens145();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 145), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 146) {
upgradeSurveyTimings146();
// Fix permissions for new feature quick-translation
try {
setTransactionBookmark();
$oDB->createCommand("INSERT into {{survey_permissions}} (sid,uid,permission,read_p,update_p) SELECT sid,owner_id,'translations','1','1' from {{surveys}}")->execute();
echo $modifyoutput;
flush();
@ob_flush();
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 146), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 147) {
addColumn('{{users}}', 'templateeditormode', "string(7) NOT NULL default 'default'");
addColumn('{{users}}', 'questionselectormode', "string(7) NOT NULL default 'default'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 147), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 148) {
addColumn('{{users}}', 'participant_panel', "integer NOT NULL default 0");
$oDB->createCommand()->createTable('{{participants}}', array('participant_id' => 'string(50) NOT NULL', 'firstname' => 'string(40) default NULL', 'lastname' => 'string(40) default NULL', 'email' => 'string(80) default NULL', 'language' => 'string(40) default NULL', 'blacklisted' => 'string(1) NOT NULL', 'owner_uid' => "integer NOT NULL"));
addPrimaryKey('participants', array('participant_id'));
$oDB->createCommand()->createTable('{{participant_attribute}}', array('participant_id' => 'string(50) NOT NULL', 'attribute_id' => "integer NOT NULL", 'value' => 'string(50) NOT NULL'));
addPrimaryKey('participant_attribute', array('participant_id', 'attribute_id'));
$oDB->createCommand()->createTable('{{participant_attribute_names}}', array('attribute_id' => 'autoincrement', 'attribute_type' => 'string(4) NOT NULL', 'visible' => 'string(5) NOT NULL', 'PRIMARY KEY (attribute_id,attribute_type)'));
$oDB->createCommand()->createTable('{{participant_attribute_names_lang}}', array('attribute_id' => 'integer NOT NULL', 'attribute_name' => 'string(30) NOT NULL', 'lang' => 'string(20) NOT NULL'));
addPrimaryKey('participant_attribute_names_lang', array('attribute_id', 'lang'));
$oDB->createCommand()->createTable('{{participant_attribute_values}}', array('attribute_id' => 'integer NOT NULL', 'value_id' => 'pk', 'value' => 'string(20) NOT NULL'));
$oDB->createCommand()->createTable('{{participant_shares}}', array('participant_id' => 'string(50) NOT NULL', 'share_uid' => 'integer NOT NULL', 'date_added' => 'datetime NOT NULL', 'can_edit' => 'string(5) NOT NULL'));
addPrimaryKey('participant_shares', array('participant_id', 'share_uid'));
$oDB->createCommand()->createTable('{{survey_links}}', array('participant_id' => 'string(50) NOT NULL', 'token_id' => 'integer NOT NULL', 'survey_id' => 'integer NOT NULL', 'date_created' => 'datetime NOT NULL'));
addPrimaryKey('survey_links', array('participant_id', 'token_id', 'survey_id'));
// Add language field to question_attributes table
addColumn('{{question_attributes}}', 'language', "string(20)");
upgradeQuestionAttributes148();
fixSubquestions();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 148), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 149) {
$aFields = array('id' => 'integer', 'sid' => 'integer', 'parameter' => 'string(50)', 'targetqid' => 'integer', 'targetsqid' => 'integer');
$oDB->createCommand()->createTable('{{survey_url_parameters}}', $aFields);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 149), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 150) {
addColumn('{{questions}}', 'relevance', 'TEXT');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 150), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 151) {
addColumn('{{groups}}', 'randomization_group', "string(20) NOT NULL default ''");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 151), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 152) {
$oDB->createCommand()->createIndex('question_attributes_idx3', '{{question_attributes}}', 'attribute');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 152), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 153) {
$oDB->createCommand()->createTable('{{expression_errors}}', array('id' => 'pk', 'errortime' => 'string(50)', 'sid' => 'integer', 'gid' => 'integer', 'qid' => 'integer', 'gseq' => 'integer', 'qseq' => 'integer', 'type' => 'string(50)', 'eqn' => 'text', 'prettyprint' => 'text'));
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 153), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 154) {
$oDB->createCommand()->addColumn('{{groups}}', 'grelevance', "text");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 154), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 155) {
addColumn('{{surveys}}', 'googleanalyticsstyle', "string(1)");
addColumn('{{surveys}}', 'googleanalyticsapikey', "string(25)");
try {
setTransactionBookmark();
$oDB->createCommand()->renameColumn('{{surveys}}', 'showXquestions', 'showxquestions');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 155), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 156) {
try {
$oDB->createCommand()->dropTable('{{survey_url_parameters}}');
} catch (Exception $e) {
// do nothing
}
$oDB->createCommand()->createTable('{{survey_url_parameters}}', array('id' => 'pk', 'sid' => 'integer NOT NULL', 'parameter' => 'string(50) NOT NULL', 'targetqid' => 'integer', 'targetsqid' => 'integer'));
$oDB->createCommand()->dropTable('{{sessions}}');
if (Yii::app()->db->driverName == 'mysql') {
$oDB->createCommand()->createTable('{{sessions}}', array('id' => 'string(32) NOT NULL', 'expire' => 'integer', 'data' => 'longtext'));
} else {
$oDB->createCommand()->createTable('{{sessions}}', array('id' => 'string(32) NOT NULL', 'expire' => 'integer', 'data' => 'text'));
}
addPrimaryKey('sessions', array('id'));
addColumn('{{surveys_languagesettings}}', 'surveyls_attributecaptions', "TEXT");
addColumn('{{surveys}}', 'sendconfirmation', "string(1) default 'Y'");
upgradeSurveys156();
// If a survey has an deleted owner, re-own the survey to the superadmin
$oDB->schema->refresh();
Survey::model()->refreshMetaData();
$surveys = Survey::model();
$surveys = $surveys->with(array('owner'))->findAll();
foreach ($surveys as $row) {
if (!isset($row->owner->attributes)) {
Survey::model()->updateByPk($row->sid, array('owner_id' => 1));
}
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 156), "stg_name='DBVersion'");
$oTransaction->commit();
$oTransaction = $oDB->beginTransaction();
}
if ($iOldDBVersion < 157) {
// MySQL DB corrections
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('questions_idx4', '{{questions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{answers}}', 'assessment_value', 'integer', false, '0');
dropPrimaryKey('answers');
alterColumn('{{answers}}', 'scale_id', 'integer', false, '0');
addPrimaryKey('answers', array('qid', 'code', 'language', 'scale_id'));
alterColumn('{{conditions}}', 'method', "string(5)", false, '');
alterColumn('{{participants}}', 'owner_uid', 'integer', false);
alterColumn('{{participant_attribute_names}}', 'visible', 'string(5)', false);
alterColumn('{{questions}}', 'type', "string(1)", false, 'T');
alterColumn('{{questions}}', 'other', "string(1)", false, 'N');
alterColumn('{{questions}}', 'mandatory', "string(1)");
alterColumn('{{questions}}', 'scale_id', 'integer', false, '0');
alterColumn('{{questions}}', 'parent_qid', 'integer', false, '0');
alterColumn('{{questions}}', 'same_default', 'integer', false, '0');
alterColumn('{{quota}}', 'qlimit', 'integer');
alterColumn('{{quota}}', 'action', 'integer');
alterColumn('{{quota}}', 'active', 'integer', false, '1');
alterColumn('{{quota}}', 'autoload_url', 'integer', false, '0');
alterColumn('{{saved_control}}', 'status', "string(1)", false, '');
try {
setTransactionBookmark();
alterColumn('{{sessions}}', 'id', "string(32)", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{surveys}}', 'active', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'anonymized', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'format', "string(1)");
alterColumn('{{surveys}}', 'savetimings', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'datestamp', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'usecookie', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'allowregister', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'allowsave', "string(1)", false, 'Y');
alterColumn('{{surveys}}', 'autonumber_start', 'integer', false, '0');
alterColumn('{{surveys}}', 'autoredirect', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'allowprev', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'printanswers', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'ipaddr', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'refurl', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'publicstatistics', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'publicgraphs', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'listpublic', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'htmlemail', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'sendconfirmation', "string(1)", false, 'Y');
alterColumn('{{surveys}}', 'tokenanswerspersistence', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'assessments', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'usecaptcha', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'usetokens', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'tokenlength', 'integer', false, '15');
alterColumn('{{surveys}}', 'showxquestions', "string(1)", true, 'Y');
alterColumn('{{surveys}}', 'showgroupinfo', "string(1) ", true, 'B');
alterColumn('{{surveys}}', 'shownoanswer', "string(1) ", true, 'Y');
alterColumn('{{surveys}}', 'showqnumcode', "string(1) ", true, 'X');
alterColumn('{{surveys}}', 'bouncetime', 'integer');
alterColumn('{{surveys}}', 'showwelcome', "string(1)", true, 'Y');
alterColumn('{{surveys}}', 'showprogress', "string(1)", true, 'Y');
alterColumn('{{surveys}}', 'allowjumps', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'navigationdelay', 'integer', false, '0');
alterColumn('{{surveys}}', 'nokeyboard', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'alloweditaftercompletion', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'googleanalyticsstyle', "string(1)");
alterColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', 'integer', false, 1);
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'sid', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'uid', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{survey_permissions}}', 'create_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'read_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'update_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'delete_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'import_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'export_p', 'integer', false, '0');
alterColumn('{{survey_url_parameters}}', 'targetqid', 'integer');
alterColumn('{{survey_url_parameters}}', 'targetsqid', 'integer');
alterColumn('{{templates_rights}}', 'use', 'integer', false);
alterColumn('{{users}}', 'create_survey', 'integer', false, '0');
alterColumn('{{users}}', 'create_user', 'integer', false, '0');
alterColumn('{{users}}', 'participant_panel', 'integer', false, '0');
alterColumn('{{users}}', 'delete_user', 'integer', false, '0');
alterColumn('{{users}}', 'superadmin', 'integer', false, '0');
alterColumn('{{users}}', 'configurator', 'integer', false, '0');
alterColumn('{{users}}', 'manage_template', 'integer', false, '0');
alterColumn('{{users}}', 'manage_label', 'integer', false, '0');
alterColumn('{{users}}', 'dateformat', 'integer', false, 1);
alterColumn('{{users}}', 'participant_panel', 'integer', false, '0');
alterColumn('{{users}}', 'parent_id', 'integer', false);
try {
setTransactionBookmark();
alterColumn('{{surveys_languagesettings}}', 'surveyls_survey_id', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{user_groups}}', 'owner_id', "integer", false);
dropPrimaryKey('user_in_groups');
alterColumn('{{user_in_groups}}', 'ugid', "integer", false);
alterColumn('{{user_in_groups}}', 'uid', "integer", false);
// Additional corrections for Postgres
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('questions_idx3', '{{questions}}', 'gid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('conditions_idx3', '{{conditions}}', 'cqid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('questions_idx4', '{{questions}}', 'type');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('user_in_groups_idx1', '{{user_in_groups}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('{{user_name_key}}', '{{users}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('users_name', '{{users}}', 'users_name', true);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
addPrimaryKey('user_in_groups', array('ugid', 'uid'));
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{participant_attribute}}', 'value', "string(50)", false);
try {
setTransactionBookmark();
alterColumn('{{participant_attribute_names}}', 'attribute_type', "string(4)", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
dropColumn('{{participant_attribute_names_lang}}', 'id');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
addPrimaryKey('participant_attribute_names_lang', array('attribute_id', 'lang'));
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->renameColumn('{{participant_shares}}', 'shared_uid', 'share_uid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{participant_shares}}', 'date_added', "datetime", false);
alterColumn('{{participants}}', 'firstname', "string(40)");
alterColumn('{{participants}}', 'lastname', "string(40)");
alterColumn('{{participants}}', 'email', "string(80)");
alterColumn('{{participants}}', 'language', "string(40)");
alterColumn('{{quota_languagesettings}}', 'quotals_name', "string");
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'sid', 'integer', false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'uid', 'integer', false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{users}}', 'htmleditormode', "string(7)", true, 'default');
// Sometimes the survey_links table was deleted before this step, if so
// we recreate it (copied from line 663)
if (!tableExists('{survey_links}')) {
$oDB->createCommand()->createTable('{{survey_links}}', array('participant_id' => 'string(50) NOT NULL', 'token_id' => 'integer NOT NULL', 'survey_id' => 'integer NOT NULL', 'date_created' => 'datetime NOT NULL'));
addPrimaryKey('survey_links', array('participant_id', 'token_id', 'survey_id'));
}
alterColumn('{{survey_links}}', 'date_created', "datetime", true);
alterColumn('{{saved_control}}', 'identifier', "text", false);
alterColumn('{{saved_control}}', 'email', "string(320)");
alterColumn('{{surveys}}', 'adminemail', "string(320)");
alterColumn('{{surveys}}', 'bounce_email', "string(320)");
alterColumn('{{users}}', 'email', "string(320)");
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('assessments_idx', '{{assessments}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('assessments_idx3', '{{assessments}}', 'gid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('ixcode', '{{labels}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('{{labels_ixcode_idx}}', '{{labels}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('labels_code_idx', '{{labels}}', 'code');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
if (Yii::app()->db->driverName == 'pgsql') {
try {
setTransactionBookmark();
$oDB->createCommand("ALTER TABLE ONLY {{user_groups}} ADD PRIMARY KEY (ugid); ")->execute;
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand("ALTER TABLE ONLY {{users}} ADD PRIMARY KEY (uid); ")->execute;
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
}
// Additional corrections for MSSQL
alterColumn('{{answers}}', 'answer', "text", false);
alterColumn('{{assessments}}', 'name', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
alterColumn('{{defaultvalues}}', 'defaultvalue', "text");
alterColumn('{{expression_errors}}', 'eqn', "text");
alterColumn('{{expression_errors}}', 'prettyprint', "text");
alterColumn('{{groups}}', 'description', "text");
alterColumn('{{groups}}', 'grelevance', "text");
alterColumn('{{labels}}', 'title', "text");
alterColumn('{{question_attributes}}', 'value', "text");
alterColumn('{{questions}}', 'preg', "text");
alterColumn('{{questions}}', 'help', "text");
alterColumn('{{questions}}', 'relevance', "text");
alterColumn('{{questions}}', 'question', "text", false);
alterColumn('{{quota_languagesettings}}', 'quotals_quota_id', "integer", false);
alterColumn('{{quota_languagesettings}}', 'quotals_message', "text", false);
alterColumn('{{saved_control}}', 'refurl', "text");
alterColumn('{{saved_control}}', 'access_code', "text", false);
alterColumn('{{saved_control}}', 'ip', "text", false);
alterColumn('{{saved_control}}', 'saved_thisstep', "text", false);
alterColumn('{{saved_control}}', 'saved_date', "datetime", false);
alterColumn('{{surveys}}', 'attributedescriptions', "text");
alterColumn('{{surveys}}', 'emailresponseto', "text");
alterColumn('{{surveys}}', 'emailnotificationto', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_description', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_welcometext', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_invite', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_remind', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_register', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_confirm', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_attributecaptions', "text");
alterColumn('{{surveys_languagesettings}}', 'email_admin_notification', "text");
alterColumn('{{surveys_languagesettings}}', 'email_admin_responses', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_endtext', "text");
alterColumn('{{user_groups}}', 'description', "text", false);
alterColumn('{{conditions}}', 'value', 'string', false, '');
alterColumn('{{participant_shares}}', 'can_edit', "string(5)", false);
alterColumn('{{users}}', 'password', "binary", false);
dropColumn('{{users}}', 'one_time_pw');
addColumn('{{users}}', 'one_time_pw', 'binary');
$oDB->createCommand()->update('{{question_attributes}}', array('value' => '1'), "attribute = 'random_order' and value = '2'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 157), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 158) {
LimeExpressionManager::UpgradeConditionsToRelevance();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 158), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 159) {
alterColumn('{{failed_login_attempts}}', 'ip', "string(40)", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 159), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 160) {
alterLanguageCode('it', 'it-informal');
alterLanguageCode('it-formal', 'it');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 160), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 161) {
addColumn('{{survey_links}}', 'date_invited', 'datetime NULL default NULL');
addColumn('{{survey_links}}', 'date_completed', 'datetime NULL default NULL');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 161), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 162) {
/* Fix participant db types */
alterColumn('{{participant_attribute}}', 'value', "text", false);
alterColumn('{{participant_attribute_names_lang}}', 'attribute_name', "string(255)", false);
alterColumn('{{participant_attribute_values}}', 'value', "text", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 162), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 163) {
//Replace by <script type="text/javascript" src="{TEMPLATEURL}template.js"></script> by {TEMPLATEJS}
$replacedTemplate = replaceTemplateJS();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 163), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 164) {
upgradeTokens148();
// this should have bee done in 148 - that's why it is named this way
// fix survey tables for missing or incorrect token field
upgradeSurveyTables164();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 164), "stg_name='DBVersion'");
// Not updating settings table as upgrade process takes care of that step now
}
if ($iOldDBVersion < 165) {
$oDB->createCommand()->createTable('{{plugins}}', array('id' => 'pk', 'name' => 'string NOT NULL', 'active' => 'boolean'));
$oDB->createCommand()->createTable('{{plugin_settings}}', array('id' => 'pk', 'plugin_id' => 'integer NOT NULL', 'model' => 'string', 'model_id' => 'integer', 'key' => 'string', 'value' => 'text'));
alterColumn('{{surveys_languagesettings}}', 'surveyls_url', "text");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 165), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 166) {
$oDB->createCommand()->renameTable('{{survey_permissions}}', '{{permissions}}');
dropPrimaryKey('permissions');
alterColumn('{{permissions}}', 'permission', "string(100)", false);
$oDB->createCommand()->renameColumn('{{permissions}}', 'sid', 'entity_id');
alterColumn('{{permissions}}', 'entity_id', "string(100)", false);
addColumn('{{permissions}}', 'entity', "string(50)");
$oDB->createCommand("update {{permissions}} set entity='survey'")->query();
addColumn('{{permissions}}', 'id', 'pk');
$oDB->createCommand()->createIndex('idxPermissions', '{{permissions}}', 'entity_id,entity,permission,uid', true);
upgradePermissions166();
dropColumn('{{users}}', 'create_survey');
dropColumn('{{users}}', 'create_user');
dropColumn('{{users}}', 'delete_user');
dropColumn('{{users}}', 'superadmin');
dropColumn('{{users}}', 'configurator');
dropColumn('{{users}}', 'manage_template');
dropColumn('{{users}}', 'manage_label');
dropColumn('{{users}}', 'participant_panel');
$oDB->createCommand()->dropTable('{{templates_rights}}');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 166), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 167) {
addColumn('{{surveys_languagesettings}}', 'attachments', 'text');
addColumn('{{users}}', 'created', 'datetime');
addColumn('{{users}}', 'modified', 'datetime');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 167), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 168) {
addColumn('{{participants}}', 'created', 'datetime');
addColumn('{{participants}}', 'modified', 'datetime');
addColumn('{{participants}}', 'created_by', 'integer');
$oDB->createCommand('update {{participants}} set created_by=owner_uid')->query();
alterColumn('{{participants}}', 'created_by', "integer", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 168), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 169) {
// Add new column for question index options.
addColumn('{{surveys}}', 'questionindex', 'integer not null default 0');
// Set values for existing surveys.
$oDB->createCommand("update {{surveys}} set questionindex = 0 where allowjumps <> 'Y'")->query();
$oDB->createCommand("update {{surveys}} set questionindex = 1 where allowjumps = 'Y'")->query();
// Remove old column.
dropColumn('{{surveys}}', 'allowjumps');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 169), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 170) {
// renamed advanced attributes fields dropdown_dates_year_min/max
$oDB->createCommand()->update('{{question_attributes}}', array('attribute' => 'date_min'), "attribute='dropdown_dates_year_min'");
$oDB->createCommand()->update('{{question_attributes}}', array('attribute' => 'date_max'), "attribute='dropdown_dates_year_max'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 170), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 171) {
try {
dropColumn('{{sessions}}', 'data');
} catch (Exception $e) {
}
switch (Yii::app()->db->driverName) {
case 'mysql':
case 'mysqli':
addColumn('{{sessions}}', 'data', 'longbinary');
break;
case 'sqlsrv':
case 'dblib':
case 'mssql':
addColumn('{{sessions}}', 'data', 'VARBINARY(MAX)');
break;
case 'pgsql':
addColumn('{{sessions}}', 'data', 'BYTEA');
break;
default:
die('Unknown database type');
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 171), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 172) {
switch (Yii::app()->db->driverName) {
case 'pgsql':
// Special treatment for Postgres as it is too dumb to convert a string to a number without explicit being told to do so ... seriously?
alterColumn('{{permissions}}', 'entity_id', "INTEGER USING (entity_id::integer)", false);
break;
case 'sqlsrv':
case 'dblib':
case 'mssql':
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('permissions_idx2', '{{permissions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('idxPermissions', '{{permissions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{permissions}}', 'entity_id', "INTEGER", false);
$oDB->createCommand()->createIndex('permissions_idx2', '{{permissions}}', 'entity_id,entity,permission,uid', true);
break;
default:
alterColumn('{{permissions}}', 'entity_id', "INTEGER", false);
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 172), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 173) {
addColumn('{{participant_attribute_names}}', 'defaultname', "string(50) NOT NULL default ''");
upgradeCPDBAttributeDefaultNames173();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 173), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 174) {
alterColumn('{{participants}}', 'email', "string(254)");
alterColumn('{{saved_control}}', 'email', "string(254)");
alterColumn('{{surveys}}', 'adminemail', "string(254)");
alterColumn('{{surveys}}', 'bounce_email', "string(254)");
switch (Yii::app()->db->driverName) {
case 'sqlsrv':
case 'dblib':
case 'mssql':
dropUniqueKeyMSSQL('email', '{{users}}');
}
alterColumn('{{users}}', 'email', "string(254)");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 174), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 175) {
switch (Yii::app()->db->driverName) {
case 'pgsql':
// Special treatment for Postgres as it is too dumb to convert a boolean to a number without explicit being told to do so
alterColumn('{{plugins}}', 'active', "INTEGER USING (active::integer)", false);
break;
default:
alterColumn('{{plugins}}', 'active', "integer", false, '0');
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 175), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 176) {
upgradeTokens176();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 176), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 177) {
if (Yii::app()->getConfig('auth_webserver') === true) {
// using auth webserver, now activate the plugin with default settings.
if (!class_exists('Authwebserver', false)) {
$plugin = Plugin::model()->findByAttributes(array('name' => 'Authwebserver'));
if (!$plugin) {
$plugin = new Plugin();
$plugin->name = 'Authwebserver';
$plugin->active = 1;
$plugin->save();
$plugin = App()->getPluginManager()->loadPlugin('Authwebserver', $plugin->id);
$aPluginSettings = $plugin->getPluginSettings(true);
$aDefaultSettings = array();
foreach ($aPluginSettings as $key => $settings) {
if (is_array($settings) && array_key_exists('current', $settings)) {
$aDefaultSettings[$key] = $settings['current'];
}
}
$plugin->saveSettings($aDefaultSettings);
} else {
$plugin->active = 1;
$plugin->save();
}
}
}
upgradeSurveys177();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 177), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 178) {
if (Yii::app()->db->driverName == 'mysql' || Yii::app()->db->driverName == 'mysqli') {
modifyPrimaryKey('questions', array('qid', 'language'));
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 178), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 179) {
upgradeSurveys177();
// Needs to be run again to make sure
upgradeTokenTables179();
alterColumn('{{participants}}', 'email', "string(254)", false);
alterColumn('{{participants}}', 'firstname', "string(150)", false);
alterColumn('{{participants}}', 'lastname', "string(150)", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 179), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 180) {
$aUsers = User::model()->findAll();
$aPerm = array('entity_id' => 0, 'entity' => 'global', 'uid' => 0, 'permission' => 'auth_db', 'create_p' => 0, 'read_p' => 1, 'update_p' => 0, 'delete_p' => 0, 'import_p' => 0, 'export_p' => 0);
foreach ($aUsers as $oUser) {
if (!Permission::model()->hasGlobalPermission('auth_db', 'read', $oUser->uid)) {
$oPermission = new Permission();
foreach ($aPerm as $k => $v) {
$oPermission->{$k} = $v;
}
$oPermission->uid = $oUser->uid;
$oPermission->save();
}
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 180), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 181) {
upgradeTokenTables181();
upgradeSurveyTables181();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 181), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 182) {
fixKCFinder182();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 182), "stg_name='DBVersion'");
}
$oTransaction->commit();
// Activate schema caching
$oDB->schemaCachingDuration = 3600;
// Load all tables of the application in the schema
$oDB->schema->getTables();
// clear the cache of all loaded tables
$oDB->schema->refresh();
} catch (Exception $e) {
$oTransaction->rollback();
// Activate schema caching
$oDB->schemaCachingDuration = 3600;
// Load all tables of the application in the schema
$oDB->schema->getTables();
// clear the cache of all loaded tables
$oDB->schema->refresh();
echo '<br /><br />' . gT('An non-recoverable error happened during the update. Error details:') . "<p>" . htmlspecialchars($e->getMessage()) . '</p><br />';
return false;
}
fixLanguageConsistencyAllSurveys();
echo '<br /><br />' . sprintf(gT('Database update finished (%s)'), date('Y-m-d H:i:s')) . '<br /><br />';
return true;
}
