/// <summary>
/// Calls the unconfirmed booking method for each attendee of the group booking
/// </summary>
/// <param name="date">The date</param>
/// <param name="users">The attendees</param>
/// <param name="teams">The teams</param>
/// <param name="startTime">The start time</param>
/// <param name="endTime">The end time</param>
/// <param name="resourceId">The resource</param>
/// <param name="userId">The user</param>
public void AddGroupBooking(DateTime date, List <string> users, List <string> teams, Guid?startTime, Guid?endTime, Guid?resourceId, Guid?userId)
{
var db = new ReScrumEntities();
var startSlot = db.Slots.Where(s => s.SlotId == startTime).FirstOrDefault();
var endSlot = db.Slots.Where(s => s.SlotId == endTime).FirstOrDefault();
var user = db.Users.Where(u => u.UserId == userId).FirstOrDefault();
var resource = db.Resources.Where(r => r.ResourceId == resourceId).FirstOrDefault();
var slotList = db.Slots.Where(s => s.StartTime >= startSlot.StartTime &&
s.EndTime <= endSlot.EndTime).ToList();
//Add booking for Attendees
foreach (string data in users)
{
var attendee = db.Users.Where(u => u.UserId == new Guid(data)).FirstOrDefault();
AddUnconfirmedBooking(db, slotList, attendee, resource, date, user);
}
//Add booking for team memebers
foreach (string data in teams)
{
var team = db.Teams.Where(u => u.TeamId == new Guid(data)).FirstOrDefault();
foreach (DataLayer.Models.User member in team.Members)
{
AddUnconfirmedBooking(db, slotList, member, resource, date, user);
}
}
db.SaveChanges();
}
/// <summary>
/// Calculates the frequency rate for an individual resource between a given date range
/// Frequency rate: percentage of time space is used compared to its availability
/// </summary>
/// <param name="startDate">The start date to search from</param>
/// <param name="endDate">The end date to search to</param>
/// <param name="resource">The resource to calculate the frequency rate for</param>
/// <returns>The frequency rate for the resource</returns>
public float CalculateResourceFrequencyRate(DateTime startDate, DateTime endDate, Guid?resourceId)
{
var db = new ReScrumEntities();
var resource = db.Resources.Where(r => r.ResourceId == resourceId).FirstOrDefault();
//Calculate how many days
var dateRange = endDate.DayOfYear - startDate.DayOfYear + 1;
//Resource availability
var slots = db.Slots.Count();
var availability = slots * dateRange;
//How many times the resource was used
var hoursUsed = GetSlotsUtilised(startDate, endDate, resource.ResourceId).Count();
//Number of slots the space was in use/Number of slots available = frequency rate %
var frequencyRate = ((float)hoursUsed / (float)availability);
if (float.IsNaN(frequencyRate))
{
return(0);
}
return(frequencyRate);
}
/// <summary>
/// Adds a booking to the database
/// </summary>
/// <param name="booking">The booking to be added</param>
public void AddBooking(Booking booking)
{
var db = new ReScrumEntities();
var slot = db.Slots.Where(s => s.SlotId == booking.Slot.SlotId).FirstOrDefault();
var user = db.Users.Where(u => u.UserId == booking.User.UserId).FirstOrDefault();
//check if a current booking exist for the user, date and time slot
var newBooking = db.Booking.Where(b => b.User.UserId == user.UserId &&
b.Slot.SlotId == slot.SlotId &&
b.Date == booking.Date).FirstOrDefault();
//if not create a new booking parameter
if (newBooking == null)
{
newBooking = new DataLayer.Models.Booking();
}
//set/update the booking values
newBooking.Date = booking.Date;
newBooking.Slot = slot;
newBooking.Resource = db.Resources.Where(r => r.ResourceId == booking.Resource.ResourceId).FirstOrDefault();
newBooking.User = user;
newBooking.BookedBy = user;
//if it is a new booking add it (creating new guid id), else the exisitng booking will be updated => no double booking
if (newBooking.BookingId == null)
{
db.Booking.Add(newBooking);
}
db.SaveChanges();
}
/// <summary>
/// Gets all of the users that are not a member of the team
/// </summary>
/// <returns>Returns a list of users</returns>
public List <User> GetPotentialTeamMembers(Guid?teamId)
{
var db = new ReScrumEntities();
var team = db.Teams.Where(t => t.TeamId == teamId).FirstOrDefault();
var members = team.Members.ToList();
//get the active users
var users = db.Users.Where(u => u.CancellationDate == null).ToList();
//get all users who are not already members of the team
var nonMembers = users.Except(members);
var potenatialUsers = new List <User>();
foreach (DataLayer.Models.User data in nonMembers)
{
var user = new User
{
UserId = data.UserId,
EmployeeNumber = data.EmployeeNumber,
Forename = data.Forename,
Surname = data.Surname,
Email = data.Email,
JobTitle = data.JobTitle,
IsLineManager = data.IsLineManager,
IsAdministrator = data.IsAdministrator,
};
potenatialUsers.Add(user);
}
return(potenatialUsers);
}
/// <summary>
/// Checks the email and password match for a user in the system
/// </summary>
/// <param name="email">The email</param>
/// <param name="password">The password</param>
/// <returns>True or false</returns>
public bool Login(String email, String password)
{
var db = new ReScrumEntities();
var user = db.Users.Where(u => u.Email.Equals(email)).FirstOrDefault();
if (user == null)
{
return(false);
}
var account = db.Accounts.Where(a => a.User.UserId == user.UserId).FirstOrDefault();
if (account == null)
{
return(false);
}
var savedPassword = Encoding.UTF8.GetString(account.Password);
if (!savedPassword.Equals(password))
{
return(false);
}
return(true);
}
/// <summary>
/// Calculates the occupnacy rate for an individual resource between a given date range
/// Occupancy rate: how full the space is compared to its capacity
/// </summary>
/// <param name="startDate">The start date to search from</param>
/// <param name="endDate">The end date to search to</param>
/// <param name="resource">The resource to calculate the occupancy rate for</param>
/// <returns>The occupancy rate for the resource</returns>
public float CalculateResourceOccupancyRate(DateTime startDate, DateTime endDate, Guid?resourceId)
{
var db = new ReScrumEntities();
//Resource capacity
var resource = db.Resources.Where(r => r.ResourceId == resourceId).FirstOrDefault();
float capacity = resource.Capacity;
//Total number of people occupaying resource
var scheduleEntries = GetSlotsUtilised(startDate, endDate, resourceId);
float occupants = scheduleEntries.Count();
//Number of hours resource was in use
var date = startDate;
float hoursResourceWasInUse = 0;
while (date <= endDate)
{
//number of hours the resource was in use on "date" => e.g. used for 4 hours on Wednesday, 2 hours on Friday
var hoursUsedInDay = scheduleEntries.Where(b => b.Date == date).ToList();
hoursResourceWasInUse += (float)hoursUsedInDay.Count();
date = date.AddDays(1);
}
//Occupancy rate = occupants / (capacity * hoursResourceWasInUse)
var occupancyRate = (occupants / (capacity * hoursResourceWasInUse));
if (float.IsNaN(occupancyRate))
{
return(0);
}
return(occupancyRate);
}
/// <summary>
/// Calculates the occupnacy rate for a slot time for a given date
/// </summary>
/// <param name="date">The date</param>
/// <param name="slotId">The slot time</param>
/// <returns>The occupancy rate for the time slot</returns>
public float CalculateSlotOccupancyRate(DateTime date, Guid?slotId)
{
var db = new ReScrumEntities();
/// Occupancy rate: how full the space is compared to its capacity
float occupancyRate = 0;
//All of the bookings for the time on the day -> all 9am booking for Tuesday 18th April
var bookings = db.Booking.Where(b => b.Date == date && b.Slot.SlotId == slotId).ToList();
if (bookings.Count() < 1)
{
return(occupancyRate);
}
//Get all the resources used in the bookings
var resources = bookings.Select(b => b.Resource).ToList();
foreach (DataLayer.Models.Resource resource in resources)
{
var totalOccupants = bookings.Where(r => r.Resource.ResourceId == resource.ResourceId).Count();
occupancyRate += (float)totalOccupants / (float)resource.Capacity;
}
if (float.IsNaN(occupancyRate))
{
return(0);
}
occupancyRate = occupancyRate / resources.Count();
return(occupancyRate);
}
/// <summary>
/// Creates a new user and account
/// </summary>
/// <param name="user">The user</param>
/// <param name="password">The account</param>
public void AddUserAndAccount(User user, string password)
{
var db = new ReScrumEntities();
var newUser = new data.User
{
EmployeeNumber = user.EmployeeNumber,
Forename = user.Forename,
Surname = user.Surname,
Email = user.Email,
JobTitle = user.JobTitle,
IsLineManager = user.IsLineManager,
IsAdministrator = user.IsAdministrator,
};
db.Users.Add(newUser);
var team = db.Teams.Where(t => t.TeamId == user.Team).FirstOrDefault();
newUser.Teams.Add(team);
var account = new data.Account
{
User = newUser,
Password = Encoding.UTF8.GetBytes(password),
};
db.Accounts.Add(account);
db.SaveChanges();
}
/// <summary>
/// Calculates the occupnacy rate for the company between a given date range
/// </summary>
/// <param name="startDate">The start date to search from</param>
/// <param name="endDate">The end date to search to</param>
/// <returns>The occupancy rate for the company</returns>
public float CalculateOccupancyRate(DateTime startDate, DateTime endDate)
{
var db = new ReScrumEntities();
var resources = db.Resources.Where(r => r.CancellationDate == null).ToList();
/// Occupancy rate: how full the space is compared to its capacity
float occupancyRate = 0;
float resourcesUsedInDateRange = 0;
foreach (DataLayer.Models.Resource resource in resources)
{
//only calculate the occupany rate if the resource was used in the date range
var bookings = GetSlotsUtilised(startDate, endDate, resource.ResourceId);
if (bookings.Count > 0)
{
occupancyRate += CalculateResourceOccupancyRate(startDate, endDate, resource.ResourceId);
resourcesUsedInDateRange++;
}
}
if (float.IsNaN(occupancyRate) || occupancyRate == 0.0)
{
return(0);
}
occupancyRate = occupancyRate / resourcesUsedInDateRange;
return(occupancyRate);
}
/// <summary>
/// Gets all of a resources bookings for a week
/// </summary>
/// <param name="resourceId">The resource</param>
/// <param name="date">The date</param>
/// <returns>A list of bookings</returns>
public List <Booking> GetThisWeeksBookingsForAResource(Guid?resourceId, DateTime date)
{
//work out the start of the week
var startDate = FindStartDate(date);
var endDate = date.AddDays(5);
//db connection
var db = new ReScrumEntities();
//the bookings that belong to the user
var data = db.Booking.Where(u => u.Resource.ResourceId == resourceId).ToList();
var bookings = new List <Booking>();
if (data.Count < 1)
{
return(bookings);
}
//get this weeks bookings
data = data.Where(b => b.Date >= startDate && b.Date < endDate).ToList();
var bookingEntries = converter.ConvertDataBookingListToWrapper(data);
foreach (Booking entry in bookingEntries)
{
bookings.Add(entry);
}
return(bookings);
}
//ISlotService slotService = new SlotService();
#endregion
/// <summary>
/// Gets of all of a users bookings for a week
/// </summary>
/// <param name="userId">The user</param>
/// <returns>A list of bookings</returns>
public List <Booking> GetThisWeeksBookings(Guid?userId)
{
//work out the start of the week
var date = FindStartDate(DateTime.Today);
var endDate = date.AddDays(5);
//db connection
var db = new ReScrumEntities();
//the bookings that belong to the user
var data = db.Booking.Where(u => u.User.UserId == userId).ToList();
var bookings = new List <Booking>();
//if there are no bookings return the empty list
if (data.Count < 1)
{
return(bookings);
}
//get this weeks bookings
data = data.Where(b => b.Date >= date && b.Date < endDate).ToList();
var bookingEntries = converter.ConvertDataBookingListToWrapper(data);
foreach (Booking entry in bookingEntries)
{
bookings.Add(entry);
}
return(bookings);
}
/// <summary>
/// Deletes an existing team from the database
/// </summary>
/// <param name="data">The team to be deleted</param>
public void DeleteTeam(Guid?teamId)
{
var db = new ReScrumEntities();
var team = db.Teams.Where(u => u.TeamId == teamId).FirstOrDefault();
db.Teams.Remove(team);
db.SaveChanges();
}
/// <summary>
/// Gets the resource using the resourceId
/// </summary>
/// <returns>Returns the resource</returns>
public Resource GetResource(Guid?resourceId)
{
var db = new ReScrumEntities();
var data = db.Resources.Where(u => u.ResourceId == resourceId).FirstOrDefault();
var resource = converter.ConvertDataResourceToWrapper(data);
return(resource);
}
/// <summary>
/// Calculates the number of slots (hours) the resource was in use
/// </summary>
/// <param name="startDate">The start date to search from</param>
/// <param name="endDate">The end date to search to</param>
/// <param name="resource">The resource</param>
/// <returns>The number of slots the resource was booked for during the date range entered</returns>
public List <DataLayer.Models.Booking> GetSlotsUtilised(DateTime startDate, DateTime endDate, Guid?resourceId)
{
var db = new ReScrumEntities();
var bookings = db.Booking.Where(r => r.Resource.ResourceId == resourceId &&
r.Date >= startDate &&
r.Date <= endDate).ToList();
return(bookings);
}
/// <summary>
/// Deletes a resource from the database
/// </summary>
/// <param name="data">The resource to be deleted</param>
public void DeleteResource(Guid?resourceId)
{
var db = new ReScrumEntities();
var resource = db.Resources.Where(u => u.ResourceId == resourceId).FirstOrDefault();
resource.CancellationDate = DateTime.Today;
db.SaveChanges();
}
/// <summary>
/// Gets the user using the id
/// </summary>
/// <returns>Returns the user</returns>
public User GetUser(Guid?userId)
{
var db = new ReScrumEntities();
var data = db.Users.Where(u => u.UserId == userId).FirstOrDefault();
var user = converter.ConvertDataUserToWrapper(data);
return(user);
}
/// <summary>
/// Deletes a booking from the database
/// </summary>
/// <param name="bookingId">The id of the booking to be removed</param>
public void DeleteBooking(Guid?bookingId)
{
var db = new ReScrumEntities();
var booking = db.Booking.Where(b => b.BookingId == bookingId).FirstOrDefault();
db.Booking.Remove(booking);
db.SaveChanges();
}
/// <summary>
/// Updates an existing slot
/// </summary>
/// <param name="data">The new slot details</param>
public void UpdateSlot(Slot data)
{
var db = new ReScrumEntities();
var slot = db.Slots.Where(u => u.SlotId == data.SlotId).FirstOrDefault();
slot.Time = data.Time;
db.SaveChanges();
}
/// <summary>
/// Deletes an existing slot from the database
/// </summary>
/// <param name="data">The slot to be deleted</param>
public void DeleteSlot(Guid?slotId)
{
var db = new ReScrumEntities();
var slot = db.Slots.Where(u => u.SlotId == slotId).FirstOrDefault();
slot.CancellationDate = DateTime.Today;
db.SaveChanges();
}
/// <summary>
/// Checks the email is not already registered in the database
/// </summary>
/// <param name="email">The email</param>
/// <returns>True or false</returns>
public bool CheckEmailIsUnique(String email)
{
var db = new ReScrumEntities();
if (db.Users.Where(u => u.Email.Equals(email)).Any())
{
return(false);
}
return(true);
}
/// <summary>
/// Calculates the utilisation rate for a time slot for a given date
/// </summary>
/// <param name="startDate">The date</param>
/// <param name="endDate">The time slot</param>
/// <returns>The utilisation rate for the time slot</returns>
public float CalculateSlotUtilisationRate(DateTime date, Guid?slotId)
{
var db = new ReScrumEntities();
//Utilisation rate: frequency rate * occupancy rate
float frequencyRate = CalculateSlotFrequencyRate(date, slotId);
float occupancyRate = CalculateSlotOccupancyRate(date, slotId);
var utilisationRate = frequencyRate * occupancyRate;
return(utilisationRate);
}
/// <summary>
/// Updates an existing team
/// </summary>
/// <param name="data">The new team details</param>
public void UpdateTeam(Team data)
{
var db = new ReScrumEntities();
var team = db.Teams.Where(u => u.TeamId == data.TeamId).FirstOrDefault();
team.TeamId = data.TeamId;
team.Name = data.Name;
team.Colour = data.Colour;
db.SaveChanges();
}
/// <summary>
/// Adds a user to the team
/// </summary>
/// <param name="data">The teamId and the new userId</param>
public void AddMember(Guid?teamId, Guid?userId)
{
var db = new ReScrumEntities();
var team = db.Teams.Where(t => t.TeamId == teamId).FirstOrDefault();
var user = db.Users.Where(t => t.UserId == userId).FirstOrDefault();
team.Members.Add(user);
db.SaveChanges();
}
/// <summary>
/// Adds a slot to the database
/// </summary>
/// <param name="slot">The new slot to be added</param>
public void AddSlot(Slot slot)
{
var db = new ReScrumEntities();
var newslot = new DataLayer.Models.Slot
{
SlotId = slot.SlotId,
Time = slot.Time
};
db.Slots.Add(newslot);
db.SaveChanges();
}
/// <summary>
/// Adds a team to the database
/// </summary>
/// <param name="team">The new team to be added</param>
public void AddTeam(Team team)
{
var db = new ReScrumEntities();
var newTeam = new DataLayer.Models.Team
{
TeamId = team.TeamId,
Name = team.Name,
Colour = team.Colour,
};
db.Teams.Add(newTeam);
db.SaveChanges();
}
/// <summary>
/// Gets the slot using the id
/// </summary>
/// <returns>Returns the slot</returns>
public Slot GetSlot(Guid?slotId)
{
var db = new ReScrumEntities();
var data = db.Slots.Where(s => s.SlotId == slotId).FirstOrDefault();
var slot = new Slot
{
SlotId = data.SlotId,
Time = data.Time,
StartTime = data.StartTime,
EndTime = data.EndTime,
};
return(slot);
}
/// <summary>
/// Gets the team using the id
/// </summary>
/// <returns>Returns the team</returns>
public Team GetTeam(Guid?teamId)
{
var db = new ReScrumEntities();
var data = db.Teams.Where(u => u.TeamId == teamId).FirstOrDefault();
var team = new Team
{
TeamId = data.TeamId,
Name = data.Name,
Colour = data.Colour,
Members = converter.ConvertDataUserListToWrapper(data.Members),
};
return(team);
}
/// <summary>
/// Adds attendees to an existing group booking
/// </summary>
/// <param name="bookingId">The booking the users should be added to</param>
/// <param name="userId">The attendee to be added</param>
public void AddAttendeeToGroupBooking(Guid?bookingId, Guid?userId)
{
var db = new ReScrumEntities();
var booking = db.Booking.Where(b => b.BookingId == bookingId).FirstOrDefault();
var slots = new List <DataLayer.Models.Slot>();
slots.Add(booking.Slot);
var attendee = db.Users.Where(u => u.UserId == userId).FirstOrDefault();
AddUnconfirmedBooking(db, slots, attendee, booking.Resource, booking.Date, booking.BookedBy);
db.SaveChanges();
}
/// <summary>
/// Updates an existing resource
/// </summary>
/// <param name="data">The new resource details</param>
public void UpdateResource(Resource data)
{
var db = new ReScrumEntities();
var resource = db.Resources.Where(u => u.ResourceId == data.ResourceId).FirstOrDefault();
resource.ResourceId = data.ResourceId;
resource.Name = data.Name;
resource.Description = data.Description;
resource.Category = data.Category;
resource.Capacity = data.Capacity;
resource.Location = data.Location;
resource.CancellationDate = data.CancellationDate;
db.SaveChanges();
}
/// <summary>
/// Gets all of the users in the database
/// </summary>
/// <returns>Returns a list of users</returns>
public List <User> GetUsers()
{
var db = new ReScrumEntities();
var userData = db.Users.Where(u => u.CancellationDate == null).ToList();
var users = new List <User>();
foreach (DataLayer.Models.User data in userData)
{
var user = converter.ConvertDataUserToWrapper(data);
users.Add(user);
}
return(users);
}
