/// <summary> /// Shows the detail. /// </summary> private void ShowDetail() { var transactionTypes = BindTransactionTypes(); BindAccounts(); // Load values from the system settings var givingAutomationSetting = GivingAutomationSettings.LoadGivingAutomationSettings(); var savedTransactionTypeGuids = givingAutomationSetting.TransactionTypeGuids ?? new List <Guid> { Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid() }; var savedTransactionTypeGuidStrings = savedTransactionTypeGuids.Select(g => g.ToString()) .Intersect(transactionTypes.Select(dv => dv.Guid.ToString())); var savedAccountGuids = givingAutomationSetting.FinancialAccountGuids ?? new List <Guid>(); var accounts = new List <FinancialAccount>(); var areChildAccountsIncluded = givingAutomationSetting.AreChildAccountsIncluded ?? false; if (savedAccountGuids.Any()) { using (var rockContext = new RockContext()) { var accountService = new FinancialAccountService(rockContext); accounts = accountService.Queryable() .AsNoTracking() .Where(a => savedAccountGuids.Contains(a.Guid)) .ToList(); } } // Sync the system setting values to the controls divAccounts.Visible = savedAccountGuids.Any(); apGivingAutomationAccounts.SetValues(accounts); rblAccountTypes.SetValue(savedAccountGuids.Any() ? AccountTypes_Custom : AccountTypes_AllTaxDeductible); cbGivingAutomationIncludeChildAccounts.Checked = areChildAccountsIncluded; cblTransactionTypes.SetValues(savedTransactionTypeGuidStrings); // Main Giving Automation Settings cbEnableGivingAutomation.Checked = givingAutomationSetting.GivingAutomationJobSettings.IsEnabled; dwpDaysToUpdateClassifications.SelectedDaysOfWeek = givingAutomationSetting.GivingClassificationSettings.RunDays?.ToList(); // Giving Journey Settings dwpDaysToUpdateGivingJourneys.SelectedDaysOfWeek = givingAutomationSetting.GivingJourneySettings.DaysToUpdateGivingJourneys?.ToList(); nbFormerGiverNoContributionInTheLastDays.IntegerValue = givingAutomationSetting.GivingJourneySettings.FormerGiverNoContributionInTheLastDays; nbFormerGiverMedianFrequencyLessThanDays.IntegerValue = givingAutomationSetting.GivingJourneySettings.FormerGiverMedianFrequencyLessThanDays; nbLapsedGiverNoContributionInTheLastDays.IntegerValue = givingAutomationSetting.GivingJourneySettings.LapsedGiverNoContributionInTheLastDays; nbLapsedGiverMedianFrequencyLessThanDays.IntegerValue = givingAutomationSetting.GivingJourneySettings.LapsedGiverMedianFrequencyLessThanDays; nreNewGiverContributionCountBetween.LowerValue = givingAutomationSetting.GivingJourneySettings.NewGiverContributionCountBetweenMinimum; nreNewGiverContributionCountBetween.UpperValue = givingAutomationSetting.GivingJourneySettings.NewGiverContributionCountBetweenMaximum; nbNewGiverFirstGiftInLastDays.IntegerValue = givingAutomationSetting.GivingJourneySettings.NewGiverFirstGiftInTheLastDays; nreOccasionalGiverMedianFrequencyDays.LowerValue = givingAutomationSetting.GivingJourneySettings.OccasionalGiverMedianFrequencyDaysMinimum; nreOccasionalGiverMedianFrequencyDays.UpperValue = givingAutomationSetting.GivingJourneySettings.OccasionalGiverMedianFrequencyDaysMaximum; nbConsistentGiverMedianLessThanDays.IntegerValue = givingAutomationSetting.GivingJourneySettings.ConsistentGiverMedianLessThanDays; nbGlobalRepeatPreventionDuration.Text = givingAutomationSetting.GivingAlertingSettings.GlobalRepeatPreventionDurationDays.ToStringSafe(); nbGratitudeRepeatPreventionDuration.Text = givingAutomationSetting.GivingAlertingSettings.GratitudeRepeatPreventionDurationDays.ToStringSafe(); nbFollowupRepeatPreventionDuration.Text = givingAutomationSetting.GivingAlertingSettings.FollowupRepeatPreventionDurationDays.ToStringSafe(); BindAlerts(); }
/// <summary> /// Handles the Click event of the btnSave control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param> protected void btnSave_Click(object sender, EventArgs e) { if (!Page.IsValid) { return; } var selectedGivingAutomationAccountIds = apGivingAutomationAccounts.SelectedIds; var selectedGivingAutomationAccountGuids = new List <Guid>(); if (selectedGivingAutomationAccountIds.Any()) { using (var rockContext = new RockContext()) { var accountService = new FinancialAccountService(rockContext); selectedGivingAutomationAccountGuids = accountService.Queryable() .AsNoTracking() .Where(a => selectedGivingAutomationAccountIds.Contains(a.Id)) .Select(a => a.Guid) .ToList(); } } var isCustomAccounts = rblAccountTypes.SelectedValue == AccountTypes_Custom; var givingAutomationSettings = GivingAutomationSettings.LoadGivingAutomationSettings(); givingAutomationSettings.FinancialAccountGuids = isCustomAccounts ? selectedGivingAutomationAccountGuids : null; givingAutomationSettings.AreChildAccountsIncluded = isCustomAccounts ? cbGivingAutomationIncludeChildAccounts.Checked : ( bool? )null; givingAutomationSettings.TransactionTypeGuids = cblTransactionTypes.SelectedValues.AsGuidList(); // Main Giving Automation Settings givingAutomationSettings.GivingAutomationJobSettings.IsEnabled = cbEnableGivingAutomation.Checked; givingAutomationSettings.GivingClassificationSettings.RunDays = dwpDaysToUpdateClassifications.SelectedDaysOfWeek?.ToArray(); // Giving Journey Settings givingAutomationSettings.GivingJourneySettings.DaysToUpdateGivingJourneys = dwpDaysToUpdateGivingJourneys.SelectedDaysOfWeek?.ToArray(); givingAutomationSettings.GivingJourneySettings.FormerGiverNoContributionInTheLastDays = nbFormerGiverNoContributionInTheLastDays.IntegerValue; givingAutomationSettings.GivingJourneySettings.FormerGiverMedianFrequencyLessThanDays = nbFormerGiverMedianFrequencyLessThanDays.IntegerValue; givingAutomationSettings.GivingJourneySettings.LapsedGiverNoContributionInTheLastDays = nbLapsedGiverNoContributionInTheLastDays.IntegerValue; givingAutomationSettings.GivingJourneySettings.LapsedGiverMedianFrequencyLessThanDays = nbLapsedGiverMedianFrequencyLessThanDays.IntegerValue; givingAutomationSettings.GivingJourneySettings.NewGiverContributionCountBetweenMinimum = ( int? )nreNewGiverContributionCountBetween.LowerValue; givingAutomationSettings.GivingJourneySettings.NewGiverContributionCountBetweenMaximum = ( int? )nreNewGiverContributionCountBetween.UpperValue; givingAutomationSettings.GivingJourneySettings.NewGiverFirstGiftInTheLastDays = nbNewGiverFirstGiftInLastDays.IntegerValue; givingAutomationSettings.GivingJourneySettings.OccasionalGiverMedianFrequencyDaysMinimum = ( int? )nreOccasionalGiverMedianFrequencyDays.LowerValue; givingAutomationSettings.GivingJourneySettings.OccasionalGiverMedianFrequencyDaysMaximum = ( int? )nreOccasionalGiverMedianFrequencyDays.UpperValue; givingAutomationSettings.GivingJourneySettings.ConsistentGiverMedianLessThanDays = nbConsistentGiverMedianLessThanDays.IntegerValue; // Alerting Settings givingAutomationSettings.GivingAlertingSettings.GlobalRepeatPreventionDurationDays = nbGlobalRepeatPreventionDuration.Text.AsIntegerOrNull(); givingAutomationSettings.GivingAlertingSettings.GratitudeRepeatPreventionDurationDays = nbGratitudeRepeatPreventionDuration.Text.AsIntegerOrNull(); givingAutomationSettings.GivingAlertingSettings.FollowupRepeatPreventionDurationDays = nbFollowupRepeatPreventionDuration.Text.AsIntegerOrNull(); GivingAutomationSettings.SaveGivingAutomationSettings(givingAutomationSettings); this.NavigateToParentPage(); }
/// <summary> /// Gets the giving automation source transaction query. /// This is used by <see cref="Rock.Jobs.GivingAutomation"/>. /// </summary> /// <returns></returns> public IQueryable <FinancialTransaction> GetGivingAutomationSourceTransactionQuery() { var query = Queryable().AsNoTracking(); var settings = GivingAutomationSettings.LoadGivingAutomationSettings(); // Filter by transaction type (defaults to contributions only) var transactionTypeIds = settings.TransactionTypeGuids.Select(DefinedValueCache.Get).Select(dv => dv.Id).ToList(); if (transactionTypeIds.Count() == 1) { var transactionTypeId = transactionTypeIds[0]; query = query.Where(t => t.TransactionTypeValueId == transactionTypeId); } else { query = query.Where(t => transactionTypeIds.Contains(t.TransactionTypeValueId)); } List <int> accountIds; if (settings.FinancialAccountGuids?.Any() == true) { accountIds = new FinancialAccountService(this.Context as RockContext).GetByGuids(settings.FinancialAccountGuids).Select(a => a.Id).ToList(); } else { accountIds = new List <int>(); } // Filter accounts, defaults to tax deductible only if (!accountIds.Any()) { query = query.Where(t => t.TransactionDetails.Any(td => td.Account.IsTaxDeductible)); } else if (settings.AreChildAccountsIncluded == true) { if (accountIds.Count() == 1) { var accountId = accountIds[0]; query = query.Where(t => t.TransactionDetails.Any(td => td.AccountId == accountId || (td.Account.ParentAccountId.HasValue && accountId == td.Account.ParentAccountId.Value))); } else { query = query.Where(t => t.TransactionDetails.Any(td => accountIds.Contains(td.AccountId) || (td.Account.ParentAccountId.HasValue && accountIds.Contains(td.Account.ParentAccountId.Value)))); } } else { if (accountIds.Count() == 1) { var accountId = accountIds[0]; query = query.Where(t => t.TransactionDetails.Any(td => accountId == td.AccountId)); } else { query = query.Where(t => t.TransactionDetails.Any(td => accountIds.Contains(td.AccountId))); } } // We'll need to factor in partial amount refunds... // Exclude transactions that have full refunds. // If it does have a refund, include the transaction if it is just a partial refund query = query.Where(t => // Limit to ones that don't have refunds, or has a partial refund !t.Refunds.Any() || // If it does have refunds, we can exclude the transaction if the refund amount is the same amount (full refund) // Otherwise, we'll have to include the transaction and figure out the partial amount left after the refund. ( ( // total original amount t.TransactionDetails.Sum(xx => xx.Amount) // total amount of any refund(s) for this transaction + t.Refunds.Sum(r => r.FinancialTransaction.TransactionDetails.Sum(d => ( decimal? )d.Amount) ?? 0.00M) ) != 0.00M ) ); // Remove transactions with $0 or negative amounts. If those are refunds, those will factored in above query = query.Where(t => t.TransactionDetails.Any(d => d.Amount > 0M)); return(query); }
/// <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); }
/// <summary> /// Gets the giving automation source transaction query. /// This is used by <see cref="Rock.Jobs.GivingAutomation"/>. /// </summary> /// <returns></returns> public IQueryable <FinancialTransaction> GetGivingAutomationSourceTransactionQuery() { var query = Queryable().AsNoTracking(); var settings = GivingAutomationSettings.LoadGivingAutomationSettings(); // Filter by transaction type (defaults to contributions only) var transactionTypeIds = settings.TransactionTypeGuids.Select(DefinedValueCache.Get).Select(dv => dv.Id).ToList(); if (transactionTypeIds.Count() == 1) { var transactionTypeId = transactionTypeIds[0]; query = query.Where(t => t.TransactionTypeValueId == transactionTypeId); } else { query = query.Where(t => transactionTypeIds.Contains(t.TransactionTypeValueId)); } List <int> accountIds; if (settings.FinancialAccountGuids?.Any() == true) { accountIds = new FinancialAccountService(this.Context as RockContext).GetByGuids(settings.FinancialAccountGuids).Select(a => a.Id).ToList(); } else { accountIds = new List <int>(); } // Filter accounts, defaults to tax deductible only if (!accountIds.Any()) { query = query.Where(t => t.TransactionDetails.Any(td => td.Account.IsTaxDeductible)); } else if (settings.AreChildAccountsIncluded == true) { if (accountIds.Count() == 1) { var accountId = accountIds[0]; query = query.Where(t => t.TransactionDetails.Any(td => td.AccountId == accountId || (td.Account.ParentAccountId.HasValue && accountId == td.Account.ParentAccountId.Value))); } else { query = query.Where(t => t.TransactionDetails.Any(td => accountIds.Contains(td.AccountId) || (td.Account.ParentAccountId.HasValue && accountIds.Contains(td.Account.ParentAccountId.Value)))); } } else { if (accountIds.Count() == 1) { var accountId = accountIds[0]; query = query.Where(t => t.TransactionDetails.Any(td => accountId == td.AccountId)); } else { query = query.Where(t => t.TransactionDetails.Any(td => accountIds.Contains(td.AccountId))); } } // Remove transactions that have refunds query = query.Where(t => !t.Refunds.Any()); // Remove transactions with $0 or negative amounts query = query.Where(t => t.TransactionDetails.Any(d => d.Amount > 0M)); return(query); }