public override Variable[] Read()
{
if (this.Document == null)
{
throw new Exception("File not validated yet.");
}
Dictionary <string, Variable> variables = new Dictionary <string, Variable>();
Dictionary <string, Dictionary <string, Guid> > categories = new Dictionary <string, Dictionary <string, Guid> >();
base.Status = BaseClasses.DataImportStatus.Step4;
base.Progress = 0;
XmlNodeList xmlNodes = this.Document.DocumentElement.SelectNodes("Variables/Variable");
Variable variable;
VariableLabel variableLabel;
Category category;
CategoryLabel categoryLabel;
int i = 0;
StringBuilder query = new StringBuilder();
foreach (XmlNode xmlNode in xmlNodes)
{
variable = new Variable(base.Core.Variables);
variable.Name = xmlNode.Attributes["Name"].Value;
variable.IdStudy = base.Study.Id;
variable.Type = (VariableType)Enum.Parse(
typeof(VariableType),
xmlNode.Attributes["Type"].Value
);
variable.Insert();
variableLabel = new VariableLabel(base.Core.VariableLabels);
variableLabel.IdVariable = variable.Id;
variableLabel.Label = xmlNode.SelectSingleNode("Label").InnerText;
variableLabel.IdLanguage = base.IdLanguage;
variableLabel.Insert();
if (!variables.ContainsKey(variable.Name))
{
variables.Add(variable.Name, variable);
categories.Add(variable.Name, new Dictionary <string, Guid>());
}
// Create the responses database table for the variable.
CreateResponsesTable(variable);
query.Clear();
foreach (XmlNode xmlNodeCategory in xmlNode.ChildNodes)
{
if (xmlNodeCategory.Name != "Category")
{
continue;
}
category = new Category(base.Core.Categories);
category.IdVariable = variable.Id;
category.Name = xmlNodeCategory.Attributes["Name"].Value;
query.Append(category.RenderInsertQuery());
//category.Insert();
if (!categories[variable.Name].ContainsKey(category.Name))
{
categories[variable.Name].Add(category.Name, category.Id);
}
categoryLabel = new CategoryLabel(base.Core.CategoryLabels);
categoryLabel.IdCategory = category.Id;
categoryLabel.Label = xmlNodeCategory.InnerText;
categoryLabel.IdLanguage = base.IdLanguage;
//categoryLabel.Insert();
query.Append(categoryLabel.RenderInsertQuery());
if (query.Length >= 200000)
{
base.Core.Categories.ExecuteQuery(query.ToString());
query.Clear();
}
}
if (query.Length > 0)
{
base.Core.Categories.ExecuteQuery(query.ToString());
}
// Calculate the current reading progress.
base.Progress = (int)(i++ *100 / xmlNodes.Count);
}
base.Progress = 100;
base.Status = BaseClasses.DataImportStatus.Step5;
base.Progress = 0;
Dictionary <string, Guid> respondents = new Dictionary <string, Guid>();
query.Clear();
xmlNodes = this.Document.DocumentElement.SelectNodes("Respondents/Respondent");
foreach (XmlNode xmlNode in xmlNodes)
{
Respondent respondent = new Respondent(base.Core.Respondents);
respondent.IdStudy = base.Study.Id;
respondent.OriginalRespondentID = xmlNode.Attributes["Id"].Value;
query.Append(respondent.RenderInsertQuery());
if (!respondents.ContainsKey(respondent.OriginalRespondentID))
{
respondents.Add(respondent.OriginalRespondentID, respondent.Id);
}
if (query.Length >= 200000)
{
base.Core.Respondents.ExecuteQuery(query.ToString());
query.Clear();
}
}
if (query.Length > 0)
{
base.Core.Respondents.ExecuteQuery(query.ToString());
}
base.Status = BaseClasses.DataImportStatus.Step6;
base.Progress = 0;
query.Clear();
xmlNodes = this.Document.DocumentElement.SelectNodes("Responses/Response");
i = 0;
Variable v;
foreach (XmlNode xmlNode in xmlNodes)
{
v = variables[xmlNode.Attributes["Variable"].Value];
if (v.Type == VariableType.Numeric)
{
query.Append(string.Format(
"INSERT INTO [resp].[Var_{0}] (Id, IdRespondent, IdStudy, NumericAnswer) VALUES (NEWID(), '{1}', '{2}', '{3}')" + Environment.NewLine,
v.Id,
respondents[xmlNode.Attributes["Respondent"].Value],
base.Study.Id,
double.Parse(xmlNode.Attributes["Category"].Value)
));
}
else if (v.Type == VariableType.Text)
{
query.Append(string.Format(
"INSERT INTO [resp].[Var_{0}] (Id, IdRespondent, IdStudy, TextAnswer) VALUES (NEWID(), '{1}', '{2}', '{3}')" + Environment.NewLine,
v.Id,
respondents[xmlNode.Attributes["Respondent"].Value],
base.Study.Id,
xmlNode.Attributes["Category"].Value.Replace("'", "''")
));
}
else
{
query.Append(string.Format(
"INSERT INTO [resp].[Var_{0}] (Id, IdRespondent, IdStudy, IdCategory) VALUES (NEWID(), '{1}', '{2}', '{3}')" + Environment.NewLine,
v.Id,
respondents[xmlNode.Attributes["Respondent"].Value],
base.Study.Id,
categories[xmlNode.Attributes["Variable"].Value][xmlNode.Attributes["Category"].Value]
));
}
if (xmlNodes.Count <= 100 || i % (xmlNodes.Count / 100) == 0)
{
base.Core.Respondents.ExecuteQuery(query.ToString());
query.Clear();
base.Progress = ((i++ *100) / (double)xmlNodes.Count);
}
}
if (query.Length != 0)
{
base.Core.Respondents.ExecuteQuery(query.ToString());
query.Clear();
}
base.Progress = 100;
return(variables.Values.ToArray());
}
public override Variable[] Read()
{
ExcelReader reader = new ExcelReader(this.FileName);
// Create a new list of variables for the result.
List <Variable> result = new List <Variable>();
base.Status = BaseClasses.DataImportStatus.Step4;
base.Progress = 0;
Dictionary <string, Dictionary <string, int> > variables = new Dictionary <string, Dictionary <string, int> >();
int columnCount = 0;
while (true)
{
if (string.IsNullOrEmpty(reader[columnCount]))
{
break;
}
columnCount++;
}
int respondentVariableIndex = 0;
int lineCount = 0;
while (reader.Read())
{
for (int i = 0; i < columnCount; i++)
{
string variableName = reader[0, i];
if (variableName == this.RespondentVariable)
{
respondentVariableIndex = i;
}
if (string.IsNullOrEmpty(reader[0]))
{
break;
}
string category = reader[i];
if (string.IsNullOrEmpty(category))
{
continue;
}
if (!variables.ContainsKey(variableName))
{
variables.Add(variableName, new Dictionary <string, int>());
}
if (variables[variableName].ContainsKey(category))
{
variables[variableName][category]++;
}
else
{
variables[variableName].Add(category, 1);
}
}
lineCount++;
}
base.Status = BaseClasses.DataImportStatus.Step4;
base.Progress = 0;
int variableOrder = 0;
foreach (string variableName in variables.Keys)
{
VariableType variableType = VariableType.Single;
/*if (variableName.Contains("LiNK_Var_Type_"))
* {
* variableType = (VariableType)Enum.Parse(
* typeof(VariableType),
* variableName.Split(
* );
* }*/
//int typeCheck = variables[variableName].Values.Count(x => x > 1);
int test = variables[variableName].Keys.Count * 10 / 100;
int typeCheck = variables[variableName].Values.Count(x => x > test);
if (typeCheck == 0)
{
variableType = VariableType.Text;
}
bool isNumeric = true;
foreach (string category in variables[variableName].Keys)
{
double value;
if (double.TryParse(category, out value) == false && category != "")
{
isNumeric = false;
break;
}
if (category.Contains(" "))
{
variableType = VariableType.Text;
break;
}
}
if (isNumeric)
{
variableType = VariableType.Numeric;
}
// Create a new variable.
Variable variable = new Variable(base.Core.Variables);
// Set the variable's project id.
variable.IdStudy = base.Study.Id;
// Set the variable's values.
variable.Name = variableName.Replace(" ", "");
variable.Type = variableType;
variable.Order = variableOrder++;
// Lock the insert action.
lock (this)
{
// Insert the new variable into the database.
variable.Insert();
}
// Create a new variable label.
VariableLabel variableLabel = new VariableLabel(this.Core.VariableLabels);
variableLabel.Label = variableName;
variableLabel.IdLanguage = 2057;
variableLabel.IdVariable = variable.Id;
// Lock the insert action.
lock (this)
{
// Insert the new variable label into the database.
variableLabel.Insert();
}
this.Categories.Add(variable.Id, new Dictionary <string, Guid>());
if (variable.Type != VariableType.Numeric && variable.Type != VariableType.Text)
{
int categoryOrder = 0;
// Run through all parsed categories of the variable.
foreach (string categoryNames in variables[variableName].Keys)
{
foreach (string categoryName in categoryNames.Split(','))
{
if (this.Categories[variable.Id].ContainsKey(categoryName.Replace(" ", "_")))
{
continue;
}
// Create a new category.
Category category = new Category(base.Core.Categories);
// Set the category's variable id.
category.IdVariable = variable.Id;
// Set the category's values.
category.Name = categoryName.Replace(" ", "_");
//category.Factor = (categoryOrder + 1);
category.Value = (categoryOrder + 1);
category.Order = categoryOrder++;
// Lock the insert action.
lock (this)
{
// Insert the new variable into the database.
category.Insert();
}
// Create a new category label.
CategoryLabel categoryLabel = new CategoryLabel(this.Core.CategoryLabels);
categoryLabel.Label = categoryName;
categoryLabel.IdLanguage = 2057;
categoryLabel.IdCategory = category.Id;
// Lock the insert action.
lock (this)
{
// Insert the new category label into the database.
categoryLabel.Insert();
}
this.Categories[variable.Id].Add(category.Name, category.Id);
}
}
}
this.Variables.Add(variable.Name, variable);
// Calculate the current reading progress.
base.Progress = (int)(variableOrder * 100 / variables.Count);
}
base.Progress = 100;
base.Status = BaseClasses.DataImportStatus.Step5;
base.Progress = 0;
StringBuilder bulkInsertBuilder = new StringBuilder();
int insertSteps = lineCount / 100;
if (insertSteps == 0)
{
insertSteps = 1;
}
reader.Position = 0;
while (reader.Read())
{
if (string.IsNullOrEmpty(reader[respondentVariableIndex]))
{
break;
}
string respondentId = reader[respondentVariableIndex];
Respondent respondent = new Respondent(this.Core.Respondents);
respondent.OriginalRespondentID = respondentId;
respondent.IdStudy = this.Study.Id;
//respondent.Insert();
bulkInsertBuilder.Append(respondent.RenderInsertQuery());
bulkInsertBuilder.Append(Environment.NewLine);
this.Respondents.Add(respondentId, respondent.Id);
if (reader.Position % insertSteps == 0)
{
this.Core.Respondents.ExecuteQuery(bulkInsertBuilder.ToString());
bulkInsertBuilder = new StringBuilder();
base.Progress = reader.Position * 100 / lineCount;
}
}
if (bulkInsertBuilder.Length > 0)
{
this.Core.Respondents.ExecuteQuery(bulkInsertBuilder.ToString());
}
base.ResponseInsertStarted = DateTime.Now;
base.Status = BaseClasses.DataImportStatus.Step6;
base.Progress = 0;
reader.Position = 0;
List <Guid> createdResponsesTables = new List <Guid>();
Dictionary <string, StringBuilder> responseBulkInsertBuilder = new Dictionary <string, StringBuilder>();
while (reader.Read())
{
for (int i = 0; i < columnCount; i++)
{
string variableName = reader[0, i].Replace(" ", "");
if (!this.Variables.ContainsKey(variableName))
{
continue;
}
if (!responseBulkInsertBuilder.ContainsKey(variableName))
{
responseBulkInsertBuilder.Add(variableName, new StringBuilder());
}
if (!createdResponsesTables.Contains(this.Variables[variableName].Id))
{
// Create the responses database table for the variable.
CreateResponsesTable(this.Variables[variableName]);
createdResponsesTables.Add(this.Variables[variableName].Id);
}
if (string.IsNullOrEmpty(reader[0]))
{
break;
}
string[] values = new string[] { reader[i].Replace(" ", "_") };
if (this.Variables[variableName].Type == VariableType.Single ||
this.Variables[variableName].Type == VariableType.Multi)
{
values = values[0].Split(',');
}
foreach (string value in values)
{
Response response = new Response(this.Core.Responses[this.Variables[variableName].Id]);
response.IdRespondent = this.Respondents[reader[respondentVariableIndex]];
response.IdStudy = this.Study.Id;
switch (this.Variables[variableName].Type)
{
case VariableType.Text:
response.TextAnswer = value;
break;
case VariableType.Single:
case VariableType.Multi:
if (!this.Categories[this.Variables[variableName].Id].ContainsKey(value))
{
continue;
}
response.IdCategory = this.Categories[this.Variables[variableName].Id][value];
break;
case VariableType.Numeric:
decimal numericValue;
if (decimal.TryParse(value, out numericValue))
{
response.NumericAnswer = numericValue;
}
break;
}
//response.Insert();
responseBulkInsertBuilder[variableName].Append(response.RenderInsertQuery());
responseBulkInsertBuilder[variableName].Append(Environment.NewLine);
}
}
//base.Progress = reader.Position * 100 / lineCount;
if (reader.Position % insertSteps == 0)
{
foreach (string variableName in responseBulkInsertBuilder.Keys.ToList())
{
if (responseBulkInsertBuilder[variableName].Length > 0)
{
this.Core.Responses[this.Variables[variableName].Id].ExecuteQuery(
responseBulkInsertBuilder[variableName].ToString()
);
}
responseBulkInsertBuilder[variableName] = new StringBuilder();
}
if (lineCount != 0)
{
base.Progress = reader.Position * 100 / lineCount;
}
}
}
foreach (string variableName in responseBulkInsertBuilder.Keys.ToList())
{
if (responseBulkInsertBuilder[variableName].Length == 0)
{
continue;
}
this.Core.Responses[this.Variables[variableName].Id].ExecuteQuery(
responseBulkInsertBuilder[variableName].ToString()
);
responseBulkInsertBuilder[variableName] = new StringBuilder();
}
CreateStudyLinkVariable(reader, respondentVariableIndex);
return(result.ToArray());
}