// // GET: /ViewCourseStatus/ public ViewResult Index(int?departmentId) { ViewBag.DepartmentId = new SelectList(db.Departments, "DepartmentId", "Name"); List <ViewCourseStatus> filteredViews = new List <ViewCourseStatus>(); var courses = db.Courses.Where(c => c.DepartmentId != 0); var selectedViews = db.CourseAssigns.Include(c => c.Teacher).Include(c => c.Course); if (departmentId != null) { courses = db.Courses.Where(c => c.DepartmentId == departmentId); selectedViews = db.CourseAssigns.Include(c => c.Teacher).Include(c => c.Course).Where(c => (c.Course.DepartmentId == departmentId)); } foreach (var course in courses) { ViewCourseStatus aViewStatus = new ViewCourseStatus(); if (db.CourseAssigns.Any(c => c.CourseId == course.CourseId)) { aViewStatus.Code = course.Code; aViewStatus.Name = course.Name; var teacherName = selectedViews.Include(c => c.Teacher).Where(c => c.CourseId == course.CourseId); aViewStatus.Teacher = teacherName.First().Teacher.Name; var semesterName = db.Courses.Include(c => c.Semester).Where(c => c.CourseId == course.CourseId); aViewStatus.Semester = semesterName.First().Semester.Name; } else { aViewStatus.Code = course.Code; aViewStatus.Name = course.Name; aViewStatus.Teacher = "Not Assigned"; var semesterName = db.Courses.Include(c => c.Semester).Where(c => c.CourseId == course.CourseId); aViewStatus.Semester = semesterName.First().Semester.Name; } filteredViews.Add(aViewStatus); } if (filteredViews.Count <= 0) { ViewBag.Message = "No courses entered."; } return(View(filteredViews)); }
internal List <ViewCourseStatus> GetAllCoursesStatus() { connection.ConnectionString = connectionString; string query = "SELECT * FROM FinalCourseStatisticDetails"; connection.ConnectionString = connectionString; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <ViewCourseStatus> viewCourseList = new List <ViewCourseStatus>(); while (reader.Read()) { ViewCourseStatus viewCourseStatus = new ViewCourseStatus(); viewCourseStatus.DepartmentId = (int)reader["DepartmentID"]; viewCourseStatus.DepartmentName = reader["DepartmentName"].ToString(); viewCourseStatus.CourseId = (int)reader["CourseID"]; viewCourseStatus.CourseCode = reader["CourseCode"].ToString(); viewCourseStatus.CourseName = reader["CourseName"].ToString(); viewCourseStatus.Semester = (int)reader["Semester"]; viewCourseStatus.SemesterName = reader["SemesterName"].ToString(); //viewCourseStatus.TeacherName = reader["TeacherName"].ToString(); string s = reader["TeacherName"].ToString(); String courseStatus = reader["CourseStatus"].ToString(); if (courseStatus == "") { viewCourseStatus.TeacherName = "Not Assigned Yet"; } else { viewCourseStatus.TeacherName = s; } viewCourseList.Add(viewCourseStatus); } reader.Close(); connection.Close(); return(viewCourseList); }