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