public int unitNumber(int crewId) { using (var context = new COESystemContext()) { return(context.Crews.Find(crewId).Unit.UnitID); } }
public int GetYardId(int?employeeId) { using (var context = new COESystemContext()) { return(context.Employees.Find(employeeId).YardID); } }
//Watering Routes public List <RouteStatus> WateringList(int yardId) { using (var context = new COESystemContext()) { var WateringList = from site in context.Sites where site.YardID == 1 && site.Watering == true && site.Season.SeasonYear == DateTime.Now.Year orderby site.Pin select new RouteStatus { SiteID = site.SiteID, Pin = site.Pin, Community = site.Community.Name, Neighbourhood = site.Neighbourhood, Address = site.StreetAddress, Area = site.Area, Notes = site.Notes, Cycle1 = ((from water in context.Waterings where water.CrewSite.SiteID == site.SiteID select new { Date = water.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from water in context.Waterings where water.CrewSite.SiteID == site.SiteID select new { Date = water.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Date, Cycle2 = ((from water in context.Waterings where water.CrewSite.SiteID == site.SiteID select new { Date = water.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).Skip(1).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from water in context.Waterings where water.CrewSite.SiteID == site.SiteID select new { Date = water.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).Skip(1).FirstOrDefault()).Date, Cycle3 = ((from water in context.Waterings where water.CrewSite.SiteID == site.SiteID select new { Date = water.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).Skip(2).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from water in context.Waterings where water.CrewSite.SiteID == site.SiteID select new { Date = water.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).Skip(2).FirstOrDefault()).Date }; return(WateringList.ToList()); } }
//Returns the Yard Name based on the EmployeeID public string GetYardName(int?userId) { using (var context = new COESystemContext()) { return(context.Employees.Find(userId).Yard.YardName); } }
//Planting Routes public List <RouteStatus> PlantingList(int yardId) { using (var context = new COESystemContext()) { var PlantingList = from site in context.Sites where site.YardID == yardId && site.Planting == true && site.Season.SeasonYear == DateTime.Now.Year orderby site.Pin select new RouteStatus { SiteID = site.SiteID, Pin = site.Pin, Community = site.Community.Name, Neighbourhood = site.Neighbourhood, Address = site.StreetAddress, Area = site.Area, Notes = site.Notes, Planting = ((from plant in context.Plantings where plant.CrewSite.SiteID == site.SiteID select new Cycle { Date = plant.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from plant in context.Plantings where plant.CrewSite.SiteID == site.SiteID select new Cycle { Date = plant.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Date, Uprooting = ((from uproot in context.Uprootings where uproot.CrewSite.SiteID == site.SiteID select new { Date = uproot.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from uproot in context.Uprootings where uproot.CrewSite.SiteID == site.SiteID select new Cycle { Date = uproot.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Date }; return(PlantingList.ToList()); } }
public Unit GetUnit(int unitId) { using (var context = new COESystemContext()) { return(context.Units.Find(unitId)); } }
//Grass List public List <RouteStatus> GrassRouteList(int yardId) { using (var context = new COESystemContext()) { var RouteList = from site in context.Sites where site.Season.SeasonYear == DateTime.Now.Year && site.YardID == yardId && site.Grass > 0 orderby site.Community.Name ascending select new RouteStatus { SiteID = site.SiteID, Pin = site.Pin, Community = site.Community.Name, Neighbourhood = site.Neighbourhood, Address = site.StreetAddress, Area = site.Area, Notes = site.Notes, Count = site.Grass, Trimming = ((from grass in context.Grasses where grass.CrewSite.SiteID == site.SiteID select new Cycle { Date = grass.CrewSite.Crew.CrewDate }).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from grass in context.Grasses where grass.CrewSite.SiteID == site.SiteID select new Cycle { Date = grass.CrewSite.Crew.CrewDate }).FirstOrDefault()).Date }; return(RouteList.ToList()); } }
//This Method returns the List of the current crews public List <CurrentCrew> GetCurrentCrew(int yardId) { using (var context = new COESystemContext()) { var CurrentCrews = from x in context.Crews where x.Unit.YardID == yardId && DbFunctions.TruncateTime(x.CrewDate) == DbFunctions.TruncateTime(DateTime.Now) orderby x.CrewID descending select new CurrentCrew { CrewID = x.CrewID, Unit = x.Unit.UnitNumber, UnitID = x.UnitID, Crew = (from cr in context.CrewMembers where cr.CrewID == x.CrewID orderby cr.Employee.FirstName select new Member { CrewMemberID = cr.CrewMemberID, Name = cr.Employee.FirstName + " " + cr.Employee.LastName, Driver = cr.Driver }).ToList(), Sites = (from y in context.CrewSites where y.CrewID == x.CrewID orderby y.SiteID ascending select new WorkSite { SiteID = y.SiteID.Equals(null) ? 0 : y.SiteID, Pin = y.Site.Pin.Equals(null) ? 0 : y.Site.Pin }).ToList() }; return(CurrentCrews.ToList()); } }
public int GetCrewID(int unitId) { using (var context = new COESystemContext()) { return((from x in context.Crews where x.UnitID == unitId && DbFunctions.TruncateTime(x.CrewDate) == DbFunctions.TruncateTime(DateTime.Now) select x.CrewID).FirstOrDefault()); } }
public Crew GetCrew(int unitID, DateTime date) { using (var context = new COESystemContext()) { Crew crew = (from x in context.Crews where x.UnitID == unitID && DbFunctions.TruncateTime(date) == DbFunctions.TruncateTime(x.CrewDate) select x).FirstOrDefault(); return(crew); } }
public List <Employee> GetEmployees(int yardId) { using (var context = new COESystemContext()) { var employeeList = from x in context.Employees where x.YardID == yardId && x.TeamLeader == false && x.CrewLeader == false select x; return(employeeList.ToList()); } }
//Assigns Sites to a Crew public string Add_Site_To_Crew(int crewId, int siteId) { using (var context = new COESystemContext()) { string units = ""; CrewSite crewSite = (from x in context.CrewSites where x.CrewID == crewId && x.SiteID == siteId select x).FirstOrDefault(); List <string> reasons = new List <string>(); if (crewSite != null) { reasons.Add("This site is already assigned to the current crew"); } else { //Notify user that the site is already assigned to at least another crew List <CrewSite> crewSites = new List <CrewSite>(); crewSites = (from x in context.CrewSites where DbFunctions.TruncateTime(x.Crew.CrewDate) == DbFunctions.TruncateTime(DateTime.Now) && siteId == x.SiteID select x).ToList(); if (crewSites != null) { foreach (CrewSite cs in crewSites) { units += cs.Crew.Unit.UnitNumber + ", "; } } //Create a new CrewSite for the current Crew crewSite = new CrewSite(); crewSite.SiteID = siteId; crewSite.CrewID = crewId; context.CrewSites.Add(crewSite); } if (reasons.Count() > 0) { throw new BusinessRuleException("Adding Site", reasons); } context.SaveChanges(); return(units); } }
public List <YardUnits> GetUnits(int yardId) { using (var context = new COESystemContext()) { var CurrentUnit = from x in context.Units where x.YardID == yardId select new YardUnits { UnitID = x.UnitID, Number = x.UnitNumber, Description = x.UnitDescription, }; return(CurrentUnit.ToList()); } }
//This method deletes a Crew and all its crew members public void DeleteCrew(int crewId) { using (var context = new COESystemContext()) { Crew crew = context.Crews.Find(crewId); List <string> message = new List <string>(); if (crew == null) { message.Add("This Crew is no longer in the database"); } else { List <CrewMember> crewMembers = crew.CrewMembers.Select(x => x).ToList(); List <CrewSite> crewSites = crew.CrewSites.Select(x => x).ToList(); if (crewMembers != null) { foreach (CrewMember cm in crewMembers) { context.CrewMembers.Remove(cm); } } if (crewSites != null) { foreach (CrewSite cs in crewSites) { context.CrewSites.Remove(cs); } } } if (message.Count > 0) { throw new BusinessRuleException("Removing crew Failed!", message); } context.Crews.Remove(crew); context.SaveChanges(); } }
//This method removes a Crew member from his crew. public void RemoveCrewMember(int crewMemberID) { using (var context = new COESystemContext()) { CrewMember member = context.CrewMembers.Find(crewMemberID); List <string> message = new List <string>(); if (member == null) { message.Add("This employee you tried to remove did not belong to the current crew"); } if (message.Count() > 0) { throw new BusinessRuleException("Removing a crew Memeber Failed", message); } else { context.CrewMembers.Remove(member); context.SaveChanges(); } } }
public List <RouteStatus> RouteList(int yardId, int siteTypeId) { using (var context = new COESystemContext()) { //var RouteList = from site in context.Sites // orderby site.Community.Name ascending // where site.Yard.YardID == yardId && site.SiteTypeID == siteTypeId // let Cycles = (from sbm in context.SBMs // where sbm.CrewSite.SiteID == site.SiteID && sbm.CrewSite.Crew.CrewDate.Year == DateTime.Now.Year // orderby sbm.CrewSite.Crew.CrewDate ascending // select new Cycle // { // Date = sbm.CrewSite.Crew.CrewDate // }).ToList() // select new RouteStatus // { // Pin = site.Pin, // Community = site.Community.Name, // Neighbourhood = site.Neighbourhood, // Address = site.StreetAddress, // Area = site.Area, // Notes = site.Notes, // Cycle1 = Cycles.OrderBy(x => x.Date).FirstOrDefault().Date.Equals(null) ? (DateTime?)null : Cycles.OrderBy(x => x.Date).FirstOrDefault().Date, // Cycle2 = Cycles.OrderBy(x => x.Date).Skip(1).FirstOrDefault().Equals(null) ? (DateTime?)null : Cycles.OrderBy(x => x.Date).Skip(1).FirstOrDefault().Date, // Cycle3 = Cycles.OrderBy(x => x.Date).Skip(2).FirstOrDefault().Equals(null) ? (DateTime?)null : Cycles.OrderBy(x => x.Date).Skip(2).FirstOrDefault().Date, // Cycle4 = Cycles.OrderBy(x => x.Date).Skip(3).FirstOrDefault().Equals(null) ? (DateTime?)null : Cycles.OrderBy(x => x.Date).Skip(3).FirstOrDefault().Date, // Cycle5 = Cycles.OrderBy(x => x.Date).Skip(4).FirstOrDefault().Equals(null) ? (DateTime?)null : Cycles.OrderBy(x => x.Date).Skip(4).FirstOrDefault().Date, // Pruning = ((from prune in context.Prunings where prune.CrewSite.SiteID == site.SiteID select new { Date = prune.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Equals(null) // ? (DateTime?)null : ((from prune in context.Prunings where prune.CrewSite.SiteID == site.SiteID select new { Date = prune.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Date, // Mulching = ((from mulch in context.Mulchings where mulch.CrewSite.SiteID == site.SiteID select new { Date = mulch.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Equals(null) // ? (DateTime?)null : ((from mulch in context.Mulchings where mulch.CrewSite.SiteID == site.SiteID select new { Date = mulch.CrewSite.Crew.CrewDate }).OrderBy(x => x.Date).FirstOrDefault()).Date // }; //return RouteList.ToList(); var RouteList = (from site in context.Sites orderby site.Community.Name ascending where site.Season.SeasonYear == DateTime.Now.Year && site.Yard.YardID == yardId && site.SiteTypeID == siteTypeId orderby site.Community.Name ascending select new RouteStatus { SiteID = site.SiteID, Pin = site.Pin, Community = site.Community.Name, Neighbourhood = site.Neighbourhood, Address = site.StreetAddress, Area = site.Area, Notes = site.Notes, Cycle1 = ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).FirstOrDefault()).Date, Cycle2 = ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(1).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(1).FirstOrDefault()).Date, Cycle3 = ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(2).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(2).FirstOrDefault()).Date, Cycle4 = ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(3).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(3).FirstOrDefault()).Date, Cycle5 = ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(4).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from sbm in context.SBMs where sbm.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(sbm.CrewSite.Crew.CrewDate) }).OrderBy(x => x.Date).Skip(4).FirstOrDefault()).Date, Pruning = ((from prune in context.Prunings where prune.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(prune.CrewSite.Crew.CrewDate) }).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from prune in context.Prunings where prune.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(prune.CrewSite.Crew.CrewDate) }).FirstOrDefault()).Date, Mulching = ((from mulch in context.Mulchings where mulch.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(mulch.CrewSite.Crew.CrewDate) }).FirstOrDefault()).Equals(null) ? (DateTime?)null : ((from mulch in context.Mulchings where mulch.CrewSite.SiteID == site.SiteID select new Cycle { Date = DbFunctions.TruncateTime(mulch.CrewSite.Crew.CrewDate) }).FirstOrDefault()).Date }); return(RouteList.ToList()); } }
//This method create a new Crew and update current Crews public int Add_To_A_Crew(int unitId, int employeeId) { int crewId = 0; using (var context = new COESystemContext()) { Crew crew = (from x in context.Crews where x.UnitID == unitId && DbFunctions.TruncateTime(DateTime.Now) == DbFunctions.TruncateTime(x.CrewDate) select x).FirstOrDefault(); List <string> reasons = new List <string>(); //Check if the added employee is already assigned to a different Crew. List <CrewMember> CurrentCrews = (from x in context.CrewMembers where DbFunctions.TruncateTime(x.Crew.CrewDate) == DbFunctions.TruncateTime(DateTime.Now) select x).ToList(); foreach (CrewMember memb in CurrentCrews) { if (memb.EmployeeID == employeeId) { throw new Exception(context.Employees.Find(employeeId).Name + " is already in assigned to a crew (" + memb.Crew.Unit.UnitNumber + ")"); } } if (crew == null) { //Create the new Crew crew = new Crew(); crew.UnitID = unitId; crew.CrewDate = DateTime.Now; context.Crews.Add(crew); } else { int count = (from x in context.CrewMembers where x.CrewID == crew.CrewID select x).Count(); CrewMember member = null; member = crew.CrewMembers.SingleOrDefault(x => x.EmployeeID == employeeId); if (member != null) { //An employee cannot be assigned only once in a Crew reasons.Add(context.Employees.Find(employeeId).Name + " is already assigned to this Crew"); } else if (count == 5) { //A Crew cannot have more than 5 employees reasons.Add("A crew cannot have more than five (5) members"); } } if (reasons.Count() > 0) { throw new BusinessRuleException("Adding Crew Member ", reasons); } else { CrewMember member = new CrewMember(); member.EmployeeID = employeeId; //Use the navigational property to add the crew memeber because the pkey (crewID) //is not know when the we create a brand new crew. crew.CrewMembers.Add(member); context.SaveChanges(); crewId = (from x in context.Crews where x.UnitID == crew.UnitID && DbFunctions.TruncateTime(DateTime.Now) == DbFunctions.TruncateTime(x.CrewDate) select x.CrewID).First(); } return(crewId); } }
public void RemoveCrewSite(int crewSiteId) { using (var context = new COESystemContext()) { List <string> message = new List <string>(); CrewSite cs = context.CrewSites.Find(crewSiteId); List <Grass> grassList = cs.Grasses.Select(x => x).ToList(); List <Watering> wateringList = cs.Waterings.Select(x => x).ToList(); List <Planting> plantinList = cs.Plantings.Select(x => x).ToList(); List <SBM> sBMList = cs.SBMs.Select(x => x).ToList(); List <Mulching> mulchingList = cs.Mulchings.Select(x => x).ToList(); List <Pruning> pruningList = cs.Prunings.Select(x => x).ToList(); List <Uprooting> uprootingList = cs.Uprootings.Select(x => x).ToList(); if (cs == null) { message.Add("This Site was not assigned to the current Crew"); } if (message.Count() > 0) { throw new BusinessRuleException("Removing Site from current Crew Failed", message); } else { foreach (Grass item in grassList) { context.Grasses.Remove(item); } foreach (Watering item in wateringList) { context.Waterings.Remove(item); } foreach (Planting item in plantinList) { context.Plantings.Remove(item); } foreach (SBM item in sBMList) { context.SBMs.Remove(item); } foreach (Mulching item in mulchingList) { context.Mulchings.Remove(item); } foreach (Pruning item in pruningList) { context.Prunings.Remove(item); } foreach (Uprooting item in uprootingList) { context.Uprootings.Remove(item); } context.CrewSites.Remove(cs); context.SaveChanges(); } } }