/// <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;
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( (RockContext)serviceInstance.Context ).GetByGuids( accountGuids ).Select( a => a.Id ).ToList();
}
bool combineGiving = false;
if ( selectionValues.Length >= 6 )
{
combineGiving = selectionValues[5].AsBooleanOrNull() ?? false;
}
int transactionTypeContributionId = Rock.Web.Cache.DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid() ).Id;
var financialTransactionQry = new FinancialTransactionService( rockContext ).Queryable()
.Where( xx => xx.AuthorizedPersonAliasId.HasValue )
.Where( xx => xx.TransactionTypeValueId == transactionTypeContributionId );
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();
// Create an explicit join to person alias so that rendered SQL is an INNER Join vs OUTER join
var personAliasQry = new PersonAliasService( rockContext ).Queryable();
var financialTransactionGivingGroupQry = financialTransactionQry
.Join( personAliasQry, t => t.AuthorizedPersonAliasId, p => p.Id, ( t, p ) => new
{
Txn = t,
GivingGroupId = p.Person.GivingGroupId
} );
// query transactions for individuals.
// If CombineGiving, exclude people that are Giving Group, and we'll get those when we union with CombineGiving
var financialTransactionDetailsIndividualQry = financialTransactionGivingGroupQry.Where( a => !combineGiving || !a.GivingGroupId.HasValue).Select( a => a.Txn )
.GroupBy( xx => xx.AuthorizedPersonAlias.PersonId
).Select( xx =>
new
{
PersonId = xx.Key,
TotalAmount = xx.Sum( ss => ss.TransactionDetails.Where( td => !limitToAccounts || accountIdList.Contains( td.AccountId ) ).Sum( td => td.Amount ) )
} );
if ( comparisonType == ComparisonType.LessThan )
{
financialTransactionDetailsIndividualQry = financialTransactionDetailsIndividualQry.Where( xx => xx.TotalAmount < amount );
}
else if ( comparisonType == ComparisonType.EqualTo )
{
financialTransactionDetailsIndividualQry = financialTransactionDetailsIndividualQry.Where( xx => xx.TotalAmount == amount );
}
else if ( comparisonType == ComparisonType.GreaterThanOrEqualTo )
{
financialTransactionDetailsIndividualQry = financialTransactionDetailsIndividualQry.Where( xx => xx.TotalAmount >= amount );
}
var innerQryIndividual = financialTransactionDetailsIndividualQry.Select( xx => xx.PersonId ).AsQueryable();
IQueryable<int> qryTransactionPersonIds;
if ( combineGiving )
{
// if CombineGiving=true, do another query to total by GivingGroupId for people with GivingGroupId specified
var financialTransactionDetailsGivingGroupQry = financialTransactionGivingGroupQry.Where( a => a.GivingGroupId.HasValue )
.GroupBy( xx => new
{
xx.GivingGroupId
} ).Select( xx =>
new
{
GivingGroupId = xx.Key,
TotalAmount = xx.Sum( ss => ss.Txn.TransactionDetails.Where( td => !limitToAccounts || accountIdList.Contains( td.AccountId ) ).Sum( td => td.Amount ) )
} );
if ( comparisonType == ComparisonType.LessThan )
{
financialTransactionDetailsGivingGroupQry = financialTransactionDetailsGivingGroupQry.Where( xx => xx.TotalAmount < amount );
}
else if ( comparisonType == ComparisonType.EqualTo )
{
financialTransactionDetailsGivingGroupQry = financialTransactionDetailsGivingGroupQry.Where( xx => xx.TotalAmount == amount );
}
else if ( comparisonType == ComparisonType.GreaterThanOrEqualTo )
{
financialTransactionDetailsGivingGroupQry = financialTransactionDetailsGivingGroupQry.Where( xx => xx.TotalAmount >= amount );
}
var personService = new PersonService( rockContext );
IQueryable<int> innerQryGivingGroupPersons = personService.Queryable()
.Where( a => financialTransactionDetailsGivingGroupQry.Select( xx => xx.GivingGroupId ).AsQueryable().Any( gg => gg.GivingGroupId == a.GivingGroupId ) )
.Select( s => s.Id );
// include people that either give as individuals or are members of a giving group
qryTransactionPersonIds = innerQryIndividual.Union( innerQryGivingGroupPersons );
}
else
{
// don't factor in GivingGroupId. Only include people that are directly associated with the transaction
qryTransactionPersonIds = innerQryIndividual;
}
var qry = new PersonService( rockContext ).Queryable()
.Where( p => qryTransactionPersonIds.Any( xx => xx == p.Id ) );
Expression extractedFilterExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( qry, parameterExpression, "p" );
return extractedFilterExpression;
}