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