/// <summary>
/// Handles the GridReorder event of the grdFinancialBatch control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="GridReorderEventArgs"/> instance containing the event data.</param>
private void rGridBatch_GridReorder(object sender, GridReorderEventArgs e)
{
var batchService = new Rock.Model.FinancialBatchService();
var queryable = batchService.Queryable();
List <Rock.Model.FinancialBatch> items = queryable.ToList();
batchService.Reorder(items, e.OldIndex, e.NewIndex, CurrentPersonId);
BindGrid();
}
/// <summary>
/// Handles the GridReorder event of the gBatchList control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="GridReorderEventArgs"/> instance containing the event data.</param>
private void gBatchList_GridReorder(object sender, GridReorderEventArgs e)
{
var rockContext = new RockContext();
var batchService = new Rock.Model.FinancialBatchService(rockContext);
var queryable = batchService.Queryable();
List <Rock.Model.FinancialBatch> items = queryable.ToList();
batchService.Reorder(items, e.OldIndex, e.NewIndex);
rockContext.SaveChanges();
BindGrid();
}
/// <summary>
/// When implemented by a class, enables a server control to process an event raised when a form is posted to the server.
/// </summary>
/// <param name="eventArgument">A <see cref="T:System.String" /> that represents an optional event argument to be passed to the event handler.</param>
public void RaisePostBackEvent( string eventArgument )
{
if ( eventArgument == "StatusUpdate" &&
ddlAction != null &&
ddlAction.SelectedValue != null &&
!string.IsNullOrWhiteSpace( ddlAction.SelectedValue ) )
{
var batchesSelected = new List<int>();
gBatchList.SelectedKeys.ToList().ForEach( b => batchesSelected.Add( b.ToString().AsInteger() ) );
if ( batchesSelected.Any() )
{
var newStatus = ddlAction.SelectedValue == "OPEN" ? BatchStatus.Open : BatchStatus.Closed;
var rockContext = new RockContext();
var batchService = new FinancialBatchService( rockContext );
var batchesToUpdate = batchService.Queryable()
.Where( b =>
batchesSelected.Contains( b.Id ) &&
b.Status != newStatus )
.ToList();
foreach ( var batch in batchesToUpdate )
{
batch.Status = newStatus;
}
rockContext.SaveChanges();
nbResult.Text = string.Format(
"{0} batches were {1}.",
batchesToUpdate.Count().ToString( "N0" ),
newStatus == BatchStatus.Open ? "opened" : "closed" );
nbResult.NotificationBoxType = NotificationBoxType.Success;
nbResult.Visible = true;
}
else
{
nbResult.Text = string.Format( "There were not any batches selected." );
nbResult.NotificationBoxType = NotificationBoxType.Warning;
nbResult.Visible = true;
}
ddlAction.SelectedIndex = 0;
BindGrid();
}
}
private List<BatchRow> GetData()
{
var batchService = new FinancialBatchService( new RockContext() );
var qry = batchService.Queryable()
.Where( b => b.BatchStartDateTime.HasValue );
// filter by date
string dateRangeValue = gfBatchFilter.GetUserPreference( "Date Range" );
if ( !string.IsNullOrWhiteSpace( dateRangeValue ) )
{
var drp = new DateRangePicker();
drp.DelimitedValues = dateRangeValue;
if ( drp.LowerValue.HasValue )
{
qry = qry.Where( b => b.BatchStartDateTime >= drp.LowerValue );
}
if ( drp.UpperValue.HasValue )
{
var endOfDay = drp.UpperValue.Value.AddDays( 1 );
qry = qry.Where( b => b.BatchStartDateTime < drp.UpperValue );
}
}
// filter by status
var status = gfBatchFilter.GetUserPreference( "Status" ).ConvertToEnumOrNull<BatchStatus>();
if ( status.HasValue )
{
qry = qry.Where( b => b.Status == status );
}
// filter by title
string title = gfBatchFilter.GetUserPreference( "Title" );
if ( !string.IsNullOrEmpty( title ) )
{
qry = qry.Where( batch => batch.Name.StartsWith( title ) );
}
// filter by accounting code
if ( tbAccountingCode.Visible )
{
string accountingCode = gfBatchFilter.GetUserPreference( "Accounting Code" );
if ( !string.IsNullOrEmpty( accountingCode ) )
{
qry = qry.Where( batch => batch.AccountingSystemCode.StartsWith( accountingCode ) );
}
}
// filter by campus
var campus = CampusCache.Read( gfBatchFilter.GetUserPreference( "Campus" ).AsInteger() );
if ( campus != null )
{
qry = qry.Where( b => b.CampusId == campus.Id );
}
IOrderedQueryable<FinancialBatch> sortedQry = null;
SortProperty sortProperty = gBatchList.SortProperty;
if ( sortProperty != null )
{
switch ( sortProperty.Property )
{
case "TransactionCount":
{
if ( sortProperty.Direction == SortDirection.Ascending )
{
sortedQry = qry.OrderBy( b => b.Transactions.Count() );
}
else
{
sortedQry = qry.OrderByDescending( b => b.Transactions.Count() );
}
break;
}
case "TransactionAmount":
{
if ( sortProperty.Direction == SortDirection.Ascending )
{
sortedQry = qry.OrderBy( b => b.Transactions.Sum( t => (decimal?)( t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M ) ) ?? 0.0M );
}
else
{
sortedQry = qry.OrderByDescending( b => b.Transactions.Sum( t => (decimal?)( t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M ) ) ?? 0.0M );
}
break;
}
default:
{
sortedQry = qry.Sort( sortProperty );
break;
}
}
}
else
{
sortedQry = qry
.OrderByDescending( b => b.BatchStartDateTime )
.ThenBy( b => b.Name );
}
return sortedQry
.Select( b => new BatchRow
{
Id = b.Id,
BatchStartDateTime = b.BatchStartDateTime.Value,
Name = b.Name,
AccountingSystemCode = b.AccountingSystemCode,
TransactionCount = b.Transactions.Count(),
TransactionAmount = b.Transactions.Sum( t => (decimal?)( t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M ) ) ?? 0.0M,
ControlAmount = b.ControlAmount,
CampusName = b.Campus != null ? b.Campus.Name : "",
Status = b.Status,
UnMatchedTxns = b.Transactions.Any( t => !t.AuthorizedPersonAliasId.HasValue )
} )
.ToList();
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var batchService = new FinancialBatchService();
var batches = batchService.Queryable();
SortProperty sortProperty = rGridBatch.SortProperty;
if ( dtBatchDate.SelectedDate.HasValue )
{
batches = batches.Where( batch => batch.BatchStartDateTime >= dtBatchDate.SelectedDate );
}
if ( (ddlStatus.SelectedValueAsInt() ?? 0) > 0 )
{
var batchStatus = ddlStatus.SelectedValueAsEnum<BatchStatus>();
batches = batches.Where( Batch => Batch.Status == batchStatus );
}
if ( !string.IsNullOrEmpty( txtTitle.Text ) )
{
batches = batches.Where( Batch => Batch.Name == txtTitle.Text );
}
if ( ddlCampus.SelectedCampusId.HasValue )
{
batches = batches.Where( Batch => Batch.CampusId == ddlCampus.SelectedCampusId.Value );
}
if ( sortProperty != null )
{
rGridBatch.DataSource = batches.Sort( sortProperty ).ToList();
}
else
{
rGridBatch.DataSource = batches.OrderBy( b => b.Name ).ToList();
}
rGridBatch.DataBind();
}
/// <summary>
/// Handles the GridReorder event of the grdFinancialBatch control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="GridReorderEventArgs"/> instance containing the event data.</param>
private void rGridBatch_GridReorder( object sender, GridReorderEventArgs e )
{
var batchService = new Rock.Model.FinancialBatchService();
var queryable = batchService.Queryable();
List<Rock.Model.FinancialBatch> items = queryable.ToList();
batchService.Reorder( items, e.OldIndex, e.NewIndex, CurrentPersonId );
BindGrid();
}
/// <summary>
/// Handles the GridReorder event of the gBatchList control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="GridReorderEventArgs"/> instance containing the event data.</param>
private void gBatchList_GridReorder( object sender, GridReorderEventArgs e )
{
var rockContext = new RockContext();
var batchService = new Rock.Model.FinancialBatchService( rockContext );
var queryable = batchService.Queryable();
List<Rock.Model.FinancialBatch> items = queryable.ToList();
batchService.Reorder( items, e.OldIndex, e.NewIndex );
rockContext.SaveChanges();
BindGrid();
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var batchService = new FinancialBatchService( new RockContext() );
var batches = batchService.Queryable();
if ( dpBatchDate.SelectedDate.HasValue )
{
batches = batches.Where( batch => batch.BatchStartDateTime >= dpBatchDate.SelectedDate );
}
string status = gfBatchFilter.GetUserPreference( "Status" );
if ( !string.IsNullOrWhiteSpace( status ) )
{
var batchStatus = (BatchStatus)Enum.Parse( typeof( BatchStatus ), status );
batches = batches.Where( batch => batch.Status == batchStatus );
}
if ( !string.IsNullOrEmpty( tbTitle.Text ) )
{
batches = batches.Where( batch => batch.Name == tbTitle.Text );
}
if ( campCampus.SelectedCampusId.HasValue )
{
batches = batches.Where( batch => batch.CampusId == campCampus.SelectedCampusId.Value );
}
SortProperty sortProperty = gBatchList.SortProperty;
if ( sortProperty != null )
{
gBatchList.DataSource = batches.Sort( sortProperty ).ToList();
}
else
{
gBatchList.DataSource = batches.OrderBy( batch => batch.Name ).ToList();
}
gBatchList.DataBind();
}
/// <summary>
/// Deletes the family's addresses, phone numbers, photos, viewed records, and people.
/// TODO: delete attendance codes for attendance data that's about to be deleted when
/// we delete the person record.
/// </summary>
/// <param name="families">The families.</param>
/// <param name="rockContext">The rock context.</param>
private void DeleteExistingFamilyData( XElement families, RockContext rockContext )
{
PersonService personService = new PersonService( rockContext );
PhoneNumberService phoneNumberService = new PhoneNumberService( rockContext );
PersonViewedService personViewedService = new PersonViewedService( rockContext );
PageViewService pageViewService = new PageViewService( rockContext );
BinaryFileService binaryFileService = new BinaryFileService( rockContext );
PersonAliasService personAliasService = new PersonAliasService( rockContext );
PersonDuplicateService personDuplicateService = new PersonDuplicateService( rockContext );
NoteService noteService = new NoteService( rockContext );
AuthService authService = new AuthService( rockContext );
CommunicationService communicationService = new CommunicationService( rockContext );
CommunicationRecipientService communicationRecipientService = new CommunicationRecipientService( rockContext );
FinancialBatchService financialBatchService = new FinancialBatchService( rockContext );
FinancialTransactionService financialTransactionService = new FinancialTransactionService( rockContext );
PersonPreviousNameService personPreviousNameService = new PersonPreviousNameService( rockContext );
ConnectionRequestService connectionRequestService = new ConnectionRequestService( rockContext );
ConnectionRequestActivityService connectionRequestActivityService = new ConnectionRequestActivityService( rockContext );
// delete the batch data
List<int> imageIds = new List<int>();
foreach ( var batch in financialBatchService.Queryable().Where( b => b.Name.StartsWith( "SampleData" ) ) )
{
imageIds.AddRange( batch.Transactions.SelectMany( t => t.Images ).Select( i => i.BinaryFileId ).ToList() );
financialTransactionService.DeleteRange( batch.Transactions );
financialBatchService.Delete( batch );
}
// delete all transaction images
foreach ( var image in binaryFileService.GetByIds( imageIds ) )
{
binaryFileService.Delete( image );
}
foreach ( var elemFamily in families.Elements( "family" ) )
{
Guid guid = elemFamily.Attribute( "guid" ).Value.Trim().AsGuid();
GroupService groupService = new GroupService( rockContext );
Group family = groupService.Get( guid );
if ( family != null )
{
var groupMemberService = new GroupMemberService( rockContext );
var members = groupMemberService.GetByGroupId( family.Id, true );
// delete the people records
string errorMessage;
List<int> photoIds = members.Select( m => m.Person ).Where( p => p.PhotoId != null ).Select( a => (int)a.PhotoId ).ToList();
foreach ( var person in members.Select( m => m.Person ) )
{
person.GivingGroup = null;
person.GivingGroupId = null;
person.PhotoId = null;
// delete phone numbers
foreach ( var phone in phoneNumberService.GetByPersonId( person.Id ) )
{
if ( phone != null )
{
phoneNumberService.Delete( phone );
}
}
// delete communication recipient
foreach ( var recipient in communicationRecipientService.Queryable().Where( r => r.PersonAlias.PersonId == person.Id ) )
{
communicationRecipientService.Delete( recipient );
}
// delete communication
foreach ( var communication in communicationService.Queryable().Where( c => c.SenderPersonAliasId == person.PrimaryAlias.Id ) )
{
communicationService.Delete( communication );
}
// delete person viewed records
foreach ( var view in personViewedService.GetByTargetPersonId( person.Id ) )
{
personViewedService.Delete( view );
}
// delete page viewed records
foreach ( var view in pageViewService.GetByPersonId( person.Id ) )
{
pageViewService.Delete( view );
}
// delete notes created by them or on their record.
foreach ( var note in noteService.Queryable().Where ( n => n.CreatedByPersonAlias.PersonId == person.Id
|| (n.NoteType.EntityTypeId == _personEntityTypeId && n.EntityId == person.Id ) ) )
{
noteService.Delete( note );
}
// delete previous names on their records
foreach ( var previousName in personPreviousNameService.Queryable().Where( r => r.PersonAlias.PersonId == person.Id ) )
{
personPreviousNameService.Delete( previousName );
}
// delete any GroupMember records they have
foreach ( var groupMember in groupMemberService.Queryable().Where( gm => gm.PersonId == person.Id ) )
{
groupMemberService.Delete( groupMember );
}
//// delete any Authorization data
//foreach ( var auth in authService.Queryable().Where( a => a.PersonId == person.Id ) )
//{
// authService.Delete( auth );
//}
// delete their aliases
foreach ( var alias in personAliasService.Queryable().Where( a => a.PersonId == person.Id ) )
{
foreach ( var duplicate in personDuplicateService.Queryable().Where( d => d.DuplicatePersonAliasId == alias.Id ) )
{
personDuplicateService.Delete( duplicate );
}
personAliasService.Delete( alias );
}
// delete any connection requests tied to them
foreach ( var request in connectionRequestService.Queryable().Where( r => r.PersonAlias.PersonId == person.Id || r.ConnectorPersonAlias.PersonId == person.Id ) )
{
connectionRequestActivityService.DeleteRange( request.ConnectionRequestActivities );
connectionRequestService.Delete( request );
}
// Save these changes so the CanDelete passes the check...
//rockContext.ChangeTracker.DetectChanges();
rockContext.SaveChanges( disablePrePostProcessing: true );
if ( personService.CanDelete( person, out errorMessage ) )
{
personService.Delete( person );
//rockContext.ChangeTracker.DetectChanges();
//rockContext.SaveChanges( disablePrePostProcessing: true );
}
else
{
throw new Exception( string.Format( "Trying to delete {0}, but: {1}", person.FullName, errorMessage ) );
}
}
//rockContext.ChangeTracker.DetectChanges();
rockContext.SaveChanges( disablePrePostProcessing: true );
// delete all member photos
foreach ( var photo in binaryFileService.GetByIds( photoIds ) )
{
binaryFileService.Delete( photo );
}
DeleteGroupAndMemberData( family, rockContext );
}
}
}
/// <summary>
/// When implemented by a class, enables a server control to process an event raised when a form is posted to the server.
/// </summary>
/// <param name="eventArgument">A <see cref="T:System.String" /> that represents an optional event argument to be passed to the event handler.</param>
public void RaisePostBackEvent( string eventArgument )
{
if ( eventArgument == "StatusUpdate" &&
ddlAction != null &&
ddlAction.SelectedValue != null &&
!string.IsNullOrWhiteSpace( ddlAction.SelectedValue ) )
{
var batchesSelected = new List<int>();
gBatchList.SelectedKeys.ToList().ForEach( b => batchesSelected.Add( b.ToString().AsInteger() ) );
if ( batchesSelected.Any() )
{
var newStatus = ddlAction.SelectedValue == "OPEN" ? BatchStatus.Open : BatchStatus.Closed;
var rockContext = new RockContext();
var batchService = new FinancialBatchService( rockContext );
var batchesToUpdate = batchService.Queryable()
.Where( b =>
batchesSelected.Contains( b.Id ) &&
b.Status != newStatus )
.ToList();
foreach ( var batch in batchesToUpdate )
{
var changes = new List<string>();
History.EvaluateChange( changes, "Status", batch.Status, newStatus );
batch.Status = newStatus;
if ( !batch.IsValid )
{
string message = string.Format( "Unable to update status for the selected batches.<br/><br/>{0}", batch.ValidationResults.AsDelimited( "<br/>" ) );
maWarningDialog.Show( message, ModalAlertType.Warning );
return;
}
HistoryService.SaveChanges(
rockContext,
typeof( FinancialBatch ),
Rock.SystemGuid.Category.HISTORY_FINANCIAL_BATCH.AsGuid(),
batch.Id,
changes,
false );
}
rockContext.SaveChanges();
nbResult.Text = string.Format(
"{0} batches were {1}.",
batchesToUpdate.Count().ToString( "N0" ),
newStatus == BatchStatus.Open ? "opened" : "closed" );
nbResult.NotificationBoxType = NotificationBoxType.Success;
nbResult.Visible = true;
}
else
{
nbResult.Text = string.Format( "There were not any batches selected." );
nbResult.NotificationBoxType = NotificationBoxType.Warning;
nbResult.Visible = true;
}
ddlAction.SelectedIndex = 0;
BindGrid();
}
}
/// <summary>
/// Processes the confirmation.
/// </summary>
/// <param name="errorMessage">The error message.</param>
/// <returns></returns>
private bool ProcessConfirmation( out string errorMessage )
{
if ( string.IsNullOrWhiteSpace( TransactionCode ) )
{
GatewayComponent gateway = hfPaymentTab.Value == "ACH" ? _achGateway : _ccGateway;
if ( gateway == null )
{
errorMessage = "There was a problem creating the payment gateway information";
return false;
}
Person person = GetPerson( true );
if ( person == null )
{
errorMessage = "There was a problem creating the person information";
return false;
}
PaymentInfo paymentInfo = GetPaymentInfo();
if ( paymentInfo == null )
{
errorMessage = "There was a problem creating the payment information";
return false;
}
else
{
paymentInfo.FirstName = person.FirstName;
paymentInfo.LastName = person.LastName;
}
if ( paymentInfo.CreditCardTypeValue != null )
{
CreditCardTypeValueId = paymentInfo.CreditCardTypeValue.Id;
}
PaymentSchedule schedule = GetSchedule();
if ( schedule != null )
{
schedule.PersonId = person.Id;
var scheduledTransaction = gateway.AddScheduledPayment( schedule, paymentInfo, out errorMessage );
if ( scheduledTransaction != null )
{
scheduledTransaction.TransactionFrequencyValueId = schedule.TransactionFrequencyValue.Id;
scheduledTransaction.AuthorizedPersonId = person.Id;
scheduledTransaction.GatewayEntityTypeId = EntityTypeCache.Read( gateway.TypeGuid ).Id;
foreach ( var account in SelectedAccounts.Where( a => a.Amount > 0 ) )
{
var transactionDetail = new FinancialScheduledTransactionDetail();
transactionDetail.Amount = account.Amount;
transactionDetail.AccountId = account.Id;
scheduledTransaction.ScheduledTransactionDetails.Add( transactionDetail );
}
var transactionService = new FinancialScheduledTransactionService();
transactionService.Add( scheduledTransaction, CurrentPersonId );
transactionService.Save( scheduledTransaction, CurrentPersonId );
ScheduleId = scheduledTransaction.GatewayScheduleId;
TransactionCode = scheduledTransaction.TransactionCode;
}
else
{
return false;
}
}
else
{
var transaction = gateway.Charge( paymentInfo, out errorMessage );
if ( transaction != null )
{
transaction.TransactionDateTime = DateTime.Now;
transaction.AuthorizedPersonId = person.Id;
transaction.GatewayEntityTypeId = gateway.TypeId;
transaction.Amount = paymentInfo.Amount;
transaction.TransactionTypeValueId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION)).Id;
transaction.CurrencyTypeValueId = paymentInfo.CurrencyTypeValue.Id;
transaction.CreditCardTypeValueId = CreditCardTypeValueId;
Guid sourceGuid = Guid.Empty;
if (Guid.TryParse(GetAttributeValue("Source"), out sourceGuid))
{
transaction.SourceTypeValueId = DefinedValueCache.Read(sourceGuid).Id;
}
foreach ( var account in SelectedAccounts.Where( a => a.Amount > 0 ) )
{
var transactionDetail = new FinancialTransactionDetail();
transactionDetail.Amount = account.Amount;
transactionDetail.AccountId = account.Id;
transaction.TransactionDetails.Add( transactionDetail );
}
// Get the batch name
string ccSuffix = string.Empty;
if ( paymentInfo.CreditCardTypeValue != null )
{
ccSuffix = paymentInfo.CreditCardTypeValue.GetAttributeValue( "BatchNameSuffix" );
}
if ( string.IsNullOrWhiteSpace( ccSuffix ) )
{
ccSuffix = paymentInfo.CurrencyTypeValue.Name;
}
string batchName = GetAttributeValue( "BatchNamePrefix" ).Trim() + " " + ccSuffix;
using ( new UnitOfWorkScope() )
{
var batchService = new FinancialBatchService();
var batch = batchService.Queryable()
.Where( b =>
b.Status == BatchStatus.Open &&
b.BatchStartDateTime <= transaction.TransactionDateTime &&
b.BatchEndDateTime > transaction.TransactionDateTime &&
b.Name == batchName )
.FirstOrDefault();
if ( batch == null )
{
batch = new FinancialBatch();
batch.Name = batchName;
batch.Status = BatchStatus.Open;
batch.BatchStartDateTime = transaction.TransactionDateTime.Value.Date.Add( gateway.BatchTimeOffset );
if ( batch.BatchStartDateTime > transaction.TransactionDateTime )
{
batch.BatchStartDateTime.Value.AddDays( -1 );
}
batch.BatchEndDateTime = batch.BatchStartDateTime.Value.AddDays( 1 ).AddMilliseconds( -1 );
batch.CreatedByPersonId = person.Id;
batchService.Add( batch, CurrentPersonId );
batchService.Save( batch, CurrentPersonId );
batch = batchService.Get( batch.Id );
}
batch.ControlAmount += transaction.Amount;
batchService.Save( batch, CurrentPersonId );
var transactionService = new FinancialTransactionService();
transaction.BatchId = batch.Id;
transactionService.Add( transaction, CurrentPersonId );
transactionService.Save( transaction, CurrentPersonId );
}
TransactionCode = transaction.TransactionCode;
}
else
{
return false;
}
}
tdTransactionCode.Description = TransactionCode;
tdTransactionCode.Visible = !string.IsNullOrWhiteSpace( TransactionCode );
tdScheduleId.Description = ScheduleId;
tdScheduleId.Visible = !string.IsNullOrWhiteSpace( ScheduleId );
// If there was a transaction code returned and this was not already created from a previous saved account,
// show the option to save the account.
if ( !( paymentInfo is ReferencePaymentInfo ) && !string.IsNullOrWhiteSpace( TransactionCode ) )
{
cbSaveAccount.Visible = true;
pnlSaveAccount.Visible = true;
txtSaveAccount.Visible = true;
// If current person does not have a login, have them create a username and password
phCreateLogin.Visible = !new UserLoginService().GetByPersonId( person.Id ).Any();
}
else
{
pnlSaveAccount.Visible = false;
}
return true;
}
else
{
pnlDupWarning.Visible = true;
errorMessage = string.Empty;
return false;
}
}
/// <summary>
/// Gets the query. Set the timeout to 90 seconds in case the user
/// has not set any filters and they've imported N years worth of
/// batch data into Rock.
/// </summary>
/// <returns></returns>
private IOrderedQueryable<FinancialBatch> GetQuery()
{
var rockContext = new RockContext();
var batchService = new FinancialBatchService( rockContext );
rockContext.Database.CommandTimeout = 90;
var qry = batchService.Queryable()
.Where( b => b.BatchStartDateTime.HasValue );
// filter by date
string dateRangeValue = gfBatchFilter.GetUserPreference( "Date Range" );
if ( !string.IsNullOrWhiteSpace( dateRangeValue ) )
{
var drp = new DateRangePicker();
drp.DelimitedValues = dateRangeValue;
if ( drp.LowerValue.HasValue )
{
qry = qry.Where( b => b.BatchStartDateTime >= drp.LowerValue.Value );
}
if ( drp.UpperValue.HasValue )
{
var endOfDay = drp.UpperValue.Value.AddDays( 1 );
qry = qry.Where( b => b.BatchStartDateTime < endOfDay );
}
}
// filter by status
var status = gfBatchFilter.GetUserPreference( "Status" ).ConvertToEnumOrNull<BatchStatus>();
if ( status.HasValue )
{
qry = qry.Where( b => b.Status == status );
}
// filter by batches that contain transactions of the specified transaction type
var transactionTypeValueId = gfBatchFilter.GetUserPreference( "Contains Transaction Type" ).AsIntegerOrNull();
if ( transactionTypeValueId.HasValue )
{
qry = qry.Where( a => a.Transactions.Any( t => t.TransactionTypeValueId == transactionTypeValueId.Value ) );
}
// filter by title
string title = gfBatchFilter.GetUserPreference( "Title" );
if ( !string.IsNullOrEmpty( title ) )
{
qry = qry.Where( batch => batch.Name.StartsWith( title ) );
}
// filter by accounting code
if ( tbAccountingCode.Visible )
{
string accountingCode = gfBatchFilter.GetUserPreference( "Accounting Code" );
if ( !string.IsNullOrEmpty( accountingCode ) )
{
qry = qry.Where( batch => batch.AccountingSystemCode.StartsWith( accountingCode ) );
}
}
// filter by campus
var campus = CampusCache.Read( gfBatchFilter.GetUserPreference( "Campus" ).AsInteger() );
if ( campus != null )
{
qry = qry.Where( b => b.CampusId == campus.Id );
}
IOrderedQueryable<FinancialBatch> sortedQry = null;
SortProperty sortProperty = gBatchList.SortProperty;
if ( sortProperty != null )
{
switch ( sortProperty.Property )
{
case "TransactionCount":
{
if ( sortProperty.Direction == SortDirection.Ascending )
{
sortedQry = qry.OrderBy( b => b.Transactions.Count() );
}
else
{
sortedQry = qry.OrderByDescending( b => b.Transactions.Count() );
}
break;
}
case "TransactionAmount":
{
if ( sortProperty.Direction == SortDirection.Ascending )
{
sortedQry = qry.OrderBy( b => b.Transactions.Sum( t => (decimal?)( t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M ) ) ?? 0.0M );
}
else
{
sortedQry = qry.OrderByDescending( b => b.Transactions.Sum( t => (decimal?)( t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M ) ) ?? 0.0M );
}
break;
}
default:
{
sortedQry = qry.Sort( sortProperty );
break;
}
}
}
else
{
sortedQry = qry
.OrderByDescending( b => b.BatchStartDateTime )
.ThenBy( b => b.Name );
}
return sortedQry;
}