/// <summary> /// Bind the data based on the configured category setting. /// </summary> private void BindData() { AttributeList = new List<int>(); string categoryGuid = GetAttributeValue( "Category" ); Guid guid = Guid.Empty; if ( Guid.TryParse( categoryGuid, out guid ) ) { var category = CategoryCache.Read( guid ); if ( category != null ) { if ( !string.IsNullOrWhiteSpace( category.IconCssClass ) ) { lCategoryName.Text = string.Format( "<i class='{0}'></i> {1}", category.IconCssClass, category.Name ); } else { lCategoryName.Text = category.Name; } var orderOverride = new List<int>(); GetAttributeValue( "AttributeOrder" ).SplitDelimitedValues().ToList().ForEach( a => orderOverride.Add( a.AsInteger() ) ); var orderedAttributeList = new AttributeService( new RockContext() ).GetByCategoryId( category.Id ) .OrderBy( a => a.Order ).ThenBy( a => a.Name ).ToList(); foreach ( int attributeId in orderOverride ) { var attribute = orderedAttributeList.FirstOrDefault( a => a.Id == attributeId ); if ( attribute != null && attribute.IsAuthorized( Authorization.VIEW, CurrentPerson ) ) { AttributeList.Add( attribute.Id ); } } foreach ( var attribute in orderedAttributeList.Where( a => !orderOverride.Contains( a.Id ) ) ) { if ( attribute.IsAuthorized( Authorization.VIEW, CurrentPerson ) ) { AttributeList.Add( attribute.Id ); } } } } CreateControls( true ); }
/// <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; int newPeople = 0; ReportProgress( 0, string.Format( "Starting Individual import ({0:N0} already exist).", ImportedPeopleKeys.Count() ) ); string[] row; row = csvData.Database.FirstOrDefault(); while ( row != 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 = ImportedFamilies.Any( g => g.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.NumberFormatted = PhoneNumber.FormattedNumber( currentNumber.CountryCode, currentNumber.Number ); 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 = lookupContext.DefinedValues.Any( s => s.DefinedTypeId == schoolDefinedType.Id && 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 = lookupContext.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 = ImportedFamilies.FirstOrDefault( g => g.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++; } } // look ahead 1 row string rowNextFamilyKey = "-1"; if ( (row = csvData.Database.FirstOrDefault()) != null ) { rowNextFamilyKey = row[FamilyId]; } newPeople++; completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} people imported.", completed ) ); } if ( newPeople >= ReportingNumber && rowNextFamilyKey != currentFamilyGroup.ForeignKey ) { SaveIndividuals( newFamilyList, newVisitorList, newNoteList ); lookupContext.SaveChanges(); ReportPartialProgress(); // Clear out variables currentFamilyGroup = new Group(); newFamilyList.Clear(); newVisitorList.Clear(); newNoteList.Clear(); newPeople = 0; } } else { row = csvData.Database.FirstOrDefault(); } } // Save any changes to new families if ( newFamilyList.Any() ) { SaveIndividuals( newFamilyList, newVisitorList, newNoteList ); } // Save any changes to existing families lookupContext.SaveChanges(); DetachAllInContext( lookupContext ); lookupContext.Dispose(); ReportProgress( 0, string.Format( "Finished individual import: {0:N0} families and {1:N0} people added.", newFamilies, completed ) ); return completed; }
/// <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 ); var dvService = new DefinedValueService( lookupContext ); var schoolList = new List<DefinedValue>(); var newSchool = new DefinedValue(); var existingSchoolLookUp = dvService.Queryable() .Where( dv => dv.DefinedTypeId == 34 ).ToList(); // 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(); string subStatus = row["SubStatus_Name"] as string; int attendeeId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE ) ).Id; if ( memberStatus == "member" ) { if ( subStatus == "Baptism" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Member > Baptism" ) .Select( dv => dv.Id ).FirstOrDefault(); // ReportProgress( 0, string.Format( "***BAPTISM ConnectionStatusValueId: {0}, {1}, {2}", person.ConnectionStatusValueId, memberStatus, subStatus ) ); } else if ( subStatus == "Connected" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Member > Connected" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( subStatus == "Transfer" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Member > Transfer" ) .Select( dv => dv.Id ).FirstOrDefault(); } else { person.ConnectionStatusValueId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER ) ).Id; person.RecordStatusValueId = recordStatusActiveId; //ReportProgress( 0, string.Format( "***MEMBER ELSE ConnectionStatusValueId: {0}, {1}, {2}", person.ConnectionStatusValueId, memberStatus, subStatus ) ); } } 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 if (memberStatus == "dropped") //Crossroads Related { if (subStatus == "Member") { person.RecordStatusValueId = recordStatusInactiveId; person.RecordStatusReasonValueId = recordStatusReasons.Where(dv => dv.Value == "Dropped Member") .Select(dv => dv.Id).FirstOrDefault(); person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Dropped > Member" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if (subStatus == "Non-Member") { person.RecordStatusValueId = recordStatusInactiveId; person.RecordStatusReasonValueId = recordStatusReasons.Where( dv => dv.Value == "Dropped Non-Member" ) .Select(dv => dv.Id).FirstOrDefault(); person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Dropped > Non-Member" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( subStatus == "Beliefs" ) { person.RecordStatusValueId = recordStatusInactiveId; person.RecordStatusReasonValueId = recordStatusReasons.Where( dv => dv.Value == "Beliefs" ) .Select( dv => dv.Id ).FirstOrDefault(); person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Dropped > Beliefs" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( subStatus == "Disagreement" ) { person.RecordStatusValueId = recordStatusInactiveId; person.RecordStatusReasonValueId = recordStatusReasons.Where( dv => dv.Value == "Disagreement" ) .Select( dv => dv.Id ).FirstOrDefault(); person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Dropped > Disagreement" ) .Select( dv => dv.Id ).FirstOrDefault(); } else { person.RecordStatusValueId = recordStatusInactiveId; person.RecordStatusReasonValueId = recordStatusReasons.Where(dv => dv.Value == "Dropped") .Select(dv => dv.Id).FirstOrDefault(); person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Dropped" ) .Select( dv => dv.Id ).FirstOrDefault(); } } else if ( memberStatus == "child of member" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Child of Member" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "connected" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Connected" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "1st time" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "1st Time" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "2nd time" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "2nd Time" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "angel tree recipient" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Angel Tree Recipient" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "attendee" ) { if ( subStatus == "Baptism" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Attendee > Baptism" ) .Select( dv => dv.Id ).FirstOrDefault(); } else { person.ConnectionStatusValueId = attendeeId; person.RecordStatusValueId = recordStatusActiveId; //person.RecordStatusValueId = recordStatusActiveId; //person.RecordStatusReasonValueId = recordStatusReasons.Where( dv => dv.Value == "Attendee" ) // .Select( dv => dv.Id ).FirstOrDefault(); } } else if ( memberStatus == "camp only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Camp Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "camp parent" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Camp Parent" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "contributor only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Contributor Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "divorce care only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Divorce Care Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "easter saturday 2014" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Easter Saturday 2014" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "evacuee" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Evacuee" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "event only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Event Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "event prospect" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Event Prospect" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "mdo only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "MDO Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "out of town" ) { if ( subStatus == "Away at College" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Out of Town > Away at College" ) .Select( dv => dv.Id ).FirstOrDefault(); } else { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Out of Town" ) .Select( dv => dv.Id ).FirstOrDefault(); } } else if ( memberStatus == "preschool playdate only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Preschool Playdate Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "sports only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Sports Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "supported missionary" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "Supported Missionary" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "vbs only" ) { person.RecordStatusValueId = recordStatusActiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "VBS Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } else if ( memberStatus == "inactive member" ) { person.RecordStatusValueId = recordStatusInactiveId; person.ConnectionStatusValueId = connectionStatusTypes.Where( dv => dv.Value == "VBS Only" ) .Select( dv => dv.Id ).FirstOrDefault(); } 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(); person.ConnectionStatusValueId = customConnectionType ?? attendeeId; person.RecordStatusValueId = recordStatusActiveId; // ReportProgress( 0, string.Format( "***ELSE ConnectionStatusValueId: {0}, {1}, {2}", person.ConnectionStatusValueId, memberStatus, subStatus ) ); } //ReportProgress( 0, string.Format( "ConnectionStatusValueId: {0}, {1}, {2}, [ {3} ]", person.ConnectionStatusValueId, memberStatus, subStatus, person.RecordStatusReasonValueId ) ); 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; var noteList = new List<Note>(); //Seeing if these will add to the timeline. var note = new Note(); note.ForeignId = string.Format( "{0}", individualId ); note.Text = status_comment; note.NoteTypeId = 1; noteList.Add( note ); if ( noteList.Any() ) { var rockContext = new RockContext(); rockContext.WrapTransaction( () => { rockContext.Configuration.AutoDetectChangesEnabled = false; rockContext.Notes.AddRange( noteList ); rockContext.SaveChanges( DisableAudit ); } ); } } // 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(), familyRole ); 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; if ( school != null ) { school = school.Trim(); var schoolNameInList = new DefinedValue(); if ( existingSchoolLookUp.FirstOrDefault( s => s.Value == school ) != null ) { schoolNameInList = existingSchoolLookUp.FirstOrDefault( s => s.Value == school ); } if ( ( !string.IsNullOrEmpty( schoolNameInList.Value ) ) ) { person.Attributes.Add( schoolAttribute.Key, schoolAttribute ); person.AttributeValues.Add( schoolAttribute.Key, new AttributeValue() { AttributeId = schoolAttribute.Id, Value = string.Format( "{0}", existingSchoolLookUp.FirstOrDefault( s => s.Value == school ).Guid /*schoolNameInList.Guid*/), } ); } //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 person. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> public void MapPerson( IQueryable<Row> tableData ) { 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; int memberStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER ) ).Id; int visitorStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR ) ).Id; int attendeeStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE ) ).Id; // Record statuses/reasons: Active, Inactive, Pending, Deceased, etc var recordStatuses = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_RECORD_STATUS ) ).DefinedValues; var recordStatusReasons = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_RECORD_STATUS_REASON ), lookupContext ).DefinedValues; int recordStatusActiveId = recordStatuses.FirstOrDefault( r => r.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE ) ).Id; int recordStatusInactiveId = recordStatuses.FirstOrDefault( r => r.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE ) ).Id; int recordStatusPendingId = recordStatuses.FirstOrDefault( r => r.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_PENDING ) ).Id; int statusReasonDeceasedId = recordStatusReasons.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_REASON_DECEASED ) ).Id; int statusReasonNoActivityId = recordStatusReasons.Where( dv => dv.Value == "No Activity" ).Select( dv => dv.Id ).FirstOrDefault(); // 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; // 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 ).AsNoTracking().ToList(); // F1 attributes: IndividualId, HouseholdId // Core attributes: PreviousChurch, Position, Employer, School var previousChurchAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "PreviousChurch", StringComparison.InvariantCultureIgnoreCase ) ) ); var membershipDateAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "MembershipDate", StringComparison.InvariantCultureIgnoreCase ) ) ); var firstVisitAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "FirstVisit", StringComparison.InvariantCultureIgnoreCase ) ) ); var legalNoteAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "LegalNotes", StringComparison.InvariantCultureIgnoreCase ) ) ); var employerAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Employer", StringComparison.InvariantCultureIgnoreCase ) ) ); var positionAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Position", StringComparison.InvariantCultureIgnoreCase ) ) ); var schoolAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "School", StringComparison.InvariantCultureIgnoreCase ) ) ); var familyList = new List<Group>(); var visitorList = new List<Group>(); var previousNamesList = new Dictionary<Guid, string>(); var householdCampusList = new List<string>(); 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(); householdCampusList.Clear(); foreach ( var row in groupedRows.Where( r => r != null ) ) { var familyRoleId = FamilyRole.Adult; string currentCampus = string.Empty; int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; var personKeys = GetPersonKeys( individualId, householdId ); if ( personKeys == 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.IsDeceased = false; var DOB = row["Date_Of_Birth"] as DateTime?; if ( DOB != null ) { var birthDate = (DateTime)DOB; person.BirthDay = birthDate.Day; person.BirthMonth = birthDate.Month; person.BirthYear = birthDate.Year; } person.CreatedByPersonAliasId = ImportPersonAliasId; person.RecordTypeValueId = personRecordTypeId; person.ForeignKey = individualId.ToString(); person.ForeignId = individualId; 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"] as string; if ( familyRole != null ) { familyRole = familyRole.ToString().ToLower(); if ( familyRole == "visitor" ) { familyRoleId = FamilyRole.Visitor; } else if ( familyRole == "child" || person.Age < 18 ) { familyRoleId = FamilyRole.Child; } } string memberStatus = row["Status_Name"] as string; if ( memberStatus != null ) { memberStatus = memberStatus.ToLower(); if ( memberStatus.Equals( "member" ) ) { person.ConnectionStatusValueId = memberStatusId; person.RecordStatusValueId = recordStatusActiveId; } else if ( memberStatus.Equals( "visitor" ) ) { person.ConnectionStatusValueId = visitorStatusId; person.RecordStatusValueId = recordStatusActiveId; // F1 can designate visitors by member status or household position familyRoleId = FamilyRole.Visitor; } else if ( memberStatus.Equals( "deceased" ) ) { person.IsDeceased = true; person.RecordStatusReasonValueId = statusReasonDeceasedId; person.RecordStatusValueId = recordStatusInactiveId; } else if ( memberStatus.Equals( "dropped" ) || memberStatus.StartsWith( "inactive" ) ) { person.RecordStatusReasonValueId = statusReasonNoActivityId; person.RecordStatusValueId = recordStatusInactiveId; } else { // Lookup others that may be user-defined var customConnectionType = connectionStatusTypes.Where( dv => dv.Value == memberStatus ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); person.ConnectionStatusValueId = customConnectionType ?? attendeeStatusId; 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; } string previousName = row["Former_Name"] as string; if ( previousName != null ) { previousNamesList.Add( person.Guid, previousName ); } // set a processing flag to keep visitors from receiving household info person.ReviewReasonNote = familyRoleId.ToString(); // Map F1 attributes person.Attributes = new Dictionary<string, AttributeCache>(); person.AttributeValues = new Dictionary<string, AttributeValueCache>(); // IndividualId already defined in scope AddPersonAttribute( IndividualIdAttribute, person, individualId.ToString() ); // HouseholdId already defined in scope AddPersonAttribute( HouseholdIdAttribute, person, householdId.ToString() ); string previousChurch = row["Former_Church"] as string; if ( previousChurch != null ) { AddPersonAttribute( previousChurchAttribute, person, previousChurch ); } string employer = row["Employer"] as string; if ( employer != null ) { AddPersonAttribute( employerAttribute, person, employer ); } string position = row["Occupation_Name"] as string ?? row["Occupation_Description"] as string; if ( position != null ) { AddPersonAttribute( positionAttribute, person, position ); } string school = row["School_Name"] as string; if ( school != null ) { AddPersonAttribute( schoolAttribute, person, school ); } DateTime? firstVisit = row["First_Record"] as DateTime?; if ( firstVisit != null ) { person.CreatedDateTime = firstVisit; AddPersonAttribute( firstVisitAttribute, person, firstVisit.Value.ToString( "MM/dd/yyyy" ) ); } // Only import membership date if they are a member DateTime? membershipDate = row["Status_Date"] as DateTime?; if ( membershipDate != null && memberStatus.Contains( "member" ) ) { AddPersonAttribute( membershipDateAttribute, person, membershipDate.Value.ToString( "MM/dd/yyyy" ) ); } string checkinNote = row["Default_tag_comment"] as string; if ( checkinNote != null ) { AddPersonAttribute( legalNoteAttribute, person, checkinNote ); } var groupMember = new GroupMember(); groupMember.Person = person; groupMember.GroupRoleId = familyRoleId != FamilyRole.Child ? adultRoleId : childRoleId; groupMember.GroupMemberStatus = GroupMemberStatus.Active; if ( familyRoleId != FamilyRole.Visitor ) { householdCampusList.Add( currentCampus ); familyGroup.Members.Add( groupMember ); familyGroup.ForeignKey = householdId.ToString(); familyGroup.ForeignId = householdId; } else { var visitorGroup = new Group(); visitorGroup.Members.Add( groupMember ); visitorGroup.GroupTypeId = familyGroupTypeId; visitorGroup.ForeignKey = householdId.ToString(); visitorGroup.ForeignId = householdId; 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 ( !string.IsNullOrWhiteSpace( primaryHouseholdCampus ) ) { 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, previousNamesList, ownerRole, childRoleId, inviteeRoleId, invitedByRoleId, canCheckInRoleId, allowCheckInByRoleId ); familyList.Clear(); visitorList.Clear(); previousNamesList.Clear(); ReportPartialProgress(); } } } // Save any remaining families in the batch if ( familyList.Any() ) { SavePeople( familyList, visitorList, previousNamesList, ownerRole, childRoleId, inviteeRoleId, invitedByRoleId, canCheckInRoleId, allowCheckInByRoleId ); } ReportProgress( 100, string.Format( "Finished person import: {0:N0} people imported.", 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; }