private void ReconcileEmergencyWorkersAddMember(ReconcileEmergencyWorkersViewModel model, string unit, ReconcileEmergencyWorkersRow row) { if (!model.MembersByUnit.ContainsKey(unit)) { model.MembersByUnit.Add(unit, new List<ReconcileEmergencyWorkersRow>()); } model.MembersByUnit[unit].Add(row); }
public ActionResult ReconcileEmergencyWorkers(bool? saveFile) { if (Request.Files.Count > 1) { throw new InvalidOperationException("Can only submit one roster"); } byte[] fileData; var postedFile = Request.Files[0]; string fileName = postedFile.FileName; string cachePath = this.ReconcileEmergencyWorkersCache; if (!string.IsNullOrWhiteSpace(fileName)) { if (!fileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase)) { throw new InvalidOperationException("Must use XLSX file"); } fileData = new byte[postedFile.ContentLength]; postedFile.InputStream.Read(fileData, 0, fileData.Length); if (saveFile ?? false) { string[] pieces = postedFile.FileName.Split('.'); string extension = pieces.Last(); IO.File.WriteAllBytes(cachePath, fileData); } } else if (IO.File.Exists(cachePath)) { fileData = IO.File.ReadAllBytes(cachePath); fileName = IO.Path.GetFileName(cachePath); } else { throw new InvalidOperationException("No new file was uploaded, and no older file is cached"); } var stream = new System.IO.MemoryStream(fileData); stream.Position = 0; ExcelFile xl = ExcelService.Read(stream, ExcelFileType.XLSX); ExcelSheet sheet = xl.GetSheet(0); string[] columns = new[] { "EMT?", "DEM #", "LastName", "FirstName", "Common Name", "ns1:OrgRank", "ns1:Status" }; for (int i = 0; i < columns.Length; i++) { string column = sheet.CellAt(0, i).StringValue; if (column != columns[i]) { return new ContentResult { Content = "Unexpected column: " + column }; } } ReconcileEmergencyWorkersViewModel model = new ReconcileEmergencyWorkersViewModel(); List<Guid> seen = new List<Guid>(); int row = 1; DateTime now = DateTime.Now; DateTime eighteenMonths = now.AddMonths(-18); var members = this.db.Members.Include("Memberships.Status").Include("Memberships.Unit").Include("ComputedAwards.Course"); var courses = (from c in this.db.TrainingCourses where c.WacRequired > 0 select c).OrderBy(x => x.DisplayName).ToList(); while (!string.IsNullOrWhiteSpace(sheet.CellAt(row, 0).StringValue)) { string dem = sheet.CellAt(row, 1).StringValue.Trim().PadLeft(4, '0'); string last = sheet.CellAt(row, 2).StringValue.Trim().Split(',')[0]; string common = sheet.CellAt(row, 4).StringValue.Trim(); string first = string.IsNullOrWhiteSpace(common) ? sheet.CellAt(row, 3).StringValue.Trim() : common; string wacRank = sheet.CellAt(row, 5).StringValue.Trim(); string active = sheet.CellAt(row, 6).StringValue.Trim(); if (wacRank == "Canine") { } else { var nameMatches = members.Where(f => (f.LastName == last || f.LastName.StartsWith(last + ",")) && f.FirstName == first); // Not super efficient, but works ok. if (nameMatches.Count() > 1) { nameMatches = members.Where(f => f.LastName == last && f.FirstName == first && f.DEM == dem); if (nameMatches.Count() == 0) { nameMatches = members.Where(f => f.LastName == last && f.FirstName == first); } } string result = dem + " " + last + ", " + first; if (nameMatches.Count() == 0) { model.NoMatch.Add(result + " - Not found"); } else if (nameMatches.Count() > 1) { model.NoMatch.Add(string.Format("{0} - {1} possible matches", result, nameMatches.Count())); } else { var member = nameMatches.First(); seen.Add(member.Id); var trainingStatus = CompositeTrainingStatus.Compute(member, courses, now); var units = member.Memberships.Where(f => f.Status.IsActive && f.Activated < now && (f.EndTime == null || f.EndTime.Value > now)).Select(f => f.Unit.DisplayName).Distinct().ToList(); if (units.Count == 0) { model.NoMatch.Add(result + " - found in KCSARA database, but has no active unit"); } units.ForEach(f => ReconcileEmergencyWorkersAddMember(model, f, new ReconcileEmergencyWorkersRow { Id = member.Id, Name = last + ", " + first, DEM = dem, DemIsNew = (dem != member.DEM), WacIsNew = (wacRank != member.WacLevel.ToString()), WacLevel = wacRank + ((wacRank != member.WacLevel.ToString()) ? ("/" + member.WacLevel.ToString()) : ""), UnitStatus = member.Memberships.Where(g => g.Unit.DisplayName == f).OrderByDescending(g => g.Activated).First().Status.StatusName, IsOldNovice = (wacRank == "Novice" && member.WacLevel == WacLevel.Novice && member.WacLevelDate.AddYears(1) < now), TrainingCurrent = trainingStatus.IsGood, MissionCount = this.db.MissionRosters.Where(g => g.Person.Id == member.Id && g.Mission.StartTime > eighteenMonths).Select(g => g.Mission.Id).Distinct().Count(), TrainingCount = this.db.TrainingRosters.Where(g => g.Person.Id == member.Id && g.Training.StartTime > eighteenMonths).Select(g => g.Training.Id).Distinct().Count() })); } } model.RowCount++; row++; } foreach (var member in members.Where(f => f.Memberships.Any(g => g.Status.IsActive && g.Activated < now && (g.EndTime == null || g.EndTime.Value > now)) && !seen.Contains(f.Id)).OrderBy(f => f.LastName).ThenBy(f => f.FirstName)) { var units = member.Memberships.Where(f => f.Status.WacLevel != WacLevel.None && f.Activated < now && (f.EndTime == null || f.EndTime.Value > now)).Select(f => f.Unit.DisplayName).Distinct().ToList(); units.ForEach(f => ReconcileEmergencyWorkersAddMember(model, f, new ReconcileEmergencyWorkersRow { Name = member.ReverseName, DEM = "", DemIsNew = !string.IsNullOrWhiteSpace(member.DEM), WacIsNew = (member.WacLevel != WacLevel.None), WacLevel = WacLevel.None.ToString(), UnitStatus = member.Memberships.Where(g => g.Unit.DisplayName == f).OrderByDescending(g => g.Activated).First().Status.StatusName, TrainingCurrent = null, MissionCount = this.db.MissionRosters.Where(g => g.Person.Id == member.Id && g.Mission.StartTime > eighteenMonths).Select(g => g.Mission.Id).Distinct().Count(), TrainingCount = this.db.TrainingRosters.Where(g => g.Person.Id == member.Id && g.Training.StartTime > eighteenMonths).Select(g => g.Training.Id).Distinct().Count() })); } return View("ReconcileEmergencyWorkerResults", model); }