public void CopyFrom(Address other)
{
this.Street = other.Street;
this.City = other.City;
this.State = other.State;
this.Zip = other.Zip;
this.Location = other.Location;
}
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 });
}
