public byte[] ExportValues()
{
string fileName = Path.GetTempFileName() + ".xlsx";
// Create a new excel writer to build the result document.
ExcelWriter writer = new ExcelWriter(fileName);
// Get the study's base value.
double studyBase = this.Core.Respondents.GetCount("IdStudy", this.IdStudy);
// Render the document's headline.
writer.Write(0, "Variable");
//writer.Write(0, "Category");
writer.Write(1, "Variable Type");
writer.Write(2, "% Complete");
writer.Write(3, "Valid records");
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 3].Interior.Color = SpreadsheetGear.Color.FromArgb(54, 94, 146);
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 3].Font.Color = SpreadsheetGear.Color.FromArgb(255, 255, 255);
writer.ActiveSheet.Cells[writer.Position, 0].EntireColumn.ColumnWidth = 50;
writer.ActiveSheet.Cells[writer.Position, 0].EntireColumn.WrapText = true;
writer.ActiveSheet.Cells[writer.Position, 1, writer.Position, 3].EntireColumn.ColumnWidth = 12;
writer.ActiveSheet.WindowInfo.FreezePanes = true;
writer.ActiveSheet.Cells[0, 0, 0, 4].Select();
writer.NewLine();
// Get all variables of the study.
List <object[]> variables = this.Core.Variables.GetValues(
new string[] { "Id", "Name", "Type" },
new string[] { "IdStudy" },
new object[] { this.IdStudy }
);
Dictionary <string, double> variableBases = new Dictionary <string, double>();
// Run through all variables of the study.
foreach (object[] variable in variables)
{
object idSystemMissingCategory = this.Core.Categories.GetValue(
"Id",
new string[] { "IdVariable", "Name" },
new object[] { variable[0], "SystemMissing" }
);
double baseValue;
if (idSystemMissingCategory != null)
{
if (this.Core.CaseDataLocation == CaseDataLocation.Sql || (VariableType)variable[2] == VariableType.Text)
{
baseValue = (int)this.Core.Responses[(Guid)variable[0]].ExecuteReader(string.Format(
"SELECT Count(*) FROM resp.[Var_{0}] WHERE IdCategory <> '{1}' OR IdCategory IS NULL",
variable[0],
idSystemMissingCategory
), typeof(int))[0][0];
}
else
{
baseValue = 0;
CaseDataCore1.CaseDataLink dataLink = new CaseDataCore1.CaseDataLink();
string commandText = "";
switch ((VariableType)variable[2])
{
case VariableType.Numeric:
commandText = string.Format(
"SELECT IdRespondent, NumericAnswer FROM [resp].[Var_{0}]",
variable[0]
);
break;
case VariableType.Text:
commandText = string.Format(
"SELECT IdRespondent, TextAnswer FROM [resp].[Var_{0}]",
variable[0]
);
break;
case VariableType.Single:
case VariableType.Multi:
commandText = string.Format(
"SELECT IdRespondent, IdCategory FROM [resp].[Var_{0}]",
variable[0]
);
break;
}
List <object[]> responses = dataLink.Select2(
commandText,
this.Core.ClientName
);
if ((VariableType)variable[2] != VariableType.Single && (VariableType)variable[2] != VariableType.Multi)
{
baseValue = responses.Count;
}
else
{
foreach (object[] response in responses)
{
if ((Guid)response[1] == (Guid)idSystemMissingCategory)
{
continue;
}
baseValue++;
}
}
}
}
else
{
if (this.Core.CaseDataLocation == CaseDataLocation.Sql)
{
baseValue = this.Core.Responses[(Guid)variable[0]].GetCount();
}
else
{
CaseDataCore1.CaseDataLink dataLink = new CaseDataCore1.CaseDataLink();
CaseDataCore1.CaseDataType dataType = CaseDataCore1.CaseDataType.Categorical;
if ((VariableType)variable[2] == VariableType.Single)
{
dataType = CaseDataCore1.CaseDataType.Numeric;
}
else if ((VariableType)variable[2] == VariableType.Text)
{
dataType = CaseDataCore1.CaseDataType.Text;
}
baseValue = dataLink.Count(
(Guid)variable[0],
dataType,
this.Core.ClientName
);
}
}
writer.Write(0, (string)variable[1]);
writer.Write(1, ((VariableType)variable[2]).ToString());
writer.Write(2, Math.Round(baseValue * 100 / studyBase, 0).ToString() + "%");
writer.Write(3, baseValue.ToString());
if (baseValue == 0)
{
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 3].Interior.Color = SpreadsheetGear.Color.FromArgb(255, 0, 0);
}
writer.NewLine();
variableBases.Add((string)variable[1], baseValue);
}
writer.NewSheet("Categories");
writer.Write(0, "Variable");
writer.Write(1, "Category");
writer.Write(2, "% Complete");
writer.Write(3, "Base");
writer.Write(4, "Valid records");
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 4].Interior.Color = SpreadsheetGear.Color.FromArgb(54, 94, 146);
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 4].Font.Color = SpreadsheetGear.Color.FromArgb(255, 255, 255);
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 1].EntireColumn.ColumnWidth = 50;
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 1].EntireColumn.WrapText = true;
writer.ActiveSheet.Cells[writer.Position, 2, writer.Position, 4].EntireColumn.ColumnWidth = 12;
writer.ActiveSheet.WindowInfo.FreezePanes = true;
writer.ActiveSheet.Cells[0, 0, 0, 4].Select();
writer.NewLine();
// Run through all variables of the study.
foreach (object[] variable in variables)
{
VariableType variableType = (VariableType)variable[2];
// Check if the variable is a categorical variable.
if (variableType != VariableType.Single && variableType != VariableType.Multi)
{
continue;
}
// Get all categories of the variable.
List <object[]> categories = this.Core.Categories.GetValues(
new string[] { "Id", "Name" },
new string[] { "IdVariable" },
new object[] { variable[0] }
);
List <object[]> variableData = new List <object[]>();
if (this.Core.CaseDataLocation == CaseDataLocation.File)
{
CaseDataCore1.CaseDataLink dataLink = new CaseDataCore1.CaseDataLink();
variableData = dataLink.Select2(string.Format(
"SELECT IdRespondent, IdCategory FROM [resp].[Var_{0}]",
variable[0]
), this.Core.ClientName);
}
// Run through all categories of the variable.
foreach (object[] category in categories)
{
// Check if the category is the system missing category.
if ((string)category[1] == "SystemMissing")
{
continue;
}
double baseValue = 0;
if (this.Core.CaseDataLocation == CaseDataLocation.Sql)
{
baseValue = this.Core.Responses[(Guid)variable[0]].GetCount("IdCategory", category[0]);
}
else
{
foreach (object[] response in variableData)
{
if ((Guid)response[1] == (Guid)category[0])
{
baseValue++;
}
}
}
writer.Write(0, (string)variable[1]);
writer.Write(1, (string)category[1]);
writer.Write(2, Math.Round(baseValue * 100 / variableBases[(string)variable[1]], 0).ToString() + "%");
writer.Write(3, variableBases[(string)variable[1]].ToString());
writer.Write(4, baseValue.ToString());
if (baseValue == 0)
{
writer.ActiveSheet.Cells[writer.Position, 0, writer.Position, 4].Interior.Color = SpreadsheetGear.Color.FromArgb(255, 0, 0);
}
writer.NewLine();
}
}
return(writer.Save());
}
/// <summary>
/// Checks if all of the study's respondent have a weight.
/// </summary>
/// <param name="idVariable">The weighting variable.</param>
public List <DataAnalyserLog> CheckWeights(Variable variable)
{
// Create a list that stores all result data analyser log messages.
List <DataAnalyserLog> result = new List <DataAnalyserLog>();
List <object[]> respondentsMissing;
List <object[]> respondentsZero;
if (this.Core.CaseDataLocation == CaseDataLocation.Sql)
{
respondentsMissing = this.Core.Respondents.ExecuteReader(string.Format(
"SELECT (SELECT OriginalRespondentID FROM Respondents WHERE Respondents.Id=IdRespondent), IdRespondent FROM resp.[Var_{0}] WHERE IdRespondent NOT IN (SELECT Id FROM Respondents WHERE IdStudy='{1}')",
variable.Id,
this.IdStudy
));
// Get all respondents that have a response in the weight variable, but the value is zero.
respondentsZero = this.Core.Respondents.ExecuteReader(string.Format(
"SELECT (SELECT OriginalRespondentID FROM Respondents WHERE Respondents.Id=IdRespondent), IdRespondent FROM resp.[Var_{0}] WHERE [NumericAnswer]=0",
variable.Id,
this.IdStudy
));
}
else
{
CaseDataCore1.CaseDataLink dataLink = new CaseDataCore1.CaseDataLink();
List <object[]> _respondents = this.Core.Respondents.GetValues(
new string[] { "OriginalRespondentID", "Id" },
new string[] { "IdStudy" },
new object[] { this.IdStudy }
);
Dictionary <Guid, List <object> > responses = dataLink.Select(
string.Format("SELECT IdRespondent, NumericAnswer FROM [resp].[Var_{0}]", variable.Id),
this.Core.ClientName
);
respondentsMissing = new List <object[]>();
respondentsZero = new List <object[]>();
foreach (object[] respondent in _respondents)
{
if (responses.ContainsKey((Guid)respondent[1]))
{
if ((double)(responses[(Guid)respondent[1]])[0] == 0.0)
{
respondentsZero.Add(respondent);
}
continue;
}
respondentsMissing.Add(respondent);
}
}
// Run through all respondents without a response in the weight variable.
foreach (object[] respondent in respondentsMissing)
{
DataAnalyserLog log = new DataAnalyserLog();
log.Message = string.Format(
"The respondent with the id '{0}' hasn't got a response in the weighting variable '{1}'",
respondent[0],
variable.Name
);
result.Add(log);
}
// Run through all respondents that have a response
// in the weight variable, but the value is zero.
foreach (object[] respondent in respondentsZero)
{
DataAnalyserLog log = new DataAnalyserLog();
log.Message = string.Format(
"The respondent with the id '{0}' has a weight value of zero in the weighting variable '{1}'",
respondent[0],
variable.Name
);
result.Add(log);
}
return(result);
}
