/// <summary> /// Saves all family changes. /// </summary> private void SaveFamilies(List <Group> newFamilyList, Dictionary <GroupLocation, string> newGroupLocations) { var rockContext = new RockContext(); // First save any unsaved families if (newFamilyList.Any()) { rockContext.WrapTransaction(() => { rockContext.Groups.AddRange(newFamilyList); rockContext.SaveChanges(DisableAuditing); }); // Add these new families to the global list ImportedPeople.AddRange(newFamilyList); } // Now save locations if (newGroupLocations.Any()) { // Set updated family id on locations foreach (var locationPair in newGroupLocations) { int?familyGroupId = ImportedPeople.Where(g => g.ForeignKey == locationPair.Value).Select(g => (int?)g.Id).FirstOrDefault(); if (familyGroupId != null) { locationPair.Key.GroupId = (int)familyGroupId; } } // Save locations rockContext.WrapTransaction(() => { rockContext.Configuration.AutoDetectChangesEnabled = false; rockContext.GroupLocations.AddRange(newGroupLocations.Keys); rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges(DisableAuditing); }); } }
/// <summary> /// Saves the people. /// </summary> /// <param name="familyList">The family list.</param> /// <param name="visitorList">The visitor list.</param> /// <param name="previousNamesList">The previous names list.</param> private static void SavePeople(List <Group> familyList, List <Group> visitorList, Dictionary <Guid, string> previousNamesList) { var rockContext = new RockContext(); var groupMemberService = new GroupMemberService(rockContext); rockContext.WrapTransaction(() => { rockContext.Configuration.AutoDetectChangesEnabled = false; rockContext.Groups.AddRange(familyList); rockContext.SaveChanges(DisableAuditing); foreach (var familyGroups in familyList.GroupBy(g => g.ForeignId)) { var visitorsExist = familyGroups.Count() > 1; foreach (var newFamilyGroup in familyGroups) { foreach (var groupMember in newFamilyGroup.Members) { // don't call LoadAttributes, it only rewrites existing cache objects // groupMember.Person.LoadAttributes( rockContext ); var memberPersonAttributeValues = groupMember.Person.Attributes.Select(a => a.Value) .Select(a => new AttributeValue { AttributeId = a.Id, EntityId = groupMember.Person.Id, Value = groupMember.Person.AttributeValues[a.Key].Value }).ToList(); rockContext.AttributeValues.AddRange(memberPersonAttributeValues); // add a default person alias if (!groupMember.Person.Aliases.Any(a => a.AliasPersonId == groupMember.Person.Id)) { groupMember.Person.Aliases.Add(new PersonAlias { AliasPersonId = groupMember.Person.Id, AliasPersonGuid = groupMember.Person.Guid, ForeignId = groupMember.Person.ForeignId, ForeignKey = groupMember.Person.ForeignKey }); } // assign the previous name if (previousNamesList.Any(l => l.Key.Equals(groupMember.Person.Guid))) { var newPreviousName = new PersonPreviousName { LastName = previousNamesList[groupMember.Person.Guid], PersonAlias = groupMember.Person.Aliases.FirstOrDefault() }; rockContext.PersonPreviousNames.Add(newPreviousName); } // assign the giving group if (groupMember.GroupRoleId != FamilyChildRoleId) { groupMember.Person.GivingGroupId = newFamilyGroup.Id; } // Add known relationship group var knownGroupMember = new GroupMember { PersonId = groupMember.Person.Id, GroupRoleId = KnownRelationshipOwnerRoleId }; var knownRelationshipGroup = new Group { Name = KnownRelationshipGroupType.Name, GroupTypeId = KnownRelationshipGroupType.Id, IsPublic = true }; knownRelationshipGroup.Members.Add(knownGroupMember); rockContext.Groups.Add(knownRelationshipGroup); // Add implied relationship group var impliedGroupMember = new GroupMember { PersonId = groupMember.Person.Id, GroupRoleId = ImpliedRelationshipOwnerRoleId }; var impliedGroup = new Group { Name = ImpliedRelationshipGroupType.Name, GroupTypeId = ImpliedRelationshipGroupType.Id, IsPublic = true }; impliedGroup.Members.Add(impliedGroupMember); rockContext.Groups.Add(impliedGroup); if (visitorsExist) { // if this is a visitor, then add relationships to the family member(s) if (visitorList.Where(v => v.ForeignId == newFamilyGroup.ForeignId) .Any(v => v.Members.Any(m => m.Person.ForeignId.Equals(groupMember.Person.ForeignId)))) { var familyMembers = familyGroups.Except(visitorList).SelectMany(g => g.Members); foreach (var familyMember in familyMembers.Select(m => m.Person)) { var invitedByMember = new GroupMember { PersonId = familyMember.Id, GroupRoleId = InvitedByKnownRelationshipId }; knownRelationshipGroup.Members.Add(invitedByMember); if (groupMember.Person.Age < 18 && familyMember.Age > 18) { var allowCheckinMember = new GroupMember { PersonId = familyMember.Id, GroupRoleId = AllowCheckInByKnownRelationshipId }; knownRelationshipGroup.Members.Add(allowCheckinMember); } } } else { // not a visitor, add the visitors to the family member's known relationship var visitors = visitorList.Where(v => v.ForeignId == newFamilyGroup.ForeignId) .SelectMany(g => g.Members).ToList(); foreach (var visitor in visitors.Select(g => g.Person)) { var inviteeMember = new GroupMember { PersonId = visitor.Id, GroupRoleId = InviteeKnownRelationshipId }; knownRelationshipGroup.Members.Add(inviteeMember); // if visitor can be checked in and this person is considered an adult if (visitor.Age < 18 && groupMember.Person.Age > 18) { var canCheckInMember = new GroupMember { PersonId = visitor.Id, GroupRoleId = CanCheckInKnownRelationshipId }; knownRelationshipGroup.Members.Add(canCheckInMember); } } } } } } } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges(DisableAuditing); }); // end wrap transaction // add the new people to our tracking list if (familyList.Any()) { var familyMembers = familyList.SelectMany(gm => gm.Members); ImportedPeople.AddRange(familyMembers.Select(m => new PersonKeys { PersonAliasId = (int)m.Person.PrimaryAliasId, PersonId = m.Person.Id, PersonForeignId = m.Person.ForeignId, GroupForeignId = m.Group.ForeignId, FamilyRoleId = m.Person.ReviewReasonNote.ConvertToEnum <FamilyRole>() }).ToList() ); } if (visitorList.Any()) { var visitors = visitorList.SelectMany(gm => gm.Members); ImportedPeople.AddRange(visitors.Select(m => new PersonKeys { PersonAliasId = (int)m.Person.PrimaryAliasId, PersonId = m.Person.Id, PersonForeignId = m.Person.ForeignId, GroupForeignId = m.Group.ForeignId, FamilyRoleId = m.Person.ReviewReasonNote.ConvertToEnum <FamilyRole>() }).ToList() ); } }
/// <summary> /// Saves the companies. /// </summary> /// <param name="businessList">The business list.</param> private static void SaveCompanies(List <Group> businessList) { var rockContext = new RockContext(); rockContext.WrapTransaction(() => { rockContext.Configuration.AutoDetectChangesEnabled = false; rockContext.Groups.AddRange(businessList); rockContext.SaveChanges(DisableAuditing); foreach (var newBusiness in businessList) { foreach (var groupMember in newBusiness.Members) { // don't call LoadAttributes, it only rewrites existing cache objects // groupMember.Person.LoadAttributes( rockContext ); foreach (var attributeCache in groupMember.Person.Attributes.Select(a => a.Value)) { var existingValue = rockContext.AttributeValues.FirstOrDefault(v => v.Attribute.Key == attributeCache.Key && v.EntityId == groupMember.Person.Id); var newAttributeValue = groupMember.Person.AttributeValues[attributeCache.Key]; // set the new value and add it to the database if (existingValue == null) { existingValue = new AttributeValue { AttributeId = newAttributeValue.AttributeId, EntityId = groupMember.Person.Id, Value = newAttributeValue.Value }; rockContext.AttributeValues.Add(existingValue); } else { existingValue.Value = newAttributeValue.Value; rockContext.Entry(existingValue).State = EntityState.Modified; } } if (!groupMember.Person.Aliases.Any(a => a.AliasPersonId == groupMember.Person.Id)) { groupMember.Person.Aliases.Add(new PersonAlias { AliasPersonId = groupMember.Person.Id, AliasPersonGuid = groupMember.Person.Guid }); } groupMember.Person.GivingGroupId = newBusiness.Id; } } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges(DisableAuditing); if (businessList.Any()) { var groupMembers = businessList.SelectMany(gm => gm.Members); ImportedPeople.AddRange(groupMembers.Select(m => new PersonKeys { PersonAliasId = (int)m.Person.PrimaryAliasId, PersonId = m.Person.Id, PersonForeignId = null, GroupForeignId = m.Group.ForeignId, FamilyRoleId = FamilyRole.Adult }).ToList() ); } }); }
/// <summary> /// Loads the family data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadFamily(CSVInstance 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 currentFamilyKey = 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 rowFamilyKey = row[FamilyId]; int? rowFamilyId = rowFamilyKey.AsType <int?>(); string rowFamilyName = row[FamilyName]; if (rowFamilyKey != null && rowFamilyKey != currentFamilyGroup.ForeignKey) { currentFamilyGroup = ImportedPeople.FirstOrDefault(p => p.ForeignKey == rowFamilyKey); if (currentFamilyGroup == null) { currentFamilyGroup = new Group(); currentFamilyGroup.ForeignKey = rowFamilyKey; currentFamilyGroup.ForeignId = rowFamilyId; currentFamilyGroup.Name = row[FamilyName]; currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAliasId; 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(DisableAuditing); 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, verifyLocation: false); if (primaryAddress != null) { var primaryLocation = new GroupLocation(); primaryLocation.LocationId = primaryAddress.Id; primaryLocation.IsMailingLocation = true; primaryLocation.IsMappedLocation = true; primaryLocation.GroupLocationTypeValueId = homeLocationTypeId; newGroupLocations.Add(primaryLocation, rowFamilyKey); } 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, verifyLocation: false); if (secondaryAddress != null) { var secondaryLocation = new GroupLocation(); secondaryLocation.LocationId = secondaryAddress.Id; secondaryLocation.IsMailingLocation = true; secondaryLocation.IsMappedLocation = true; secondaryLocation.GroupLocationTypeValueId = workLocationTypeId; newGroupLocations.Add(secondaryLocation, rowFamilyKey); } DateTime createdDateValue; if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue)) { currentFamilyGroup.CreatedDateTime = createdDateValue; currentFamilyGroup.ModifiedDateTime = ImportDateTime; } else { currentFamilyGroup.CreatedDateTime = ImportDateTime; currentFamilyGroup.ModifiedDateTime = ImportDateTime; } 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> /// Saves the individuals. /// </summary> /// <param name="newFamilyList">The family list.</param> /// <param name="visitorList">The optional visitor list.</param> private void SaveIndividuals(List <Group> newFamilyList, List <Group> visitorList = null) { if (newFamilyList.Any()) { var rockContext = new RockContext(); rockContext.WrapTransaction(() => { rockContext.Groups.AddRange(newFamilyList); rockContext.SaveChanges(true); ImportedPeople.AddRange(newFamilyList); foreach (var familyGroups in newFamilyList.GroupBy <Group, string>(g => g.ForeignId)) { bool visitorsExist = visitorList.Any() && familyGroups.Count() > 1; foreach (var newFamilyGroup in familyGroups) { foreach (var person in newFamilyGroup.Members.Select(m => m.Person)) { foreach (var attributeCache in person.Attributes.Select(a => a.Value)) { var newAttributeValue = person.AttributeValues[attributeCache.Key]; if (newAttributeValue != null) { newAttributeValue.EntityId = person.Id; rockContext.AttributeValues.Add(newAttributeValue); } } if (!person.Aliases.Any(a => a.AliasPersonId == person.Id)) { person.Aliases.Add(new PersonAlias { AliasPersonId = person.Id, AliasPersonGuid = person.Guid }); } person.GivingGroupId = newFamilyGroup.Id; if (visitorsExist) { var groupTypeRoleService = new GroupTypeRoleService(rockContext); var ownerRole = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER)); int inviteeRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_INVITED)).Id; int invitedByRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_INVITED_BY)).Id; int canCheckInRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_CAN_CHECK_IN)).Id; int allowCheckInByRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_ALLOW_CHECK_IN_BY)).Id; // Retrieve or create the group this person is an owner of var ownerGroup = new GroupMemberService(rockContext).Queryable() .Where(m => m.PersonId == person.Id && m.GroupRoleId == ownerRole.Id) .Select(m => m.Group).FirstOrDefault(); if (ownerGroup == null) { var ownerGroupMember = new GroupMember(); ownerGroupMember.PersonId = person.Id; ownerGroupMember.GroupRoleId = ownerRole.Id; ownerGroup = new Group(); ownerGroup.Name = ownerRole.GroupType.Name; ownerGroup.GroupTypeId = ownerRole.GroupTypeId.Value; ownerGroup.Members.Add(ownerGroupMember); rockContext.Groups.Add(ownerGroup); } // Visitor, add relationships to the family members if (visitorList.Where(v => v.ForeignId == newFamilyGroup.ForeignId) .Any(v => v.Members.Any(m => m.Person.ForeignId.Equals(person.ForeignId)))) { var familyMembers = familyGroups.Except(visitorList).SelectMany(g => g.Members); foreach (var familyMember in familyMembers) { // Add visitor invitedBy relationship var invitedByMember = new GroupMember(); invitedByMember.PersonId = familyMember.Person.Id; invitedByMember.GroupRoleId = invitedByRoleId; ownerGroup.Members.Add(invitedByMember); if (person.Age < 18 && familyMember.Person.Age > 15) { // Add visitor allowCheckInBy relationship var allowCheckinMember = new GroupMember(); allowCheckinMember.PersonId = familyMember.Person.Id; allowCheckinMember.GroupRoleId = allowCheckInByRoleId; ownerGroup.Members.Add(allowCheckinMember); } } } else { // Family member, add relationships to the visitor(s) var familyVisitors = visitorList.Where(v => v.ForeignId == newFamilyGroup.ForeignId).SelectMany(g => g.Members).ToList(); foreach (var visitor in familyVisitors) { // Add invited visitor relationship var inviteeMember = new GroupMember(); inviteeMember.PersonId = visitor.Person.Id; inviteeMember.GroupRoleId = inviteeRoleId; ownerGroup.Members.Add(inviteeMember); if (visitor.Person.Age < 18 && person.Age > 15) { // Add canCheckIn visitor relationship var canCheckInMember = new GroupMember(); canCheckInMember.PersonId = visitor.Person.Id; canCheckInMember.GroupRoleId = canCheckInRoleId; ownerGroup.Members.Add(canCheckInMember); } } } } } } } rockContext.SaveChanges(true); }); } }
/// <summary> /// Maps the specified folder. /// </summary> /// <param name="folder">The folder.</param> /// <param name="personImageType">Type of the person image file.</param> public void Map(ZipArchive folder, BinaryFileType personImageType) { // check for existing images var lookupContext = new RockContext(); var existingImageList = new PersonService(lookupContext).Queryable().AsNoTracking() .Where(p => p.Photo != null) .ToDictionary(p => p.Id, p => p.Photo.CreatedDateTime); var emptyJsonObject = "{}"; var newFileList = new Dictionary <int, Rock.Model.BinaryFile>(); var storageProvider = personImageType.StorageEntityTypeId == DatabaseProvider.EntityType.Id ? (ProviderComponent)DatabaseProvider : (ProviderComponent)FileSystemProvider; var completedItems = 0; var totalEntries = folder.Entries.Count; var percentage = (totalEntries - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying person images import ({0:N0} found.", totalEntries)); foreach (var file in folder.Entries) { var fileExtension = Path.GetExtension(file.Name); if (FileTypeBlackList.Contains(fileExtension)) { LogException("Binary File Import", string.Format("{0} filetype not allowed ({1})", fileExtension, file.Name)); continue; } var personForeignId = Path.GetFileNameWithoutExtension(file.Name).AsType <int?>(); var personKeys = ImportedPeople.FirstOrDefault(p => p.PersonForeignId == personForeignId); if (personKeys != null) { // only import the most recent profile photo if (!existingImageList.ContainsKey(personKeys.PersonId) || existingImageList[personKeys.PersonId].Value < file.LastWriteTime.DateTime) { var rockFile = new Rock.Model.BinaryFile { IsSystem = false, IsTemporary = false, FileName = file.Name, BinaryFileTypeId = personImageType.Id, MimeType = GetMIMEType(file.Name), CreatedDateTime = file.LastWriteTime.DateTime, Description = string.Format("Imported as {0}", file.Name) }; rockFile.SetStorageEntityTypeId(personImageType.StorageEntityTypeId); rockFile.StorageEntitySettings = emptyJsonObject; if (personImageType.AttributeValues.Any()) { rockFile.StorageEntitySettings = personImageType.AttributeValues .ToDictionary(a => a.Key, v => v.Value.Value).ToJson(); } // use base stream instead of file stream to keep the byte[] // NOTE: if byte[] converts to a string it will corrupt the stream using (var fileContent = new StreamReader(file.Open())) { rockFile.ContentStream = new MemoryStream(fileContent.BaseStream.ReadBytesToEnd()); } newFileList.Add(personKeys.PersonId, rockFile); } completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, string.Format("{0:N0} person image files imported ({1}% complete).", completedItems, percentComplete)); } else if (completedItems % ReportingNumber < 1) { SaveFiles(newFileList, storageProvider); // add image keys to master list foreach (var newFile in newFileList) { existingImageList.AddOrReplace(newFile.Key, newFile.Value.CreatedDateTime); } // Reset batch list newFileList.Clear(); ReportPartialProgress(); } } } if (newFileList.Any()) { SaveFiles(newFileList, storageProvider); } lookupContext.Dispose(); ReportProgress(100, string.Format("Finished files import: {0:N0} person images imported.", completedItems)); }
/// <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 individual data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadIndividuals(CSVInstance 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; // Personal note type id var personalNoteTypeId = new NoteTypeService(lookupContext).Get(new Guid(Rock.SystemGuid.NoteType.PERSON_TIMELINE_NOTE)).Id; // School defined type var schoolDefinedType = DefinedTypeCache.Read(new Guid("576FF1E2-6225-4565-A16D-230E26167A3D")); // Look up existing Person attributes var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId).ToList(); var schoolAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "School")); // Text field type id int textFieldTypeId = FieldTypeCache.Read(new Guid(Rock.SystemGuid.FieldType.TEXT), lookupContext).Id; int dateFieldTypeId = FieldTypeCache.Read(new Guid(Rock.SystemGuid.FieldType.DATE), 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(); // Look for custom attributes in the Individual file var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList(); Dictionary <int, string> customAttributes = allFields .Where(f => f.index > SecurityNote) .ToDictionary(f => f.index, f => f.node.Name.RemoveWhitespace()); // Add any attributes 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.Key == 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(DisableAuditing); personAttributes.AddRange(newAttributes); } // Set the supported date formats var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" }; var currentFamilyGroup = new Group(); var newFamilyList = new List <Group>(); var newVisitorList = new List <Group>(); var newNoteList = new List <Note>(); int completed = 0; int newFamilies = 0; ReportProgress(0, string.Format("Starting Individual import ({0:N0} already exist).", ImportedPeople.Count(p => p.Members.Any(m => m.Person.ForeignKey != 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 rowFamilyName = row[FamilyName]; string rowFamilyKey = row[FamilyId]; string rowPersonKey = row[PersonId]; int? rowFamilyId = rowFamilyKey.AsType <int?>(); int? rowPersonId = rowPersonKey.AsType <int?>(); // Check that this person isn't already in our data var personExists = ImportedPeople.Any(p => p.Members.Any(m => m.Person.ForeignKey == rowPersonKey)); if (!personExists) { #region person create var person = new Person(); person.ForeignKey = rowPersonKey; person.ForeignId = rowPersonId; person.SystemNote = string.Format("Imported via Excavator on {0}", ImportDateTime); person.RecordTypeValueId = personRecordTypeId; person.CreatedByPersonAliasId = ImportPersonAliasId; string firstName = row[FirstName].Left(50); string nickName = row[NickName].Left(50); person.FirstName = firstName; person.NickName = string.IsNullOrWhiteSpace(nickName) ? firstName : nickName; person.MiddleName = row[MiddleName].Left(50); person.LastName = row[LastName].Left(50); DateTime createdDateValue; if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue)) { person.CreatedDateTime = createdDateValue; person.ModifiedDateTime = ImportDateTime; } else { person.CreatedDateTime = ImportDateTime; person.ModifiedDateTime = ImportDateTime; } DateTime birthDate; if (DateTime.TryParseExact(row[DateOfBirth], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out birthDate)) { person.BirthDay = birthDate.Day; person.BirthMonth = birthDate.Month; person.BirthYear = birthDate.Year; } DateTime graduationDate; if (DateTime.TryParseExact(row[GraduationDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out graduationDate)) { person.GraduationYear = graduationDate.Year; } 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 { // 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().ToLower()) { case "active": person.RecordStatusValueId = recordStatusActiveId; break; case "inactive": person.RecordStatusValueId = recordStatusInactiveId; break; default: person.RecordStatusValueId = recordStatusPendingId; break; } } string isDeceasedValue = row[IsDeceased]; if (!string.IsNullOrWhiteSpace(isDeceasedValue)) { switch (isDeceasedValue.Trim().ToLower()) { case "y": case "yes": person.IsDeceased = true; person.RecordStatusReasonValueId = recordStatusDeceasedId; person.RecordStatusValueId = recordStatusInactiveId; break; default: person.IsDeceased = false; 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().TrimStart(new Char[] { '0' }); 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 = ImportPersonAliasId; currentNumber.Extension = extension.Left(20); currentNumber.Number = normalizedNumber.TrimStart(new Char[] { '0' }).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, AttributeValueCache>(); 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; } person.EmailPreference = emailPreference; string primaryEmail = row[Email].Trim().Left(75); if (!string.IsNullOrWhiteSpace(primaryEmail)) { if (primaryEmail.IsEmail()) { person.Email = primaryEmail; person.IsEmailActive = isEmailActive; } else { LogException("InvalidPrimaryEmail", string.Format("PersonId: {0} - Email: {1}", rowPersonKey, primaryEmail)); } } 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().ToUpper()); } foreach (var attributePair in customAttributes) { string newAttributeValue = row[attributePair.Key]; if (!string.IsNullOrWhiteSpace(newAttributeValue)) { // check if this attribute value is a date DateTime valueAsDateTime; if (DateTime.TryParseExact(newAttributeValue, dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out valueAsDateTime)) { newAttributeValue = valueAsDateTime.ToString("yyyy-MM-dd"); } int?newAttributeId = personAttributes.Where(a => a.Key == attributePair.Value.RemoveWhitespace()) .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]); foreach (var notePair in notePairs.Where(n => !string.IsNullOrWhiteSpace(n.Value))) { var newNote = new Note(); newNote.NoteTypeId = personalNoteTypeId; newNote.CreatedByPersonAliasId = ImportPersonAliasId; newNote.CreatedDateTime = ImportDateTime; newNote.Text = notePair.Value; newNote.ForeignKey = rowPersonKey; newNote.ForeignId = rowPersonId; newNote.Caption = string.Format("{0} Note", notePair.Key); if (!notePair.Key.Equals("General")) { newNote.IsAlert = true; } newNoteList.Add(newNote); } #endregion person create var groupMember = new GroupMember(); groupMember.Person = person; groupMember.GroupRoleId = groupRoleId; groupMember.CreatedDateTime = ImportDateTime; groupMember.ModifiedDateTime = ImportDateTime; groupMember.CreatedByPersonAliasId = ImportPersonAliasId; groupMember.GroupMemberStatus = GroupMemberStatus.Active; if (rowFamilyKey != currentFamilyGroup.ForeignKey) { // person not part of the previous family, see if that family exists or create a new one currentFamilyGroup = ImportedPeople.FirstOrDefault(p => p.ForeignKey == rowFamilyKey); if (currentFamilyGroup == null) { currentFamilyGroup = CreateFamilyGroup(row[FamilyName], rowFamilyKey); newFamilyList.Add(currentFamilyGroup); newFamilies++; } else { lookupContext.Groups.Attach(currentFamilyGroup); lookupContext.Entry(currentFamilyGroup).State = EntityState.Modified; } currentFamilyGroup.Members.Add(groupMember); } else { // person is part of this family group, check if they're a visitor if (isFamilyRelationship || currentFamilyGroup.Members.Count() < 1) { currentFamilyGroup.Members.Add(groupMember); } else { var visitorFamily = CreateFamilyGroup(person.LastName + " Family", rowFamilyKey); visitorFamily.Members.Add(groupMember); newFamilyList.Add(visitorFamily); newVisitorList.Add(visitorFamily); newFamilies++; } } completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} people imported.", completed)); } else if (completed % ReportingNumber < 1) { SaveIndividuals(newFamilyList, newVisitorList, newNoteList); lookupContext.SaveChanges(); ReportPartialProgress(); // Clear out variables currentFamilyGroup = new Group(); newFamilyList.Clear(); newVisitorList.Clear(); newNoteList.Clear(); } } } // Save any changes to new families if (newFamilyList.Any()) { SaveIndividuals(newFamilyList, newVisitorList, newNoteList); } // Save any changes to existing families lookupContext.SaveChanges(); lookupContext.Dispose(); ReportProgress(0, string.Format("Finished individual import: {0:N0} families and {1:N0} people added.", newFamilies, completed)); return(completed); }
/// <summary> /// Maps the specified folder. /// </summary> /// <param name="folder">The folder.</param> /// <param name="ministryFileType">Type of the ministry file.</param> public int Map(ZipArchive folder, BinaryFileType ministryFileType) { var lookupContext = new RockContext(); var personEntityTypeId = EntityTypeCache.GetId <Person>(); var binaryFileTypeService = new BinaryFileTypeService(lookupContext); var fileFieldTypeId = FieldTypeCache.Get(Rock.SystemGuid.FieldType.FILE.AsGuid(), lookupContext).Id; var backgroundFieldTypeId = FieldTypeCache.Get(Rock.SystemGuid.FieldType.BACKGROUNDCHECK.AsGuid(), lookupContext).Id; var existingAttributes = new AttributeService(lookupContext).GetByFieldTypeId(fileFieldTypeId) .Where(a => a.EntityTypeId == personEntityTypeId) .ToDictionary(a => a.Key, a => a); var backgroundCheckFileAttributes = new AttributeService(lookupContext).GetByFieldTypeId(backgroundFieldTypeId) .Where(a => a.EntityTypeId == personEntityTypeId) .ToDictionary(a => a.Key, a => a); foreach (var backgroundCheckFileAttribute in backgroundCheckFileAttributes) { if (!existingAttributes.ContainsKey(backgroundCheckFileAttribute.Key)) { existingAttributes.Add(backgroundCheckFileAttribute.Key, backgroundCheckFileAttribute.Value); } } var emptyJsonObject = "{}"; var newFileList = new List <DocumentKeys>(); var completedItems = 0; var totalRows = folder.Entries.Count; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying ministry document import ({0:N0} found)", totalRows)); foreach (var file in folder.Entries.OrderBy(f => f.Name)) { var fileExtension = Path.GetExtension(file.Name); if (FileTypeBlackList.Contains(fileExtension)) { LogException("Binary File Import", string.Format("{0} filetype not allowed ({1})", fileExtension, file.Name)); continue; } var nameWithoutExtension = file.Name.ReplaceLastOccurrence(fileExtension, string.Empty); var parsedFileName = nameWithoutExtension.Split('_'); // Ministry docs should follow this pattern: // 0. Firstname // 1. Lastname // 2. ForeignId // 3. Filename // 4. Doc Id if (parsedFileName.Length < 3) { break; } var personForeignId = parsedFileName[2].AsType <int?>(); var personKeys = ImportedPeople.FirstOrDefault(p => p.PersonForeignId == personForeignId); if (personKeys != null) { var attributeName = string.Empty; var documentForeignId = string.Empty; if (parsedFileName.Count() > 4) { attributeName = parsedFileName[3]; documentForeignId = parsedFileName[4]; } else { var filename = parsedFileName[3].ReplaceLastOccurrence(fileExtension, string.Empty); attributeName = Regex.Replace(filename, "\\d{4,}[.\\w]+$", string.Empty); documentForeignId = Regex.Match(filename, "\\d+$").Value; } // append "Document" to attribute name to create unique attributes // this matches core attribute "Background Check Document" attributeName = !attributeName.EndsWith("Document", StringComparison.OrdinalIgnoreCase) ? string.Format("{0} Document", attributeName) : attributeName; var attributeKey = attributeName.RemoveSpecialCharacters(); Attribute fileAttribute = null; var attributeBinaryFileType = ministryFileType; if (!existingAttributes.ContainsKey(attributeKey)) { fileAttribute = new Attribute { FieldTypeId = fileFieldTypeId, EntityTypeId = personEntityTypeId, EntityTypeQualifierColumn = string.Empty, EntityTypeQualifierValue = string.Empty, Key = attributeKey, Name = attributeName, Description = string.Format("{0} created by binary file import", attributeName), IsGridColumn = false, IsMultiValue = false, IsRequired = false, AllowSearch = false, IsSystem = false, Order = 0 }; fileAttribute.AttributeQualifiers.Add(new AttributeQualifier() { Key = "binaryFileType", Value = ministryFileType.Guid.ToString() }); lookupContext.Attributes.Add(fileAttribute); lookupContext.SaveChanges(); existingAttributes.Add(fileAttribute.Key, fileAttribute); } else { // if attribute already exists in Rock, override default file type with the Rock-specified file type fileAttribute = existingAttributes[attributeKey]; var attributeBinaryFileTypeGuid = fileAttribute.AttributeQualifiers.FirstOrDefault(q => q.Key.Equals("binaryFileType")); if (attributeBinaryFileTypeGuid != null) { attributeBinaryFileType = binaryFileTypeService.Get(attributeBinaryFileTypeGuid.Value.AsGuid()); } } var rockFile = new Rock.Model.BinaryFile { IsSystem = false, IsTemporary = false, MimeType = GetMIMEType(file.Name), BinaryFileTypeId = attributeBinaryFileType.Id, FileName = file.Name, Description = string.Format("Imported as {0}", file.Name), CreatedDateTime = file.LastWriteTime.DateTime, ModifiedDateTime = file.LastWriteTime.DateTime, CreatedByPersonAliasId = ImportPersonAliasId, ForeignKey = documentForeignId, ForeignId = documentForeignId.AsIntegerOrNull() }; rockFile.SetStorageEntityTypeId(attributeBinaryFileType.StorageEntityTypeId); rockFile.StorageEntitySettings = emptyJsonObject; if (attributeBinaryFileType.AttributeValues != null) { rockFile.StorageEntitySettings = attributeBinaryFileType.AttributeValues .ToDictionary(a => a.Key, v => v.Value.Value).ToJson(); } // use base stream instead of file stream to keep the byte[] // NOTE: if byte[] converts to a string it will corrupt the stream using (var fileContent = new StreamReader(file.Open())) { rockFile.ContentStream = new MemoryStream(fileContent.BaseStream.ReadBytesToEnd()); } newFileList.Add(new DocumentKeys() { PersonId = personKeys.PersonId, AttributeId = fileAttribute.Id, File = rockFile }); completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, string.Format("{0:N0} ministry document files imported ({1}% complete).", completedItems, percentComplete)); } if (completedItems % ReportingNumber < 1) { SaveFiles(newFileList); // Reset list newFileList.Clear(); ReportPartialProgress(); } } } if (newFileList.Any()) { SaveFiles(newFileList); } ReportProgress(100, string.Format("Finished documents import: {0:N0} ministry documents imported.", completedItems)); return(completedItems); }
/// <summary> /// Maps the person. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapPerson(IQueryable <Row> tableData, List <string> selectedColumns = null) { var lookupContext = new RockContext(); var groupTypeRoleService = new GroupTypeRoleService(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; // Record status reasons: No Activity, Moved, Deceased, etc var recordStatusReasons = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_RECORD_STATUS_REASON), 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; // Record type: Person int?personRecordTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON), lookupContext).Id; // Suffix type: Dr., Jr., II, etc var suffixTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_SUFFIX)).DefinedValues; // Title type: Mr., Mrs. Dr., etc var titleTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_TITLE), lookupContext).DefinedValues; // Note type: Comment int noteCommentTypeId = new NoteTypeService(lookupContext).Get(new Guid("7E53487C-D650-4D85-97E2-350EB8332763")).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; int inviteeRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_INVITED)).Id; int invitedByRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_INVITED_BY)).Id; int canCheckInRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_CAN_CHECK_IN)).Id; int allowCheckInByRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_ALLOW_CHECK_IN_BY)).Id; // Group type: Family int familyGroupTypeId = new GroupTypeService(lookupContext).Get(new Guid(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY)).Id; // Look up additional Person attributes (existing) var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId).ToList(); // Cached F1 attributes: IndividualId, HouseholdId // Core attributes: PreviousChurch, Position, Employer, School var individualIdAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "F1IndividualId")); var householdIdAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "F1HouseholdId")); 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 familyList = new List <Group>(); var visitorList = new List <Group>(); int completed = 0; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying person import ({0:N0} found, {1:N0} already exist).", totalRows, ImportedPeople.Count())); foreach (var groupedRows in tableData.GroupBy <Row, int?>(r => r["Household_ID"] as int?)) { var familyGroup = new Group(); var householdCampusList = new List <string>(); foreach (var row in groupedRows) { bool isFamilyRelationship = true; string currentCampus = string.Empty; int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; if (GetPersonAliasId(individualId, householdId) == null) { var person = new Person(); person.FirstName = row["First_Name"] as string; person.MiddleName = row["Middle_Name"] as string; person.NickName = row["Goes_By"] as string ?? person.FirstName; person.LastName = row["Last_Name"] as string; person.BirthDate = row["Date_Of_Birth"] as DateTime?; person.CreatedByPersonAliasId = ImportPersonAlias.Id; person.RecordTypeValueId = personRecordTypeId; person.ForeignId = individualId.ToString(); int groupRoleId = adultRoleId; var gender = row["Gender"] as string; if (gender != null) { person.Gender = (Gender)Enum.Parse(typeof(Gender), gender); } string prefix = row["Prefix"] as string; if (prefix != null) { prefix = prefix.RemoveSpecialCharacters().Trim(); person.TitleValueId = titleTypes.Where(s => prefix == s.Value.RemoveSpecialCharacters()) .Select(s => (int?)s.Id).FirstOrDefault(); } string suffix = row["Suffix"] as string; if (suffix != null) { suffix = suffix.RemoveSpecialCharacters().Trim(); person.SuffixValueId = suffixTypes.Where(s => suffix == s.Value.RemoveSpecialCharacters()) .Select(s => (int?)s.Id).FirstOrDefault(); } string maritalStatus = row["Marital_Status"] as string; if (maritalStatus != null) { 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["Household_Position"].ToString().ToLower(); if (familyRole != null) { if (familyRole == "visitor") { isFamilyRelationship = false; } if (familyRole == "child" || person.Age < 18) { groupRoleId = childRoleId; } } string memberStatus = row["Status_Name"].ToString().ToLower(); if (memberStatus == "member") { person.ConnectionStatusValueId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER)).Id; person.RecordStatusValueId = recordStatusActiveId; } else if (memberStatus == "visitor") { person.ConnectionStatusValueId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR)).Id; person.RecordStatusValueId = recordStatusActiveId; } else if (memberStatus == "deceased") { person.IsDeceased = true; person.RecordStatusReasonValueId = recordStatusReasons.Where(dv => dv.Value == "Deceased") .Select(dv => dv.Id).FirstOrDefault(); person.RecordStatusValueId = recordStatusInactiveId; } else { // F1 defaults are Member & Visitor; all others are user-defined var customConnectionType = connectionStatusTypes.Where(dv => dv.Value == memberStatus) .Select(dv => (int?)dv.Id).FirstOrDefault(); int attendeeId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE)).Id; person.ConnectionStatusValueId = customConnectionType ?? attendeeId; person.RecordStatusValueId = recordStatusActiveId; } string campus = row["SubStatus_Name"] as string; if (campus != null) { currentCampus = campus; } string status_comment = row["Status_Comment"] as string; if (status_comment != null) { person.SystemNote = status_comment; } // Map F1 attributes person.Attributes = new Dictionary <string, AttributeCache>(); person.AttributeValues = new Dictionary <string, AttributeValue>(); // individual_id already defined in scope AddPersonAttribute(individualIdAttribute, person, individualId.ToString()); // household_id already defined in scope AddPersonAttribute(householdIdAttribute, person, householdId.ToString()); string previousChurch = row["Former_Church"] as string; AddPersonAttribute(previousChurchAttribute, person, previousChurch); string employer = row["Employer"] as string; AddPersonAttribute(employerAttribute, person, employer); string position = row["Occupation_Name"] as string ?? row["Occupation_Description"] as string; AddPersonAttribute(positionAttribute, person, position); string school = row["School_Name"] as string; AddPersonAttribute(schoolAttribute, person, school); DateTime?membershipDate = row["Status_Date"] as DateTime?; if (membershipDate != null) { person.CreatedDateTime = membershipDate; AddPersonAttribute(membershipDateAttribute, person, membershipDate.Value.ToString("MM/dd/yyyy")); } DateTime?firstVisit = row["First_Record"] as DateTime?; if (firstVisit != null) { person.CreatedDateTime = firstVisit; AddPersonAttribute(firstVisitAttribute, person, firstVisit.Value.ToString("MM/dd/yyyy")); } // Other Attributes to create: // former name // bar_code // member_env_code // denomination_name var groupMember = new GroupMember(); groupMember.Person = person; groupMember.GroupRoleId = groupRoleId; groupMember.GroupMemberStatus = GroupMemberStatus.Active; if (isFamilyRelationship) { householdCampusList.Add(currentCampus); familyGroup.Members.Add(groupMember); familyGroup.ForeignId = householdId.ToString(); } else { var visitorGroup = new Group(); visitorGroup.ForeignId = householdId.ToString(); visitorGroup.Members.Add(groupMember); visitorGroup.GroupTypeId = familyGroupTypeId; visitorGroup.Name = person.LastName + " Family"; visitorGroup.CampusId = CampusList.Where(c => c.Name.StartsWith(currentCampus) || c.ShortCode == currentCampus) .Select(c => (int?)c.Id).FirstOrDefault(); familyList.Add(visitorGroup); completed += visitorGroup.Members.Count; visitorList.Add(visitorGroup); } } } if (familyGroup.Members.Any()) { familyGroup.Name = familyGroup.Members.OrderByDescending(p => p.Person.Age) .FirstOrDefault().Person.LastName + " Family"; familyGroup.GroupTypeId = familyGroupTypeId; string primaryHouseholdCampus = householdCampusList.GroupBy(c => c).OrderByDescending(c => c.Count()) .Select(c => c.Key).FirstOrDefault(); if (primaryHouseholdCampus != null) { familyGroup.CampusId = CampusList.Where(c => c.Name.StartsWith(primaryHouseholdCampus) || c.ShortCode == primaryHouseholdCampus) .Select(c => (int?)c.Id).FirstOrDefault(); } familyList.Add(familyGroup); completed += familyGroup.Members.Count; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} people imported ({1}% complete).", completed, percentComplete)); } else if (completed % ReportingNumber < 1) { SavePeople(familyList, visitorList, ownerRole, childRoleId, inviteeRoleId, invitedByRoleId, canCheckInRoleId, allowCheckInByRoleId); familyList.Clear(); visitorList.Clear(); ReportPartialProgress(); } } } // Save any remaining families in the batch if (familyList.Any()) { SavePeople(familyList, visitorList, ownerRole, childRoleId, inviteeRoleId, invitedByRoleId, canCheckInRoleId, allowCheckInByRoleId); } ReportProgress(100, string.Format("Finished person import: {0:N0} people imported.", completed)); }
/// <summary> /// Maps the notes. /// </summary> /// <param name="tableData">The table data.</param> public void MapNotes(IQueryable <Row> tableData) { var lookupContext = new RockContext(); var categoryService = new CategoryService(lookupContext); var personService = new PersonService(lookupContext); var noteTypes = new NoteTypeService(lookupContext).Queryable().AsNoTracking().ToList(); var personalNoteType = noteTypes.FirstOrDefault(nt => nt.Guid == new Guid(Rock.SystemGuid.NoteType.PERSON_TIMELINE_NOTE)); var importedUsers = new UserLoginService(lookupContext).Queryable().AsNoTracking() .Where(u => u.ForeignId != null) .ToDictionary(t => t.ForeignId, t => t.PersonId); var noteList = new List <Note>(); int completed = 0; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying note import ({0:N0} found).", totalRows)); foreach (var row in tableData.Where(r => r != null)) { string text = row["Note_Text"] as string; int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; var noteTypeActive = row["NoteTypeActive"] as Boolean?; bool noteArchived = false; if (row.Columns.FirstOrDefault(v => v.Name.Equals("IsInactive")) != null) { /* ===================================================================== * the NoteArchived column *should* work, but OrcaMDF won't read it... * instead check for a manually added column: IsInactive int null * var noteActive = row["NoteArchived"] as Boolean?; * if ( noteActive == null ) throw new NullReferenceException(); * /* ===================================================================== */ var rowInactiveValue = row["IsInactive"] as int?; noteArchived = rowInactiveValue.Equals(1); } var personKeys = GetPersonKeys(individualId, householdId); if (personKeys != null && !string.IsNullOrWhiteSpace(text) && noteTypeActive == true && !noteArchived) { DateTime?dateCreated = row["NoteCreated"] as DateTime?; string noteType = row["Note_Type_Name"] as string; var note = new Note(); note.CreatedDateTime = dateCreated; note.EntityId = personKeys.PersonId; // These replace methods don't like being chained together text = Regex.Replace(text, @"\t|\ ", " "); text = text.Replace("-", "-"); text = text.Replace("<", "<"); text = text.Replace(">", ">"); text = text.Replace("&", "&"); text = text.Replace(""", @""""); text = text.Replace("
", string.Empty); note.Text = text.Trim(); int?userId = row["NoteCreatedByUserID"] as int?; if (userId != null && importedUsers.ContainsKey(userId)) { var userKeys = ImportedPeople.FirstOrDefault(p => p.PersonId == (int)importedUsers[userId]); if (userKeys != null) { note.CreatedByPersonAliasId = userKeys.PersonAliasId; } } int?matchingNoteTypeId = null; if (!noteType.StartsWith("General", StringComparison.InvariantCultureIgnoreCase)) { matchingNoteTypeId = noteTypes.Where(nt => nt.Name == noteType).Select(i => (int?)i.Id).FirstOrDefault(); } else { matchingNoteTypeId = personalNoteType.Id; } if (matchingNoteTypeId != null) { note.NoteTypeId = (int)matchingNoteTypeId; } else { // create the note type var newNoteType = new NoteType(); newNoteType.EntityTypeId = personalNoteType.EntityTypeId; newNoteType.EntityTypeQualifierColumn = string.Empty; newNoteType.EntityTypeQualifierValue = string.Empty; newNoteType.UserSelectable = true; newNoteType.IsSystem = false; newNoteType.Name = noteType; newNoteType.Order = 0; lookupContext.NoteTypes.Add(newNoteType); lookupContext.SaveChanges(DisableAuditing); noteTypes.Add(newNoteType); note.NoteTypeId = newNoteType.Id; } noteList.Add(note); completed++; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} notes imported ({1}% complete).", completed, percentComplete)); } else if (completed % ReportingNumber < 1) { SaveNotes(noteList); ReportPartialProgress(); noteList.Clear(); } } } if (noteList.Any()) { SaveNotes(noteList); } ReportProgress(100, string.Format("Finished note import: {0:N0} notes imported.", 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); }