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