//This Method Remove a Crew Member from a given Crew
public void RemoveCrewMember(int memberId, int crewId)
{
using (var context = new MarigoldSystemContext())
{
CrewMember member = context.CrewMembers.Find(memberId);
context.CrewMembers.Remove(member);
context.SaveChanges();
}
}
public List <Equipment> GetEquipments(int yardId)
{
using (var context = new MarigoldSystemContext())
{
var equipmemts = context.Equipments
.Where(x => x.YardID == yardId)
.Select(x => x).ToList();
return(equipmemts);
}
}
public string GetUserRole(int userId)
{
using (var context = new MarigoldSystemContext())
{
return(context.EmployeeRoles
.Where(x => x.EmployeeID == userId)
.Select(x => x.Role.EmployeeRoles)
.ToString());
}
}
public List <Truck> GetTrucks(int yardId)
{
using (var context = new MarigoldSystemContext())
{
var units = context.Trucks
.Where(x => x.YardID == yardId)
.Select(x => x).ToList();
return(units);
}
}
public void UpdateJobCard(int jobCardId, string completedDate)
{
using (var context = new MarigoldSystemContext())
{
JobCard jobCard = context.JobCards.Find(jobCardId);
jobCard.ClosedDate = DateTime.Parse(completedDate);
context.Entry(jobCard).Property(x => x.ClosedDate).IsModified = true;
context.Entry(jobCard).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public int?GetYardID(int userId)
{
using (var context = new MarigoldSystemContext())
{
return(context.YardEmployees
.Where(x => x.EmployeeID == userId)
.OrderBy(x => x.AssignedDate)
.Select(x => x.YardID)
.AsEnumerable()
.Last());
}
}
public string GetUnitDescription(int crewId)
{
using (var context = new MarigoldSystemContext())
{
if (context.Crews.Find(crewId).EquipmentID != null)
{
return(context.Crews.Find(crewId).Equipment.Description);
}
else
{
return(context.Crews.Find(crewId).Truck.TruckDescription);
}
}
}
public void UpdateCrew(int crewId, int KmStart, int KmEnd, string comment)
{
using (var context = new MarigoldSystemContext())
{
Crew crew = context.Crews.Find(crewId);
if (crew == null)
{
throw new Exception("This crew does not exist anymore");
}
else
{
string message = "";
if (KmStart == 0)
{
message += "KM Start cannot be equal to 0. ";
}
if (KmEnd == 0)
{
message += " KM End cannot be equal to 0. ";
}
if (KmStart > KmEnd)
{
message += " KM Start cannot be greater than KM End";
}
if (string.IsNullOrEmpty(message))
{
crew.KM_Start = KmStart;
context.Entry(crew).Property(x => x.KM_Start).IsModified = true;
crew.KM_End = KmEnd;
context.Entry(crew).Property(x => x.KM_End).IsModified = true;
if (!string.IsNullOrEmpty(comment))
{
crew.AdditionalComments = comment;
context.Entry(crew).Property(x => x.AdditionalComments).IsModified = true;
}
context.Entry(crew).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
else
{
throw new Exception(message);
}
}
}
}
public List <Driver> GetEmployees(int yardId)
{
using (var context = new MarigoldSystemContext())
{
var employees = (from yard in context.YardEmployees
where yard.YardID == yardId
select new Driver
{
EmployeeID = yard.Employee.EmployeeID,
Name = yard.Employee.FirstName + " " + yard.Employee.LastName,
Phone = yard.Employee.Phone
}).ToList();
return(employees);
}
}
/// <summary>
/// This method deletes a crew
/// It deletes all the Crew members associated to the crew
/// It deletes all the JobCards assigned to the Crew
/// It deletes the crew table
/// </summary>
/// <param name="crewId"></param>
public void DeleteCrew(int crewId)
{
using (var context = new MarigoldSystemContext())
{
Crew crew = context.Crews.Find(crewId);
if (crew == null)
{
throw new Exception("This Crew no longer exists");
}
else
{
List <CrewMember> crewMembers = crew.CrewMembers.Select(x => x).ToList();
List <JobCardCrew> jobCardCrews = crew.JobCardCrews.Select(x => x).ToList();
if (crewMembers.Count() > 0)
{
foreach (CrewMember cm in crewMembers)
{
context.CrewMembers.Remove(cm);
}
}
if (jobCardCrews.Count() > 0)
{
foreach (JobCardCrew jobCardCrew in jobCardCrews)
{
int jobCardId = jobCardCrew.JobCardID;
int count = context.JobCardCrews
.Where(x => x.JobCardID == jobCardId)
.Select(x => x)
.Count();
if (count > 1)
{
context.JobCardCrews.Remove(jobCardCrew);
}
else
{
JobCard jobCard = context.JobCards.Find(jobCardId);
context.JobCardCrews.Remove(jobCardCrew);
context.JobCards.Remove(jobCard);
}
}
}
}
context.Crews.Remove(crew);
context.SaveChanges();
}
}
public List <UnitReport> GetUnitReports(int yardId)
{
using (var context = new MarigoldSystemContext())
{
return(context.Crews
.Where(x => (x.KM_Start == null || x.KM_End == null) && (x.Truck.YardID == yardId || x.Equipment.YardID == yardId))
.Select(x => new UnitReport
{
CrewID = x.CrewID,
Date = DbFunctions.TruncateTime(x.CrewDate),
Unit = x.TruckID == null ? x.Equipment.Description : x.Truck.TruckDescription,
KM_Start = x.KM_Start,
KM_End = x.KM_End,
Comment = x.AdditionalComments
}).ToList());
}
}
public List <JobCardStatus> Get_JobCardStatus()
{
using (var context = new MarigoldSystemContext())
{
return(context.JobCards
.Where(x => x.ClosedDate == null)
.Select(job => new JobCardStatus
{
JobCardID = job.JobCardID,
AssignedDate = job.JobCardCrews.FirstOrDefault().Crew.CrewDate,
Pin = job.Site.Pin,
Community = job.Site.Community.Name,
Description = job.Site.Description,
Address = job.Site.StreetAddress,
Task = job.Task.Description,
CompletedDate = DateTime.Today
}).ToList());
}
}
public bool FoundUnit(int unitId, string category)
{
using (var context = new MarigoldSystemContext())
{
switch (category)
{
case "Equipments":
Crew crew = context.Crews
.Where(x => DbFunctions.TruncateTime(x.CrewDate) == DbFunctions.TruncateTime(DateTime.Today) && x.EquipmentID == unitId)
.Select(x => x)
.FirstOrDefault();
if (crew != null)
{
return(true);
}
else
{
return(false);
}
//break;
case "Trucks":
Crew crews = context.Crews
.Where(x => DbFunctions.TruncateTime(x.CrewDate) == DbFunctions.TruncateTime(DateTime.Today) && x.TruckID == unitId)
.Select(x => x)
.FirstOrDefault();
if (crews != null)
{
return(true);
}
else
{
return(false);
}
//break;
default:
return(false);
}
}
}
/// <summary>
/// This method deletes a jobCard
/// </summary>
/// <param name="jobCardId"></param>
public void DeleteJobCardCrew(int jobCardCrewId)
{
using (var context = new MarigoldSystemContext())
{
JobCardCrew jobCardCrew = context.JobCardCrews.Find(jobCardCrewId);
int count = context.JobCardCrews
.Where(x => x.JobCardID == jobCardCrew.JobCardID)
.Select(x => x)
.Count();
if (count > 1)
{
context.JobCardCrews.Remove(jobCardCrew);
}
else
{
int jobCardId = jobCardCrew.JobCardID;
JobCard jobCard = context.JobCards.Find(jobCardId);
context.JobCardCrews.Remove(jobCardCrew);
context.JobCards.Remove(jobCard);
}
context.SaveChanges();
}
}
//This Method adds a Crew Member to a given Crew
public void AddCrewMember(int crewId, int memberId)
{
using (var context = new MarigoldSystemContext())
{
CrewMember newCrewMember = new CrewMember();
// Check if the new crew member is already assigned in a different crew
List <int> CrewMemberIDs = (context.CrewMembers
.Where(x => DbFunctions.TruncateTime(x.Crew.CrewDate) == DbFunctions.TruncateTime(DateTime.Now))
.Select(x => x.EmployeeID))
.ToList();
int count = context.Crews.Find(crewId).CrewMembers.Count();
if (count == 5)
{
throw new Exception("A crew cannot have more than five (5) crew members");
}
if (CrewMemberIDs != null)
{
foreach (int id in CrewMemberIDs)
{
if (id == memberId)
{
string name = context.Employees
.Where(x => x.EmployeeID == memberId)
.Select(x => x.FirstName + " " + x.LastName)
.FirstOrDefault();
throw new Exception(name + " is already assigned to a different crew");
}
}
}
//End check
newCrewMember.EmployeeID = memberId;
context.Crews.Find(crewId).CrewMembers.Add(newCrewMember);
context.SaveChanges();
}
}
//This method retrieves all Current Crews
public List <CurrentCrews> GetCurrentCrews(int yardId)
{
using (var context = new MarigoldSystemContext())
{
var crews = (from crew in context.Crews
where (((crew.Truck.YardID == yardId || crew.Equipment.YardID == yardId) && DbFunctions.TruncateTime(crew.CrewDate) == DbFunctions.TruncateTime(DateTime.Today)) && (crew.JobCardCrews
.Where(y => y.JobCard.ClosedDate == null)
.Select(y => y))
.Count() > 0 || crew.JobCardCrews.Count() == 0)
orderby crew.CrewID descending
select new CurrentCrews
{
CrewID = crew.CrewID,
Description = crew.Truck.TruckID.Equals(null) ? crew.Equipment.Description : crew.Truck.TruckDescription,
Crew = (from member in context.CrewMembers
where member.CrewID == crew.CrewID
orderby member.Employee.FirstName ascending
select new Member
{
CrewMemberId = member.CrewMemberID,
Name = member.Employee.FirstName + " " + member.Employee.LastName.Substring(0, 1) + "." + " " + (member.Driver == true ? "(D)" : " "),
Phone = member.Employee.Phone
}).ToList(),
CardCrew = (from job in context.JobCardCrews
where job.CrewID == crew.CrewID && job.JobCard.ClosedDate == null
orderby job.JobCard.Site.Pin
select new Job
{
JobCardCrewID = job.JobCardCrewID,
Pin = job.JobCard.Site.Pin,
Address = job.JobCard.Site.StreetAddress
}).ToList()
}).ToList();
return(crews);
}
}
/// <summary>
/// This method add a JobCard to a crew
/// It verifies the same site is not assigned twice to the same crew
/// It notifies the user when a Jobcard is assigned to more than one crew
/// It adds a Jobcard to a crew.
/// </summary>
/// <param name="crewId"></param>
/// <param name="siteId"></param>
/// <param name="taskId"></param>
/// <returns></returns>
public string AddJobCard(int crewId, int siteId, int taskId)
{
string message = "";
using (var context = new MarigoldSystemContext())
{
DateTime today = context.Crews.Find(crewId).CrewDate;
JobCard jobcard = context.JobCardCrews
.Where(x => DbFunctions.TruncateTime(x.Crew.CrewDate) == DbFunctions.TruncateTime(DateTime.Now) && x.JobCard.SiteID == siteId)
.Select(x => x.JobCard)
.FirstOrDefault();
if (jobcard != null && jobcard.ClosedDate != null)
{
throw new Exception("The current job card was closed for the day. You can no longer assigned it today.");
}
else
{
//Check How many times the curent site is done for the current season
if (taskId == 1)
{
//Determines how many times the cuurent site was worked on in the current season.
int cycle = context.Sites.Find(siteId).SiteType.NumberOfCyle;
int count = context.JobCards
.Where(x => x.SiteID == siteId && ((DateTime)x.ClosedDate).Year == DateTime.Now.Year)
.Select(x => x)
.Count();
if (count >= cycle)
{
throw new Exception("The number of cycle (" + cycle + ") for this site has been reached for this season");
}
}
JobCardCrew jobCardCrew = context.JobCardCrews
.Where(x => x.CrewID == crewId && x.JobCard.SiteID == siteId)
.Select(x => x)
.FirstOrDefault();
if (jobCardCrew != null)
{
//Check if the same site is not already assigned to the current crew
throw new Exception("This site is already assigned to the current Crew");
}
else
{
List <JobCardCrew> jobCardCrews = context.JobCardCrews
.Where(x => x.JobCard.SiteID == siteId && DbFunctions.TruncateTime(x.Crew.CrewDate) == DbFunctions.TruncateTime(DateTime.Today))
.Select(x => x)
.ToList();
//Notifies the users that existing crew(s) are also assigned to work on the same site.
if (jobCardCrews.Count > 0)
{
foreach (JobCardCrew job in jobCardCrews)
{
string unit = job.Crew.EquipmentID == null ? job.Crew.Truck.TruckDescription : job.Crew.Equipment.Description;
message += unit + ", ";
}
jobCardCrew = new JobCardCrew();
jobCardCrew.CrewID = crewId;
jobCardCrew.JobCardID = jobCardCrews[0].JobCardID;
context.JobCardCrews.Add(jobCardCrew);
}
else
{
JobCard jobCard = new JobCard();
jobCard.SiteID = siteId;
jobCard.TaskID = taskId;
context.JobCards.Add(jobCard);
jobCardCrew = new JobCardCrew();
jobCardCrew.CrewID = crewId;
jobCard.JobCardCrews.Add(jobCardCrew);
};
context.SaveChanges();
}
}
return(message);
}
}
//This method Creates a Crew
public void CreateCrew(int unitId, int driverId, string category)
{
using (var context = new MarigoldSystemContext())
{
// Check if the driver is already assigned in a different crew
List <int> CrewMemberIDs = (context.CrewMembers
.Where(x => DbFunctions.TruncateTime(x.Crew.CrewDate) == DbFunctions.TruncateTime(DateTime.Today))
.Select(x => x.EmployeeID))
.ToList();
if (CrewMemberIDs != null)
{
foreach (int id in CrewMemberIDs)
{
if (id == driverId)
{
string name = context.Employees
.Where(x => x.EmployeeID == driverId)
.Select(x => x.FirstName + " " + x.LastName)
.FirstOrDefault();
throw new Exception(name + " is already assigned to a different crew");
}
}
}
//End check
Crew crew = new Crew();
switch (category)
{
case "Equipments":
crew = (context.Crews
.Where(x => x.EquipmentID == unitId && DbFunctions.TruncateTime(x.CrewDate) == DbFunctions.TruncateTime(DateTime.Today))
.Select(x => x))
.FirstOrDefault();
break;
case "Trucks":
crew = (context.Crews
.Where(x => x.TruckID == unitId && DbFunctions.TruncateTime(x.CrewDate) == DbFunctions.TruncateTime(DateTime.Today))
.Select(x => x))
.FirstOrDefault();
break;
}
if (crew == null)
{
//Create a new Crew
crew = new Crew();
if (category == "Equipments")
{
crew.EquipmentID = unitId;
}
else if (category == "Trucks")
{
crew.TruckID = unitId;
}
crew.CrewDate = DateTime.Today;
context.Crews.Add(crew);
//Add the dirver as the first Crew Member
CrewMember member = new CrewMember();
member.EmployeeID = driverId;
member.Driver = true;
crew.CrewMembers.Add(member);
}
else
{
throw new Exception("The selected Unit is already assigned to a different Crew");
}
context.SaveChanges();
}
}
public List <Driver> GetTruckDrivers(int yardId, int unitId, int type)
{
using (var context = new MarigoldSystemContext())
{
int?categoryId = 0;
switch (type)
{
case 1:
categoryId = (from equipment in context.Equipments
where equipment.EquipmentID == unitId
select equipment.CategoryID).First();
var Operators = (from operators in context.YardEmployees
join permit in context.OperatorPermits
on operators.EmployeeID equals permit.EmployeeID
where operators.YardID == yardId && permit.CategoryID == categoryId
select new Driver
{
EmployeeID = operators.EmployeeID,
Name = operators.Employee.FirstName + " " + operators.Employee.LastName,
Phone = operators.Employee.Phone,
Trailer = (context.TrailerOperators.Where(x => x.EmployeeID == operators.EmployeeID).Select(x => x)).FirstOrDefault().Equals(null) ? false : true
}).ToList();
return(Operators);
case 2:
List <Driver> Drivers = new List <Driver>();
categoryId = (from truck in context.Trucks
where truck.TruckID == unitId
select truck.CategoryID).FirstOrDefault();
//retrieve all licenses allowed to drive a given Unit
List <int> allLicenses = context.TruckLicenses
.Where(x => x.CategoryID == categoryId)
.Select(x => x.LicenseClass.LicenseClassID)
.ToList();
//Retrieves all drivers allowed to drive a given Truck
foreach (int id in allLicenses)
{
var result = (from yardemp in context.YardEmployees
join license in context.EmployeeLicenses
on yardemp.EmployeeID equals license.EmployeeID
where license.LicenseClassID == id && yardemp.YardID == yardId
orderby yardemp.Employee.FirstName
select new Driver
{
EmployeeID = yardemp.Employee.EmployeeID,
Name = yardemp.Employee.FirstName + " " + yardemp.Employee.LastName,
Phone = yardemp.Employee.Phone,
License = license.LicenseClass.Description,
Trailer = (context.TrailerOperators.Where(x => x.EmployeeID == yardemp.EmployeeID).Select(x => x)).FirstOrDefault().Equals(null) ? false : true
}).ToList();
Drivers.AddRange(result);
}
return(Drivers);
default:
return(null);
}
}
}
