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