/// <summary>
/// Checks if the file matches a known format.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <returns></returns>
private bool FileTypeMatches( CsvDataModel.RockDataType filetype, string name )
{
if ( name.ToUpper().Equals( filetype.ToString() ) )
{
return true;
}
return false;
}
/// <summary>
/// Loads the individual data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadIndividuals(CsvDataModel csvData)
{
var lookupContext = new RockContext();
var groupTypeRoleService = new GroupTypeRoleService(lookupContext);
var groupMemberService = new GroupMemberService(lookupContext);
// Marital statuses: Married, Single, Separated, etc
var maritalStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS), lookupContext).DefinedValues;
// Connection statuses: Member, Visitor, Attendee, etc
var connectionStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS), lookupContext).DefinedValues;
int memberConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER)).Id;
int visitorConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR)).Id;
int attendeeConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE)).Id;
// Suffix type: Dr., Jr., II, etc
var suffixTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_SUFFIX), lookupContext).DefinedValues;
// Title type: Mr., Mrs. Dr., etc
var titleTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_TITLE), lookupContext).DefinedValues;
// Record statuses: Active, Inactive, Pending
int?recordStatusActiveId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE), lookupContext).Id;
int?recordStatusInactiveId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE), lookupContext).Id;
int?recordStatusPendingId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_PENDING), lookupContext).Id;
// Deceased record status reason (others available: No Activity, Moved, etc)
var recordStatusDeceasedId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_REASON_DECEASED)).Id;
// Record type: Person
int?personRecordTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON), lookupContext).Id;
// Group roles: Owner, Adult, Child, others
GroupTypeRole ownerRole = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER));
int adultRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT)).Id;
int childRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD)).Id;
// Phone types: Home, Work, Mobile
var numberTypeValues = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE), lookupContext).DefinedValues;
// Timeline note type id
var noteTimelineTypeId = new NoteTypeService(lookupContext).Get(new Guid("7E53487C-D650-4D85-97E2-350EB8332763")).Id;
// School defined type
var schoolDefinedType = DefinedTypeCache.Read(new Guid("576FF1E2-6225-4565-A16D-230E26167A3D"));
// Look up additional Person attributes (existing)
var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId).ToList();
// Core attributes: PreviousChurch, Position, Employer, School, etc
var previousChurchAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "PreviousChurch"));
var employerAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "Employer"));
var positionAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "Position"));
var firstVisitAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "FirstVisit"));
var schoolAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "School"));
var membershipDateAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "MembershipDate"));
var baptismDateAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "BaptismDate"));
var facebookAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "Facebook"));
var twitterAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "Twitter"));
var instagramAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "Instagram"));
// Text field type id
int textFieldTypeId = FieldTypeCache.Read(new Guid(Rock.SystemGuid.FieldType.TEXT), lookupContext).Id;
// Attribute entity type id
int attributeEntityTypeId = EntityTypeCache.Read("Rock.Model.Attribute").Id;
// Visit info category
var visitInfoCategory = new CategoryService(lookupContext).GetByEntityTypeId(attributeEntityTypeId)
.Where(c => c.Name == "Visit Information").FirstOrDefault();
// Add a Secondary Email attribute if it doesn't exist
var secondaryEmail = personAttributes.FirstOrDefault(a => a.Key == "SecondaryEmail");
if (secondaryEmail == null)
{
secondaryEmail = new Rock.Model.Attribute();
secondaryEmail.Key = "SecondaryEmail";
secondaryEmail.Name = "Secondary Email";
secondaryEmail.FieldTypeId = textFieldTypeId;
secondaryEmail.EntityTypeId = PersonEntityTypeId;
secondaryEmail.EntityTypeQualifierValue = string.Empty;
secondaryEmail.EntityTypeQualifierColumn = string.Empty;
secondaryEmail.Description = "The secondary email for this person";
secondaryEmail.DefaultValue = string.Empty;
secondaryEmail.IsMultiValue = false;
secondaryEmail.IsRequired = false;
secondaryEmail.Order = 0;
lookupContext.Attributes.Add(secondaryEmail);
secondaryEmail.Categories.Add(visitInfoCategory);
lookupContext.SaveChanges(true);
}
var secondaryEmailAttribute = AttributeCache.Read(secondaryEmail.Id, lookupContext);
// Add a former name attribute
var formerName = personAttributes.FirstOrDefault(a => a.Key == "FormerName");
if (formerName == null)
{
formerName = new Rock.Model.Attribute();
formerName.Key = "FormerName";
formerName.Name = "Former Name";
formerName.FieldTypeId = textFieldTypeId;
formerName.EntityTypeId = PersonEntityTypeId;
formerName.EntityTypeQualifierValue = string.Empty;
formerName.EntityTypeQualifierColumn = string.Empty;
formerName.Description = "The former name for this person";
formerName.DefaultValue = string.Empty;
formerName.IsMultiValue = false;
formerName.IsRequired = false;
formerName.Order = 0;
lookupContext.Attributes.Add(formerName);
secondaryEmail.Categories.Add(visitInfoCategory);
lookupContext.SaveChanges(true);
}
var formerNameAttribute = AttributeCache.Read(formerName.Id, lookupContext);
// Look for custom attributes in the Individual file
var allFields = csvData.TableNodes.FirstOrDefault().Columns.Select((node, index) => new { node = node, index = index }).ToList();
Dictionary <int, string> customAttributes = allFields.Where(f => f.index > Twitter).ToDictionary(f => f.index, f => f.node.Name);
// Add any if they don't already exist
if (customAttributes.Any())
{
var newAttributes = new List <Rock.Model.Attribute>();
foreach (var newAttributePair in customAttributes.Where(ca => !personAttributes.Any(a => a.Name == ca.Value)))
{
var newAttribute = new Rock.Model.Attribute();
newAttribute.Name = newAttributePair.Value;
newAttribute.Key = newAttributePair.Value.RemoveWhitespace();
newAttribute.Description = newAttributePair.Value + " created by CSV import";
newAttribute.EntityTypeQualifierValue = string.Empty;
newAttribute.EntityTypeQualifierColumn = string.Empty;
newAttribute.EntityTypeId = PersonEntityTypeId;
newAttribute.FieldTypeId = textFieldTypeId;
newAttribute.DefaultValue = string.Empty;
newAttribute.IsMultiValue = false;
newAttribute.IsGridColumn = false;
newAttribute.IsRequired = false;
newAttribute.Order = 0;
newAttributes.Add(newAttribute);
}
lookupContext.Attributes.AddRange(newAttributes);
lookupContext.SaveChanges(true);
personAttributes.AddRange(newAttributes);
}
var dateFormats = new[] { "MM/dd/yyyy", "MM/dd/yy" };
var currentFamilyGroup = new Group();
var newFamilyList = new List <Group>();
var newVisitorList = new List <Group>();
var importDate = DateTime.Now;
int completed = 0;
ReportProgress(0, string.Format("Starting Individual import ({0:N0} already exist).", ImportedPeople.Count(p => p.Members.Any(m => m.Person.ForeignId != null))));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
int groupRoleId = adultRoleId;
bool isFamilyRelationship = true;
string rowFamilyId = row[FamilyId];
string rowPersonId = row[PersonId];
string rowFamilyName = row[FamilyName];
if (!string.IsNullOrWhiteSpace(rowFamilyId) && rowFamilyId != currentFamilyGroup.ForeignId)
{
currentFamilyGroup = ImportedPeople.FirstOrDefault(p => p.ForeignId == rowFamilyId);
if (currentFamilyGroup == null)
{
currentFamilyGroup = new Group();
currentFamilyGroup.ForeignId = rowFamilyId;
currentFamilyGroup.Name = row[FamilyName];
currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentFamilyGroup.GroupTypeId = FamilyGroupTypeId;
}
}
// Verify this person isn't already in our data
var personExists = ImportedPeople.Any(p => p.Members.Any(m => m.Person.ForeignId == rowPersonId));
if (!personExists)
{
var person = new Person();
person.ForeignId = rowPersonId;
person.SystemNote = string.Format("Imported via Excavator on {0}", importDate.ToString());
person.RecordTypeValueId = personRecordTypeId;
person.CreatedByPersonAliasId = ImportPersonAlias.Id;
string firstName = row[FirstName];
person.FirstName = firstName;
person.NickName = row[NickName] ?? firstName;
person.MiddleName = row[MiddleName];
person.LastName = row[LastName];
#region Assign values to the Person record
DateTime birthDate;
if (DateTime.TryParseExact(row[DateOfBirth], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out birthDate))
{
person.BirthDate = birthDate;
}
DateTime anniversary;
if (DateTime.TryParseExact(row[Anniversary], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out anniversary))
{
person.AnniversaryDate = anniversary;
}
string gender = row[Gender];
if (gender != null)
{
switch (gender.Trim().ToLower())
{
case "m":
case "male":
person.Gender = Rock.Model.Gender.Male;
break;
case "f":
case "female":
person.Gender = Rock.Model.Gender.Female;
break;
default:
person.Gender = Rock.Model.Gender.Unknown;
break;
}
}
string prefix = row[Prefix];
if (!string.IsNullOrWhiteSpace(prefix))
{
prefix = prefix.RemoveSpecialCharacters().Trim();
person.TitleValueId = titleTypes.Where(s => prefix == s.Value.RemoveSpecialCharacters())
.Select(s => (int?)s.Id).FirstOrDefault();
}
string suffix = row[Suffix];
if (!string.IsNullOrWhiteSpace(suffix))
{
suffix = suffix.RemoveSpecialCharacters().Trim();
person.SuffixValueId = suffixTypes.Where(s => suffix == s.Value.RemoveSpecialCharacters())
.Select(s => (int?)s.Id).FirstOrDefault();
}
string maritalStatus = row[MaritalStatus];
if (!string.IsNullOrWhiteSpace(maritalStatus))
{
person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value == maritalStatus)
.Select(dv => (int?)dv.Id).FirstOrDefault();
}
else
{
person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value == "Unknown")
.Select(dv => (int?)dv.Id).FirstOrDefault();
}
string familyRole = row[FamilyRole];
if (!string.IsNullOrWhiteSpace(familyRole))
{
if (familyRole == "Visitor")
{
isFamilyRelationship = false;
}
if (familyRole == "Child" || person.Age < 18)
{
groupRoleId = childRoleId;
}
}
string connectionStatus = row[ConnectionStatus];
if (!string.IsNullOrWhiteSpace(connectionStatus))
{
if (connectionStatus == "Member")
{
person.ConnectionStatusValueId = memberConnectionStatusId;
}
else if (connectionStatus == "Visitor")
{
person.ConnectionStatusValueId = visitorConnectionStatusId;
}
else if (connectionStatus == "Deceased")
{
person.IsDeceased = true;
person.RecordStatusReasonValueId = recordStatusDeceasedId;
}
else
{
// look for user-defined connection type or default to Attendee
var customConnectionType = connectionStatusTypes.Where(dv => dv.Value == connectionStatus)
.Select(dv => (int?)dv.Id).FirstOrDefault();
person.ConnectionStatusValueId = customConnectionType ?? attendeeConnectionStatusId;
person.RecordStatusValueId = recordStatusActiveId;
}
}
string recordStatus = row[RecordStatus];
if (!string.IsNullOrWhiteSpace(recordStatus))
{
switch (recordStatus.Trim())
{
case "Active":
person.RecordStatusValueId = recordStatusActiveId;
break;
case "Inactive":
person.RecordStatusValueId = recordStatusInactiveId;
break;
default:
person.RecordStatusValueId = recordStatusPendingId;
break;
}
}
var personNumbers = new Dictionary <string, string>();
personNumbers.Add("Home", row[HomePhone]);
personNumbers.Add("Mobile", row[MobilePhone]);
personNumbers.Add("Work", row[WorkPhone]);
string smsAllowed = row[AllowSMS];
foreach (var numberPair in personNumbers.Where(n => !string.IsNullOrWhiteSpace(n.Value)))
{
var extension = string.Empty;
var countryCode = Rock.Model.PhoneNumber.DefaultCountryCode();
var normalizedNumber = string.Empty;
var countryIndex = numberPair.Value.IndexOf('+');
int extensionIndex = numberPair.Value.LastIndexOf('x') > 0 ? numberPair.Value.LastIndexOf('x') : numberPair.Value.Length;
if (countryIndex >= 0)
{
countryCode = numberPair.Value.Substring(countryIndex, countryIndex + 3).AsNumeric();
normalizedNumber = numberPair.Value.Substring(countryIndex + 3, extensionIndex - 3).AsNumeric();
extension = numberPair.Value.Substring(extensionIndex);
}
else if (extensionIndex > 0)
{
normalizedNumber = numberPair.Value.Substring(0, extensionIndex).AsNumeric();
extension = numberPair.Value.Substring(extensionIndex).AsNumeric();
}
else
{
normalizedNumber = numberPair.Value.AsNumeric();
}
if (!string.IsNullOrWhiteSpace(normalizedNumber))
{
var currentNumber = new PhoneNumber();
currentNumber.CountryCode = countryCode;
currentNumber.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentNumber.Extension = extension.Left(20);
currentNumber.Number = normalizedNumber.Left(20);
currentNumber.NumberTypeValueId = numberTypeValues.Where(v => v.Value.Equals(numberPair.Key))
.Select(v => (int?)v.Id).FirstOrDefault();
if (numberPair.Key == "Mobile")
{
switch (smsAllowed.Trim().ToLower())
{
case "y":
case "yes":
case "active":
currentNumber.IsMessagingEnabled = true;
break;
default:
currentNumber.IsMessagingEnabled = false;
break;
}
}
person.PhoneNumbers.Add(currentNumber);
}
}
// Map Person attributes
person.Attributes = new Dictionary <string, AttributeCache>();
person.AttributeValues = new Dictionary <string, AttributeValue>();
string formerNameValue = row[FormerName];
if (!string.IsNullOrWhiteSpace(formerNameValue))
{
AddPersonAttribute(formerNameAttribute, person, formerNameValue);
}
bool isEmailActive;
switch (row[IsEmailActive].Trim().ToLower())
{
case "n":
case "no":
case "inactive":
isEmailActive = false;
break;
default:
isEmailActive = true;
break;
}
EmailPreference emailPreference;
switch (row[AllowBulkEmail].Trim().ToLower())
{
case "n":
case "no":
case "inactive":
emailPreference = EmailPreference.NoMassEmails;
break;
default:
emailPreference = EmailPreference.EmailAllowed;
break;
}
string primaryEmail = row[Email];
if (!string.IsNullOrWhiteSpace(primaryEmail))
{
person.Email = primaryEmail;
person.IsEmailActive = isEmailActive;
person.EmailPreference = emailPreference;
}
string secondaryEmailValue = row[SecondaryEmail];
if (!string.IsNullOrWhiteSpace(secondaryEmailValue))
{
AddPersonAttribute(secondaryEmailAttribute, person, secondaryEmailValue);
}
DateTime membershipDateValue;
if (DateTime.TryParseExact(row[MembershipDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out membershipDateValue))
{
AddPersonAttribute(membershipDateAttribute, person, membershipDateValue.ToString());
}
DateTime baptismDateValue;
if (DateTime.TryParseExact(row[BaptismDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out baptismDateValue))
{
AddPersonAttribute(baptismDateAttribute, person, baptismDateValue.ToString());
}
DateTime firstVisitValue;
if (DateTime.TryParseExact(row[FirstVisit], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out firstVisitValue))
{
AddPersonAttribute(firstVisitAttribute, person, firstVisitValue.ToString());
}
string previousChurchValue = row[PreviousChurch];
if (!string.IsNullOrWhiteSpace(previousChurchValue))
{
AddPersonAttribute(previousChurchAttribute, person, previousChurchValue);
}
string positionValue = row[Occupation];
if (!string.IsNullOrWhiteSpace(positionValue))
{
AddPersonAttribute(positionAttribute, person, positionValue);
}
string employerValue = row[Employer];
if (!string.IsNullOrWhiteSpace(employerValue))
{
AddPersonAttribute(employerAttribute, person, employerValue);
}
string schoolName = row[School];
if (!string.IsNullOrWhiteSpace(schoolName))
{
// Add school if it doesn't exist
Guid schoolGuid;
var schoolExists = schoolDefinedType.DefinedValues.Any(s => s.Value.Equals(schoolName));
if (!schoolExists)
{
var newSchool = new DefinedValue();
newSchool.DefinedTypeId = schoolDefinedType.Id;
newSchool.Value = schoolName;
newSchool.Order = 0;
lookupContext.DefinedValues.Add(newSchool);
lookupContext.SaveChanges();
schoolGuid = newSchool.Guid;
}
else
{
schoolGuid = schoolDefinedType.DefinedValues.FirstOrDefault(s => s.Value.Equals(schoolName)).Guid;
}
AddPersonAttribute(schoolAttribute, person, schoolGuid.ToString());
}
string facebookValue = row[Facebook];
if (!string.IsNullOrWhiteSpace(facebookValue))
{
AddPersonAttribute(facebookAttribute, person, facebookValue);
}
string twitterValue = row[Twitter];
if (!string.IsNullOrWhiteSpace(twitterValue))
{
AddPersonAttribute(twitterAttribute, person, twitterValue);
}
string instagramValue = row[Instagram];
if (!string.IsNullOrWhiteSpace(instagramValue))
{
AddPersonAttribute(instagramAttribute, person, instagramValue);
}
foreach (var attributePair in customAttributes)
{
string newAttributeValue = row[attributePair.Key];
if (!string.IsNullOrWhiteSpace(newAttributeValue))
{
int?newAttributeId = personAttributes.Where(a => a.Key == attributePair.Value)
.Select(a => (int?)a.Id).FirstOrDefault();
if (newAttributeId != null)
{
var newAttribute = AttributeCache.Read((int)newAttributeId);
AddPersonAttribute(newAttribute, person, newAttributeValue);
}
}
}
// Add notes to timeline
var notePairs = new Dictionary <string, string>();
notePairs.Add("General", row[GeneralNote]);
notePairs.Add("Medical", row[MedicalNote]);
notePairs.Add("Security", row[SecurityNote]);
var newNoteList = new List <Note>();
foreach (var notePair in notePairs.Where(n => !string.IsNullOrWhiteSpace(n.Value)))
{
var newNote = new Note();
newNote.CreatedByPersonAliasId = ImportPersonAlias.Id;
newNote.CreatedDateTime = importDate;
newNote.EntityId = person.Id;
newNote.Text = notePair.Value;
newNote.NoteTypeId = noteTimelineTypeId;
newNote.Caption = string.Format("{0} Note", notePair.Key);
if (!notePair.Key.Equals("General"))
{
newNote.IsAlert = true;
}
newNoteList.Add(newNote);
}
if (newNoteList.Any())
{
lookupContext.Notes.AddRange(newNoteList);
lookupContext.SaveChanges(true);
}
#endregion
var groupMember = new GroupMember();
groupMember.Person = person;
groupMember.GroupRoleId = groupRoleId;
groupMember.GroupMemberStatus = GroupMemberStatus.Active;
if (isFamilyRelationship || currentFamilyGroup.Members.Count() < 1)
{
currentFamilyGroup.Members.Add(groupMember);
newFamilyList.Add(currentFamilyGroup);
completed++;
}
else
{
var visitorGroup = new Group();
visitorGroup.ForeignId = rowFamilyId.ToString();
visitorGroup.Members.Add(groupMember);
visitorGroup.GroupTypeId = FamilyGroupTypeId;
visitorGroup.Name = person.LastName + " Family";
newFamilyList.Add(visitorGroup);
completed++;
newVisitorList.Add(visitorGroup);
}
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} people imported.", completed));
}
else if (completed % ReportingNumber < 1)
{
SaveIndividuals(newFamilyList, newVisitorList);
ReportPartialProgress();
newFamilyList.Clear();
}
}
}
if (newFamilyList.Any())
{
SaveIndividuals(newFamilyList, newVisitorList);
}
ReportProgress(0, string.Format("Finished individual import: {0:N0} people imported.", completed));
return(completed);
}
/// <summary>
/// Loads the family data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadFamily(CsvDataModel csvData)
{
// Required variables
var lookupContext = new RockContext();
var locationService = new LocationService(lookupContext);
int familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id;
int numImportedFamilies = ImportedPeople.Select(p => p.ForeignId).Distinct().Count();
int homeLocationTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME)).Id;
int workLocationTypeId = DefinedValueCache.Read(new Guid("E071472A-F805-4FC4-917A-D5E3C095C35C")).Id;
var newGroupLocations = new Dictionary <GroupLocation, string>();
var currentFamilyGroup = new Group();
var newFamilyList = new List <Group>();
var updatedFamilyList = new List <Group>();
var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" };
string currentFamilyId = string.Empty;
var importDate = DateTime.Now;
int completed = 0;
ReportProgress(0, string.Format("Starting family import ({0:N0} already exist).", numImportedFamilies));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string rowFamilyId = row[FamilyId];
string rowFamilyName = row[FamilyName];
if (!string.IsNullOrWhiteSpace(rowFamilyId) && rowFamilyId != currentFamilyGroup.ForeignId)
{
currentFamilyGroup = ImportedPeople.FirstOrDefault(p => p.ForeignId == rowFamilyId);
if (currentFamilyGroup == null)
{
currentFamilyGroup = new Group();
currentFamilyGroup.ForeignId = rowFamilyId;
currentFamilyGroup.Name = row[FamilyName];
currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentFamilyGroup.GroupTypeId = familyGroupTypeId;
newFamilyList.Add(currentFamilyGroup);
}
else
{
lookupContext.Groups.Attach(currentFamilyGroup);
}
// Set the family campus
string campusName = row[Campus];
if (!string.IsNullOrWhiteSpace(campusName))
{
var familyCampus = CampusList.Where(c => c.Name.Equals(campusName, StringComparison.InvariantCultureIgnoreCase) ||
c.ShortCode.Equals(campusName, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();
if (familyCampus == null)
{
familyCampus = new Campus();
familyCampus.IsSystem = false;
familyCampus.Name = campusName;
lookupContext.Campuses.Add(familyCampus);
lookupContext.SaveChanges(true);
CampusList.Add(familyCampus);
}
currentFamilyGroup.CampusId = familyCampus.Id;
}
// Add the family addresses since they exist in this file
string famAddress = row[Address];
string famAddress2 = row[Address2];
string famCity = row[City];
string famState = row[State];
string famZip = row[Zip];
string famCountry = row[Country];
Location primaryAddress = locationService.Get(famAddress, famAddress2, famCity, famState, famZip, famCountry);
if (primaryAddress != null)
{
var primaryLocation = new GroupLocation();
primaryLocation.LocationId = primaryAddress.Id;
primaryLocation.IsMailingLocation = true;
primaryLocation.IsMappedLocation = true;
primaryLocation.GroupLocationTypeValueId = homeLocationTypeId;
newGroupLocations.Add(primaryLocation, rowFamilyId);
}
string famSecondAddress = row[SecondaryAddress];
string famSecondAddress2 = row[SecondaryAddress2];
string famSecondCity = row[SecondaryCity];
string famSecondState = row[SecondaryState];
string famSecondZip = row[SecondaryZip];
string famSecondCountry = row[SecondaryCountry];
Location secondaryAddress = locationService.Get(famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry);
if (secondaryAddress != null)
{
var secondaryLocation = new GroupLocation();
secondaryLocation.LocationId = secondaryAddress.Id;
secondaryLocation.IsMailingLocation = true;
secondaryLocation.IsMappedLocation = true;
secondaryLocation.GroupLocationTypeValueId = workLocationTypeId;
newGroupLocations.Add(secondaryLocation, rowFamilyId);
}
DateTime createdDateValue;
if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue))
{
currentFamilyGroup.CreatedDateTime = createdDateValue;
currentFamilyGroup.ModifiedDateTime = importDate;
}
else
{
currentFamilyGroup.CreatedDateTime = importDate;
currentFamilyGroup.ModifiedDateTime = importDate;
}
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} families imported.", completed));
}
else if (completed % ReportingNumber < 1)
{
SaveFamilies(newFamilyList, newGroupLocations);
ReportPartialProgress();
// Reset lookup context
lookupContext.SaveChanges();
lookupContext = new RockContext();
locationService = new LocationService(lookupContext);
newFamilyList.Clear();
newGroupLocations.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if (newGroupLocations.Any())
{
SaveFamilies(newFamilyList, newGroupLocations);
}
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress(0, string.Format("Finished family import: {0:N0} families added or updated.", completed));
return(completed);
}
/// <summary>
/// Loads the family data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadFamily( CsvDataModel csvData )
{
// Required variables
var lookupContext = new RockContext();
var locationService = new LocationService( lookupContext );
int familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id;
int numImportedFamilies = ImportedPeople.Select( p => p.ForeignId ).Distinct().Count();
int homeLocationTypeId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME ) ).Id;
int workLocationTypeId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_WORK ) ).Id;
var newGroupLocations = new Dictionary<GroupLocation, string>();
var currentFamilyGroup = new Group();
var newFamilyList = new List<Group>();
var updatedFamilyList = new List<Group>();
var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" };
string currentFamilyId = string.Empty;
var importDate = DateTime.Now;
int completed = 0;
ReportProgress( 0, string.Format( "Starting family import ({0:N0} already exist).", numImportedFamilies ) );
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ( ( row = csvData.Database.FirstOrDefault() ) != null )
{
string rowFamilyId = row[FamilyId];
string rowFamilyName = row[FamilyName];
if ( !string.IsNullOrWhiteSpace( rowFamilyId ) && rowFamilyId != currentFamilyGroup.ForeignId )
{
currentFamilyGroup = ImportedPeople.FirstOrDefault( p => p.ForeignId == rowFamilyId );
if ( currentFamilyGroup == null )
{
currentFamilyGroup = new Group();
currentFamilyGroup.ForeignId = rowFamilyId;
currentFamilyGroup.Name = row[FamilyName];
currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentFamilyGroup.GroupTypeId = familyGroupTypeId;
newFamilyList.Add( currentFamilyGroup );
}
else
{
lookupContext.Groups.Attach( currentFamilyGroup );
}
// Set the family campus
string campusName = row[Campus];
if ( !string.IsNullOrWhiteSpace( campusName ) )
{
var familyCampus = CampusList.Where( c => c.Name.Equals( campusName, StringComparison.InvariantCultureIgnoreCase )
|| c.ShortCode.Equals( campusName, StringComparison.InvariantCultureIgnoreCase ) ).FirstOrDefault();
if ( familyCampus == null )
{
familyCampus = new Campus();
familyCampus.IsSystem = false;
familyCampus.Name = campusName;
lookupContext.Campuses.Add( familyCampus );
lookupContext.SaveChanges( true );
CampusList.Add( familyCampus );
}
currentFamilyGroup.CampusId = familyCampus.Id;
}
// Add the family addresses since they exist in this file
string famAddress = row[Address];
string famAddress2 = row[Address2];
string famCity = row[City];
string famState = row[State];
string famZip = row[Zip];
string famCountry = row[Country];
Location primaryAddress = Extensions.GetWithoutVerify( famAddress, famAddress2, famCity, famState, famZip, famCountry, false );
if ( primaryAddress != null )
{
var primaryLocation = new GroupLocation();
primaryLocation.LocationId = primaryAddress.Id;
primaryLocation.IsMailingLocation = true;
primaryLocation.IsMappedLocation = true;
primaryLocation.GroupLocationTypeValueId = homeLocationTypeId;
newGroupLocations.Add( primaryLocation, rowFamilyId );
}
string famSecondAddress = row[SecondaryAddress];
string famSecondAddress2 = row[SecondaryAddress2];
string famSecondCity = row[SecondaryCity];
string famSecondState = row[SecondaryState];
string famSecondZip = row[SecondaryZip];
string famSecondCountry = row[SecondaryCountry];
Location secondaryAddress = Extensions.GetWithoutVerify( famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry, false );
if ( secondaryAddress != null )
{
var secondaryLocation = new GroupLocation();
secondaryLocation.LocationId = secondaryAddress.Id;
secondaryLocation.IsMailingLocation = true;
secondaryLocation.IsMappedLocation = true;
secondaryLocation.GroupLocationTypeValueId = workLocationTypeId;
newGroupLocations.Add( secondaryLocation, rowFamilyId );
}
DateTime createdDateValue;
if ( DateTime.TryParseExact( row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue ) )
{
currentFamilyGroup.CreatedDateTime = createdDateValue;
currentFamilyGroup.ModifiedDateTime = importDate;
}
else
{
currentFamilyGroup.CreatedDateTime = importDate;
currentFamilyGroup.ModifiedDateTime = importDate;
}
completed++;
if ( completed % ( ReportingNumber * 10 ) < 1 )
{
ReportProgress( 0, string.Format( "{0:N0} families imported.", completed ) );
}
else if ( completed % ReportingNumber < 1 )
{
SaveFamilies( newFamilyList, newGroupLocations );
ReportPartialProgress();
// Reset lookup context
lookupContext.SaveChanges();
lookupContext = new RockContext();
locationService = new LocationService( lookupContext );
newFamilyList.Clear();
newGroupLocations.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if ( newGroupLocations.Any() )
{
SaveFamilies( newFamilyList, newGroupLocations );
}
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress( 0, string.Format( "Finished family import: {0:N0} families added or updated.", completed ) );
return completed;
}
/// <summary>
/// Loads the family data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadFamily( CsvDataModel csvData )
{
// Required variables
var lookupContext = new RockContext();
var locationService = new LocationService( lookupContext );
int familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id;
int numImportedFamilies = ImportedPeople.Select( p => p.ForeignId ).Distinct().Count();
int homeLocationTypeId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME ) ).Id;
int workLocationTypeId = DefinedValueCache.Read( new Guid( "E071472A-F805-4FC4-917A-D5E3C095C35C" ) ).Id;
var currentFamilyGroup = new Group();
var newFamilyList = new List<Group>();
var newGroupLocations = new Dictionary<GroupLocation, string>();
string currentFamilyId = string.Empty;
int completed = 0;
ReportProgress( 0, string.Format( "Starting family import ({0:N0} already exist).", numImportedFamilies ) );
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ( ( row = csvData.Database.FirstOrDefault() ) != null )
{
string rowFamilyId = row[FamilyId];
string rowFamilyName = row[FamilyName];
if ( !string.IsNullOrWhiteSpace( rowFamilyId ) && rowFamilyId != currentFamilyGroup.ForeignId )
{
currentFamilyGroup = ImportedPeople.FirstOrDefault( p => p.ForeignId == rowFamilyId );
if ( currentFamilyGroup == null )
{
currentFamilyGroup = new Group();
currentFamilyGroup.ForeignId = rowFamilyId;
currentFamilyGroup.Name = row[FamilyName];
currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentFamilyGroup.GroupTypeId = familyGroupTypeId;
newFamilyList.Add( currentFamilyGroup );
}
// Set the family campus
string campusName = row[Campus];
if ( !string.IsNullOrWhiteSpace( campusName ) )
{
var familyCampus = CampusList.Where( c => c.Name.StartsWith( campusName ) || c.ShortCode.StartsWith( campusName ) ).FirstOrDefault();
if ( familyCampus == null )
{
familyCampus = new Campus();
familyCampus.IsSystem = false;
familyCampus.Name = campusName;
lookupContext.Campuses.Add( familyCampus );
lookupContext.SaveChanges( true );
}
// This won't assign a campus if the family already exists because the context doesn't get saved
currentFamilyGroup.CampusId = familyCampus.Id;
}
// Add the family addresses since they exist in this file
string famAddress = row[Address];
string famAddress2 = row[Address2];
string famCity = row[City];
string famState = row[State];
string famZip = row[Zip];
string famCountry = row[Country];
// Use the core Rock location service to add or lookup an address
Location primaryAddress = locationService.Get( famAddress, famAddress2, famCity, famState, famZip, famCountry );
if ( primaryAddress != null )
{
primaryAddress.Name = currentFamilyGroup.Name + " Home";
var primaryLocation = new GroupLocation();
primaryLocation.LocationId = primaryAddress.Id;
primaryLocation.IsMailingLocation = true;
primaryLocation.IsMappedLocation = true;
primaryLocation.GroupLocationTypeValueId = homeLocationTypeId;
newGroupLocations.Add( primaryLocation, rowFamilyId );
}
string famSecondAddress = row[SecondaryAddress];
string famSecondAddress2 = row[SecondaryAddress2];
string famSecondCity = row[SecondaryCity];
string famSecondState = row[SecondaryState];
string famSecondZip = row[SecondaryZip];
string famSecondCountry = row[SecondaryCountry];
Location secondaryAddress = locationService.Get( famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry );
if ( secondaryAddress != null )
{
secondaryAddress.Name = currentFamilyGroup.Name + " Work";
var secondaryLocation = new GroupLocation();
secondaryLocation.LocationId = primaryAddress.Id;
secondaryLocation.IsMailingLocation = true;
secondaryLocation.IsMappedLocation = true;
secondaryLocation.GroupLocationTypeValueId = workLocationTypeId;
newGroupLocations.Add( secondaryLocation, rowFamilyId );
}
completed++;
if ( completed % ( ReportingNumber * 10 ) < 1 )
{
ReportProgress( 0, string.Format( "{0:N0} families imported.", completed ) );
}
else if ( completed % ReportingNumber < 1 )
{
SaveFamilies( newFamilyList, newGroupLocations );
ReportPartialProgress();
// Reset lookup context
lookupContext = new RockContext();
locationService = new LocationService( lookupContext );
newFamilyList.Clear();
newGroupLocations.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if ( newGroupLocations.Any() )
{
SaveFamilies( newFamilyList, newGroupLocations );
}
ReportProgress( 0, string.Format( "Finished family import: {0:N0} families imported.", completed ) );
return completed;
}
/// <summary>
/// Loads the individual data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadIndividuals( CsvDataModel csvData )
{
var lookupContext = new RockContext();
var groupTypeRoleService = new GroupTypeRoleService( lookupContext );
var groupMemberService = new GroupMemberService( lookupContext );
// Marital statuses: Married, Single, Separated, etc
var maritalStatusTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS ), lookupContext ).DefinedValues;
// Connection statuses: Member, Visitor, Attendee, etc
var connectionStatusTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS ), lookupContext ).DefinedValues;
int memberConnectionStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER ) ).Id;
int visitorConnectionStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR ) ).Id;
int attendeeConnectionStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE ) ).Id;
// Suffix type: Dr., Jr., II, etc
var suffixTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_SUFFIX ), lookupContext ).DefinedValues;
// Title type: Mr., Mrs. Dr., etc
var titleTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_TITLE ), lookupContext ).DefinedValues;
// Record statuses: Active, Inactive, Pending
int? recordStatusActiveId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE ), lookupContext ).Id;
int? recordStatusInactiveId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE ), lookupContext ).Id;
int? recordStatusPendingId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_PENDING ), lookupContext ).Id;
// Deceased record status reason (others available: No Activity, Moved, etc)
var recordStatusDeceasedId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_REASON_DECEASED ) ).Id;
// Record type: Person
int? personRecordTypeId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON ), lookupContext ).Id;
// Group roles: Owner, Adult, Child, others
GroupTypeRole ownerRole = groupTypeRoleService.Get( new Guid( Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER ) );
int adultRoleId = groupTypeRoleService.Get( new Guid( Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT ) ).Id;
int childRoleId = groupTypeRoleService.Get( new Guid( Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD ) ).Id;
// Phone types: Home, Work, Mobile
var numberTypeValues = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE ), lookupContext ).DefinedValues;
// Timeline note type id
var noteTimelineTypeId = new NoteTypeService( lookupContext ).Get( new Guid( "7E53487C-D650-4D85-97E2-350EB8332763" ) ).Id;
// School defined type
var schoolDefinedType = DefinedTypeCache.Read( new Guid( "576FF1E2-6225-4565-A16D-230E26167A3D" ) );
// Look up additional Person attributes (existing)
var personAttributes = new AttributeService( lookupContext ).GetByEntityTypeId( PersonEntityTypeId ).ToList();
// Core attributes: PreviousChurch, Position, Employer, School, etc
var previousChurchAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "PreviousChurch" ) );
var employerAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Employer" ) );
var positionAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Position" ) );
var firstVisitAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "FirstVisit" ) );
var schoolAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "School" ) );
var membershipDateAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "MembershipDate" ) );
var baptismDateAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "BaptismDate" ) );
var facebookAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Facebook" ) );
var twitterAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Twitter" ) );
var instagramAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Instagram" ) );
// Text field type id
int textFieldTypeId = FieldTypeCache.Read( new Guid( Rock.SystemGuid.FieldType.TEXT ), lookupContext ).Id;
// Attribute entity type id
int attributeEntityTypeId = EntityTypeCache.Read( "Rock.Model.Attribute" ).Id;
// Visit info category
var visitInfoCategory = new CategoryService( lookupContext ).GetByEntityTypeId( attributeEntityTypeId )
.Where( c => c.Name == "Visit Information" ).FirstOrDefault();
// Add a Secondary Email attribute if it doesn't exist
var secondaryEmail = personAttributes.FirstOrDefault( a => a.Key == "SecondaryEmail" );
if ( secondaryEmail == null )
{
secondaryEmail = new Rock.Model.Attribute();
secondaryEmail.Key = "SecondaryEmail";
secondaryEmail.Name = "Secondary Email";
secondaryEmail.FieldTypeId = textFieldTypeId;
secondaryEmail.EntityTypeId = PersonEntityTypeId;
secondaryEmail.EntityTypeQualifierValue = string.Empty;
secondaryEmail.EntityTypeQualifierColumn = string.Empty;
secondaryEmail.Description = "The secondary email for this person";
secondaryEmail.DefaultValue = string.Empty;
secondaryEmail.IsMultiValue = false;
secondaryEmail.IsRequired = false;
secondaryEmail.Order = 0;
lookupContext.Attributes.Add( secondaryEmail );
secondaryEmail.Categories.Add( visitInfoCategory );
lookupContext.SaveChanges( true );
}
var secondaryEmailAttribute = AttributeCache.Read( secondaryEmail.Id, lookupContext );
// Add a former name attribute
var formerName = personAttributes.FirstOrDefault( a => a.Key == "FormerName" );
if ( formerName == null )
{
formerName = new Rock.Model.Attribute();
formerName.Key = "FormerName";
formerName.Name = "Former Name";
formerName.FieldTypeId = textFieldTypeId;
formerName.EntityTypeId = PersonEntityTypeId;
formerName.EntityTypeQualifierValue = string.Empty;
formerName.EntityTypeQualifierColumn = string.Empty;
formerName.Description = "The former name for this person";
formerName.DefaultValue = string.Empty;
formerName.IsMultiValue = false;
formerName.IsRequired = false;
formerName.Order = 0;
lookupContext.Attributes.Add( formerName );
secondaryEmail.Categories.Add( visitInfoCategory );
lookupContext.SaveChanges( true );
}
var formerNameAttribute = AttributeCache.Read( formerName.Id, lookupContext );
// Look for custom attributes in the Individual file
var allFields = csvData.TableNodes.FirstOrDefault().Columns.Select( ( node, index ) => new { node = node, index = index } ).ToList();
Dictionary<int, string> customAttributes = allFields.Where( f => f.index > Twitter ).ToDictionary( f => f.index, f => f.node.Name );
// Add any if they don't already exist
if ( customAttributes.Any() )
{
var newAttributes = new List<Rock.Model.Attribute>();
foreach ( var newAttributePair in customAttributes.Where( ca => !personAttributes.Any( a => a.Name == ca.Value ) ) )
{
var newAttribute = new Rock.Model.Attribute();
newAttribute.Name = newAttributePair.Value;
newAttribute.Key = newAttributePair.Value.RemoveWhitespace();
newAttribute.Description = newAttributePair.Value + " created by CSV import";
newAttribute.EntityTypeQualifierValue = string.Empty;
newAttribute.EntityTypeQualifierColumn = string.Empty;
newAttribute.EntityTypeId = PersonEntityTypeId;
newAttribute.FieldTypeId = textFieldTypeId;
newAttribute.DefaultValue = string.Empty;
newAttribute.IsMultiValue = false;
newAttribute.IsGridColumn = false;
newAttribute.IsRequired = false;
newAttribute.Order = 0;
newAttributes.Add( newAttribute );
}
lookupContext.Attributes.AddRange( newAttributes );
lookupContext.SaveChanges( true );
personAttributes.AddRange( newAttributes );
}
var dateFormats = new[] { "MM/dd/yyyy", "MM/dd/yy" };
var currentFamilyGroup = new Group();
var newFamilyList = new List<Group>();
var newVisitorList = new List<Group>();
var importDate = DateTime.Now;
int completed = 0;
ReportProgress( 0, string.Format( "Starting Individual import ({0:N0} already exist).", ImportedPeople.Count( p => p.Members.Any( m => m.Person.ForeignId != null ) ) ) );
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ( ( row = csvData.Database.FirstOrDefault() ) != null )
{
int groupRoleId = adultRoleId;
bool isFamilyRelationship = true;
string rowFamilyId = row[FamilyId];
string rowPersonId = row[PersonId];
string rowFamilyName = row[FamilyName];
if ( !string.IsNullOrWhiteSpace( rowFamilyId ) && rowFamilyId != currentFamilyGroup.ForeignId )
{
currentFamilyGroup = ImportedPeople.FirstOrDefault( p => p.ForeignId == rowFamilyId );
if ( currentFamilyGroup == null )
{
currentFamilyGroup = new Group();
currentFamilyGroup.ForeignId = rowFamilyId;
currentFamilyGroup.Name = row[FamilyName];
currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentFamilyGroup.GroupTypeId = FamilyGroupTypeId;
}
}
// Verify this person isn't already in our data
var personExists = ImportedPeople.Any( p => p.Members.Any( m => m.Person.ForeignId == rowPersonId ) );
if ( !personExists )
{
var person = new Person();
person.ForeignId = rowPersonId;
person.SystemNote = string.Format( "Imported via Excavator on {0}", importDate.ToString() );
person.RecordTypeValueId = personRecordTypeId;
person.CreatedByPersonAliasId = ImportPersonAlias.Id;
string firstName = row[FirstName];
person.FirstName = firstName;
person.NickName = row[NickName] ?? firstName;
person.MiddleName = row[MiddleName];
person.LastName = row[LastName];
#region Assign values to the Person record
DateTime birthDate;
if ( DateTime.TryParseExact( row[DateOfBirth], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out birthDate ) )
{
person.BirthDate = birthDate;
}
DateTime anniversary;
if ( DateTime.TryParseExact( row[Anniversary], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out anniversary ) )
{
person.AnniversaryDate = anniversary;
}
string gender = row[Gender];
if ( gender != null )
{
switch ( gender.Trim().ToLower() )
{
case "m":
case "male":
person.Gender = Rock.Model.Gender.Male;
break;
case "f":
case "female":
person.Gender = Rock.Model.Gender.Female;
break;
default:
person.Gender = Rock.Model.Gender.Unknown;
break;
}
}
string prefix = row[Prefix];
if ( !string.IsNullOrWhiteSpace( prefix ) )
{
prefix = prefix.RemoveSpecialCharacters().Trim();
person.TitleValueId = titleTypes.Where( s => prefix == s.Value.RemoveSpecialCharacters() )
.Select( s => (int?)s.Id ).FirstOrDefault();
}
string suffix = row[Suffix];
if ( !string.IsNullOrWhiteSpace( suffix ) )
{
suffix = suffix.RemoveSpecialCharacters().Trim();
person.SuffixValueId = suffixTypes.Where( s => suffix == s.Value.RemoveSpecialCharacters() )
.Select( s => (int?)s.Id ).FirstOrDefault();
}
string maritalStatus = row[MaritalStatus];
if ( !string.IsNullOrWhiteSpace( maritalStatus ) )
{
person.MaritalStatusValueId = maritalStatusTypes.Where( dv => dv.Value == maritalStatus )
.Select( dv => (int?)dv.Id ).FirstOrDefault();
}
else
{
person.MaritalStatusValueId = maritalStatusTypes.Where( dv => dv.Value == "Unknown" )
.Select( dv => (int?)dv.Id ).FirstOrDefault();
}
string familyRole = row[FamilyRole];
if ( !string.IsNullOrWhiteSpace( familyRole ) )
{
if ( familyRole == "Visitor" )
{
isFamilyRelationship = false;
}
if ( familyRole == "Child" || person.Age < 18 )
{
groupRoleId = childRoleId;
}
}
string connectionStatus = row[ConnectionStatus];
if ( !string.IsNullOrWhiteSpace( connectionStatus ) )
{
if ( connectionStatus == "Member" )
{
person.ConnectionStatusValueId = memberConnectionStatusId;
}
else if ( connectionStatus == "Visitor" )
{
person.ConnectionStatusValueId = visitorConnectionStatusId;
}
else if ( connectionStatus == "Deceased" )
{
person.IsDeceased = true;
person.RecordStatusReasonValueId = recordStatusDeceasedId;
}
else
{
// look for user-defined connection type or default to Attendee
var customConnectionType = connectionStatusTypes.Where( dv => dv.Value == connectionStatus )
.Select( dv => (int?)dv.Id ).FirstOrDefault();
person.ConnectionStatusValueId = customConnectionType ?? attendeeConnectionStatusId;
person.RecordStatusValueId = recordStatusActiveId;
}
}
string recordStatus = row[RecordStatus];
if ( !string.IsNullOrWhiteSpace( recordStatus ) )
{
switch ( recordStatus.Trim() )
{
case "Active":
person.RecordStatusValueId = recordStatusActiveId;
break;
case "Inactive":
person.RecordStatusValueId = recordStatusInactiveId;
break;
default:
person.RecordStatusValueId = recordStatusPendingId;
break;
}
}
var personNumbers = new Dictionary<string, string>();
personNumbers.Add( "Home", row[HomePhone] );
personNumbers.Add( "Mobile", row[MobilePhone] );
personNumbers.Add( "Work", row[WorkPhone] );
string smsAllowed = row[AllowSMS];
foreach ( var numberPair in personNumbers.Where( n => !string.IsNullOrWhiteSpace( n.Value ) ) )
{
var extension = string.Empty;
var countryCode = Rock.Model.PhoneNumber.DefaultCountryCode();
var normalizedNumber = string.Empty;
var countryIndex = numberPair.Value.IndexOf( '+' );
int extensionIndex = numberPair.Value.LastIndexOf( 'x' ) > 0 ? numberPair.Value.LastIndexOf( 'x' ) : numberPair.Value.Length;
if ( countryIndex >= 0 )
{
countryCode = numberPair.Value.Substring( countryIndex, countryIndex + 3 ).AsNumeric();
normalizedNumber = numberPair.Value.Substring( countryIndex + 3, extensionIndex - 3 ).AsNumeric();
extension = numberPair.Value.Substring( extensionIndex );
}
else if ( extensionIndex > 0 )
{
normalizedNumber = numberPair.Value.Substring( 0, extensionIndex ).AsNumeric();
extension = numberPair.Value.Substring( extensionIndex ).AsNumeric();
}
else
{
normalizedNumber = numberPair.Value.AsNumeric();
}
if ( !string.IsNullOrWhiteSpace( normalizedNumber ) )
{
var currentNumber = new PhoneNumber();
currentNumber.CountryCode = countryCode;
currentNumber.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentNumber.Extension = extension.Left( 20 );
currentNumber.Number = normalizedNumber.Left( 20 );
currentNumber.NumberTypeValueId = numberTypeValues.Where( v => v.Value.Equals( numberPair.Key ) )
.Select( v => (int?)v.Id ).FirstOrDefault();
if ( numberPair.Key == "Mobile" )
{
switch ( smsAllowed.Trim().ToLower() )
{
case "y":
case "yes":
case "active":
currentNumber.IsMessagingEnabled = true;
break;
default:
currentNumber.IsMessagingEnabled = false;
break;
}
}
person.PhoneNumbers.Add( currentNumber );
}
}
// Map Person attributes
person.Attributes = new Dictionary<string, AttributeCache>();
person.AttributeValues = new Dictionary<string, AttributeValue>();
string formerNameValue = row[FormerName];
if ( !string.IsNullOrWhiteSpace( formerNameValue ) )
{
AddPersonAttribute( formerNameAttribute, person, formerNameValue );
}
bool isEmailActive;
switch ( row[IsEmailActive].Trim().ToLower() )
{
case "n":
case "no":
case "inactive":
isEmailActive = false;
break;
default:
isEmailActive = true;
break;
}
EmailPreference emailPreference;
switch ( row[AllowBulkEmail].Trim().ToLower() )
{
case "n":
case "no":
case "inactive":
emailPreference = EmailPreference.NoMassEmails;
break;
default:
emailPreference = EmailPreference.EmailAllowed;
break;
}
string primaryEmail = row[Email];
if ( !string.IsNullOrWhiteSpace( primaryEmail ) )
{
person.Email = primaryEmail;
person.IsEmailActive = isEmailActive;
person.EmailPreference = emailPreference;
}
string secondaryEmailValue = row[SecondaryEmail];
if ( !string.IsNullOrWhiteSpace( secondaryEmailValue ) )
{
AddPersonAttribute( secondaryEmailAttribute, person, secondaryEmailValue );
}
DateTime membershipDateValue;
if ( DateTime.TryParseExact( row[MembershipDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out membershipDateValue ) )
{
AddPersonAttribute( membershipDateAttribute, person, membershipDateValue.ToString() );
}
DateTime baptismDateValue;
if ( DateTime.TryParseExact( row[BaptismDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out baptismDateValue ) )
{
AddPersonAttribute( baptismDateAttribute, person, baptismDateValue.ToString() );
}
DateTime firstVisitValue;
if ( DateTime.TryParseExact( row[FirstVisit], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out firstVisitValue ) )
{
AddPersonAttribute( firstVisitAttribute, person, firstVisitValue.ToString() );
}
string previousChurchValue = row[PreviousChurch];
if ( !string.IsNullOrWhiteSpace( previousChurchValue ) )
{
AddPersonAttribute( previousChurchAttribute, person, previousChurchValue );
}
string positionValue = row[Occupation];
if ( !string.IsNullOrWhiteSpace( positionValue ) )
{
AddPersonAttribute( positionAttribute, person, positionValue );
}
string employerValue = row[Employer];
if ( !string.IsNullOrWhiteSpace( employerValue ) )
{
AddPersonAttribute( employerAttribute, person, employerValue );
}
string schoolName = row[School];
if ( !string.IsNullOrWhiteSpace( schoolName ) )
{
// Add school if it doesn't exist
Guid schoolGuid;
var schoolExists = schoolDefinedType.DefinedValues.Any( s => s.Value.Equals( schoolName ) );
if ( !schoolExists )
{
var newSchool = new DefinedValue();
newSchool.DefinedTypeId = schoolDefinedType.Id;
newSchool.Value = schoolName;
newSchool.Order = 0;
lookupContext.DefinedValues.Add( newSchool );
lookupContext.SaveChanges();
schoolGuid = newSchool.Guid;
}
else
{
schoolGuid = schoolDefinedType.DefinedValues.FirstOrDefault( s => s.Value.Equals( schoolName ) ).Guid;
}
AddPersonAttribute( schoolAttribute, person, schoolGuid.ToString() );
}
string facebookValue = row[Facebook];
if ( !string.IsNullOrWhiteSpace( facebookValue ) )
{
AddPersonAttribute( facebookAttribute, person, facebookValue );
}
string twitterValue = row[Twitter];
if ( !string.IsNullOrWhiteSpace( twitterValue ) )
{
AddPersonAttribute( twitterAttribute, person, twitterValue );
}
string instagramValue = row[Instagram];
if ( !string.IsNullOrWhiteSpace( instagramValue ) )
{
AddPersonAttribute( instagramAttribute, person, instagramValue );
}
foreach ( var attributePair in customAttributes )
{
string newAttributeValue = row[attributePair.Key];
if ( !string.IsNullOrWhiteSpace( newAttributeValue ) )
{
int? newAttributeId = personAttributes.Where( a => a.Key == attributePair.Value )
.Select( a => (int?)a.Id ).FirstOrDefault();
if ( newAttributeId != null )
{
var newAttribute = AttributeCache.Read( (int)newAttributeId );
AddPersonAttribute( newAttribute, person, newAttributeValue );
}
}
}
// Add notes to timeline
var notePairs = new Dictionary<string, string>();
notePairs.Add( "General", row[GeneralNote] );
notePairs.Add( "Medical", row[MedicalNote] );
notePairs.Add( "Security", row[SecurityNote] );
var newNoteList = new List<Note>();
foreach ( var notePair in notePairs.Where( n => !string.IsNullOrWhiteSpace( n.Value ) ) )
{
var newNote = new Note();
newNote.CreatedByPersonAliasId = ImportPersonAlias.Id;
newNote.CreatedDateTime = importDate;
newNote.EntityId = person.Id;
newNote.Text = notePair.Value;
newNote.NoteTypeId = noteTimelineTypeId;
newNote.Caption = string.Format( "{0} Note", notePair.Key );
if ( !notePair.Key.Equals( "General" ) )
{
newNote.IsAlert = true;
}
newNoteList.Add( newNote );
}
if ( newNoteList.Any() )
{
lookupContext.Notes.AddRange( newNoteList );
lookupContext.SaveChanges( true );
}
#endregion
var groupMember = new GroupMember();
groupMember.Person = person;
groupMember.GroupRoleId = groupRoleId;
groupMember.GroupMemberStatus = GroupMemberStatus.Active;
if ( isFamilyRelationship || currentFamilyGroup.Members.Count() < 1 )
{
currentFamilyGroup.Members.Add( groupMember );
newFamilyList.Add( currentFamilyGroup );
completed++;
}
else
{
var visitorGroup = new Group();
visitorGroup.ForeignId = rowFamilyId.ToString();
visitorGroup.Members.Add( groupMember );
visitorGroup.GroupTypeId = FamilyGroupTypeId;
visitorGroup.Name = person.LastName + " Family";
newFamilyList.Add( visitorGroup );
completed++;
newVisitorList.Add( visitorGroup );
}
if ( completed % ( ReportingNumber * 10 ) < 1 )
{
ReportProgress( 0, string.Format( "{0:N0} people imported.", completed ) );
}
else if ( completed % ReportingNumber < 1 )
{
SaveIndividuals( newFamilyList, newVisitorList );
ReportPartialProgress();
newFamilyList.Clear();
}
}
}
if ( newFamilyList.Any() )
{
SaveIndividuals( newFamilyList, newVisitorList );
}
ReportProgress( 0, string.Format( "Finished individual import: {0:N0} people imported.", completed ) );
return completed;
}