/// <summary>
/// Import the given surveys into the DB
/// </summary>
public void ImportSurveys(NSurveyForm importSurveys, int userId, int folderId)
{
ISurvey survey = SurveyFactory.Create();
this.TurnOverPrimaryKeys(importSurveys);
survey.ImportSurveys(importSurveys, userId, folderId);
}
private void ImportXMLButton_Click(object sender, System.EventArgs e)
{
if (ImportFile.PostedFile != null)
{
NSurveyForm importedSurveys = new NSurveyForm();
try
{
importedSurveys.ReadXml(System.Xml.XmlReader.Create(ImportFile.PostedFile.InputStream, (new System.Xml.XmlReaderSettings()
{
XmlResolver = null
})));
if (importedSurveys.Survey.Rows.Count > 0)
{
// Prevents SQL injection from custom hand written datasources Sql answer types in the import Xml
if (!GlobalConfig.SqlBasedAnswerTypesAllowed || !(((PageBase)Page).NSurveyUser.Identity.IsAdmin || ((PageBase)Page).CheckRight(NSurveyRights.SqlAnswerTypesEdition, false)))
{
foreach (NSurveyForm.AnswerTypeRow answerType in importedSurveys.AnswerType)
{
answerType.DataSource = null;
}
}
new Survey().ImportSurveys(importedSurveys, ((PageBase)Page).NSurveyUser.Identity.UserId, ((PageBase)Page).SelectedFolderId ?? -1);
Surveys srv = new Surveys();
srv.SetFolderId(((PageBase)Page).SelectedFolderId, importedSurveys.Survey[0].SurveyID);
AssignSurveyToUser(importedSurveys.Survey[0].SurveyID);
SurveyId = importedSurveys.Survey[0].SurveyID;
UINavigator.NavigateToSurveyBuilder(importedSurveys.Survey[0].SurveyID, 4);
((PageBase)Page).ShowNormalMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImported"));
}
else
{
((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyNotImported"));
}
MessageLabel.Visible = true;
}
catch (Exception ex)
{
if (ex.Message == "DUPLICATEFOLDER")
{
((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImportDuplicate"));
}
else
{
((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("Exception") + " " + ex.Message);
}
MessageLabel.Visible = true;
}
}
}
private void ExportSurveyButton_Click(object sender, System.EventArgs e)
{
NSurveyForm surveyForm = null;
Response.Charset = "utf-8";
Response.ContentType = "application/octet-stream";
Response.ContentType = "text/xml";
Response.AddHeader("Content-Disposition", "attachment; filename=\"SurveyExport" + SurveyId + ".xml\"");
surveyForm = new Surveys().GetFormForExport(SurveyId);
surveyForm.WriteXml(Response.OutputStream, System.Data.XmlWriteMode.IgnoreSchema);
Response.End();
}
/// <summary>
/// Returns all the survey, questions, answers
/// for a survey
/// </summary>
public NSurveyForm GetFormForExport(int surveyId)
{
NSurveyForm formForExport = SurveyFactory.Create().GetFormForExport(surveyId);
if (formForExport.Survey.Rows.Count > 0)
{
formForExport.Survey[0].SurveyID = 1;
int num = 1;
foreach (NSurveyForm.QuestionRow row in formForExport.Survey[0].GetQuestionRows())
{
row.QuestionId = num;
num++;
}
}
return(formForExport);
}
/// <summary>
/// Invert main table keys signs to avoid key collisions on insertion
/// of new row with existing keys in the dataset that have not
/// yet been updated with their "live" DB key.
/// Changes will casade to child tables
/// </summary>
private void TurnOverPrimaryKeys(NSurveyForm importSurveys)
{
foreach (NSurveyForm.AnswerTypeRow row in importSurveys.AnswerType)
{
row.AnswerTypeID = (short)-row.AnswerTypeID;
}
foreach (NSurveyForm.RegularExpressionRow row2 in importSurveys.RegularExpression)
{
row2.RegularExpressionId = -row2.RegularExpressionId;
}
foreach (NSurveyForm.QuestionRow row3 in importSurveys.Question)
{
row3.QuestionId = -row3.QuestionId;
}
foreach (NSurveyForm.AnswerRow row4 in importSurveys.Answer)
{
row4.AnswerId = -row4.AnswerId;
}
}
private void ImportXMLButton_Click(object sender, System.EventArgs e)
{
if (ImportFile.PostedFile != null)
{
NSurveyForm importedSurveys = new NSurveyForm();
try
{
importedSurveys.ReadXml(ImportFile.PostedFile.InputStream);
if (importedSurveys.Survey.Rows.Count > 0)
{
// Prevents SQL injection from custom hand written datasources Sql answer types in the import Xml
if (!GlobalConfig.SqlBasedAnswerTypesAllowed || !(((PageBase)Page).NSurveyUser.Identity.IsAdmin || ((PageBase)Page).CheckRight(NSurveyRights.SqlAnswerTypesEdition, false)))
{
foreach (NSurveyForm.AnswerTypeRow answerType in importedSurveys.AnswerType)
{
answerType.DataSource = null;
}
}
new Survey().ImportSurveys(importedSurveys, ((PageBase)Page).NSurveyUser.Identity.UserId, ((PageBase)Page).SelectedFolderId ?? -1);
Surveys srv = new Surveys();
srv.SetFolderId(((PageBase)Page).SelectedFolderId, importedSurveys.Survey[0].SurveyID);
AssignSurveyToUser(importedSurveys.Survey[0].SurveyID);
SurveyId = importedSurveys.Survey[0].SurveyID;
UINavigator.NavigateToSurveyBuilder(importedSurveys.Survey[0].SurveyID, 4);
((PageBase)Page).ShowNormalMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImported"));
}
else ((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyNotImported"));
MessageLabel.Visible = true;
}
catch (Exception ex)
{
if (ex.Message == "DUPLICATEFOLDER")
((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImportDuplicate") );
else
((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("Exception") + " " + ex.Message);
MessageLabel.Visible = true;
}
}
}
/// <summary>
/// Invert main table keys signs to avoid key collisions on insertion
/// of new row with existing keys in the dataset that have not
/// yet been updated with their "live" DB key.
/// Changes will casade to child tables
/// </summary>
private void TurnOverPrimaryKeys(NSurveyForm importSurveys)
{
foreach (NSurveyForm.AnswerTypeRow row in importSurveys.AnswerType)
{
row.AnswerTypeID = (short)-row.AnswerTypeID;
}
foreach (NSurveyForm.RegularExpressionRow row2 in importSurveys.RegularExpression)
{
row2.RegularExpressionId = -row2.RegularExpressionId;
}
foreach (NSurveyForm.QuestionRow row3 in importSurveys.Question)
{
row3.QuestionId = -row3.QuestionId;
}
foreach (NSurveyForm.AnswerRow row4 in importSurveys.Answer)
{
row4.AnswerId = -row4.AnswerId;
}
}
/// <summary>
/// Import the given surveys into the DB
/// </summary>
public void ImportSurveys(NSurveyForm importSurveys, int userId,int folderId)
{
ISurvey survey = SurveyFactory.Create();
this.TurnOverPrimaryKeys(importSurveys);
survey.ImportSurveys(importSurveys, userId,folderId);
}
/// <summary>
/// Import the given surveys into the DB
/// </summary>
public void ImportSurveys(NSurveyForm importSurveys, int userId, int folderId)
{
// Prepare connection and transaction
SqlConnection dbConnection = new SqlConnection(DbConnection.NewDbConnectionString);
dbConnection.Open();
SqlTransaction insertTransaction = dbConnection.BeginTransaction();
// Setup the insert commands for the survey, answer types, questions and answers
SqlCommand addNewAnswerType = new AnswerType().GetInsertAnswerTypeCommand(dbConnection, insertTransaction, userId);
SqlCommand addNewRegularExpression = new RegularExpression().GetInsertRegularExpressionCommand(dbConnection, insertTransaction, userId);
SqlCommand addNewSurvey = GetInsertSurveyCommand(dbConnection, insertTransaction);
SqlCommand addNewQuestion = new Question().GetInsertQuestionCommand(dbConnection, insertTransaction);
SqlCommand addNewChildQuestion = new Question().GetInsertChildQuestionCommand(dbConnection, insertTransaction);
SqlCommand addNewAnswer = new Answer().GetInsertAnswerCommand(dbConnection, insertTransaction);
SqlCommand addNewAnswerConnection = new Answer().GetInsertAnswerConnectionCommand(dbConnection, insertTransaction);
SqlCommand addNewAnswerProperty = new Answer().GetInsertAnswerPropertyCommand(dbConnection, insertTransaction);
SqlCommand addNewQuestionSection = new Question().GetInsertQuestionSectionCommand(dbConnection, insertTransaction, "");
SqlCommand addNewQuestionSectionGridAnswers = new Question().GetInsertQuestionSectionGridAnswersCommand(dbConnection, insertTransaction);
// Save the data in the DB
try
{
importSurveys.Survey[0].FolderId = folderId;
addNewSurvey.Parameters.Add(new SqlParameter("@MultiLanguageModeId", SqlDbType.Int, 4, "MultiLanguageModeId"));
addNewSurvey.Parameters.Add(new SqlParameter("@ThankYouMessage", SqlDbType.NVarChar, 4000, "ThankYouMessage"));
DbConnection.db.UpdateDataSet(importSurveys, "AnswerType", addNewAnswerType, new SqlCommand(), addNewAnswerType, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "RegularExpression", addNewRegularExpression, new SqlCommand(), addNewRegularExpression, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "Survey", addNewSurvey, new SqlCommand(), addNewSurvey, UpdateBehavior.Transactional);
// Add Question groups so we can attach them to questions
var groups = new QuestionGroups();
var defl = importSurveys.SurveyLanguage.SingleOrDefault(x => x.DefaultLanguage);
string defaultLang = defl == null ? null : defl.LanguageCode;
defaultLang = defaultLang ?? "en-US";
if (defaultLang != null) //Load groups only if default language is not null
{
var existingGroups = groups.GetAll(defaultLang).QuestionGroups;
foreach (var qgrp in importSurveys.QuestionGroup
.OrderBy(x => x.IsParentGroupIdNull() ? 0 : 1)) //Load parent groups first
{
var grpHere = existingGroups.FirstOrDefault(x => x.GroupName == qgrp.GroupName);
int groupIdHere;
if (grpHere == null)
{
int? parentGroupId = null;
if (!qgrp.IsParentGroupIdNull()) //Has Parent Group
{
var pgrp = importSurveys.QuestionGroup.SingleOrDefault(x => x.Id == qgrp.ParentGroupId);
if (pgrp != null)
{
var exPar = groups.GetAll(defaultLang).QuestionGroups
.FirstOrDefault(x => x.GroupName == pgrp.GroupName);
if (exPar != null) parentGroupId = exPar.ID;
}
}
groups.AddNewGroup(qgrp.GroupName, parentGroupId, defaultLang);
var grt = groups.GetAll(defaultLang).QuestionGroups
.FirstOrDefault(x => x.GroupName == qgrp.GroupName);
groupIdHere = grt.ID;
}
else
{
groupIdHere = grpHere.ID;
}
importSurveys.Question
.Where(x =>!x.IsQuestionGroupIdNull() && (x.QuestionGroupId == qgrp.OldId))
.ToList().ForEach(x => x.QuestionGroupId = groupIdHere);
}
}
DbConnection.db.UpdateDataSet(importSurveys, "Question", addNewQuestion, new SqlCommand(), addNewQuestion, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "Answer", addNewAnswer, new SqlCommand(), addNewAnswer, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "AnswerConnection", addNewAnswerConnection, new SqlCommand(), addNewAnswerConnection, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "ChildQuestion", addNewChildQuestion, new SqlCommand(), addNewChildQuestion, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "AnswerProperty", addNewAnswerProperty, new SqlCommand(), addNewAnswerProperty, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "QuestionSectionOption", addNewQuestionSection, new SqlCommand(), addNewQuestionSection, UpdateBehavior.Transactional);
int newSurveyId = importSurveys.Survey[0].SurveyID;
DbConnection.db.UpdateDataSet(importSurveys, "QuestionSectionGridAnswer", addNewQuestionSectionGridAnswers, new SqlCommand(), addNewQuestionSectionGridAnswers, UpdateBehavior.Transactional);
insertTransaction.Commit();
var multiLanguage = new MultiLanguage();
foreach (var lang in importSurveys.SurveyLanguage)
multiLanguage.UpdateSurveyLanguage(newSurveyId, lang.LanguageCode, lang.DefaultLanguage, Constants.Constants.EntitySurvey);
var localGroups = groups.GetAll(defaultLang).QuestionGroups;
foreach (var langText in importSurveys.MultiLanguageText)
{
//Process Survey level
if (langText.LanguageMessageTypeId == 10)
{
var impGrp = importSurveys.QuestionGroup.SingleOrDefault(x => x.OldId == langText.LanguageItemId);
if (impGrp != null)
{
var localGrp = localGroups.SingleOrDefault(x => x.GroupName == impGrp.GroupName);
try
{
if (localGrp != null)
multiLanguage.AddMultiLanguageText(localGrp.ID, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
}
catch (Exception ex) { }
}
}
if (langText.LanguageMessageTypeId == 4 || langText.LanguageMessageTypeId == 5)
multiLanguage.AddMultiLanguageText(newSurveyId, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
if (langText.LanguageMessageTypeId == 3 || langText.LanguageMessageTypeId == 11 || langText.LanguageMessageTypeId == 12)
multiLanguage.AddMultiLanguageText(importSurveys.Question.Single(x => x.OldQuestionId == langText.LanguageItemId).QuestionId
, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
if (langText.LanguageMessageTypeId == 1 || langText.LanguageMessageTypeId == 2 || langText.LanguageMessageTypeId == 13)
multiLanguage.AddMultiLanguageText(importSurveys.Answer.Single(x => x.OldAnswerId == langText.LanguageItemId).AnswerId
, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
}
dbConnection.Close();
}
catch (Exception e)
{
throw (e);
}
}
/// <summary>
/// Returns all the survey, questions, answers
/// for a survey
/// </summary>
public NSurveyForm GetFormForExport(int surveyId)
{
NSurveyForm surveyForm = new NSurveyForm();
DbConnection.db.LoadDataSet("vts_spSurveyGetForExport", surveyForm, new string[] { "AnswerType", "RegularExpression", "Survey", "Question", "Answer", "AnswerConnection", "ChildQuestion", "AnswerProperty", "QuestionSectionOption", "QuestionSectionGridAnswer", "Surveylanguage", "MultiLanguageText", "QuestionGroup" }, new SqlParameter("@SurveyId", surveyId).SqlValue);
return surveyForm;
}
