Example #1
0
 /// <summary>
 /// Returns survey comments for the specified survey.
 /// </summary>
 /// <param name="SurvID"></param>
 /// <returns></returns>
 public static List <SurveyComment> GetSurvCommentsBySurvey(Survey survey)
 {
     return(GetSurvCommentsBySurvey(survey.SID));
 }
Example #2
0
        //
        // Fill methods
        //


        /// <summary>
        /// // TODO replace with server function
        /// </summary>
        /// <param name="s"></param>
        /// <param name="commentTypes"></param>
        /// <param name="commentDate"></param>
        /// <param name="commentAuthors"></param>
        /// <param name="commentSources"></param>
        public static void FillCommentsBySurvey(Survey s, List <string> commentTypes = null, DateTime?commentDate = null, List <int> commentAuthors = null, List <string> commentSources = null, string commentText = null)
        {
            QuestionComment c;
            string          query = "SELECT * FROM qryCommentsQues WHERE SurvID = @sid";

            using (SqlDataAdapter sql = new SqlDataAdapter())
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISISConnectionString"].ConnectionString))
                {
                    conn.Open();

                    sql.SelectCommand = new SqlCommand();
                    sql.SelectCommand.Parameters.AddWithValue("@sid", s.SID);

                    if (commentTypes != null && commentTypes.Count != 0)
                    {
                        query += " AND (";
                        for (int i = 0; i < commentTypes.Count; i++)
                        {
                            sql.SelectCommand.Parameters.AddWithValue("@commentTypes" + i, commentTypes[i]);
                            query += " NoteType = @commentTypes" + i + " OR ";
                        }
                        query  = Utilities.TrimString(query, " OR ");
                        query += ")";
                    }

                    if (commentDate != null)
                    {
                        sql.SelectCommand.Parameters.AddWithValue("@commentDate", commentDate.Value);
                        query += " AND NoteDate >= @commentDate";
                    }

                    if (commentAuthors != null && commentAuthors.Count != 0)
                    {
                        query += " AND (";
                        for (int i = 0; i < commentAuthors.Count; i++)
                        {
                            sql.SelectCommand.Parameters.AddWithValue("@commentAuthors" + i, commentAuthors[i]);
                            query += " NoteInit = @commentAuthors" + i + " OR ";
                        }
                        query  = Utilities.TrimString(query, " OR ");
                        query += ")";
                    }

                    if (commentSources != null && commentSources.Count != 0)
                    {
                        query += " AND (";
                        for (int i = 0; i < commentSources.Count; i++)
                        {
                            sql.SelectCommand.Parameters.AddWithValue("@commentSources" + i, commentSources[i]);
                            query += " SourceName = @commentSources" + i + " OR ";
                        }
                        query  = Utilities.TrimString(query, " OR ");
                        query += ")";
                    }

                    if (commentText != null)
                    {
                        query += " AND ";
                        sql.SelectCommand.Parameters.AddWithValue("@commentText", commentText);
                        query += " Notes LIKE '%' + @commentText + '%'";
                    }

                    query += " ORDER BY NoteDate ASC";

                    sql.SelectCommand.CommandText = query;
                    sql.SelectCommand.Connection  = conn;

                    try
                    {
                        using (SqlDataReader rdr = sql.SelectCommand.ExecuteReader())
                        {
                            while (rdr.Read())
                            {
                                c = new QuestionComment
                                {
                                    Notes         = new Note((int)rdr["ID"], (string)rdr["Notes"]),
                                    QID           = (int)rdr["QID"],
                                    Survey        = (string)rdr["Survey"],
                                    VarName       = (string)rdr["VarName"],
                                    CID           = (int)rdr["CID"],
                                    NoteDate      = (DateTime)rdr["NoteDate"],
                                    NoteInit      = (int)rdr["NoteInit"],
                                    Name          = (string)rdr["Name"],
                                    NoteType      = (string)rdr["NoteType"],
                                    ShortNoteType = (string)rdr["ShortForm"],
                                    SurvID        = (int)rdr["SurvID"]
                                };
                                if (!rdr.IsDBNull(rdr.GetOrdinal("SourceName")))
                                {
                                    c.SourceName = (string)rdr["SourceName"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("Source")))
                                {
                                    c.Source = (string)rdr["Source"];
                                }

                                s.QuestionByID((int)rdr["QID"]).Comments.Add(c);
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Console.Write(e.Message);
                        return;
                    }
                }
        }
Example #3
0
 /// <summary>
 /// Returns question comments for the specified survey.
 /// </summary>
 /// <param name="survey">Survey object.</param>
 /// <returns></returns>
 public static List <QuestionComment> GetQuesCommentsBySurvey(Survey survey)
 {
     return(GetQuesCommentsBySurvey(survey.SID));
 }
        //
        // Fill Methods
        //

        /// <summary>
        /// Populates the provided Survey's question list.
        /// </summary>
        /// <param name="s"></param>
        public static void FillQuestions(Survey s, bool clearBeforeFill = false)
        {
            if (clearBeforeFill)
            {
                s.Questions.Clear();
            }

            SurveyQuestion q;
            string         query = "SELECT * FROM Questions.FN_GetSurveyQuestions(@SID) ORDER BY Qnum";

            using (SqlDataAdapter sql = new SqlDataAdapter())
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISISConnectionString"].ConnectionString))
                {
                    conn.Open();

                    sql.SelectCommand = new SqlCommand(query, conn);
                    sql.SelectCommand.Parameters.AddWithValue("@SID", s.SID);
                    try
                    {
                        using (SqlDataReader rdr = sql.SelectCommand.ExecuteReader())
                        {
                            while (rdr.Read())
                            {
                                q = new SurveyQuestion
                                {
                                    ID         = (int)rdr["ID"],
                                    SurveyCode = (string)rdr["Survey"],
                                    // VarName = (string)rdr["VarName"],
                                    Qnum = (string)rdr["Qnum"],
                                    //PreP = new Wording((int)rdr["PreP#"], (string)rdr["PreP"]),
                                    PrePNum = (int)rdr["PreP#"],
                                    //PreP = (string)rdr["PreP"],
                                    PreINum = (int)rdr["PreI#"],
                                    // PreI = (string)rdr["PreI"],
                                    PreANum = (int)rdr["PreA#"],
                                    //PreA = (string)rdr["PreA"],
                                    LitQNum = (int)rdr["LitQ#"],
                                    // LitQ = (string)rdr["LitQ"],
                                    PstINum = (int)rdr["PstI#"],
                                    // PstI = (string)rdr["PstI"],
                                    PstPNum = (int)rdr["PstP#"],
                                    // PstP = (string)rdr["PstP"],
                                    RespName    = (string)rdr["RespName"],
                                    RespOptions = (string)rdr["RespOptions"],
                                    NRName      = (string)rdr["NRName"],
                                    NRCodes     = (string)rdr["NRCodes"],

                                    VarName = new VariableName((string)rdr["VarName"])
                                    {
                                        VarLabel = (string)rdr["VarLabel"],
                                        Domain   = new DomainLabel((int)rdr["DomainNum"], (string)rdr["Domain"]),
                                        Topic    = new TopicLabel((int)rdr["TopicNum"], (string)rdr["Topic"]),
                                        Content  = new ContentLabel((int)rdr["ContentNum"], (string)rdr["Content"]),
                                        Product  = new ProductLabel((int)rdr["ProductNum"], (string)rdr["Product"])
                                    },
                                    TableFormat   = (bool)rdr["TableFormat"],
                                    CorrectedFlag = (bool)rdr["CorrectedFlag"],
                                    NumCol        = (int)rdr["NumCol"],
                                    NumDec        = (int)rdr["NumDec"],
                                    VarType       = (string)rdr["VarType"],
                                    ScriptOnly    = (bool)rdr["ScriptOnly"]
                                };

                                if (!rdr.IsDBNull(rdr.GetOrdinal("AltQnum")))
                                {
                                    q.AltQnum = (string)rdr["AltQnum"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("AltQnum2")))
                                {
                                    q.AltQnum2 = (string)rdr["AltQnum2"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("AltQnum3")))
                                {
                                    q.AltQnum3 = (string)rdr["AltQnum3"];
                                }

                                if (!rdr.IsDBNull(rdr.GetOrdinal("PreP")))
                                {
                                    q.PreP = (string)rdr["PreP"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("PreI")))
                                {
                                    q.PreI = (string)rdr["PreI"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("PreA")))
                                {
                                    q.PreA = (string)rdr["PreA"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("LitQ")))
                                {
                                    q.LitQ = (string)rdr["LitQ"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("PstI")))
                                {
                                    q.PstI = (string)rdr["PstI"];
                                }
                                if (!rdr.IsDBNull(rdr.GetOrdinal("PstP")))
                                {
                                    q.PstP = (string)rdr["PstP"];
                                }

                                s.AddQuestion(q);
                            }
                        }
                    }
                    catch (Exception)
                    {
                        return;
                    }
                }
        }
        /// <summary>
        /// Returns a list of questions from a backup database.
        /// </summary>
        /// <remarks>
        /// This could be achieved by changing the FROM clause in GetSurveyTable but often there are columns that don't exist in the backups, due to
        /// their age and all the changes that have happened to the database over the years.
        /// </remarks>
        public static List <SurveyQuestion> GetBackupQuestions(Survey s, DateTime backup)
        {
            List <SurveyQuestion> qs = new List <SurveyQuestion>();
            SurveyQuestion        q;
            DataTable             rawTable;
            //string filePath = backup.ToString("yyyy-MM-dd") + ".7z";
            BackupConnection bkp    = new BackupConnection(backup);
            string           select = "SELECT tblSurveyNumbers.[ID], [Qnum] AS SortBy, [Survey], tblSurveyNumbers.[VarName], refVarName, Qnum, AltQnum, CorrectedFlag, TableFormat, tblDomain.ID AS DomainNum, tblDomain.[Domain], " +
                                      "tblTopic.ID AS TopicNum, [Topic], tblContent.ID AS ContentNum, [Content], VarLabel, tblProduct.ID AS ProductNum, [Product], PreP, [PreP#], PreI, [PreI#], PreA, [PreA#], LitQ, [LitQ#], PstI, [PstI#], PstP, [PstP#], RespOptions, tblSurveyNumbers.RespName, NRCodes, tblSurveyNumbers.NRName ";

            string where = "Survey = '" + s.SurveyCode + "'";


            if (bkp.Connected)
            {
                Console.Write("unzipped");
                rawTable = bkp.GetSurveyTable(select, where);
            }
            else
            {
                // could not unzip backup/7zip not installed etc.
                return(null);
            }

            foreach (DataRow r in rawTable.Rows)
            {
                q = new SurveyQuestion();

                q.ID                  = (int)r["ID"];
                q.SurveyCode          = (string)r["Survey"];
                q.VarName.FullVarName = (string)r["VarName"];

                q.Qnum = (string)r["Qnum"];
                if (!DBNull.Value.Equals(r["AltQnum"]))
                {
                    q.AltQnum = (string)r["AltQnum"];
                }
                //q.PreP = new Wording(Convert.ToInt32(r["PreP#"]), (string)r["PreP"]);
                q.PrePNum = Convert.ToInt32(r["PreP#"]);
                q.PreP    = r["PreP"].Equals(DBNull.Value) ? "" : (string)r["PreP"];
                q.PreINum = Convert.ToInt32(r["PreI#"]);
                q.PreI    = r["PreI"].Equals(DBNull.Value) ? "" : (string)r["PreI"];
                q.PreANum = Convert.ToInt32(r["PreA#"]);
                q.PreA    = r["PreA"].Equals(DBNull.Value) ? "" : (string)r["PreA"];
                q.LitQNum = Convert.ToInt32(r["LitQ#"]);
                q.LitQ    = r["LitQ"].Equals(DBNull.Value) ? "" : (string)r["LitQ"];
                q.PstINum = Convert.ToInt32(r["PstI#"]);
                if (DBNull.Value.Equals(r["PstI"]))
                {
                    q.PstI = "";
                }
                else
                {
                    q.PstI = (string)r["PstI"];
                }
                q.PstPNum     = Convert.ToInt32(r["PstP#"]);
                q.PstP        = r["PstP"].Equals(DBNull.Value) ? "" : (string)r["PstP"];
                q.RespName    = (string)r["RespName"];
                q.RespOptions = r["RespOptions"].Equals(DBNull.Value) ? "" : (string)r["RespOptions"];
                q.NRName      = (string)r["NRName"];
                q.NRCodes     = r["NRCodes"].Equals(DBNull.Value) ? "" : (string)r["NRCodes"];

                q.VarName = new VariableName((string)r["VarName"])
                {
                    VarLabel = (string)r["VarLabel"],
                    Domain   = new DomainLabel((int)r["DomainNum"], (string)r["Domain"]),
                    Topic    = new TopicLabel((int)r["TopicNum"], (string)r["Topic"]),
                    Content  = new ContentLabel((int)r["ContentNum"], (string)r["Content"]),
                    Product  = new ProductLabel((int)r["ProductNum"], (string)r["Product"])
                };

                q.TableFormat   = (bool)r["TableFormat"];
                q.CorrectedFlag = (bool)r["CorrectedFlag"];

                qs.Add(q);
            }

            return(qs);
        }
Example #6
0
        /// <summary>
        /// Returns a Survey object with the provided survey code.
        /// </summary>
        /// <param name="code">A valid survey code. Null is returned if the survey code is not found in the database.</param>
        /// <returns></returns>
        public static Survey GetSurveyInfo(string code)
        {
            Survey s;
            string query = "SELECT * FROM Surveys.FN_GetSurveyInfo (@survey)";

            using (SqlDataAdapter sql = new SqlDataAdapter())
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISISConnectionString"].ConnectionString))
                {
                    conn.Open();

                    sql.SelectCommand = new SqlCommand(query, conn);
                    sql.SelectCommand.Parameters.AddWithValue("@survey", code);
                    try
                    {
                        using (SqlDataReader rdr = sql.SelectCommand.ExecuteReader())
                        {
                            rdr.Read();
                            s = new Survey
                            {
                                SID              = (int)rdr["ID"],
                                SurveyCode       = (string)rdr["Survey"],
                                SurveyCodePrefix = (string)rdr["ISO_Code"],
                                Title            = (string)rdr["SurveyTitle"],
                                CountryCode      = (string)rdr["CountryCode"],
                                Locked           = (bool)rdr["Locked"],
                                EnglishRouting   = (bool)rdr["EnglishRouting"],
                                HideSurvey       = (bool)rdr["HideSurvey"],
                                ReRun            = (bool)rdr["ReRun"],
                                NCT              = (bool)rdr["NCT"]
                            };

                            // language
                            if (!rdr.IsDBNull(rdr.GetOrdinal("Languages")))
                            {
                                s.Languages = (string)rdr["Languages"];
                            }

                            // group
                            if (!rdr.IsDBNull(rdr.GetOrdinal("GroupName")))
                            {
                                s.Group = new SurveyUserGroup((int)rdr["GroupID"], (string)rdr["GroupName"]);
                            }
                            else
                            {
                                s.Group = new SurveyUserGroup();
                            }

                            // cohort
                            if (!rdr.IsDBNull(rdr.GetOrdinal("Cohort")))
                            {
                                s.Cohort = new SurveyCohort((int)rdr["CohortID"], (string)rdr["Cohort"]);
                                if (!rdr.IsDBNull(rdr.GetOrdinal("CohortCode")))
                                {
                                    s.Cohort.Code = (string)rdr["CohortCode"];
                                }
                                else
                                {
                                    s.Cohort.Code = "";
                                }
                            }

                            // mode
                            if (!rdr.IsDBNull(rdr.GetOrdinal("Mode")))
                            {
                                s.Mode = new SurveyMode((int)rdr["Mode"], (string)rdr["ModeLong"], (string)rdr["ModeAbbrev"]);
                            }
                        }
                    }
                    catch (Exception)
                    {
                        return(null);
                    }
                }

            // check for corrected wordings
            s.HasCorrectedWordings = HasCorrectedWordings(s.SurveyCode);

            return(s);
        }
Example #7
0
        /// <summary>
        /// Constructor for copying a base version of a survey.
        /// </summary>
        /// <param name="s"></param>
        public ReportSurvey(Survey s)
        {
            // copy values from base
            SID            = s.SID;
            SurveyCode     = s.SurveyCode;
            Title          = s.Title;
            Languages      = s.Languages;
            Group          = s.Group;
            Mode           = s.Mode;
            CountryCode    = s.CountryCode;
            WebName        = s.WebName;
            EnglishRouting = s.EnglishRouting;

            EssentialList        = s.EssentialList;
            HasCorrectedWordings = s.HasCorrectedWordings;
            AddQuestions(s.Questions);
            CorrectedQuestions = s.CorrectedQuestions;

            // initialize derived properties
            Backend = DateTime.Today;

            Prefixes = new List <string>();
            Varnames = new List <VariableName>();
            Headings = new List <Heading>();
            Products = new List <ProductLabel>();

            //CommentDate =  new DateTime(2000, 1, 1);
            CommentAuthors = new List <int>();
            CommentSources = new List <string>();

            RepeatedFields = new List <string>();
            CommentFields  = new List <string>();
            TransFields    = new List <string>();

            StdFields = new List <string>
            {
                "PreP",
                "PreI",
                "PreA",
                "LitQ",
                "RespOptions",
                "NRCodes",
                "PstI",
                "PstP"
            };

            StdFieldsChosen = new List <string>
            {
                "PreP",
                "PreI",
                "PreA",
                "LitQ",
                "RespOptions",
                "NRCodes",
                "PstI",
                "PstP"
            };

            RoutingFormat = RoutingStyle.Normal;

            VarChanges = new List <VarNameChange>();
        }