示例#1
0
        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());
        }
示例#2
0
        /// <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);
        }