/// <summary> /// Maps the RLC data to rooms, locations & classes /// </summary> /// <param name="tableData">The table data.</param> /// <returns></returns> private void MapRLC( IQueryable<Row> tableData ) { int locationEntityTypeId = EntityTypeCache.Read( "Rock.Model.Location" ).Id; int groupEntityTypeId = EntityTypeCache.Read( "Rock.Model.Group" ).Id; var attributeService = new AttributeService(); // Add an Attribute for the unique F1 RLC Id var rlcAttributeId = attributeService.Queryable().Where( a => a.EntityTypeId == locationEntityTypeId && a.Key == "F1RLCId" ).Select( a => a.Id ).FirstOrDefault(); if ( rlcAttributeId == 0 ) { var newRLCAttribute = new Rock.Model.Attribute(); newRLCAttribute.Key = "F1RLCId"; newRLCAttribute.Name = "F1 RLC Id"; newRLCAttribute.FieldTypeId = IntegerFieldTypeId; newRLCAttribute.EntityTypeId = locationEntityTypeId; newRLCAttribute.EntityTypeQualifierValue = string.Empty; newRLCAttribute.EntityTypeQualifierColumn = string.Empty; newRLCAttribute.Description = "The FellowshipOne identifier for the RLC (Room/Location/Class) that was imported"; newRLCAttribute.DefaultValue = string.Empty; newRLCAttribute.IsMultiValue = false; newRLCAttribute.IsRequired = false; newRLCAttribute.Order = 0; attributeService.Add( newRLCAttribute, ImportPersonAlias ); attributeService.Save( newRLCAttribute, ImportPersonAlias ); rlcAttributeId = newRLCAttribute.Id; } // Add an Attribute for the unique F1 Activity Id var activityAttributeId = attributeService.Queryable().Where( a => a.EntityTypeId == locationEntityTypeId && a.Key == "F1ActivityId" ).Select( a => a.Id ).FirstOrDefault(); if ( rlcAttributeId == 0 ) { var newActivityAttribute = new Rock.Model.Attribute(); newActivityAttribute.Key = "F1ActivityId"; newActivityAttribute.Name = "F1 Activity Id"; newActivityAttribute.FieldTypeId = IntegerFieldTypeId; newActivityAttribute.EntityTypeId = locationEntityTypeId; newActivityAttribute.EntityTypeQualifierValue = string.Empty; newActivityAttribute.EntityTypeQualifierColumn = string.Empty; newActivityAttribute.Description = "The FellowshipOne identifier for the activity that was imported"; newActivityAttribute.DefaultValue = string.Empty; newActivityAttribute.IsMultiValue = false; newActivityAttribute.IsRequired = false; newActivityAttribute.Order = 0; attributeService.Add( newActivityAttribute, ImportPersonAlias ); attributeService.Save( newActivityAttribute, ImportPersonAlias ); activityAttributeId = newActivityAttribute.Id; } var rlcAttribute = AttributeCache.Read( rlcAttributeId ); var activityAttribute = AttributeCache.Read( activityAttributeId ); // Get any previously imported RLCs var importedRLC = new AttributeValueService().GetByAttributeId( rlcAttributeId ) .Select( av => new { RLCId = av.Value.AsType<int?>(), LocationId = av.EntityId } ) .ToDictionary( t => t.RLCId, t => t.LocationId ); ImportedActivities = new AttributeValueService().GetByAttributeId( activityAttributeId ) .Select( av => new { ActivityId = av.Value.AsType<int?>(), GroupId = av.EntityId } ) .ToDictionary( t => t.ActivityId, t => t.GroupId ); foreach ( var row in tableData ) { int? rlcId = row["RLC_ID"] as int?; if ( rlcId != null && !importedRLC.ContainsKey( rlcId ) ) { // Activity_ID // RLC_Name // Activity_Group_ID // Start_Age_Date // End_Age_Date // Is_Active // Room_Code // Room_Desc // Room_Name // Max_Capacity // Building_Name } } }
/// <summary> /// Maps the activity ministry. /// </summary> /// <param name="tableData">The table data.</param> /// <returns></returns> private void MapActivityMinistry( IQueryable<Row> tableData ) { int groupEntityTypeId = EntityTypeCache.Read( "Rock.Model.Group" ).Id; var attributeService = new AttributeService(); // Add an Attribute for the unique F1 Ministry Id var ministryAttributeId = attributeService.Queryable().Where( a => a.EntityTypeId == groupEntityTypeId && a.Key == "F1MinistryId" ).Select( a => a.Id ).FirstOrDefault(); if ( ministryAttributeId == 0 ) { var newMinistryAttribute = new Rock.Model.Attribute(); newMinistryAttribute.Key = "F1MinistryId"; newMinistryAttribute.Name = "F1 Ministry Id"; newMinistryAttribute.FieldTypeId = IntegerFieldTypeId; newMinistryAttribute.EntityTypeId = groupEntityTypeId; newMinistryAttribute.EntityTypeQualifierValue = string.Empty; newMinistryAttribute.EntityTypeQualifierColumn = string.Empty; newMinistryAttribute.Description = "The FellowshipOne identifier for the ministry that was imported"; newMinistryAttribute.DefaultValue = string.Empty; newMinistryAttribute.IsMultiValue = false; newMinistryAttribute.IsRequired = false; newMinistryAttribute.Order = 0; attributeService.Add( newMinistryAttribute, ImportPersonAlias ); attributeService.Save( newMinistryAttribute, ImportPersonAlias ); ministryAttributeId = newMinistryAttribute.Id; } // Get previously imported Ministries var importedMinistries = new AttributeValueService().GetByAttributeId( ministryAttributeId ) .Select( av => new { RLCId = av.Value.AsType<int?>(), LocationId = av.EntityId } ) .ToDictionary( t => t.RLCId, t => t.LocationId ); foreach ( var row in tableData ) { int? ministryId = row["Ministry_ID"] as int?; if ( ministryId != null && !importedMinistries.ContainsKey( ministryId ) ) { // Activity_ID // Ministry_Name // Activity_Name // Ministry_Active // Activity_Active } } }
/// <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 ) ); }