//
// Fill Methods
//
/// <summary>
/// Populates the provided Survey's questions with translations.
/// </summary>
/// <param name="s"></param>
/// <param name="language"></param>
public static void FillTranslationsBySurvey(Survey s, string language)
{
Translation t;
string query = "SELECT * FROM Translations.FN_GetSurveyTranslations(@sid, @language)";
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);
sql.SelectCommand.Parameters.AddWithValue("@language", language);
try
{
using (SqlDataReader rdr = sql.SelectCommand.ExecuteReader())
{
while (rdr.Read())
{
t = new Translation
{
ID = (int)rdr["ID"],
QID = (int)rdr["QID"],
Language = (string)rdr["Lang"],
TranslationText = (string)rdr["Translation"],
Bilingual = (bool)rdr["Bilingual"]
};
s.QuestionByID(t.QID).Translations.Add(t);
}
}
}
catch (Exception)
{
return;
}
}
}
//
// 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;
}
}
}
