/// <summary> /// Maps the family address. /// </summary> /// <param name="tableData">The table data.</param> /// <returns></returns> private void MapFamilyAddress( IQueryable<Row> tableData ) { var locationService = new LocationService(); int groupEntityTypeId = EntityTypeCache.Read( "Rock.Model.Group" ).Id; List<DefinedValue> groupLocationTypeList = new DefinedValueService().Queryable().Where( dv => dv.DefinedType.Guid == new Guid( Rock.SystemGuid.DefinedType.GROUP_LOCATION_TYPE ) ).ToList(); List<GroupMember> groupMembershipList = new GroupMemberService().Queryable().Where( gm => gm.Group.GroupType.Guid == new Guid( Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY ) ).ToList(); int homeGroupLocationTypeId = groupLocationTypeList.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME ) ).Id; int workGroupLocationTypeId = groupLocationTypeList.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_WORK ) ).Id; int previousGroupLocationTypeId = groupLocationTypeList.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_PREVIOUS ) ).Id; var newGroupLocations = new List<GroupLocation>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Checking address import ({0:N0} found).", totalRows ) ); foreach ( var row in tableData ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? associatedPersonId = GetPersonId( individualId, householdId ); if ( associatedPersonId != null ) { var familyGroup = groupMembershipList.Where( gm => gm.PersonId == (int)associatedPersonId ) .Select( gm => gm.Group ).FirstOrDefault(); if ( familyGroup != null ) { var groupLocation = new GroupLocation(); string address = row["Address_1"] as string; string supplemental = row["Address_2"] as string; string city = row["City"] as string; string state = row["State"] as string; string country = row["country"] as string; // NOT A TYPO: F1 has property in lower-case string zip = row["Postal_Code"] as string; // Get new or existing location and associate it with group var familyAddress = locationService.Get( address, supplemental, city, state, zip ); familyAddress.CreatedByPersonAliasId = ImportPersonAlias.Id; familyAddress.Name = familyGroup.Name; familyAddress.IsActive = true; groupLocation.GroupId = familyGroup.Id; groupLocation.LocationId = familyAddress.Id; groupLocation.IsMailingLocation = true; groupLocation.IsMappedLocation = true; string addressType = row["Address_Type"] as string; if ( addressType.Equals( "Primary" ) ) { groupLocation.GroupLocationTypeValueId = homeGroupLocationTypeId; } else if ( addressType.Equals( "Business" ) || addressType.Equals( "Org" ) ) { groupLocation.GroupLocationTypeValueId = workGroupLocationTypeId; } else if ( addressType.Equals( "Previous" ) ) { groupLocation.GroupLocationTypeValueId = previousGroupLocationTypeId; } else if ( !string.IsNullOrEmpty( addressType ) ) { groupLocation.GroupLocationTypeValueId = groupLocationTypeList.Where( dv => dv.Name.Equals( addressType ) ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); } newGroupLocations.Add( groupLocation ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} addresses imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { RockTransactionScope.WrapTransaction( () => { var groupLocationService = new GroupLocationService(); groupLocationService.RockContext.GroupLocations.AddRange( newGroupLocations ); groupLocationService.RockContext.SaveChanges(); } ); ReportPartialProgress(); } } } } RockTransactionScope.WrapTransaction( () => { var groupLocationService = new GroupLocationService(); groupLocationService.RockContext.GroupLocations.AddRange( newGroupLocations ); groupLocationService.RockContext.SaveChanges(); } ); ReportProgress( 100, string.Format( "Finished address import: {0:N0} addresses imported.", completed ) ); }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapContribution( IQueryable<Row> tableData, List<string> selectedColumns = null ) { int transactionEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialTransaction" ).Id; var accountService = new FinancialAccountService(); var attributeService = new AttributeService(); var transactionTypeContributionId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION ) ).Id; int currencyTypeACH = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH ) ).Id; int currencyTypeCash = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH ) ).Id; int currencyTypeCheck = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK ) ).Id; int currencyTypeCreditCard = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD ) ).Id; List<DefinedValue> refundReasons = new DefinedValueService().Queryable().Where( dv => dv.DefinedType.Guid == new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON ) ).ToList(); List<FinancialPledge> pledgeList = new FinancialPledgeService().Queryable().ToList(); List<FinancialAccount> accountList = accountService.Queryable().ToList(); // Add an Attribute for the unique F1 Contribution Id int contributionAttributeId = attributeService.Queryable().Where( a => a.EntityTypeId == transactionEntityTypeId && a.Key == "F1ContributionId" ).Select( a => a.Id ).FirstOrDefault(); if ( contributionAttributeId == 0 ) { var newContributionAttribute = new Rock.Model.Attribute(); newContributionAttribute.Key = "F1ContributionId"; newContributionAttribute.Name = "F1 Contribution Id"; newContributionAttribute.FieldTypeId = IntegerFieldTypeId; newContributionAttribute.EntityTypeId = transactionEntityTypeId; newContributionAttribute.EntityTypeQualifierValue = string.Empty; newContributionAttribute.EntityTypeQualifierColumn = string.Empty; newContributionAttribute.Description = "The FellowshipOne identifier for the contribution that was imported"; newContributionAttribute.DefaultValue = string.Empty; newContributionAttribute.IsMultiValue = false; newContributionAttribute.IsRequired = false; newContributionAttribute.Order = 0; attributeService.Add( newContributionAttribute, ImportPersonAlias ); attributeService.Save( newContributionAttribute, ImportPersonAlias ); contributionAttributeId = newContributionAttribute.Id; } var contributionAttribute = AttributeCache.Read( contributionAttributeId ); // Get all imported contributions var importedContributions = new AttributeValueService().GetByAttributeId( contributionAttributeId ) .Select( av => new { ContributionId = av.Value.AsType<int?>(), TransactionId = av.EntityId } ) .ToDictionary( t => t.ContributionId, t => t.TransactionId ); // List for batching new contributions var newTransactions = new List<FinancialTransaction>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Checking contribution import ({0:N0} found, {1:N0} already exist).", totalRows, importedContributions.Count() ) ); foreach ( var row in tableData ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? contributionId = row["ContributionID"] as int?; if ( contributionId != null && !importedContributions.ContainsKey( contributionId ) ) { var transaction = new FinancialTransaction(); transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.AuthorizedPersonId = GetPersonId( individualId, householdId ); transaction.CreatedByPersonAliasId = ImportPersonAlias.Id; transaction.AuthorizedPersonId = GetPersonId( individualId, householdId ); string summary = row["Memo"] as string; if ( summary != null ) { transaction.Summary = summary; } int? batchId = row["BatchID"] as int?; if ( batchId != null && ImportedBatches.Any( b => b.Key == batchId ) ) { transaction.BatchId = ImportedBatches.FirstOrDefault( b => b.Key == batchId ).Value; } DateTime? receivedDate = row["Received_Date"] as DateTime?; if ( receivedDate != null ) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; } bool isTypeNonCash = false; string contributionType = row["Contribution_Type_Name"] as string; if ( contributionType != null ) { if ( contributionType == "ACH" ) { transaction.CurrencyTypeValueId = currencyTypeACH; } else if ( contributionType == "Cash" ) { transaction.CurrencyTypeValueId = currencyTypeCash; } else if ( contributionType == "Check" ) { transaction.CurrencyTypeValueId = currencyTypeCheck; } else if ( contributionType == "Credit Card" ) { transaction.CurrencyTypeValueId = currencyTypeCreditCard; } else { isTypeNonCash = true; } } string checkNumber = row["Check_Number"] as string; if ( checkNumber != null && checkNumber.AsType<int?>() != null ) { // routing & account set to zero transaction.CheckMicrEncrypted = Encryption.EncryptString( string.Format( "{0}_{1}_{2}", 0, 0, checkNumber ) ); } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; decimal? amount = row["Amount"] as decimal?; if ( fundName != null & amount != null ) { FinancialAccount matchingAccount = null; fundName = fundName.Trim(); int? fundCampusId = null; if ( subFund != null ) { subFund = subFund.Trim(); fundCampusId = CampusList.Where( c => c.Name.StartsWith( subFund ) || c.ShortCode == subFund ) .Select( c => (int?)c.Id ).FirstOrDefault(); if ( fundCampusId != null ) { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.CampusId != null && a.CampusId.Equals( fundCampusId ) ); } else { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.Name.StartsWith( subFund ) ); } } else { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.CampusId == null ); } if ( matchingAccount == null ) { matchingAccount = new FinancialAccount(); matchingAccount.Name = fundName; matchingAccount.PublicName = fundName; matchingAccount.IsTaxDeductible = true; matchingAccount.IsActive = true; matchingAccount.CampusId = fundCampusId; matchingAccount.CreatedByPersonAliasId = ImportPersonAlias.Id; accountService.Add( matchingAccount ); accountService.Save( matchingAccount ); accountList.Add( matchingAccount ); } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = matchingAccount.Id; transactionDetail.IsNonCash = isTypeNonCash; transaction.TransactionDetails.Add( transactionDetail ); if ( amount < 0 ) { var transactionRefund = new FinancialTransactionRefund(); transactionRefund.CreatedDateTime = receivedDate; transactionRefund.RefundReasonSummary = summary; transactionRefund.RefundReasonValueId = refundReasons.Where( dv => summary != null && dv.Name.Contains( summary ) ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); transaction.Refund = transactionRefund; } } // Other Attributes to create: // Pledge_Drive_Name // Stated_Value // True_Value // Liquidation_cost transaction.Attributes = new Dictionary<string, AttributeCache>(); transaction.AttributeValues = new Dictionary<string, List<AttributeValue>>(); transaction.Attributes.Add( contributionAttribute.Key, contributionAttribute ); transaction.AttributeValues.Add( contributionAttribute.Key, new List<AttributeValue>() ); transaction.AttributeValues[contributionAttribute.Key].Add( new AttributeValue() { AttributeId = contributionAttribute.Id, Value = contributionId.ToString(), Order = 0 } ); newTransactions.Add( transaction ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} contributions imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { RockTransactionScope.WrapTransaction( () => { var transactionService = new FinancialTransactionService(); transactionService.RockContext.FinancialTransactions.AddRange( newTransactions ); transactionService.RockContext.SaveChanges(); var attributeValueService = new AttributeValueService(); foreach ( var contribution in newTransactions.Where( c => c.Attributes.Any() ) ) { var attributeValue = contribution.AttributeValues[contributionAttribute.Key].FirstOrDefault(); if ( attributeValue != null ) { attributeValue.EntityId = contribution.Id; attributeValueService.RockContext.AttributeValues.Add( attributeValue ); } } attributeValueService.RockContext.SaveChanges(); } ); newTransactions.Clear(); ReportPartialProgress(); } } } if ( newTransactions.Any() ) { RockTransactionScope.WrapTransaction( () => { var transactionService = new FinancialTransactionService(); transactionService.RockContext.FinancialTransactions.AddRange( newTransactions ); transactionService.RockContext.SaveChanges(); var attributeValueService = new AttributeValueService(); foreach ( var contribution in newTransactions.Where( c => c.Attributes.Any() ) ) { var attributeValue = contribution.AttributeValues[contributionAttribute.Key].FirstOrDefault(); if ( attributeValue != null ) { attributeValue.EntityId = contribution.Id; attributeValueService.RockContext.AttributeValues.Add( attributeValue ); } } attributeValueService.RockContext.SaveChanges(); } ); } ReportProgress( 100, string.Format( "Finished contribution import: {0:N0} contributions imported.", completed ) ); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge( IQueryable<Row> tableData ) { var accountService = new FinancialAccountService(); List<FinancialAccount> importedAccounts = accountService.Queryable().ToList(); List<DefinedValue> pledgeFrequencies = new DefinedValueService().GetByDefinedTypeGuid( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY ) ).ToList(); List<FinancialPledge> importedPledges = new FinancialPledgeService().Queryable().ToList(); var newPledges = new List<FinancialPledge>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Checking pledge import ({0:N0} found).", totalRows ) ); foreach ( var row in tableData ) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime? startDate = row["Start_Date"] as DateTime?; DateTime? endDate = row["End_Date"] as DateTime?; if ( amount != null && startDate != null && endDate != null ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? personId = GetPersonId( individualId, householdId ); if ( personId != null && !importedPledges.Any( p => p.PersonId == personId && p.TotalAmount == amount && p.StartDate.Equals( startDate ) ) ) { var pledge = new FinancialPledge(); pledge.CreatedByPersonAliasId = ImportPersonAlias.Id; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row["Pledge_Frequency_Name"] as string; if ( frequency != null ) { if ( frequency == "One Time" || frequency == "As Can" ) { pledge.PledgeFrequencyValueId = pledgeFrequencies.FirstOrDefault( f => f.Guid == new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME ) ).Id; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where( f => f.Name.StartsWith( frequency ) || f.Description.StartsWith( frequency ) ) .Select( f => f.Id ).FirstOrDefault(); } } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; if ( fundName != null ) { FinancialAccount matchingAccount = null; int? fundCampusId = null; if ( subFund != null ) { // match by campus if the subfund appears to be a campus fundCampusId = CampusList.Where( c => c.Name.StartsWith( subFund ) || c.ShortCode == subFund ) .Select( c => (int?)c.Id ).FirstOrDefault(); if ( fundCampusId != null ) { matchingAccount = importedAccounts.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.CampusId != null && a.CampusId.Equals( fundCampusId ) ); } else { matchingAccount = importedAccounts.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.Name.StartsWith( subFund ) ); } } else { matchingAccount = importedAccounts.FirstOrDefault( a => a.Name.StartsWith( fundName ) ); } if ( matchingAccount == null ) { matchingAccount = new FinancialAccount(); matchingAccount.Name = fundName; matchingAccount.PublicName = fundName; matchingAccount.IsTaxDeductible = true; matchingAccount.IsActive = true; matchingAccount.CampusId = fundCampusId; matchingAccount.CreatedByPersonAliasId = ImportPersonAlias.Id; accountService.Add( matchingAccount ); accountService.Save( matchingAccount ); importedAccounts.Add( matchingAccount ); pledge.AccountId = matchingAccount.Id; } } // Attributes to add? // Pledge_Drive_Name newPledges.Add( pledge ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} pledges imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { RockTransactionScope.WrapTransaction( () => { var pledgeService = new FinancialPledgeService(); pledgeService.RockContext.FinancialPledges.AddRange( newPledges ); pledgeService.RockContext.SaveChanges(); } ); ReportPartialProgress(); } } } } if ( newPledges.Any() ) { RockTransactionScope.WrapTransaction( () => { var pledgeService = new FinancialPledgeService(); pledgeService.RockContext.FinancialPledges.AddRange( newPledges ); pledgeService.RockContext.SaveChanges(); } ); } ReportProgress( 100, string.Format( "Finished pledge import: {0:N0} pledges imported.", completed ) ); }
/// <summary> /// Maps the communication data. /// </summary> /// <param name="tableData">The table data.</param> /// <returns></returns> private void MapCommunication( IQueryable<Row> tableData ) { var categoryService = new CategoryService(); var personService = new PersonService(); List<DefinedValue> numberTypeValues = new DefinedValueService().Queryable() .Where( dv => dv.DefinedType.Guid == new Guid( Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE ) ).ToList(); // Add a Social Media category if it doesn't exist int attributeEntityTypeId = EntityTypeCache.Read( "Rock.Model.Attribute" ).Id; int socialMediaCategoryId = categoryService.Queryable().Where( c => c.EntityType.Id == attributeEntityTypeId && c.Name == "Social Media" ).Select( c => c.Id ).FirstOrDefault(); if ( socialMediaCategoryId == 0 ) { var socialMediaCategory = new Category(); socialMediaCategory.IsSystem = false; socialMediaCategory.Name = "Social Media"; socialMediaCategory.IconCssClass = "fa fa-twitter"; socialMediaCategory.EntityTypeId = attributeEntityTypeId; socialMediaCategory.EntityTypeQualifierColumn = "EntityTypeId"; socialMediaCategory.EntityTypeQualifierValue = PersonEntityTypeId.ToString(); socialMediaCategory.Order = 0; categoryService.Add( socialMediaCategory, ImportPersonAlias ); categoryService.Save( socialMediaCategory, ImportPersonAlias ); socialMediaCategoryId = socialMediaCategory.Id; } int visitInfoCategoryId = categoryService.Queryable().Where( c => c.EntityTypeId == attributeEntityTypeId && c.Name == "Visit Information" ).Select( c => c.Id ).FirstOrDefault(); // Look up additional Person attributes (existing) var personAttributes = new AttributeService().GetByEntityTypeId( PersonEntityTypeId ).ToList(); // Add an Attribute for the secondary email int secondaryEmailAttributeId = personAttributes.Where( a => a.Key == "SecondaryEmail" ).Select( a => a.Id ).FirstOrDefault(); if ( secondaryEmailAttributeId == 0 ) { var newSecondaryEmailAttribute = new Rock.Model.Attribute(); newSecondaryEmailAttribute.Key = "SecondaryEmail"; newSecondaryEmailAttribute.Name = "Secondary Email"; newSecondaryEmailAttribute.FieldTypeId = TextFieldTypeId; newSecondaryEmailAttribute.EntityTypeId = PersonEntityTypeId; newSecondaryEmailAttribute.EntityTypeQualifierValue = string.Empty; newSecondaryEmailAttribute.EntityTypeQualifierColumn = string.Empty; newSecondaryEmailAttribute.Description = "The secondary email for this person"; newSecondaryEmailAttribute.DefaultValue = string.Empty; newSecondaryEmailAttribute.IsMultiValue = false; newSecondaryEmailAttribute.IsRequired = false; newSecondaryEmailAttribute.Order = 0; using ( new UnitOfWorkScope() ) { var attributeService = new AttributeService(); attributeService.Add( newSecondaryEmailAttribute ); var visitInfoCategory = new CategoryService().Get( visitInfoCategoryId ); newSecondaryEmailAttribute.Categories.Add( visitInfoCategory ); attributeService.Save( newSecondaryEmailAttribute ); secondaryEmailAttributeId = newSecondaryEmailAttribute.Id; } } // Add an Attribute for Twitter int twitterAttributeId = personAttributes.Where( a => a.Key == "TwitterUsername" ).Select( a => a.Id ).FirstOrDefault(); if ( twitterAttributeId == 0 ) { var newTwitterAttribute = new Rock.Model.Attribute(); newTwitterAttribute.Key = "TwitterUsername"; newTwitterAttribute.Name = "Twitter Username"; newTwitterAttribute.FieldTypeId = TextFieldTypeId; newTwitterAttribute.EntityTypeId = PersonEntityTypeId; newTwitterAttribute.EntityTypeQualifierValue = string.Empty; newTwitterAttribute.EntityTypeQualifierColumn = string.Empty; newTwitterAttribute.Description = "The Twitter username (or link) for this person"; newTwitterAttribute.DefaultValue = string.Empty; newTwitterAttribute.IsMultiValue = false; newTwitterAttribute.IsRequired = false; newTwitterAttribute.Order = 0; using ( new UnitOfWorkScope() ) { var attributeService = new AttributeService(); attributeService.Add( newTwitterAttribute ); var socialMediaCategory = new CategoryService().Get( socialMediaCategoryId ); newTwitterAttribute.Categories.Add( socialMediaCategory ); attributeService.Save( newTwitterAttribute ); twitterAttributeId = newTwitterAttribute.Id; } } // Add an Attribute for Facebook var facebookAttributeId = personAttributes.Where( a => a.Key == "FacebookUsername" ).Select( a => a.Id ).FirstOrDefault(); if ( facebookAttributeId == 0 ) { var newFacebookAttribute = new Rock.Model.Attribute(); newFacebookAttribute.Key = "FacebookUsername"; newFacebookAttribute.Name = "Facebook Username"; newFacebookAttribute.FieldTypeId = TextFieldTypeId; newFacebookAttribute.EntityTypeId = PersonEntityTypeId; newFacebookAttribute.EntityTypeQualifierValue = string.Empty; newFacebookAttribute.EntityTypeQualifierColumn = string.Empty; newFacebookAttribute.Description = "The Facebook username (or link) for this person"; newFacebookAttribute.DefaultValue = string.Empty; newFacebookAttribute.IsMultiValue = false; newFacebookAttribute.IsRequired = false; newFacebookAttribute.Order = 0; using ( new UnitOfWorkScope() ) { var attributeService = new AttributeService(); attributeService.Add( newFacebookAttribute ); var socialMediaCategory = new CategoryService().Get( socialMediaCategoryId ); newFacebookAttribute.Categories.Add( socialMediaCategory ); attributeService.Save( newFacebookAttribute ); facebookAttributeId = newFacebookAttribute.Id; } } var secondaryEmailAttribute = AttributeCache.Read( secondaryEmailAttributeId ); var twitterUsernameAttribute = AttributeCache.Read( twitterAttributeId ); var facebookUsernameAttribute = AttributeCache.Read( facebookAttributeId ); var existingNumbers = new PhoneNumberService().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( "Checking communication import ({0:N0} found).", totalRows ) ); 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?; int? personId = GetPersonId( individualId, householdId ); if ( personId != null && !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"]; if ( type.Contains( "Phone" ) || type.Contains( "Mobile" ) ) { var extension = string.Empty; int extensionIndex = value.LastIndexOf( 'x' ); if ( extensionIndex > 0 ) { extension = value.Substring( extensionIndex ).AsNumeric(); value = value.Substring( 0, extensionIndex ).AsNumeric(); } else { value = value.AsNumeric(); } if ( !string.IsNullOrWhiteSpace( value ) ) { bool numberExists = existingNumbers.Any( n => n.PersonId == (int)personId && n.Number.Equals( value ) ); if ( !numberExists ) { var newNumber = new PhoneNumber(); newNumber.CreatedByPersonAliasId = ImportPersonAlias.Id; newNumber.ModifiedDateTime = lastUpdated; newNumber.PersonId = (int)personId; newNumber.IsMessagingEnabled = false; newNumber.IsUnlisted = !isListed; newNumber.Extension = extension.Left( 20 ); newNumber.Number = value.Left( 20 ); newNumber.Description = communicationComment; newNumber.NumberTypeValueId = numberTypeValues.Where( v => type.StartsWith( v.Name ) ) .Select( v => (int?)v.Id ).FirstOrDefault(); newNumberList.Add( newNumber ); completed++; } } } else { var person = personService.Get( (int)personId ); person.Attributes = new Dictionary<string, AttributeCache>(); person.AttributeValues = new Dictionary<string, List<AttributeValue>>(); if ( value.IsValidEmail() ) { string secondaryEmail = string.Empty; if ( string.IsNullOrWhiteSpace( person.Email ) || ( isListed && person.IsEmailActive == false ) ) { secondaryEmail = person.Email; person.Email = value.Left( 75 ); person.IsEmailActive = isListed; person.DoNotEmail = !isListed; person.ModifiedDateTime = lastUpdated; person.EmailNote = communicationComment; } else if ( !person.Email.Equals( value ) ) { secondaryEmail = value; } if ( !string.IsNullOrWhiteSpace( secondaryEmail ) ) { person.Attributes.Add( "SecondaryEmail", secondaryEmailAttribute ); person.AttributeValues.Add( "SecondaryEmail", new List<AttributeValue>() ); person.AttributeValues["SecondaryEmail"].Add( new AttributeValue() { AttributeId = secondaryEmailAttribute.Id, Value = secondaryEmail, Order = 0 } ); } } else if ( type.Contains( "Twitter" ) ) { person.Attributes.Add( "TwitterUsername", twitterUsernameAttribute ); person.AttributeValues.Add( "TwitterUsername", new List<AttributeValue>() ); person.AttributeValues["TwitterUsername"].Add( new AttributeValue() { AttributeId = twitterUsernameAttribute.Id, Value = value, Order = 0 } ); } else if ( type.Contains( "Facebook" ) ) { person.Attributes.Add( "FacebookUsername", facebookUsernameAttribute ); person.AttributeValues.Add( "FacebookUsername", new List<AttributeValue>() ); person.AttributeValues["FacebookUsername"].Add( new AttributeValue() { AttributeId = facebookUsernameAttribute.Id, Value = value, Order = 0 } ); } 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 ) { RockTransactionScope.WrapTransaction( () => { var numberService = new PhoneNumberService(); numberService.RockContext.PhoneNumbers.AddRange( newNumberList ); numberService.RockContext.SaveChanges(); // don't add updatedPeople, they're already tracked with current context personService.RockContext.SaveChanges(); var attributeValueService = new AttributeValueService(); foreach ( var updatedPerson in updatedPersonList.Where( p => p.Attributes.Any() ) ) { foreach ( var attributeCache in updatedPerson.Attributes.Select( a => a.Value ) ) { var newValue = updatedPerson.AttributeValues[attributeCache.Key].FirstOrDefault(); if ( newValue != null ) { newValue.EntityId = updatedPerson.Id; attributeValueService.RockContext.AttributeValues.Add( newValue ); } } } attributeValueService.RockContext.SaveChanges(); } ); // reset the person context so it doesn't bloat if ( updatedPersonList.Any() ) { personService = new PersonService(); updatedPersonList.Clear(); } newNumberList.Clear(); ReportPartialProgress(); } } } RockTransactionScope.WrapTransaction( () => { var numberService = new PhoneNumberService(); numberService.RockContext.PhoneNumbers.AddRange( newNumberList ); numberService.RockContext.SaveChanges(); personService.RockContext.SaveChanges(); var attributeValueService = new AttributeValueService(); foreach ( var updatedPerson in updatedPersonList.Where( p => p.Attributes.Any() ) ) { foreach ( var attributeCache in updatedPerson.Attributes.Select( a => a.Value ) ) { var newValue = updatedPerson.AttributeValues[attributeCache.Key].FirstOrDefault(); if ( newValue != null ) { newValue.EntityId = updatedPerson.Id; attributeValueService.RockContext.AttributeValues.Add( newValue ); } } } attributeValueService.RockContext.SaveChanges(); } ); ReportProgress( 100, string.Format( "Finished communication import: {0:N0} records imported.", completed ) ); }