C# FinancialTransactionService.Where方法代码示例

        /// <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>
        /// Binds the grid.
        /// </summary>
        private void BindGrid( bool isExporting = false )
        {
            _currencyTypes = new Dictionary<int,string>();
            _creditCardTypes = new Dictionary<int,string>();

            // If configured for a registration and registration is null, return
            int registrationEntityTypeId = EntityTypeCache.Read( typeof( Rock.Model.Registration ) ).Id;
            if ( ContextTypesRequired.Any( e => e.Id == registrationEntityTypeId ) && _registration == null )
            {
                return;
            }

            // If configured for a person and person is null, return
            int personEntityTypeId = EntityTypeCache.Read( "Rock.Model.Person" ).Id;
            if ( ContextTypesRequired.Any( e => e.Id == personEntityTypeId ) && _person == null )
            {
                return;
            }

            // If configured for a batch and batch is null, return
            int batchEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialBatch" ).Id;
            if ( ContextTypesRequired.Any( e => e.Id == batchEntityTypeId ) && _batch == null )
            {
                return;
            }

            // If configured for a batch and batch is null, return
            int scheduledTxnEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialScheduledTransaction" ).Id;
            if ( ContextTypesRequired.Any( e => e.Id == scheduledTxnEntityTypeId ) && _scheduledTxn == null )
            {
                return;
            }

            // Qry
            var rockContext = new RockContext();
            var qry = new FinancialTransactionService( rockContext ).Queryable();

            // Transaction Types
            var transactionTypeValueIdList = GetAttributeValue( "TransactionTypes" ).SplitDelimitedValues().AsGuidList().Select( a => DefinedValueCache.Read( a ) ).Where( a => a != null ).Select( a => a.Id ).ToList();

            if ( transactionTypeValueIdList.Any() )
            {
                qry = qry.Where( t => transactionTypeValueIdList.Contains( t.TransactionTypeValueId ) );
            }

            // Set up the selection filter
            if ( _batch != null )
            {
                // If transactions are for a batch, the filter is hidden so only check the batch id
                qry = qry.Where( t => t.BatchId.HasValue && t.BatchId.Value == _batch.Id );

                // If the batch is closed, do not allow any editing of the transactions
                if ( _batch.Status != BatchStatus.Closed && _canEdit )
                {
                    gTransactions.IsDeleteEnabled = _canEdit;
                }
                else
                {
                    gTransactions.IsDeleteEnabled = false;
                }
            }
            else if ( _scheduledTxn != null )
            {
                // If transactions are for a batch, the filter is hidden so only check the batch id
                qry = qry.Where( t => t.ScheduledTransactionId.HasValue && t.ScheduledTransactionId.Value == _scheduledTxn.Id );

                gTransactions.IsDeleteEnabled = false;
            }
            else if ( _registration != null )
            {
                qry = qry
                    .Where( t => t.TransactionDetails
                        .Any( d =>
                            d.EntityTypeId.HasValue &&
                            d.EntityTypeId.Value == registrationEntityTypeId &&
                            d.EntityId.HasValue &&
                            d.EntityId.Value == _registration.Id ) );

                gTransactions.IsDeleteEnabled = false;
            }
            else    // Person
            {
                // otherwise set the selection based on filter settings
                if ( _person != null )
                {
                    // get the transactions for the person or all the members in the person's giving group (Family)
                    qry = qry.Where( t => t.AuthorizedPersonAlias.Person.GivingId == _person.GivingId );
                }

                // Date Range
                var drp = new DateRangePicker();
                drp.DelimitedValues = gfTransactions.GetUserPreference( "Date Range" );
                if ( drp.LowerValue.HasValue )
                {
                    qry = qry.Where( t => t.TransactionDateTime >= drp.LowerValue.Value );
                }

//.........这里部分代码省略.........

        /// <summary>
        /// Binds the payments grid.
        /// </summary>
        private void BindPaymentsGrid()
        {
            int? instanceId = hfRegistrationInstanceId.Value.AsIntegerOrNull();
            if ( instanceId.HasValue )
            {
                using ( var rockContext = new RockContext() )
                {
                    var currencyTypes = new Dictionary<int, string>();
                    var creditCardTypes = new Dictionary<int, string>();

                    // If configured for a registration and registration is null, return
                    int registrationEntityTypeId = EntityTypeCache.Read( typeof( Rock.Model.Registration ) ).Id;

                    // Get all the registrations for this instance
                    PaymentRegistrations = new RegistrationService( rockContext )
                        .Queryable( "PersonAlias.Person,Registrants.PersonAlias.Person" ).AsNoTracking()
                        .Where( r =>
                            r.RegistrationInstanceId == instanceId.Value &&
                            !r.IsTemporary )
                        .ToList();

                    // Get the Registration Ids
                    var registrationIds = PaymentRegistrations
                        .Select( r => r.Id )
                        .ToList();

                    // Get all the transactions relate to these registrations
                    var qry = new FinancialTransactionService( rockContext )
                        .Queryable().AsNoTracking()
                        .Where( t => t.TransactionDetails
                            .Any( d =>
                                d.EntityTypeId.HasValue &&
                                d.EntityTypeId.Value == registrationEntityTypeId &&
                                d.EntityId.HasValue &&
                                registrationIds.Contains( d.EntityId.Value ) ) );

                    // Date Range
                    var drp = new DateRangePicker();
                    drp.DelimitedValues = fPayments.GetUserPreference( "Date Range" );
                    if ( drp.LowerValue.HasValue )
                    {
                        qry = qry.Where( t => t.TransactionDateTime >= drp.LowerValue.Value );
                    }

                    if ( drp.UpperValue.HasValue )
                    {
                        DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 );
                        qry = qry.Where( t => t.TransactionDateTime < upperDate );
                    }

                    SortProperty sortProperty = gPayments.SortProperty;
                    if ( sortProperty != null )
                    {
                        if ( sortProperty.Property == "TotalAmount" )
                        {
                            if ( sortProperty.Direction == SortDirection.Ascending )
                            {
                                qry = qry.OrderBy( t => t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.00M );
                            }
                            else
                            {
                                qry = qry.OrderByDescending( t => t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M );
                            }
                        }
                        else
                        {
                            qry = qry.Sort( sortProperty );
                        }
                    }
                    else
                    {
                        qry = qry.OrderByDescending( t => t.TransactionDateTime ).ThenByDescending( t => t.Id );
                    }

                    gPayments.SetLinqDataSource( qry.AsNoTracking() );
                    gPayments.DataBind();
                }
            }
        }

        /// <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 values = selection.Split( '|' );

            ComparisonType comparisonType = values[0].ConvertToEnum<ComparisonType>( ComparisonType.EqualTo );
            decimal? amountValue = values[1].AsDecimalOrNull();

            var qry = new FinancialTransactionService( (RockContext)serviceInstance.Context ).Queryable();
            var totalAmountEqualQuery = qry.Where( p => p.TransactionDetails.Sum(a => a.Amount) == amountValue );

            BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract<Rock.Model.FinancialTransaction>( totalAmountEqualQuery, parameterExpression, "p" ) as BinaryExpression;
            BinaryExpression result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, null );

            return result;
        }

        /// <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;
            DateTime? startDate = selectionValues[2].AsDateTime();
            DateTime? endDate = selectionValues[3].AsDateTime();
            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 ( startDate.HasValue )
            {
                financialTransactionQry = financialTransactionQry.Where( xx => xx.TransactionDateTime >= startDate.Value );
            }

            if ( endDate.HasValue )
            {
                financialTransactionQry = financialTransactionQry.Where( xx => xx.TransactionDateTime < endDate.Value );
            }

            bool limitToAccounts = accountIdList.Any();

            // query transactions for individuals.
            // If CombineGiving, exclude people that are Giving Group, and we'll get those when we union with CombineGiving
            var financialTransactionDetailsIndividualQry = financialTransactionQry.Where( a => !combineGiving || !a.AuthorizedPersonAlias.Person.GivingGroupId.HasValue )
                .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 = financialTransactionQry.Where( a => a.AuthorizedPersonAlias.Person.GivingGroupId.HasValue )
                .GroupBy( xx => new
                {
                    xx.AuthorizedPersonAlias.Person.GivingGroupId
                } ).Select( xx =>
                    new
                    {
                        GivingGroupId = xx.Key,
                        TotalAmount = xx.Sum( ss => ss.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 )
//.........这里部分代码省略.........

        /// <summary>
        /// Binds the grid.
        /// </summary>
        private void BindGrid()
        {
            _currencyTypes = new Dictionary<int,string>();
            _creditCardTypes = new Dictionary<int,string>();

            // If configured for a person and person is null, return
            int personEntityTypeId = EntityTypeCache.Read( "Rock.Model.Person" ).Id;
            if ( ContextTypesRequired.Any( e => e.Id == personEntityTypeId ) && _person == null )
            {
                return;
            }

            // If configured for a batch and batch is null, return
            int batchEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialBatch" ).Id;
            if ( ContextTypesRequired.Any( e => e.Id == batchEntityTypeId ) && _batch == null )
            {
                return;
            }

            // If configured for a batch and batch is null, return
            int scheduledTxnEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialScheduledTransaction" ).Id;
            if ( ContextTypesRequired.Any( e => e.Id == scheduledTxnEntityTypeId ) && _scheduledTxn == null )
            {
                return;
            }

            // Qry
            var qry = new FinancialTransactionService( new RockContext() )
                .Queryable( "AuthorizedPersonAlias.Person,ProcessedByPersonAlias.Person" );

            // Set up the selection filter
            if ( _batch != null )
            {
                // If transactions are for a batch, the filter is hidden so only check the batch id
                qry = qry.Where( t => t.BatchId.HasValue && t.BatchId.Value == _batch.Id );

                // If the batch is closed, do not allow any editing of the transactions
                if ( _batch.Status != BatchStatus.Closed && _canEdit )
                {
                    gTransactions.IsDeleteEnabled = true;
                }
                else
                {
                    gTransactions.IsDeleteEnabled = false;
                }
            }
            else if ( _scheduledTxn != null )
            {
                // If transactions are for a batch, the filter is hidden so only check the batch id
                qry = qry.Where( t => t.ScheduledTransactionId.HasValue && t.ScheduledTransactionId.Value == _scheduledTxn.Id );

                gTransactions.IsDeleteEnabled = false;
            }
            else    // Person
            {
                // otherwise set the selection based on filter settings
                if ( _person != null )
                {
                    qry = qry.Where( t => t.AuthorizedPersonAlias.PersonId == _person.Id );
                }

                // Date Range
                var drp = new DateRangePicker();
                drp.DelimitedValues = gfTransactions.GetUserPreference( "Date Range" );
                if ( drp.LowerValue.HasValue )
                {
                    qry = qry.Where( t => t.TransactionDateTime >= drp.LowerValue.Value );
                }

                if ( drp.UpperValue.HasValue )
                {
                    DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 );
                    qry = qry.Where( t => t.TransactionDateTime < upperDate );
                }

                // Amount Range
                var nre = new NumberRangeEditor();
                nre.DelimitedValues = gfTransactions.GetUserPreference( "Amount Range" );
                if ( nre.LowerValue.HasValue )
                {
                    qry = qry.Where( t => t.TransactionDetails.Sum( d => d.Amount ) >= nre.LowerValue.Value );
                }

                if ( nre.UpperValue.HasValue )
                {
                    qry = qry.Where( t => t.TransactionDetails.Sum( d => d.Amount ) <= nre.UpperValue.Value );
                }

                // Transaction Code
                string transactionCode = gfTransactions.GetUserPreference( "Transaction Code" );
                if ( !string.IsNullOrWhiteSpace( transactionCode ) )
                {
                    qry = qry.Where( t => t.TransactionCode == transactionCode.Trim() );
                }

                // Account Id
                int accountId = int.MinValue;
//.........这里部分代码省略.........

        /// <summary>
        /// Binds the grid.
        /// </summary>
        private void BindGrid()
        {
            var queryable = new FinancialTransactionService( new RockContext() ).Queryable();

            // Set up the selection filter
            if ( _batch != null )
            {
                // If transactions are for a batch, the filter is hidden so only check the batch id
                queryable = queryable.Where( t => t.BatchId.HasValue && t.BatchId.Value == _batch.Id );

                // If the batch is closed, do not allow any editing of the transactions
                if ( _batch.Status != BatchStatus.Open && _canConfigure )
                {
                    gTransactions.Actions.ShowAdd = false;
                    gTransactions.IsDeleteEnabled = false;
                }
                else
                {
                    gTransactions.Actions.ShowAdd = true;
                    gTransactions.IsDeleteEnabled = true;
                }
            }
            else if ( !string.IsNullOrWhiteSpace( PageParameter( "financialBatchId" ) ) && _batch == null )
            {
                // this makes sure the grid will show no transactions when you're adding a new financial batch.
                queryable = queryable.Where( t => t.BatchId.HasValue && t.BatchId.Value == 0 );
            }
            else
            {
                // otherwise set the selection based on filter settings
                if ( _person != null )
                {
                    queryable = queryable.Where( t => t.AuthorizedPersonId == _person.Id );
                }

                // Date Range
                var drp = new DateRangePicker();
                drp.DelimitedValues = gfTransactions.GetUserPreference( "Date Range" );
                if ( drp.LowerValue.HasValue )
                {
                    queryable = queryable.Where( t => t.TransactionDateTime >= drp.LowerValue.Value );
                }

                if ( drp.UpperValue.HasValue )
                {
                    DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 );
                    queryable = queryable.Where( t => t.TransactionDateTime < upperDate );
                }

                // Amount Range
                var nre = new NumberRangeEditor();
                nre.DelimitedValues = gfTransactions.GetUserPreference( "Amount Range" );
                if ( nre.LowerValue.HasValue )
                {
                    queryable = queryable.Where( t => t.TransactionDetails.Sum( d => d.Amount ) >= nre.LowerValue.Value );
                }

                if ( nre.UpperValue.HasValue )
                {
                    queryable = queryable.Where( t => t.TransactionDetails.Sum( d => d.Amount ) <= nre.UpperValue.Value );
                }

                // Transaction Code
                string transactionCode = gfTransactions.GetUserPreference( "Transaction Code" );
                if ( !string.IsNullOrWhiteSpace( transactionCode ) )
                {
                    queryable = queryable.Where( t => t.TransactionCode == transactionCode.Trim() );
                }

                // Account Id
                int accountId = int.MinValue;
                if ( int.TryParse( gfTransactions.GetUserPreference( "Account" ), out accountId ) )
                {
                    queryable = queryable.Where( t => t.TransactionDetails.Any( d => d.AccountId == accountId ) );
                }

                // Transaction Type
                int transactionTypeId = int.MinValue;
                if ( int.TryParse( gfTransactions.GetUserPreference( "Transaction Type" ), out transactionTypeId ) )
                {
                    queryable = queryable.Where( t => t.TransactionTypeValueId == transactionTypeId );
                }

                // Currency Type
                int currencyTypeId = int.MinValue;
                if ( int.TryParse( gfTransactions.GetUserPreference( "Currency Type" ), out currencyTypeId ) )
                {
                    queryable = queryable.Where( t => t.CurrencyTypeValueId == currencyTypeId );
                }

                // Credit Card Type
                int creditCardTypeId = int.MinValue;
                if ( int.TryParse( gfTransactions.GetUserPreference( "Credit Card Type" ), out creditCardTypeId ) )
                {
                    queryable = queryable.Where( t => t.CreditCardTypeValueId == creditCardTypeId );
                }

//.........这里部分代码省略.........

        /// <summary>
        /// Binds the grid.
        /// </summary>
        private void BindGrid()
        {
            var queryable = new FinancialTransactionService().Queryable();

            // Set up the selection filter
            if ( _batch != null )
            {
                // If transactions are for a batch, the filter is hidden so only check the batch id
                queryable = queryable.Where( t => t.BatchId.HasValue && t.BatchId.Value == _batch.Id );
            }
            else
            {
                // otherwise set the selection based on filter settings
                if ( _person != null )
                {
                    queryable = queryable.Where( t => t.AuthorizedPersonId == _person.Id );
                }

                // Date Range
                var drp = new DateRangePicker();
                drp.DelimitedValues = rFilter.GetUserPreference( "Date Range" );
                if ( drp.LowerValue.HasValue )
                {
                    queryable = queryable.Where( t => t.TransactionDateTime >= drp.LowerValue.Value );
                }
                if ( drp.UpperValue.HasValue )
                {
                    DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 );
                    queryable = queryable.Where( t => t.TransactionDateTime < upperDate );
                }

                // Amount Range
                var nre = new NumberRangeEditor();
                nre.DelimitedValues = rFilter.GetUserPreference( "Amount Range" );
                if ( nre.LowerValue.HasValue )
                {
                    queryable = queryable.Where( t => t.Amount >= nre.LowerValue.Value );
                }
                if ( nre.UpperValue.HasValue )
                {
                    queryable = queryable.Where( t => t.Amount <= nre.UpperValue.Value );
                }

                // Transaction Code
                string transactionCode = rFilter.GetUserPreference( "Transaction Code" );
                if ( !string.IsNullOrWhiteSpace( transactionCode ) )
                {
                    queryable = queryable.Where( t => t.TransactionCode == transactionCode.Trim() );
                }

                // Account Id
                int accountId = int.MinValue; 
                if ( int.TryParse( rFilter.GetUserPreference( "Account" ), out accountId ) )
                {
                    queryable = queryable.Where( t => t.TransactionDetails.Any( d => d.AccountId == accountId ) );
                }

                // Transaction Type
                int transactionTypeId = int.MinValue;
                if ( int.TryParse( rFilter.GetUserPreference( "Transaction Type" ), out transactionTypeId ) )
                {
                    queryable = queryable.Where( t => t.TransactionTypeValueId == transactionTypeId );
                }

                // Currency Type
                int currencyTypeId = int.MinValue;
                if ( int.TryParse( rFilter.GetUserPreference( "Currency Type" ), out currencyTypeId ) )
                {
                    queryable = queryable.Where( t => t.CurrencyTypeValueId == currencyTypeId );
                }

                // Credit Card Type
                int creditCardTypeId = int.MinValue;
                if ( int.TryParse( rFilter.GetUserPreference( "Credit Card Type" ), out creditCardTypeId ) )
                {
                    queryable = queryable.Where( t => t.CreditCardTypeValueId == creditCardTypeId );
                }

                // Source Type
                int sourceTypeId = int.MinValue;
                if ( int.TryParse( rFilter.GetUserPreference( "Source Type" ), out sourceTypeId ) )
                {
                    queryable = queryable.Where( t => t.SourceTypeValueId == sourceTypeId );
                }
            }

            SortProperty sortProperty = rGridTransactions.SortProperty;
            if ( sortProperty != null )
            {
                queryable = queryable.Sort( sortProperty );
            }
            else
            {
                queryable = queryable.OrderBy( t => t.TransactionDateTime );
            }

            rGridTransactions.DataSource = queryable.AsNoTracking().ToList();
//.........这里部分代码省略.........

        /// <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, object serviceInstance, Expression parameterExpression, string selection )
        {
            string[] selectionValues = selection.Split( '|' );
            if ( selectionValues.Length != 4 )
            {
                return null;
            }

            ComparisonType comparisonType = selectionValues[0].ConvertToEnum<ComparisonType>( ComparisonType.GreaterThanOrEqualTo );
            decimal amount = selectionValues[1].AsDecimal() ?? 0.00M;
            DateTime startDate = selectionValues[2].AsDateTime() ?? DateTime.MinValue;
            DateTime endDate = selectionValues[3].AsDateTime() ?? DateTime.MaxValue;

            Rock.Data.RockContext context = serviceInstance.GetPropertyValue( "RockContext" ) as Rock.Data.RockContext;

            var financialTransactionQry = new FinancialTransactionService( context ).Queryable()
                .Where( xx => xx.TransactionDateTime >= startDate && xx.TransactionDateTime < endDate )
                .GroupBy( xx => xx.AuthorizedPersonId ).Select( xx =>
                    new
                    {
                        PersonId = xx.Key,
                        TotalAmount = xx.Sum( ss => ss.Amount )
                    } );

            if ( comparisonType == ComparisonType.LessThan )
            {
                financialTransactionQry = financialTransactionQry.Where( xx => xx.TotalAmount < amount );
            }
            else
            {
                financialTransactionQry = financialTransactionQry.Where( xx => xx.TotalAmount >= amount );
            }

            var innerQry = financialTransactionQry.Select( xx => xx.PersonId ?? 0 ).AsQueryable();

            var qry = new PersonService( context ).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 < 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 );
            }
//.........这里部分代码省略.........

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