public static string GenerateRoutes(DateTime startWeekDate) { using (var db = new pileEntities()) { var endWeekDate = startWeekDate.AddDays(7); var existingRouteCount = db.Routes.Count(x => x.Date >= startWeekDate && x.Date < endWeekDate); if (existingRouteCount > 0) { return($"{existingRouteCount} routes already exist for {startWeekDate.ToShortDateString()}"); } var activeCustomers = db.Customers.Where(x => x.Status == "A"); var serviceDetails = db.ServiceDetails.Where(x => x.CountDown > 0).Join(activeCustomers, s => s.CustomerId, c => c.Id, (s, c) => s); var pauses = db.Pauses.Where(x => x.PauseDate <= endWeekDate && x.RestartDate > startWeekDate); var routeList = new List <Route>(); foreach (var serviceDetail in serviceDetails) { var customer = activeCustomers.Single(x => x.Id == serviceDetail.CustomerId); var custRoute = GenerateCustomerRoute(customer, serviceDetail, startWeekDate); //don't add 'em if paused. if (IsRouteValid(custRoute, customer, pauses, serviceDetail)) { routeList.Add(custRoute); } } db.Routes.AddRange(routeList); var toCountDownList = serviceDetails.Where(x => x.CountDown < 999); foreach (var toCountDown in toCountDownList) { if (routeList.Any(x => x.CustomerID == toCountDown.CustomerId && x.ServiceId == toCountDown.ServiceId)) { toCountDown.CountDown -= 1; } } db.SaveChanges(); } return(""); }
public void SaveChildObjects(pileEntities db) { foreach (var address in Addresses.Where(x => x.Id != 0)) { db.Entry(address).State = EntityState.Modified; } foreach (var email in EmailAddresses.Where(x => x.Id != 0)) { db.Entry(email).State = EntityState.Modified; } foreach (var note in Notes.Where(x => x.Id != 0)) { db.Entry(note).State = EntityState.Modified; } foreach (var phone in Phones.Where(x => x.Id != 0)) { db.Entry(phone).State = EntityState.Modified; } foreach (var address in Addresses.Where(x => x.Id == 0)) { db.Addresses.Add(address); } foreach (var email in EmailAddresses.Where(x => x.Id == 0)) { db.EmailAddresses.Add(email); } foreach (var note in Notes.Where(x => x.Id == 0)) { db.Notes.Add(note); } foreach (var phone in Phones.Where(x => x.Id == 0)) { db.Phones.Add(phone); } db.SaveChanges(); }
public static void UpdateEstNums(int customerId) { using (var db = new pileEntities()) { var crewDayList = db.ServiceDays.Where(x => x.CustomerId == customerId).Select(x => new { Crew = x.Crew, Day = x.Day }).ToList(); foreach (var crewDay in crewDayList.Distinct()) { var crew = crewDay.Crew; var day = crewDay.Day; var orderedCustId = (from sd in db.ServiceDays join c in db.Customers on sd.CustomerId equals c.Id where c.Status == "A" && sd.Day == day && sd.Crew == crew select new { EstNum = sd.EstNum, CustomerId = sd.CustomerId }).Distinct().OrderBy(x => x.EstNum).Select(x => x.CustomerId).ToList(); var serviceDetailsForCrewDay = db.ServiceDays.Where(x => orderedCustId.Contains(x.CustomerId) && x.Day == day && x.Crew == crew); for (int i = 0; i < orderedCustId.Count(); i++) { var custId = orderedCustId.ElementAt(i); var serviceDaysToUpdate = serviceDetailsForCrewDay.Where(x => x.CustomerId == custId).ToList(); foreach (var detailToUpdate in serviceDaysToUpdate) { detailToUpdate.EstNum = i + 1; } } } db.SaveChanges(); } }
private static void InsertRoute(Customer cust, DateTime weekStart) { using (var db = new pileEntities()) { if (cust.Status != "A") { throw new Exception(string.Format("Adding a route to an inactive customer is not allowed. CustId {0}", cust.Id)); } var thisWeekend = Route.ThisWeekEnd; //Make sure there aren't already routes for customer & timeframe if (!db.Routes.Any(x => x.CustomerID == cust.Id && x.Date >= weekStart && x.Date <= thisWeekend)) { //Make sure at least one other customer has routes defined in timeframe (original logic). if (!db.Routes.Any(x => x.Date >= weekStart && x.Date <= thisWeekend)) { return; } var svcDetails = db.ServiceDetails.Where(x => x.CustomerId == cust.Id).ToList(); if (svcDetails == null || svcDetails.Count(x => x.CountDown > 0) == 0) { return; } var routesToAdd = new List <Route>(); foreach (var serviceDetail in svcDetails) { var service = db.Services.Single(x => x.Id == serviceDetail.ServiceId); if (RouteQualifies(weekStart, service, serviceDetail, cust)) { var employee = serviceDetail.ServiceDay.Crew.Employees.First(); //db.Employees.Single(x => x.Crew == serviceDetail.ServiceDay.Crew && x.Status != "I"); var total = serviceDetail.GetStopAmout(); //these discounts are already figured into the total above. The discount is stored on the route //for reference purposes, I suppose. I didn't write it. . . I just rewrote it. var discounts = serviceDetail.Discount; var employeeAmount = service.GetEmployeeAmount(employee, total, serviceDetail.Qty); routesToAdd.Add(new Route { Date = weekStart.AddDays(serviceDetail.ServiceDay.Day), CustomerID = cust.Id, Discount = discounts, EmpAmount = employeeAmount.Amount, EmployeeId = employee.Id, EmpPerc = employeeAmount.Percent, EstNum = serviceDetail.ServiceDay.EstNum, ServiceDetailId = serviceDetail.Id, ServiceId = service.Id, Status = "A", WeeklyRate = total }); if (service.CountDown != 999) { service.CountDown -= 1; } } } db.Routes.AddRange(routesToAdd); db.SaveChanges(); } } }