/// <summary> /// Processes the giving journeys. /// </summary> internal void UpdateGivingJourneyStages() { var givingAnalyticsSetting = GivingAutomationSettings.LoadGivingAutomationSettings(); var rockContext = new RockContext(); rockContext.Database.CommandTimeout = this.SqlCommandTimeout; var personService = new PersonService(rockContext); // Limit to only Business and Person type records. // Include deceased to cover transactions that could have occurred when they were not deceased // or transactions that are dated after they were marked deceased. var personQuery = personService.Queryable(new PersonService.PersonQueryOptions { IncludeDeceased = true, IncludeBusinesses = true, IncludePersons = true, IncludeNameless = false, IncludeRestUsers = false }); var personAliasService = new PersonAliasService(rockContext); var personAliasQuery = personAliasService.Queryable(); var financialTransactionService = new FinancialTransactionService(rockContext); var financialTransactionGivingAnalyticsQuery = financialTransactionService.GetGivingAutomationSourceTransactionQuery(); if (OnProgress != null) { string progressMessage = "Calculating journey classifications..."; OnProgress.Invoke(this, new ProgressEventArgs(progressMessage)); } /* Get Non-Giver GivingIds */ var nonGiverGivingIdsQuery = personQuery.Where(p => !financialTransactionGivingAnalyticsQuery.Any(ft => personAliasQuery.Any(pa => pa.Id == ft.AuthorizedPersonAliasId && pa.Person.GivingId == p.GivingId))); var nonGiverGivingIdsList = nonGiverGivingIdsQuery.Select(a => a.GivingId).Distinct().ToList(); /* Get TransactionDateList for each GivingId in the system */ var transactionDateTimes = financialTransactionGivingAnalyticsQuery.Select(a => new { GivingId = personAliasQuery.Where(pa => pa.Id == a.AuthorizedPersonAliasId).Select(pa => pa.Person.GivingId).FirstOrDefault(), a.TransactionDateTime }).Where(a => a.GivingId != null).ToList(); var transactionDateTimesByGivingId = transactionDateTimes .GroupBy(g => g.GivingId) .Select(s => new { GivingId = s.Key, TransactionDateTimeList = s.Select(x => x.TransactionDateTime).ToList() }).ToDictionary(k => k.GivingId, v => v.TransactionDateTimeList); List <AttributeCache> journeyStageAttributesList = new List <AttributeCache> { _currentJourneyStageAttribute, _previousJourneyStageAttribute, _journeyStageChangeDateAttribute }; if (journeyStageAttributesList.Any(a => a == null)) { throw new Exception("Journey Stage Attributes are not installed correctly."); } var journeyStageAttributeIds = journeyStageAttributesList.Where(a => a != null).Select(a => a.Id).ToList(); var personCurrentJourneyAttributeValues = new AttributeValueService(rockContext).Queryable() .WhereAttributeIds(journeyStageAttributeIds) .Where(av => av.EntityId.HasValue) .Join( personQuery.Where(x => !string.IsNullOrEmpty(x.GivingId)), av => av.EntityId.Value, p => p.Id, (av, p) => new { AttributeId = av.AttributeId, AttributeValue = av.Value, PersonGivingId = p.GivingId, PersonId = p.Id }) .GroupBy(a => a.PersonGivingId) .Select(a => new { GivingId = a.Key, AttributeValues = a.ToList() }).ToDictionary(k => k.GivingId, v => v.AttributeValues); var givingJourneySettings = givingAnalyticsSetting.GivingJourneySettings; var currentDate = RockDateTime.Today; var formerGiverGivingIds = new List <string>(); var lapsedGiverGivingIds = new List <string>(); var newGiverGivingIds = new List <string>(); var occasionalGiverGivingIds = new List <string>(); var consistentGiverGivingIds = new List <string>(); var noneOfTheAboveGiverGivingIds = new List <string>(); foreach (var givingIdTransactions in transactionDateTimesByGivingId) { var givingId = givingIdTransactions.Key; var transactionDateList = givingIdTransactions.Value.Where(a => a.HasValue).Select(a => a.Value).ToList(); GivingJourneyStage?givingIdGivingJourneyStage = GetGivingJourneyStage(givingJourneySettings, currentDate, transactionDateList); switch (givingIdGivingJourneyStage) { case GivingJourneyStage.Former: formerGiverGivingIds.Add(givingId); break; case GivingJourneyStage.Lapsed: lapsedGiverGivingIds.Add(givingId); break; case GivingJourneyStage.New: newGiverGivingIds.Add(givingId); break; case GivingJourneyStage.Occasional: occasionalGiverGivingIds.Add(givingId); break; case GivingJourneyStage.Consistent: consistentGiverGivingIds.Add(givingId); break; case GivingJourneyStage.None: // Shouldn't happen since we are only looking at people with transactions, and we have already // figured out the non-givers break; default: // if they are non of the above, then add them to the "none of the above" list noneOfTheAboveGiverGivingIds.Add(givingId); break; } } Debug.WriteLine($@" FormerGiverCount: {formerGiverGivingIds.Count} LapsedGiverCount: {lapsedGiverGivingIds.Count} NewGiverCount: {newGiverGivingIds.Count} OccasionalGiverCount: {occasionalGiverGivingIds.Count} ConsistentGiverCount: {consistentGiverGivingIds.Count} NonGiverCount: {nonGiverGivingIdsList.Count} NoneOfTheAboveCount: {noneOfTheAboveGiverGivingIds.Count} "); _attributeValuesByGivingIdAndPersonId = personCurrentJourneyAttributeValues .ToDictionary( k => k.Key, v => { var lookupByPersonId = v.Value .Select(s => new AttributeValueCache(s.AttributeId, s.PersonId, s.AttributeValue)) .GroupBy(g => g.EntityId.Value) .ToDictionary(k => k.Key, vv => vv.ToList()); return(lookupByPersonId); }); _personIdsByGivingId = personQuery.Where(x => !string.IsNullOrEmpty(x.GivingId)) .Select(a => new { a.GivingId, PersonId = a.Id }) .GroupBy(a => a.GivingId) .ToDictionary( k => k.Key, v => v.Select(p => p.PersonId).ToList()); UpdateJourneyStageAttributeValuesForGivingId(formerGiverGivingIds, GivingJourneyStage.Former); UpdateJourneyStageAttributeValuesForGivingId(lapsedGiverGivingIds, GivingJourneyStage.Lapsed); UpdateJourneyStageAttributeValuesForGivingId(newGiverGivingIds, GivingJourneyStage.New); UpdateJourneyStageAttributeValuesForGivingId(occasionalGiverGivingIds, GivingJourneyStage.Occasional); UpdateJourneyStageAttributeValuesForGivingId(consistentGiverGivingIds, GivingJourneyStage.Consistent); UpdateJourneyStageAttributeValuesForGivingId(nonGiverGivingIdsList, GivingJourneyStage.None); UpdateJourneyStageAttributeValuesForGivingId(noneOfTheAboveGiverGivingIds, null); }