/**
* This function imports the old CSV data from 1.50 to 1.87 or older. Starting with 1.90 (DBVersion 143) there is an XML format instead
*
* @param array $sFullFilepath
* @returns array Information of imported questions/answers/etc.
*/
function CSVImportSurvey($sFullFilepath, $iDesiredSurveyId = NULL, $bTranslateLinks = true)
{
Yii::app()->loadHelper('database');
$clang = Yii::app()->lang;
$handle = fopen($sFullFilepath, "r");
while (!feof($handle)) {
$buffer = fgets($handle);
$bigarray[] = $buffer;
}
fclose($handle);
$aIgnoredAnswers = array();
$aSQIDReplacements = array();
$aLIDReplacements = array();
$aGIDReplacements = array();
$substitutions = array();
$aQuotaReplacements = array();
$importresults['error'] = false;
$importresults['importwarnings'] = array();
$importresults['question_attributes'] = 0;
if (isset($bigarray[0])) {
$bigarray[0] = removeBOM($bigarray[0]);
}
// Now we try to determine the dataformat of the survey file.
$importversion = 0;
if (isset($bigarray[1]) && isset($bigarray[4]) && substr($bigarray[1], 0, 22) == "# SURVEYOR SURVEY DUMP") {
$importversion = 100;
// Version 0.99 or 1.0 file
} elseif (substr($bigarray[0], 0, 24) == "# LimeSurvey Survey Dump" || substr($bigarray[0], 0, 25) == "# PHPSurveyor Survey Dump") {
// Seems to be a >1.0 version file - these files carry the version information to read in line two
$importversion = substr($bigarray[1], 12, 3);
} else {
$importresults['error'] = $clang->gT("This file is not a LimeSurvey survey file. Import failed.") . "\n";
return $importresults;
}
if ((int) $importversion < 112) {
$importresults['error'] = $clang->gT("This file is too old. Only files from LimeSurvey version 1.50 (DBVersion 112) and newer are supported.");
return $importresults;
}
// okay.. now lets drop the first 9 lines and get to the data
// This works for all versions
for ($i = 0; $i < 9; $i++) {
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//SURVEYS
if (array_search("# GROUPS TABLE\n", $bigarray)) {
$stoppoint = array_search("# GROUPS TABLE\n", $bigarray);
} elseif (array_search("# GROUPS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# GROUPS TABLE\r\n", $bigarray);
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$surveyarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//GROUPS
if (array_search("# QUESTIONS TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUESTIONS TABLE\n", $bigarray);
} elseif (array_search("# QUESTIONS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUESTIONS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$grouparray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUESTIONS
if (array_search("# ANSWERS TABLE\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\n", $bigarray);
} elseif (array_search("# ANSWERS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$questionarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//ANSWERS
if (array_search("# CONDITIONS TABLE\n", $bigarray)) {
$stoppoint = array_search("# CONDITIONS TABLE\n", $bigarray);
} elseif (array_search("# CONDITIONS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# CONDITIONS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$answerarray[] = str_replace("`default`", "`default_value`", $bigarray[$i]);
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//CONDITIONS
if (array_search("# LABELSETS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\n", $bigarray);
} elseif (array_search("# LABELSETS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\r\n", $bigarray);
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$conditionsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELSETS
if (array_search("# LABELS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\n", $bigarray);
} elseif (array_search("# LABELS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsetsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELS
if (array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray);
} elseif (array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//Question attributes
if (array_search("# ASSESSMENTS TABLE\n", $bigarray)) {
$stoppoint = array_search("# ASSESSMENTS TABLE\n", $bigarray);
} elseif (array_search("# ASSESSMENTS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# ASSESSMENTS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$question_attributesarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//ASSESSMENTS
if (array_search("# SURVEYS_LANGUAGESETTINGS TABLE\n", $bigarray)) {
$stoppoint = array_search("# SURVEYS_LANGUAGESETTINGS TABLE\n", $bigarray);
} elseif (array_search("# SURVEYS_LANGUAGESETTINGS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# SURVEYS_LANGUAGESETTINGS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
if ($i < $stoppoint - 2) {
$assessmentsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LANGAUGE SETTINGS
if (array_search("# QUOTA TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUOTA TABLE\n", $bigarray);
} elseif (array_search("# QUOTA TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUOTA TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
//$bigarray[$i]= trim($bigarray[$i]);
if (isset($bigarray[$i]) && trim($bigarray[$i]) != '') {
if (strpos($bigarray[$i], "#") === 0) {
unset($bigarray[$i]);
unset($bigarray[$i + 1]);
unset($bigarray[$i + 2]);
break;
} else {
$surveylsarray[] = $bigarray[$i];
}
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUOTA
if (array_search("# QUOTA_MEMBERS TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUOTA_MEMBERS TABLE\n", $bigarray);
} elseif (array_search("# QUOTA_MEMBERS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUOTA_MEMBERS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
if ($i < $stoppoint - 2) {
$quotaarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUOTA MEMBERS
if (array_search("# QUOTA_LANGUAGESETTINGS TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUOTA_LANGUAGESETTINGS TABLE\n", $bigarray);
} elseif (array_search("# QUOTA_LANGUAGESETTINGS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUOTA_LANGUAGESETTINGS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
if ($i < $stoppoint - 2) {
$quotamembersarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//Whatever is the last table - currently
//QUOTA LANGUAGE SETTINGS
$stoppoint = count($bigarray) - 1;
for ($i = 0; $i < $stoppoint - 1; $i++) {
if ($i <= $stoppoint) {
$quotalsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
if (isset($surveyarray)) {
$importresults['surveys'] = count($surveyarray);
} else {
$importresults['surveys'] = 0;
}
if (isset($surveylsarray)) {
$importresults['languages'] = count($surveylsarray) - 1;
} else {
$importresults['languages'] = 1;
}
if (isset($grouparray)) {
$importresults['groups'] = count($grouparray) - 1;
} else {
$importresults['groups'] = 0;
}
if (isset($questionarray)) {
$importresults['questions'] = count($questionarray);
} else {
$importresults['questions'] = 0;
}
if (isset($answerarray)) {
$importresults['answers'] = count($answerarray);
} else {
$importresults['answers'] = 0;
}
if (isset($conditionsarray)) {
$importresults['conditions'] = count($conditionsarray);
} else {
$importresults['conditions'] = 0;
}
if (isset($labelsetsarray)) {
$importresults['labelsets'] = count($labelsetsarray);
} else {
$importresults['labelsets'] = 0;
}
if (isset($assessmentsarray)) {
$importresults['assessments'] = count($assessmentsarray);
} else {
$importresults['assessments'] = 0;
}
if (isset($quotaarray)) {
$importresults['quota'] = count($quotaarray);
} else {
$importresults['quota'] = 0;
}
if (isset($quotamembersarray)) {
$importresults['quotamembers'] = count($quotamembersarray);
} else {
$importresults['quotamembers'] = 0;
}
if (isset($quotalsarray)) {
$importresults['quotals'] = count($quotalsarray);
} else {
$importresults['quotals'] = 0;
}
// CREATE SURVEY
if ($importresults['surveys'] > 0) {
$importresults['surveys']--;
}
if ($importresults['answers'] > 0) {
$importresults['answers'] = ($importresults['answers'] - 1) / $importresults['languages'];
}
if ($importresults['groups'] > 0) {
$countgroups = ($importresults['groups'] - 1) / $importresults['languages'];
}
if ($importresults['questions'] > 0) {
$importresults['questions'] = ($importresults['questions'] - 1) / $importresults['languages'];
}
if ($importresults['assessments'] > 0) {
$importresults['assessments']--;
}
if ($importresults['conditions'] > 0) {
$importresults['conditions']--;
}
if ($importresults['labelsets'] > 0) {
$importresults['labelsets']--;
}
if ($importresults['quota'] > 0) {
$importresults['quota']--;
}
$sfieldorders = convertCSVRowToArray($surveyarray[0], ',', '"');
$sfieldcontents = convertCSVRowToArray($surveyarray[1], ',', '"');
$surveyrowdata = array_combine($sfieldorders, $sfieldcontents);
$iOldSID = $surveyrowdata["sid"];
if (!$iOldSID) {
if ($importingfrom == "http") {
$importsurvey .= "<br /><div class='warningheader'>" . $clang->gT("Error") . "</div><br />\n";
$importsurvey .= $clang->gT("Import of this survey file failed") . "<br />\n";
$importsurvey .= $clang->gT("File does not contain LimeSurvey data in the correct format.") . "<br /><br />\n";
//Couldn't find the SID - cannot continue
$importsurvey .= "<input type='submit' value='" . $clang->gT("Main Admin Screen") . "' onclick=\"window.open('{$scriptname}', '_top')\" />\n";
$importsurvey .= "</div>\n";
unlink($sFullFilepath);
//Delete the uploaded file
return;
} else {
$clang->eT("Import of this survey file failed") . "\n" . $clang->gT("File does not contain LimeSurvey data in the correct format.") . "\n";
return;
}
}
if ($iDesiredSurveyId != NULL) {
$iNewSID = GetNewSurveyID($iDesiredSurveyId);
} else {
$iNewSID = GetNewSurveyID($iOldSID);
}
$insert = $surveyarray[0];
$sfieldorders = convertCSVRowToArray($surveyarray[0], ',', '"');
$sfieldcontents = convertCSVRowToArray($surveyarray[1], ',', '"');
$surveyrowdata = array_combine($sfieldorders, $sfieldcontents);
// Set new owner ID
$surveyrowdata['owner_id'] = Yii::app()->session['loginID'];
// Set new survey ID
$surveyrowdata['sid'] = $iNewSID;
$surveyrowdata['active'] = 'N';
if (validateTemplateDir($surveyrowdata['template']) !== $surveyrowdata['template']) {
$importresults['importwarnings'][] = sprintf($clang->gT('Template %s not found, please review when activating.'), $surveyrowdata['template']);
}
//if (isset($surveyrowdata['datecreated'])) {$surveyrowdata['datecreated'] = $connect->BindTimeStamp($surveyrowdata['datecreated']);}
unset($surveyrowdata['expires']);
unset($surveyrowdata['attribute1']);
unset($surveyrowdata['attribute2']);
unset($surveyrowdata['usestartdate']);
unset($surveyrowdata['notification']);
unset($surveyrowdata['useexpiry']);
unset($surveyrowdata['url']);
unset($surveyrowdata['lastpage']);
if (isset($surveyrowdata['private'])) {
$surveyrowdata['anonymized'] = $surveyrowdata['private'];
unset($surveyrowdata['private']);
}
if (isset($surveyrowdata['startdate'])) {
unset($surveyrowdata['startdate']);
}
$surveyrowdata['bounce_email'] = $surveyrowdata['adminemail'];
if (empty($surveyrowdata['datecreated'])) {
$surveyrowdata['datecreated'] = new CDbExpression('NOW()');
}
$iNewSID = Survey::model()->insertNewSurvey($surveyrowdata) or safeDie("<br />" . $clang->gT("Import of this survey file failed") . "<br />{$surveyarray[0]}<br /><br />\n");
// Now import the survey language settings
$fieldorders = convertCSVRowToArray($surveylsarray[0], ',', '"');
unset($surveylsarray[0]);
foreach ($surveylsarray as $slsrow) {
$fieldcontents = convertCSVRowToArray($slsrow, ',', '"');
$surveylsrowdata = array_combine($fieldorders, $fieldcontents);
// convert back the '\'.'n' char from the CSV file to true return char "\n"
$surveylsrowdata = array_map('convertCSVReturnToReturn', $surveylsrowdata);
// Convert the \n return char from welcometext to <br />
// translate internal links
if ($bTranslateLinks) {
$surveylsrowdata['surveyls_title'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_title']);
$surveylsrowdata['surveyls_description'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_description']);
$surveylsrowdata['surveyls_welcometext'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_welcometext']);
$surveylsrowdata['surveyls_urldescription'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_urldescription']);
$surveylsrowdata['surveyls_email_invite'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_email_invite']);
$surveylsrowdata['surveyls_email_remind'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_email_remind']);
$surveylsrowdata['surveyls_email_register'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_email_register']);
$surveylsrowdata['surveyls_email_confirm'] = translateLinks('survey', $iOldSID, $iNewSID, $surveylsrowdata['surveyls_email_confirm']);
}
unset($surveylsrowdata['lastpage']);
$surveylsrowdata['surveyls_survey_id'] = $iNewSID;
$lsiresult = Surveys_languagesettings::model()->insertNewSurvey($surveylsrowdata) or safeDie("<br />" . $clang->gT("Import of this survey file failed") . "<br />");
}
// The survey languagesettings are imported now
$aLanguagesSupported = array();
// this array will keep all the languages supported for the survey
$sBaseLanguage = Survey::model()->findByPk($iNewSID)->language;
$aLanguagesSupported[] = $sBaseLanguage;
// adds the base language to the list of supported languages
$aLanguagesSupported = array_merge($aLanguagesSupported, Survey::model()->findByPk($iNewSID)->additionalLanguages);
// DO SURVEY_RIGHTS
Survey_permissions::model()->giveAllSurveyPermissions(Yii::app()->session['loginID'], $iNewSID);
$importresults['deniedcountls'] = 0;
$qtypes = getQuestionTypeList("", "array");
$results['labels'] = 0;
$results['labelsets'] = 0;
$results['answers'] = 0;
$results['subquestions'] = 0;
//Do label sets
if (isset($labelsetsarray) && $labelsetsarray) {
$csarray = buildLabelSetCheckSumArray();
// build checksums over all existing labelsets
$count = 0;
foreach ($labelsetsarray as $lsa) {
$fieldorders = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$fieldcontents = convertCSVRowToArray($lsa, ',', '"');
if ($count == 0) {
$count++;
continue;
}
$labelsetrowdata = array_combine($fieldorders, $fieldcontents);
// Save old labelid
$oldlid = $labelsetrowdata['lid'];
unset($labelsetrowdata['lid']);
$lblsets = Labelsets::model();
$lsiresult = $lblsets->insertRecords($labelsetrowdata);
$results['labelsets']++;
// Get the new insert id for the labels inside this labelset
$newlid = Yii::app()->db->createCommand('Select LAST_INSERT_ID()')->query()->read();
$newlid = $newlid['LAST_INSERT_ID()'];
if ($labelsarray) {
$count = 0;
foreach ($labelsarray as $la) {
$lfieldorders = convertCSVRowToArray($labelsarray[0], ',', '"');
$lfieldcontents = convertCSVRowToArray($la, ',', '"');
if ($count == 0) {
$count++;
continue;
}
// Combine into one array with keys and values since its easier to handle
$labelrowdata = array_combine($lfieldorders, $lfieldcontents);
$labellid = $labelrowdata['lid'];
if ($importversion <= 132) {
$labelrowdata["assessment_value"] = (int) $labelrowdata["code"];
}
if ($labellid == $oldlid) {
$labelrowdata['lid'] = $newlid;
// translate internal links
if ($bTranslateLinks) {
$labelrowdata['title'] = translateLinks('label', $oldlid, $newlid, $labelrowdata['title']);
}
$liresult = Label::model()->insertRecords($labelrowdata);
if ($liresult !== false) {
$results['labels']++;
}
}
}
}
//CHECK FOR DUPLICATE LABELSETS
$thisset = "";
$query2 = "SELECT code, title, sortorder, language, assessment_value\n FROM {{labels}}\n WHERE lid=" . $newlid . "\n ORDER BY language, sortorder, code";
$result2 = Yii::app()->db->createCommand($query2)->query() or die("Died querying labelset {$lid}<br />");
foreach ($result2->readAll() as $row2) {
$row2 = array_values($row2);
$thisset .= implode('.', $row2);
}
// while
$newcs = dechex(crc32($thisset) * 1);
unset($lsmatch);
if (isset($csarray)) {
foreach ($csarray as $key => $val) {
if ($val == $newcs) {
$lsmatch = $key;
}
}
}
if (isset($lsmatch) || Yii::app()->session['USER_RIGHT_MANAGE_LABEL'] != 1) {
//There is a matching labelset or the user is not allowed to edit labels -
// So, we will delete this one and refer to the matched one.
$query = "DELETE FROM {{labels}} WHERE lid={$newlid}";
$result = Yii::app()->db->createCommand($query)->execute();
$results['labels'] = $results['labels'] - $result;
$query = "DELETE FROM {{labelsets}} WHERE lid={$newlid}";
$result = Yii::app()->db->createCommand($query)->execute();
$results['labelsets'] = $results['labelsets'] - $result;
$newlid = $lsmatch;
} else {
//There isn't a matching labelset, add this checksum to the $csarray array
$csarray[$newlid] = $newcs;
}
//END CHECK FOR DUPLICATES
$aLIDReplacements[$oldlid] = $newlid;
}
}
// Import groups
if (isset($grouparray) && $grouparray) {
// do GROUPS
$gafieldorders = convertCSVRowToArray($grouparray[0], ',', '"');
unset($grouparray[0]);
foreach ($grouparray as $ga) {
$gacfieldcontents = convertCSVRowToArray($ga, ',', '"');
$grouprowdata = array_combine($gafieldorders, $gacfieldcontents);
//Now an additional integrity check if there are any groups not belonging into this survey
if ($grouprowdata['sid'] != $iOldSID) {
$results['fatalerror'] = $clang->gT("A group in the CSV/SQL file is not part of the same survey. The import of the survey was stopped.") . "<br />\n";
return $results;
}
$grouprowdata['sid'] = $iNewSID;
// remember group id
$oldgid = $grouprowdata['gid'];
//update/remove the old group id
if (isset($aGIDReplacements[$oldgid])) {
$grouprowdata['gid'] = $aGIDReplacements[$oldgid];
} else {
unset($grouprowdata['gid']);
}
// Everything set - now insert it
$grouprowdata = array_map('convertCSVReturnToReturn', $grouprowdata);
// translate internal links
if ($bTranslateLinks) {
$grouprowdata['group_name'] = translateLinks('survey', $iOldSID, $iNewSID, $grouprowdata['group_name']);
$grouprowdata['description'] = translateLinks('survey', $iOldSID, $iNewSID, $grouprowdata['description']);
}
if (isset($grouprowdata['gid'])) {
switchMSSQLIdentityInsert('groups', true);
}
$gres = Groups::model()->insertRecords($grouprowdata) or safeDie($clang->gT('Error') . ": Failed to insert group<br />\\<br />\n");
if (isset($grouprowdata['gid'])) {
switchMSSQLIdentityInsert('groups', false);
}
if (!isset($grouprowdata['gid'])) {
$aGIDReplacements[$oldgid] = Yii::app()->db->createCommand('Select LAST_INSERT_ID()')->query()->read();
$aGIDReplacements[$oldgid] = $aGIDReplacements[$oldgid]['LAST_INSERT_ID()'];
}
}
// Fix sortorder of the groups - if users removed groups manually from the csv file there would be gaps
fixSortOrderGroups($iNewSID);
}
// GROUPS is DONE
// Import questions
if (isset($questionarray) && $questionarray) {
$qafieldorders = convertCSVRowToArray($questionarray[0], ',', '"');
unset($questionarray[0]);
foreach ($questionarray as $qa) {
$qacfieldcontents = convertCSVRowToArray($qa, ',', '"');
$questionrowdata = array_combine($qafieldorders, $qacfieldcontents);
$questionrowdata = array_map('convertCSVReturnToReturn', $questionrowdata);
$questionrowdata["type"] = strtoupper($questionrowdata["type"]);
// Skip not supported languages
if (!in_array($questionrowdata['language'], $aLanguagesSupported)) {
continue;
}
// replace the sid
$questionrowdata["sid"] = $iNewSID;
// Skip if gid is invalid
if (!isset($aGIDReplacements[$questionrowdata['gid']])) {
continue;
}
$questionrowdata["gid"] = $aGIDReplacements[$questionrowdata['gid']];
if (isset($aQIDReplacements[$questionrowdata['qid']])) {
$questionrowdata['qid'] = $aQIDReplacements[$questionrowdata['qid']];
} else {
$oldqid = $questionrowdata['qid'];
unset($questionrowdata['qid']);
}
unset($oldlid1);
unset($oldlid2);
if (isset($questionrowdata['lid']) && $questionrowdata['lid'] > 0) {
$oldlid1 = $questionrowdata['lid'];
}
if (isset($questionrowdata['lid1']) && $questionrowdata['lid1'] > 0) {
$oldlid2 = $questionrowdata['lid1'];
}
unset($questionrowdata['lid']);
unset($questionrowdata['lid1']);
if ($questionrowdata['type'] == 'W') {
$questionrowdata['type'] = '!';
} elseif ($questionrowdata['type'] == 'Z') {
$questionrowdata['type'] = 'L';
$aIgnoredAnswers[] = $oldqid;
}
if (!isset($questionrowdata["question_order"]) || $questionrowdata["question_order"] == '') {
$questionrowdata["question_order"] = 0;
}
// translate internal links
if ($bTranslateLinks) {
$questionrowdata['question'] = translateLinks('survey', $iOldSID, $iNewSID, $questionrowdata['question']);
$questionrowdata['help'] = translateLinks('survey', $iOldSID, $iNewSID, $questionrowdata['help']);
}
if (isset($questionrowdata['qid'])) {
switchMSSQLIdentityInsert('questions', true);
}
$qres = Questions::model()->insertRecords($questionrowdata) or safeDie($clang->gT("Error") . ": Failed to insert question<br />");
if (isset($questionrowdata['qid'])) {
switchMSSQLIdentityInsert('questions', false);
$saveqid = $questionrowdata['qid'];
} else {
$aQIDReplacements[$oldqid] = Yii::app()->db->createCommand('Select LAST_INSERT_ID()')->query()->read();
$aQIDReplacements[$oldqid] = $aQIDReplacements[$oldqid]['LAST_INSERT_ID()'];
$saveqid = $aQIDReplacements[$oldqid];
}
// Now we will fix up old label sets where they are used as answers
if ((isset($oldlid1) && isset($aLIDReplacements[$oldlid1]) || isset($oldlid2) && isset($aLIDReplacements[$oldlid2])) && ($qtypes[$questionrowdata['type']]['answerscales'] > 0 || $qtypes[$questionrowdata['type']]['subquestions'] > 1)) {
$query = "select * from {{labels}} where lid={$aLIDReplacements[$oldlid1]} and language='{$questionrowdata['language']}'";
$oldlabelsresult = Yii::app()->db->createCommand($query)->query();
foreach ($oldlabelsresult->readAll() as $labelrow) {
if (in_array($labelrow['language'], $aLanguagesSupported)) {
if ($qtypes[$questionrowdata['type']]['subquestions'] < 2) {
$qinsert = "insert INTO {{answers}} (qid,code,answer,sortorder,language,assessment_value)\n VALUES ({$aQIDReplacements[$oldqid]},'" . $labelrow['code'] . "','" . $labelrow['title'] . "','" . $labelrow['sortorder'] . "','" . $labelrow['language'] . "','" . $labelrow['assessment_value'] . "')";
$qres = Yii::app()->db->createCommand($qinsert)->query() or safeDie($clang->gT("Error") . ": Failed to insert answer (lid1) <br />\n{$qinsert}<br />\n");
} else {
if (isset($aSQIDReplacements[$labelrow['code'] . '_' . $saveqid])) {
$fieldname = 'qid,';
$data = $aSQIDReplacements[$labelrow['code'] . '_' . $saveqid] . ',';
} else {
$fieldname = '';
$data = '';
}
$qinsert = "insert INTO {{questions}} ({$fieldname} parent_qid,title,question,question_order,language,scale_id,type, sid, gid)\n VALUES ({$data}{$aQIDReplacements[$oldqid]},'" . $labelrow['code'] . "','" . $labelrow['title'] . "','" . $labelrow['sortorder'] . "','" . $labelrow['language'] . "',1,'{$questionrowdata['type']}',{$questionrowdata['sid']},{$questionrowdata['gid']})";
$qres = Yii::app()->db->createCommand($qinsert)->query() or safeDie($clang->gT("Error") . ": Failed to insert question <br />\n{$qinsert}<br />\n");
if ($fieldname == '') {
$aSQIDReplacements[$labelrow['code'] . '_' . $saveqid] = Yii::app()->db->getCommandBuilder()->getLastInsertID('{{questions}}');
}
}
}
}
if (isset($oldlid2) && $qtypes[$questionrowdata['type']]['answerscales'] > 1) {
$query = "select * from {{labels}} where lid={$aLIDReplacements[$oldlid2]} and language='{$questionrowdata['language']}'";
$oldlabelsresult = Yii::app()->db->createCommand($query)->query();
foreach ($oldlabelsresult->readAll() as $labelrow) {
$qinsert = "insert INTO {{answers}} (qid,code,answer,sortorder,language,assessment_value,scale_id)\n VALUES ({$aQIDReplacements[$oldqid]},'" . $labelrow['code'] . "','" . $labelrow['title'] . "','" . $labelrow['sortorder'] . "','" . $labelrow['language'] . "','" . $labelrow['assessment_value'] . "',1)";
$qres = Yii::app()->db->createCommand($qinsert)->query() or safeDie($clang->gT("Error") . ": Failed to insert answer (lid2)<br />\n{$qinsert}<br />\n");
}
}
}
}
}
//Do answers
if (isset($answerarray) && $answerarray) {
$answerfieldnames = convertCSVRowToArray($answerarray[0], ',', '"');
unset($answerarray[0]);
foreach ($answerarray as $aa) {
$answerfieldcontents = convertCSVRowToArray($aa, ',', '"');
$answerrowdata = array_combine($answerfieldnames, $answerfieldcontents);
if (in_array($answerrowdata['qid'], $aIgnoredAnswers)) {
// Due to a bug in previous LS versions there may be orphaned answers with question type Z (which is now L)
// this way they are ignored
continue;
}
if ($answerrowdata === false) {
$importquestion .= '<br />' . $clang->gT("Faulty line in import - fields and data don't match") . ":" . implode(',', $answerfieldcontents);
}
// Skip not supported languages
if (!in_array($answerrowdata['language'], $aLanguagesSupported)) {
continue;
}
// replace the qid for the new one (if there is no new qid in the $aQIDReplacements array it mean that this answer is orphan -> error, skip this record)
if (isset($aQIDReplacements[$answerrowdata["qid"]])) {
$answerrowdata["qid"] = $aQIDReplacements[$answerrowdata["qid"]];
} else {
continue;
}
// a problem with this answer record -> don't consider
if ($importversion <= 132) {
$answerrowdata["assessment_value"] = (int) $answerrowdata["code"];
}
// Convert default values for single select questions
$query1 = 'select type,gid from {{questions}} where qid=' . $answerrowdata["qid"];
$resultquery1 = Yii::app()->db->createCommand($query1)->query();
$questiontemp = $resultquery1->read();
$oldquestion['newtype'] = $questiontemp['type'];
$oldquestion['gid'] = $questiontemp['gid'];
if ($answerrowdata['default_value'] == 'Y' && ($oldquestion['newtype'] == 'L' || $oldquestion['newtype'] == 'O' || $oldquestion['newtype'] == '!')) {
$insertdata = array();
$insertdata['qid'] = $newqid;
$insertdata['language'] = $answerrowdata['language'];
$insertdata['defaultvalue'] = $answerrowdata['answer'];
$qres = Defaultvalues::model()->insertRecords($insertdata) or safeDie("Error: Failed to insert defaultvalue <br />");
}
// translate internal links
if ($bTranslateLinks) {
$answerrowdata['answer'] = translateLinks('survey', $iOldSID, $iNewSID, $answerrowdata['answer']);
}
// Everything set - now insert it
$answerrowdata = array_map('convertCSVReturnToReturn', $answerrowdata);
if ($qtypes[$oldquestion['newtype']]['subquestions'] > 0) {
$questionrowdata = array();
if (isset($aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']])) {
$questionrowdata['qid'] = $aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']];
}
$questionrowdata['parent_qid'] = $answerrowdata['qid'];
$questionrowdata['sid'] = $iNewSID;
$questionrowdata['gid'] = $oldquestion['gid'];
$questionrowdata['title'] = $answerrowdata['code'];
$questionrowdata['question'] = $answerrowdata['answer'];
$questionrowdata['question_order'] = $answerrowdata['sortorder'];
$questionrowdata['language'] = $answerrowdata['language'];
$questionrowdata['type'] = $oldquestion['newtype'];
if (isset($questionrowdata['qid'])) {
switchMSSQLIdentityInsert('questions', true);
}
if ($questionrowdata) {
XSSFilterArray($questionrowdata);
}
$qres = Questions::model()->insertRecords($questionrowdata) or safeDie("Error: Failed to insert subquestion <br />");
if (!isset($questionrowdata['qid'])) {
$aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']] = Yii::app()->db->createCommand('Select LAST_INSERT_ID()')->query()->read();
$aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']] = $aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']]['LAST_INSERT_ID()'];
} else {
switchMSSQLIdentityInsert('questions', false);
}
$results['subquestions']++;
// also convert default values subquestions for multiple choice
if ($answerrowdata['default_value'] == 'Y' && ($oldquestion['newtype'] == 'M' || $oldquestion['newtype'] == 'P')) {
$insertdata = array();
$insertdata['qid'] = $newqid;
$insertdata['sqid'] = $aSQIDReplacements[$answerrowdata['code']];
$insertdata['language'] = $answerrowdata['language'];
$insertdata['defaultvalue'] = 'Y';
if ($insertdata) {
XSSFilterArray($insertdata);
}
$qres = Defaultvalues::model()->insertRecords($insertdata) or safeDie("Error: Failed to insert defaultvalue <br />");
}
} else {
unset($answerrowdata['default_value']);
if ($answerrowdata) {
XSSFilterArray($answerrowdata);
}
$ares = Answers::model()->insertRecords($answerrowdata) or safeDie("Error: Failed to insert answer<br />");
$results['answers']++;
}
}
}
// get all group ids and fix questions inside each group
$gquery = "SELECT gid FROM {{groups}} where sid={$iNewSID} group by gid ORDER BY gid";
//Get last question added (finds new qid)
$gres = Yii::app()->db->createCommand($gquery)->query();
foreach ($gres->readAll() as $grow) {
Questions::model()->updateQuestionOrder($grow['gid'], $iNewSID);
}
//We've built two arrays along the way - one containing the old SID, GID and QIDs - and their NEW equivalents
//and one containing the old 'extended fieldname' and its new equivalent. These are needed to import conditions and question_attributes.
if (isset($question_attributesarray) && $question_attributesarray) {
//ONLY DO THIS IF THERE ARE QUESTION_ATTRIBUES
$fieldorders = convertCSVRowToArray($question_attributesarray[0], ',', '"');
unset($question_attributesarray[0]);
foreach ($question_attributesarray as $qar) {
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
$qarowdata = array_combine($fieldorders, $fieldcontents);
$newqid = "";
$qarowdata["qid"] = $aQIDReplacements[$qarowdata["qid"]];
unset($qarowdata["qaid"]);
$result = Question_attributes::model()->insertRecords($qarowdata);
if ($result > 0) {
$importresults['question_attributes']++;
}
}
}
if (isset($assessmentsarray) && $assessmentsarray) {
//ONLY DO THIS IF THERE ARE QUESTION_ATTRIBUTES
$fieldorders = convertCSVRowToArray($assessmentsarray[0], ',', '"');
unset($assessmentsarray[0]);
foreach ($assessmentsarray as $qar) {
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
$asrowdata = array_combine($fieldorders, $fieldcontents);
if (isset($asrowdata['link'])) {
if (trim($asrowdata['link']) != '') {
$asrowdata['message'] = $asrowdata['message'] . '<br /><a href="' . $asrowdata['link'] . '">' . $asrowdata['link'] . '</a>';
}
unset($asrowdata['link']);
}
if ($asrowdata["gid"] > 0) {
$asrowdata["gid"] = $aGIDReplacements[$asrowdata["gid"]];
}
$asrowdata["sid"] = $iNewSID;
unset($asrowdata["id"]);
$result = Assessments::model()->insertRecords($asrowdata) or safeDie("Couldn't insert assessment<br />");
unset($newgid);
}
}
if (isset($quotaarray) && $quotaarray) {
//ONLY DO THIS IF THERE ARE QUOTAS
$fieldorders = convertCSVRowToArray($quotaarray[0], ',', '"');
unset($quotaarray[0]);
foreach ($quotaarray as $qar) {
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
$asrowdata = array_combine($fieldorders, $fieldcontents);
$iOldSID = $asrowdata["sid"];
foreach ($substitutions as $subs) {
if ($iOldSID == $subs[0]) {
$iNewSID = $subs[3];
}
}
$asrowdata["sid"] = $iNewSID;
$oldid = $asrowdata["id"];
unset($asrowdata["id"]);
$quotadata[] = $asrowdata;
//For use later if needed
$result = Quota::model()->insertRecords($asrowdata) or safeDie("Couldn't insert quota<br />");
$aQuotaReplacements[$oldid] = Yii::app()->db->createCommand('Select LAST_INSERT_ID()')->query()->read();
$aQuotaReplacements[$oldid] = $aQuotaReplacements[$oldid]['LAST_INSERT_ID()'];
}
}
if (isset($quotamembersarray) && $quotamembersarray) {
//ONLY DO THIS IF THERE ARE QUOTA MEMBERS
$count = 0;
foreach ($quotamembersarray as $qar) {
$fieldorders = convertCSVRowToArray($quotamembersarray[0], ',', '"');
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
if ($count == 0) {
$count++;
continue;
}
$asrowdata = array_combine($fieldorders, $fieldcontents);
$iOldSID = $asrowdata["sid"];
$newqid = "";
$newquotaid = "";
$oldqid = $asrowdata['qid'];
$oldquotaid = $asrowdata['quota_id'];
foreach ($substitutions as $subs) {
if ($iOldSID == $subs[0]) {
$iNewSID = $subs[3];
}
if ($oldqid == $subs[2]) {
$newqid = $subs[5];
}
}
$newquotaid = $aQuotaReplacements[$oldquotaid];
$asrowdata["sid"] = $iNewSID;
$asrowdata["qid"] = $newqid;
$asrowdata["quota_id"] = $newquotaid;
unset($asrowdata["id"]);
$result = Quota_members::model()->insertRecords($asrowdata) or safeDie("Couldn't insert quota<br />");
}
}
if (isset($quotalsarray) && $quotalsarray) {
//ONLY DO THIS IF THERE ARE QUOTA LANGUAGE SETTINGS
$count = 0;
foreach ($quotalsarray as $qar) {
$fieldorders = convertCSVRowToArray($quotalsarray[0], ',', '"');
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
if ($count == 0) {
$count++;
continue;
}
$asrowdata = array_combine($fieldorders, $fieldcontents);
$newquotaid = "";
$oldquotaid = $asrowdata['quotals_quota_id'];
$newquotaid = $aQuotaReplacements[$oldquotaid];
$asrowdata["quotals_quota_id"] = $newquotaid;
unset($asrowdata["quotals_id"]);
$result = Quota_languagesettings::model()->insertRecords($asrowdata) or safeDie("Couldn't insert quota<br />");
}
}
//if there are quotas, but no quotals, then we need to create default dummy for each quota (this handles exports from pre-language quota surveys)
if ($importresults['quota'] > 0 && (!isset($importresults['quotals']) || $importresults['quotals'] == 0)) {
$i = 0;
$defaultsurveylanguage = isset($defaultsurveylanguage) ? $defaultsurveylanguage : "en";
foreach ($aQuotaReplacements as $oldquotaid => $newquotaid) {
$asrowdata = array("quotals_quota_id" => $newquotaid, "quotals_language" => $defaultsurveylanguage, "quotals_name" => $quotadata[$i]["name"], "quotals_message" => $clang->gT("Sorry your responses have exceeded a quota on this survey."), "quotals_url" => "", "quotals_urldescrip" => "");
$i++;
}
$result = Quota_languagesettings::model()->insertRecords($asrowdata) or safeDie("Couldn't insert quota<br />");
$countquotals = $i;
}
// Do conditions
if (isset($conditionsarray) && $conditionsarray) {
//ONLY DO THIS IF THERE ARE CONDITIONS!
$fieldorders = convertCSVRowToArray($conditionsarray[0], ',', '"');
unset($conditionsarray[0]);
// Exception for conditions based on attributes
$aQIDReplacements[0] = 0;
foreach ($conditionsarray as $car) {
$fieldcontents = convertCSVRowToArray($car, ',', '"');
$conditionrowdata = array_combine($fieldorders, $fieldcontents);
unset($conditionrowdata["cid"]);
if (!isset($conditionrowdata["method"]) || trim($conditionrowdata["method"]) == '') {
$conditionrowdata["method"] = '==';
}
if (!isset($conditionrowdata["scenario"]) || trim($conditionrowdata["scenario"]) == '') {
$conditionrowdata["scenario"] = 1;
}
$oldcqid = $conditionrowdata["cqid"];
$query = 'select gid from {{questions}} where qid=' . $aQIDReplacements[$conditionrowdata["cqid"]];
$res = Yii::app()->db->createCommand($query)->query();
$resrow = $res->read();
$oldgid = array_search($resrow['gid'], $aGIDReplacements);
$conditionrowdata["qid"] = $aQIDReplacements[$conditionrowdata["qid"]];
$conditionrowdata["cqid"] = $aQIDReplacements[$conditionrowdata["cqid"]];
$oldcfieldname = $conditionrowdata["cfieldname"];
$conditionrowdata["cfieldname"] = str_replace($iOldSID . 'X' . $oldgid . 'X' . $oldcqid, $iNewSID . 'X' . $aGIDReplacements[$oldgid] . 'X' . $conditionrowdata["cqid"], $conditionrowdata["cfieldname"]);
$result = Conditions::model()->insertRecords($conditionrowdata) or safeDie("Couldn't insert condition<br />");
}
}
LimeExpressionManager::RevertUpgradeConditionsToRelevance($iNewSID);
LimeExpressionManager::UpgradeConditionsToRelevance($iNewSID);
LimeExpressionManager::SetSurveyId($iNewSID);
$importresults['importversion'] = $importversion;
$importresults['newsid'] = $iNewSID;
$importresults['oldsid'] = $iOldSID;
return $importresults;
}
{
$ignoredcolumns[]=$fieldname;
}
}
if (!in_array('firstname',$firstline) || !in_array('lastname',$firstline) || !in_array('email',$firstline))
{
$tokenoutput .= "<div class='warningheader'>".$clang->gT("Error: Your uploaded file is missing one or more of the mandatory columns: 'firstname', 'lastname' or 'email'")."</div><br />";
$recordcount=count($tokenlistarray);
break;
}
}
else
{
$line = convertCSVRowToArray($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;
/**
* Checks that each object from an array of CSV data [question-rows,answer-rows,labelsets-row] supports at least a given language
*
* @param mixed $csvarray array with a line of csv data per row
* @param mixed $idkeysarray array of integers giving the csv-row numbers of the object keys
* @param mixed $langfieldnum integer giving the csv-row number of the language(s) filed
* ==> the language field can be a single language code or a
* space separated language code list
* @param mixed $langcode the language code to be tested
* @param mixed $hasheader if we should strip off the first line (if it contains headers)
*/
function doesImportArraySupportLanguage($csvarray, $idkeysarray, $langfieldnum, $langcode, $hasheader = false)
{
// An array with one row per object id and langsupport status as value
$objlangsupportarray = array();
if ($hasheader === true) {
// stripping first row to skip headers if any
array_shift($csvarray);
}
foreach ($csvarray as $csvrow) {
$rowcontents = convertCSVRowToArray($csvrow, ',', '"');
$rowid = "";
foreach ($idkeysarray as $idfieldnum) {
$rowid .= $rowcontents[$idfieldnum] . "-";
}
$rowlangarray = explode(" ", @$rowcontents[$langfieldnum]);
if (!isset($objlangsupportarray[$rowid])) {
if (array_search($langcode, $rowlangarray) !== false) {
$objlangsupportarray[$rowid] = "true";
} else {
$objlangsupportarray[$rowid] = "false";
}
} else {
if ($objlangsupportarray[$rowid] == "false" && array_search($langcode, $rowlangarray) !== false) {
$objlangsupportarray[$rowid] = "true";
}
}
}
// end foreach rown
// If any of the object doesn't support the given language, return false
if (array_search("false", $objlangsupportarray) === false) {
return true;
} else {
return false;
}
}
function CSVImportLabelset($sFullFilepath, $options)
{
global $dbprefix, $connect, $clang;
$results['labelsets'] = 0;
$results['labels'] = 0;
$results['warnings'] = array();
$csarray = buildLabelSetCheckSumArray();
//$csarray is now a keyed array with the Checksum of each of the label sets, and the lid as the key
$handle = fopen($sFullFilepath, "r");
while (!feof($handle)) {
$buffer = fgets($handle);
//To allow for very long survey welcomes (up to 10k)
$bigarray[] = $buffer;
}
fclose($handle);
if (substr($bigarray[0], 0, 27) != "# LimeSurvey Label Set Dump" && substr($bigarray[0], 0, 28) != "# PHPSurveyor Label Set Dump") {
$results['fatalerror'] = $clang->gT("This file is not a LimeSurvey label set file. Import failed.");
return $results;
}
for ($i = 0; $i < 9; $i++) {
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABEL SETS
if (array_search("# LABELS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\n", $bigarray);
} elseif (array_search("# LABELS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsetsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELS
$stoppoint = count($bigarray) - 1;
for ($i = 0; $i < $stoppoint; $i++) {
// do not import empty lines
if (trim($bigarray[$i]) != '') {
$labelsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$countlabelsets = count($labelsetsarray) - 1;
$countlabels = count($labelsarray) - 1;
if (isset($labelsetsarray) && $labelsetsarray) {
$count = 0;
foreach ($labelsetsarray as $lsa) {
$fieldorders = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$fieldcontents = convertCSVRowToArray($lsa, ',', '"');
if ($count == 0) {
$count++;
continue;
}
$labelsetrowdata = array_combine($fieldorders, $fieldcontents);
// Save old labelid
$oldlid = $labelsetrowdata['lid'];
// set the new language
unset($labelsetrowdata['lid']);
$newvalues = array_values($labelsetrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lsainsert = "insert INTO {$dbprefix}labelsets (" . implode(',', array_keys($labelsetrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$lsiresult = $connect->Execute($lsainsert);
$results['labelsets']++;
// Get the new insert id for the labels inside this labelset
$newlid = $connect->Insert_ID("{$dbprefix}labelsets", 'lid');
if ($labelsarray) {
$count = 0;
$lfieldorders = convertCSVRowToArray($labelsarray[0], ',', '"');
unset($labelsarray[0]);
foreach ($labelsarray as $la) {
$lfieldcontents = convertCSVRowToArray($la, ',', '"');
// Combine into one array with keys and values since its easier to handle
$labelrowdata = array_combine($lfieldorders, $lfieldcontents);
$labellid = $labelrowdata['lid'];
if ($labellid == $oldlid) {
$labelrowdata['lid'] = $newlid;
// translate internal links
$labelrowdata['title'] = translink('label', $oldlid, $newlid, $labelrowdata['title']);
if (!isset($labelrowdata["assessment_value"])) {
$labelrowdata["assessment_value"] = (int) $labelrowdata["code"];
}
$newvalues = array_values($labelrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lainsert = "insert INTO {$dbprefix}labels (" . implode(',', array_keys($labelrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$liresult = $connect->Execute($lainsert);
$results['labels']++;
}
}
}
//CHECK FOR DUPLICATE LABELSETS
if (isset($_POST['checkforduplicates'])) {
$thisset = "";
$query2 = "SELECT code, title, sortorder, language, assessment_value\n FROM " . db_table_name('labels') . "\n WHERE lid=" . $newlid . "\n ORDER BY language, sortorder, code";
$result2 = db_execute_num($query2) or safe_die("Died querying labelset {$lid}<br />{$query2}<br />" . $connect->ErrorMsg());
while ($row2 = $result2->FetchRow()) {
$thisset .= implode('.', $row2);
}
// while
$newcs = dechex(crc32($thisset) * 1);
unset($lsmatch);
if (isset($csarray) && $options['checkforduplicates'] == 'on') {
foreach ($csarray as $key => $val) {
// echo $val."-".$newcs."<br/>"; For debug purposes
if ($val == $newcs) {
$lsmatch = $key;
}
}
}
if (isset($lsmatch)) {
//There is a matching labelset. So, we will delete this one and refer
//to the matched one.
$query = "DELETE FROM {$dbprefix}labels WHERE lid={$newlid}";
$result = $connect->Execute($query) or safe_die("Couldn't delete labels<br />{$query}<br />" . $connect->ErrorMsg());
$query = "DELETE FROM {$dbprefix}labelsets WHERE lid={$newlid}";
$result = $connect->Execute($query) or safe_die("Couldn't delete labelset<br />{$query}<br />" . $connect->ErrorMsg());
$newlid = $lsmatch;
$results['warnings'][] = $clang->gT("Label set was not imported because the same label set already exists.") . " " . sprintf($clang->gT("Existing LID: %s"), $newlid);
}
//END CHECK FOR DUPLICATES
}
}
}
return $results;
}
function uploadCSV()
{
$clang = $this->getController()->lang;
unset(Yii::app()->session['summary']);
$characterset = Yii::app()->request->getPost('characterset');
$separator = Yii::app()->request->getPost('separatorused');
$newarray = Yii::app()->request->getPost('newarray');
$mappedarray = Yii::app()->request->getPost('mappedarray', false);
$filterblankemails = Yii::app()->request->getPost('filterbea');
$overwrite = Yii::app()->request->getPost('overwrite');
$sFilePath = Yii::app()->getConfig('tempdir') . '/' . basename(Yii::app()->request->getPost('fullfilepath'));
$errorinupload = "";
$recordcount = 0;
$mandatory = 0;
$mincriteria = 0;
$imported = 0;
$dupcount = 0;
$overwritten = 0;
$dupreason = "nameemail";
//Default duplicate comparison method
$duplicatelist = array();
$invalidemaillist = array();
$invalidformatlist = array();
$invalidattribute = array();
$invalidparticipantid = array();
$aGlobalErrors = array();
/* If no mapped array */
if (!$mappedarray) {
$mappedarray = array();
}
/* Adjust system settings to read file with MAC line endings */
@ini_set('auto_detect_line_endings', true);
/* Open the uploaded file into an array */
$tokenlistarray = file($sFilePath);
// open it and trim the endings
$separator = Yii::app()->request->getPost('separatorused');
$uploadcharset = Yii::app()->request->getPost('characterset');
/* The $newarray contains a list of fields that will be used
to create new attributes */
if (!empty($newarray)) {
/* Create a new entry in the lime_participant_attribute_names table,
and it's associated lime_participant_attribute_names_lang table
for each NEW attribute being created in this import process */
foreach ($newarray as $key => $value) {
$aData = array('attribute_type' => 'TB', 'attribute_name' => $value, 'visible' => 'FALSE');
$insertid = ParticipantAttributeName::model()->storeAttributeCSV($aData);
/* Keep a record of the attribute_id for this new attribute
in the $mappedarray string. For example, if the new attribute
has attribute_id of 35 and is called "gender",
$mappedarray['35']='gender' */
$mappedarray[$insertid] = $value;
}
}
if (!isset($uploadcharset)) {
$uploadcharset = 'auto';
}
foreach ($tokenlistarray as $buffer) {
//Iterate through the CSV file line by line
$buffer = @mb_convert_encoding($buffer, "UTF-8", $uploadcharset);
$firstname = "";
$lastname = "";
$email = "";
$language = "";
if ($recordcount == 0) {
//The first time we iterate through the file we look at the very
//first line, which contains field names, not values to import
// Pick apart the first line
$buffer = removeBOM($buffer);
$attrid = ParticipantAttributeName::model()->getAttributeID();
$allowedfieldnames = array('participant_id', 'firstname', 'lastname', 'email', 'language', 'blacklisted');
$aFilterDuplicateFields = array('firstname', 'lastname', 'email');
if (!empty($mappedarray)) {
foreach ($mappedarray as $key => $value) {
array_push($allowedfieldnames, strtolower($value));
}
}
//For Attributes
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 = convertCSVRowToArray($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(strtolower($fieldname), $allowedfieldnames) && !in_array($fieldname, $mappedarray)) {
$ignoredcolumns[] = $fieldname;
} else {
$firstline[$index] = strtolower($fieldname);
}
}
if (!in_array('firstname', $firstline) && !in_array('lastname', $firstline) && !in_array('email', $firstline) && !in_array('participant_id', $firstline)) {
$recordcount = count($tokenlistarray);
break;
}
} else {
// After looking at the first line, we now import the actual values
$line = convertCSVRowToArray($buffer, $separator, '"');
if (count($firstline) != count($line)) {
$invalidformatlist[] = $recordcount;
continue;
}
$writearray = array_combine($firstline, $line);
//kick out ignored columns
foreach ($ignoredcolumns as $column) {
unset($writearray[$column]);
}
// Add aFilterDuplicateFields not in CSV to writearray : quick fix
foreach ($aFilterDuplicateFields as $sFilterDuplicateField) {
if (!in_array($sFilterDuplicateField, $firstline)) {
$writearray[$sFilterDuplicateField] = "";
}
}
$invalidemail = false;
$dupfound = false;
$thisduplicate = 0;
//Check for duplicate participants
$aData = array('firstname' => $writearray['firstname'], 'lastname' => $writearray['lastname'], 'email' => $writearray['email'], 'owner_uid' => Yii::app()->session['loginID']);
//HACK - converting into SQL instead of doing an array search
if (in_array('participant_id', $firstline)) {
$dupreason = "participant_id";
$aData = "participant_id = " . Yii::app()->db->quoteValue($writearray['participant_id']);
} else {
$dupreason = "nameemail";
$aData = "firstname = " . Yii::app()->db->quoteValue($writearray['firstname']) . " AND lastname = " . Yii::app()->db->quoteValue($writearray['lastname']) . " AND email = " . Yii::app()->db->quoteValue($writearray['email']) . " AND owner_uid = '" . Yii::app()->session['loginID'] . "'";
}
//End of HACK
$aData = Participant::model()->checkforDuplicate($aData, "participant_id");
if ($aData !== false) {
$thisduplicate = 1;
$dupcount++;
if ($overwrite == "true") {
//Although this person already exists, we want to update the mapped attribute values
if (!empty($mappedarray)) {
//The mapped array contains the attributes we are
//saving in this import
foreach ($mappedarray as $attid => $attname) {
if (!empty($attname)) {
$bData = array('participant_id' => $aData, 'attribute_id' => $attid, 'value' => $writearray[strtolower($attname)]);
ParticipantAttribute::model()->updateParticipantAttributeValue($bData);
} else {
//If the value is empty, don't write the value
}
}
$overwritten++;
}
}
}
if ($thisduplicate == 1) {
$dupfound = true;
$duplicatelist[] = $writearray['firstname'] . " " . $writearray['lastname'] . " (" . $writearray['email'] . ")";
}
//Checking the email address is in a valid format
$invalidemail = false;
$writearray['email'] = trim($writearray['email']);
if ($writearray['email'] != '') {
$aEmailAddresses = explode(';', $writearray['email']);
// Ignore additional email addresses
$sEmailaddress = $aEmailAddresses[0];
if (!validateEmailAddress($sEmailaddress)) {
$invalidemail = true;
$invalidemaillist[] = $line[0] . " " . $line[1] . " (" . $line[2] . ")";
}
}
if (!$dupfound && !$invalidemail) {
//If it isn't a duplicate value or an invalid email, process the entry as a new participant
//First, process the known fields
if (!isset($writearray['participant_id']) || $writearray['participant_id'] == "") {
$uuid = $this->gen_uuid();
//Generate a UUID for the new participant
$writearray['participant_id'] = $uuid;
}
if (isset($writearray['emailstatus']) && trim($writearray['emailstatus'] == '')) {
unset($writearray['emailstatus']);
}
if (!isset($writearray['language']) || $writearray['language'] == "") {
$writearray['language'] = "en";
}
if (!isset($writearray['blacklisted']) || $writearray['blacklisted'] == "") {
$writearray['blacklisted'] = "N";
}
$writearray['owner_uid'] = Yii::app()->session['loginID'];
if (isset($writearray['validfrom']) && trim($writearray['validfrom'] == '')) {
unset($writearray['validfrom']);
}
if (isset($writearray['validuntil']) && trim($writearray['validuntil'] == '')) {
unset($writearray['validuntil']);
}
$dontimport = false;
if ($filterblankemails == "accept" && $writearray['email'] == "" || $writearray['firstname'] == "" || $writearray['lastname'] == "") {
//The mandatory fields of email, firstname and lastname
//must be filled, but one or more are empty
$mandatory++;
$dontimport = true;
} else {
foreach ($writearray as $key => $value) {
if (!empty($mappedarray)) {
//The mapped array contains the attributes we are
//saving in this import
if (in_array($key, $allowedfieldnames)) {
foreach ($mappedarray as $attid => $attname) {
if (strtolower($attname) == $key) {
if (!empty($value)) {
$aData = array('participant_id' => $writearray['participant_id'], 'attribute_id' => $attid, 'value' => $value);
ParticipantAttributeName::model()->saveParticipantAttributeValue($aData);
} else {
//If the value is empty, don't write the value
}
}
}
}
}
}
}
//If any of the mandatory fields are blank, then don't import this user
if (!$dontimport) {
Participant::model()->insertParticipantCSV($writearray);
$imported++;
}
}
$mincriteria++;
}
$recordcount++;
}
unlink($sFilePath);
$aData = array();
$aData['clang'] = $clang;
$aData['recordcount'] = $recordcount - 1;
$aData['duplicatelist'] = $duplicatelist;
$aData['mincriteria'] = $mincriteria;
$aData['imported'] = $imported;
$aData['errorinupload'] = $errorinupload;
$aData['invalidemaillist'] = $invalidemaillist;
$aData['mandatory'] = $mandatory;
$aData['invalidattribute'] = $invalidattribute;
$aData['invalidparticipantid'] = $invalidparticipantid;
$aData['overwritten'] = $overwritten;
$aData['dupreason'] = $dupreason;
$aData['aGlobalErrors'] = $aGlobalErrors;
$this->getController()->renderPartial('/admin/participants/uploadSummary_view', $aData);
}
/**
* This function imports the old CSV data from 1.50 to 1.87 or older. Starting with 1.90 (DBVersion 143) there is an XML format instead
*
* @param array $sFullFilepath
* @returns array Information of imported questions/answers/etc.
*/
function CSVImportSurvey($sFullFilepath,$iDesiredSurveyId=NULL)
{
global $dbprefix, $connect, $timeadjust, $clang;
$handle = fopen($sFullFilepath, "r");
while (!feof($handle))
{
$buffer = fgets($handle);
$bigarray[] = $buffer;
}
fclose($handle);
$aIgnoredAnswers=array();
$aSQIDReplacements=array();
$aLIDReplacements=array();
$aGIDReplacements=array();
$substitutions=array();
$aQuotaReplacements=array();
$importresults['error']=false;
$importresults['importwarnings']=array();
$importresults['question_attributes']=0;
if (isset($bigarray[0])) $bigarray[0]=removeBOM($bigarray[0]);
// Now we try to determine the dataformat of the survey file.
$importversion=0;
if (isset($bigarray[1]) && isset($bigarray[4])&& (substr($bigarray[1], 0, 22) == "# SURVEYOR SURVEY DUMP"))
{
$importversion = 100; // Version 0.99 or 1.0 file
}
elseif
(substr($bigarray[0], 0, 24) == "# LimeSurvey Survey Dump" || substr($bigarray[0], 0, 25) == "# PHPSurveyor Survey Dump")
{ // Seems to be a >1.0 version file - these files carry the version information to read in line two
$importversion=substr($bigarray[1], 12, 3);
}
else // unknown file - show error message
{
$importresults['error'] = $clang->gT("This file is not a LimeSurvey survey file. Import failed.")."\n";
return $importresults;
}
if ((int)$importversion<112)
{
$importresults['error'] = $clang->gT("This file is too old. Only files from LimeSurvey version 1.50 (DBVersion 112) and newer are supported.");
return $importresults;
}
// okay.. now lets drop the first 9 lines and get to the data
// This works for all versions
for ($i=0; $i<9; $i++)
{
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//SURVEYS
if (array_search("# GROUPS TABLE\n", $bigarray))
{
$stoppoint = array_search("# GROUPS TABLE\n", $bigarray);
}
elseif (array_search("# GROUPS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# GROUPS TABLE\r\n", $bigarray);
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2) {$surveyarray[] = $bigarray[$i];}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//GROUPS
if (array_search("# QUESTIONS TABLE\n", $bigarray))
{
$stoppoint = array_search("# QUESTIONS TABLE\n", $bigarray);
}
elseif (array_search("# QUESTIONS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# QUESTIONS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2) {$grouparray[] = $bigarray[$i];}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUESTIONS
if (array_search("# ANSWERS TABLE\n", $bigarray))
{
$stoppoint = array_search("# ANSWERS TABLE\n", $bigarray);
}
elseif (array_search("# ANSWERS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# ANSWERS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2)
{
$questionarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//ANSWERS
if (array_search("# CONDITIONS TABLE\n", $bigarray))
{
$stoppoint = array_search("# CONDITIONS TABLE\n", $bigarray);
}
elseif (array_search("# CONDITIONS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# CONDITIONS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2)
{
$answerarray[] = str_replace("`default`", "`default_value`", $bigarray[$i]);
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//CONDITIONS
if (array_search("# LABELSETS TABLE\n", $bigarray))
{
$stoppoint = array_search("# LABELSETS TABLE\n", $bigarray);
}
elseif (array_search("# LABELSETS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# LABELSETS TABLE\r\n", $bigarray);
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2) {$conditionsarray[] = $bigarray[$i];}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELSETS
if (array_search("# LABELS TABLE\n", $bigarray))
{
$stoppoint = array_search("# LABELS TABLE\n", $bigarray);
}
elseif (array_search("# LABELS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# LABELS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2) {$labelsetsarray[] = $bigarray[$i];}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELS
if (array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray))
{
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray);
}
elseif (array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2) {$labelsarray[] = $bigarray[$i];}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//Question attributes
if (array_search("# ASSESSMENTS TABLE\n", $bigarray))
{
$stoppoint = array_search("# ASSESSMENTS TABLE\n", $bigarray);
}
elseif (array_search("# ASSESSMENTS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# ASSESSMENTS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
if ($i<$stoppoint-2) {$question_attributesarray[] = $bigarray[$i];}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//ASSESSMENTS
if (array_search("# SURVEYS_LANGUAGESETTINGS TABLE\n", $bigarray))
{
$stoppoint = array_search("# SURVEYS_LANGUAGESETTINGS TABLE\n", $bigarray);
}
elseif (array_search("# SURVEYS_LANGUAGESETTINGS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# SURVEYS_LANGUAGESETTINGS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
if ($i<$stoppoint-2)
{
$assessmentsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LANGAUGE SETTINGS
if (array_search("# QUOTA TABLE\n", $bigarray))
{
$stoppoint = array_search("# QUOTA TABLE\n", $bigarray);
}
elseif (array_search("# QUOTA TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# QUOTA TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
//$bigarray[$i]= trim($bigarray[$i]);
if (isset($bigarray[$i]) && (trim($bigarray[$i])!=''))
{
if (strpos($bigarray[$i],"#")===0)
{
unset($bigarray[$i]);
unset($bigarray[$i+1]);
unset($bigarray[$i+2]);
break ;
}
else
{
$surveylsarray[] = $bigarray[$i];
}
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUOTA
if (array_search("# QUOTA_MEMBERS TABLE\n", $bigarray))
{
$stoppoint = array_search("# QUOTA_MEMBERS TABLE\n", $bigarray);
}
elseif (array_search("# QUOTA_MEMBERS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# QUOTA_MEMBERS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
if ($i<$stoppoint-2)
{
$quotaarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUOTA MEMBERS
if (array_search("# QUOTA_LANGUAGESETTINGS TABLE\n", $bigarray))
{
$stoppoint = array_search("# QUOTA_LANGUAGESETTINGS TABLE\n", $bigarray);
}
elseif (array_search("# QUOTA_LANGUAGESETTINGS TABLE\r\n", $bigarray))
{
$stoppoint = array_search("# QUOTA_LANGUAGESETTINGS TABLE\r\n", $bigarray);
}
else
{
$stoppoint = count($bigarray)-1;
}
for ($i=0; $i<=$stoppoint+1; $i++)
{
// if ($i<$stoppoint-2 || $i==count($bigarray)-1)
if ($i<$stoppoint-2)
{
$quotamembersarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//Whatever is the last table - currently
//QUOTA LANGUAGE SETTINGS
$stoppoint = count($bigarray)-1;
for ($i=0; $i<$stoppoint-1; $i++)
{
if ($i<=$stoppoint) {$quotalsarray[] = $bigarray[$i];}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
if (isset($surveyarray)) {$importresults['surveys'] = count($surveyarray);} else {$importresults['surveys'] = 0;}
if (isset($surveylsarray)) {$importresults['languages'] = count($surveylsarray)-1;} else {$importresults['languages'] = 1;}
if (isset($grouparray)) {$importresults['groups'] = count($grouparray)-1;} else {$importresults['groups'] = 0;}
if (isset($questionarray)) {$importresults['questions'] = count($questionarray);} else {$importresults['questions']=0;}
if (isset($answerarray)) {$importresults['answers'] = count($answerarray);} else {$importresults['answers']=0;}
if (isset($conditionsarray)) {$importresults['conditions'] = count($conditionsarray);} else {$importresults['conditions']=0;}
if (isset($labelsetsarray)) {$importresults['labelsets'] = count($labelsetsarray);} else {$importresults['labelsets']=0;}
if (isset($assessmentsarray)) {$importresults['assessments']=count($assessmentsarray);} else {$importresults['assessments']=0;}
if (isset($quotaarray)) {$importresults['quota']=count($quotaarray);} else {$importresults['quota']=0;}
if (isset($quotamembersarray)) {$importresults['quotamembers']=count($quotamembersarray);} else {$importresults['quotamembers']=0;}
if (isset($quotalsarray)) {$importresults['quotals']=count($quotalsarray);} else {$importresults['quotals']=0;}
// CREATE SURVEY
if ($importresults['surveys']>0){$importresults['surveys']--;};
if ($importresults['answers']>0){$importresults['answers']=($importresults['answers']-1)/$importresults['languages'];};
if ($importresults['groups']>0){$countgroups=($importresults['groups']-1)/$importresults['languages'];};
if ($importresults['questions']>0){$importresults['questions']=($importresults['questions']-1)/$importresults['languages'];};
if ($importresults['assessments']>0){$importresults['assessments']--;};
if ($importresults['conditions']>0){$importresults['conditions']--;};
if ($importresults['labelsets']>0){$importresults['labelsets']--;};
if ($importresults['quota']>0){$importresults['quota']--;};
$sfieldorders =convertCSVRowToArray($surveyarray[0],',','"');
$sfieldcontents=convertCSVRowToArray($surveyarray[1],',','"');
$surveyrowdata=array_combine($sfieldorders,$sfieldcontents);
$oldsid=$surveyrowdata["sid"];
if($iDesiredSurveyId!=NULL)
$oldsid = $iDesiredSurveyId;
if (!$oldsid)
{
if ($importingfrom == "http")
{
$importsurvey .= "<br /><div class='warningheader'>".$clang->gT("Error")."</div><br />\n";
$importsurvey .= $clang->gT("Import of this survey file failed")."<br />\n";
$importsurvey .= $clang->gT("File does not contain LimeSurvey data in the correct format.")."<br /><br />\n"; //Couldn't find the SID - cannot continue
$importsurvey .= "<input type='submit' value='".$clang->gT("Main Admin Screen")."' onclick=\"window.open('$scriptname', '_top')\" />\n";
$importsurvey .= "</div>\n";
unlink($sFullFilepath); //Delete the uploaded file
return;
}
else
{
echo $clang->gT("Import of this survey file failed")."\n".$clang->gT("File does not contain LimeSurvey data in the correct format.")."\n";
return;
}
}
$newsid = GetNewSurveyID($oldsid);
$insert=$surveyarray[0];
$sfieldorders =convertCSVRowToArray($surveyarray[0],',','"');
$sfieldcontents=convertCSVRowToArray($surveyarray[1],',','"');
$surveyrowdata=array_combine($sfieldorders,$sfieldcontents);
// Set new owner ID
$surveyrowdata['owner_id']=$_SESSION['loginID'];
// Set new survey ID
$surveyrowdata['sid']=$newsid;
$surveyrowdata['active']='N';
if (validate_templatedir($surveyrowdata['template'])!==$surveyrowdata['template']) $importresults['importwarnings'][] = sprintf($clang->gT('Template %s not found, please review when activating.'),$surveyrowdata['template']);
if (isset($surveyrowdata['datecreated'])) {$surveyrowdata['datecreated']=$connect->BindTimeStamp($surveyrowdata['datecreated']);}
unset($surveyrowdata['expires']);
unset($surveyrowdata['attribute1']);
unset($surveyrowdata['attribute2']);
unset($surveyrowdata['usestartdate']);
unset($surveyrowdata['notification']);
unset($surveyrowdata['useexpiry']);
unset($surveyrowdata['url']);
unset($surveyrowdata['lastpage']);
if (isset($surveyrowdata['private'])){
$surveyrowdata['anonymized']=$surveyrowdata['private'];
unset($surveyrowdata['private']);
}
if (isset($surveyrowdata['startdate'])) {unset($surveyrowdata['startdate']);}
$surveyrowdata['bounce_email']=$surveyrowdata['adminemail'];
if (!isset($surveyrowdata['datecreated']) || $surveyrowdata['datecreated']=='' || $surveyrowdata['datecreated']=='null') {$surveyrowdata['datecreated']=$connect->BindTimeStamp(date_shift(date("Y-m-d H:i:s"), "Y-m-d", $timeadjust));}
$values=array_values($surveyrowdata);
$values=array_map(array(&$connect, "qstr"),$values); // quote everything accordingly
$insert = "INSERT INTO {$dbprefix}surveys (".implode(',',array_keys($surveyrowdata)).") VALUES (".implode(',',$values).")"; //handle db prefix
$iresult = $connect->Execute($insert) or safe_die("<br />".$clang->gT("Import of this survey file failed")."<br />\n[$insert]<br />{$surveyarray[0]}<br /><br />\n" . $connect->ErrorMsg());
// Now import the survey language settings
$fieldorders=convertCSVRowToArray($surveylsarray[0],',','"');
unset($surveylsarray[0]);
foreach ($surveylsarray as $slsrow) {
$fieldcontents=convertCSVRowToArray($slsrow,',','"');
$surveylsrowdata=array_combine($fieldorders,$fieldcontents);
// convert back the '\'.'n' char from the CSV file to true return char "\n"
$surveylsrowdata=array_map('convertCsvreturn2return', $surveylsrowdata);
// Convert the \n return char from welcometext to <br />
// translate internal links
$surveylsrowdata['surveyls_title']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_title']);
$surveylsrowdata['surveyls_description']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_description']);
$surveylsrowdata['surveyls_welcometext']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_welcometext']);
$surveylsrowdata['surveyls_urldescription']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_urldescription']);
$surveylsrowdata['surveyls_email_invite']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_email_invite']);
$surveylsrowdata['surveyls_email_remind']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_email_remind']);
$surveylsrowdata['surveyls_email_register']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_email_register']);
$surveylsrowdata['surveyls_email_confirm']=translink('survey', $oldsid, $newsid, $surveylsrowdata['surveyls_email_confirm']);
unset($surveylsrowdata['lastpage']);
$surveylsrowdata['surveyls_survey_id']=$newsid;
$newvalues=array_values($surveylsrowdata);
$newvalues=array_map(array(&$connect, "qstr"),$newvalues); // quote everything accordingly
$lsainsert = "INSERT INTO {$dbprefix}surveys_languagesettings (".implode(',',array_keys($surveylsrowdata)).") VALUES (".implode(',',$newvalues).")"; //handle db prefix
$lsiresult=$connect->Execute($lsainsert) or safe_die("<br />".$clang->gT("Import of this survey file failed")."<br />\n[$lsainsert]<br />\n" . $connect->ErrorMsg() );
}
// The survey languagesettings are imported now
$aLanguagesSupported = array(); // this array will keep all the languages supported for the survey
$sBaseLanguage = GetBaseLanguageFromSurveyID($newsid);
$aLanguagesSupported[]=$sBaseLanguage; // adds the base language to the list of supported languages
$aLanguagesSupported=array_merge($aLanguagesSupported,GetAdditionalLanguagesFromSurveyID($newsid));
// DO SURVEY_RIGHTS
GiveAllSurveyPermissions($_SESSION['loginID'],$newsid);
$importresults['deniedcountls'] =0;
$qtypes = getqtypelist("" ,"array");
$results['labels']=0;
$results['labelsets']=0;
$results['answers']=0;
$results['subquestions']=0;
//Do label sets
if (isset($labelsetsarray) && $labelsetsarray)
{
$csarray=buildLabelSetCheckSumArray(); // build checksums over all existing labelsets
$count=0;
foreach ($labelsetsarray as $lsa) {
$fieldorders =convertCSVRowToArray($labelsetsarray[0],',','"');
$fieldcontents=convertCSVRowToArray($lsa,',','"');
if ($count==0) {$count++; continue;}
$labelsetrowdata=array_combine($fieldorders,$fieldcontents);
// Save old labelid
$oldlid=$labelsetrowdata['lid'];
unset($labelsetrowdata['lid']);
$newvalues=array_values($labelsetrowdata);
$newvalues=array_map(array(&$connect, "qstr"),$newvalues); // quote everything accordingly
$lsainsert = "INSERT INTO {$dbprefix}labelsets (".implode(',',array_keys($labelsetrowdata)).") VALUES (".implode(',',$newvalues).")"; //handle db prefix
$lsiresult=$connect->Execute($lsainsert);
$results['labelsets']++;
// Get the new insert id for the labels inside this labelset
$newlid=$connect->Insert_ID("{$dbprefix}labelsets",'lid');
if ($labelsarray) {
$count=0;
foreach ($labelsarray as $la) {
$lfieldorders =convertCSVRowToArray($labelsarray[0],',','"');
$lfieldcontents=convertCSVRowToArray($la,',','"');
if ($count==0) {$count++; continue;}
// Combine into one array with keys and values since its easier to handle
$labelrowdata=array_combine($lfieldorders,$lfieldcontents);
$labellid=$labelrowdata['lid'];
if ($importversion<=132)
{
$labelrowdata["assessment_value"]=(int)$labelrowdata["code"];
}
if ($labellid == $oldlid) {
$labelrowdata['lid']=$newlid;
// translate internal links
$labelrowdata['title']=translink('label', $oldlid, $newlid, $labelrowdata['title']);
$newvalues=array_values($labelrowdata);
$newvalues=array_map(array(&$connect, "qstr"),$newvalues); // quote everything accordingly
$lainsert = "INSERT INTO {$dbprefix}labels (".implode(',',array_keys($labelrowdata)).") VALUES (".implode(',',$newvalues).")"; //handle db prefix
$liresult=$connect->Execute($lainsert);
if ($liresult!==false) $results['labels']++;
}
}
}
//CHECK FOR DUPLICATE LABELSETS
$thisset="";
$query2 = "SELECT code, title, sortorder, language, assessment_value
FROM {$dbprefix}labels
WHERE lid=".$newlid."
ORDER BY language, sortorder, code";
$result2 = db_execute_num($query2) or safe_die("Died querying labelset $lid<br />$query2<br />".$connect->ErrorMsg());
while($row2=$result2->FetchRow())
{
$thisset .= implode('.', $row2);
} // while
$newcs=dechex(crc32($thisset)*1);
unset($lsmatch);
if (isset($csarray))
{
foreach($csarray as $key=>$val)
{
if ($val == $newcs)
{
$lsmatch=$key;
}
}
}
if (isset($lsmatch) || ($_SESSION['USER_RIGHT_MANAGE_LABEL'] != 1))
{
//There is a matching labelset or the user is not allowed to edit labels -
// So, we will delete this one and refer to the matched one.
$query = "DELETE FROM {$dbprefix}labels WHERE lid=$newlid";
$result=$connect->Execute($query) or safe_die("Couldn't delete labels<br />$query<br />".$connect->ErrorMsg());
$results['labels']=$results['labels']-$connect->Affected_Rows();
$query = "DELETE FROM {$dbprefix}labelsets WHERE lid=$newlid";
$result=$connect->Execute($query) or safe_die("Couldn't delete labelset<br />$query<br />".$connect->ErrorMsg());
$results['labelsets']=$results['labelsets']-$connect->Affected_Rows();
$newlid=$lsmatch;
}
else
{
//There isn't a matching labelset, add this checksum to the $csarray array
$csarray[$newlid]=$newcs;
}
//END CHECK FOR DUPLICATES
$aLIDReplacements[$oldlid]=$newlid;
}
}
// Import groups
if (isset($grouparray) && $grouparray)
{
// do GROUPS
$gafieldorders=convertCSVRowToArray($grouparray[0],',','"');
unset($grouparray[0]);
foreach ($grouparray as $ga)
{
$gacfieldcontents=convertCSVRowToArray($ga,',','"');
$grouprowdata=array_combine($gafieldorders,$gacfieldcontents);
//Now an additional integrity check if there are any groups not belonging into this survey
if ($grouprowdata['sid'] != $oldsid)
{
$results['fatalerror'] = $clang->gT("A group in the CSV/SQL file is not part of the same survey. The import of the survey was stopped.")."<br />\n";
return $results;
}
$grouprowdata['sid']=$newsid;
// remember group id
$oldgid=$grouprowdata['gid'];
//update/remove the old group id
if (isset($aGIDReplacements[$oldgid]))
$grouprowdata['gid'] = $aGIDReplacements[$oldgid];
else
unset($grouprowdata['gid']);
// Everything set - now insert it
$grouprowdata=array_map('convertCsvreturn2return', $grouprowdata);
// translate internal links
$grouprowdata['group_name']=translink('survey', $oldsid, $newsid, $grouprowdata['group_name']);
$grouprowdata['description']=translink('survey', $oldsid, $newsid, $grouprowdata['description']);
if (isset($grouprowdata['gid'])) db_switchIDInsert('groups',true);
$tablename=$dbprefix.'groups';
$ginsert = $connect->GetinsertSQL($tablename,$grouprowdata);
$gres = $connect->Execute($ginsert) or safe_die($clang->gT('Error').": Failed to insert group<br />\n$ginsert<br />\n".$connect->ErrorMsg());
if (isset($grouprowdata['gid'])) db_switchIDInsert('groups',false);
//GET NEW GID
if (!isset($grouprowdata['gid'])) {$aGIDReplacements[$oldgid]=$connect->Insert_ID("{$dbprefix}groups","gid");}
}
// Fix sortorder of the groups - if users removed groups manually from the csv file there would be gaps
fixSortOrderGroups($newsid);
}
// GROUPS is DONE
// Import questions
if (isset($questionarray) && $questionarray)
{
$qafieldorders=convertCSVRowToArray($questionarray[0],',','"');
unset($questionarray[0]);
foreach ($questionarray as $qa)
{
$qacfieldcontents=convertCSVRowToArray($qa,',','"');
$questionrowdata=array_combine($qafieldorders,$qacfieldcontents);
$questionrowdata=array_map('convertCsvreturn2return', $questionrowdata);
$questionrowdata["type"]=strtoupper($questionrowdata["type"]);
// Skip not supported languages
if (!in_array($questionrowdata['language'],$aLanguagesSupported))
continue;
// replace the sid
$questionrowdata["sid"] = $newsid;
// Skip if gid is invalid
if (!isset($aGIDReplacements[$questionrowdata['gid']])) continue;
$questionrowdata["gid"] = $aGIDReplacements[$questionrowdata['gid']];
if (isset($aQIDReplacements[$questionrowdata['qid']]))
{
$questionrowdata['qid']=$aQIDReplacements[$questionrowdata['qid']];
}
else
{
$oldqid=$questionrowdata['qid'];
unset($questionrowdata['qid']);
}
unset($oldlid1); unset($oldlid2);
if ((isset($questionrowdata['lid']) && $questionrowdata['lid']>0))
{
$oldlid1=$questionrowdata['lid'];
}
if ((isset($questionrowdata['lid1']) && $questionrowdata['lid1']>0))
{
$oldlid2=$questionrowdata['lid1'];
}
unset($questionrowdata['lid']);
unset($questionrowdata['lid1']);
if ($questionrowdata['type']=='W')
{
$questionrowdata['type']='!';
}
elseif ($questionrowdata['type']=='Z')
{
$questionrowdata['type']='L';
$aIgnoredAnswers[]=$oldqid;
}
if (!isset($questionrowdata["question_order"]) || $questionrowdata["question_order"]=='') {$questionrowdata["question_order"]=0;}
// translate internal links
$questionrowdata['title']=translink('survey', $oldsid, $newsid, $questionrowdata['title']);
$questionrowdata['question']=translink('survey', $oldsid, $newsid, $questionrowdata['question']);
$questionrowdata['help']=translink('survey', $oldsid, $newsid, $questionrowdata['help']);
if (isset($questionrowdata['qid'])) {
db_switchIDInsert('questions',true);
}
$tablename=$dbprefix.'questions';
$qinsert = $connect->GetInsertSQL($tablename,$questionrowdata);
$qres = $connect->Execute($qinsert) or safe_die ($clang->gT("Error").": Failed to insert question<br />\n$qinsert<br />\n".$connect->ErrorMsg());
if (isset($questionrowdata['qid'])) {
db_switchIDInsert('questions',false);
$saveqid=$questionrowdata['qid'];
}
else
{
$aQIDReplacements[$oldqid]=$connect->Insert_ID("{$dbprefix}questions",'qid');
$saveqid=$aQIDReplacements[$oldqid];
}
// Now we will fix up old label sets where they are used as answers
if (((isset($oldlid1) && isset($aLIDReplacements[$oldlid1])) || (isset($oldlid2) && isset($aLIDReplacements[$oldlid2]))) && ($qtypes[$questionrowdata['type']]['answerscales']>0 || $qtypes[$questionrowdata['type']]['subquestions']>1))
{
$query="select * from ".db_table_name('labels')." where lid={$aLIDReplacements[$oldlid1]} and language='{$questionrowdata['language']}'";
$oldlabelsresult=db_execute_assoc($query);
while($labelrow=$oldlabelsresult->FetchRow())
{
if (in_array($labelrow['language'],$aLanguagesSupported))
{
if ($qtypes[$questionrowdata['type']]['subquestions']<2)
{
$qinsert = "insert INTO ".db_table_name('answers')." (qid,code,answer,sortorder,language,assessment_value)
VALUES ({$aQIDReplacements[$oldqid]},".db_quoteall($labelrow['code']).",".db_quoteall($labelrow['title']).",".db_quoteall($labelrow['sortorder']).",".db_quoteall($labelrow['language']).",".db_quoteall($labelrow['assessment_value']).")";
$qres = $connect->Execute($qinsert) or safe_die ($clang->gT("Error").": Failed to insert answer (lid1) <br />\n$qinsert<br />\n".$connect->ErrorMsg());
}
else
{
if (isset($aSQIDReplacements[$labelrow['code'].'_'.$saveqid])){
$fieldname='qid,';
$data=$aSQIDReplacements[$labelrow['code'].'_'.$saveqid].',';
}
else{
$fieldname='' ;
$data='';
}
$qinsert = "insert INTO ".db_table_name('questions')." ($fieldname parent_qid,title,question,question_order,language,scale_id,type, sid, gid)
VALUES ($data{$aQIDReplacements[$oldqid]},".db_quoteall($labelrow['code']).",".db_quoteall($labelrow['title']).",".db_quoteall($labelrow['sortorder']).",".db_quoteall($labelrow['language']).",1,'{$questionrowdata['type']}',{$questionrowdata['sid']},{$questionrowdata['gid']})";
$qres = $connect->Execute($qinsert) or safe_die ($clang->gT("Error").": Failed to insert question <br />\n$qinsert<br />\n".$connect->ErrorMsg());
if ($fieldname=='')
{
$aSQIDReplacements[$labelrow['code'].'_'.$saveqid]=$connect->Insert_ID("{$dbprefix}questions","qid");
}
}
}
}
if (isset($oldlid2) && $qtypes[$questionrowdata['type']]['answerscales']>1)
{
$query="select * from ".db_table_name('labels')." where lid={$aLIDReplacements[$oldlid2]} and language='{$questionrowdata['language']}'";
$oldlabelsresult=db_execute_assoc($query);
while($labelrow=$oldlabelsresult->FetchRow())
{
$qinsert = "insert INTO ".db_table_name('answers')." (qid,code,answer,sortorder,language,assessment_value,scale_id)
VALUES ({$aQIDReplacements[$oldqid]},".db_quoteall($labelrow['code']).",".db_quoteall($labelrow['title']).",".db_quoteall($labelrow['sortorder']).",".db_quoteall($labelrow['language']).",".db_quoteall($labelrow['assessment_value']).",1)";
$qres = $connect->Execute($qinsert) or safe_die ($clang->gT("Error").": Failed to insert answer (lid2)<br />\n$qinsert<br />\n".$connect->ErrorMsg());
}
}
}
}
}
//Do answers
if (isset($answerarray) && $answerarray)
{
$answerfieldnames = convertCSVRowToArray($answerarray[0],',','"');
unset($answerarray[0]);
foreach ($answerarray as $aa)
{
$answerfieldcontents = convertCSVRowToArray($aa,',','"');
$answerrowdata = array_combine($answerfieldnames,$answerfieldcontents);
if (in_array($answerrowdata['qid'],$aIgnoredAnswers))
{
// Due to a bug in previous LS versions there may be orphaned answers with question type Z (which is now L)
// this way they are ignored
continue;
}
if ($answerrowdata===false)
{
$importquestion.='<br />'.$clang->gT("Faulty line in import - fields and data don't match").":".implode(',',$answerfieldcontents);
}
// Skip not supported languages
if (!in_array($answerrowdata['language'],$aLanguagesSupported))
continue;
// replace the qid for the new one (if there is no new qid in the $aQIDReplacements array it mean that this answer is orphan -> error, skip this record)
if (isset($aQIDReplacements[$answerrowdata["qid"]]))
$answerrowdata["qid"] = $aQIDReplacements[$answerrowdata["qid"]];
else
continue; // a problem with this answer record -> don't consider
if ($importversion<=132)
{
$answerrowdata["assessment_value"]=(int)$answerrowdata["code"];
}
// Convert default values for single select questions
$questiontemp=$connect->GetRow('select type,gid from '.db_table_name('questions').' where qid='.$answerrowdata["qid"]);
$oldquestion['newtype']=$questiontemp['type'];
$oldquestion['gid']=$questiontemp['gid'];
if ($answerrowdata['default_value']=='Y' && ($oldquestion['newtype']=='L' || $oldquestion['newtype']=='O' || $oldquestion['newtype']=='!'))
{
$insertdata=array();
$insertdata['qid']=$newqid;
$insertdata['language']=$answerrowdata['language'];
$insertdata['defaultvalue']=$answerrowdata['answer'];
$query=$connect->GetInsertSQL($dbprefix.'defaultvalues',$insertdata);
$qres = $connect->Execute($query) or safe_die ("Error: Failed to insert defaultvalue <br />{$query}<br />\n".$connect->ErrorMsg());
}
// translate internal links
$answerrowdata['answer']=translink('survey', $oldsid, $newsid, $answerrowdata['answer']);
// Everything set - now insert it
$answerrowdata = array_map('convertCsvreturn2return', $answerrowdata);
if ($qtypes[$oldquestion['newtype']]['subquestions']>0) //hmmm.. this is really a subquestion
{
$questionrowdata=array();
if (isset($aSQIDReplacements[$answerrowdata['code'].$answerrowdata['qid']])){
$questionrowdata['qid']=$aSQIDReplacements[$answerrowdata['code'].$answerrowdata['qid']];
}
$questionrowdata['parent_qid']=$answerrowdata['qid'];;
$questionrowdata['sid']=$newsid;
$questionrowdata['gid']=$oldquestion['gid'];
$questionrowdata['title']=$answerrowdata['code'];
$questionrowdata['question']=$answerrowdata['answer'];
$questionrowdata['question_order']=$answerrowdata['sortorder'];
$questionrowdata['language']=$answerrowdata['language'];
$questionrowdata['type']=$oldquestion['newtype'];
$tablename=$dbprefix.'questions';
$query=$connect->GetInsertSQL($tablename,$questionrowdata);
if (isset($questionrowdata['qid'])) db_switchIDInsert('questions',true);
$qres = $connect->Execute($query) or safe_die ("Error: Failed to insert subquestion <br />{$query}<br />".$connect->ErrorMsg());
if (!isset($questionrowdata['qid']))
{
$aSQIDReplacements[$answerrowdata['code'].$answerrowdata['qid']]=$connect->Insert_ID("{$dbprefix}questions","qid");
}
else
{
db_switchIDInsert('questions',false);
}
$results['subquestions']++;
// also convert default values subquestions for multiple choice
if ($answerrowdata['default_value']=='Y' && ($oldquestion['newtype']=='M' || $oldquestion['newtype']=='P'))
{
$insertdata=array();
$insertdata['qid']=$newqid;
$insertdata['sqid']=$aSQIDReplacements[$answerrowdata['code']];
$insertdata['language']=$answerrowdata['language'];
$insertdata['defaultvalue']='Y';
$tablename=$dbprefix.'defaultvalues';
$query=$connect->GetInsertSQL($tablename,$insertdata);
$qres = $connect->Execute($query) or safe_die ("Error: Failed to insert defaultvalue <br />{$query}<br />\n".$connect->ErrorMsg());
}
}
else // insert answers
{
unset($answerrowdata['default_value']);
$tablename=$dbprefix.'answers';
$query=$connect->GetInsertSQL($tablename,$answerrowdata);
$ares = $connect->Execute($query) or safe_die ("Error: Failed to insert answer<br />{$query}<br />\n".$connect->ErrorMsg());
$results['answers']++;
}
}
}
// get all group ids and fix questions inside each group
$gquery = "SELECT gid FROM {$dbprefix}groups where sid=$newsid group by gid ORDER BY gid"; //Get last question added (finds new qid)
$gres = db_execute_assoc($gquery);
while ($grow = $gres->FetchRow())
{
fixsortorderQuestions($grow['gid'], $newsid);
}
//We've built two arrays along the way - one containing the old SID, GID and QIDs - and their NEW equivalents
//and one containing the old 'extended fieldname' and its new equivalent. These are needed to import conditions and question_attributes.
if (isset($question_attributesarray) && $question_attributesarray) {//ONLY DO THIS IF THERE ARE QUESTION_ATTRIBUES
$fieldorders =convertCSVRowToArray($question_attributesarray[0],',','"');
unset($question_attributesarray[0]);
foreach ($question_attributesarray as $qar) {
$fieldcontents=convertCSVRowToArray($qar,',','"');
$qarowdata=array_combine($fieldorders,$fieldcontents);
$newqid="";
$qarowdata["qid"]=$aQIDReplacements[$qarowdata["qid"]];
unset($qarowdata["qaid"]);
$newvalues=array_values($qarowdata);
$newvalues=array_map(array(&$connect, "qstr"),$newvalues); // quote everything accordingly
$qainsert = "insert INTO {$dbprefix}question_attributes (".implode(',',array_keys($qarowdata)).") VALUES (".implode(',',$newvalues).")";
$result=$connect->Execute($qainsert); // no safe_die since some LimeSurvey version export duplicate question attributes - these are just ignored
if ($connect->Affected_Rows()>0) {$importresults['question_attributes']++;}
}
}
if (isset($assessmentsarray) && $assessmentsarray) {//ONLY DO THIS IF THERE ARE QUESTION_ATTRIBUTES
$fieldorders=convertCSVRowToArray($assessmentsarray[0],',','"');
unset($assessmentsarray[0]);
foreach ($assessmentsarray as $qar)
{
$fieldcontents=convertCSVRowToArray($qar,',','"');
$asrowdata=array_combine($fieldorders,$fieldcontents);
if (isset($asrowdata['link']))
{
if (trim($asrowdata['link'])!='') $asrowdata['message']=$asrowdata['message'].'<br /><a href="'.$asrowdata['link'].'">'.$asrowdata['link'].'</a>';
unset($asrowdata['link']);
}
if ($asrowdata["gid"]>0)
{
$asrowdata["gid"]=$aGIDReplacements[$asrowdata["gid"]];
}
$asrowdata["sid"]=$newsid;
unset($asrowdata["id"]);
$tablename=$dbprefix.'assessments';
$asinsert = $connect->GetInsertSQL($tablename,$asrowdata);
$result=$connect->Execute($asinsert) or safe_die ("Couldn't insert assessment<br />$asinsert<br />".$connect->ErrorMsg());
unset($newgid);
}
}
if (isset($quotaarray) && $quotaarray) {//ONLY DO THIS IF THERE ARE QUOTAS
$fieldorders=convertCSVRowToArray($quotaarray[0],',','"');
unset($quotaarray[0]);
foreach ($quotaarray as $qar)
{
$fieldcontents=convertCSVRowToArray($qar,',','"');
$asrowdata=array_combine($fieldorders,$fieldcontents);
$oldsid=$asrowdata["sid"];
foreach ($substitutions as $subs) {
if ($oldsid==$subs[0]) {$newsid=$subs[3];}
}
$asrowdata["sid"]=$newsid;
$oldid = $asrowdata["id"];
unset($asrowdata["id"]);
$quotadata[]=$asrowdata; //For use later if needed
$tablename=$dbprefix.'quota';
$asinsert = $connect->getInsertSQL($tablename,$asrowdata);
$result=$connect->Execute($asinsert) or safe_die ("Couldn't insert quota<br />$asinsert<br />".$connect->ErrorMsg());
$aQuotaReplacements[$oldid] = $connect->Insert_ID(db_table_name_nq('quota'),"id");
}
}
if (isset($quotamembersarray) && $quotamembersarray) {//ONLY DO THIS IF THERE ARE QUOTA MEMBERS
$count=0;
foreach ($quotamembersarray as $qar) {
$fieldorders =convertCSVRowToArray($quotamembersarray[0],',','"');
$fieldcontents=convertCSVRowToArray($qar,',','"');
if ($count==0) {$count++; continue;}
$asrowdata=array_combine($fieldorders,$fieldcontents);
$oldsid=$asrowdata["sid"];
$newqid="";
$newquotaid="";
$oldqid=$asrowdata['qid'];
$oldquotaid=$asrowdata['quota_id'];
foreach ($substitutions as $subs) {
if ($oldsid==$subs[0]) {$newsid=$subs[3];}
if ($oldqid==$subs[2]) {$newqid=$subs[5];}
}
$newquotaid=$aQuotaReplacements[$oldquotaid];
$asrowdata["sid"]=$newsid;
$asrowdata["qid"]=$newqid;
$asrowdata["quota_id"]=$newquotaid;
unset($asrowdata["id"]);
$tablename=$dbprefix.'quota_members';
$asinsert = $connect->getInsertSQL($tablename,$asrowdata);
$result=$connect->Execute($asinsert) or safe_die ("Couldn't insert quota<br />$asinsert<br />".$connect->ErrorMsg());
}
}
if (isset($quotalsarray) && $quotalsarray) {//ONLY DO THIS IF THERE ARE QUOTA LANGUAGE SETTINGS
$count=0;
foreach ($quotalsarray as $qar) {
$fieldorders =convertCSVRowToArray($quotalsarray[0],',','"');
$fieldcontents=convertCSVRowToArray($qar,',','"');
if ($count==0) {$count++; continue;}
$asrowdata=array_combine($fieldorders,$fieldcontents);
$newquotaid="";
$oldquotaid=$asrowdata['quotals_quota_id'];
$newquotaid=$aQuotaReplacements[$oldquotaid];
$asrowdata["quotals_quota_id"]=$newquotaid;
unset($asrowdata["quotals_id"]);
$tablename=$dbprefix.'quota_languagesettings';
$asinsert = $connect->getInsertSQL($tablename,$asrowdata);
$result=$connect->Execute($asinsert) or safe_die ("Couldn't insert quota<br />$asinsert<br />".$connect->ErrorMsg());
}
}
//if there are quotas, but no quotals, then we need to create default dummy for each quota (this handles exports from pre-language quota surveys)
if ($importresults['quota'] > 0 && (!isset($importresults['quotals']) || $importresults['quotals'] == 0)) {
$i=0;
$defaultsurveylanguage=isset($defaultsurveylanguage) ? $defaultsurveylanguage : "en";
foreach($aQuotaReplacements as $oldquotaid=>$newquotaid) {
$asrowdata=array("quotals_quota_id" => $newquotaid,
"quotals_language" => $defaultsurveylanguage,
"quotals_name" => $quotadata[$i]["name"],
"quotals_message" => $clang->gT("Sorry your responses have exceeded a quota on this survey."),
"quotals_url" => "",
"quotals_urldescrip" => "");
$i++;
}
$tablename=$dbprefix.'quota_languagesettings';
$asinsert = $connect->getInsertSQL($tablename,$asrowdata);
$result=$connect->Execute($asinsert) or safe_die ("Couldn't insert quota<br />$asinsert<br />".$connect->ErrorMsg());
$countquotals=$i;
}
// Do conditions
if (isset($conditionsarray) && $conditionsarray) {//ONLY DO THIS IF THERE ARE CONDITIONS!
$fieldorders =convertCSVRowToArray($conditionsarray[0],',','"');
unset($conditionsarray[0]);
// Exception for conditions based on attributes
$aQIDReplacements[0]=0;
foreach ($conditionsarray as $car) {
$fieldcontents=convertCSVRowToArray($car,',','"');
$conditionrowdata=array_combine($fieldorders,$fieldcontents);
unset($conditionrowdata["cid"]);
if (!isset($conditionrowdata["method"]) || trim($conditionrowdata["method"])=='')
{
$conditionrowdata["method"]='==';
}
if (!isset($conditionrowdata["scenario"]) || trim($conditionrowdata["scenario"])=='')
{
$conditionrowdata["scenario"]=1;
}
$oldcqid=$conditionrowdata["cqid"];
$oldgid=array_search($connect->GetOne('select gid from '.db_table_name('questions').' where qid='.$aQIDReplacements[$conditionrowdata["cqid"]]),$aGIDReplacements);
$conditionrowdata["qid"]=$aQIDReplacements[$conditionrowdata["qid"]];
$conditionrowdata["cqid"]=$aQIDReplacements[$conditionrowdata["cqid"]];
$oldcfieldname=$conditionrowdata["cfieldname"];
$conditionrowdata["cfieldname"]=str_replace($oldsid.'X'.$oldgid.'X'.$oldcqid,$newsid.'X'.$aGIDReplacements[$oldgid].'X'.$conditionrowdata["cqid"],$conditionrowdata["cfieldname"]);
$tablename=$dbprefix.'conditions';
$conditioninsert = $connect->getInsertSQL($tablename,$conditionrowdata);
$result=$connect->Execute($conditioninsert) or safe_die ("Couldn't insert condition<br />$conditioninsert<br />".$connect->ErrorMsg());
}
}
$importresults['importversion']=$importversion;
$importresults['newsid']=$newsid;
$importresults['oldsid']=$oldsid;
return $importresults;
}
/**
*
* Enter description here...
* @param $surveyid
* @param $sMod
* @param $newGroup
* @return unknown_type
*/
function importQuestion($surveyid, $sMod, $newGroup = false)
{
global $connect;
global $dbprefix;
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
include "lsrc.config.php";
$newsid = $surveyid;
$this->debugLsrc("wir sind in " . __FUNCTION__ . " Line " . __LINE__ . ", START OK {$dbprefix} ");
//$getGidSql = "SELECT gid FROM {$dbprefix} ";
$getGidSql = "SELECT gid\r\n\t FROM {$dbprefix}groups \r\n\t WHERE sid=" . $surveyid . " AND language='" . GetBaseLanguageFromSurveyID($surveyid) . "'\r\n\t ORDER BY gid desc ";
$getGidRs = db_execute_num($getGidSql);
$gidRow = $getGidRs->FetchRow();
$gid = $gidRow[0];
if ($gid == '') {
$this->debugLsrc("No Group for importing the question, available!");
return "No Group for importing the question, available! Import failed.";
}
if ($newGroup === true) {
++$gid;
}
$the_full_file_path = $queDir . $sMod . ".csv";
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK {$the_full_file_path} ");
$handle = fopen($the_full_file_path, "r");
while (!feof($handle)) {
$buffer = fgets($handle, 10240);
//To allow for very long survey welcomes (up to 10k)
$bigarray[] = $buffer;
}
fclose($handle);
// Now we try to determine the dataformat of the survey file.
if (substr($bigarray[1], 0, 24) == "# SURVEYOR QUESTION DUMP" && substr($bigarray[4], 0, 29) == "# http://www.phpsurveyor.org/") {
$importversion = 100;
// version 1.0 file
} elseif (substr($bigarray[1], 0, 24) == "# SURVEYOR QUESTION DUMP" && substr($bigarray[4], 0, 37) == "# http://phpsurveyor.sourceforge.net/") {
$importversion = 99;
// Version 0.99 file or older - carries a different URL
} elseif (substr($bigarray[0], 0, 26) == "# LimeSurvey Question Dump" || substr($bigarray[0], 0, 27) == "# PHPSurveyor Question Dump") {
// Wow.. this seems to be a >1.0 version file - these files carry the version information to read in line two
$importversion = substr($bigarray[1], 12, 3);
} else {
// $importquestion .= "<strong><font color='red'>".("Error")."</font></strong>\n";
// $importquestion .= ("This file is not a LimeSurvey question file. Import failed.")."\n";
// $importquestion .= "</font></td></tr></table>\n";
// $importquestion .= "</body>\n</html>\n";
// unlink($the_full_file_path);
return "This is not a Limesurvey question file. Import failed";
}
// if ($importversion != $dbversionnumber)
// {
//// $importquestion .= "<strong><font color='red'>".("Error")."</font></strong>\n";
//// $importquestion .= ("Sorry, importing questions is limited to the same version. Import failed.")."\n";
//// $importquestion .= "</font></td></tr></table>\n";
//// $importquestion .= "</body>\n</html>\n";
//// unlink($the_full_file_path);
// return;
// }
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
for ($i = 0; $i < 9; $i++) {
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
//QUESTIONS
if (array_search("# ANSWERS TABLE\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\n", $bigarray);
} elseif (array_search("# ANSWERS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$questionarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
//ANSWERS
if (array_search("# LABELSETS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\n", $bigarray);
} elseif (array_search("# LABELSETS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$answerarray[] = str_replace("`default`", "`default_value`", $bigarray[$i]);
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
//LABELSETS
if (array_search("# LABELS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\n", $bigarray);
} elseif (array_search("# LABELS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsetsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
//LABELS
if (array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray);
} elseif (array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
//Question_attributes
if (!isset($noconditions) || $noconditions != "Y") {
$stoppoint = count($bigarray);
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 1) {
$question_attributesarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
}
$bigarray = array_values($bigarray);
if (isset($questionarray)) {
$countquestions = count($questionarray) - 1;
} else {
$countquestions = 0;
}
if (isset($answerarray)) {
$answerfieldnames = convertCSVRowToArray($answerarray[0], ',', '"');
unset($answerarray[0]);
$countanswers = count($answerarray);
} else {
$countanswers = 0;
}
if (isset($labelsetsarray)) {
$countlabelsets = count($labelsetsarray) - 1;
} else {
$countlabelsets = 0;
}
if (isset($labelsarray)) {
$countlabels = count($labelsarray) - 1;
} else {
$countlabels = 0;
}
if (isset($question_attributesarray)) {
$countquestion_attributes = count($question_attributesarray) - 1;
} else {
$countquestion_attributes = 0;
}
$languagesSupported = array();
// this array will keep all the languages supported for the survey
// Let's check that imported objects support at least the survey's baselang
$langcode = GetBaseLanguageFromSurveyID($surveyid);
$languagesSupported[$langcode] = 1;
// adds the base language to the list of supported languages
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
if ($countquestions > 0) {
$questionfieldnames = convertCSVRowToArray($questionarray[0], ',', '"');
$langfieldnum = array_search("language", $questionfieldnames);
$qidfieldnum = array_search("qid", $questionfieldnames);
$questionssupportbaselang = bDoesImportarraySupportsLanguage($questionarray, array($qidfieldnum), $langfieldnum, $langcode, true);
if (!$questionssupportbaselang) {
// $importquestion .= "<strong><font color='red'>".("Error")."</font></strong>\n"
// .("You can't import a question which doesn't support the current survey's base language")."\n"
// ."</td></tr></table>\n";
// unlink($the_full_file_path);
return "You can't import a question which doesn't support the current survey's base language";
}
}
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
foreach (GetAdditionalLanguagesFromSurveyID($surveyid) as $language) {
$languagesSupported[$language] = 1;
}
// Let's assume that if the questions do support tye baselang
// Then the answers do support it as well.
// ==> So the following section is commented for now
//if ($countanswers > 0)
//{
// $langfieldnum = array_search("language", $answerfieldnames);
// $answercodefilednum1 = array_search("qid", $answerfieldnames);
// $answercodefilednum2 = array_search("code", $answerfieldnames);
// $answercodekeysarr = Array($answercodefilednum1,$answercodefilednum2);
// $answerssupportbaselang = bDoesImportarraySupportsLanguage($answerarray,$answercodekeysarr,$langfieldnum,$langcode);
// if (!$answerssupportbaselang)
// {
// $importquestion .= "<strong><font color='red'>".("Error")."</font></strong>\n"
// .("You can't import answers which don't support current survey's base language")."\n"
// ."</td></tr></table>\n";
// return;
// }
//
//}
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
if ($countlabelsets > 0) {
$labelsetfieldname = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$langfieldnum = array_search("languages", $labelsetfieldname);
$lidfilednum = array_search("lid", $labelsetfieldname);
$labelsetssupportbaselang = bDoesImportarraySupportsLanguage($labelsetsarray, array($lidfilednum), $langfieldnum, $langcode, true);
if (!$labelsetssupportbaselang) {
// $importquestion .= "<strong><font color='red'>".("Error")."</font></strong>\n"
// .("You can't import label sets which don't support the current survey's base language")."\n"
// ."</td></tr></table>\n";
// unlink($the_full_file_path);
return "You can't import label sets which don't support the current survey's base language";
}
}
// I assume that if a labelset supports the survey's baselang,
// then it's labels do support it as well
// GET SURVEY AND GROUP DETAILS
//$surveyid=$postsid;
//$gid=$postgid;
$newsid = $surveyid;
$newgid = $gid;
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
//DO ANY LABELSETS FIRST, SO WE CAN KNOW WHAT THEIR NEW LID IS FOR THE QUESTIONS
if (isset($labelsetsarray) && $labelsetsarray) {
$csarray = buildLabelSetCheckSumArray();
// build checksums over all existing labelsets
$count = 0;
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
foreach ($labelsetsarray as $lsa) {
$fieldorders = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$fieldcontents = convertCSVRowToArray($lsa, ',', '"');
if ($count == 0) {
$count++;
continue;
}
$labelsetrowdata = array_combine($fieldorders, $fieldcontents);
// Save old labelid
$oldlid = $labelsetrowdata['lid'];
// set the new language
unset($labelsetrowdata['lid']);
$newvalues = array_values($labelsetrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lsainsert = "INSERT INTO {$dbprefix}labelsets (" . implode(',', array_keys($labelsetrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$lsiresult = $connect->Execute($lsainsert);
// Get the new insert id for the labels inside this labelset
$newlid = $connect->Insert_ID("{$dbprefix}labelsets", "lid");
if ($labelsarray) {
$count = 0;
foreach ($labelsarray as $la) {
$lfieldorders = convertCSVRowToArray($labelsarray[0], ',', '"');
$lfieldcontents = convertCSVRowToArray($la, ',', '"');
if ($count == 0) {
$count++;
continue;
}
// Combine into one array with keys and values since its easier to handle
$labelrowdata = array_combine($lfieldorders, $lfieldcontents);
$labellid = $labelrowdata['lid'];
if ($labellid == $oldlid) {
$labelrowdata['lid'] = $newlid;
// translate internal links
$labelrowdata['title'] = translink('label', $oldlid, $newlid, $labelrowdata['title']);
$newvalues = array_values($labelrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lainsert = "INSERT INTO {$dbprefix}labels (" . implode(',', array_keys($labelrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$liresult = $connect->Execute($lainsert);
}
}
}
//CHECK FOR DUPLICATE LABELSETS
$thisset = "";
$query2 = "SELECT code, title, sortorder, language\r\n\t\t FROM {$dbprefix}labels\r\n\t\t WHERE lid=" . $newlid . "\r\n\t\t ORDER BY language, sortorder, code";
$result2 = db_execute_num($query2) or $this->debugLsrc("Died querying labelset {$lid}{$query2}" . $connect->ErrorMsg());
while ($row2 = $result2->FetchRow()) {
$thisset .= implode('.', $row2);
}
// while
$newcs = dechex(crc32($thisset) * 1);
unset($lsmatch);
if (isset($csarray)) {
foreach ($csarray as $key => $val) {
if ($val == $newcs) {
$lsmatch = $key;
}
}
}
if (isset($lsmatch)) {
//There is a matching labelset. So, we will delete this one and refer
//to the matched one.
$query = "DELETE FROM {$dbprefix}labels WHERE lid={$newlid}";
$result = $connect->Execute($query) or $this->debugLsrc("Couldn't delete labels{$query}" . $connect->ErrorMsg());
$query = "DELETE FROM {$dbprefix}labelsets WHERE lid={$newlid}";
$result = $connect->Execute($query) or $this->debugLsrc("Couldn't delete labelset{$query}" . $connect->ErrorMsg());
$newlid = $lsmatch;
} else {
//There isn't a matching labelset, add this checksum to the $csarray array
$csarray[$newlid] = $newcs;
}
//END CHECK FOR DUPLICATES
$labelreplacements[] = array($oldlid, $newlid);
}
}
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
// QUESTIONS, THEN ANSWERS FOR QUESTIONS IN A NESTED FORMAT!
if (isset($questionarray) && $questionarray) {
$qafieldorders = convertCSVRowToArray($questionarray[0], ',', '"');
unset($questionarray[0]);
//Assuming we will only import one question at a time we will now find out the maximum question order in this group
//and save it for later
$qmaxqo = "SELECT MAX(question_order) AS maxqo FROM " . db_table_name('questions') . " WHERE sid={$newsid} AND gid={$newgid}";
$qres = db_execute_assoc($qmaxqo) or $this->debugLsrc("Error: " . ": Failed to find out maximum question order value\n{$qmaxqo}\n" . $connect->ErrorMsg());
$qrow = $qres->FetchRow();
$newquestionorder = $qrow['maxqo'] + 1;
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
foreach ($questionarray as $qa) {
$qacfieldcontents = convertCSVRowToArray($qa, ',', '"');
$newfieldcontents = $qacfieldcontents;
$questionrowdata = array_combine($qafieldorders, $qacfieldcontents);
if (isset($languagesSupported[$questionrowdata["language"]])) {
$oldqid = $questionrowdata['qid'];
$oldsid = $questionrowdata['sid'];
$oldgid = $questionrowdata['gid'];
// Remove qid field if there is no newqid; and set it to newqid if it's set
if (!isset($newqid)) {
unset($questionrowdata['qid']);
} else {
$questionrowdata['qid'] = $newqid;
}
$questionrowdata["sid"] = $newsid;
$questionrowdata["gid"] = $newgid;
$questionrowdata["question_order"] = $newquestionorder;
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
// Now we will fix up the label id
$type = $questionrowdata["type"];
//Get the type
if ($type == "F" || $type == "H" || $type == "1" || $type == ":" || $type == ";") {
//IF this is a flexible label array, update the lid entry
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
if (isset($labelreplacements)) {
foreach ($labelreplacements as $lrp) {
if ($lrp[0] == $questionrowdata["lid"]) {
$questionrowdata["lid"] = $lrp[1];
}
if ($lrp[0] == $questionrowdata["lid1"]) {
$questionrowdata["lid1"] = $lrp[1];
}
}
}
}
$other = $questionrowdata["other"];
//Get 'other' field value
$oldlid = $questionrowdata["lid"];
$questionrowdata = array_map('convertCsvreturn2return', $questionrowdata);
// translate internal links
$questionrowdata['title'] = translink('survey', $oldsid, $newsid, $questionrowdata['title']);
$questionrowdata['question'] = translink('survey', $oldsid, $newsid, $questionrowdata['question']);
$questionrowdata['help'] = translink('survey', $oldsid, $newsid, $questionrowdata['help']);
$newvalues = array_values($questionrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$qinsert = "INSERT INTO {$dbprefix}questions (" . implode(',', array_keys($questionrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
$qres = $connect->Execute($qinsert) or $this->debugLsrc("Error: " . ": Failed to insert question\n{$qinsert}\n" . $connect->ErrorMsg());
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
// set the newqid only if is not set
if (!isset($newqid)) {
$newqid = $connect->Insert_ID("{$dbprefix}questions", "qid");
}
}
}
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
//NOW DO ANSWERS FOR THIS QID - Is called just once and only if there was a question
if (isset($answerarray) && $answerarray) {
foreach ($answerarray as $aa) {
$answerfieldcontents = convertCSVRowToArray($aa, ',', '"');
$answerrowdata = array_combine($answerfieldnames, $answerfieldcontents);
if ($answerrowdata === false) {
$importquestion .= '' . "Faulty line in import - fields and data don't match" . ":" . implode(',', $answerfieldcontents);
}
if (isset($languagesSupported[$answerrowdata["language"]])) {
$code = $answerrowdata["code"];
$thisqid = $answerrowdata["qid"];
$answerrowdata["qid"] = $newqid;
// translate internal links
$answerrowdata['answer'] = translink('survey', $oldsid, $newsid, $answerrowdata['answer']);
$newvalues = array_values($answerrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$ainsert = "INSERT INTO {$dbprefix}answers (" . implode(',', array_keys($answerrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
$ares = $connect->Execute($ainsert) or $this->debugLsrc("Error: " . ": Failed to insert answer\n{$ainsert}\n" . $connect->ErrorMsg());
}
}
}
$this->debugLsrc("wir sind in " . __FILE__ . " - " . __FUNCTION__ . " Line " . __LINE__ . ", OK ");
// Finally the question attributes - Is called just once and only if there was a question
if (isset($question_attributesarray) && $question_attributesarray) {
//ONLY DO THIS IF THERE ARE QUESTION_ATTRIBUES
$fieldorders = convertCSVRowToArray($question_attributesarray[0], ',', '"');
unset($question_attributesarray[0]);
foreach ($question_attributesarray as $qar) {
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
$qarowdata = array_combine($fieldorders, $fieldcontents);
$qarowdata["qid"] = $newqid;
unset($qarowdata["qaid"]);
$newvalues = array_values($qarowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$qainsert = "INSERT INTO {$dbprefix}question_attributes (" . implode(',', array_keys($qarowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
$result = $connect->Execute($qainsert) or $this->debugLsrc("Couldn't insert question_attribute{$qainsert}" . $connect->ErrorMsg());
}
}
}
$this->debugLsrc("wir sind in - " . __FUNCTION__ . " Line " . __LINE__ . ", FERTIG ");
// CONDITIONS is DONE
return array('gid' => $newgid, 'qid' => $newqid);
//return $newgid;
}
/**
* This function imports an old-school question group file (*.csv,*.sql)
*
* @param mixed $sFullFilepath Full file patch to the import file
* @param mixed $newsid Survey ID to which the question is attached
*/
function CSVImportGroup($sFullFilepath, $newsid)
{
global $dbprefix, $connect, $clang;
$aLIDReplacements = array();
$aQIDReplacements = array();
// this array will have the "new qid" for the questions, the key will be the "old qid"
$aGIDReplacements = array();
$handle = fopen($sFullFilepath, "r");
while (!feof($handle)) {
$buffer = fgets($handle);
$bigarray[] = $buffer;
}
fclose($handle);
if (substr($bigarray[0], 0, 23) != "# LimeSurvey Group Dump") {
$results['fatalerror'] = $clang->gT("This file is not a LimeSurvey question file. Import failed.");
$importversion = 0;
} else {
$importversion = (int) trim(substr($bigarray[1], 12));
}
if ((int) $importversion < 112) {
$results['fatalerror'] = $clang->gT("This file is too old. Only files from LimeSurvey version 1.50 (DBVersion 112) and newer are supported.");
}
for ($i = 0; $i < 9; $i++) {
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//GROUPS
if (array_search("# QUESTIONS TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUESTIONS TABLE\n", $bigarray);
} elseif (array_search("# QUESTIONS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUESTIONS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$grouparray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUESTIONS
if (array_search("# ANSWERS TABLE\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\n", $bigarray);
} elseif (array_search("# ANSWERS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$questionarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//ANSWERS
if (array_search("# CONDITIONS TABLE\n", $bigarray)) {
$stoppoint = array_search("# CONDITIONS TABLE\n", $bigarray);
} elseif (array_search("# CONDITIONS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# CONDITIONS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$answerarray[] = str_replace("`default`", "`default_value`", $bigarray[$i]);
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//CONDITIONS
if (array_search("# LABELSETS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\n", $bigarray);
} elseif (array_search("# LABELSETS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\r\n", $bigarray);
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$conditionsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELSETS
if (array_search("# LABELS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\n", $bigarray);
} elseif (array_search("# LABELS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsetsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELS
if (array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray);
} elseif (array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//Question attributes
if (!isset($noconditions) || $noconditions != "Y") {
// stoppoint is the last line number
// this is an empty line after the QA CSV lines
$stoppoint = count($bigarray) - 1;
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i <= $stoppoint - 1) {
$question_attributesarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
}
$bigarray = array_values($bigarray);
$countgroups = 0;
if (isset($questionarray)) {
$questionfieldnames = convertCSVRowToArray($questionarray[0], ',', '"');
unset($questionarray[0]);
$countquestions = 0;
}
if (isset($answerarray)) {
$answerfieldnames = convertCSVRowToArray($answerarray[0], ',', '"');
unset($answerarray[0]);
$countanswers = count($answerarray);
} else {
$countanswers = 0;
}
$aLanguagesSupported = array();
// this array will keep all the languages supported for the survey
$sBaseLanguage = GetBaseLanguageFromSurveyID($newsid);
$aLanguagesSupported[] = $sBaseLanguage;
// adds the base language to the list of supported languages
$aLanguagesSupported = array_merge($aLanguagesSupported, GetAdditionalLanguagesFromSurveyID($newsid));
// Let's check that imported objects support at least the survey's baselang
$langcode = GetBaseLanguageFromSurveyID($newsid);
if (isset($grouparray)) {
$groupfieldnames = convertCSVRowToArray($grouparray[0], ',', '"');
$langfieldnum = array_search("language", $groupfieldnames);
$gidfieldnum = array_search("gid", $groupfieldnames);
$groupssupportbaselang = bDoesImportarraySupportsLanguage($grouparray, array($gidfieldnum), $langfieldnum, $sBaseLanguage, true);
if (!$groupssupportbaselang) {
$results['fatalerror'] = $clang->gT("You can't import a group which doesn't support at least the survey base language.");
return $results;
}
}
if (isset($questionarray)) {
$langfieldnum = array_search("language", $questionfieldnames);
$qidfieldnum = array_search("qid", $questionfieldnames);
$questionssupportbaselang = bDoesImportarraySupportsLanguage($questionarray, array($qidfieldnum), $langfieldnum, $sBaseLanguage, true);
if (!$questionssupportbaselang) {
$results['fatalerror'] = $clang->gT("You can't import a question which doesn't support at least the survey base language.");
return $results;
}
}
if ($countanswers > 0) {
$langfieldnum = array_search("language", $answerfieldnames);
$answercodefilednum1 = array_search("qid", $answerfieldnames);
$answercodefilednum2 = array_search("code", $answerfieldnames);
$answercodekeysarr = array($answercodefilednum1, $answercodefilednum2);
$answerssupportbaselang = bDoesImportarraySupportsLanguage($answerarray, $answercodekeysarr, $langfieldnum, $sBaseLanguage);
if (!$answerssupportbaselang) {
$results['fatalerror'] = $clang->gT("You can't import answers which doesn't support at least the survey base language.");
return $results;
}
}
if (count($labelsetsarray) > 1) {
$labelsetfieldname = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$langfieldnum = array_search("languages", $labelsetfieldname);
$lidfilednum = array_search("lid", $labelsetfieldname);
$labelsetssupportbaselang = bDoesImportarraySupportsLanguage($labelsetsarray, array($lidfilednum), $langfieldnum, $sBaseLanguage, true);
if (!$labelsetssupportbaselang) {
$results['fatalerror'] = $clang->gT("You can't import label sets which don't support the current survey's base language");
return $results;
}
}
// I assume that if a labelset supports the survey's baselang,
// then it's labels do support it as well
//DO ANY LABELSETS FIRST, SO WE CAN KNOW WHAT THEIR NEW LID IS FOR THE QUESTIONS
$results['labelsets'] = 0;
$qtypes = getqtypelist("", "array");
$results['labels'] = 0;
$results['labelsets'] = 0;
$results['answers'] = 0;
$results['subquestions'] = 0;
//Do label sets
if (isset($labelsetsarray) && $labelsetsarray) {
$csarray = buildLabelSetCheckSumArray();
// build checksums over all existing labelsets
$count = 0;
foreach ($labelsetsarray as $lsa) {
$fieldorders = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$fieldcontents = convertCSVRowToArray($lsa, ',', '"');
if ($count == 0) {
$count++;
continue;
}
$labelsetrowdata = array_combine($fieldorders, $fieldcontents);
// Save old labelid
$oldlid = $labelsetrowdata['lid'];
unset($labelsetrowdata['lid']);
$newvalues = array_values($labelsetrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lsainsert = "INSERT INTO {$dbprefix}labelsets (" . implode(',', array_keys($labelsetrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$lsiresult = $connect->Execute($lsainsert);
$results['labelsets']++;
// Get the new insert id for the labels inside this labelset
$newlid = $connect->Insert_ID("{$dbprefix}labelsets", 'lid');
if ($labelsarray) {
$count = 0;
foreach ($labelsarray as $la) {
$lfieldorders = convertCSVRowToArray($labelsarray[0], ',', '"');
$lfieldcontents = convertCSVRowToArray($la, ',', '"');
if ($count == 0) {
$count++;
continue;
}
// Combine into one array with keys and values since its easier to handle
$labelrowdata = array_combine($lfieldorders, $lfieldcontents);
$labellid = $labelrowdata['lid'];
if ($importversion <= 132) {
$labelrowdata["assessment_value"] = (int) $labelrowdata["code"];
}
if ($labellid == $oldlid) {
$labelrowdata['lid'] = $newlid;
// translate internal links
$labelrowdata['title'] = translink('label', $oldlid, $newlid, $labelrowdata['title']);
$newvalues = array_values($labelrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lainsert = "INSERT INTO {$dbprefix}labels (" . implode(',', array_keys($labelrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$liresult = $connect->Execute($lainsert);
if ($liresult !== false) {
$results['labels']++;
}
}
}
}
//CHECK FOR DUPLICATE LABELSETS
$thisset = "";
$query2 = "SELECT code, title, sortorder, language, assessment_value\n FROM {$dbprefix}labels\n WHERE lid=" . $newlid . "\n ORDER BY language, sortorder, code";
$result2 = db_execute_num($query2) or safe_die("Died querying labelset {$lid}<br />{$query2}<br />" . $connect->ErrorMsg());
while ($row2 = $result2->FetchRow()) {
$thisset .= implode('.', $row2);
}
// while
$newcs = dechex(crc32($thisset) * 1);
unset($lsmatch);
if (isset($csarray)) {
foreach ($csarray as $key => $val) {
if ($val == $newcs) {
$lsmatch = $key;
}
}
}
if (isset($lsmatch) || $_SESSION['USER_RIGHT_MANAGE_LABEL'] != 1) {
//There is a matching labelset or the user is not allowed to edit labels -
// So, we will delete this one and refer to the matched one.
$query = "DELETE FROM {$dbprefix}labels WHERE lid={$newlid}";
$result = $connect->Execute($query) or safe_die("Couldn't delete labels<br />{$query}<br />" . $connect->ErrorMsg());
$results['labels'] = $results['labels'] - $connect->Affected_Rows();
$query = "DELETE FROM {$dbprefix}labelsets WHERE lid={$newlid}";
$result = $connect->Execute($query) or safe_die("Couldn't delete labelset<br />{$query}<br />" . $connect->ErrorMsg());
$results['labelsets'] = $results['labelsets'] - $connect->Affected_Rows();
$newlid = $lsmatch;
} else {
//There isn't a matching labelset, add this checksum to the $csarray array
$csarray[$newlid] = $newcs;
}
//END CHECK FOR DUPLICATES
$aLIDReplacements[$oldlid] = $newlid;
}
}
// Import groups
if (isset($grouparray) && $grouparray) {
// do GROUPS
$gafieldorders = convertCSVRowToArray($grouparray[0], ',', '"');
unset($grouparray[0]);
$newgid = 0;
$group_order = 0;
// just to initialize this variable
foreach ($grouparray as $ga) {
$gacfieldcontents = convertCSVRowToArray($ga, ',', '"');
$grouprowdata = array_combine($gafieldorders, $gacfieldcontents);
// Skip not supported languages
if (!in_array($grouprowdata['language'], $aLanguagesSupported)) {
$skippedlanguages[] = $grouprowdata['language'];
// this is for the message in the end.
continue;
}
// replace the sid
$oldsid = $grouprowdata['sid'];
$grouprowdata['sid'] = $newsid;
// replace the gid or remove it if needed (it also will calculate the group order if is a new group)
$oldgid = $grouprowdata['gid'];
if ($newgid == 0) {
unset($grouprowdata['gid']);
// find the maximum group order and use this grouporder+1 to assign it to the new group
$qmaxgo = "select max(group_order) as maxgo from " . db_table_name('groups') . " where sid={$newsid}";
$gres = db_execute_assoc($qmaxgo) or safe_die($clang->gT("Error") . " Failed to find out maximum group order value<br />\n{$qmaxqo}<br />\n" . $connect->ErrorMsg());
$grow = $gres->FetchRow();
$group_order = $grow['maxgo'] + 1;
} else {
$grouprowdata['gid'] = $newgid;
}
$grouprowdata["group_order"] = $group_order;
// Everything set - now insert it
$grouprowdata = array_map('convertCsvreturn2return', $grouprowdata);
// translate internal links
$grouprowdata['group_name'] = translink('survey', $oldsid, $newsid, $grouprowdata['group_name']);
$grouprowdata['description'] = translink('survey', $oldsid, $newsid, $grouprowdata['description']);
db_switchIDInsert('groups', true);
$tablename = $dbprefix . 'groups';
$ginsert = $connect->GetinsertSQL($tablename, $grouprowdata);
$gres = $connect->Execute($ginsert) or safe_die($clang->gT('Error') . ": Failed to insert group<br />\n{$ginsert}<br />\n" . $connect->ErrorMsg());
db_switchIDInsert('groups', false);
//GET NEW GID .... if is not done before and we count a group if a new gid is required
if ($newgid == 0) {
$newgid = $connect->Insert_ID("{$dbprefix}groups", 'gid');
$countgroups++;
}
}
// GROUPS is DONE
// Import questions
if (isset($questionarray) && $questionarray) {
foreach ($questionarray as $qa) {
$qacfieldcontents = convertCSVRowToArray($qa, ',', '"');
$questionrowdata = array_combine($questionfieldnames, $qacfieldcontents);
$questionrowdata = array_map('convertCsvreturn2return', $questionrowdata);
$questionrowdata["type"] = strtoupper($questionrowdata["type"]);
// Skip not supported languages
if (!in_array($questionrowdata['language'], $aLanguagesSupported)) {
continue;
}
// replace the sid
$questionrowdata["sid"] = $newsid;
// replace the gid (if the gid is not in the oldgid it means there is a problem with the exported record, so skip it)
if ($questionrowdata['gid'] == $oldgid) {
$questionrowdata['gid'] = $newgid;
} else {
continue;
}
// a problem with this question record -> don't consider
if (isset($aQIDReplacements[$questionrowdata['qid']])) {
$questionrowdata['qid'] = $aQIDReplacements[$questionrowdata['qid']];
} else {
$oldqid = $questionrowdata['qid'];
unset($questionrowdata['qid']);
}
// Save the following values - will need them for proper conversion later if ((int)$questionrowdata['lid']>0)
unset($oldlid1);
unset($oldlid2);
if (isset($questionrowdata['lid']) && $questionrowdata['lid'] > 0) {
$oldlid1 = $questionrowdata['lid'];
}
if (isset($questionrowdata['lid1']) && $questionrowdata['lid1'] > 0) {
$oldlid2 = $questionrowdata['lid1'];
}
unset($questionrowdata['lid']);
unset($questionrowdata['lid1']);
if ($questionrowdata['type'] == 'W') {
$questionrowdata['type'] = '!';
} elseif ($questionrowdata['type'] == 'Z') {
$questionrowdata['type'] = 'L';
}
if (!isset($questionrowdata["question_order"]) || $questionrowdata["question_order"] == '') {
$questionrowdata["question_order"] = 0;
}
$questionrowdata = array_map('convertCsvreturn2return', $questionrowdata);
// translate internal links
$questionrowdata['title'] = translink('survey', $oldsid, $newsid, $questionrowdata['title']);
$questionrowdata['question'] = translink('survey', $oldsid, $newsid, $questionrowdata['question']);
$questionrowdata['help'] = translink('survey', $oldsid, $newsid, $questionrowdata['help']);
$newvalues = array_values($questionrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
if (isset($questionrowdata['qid'])) {
db_switchIDInsert('questions', true);
}
$tablename = $dbprefix . 'questions';
$qinsert = $connect->GetInsertSQL($tablename, $questionrowdata);
$qres = $connect->Execute($qinsert) or safe_die($clang->gT("Error") . ": Failed to insert question<br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
$results['questions']++;
//GET NEW QID .... if is not done before and we count a question if a new qid is required
if (isset($questionrowdata['qid'])) {
$saveqid = $questionrowdata['qid'];
} else {
$aQIDReplacements[$oldqid] = $connect->Insert_ID("{$dbprefix}questions", 'qid');
$saveqid = $aQIDReplacements[$oldqid];
}
$qtypes = getqtypelist("", "array");
$aSQIDReplacements = array();
db_switchIDInsert('questions', false);
// Now we will fix up old label sets where they are used as answers
if ((isset($oldlid1) || isset($oldlid2)) && ($qtypes[$questionrowdata['type']]['answerscales'] > 0 || $qtypes[$questionrowdata['type']]['subquestions'] > 1)) {
$query = "select * from " . db_table_name('labels') . " where lid={$aLIDReplacements[$oldlid1]} and language='{$questionrowdata['language']}'";
$oldlabelsresult = db_execute_assoc($query);
while ($labelrow = $oldlabelsresult->FetchRow()) {
if (in_array($labelrow['language'], $aLanguagesSupported)) {
if ($qtypes[$questionrowdata['type']]['subquestions'] < 2) {
$qinsert = "insert INTO " . db_table_name('answers') . " (qid,code,answer,sortorder,language,assessment_value)\n VALUES ({$aQIDReplacements[$oldqid]}," . db_quoteall($labelrow['code']) . "," . db_quoteall($labelrow['title']) . "," . db_quoteall($labelrow['sortorder']) . "," . db_quoteall($labelrow['language']) . "," . db_quoteall($labelrow['assessment_value']) . ")";
$qres = $connect->Execute($qinsert) or safe_die($clang->gT("Error") . ": Failed to insert answer (lid1) <br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
} else {
if (isset($aSQIDReplacements[$labelrow['code'] . '_' . $saveqid])) {
$fieldname = 'qid,';
$data = $aSQIDReplacements[$labelrow['code'] . '_' . $saveqid] . ',';
} else {
$fieldname = '';
$data = '';
}
$qinsert = "insert INTO " . db_table_name('questions') . " ({$fieldname} parent_qid,title,question,question_order,language,scale_id,type, sid, gid)\n VALUES ({$data}{$aQIDReplacements[$oldqid]}," . db_quoteall($labelrow['code']) . "," . db_quoteall($labelrow['title']) . "," . db_quoteall($labelrow['sortorder']) . "," . db_quoteall($labelrow['language']) . ",1,'{$questionrowdata['type']}',{$questionrowdata['sid']},{$questionrowdata['gid']})";
$qres = $connect->Execute($qinsert) or safe_die($clang->gT("Error") . ": Failed to insert question <br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
if ($fieldname == '') {
$aSQIDReplacements[$labelrow['code'] . '_' . $saveqid] = $connect->Insert_ID("{$dbprefix}questions", "qid");
}
}
}
}
if (isset($oldlid2) && $qtypes[$questionrowdata['type']]['answerscales'] > 1) {
$query = "select * from " . db_table_name('labels') . " where lid={$aLIDReplacements[$oldlid2]} and language='{$questionrowdata['language']}'";
$oldlabelsresult = db_execute_assoc($query);
while ($labelrow = $oldlabelsresult->FetchRow()) {
$qinsert = "insert INTO " . db_table_name('answers') . " (qid,code,answer,sortorder,language,assessment_value,scale_id)\n VALUES ({$aQIDReplacements[$oldqid]}," . db_quoteall($labelrow['code']) . "," . db_quoteall($labelrow['title']) . "," . db_quoteall($labelrow['sortorder']) . "," . db_quoteall($labelrow['language']) . "," . db_quoteall($labelrow['assessment_value']) . ",1)";
$qres = $connect->Execute($qinsert) or safe_die($clang->gT("Error") . ": Failed to insert answer (lid2)<br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
}
}
}
}
}
//Do answers
$results['subquestions'] = 0;
if (isset($answerarray) && $answerarray) {
foreach ($answerarray as $aa) {
$answerfieldcontents = convertCSVRowToArray($aa, ',', '"');
$answerrowdata = array_combine($answerfieldnames, $answerfieldcontents);
if ($answerrowdata === false) {
$importquestion .= '<br />' . $clang->gT("Faulty line in import - fields and data don't match") . ":" . implode(',', $answerfieldcontents);
}
// Skip not supported languages
if (!in_array($answerrowdata['language'], $aLanguagesSupported)) {
continue;
}
// replace the qid for the new one (if there is no new qid in the $aQIDReplacements array it mean that this answer is orphan -> error, skip this record)
if (isset($aQIDReplacements[$answerrowdata["qid"]])) {
$answerrowdata["qid"] = $aQIDReplacements[$answerrowdata["qid"]];
} else {
continue;
}
// a problem with this answer record -> don't consider
if ($importversion <= 132) {
$answerrowdata["assessment_value"] = (int) $answerrowdata["code"];
}
// Convert default values for single select questions
$questiontemp = $connect->GetRow('select type,gid from ' . db_table_name('questions') . ' where qid=' . $answerrowdata["qid"]);
$oldquestion['newtype'] = $questiontemp['type'];
$oldquestion['gid'] = $questiontemp['gid'];
if ($answerrowdata['default_value'] == 'Y' && ($oldquestion['newtype'] == 'L' || $oldquestion['newtype'] == 'O' || $oldquestion['newtype'] == '!')) {
$insertdata = array();
$insertdata['qid'] = $newqid;
$insertdata['language'] = $answerrowdata['language'];
$insertdata['defaultvalue'] = $answerrowdata['answer'];
$query = $connect->GetInsertSQL($dbprefix . 'defaultvalues', $insertdata);
$qres = $connect->Execute($query) or safe_die("Error: Failed to insert defaultvalue <br />{$query}<br />\n" . $connect->ErrorMsg());
}
// translate internal links
$answerrowdata['answer'] = translink('survey', $oldsid, $newsid, $answerrowdata['answer']);
// Everything set - now insert it
$answerrowdata = array_map('convertCsvreturn2return', $answerrowdata);
if ($qtypes[$oldquestion['newtype']]['subquestions'] > 0) {
$questionrowdata = array();
if (isset($aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']])) {
$questionrowdata['qid'] = $aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']];
}
$questionrowdata['parent_qid'] = $answerrowdata['qid'];
$questionrowdata['sid'] = $newsid;
$questionrowdata['gid'] = $oldquestion['gid'];
$questionrowdata['title'] = $answerrowdata['code'];
$questionrowdata['question'] = $answerrowdata['answer'];
$questionrowdata['question_order'] = $answerrowdata['sortorder'];
$questionrowdata['language'] = $answerrowdata['language'];
$questionrowdata['type'] = $oldquestion['newtype'];
$tablename = $dbprefix . 'questions';
$query = $connect->GetInsertSQL($tablename, $questionrowdata);
if (isset($questionrowdata['qid'])) {
db_switchIDInsert('questions', true);
}
$qres = $connect->Execute($query) or safe_die("Error: Failed to insert subquestion <br />{$query}<br />" . $connect->ErrorMsg());
if (!isset($questionrowdata['qid'])) {
$aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']] = $connect->Insert_ID("{$dbprefix}questions", "qid");
} else {
db_switchIDInsert('questions', false);
}
$results['subquestions']++;
// also convert default values subquestions for multiple choice
if ($answerrowdata['default_value'] == 'Y' && ($oldquestion['newtype'] == 'M' || $oldquestion['newtype'] == 'P')) {
$insertdata = array();
$insertdata['qid'] = $newqid;
$insertdata['sqid'] = $aSQIDReplacements[$answerrowdata['code']];
$insertdata['language'] = $answerrowdata['language'];
$insertdata['defaultvalue'] = 'Y';
$tablename = $dbprefix . 'defaultvalues';
$query = $connect->GetInsertSQL($tablename, $insertdata);
$qres = $connect->Execute($query) or safe_die("Error: Failed to insert defaultvalue <br />{$query}<br />\n" . $connect->ErrorMsg());
}
} else {
unset($answerrowdata['default_value']);
$tablename = $dbprefix . 'answers';
$query = $connect->GetInsertSQL($tablename, $answerrowdata);
$ares = $connect->Execute($query) or safe_die("Error: Failed to insert answer<br />{$query}<br />\n" . $connect->ErrorMsg());
$results['answers']++;
}
}
}
// ANSWERS is DONE
// Fix sortorder of the groups - if users removed groups manually from the csv file there would be gaps
fixSortOrderGroups($surveyid);
//... and for the questions inside the groups
// get all group ids and fix questions inside each group
$gquery = "SELECT gid FROM {$dbprefix}groups where sid={$newsid} group by gid ORDER BY gid";
//Get last question added (finds new qid)
$gres = db_execute_assoc($gquery);
while ($grow = $gres->FetchRow()) {
fixsortorderQuestions($grow['gid'], $newsid);
}
}
$results['question_attributes'] = 0;
// Finally the question attributes - it is called just once and only if there was a question
if (isset($question_attributesarray) && $question_attributesarray) {
//ONLY DO THIS IF THERE ARE QUESTION_ATTRIBUES
$fieldorders = convertCSVRowToArray($question_attributesarray[0], ',', '"');
unset($question_attributesarray[0]);
foreach ($question_attributesarray as $qar) {
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
$qarowdata = array_combine($fieldorders, $fieldcontents);
// replace the qid for the new one (if there is no new qid in the $aQIDReplacements array it mean that this attribute is orphan -> error, skip this record)
if (isset($aQIDReplacements[$qarowdata["qid"]])) {
$qarowdata["qid"] = $aQIDReplacements[$qarowdata["qid"]];
} else {
continue;
}
// a problem with this answer record -> don't consider
unset($qarowdata["qaid"]);
$tablename = "{$dbprefix}question_attributes";
$qainsert = $connect->GetInsertSQL($tablename, $qarowdata);
$result = $connect->Execute($qainsert);
if ($result !== false) {
$results['question_attributes']++;
}
}
}
// ATTRIBUTES is DONE
// do CONDITIONS
$results['conditions'] = 0;
if (isset($conditionsarray) && $conditionsarray) {
$fieldorders = convertCSVRowToArray($conditionsarray[0], ',', '"');
unset($conditionsarray[0]);
foreach ($conditionsarray as $car) {
$fieldcontents = convertCSVRowToArray($car, ',', '"');
$conditionrowdata = array_combine($fieldorders, $fieldcontents);
$oldqid = $conditionrowdata["qid"];
$oldcqid = $conditionrowdata["cqid"];
// replace the qid for the new one (if there is no new qid in the $aQIDReplacements array it mean that this condition is orphan -> error, skip this record)
if (isset($aQIDReplacements[$oldqid])) {
$conditionrowdata["qid"] = $aQIDReplacements[$oldqid];
} else {
continue;
}
// a problem with this answer record -> don't consider
// replace the cqid for the new one (if there is no new qid in the $aQIDReplacements array it mean that this condition is orphan -> error, skip this record)
if (isset($aQIDReplacements[$oldcqid])) {
$conditionrowdata["cqid"] = $aQIDReplacements[$oldcqid];
} else {
continue;
}
// a problem with this answer record -> don't consider
list($oldcsid, $oldcgid, $oldqidanscode) = explode("X", $conditionrowdata["cfieldname"], 3);
if ($oldcgid != $oldgid) {
// this means that the condition is in another group (so it should not have to be been exported -> skip it
continue;
}
unset($conditionrowdata["cid"]);
// recreate the cfieldname with the new IDs
if (preg_match("/^\\+/", $oldcsid)) {
$newcfieldname = '+' . $newsid . "X" . $newgid . "X" . $conditionrowdata["cqid"] . substr($oldqidanscode, strlen($oldqid));
} else {
$newcfieldname = $newsid . "X" . $newgid . "X" . $conditionrowdata["cqid"] . substr($oldqidanscode, strlen($oldqid));
}
$conditionrowdata["cfieldname"] = $newcfieldname;
if (!isset($conditionrowdata["method"]) || trim($conditionrowdata["method"]) == '') {
$conditionrowdata["method"] = '==';
}
$newvalues = array_values($conditionrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$conditioninsert = "insert INTO {$dbprefix}conditions (" . implode(',', array_keys($conditionrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
$result = $connect->Execute($conditioninsert) or safe_die("Couldn't insert condition<br />{$conditioninsert}<br />" . $connect->ErrorMsg());
$results['conditions']++;
}
}
LimeExpressionManager::RevertUpgradeConditionsToRelevance($newsid);
LimeExpressionManager::UpgradeConditionsToRelevance($newsid);
$results['groups'] = 1;
$results['newgid'] = $newgid;
return $results;
}
/**
* 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') . 'tokens.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);
$allowedfieldnames = array('firstname', 'lastname', 'email', 'emailstatus', 'token', 'language', 'validfrom', 'validuntil', 'usesleft');
$allowedfieldnames = array_merge($attrfieldnames, $allowedfieldnames);
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 = convertCSVRowToArray($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 (!in_array('firstname', $firstline) || !in_array('lastname', $firstline) || !in_array('email', $firstline)) {
$recordcount = count($tokenlistarray);
break;
}
} else {
$line = convertCSVRowToArray($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'] = '';
} else {
$writearray['token'] = sanitize_token($writearray['token']);
}
if (!$dupfound && !$invalidemail) {
if (!isset($writearray['emailstatus']) || $writearray['emailstatus'] == '') {
$writearray['emailstatus'] = "OK";
}
if (!isset($writearray['usesleft']) || $writearray['usesleft'] == '') {
$writearray['usesleft'] = 1;
}
if (!isset($writearray['language']) || $writearray['language'] == "") {
$writearray['language'] = $sBaseLanguage;
}
if (isset($writearray['validfrom']) && trim($writearray['validfrom'] == '')) {
unset($writearray['validfrom']);
}
if (isset($writearray['validuntil']) && trim($writearray['validuntil'] == '')) {
unset($writearray['validuntil']);
}
// sanitize it before writing into table
foreach ($writearray as $key => $value) {
if (substr($value, 0, 1) == '"' && substr($value, -1) == '"') {
$value = substr($value, 1, -1);
}
$sanitizedArray[Yii::app()->db->quoteColumnName($key)] = Yii::app()->db->quoteValue($value);
}
$iq = "INSERT INTO {{tokens_{$iSurveyId}}} \n" . "(" . implode(',', array_keys($writearray)) . ") \n" . "VALUES (" . implode(",", $sanitizedArray) . ")";
$ir = Yii::app()->db->createCommand($iq)->execute();
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);
}
}
/**
* import from csv
*/
function import($iSurveyId)
{
// CHECK TO SEE IF A TOKEN TABLE EXISTS FOR THIS SURVEY
$bTokenExists = tableExists('{{tokens_' . $iSurveyId . '}}');
if (!$bTokenExists) {
self::_newtokentable($iSurveyId);
}
$clang = $this->getController()->lang;
$iSurveyId = (int) $iSurveyId;
if (!hasSurveyPermission($iSurveyId, 'tokens', 'create')) {
die('access denied');
}
$this->getController()->_js_admin_includes(Yii::app()->getConfig('adminscripts') . 'tokens.js');
$aEncodings = array("armscii8" => $clang->gT("ARMSCII-8 Armenian"), "ascii" => $clang->gT("US ASCII"), "auto" => $clang->gT("Automatic"), "big5" => $clang->gT("Big5 Traditional Chinese"), "binary" => $clang->gT("Binary pseudo charset"), "cp1250" => $clang->gT("Windows Central European"), "cp1251" => $clang->gT("Windows Cyrillic"), "cp1256" => $clang->gT("Windows Arabic"), "cp1257" => $clang->gT("Windows Baltic"), "cp850" => $clang->gT("DOS West European"), "cp852" => $clang->gT("DOS Central European"), "cp866" => $clang->gT("DOS Russian"), "cp932" => $clang->gT("SJIS for Windows Japanese"), "dec8" => $clang->gT("DEC West European"), "eucjpms" => $clang->gT("UJIS for Windows Japanese"), "euckr" => $clang->gT("EUC-KR Korean"), "gb2312" => $clang->gT("GB2312 Simplified Chinese"), "gbk" => $clang->gT("GBK Simplified Chinese"), "geostd8" => $clang->gT("GEOSTD8 Georgian"), "greek" => $clang->gT("ISO 8859-7 Greek"), "hebrew" => $clang->gT("ISO 8859-8 Hebrew"), "hp8" => $clang->gT("HP West European"), "keybcs2" => $clang->gT("DOS Kamenicky Czech-Slovak"), "koi8r" => $clang->gT("KOI8-R Relcom Russian"), "koi8u" => $clang->gT("KOI8-U Ukrainian"), "latin1" => $clang->gT("cp1252 West European"), "latin2" => $clang->gT("ISO 8859-2 Central European"), "latin5" => $clang->gT("ISO 8859-9 Turkish"), "latin7" => $clang->gT("ISO 8859-13 Baltic"), "macce" => $clang->gT("Mac Central European"), "macroman" => $clang->gT("Mac West European"), "sjis" => $clang->gT("Shift-JIS Japanese"), "swe7" => $clang->gT("7bit Swedish"), "tis620" => $clang->gT("TIS620 Thai"), "ucs2" => $clang->gT("UCS-2 Unicode"), "ujis" => $clang->gT("EUC-JP Japanese"), "utf8" => $clang->gT("UTF-8 Unicode"));
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');
$sFileName = $_FILES['the_file']['name'];
$sFileTmpName = $_FILES['the_file']['tmp_name'];
$sFilePath = $sPath . '/' . $sFileName;
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);
$firstname = "";
$lastname = "";
$email = "";
$emailstatus = "OK";
$token = "";
$language = "";
$attribute1 = "";
$attribute2 = "";
//Clear out values from the last path, in case the next line is missing a value
if ($recordcount == 0) {
// Pick apart the first line
$buffer = removeBOM($buffer);
$allowedfieldnames = array('firstname', 'lastname', 'email', 'emailstatus', 'token', 'language', 'validfrom', 'validuntil', 'usesleft');
$allowedfieldnames = array_merge($attrfieldnames, $allowedfieldnames);
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 = convertCSVRowToArray($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 (!in_array('firstname', $firstline) || !in_array('lastname', $firstline) || !in_array('email', $firstline)) {
$recordcount = count($tokenlistarray);
break;
}
} else {
$line = convertCSVRowToArray($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'] = '';
} else {
$writearray['token'] = sanitize_token($writearray['token']);
}
if (!$dupfound && !$invalidemail) {
if (!isset($writearray['emailstatus']) || $writearray['emailstatus'] == '') {
$writearray['emailstatus'] = "OK";
}
if (!isset($writearray['usesleft']) || $writearray['usesleft'] == '') {
$writearray['usesleft'] = 1;
}
if (!isset($writearray['language']) || $writearray['language'] == "") {
$writearray['language'] = $sBaseLanguage;
}
if (isset($writearray['validfrom']) && trim($writearray['validfrom'] == '')) {
unset($writearray['validfrom']);
}
if (isset($writearray['validuntil']) && trim($writearray['validuntil'] == '')) {
unset($writearray['validuntil']);
}
// sanitize it before writing into table
foreach ($writearray as $key => $value) {
if (substr($value, 0, 1) == '"' && substr($value, -1) == '"') {
$value = substr($value, 1, -1);
}
$sanitizedArray[Yii::app()->db->quoteColumnName($key)] = Yii::app()->db->quoteValue($value);
}
$iq = "INSERT INTO {{tokens_{$iSurveyId}}} \n" . "(" . implode(',', array_keys($writearray)) . ") \n" . "VALUES (" . implode(",", $sanitizedArray) . ")";
$ir = Yii::app()->db->createCommand($iq)->execute();
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;
$this->_renderWrappedTemplate('token', array('tokenbar', 'csvupload'), $aData);
}
}
/**
* This function imports an old-school question file (*.csv,*.sql)
*
* @param mixed $sFullFilepath Full file patch to the import file
* @param mixed $newsid Survey ID to which the question is attached
* @param mixed $newgid Group ID top which the question is attached
*/
function CSVImportQuestion($sFullFilepath, $newsid, $newgid)
{
global $dbprefix, $connect, $clang;
$aLIDReplacements = array();
$aQIDReplacements = array();
// this array will have the "new qid" for the questions, the key will be the "old qid"
$aSQIDReplacements = array();
$results['labelsets'] = 0;
$results['labels'] = 0;
$handle = fopen($sFullFilepath, "r");
while (!feof($handle)) {
$buffer = fgets($handle);
//To allow for very long survey welcomes (up to 10k)
$bigarray[] = $buffer;
}
fclose($handle);
$importversion = 0;
// Now we try to determine the dataformat of the survey file.
if (substr($bigarray[1], 0, 24) == "# SURVEYOR QUESTION DUMP") {
$importversion = 100;
// version 1.0 or 0.99 file
} elseif (substr($bigarray[0], 0, 26) == "# LimeSurvey Question Dump" || substr($bigarray[0], 0, 27) == "# PHPSurveyor Question Dump") {
// This is a >1.0 version file - these files carry the version information to read in line two
$importversion = (int) substr($bigarray[1], 12, 3);
} else {
$results['fatalerror'] = $clang->gT("This file is not a LimeSurvey question file. Import failed.");
return $results;
}
if ((int) $importversion < 112) {
$results['fatalerror'] = $clang->gT("This file is too old. Only files from LimeSurvey version 1.50 (DBVersion 112) and newer are supported.");
return $results;
}
for ($i = 0; $i < 9; $i++) {
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//QUESTIONS
if (array_search("# ANSWERS TABLE\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\n", $bigarray);
} elseif (array_search("# ANSWERS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# ANSWERS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$questionarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//ANSWERS
if (array_search("# LABELSETS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\n", $bigarray);
} elseif (array_search("# LABELSETS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELSETS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$answerarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELSETS
if (array_search("# LABELS TABLE\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\n", $bigarray);
} elseif (array_search("# LABELS TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# LABELS TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsetsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//LABELS
if (array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\n", $bigarray);
} elseif (array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray)) {
$stoppoint = array_search("# QUESTION_ATTRIBUTES TABLE\r\n", $bigarray);
} else {
$stoppoint = count($bigarray) - 1;
}
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 2) {
$labelsarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
//Question_attributes
$stoppoint = count($bigarray);
for ($i = 0; $i <= $stoppoint + 1; $i++) {
if ($i < $stoppoint - 1) {
$question_attributesarray[] = $bigarray[$i];
}
unset($bigarray[$i]);
}
$bigarray = array_values($bigarray);
if (isset($questionarray)) {
$questionfieldnames = convertCSVRowToArray($questionarray[0], ',', '"');
unset($questionarray[0]);
$countquestions = count($questionarray) - 1;
} else {
$countquestions = 0;
}
if (isset($answerarray)) {
$answerfieldnames = convertCSVRowToArray($answerarray[0], ',', '"');
unset($answerarray[0]);
$countanswers = count($answerarray);
} else {
$countanswers = 0;
}
if (isset($labelsetsarray)) {
$countlabelsets = count($labelsetsarray) - 1;
} else {
$countlabelsets = 0;
}
if (isset($labelsarray)) {
$countlabels = count($labelsarray) - 1;
} else {
$countlabels = 0;
}
if (isset($question_attributesarray)) {
$countquestion_attributes = count($question_attributesarray) - 1;
} else {
$countquestion_attributes = 0;
}
$aLanguagesSupported = array();
// this array will keep all the languages supported for the survey
$sBaseLanguage = GetBaseLanguageFromSurveyID($newsid);
$aLanguagesSupported[] = $sBaseLanguage;
// adds the base language to the list of supported languages
$aLanguagesSupported = array_merge($aLanguagesSupported, GetAdditionalLanguagesFromSurveyID($newsid));
// Let's check that imported objects support at least the survey's baselang
if (isset($questionarray)) {
$langfieldnum = array_search("language", $questionfieldnames);
$qidfieldnum = array_search("qid", $questionfieldnames);
$questionssupportbaselang = bDoesImportarraySupportsLanguage($questionarray, array($qidfieldnum), $langfieldnum, $sBaseLanguage, true);
if (!$questionssupportbaselang) {
$results['fatalerror'] = $clang->gT("You can't import a question which doesn't support at least the survey base language.");
return $results;
}
}
if ($countanswers > 0) {
$langfieldnum = array_search("language", $answerfieldnames);
$answercodefilednum1 = array_search("qid", $answerfieldnames);
$answercodefilednum2 = array_search("code", $answerfieldnames);
$answercodekeysarr = array($answercodefilednum1, $answercodefilednum2);
$answerssupportbaselang = bDoesImportarraySupportsLanguage($answerarray, $answercodekeysarr, $langfieldnum, $sBaseLanguage);
if (!$answerssupportbaselang) {
$results['fatalerror'] = $clang->gT("You can't import answers which doesn't support at least the survey base language.");
return $results;
}
}
if ($countlabelsets > 0) {
$labelsetfieldname = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$langfieldnum = array_search("languages", $labelsetfieldname);
$lidfilednum = array_search("lid", $labelsetfieldname);
$labelsetssupportbaselang = bDoesImportarraySupportsLanguage($labelsetsarray, array($lidfilednum), $langfieldnum, $sBaseLanguage, true);
if (!$labelsetssupportbaselang) {
$results['fatalerror'] = $clang->gT("You can't import label sets which don't support the current survey's base language");
return $results;
}
}
// I assume that if a labelset supports the survey's baselang,
// then it's labels do support it as well
//DO ANY LABELSETS FIRST, SO WE CAN KNOW WHAT THEIR NEW LID IS FOR THE QUESTIONS
if (isset($labelsetsarray) && $labelsetsarray) {
$csarray = buildLabelSetCheckSumArray();
// build checksums over all existing labelsets
$count = 0;
foreach ($labelsetsarray as $lsa) {
$fieldorders = convertCSVRowToArray($labelsetsarray[0], ',', '"');
$fieldcontents = convertCSVRowToArray($lsa, ',', '"');
if ($count == 0) {
$count++;
continue;
}
$results['labelsets']++;
$labelsetrowdata = array_combine($fieldorders, $fieldcontents);
// Save old labelid
$oldlid = $labelsetrowdata['lid'];
// set the new language
unset($labelsetrowdata['lid']);
$newvalues = array_values($labelsetrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lsainsert = "INSERT INTO {$dbprefix}labelsets (" . implode(',', array_keys($labelsetrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$lsiresult = $connect->Execute($lsainsert);
// Get the new insert id for the labels inside this labelset
$newlid = $connect->Insert_ID("{$dbprefix}labelsets", 'lid');
if ($labelsarray) {
$count = 0;
foreach ($labelsarray as $la) {
$lfieldorders = convertCSVRowToArray($labelsarray[0], ',', '"');
$lfieldcontents = convertCSVRowToArray($la, ',', '"');
if ($count == 0) {
$count++;
continue;
}
// Combine into one array with keys and values since its easier to handle
$labelrowdata = array_combine($lfieldorders, $lfieldcontents);
$labellid = $labelrowdata['lid'];
if ($importversion <= 132) {
$labelrowdata["assessment_value"] = (int) $labelrowdata["code"];
}
if ($labellid == $oldlid) {
$labelrowdata['lid'] = $newlid;
// translate internal links
$labelrowdata['title'] = translink('label', $oldlid, $newlid, $labelrowdata['title']);
$newvalues = array_values($labelrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$lainsert = "INSERT INTO {$dbprefix}labels (" . implode(',', array_keys($labelrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
//handle db prefix
$liresult = $connect->Execute($lainsert);
$results['labels']++;
}
}
}
//CHECK FOR DUPLICATE LABELSETS
$thisset = "";
$query2 = "SELECT code, title, sortorder, language, assessment_value\n FROM {$dbprefix}labels\n WHERE lid=" . $newlid . "\n ORDER BY language, sortorder, code";
$result2 = db_execute_num($query2) or safe_die("Died querying labelset {$lid}<br />{$query2}<br />" . $connect->ErrorMsg());
while ($row2 = $result2->FetchRow()) {
$thisset .= implode('.', $row2);
}
// while
$newcs = dechex(crc32($thisset) * 1);
unset($lsmatch);
if (isset($csarray)) {
foreach ($csarray as $key => $val) {
if ($val == $newcs) {
$lsmatch = $key;
}
}
}
if (isset($lsmatch)) {
//There is a matching labelset. So, we will delete this one and refer
//to the matched one.
$query = "DELETE FROM {$dbprefix}labels WHERE lid={$newlid}";
$result = $connect->Execute($query) or safe_die("Couldn't delete labels<br />{$query}<br />" . $connect->ErrorMsg());
$query = "DELETE FROM {$dbprefix}labelsets WHERE lid={$newlid}";
$result = $connect->Execute($query) or safe_die("Couldn't delete labelset<br />{$query}<br />" . $connect->ErrorMsg());
$newlid = $lsmatch;
} else {
//There isn't a matching labelset, add this checksum to the $csarray array
$csarray[$newlid] = $newcs;
}
//END CHECK FOR DUPLICATES
$aLIDReplacements[$oldlid] = $newlid;
}
}
// Import questions
if (isset($questionarray) && $questionarray) {
//Assuming we will only import one question at a time we will now find out the maximum question order in this group
//and save it for later
$newquestionorder = $connect->GetOne("SELECT MAX(question_order) AS maxqo FROM " . db_table_name('questions') . " WHERE sid={$newsid} AND gid={$newgid}");
if (is_null($newquestionorder)) {
$newquestionorder = 0;
} else {
$newquestionorder++;
}
foreach ($questionarray as $qa) {
$qacfieldcontents = convertCSVRowToArray($qa, ',', '"');
$questionrowdata = array_combine($questionfieldnames, $qacfieldcontents);
// Skip not supported languages
if (!in_array($questionrowdata['language'], $aLanguagesSupported)) {
continue;
}
// replace the sid
$oldqid = $questionrowdata['qid'];
$oldsid = $questionrowdata['sid'];
$oldgid = $questionrowdata['gid'];
// Remove qid field if there is no newqid; and set it to newqid if it's set
if (!isset($newqid)) {
unset($questionrowdata['qid']);
} else {
db_switchIDInsert('questions', true);
$questionrowdata['qid'] = $newqid;
}
$questionrowdata["sid"] = $newsid;
$questionrowdata["gid"] = $newgid;
$questionrowdata["question_order"] = $newquestionorder;
// Save the following values - will need them for proper conversion later if ((int)$questionrowdata['lid']>0)
if ((int) $questionrowdata['lid'] > 0) {
$oldquestion['lid1'] = (int) $questionrowdata['lid'];
}
if ((int) $questionrowdata['lid1'] > 0) {
$oldquestion['lid2'] = (int) $questionrowdata['lid1'];
}
$oldquestion['oldtype'] = $questionrowdata['type'];
// Unset label set IDs and convert question types
unset($questionrowdata['lid']);
unset($questionrowdata['lid1']);
if ($questionrowdata['type'] == 'W') {
$questionrowdata['type'] = '!';
} elseif ($questionrowdata['type'] == 'Z') {
$questionrowdata['type'] = 'L';
}
$oldquestion['newtype'] = $questionrowdata['type'];
$questionrowdata = array_map('convertCsvreturn2return', $questionrowdata);
// translate internal links
$questionrowdata['title'] = translink('survey', $oldsid, $newsid, $questionrowdata['title']);
$questionrowdata['question'] = translink('survey', $oldsid, $newsid, $questionrowdata['question']);
$questionrowdata['help'] = translink('survey', $oldsid, $newsid, $questionrowdata['help']);
$newvalues = array_values($questionrowdata);
$newvalues = array_map(array(&$connect, "qstr"), $newvalues);
// quote everything accordingly
$qinsert = "INSERT INTO {$dbprefix}questions (" . implode(',', array_keys($questionrowdata)) . ") VALUES (" . implode(',', $newvalues) . ")";
$qres = $connect->Execute($qinsert) or safe_die("Error: Failed to insert question<br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
// set the newqid only if is not set
if (!isset($newqid)) {
$newqid = $connect->Insert_ID("{$dbprefix}questions", "qid");
} else {
db_switchIDInsert('questions', false);
}
}
$qtypes = getqtypelist("", "array");
$results['answers'] = 0;
$results['subquestions'] = 0;
// Now we will fix up old label sets where they are used as answers
if ((isset($oldquestion['lid1']) || isset($oldquestion['lid2'])) && ($qtypes[$oldquestion['newtype']]['answerscales'] > 0 || $qtypes[$oldquestion['newtype']]['subquestions'] > 1)) {
$query = "select * from " . db_table_name('labels') . " where lid={$aLIDReplacements[$oldquestion['lid1']]} ";
$oldlabelsresult = db_execute_assoc($query);
while ($labelrow = $oldlabelsresult->FetchRow()) {
if (in_array($labelrow['language'], $aLanguagesSupported)) {
if ($qtypes[$oldquestion['newtype']]['subquestions'] < 2) {
$qinsert = "insert INTO " . db_table_name('answers') . " (qid,code,answer,sortorder,language,assessment_value,scale_id)\n VALUES ({$newqid}," . db_quoteall($labelrow['code']) . "," . db_quoteall($labelrow['title']) . "," . db_quoteall($labelrow['sortorder']) . "," . db_quoteall($labelrow['language']) . "," . db_quoteall($labelrow['assessment_value']) . ",0)";
$qres = $connect->Execute($qinsert) or safe_die("Error: Failed to insert answer <br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
$results['answers']++;
} else {
if (isset($aSQIDReplacements[$labelrow['code']])) {
$fieldname = 'qid,';
$data = $aSQIDReplacements[$labelrow['code']] . ',';
db_switchIDInsert('questions', true);
} else {
$fieldname = '';
$data = '';
}
$qinsert = "insert INTO " . db_table_name('questions') . " ({$fieldname} sid,gid,parent_qid,title,question,question_order,language,scale_id,type)\n VALUES ({$data} {$newsid},{$newgid},{$newqid}," . db_quoteall($labelrow['code']) . "," . db_quoteall($labelrow['title']) . "," . db_quoteall($labelrow['sortorder']) . "," . db_quoteall($labelrow['language']) . ",1," . db_quoteall($oldquestion['newtype']) . ")";
$qres = $connect->Execute($qinsert) or safe_die("Error: Failed to insert subquestion <br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
if ($fieldname == '') {
$aSQIDReplacements[$labelrow['code']] = $connect->Insert_ID("{$dbprefix}questions", "qid");
} else {
db_switchIDInsert('questions', false);
}
}
}
}
if (isset($oldquestion['lid2']) && $qtypes[$oldquestion['newtype']]['answerscales'] > 1) {
$query = "select * from " . db_table_name('labels') . " where lid={$aLIDReplacements[$oldquestion['lid2']]}";
$oldlabelsresult = db_execute_assoc($query);
while ($labelrow = $oldlabelsresult->FetchRow()) {
if (in_array($labelrow['language'], $aLanguagesSupported)) {
$qinsert = "insert INTO " . db_table_name('answers') . " (qid,code,answer,sortorder,language,assessment_value,scale_id)\n VALUES ({$newqid}," . db_quoteall($labelrow['code']) . "," . db_quoteall($labelrow['title']) . "," . db_quoteall($labelrow['sortorder']) . "," . db_quoteall($labelrow['language']) . "," . db_quoteall($labelrow['assessment_value']) . ",1)";
$qres = $connect->Execute($qinsert) or safe_die($clang->gT("Error") . ": Failed to insert answer <br />\n{$qinsert}<br />\n" . $connect->ErrorMsg());
}
}
}
}
//Do answers
if (isset($answerarray) && $answerarray) {
foreach ($answerarray as $aa) {
$answerfieldcontents = convertCSVRowToArray($aa, ',', '"');
$answerrowdata = array_combine($answerfieldnames, $answerfieldcontents);
if ($answerrowdata === false) {
$importquestion .= '<br />' . $clang->gT("Faulty line in import - fields and data don't match") . ":" . implode(',', $answerfieldcontents);
}
// Skip not supported languages
if (!in_array($answerrowdata['language'], $aLanguagesSupported)) {
continue;
}
$code = $answerrowdata["code"];
$thisqid = $answerrowdata["qid"];
$answerrowdata["qid"] = $newqid;
if ($importversion <= 132) {
$answerrowdata["assessment_value"] = (int) $answerrowdata["code"];
}
// Convert default values for single select questions
if ($answerrowdata['default_value'] == 'Y' && ($oldquestion['newtype'] == 'L' || $oldquestion['newtype'] == 'O' || $oldquestion['newtype'] == '!')) {
$insertdata = array();
$insertdata['qid'] = $newqid;
$insertdata['language'] = $answerrowdata['language'];
$insertdata['defaultvalue'] = $answerrowdata['answer'];
$query = $connect->GetInsertSQL($dbprefix . 'defaultvalues', $insertdata);
$qres = $connect->Execute($query) or safe_die("Error: Failed to insert defaultvalue <br />{$query}<br />\n" . $connect->ErrorMsg());
}
// translate internal links
$answerrowdata['answer'] = translink('survey', $oldsid, $newsid, $answerrowdata['answer']);
// Everything set - now insert it
$answerrowdata = array_map('convertCsvreturn2return', $answerrowdata);
if ($qtypes[$oldquestion['newtype']]['subquestions'] > 0) {
$questionrowdata = array();
if (isset($aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']])) {
$questionrowdata['qid'] = $aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']];
db_switchIDInsert('questions', true);
}
$questionrowdata['parent_qid'] = $answerrowdata['qid'];
$questionrowdata['sid'] = $newsid;
$questionrowdata['gid'] = $newgid;
$questionrowdata['title'] = $answerrowdata['code'];
$questionrowdata['question'] = $answerrowdata['answer'];
$questionrowdata['question_order'] = $answerrowdata['sortorder'];
$questionrowdata['language'] = $answerrowdata['language'];
$questionrowdata['type'] = $oldquestion['newtype'];
$tablename = $dbprefix . 'questions';
$query = $connect->GetInsertSQL($tablename, $questionrowdata);
$qres = $connect->Execute($query) or safe_die("Error: Failed to insert question <br />{$query}<br />\n" . $connect->ErrorMsg());
if (!isset($questionrowdata['qid'])) {
$aSQIDReplacements[$answerrowdata['code'] . $answerrowdata['qid']] = $connect->Insert_ID("{$dbprefix}questions", "qid");
} else {
db_switchIDInsert('questions', false);
}
$results['subquestions']++;
// also convert default values subquestions for multiple choice
if ($answerrowdata['default_value'] == 'Y' && ($oldquestion['newtype'] == 'M' || $oldquestion['newtype'] == 'P')) {
$insertdata = array();
$insertdata['qid'] = $newqid;
$insertdata['sqid'] = $aSQIDReplacements[$answerrowdata['code']];
$insertdata['language'] = $answerrowdata['language'];
$insertdata['defaultvalue'] = 'Y';
$tablename = $dbprefix . 'defaultvalues';
$query = $connect->GetInsertSQL($tablename, $insertdata);
$qres = $connect->Execute($query) or safe_die("Error: Failed to insert defaultvalue <br />{$query}<br />\n" . $connect->ErrorMsg());
}
} else {
unset($answerrowdata['default_value']);
$tablename = $dbprefix . 'answers';
$query = $connect->GetInsertSQL($tablename, $answerrowdata);
$ares = $connect->Execute($query) or safe_die("Error: Failed to insert answer<br />{$query}<br />\n" . $connect->ErrorMsg());
$results['answers']++;
}
}
}
$results['question_attributes'] = 0;
// Finally the question attributes - it is called just once and only if there was a question
if (isset($question_attributesarray) && $question_attributesarray) {
//ONLY DO THIS IF THERE ARE QUESTION_ATTRIBUES
$fieldorders = convertCSVRowToArray($question_attributesarray[0], ',', '"');
unset($question_attributesarray[0]);
foreach ($question_attributesarray as $qar) {
$fieldcontents = convertCSVRowToArray($qar, ',', '"');
$qarowdata = array_combine($fieldorders, $fieldcontents);
$qarowdata["qid"] = $newqid;
unset($qarowdata["qaid"]);
$tablename = "{$dbprefix}question_attributes";
$qainsert = $connect->GetInsertSQL($tablename, $qarowdata);
$result = $connect->Execute($qainsert) or safe_die("Couldn't insert question_attribute<br />{$qainsert}<br />" . $connect->ErrorMsg());
$results['question_attributes']++;
}
}
}
LimeExpressionManager::SetDirtyFlag();
// so refreshes syntax highlighting
$results['newqid'] = $newqid;
$results['questions'] = 1;
$results['newqid'] = $newqid;
return $results;
}
$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 (!in_array('firstname', $firstline) || !in_array('lastname', $firstline) || !in_array('email', $firstline)) {
$tokenoutput .= "<div class='warningheader'>" . $clang->gT("Error: Your uploaded file is missing one or more of the mandatory columns: 'firstname', 'lastname' or 'email'") . "</div><br />";
$recordcount = count($tokenlistarray);
break;
}
} else {
$line = convertCSVRowToArray($buffer, ',', '"');
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) {
if (!isset($_POST['filterduplicatefields']) || isset($_POST['filterduplicatefields']) && count($_POST['filterduplicatefields']) == 0) {
$filterduplicatefields = array('firstname', 'lastname', 'email');
