protected void loadAction()
{
//Variable to hold the HTML text in
string html = "";
//Get the Action ID
int ID = Convert.ToInt32(Request.QueryString["ID"]);
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "SELECT * FROM CorrectiveActions WHERE ID = " + ID;
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//Assign values to variables for easier access;
int labID = reader.GetInt32(2);
int technicianID = reader.GetInt32(3);
int hazardID = reader.GetInt32(4);
string hazardName = DataLookup.GetHazardName(hazardID);
int areaID = reader.GetInt32(5);
string areaName = DataLookup.GetAreaName(areaID);
string detailDescription = reader.GetString(6);
string actionDescription = reader.GetString(7);
DateTime dueDate = reader.GetDateTime(8);
bool isComplete = reader.GetBoolean(9);
//Determine corrective action status
string status = "";
if (isComplete == true)
{
status = "Complete";
}
if (isComplete == false && (dueDate < DateTime.Now))
{
status = "Overdue";
}
if (isComplete == false && (dueDate > DateTime.Now))
{
status = "Pending";
}
//Create a table using a string value and assign it to the elements inner html
html += "<tr><td>" + ID + "</td><td>" + hazardName + "</td><td>" + areaName + "</td><td>" + detailDescription + "</td><td>" + actionDescription + "</td><td>" + dueDate + "</td><td>" + status + "</td>";
results.InnerHtml = html;
}
//Close the connection
conn.Close();
}
protected void AreaDropdwon()
{
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "SELECT * FROM Area";
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int areaID = reader.GetInt32(0);
int labID = reader.GetInt32(1);
string labName = DataLookup.GetLabName(labID);
string areaName = reader.GetString(2);
//Add items to the technician and lab drop downs
ddlArea.Items.Add(areaID + "-" + labName + "-" + areaName);
}
//Close the connection
conn.Close();
}
protected void btnFinalize_Click(object sender, EventArgs e)
{
//Inspection is technically already created. The only data that needs to be added
//iS the finish date and the hazard items
string messageBoxText = "Are you sure you want to finalize this inspection?";
string caption = "Finalize Inspection Warning";
MessageBoxButton button = MessageBoxButton.YesNoCancel;
MessageBoxImage icon = MessageBoxImage.Warning;
MessageBoxResult result = MessageBox.Show(messageBoxText, caption, button, icon);
if (result == MessageBoxResult.Yes)
{
//Get the inspectionID
int inspectionID = Convert.ToInt32(Request.QueryString["ID"]);
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "UPDATE Inspection SET FinishDate=@finishDate, IsComplete=1 WHERE ID = " + inspectionID;
//Assign the current date time to the query
comm.Parameters.AddWithValue("@finishDate", DateTime.Now);
//Open the connection
conn.Open();
//Execute the command
int rows = comm.ExecuteNonQuery();
//Close the connection
conn.Close();
if (rows > 0)
{
MessageBox.Show("Inspection Finalized");
//Reload the page
Response.Redirect("Homepage.aspx");
//Send out an email to the admin users
string mailBody = "Hello Administrator users. Technician " + lblTechnicianName.Text + " has completed inspection " + inspectionID + " In the lab/room " + lblRoomName.Text + " At the time of " + DateTime.Now + ".";
DataLookup.SendMail(999, "An Inspection Has Been Completed", mailBody);
}
else
{
MessageBox.Show("Error. No Action Taken.");
}
}
if (result == MessageBoxResult.No | result == MessageBoxResult.Cancel)
{
MessageBox.Show("No Action Taken");
}
}
protected void btnDeleteLabe_Click(object sender, EventArgs e)
{
//Allow the user to back out
string messageBoxText = "Are you sure you want to delete this lab?";
string caption = "Lab Technician Warning";
MessageBoxButton button = MessageBoxButton.YesNoCancel;
MessageBoxImage icon = MessageBoxImage.Warning;
MessageBoxResult result = MessageBox.Show(messageBoxText, caption, button, icon);
if (result == MessageBoxResult.Yes)
{
//Get the lab ID from the dropdown item
string room = ddlLab1.SelectedItem.Text;
var splitValueLab = room.Split(' ');
int labID = Convert.ToInt16(splitValueLab[0]);
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Delete child records
DataLookup.DeleteActionsFromLab(labID);
DataLookup.DeleteAreaFromLab(labID);
//Assign the query to the command
comm.CommandText = "DELETE FROM Lab WHERE ID = @labID";
comm.Parameters.AddWithValue("@labID", labID);
//Open the connection
conn.Open();
//Execute the command
int rows = comm.ExecuteNonQuery();
//Close the connection
conn.Close();
//Message box for success/fail
if (rows > 0)
{
MessageBox.Show("Lab Deleted.");
}
else
{
MessageBox.Show("Error. No Action Taken.");
}
}
if (result == MessageBoxResult.No | result == MessageBoxResult.Cancel)
{
MessageBox.Show("No Action Taken");
}
}
protected void LoadCompleted()
{
//Variable to hold the HTML text in
string html = "";
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "SELECT * FROM Inspection WHERE IsComplete = 1 AND TechnicianID=@technicianID";
comm.Parameters.AddWithValue("technicianID", Globals.technicianID);
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//Get values from the reader and store them in variables for easier access and reading
int ID = reader.GetInt32(0);
int technicianID = reader.GetInt32(1);
string techName = DataLookup.GetTechName(technicianID);
int labID = reader.GetInt32(2);
string labName = DataLookup.GetLabName(labID);
DateTime dueDate = reader.GetDateTime(5);
DateTime startDate = reader.GetDateTime(6);
DateTime finishDate = reader.GetDateTime(7);
//This commented code here is if the desired outcome was the 'list group items' that I made. I thought they might look better than tables but
//in this situation I am not too sure. There is code for both. Commenting and uncommenting will switch.
html += "<a href='../InspectionCreate.aspx?inspectionID=" + ID + "' class='list-group-item' id=''>Inspection ID: " + ID + " - Room: " + labName + " - Due Date: " + dueDate + " - Technician: " + techName + "</a>";
//html += "<tr><td>" + ID + "</td><td>" + techName + "</td><td>" + labName + "</td><td>" + dueDate + "</td><td>" + startDate + "</td><td>" + finishDate + "</td><td><a href='InspectionView.aspx?inspectionID=" + ID + "'><button>View</button></a></td></tr>";
completeResults.InnerHtml = html;
}
//Close the connection
conn.Close();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
//Variable to hold the HTML text in
string html = "";
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Get filters
string parameters = GetParameters();
//Assign the query to the command
comm.CommandText = "SELECT * FROM Inspection INNER JOIN Lab ON Inspection.labID = lab.ID WHERE " + parameters;
//Logic to determine if parameters is blank
if (parameters.Length == 0)
{
comm.CommandText = "SELECT * FROM Inspection";
}
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int ID = reader.GetInt32(0);
int technicianID = reader.GetInt32(1);
string technicianName = DataLookup.GetTechName(technicianID);
int labID = reader.GetInt32(2);
string labName = DataLookup.GetLabName(labID);
DateTime DueDate = reader.GetDateTime(5);
html += "<a href='../InspectionView.aspx?inspectionID=" + ID + "' class='list-group-item' id=''>Inspection ID: " + ID + " - Room: " + labName + " - Due Date: " + DueDate + " - Technician: " + technicianName + "</a>";
resultsList.InnerHtml = html;
}
//Close the connection
conn.Close();
}
protected void LoadData()
{
//Get the ID
int areaID = Convert.ToInt32(Request.QueryString["areaID"]);
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "SELECT * FROM Area WHERE ID=@areaID";
comm.Parameters.AddWithValue("@areaID", areaID);
//Open the connection
conn.Open();
//Execute the command
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//Fill the textboxes
txtAreaID.Text = reader.GetInt32(0).ToString();
txtAreaName.Text = reader.GetString(2);
int labID = reader.GetInt32(1);
string labName = DataLookup.GetLabName(labID);
ddlLab.Items.Add(labID + " - " + labName);
}
//Close the connection
conn.Close();
lblName1.Text = Globals.firstName + " " + Globals.lastName;
lblName2.Text = Globals.firstName + " " + Globals.lastName;
if (!IsPostBack)
{
//Populate labdropdown
labDropdown();
}
}
protected void loadCompleted()
{
//Variable to hold the HTML text in
string html = "";
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "SELECT TOP 4 * FROM Inspection WHERE IsComplete = 1 ORDER BY DueDate DESC";
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//Get values from the reader and store them in variables for easier access and reading
int ID = reader.GetInt32(0);
int technicianID = reader.GetInt32(1);
string techName = DataLookup.GetTechName(technicianID);
int labID = reader.GetInt32(2);
string labName = DataLookup.GetLabName(labID);
DateTime dueDate = reader.GetDateTime(5);
html += "<a href='../InspectionView.aspx?inspectionID=" + ID + "' class='list-group-item' id=''>Inspection ID: " + ID + " - Room: " + labName + " - Due Date: " + dueDate + " - Technician: " + techName + "</a>";
completedResults.InnerHtml = html;
}
//Close the connection
conn.Close();
}
protected void loadData()
{
//Get the Action ID
int ID = Convert.ToInt32(Request.QueryString["ID"]);
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "SELECT CorrectiveActions.ID, CorrectiveActions.InspectionID, CorrectiveActions.TechnicianID, CorrectiveActions.LabID, AssignedDate, CanStartDate, CorrectiveActions.DueDate, StartDate, FinishDate, CorrectiveActions.IsComplete FROM CorrectiveActions INNER JOIN Inspection ON CorrectiveActions.InspectionID = Inspection.ID WHERE CorrectiveActions.ID = " + ID;
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//Assign values to variables for easier access
int inspectionID = reader.GetInt32(1);
int technicianID = reader.GetInt32(2);
string technicianName = DataLookup.GetTechName(technicianID);
int labID = reader.GetInt32(3);
string labName = DataLookup.GetLabName(labID);
DateTime assignedDate = reader.IsDBNull(4)
? DateTime.MaxValue
: reader.GetDateTime(4);
DateTime canStartDate = reader.IsDBNull(5)
? DateTime.MaxValue
: reader.GetDateTime(5);
DateTime dueDate = reader.IsDBNull(6)
? DateTime.MaxValue
: reader.GetDateTime(6);
DateTime startDate = reader.IsDBNull(7)
? DateTime.MaxValue
: reader.GetDateTime(7);
DateTime finishDate = reader.IsDBNull(8)
? DateTime.MaxValue
: reader.GetDateTime(8);
bool isComplete = reader.GetBoolean(9);
//Determine inspection status
string status = "";
if (isComplete == true)
{
status = "Complete";
}
if (isComplete == false && (dueDate < DateTime.Now))
{
status = "Overdue";
}
if (isComplete == false && (dueDate > DateTime.Now))
{
status = "Pending";
}
//Insert variable values into display labels
lblBigInspectionID.Text = ID.ToString();
lblInspectionID.Text = inspectionID.ToString();
lblTechnicianID.Text = technicianID.ToString();
lblRoomID.Text = labID.ToString();
lblInspectionStatus.Text = status;
lblTechnicianName.Text = technicianName;
lblRoomName.Text = labName;
lblAssigned.Text = assignedDate.ToString("MMMM dd, yyyy");
lblCanStart.Text = canStartDate.ToString("MMMM dd, yyyy");
lblDueDate.Text = dueDate.ToString("MMMM dd, yyyy");
lblStartDate.Text = startDate.ToString("MMMM dd, yyyy");
lblFinishDate.Text = finishDate.ToString("MMMM dd, yyyy");
if (finishDate == DateTime.MaxValue)
{
lblFinishDate.Text = "";
}
if (startDate == DateTime.MaxValue)
{
lblStartDate.Text = "";
}
if (assignedDate == DateTime.MaxValue)
{
lblAssigned.Text = "";
}
if (dueDate == DateTime.MaxValue)
{
lblDueDate.Text = "";
}
if (canStartDate == DateTime.MaxValue)
{
lblCanStart.Text = "";
}
}
//Close the connection
conn.Close();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
//Inspection is technically already created. The only data that needs to be added
//iS the finish date and the hazard items
string messageBoxText = "Are you sure you want to insert this corrective action?";
string caption = "Corrective Action Warning";
MessageBoxButton button = MessageBoxButton.YesNoCancel;
MessageBoxImage icon = MessageBoxImage.Warning;
MessageBoxResult result = MessageBox.Show(messageBoxText, caption, button, icon);
if (result == MessageBoxResult.Yes)
{
//Get the inspectionID
int inspectionID = Convert.ToInt32(Request.QueryString["ID"]);
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "UPDATE Inspection SET StartDate=@startDate, isStarted=1 WHERE ID = " + inspectionID;
//Assign the current date time to the query
comm.Parameters.AddWithValue("@startDate", DateTime.Now);
//Open the connection
conn.Open();
//Execute the command
comm.ExecuteNonQuery();
//Close the connection
conn.Close();
//Gather data to create insert query for hazard item
int labID = Convert.ToInt32(lblRoomID.Text);
int technicianID = Convert.ToInt32(lblTechnicianID.Text);
string hazard = ddlHazard.SelectedItem.Text;
var splitValueHazard = hazard.Split('-');
int hazardID = Convert.ToInt16(splitValueHazard[0]);
string area = ddlArea.SelectedItem.Text;
var splitValueArea = area.Split('-');
int areaID = Convert.ToInt16(splitValueArea[0]);
string hazardOther = txtHazardOther.Text;
string areaOther = txtArea.Text;
string hazardDesc = txtHazardDesc.Text;
string actionDesc = txtActionDesc.Text;
DateTime dueDate = calDueDate.SelectedDate;
//Execute the public method for inserting a hazard to an inspection
int rows = DataLookup.InsertAction(inspectionID, labID, technicianID, hazardID, areaID, hazardDesc, actionDesc, dueDate, hazardOther, areaOther);
//Shows feedback based on the success of the transaction
if (rows > 0)
{
MessageBox.Show("Hazard has been added to Inspection #" + inspectionID);
}
else
{
MessageBox.Show("Error. No changes have been made.");
}
//Reload the page so another corrective action can be added
Response.Redirect("InspectionCreateStatic.aspx?ID=" + inspectionID);
}
if (result == MessageBoxResult.No | result == MessageBoxResult.Cancel)
{
MessageBox.Show("No Action Taken");
}
}
protected void loadData()
{
//Get the inspectionID
int inspectionID = Convert.ToInt32(Request.QueryString["inspectionID"]);
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "SELECT * FROM Inspection WHERE ID = " + inspectionID;
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//Assign values to variables for easier access
int technicianID = reader.GetInt32(1);
string technicianName = DataLookup.GetTechName(technicianID);
int labID = reader.GetInt32(2);
string labName = DataLookup.GetLabName(labID);
DateTime assignedDate = reader.IsDBNull(3)
? DateTime.MaxValue
: reader.GetDateTime(3);
DateTime canStartDate = reader.IsDBNull(4)
? DateTime.MaxValue
: reader.GetDateTime(4);
DateTime dueDate = reader.IsDBNull(5)
? DateTime.MaxValue
: reader.GetDateTime(5);
DateTime startDate = reader.IsDBNull(6)
? DateTime.MaxValue
: reader.GetDateTime(6);
DateTime finishDate = reader.IsDBNull(7)
? DateTime.MaxValue
: reader.GetDateTime(7);
bool isComplete = reader.GetBoolean(8);
//Insert variable values into display labels
lblBigInspectionID.Text = inspectionID.ToString();
txtInspectionID.Text = inspectionID.ToString();
//Fill the dropdown list
if (isComplete == true)
{
ddlInspectionStatus.Items.Add("Complete");
ddlInspectionStatus.Items.Add("Incomplete");
}
if (isComplete == false)
{
ddlInspectionStatus.Items.Add("Incomplete");
ddlInspectionStatus.Items.Add("Complete");
}
//Fill the calendars
calDateAssigned.SelectedDate = assignedDate;
calDateCanStart.SelectedDate = canStartDate;
calDueDate.SelectedDate = dueDate;
calStartDate.SelectedDate = startDate;
calFinishDate.SelectedDate = finishDate;
//Fill the lab drop down with the current value first
currentLabItem(labID);
//Fill the technician drop down with the current value first
currentTechnicianItem(technicianID);
}
//Close the connection
conn.Close();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
//Variable to hold the HTML text in
string html = "";
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Get filters
string parameters = GetParameters();
//Assign the query to the command
comm.CommandText = "SELECT * FROM CorrectiveActions INNER JOIN Lab ON CorrectiveActions.LabID = Lab.ID WHERE " + parameters;
//Logic to determine if parameters is blank
if (parameters.Length == 0)
{
comm.CommandText = "SELECT * FROM CorrectiveActions";
}
//Open the connection
conn.Open();
//Assign the command to a reader and execute
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//Hold reader values in variables for easier access
int ID = reader.GetInt32(0);
int inspectionID = reader.GetInt32(1);
string labName = DataLookup.GetLabName(reader.GetInt32(2));
string technicianName = DataLookup.GetTechName(reader.GetInt32(3));
string hazardName = DataLookup.GetHazardName(reader.GetInt32(4));
string areaName = DataLookup.GetAreaName(reader.GetInt32(5));
string detailDescription = reader.GetString(6);
string actionDescription = reader.GetString(7);
DateTime dueDate = reader.GetDateTime(8);
bool isComplete = reader.GetBoolean(9);
string status = "";
if (isComplete == true)
{
status = "Complete";
}
if (isComplete == false && dueDate > DateTime.Now)
{
status = "Pending";
}
if (isComplete == false && dueDate < DateTime.Now)
{
status = "Overdue";
}
html += "<tr><td>" + ID + "</td><td>" + status + "</td><td>" + inspectionID + "</td><td>" + labName + "</td><td>" + technicianName + "</td><td>" + hazardName + "</td><td>" + areaName + "</td><td>" + detailDescription + "</td><td>" + actionDescription + "</td><td>" + dueDate + "</td><td><a href='ActionView.aspx?ID=" + ID + "'><button>View</button></a></td></tr>";
results.InnerHtml = html;
}
//Close the connection
conn.Close();
}
protected void btnSchedule_Click(object sender, EventArgs e)
{
string messageBoxText = "Are you sure you want to schedule this inspection?";
string caption = "Inspection Schedule Warning";
MessageBoxButton button = MessageBoxButton.YesNoCancel;
MessageBoxImage icon = MessageBoxImage.Warning;
MessageBoxResult result = MessageBox.Show(messageBoxText, caption, button, icon);
if (result == MessageBoxResult.Yes)
{
//Get the lab ID
string room = ddlLab.SelectedItem.Text;
var splitValueLab = room.Split(' ');
int labID = Convert.ToInt16(splitValueLab[0]);
//Get the technician ID
string technician = ddlTechnician.SelectedItem.Text;
var splitValueTech = technician.Split(' ');
int technicianID = Convert.ToInt16(splitValueTech[3]);
//Get the due date value
DateTime dueDate = calDueDate.SelectedDate;
//Create a new SQL connection
SqlConnection conn = new SqlConnection(connectionString);
//Create a new SQL command
SqlCommand comm = conn.CreateCommand();
//Assign the query to the command
comm.CommandText = "INSERT INTO Inspection (TechnicianID, LabID, AssignedDate, DueDate) VALUES (@technicianID, @labID, @assignedDate, @dueDate)";
comm.Parameters.Add("@technicianID", technicianID);
comm.Parameters.Add("@labID", labID);
comm.Parameters.Add("@assignedDate", SqlDbType.DateTime).Value = DateTime.Now;
comm.Parameters.Add("@dueDate", SqlDbType.DateTime).Value = dueDate;
//Open the connection
conn.Open();
//Execute the query
int rows = comm.ExecuteNonQuery();
//Close the connection
conn.Close();
//Shows feedback based on the success of the transaction
if (rows > 0)
{
MessageBox.Show("Inspection has been scheduled.");
//Send a notification Email
string mailBody = "Hello " + technician + ", You have a new inspection in room " + room + " that is due on " + dueDate;
DataLookup.SendMail(technicianID, "You Have a New Scheduled Inspection", mailBody);
}
else
{
MessageBox.Show("Error. Inspection has not been scheduled.");
}
}
if (result == MessageBoxResult.No | result == MessageBoxResult.Cancel)
{
MessageBox.Show("No Action Taken");
}
}