private void PreviewStenerButton_Click(object sender, EventArgs e)
{
if (submittedStenerListView.SelectedItems.Count > 0)
{
int qSetID = Convert.ToInt32(submittedStenerListView.SelectedItems[0].SubItems[0].Text);
QuestionSet qSet = stenerManagement.GetQuestionSet(qSetID);
OversightPreview preview = new OversightPreview(qSetID);
preview.Show();
}
}
public bool SubmitQuestionSet(QuestionSet qSet)
{
bool result = SqlManager.ModifyQuestionSet(qSet);
if (result == true)
{
foreach (Question q in qSet.Questions)
{
SqlManager.ModifyQuestion(q, qSet.UniqueID);
}
}
return(result);
}
private void approveButton_Click(object sender, EventArgs e)
{
if (submittedStenerListView.SelectedItems.Count > 0)
{
int qSetID = Convert.ToInt32(submittedStenerListView.SelectedItems[0].SubItems[0].Text);
QuestionSet qSet = stenerManagement.GetQuestionSet(qSetID);
qSet.Status = "APPROVED";
bool result = stenerManagement.SubmitQuestionSet(qSet);
this.refreshSubmissionsList();
submissionStatusLabel.Text = "Approved STENER!";
}
}
static public bool AddQuestion(QuestionSet questionSet, Question question)
{
string queryString =
"INSERT INTO StenerTable(QuestionUID, StenerSetUID, DepartmentUID, Category, Question, Answer, LocationEvidence, Priority, DueDate, Status, Compliance, PlanForSolution, SubmittedDate, Violated)"
+ "VALUES('" + question.QuestionID.ToString() + "','" + questionSet.UniqueID.ToString() + "','"
+ questionSet.AssignedDepartment.ToString() + "','" + questionSet.Category + "','"
+ question.QuestionText + "','" + question.Answer + "','" + question.EvidenceLocation + "','"
+ questionSet.Priority.ToString() + "','" + questionSet.DueDate.ToString("MM/dd/yyyy hh:mm:ss tt") + "','"
+ questionSet.Status + "','" + Convert.ToInt32(question.Compliance) + "','"
+ question.PlanForSolution + "','" + questionSet.SubmittedDate.ToString("MM/dd/yyyy hh:mm:ss tt") + "','" + Convert.ToInt32(questionSet.Violated) + "')";
bool passed = NonQueryDatabase(queryString);
return(passed);
}
static public bool ModifyQuestionSet(QuestionSet updatedQSet)
{
string queryString =
"UPDATE StenerTable SET DepartmentUID = '" + updatedQSet.AssignedDepartment.ToString()
+ "', Priority = '" + updatedQSet.Priority.ToString()
+ "', DueDate = '" + updatedQSet.DueDate.ToString("MM/dd/yyyy hh:mm:ss tt")
+ "', SubmittedDate = '" + updatedQSet.SubmittedDate.ToString("MM/dd/yyyy hh:mm:ss tt")
+ "', Status = '" + updatedQSet.Status
+ "', Category = '" + updatedQSet.Category
+ "' WHERE StenerSetUID = '" + updatedQSet.UniqueID.ToString() + "'";
bool passed = NonQueryDatabase(queryString);
return(passed);
}
// event handler for hitting the save/close button when completing a question set.
private void SaveAndCloseSetButton_Click(object sender, EventArgs e)
{
if (currentQuestionSet.Status != "SUBMITTED" && currentQuestionSet.Status != "APPROVED")
{
// update the status of the q set
currentQuestionSet.Status = "INPROGRESS";
var result = stenerManagement.SubmitQuestionSet(currentQuestionSet);
// display the status of saving or loading
if (result)
{
completeStenerStatusLabel.Text = "Saved Successfully!";
}
else
{
completeStenerStatusLabel.Text = "Error Saving!";
}
}
// clear the input fields
questionCountTextBox.Text = "";
questionBodyTextBox.Text = "";
complianceCheckBox.Checked = false;
planForSolutionTextBox.Text = "";
evidenceLocationTextBox.Text = "";
answerTextBox.Text = "";
answerTextBox.Enabled = false;
complianceCheckBox.Enabled = false;
planForSolutionTextBox.Enabled = false;
evidenceLocationTextBox.Enabled = false;
backButton.Enabled = false;
nextButton.Enabled = false;
saveAndCloseSetButton.Enabled = false;
submitQuestionSetButton.Enabled = false;
completeQuestionSetListView.Enabled = true;
workOnSelectedStenerButton.Enabled = true;
refreshCompleteListButton.Enabled = true;
;
currentQuestion = 1;
currentQuestionSet = null;
// refresh the ui lists
RefreshQuestionSetList();
completeStenerStatusLabel.Text = "Closed Successfully!";
}
public string previewStener(int qID)
{
QuestionSet resultsByID = SqlManager.FindQuestionSet(qID);
string resultStr = "";
resultStr += "Assigned Department : ";
resultStr += " " + resultsByID.AssignedDepartment + "\r\n";
resultStr += "Category : ";
resultStr += " " + resultsByID.Category + "\r\n";
resultStr += "Due Date : ";
resultStr += " " + resultsByID.DueDate + "\r\n";
resultStr += "Priority : ";
resultStr += " " + resultsByID.Priority + "\r\n";
resultStr += "Question Count: ";
resultStr += " " + resultsByID.QuestionCount + "\r\n";
resultStr += "Submitted Date : ";
resultStr += " " + resultsByID.SubmittedDate + "\r\n";
resultStr += "Unique ID : ";
resultStr += " " + resultsByID.UniqueID + "\r\n";
resultStr += "Violated : ";
resultStr += " " + resultsByID.Violated + "\r\n";
resultStr += "Status : ";
resultStr += " " + resultsByID.Status + "\r\n";
foreach (Question question in resultsByID.Questions)
{
resultStr += "Question ID: ";
resultStr += question.QuestionID + "\r\n";
resultStr += "Question: ";
resultStr += question.QuestionText + "\r\n";
resultStr += "Answer: ";
resultStr += question.Answer + "\r\n";
resultStr += "Evidence Location: ";
resultStr += question.EvidenceLocation + "\r\n";
resultStr += "Compliance: ";
resultStr += question.Compliance + "\r\n";
resultStr += "Plan For Solution: ";
resultStr += question.PlanForSolution + "\r\n";
}
return(resultStr);
}
private int GetNextQuestionID(QuestionSet qSet)
{
int nextID = 0;
if (qSet.Questions != null)
{
foreach (Question q in qSet.Questions)
{
if (q.QuestionID > nextID)
{
nextID = q.QuestionID;
}
}
}
nextID = nextID + 1;
return(nextID);
}
private static int GetNextQuestionId(QuestionSet qSet)
{
var nextId = 0;
if (qSet.Questions != null)
{
foreach (var q in qSet.Questions)
{
if (q.QuestionId > nextId)
{
nextId = q.QuestionId;
}
}
}
nextId += 1;
return(nextId);
}
private void SaveAndCloseSetButton_Click(object sender, EventArgs e)
{
if (currentQuestionSet.Status != "SUBMITTED" && currentQuestionSet.Status != "APPROVED")
{
currentQuestionSet.Status = "INPROGRESS";
bool result = stenerManagement.SubmitQuestionSet(currentQuestionSet);
if (result == true)
{
completeStenerStatusLabel.Text = "Saved Successfully!";
}
else
{
completeStenerStatusLabel.Text = "Error Saving!";
}
}
questionCountTextBox.Text = "";
questionBodyTextBox.Text = "";
complianceCheckBox.Checked = false;
planForSolutionTextBox.Text = "";
evidenceLocationTextBox.Text = "";
answerTextBox.Text = "";
answerTextBox.Enabled = false;
complianceCheckBox.Enabled = false;
planForSolutionTextBox.Enabled = false;
evidenceLocationTextBox.Enabled = false;
backButton.Enabled = false;
nextButton.Enabled = false;
saveAndCloseSetButton.Enabled = false;
submitQuestionSetButton.Enabled = false;
completeQuestionSetListView.Enabled = true;
workOnSelectedStenerButton.Enabled = true;
refreshCompleteListButton.Enabled = true;;
currentQuestion = 1;
currentQuestionSet = null;
refreshQuestionSetList();
completeStenerStatusLabel.Text = "Closed Successfully!";
}
public bool AddQuestion(string qText, int qSetID)
{
QuestionSet qSet = SqlManager.FindQuestionSet(qSetID);
if (qSet != null)
{
Question question = new Question();
question.QuestionText = qText;
question.QuestionID = GetNextQuestionID(qSet);
qSet.Questions.Add(question);
qSet.Status = "UPDATED";
bool passed = SqlManager.AddQuestion(qSet, question);
SqlManager.ModifyQuestionSet(qSet);
return(passed);
}
else
{
return(false);
}
}
private void StenerManagementListView_ItemSelectionChanged(object sender, EventArgs e)
{
stenerManagementQuestionList.Items.Clear();
if (StenerManagementListView.SelectedItems.Count > 0)
{
int selectedID = Convert.ToInt32(StenerManagementListView.SelectedItems[0].SubItems[0].Text);
QuestionSet qSet = stenerManagement.GetQuestionSet(selectedID);
if (qSet != null)
{
foreach (Question question in qSet.Questions)
{
string[] row = { question.QuestionID.ToString(), question.QuestionText };
var listItem = new ListViewItem(row);
stenerManagementQuestionList.Items.Add(listItem);
}
}
}
}
public static bool CreateNewQuestionSet(QuestionSet questionSet)
{
foreach (var question in questionSet.Questions)
{
var queryString =
"INSERT INTO StenerTable(QuestionUID, StenerSetUid, DepartmentUid, Category, Question, Answer, LocationEvidence, Priority, DueDate, Status, Compliance, PlanForSolution, Violated)"
+ "VALUES('" + question.QuestionId + "','" + questionSet.UniqueID + "','"
+ questionSet.AssignedDepartment + "','" + questionSet.Category + "','"
+ question.QuestionText + "','" + question.Answer + "','" + question.EvidenceLocation + "','"
+ questionSet.Priority + "','" + questionSet.DueDate.ToString("MM/dd/yyyy hh:mm:ss tt") + "','"
+ questionSet.Status + "','" + Convert.ToInt32(question.Compliance) + "','"
+ question.PlanForSolution + "','" + Convert.ToInt32(questionSet.Violated) + "')";
var passed = NonQueryDatabase(queryString);
if (passed == false)
{
return(false);
}
}
return(true);
}
public bool CreateQuestionSet(int departmentID, int priority, DateTime dueDate, string question, string category)
{
QuestionSet qSet = new QuestionSet();
//TODO: Getassigned department ID from a string parmaneter
qSet.AssignedDepartment = departmentID;
qSet.Priority = priority;
qSet.DueDate = dueDate;
qSet.Category = category;
qSet.UniqueID = GetNextQuestionSetID();
qSet.Status = "CREATED";
Question newQuestion = new Question();
newQuestion.QuestionText = question;
newQuestion.QuestionID = GetNextQuestionID(qSet);
qSet.Questions.Add(newQuestion);
bool result = SqlManager.CreateNewQuestionSet(qSet);
return(result);
}
static public List <QuestionSet> GetAllQuestionSets()
{
try
{
DataTable questionsDataTable = QueryDatabase("SELECT * FROM StenerTable");
List <DataRow> qSetRows = new List <DataRow>();
List <int> qSetIds = new List <int>();
foreach (DataRow row in questionsDataTable.Rows)
{
if (qSetIds.Contains(row.Field <int>("StenerSetUID")) == false)
{
qSetIds.Add(row.Field <int>("StenerSetUID"));
qSetRows.Add(row);
}
}
List <QuestionSet> questionSets = new List <QuestionSet>();
foreach (DataRow row in qSetRows)
{
QuestionSet qSet = new QuestionSet();
qSet.UniqueID = row.Field <int>("StenerSetUID");
qSet.AssignedDepartment = row.Field <int>("DepartmentUID");
qSet.Priority = row.Field <int>("Priority");
qSet.Violated = Convert.ToBoolean(row.Field <int>("Violated"));
if (row.Field <string>("Category") != null)
{
qSet.Category = row.Field <string>("Category");
}
if (row.Field <string>("DueDate") != null)
{
//test
string test = row.Field <string>("DueDate");
qSet.DueDate = DateTime.ParseExact(row.Field <string>("DueDate"), "MM/dd/yyyy hh:mm:ss tt", null);
}
if (row.Field <string>("SubmittedDate") != null)
{
string test = row.Field <string>("SubmittedDate");
qSet.SubmittedDate = DateTime.ParseExact(row.Field <string>("SubmittedDate"), "MM/dd/yyyy hh:mm:ss tt", null);
}
if (row.Field <string>("Status") != null)
{
qSet.Status = row.Field <string>("Status");
}
qSet.Questions = new List <Question>();
DataTable questionTable = QueryDatabase("SELECT * FROM StenerTable WHERE StenerSetUID = " + qSet.UniqueID.ToString());
foreach (DataRow questionRow in questionTable.Rows)
{
Question question = new Question();
question.QuestionID = questionRow.Field <int>("QuestionUID");
question.Compliance = Convert.ToBoolean(questionRow.Field <int>("Compliance"));
if (questionRow.Field <string>("Question") != null)
{
question.QuestionText = questionRow.Field <string>("Question");
}
if (questionRow.Field <string>("Answer") != null)
{
question.Answer = questionRow.Field <string>("Answer");
}
if (questionRow.Field <string>("LocationEvidence") != null)
{
question.EvidenceLocation = questionRow.Field <string>("LocationEvidence");
}
if (questionRow.Field <string>("PlanForSolution") != null)
{
question.PlanForSolution = questionRow.Field <string>("PlanForSolution");
}
qSet.Questions.Add(question);
}
questionSets.Add(qSet);
}
return(questionSets);
}
catch (Exception e)
{
Console.WriteLine(e);
return(new List <QuestionSet>());
}
}
public QuestionSet GetQuestionSet(int id)
{
QuestionSet qSet = SqlManager.FindQuestionSet(id);
return(qSet);
}
public static QuestionSet FindQuestionSet(int qSetID)
{
var query = "SELECT * FROM StenerTable WHERE StenerSetUid = " + qSetID;
var dataTable = QueryDatabase(query);
if (dataTable.Rows.Count > 0)
{
var row = dataTable.Rows[0];
var qSet = new QuestionSet
{
UniqueID = row.Field <int>("StenerSetUid"),
AssignedDepartment = row.Field <int>("DepartmentUid"),
Priority = row.Field <int>("Priority"),
Violated = Convert.ToBoolean(row.Field <int>("Violated"))
};
if (row.Field <string>("Category") != null)
{
qSet.Category = row.Field <string>("Category");
}
if (row.Field <string>("DueDate") != null)
{
qSet.DueDate = DateTime.ParseExact(row.Field <string>("DueDate"), "MM/dd/yyyy hh:mm:ss tt", null);
}
if (row.Field <string>("SubmittedDate") != null)
{
qSet.SubmittedDate = DateTime.ParseExact(row.Field <string>("SubmittedDate"),
"MM/dd/yyyy hh:mm:ss tt", null);
}
if (row.Field <string>("Status") != null)
{
qSet.Status = row.Field <string>("Status");
}
foreach (DataRow questionRow in dataTable.Rows)
{
var question = new Question
{
QuestionId = questionRow.Field <int>("QuestionUID"),
Compliance = Convert.ToBoolean(questionRow.Field <int>("Compliance"))
};
if (questionRow.Field <string>("Question") != null)
{
question.QuestionText = questionRow.Field <string>("Question");
}
if (questionRow.Field <string>("Answer") != null)
{
question.Answer = questionRow.Field <string>("Answer");
}
if (questionRow.Field <string>("LocationEvidence") != null)
{
question.EvidenceLocation = questionRow.Field <string>("LocationEvidence");
}
if (questionRow.Field <string>("PlanForSolution") != null)
{
question.PlanForSolution = questionRow.Field <string>("PlanForSolution");
}
qSet.Questions.Add(question);
}
return(qSet);
}
return(null);
}
// event handler for selecting a stener to work on.
private void WorkOnSelectedStenerButton_Click(object sender, EventArgs e)
{
// verify that a q-set is actually selected
if (completeQuestionSetListView.SelectedItems.Count > 0)
{
var qSetID = Convert.ToInt32(completeQuestionSetListView.SelectedItems[0].SubItems[1].Text);
// retrieve the current question set from the db
currentQuestionSet = stenerManagement.GetQuestionSet(qSetID);
if (currentQuestionSet != null)
{
currentQuestion = 1;
// disable editing of the question set if it has been approved or is still in the review process
if (currentQuestionSet.Status == "SUBMITTED" || currentQuestionSet.Status == "APPROVED")
{
// disable text boxes
answerTextBox.Enabled = false;
evidenceLocationTextBox.Enabled = false;
complianceCheckBox.Enabled = false;
planForSolutionTextBox.Enabled = false;
// handle button activations
saveAndCloseSetButton.Enabled = true;
completeQuestionSetListView.Enabled = false;
workOnSelectedStenerButton.Enabled = false;
refreshCompleteListButton.Enabled = false;
}
else
{
answerTextBox.Enabled = true;
complianceCheckBox.Enabled = true;
}
saveAndCloseSetButton.Enabled = true;
// populate the text boxes with the question's data
questionCountTextBox.Text = currentQuestion + "/" + currentQuestionSet.Questions.Count;
questionBodyTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].QuestionText;
complianceCheckBox.Checked = currentQuestionSet.Questions[currentQuestion - 1].Compliance;
answerTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].Answer;
planForSolutionTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].PlanForSolution;
evidenceLocationTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].EvidenceLocation;
// enable editing if the question has not been approved and is not in the review process
if (currentQuestionSet.Status != "SUBMITTED" && currentQuestionSet.Status != "APPROVED")
{
if (complianceCheckBox.Checked)
{
planForSolutionTextBox.Enabled = false;
evidenceLocationTextBox.Enabled = true;
}
else
{
planForSolutionTextBox.Enabled = true;
evidenceLocationTextBox.Enabled = false;
}
}
// if this is this last question disable the next button
if (currentQuestion == currentQuestionSet.Questions.Count)
{
nextButton.Enabled = false;
if (currentQuestionSet.Status != "SUBMITTED" && currentQuestionSet.Status != "APPROVED")
{
submitQuestionSetButton.Enabled = true;
}
}
// otherwise enable the next button
else
{
nextButton.Enabled = true;
submitQuestionSetButton.Enabled = false;
}
// enable back if this is not the first question
if (currentQuestion > 1)
{
backButton.Enabled = true;
}
// otherwise disable it.
else
{
backButton.Enabled = false;
}
}
}
}
// If requesting for all departments, pass a negative department id.
// If requesting for all status, pass and empty status string.
public static List <QuestionSet> GetAllQuestionSets(int departmentID, string status)
{
try
{
DataTable questionsDataTable;
if (departmentID >= 0)
{
if (status == "")
{
questionsDataTable =
QueryDatabase("SELECT * FROM StenerTable WHERE DepartmentUid = " + departmentID);
}
else
{
questionsDataTable = QueryDatabase("SELECT * FROM StenerTable WHERE DepartmentUid = "
+ departmentID + " AND "
+ " Status = '" + status + "'");
}
}
else if (status != "")
{
questionsDataTable = QueryDatabase("SELECT * FROM StenerTable WHERE Status = '" + status + "'");
}
else
{
questionsDataTable = QueryDatabase("SELECT * FROM StenerTable");
}
var qSetRows = new List <DataRow>();
var qSetIds = new List <int>();
foreach (DataRow row in questionsDataTable.Rows)
{
if (qSetIds.Contains(row.Field <int>("StenerSetUid")) == false)
{
qSetIds.Add(row.Field <int>("StenerSetUid"));
qSetRows.Add(row);
}
}
var questionSets = new List <QuestionSet>();
foreach (var row in qSetRows)
{
var qSet = new QuestionSet
{
UniqueID = row.Field <int>("StenerSetUid"),
AssignedDepartment = row.Field <int>("DepartmentUid"),
Priority = row.Field <int>("Priority"),
Violated = Convert.ToBoolean(row.Field <int>("Violated"))
};
if (row.Field <string>("Category") != null)
{
qSet.Category = row.Field <string>("Category");
}
if (row.Field <string>("DueDate") != null)
{
//test
var test = row.Field <string>("DueDate");
qSet.DueDate =
DateTime.ParseExact(row.Field <string>("DueDate"), "MM/dd/yyyy hh:mm:ss tt", null);
}
if (row.Field <string>("SubmittedDate") != null)
{
var test = row.Field <string>("SubmittedDate");
qSet.SubmittedDate = DateTime.ParseExact(row.Field <string>("SubmittedDate"),
"MM/dd/yyyy hh:mm:ss tt", null);
}
if (row.Field <string>("Status") != null)
{
qSet.Status = row.Field <string>("Status");
}
qSet.Questions = new List <Question>();
var questionTable =
QueryDatabase("SELECT * FROM StenerTable WHERE StenerSetUid = " + qSet.UniqueID);
foreach (DataRow questionRow in questionTable.Rows)
{
var question = new Question();
question.QuestionId = questionRow.Field <int>("QuestionUID");
question.Compliance = Convert.ToBoolean(questionRow.Field <int>("Compliance"));
if (questionRow.Field <string>("Question") != null)
{
question.QuestionText = questionRow.Field <string>("Question");
}
if (questionRow.Field <string>("Answer") != null)
{
question.Answer = questionRow.Field <string>("Answer");
}
if (questionRow.Field <string>("LocationEvidence") != null)
{
question.EvidenceLocation = questionRow.Field <string>("LocationEvidence");
}
if (questionRow.Field <string>("PlanForSolution") != null)
{
question.PlanForSolution = questionRow.Field <string>("PlanForSolution");
}
qSet.Questions.Add(question);
}
questionSets.Add(qSet);
}
return(questionSets);
}
catch (Exception e)
{
Console.WriteLine(e);
return(new List <QuestionSet>());
}
}
private void WorkOnSelectedStenerButton_Click(object sender, EventArgs e)
{
if (completeQuestionSetListView.SelectedItems.Count > 0)
{
int qSetID = Convert.ToInt32(completeQuestionSetListView.SelectedItems[0].SubItems[1].Text);
currentQuestionSet = stenerManagement.GetQuestionSet(qSetID);
if (currentQuestionSet != null)
{
currentQuestion = 1;
if (currentQuestionSet.Status == "SUBMITTED" || currentQuestionSet.Status == "APPROVED")
{
answerTextBox.Enabled = false;
evidenceLocationTextBox.Enabled = false;
complianceCheckBox.Enabled = false;
planForSolutionTextBox.Enabled = false;
saveAndCloseSetButton.Enabled = true;
completeQuestionSetListView.Enabled = false;
workOnSelectedStenerButton.Enabled = false;
refreshCompleteListButton.Enabled = false;
}
else
{
answerTextBox.Enabled = true;
complianceCheckBox.Enabled = true;
}
saveAndCloseSetButton.Enabled = true;
questionCountTextBox.Text = currentQuestion + "/" + currentQuestionSet.Questions.Count.ToString();
questionBodyTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].QuestionText;
complianceCheckBox.Checked = currentQuestionSet.Questions[currentQuestion - 1].Compliance;
answerTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].Answer;
planForSolutionTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].PlanForSolution;
evidenceLocationTextBox.Text = currentQuestionSet.Questions[currentQuestion - 1].EvidenceLocation;
if (currentQuestionSet.Status != "SUBMITTED" && currentQuestionSet.Status != "APPROVED")
{
if (complianceCheckBox.Checked == true)
{
planForSolutionTextBox.Enabled = false;
evidenceLocationTextBox.Enabled = true;
}
else
{
planForSolutionTextBox.Enabled = true;
evidenceLocationTextBox.Enabled = false;
}
}
if (currentQuestion == currentQuestionSet.Questions.Count)
{
nextButton.Enabled = false;
if (currentQuestionSet.Status != "SUBMITTED" && currentQuestionSet.Status != "APPROVED")
{
submitQuestionSetButton.Enabled = true;
}
}
else
{
nextButton.Enabled = true;
submitQuestionSetButton.Enabled = false;
}
if (currentQuestion > 1)
{
backButton.Enabled = true;
}
else
{
backButton.Enabled = false;
}
}
}
}
