/// <summary> /// Creates the child controls. /// </summary> /// <returns></returns> public override Control[] CreateChildControls(Type entityType, FilterField filterControl) { var controls = new List <Control>(); var ddlIntegerCompare = ComparisonHelper.ComparisonControl(ComparisonHelper.NumericFilterComparisonTypes | ComparisonType.Between); ddlIntegerCompare.ID = string.Format("{0}_{1}", filterControl.ID, "ddlIntegerCompare"); ddlIntegerCompare.AddCssClass("js-filter-compare"); filterControl.Controls.Add(ddlIntegerCompare); controls.Add(ddlIntegerCompare); var numberBox = new NumberBox(); numberBox.ID = string.Format("{0}_{1}", filterControl.ID, "numberBox"); numberBox.AddCssClass("js-filter-control"); filterControl.Controls.Add(numberBox); controls.Add(numberBox); numberBox.FieldName = "Age"; var numberRangeEditor = new Rock.Web.UI.Controls.NumberRangeEditor(); numberRangeEditor.ID = string.Format("{0}_{1}", filterControl.ID, "numberRangeEditor"); numberRangeEditor.RangeLabel = "and"; numberRangeEditor.AddCssClass("js-filter-control-between"); filterControl.Controls.Add(numberRangeEditor); controls.Add(numberRangeEditor); return(controls.ToArray()); }
/// <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 ); } 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 var accountIds = (gfTransactions.GetUserPreference( "Account" ) ?? "").SplitDelimitedValues().AsIntegerList().Where( a => a > 0 ).ToList(); { if ( accountIds.Any() ) { qry = qry.Where( t => t.TransactionDetails.Any( d => accountIds.Contains( d.AccountId ) || (d.Account.ParentAccountId.HasValue && accountIds.Contains(d.Account.ParentAccountId.Value) ) ) ); } } // Transaction Type int transactionTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Transaction Type" ), out transactionTypeId ) ) { qry = qry.Where( t => t.TransactionTypeValueId == transactionTypeId ); } // Currency Type int currencyTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Currency Type" ), out currencyTypeId ) ) { qry = qry.Where( t => t.FinancialPaymentDetail != null && t.FinancialPaymentDetail.CurrencyTypeValueId == currencyTypeId ); } // Credit Card Type int creditCardTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Credit Card Type" ), out creditCardTypeId ) ) { qry = qry.Where( t => t.FinancialPaymentDetail != null && t.FinancialPaymentDetail.CreditCardTypeValueId == creditCardTypeId ); } // Source Type int sourceTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Source Type" ), out sourceTypeId ) ) { qry = qry.Where( t => t.SourceTypeValueId == sourceTypeId ); } // Campus if ( this.ContextEntity() == null ) { var campus = CampusCache.Read( gfTransactions.GetUserPreference( "Campus" ).AsInteger() ); if ( campus != null ) { qry = qry.Where( b => b.Batch != null && b.Batch.CampusId == campus.Id ); } } } SortProperty sortProperty = gTransactions.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 { // Default sort by Id if the transations are seen via the batch, // otherwise sort by descending date time. if ( ContextTypesRequired.Any( e => e.Id == batchEntityTypeId ) ) { qry = qry.OrderBy( t => t.Id ); } else { qry = qry.OrderByDescending( t => t.TransactionDateTime ).ThenByDescending( t => t.Id ); } } var lTransactionImageField = gTransactions.ColumnsOfType<RockLiteralField>().FirstOrDefault( a => a.ID == "lTransactionImage" ); var summaryField = gTransactions.ColumnsOfType<RockBoundField>().FirstOrDefault( a => a.DataField == "Summary" ); var showImages = bddlOptions.SelectedValue.AsIntegerOrNull() == 1; if ( lTransactionImageField != null) { lTransactionImageField.Visible = showImages; } if ( summaryField != null ) { summaryField.Visible = !showImages; } if ( showImages ) { qry = qry.Include( a => a.Images ); } _isExporting = isExporting; gTransactions.SetLinqDataSource( qry.AsNoTracking() ); gTransactions.DataBind(); _isExporting = false; if ( _batch == null && _scheduledTxn == null && _registration == null && _person == null ) { pnlSummary.Visible = true; // No context - show account summary var qryTransactionDetails = qry.SelectMany( a => a.TransactionDetails ); var qryFinancialAccount = new FinancialAccountService( rockContext ).Queryable(); var accountSummaryQry = qryTransactionDetails.GroupBy( a => a.AccountId ).Select( a => new { AccountId = a.Key, TotalAmount = (decimal?)a.Sum( d => d.Amount ) } ).Join( qryFinancialAccount, k1 => k1.AccountId, k2 => k2.Id, ( td, fa ) => new { td.TotalAmount, fa.Name, fa.Order } ) .OrderBy( a => a.Order ); var summaryList = accountSummaryQry.ToList(); var grandTotalAmount = ( summaryList.Count > 0 ) ? summaryList.Sum( a => a.TotalAmount ?? 0 ) : 0; lGrandTotal.Text = grandTotalAmount.FormatAsCurrency(); rptAccountSummary.DataSource = summaryList.Select( a => new { a.Name, TotalAmount = a.TotalAmount.FormatAsCurrency() } ).ToList(); rptAccountSummary.DataBind(); } else { pnlSummary.Visible = false; } }
/// <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; if ( int.TryParse( gfTransactions.GetUserPreference( "Account" ), out accountId ) ) { qry = qry.Where( t => t.TransactionDetails.Any( d => d.AccountId == accountId ) ); } // Transaction Type int transactionTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Transaction Type" ), out transactionTypeId ) ) { qry = qry.Where( t => t.TransactionTypeValueId == transactionTypeId ); } // Currency Type int currencyTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Currency Type" ), out currencyTypeId ) ) { qry = qry.Where( t => t.CurrencyTypeValueId == currencyTypeId ); } // Credit Card Type int creditCardTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Credit Card Type" ), out creditCardTypeId ) ) { qry = qry.Where( t => t.CreditCardTypeValueId == creditCardTypeId ); } // Source Type int sourceTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Source Type" ), out sourceTypeId ) ) { qry = qry.Where( t => t.SourceTypeValueId == sourceTypeId ); } } SortProperty sortProperty = gTransactions.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.OrderBy( t => t.Id ); } // Row Limit int? rowLimit = gfTransactions.GetUserPreference( "Row Limit" ).AsIntegerOrNull(); if ( rowLimit.HasValue ) { qry = qry.Take( rowLimit.Value ); } gTransactions.DataSource = qry.AsNoTracking().ToList(); gTransactions.DataBind(); }
/// <summary> /// Creates the control(s) neccessary for prompting user for a new value /// </summary> /// <param name="configurationValues">The configuration values.</param> /// <param name="id"></param> /// <returns> /// The control /// </returns> public override System.Web.UI.Control EditControl( System.Collections.Generic.Dictionary<string, ConfigurationValue> configurationValues, string id ) { var numberRangeEditor = new NumberRangeEditor { ID = id }; numberRangeEditor.NumberType = System.Web.UI.WebControls.ValidationDataType.Integer; return numberRangeEditor; }
/// <summary> /// Creates the child controls. /// </summary> /// <returns></returns> public override Control[] CreateChildControls( Type entityType, FilterField filterControl ) { var controls = new List<Control>(); var ddlIntegerCompare = ComparisonHelper.ComparisonControl( ComparisonHelper.NumericFilterComparisonTypes | ComparisonType.Between ); ddlIntegerCompare.ID = string.Format( "{0}_{1}", filterControl.ID, "ddlIntegerCompare" ); ddlIntegerCompare.AddCssClass( "js-filter-compare" ); filterControl.Controls.Add( ddlIntegerCompare ); controls.Add( ddlIntegerCompare ); var numberBox = new NumberBox(); numberBox.ID = string.Format( "{0}_{1}", filterControl.ID, "numberBox" ); numberBox.AddCssClass( "js-filter-control" ); filterControl.Controls.Add( numberBox ); controls.Add( numberBox ); numberBox.FieldName = "Age"; var numberRangeEditor = new Rock.Web.UI.Controls.NumberRangeEditor(); numberRangeEditor.ID = string.Format( "{0}_{1}", filterControl.ID, "numberRangeEditor" ); numberRangeEditor.RangeLabel = "and"; numberRangeEditor.AddCssClass( "js-filter-control-between" ); filterControl.Controls.Add( numberRangeEditor ); controls.Add( numberRangeEditor ); return controls.ToArray(); }
/// <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 ) { DateTime currentDate = RockDateTime.Today; int currentDayOfYear = currentDate.DayOfYear; var values = selection.Split( '|' ); ComparisonType comparisonType = values[0].ConvertToEnum<ComparisonType>( ComparisonType.EqualTo ); int? ageValue = values[1].AsIntegerOrNull(); var rockContext = (RockContext)serviceInstance.Context; var personAgeQuery = new PersonService( rockContext ).Queryable(); MemberExpression idExpression = Expression.Property( parameterExpression, "Id" ); Expression ageSelect = new Rock.Reporting.DataSelect.Person.AgeSelect().GetExpression( rockContext, idExpression, "" ); if ( values.Length >= 3 && comparisonType == ComparisonType.Between ) { var numberRangeEditor = new NumberRangeEditor(); numberRangeEditor.DelimitedValues = values[2]; decimal ageValueStart = numberRangeEditor.LowerValue ?? 0; decimal ageValueEnd = numberRangeEditor.UpperValue ?? decimal.MaxValue; var personAgeBetweenQuery = personAgeQuery.Where( p => ( ( p.BirthDate > SqlFunctions.DateAdd( "year", -SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ), currentDate ) ? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1 : SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) ) >= ageValueStart ) && ( ( p.BirthDate > SqlFunctions.DateAdd( "year", -SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ), currentDate ) ? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1 : SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) ) <= ageValueEnd ) ); BinaryExpression result = FilterExpressionExtractor.Extract<Rock.Model.Person>( personAgeBetweenQuery, parameterExpression, "p" ) as BinaryExpression; return result; } else { var personAgeEqualQuery = personAgeQuery.Where( p => ( p.BirthDate > SqlFunctions.DateAdd( "year", -SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ), currentDate ) ? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1 : SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) ) == ageValue ); BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( personAgeEqualQuery, parameterExpression, "p" ) as BinaryExpression; BinaryExpression result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, null ); return result; } }
/// <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 ); } // Source Type int sourceTypeId = int.MinValue; if ( int.TryParse( gfTransactions.GetUserPreference( "Source Type" ), out sourceTypeId ) ) { queryable = queryable.Where( t => t.SourceTypeValueId == sourceTypeId ); } } SortProperty sortProperty = gTransactions.SortProperty; if ( sortProperty != null ) { if ( sortProperty.Property == "TotalAmount" ) { if ( sortProperty.Direction == SortDirection.Ascending ) { queryable = queryable.OrderBy( t => t.TransactionDetails.Sum( d => d.Amount ) ); } else { queryable = queryable.OrderByDescending( t => t.TransactionDetails.Sum( d => d.Amount ) ); } } else { queryable = queryable.Sort( sortProperty ); } } else { queryable = queryable.OrderBy( t => t.TransactionDateTime ); } gTransactions.DataSource = queryable.AsNoTracking().ToList(); gTransactions.DataBind(); }
/// <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(); rGridTransactions.DataBind(); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { int? personId = null; int? givingGroupId = null; bool validRequest = false; if ( TargetPerson != null ) { personId = TargetPerson.Id; givingGroupId = TargetPerson.GivingGroupId; validRequest = true; } else { int personEntityTypeId = EntityTypeCache.Read( "Rock.Model.Person" ).Id; if ( !ContextTypesRequired.Any( e => e.Id == personEntityTypeId ) ) { validRequest = true; } } if ( validRequest ) { var rockContext = new RockContext(); var qry = new FinancialScheduledTransactionService( rockContext ) .Queryable( "ScheduledTransactionDetails,FinancialPaymentDetail.CurrencyTypeValue,FinancialPaymentDetail.CreditCardTypeValue" ) .AsNoTracking(); // Amount Range var nre = new NumberRangeEditor(); nre.DelimitedValues = gfSettings.GetUserPreference( "Amount" ); if ( nre.LowerValue.HasValue ) { qry = qry.Where( t => t.ScheduledTransactionDetails.Sum( d => d.Amount ) >= nre.LowerValue.Value ); } if ( nre.UpperValue.HasValue ) { qry = qry.Where( t => t.ScheduledTransactionDetails.Sum( d => d.Amount ) <= nre.UpperValue.Value ); } // Frequency int? frequencyTypeId = gfSettings.GetUserPreference( "Frequency" ).AsIntegerOrNull(); if ( frequencyTypeId.HasValue ) { qry = qry.Where( t => t.TransactionFrequencyValueId == frequencyTypeId.Value ); } // Date Range var drp = new DateRangePicker(); drp.DelimitedValues = gfSettings.GetUserPreference( "Created" ); if ( drp.LowerValue.HasValue ) { qry = qry.Where( t => t.CreatedDateTime >= drp.LowerValue.Value ); } if ( drp.UpperValue.HasValue ) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 ); qry = qry.Where( t => t.CreatedDateTime < upperDate ); } // Account Id int accountId = int.MinValue; if ( int.TryParse( gfSettings.GetUserPreference( "Account" ), out accountId ) ) { qry = qry.Where( t => t.ScheduledTransactionDetails.Any( d => d.AccountId == accountId ) ); } // Active only (no filter) if ( string.IsNullOrWhiteSpace( gfSettings.GetUserPreference( "Include Inactive" ) ) ) { qry = qry.Where( t => t.IsActive ); } if ( givingGroupId.HasValue ) { // Person contributes with family qry = qry.Where( t => t.AuthorizedPersonAlias.Person.GivingGroupId == givingGroupId ); } else if ( personId.HasValue ) { // Person contributes individually qry = qry.Where( t => t.AuthorizedPersonAlias.PersonId == personId ); } SortProperty sortProperty = gList.SortProperty; if ( sortProperty != null ) { if ( sortProperty.Property == "Amount" ) { if ( sortProperty.Direction == SortDirection.Ascending ) { qry = qry.OrderBy( t => t.ScheduledTransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.00M ); } else { qry = qry.OrderByDescending( t => t.ScheduledTransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M ); } } else { qry = qry.Sort( sortProperty ); } } else { qry = qry .OrderBy( t => t.AuthorizedPersonAlias.Person.LastName ) .ThenBy( t => t.AuthorizedPersonAlias.Person.NickName ) .ThenByDescending( t => t.IsActive ) .ThenByDescending( t => t.StartDate ); } gList.SetLinqDataSource<FinancialScheduledTransaction>( qry ); gList.DataBind(); } }