protected void notesList_SelectedIndexChanged(object sender, EventArgs e)
{
//is for wheneveer they change their selected note
//heres the part for the notes
String svcNm2 = SvcLB.SelectedValue.ToString();
char separator2 = '|';
String[] svcNm4 = svcNm2.Split(separator2);
String svcNm3 = svcNm4[0];
//heres the part for the notes
String svcNm21 = notesList.SelectedValue.ToString();
char separator21 = '|';
String[] svcNm41 = svcNm2.Split(separator2);
String svcNm31 = svcNm4[0];
using (SqlConnection sqlConnectD = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ToString()))
{
{
// Populate Employee Gridview based off SvcName
sqlConnectD.Open();
// for just one result SqlDataAdapter adapter2 = new SqlDataAdapter("SELECT Service.ServiceName, Note.NoteTitle, Note.NoteBody FROM Workflow INNER JOIN Service ON Workflow.ServiceID = Service.ServiceID INNER JOIN Note ON Workflow.WorkflowID = Note.WorkflowID WHERE Service.ServiceID != 1 AND Note.NoteTitle = " +"'"+ svcNm21 +"'"+ "AND Service.ServiceName = "+ "'"+svcNm3 + "'", sqlConnectD);
SqlDataAdapter adapter2 = new SqlDataAdapter("SELECT Service.ServiceName, Note.NoteTitle, Note.NoteBody FROM Workflow INNER JOIN Service ON Workflow.ServiceID = Service.ServiceID INNER JOIN Note ON Workflow.WorkflowID = Note.WorkflowID WHERE Service.ServiceID != 1 AND Service.ServiceName = " + "'" + svcNm3 + "'", sqlConnectD);
DataTable dt2 = new DataTable();
adapter2.Fill(dt2);
NoteGV.DataSource = dt2;
NoteGV.DataBind();
if (dt2.Rows.Count == 0)
{
// Label7.Text = "There are currently no notes associated with this Service";
}
}
}
}
protected void SvcLB_SelectedIndexChanged(object sender, EventArgs e)
{
// Label5.Text = "";
//Label6.Text = "";
//Label7.Text = "";
InfoGV.Visible = true;
NoteGV.Visible = true;
HistoryGV.Visible = true;
EquipmentGV.Visible = true;
ProgressTbl.Visible = true;
//heres the part for the notes
String svcNm22 = SvcLB.SelectedValue.ToString();
char separator22 = '|';
String[] svcNm42 = svcNm22.Split(separator22);
String svcNm32 = svcNm42[0];
//heres the part for the notes
String svcNm21 = notesList.SelectedValue.ToString();
char separator21 = '|';
String[] svcNm41 = svcNm21.Split(separator21);
String svcNm31 = svcNm41[0];
using (SqlConnection sqlConnectD = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ToString()))
{
{
// Populate Employee Gridview based off SvcName
sqlConnectD.Open();
// for just one result SqlDataAdapter adapter2 = new SqlDataAdapter("SELECT Service.ServiceName, Note.NoteTitle, Note.NoteBody FROM Workflow INNER JOIN Service ON Workflow.ServiceID = Service.ServiceID INNER JOIN Note ON Workflow.WorkflowID = Note.WorkflowID WHERE Service.ServiceID != 1 AND Note.NoteTitle = " +"'"+ svcNm21 +"'"+ "AND Service.ServiceName = "+ "'"+svcNm3 + "'", sqlConnectD);
SqlDataAdapter adapter2 = new SqlDataAdapter("SELECT Service.ServiceName, Note.NoteTitle, Note.NoteBody FROM Workflow INNER JOIN Service ON Workflow.ServiceID = Service.ServiceID INNER JOIN Note ON Workflow.WorkflowID = Note.WorkflowID WHERE Service.ServiceID != 1 AND Service.ServiceName = " + "'" + svcNm32 + "'", sqlConnectD);
DataTable dt2 = new DataTable();
adapter2.Fill(dt2);
NoteGV.DataSource = dt2;
NoteGV.DataBind();
if (dt2.Rows.Count == 0)
{
// Label7.Text = "There are currently no notes associated with this Service";
}
}
}
//heres the part for the employees and the equipment
String svcNm = SvcLB.SelectedValue.ToString();
char separator = '|';
String[] svcNmA = svcNm.Split(separator);
String svcNmB = svcNmA[0];
SqlConnection sqlConnect2 = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ToString());
sqlConnect2.Open();
String cidCheck = "SELECT CustomerID FROM CUSTOMER WHERE CustomerName = @CustomerName";
SqlCommand cCheck = new SqlCommand(cidCheck, sqlConnect2);
cCheck.Parameters.AddWithValue("@CustomerName", CustLB.SelectedValue.ToString());
int yCID = Convert.ToInt32(cCheck.ExecuteScalar());
using (SqlConnection sqlConnectA = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ToString()))
{
{
// Populate Gridview based off CID
sqlConnectA.Open();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT DISTINCT Service.ServiceName, Service.ServiceDate, Service.CompletionDate, Service.UpdateStatus, Service.PaymentStatus, Service.Origin, Service.Destination FROM Service INNER JOIN Workflow ON Service.ServiceID = Workflow.ServiceID WHERE Service.ServiceID != 1 AND Service.ServiceName = '" + svcNmB + "' AND Service.CustomerID = " + yCID, sqlConnect2);
DataTable dt = new DataTable();
adapter.Fill(dt);
InfoGV.DataSource = dt;
InfoGV.DataBind();
}
}
sqlConnect2.Close();
using (SqlConnection sqlConnectC = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ToString()))
{
{
// Populate Employee Gridview based off SvcName
sqlConnectC.Open();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT Service.ServiceName, Employee.EmployeeName, Employee.EmployeeEmail, Workflow.StartDate, Workflow.EndDate, Workflow.Status FROM Workflow INNER JOIN Service ON Workflow.ServiceID = Service.ServiceID INNER JOIN Employee ON Workflow.EmployeeID = Employee.EmployeeID WHERE Service.ServiceID != 1 AND Service.ServiceName = " + "'" + svcNmB + "'", sqlConnectC);
DataTable dtA = new DataTable();
adapter.Fill(dtA);
HistoryGV.DataSource = dtA;
HistoryGV.DataBind();
if (dtA.Rows.Count == 0)
{
// Label5.Text = "There are currently no employees assgined to this Service";
}
}
}
using (SqlConnection sqlConnectB = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ToString()))
{
{
// Populate Equipment Gridview based off SvcName
sqlConnectB.Open();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT Service.ServiceName, Equipment.EquipmentName, EquipmentRent.RentCondition, EquipmentRent.ReturnCondition, EquipmentRent.RentDate, EquipmentRent.ReturnDate FROM Equipment INNER JOIN EquipmentRent ON Equipment.EquipmentID = EquipmentRent.EquipmentID INNER JOIN Service ON EquipmentRent.ServiceID = Service.ServiceID WHERE Service.ServiceID != 1 AND Service.ServiceName = " + "'" + svcNmB + "'", sqlConnectB);
DataTable dtB = new DataTable();
adapter.Fill(dtB);
EquipmentGV.DataSource = dtB;
EquipmentGV.DataBind();
if (dtB.Rows.Count == 0)
{
// Label6.Text = "There is currently no equipment assgined to this Service";
}
}
}
//make it so that it clears, then popualtes the notes listbox
notesList.Items.Clear();
String svcNm2 = SvcLB.SelectedValue.ToString();
char separator2 = '|';
String[] svcNmA2 = svcNm2.Split(separator2);
String svcNmB2 = svcNmA2[0];
String serviceName = HttpUtility.HtmlEncode(svcNmB2);
String serviceID = "";
String sqlQuery3 = "Select ServiceID FROM Service WHERE @ServiceName = ServiceName and ServiceID != 1"; //pull the service ID based off the service name
SqlConnection sqlConnect3 = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ConnectionString);
SqlCommand sqlCommand3 = new SqlCommand();
sqlCommand3.Parameters.AddWithValue("ServiceName", serviceName);
sqlCommand3.Connection = sqlConnect3;
sqlCommand3.CommandType = CommandType.Text;
sqlCommand3.CommandText = sqlQuery3;
sqlConnect3.Open();
SqlDataReader queryResults3 = sqlCommand3.ExecuteReader();
//prints values taken from the database
while (queryResults3.Read())
{
serviceID = (queryResults3["ServiceID"].ToString());
}
queryResults3.Close();//closes connection
sqlConnect3.Close();
//"Select WorkflowID from FROM Workflow WHERE ServiceID = @ServiceID";
String sqlQuery4 = "Select s.NoteTitle FROM Note s, Workflow e WHERE e.ServiceID = @ServiceID AND e.WorkflowID = s.WorkflowID AND e.ServiceID != 1";
SqlConnection sqlConnect4 = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ConnectionString);
SqlCommand sqlCommand4 = new SqlCommand();
sqlCommand4.Parameters.AddWithValue("ServiceID", serviceID);
sqlCommand4.Connection = sqlConnect4;
sqlCommand4.CommandType = CommandType.Text;
sqlCommand4.CommandText = sqlQuery4;
sqlConnect4.Open();
// testlbl.Text = serviceID;
SqlDataReader queryResults4 = sqlCommand4.ExecuteReader();
//prints values taken from the database
while (queryResults4.Read())
{
notesList.Items.Add(queryResults4["NoteTitle"].ToString()); //pull and display all service names
}
queryResults4.Close(); //closes connection
sqlConnect4.Close();
//heres the part to get the status bar displayed
String serviceStatus = "";
String payStatus = "";
//Service name stored in this variable svcNmB
SqlConnection sqlConnect = new SqlConnection(WebConfigurationManager.ConnectionStrings["Lab3"].ToString());
sqlConnect.Open();
// Query for getting updateStatus
String updateString = "SELECT UpdateStatus, PaymentStatus FROM Service WHERE ServiceID != 1 AND ServiceName = " + "'" + svcNmB + "'";
SqlCommand sqlCmd1 = new SqlCommand(); // Command needs these 3 things
sqlCmd1.Connection = sqlConnect;
sqlCmd1.CommandType = CommandType.Text;
sqlCmd1.CommandText = updateString;
// Takes all the data from query and executes
SqlDataReader queryResult = sqlCmd1.ExecuteReader();
while (queryResult.Read()) // Populate Ticket Number dropdown
{
serviceStatus = queryResult["UpdateStatus"].ToString();
payStatus = queryResult["PaymentStatus"].ToString();
}
Bar0.Visible = false;
Bar1.Visible = false;
Bar2.Visible = false;
Bar3.Visible = false;
Bar4.Visible = false;
Bar5.Visible = false;
Bar6.Visible = false;
//Start choosing which bar to display
if (serviceStatus.Equals("") && payStatus.Equals(""))
{
Bar0.Visible = true;
Lbl0.Text = "Service Status - Waiting for Scheduling | Payment Status - NOT RECEIVED";
}
else if (serviceStatus.Equals("Not Started") && payStatus.Equals("Incomplete"))
{
Bar1.Visible = true;
Lbl1.Text = "Service Status - NOT STARTED | Payment Status - NOT RECEIVED";
}
else if (serviceStatus.Equals("Not Started") && payStatus.Equals("Complete"))
{
Bar2.Visible = true;
Lbl2.Text = "Service Status - NOT STARTED | Payment Status - RECEIVED";
}
else if (serviceStatus.Equals("In Progress") && payStatus.Equals("Incomplete"))
{
Bar3.Visible = true;
Lbl3.Text = "Service Status - IN PROGRESS | Payment Status - NOT RECEIVED";
}
else if (serviceStatus.Equals("In Progress") && payStatus.Equals("Complete"))
{
Bar4.Visible = true;
Lbl4.Text = "Service Status - IN PROGRESS | Payment Status - RECEIVED";
}
else if (serviceStatus.Equals("Complete") && payStatus.Equals("Incomplete"))
{
Bar5.Visible = true;
Lbl5.Text = "Service Status - COMPLETE| Payment Status - NOT RECEIVED";
}
else if (serviceStatus.Equals("Complete") && payStatus.Equals("Complete"))
{
Bar6.Visible = true;
Lbl6.Text = "Service Status - COMPLETE | Payment Status - RECEIVED";
}
else
{
Bar0.Visible = true;
Lbl0.Text = "The Services status is in limbo.";
}
queryResult.Close();
}