// GET: aAddDepartment public ActionResult Index() { aDepartment EmptyDepartment = new aDepartment(); //object created to make a list of LMs with their IDs and Names stored in it List <string> departmentNames = new List <string>(); //list to display department names int temproleID = 0; //displays a list of existing departments for the user's reference var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string queryString = "Select Department_Name FROM dbo.Department "; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(queryString, connection); connection.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { departmentNames.Add((string)reader[0]); } } //for the next query to get the role id of line manager queryString = "Select Role_Id FROM dbo.Role Where Role_Name= 'LM'"; command = new SqlCommand(queryString, connection); using (var reader = command.ExecuteReader()) { while (reader.Read()) { temproleID = (int)reader[0]; } } System.Diagnostics.Debug.WriteLine("This the LM role ID - " + temproleID); //displays a list of employees who fall under the staff type category Line Manager queryString = "Select Employee.Employee_ID, First_Name,Last_Name FROM dbo.Employee Full Join dbo.Employee_Role On dbo.Employee_Role.Employee_ID = dbo.Employee.Employee_ID WHERE Employee_Role.Role_ID ='" + temproleID + "' AND Account_Status != 'False'"; //display list of lms command = new SqlCommand(queryString, connection); using (var reader = command.ExecuteReader()) { while (reader.Read()) { string fullName = (string)reader[1] + " " + (string)reader[2]; EmptyDepartment.linemanagerSelectionListOptions.Add((int)reader[0], fullName); } } connection.Close(); ViewBag.departmentNames = departmentNames; //sends the list of departments to the view } return(View(EmptyDepartment)); //sends the list of line managers to the view }
// GET: aEditDepartment public ActionResult Index() { aDepartment EmptyDepartment = new aDepartment(); //object created to make a list of LMs with their IDs and Names stored in it List <string> departmentNames = new List <string>(); //list to display department names List <string> departmentDetails = new List <string>(); int temproleID = 0; //to display a list of department along with the line managers var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string queryString = "Select Department.Department_Name, Employee.First_Name, Employee.Last_Name FROM dbo.Department INNER JOIN dbo.Employee ON Employee.Employee_Id = Department.Line_Manager_ID Where Employee.Employee_Id = Department.Line_Manager_ID"; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(queryString, connection); connection.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { string deptNames = (string)reader[0]; string lmNames = (string)reader[1] + " " + (string)reader[2]; string tempDisplay = deptNames + "-" + lmNames; departmentDetails.Add(tempDisplay); } } queryString = "Select Department_Name FROM dbo.Department"; command = new SqlCommand(queryString, connection); { using (var reader = command.ExecuteReader()) while (reader.Read()) { departmentNames.Add((string)reader[0]); } } queryString = "Select Role_Id FROM dbo.Role Where Role_Name= 'LM'"; command = new SqlCommand(queryString, connection); using (var reader = command.ExecuteReader()) { while (reader.Read()) { temproleID = (int)reader[0]; } } //System.Diagnostics.Debug.WriteLine("This the LM role ID - " + temproleID); //display a list of LM's for the user to select a new one for a particular department queryString = "Select Employee.Employee_ID, First_Name,Last_Name FROM dbo.Employee Full Join dbo.Employee_Role On dbo.Employee_Role.Employee_ID = dbo.Employee.Employee_ID WHERE Employee_Role.Role_ID ='" + temproleID + "' AND Account_Status != 'False'"; command = new SqlCommand(queryString, connection); using (var reader = command.ExecuteReader()) { while (reader.Read()) { string fullName = (string)reader[1] + " " + (string)reader[2]; EmptyDepartment.linemanagerSelectionListOptions.Add((int)reader[0], fullName); } } connection.Close(); ViewBag.departmentNames = departmentNames; //sends the list of departments to the view ViewBag.departmentDetails = departmentDetails; } return(View(EmptyDepartment)); //sends the list of line managers to the view }