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