/// <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 ) );
}
