/// <summary> /// Maps the communication data. /// </summary> /// <param name="tableData">The table data.</param> /// <returns></returns> private void MapCommunication( IQueryable<Row> tableData ) { var lookupContext = new RockContext(); var personService = new PersonService( lookupContext ); var attributeService = new AttributeService( lookupContext ); var definedTypePhoneType = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE ), lookupContext ); var otherNumberType = definedTypePhoneType.DefinedValues.Where( dv => dv.Value.StartsWith( "Other" ) ).Select( v => (int?)v.Id ).FirstOrDefault(); if ( otherNumberType == null ) { var otherType = new DefinedValue(); otherType.IsSystem = false; otherType.DefinedTypeId = definedTypePhoneType.Id; otherType.Order = 0; otherType.Value = "Other"; otherType.Description = "Imported from FellowshipOne"; otherType.CreatedByPersonAliasId = ImportPersonAliasId; lookupContext.DefinedValues.Add( otherType ); lookupContext.SaveChanges( DisableAuditing ); } // Look up existing Person attributes var personAttributes = attributeService.GetByEntityTypeId( PersonEntityTypeId ).ToList(); // Cached Rock attributes: Facebook, Twitter, Instagram var twitterAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Twitter", StringComparison.InvariantCultureIgnoreCase ) ) ); var facebookAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Facebook", StringComparison.InvariantCultureIgnoreCase ) ) ); var instagramAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Instagram", StringComparison.InvariantCultureIgnoreCase ) ) ); var newNumbers = new List<PhoneNumber>(); var existingNumbers = new PhoneNumberService( lookupContext ).Queryable().AsNoTracking().ToList(); var newPeopleAttributes = new Dictionary<int, Person>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying communication import ({0:N0} found, {1:N0} already exist).", totalRows, existingNumbers.Count ) ); foreach ( var groupedRows in tableData.OrderByDescending( r => r["LastUpdatedDate"] ).GroupBy<Row, int?>( r => r["Household_ID"] as int? ) ) { foreach ( var row in groupedRows.Where( r => r != null ) ) { string value = row["Communication_Value"] as string; int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; var peopleToUpdate = new List<PersonKeys>(); if ( individualId != null ) { var matchingPerson = GetPersonKeys( individualId, householdId, includeVisitors: false ); if ( matchingPerson != null ) { peopleToUpdate.Add( matchingPerson ); } } else { peopleToUpdate = GetFamilyByHouseholdId( householdId, includeVisitors: false ); } if ( peopleToUpdate.Any() && !string.IsNullOrWhiteSpace( value ) ) { DateTime? lastUpdated = row["LastUpdatedDate"] as DateTime?; string communicationComment = row["Communication_Comment"] as string; string type = row["Communication_Type"] as string; bool isListed = (bool)row["Listed"]; value = value.RemoveWhitespace(); // Communication value is a number if ( type.Contains( "Phone" ) || type.Contains( "Mobile" ) ) { var extension = string.Empty; var countryCode = PhoneNumber.DefaultCountryCode(); var normalizedNumber = string.Empty; var countryIndex = value.IndexOf( '+' ); int extensionIndex = value.LastIndexOf( 'x' ) > 0 ? value.LastIndexOf( 'x' ) : value.Length; if ( countryIndex >= 0 ) { countryCode = value.Substring( countryIndex, countryIndex + 3 ).AsNumeric(); normalizedNumber = value.Substring( countryIndex + 3, extensionIndex - 3 ).AsNumeric(); extension = value.Substring( extensionIndex ); } else if ( extensionIndex > 0 ) { normalizedNumber = value.Substring( 0, extensionIndex ).AsNumeric(); extension = value.Substring( extensionIndex ).AsNumeric(); } else { normalizedNumber = value.AsNumeric(); } if ( !string.IsNullOrWhiteSpace( normalizedNumber ) ) { foreach ( var personKeys in peopleToUpdate ) { bool numberExists = existingNumbers.Any( n => n.PersonId == personKeys.PersonId && n.Number.Equals( value ) ); if ( !numberExists ) { var newNumber = new PhoneNumber(); newNumber.CreatedByPersonAliasId = ImportPersonAliasId; newNumber.ModifiedDateTime = lastUpdated; newNumber.PersonId = (int)personKeys.PersonId; newNumber.IsMessagingEnabled = false; newNumber.CountryCode = countryCode; newNumber.IsUnlisted = !isListed; newNumber.Extension = extension.Left( 20 ) ?? string.Empty; newNumber.Number = normalizedNumber.Left( 20 ); newNumber.Description = communicationComment; newNumber.NumberFormatted = PhoneNumber.FormattedNumber( countryCode, newNumber.Number, true ); var matchingNumberType = definedTypePhoneType.DefinedValues.Where( v => type.StartsWith( v.Value ) ) .Select( v => (int?)v.Id ).FirstOrDefault(); newNumber.NumberTypeValueId = matchingNumberType ?? otherNumberType; newNumbers.Add( newNumber ); existingNumbers.Add( newNumber ); } } completed++; } } else { Person person = null; var personKeys = peopleToUpdate.FirstOrDefault(); if ( !newPeopleAttributes.ContainsKey( personKeys.PersonId ) ) { // not in dictionary, get person from database person = personService.Queryable( includeDeceased: true ).FirstOrDefault( p => p.Id == personKeys.PersonId ); } else { // reuse person from dictionary person = newPeopleAttributes[personKeys.PersonId]; } if ( person != null ) { if ( person.Attributes == null || person.AttributeValues == null ) { // make sure we have valid objects to assign to person.Attributes = new Dictionary<string, AttributeCache>(); person.AttributeValues = new Dictionary<string, AttributeValueCache>(); } // Check for an InFellowship ID/email before checking other types of email var isLoginValue = type.IndexOf( "InFellowship", StringComparison.OrdinalIgnoreCase ) >= 0; var personAlreadyHasLogin = person.Attributes.ContainsKey( InFellowshipLoginAttribute.Key ); if ( isLoginValue && !personAlreadyHasLogin ) { AddPersonAttribute( InFellowshipLoginAttribute, person, value ); AddUserLogin( AuthProviderEntityTypeId, person, value ); } else if ( value.IsEmail() ) { // person email is empty if ( string.IsNullOrWhiteSpace( person.Email ) ) { person.Email = value.Left( 75 ); person.IsEmailActive = isListed; person.EmailPreference = isListed ? EmailPreference.EmailAllowed : EmailPreference.DoNotEmail; person.ModifiedDateTime = lastUpdated; person.EmailNote = communicationComment; lookupContext.SaveChanges( DisableAuditing ); } // this is a different email, assign it to SecondaryEmail else if ( !person.Email.Equals( value ) && !person.Attributes.ContainsKey( SecondaryEmailAttribute.Key ) ) { AddPersonAttribute( SecondaryEmailAttribute, person, value ); } } else if ( type.Contains( "Twitter" ) && !person.Attributes.ContainsKey( twitterAttribute.Key ) ) { AddPersonAttribute( twitterAttribute, person, value ); } else if ( type.Contains( "Facebook" ) && !person.Attributes.ContainsKey( facebookAttribute.Key ) ) { AddPersonAttribute( facebookAttribute, person, value ); } else if ( type.Contains( "Instagram" ) && !person.Attributes.ContainsKey( instagramAttribute.Key ) ) { AddPersonAttribute( instagramAttribute, person, value ); } if ( !newPeopleAttributes.ContainsKey( personKeys.PersonId ) ) { newPeopleAttributes.Add( personKeys.PersonId, person ); } else { newPeopleAttributes[personKeys.PersonId] = person; } } completed++; } if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} records imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { if ( newNumbers.Any() || newPeopleAttributes.Any() ) { SaveCommunication( newNumbers, newPeopleAttributes ); } // reset so context doesn't bloat lookupContext = new RockContext(); personService = new PersonService( lookupContext ); newPeopleAttributes.Clear(); newNumbers.Clear(); ReportPartialProgress(); } } } } if ( newNumbers.Any() || newPeopleAttributes.Any() ) { SaveCommunication( newNumbers, newPeopleAttributes ); } ReportProgress( 100, string.Format( "Finished communication import: {0:N0} records imported.", completed ) ); }
/// <summary> /// Maps the communication data. /// </summary> /// <param name="tableData">The table data.</param> /// <returns></returns> private void MapCommunication( IQueryable<Row> tableData ) { var lookupContext = new RockContext(); var personService = new PersonService( lookupContext ); var attributeService = new AttributeService( lookupContext ); var numberTypeValues = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE ), lookupContext ).DefinedValues; // Look up additional Person attributes (existing) var personAttributes = attributeService.GetByEntityTypeId( PersonEntityTypeId ).ToList(); // Remove previously defined Excavator social attributes & categories if they exist var oldFacebookAttribute = personAttributes.Where( a => a.Key == "FacebookUsername" ).FirstOrDefault(); if ( oldFacebookAttribute != null ) { Rock.Web.Cache.AttributeCache.Flush( oldFacebookAttribute.Id ); attributeService.Delete( oldFacebookAttribute ); lookupContext.SaveChanges( true ); } var oldTwitterAttribute = personAttributes.Where( a => a.Key == "TwitterUsername" ).FirstOrDefault(); if ( oldTwitterAttribute != null ) { Rock.Web.Cache.AttributeCache.Flush( oldTwitterAttribute.Id ); attributeService.Delete( oldTwitterAttribute ); lookupContext.SaveChanges( true ); } int attributeEntityTypeId = EntityTypeCache.Read( "Rock.Model.Attribute" ).Id; var socialMediaCategory = new CategoryService( lookupContext ).GetByEntityTypeId( attributeEntityTypeId ) .Where( c => c.Name == "Social Media" && c.EntityTypeQualifierValue == PersonEntityTypeId.ToString() && c.IconCssClass == "fa fa-twitter" ) .FirstOrDefault(); if ( socialMediaCategory != null ) { lookupContext.Categories.Remove( socialMediaCategory ); lookupContext.SaveChanges( true ); } // Cached Rock attributes: Facebook, Twitter, Instagram var twitterAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Twitter" ) ); var facebookAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Facebook" ) ); var instagramAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "Instagram" ) ); var secondaryEmailAttribute = AttributeCache.Read( SecondaryEmailAttributeId ); var existingNumbers = new PhoneNumberService( lookupContext ).Queryable().ToList(); var newNumberList = new List<PhoneNumber>(); var updatedPersonList = new List<Person>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying communication import ({0:N0} found, {1:N0} already exist).", totalRows, existingNumbers.Count() ) ); foreach ( var row in tableData ) { string value = row["Communication_Value"] as string; int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; var personList = new List<int?>(); if ( individualId != null ) { int? personId = GetPersonAliasId( individualId, householdId ); if ( personId != null ) { personList.Add( personId ); } } else { List<int?> personIds = GetFamilyByHouseholdId( householdId ); if ( personIds.Any() ) { personList.AddRange( personIds ); } } if ( personList.Any() && !string.IsNullOrWhiteSpace( value ) ) { DateTime? lastUpdated = row["LastUpdatedDate"] as DateTime?; string communicationComment = row["Communication_Comment"] as string; string type = row["Communication_Type"] as string; bool isListed = (bool)row["Listed"]; value = value.RemoveWhitespace(); // Communication value is a number if ( type.Contains( "Phone" ) || type.Contains( "Mobile" ) ) { var extension = string.Empty; var countryCode = Rock.Model.PhoneNumber.DefaultCountryCode(); var normalizedNumber = string.Empty; var countryIndex = value.IndexOf( '+' ); int extensionIndex = value.LastIndexOf( 'x' ) > 0 ? value.LastIndexOf( 'x' ) : value.Length; if ( countryIndex >= 0 ) { countryCode = value.Substring( countryIndex, countryIndex + 3 ).AsNumeric(); normalizedNumber = value.Substring( countryIndex + 3, extensionIndex - 3 ).AsNumeric(); extension = value.Substring( extensionIndex ); } else if ( extensionIndex > 0 ) { normalizedNumber = value.Substring( 0, extensionIndex ).AsNumeric(); extension = value.Substring( extensionIndex ).AsNumeric(); } else { normalizedNumber = value.AsNumeric(); } if ( !string.IsNullOrWhiteSpace( normalizedNumber ) ) { foreach ( var familyPersonId in personList ) { bool numberExists = existingNumbers.Any( n => n.PersonId == familyPersonId && n.Number.Equals( value ) ); if ( !numberExists ) { var newNumber = new PhoneNumber(); newNumber.CreatedByPersonAliasId = ImportPersonAlias.Id; newNumber.ModifiedDateTime = lastUpdated; newNumber.PersonId = (int)familyPersonId; newNumber.IsMessagingEnabled = false; newNumber.CountryCode = countryCode; newNumber.IsUnlisted = !isListed; newNumber.Extension = extension.Left( 20 ); newNumber.Number = normalizedNumber.Left( 20 ); newNumber.Description = communicationComment; newNumber.NumberTypeValueId = numberTypeValues.Where( v => type.StartsWith( v.Value ) ) .Select( v => (int?)v.Id ).FirstOrDefault(); newNumberList.Add( newNumber ); existingNumbers.Add( newNumber ); } } completed++; } } else { var person = personService.Queryable( includeDeceased: true ).FirstOrDefault( p => p.Id == personList.FirstOrDefault() ); person.Attributes = new Dictionary<string, AttributeCache>(); person.AttributeValues = new Dictionary<string, AttributeValue>(); if ( value.IsValidEmail() ) { string secondaryEmail = string.Empty; if ( string.IsNullOrWhiteSpace( person.Email ) ) { secondaryEmail = person.Email; person.Email = value.Left( 75 ); person.IsEmailActive = isListed; person.ModifiedDateTime = lastUpdated; person.EmailNote = communicationComment; lookupContext.SaveChanges( true ); } else if ( !person.Email.Equals( value ) ) { secondaryEmail = value; } var existingSecondaryEmail = new AttributeValueService( lookupContext ).Queryable().Where( av => av.AttributeId == SecondaryEmailAttributeId && av.EntityId == person.Id ).FirstOrDefault(); if ( !string.IsNullOrWhiteSpace( secondaryEmail ) && existingSecondaryEmail == null ) { person.Attributes.Add( secondaryEmailAttribute.Key, secondaryEmailAttribute ); person.AttributeValues.Add( secondaryEmailAttribute.Key, new AttributeValue() { AttributeId = secondaryEmailAttribute.Id, Value = secondaryEmail } ); } } else if ( type.Contains( "Twitter" ) ) { person.Attributes.Add( twitterAttribute.Key, twitterAttribute ); person.AttributeValues.Add( twitterAttribute.Key, new AttributeValue() { AttributeId = twitterAttribute.Id, Value = value } ); } else if ( type.Contains( "Facebook" ) ) { var existingFacebook = new AttributeValueService( lookupContext ).Queryable().Where( av => av.AttributeId == facebookAttribute.Id && av.EntityId == person.Id ).FirstOrDefault(); if ( existingFacebook == null ) { person.Attributes.Add( facebookAttribute.Key, facebookAttribute ); person.AttributeValues.Add( facebookAttribute.Key, new AttributeValue() { AttributeId = facebookAttribute.Id, Value = value } ); } } else if ( type.Contains( "Instagram" ) ) { person.Attributes.Add( instagramAttribute.Key, instagramAttribute ); person.AttributeValues.Add( instagramAttribute.Key, new AttributeValue() { AttributeId = instagramAttribute.Id, Value = value } ); } updatedPersonList.Add( person ); completed++; } if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} records imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { SaveCommunication( newNumberList, updatedPersonList ); // reset so context doesn't bloat lookupContext = new RockContext(); personService = new PersonService( lookupContext ); updatedPersonList.Clear(); newNumberList.Clear(); ReportPartialProgress(); } } } if ( newNumberList.Any() || updatedPersonList.Any() ) { SaveCommunication( newNumberList, updatedPersonList ); } ReportProgress( 100, string.Format( "Finished communication import: {0:N0} records imported.", completed ) ); }