/// <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 );
}
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 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 )
{
financialTransactionDetailsGivingGroupQry = financialTransactionDetailsGivingGroupQry.Where( xx => xx.TotalAmount == amount );
}
else if ( comparisonType == ComparisonType.GreaterThanOrEqualTo )
{
financialTransactionDetailsGivingGroupQry = financialTransactionDetailsGivingGroupQry.Where( xx => xx.TotalAmount >= amount );
}
var groupMemberService = new GroupMemberService( rockContext );
IQueryable<int> innerQryGivingGroupPersons = groupMemberService.Queryable()
.Where( a => financialTransactionDetailsGivingGroupQry.Select( xx => xx.GivingGroupId ).AsQueryable().Any( gg => gg.GivingGroupId == a.GroupId ) )
.Select( s => s.PersonId );
// 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;
}
/// <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>
/// 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>
/// 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 );
}
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;
}
/// <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;
}
