public List <CourseAssignmentVm> GetAllCourses(int id)
{
Query = "select ac.Id,c.Code, c.Name CourseName, s.Name SemesterName ,e.Name Teacher from AssignCourse ac join Course c on c.Id = ac.CourseId join Semester s On s.Id = c.SemesterId JOin Department d on d.Id = c.DepartmentId JOIN Employee e On e.Id = ac.TeacherId WHERE d.Id = @dId";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("dId", SqlDbType.Int);
Command.Parameters["dId"].Value = id;
Reader = Command.ExecuteReader();
List <CourseAssignmentVm> courses = new List <CourseAssignmentVm>();
while (Reader.Read())
{
CourseAssignmentVm course = new CourseAssignmentVm()
{
Id = (int)Reader["Id"],
CourseName = Reader["CourseName"].ToString(),
CourseCode = Reader["Code"].ToString(),
SemesterName = Reader["SemesterName"].ToString(),
TeacherName = Reader["Teacher"].ToString(),
};
courses.Add(course);
}
Reader.Close();
Connection.Close();
return(courses);
}
public CourseAssignmentVm GetTeacherInfoById(int id)
{
Query = "SELECT SUM(c.Credit) CreditTaken FROM AssignCourse ac JOIN Course c ON c.Id = ac.CourseId JOIN Employee e on e.Id = ac.TeacherId WHERE ac.TeacherId = @tId";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("tId", SqlDbType.Int);
Command.Parameters["tId"].Value = id;
Reader = Command.ExecuteReader();
CourseAssignmentVm teacher = null;
if (Reader.Read())
{
string credit = null;
teacher = new CourseAssignmentVm();
credit = Reader["CreditTaken"].ToString();
if (credit == "")
{
teacher.Message = "0";
}
else
{
teacher.CreditTaken = Convert.ToDouble(credit);
}
}
Reader.Close();
Connection.Close();
return(teacher);
}
public ActionResult ManageAssignedCourses(CourseAssignmentVm courseAssignment)
{
ViewBag.Departments = GetDepartmentsForDropdownList();
int id = courseAssignment.DepartmentId;
ViewBag.Courses = anAcademicManager.GetAllCourses(id);
return(View());
}
public JsonResult GetTeacherCourseByDepartmentId(int id)
{
List <Employee> teacherInfo = anAcademicManager.GetTeacherByDepartmentId(id);
var courseInfo = anAcademicManager.GetCourseByDepartmentId(id);
CourseAssignmentVm assignmentVm = new CourseAssignmentVm();
assignmentVm.Courses = courseInfo;
assignmentVm.Employees = teacherInfo;
return(Json(assignmentVm));
}
public string SaveCourseAssignment(CourseAssignmentVm courseAssignment)
{
if (anAcademicGateway.IsCourseAssigned(courseAssignment))
{
return("Course alrerady assigned!");
}
int rowAffected = anAcademicGateway.SaveCourseAssignment(courseAssignment);
if (rowAffected > 0)
{
return("Course assigned successfully");
}
return("Saving failed!");
}
public int SaveCourseAssignment(CourseAssignmentVm courseAssignment)
{
Query = "INSERT INTO AssignCourse VALUES(@teacherId, @courseId,@departmentId)";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("teacherId", SqlDbType.Int);
Command.Parameters["teacherId"].Value = courseAssignment.TeacherId;
Command.Parameters.Add("courseId", SqlDbType.Int);
Command.Parameters["courseId"].Value = courseAssignment.CourseId;
Command.Parameters.Add("departmentId", SqlDbType.Int);
Command.Parameters["departmentId"].Value = courseAssignment.DepartmentId;
int isRowEffected = Command.ExecuteNonQuery();
Connection.Close();
return(isRowEffected);
}
public bool IsCourseAssigned(CourseAssignmentVm courseAssignment)
{
Query = "SELECT * FROM AssignCourse Where TeacherId = @teacherId And CourseId = @courseId ";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("teacherId", SqlDbType.Int);
Command.Parameters["teacherId"].Value = courseAssignment.TeacherId;
Command.Parameters.Add("courseId", SqlDbType.Int);
Command.Parameters["courseId"].Value = courseAssignment.CourseId;
Reader = Command.ExecuteReader();
bool hasRow = false;
if (Reader.HasRows)
{
hasRow = true;
}
Reader.Close();
Connection.Close();
return(hasRow);
}
public ActionResult AssignCourseToTeacher(CourseAssignmentVm courseAssignment)
{
ViewBag.Departments = GetDepartmentsForDropdownList();
ViewBag.Message = anAcademicManager.SaveCourseAssignment(courseAssignment);
return(View());
}
