public ActionResult RosterManagement(Guid q) { if (!Permissions.HasPermission(PermissionType.ViewOrganizationDetail, q)) return GetLoginError(); UnitMembership model = new UnitMembership { Member = new SarMember(), }; using (var ctx = GetRepository()) { var org = ctx.Organizations.IncludePaths("UnitStatusTypes").Single(f => f.Id == q); model.Organization = org; model.OrganizationId = org.Id; model.Status = org.UnitStatusTypes.First(); } return PartialView(model); }
public DataActionResult SubmitUnitMembership(UnitMembership model, int? mode) { // Values that must be set on entering: // model.OrganizationId // model.Member.Id // model.Status.Id if (model.Member == null) { throw new ArgumentNullException("Member"); } if (model.Status == null) { throw new ArgumentNullException("Status"); } List<SubmitError> errors = new List<SubmitError>(); if (!Permissions.HasPermission(PermissionType.AddOrganizationMembers, model.OrganizationId)) return GetLoginError(); SarMember m = model.Member; ModelState.Remove("Status.Organization"); ModelState.Remove("Status.Name"); ModelState.Remove("Organization"); ModelState.Remove("Member.Id"); using (var ctx = GetRepository()) { var org = ctx.Organizations.Where(f => f.Id == model.OrganizationId).FirstOrDefault(); if (org == null) { throw new NotImplementedException(); } model.Organization = org; var member = ctx.Members.IncludePaths("OldMemberships", "Memberships").FirstOrDefault(f => f.Id == model.Member.Id); if (member == null) { ctx.Members.Add(model.Member); //member = model.Member; } else { member.CopyFrom(model.Member); model.Member = member; } var status = ctx.Organizations.Where(f => f.Id == org.Id).SelectMany(f => f.UnitStatusTypes).SingleOrDefault(f => f.Id == model.Status.Id); if (status == null) { throw new NotImplementedException(); } status.Organization = org; model.Status = status; var working = ctx.Organizations.Where(f => f.Id == model.OrganizationId).SelectMany(f => f.Memberships).SingleOrDefault(f => f.Id == model.Id); if (working == null) { org.Memberships.Add(model); working = model; } else { working.CopyFrom(model); } if (working.Member.FirstName.ToLower().Contains('q')) { ModelState.AddModelError("Member.FirstName", "Test error"); } //working.Member.Memberships.Add(working); bool addMembership = true; //foreach (var oldMembership in working.Member.OldMemberships) //{ // if (oldMembership.OrganizationId == org.Id && oldMembership.Finish == null && oldMembership.Start < working.Start) // { // oldMembership.Finish = working.Start; // System.Diagnostics.Debug.Assert(oldMembership == working.Member.Memberships.Single(f => f.Id == oldMembership.Id)); // working.Member.Memberships.Remove(oldMembership); // } //} if (addMembership) working.Member.Memberships.Add(working); working.Member.OldMemberships.Add(working); ValidationErrorsToModelState(ctx.GetValidationErrors()); if (ModelState.IsValid) { ctx.SaveChanges(); } else { ModelStateToSubmitErrors(errors); } } if (!mode.HasValue) { return Data(new SubmitResult<UnitMembership> { Errors = errors.ToArray(), Result = model }); } else if (mode == 1) { return Data(new SubmitResult<RosterViewModel> { Errors = errors.ToArray(), Result = new RosterViewModel { FirstName = model.Member.FirstName, LastName = model.Member.LastName, Id = model.Member.Id, Designator = model.WorkerNumber, Status = model.Status.Name }}); } throw new InvalidOperationException("Invalid mode"); }
public ActionResult ImportRoster(Guid q, FormCollection fields) { // Provide a common way to abort this method while providing useful messages to the user Func<string, ActionResult> fail = x => { ViewData["error"] = x; return ImportRoster(q); }; // Permission check if (!Permissions.HasPermission(PermissionType.AddOrganizationMembers, q)) return GetLoginRedirect(); // Basic validation of file if (Request.Files.Count != 1) { throw new InvalidOperationException("Can only submit one roster"); } Organization org; UnitStatusType defaultStatus; using (var ctx = this.GetRepository()) { org = ctx.Organizations.IncludePaths("UnitStatusTypes").Single(f => f.Id == q); defaultStatus = org.UnitStatusTypes.First(f => f.IsActive && f.IsMissionQualified); } var postedFile = Request.Files[0]; byte[] fileData = new byte[postedFile.ContentLength]; // Read file as Excel spreadsheet ExcelFileType fileType; if (!Enum.TryParse<ExcelFileType>(System.IO.Path.GetExtension(postedFile.FileName).TrimStart('.'), true, out fileType)) { return fail("Filename does not end in one of: ." + string.Join(", .", Enum.GetNames(typeof(ExcelFileType)))); } ExcelFile file; try { file = ExcelService.Read(postedFile.InputStream, fileType); } catch { return fail(string.Format("Error reading {0} file \"{1}\"", fileType, postedFile.FileName)); } ExcelSheet sheet = file.GetSheet(0); // Figure out the order of the columns, and which ones were included... Dictionary<RosterImportColumn, int> columnLookup = new Dictionary<RosterImportColumn, int>(); // Figure out which columns the user included in the spreadsheet int col = -1; do { col++; string header = sheet.CellAt(0, col).StringValue; if (string.IsNullOrWhiteSpace(header)) { break; } RosterImportColumn column; if (Enum.TryParse<RosterImportColumn>(header, true, out column)) { if (columnLookup.ContainsKey(column)) { return fail(string.Format("Found multiple '{0}' columns", column)); } columnLookup.Add(column, col); } } while (col < 100); // Make sure the required columns are included if (!columnLookup.ContainsKey(RosterImportColumn.LastName)) { return fail(string.Format("File does not contain required column '{0}'", RosterImportColumn.LastName)); } int row = 0; string externalKeySource = string.Format("roster{0}", q); List<string> errors = new List<string>(); // ### Start Processing Rows do { row++; // Get the lastname. Lastname is required - a column without indicates the end of the data string lastname = sheet.CellAt(row, columnLookup[RosterImportColumn.LastName]).StringValue; if (string.IsNullOrWhiteSpace(lastname)) { break; } using (var ctx = GetRepository()) { ctx.Organizations.Attach(org); // If the spreadsheet supplies an external key, look the member up in the database. string key = columnLookup.ContainsKey(RosterImportColumn.MemberId) ? sheet.CellAt(row, columnLookup[RosterImportColumn.MemberId]).StringValue : null; SarMember member = null; if (!string.IsNullOrWhiteSpace(key)) { var query = from x in (from f in ctx.ExternalReferences where f.Source == externalKeySource && f.ExternalKey == key select f) join m in ctx.Members.IncludePaths("Addresses", "ContactInfo") on x.InternalKey equals m.Id into mbrs select mbrs; member = query.SelectMany(f => f).FirstOrDefault(); } // If the member was not found by external key, add it to this database. if (member == null) { member = new SarMember(); ctx.Members.Add(member); if (!string.IsNullOrWhiteSpace(key)) { ctx.ExternalReferences.Add(new ExternalReference { Source = externalKeySource, ExternalKey = key, InternalKey = member.Id }); } } // Some data is updated all the time. // First and Last names are. member.LastName = lastname; member.FirstName = columnLookup.ContainsKey(RosterImportColumn.FirstName) ? sheet.CellAt(row, columnLookup[RosterImportColumn.FirstName]).StringValue : null; // Birthdate is updated if the column exists. // I think this is formatted differently than the FirstName because it was using more // than one statement. Should be able to change it back. if (columnLookup.ContainsKey(RosterImportColumn.BirthDate)) { member.BirthDate = sheet.CellAt(row, columnLookup[RosterImportColumn.BirthDate]).DateValue; } // Gender if the column exists if (columnLookup.ContainsKey(RosterImportColumn.Gender)) { Gender gender; string attempt = sheet.CellAt(row, columnLookup[RosterImportColumn.Gender]).StringValue; if (!Enum.TryParse<Gender>(attempt, true, out gender)) { errors.Add(string.Format("Row {0}: Can't understand gender '{1}'", row + 1, attempt)); continue; } member.Gender = gender; } DateTimeOffset joined = DataStoreService.DATE_UNKNOWN; if (columnLookup.ContainsKey(RosterImportColumn.DateJoined)) { string dateString = sheet.CellAt(row, columnLookup[RosterImportColumn.DateJoined]).StringValue; DateTime rowDateTime; if (DateTime.TryParse(dateString, out rowDateTime)) { joined = TimeZoneInfo.ConvertTimeToUtc(rowDateTime, TimeZoneInfo.FindSystemTimeZoneById(org.TimeZone)); } } UnitStatusType status = defaultStatus; if (columnLookup.ContainsKey(RosterImportColumn.Status)) { status = org.UnitStatusTypes.Single( f => f.Name.Equals(sheet.CellAt(row, columnLookup[RosterImportColumn.Status]).StringValue, StringComparison.OrdinalIgnoreCase)); } string cardNumber = null; if (columnLookup.ContainsKey(RosterImportColumn.Designator)) { cardNumber = sheet.CellAt(row, columnLookup[RosterImportColumn.Designator]).StringValue; } UnitMembership um = new UnitMembership { Member = member, OrganizationId = org.Id, Start = joined.UtcDateTime, WorkerNumber = cardNumber, Status = status, Organization = org }; member.Memberships.Add(um); member.OldMemberships.Add(um); if (columnLookup.ContainsKey(RosterImportColumn.Street) && columnLookup.ContainsKey(RosterImportColumn.City) && columnLookup.ContainsKey(RosterImportColumn.State) && columnLookup.ContainsKey(RosterImportColumn.ZIP)) { Address address = new Address { Street = sheet.CellAt(row, columnLookup[RosterImportColumn.Street]).StringValue, City = sheet.CellAt(row, columnLookup[RosterImportColumn.City]).StringValue, State = sheet.CellAt(row, columnLookup[RosterImportColumn.State]).StringValue, Zip = sheet.CellAt(row, columnLookup[RosterImportColumn.ZIP]).StringValue }; if (!(string.IsNullOrWhiteSpace(address.Street) || string.IsNullOrWhiteSpace(address.City) || string.IsNullOrWhiteSpace(address.State) || string.IsNullOrWhiteSpace(address.Zip)) && !member.Addresses.Any(f => f.Address.Street == address.Street && f.Address.City == address.City && f.Address.State == address.State && f.Address.Zip == address.Zip)) { member.Addresses.Add(new MemberAddress { Member = member, Type = MemberAddressType.Mailing, Address = address }); } } foreach (var column in new[] { RosterImportColumn.HomePhone, RosterImportColumn.WorkPhone, RosterImportColumn.CellPhone, RosterImportColumn.Pager }) { if (columnLookup.ContainsKey(column)) { string phone = sheet.CellAt(row, columnLookup[column]).StringValue; if (!string.IsNullOrWhiteSpace(phone) && !member.ContactInfo.Any(f => f.Value == phone)) { member.ContactInfo.Add(new MemberContact { Member = member, Type = ContactType.Phone, SubType = column.ToString().ToLowerInvariant().Replace("phone", ""), Value = phone }); } } } if (columnLookup.ContainsKey(RosterImportColumn.Email)) { string email = sheet.CellAt(row, columnLookup[RosterImportColumn.Email]).StringValue; if (!string.IsNullOrWhiteSpace(email)) { member.ContactInfo.Add(new MemberContact { Member = member, Type = ContactType.Email, Value = email }); } } ctx.SaveChanges(); } } while (row < 1002); // Only allow 1000 rows per file if (errors.Count > 0) { throw new NotImplementedException("Should send email with errors: " + string.Join("::", errors.ToArray())); } file.Dispose(); return RedirectToAction("ImportRosterResults", new { q = q, success = row - errors.Count, errors = errors.Count }); }