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();
}
}
}
