/// <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 < 3 )
{
return null;
}
DateTime? startDate = selectionValues[0].AsDateTime();
DateTime? endDate = selectionValues[1].AsDateTime();
var accountGuids = selectionValues[2].Split( ',' ).Select( a => a.AsGuid() ).ToList();
var accountIdList = new FinancialAccountService( (RockContext)serviceInstance.Context ).GetByGuids( accountGuids ).Select( a => a.Id ).ToList();
int transactionTypeContributionId = Rock.Web.Cache.DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid() ).Id;
var financialTransactionsQry = new FinancialTransactionService( rockContext ).Queryable()
.Where( xx => xx.TransactionTypeValueId == transactionTypeContributionId );
if ( accountIdList.Any() )
{
if ( accountIdList.Count == 1 )
{
int accountId = accountIdList.First();
financialTransactionsQry = financialTransactionsQry.Where( xx => xx.TransactionDetails.Any( a => a.AccountId == accountId ) );
}
else
{
financialTransactionsQry = financialTransactionsQry.Where( xx => xx.TransactionDetails.Any( a => accountIdList.Contains( a.AccountId ) ) );
}
}
var firstContributionDateQry = financialTransactionsQry
.GroupBy( xx => xx.AuthorizedPersonAlias.PersonId )
.Select( ss => new
{
PersonId = ss.Key,
FirstTransactionDateTime = ss.Min( a => a.TransactionDateTime )
} );
if ( startDate.HasValue )
{
firstContributionDateQry = firstContributionDateQry.Where( xx => xx.FirstTransactionDateTime >= startDate.Value );
}
if ( endDate.HasValue )
{
firstContributionDateQry = firstContributionDateQry.Where( xx => xx.FirstTransactionDateTime < endDate );
}
var innerQry = firstContributionDateQry.Select( xx => xx.PersonId ).AsQueryable();
var qry = new PersonService( rockContext ).Queryable()
.Where( p => innerQry.Any( xx => xx == p.Id ) );
Expression extractedFilterExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( qry, parameterExpression, "p" );
return extractedFilterExpression;
}
/// <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 < 3 )
{
return null;
}
DateRange dateRange;
if ( selectionValues.Length >= 4 )
{
string slidingDelimitedValues = selectionValues[3].Replace( ',', '|' );
dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( slidingDelimitedValues );
}
else
{
// if converting from a previous version of the selection
DateTime? startDate = selectionValues[0].AsDateTime();
DateTime? endDate = selectionValues[1].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 accountGuids = selectionValues[2].Split( ',' ).Select( a => a.AsGuid() ).ToList();
var accountIdList = new FinancialAccountService( (RockContext)serviceInstance.Context ).GetByGuids( accountGuids ).Select( a => a.Id ).ToList();
int transactionTypeContributionId = Rock.Web.Cache.DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid() ).Id;
var financialTransactionsQry = new FinancialTransactionService( rockContext ).Queryable()
.Where( xx => xx.TransactionTypeValueId == transactionTypeContributionId );
if ( accountIdList.Any() )
{
if ( accountIdList.Count == 1 )
{
int accountId = accountIdList.First();
financialTransactionsQry = financialTransactionsQry.Where( xx => xx.TransactionDetails.Any( a => a.AccountId == accountId ) );
}
else
{
financialTransactionsQry = financialTransactionsQry.Where( xx => xx.TransactionDetails.Any( a => accountIdList.Contains( a.AccountId ) ) );
}
}
var firstContributionDateQry = financialTransactionsQry
.GroupBy( xx => xx.AuthorizedPersonAlias.PersonId )
.Select( ss => new
{
PersonId = ss.Key,
FirstTransactionSundayDate = ss.Min( a => a.SundayDate )
} );
if ( dateRange.Start.HasValue )
{
firstContributionDateQry = firstContributionDateQry.Where( xx => xx.FirstTransactionSundayDate >= dateRange.Start.Value );
}
if ( dateRange.End.HasValue )
{
firstContributionDateQry = firstContributionDateQry.Where( xx => xx.FirstTransactionSundayDate < dateRange.End.Value );
}
var innerQry = firstContributionDateQry.Select( xx => xx.PersonId ).AsQueryable();
var qry = new PersonService( rockContext ).Queryable()
.Where( p => innerQry.Any( xx => xx == p.Id ) );
Expression extractedFilterExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( qry, parameterExpression, "p" );
return extractedFilterExpression;
}
