/// <summary> /// Returns comments for the specified question. /// </summary> /// <param name="QID"></param> /// <returns></returns> public static List <QuestionComment> GetQuesCommentsByQID(int QID) { List <QuestionComment> comments = new List <QuestionComment>(); QuestionComment c; string query = "SELECT * FROM Comments.FN_GetQuesCommentsByQID(@qid)"; 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("@qid", QID); 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"]; } comments.Add(c); } } } catch (Exception) { return(comments); } } return(comments); }
// // Deleted Question Comments // /// <summary> /// Returns all question comments with the specified note. /// </summary> /// <param name="CID"></param> /// <returns></returns> public static List <QuestionComment> GetDeletedComments(string survey, string varname) { List <QuestionComment> cs = new List <QuestionComment>(); QuestionComment c; string query = "SELECT * FROM Comments.FN_GetDeletedComments (@survey, @varname)"; 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", survey); sql.SelectCommand.Parameters.AddWithValue("@varname", varname); try { using (SqlDataReader rdr = sql.SelectCommand.ExecuteReader()) { while (rdr.Read()) { c = new QuestionComment { Notes = new Note((int)rdr["ID"], (string)rdr["Notes"]), Survey = (string)rdr["Survey"], VarName = (string)rdr["VarName"], CID = (int)rdr["CID"], NoteDate = (DateTime)rdr["NoteDate"], NoteInit = (int)rdr["NoteInit"], Name = (string)rdr["Name"], SourceName = (string)rdr["SourceName"], NoteType = (string)rdr["NoteType"], Source = (string)rdr["Source"], }; cs.Add(c); } } } catch (Exception) { int i = 0; } } return(cs); }
/// <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(ReportSurvey s) { 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 (s.CommentFields != null && s.CommentFields.Count != 0) { query += " AND ("; for (int i = 0; i < s.CommentFields.Count; i++) { sql.SelectCommand.Parameters.AddWithValue("@commentTypes" + i, s.CommentFields[i]); query += " NoteType = @commentTypes" + i + " OR "; } query = Utilities.TrimString(query, " OR "); query += ")"; } if (s.CommentDate != null) { sql.SelectCommand.Parameters.AddWithValue("@commentDate", s.CommentDate.Value); query += " AND NoteDate >= @commentDate"; } if (s.CommentAuthors != null && s.CommentAuthors.Count != 0) { query += " AND ("; for (int i = 0; i < s.CommentAuthors.Count; i++) { sql.SelectCommand.Parameters.AddWithValue("@commentAuthors" + i, s.CommentAuthors[i]); query += " NoteInit = @commentAuthors" + i + " OR "; } query = Utilities.TrimString(query, " OR "); query += ")"; } if (s.CommentSources != null && s.CommentSources.Count != 0) { query += " AND ("; for (int i = 0; i < s.CommentSources.Count; i++) { sql.SelectCommand.Parameters.AddWithValue("@commentSources" + i, s.CommentSources[i]); query += " SourceName = @commentSources" + i + " OR "; } query = Utilities.TrimString(query, " OR "); query += ")"; } if (s.CommentText != null) { query += " AND "; sql.SelectCommand.Parameters.AddWithValue("@commentText", s.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; } } }
// TODO replace with server function /// <summary> /// /// </summary> /// <param name="SurvID"></param> /// <param name="commentTypes"></param> /// <param name="commentDate"></param> /// <param name="commentAuthors"></param> /// <param name="commentSources"></param> /// <returns></returns> public static List <QuestionComment> GetQuesCommentsBySurvey(int SurvID, List <string> commentTypes = null, DateTime?commentDate = null, List <int> commentAuthors = null, List <string> commentSources = null) { List <QuestionComment> cs = new List <QuestionComment>(); 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(query, conn); sql.SelectCommand.Parameters.AddWithValue("@sid", SurvID); if (commentTypes != null && commentTypes.Count != 0) { sql.SelectCommand.Parameters.AddWithValue("@commentTypes", String.Join(",", commentTypes)); query += " AND NoteType IN (@commentTypes)"; } if (commentDate != null) { sql.SelectCommand.Parameters.AddWithValue("@commentDate", commentDate); query += " AND NoteDate >= (@commentDate)"; } if (commentAuthors != null && commentAuthors.Count != 0) { sql.SelectCommand.Parameters.AddWithValue("@commentAuthors", String.Join(",", commentAuthors)); query += " AND NoteInit IN (@commentAuthors)"; } if (commentSources != null && commentSources.Count != 0) { sql.SelectCommand.Parameters.AddWithValue("@commentSources", String.Join(",", commentSources)); query += " AND Source IN (@commentSources)"; } 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"], SourceName = (string)rdr["SourceName"], NoteType = (string)rdr["NoteType"], ShortNoteType = (string)rdr["ShortForm"], Source = (string)rdr["Source"], SurvID = (int)rdr["SurvID"] }; cs.Add(c); } } } catch (Exception) { return(null); } } return(cs); }