public List <CourseStaticsViewModel> GetAllCourseListByDeptId(int departmentId) { string query = "SELECT Course.CourseCode AS CourseCode, Course.CourseName AS CourseName, Semester.SemesterName AS Semester, Teacher.Name AS AssignedTeacherName From Course LEFT JOIN Departments ON Course.DeptId = Departments.DeptId LEFT JOIN Semester on Semester.SemesterId = Course.SemesterId LEFT JOIN CourseAssignToTeacher on Course.CourseId = CourseAssignToTeacher.CourseId LEFT JOIN Teacher on Teacher.TeacherId = CourseAssignToTeacher.TeacherId AND CourseAssignToTeacher.Action=@insert WHERE Departments.DeptId = @departmentId "; Command = new SqlCommand(query, Connection); Command.Parameters.AddWithValue("@insert", "insert"); Command.Parameters.AddWithValue("@departmentId", departmentId); Connection.Open(); Reader = Command.ExecuteReader(); List <CourseStaticsViewModel> courseStaticList = new List <CourseStaticsViewModel>(); while (Reader.Read()) { CourseStaticsViewModel aCourseStatics = new CourseStaticsViewModel(); aCourseStatics.CourseCode = Reader["CourseCode"].ToString(); aCourseStatics.CourseName = Reader["CourseName"].ToString(); aCourseStatics.Semester = Reader["Semester"].ToString(); string assignto = Reader["AssignedTeacherName"].ToString(); if (string.IsNullOrEmpty(assignto)) { aCourseStatics.AssignedTeacherName = "Not Assigned yet"; } else { aCourseStatics.AssignedTeacherName = Reader["AssignedTeacherName"].ToString(); } courseStaticList.Add(aCourseStatics); } Reader.Close(); Connection.Close(); return(courseStaticList); }
public List <CourseStaticsViewModel> loadCourseInfo(int departmentId) { SqlConnection connection = new SqlConnection(connectionString); string quary = "Select C.Code, C.Name, S.Name AS SemesterName, T.Name AS TeacherName from Course AS C FULL OUTER JOIN TeacherAssign AS TA ON C.Id = TA.CourseId FULL OUTER JOIN Teacher AS T ON TA.TeacherId = T.Id FULL OUTER JOIN Semester AS S ON C.SemisterId = S.Id Where C.DepartementId = '" + departmentId + "' "; SqlCommand command = new SqlCommand(quary, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <CourseStaticsViewModel> courseStaticsViewList = new List <CourseStaticsViewModel>(); while (reader.Read()) { CourseStaticsViewModel coursesStatics = new CourseStaticsViewModel(); coursesStatics.Code = reader["Code"].ToString(); coursesStatics.Name = reader["Name"].ToString(); coursesStatics.Semester = reader["SemesterName"].ToString(); coursesStatics.AssignedTo = reader["TeacherName"].ToString(); courseStaticsViewList.Add(coursesStatics); } reader.Close(); connection.Close(); return(courseStaticsViewList); }
public JsonResult GetCourseStaticsByDepartmentId(Department department) { var getCourseByDeptId = (from course in db.Courses join semester in db.Semesters on course.SemesterId equals semester.Id where course.DepartmentId == department.Id select new { Id = course.Id, Code = course.Code, Name = course.Name, SemesterNo = semester.SemesterNo }).ToList(); List <CourseStaticsViewModel> aStaticsViewModels = new List <CourseStaticsViewModel>(); foreach (var data in getCourseByDeptId) { CourseStaticsViewModel aCourseStaticsViewModel = new CourseStaticsViewModel(); aCourseStaticsViewModel.Code = data.Code; aCourseStaticsViewModel.Name = data.Name; aCourseStaticsViewModel.SemesterNo = data.SemesterNo; int courseId = data.Id; var getTeacher = (from assignCourse in db.AssignCrouses join teacher in db.Teachers on assignCourse.TeacherId equals teacher.Id where assignCourse.CourseId == courseId && assignCourse.IsUnassign == false select new { Name = teacher.Name }).SingleOrDefault(); if (getTeacher != null) { string teacherName = getTeacher.Name; if (string.IsNullOrEmpty(teacherName)) { aCourseStaticsViewModel.TeacherName = "Not Assigned Yet"; } else { aCourseStaticsViewModel.TeacherName = teacherName; } } else { aCourseStaticsViewModel.TeacherName = "Not Assigned Yet"; } aStaticsViewModels.Add(aCourseStaticsViewModel); } return(Json(aStaticsViewModels)); }
//************************************** For 6th Story By Warid ********************************* public List <CourseStaticsViewModel> GetCourseStatitics(string deptName) { string query = " select c.CourseCode as CourseCode, c.CourseName as CourseName,sem.SemesterName as SemesterName," + " t.TeacherName as TeacherName,cat.Status as Status " + "from Course as c " + "left join CourseAssignToTeacher as cat " + "on c.Id=cat.CourseId " + "left join Teacher as t " + "on cat.TeacherId= t.Id " + "inner join Semester as sem " + "on c.SemesterId= sem.Id" + " inner join Department as d on c.DeptId=d.Id" + " where d.DepartmentName= '" + deptName + "'"; Command = new SqlCommand(query, Connection); Connection.Open(); Reader = Command.ExecuteReader(); List <CourseStaticsViewModel> courseStaticsViewModels = new List <CourseStaticsViewModel>(); while (Reader.Read()) { CourseStaticsViewModel courseStaticsViewModel = new CourseStaticsViewModel(); courseStaticsViewModel.CourseCode = Reader["CourseCode"].ToString(); courseStaticsViewModel.CourseName = Reader["CourseName"].ToString(); courseStaticsViewModel.Semester = Reader["SemesterName"].ToString(); courseStaticsViewModel.TeacherName = Reader["TeacherName"].ToString(); courseStaticsViewModel.Status = Reader["Status"].ToString(); if (courseStaticsViewModel.Status == "Unassigned") { courseStaticsViewModel.TeacherName = "Not Assign Yet"; } courseStaticsViewModels.Add(courseStaticsViewModel); } Reader.Close(); Connection.Close(); return(courseStaticsViewModels); }
public List <CourseStaticsViewModel> GetAllSDepartments() { string query = "SELECT * FROM Department"; Command = new SqlCommand(query, Connection); Connection.Open(); Reader = Command.ExecuteReader(); List <CourseStaticsViewModel> departments = new List <CourseStaticsViewModel>(); while (Reader.Read()) { CourseStaticsViewModel department = new CourseStaticsViewModel(); department.DepartmentName = Reader["DepartmentName"].ToString(); departments.Add(department); } Reader.Close(); Connection.Close(); return(departments); }