/// <summary>
/// Saves Qnum field for a specified question. USES TEST BACKEND.
/// </summary>
/// <param name="sq"></param>
/// <returns></returns>
public static int UpdateQnum(SurveyQuestion sq)
{
using (SqlDataAdapter sql = new SqlDataAdapter())
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISISConnectionString"].ConnectionString))
{
conn.Open();
sql.UpdateCommand = new SqlCommand("proc_updateQnum", conn)
{
CommandType = CommandType.StoredProcedure
};
sql.UpdateCommand.Parameters.AddWithValue("@newqnum", sq.Qnum);
sql.UpdateCommand.Parameters.AddWithValue("@qid", sq.ID);
try
{
sql.UpdateCommand.ExecuteNonQuery();
}
catch (Exception)
{
return(1);
}
}
return(0);
}
/// <summary>
/// Returns the variable name immediately following the provided heading question.
/// </summary>
/// <param name="sq"></param>
/// <returns></returns>
public string GetSectionLowerBound(SurveyQuestion sq)
{
if (!sq.VarName.FullVarName.StartsWith("Z"))
{
return(sq.VarName.FullVarName);
}
int index = 0;
for (int i = 0; i < Questions.Count; i++)
{
if (Questions[i].VarName.Equals(sq.VarName))
{
index = i;
break;
}
}
// if this heading is the last question, return nothing
if (index == Questions.Count)
{
return("");
}
// if a heading is the next question return this Varname
if (Questions[index + 1].VarName.FullVarName.StartsWith("Z"))
{
return(sq.VarName.FullVarName);
}
return(Questions[index + 1].VarName.FullVarName);
}
/// <summary>
/// Updates the wording numbers for the provided question. USES TEST BACKEND
/// </summary>
/// <param name="question"></param>
/// <returns></returns>
public static int UpdateQuestionWordings(SurveyQuestion question)
{
using (SqlDataAdapter sql = new SqlDataAdapter())
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISISConnectionString"].ConnectionString))
{
conn.Open();
sql.UpdateCommand = new SqlCommand("proc_updateQuestionWordings", conn)
{
CommandType = CommandType.StoredProcedure
};
sql.UpdateCommand.Parameters.AddWithValue("@QID", question.ID);
sql.UpdateCommand.Parameters.AddWithValue("@prep", question.PrePNum);
sql.UpdateCommand.Parameters.AddWithValue("@prei", question.PreINum);
sql.UpdateCommand.Parameters.AddWithValue("@prea", question.PreANum);
sql.UpdateCommand.Parameters.AddWithValue("@litq", question.LitQNum);
sql.UpdateCommand.Parameters.AddWithValue("@psti", question.PstINum);
sql.UpdateCommand.Parameters.AddWithValue("@pstp", question.PstPNum);
sql.UpdateCommand.Parameters.AddWithValue("@respname", question.RespName);
sql.UpdateCommand.Parameters.AddWithValue("@nrname", question.NRName);
try
{
sql.UpdateCommand.ExecuteNonQuery();
}
catch (Exception)
{
return(1);
}
}
return(0);
}
public SurveyQuestion DeepCopyWordings()
{
SurveyQuestion copy = new SurveyQuestion();
copy.VarName = new VariableName(VarName.FullVarName);
copy.PreP = string.Copy(PreP);
copy.PreI = string.Copy(PreI);
copy.PreA = string.Copy(PreA);
copy.LitQ = string.Copy(LitQ);
copy.PstI = string.Copy(PstI);
copy.PstP = string.Copy(PstP);
copy.RespOptions = string.Copy(RespOptions);
copy.NRCodes = string.Copy(NRCodes);
foreach (Translation t in Translations)
{
copy.Translations.Add(new Translation
{
ID = t.ID,
QID = t.QID,
Language = string.Copy(t.Language),
TranslationText = string.Copy(t.TranslationText),
Bilingual = t.Bilingual
});
}
return(copy);
}
/// <summary>
/// Adds a question to the survey's question list.
/// </summary>
/// <param name="newQ"></param>
public void AddQuestion(SurveyQuestion newQ)
{
if (!Questions.Contains(newQ, new SurveyQuestionComparer()))
{
Questions.Add(newQ);
UpdateEssentialQuestions();
}
}
/// <summary>
/// Adds a question to the survey's question list at the specified location.
/// </summary>
/// <param name="newQ"></param>
public void AddQuestion(SurveyQuestion newQ, int afterIndex, bool withRenumber)
{
Questions.Insert(afterIndex, newQ);
if (withRenumber)
{
Renumber(0);
}
UpdateEssentialQuestions();
}
/// <summary>
/// Removes the question from the Survey's question list.
/// </summary>
/// <param name="newQ"></param>
public void RemoveQuestion(SurveyQuestion q, bool withRenumber)
{
Questions.Remove(q);
if (withRenumber)
{
Renumber(0);
}
UpdateEssentialQuestions();
}
/// <summary>
/// Inserts LITQ and TBLROS/E tags around the LitQ and Response sets in the translation text. Only works on series starters (Qnum ends in 'a')
/// </summary>
/// <param name="sq"></param>
public void InsertTranslationTableTags(SurveyQuestion sq)
{
if (!sq.Qnum.EndsWith("a"))
{
return;
}
Regex rx = new Regex("[0-9][0-9]* ");
MatchCollection matches;
string litq, currentChar;
int litqPos = 0, index = 0;
foreach (Translation t in sq.Translations)
{
matches = rx.Matches(t.TranslationText);
if (matches.Count == 1)
{
t.TranslationText = t.TranslationText.Substring(0, matches[0].Index) + "[/LitQ][TBLROS]" + t.TranslationText.Substring(matches[0].Index + 1) + "[TBLROE]";
}
if (string.IsNullOrEmpty(t.LitQ))
{
litq = sq.LitQ;
}
else
{
litq = t.LitQ;
}
index = 0;
do
{
if (index > litq.Length)
{
break;
}
currentChar = litq.Substring(index, 1);
if (currentChar.Equals(".") || currentChar.Equals("/") || currentChar.Equals("\r") || currentChar.Equals("\n") || currentChar.Equals(":") || currentChar.Equals("?") ||
string.IsNullOrEmpty(currentChar))
{
break;
}
index++;
}while (true);
litq = litq.Substring(0, index - 1);
litqPos = t.TranslationText.IndexOf(litq, StringComparison.OrdinalIgnoreCase);
if (litqPos > 0)
{
t.TranslationText = t.TranslationText.Substring(0, litqPos - 1) + "[LitQ]" + t.TranslationText.Substring(litqPos);
}
}
}
/// <summary>
/// Adds a question to the survey's question list.
/// </summary>
/// <param name="newQ"></param>
public void AddQuestion(SurveyQuestion newQ, bool withRenumber)
{
Questions.Add(newQ);
if (withRenumber)
{
Renumber(0);
}
UpdateEssentialQuestions();
}
/// <summary>
/// Searches a string for a VarName pattern and, if found, looks up and inserts the Qnum right before the VarName. If a Qnum can not be found,
/// "QNU" (Qnum Unknown) is used instead.
/// </summary>
/// <remarks> Each word in the string is compared to the VarName pattern.</remarks>
/// <param name="wording"></param>
/// <param name="numbering"></param>
/// <returns></returns>
private string InsertQnums(string wording, Enumeration numbering)
{
int offset = 0;
string qnum = "";
string foundVarname = "";
MatchCollection matches;
Regex rx = new Regex("\\b[A-Z]{2}[0-9]{3}[a-z]*", RegexOptions.IgnoreCase);
// if wording contains a variable name, look up the qnum and place it before the variable
if (rx.Match(wording).Success)
{
matches = rx.Matches(wording);
foreach (Match match in matches)
{
// for each found pattern, replace the found variable once, starting at the position in the string where it was found
// an offset is added to this position to account for previous insertions
foundVarname = match.Groups[0].Value;
SurveyQuestion foundQ = Questions.SingleOrDefault(x => x.VarName.RefVarName == foundVarname);
if (foundQ == null)
{
qnum = "QNU";
}
else
{
switch (numbering)
{
case Enumeration.Both:
case Enumeration.Qnum:
qnum = foundQ.Qnum;
break;
case Enumeration.AltQnum:
qnum = foundQ.AltQnum;
break;
default:
qnum = foundQ.Qnum;
break;
}
}
wording = rx.Replace(wording, qnum + "/" + foundVarname, 1, match.Index + offset);
offset += qnum.Length + 1; // offset the starting point but the length of the qnum and slash just inserted
}
if (qnum.Equals("QNU"))
{
QNUlist.Add(foundVarname);
}
offset = 0;
}
return(wording);
}
/// <summary>
/// Populates the provided Survey's corrected questions list.
/// </summary>
/// <param name="s"></param>
/// <param name="withComments"></param>
/// <param name="withTranslation"></param>
public static void FillCorrectedQuestions(Survey s)
{
SurveyQuestion q;
string query = "SELECT * FROM Questions.FN_GetCorrectedQuestions(@survey) 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("@survey", s.SurveyCode);
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"],
//AltQnum = (string)rdr["AltQnum"],
//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"]
};
s.CorrectedQuestions.Add(q);
}
}
}
catch (Exception)
{
return;
}
}
}
/// <summary>
/// Compares each wording field between 2 questions and determines if they are equal.
/// </summary>
/// <param name="sq1"></param>
/// <param name="sq2"></param>
/// <returns>True if all wording fields are equal.</returns>
private bool AreWordingsEqual(SurveyQuestion sq1, SurveyQuestion sq2)
{
return((sq1.PreP == sq2.PreP) &&
(sq1.PreI == sq2.PreI) &&
(sq1.PreA == sq2.PreA) &&
(sq1.LitQ == sq2.LitQ) &&
(sq1.PstI == sq2.PstI) &&
(sq1.PstP == sq2.PstP) &&
(sq1.NRCodes == sq2.NRCodes) &&
(sq1.RespOptions == sq2.RespOptions));
}
/// <summary>
/// Modifies each wording field in a single SurveyQuestion object to include the country code for any standard variable name found in the wording.
/// </summary>
/// <param name="sq">The question to modify.</param>
public void InsertCountryCodes(SurveyQuestion sq)
{
sq.PreP = InsertCountryCodes(sq.PreP);
sq.PreI = InsertCountryCodes(sq.PreI);
sq.PreA = InsertCountryCodes(sq.PreA);
sq.LitQ = InsertCountryCodes(sq.LitQ);
sq.PstI = InsertCountryCodes(sq.PstI);
sq.PstP = InsertCountryCodes(sq.PstP);
sq.RespOptions = InsertCountryCodes(sq.RespOptions);
sq.NRCodes = InsertCountryCodes(sq.NRCodes);
}
/// <summary>
/// Modifies each wording field in a single SurveyQuestion object to include Qnums before any non-standard variable name found in the wording.
/// </summary>
/// <param name="numbering">Determines whether Qnum or AltQnum is inserted.</param>
/// <param name="sq">The question to modify.</param>
public void InsertOddQnums(SurveyQuestion sq, Enumeration numbering)
{
sq.PreP = InsertOddQnums(sq.PreP, numbering);
sq.PreI = InsertOddQnums(sq.PreI, numbering);
sq.PreA = InsertOddQnums(sq.PreA, numbering);
sq.LitQ = InsertOddQnums(sq.LitQ, numbering);
sq.PstI = InsertOddQnums(sq.PstI, numbering);
sq.PstP = InsertOddQnums(sq.PstP, numbering);
sq.RespOptions = InsertOddQnums(sq.RespOptions, numbering);
sq.NRCodes = InsertOddQnums(sq.NRCodes, numbering);
}
public static int InsertQuestion(string surveyCode, SurveyQuestion question)
{
using (SqlDataAdapter sql = new SqlDataAdapter())
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISISConnectionString"].ConnectionString))
{
conn.Open();
//
}
return(1);
}
/// <summary>
///
/// </summary>
/// <param name="sq"></param>
public void FormatRouting(SurveyQuestion sq)
{
QuestionRouting qr;
// format the response options
qr = new QuestionRouting(sq.PstP, sq.RespOptions);
sq.RespOptions = qr.ToString();
// format the non-response options
qr = new QuestionRouting(sq.PstP, sq.NRCodes);
sq.NRCodes = qr.ToString();
// format the PstP
sq.PstP = string.Join("\r\n", qr.RoutingText);
}
/// <summary>
/// Returns a string that contains all the Survey Codes that match the provided SurveyQuestion.
/// </summary>
/// <param name="cq"></param>
/// <returns></returns>
private string GetSurveyList(SurveyQuestion cq)
{
//string list = "";
List <string> list = new List <string>();
foreach (SurveyQuestion sq in questions)
{
if (HarmonyMatch(sq, cq))
{
list.Add(sq.SurveyCode);
}
}
list.Sort();
return(String.Join(", ", list.ToArray()));
}
public SurveyQuestion Copy()
{
SurveyQuestion sq;
sq = new SurveyQuestion
{
//VarName = this.VarName,
VarName = VarName,
//RefVarName = RefVarName,
Qnum = Qnum,
AltQnum = AltQnum,
AltQnum2 = AltQnum2,
AltQnum3 = AltQnum3,
PreviousNameList = PreviousNameList,
//PreP = new Wording(this.PreP.ID, this.PreP.WordingText),
PrePNum = PrePNum,
PreP = PreP,
PreINum = PreINum,
PreI = PreI,
PreANum = PreANum,
PreA = PreA,
LitQNum = LitQNum,
LitQ = LitQ,
PstINum = PstINum,
PstI = PstI,
PstPNum = PstPNum,
PstP = PstP,
RespName = RespName,
RespOptions = RespOptions,
NRName = NRName,
NRCodes = NRCodes,
NumCol = NumCol,
NumDec = NumDec,
NumFmt = NumFmt,
VarType = VarType,
ScriptOnly = ScriptOnly,
TableFormat = TableFormat,
CorrectedFlag = CorrectedFlag
};
return(sq);
}
/// <summary>
/// Compares each translation field of the same language between 2 questions and determines if they are equal.
/// </summary>
/// <param name="sq1"></param>
/// <param name="sq2"></param>
/// <returns></returns>
private bool AreTranslationsEqual(SurveyQuestion sq1, SurveyQuestion sq2)
{
foreach (Translation t1 in sq1.Translations)
{
Translation t2 = sq2.GetTranslation(t1.Language);
if (t2 == null)
{
return(false);
}
else
{
if (t1.TranslationText == t2.TranslationText)
{
return(true);
}
}
}
return(false);
}
/// <summary>
/// Apply corrected wordings to the questions list. Overwrites the wording fields in the questions list with those found in the correctedQuestions list.
/// </summary>
public void CorrectWordings()
{
foreach (SurveyQuestion cq in CorrectedQuestions)
{
try
{
SurveyQuestion sq = Questions.Single(x => x.ID == cq.ID);
sq.PreP = cq.PreP;
sq.PreI = cq.PreI;
sq.PreA = cq.PreA;
sq.LitQ = cq.LitQ;
sq.PstI = cq.PstI;
sq.PstP = cq.PstP;
sq.RespOptions = cq.RespOptions;
sq.NRCodes = cq.NRCodes;
}
catch (ArgumentNullException)
{
}
}
}
/// <summary>
/// Determines the type of questions for the given row.
/// </summary>
/// <param name="row"></param>
/// <returns>QuestionType enum based on the Qnum and VarName.</returns>
public static QuestionType GetQuestionType(SurveyQuestion q)
{
string qnum = q.Qnum;
string varname = q.VarName.FullVarName;
int head = Int32.Parse(GetSeriesQnum(qnum));
string tail = GetQnumSuffix(qnum);
QuestionType qType;
// get Question Type
if (varname.StartsWith("Z"))
{
if (varname.EndsWith("s"))
{
qType = QuestionType.Subheading; // subheading
}
else
{
qType = QuestionType.Heading; // heading
}
}
else if (varname.StartsWith("HG"))
{
qType = QuestionType.Standalone; // QuestionType.InterviewerNote; // interviewer note
}
else
{
if ((tail == "" || tail == "a") && (head != 0))
{
qType = QuestionType.Standalone; // standalone or first in series
}
else
{
qType = QuestionType.Series; // series
}
}
return(qType);
}
/// <summary>
/// Looks for any refVarName in the wording and places it's Qnum or AltQnum before the refVarName. This method does not detect unknown VarNames.
/// </summary>
/// <param name="wording"></param>
/// <param name="numbering"></param>
/// <returns></returns>
private string InsertAllQnums(string wording, Enumeration numbering)
{
foreach (SurveyQuestion sq in Questions)
{
Regex rxReplace = new Regex("\\b" + sq.VarName.RefVarName + "\\b", RegexOptions.IgnoreCase);
// if words[i] contains a variable name, look up the qnum and place it before the variable
if (rxReplace.Match(wording).Success)
{
SurveyQuestion foundQ = Questions.SingleOrDefault(x => x.VarName.RefVarName == sq.VarName.RefVarName);
string qnum;
switch (numbering)
{
case Enumeration.Both:
case Enumeration.Qnum:
qnum = foundQ.Qnum;
break;
case Enumeration.AltQnum:
qnum = foundQ.AltQnum;
break;
default:
qnum = foundQ.Qnum;
break;
}
if (qnum.Equals(""))
{
QNUlist.Add(sq.VarName.RefVarName);
qnum = "QNU";
}
wording = rxReplace.Replace(wording, qnum + "/" + sq.VarName.RefVarName);
}
}
return(wording);
}
/// <summary>
/// Returns the variable name immediately preceding the heading that follows the provided heading question.
/// </summary>
/// <param name="sq"></param>
/// <returns></returns>
public string GetSectionUpperBound(SurveyQuestion sq)
{
if (!sq.VarName.FullVarName.StartsWith("Z"))
{
return(sq.VarName.FullVarName);
}
int index = 0;
bool inSection = false;
for (int i = 0; i < Questions.Count; i++)
{
if (Questions[i].VarName.Equals(sq.VarName))
{
inSection = true;
continue;
}
if (Questions[i].VarName.FullVarName.StartsWith("Z") && inSection)
{
index = i - 1;
break;
}
}
// next heading not found, so we must be looking for the end of the survey
if (index == 0)
{
return(Questions[Questions.Count - 1].VarName.FullVarName);
}
// if the next heading is the next question return nothing
if (Questions[index].VarName.Equals(sq.VarName))
{
return("");
}
return(Questions[index].VarName.FullVarName);
}
/// <summary>
/// Returns comments for the specified question.
/// </summary>
/// <param name="QID"></param>
/// <returns></returns>
public static List <QuestionComment> GetQuesCommentsByQID(SurveyQuestion question)
{
return(GetQuesCommentsByQID(question.ID));
}
//
// 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>
/// Removes the question from the Survey's question list.
/// </summary>
/// <param name="newQ"></param>
public void RemoveQuestion(SurveyQuestion q)
{
Questions.Remove(q);
UpdateEssentialQuestions();
}
/// <summary>
/// Return a string that contains the values of the match fields.
/// </summary>
/// <param name="sq"></param>
/// <returns></returns>
private string GetGroupByFields(SurveyQuestion sq)
{
List <string> matchFieldValues = new List <string>();
foreach (string s in matchFields)
{
if (s.Equals("PreP"))
{
matchFieldValues.Add(Convert.ToString(sq.PrePNum));
}
if (s.Equals("PreI"))
{
matchFieldValues.Add(Convert.ToString(sq.PreINum));
}
if (s.Equals("PreA"))
{
matchFieldValues.Add(Convert.ToString(sq.PreANum));
}
if (s.Equals("LitQ"))
{
matchFieldValues.Add(Convert.ToString(sq.LitQNum));
}
if (s.Equals("PstI"))
{
matchFieldValues.Add(Convert.ToString(sq.PstINum));
}
if (s.Equals("PstP"))
{
matchFieldValues.Add(Convert.ToString(sq.PstPNum));
}
if (s.Equals("RespOptions"))
{
matchFieldValues.Add(sq.RespName);
}
if (s.Equals("NRCodes"))
{
matchFieldValues.Add(sq.NRName);
}
if (s.Equals("Domain"))
{
matchFieldValues.Add(sq.VarName.Domain.LabelText);
}
if (s.Equals("Topic"))
{
matchFieldValues.Add(sq.VarName.Topic.LabelText);
}
if (s.Equals("Content"))
{
matchFieldValues.Add(sq.VarName.Content.LabelText);
}
if (s.Equals("Product"))
{
matchFieldValues.Add(sq.VarName.Product.LabelText);
}
if (s.Equals("VarLabel"))
{
matchFieldValues.Add(sq.VarName.VarLabel);
}
if (s.Equals("Translation"))
{
matchFieldValues.Add(Lang);
}
}
return(String.Join(", ", matchFieldValues));
}
/// <summary>
/// Returns true if the provided SurveyQuestion objects are equal in terms of refVarName and the match fields.
/// </summary>
/// <param name="sq1"></param>
/// <param name="sq2"></param>
/// <returns></returns>
public bool HarmonyMatch(SurveyQuestion sq1, SurveyQuestion sq2)
{
bool prepMatch = false, preiMatch = false, preaMatch = false, litqMatch = false, pstiMatch = false, pstpMatch = false, roMatch = false, nrMatch = false;
bool tranMatch = false;
if (!matchFields.Contains("PreP"))
{
prepMatch = true;
}
if (!matchFields.Contains("PreI"))
{
preiMatch = true;
}
if (!matchFields.Contains("PreA"))
{
preaMatch = true;
}
if (!matchFields.Contains("LitQ"))
{
litqMatch = true;
}
if (!matchFields.Contains("PstI"))
{
pstiMatch = true;
}
if (!matchFields.Contains("PstP"))
{
pstpMatch = true;
}
if (!matchFields.Contains("RespOptions"))
{
roMatch = true;
}
if (!matchFields.Contains("NRCodes"))
{
nrMatch = true;
}
if (!matchFields.Contains("Translation"))
{
tranMatch = true;
}
foreach (string s in matchFields)
{
if (s.Equals("PreP"))
{
prepMatch = (sq1.PreP == sq2.PreP);
}
if (s.Equals("PreI"))
{
preiMatch = (sq1.PreI == sq2.PreI);
}
if (s.Equals("PreA"))
{
preaMatch = (sq1.PreA == sq2.PreA);
}
if (s.Equals("LitQ"))
{
litqMatch = (sq1.LitQ == sq2.LitQ);
}
if (s.Equals("PstI"))
{
pstiMatch = (sq1.PstI == sq2.PstI);
}
if (s.Equals("PstP"))
{
pstpMatch = (sq1.PstP == sq2.PstP);
}
if (s.Equals("RespOptions"))
{
roMatch = (sq1.RespOptions == sq2.RespOptions);
}
if (s.Equals("NRCodes"))
{
nrMatch = (sq1.NRCodes == sq2.NRCodes);
}
if (s.Equals("Translation"))
{
tranMatch = (sq1.GetTranslationText(Lang) == sq2.GetTranslationText(Lang));
}
}
return(prepMatch && preiMatch && preaMatch && litqMatch && pstiMatch & pstpMatch && roMatch && nrMatch);
}
/// <summary>
/// Retrieves a set of records for a particular VarName and returns a list of SurveyQuestion objects.
/// </summary>
/// <param name="refvarname">A valid VarName.</param>
/// <param name="surveyGlob">Survey code pattern.</param>
/// <returns>List of SurveyQuestions</returns>
public static List <SurveyQuestion> GetRefVarNameQuestionsGlob(string refvarname, string surveyGlob)
{
List <SurveyQuestion> qs = new List <SurveyQuestion>();
SurveyQuestion q;
string query = "SELECT * FROM Questions.FN_GetRefVarNameQuestionsGlob(@refvarname, @surveyPattern) 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("@refVarName", refvarname);
sql.SelectCommand.Parameters.AddWithValue("@surveyPattern", surveyGlob.Replace("*", "%"));
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"];
}
qs.Add(q);
}
}
}
catch (Exception)
{
}
}
return(qs);
}
