/// <summary>
/// Create a new staff member from a given staff member object and return their new allocted database ID
/// </summary>
/// <param name="staffMember">the staff member data to save in the database</param>
/// <returns>the integer identifier of the new staff entry</returns>
public int Create(DataObject.StaffMember staffMember)
{
using (var dbContext = new AllocationContext(_dbOptions))
{
// Find if this staff member's designation is already known
Model.Designation designation = dbContext.Designations
.SingleOrDefault(d => String.Equals(d.Name, staffMember.Designation, StringComparison.OrdinalIgnoreCase));
if (designation == null)
{
throw new ArgumentException(String.Format("Unrecognised designation: {0}", staffMember.Designation));
}
// Now dig out the skills we already know about. We could ask for only the skills that this
// staff member has, but then we need to check those skills against the staff member's with
// case insensitive comparison on the DB's side. It's probably both faster and simpler to read
// in all the skills and then filter on our side
IDictionary <string, Model.Skill> dbSkills = new Dictionary <string, Model.Skill>(dbContext.Skills.ToDictionary(s => s.Name, s => s),
StringComparer.OrdinalIgnoreCase);
// Create the new staff member entry
StaffMember dbStaffMember = new StaffMember()
{
FirstName = staffMember.FirstName,
LastName = staffMember.LastName,
Alias = staffMember.Alias,
Designation = designation,
LastDouble = staffMember.LastDouble,
PhotoId = staffMember.PhotoId,
RosterOnId = staffMember.RosterOnId,
};
// Now generate their skill entries
var skills = new List <StaffSkill>();
foreach (string skillName in staffMember.Skills)
{
if (!dbSkills.ContainsKey(skillName))
{
throw new ArgumentException(String.Format("Unrecognised skill: {0}", skillName));
}
skills.Add(new StaffSkill()
{
StaffMember = dbStaffMember, Skill = dbSkills[skillName]
});
}
dbStaffMember.StaffSkills = skills;
// Finally, stick them in the database
dbContext.StaffMembers.Add(dbStaffMember);
dbContext.SaveChanges();
// EF Core should magically populate this property after the commit
return(dbStaffMember.StaffMemberId);
}
}
/// <summary>
/// Get a designation matching the Id
/// </summary>
/// <param name="designationId">The id with which to query the databse</param>
/// <returns>The matching designation</returns>
public KnownDesignation Get(int id)
{
using (var dbContext = new AllocationContext(_dbContextOptions))
{
Model.Designation designation = dbContext.Designations.Find(id);
if (designation == null)
{
return(null);
}
return(new KnownDesignation(designation.DesignationId, designation.Name));
}
}
/// <summary>
/// Update an existing designation
/// </summary>
/// <param name="designationId">The id of existing designation</param>
/// <param name="designation">The new designation to update to</param>
/// <returns>Success status</returns>
public bool Update(int designationId, KnownDesignation designation)
{
using (var dbContext = new AllocationContext(_dbContextOptions))
{
Model.Designation dbDesignation = dbContext.Designations.Find(designationId);
if (dbDesignation == null)
{
return(false);
}
dbDesignation.Name = designation.Name;
dbContext.SaveChanges();
return(true);
}
}
/// <summary>
/// Deletes a designation if not associated with any staff
/// </summary>
/// <param name="designationID">The id of designation to delete</param>
/// <returns>Success status</returns>
public bool Delete(int designationId)
{
using (var dbContext = new AllocationContext(_dbContextOptions))
{
Model.Designation designation = dbContext.Designations
.Include(d => d.StaffMembers)
.SingleOrDefault(d => d.DesignationId == designationId);
// Only delete a skill that exists and is not associated with other staff
if (designation == null || designation.StaffMembers?.Any() == true)
{
return(false);
}
dbContext.Designations.Remove(designation);
dbContext.SaveChanges();
return(true);
}
}
/// <summary>
/// Create a new designation
/// </summary>
/// <param name="newDesignation">New designation to store in the database</param>
/// <returns>The database identifier key of newly created designation</returns>
public int Create(KnownDesignation newDesignation)
{
using (var dbContext = new AllocationContext(_dbContextOptions))
{
Model.Designation designation = dbContext.Designations.SingleOrDefault(
d => String.Equals(d.Name, newDesignation.Name, StringComparison.OrdinalIgnoreCase)
);
if (designation == null) // No existing designation was found
{
designation = new Model.Designation()
{
Name = newDesignation.Name
};
dbContext.Designations.Add(designation);
dbContext.SaveChanges();
}
return(designation.DesignationId);
}
}
public UDesignation()
{
_Designation = new Model.Designation();
}
/// <summary>
/// Update an existing staff member in the database with the new values assigned
/// </summary>
/// <param name="staffId">the database ID of the staff member to update</param>
/// <param name="updatedStaffMember">the new values to assign to the given staff member</param>
/// <returns></returns>
public DataObject.StaffMember Update(int staffId, DataObject.StaffMember updatedStaffMember)
{
using (var dbContext = new AllocationContext(_dbOptions))
{
StaffMember staffMember = dbContext.StaffMembers
.Include(sm => sm.Designation)
.Include(sm => sm.StaffSkills)
.ThenInclude(ss => ss.Skill)
.SingleOrDefault(sm => sm.StaffMemberId == staffId);
// Save a lot of work if we don't recognise the ID
if (staffMember == null)
{
throw new ArgumentException(String.Format("Unrecognised staff ID: {0}", staffId));
}
staffMember.FirstName = updatedStaffMember.FirstName;
staffMember.LastName = updatedStaffMember.LastName;
staffMember.Alias = updatedStaffMember.Alias;
staffMember.LastDouble = updatedStaffMember.LastDouble;
staffMember.RosterOnId = updatedStaffMember.RosterOnId;
staffMember.PhotoId = updatedStaffMember.PhotoId;
// If we have a new designation, we need to ask the database
if (updatedStaffMember.Designation != staffMember.Designation.Name)
{
Model.Designation designation = dbContext.Designations
.SingleOrDefault(d => String.Equals(d.Name, updatedStaffMember.Designation, StringComparison.OrdinalIgnoreCase));
staffMember.Designation = designation ?? throw new ArgumentException(String.Format("Unrecognised designation: {0}", updatedStaffMember.Designation));
}
// Work out what skills have been changed
var currentSkills = new HashSet <string>(staffMember.StaffSkills.Select(ss => ss.Skill.Name));
var updatedSkills = new HashSet <string>(updatedStaffMember.Skills);
// Skills that this staff member has that they didn't before
IEnumerable <string> addedSkills = updatedSkills.Except(currentSkills, StringComparer.OrdinalIgnoreCase);
// Skills this staff member used to have but now doesn't
IEnumerable <string> deletedSkills = currentSkills.Except(updatedSkills, StringComparer.OrdinalIgnoreCase);
// Remove all the deleted skills from the staff member
staffMember.StaffSkills
.RemoveAll(s => deletedSkills.Contains(s.Skill.Name, StringComparer.OrdinalIgnoreCase));
// Add the new skills
if (addedSkills.Any())
{
// Query the database for all the skills (figure this is faster than a query per skill)
IDictionary <string, Model.Skill> skills = new Dictionary <string, Model.Skill>(dbContext.Skills.ToDictionary(s => s.Name, s => s),
StringComparer.OrdinalIgnoreCase);
// If there are any skills we're trying to add by name that aren't in the database,
// we have a problem and cannot update
IEnumerable <string> unknownSkills = addedSkills.Except(skills.Keys, StringComparer.OrdinalIgnoreCase);
if (unknownSkills.Any())
{
throw new ArgumentException(String.Format("Unrecognised skills: {0}", String.Join(",", unknownSkills)));
}
// Finally add all the new skills in with new relations
staffMember.StaffSkills.AddRange(addedSkills.Select(s => new StaffSkill()
{
Skill = skills[s],
StaffMember = staffMember
}));
}
// If we've gotten this far, we commit and are done
dbContext.SaveChanges();
return(new DataObject.StaffMember(staffMember.FirstName,
staffMember.LastName,
staffMember.Alias,
staffMember.Designation.Name,
staffMember.StaffSkills.Select(ss => ss.Skill.Name).ToList(),
staffMember.LastDouble,
null,
staffMember.PhotoId,
staffMember.RosterOnId));
}
}