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