/// <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);
}
