/************************************************************************************
* Instructor
************************************************************************************/
/// <summary>
//Find all instructors in the database.Include each instructor's cohort.
/// </summary>
/// <returns>A list of instructors in which each instructor object contains their cohort object.</returns>
public List <CreateInstructor> GetAllInstructorsWithCohort()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT i.Id, i.FirstName, i.LastName, i.SlackHandle, i.Speciality, i.CohortId, c.CohortName " +
"FROM Instructor i INNER JOIN Cohort c ON i.CohortID = c.id";
SqlDataReader reader = cmd.ExecuteReader();
List <CreateInstructor> instructors = new List <CreateInstructor>();
while (reader.Read())
{
CreateInstructor instructor = new CreateInstructor
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
SlackHandle = reader.GetString(reader.GetOrdinal("SlackHandle")),
Speciality = reader.GetString(reader.GetOrdinal("Speciality")),
CohortId = reader.GetInt32(reader.GetOrdinal("CohortId")),
CreateCohort = new CreateCohort
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
CohortName = reader.GetString(reader.GetOrdinal("CohortName"))
}
};
instructors.Add(instructor);
}
reader.Close();
return(instructors);
}
}
}
public IActionResult Create(InstructorViewModel viewModel, [FromServices] CreateInstructor command)
{
if (ModelState.IsValid)
{
command.Instructor = mapper.Map <Instructor>(viewModel);
//only look for courses if instructor choose to be assigned one
if (viewModel.Filters != null)
{
IList <Filter> filter = viewModel.Filters.ToList();
IList <Course> courses = queryDb.Courses.ToList();
IList <Course> coursesToBeAdded = new List <Course>();
foreach (var item in filter)
{
if (item.Selected)
{
coursesToBeAdded.Add(courses.Where(i => i.CourseId == item.Id).Single());
}
}
command.Courses = coursesToBeAdded;
}
command.Run();
if (command.Status)
{
return(RedirectToAction("Index", "Instructor"));
}
else
{
return(RedirectToAction("Error", "Instructor"));
}
}
return(View());
}
public IActionResult Edit(CreateInstructor model)
{
var instructor = _context.Instructors.FirstOrDefault(i => i.ID == model.Instructor.ID);
instructor.LastName = model.Instructor.LastName;
instructor.HireDate = model.Instructor.HireDate;
instructor.FirstMidName = model.Instructor.FirstMidName;
_context.Instructors.Update(instructor);
var officeAssignment = _context.OfficeAssignments.FirstOrDefault(oa => oa.InstructorID == model.Instructor.ID);
officeAssignment.Location = model.Location;
_context.OfficeAssignments.Update(officeAssignment);
var instructorCourseAssignments = _context.CourseAssignments.Where(ca => ca.InstructorID == model.Instructor.ID).ToList();
var listOfNewCourseAssignments = new List <CourseAssignment>();
foreach (var course in instructorCourseAssignments)
{
if (model.SelectedCourses.Contains(course.CourseID.ToString()) == false)
{
listOfNewCourseAssignments.Add(course);
}
}
_context.CourseAssignments.RemoveRange(instructorCourseAssignments);
_context.SaveChanges();
foreach (var course in listOfNewCourseAssignments)
{
instructorCourseAssignments.Remove(course);
}
var instructorCourseAssignmentsCourseIds = instructorCourseAssignments.Select(ca => ca.CourseID).ToList();
foreach (var courseId in model.SelectedCourses)
{
var newCourseAssignment = new CourseAssignment()
{
CourseID = int.Parse(courseId),
InstructorID = model.Instructor.ID,
Instructor = instructor
};
if (instructorCourseAssignmentsCourseIds.Contains(newCourseAssignment.CourseID) == false)
{
instructorCourseAssignments.Add(newCourseAssignment);
}
}
_context.CourseAssignments.AddRange(instructorCourseAssignments);
_context.SaveChanges();
return(RedirectToAction("Index", "Instructors"));
}
/// <summary>
/// Insert a new instructor into the database. Assign the instructor to an existing cohort.
/// NOTE: This method sends data to the database,
/// it does not get anything from the database, so there is nothing to return.
/// </summary>
public void AddInstructor(CreateInstructor instructor)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO Instructor (FirstName, LastName, SlackHandle, Speciality, CohortId) " +
"VALUES (@FirstName, @LastName, @SlackHandle, @Speciality, @CohortId)";
cmd.Parameters.Add(new SqlParameter("@FirstName", instructor.FirstName));
cmd.Parameters.Add(new SqlParameter("@LastName, ", instructor.LastName));
cmd.Parameters.Add(new SqlParameter("@SlackHandle, ", instructor.SlackHandle));
cmd.Parameters.Add(new SqlParameter("@Speciality, ", instructor.Speciality));
cmd.Parameters.Add(new SqlParameter("@CohortId", instructor.CohortId));
cmd.ExecuteNonQuery();
}
}
}
public IActionResult Create(CreateInstructor model)
{
if (!this.ModelState.IsValid)
{
return(this.View(model));
}
var newInstructor = new Instructor()
{
FirstMidName = model.Instructor.FirstMidName,
LastName = model.Instructor.LastName,
};
_context.Instructors.Add(newInstructor);
_context.SaveChanges();
var officeAssignment = new OfficeAssignment()
{
Location = model.Location,
InstructorID = newInstructor.ID
};
_context.OfficeAssignments.Add(officeAssignment);
var listOfCourseAssignments = new List <CourseAssignment>();
foreach (var courseId in model.SelectedCourses)
{
var newCourseAssignment = new CourseAssignment()
{
CourseID = int.Parse(courseId),
InstructorID = newInstructor.ID
};
listOfCourseAssignments.Add(newCourseAssignment);
}
_context.AddRange(listOfCourseAssignments);
_context.SaveChanges();
return(RedirectToAction("Index", "Instructors"));
}
public async Task <IActionResult> Edit(int?id)
{
var courses = _context.Courses.ToArray();
this.ViewData["courses"] = courses;
var instructor = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.AsNoTracking()
.FirstOrDefaultAsync(m => m.ID == id);
var newCreateInstructor = new CreateInstructor()
{
Instructor = instructor,
Location = instructor.OfficeAssignment.Location,
SelectedCourses = instructor.CourseAssignments.Select(c => c.CourseID.ToString()).ToArray()
};
return(View(newCreateInstructor));
}
