/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override System.Linq.Expressions.Expression GetExpression(Data.RockContext context, System.Linq.Expressions.MemberExpression entityIdProperty, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length < 4)
{
return(null);
}
ComparisonType comparisonType = selectionValues[0].ConvertToEnum <ComparisonType>(ComparisonType.GreaterThanOrEqualTo);
decimal amount = selectionValues[1].AsDecimalOrNull() ?? 0.00M;
DateRange dateRange;
if (selectionValues.Length >= 7)
{
string slidingDelimitedValues = selectionValues[6].Replace(',', '|');
dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(slidingDelimitedValues);
}
else
{
// if converting from a previous version of the selection
DateTime?startDate = selectionValues[2].AsDateTime();
DateTime?endDate = selectionValues[3].AsDateTime();
dateRange = new DateRange(startDate, endDate);
if (dateRange.End.HasValue)
{
// the DateRange picker doesn't automatically add a full day to the end date
dateRange.End.Value.AddDays(1);
}
}
var accountIdList = new List <int>();
if (selectionValues.Length >= 5)
{
var accountGuids = selectionValues[4].Split(',').Select(a => a.AsGuid()).ToList();
accountIdList = new FinancialAccountService(context).GetByGuids(accountGuids).Select(a => a.Id).ToList();
}
bool combineGiving = false;
if (selectionValues.Length >= 6)
{
combineGiving = selectionValues[5].AsBooleanOrNull() ?? false;
}
bool useAnalytics = false;
if (selectionValues.Length >= 8)
{
useAnalytics = selectionValues[7].AsBooleanOrNull() ?? false;
}
int transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
IQueryable <decimal> personTotalAmountQry;
if (useAnalytics)
{
var financialTransactionQry = new AnalyticsSourceFinancialTransactionService(context).Queryable()
.Where(xx => xx.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.AccountId.HasValue && accountIdList.Contains(xx.AccountId.Value));
}
if (comparisonType == ComparisonType.LessThan)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount < amount);
}
else if (comparisonType == ComparisonType.EqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount == amount);
}
else if (comparisonType == ComparisonType.GreaterThanOrEqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount >= amount);
}
if (combineGiving)
{
var personAmount = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(financialTransactionQry, p => p.GivingId, f => f.GivingId, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => personAmount
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
else
{
var personAmount = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(financialTransactionQry, p => p.Id, f => f.AuthorizedPersonKey, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => personAmount
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
}
else
{
var financialTransactionQry = new FinancialTransactionDetailService(context).Queryable()
.Where(xx => xx.Transaction.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.Transaction.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
financialTransactionQry = financialTransactionQry.Where(xx => accountIdList.Contains(xx.AccountId));
}
if (comparisonType == ComparisonType.LessThan)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount < amount);
}
else if (comparisonType == ComparisonType.EqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount == amount);
}
else if (comparisonType == ComparisonType.GreaterThanOrEqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount >= amount);
}
if (combineGiving)
{
//// if combineGiving..
// if they aren't in a giving group, sum up transactions amounts by the person
// if they are in a giving group, sum up transactions amounts by the persons that are in the person's giving group
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => financialTransactionQry
.Where(ww =>
(!p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id)
||
(p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.Person.GivingGroupId == p.GivingGroupId))
.Sum(aa => aa.Amount));
}
else
{
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => financialTransactionQry
.Where(ww => ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id)
.Sum(aa => aa.Amount));
}
}
var selectExpression = SelectExpressionExtractor.Extract(personTotalAmountQry, entityIdProperty, "p");
return(selectExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override System.Linq.Expressions.Expression GetExpression(Data.RockContext context, System.Linq.Expressions.MemberExpression entityIdProperty, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length < 4)
{
return(null);
}
/* 2020-05-19 MDP
* The TotalAmount Comparison logic is that the displayed TotalAmount will show blank if the criteria doesn't match
* For example:
* Total Amount >= $100.00
* If a person's total giving is $100.01, $100.01 will be displayed as the total giving in the report
* If the person's total giving is $99.99, the total giving in the report will just show blank
*
*
* This display logic is done in the GetGridField method
*/
DateRange dateRange;
if (selectionValues.Length >= 7)
{
string slidingDelimitedValues = selectionValues[6].Replace(',', '|');
dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(slidingDelimitedValues);
}
else
{
// if converting from a previous version of the selection
DateTime?startDate = selectionValues[2].AsDateTime();
DateTime?endDate = selectionValues[3].AsDateTime();
dateRange = new DateRange(startDate, endDate);
if (dateRange.End.HasValue)
{
// the DateRange picker doesn't automatically add a full day to the end date
dateRange.End.Value.AddDays(1);
}
}
var accountIdList = new List <int>();
if (selectionValues.Length >= 5)
{
var accountGuids = selectionValues[4].Split(',').Select(a => a.AsGuid()).Where(a => a != Guid.Empty).ToList();
accountIdList = new FinancialAccountService(context).GetByGuids(accountGuids).Select(a => a.Id).ToList();
}
bool combineGiving = false;
if (selectionValues.Length >= 6)
{
combineGiving = selectionValues[5].AsBooleanOrNull() ?? false;
}
bool useAnalytics = false;
if (selectionValues.Length >= 8)
{
useAnalytics = selectionValues[7].AsBooleanOrNull() ?? false;
}
int transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
IQueryable <decimal> personTotalAmountQry;
if (useAnalytics)
{
/* 2020-05-20 MDP
* Analytics tables don't have a reference between a transaction and it's refund (unless we join the analytics tables to the TransactionRefund table).
* That isn't a problem unless the refund for a transaction is later than the specified date range.
* We discussed this and decided to not worry abou the late refund problem right now.
*
* Also, the total giving will be correct even when factoring in refunds
* because the Analytics tables will have a negative amount for refund transactions
*
*/
var analyticsFinancialTransactionQry = new AnalyticsSourceFinancialTransactionService(context).Queryable()
.Where(xx => xx.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
analyticsFinancialTransactionQry = analyticsFinancialTransactionQry.Where(xx => xx.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
analyticsFinancialTransactionQry = analyticsFinancialTransactionQry.Where(xx => xx.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
analyticsFinancialTransactionQry = analyticsFinancialTransactionQry.Where(xx => xx.AccountId.HasValue && accountIdList.Contains(xx.AccountId.Value));
}
if (combineGiving)
{
var analyticsPersonAmountQry = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(analyticsFinancialTransactionQry, p => p.GivingId, f => f.GivingId, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => analyticsPersonAmountQry
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
else
{
var analyticsPersonAmountQry = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(analyticsFinancialTransactionQry, p => p.Id, f => f.AuthorizedPersonKey, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => analyticsPersonAmountQry
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
}
else
{
var financialTransactionQry = new FinancialTransactionDetailService(context).Queryable()
.Where(xx => xx.Transaction.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.Transaction.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
financialTransactionQry = financialTransactionQry.Where(xx => accountIdList.Contains(xx.AccountId));
}
// exclude the financial transactions that were used for refunds.
// This is because we'll get the refund transactions of each non-refund transaction when getting the total amount
var refundsQry = new FinancialTransactionRefundService(context).Queryable();
financialTransactionQry = financialTransactionQry.Where(xx => !refundsQry.Any(r => r.Id == xx.TransactionId));
/* 2020-05-02 MDP
* To factor in Refunds, subtract (but actually add since the amount will be negative)
* the refund amount if there is a refund associated with that transaction.
*
* Also, don't apply a date filter on the refund since we want to factor in refunds
* that might have occurred after the date range
*
* The Linq is written in a way to avoid the RefundAmount getting queried twice (once if it is null and another if it not null)
*/
if (combineGiving)
{
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p =>
financialTransactionQry.Where(ww => p.GivingId == ww.Transaction.AuthorizedPersonAlias.Person.GivingId
)
.Select(x => new
{
x.Amount,
RefundAmount = (x.Transaction.RefundDetails.FinancialTransaction
.TransactionDetails
.Where(r => r.AccountId == x.AccountId)
.Sum(r => ( decimal? )r.Amount)
)
})
.Sum
(
aa => aa.Amount + (aa.RefundAmount ?? 0.00M)
)
);
}
else
{
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p =>
financialTransactionQry.Where(ww => ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id
)
.Select(x => new
{
x.Amount,
RefundAmount = (x.Transaction.RefundDetails.FinancialTransaction
.TransactionDetails
.Where(r => r.AccountId == x.AccountId)
.Sum(r => ( decimal? )r.Amount)
)
})
.Sum
(
aa => aa.Amount + (aa.RefundAmount ?? 0.00M)
)
);
}
}
var selectExpression = SelectExpressionExtractor.Extract(personTotalAmountQry, entityIdProperty, "p");
return(selectExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var rockContext = ( RockContext )serviceInstance.Context;
var selectionConfig = SelectionConfig.Parse(selection) ?? new SelectionConfig();
ComparisonType comparisonType = selectionConfig.ComparisonType;
decimal amount = selectionConfig.Amount ?? 0.00M;
DateRange dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(selectionConfig.SlidingDateRangePickerDelimitedValues);
var accountGuids = selectionConfig.AccountGuids;
List <int> accountIdList;
if (accountGuids != null && accountGuids.Any())
{
var financialAccountService = new FinancialAccountService(( RockContext )serviceInstance.Context);
accountIdList = financialAccountService.GetByGuids(accountGuids).Select(a => a.Id).ToList();
if (selectionConfig.IncludeChildAccounts)
{
var parentAccountIds = accountIdList.ToList();
foreach (var parentAccountId in parentAccountIds)
{
var descendantChildAccountIds = financialAccountService.GetAllDescendentIds(parentAccountId);
accountIdList.AddRange(descendantChildAccountIds);
}
}
}
else
{
accountIdList = new List <int>();
}
bool combineGiving = selectionConfig.CombineGiving;
int transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
bool useAnalyticsModels = selectionConfig.UseAnalyticsModels;
IQueryable <TransactionDetailData> financialTransactionDetailBaseQry;
if (useAnalyticsModels)
{
financialTransactionDetailBaseQry = new AnalyticsSourceFinancialTransactionService(rockContext).Queryable()
.Where(xx => xx.AuthorizedPersonAliasId.HasValue)
.Where(xx => xx.TransactionTypeValueId == transactionTypeContributionId)
.Select(ss => new TransactionDetailData
{
AuthorizedPersonAliasId = ss.AuthorizedPersonAliasId.Value,
TransactionDateTime = ss.TransactionDateTime,
Amount = ss.Amount,
AccountId = ss.AccountId ?? 0
});
}
else
{
financialTransactionDetailBaseQry = new FinancialTransactionDetailService(rockContext).Queryable()
.Where(xx => xx.Transaction.AuthorizedPersonAliasId.HasValue)
.Where(xx => xx.Transaction.TransactionDateTime.HasValue)
.Where(xx => xx.Transaction.TransactionTypeValueId == transactionTypeContributionId)
.Select(ss => new TransactionDetailData
{
AuthorizedPersonAliasId = ss.Transaction.AuthorizedPersonAliasId.Value,
TransactionDateTime = ss.Transaction.TransactionDateTime.Value,
Amount = ss.Amount,
AccountId = ss.AccountId
});
}
if (dateRange.Start.HasValue)
{
financialTransactionDetailBaseQry = financialTransactionDetailBaseQry.Where(xx => xx.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
financialTransactionDetailBaseQry = financialTransactionDetailBaseQry.Where(xx => xx.TransactionDateTime < dateRange.End.Value);
}
if (accountIdList.Any())
{
if (accountIdList.Count() == 1)
{
var accountId = accountIdList[0];
financialTransactionDetailBaseQry = financialTransactionDetailBaseQry.Where(x => accountId == x.AccountId);
}
else
{
financialTransactionDetailBaseQry = financialTransactionDetailBaseQry.Where(x => accountIdList.Contains(x.AccountId));
}
}
if (selectionConfig.IgnoreInactiveAccounts)
{
var inactiveAccountIdQuery = new FinancialAccountService(rockContext).Queryable().Where(a => !a.IsActive).Select(a => a.Id);
financialTransactionDetailBaseQry = financialTransactionDetailBaseQry.Where(a => !inactiveAccountIdQuery.Contains(a.AccountId));
}
bool excludePersonsWithTransactions = false;
// Create explicit joins to person alias and person tables so that rendered SQL has an INNER Joins vs OUTER joins on Person and PersonAlias
var personAliasQry = new PersonAliasService(rockContext).Queryable();
var personQryForJoin = new PersonService(rockContext).Queryable(true);
var financialTransactionDetailAmountQry = financialTransactionDetailBaseQry
.Join(
personAliasQry,
t => t.AuthorizedPersonAliasId,
pa => pa.Id,
(t, pa) => new { TransactionDetailData = t, PersonId = pa.PersonId })
.Join(
personQryForJoin,
j1 => j1.PersonId,
p => p.Id,
(j1, p) => new { Amount = j1.TransactionDetailData.Amount, Person = p });
IQueryable <GiverAmountInfo> financialTransactionGivingAmountQry;
if (combineGiving)
{
var financialTransactionGroupByQuery = financialTransactionDetailAmountQry.GroupBy(xx => xx.Person.GivingId);
financialTransactionGivingAmountQry = financialTransactionGroupByQuery
.Select(xx => new GiverAmountInfo {
GivingId = xx.Key, TotalAmount = xx.Sum(ss => ss.Amount)
});
}
else
{
var financialTransactionGroupByQuery = financialTransactionDetailAmountQry.GroupBy(xx => xx.Person.Id);
financialTransactionGivingAmountQry = financialTransactionGroupByQuery
.Select(xx => new GiverAmountInfo {
PersonId = xx.Key, TotalAmount = xx.Sum(ss => ss.Amount)
});
}
if (comparisonType == ComparisonType.LessThan)
{
// NOTE: Since we want people that have less than the specified, but also want to include people to didn't give anything at all (no transactions)
// make this query the same as the GreaterThan, but use it to EXCLUDE people that gave MORE than the specified amount. That
// way the filter will include people that had no transactions for the specified date/range and account
financialTransactionGivingAmountQry = financialTransactionGivingAmountQry.Where(xx => xx.TotalAmount >= amount);
excludePersonsWithTransactions = true;
}
else if (comparisonType == ComparisonType.EqualTo)
{
if (amount == 0.00M)
{
// NOTE: If we want to list people that gave $0.00 (they didn't giving anything)
// EXCLUDE people that gave any amount
excludePersonsWithTransactions = true;
}
else
{
financialTransactionGivingAmountQry = financialTransactionGivingAmountQry.Where(xx => xx.TotalAmount == amount);
}
}
else if (comparisonType == ComparisonType.GreaterThanOrEqualTo)
{
// NOTE: if the amount filter is 'they gave $0.00 or more', and doing a GreaterThanOrEqualTo, then we don't need to calculate and compare against TotalAmount
if (amount == 0.00M)
{
// no need to filter by amount if greater than or equal to $0.00
}
else
{
financialTransactionGivingAmountQry = financialTransactionGivingAmountQry.Where(xx => xx.TotalAmount >= amount);
}
}
IQueryable <Model.Person> qry;
if (combineGiving)
{
if (excludePersonsWithTransactions)
{
// the filter is for people that gave LESS than the specified amount, so return people that didn't give MORE than the specified amount
qry = new PersonService(rockContext).Queryable().Where(p => !financialTransactionGivingAmountQry.Any(xx => xx.GivingId == p.GivingId));
}
else
{
qry = new PersonService(rockContext).Queryable().Where(p => financialTransactionGivingAmountQry.Any(xx => xx.GivingId == p.GivingId));
}
}
else
{
if (excludePersonsWithTransactions)
{
// the filter is for people that gave LESS than the specified amount, so return people that didn't give MORE than the specified amount
qry = new PersonService(rockContext).Queryable().Where(p => !financialTransactionGivingAmountQry.Any(xx => xx.PersonId == p.Id));
}
else
{
qry = new PersonService(rockContext).Queryable().Where(p => financialTransactionGivingAmountQry.Any(xx => xx.PersonId == p.Id));
}
}
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
