/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override System.Linq.Expressions.Expression GetExpression( Data.RockContext context, System.Linq.Expressions.MemberExpression entityIdProperty, string selection )
{
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( 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 FinancialTransactionDetailService( context ).Queryable()
.Where( xx => xx.Transaction.TransactionTypeValueId == transactionTypeContributionId )
.Where( xx => xx.Transaction.AuthorizedPersonAliasId.HasValue );
if ( startDate.HasValue )
{
financialTransactionQry = financialTransactionQry.Where( xx => xx.Transaction.TransactionDateTime >= startDate.Value );
}
if ( endDate.HasValue )
{
financialTransactionQry = financialTransactionQry.Where( xx => xx.Transaction.TransactionDateTime < endDate.Value );
}
bool limitToAccounts = accountIdList.Any();
if ( limitToAccounts )
{
financialTransactionQry = financialTransactionQry.Where( xx => accountIdList.Contains( xx.AccountId ) );
}
if ( comparisonType == ComparisonType.LessThan )
{
financialTransactionQry = financialTransactionQry.Where( xx => xx.Amount < amount );
}
else if ( comparisonType == ComparisonType.EqualTo )
{
financialTransactionQry = financialTransactionQry.Where( xx => xx.Amount == amount );
}
else if ( comparisonType == ComparisonType.GreaterThanOrEqualTo )
{
financialTransactionQry = financialTransactionQry.Where( xx => xx.Amount >= amount );
}
IQueryable<decimal> personTotalAmountQry;
if ( combineGiving )
{
//// if combineGiving..
// if they aren't in a giving group, sum up transactions amounts by the person
// if they are in a giving group, sum up transactions amounts by the persons that are in the person's giving group
personTotalAmountQry = new PersonService( context ).Queryable()
.Select( p => financialTransactionQry
.Where( ww =>
( !p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id )
||
( p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.Person.GivingGroupId == p.GivingGroupId ) )
.Sum( aa => aa.Amount ) );
}
else
{
personTotalAmountQry = new PersonService( context ).Queryable()
.Select( p => financialTransactionQry
.Where( ww => ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id )
.Sum( aa => aa.Amount ) );
}
var selectExpression = SelectExpressionExtractor.Extract<Rock.Model.Person>( personTotalAmountQry, entityIdProperty, "p" );
return selectExpression;
}
/// <summary>
/// Handles the Click event of the lbSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void lbSave_Click( object sender, EventArgs e )
{
var rockContext = new RockContext();
var txnService = new FinancialTransactionService( rockContext );
var txnDetailService = new FinancialTransactionDetailService( rockContext );
var txnImageService = new FinancialTransactionImageService( rockContext );
var binaryFileService = new BinaryFileService( rockContext );
FinancialTransaction txn = null;
int? txnId = hfTransactionId.Value.AsIntegerOrNull();
int? batchId = hfBatchId.Value.AsIntegerOrNull();
if ( txnId.HasValue )
{
txn = txnService.Get( txnId.Value );
}
if ( txn == null )
{
txn = new FinancialTransaction();
txnService.Add( txn );
txn.BatchId = batchId;
}
if ( txn != null )
{
if ( ppAuthorizedPerson.PersonId.HasValue )
{
txn.AuthorizedPersonAliasId = ppAuthorizedPerson.PersonAliasId;
}
txn.TransactionDateTime = dtTransactionDateTime.SelectedDateTime;
txn.TransactionTypeValueId = ddlTransactionType.SelectedValue.AsInteger();
txn.SourceTypeValueId = ddlSourceType.SelectedValueAsInt();
Guid? gatewayGuid = cpPaymentGateway.SelectedValueAsGuid();
if ( gatewayGuid.HasValue )
{
var gatewayEntity = EntityTypeCache.Read( gatewayGuid.Value );
if ( gatewayEntity != null )
{
txn.GatewayEntityTypeId = gatewayEntity.Id;
}
else
{
txn.GatewayEntityTypeId = null;
}
}
else
{
txn.GatewayEntityTypeId = null;
}
txn.TransactionCode = tbTransactionCode.Text;
txn.CurrencyTypeValueId = ddlCurrencyType.SelectedValueAsInt();
txn.CreditCardTypeValueId = ddlCreditCardType.SelectedValueAsInt();
txn.Summary = tbSummary.Text;
if ( !Page.IsValid || !txn.IsValid )
{
return;
}
foreach ( var txnDetail in TransactionDetailsState )
{
if ( !txnDetail.IsValid )
{
return;
}
}
rockContext.WrapTransaction( () =>
{
// Save the transaction
rockContext.SaveChanges();
// Delete any transaction details that were removed
var txnDetailsInDB = txnDetailService.Queryable().Where( a => a.TransactionId.Equals( txn.Id ) ).ToList();
var deletedDetails = from txnDetail in txnDetailsInDB
where !TransactionDetailsState.Select( d => d.Guid ).Contains( txnDetail.Guid )
select txnDetail;
deletedDetails.ToList().ForEach( txnDetail =>
{
txnDetailService.Delete( txnDetail );
} );
rockContext.SaveChanges();
// Save Transaction Details
foreach ( var editorTxnDetail in TransactionDetailsState )
{
// Add or Update the activity type
var txnDetail = txn.TransactionDetails.FirstOrDefault( d => d.Guid.Equals( editorTxnDetail.Guid ) );
if ( txnDetail == null )
{
txnDetail = new FinancialTransactionDetail();
txnDetail.Guid = editorTxnDetail.Guid;
txn.TransactionDetails.Add( txnDetail );
}
txnDetail.AccountId = editorTxnDetail.AccountId;
txnDetail.Amount = UseSimpleAccountMode ? tbSingleAccountAmount.Text.AsDecimal() : editorTxnDetail.Amount;
txnDetail.Summary = editorTxnDetail.Summary;
}
rockContext.SaveChanges();
// Delete any transaction images that were removed
var orphanedBinaryFileIds = new List<int>();
var txnImagesInDB = txnImageService.Queryable().Where( a => a.TransactionId.Equals( txn.Id ) ).ToList();
foreach ( var txnImage in txnImagesInDB.Where( i => !TransactionImagesState.Contains( i.BinaryFileId ) ) )
{
orphanedBinaryFileIds.Add( txnImage.BinaryFileId );
txnImageService.Delete( txnImage );
}
// Save Transaction Images
int imageOrder = 0;
foreach ( var binaryFileId in TransactionImagesState )
{
// Add or Update the activity type
var txnImage = txnImagesInDB.FirstOrDefault( i => i.BinaryFileId == binaryFileId );
if ( txnImage == null )
{
txnImage = new FinancialTransactionImage();
txnImage.TransactionId = txn.Id;
txn.Images.Add( txnImage );
}
txnImage.BinaryFileId = binaryFileId;
txnImage.Order = imageOrder;
imageOrder++;
}
rockContext.SaveChanges();
// Make sure updated binary files are not temporary
foreach ( var binaryFile in binaryFileService.Queryable().Where( f => TransactionImagesState.Contains( f.Id ) ) )
{
binaryFile.IsTemporary = false;
}
// Delete any orphaned images
foreach ( var binaryFile in binaryFileService.Queryable().Where( f => orphanedBinaryFileIds.Contains( f.Id ) ) )
{
binaryFileService.Delete( binaryFile );
}
rockContext.SaveChanges();
} );
// Save selected options to session state in order to prefill values for next added txn
Session["NewTxnDefault_BatchId"] = txn.BatchId;
Session["NewTxnDefault_TransactionDateTime"] = txn.TransactionDateTime;
Session["NewTxnDefault_TransactionType"] = txn.TransactionTypeValueId;
Session["NewTxnDefault_SourceType"] = txn.SourceTypeValueId;
Session["NewTxnDefault_CurrencyType"] = txn.CurrencyTypeValueId;
Session["NewTxnDefault_CreditCardType"] = txn.CreditCardTypeValueId;
if ( TransactionDetailsState.Count() == 1 )
{
Session["NewTxnDefault_Account"] = TransactionDetailsState.First().AccountId;
}
else
{
Session.Remove("NewTxnDefault_Account");
}
// Requery the batch to support EF navigation properties
var savedTxn = GetTransaction( txn.Id );
ShowReadOnlyDetails( savedTxn );
}
}
private void BuildDynamicControls()
{
// Get all the accounts grouped by campus
if ( _campusAccounts == null )
{
using ( var rockContext = new RockContext() )
{
_campusAccounts = new Dictionary<int, Dictionary<int, string>>();
Guid contributionGuid = Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid();
var contributionAccountIds = new FinancialTransactionDetailService( rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.Transaction != null &&
d.Transaction.TransactionTypeValue != null &&
d.Transaction.TransactionTypeValue.Guid.Equals( contributionGuid ) )
.Select( d => d.AccountId )
.Distinct()
.ToList();
foreach ( var campusAccounts in new FinancialAccountService( rockContext )
.Queryable().AsNoTracking()
.Where( a =>
a.IsActive &&
contributionAccountIds.Contains( a.Id ) )
.GroupBy( a => a.CampusId ?? 0 )
.Select( c => new
{
CampusId = c.Key,
Accounts = c.OrderBy( a => a.Name ).Select( a => new { a.Id, a.Name } ).ToList()
} ) )
{
_campusAccounts.Add( campusAccounts.CampusId, new Dictionary<int, string>() );
foreach ( var account in campusAccounts.Accounts )
{
_campusAccounts[campusAccounts.CampusId].Add( account.Id, account.Name );
}
}
}
}
phAccounts.Controls.Clear();
foreach( var campusId in _campusAccounts )
{
var cbList = new RockCheckBoxList();
cbList.ID = "cblAccounts" + campusId.Key.ToString();
if ( campusId.Key > 0)
{
var campus = CampusCache.Read( campusId.Key );
cbList.Label = campus != null ? campus.Name + " Accounts" : "Campus " + campusId.Key.ToString();
}
else
{
cbList.Label = "Accounts";
}
cbList.RepeatDirection = RepeatDirection.Vertical;
cbList.DataValueField = "Key";
cbList.DataTextField = "Value";
cbList.DataSource = campusId.Value;
cbList.DataBind();
phAccounts.Controls.Add( cbList );
}
}
private IEnumerable<Rock.Chart.IChartData> GetChartData()
{
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
var currenceTypeIds = new List<int>();
cblCurrencyTypes.SelectedValues.ForEach( i => currenceTypeIds.Add( i.AsInteger() ) );
var sourceIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
var chartData = new FinancialTransactionDetailService( _rockContext ).GetChartData(
hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week,
hfGraphBy.Value.ConvertToEnumOrNull<TransactionGraphBy>() ?? TransactionGraphBy.Total,
dateRange.Start,
dateRange.End,
nreAmount.LowerValue,
nreAmount.UpperValue,
currenceTypeIds,
sourceIds,
accountIds,
dvpDataView.SelectedValueAsInt() );
return chartData;
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var rockContext = new RockContext();
var transactionIdsQry = new FinancialTransactionService( rockContext ).Queryable();
var transactionDetailService = new FinancialTransactionDetailService( rockContext );
var qry = transactionDetailService.Queryable();
var targetPerson = this.ContextEntity<Person>();
if ( targetPerson != null )
{
if ( targetPerson.GivingGroupId.HasValue )
{
// gives as part of giving group
var groupMemberQry = new GroupMemberService( rockContext ).GetByGroupId( targetPerson.GivingGroupId.Value );
qry = qry.Where( a => groupMemberQry.Any( b => b.PersonId == a.Transaction.AuthorizedPersonAlias.PersonId ) );
}
else
{
// gives individually
qry = qry.Where( a => a.Transaction.AuthorizedPersonAlias.PersonId == targetPerson.Id );
}
}
var qryTransactionsDetailsByYear = qry
.Where( a => a.Transaction.TransactionDateTime.HasValue )
.GroupBy( a => SqlFunctions.DatePart( "year", a.Transaction.TransactionDateTime.Value ) );
var qryAccountSummariesByYear = qryTransactionsDetailsByYear.Select( a => new
{
Year = a.Key,
Accounts = a.GroupBy( b => b.Account ).Select( c => new
{
Account = c.Key,
TotalAmount = c.Sum( d => d.Amount )
} ).OrderBy( e => e.Account.Name )
} ).OrderByDescending( a => a.Year );
var summaryList = qryAccountSummariesByYear.ToList();
var mergeObjects = GlobalAttributesCache.GetMergeFields( this.CurrentPerson );
var yearsMergeObjects = new List<Dictionary<string, object>>();
foreach ( var item in summaryList )
{
var accountsList = new List<object>();
foreach ( var a in item.Accounts )
{
var accountDictionary = new Dictionary<string, object>();
accountDictionary.Add( "Account", a.Account );
accountDictionary.Add( "TotalAmount", a.TotalAmount );
accountsList.Add( accountDictionary );
}
var yearDictionary = new Dictionary<string, object>();
yearDictionary.Add( "Year", item.Year );
yearDictionary.Add( "SummaryRows", accountsList );
yearsMergeObjects.Add( yearDictionary );
}
mergeObjects.Add( "Rows", yearsMergeObjects );
lLavaOutput.Text = string.Empty;
if ( GetAttributeValue( "EnableDebug" ).AsBooleanOrNull().GetValueOrDefault( false ) )
{
lLavaOutput.Text = mergeObjects.lavaDebugInfo( rockContext );
}
string template = GetAttributeValue( "LavaTemplate" );
lLavaOutput.Text += template.ResolveMergeFields( mergeObjects ).ResolveClientIds( upnlContent.ClientID );
}
/// <summary>
/// Handles the RowSelected event of the gTransactionDetails control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="Rock.Web.UI.Controls.RowEventArgs"/> instance containing the event data.</param>
protected void gTransactionDetails_RowSelected( object sender, Rock.Web.UI.Controls.RowEventArgs e )
{
if ( !readOnly )
{
var rockContext = new RockContext();
LoadRelatedImages( new FinancialTransactionService( rockContext ).Get( hfIdTransValue.ValueAsInt() ).Id );
var transactionDetailsId = (int)e.RowKeyValue;
hfIdValue.Value = transactionDetailsId.ToString();
var ftd = new FinancialTransactionDetailService( rockContext ).Get( transactionDetailsId );
ddlTransactionAccount.SelectedValue = ftd.AccountId.ToString();
tbTransactionAmount.Text = ftd.Amount.ToString();
tbTransactionSummary.Text = ftd.Summary;
mdDetails.Show();
}
}
/// <summary>
/// Handles the Click event of the lbSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void lbSave_Click( object sender, EventArgs e )
{
var rockContext = new RockContext();
var txnService = new FinancialTransactionService( rockContext );
var txnDetailService = new FinancialTransactionDetailService( rockContext );
var txnImageService = new FinancialTransactionImageService( rockContext );
var binaryFileService = new BinaryFileService( rockContext );
FinancialTransaction txn = null;
int? txnId = hfTransactionId.Value.AsIntegerOrNull();
int? batchId = hfBatchId.Value.AsIntegerOrNull();
var changes = new List<string>();
if ( txnId.HasValue )
{
txn = txnService.Get( txnId.Value );
}
if ( txn == null )
{
txn = new FinancialTransaction();
txnService.Add( txn );
txn.BatchId = batchId;
changes.Add( "Created transaction" );
}
if ( txn != null )
{
if ( txn.FinancialPaymentDetail == null )
{
txn.FinancialPaymentDetail = new FinancialPaymentDetail();
}
string newPerson = ppAuthorizedPerson.PersonName;
if ( batchId.HasValue )
{
if ( !txn.AuthorizedPersonAliasId.Equals( ppAuthorizedPerson.PersonAliasId ) )
{
string prevPerson = ( txn.AuthorizedPersonAlias != null && txn.AuthorizedPersonAlias.Person != null ) ?
txn.AuthorizedPersonAlias.Person.FullName : string.Empty;
History.EvaluateChange( changes, "Person", prevPerson, newPerson );
}
History.EvaluateChange( changes, "Date/Time", txn.TransactionDateTime, dtTransactionDateTime.SelectedDateTime );
History.EvaluateChange( changes, "Type", GetDefinedValue( txn.TransactionTypeValueId ), GetDefinedValue( ddlTransactionType.SelectedValue.AsInteger() ) );
History.EvaluateChange( changes, "Source", GetDefinedValue( txn.SourceTypeValueId ), GetDefinedValue( ddlSourceType.SelectedValueAsInt() ) );
if ( !txn.FinancialGatewayId.Equals( gpPaymentGateway.SelectedValueAsInt() ) )
{
History.EvaluateChange( changes, "Gateway", GetFinancialGatewayName( txn.FinancialGatewayId, rockContext ), GetFinancialGatewayName( gpPaymentGateway.SelectedValueAsInt(), rockContext ) );
}
History.EvaluateChange( changes, "Transaction Code", txn.TransactionCode, tbTransactionCode.Text );
History.EvaluateChange( changes, "Currency Type", GetDefinedValue( txn.FinancialPaymentDetail.CurrencyTypeValueId ), GetDefinedValue( ddlCurrencyType.SelectedValueAsInt() ) );
History.EvaluateChange( changes, "Credit Card Type", GetDefinedValue( txn.FinancialPaymentDetail.CreditCardTypeValueId ), GetDefinedValue( ddlCreditCardType.SelectedValueAsInt() ) );
History.EvaluateChange( changes, "Summary", txn.Summary, tbSummary.Text );
History.EvaluateChange( changes, "Is Refund", ( txn.RefundDetails != null ), cbIsRefund.Checked );
}
txn.AuthorizedPersonAliasId = ppAuthorizedPerson.PersonAliasId;
txn.TransactionDateTime = dtTransactionDateTime.SelectedDateTime;
txn.TransactionTypeValueId = ddlTransactionType.SelectedValue.AsInteger();
txn.SourceTypeValueId = ddlSourceType.SelectedValueAsInt();
txn.FinancialGatewayId = gpPaymentGateway.SelectedValueAsInt();
txn.TransactionCode = tbTransactionCode.Text;
txn.FinancialPaymentDetail.CurrencyTypeValueId = ddlCurrencyType.SelectedValueAsInt();
txn.FinancialPaymentDetail.CreditCardTypeValueId = ddlCreditCardType.SelectedValueAsInt();
txn.Summary = tbSummary.Text;
decimal totalAmount = TransactionDetailsState.Select( d => d.Amount ).ToList().Sum();
if ( cbIsRefund.Checked && totalAmount > 0 )
{
nbErrorMessage.Title = "Incorrect Refund Amount";
nbErrorMessage.Text = "<p>A refund should have a negative amount. Please unselect the refund option, or change amounts to be negative values.</p>";
nbErrorMessage.Visible = true;
return;
}
if ( cbIsRefund.Checked )
{
if ( txn.RefundDetails != null )
{
txn.RefundDetails = new FinancialTransactionRefund();
}
txn.RefundDetails.RefundReasonValueId = ddlRefundReasonEdit.SelectedValueAsId();
txn.RefundDetails.RefundReasonSummary = tbRefundSummaryEdit.Text;
}
if ( !Page.IsValid || !txn.IsValid )
{
return;
}
foreach ( var txnDetail in TransactionDetailsState )
{
if ( !txnDetail.IsValid )
{
return;
}
}
rockContext.WrapTransaction( () =>
{
// Save the transaction
rockContext.SaveChanges();
// Delete any transaction details that were removed
var txnDetailsInDB = txnDetailService.Queryable().Where( a => a.TransactionId.Equals( txn.Id ) ).ToList();
var deletedDetails = from txnDetail in txnDetailsInDB
where !TransactionDetailsState.Select( d => d.Guid ).Contains( txnDetail.Guid )
select txnDetail;
deletedDetails.ToList().ForEach( txnDetail =>
{
if ( batchId.HasValue )
{
History.EvaluateChange( changes, txnDetail.Account != null ? txnDetail.Account.Name : "Unknown", txnDetail.Amount.FormatAsCurrency(), string.Empty );
}
txnDetailService.Delete( txnDetail );
} );
// Save Transaction Details
foreach ( var editorTxnDetail in TransactionDetailsState )
{
string oldAccountName = string.Empty;
string newAccountName = string.Empty;
decimal oldAmount = 0.0M;
decimal newAmount = 0.0M;
// Add or Update the activity type
var txnDetail = txn.TransactionDetails.FirstOrDefault( d => d.Guid.Equals( editorTxnDetail.Guid ) );
if ( txnDetail != null )
{
oldAccountName = AccountName( txnDetail.AccountId );
oldAmount = txnDetail.Amount;
}
else
{
txnDetail = new FinancialTransactionDetail();
txnDetail.Guid = editorTxnDetail.Guid;
txn.TransactionDetails.Add( txnDetail );
}
newAccountName = AccountName( editorTxnDetail.AccountId );
newAmount = UseSimpleAccountMode ? tbSingleAccountAmount.Text.AsDecimal() : editorTxnDetail.Amount;
if ( batchId.HasValue )
{
if ( string.IsNullOrWhiteSpace(oldAccountName) )
{
History.EvaluateChange( changes, newAccountName, string.Empty, newAmount.FormatAsCurrency() );
}
else
{
if ( oldAccountName == newAccountName )
{
if ( oldAmount != newAmount )
{
History.EvaluateChange( changes, oldAccountName, oldAmount.FormatAsCurrency(), newAmount.FormatAsCurrency() );
}
}
else
{
History.EvaluateChange( changes, oldAccountName, oldAmount.FormatAsCurrency(), string.Empty );
History.EvaluateChange( changes, newAccountName, string.Empty, newAmount.FormatAsCurrency() );
}
}
}
txnDetail.AccountId = editorTxnDetail.AccountId;
txnDetail.Amount = newAmount;
txnDetail.Summary = editorTxnDetail.Summary;
if ( editorTxnDetail.AttributeValues != null )
{
txnDetail.LoadAttributes();
txnDetail.AttributeValues = editorTxnDetail.AttributeValues;
rockContext.SaveChanges();
txnDetail.SaveAttributeValues( rockContext );
}
}
// Delete any transaction images that were removed
var orphanedBinaryFileIds = new List<int>();
var txnImagesInDB = txnImageService.Queryable().Where( a => a.TransactionId.Equals( txn.Id ) ).ToList();
foreach ( var txnImage in txnImagesInDB.Where( i => !TransactionImagesState.Contains( i.BinaryFileId ) ) )
{
changes.Add( "Removed Image" );
orphanedBinaryFileIds.Add( txnImage.BinaryFileId );
txnImageService.Delete( txnImage );
}
// Save Transaction Images
int imageOrder = 0;
foreach ( var binaryFileId in TransactionImagesState )
{
// Add or Update the activity type
var txnImage = txnImagesInDB.FirstOrDefault( i => i.BinaryFileId == binaryFileId );
if ( txnImage == null )
{
changes.Add( "Added Image" );
txnImage = new FinancialTransactionImage();
txnImage.TransactionId = txn.Id;
txn.Images.Add( txnImage );
}
else
{
if ( txnImage.BinaryFileId != binaryFileId )
{
changes.Add( "Updated Image" );
}
}
txnImage.BinaryFileId = binaryFileId;
txnImage.Order = imageOrder;
imageOrder++;
}
rockContext.SaveChanges();
// Make sure updated binary files are not temporary
foreach ( var binaryFile in binaryFileService.Queryable().Where( f => TransactionImagesState.Contains( f.Id ) ) )
{
binaryFile.IsTemporary = false;
}
// Delete any orphaned images
foreach ( var binaryFile in binaryFileService.Queryable().Where( f => orphanedBinaryFileIds.Contains( f.Id ) ) )
{
binaryFileService.Delete( binaryFile );
}
// Update any attributes
txn.LoadAttributes(rockContext);
txn.FinancialPaymentDetail.LoadAttributes(rockContext);
Helper.GetEditValues(phAttributeEdits, txn);
Helper.GetEditValues(phPaymentAttributeEdits, txn.FinancialPaymentDetail);
// If the transaction is associated with a batch, update that batch's history
if ( batchId.HasValue )
{
HistoryService.SaveChanges(
rockContext,
typeof( FinancialBatch ),
Rock.SystemGuid.Category.HISTORY_FINANCIAL_TRANSACTION.AsGuid(),
batchId.Value,
changes,
!string.IsNullOrWhiteSpace( newPerson ) ? newPerson : string.Format( "Transaction Id:{0}", txn.Id ),
typeof( FinancialTransaction ),
txn.Id
);
}
rockContext.SaveChanges();
txn.SaveAttributeValues(rockContext);
txn.FinancialPaymentDetail.SaveAttributeValues(rockContext);
} );
// Save selected options to session state in order to prefill values for next added txn
Session["NewTxnDefault_BatchId"] = txn.BatchId;
Session["NewTxnDefault_TransactionDateTime"] = txn.TransactionDateTime;
Session["NewTxnDefault_TransactionType"] = txn.TransactionTypeValueId;
Session["NewTxnDefault_SourceType"] = txn.SourceTypeValueId;
Session["NewTxnDefault_CurrencyType"] = txn.FinancialPaymentDetail.CurrencyTypeValueId;
Session["NewTxnDefault_CreditCardType"] = txn.FinancialPaymentDetail.CreditCardTypeValueId;
if ( TransactionDetailsState.Count() == 1 )
{
Session["NewTxnDefault_Account"] = TransactionDetailsState.First().AccountId;
}
else
{
Session.Remove("NewTxnDefault_Account");
}
// Requery the batch to support EF navigation properties
var savedTxn = GetTransaction( txn.Id );
if ( savedTxn != null )
{
savedTxn.LoadAttributes();
savedTxn.FinancialPaymentDetail.LoadAttributes();
ShowReadOnlyDetails( savedTxn );
}
}
}
/// <summary>
/// Handles the Click event of the btnNext control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnNext_Click( object sender, EventArgs e )
{
var rockContext = new RockContext();
var financialTransactionService = new FinancialTransactionService( rockContext );
var financialTransactionDetailService = new FinancialTransactionDetailService( rockContext );
var financialPersonBankAccountService = new FinancialPersonBankAccountService( rockContext );
int txnId = hfTransactionId.Value.AsInteger();
var financialTransaction = financialTransactionService
.Queryable( "AuthorizedPersonAlias.Person,ProcessedByPersonAlias.Person" )
.FirstOrDefault( t => t.Id == txnId );
// set the AuthorizedPersonId (the person who wrote the check, for example) to the if the SelectNew person (if selected) or person selected in the drop down (if there is somebody selected)
int? authorizedPersonId = ppSelectNew.PersonId ?? ddlIndividual.SelectedValue.AsIntegerOrNull();
var accountNumberSecured = hfCheckMicrHashed.Value;
if ( cbTotalAmount.Text.AsDecimalOrNull().HasValue && !authorizedPersonId.HasValue )
{
nbSaveError.Text = "Transaction must be matched to a person when the amount is specified.";
nbSaveError.Visible = true;
return;
}
// if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction and clear out the detail records (we don't want an unmatched transaction to have detail records)
if ( financialTransaction != null &&
financialTransaction.AuthorizedPersonAliasId.HasValue &&
!authorizedPersonId.HasValue )
{
financialTransaction.AuthorizedPersonAliasId = null;
foreach ( var detail in financialTransaction.TransactionDetails )
{
financialTransactionDetailService.Delete( detail );
}
rockContext.SaveChanges();
// if the transaction was unmatched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser( hfTransactionId.Value.AsInteger() );
}
// if the transaction is matched to somebody, attempt to save it. Otherwise, if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction
if ( financialTransaction != null && authorizedPersonId.HasValue )
{
bool requiresMicr = financialTransaction.CurrencyTypeValue.Guid == Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK.AsGuid();
if ( requiresMicr && string.IsNullOrWhiteSpace( accountNumberSecured ) )
{
// should be showing already, but just in case
nbNoMicrWarning.Visible = true;
return;
}
if ( cbTotalAmount.Text.AsDecimalOrNull() == null )
{
nbSaveError.Text = "Total amount must be allocated to accounts.";
nbSaveError.Visible = true;
return;
}
int? personAliasId = new PersonAliasService( rockContext ).GetPrimaryAliasId( authorizedPersonId.Value );
// if this transaction has an accountnumber associated with it (in other words, it's a scanned check), ensure there is a financialPersonBankAccount record
if ( !string.IsNullOrWhiteSpace( accountNumberSecured ) )
{
var financialPersonBankAccount = financialPersonBankAccountService.Queryable().Where( a => a.AccountNumberSecured == accountNumberSecured && a.PersonAlias.PersonId == authorizedPersonId.Value ).FirstOrDefault();
if ( financialPersonBankAccount == null )
{
if ( personAliasId.HasValue )
{
financialPersonBankAccount = new FinancialPersonBankAccount();
financialPersonBankAccount.PersonAliasId = personAliasId.Value;
financialPersonBankAccount.AccountNumberSecured = accountNumberSecured;
var checkMicrClearText = Encryption.DecryptString( financialTransaction.CheckMicrEncrypted );
var parts = checkMicrClearText.Split( '_' );
if ( parts.Length >= 2 )
{
financialPersonBankAccount.AccountNumberMasked = parts[1].Masked();
}
financialPersonBankAccountService.Add( financialPersonBankAccount );
}
}
}
if ( personAliasId.HasValue )
{
financialTransaction.AuthorizedPersonAliasId = personAliasId;
}
// just in case this transaction is getting re-edited either by the same user, or somebody else, clean out any existing TransactionDetail records
foreach ( var detail in financialTransaction.TransactionDetails.ToList() )
{
financialTransactionDetailService.Delete( detail );
}
foreach ( var accountBox in rptAccounts.ControlsOfTypeRecursive<CurrencyBox>() )
{
var amount = accountBox.Text.AsDecimalOrNull();
if ( amount.HasValue && amount.Value >= 0 )
{
var financialTransactionDetail = new FinancialTransactionDetail();
financialTransactionDetail.TransactionId = financialTransaction.Id;
financialTransactionDetail.AccountId = accountBox.Attributes["data-account-id"].AsInteger();
financialTransactionDetail.Amount = amount.Value;
financialTransactionDetailService.Add( financialTransactionDetail );
}
}
financialTransaction.ProcessedByPersonAliasId = this.CurrentPersonAlias.Id;
financialTransaction.ProcessedDateTime = RockDateTime.Now;
rockContext.SaveChanges();
}
else
{
// if the transaction was not matched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser( hfTransactionId.Value.AsInteger() );
}
NavigateToTransaction( Direction.Next );
}
/// <summary>
/// Binds the chart attendance grid.
/// </summary>
private void BindChartAmountGrid()
{
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
var currenceTypeIds = new List<int>();
cblCurrencyTypes.SelectedValues.ForEach( i => currenceTypeIds.Add( i.AsInteger() ) );
var sourceIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
SortProperty sortProperty = gChartAmount.SortProperty;
var chartData = new FinancialTransactionDetailService( _rockContext ).GetChartData(
hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week,
hfGraphBy.Value.ConvertToEnumOrNull<TransactionGraphBy>() ?? TransactionGraphBy.Total,
dateRange.Start,
dateRange.End,
nreAmount.LowerValue,
nreAmount.UpperValue,
currenceTypeIds,
sourceIds,
accountIds,
dvpDataView.SelectedValueAsInt() );
if ( sortProperty != null )
{
gChartAmount.DataSource = chartData.AsQueryable().Sort( sortProperty ).ToList();
}
else
{
gChartAmount.DataSource = chartData.OrderBy( c => c.DateTimeStamp ).ToList();
}
gChartAmount.DataBind();
}
/// <summary>
/// Shows the refund dialog.
/// </summary>
private void ShowRefundDialog()
{
int? txnId = hfTransactionId.Value.AsIntegerOrNull();
if ( txnId.HasValue )
{
using ( var rockContext = new RockContext() )
{
var txnService = new FinancialTransactionService( rockContext );
var txn = txnService.Get( txnId.Value );
if ( txn != null )
{
var totalAmount = txn.TotalAmount;
var otherAmounts = new FinancialTransactionDetailService( rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.Transaction != null &&
(
(
txn.TransactionCode != null &&
txn.TransactionCode != "" &&
d.Transaction.TransactionCode == txn.TransactionCode &&
d.TransactionId != txn.Id ) ||
(
d.Transaction.RefundDetails != null &&
d.Transaction.RefundDetails.OriginalTransactionId == txn.Id
)
)
)
.Select( d => d.Amount )
.ToList()
.Sum();
totalAmount += otherAmounts;
tbRefundAmount.Text = ( totalAmount > 0.0m ? totalAmount : 0.0m ).ToString( "N2" );
ddlRefundReason.SelectedIndex = -1;
tbRefundSummary.Text = string.Empty;
bool hasGateway = !string.IsNullOrWhiteSpace( txn.TransactionCode ) && txn.FinancialGateway != null;
cbProcess.Visible = hasGateway;
cbProcess.Checked = hasGateway;
ShowDialog( "REFUND" );
_focusControl = tbRefundAmount;
}
}
}
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var contributionType = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid() );
if ( contributionType != null )
{
var rockContext = new RockContext();
var transactionDetailService = new FinancialTransactionDetailService( rockContext );
var qry = transactionDetailService.Queryable().AsNoTracking()
.Where( a =>
a.Transaction.TransactionTypeValueId == contributionType.Id &&
a.Transaction.TransactionDateTime.HasValue );
var targetPerson = this.ContextEntity<Person>();
if ( targetPerson != null )
{
qry = qry.Where( t => t.Transaction.AuthorizedPersonAlias.Person.GivingId == targetPerson.GivingId );
}
List<SummaryRecord> summaryList;
using ( new Rock.Data.QueryHintScope( rockContext, QueryHintType.RECOMPILE ) )
{
summaryList = qry
.GroupBy( a => new { a.Transaction.TransactionDateTime.Value.Year, a.AccountId } )
.Select( t => new SummaryRecord
{
Year = t.Key.Year,
AccountId = t.Key.AccountId,
TotalAmount = t.Sum( d => d.Amount )
} ).OrderByDescending( a => a.Year )
.ToList();
}
var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields( this.RockPage, this.CurrentPerson );
var financialAccounts = new FinancialAccountService( rockContext ).Queryable().Select( a => new { a.Id, a.Name } ).ToDictionary( k => k.Id, v => v.Name );
var yearsMergeObjects = new List<Dictionary<string, object>>();
foreach ( var item in summaryList.GroupBy( a => a.Year ) )
{
var year = item.Key;
var accountsList = new List<object>();
foreach ( var a in item )
{
var accountDictionary = new Dictionary<string, object>();
accountDictionary.Add( "Account", financialAccounts.ContainsKey( a.AccountId ) ? financialAccounts[a.AccountId] : string.Empty );
accountDictionary.Add( "TotalAmount", a.TotalAmount );
accountsList.Add( accountDictionary );
}
var yearDictionary = new Dictionary<string, object>();
yearDictionary.Add( "Year", year );
yearDictionary.Add( "SummaryRows", accountsList );
yearsMergeObjects.Add( yearDictionary );
}
mergeFields.Add( "Rows", yearsMergeObjects );
lLavaOutput.Text = string.Empty;
if ( GetAttributeValue( "EnableDebug" ).AsBoolean() && IsUserAuthorized( Authorization.EDIT ) )
{
lLavaOutput.Text = mergeFields.lavaDebugInfo( rockContext );
}
string template = GetAttributeValue( "LavaTemplate" );
lLavaOutput.Text += template.ResolveMergeFields( mergeFields ).ResolveClientIds( upnlContent.ClientID );
}
}
/// <summary>
/// Handles the SaveClick event of the mdDetails control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void mdDetails_SaveClick( object sender, EventArgs e )
{
var rockContext = new RockContext();
if ( !string.IsNullOrWhiteSpace( tbTransactionAmount.Text ) )
{
var ftdService = new FinancialTransactionDetailService( rockContext );
FinancialTransactionDetail ftd = null;
var transactionDetailId = int.Parse( hfIdValue.Value );
if ( transactionDetailId > 0 )
{
ftd = ftdService.Get( transactionDetailId );
}
else
{
ftd = new FinancialTransactionDetail { Id = 0 };
}
ftd.TransactionId = hfIdTransValue.ValueAsInt();
ftd.AccountId = int.Parse( ddlTransactionAccount.SelectedValue );
ftd.Amount = decimal.Parse( tbTransactionAmount.Text );
ftd.Summary = tbTransactionSummary.Text;
if ( transactionDetailId == 0 )
{
ftdService.Add( ftd );
}
rockContext.SaveChanges();
}
mdDetails.Hide();
FinancialTransaction transaction = new FinancialTransaction();
transaction = new FinancialTransactionService( rockContext ).Get( hfIdTransValue.ValueAsInt() );
BindTransactionDetailGrid( transaction );
LoadRelatedImages( transaction.Id );
}
/// <summary>
/// Binds the transaction detail grid.
/// </summary>
/// <param name="transaction">The transaction.</param>
private void BindTransactionDetailGrid( FinancialTransaction transaction )
{
// Load the TransactionDetails grid here if this transaction already exists.
if ( transaction.Id != 0 )
{
var financialTransactionDetails = new FinancialTransactionDetailService( new RockContext() ).Queryable().Where( trans => trans.TransactionId == transaction.Id ).ToList();
gTransactionDetails.DataSource = financialTransactionDetails;
gTransactionDetails.DataBind();
pnlTransactionDetails.Visible = true;
}
else
{
pnlTransactionDetails.Visible = false;
}
}
private void DisplayResults()
{
RockContext rockContext = new RockContext();
var statementYear = RockDateTime.Now.Year;
if ( Request["StatementYear"] != null )
{
Int32.TryParse( Request["StatementYear"].ToString(), out statementYear );
}
FinancialTransactionDetailService financialTransactionDetailService = new FinancialTransactionDetailService( rockContext );
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where( t=> t.Transaction.AuthorizedPersonAlias.Person.GivingId == CurrentPerson.GivingId);
qry = qry.Where( t => t.Transaction.TransactionDateTime.Value.Year == statementYear );
if ( string.IsNullOrWhiteSpace( GetAttributeValue( "Accounts" ) ) )
{
qry = qry.Where( t => t.Account.IsTaxDeductible );
} else
{
var accountGuids = GetAttributeValue( "Accounts" ).Split( ',' ).Select( Guid.Parse ).ToList();
qry = qry.Where( t => accountGuids.Contains( t.Account.Guid ) );
}
qry = qry.OrderByDescending( t => t.Transaction.TransactionDateTime );
var mergeFields = new Dictionary<string, object>();
mergeFields.Add( "StatementStartDate", "1/1/" + statementYear.ToString() );
if ( statementYear == RockDateTime.Now.Year )
{
mergeFields.Add( "StatementEndDate", RockDateTime.Now );
}
else
{
mergeFields.Add( "StatementEndDate", "12/31/" + statementYear.ToString() );
}
var familyGroupTypeId = GroupTypeCache.Read( Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY ).Id;
var groupMemberQry = new GroupMemberService( rockContext ).Queryable().Where( m => m.Group.GroupTypeId == familyGroupTypeId );
// get giving group members in order by family role (adult -> child) and then gender (male -> female)
var givingGroup = new PersonService( rockContext ).Queryable().AsNoTracking()
.Where( p => p.GivingId == CurrentPerson.GivingId )
.GroupJoin(
groupMemberQry,
p => p.Id,
m => m.PersonId,
(p, m) => new {p, m})
.SelectMany( x => x.m.DefaultIfEmpty(), (y,z) => new { Person = y.p, GroupMember = z} )
.Select( p => new { FirstName = p.Person.NickName, LastName = p.Person.LastName, FamilyRoleOrder = p.GroupMember.GroupRole.Order, Gender = p.Person.Gender, PersonId = p.Person.Id } )
.DistinctBy(p => p.PersonId)
.OrderBy(p => p.FamilyRoleOrder).ThenBy(p => p.Gender)
.ToList();
// make a list of person ids in the giving group
List<int> givingGroupIdsOnly = new List<int>();
foreach ( var x in givingGroup )
{
givingGroupIdsOnly.Add(x.PersonId);
}
string salutation = string.Empty;
if (givingGroup.GroupBy(g => g.LastName).Count() == 1 )
{
salutation = string.Join( ", ", givingGroup.Select( g => g.FirstName ) ) + " " + givingGroup.FirstOrDefault().LastName;
if ( salutation.Contains( "," ) )
{
salutation = salutation.ReplaceLastOccurrence( ",", " &" );
}
}
else
{
salutation = string.Join( ", ", givingGroup.Select( g => g.FirstName + " " + g.LastName ) );
if ( salutation.Contains( "," ) )
{
salutation = salutation.ReplaceLastOccurrence( ",", " &" );
}
}
mergeFields.Add( "Salutation", salutation );
var homeAddress = CurrentPerson.GetHomeLocation();
if ( homeAddress != null )
{
mergeFields.Add( "StreetAddress1", homeAddress.Street1 );
mergeFields.Add( "StreetAddress2", homeAddress.Street2 );
mergeFields.Add( "City", homeAddress.City );
mergeFields.Add( "State", homeAddress.State );
mergeFields.Add( "PostalCode", homeAddress.PostalCode );
mergeFields.Add( "Country", homeAddress.Country );
}
else
{
mergeFields.Add( "StreetAddress1", string.Empty );
mergeFields.Add( "StreetAddress2", string.Empty );
mergeFields.Add( "City", string.Empty );
mergeFields.Add( "State", string.Empty );
mergeFields.Add( "PostalCode", string.Empty );
mergeFields.Add( "Country", string.Empty );
}
mergeFields.Add( "TransactionDetails", qry.ToList() );
mergeFields.Add( "AccountSummary", qry.GroupBy( t => t.Account.Name ).Select( s => new AccountSummary { AccountName = s.Key, Total = s.Sum( a => a.Amount ), Order = s.Max(a => a.Account.Order) } ).OrderBy(s => s.Order ));
// pledge information
var pledges = new FinancialPledgeService( rockContext ).Queryable().AsNoTracking()
.Where(p =>
p.PersonAlias.Person.GivingId == CurrentPerson.GivingId
&& (p.StartDate.Year == statementYear || p.EndDate.Year == statementYear))
.GroupBy(p => p.Account)
.Select(g => new PledgeSummary {
AccountId = g.Key.Id,
AccountName = g.Key.Name,
AmountPledged = g.Sum( p => p.TotalAmount ),
PledgeStartDate = g.Min(p => p.StartDate),
PledgeEndDate = g.Max( p => p.EndDate)
} )
.ToList();
// add detailed pledge information
foreach(var pledge in pledges )
{
var adjustedPedgeEndDate = pledge.PledgeEndDate.Value.Date.AddHours( 23 ).AddMinutes( 59 ).AddSeconds( 59 );
pledge.AmountGiven = new FinancialTransactionDetailService( rockContext ).Queryable()
.Where( t =>
t.AccountId == pledge.AccountId
&& givingGroupIdsOnly.Contains(t.Transaction.AuthorizedPersonAlias.PersonId)
&& t.Transaction.TransactionDateTime >= pledge.PledgeStartDate
&& t.Transaction.TransactionDateTime <= adjustedPedgeEndDate )
.Sum( t => t.Amount );
pledge.AmountRemaining = (pledge.AmountGiven > pledge.AmountPledged) ? 0 : (pledge.AmountPledged - pledge.AmountGiven);
if ( pledge.AmountPledged > 0 )
{
var test = (double)pledge.AmountGiven / (double)pledge.AmountPledged;
pledge.PercentComplete = (int)((pledge.AmountGiven * 100) / pledge.AmountPledged);
}
}
mergeFields.Add( "Pledges", pledges );
var template = GetAttributeValue( "LavaTemplate" );
lResults.Text = template.ResolveMergeFields( mergeFields );
// show debug info
if ( GetAttributeValue( "EnableDebug" ).AsBoolean() && IsUserAuthorized( Authorization.EDIT ) )
{
lDebug.Visible = true;
lDebug.Text = mergeFields.lavaDebugInfo();
}
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindGiversGrid()
{
// Get all the selected criteria values
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
var start = dateRange.Start;
var end = dateRange.End;
var minAmount = nreAmount.LowerValue;
var maxAmount = nreAmount.UpperValue;
var currencyTypeIds = new List<int>();
cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) );
var sourceIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
var groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
var graphBy = hfGraphBy.Value.ConvertToEnumOrNull<TransactionGraphBy>() ?? TransactionGraphBy.Total;
GiversViewBy viewBy = GiversViewBy.Giver;
if ( !HideViewByOption )
{
viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver;
}
// Collection of async queries to run before assembling data
var qryTasks = new List<Task>();
// Get all person summary data
var personInfoList = new List<PersonInfo>();
qryTasks.Add( Task.Run( () =>
{
var dt = FinancialTransactionDetailService.GetGivingAnalyticsPersonSummary(
start, end, minAmount, maxAmount, accountIds, currencyTypeIds, sourceIds )
.Tables[0];
foreach ( DataRow row in dt.Rows )
{
var personInfo = new PersonInfo();
if ( !DBNull.Value.Equals( row["Id"] ) )
{
personInfo.Id = (int)row["Id"];
}
if ( !DBNull.Value.Equals( row["Guid"] ) )
{
personInfo.Guid = row["Guid"].ToString().AsGuid();
}
if ( !DBNull.Value.Equals( row["NickName"] ) )
{
personInfo.NickName = row["NickName"].ToString();
}
if ( !DBNull.Value.Equals( row["LastName"] ) )
{
personInfo.LastName = row["LastName"].ToString();
}
if ( !DBNull.Value.Equals( row["Email"] ) )
{
personInfo.Email = row["Email"].ToString();
}
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
personInfo.GivingId = row["GivingId"].ToString();
}
if ( !DBNull.Value.Equals( row["FirstGift"] ) )
{
personInfo.FirstGift = row["FirstGift"].ToString().AsDateTime();
}
if ( !DBNull.Value.Equals( row["LastGift"] ) )
{
personInfo.LastGift = row["LastGift"].ToString().AsDateTime();
}
if ( !DBNull.Value.Equals( row["NumberGifts"] ) )
{
personInfo.NumberGifts = (int)row["NumberGifts"];
}
if ( !DBNull.Value.Equals( row["TotalAmount"] ) )
{
personInfo.TotalAmount = (decimal)row["TotalAmount"];
}
if ( !DBNull.Value.Equals( row["IsGivingLeader"] ) )
{
personInfo.IsGivingLeader = (bool)row["IsGivingLeader"];
}
if ( !DBNull.Value.Equals( row["IsAdult"] ) )
{
personInfo.IsAdult = (bool)row["IsAdult"];
}
if ( !DBNull.Value.Equals( row["IsChild"] ) )
{
personInfo.IsChild = (bool)row["IsChild"];
}
personInfo.AccountAmounts = new Dictionary<int, decimal>();
personInfoList.Add( personInfo );
}
} ) );
// Get the account summary values
DataTable dtAccountSummary = null;
qryTasks.Add( Task.Run( () =>
{
dtAccountSummary = FinancialTransactionDetailService.GetGivingAnalyticsAccountTotals(
start, end, accountIds, currencyTypeIds, sourceIds )
.Tables[0];
} ) );
// Get the first/last ever dates
var firstEverVals = new Dictionary<string, DateTime>();
var lastEverVals = new Dictionary<string, DateTime>();
qryTasks.Add( Task.Run( () =>
{
var dt = FinancialTransactionDetailService.GetGivingAnalyticsFirstLastEverDates()
.Tables[0];
foreach ( DataRow row in dt.Rows )
{
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
if ( !DBNull.Value.Equals( row["FirstEverGift"] ) )
{
firstEverVals.Add( row["GivingId"].ToString(), row["FirstEverGift"].ToString().AsDateTime().Value );
}
if ( !DBNull.Value.Equals( row["LastEverGift"] ) )
{
lastEverVals.Add( row["GivingId"].ToString(), row["LastEverGift"].ToString().AsDateTime().Value );
}
}
}
} ) );
// If a dataview filter was included, find the people who match that criteria
List<int> dataViewPersonIds = null;
var dataViewId = dvpDataView.SelectedValueAsInt();
if ( dataViewId.HasValue )
{
qryTasks.Add( Task.Run( () =>
{
dataViewPersonIds = new List<int>();
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
var dvPersonService = new PersonService( _rockContext );
ParameterExpression paramExpression = dvPersonService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( dvPersonService, paramExpression, out errorMessages );
SortProperty sort = null;
var dataViewPersonIdQry = dvPersonService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
} ) );
}
qryTasks.Add( Task.Run( () =>
{
// Clear all the existing grid columns
var selectField = new SelectField();
var oldSelectField = gGiversGifts.ColumnsOfType<SelectField>().FirstOrDefault();
if ( oldSelectField != null )
{
selectField.SelectedKeys.AddRange( oldSelectField.SelectedKeys );
}
gGiversGifts.Columns.Clear();
// Add a column for selecting rows
gGiversGifts.Columns.Add( selectField );
// Add a hidden column for person id
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Id",
HeaderText = "Person Id",
SortExpression = "Id",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
} );
// Add a column for the person's name
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "PersonName",
HeaderText = "Person",
SortExpression = "LastName,NickName"
} );
// add a column for email (but is only included on excel export)
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Email",
HeaderText = "Email",
SortExpression = "Email",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
} );
// Add a column for total amount
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = "TotalAmount",
HeaderText = "Total",
SortExpression = "TotalAmount"
} );
// Add columns for the selected account totals
if ( accountIds.Any() )
{
var accounts = new FinancialAccountService( _rockContext )
.Queryable().AsNoTracking()
.Where( a => accountIds.Contains( a.Id ) )
.ToList();
foreach ( int accountId in accountIds )
{
var account = accounts.FirstOrDefault( a => a.Id == accountId );
if ( account != null )
{
gGiversGifts.Columns.Add(
new GivingAnalyticsAccountField
{
DataField = string.Format( "Account_{0}", account.Id ),
HeaderText = account.Name,
SortExpression = string.Format( "Account:{0}", account.Id ),
} );
}
}
}
// Add a column for the number of gifts
var numberGiftsField = new RockBoundField
{
DataField = "NumberGifts",
HeaderText = "Number of Gifts",
SortExpression = "NumberGifts",
DataFormatString = "{0:N0}",
};
numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
gGiversGifts.Columns.Add( numberGiftsField );
if ( !radFirstTime.Checked )
{
// Add a column to indicate if this is a first time giver
gGiversGifts.Columns.Add(
new BoolField
{
DataField = "IsFirstEverGift",
HeaderText = "Is First Gift",
SortExpression = "IsFirstEverGift"
} );
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstGift",
HeaderText = "First Gift in Period",
SortExpression = "FirstGift"
} );
}
// Add a column for the first-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstEverGift",
HeaderText = "First Gift Ever",
SortExpression = "FirstEverGift"
} );
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "LastGift",
HeaderText = "Last Gift in Period",
SortExpression = "LastGift"
} );
// Add a column for the last-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "LastEverGift",
HeaderText = "Last Gift Ever",
SortExpression = "LastEverGift"
} );
} ) );
// Wait for all the queries to finish
Task.WaitAll( qryTasks.ToArray() );
// If dataview was selected and it's being used to filter results people, not in dataview
if ( dataViewId.HasValue && rblDataViewAction.SelectedValue != "All" && dataViewPersonIds.Any() )
{
personInfoList = personInfoList.Where( c => dataViewPersonIds.Contains( c.Id ) ).ToList();
}
// if dataview was selected and it includes people not in the result set,
if ( dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any() )
{
// Query for the names of each of these people
foreach ( var person in new PersonService( _rockContext )
.Queryable().AsNoTracking()
.Where( p => dataViewPersonIds.Contains( p.Id ) )
.Select( p => new
{
p.Id,
p.GivingId,
p.GivingLeaderId,
p.Guid,
p.NickName,
p.LastName,
p.Email
} ) )
{
// Check for a first ever gift date
var firstEverGiftDate = firstEverVals
.Where( f => f.Key == person.GivingId )
.Select( f => f.Value )
.FirstOrDefault();
// Check for a last ever gift date
var lastEverGiftDate = lastEverVals
.Where( f => f.Key == person.GivingId )
.Select( f => f.Value )
.FirstOrDefault();
var personInfo = new PersonInfo();
personInfo.Id = person.Id;
personInfo.Guid = person.Guid;
personInfo.NickName = person.NickName;
personInfo.LastName = person.LastName;
personInfo.Email = person.Email;
personInfo.GivingId = person.GivingId;
personInfo.IsGivingLeader = person.Id == person.GivingLeaderId;
personInfo.FirstEverGift = firstEverGiftDate;
personInfo.LastEverGift = lastEverGiftDate;
personInfoList.Add( personInfo );
}
}
// Filter out recs that don't match the view by
switch ( viewBy )
{
case GiversViewBy.Giver:
{
personInfoList = personInfoList.Where( p => p.IsGivingLeader ).ToList();
break;
}
case GiversViewBy.Adults:
{
personInfoList = personInfoList.Where( p => p.IsAdult ).ToList();
break;
}
case GiversViewBy.Children:
{
personInfoList = personInfoList.Where( p => p.IsChild ).ToList();
break;
}
}
// Add the first/last gift dates
foreach ( var personInfo in personInfoList )
{
if ( firstEverVals.ContainsKey( personInfo.GivingId ) )
{
personInfo.FirstEverGift = firstEverVals[personInfo.GivingId];
}
if ( lastEverVals.ContainsKey( personInfo.GivingId ) )
{
personInfo.LastEverGift = lastEverVals[personInfo.GivingId];
}
}
// Check to see if we're only showing first time givers
if ( radFirstTime.Checked )
{
personInfoList = personInfoList
.Where( p => p.IsFirstEverGift )
.ToList();
}
// Check to see if grid should display only people who gave a certain number of times and if so
// set the min value
if ( radByPattern.Checked )
{
int minCount = tbPatternXTimes.Text.AsInteger();
var previousGivingIds = new List<string>();
if ( cbPatternAndMissed.Checked )
{
var missedStart = drpPatternDateRange.LowerValue;
var missedEnd = drpPatternDateRange.UpperValue;
if ( missedStart.HasValue && missedEnd.HasValue )
{
// Get the givingleaderids that gave any amount during the pattern's date range. These
// are needed so that we know who to exclude from the result set
previousGivingIds = new FinancialTransactionDetailService( _rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.Transaction.TransactionDateTime.HasValue &&
d.Transaction.TransactionDateTime.Value >= missedStart.Value &&
d.Transaction.TransactionDateTime.Value < missedEnd.Value &&
( accountIds.Any() && accountIds.Contains( d.AccountId ) || d.Account.IsTaxDeductible ) &&
d.Amount != 0.0M )
.Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId )
.ToList();
}
}
personInfoList = personInfoList
.Where( p =>
!previousGivingIds.Contains( p.GivingId ) &&
p.NumberGifts >= minCount )
.ToList();
}
// Add account summary info
foreach ( DataRow row in dtAccountSummary.Rows )
{
if ( !DBNull.Value.Equals( row["GivingId"] ) &&
!DBNull.Value.Equals( row["AccountId"] ) &&
!DBNull.Value.Equals( row["Amount"] ) )
{
string givingId = row["GivingId"].ToString();
int accountId = (int)row["AccountId"];
decimal amount = (decimal)row["Amount"];
foreach ( var personInfo in personInfoList.Where( p => p.GivingId == givingId ) )
{
personInfo.AccountAmounts.AddOrIgnore( accountId, amount );
}
}
}
// Calculate Total
if ( viewBy == GiversViewBy.Giver )
{
pnlTotal.Visible = true;
decimal amountTotal = personInfoList.Sum( p => p.TotalAmount );
lTotal.Text = amountTotal.FormatAsCurrency();
}
else
{
pnlTotal.Visible = false;
}
var qry = personInfoList.AsQueryable();
if ( gGiversGifts.SortProperty != null )
{
if ( gGiversGifts.SortProperty.Property.StartsWith( "Account" ) )
{
int? accountId = gGiversGifts.SortProperty.Property.Substring( 8 ).AsIntegerOrNull();
if ( accountId.HasValue )
{
foreach ( var personInfo in personInfoList )
{
personInfo.SortAmount = personInfo.AccountAmounts.ContainsKey( accountId.Value ) ?
personInfo.AccountAmounts[accountId.Value] : 0.0M;
if ( gGiversGifts.SortProperty.Direction == SortDirection.Ascending )
{
gGiversGifts.DataSource = personInfoList.OrderBy( p => p.SortAmount ).ToList();
}
else
{
gGiversGifts.DataSource = personInfoList.OrderByDescending( p => p.SortAmount ).ToList();
}
}
}
else
{
gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList();
}
}
else
{
gGiversGifts.DataSource = qry.Sort( gGiversGifts.SortProperty ).ToList();
}
}
else
{
gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList();
}
gGiversGifts.DataBind();
}
/// <summary>
/// Handles the Click event of the lbSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void lbSave_Click( object sender, EventArgs e )
{
var rockContext = new RockContext();
var txnService = new FinancialTransactionService( rockContext );
var txnDetailService = new FinancialTransactionDetailService( rockContext );
var txnImageService = new FinancialTransactionImageService( rockContext );
var binaryFileService = new BinaryFileService( rockContext );
FinancialTransaction txn = null;
int? txnId = hfTransactionId.Value.AsIntegerOrNull();
int? batchId = hfBatchId.Value.AsIntegerOrNull();
if ( txnId.HasValue )
{
txn = txnService.Get( txnId.Value );
}
if ( txn == null && batchId.HasValue )
{
txn = new FinancialTransaction();
txnService.Add( txn );
txn.BatchId = batchId.Value;
}
if ( txn != null )
{
txn.AuthorizedPersonId = ppAuthorizedPerson.PersonId;
txn.TransactionDateTime = dtTransactionDateTime.SelectedDateTime;
txn.TransactionTypeValueId = ddlTransactionType.SelectedValue.AsInteger();
txn.SourceTypeValueId = ddlSourceType.SelectedValueAsInt();
Guid? gatewayGuid = cpPaymentGateway.SelectedValueAsGuid();
if ( gatewayGuid.HasValue )
{
var gatewayEntity = EntityTypeCache.Read( gatewayGuid.Value );
if ( gatewayEntity != null )
{
txn.GatewayEntityTypeId = gatewayEntity.Id;
}
else
{
txn.GatewayEntityTypeId = null;
}
}
else
{
txn.GatewayEntityTypeId = null;
}
txn.TransactionCode = tbTransactionCode.Text;
txn.CurrencyTypeValueId = ddlCurrencyType.SelectedValueAsInt();
txn.CreditCardTypeValueId = ddlCreditCardType.SelectedValueAsInt();
txn.Summary = tbSummary.Text;
if ( !Page.IsValid || !txn.IsValid )
{
return;
}
foreach ( var txnDetail in TransactionDetailsState )
{
if ( !txnDetail.IsValid )
{
return;
}
}
foreach ( var txnImage in TransactionImagesState )
{
if ( !txnImage.IsValid )
{
return;
}
}
rockContext.WrapTransaction( () =>
{
// Save the transaction
rockContext.SaveChanges();
// Delete any transaction details that were removed
var txnDetailsInDB = txnDetailService.Queryable().Where( a => a.TransactionId.Equals( txn.Id ) ).ToList();
var deletedDetails = from txnDetail in txnDetailsInDB
where !TransactionDetailsState.Select( d => d.Guid ).Contains( txnDetail.Guid )
select txnDetail;
deletedDetails.ToList().ForEach( txnDetail =>
{
txnDetailService.Delete( txnDetail );
} );
rockContext.SaveChanges();
// Save Transaction Details
foreach ( var editorTxnDetail in TransactionDetailsState )
{
// Add or Update the activity type
var txnDetail = txn.TransactionDetails.FirstOrDefault( d => d.Guid.Equals( editorTxnDetail.Guid ) );
if ( txnDetail == null )
{
txnDetail = new FinancialTransactionDetail();
txnDetail.Guid = editorTxnDetail.Guid;
txn.TransactionDetails.Add( txnDetail );
}
txnDetail.AccountId = editorTxnDetail.AccountId;
txnDetail.Amount = editorTxnDetail.Amount;
txnDetail.Summary = editorTxnDetail.Summary;
}
rockContext.SaveChanges();
// Remove any images that do not have a binary file
foreach ( var txnImage in TransactionImagesState.Where( i => i.BinaryFileId == 0 ).ToList() )
{
TransactionImagesState.Remove( txnImage );
}
// Delete any transaction images that were removed
var txnImagesInDB = txnImageService.Queryable().Where( a => a.TransactionId.Equals( txn.Id ) ).ToList();
var deletedImages = from txnImage in txnImagesInDB
where !TransactionImagesState.Select( d => d.Guid ).Contains( txnImage.Guid )
select txnImage;
deletedImages.ToList().ForEach( txnImage =>
{
txnImageService.Delete( txnImage );
} );
rockContext.SaveChanges();
// Save Transaction Images
foreach ( var editorTxnImage in TransactionImagesState )
{
// Add or Update the activity type
var txnImage = txn.Images.FirstOrDefault( d => d.Guid.Equals( editorTxnImage.Guid ) );
if ( txnImage == null )
{
txnImage = new FinancialTransactionImage();
txnImage.Guid = editorTxnImage.Guid;
txn.Images.Add( txnImage );
}
txnImage.BinaryFileId = editorTxnImage.BinaryFileId;
txnImage.TransactionImageTypeValueId = editorTxnImage.TransactionImageTypeValueId;
}
rockContext.SaveChanges();
// Make sure updated binary files are not temporary
var savedBinaryFileIds = txn.Images.Select( i => i.BinaryFileId ).ToList();
foreach ( var binaryFile in binaryFileService.Queryable().Where( f => savedBinaryFileIds.Contains( f.Id ) ) )
{
binaryFile.IsTemporary = false;
}
// Delete any orphaned images
var orphanedBinaryFileIds = BinaryFileIds.Where( f => !savedBinaryFileIds.Contains( f ) );
foreach ( var binaryFile in binaryFileService.Queryable().Where( f => orphanedBinaryFileIds.Contains( f.Id ) ) )
{
binaryFileService.Delete( binaryFile );
}
rockContext.SaveChanges();
} );
// Requery the batch to support EF navigation properties
var savedTxn = GetTransaction( txn.Id );
ShowReadOnlyDetails( savedTxn );
}
}
private void DisplayResults()
{
var numberOfYears = GetAttributeValue( "MaxYearsToDisplay" ).AsInteger();
RockContext rockContext = new RockContext();
FinancialTransactionDetailService financialTransactionDetailService = new FinancialTransactionDetailService( rockContext );
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where( t=> t.Transaction.AuthorizedPersonAlias.Person.GivingId == TargetPerson.GivingId);
if ( string.IsNullOrWhiteSpace( GetAttributeValue( "Accounts" ) ) )
{
qry = qry.Where( t => t.Account.IsTaxDeductible );
} else
{
var accountGuids = GetAttributeValue( "Accounts" ).Split( ',' ).Select( Guid.Parse ).ToList();
qry = qry.Where( t => accountGuids.Contains( t.Account.Guid ) );
}
var yearQry = qry.GroupBy( t => t.Transaction.TransactionDateTime.Value.Year )
.Select( g => new { Year = g.Key } )
.OrderByDescending(y => y.Year);
var mergeFields = new Dictionary<string, object>();
mergeFields.Add( "DetailPage", LinkedPageRoute( "DetailPage" ) );
mergeFields.Add( "StatementYears", yearQry.Take( numberOfYears ) );
mergeFields.Add( "PersonGuid", TargetPerson.Guid );
var template = GetAttributeValue( "LavaTemplate" );
lResults.Text = template.ResolveMergeFields( mergeFields );
// show debug info
if ( GetAttributeValue( "EnableDebug" ).AsBoolean() && IsUserAuthorized( Authorization.EDIT ) )
{
lDebug.Visible = true;
lDebug.Text = mergeFields.lavaDebugInfo();
}
}
/// <summary>
/// Handles the Click event of the btnNext control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnNext_Click( object sender, EventArgs e )
{
var changes = new List<string>();
var rockContext = new RockContext();
var financialTransactionService = new FinancialTransactionService( rockContext );
var financialTransactionDetailService = new FinancialTransactionDetailService( rockContext );
var financialPersonBankAccountService = new FinancialPersonBankAccountService( rockContext );
int txnId = hfTransactionId.Value.AsInteger();
var financialTransaction = financialTransactionService
.Queryable( "AuthorizedPersonAlias.Person,ProcessedByPersonAlias.Person" )
.FirstOrDefault( t => t.Id == txnId );
// set the AuthorizedPersonId (the person who wrote the check, for example) to the if the SelectNew person (if selected) or person selected in the drop down (if there is somebody selected)
int? authorizedPersonId = ppSelectNew.PersonId ?? ddlIndividual.SelectedValue.AsIntegerOrNull();
var accountNumberSecured = hfCheckMicrHashed.Value;
if ( cbTotalAmount.Text.AsDecimalOrNull().HasValue && !authorizedPersonId.HasValue )
{
nbSaveError.Text = "Transaction must be matched to a person when the amount is specified.";
nbSaveError.Visible = true;
return;
}
// if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction and clear out the detail records (we don't want an unmatched transaction to have detail records)
if ( financialTransaction != null &&
financialTransaction.AuthorizedPersonAliasId.HasValue &&
!authorizedPersonId.HasValue )
{
financialTransaction.AuthorizedPersonAliasId = null;
foreach ( var detail in financialTransaction.TransactionDetails )
{
History.EvaluateChange( changes, detail.Account != null ? detail.Account.Name : "Unknown", detail.Amount.ToString( "C2" ), string.Empty );
financialTransactionDetailService.Delete( detail );
}
changes.Add( "Unmatched transaction" );
HistoryService.SaveChanges(
rockContext,
typeof( FinancialBatch ),
Rock.SystemGuid.Category.HISTORY_FINANCIAL_TRANSACTION.AsGuid(),
financialTransaction.BatchId.Value,
changes,
string.Format( "Transaction Id: {0}", financialTransaction.Id ),
typeof( FinancialTransaction ),
financialTransaction.Id,
false
);
rockContext.SaveChanges();
// if the transaction was unmatched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser( hfTransactionId.Value.AsInteger() );
}
// if the transaction is matched to somebody, attempt to save it. Otherwise, if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction
if ( financialTransaction != null && authorizedPersonId.HasValue )
{
bool requiresMicr =
financialTransaction.FinancialPaymentDetail != null &&
financialTransaction.FinancialPaymentDetail.CurrencyTypeValue != null &&
financialTransaction.FinancialPaymentDetail.CurrencyTypeValue.Guid == Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK.AsGuid();
if ( cbTotalAmount.Text.AsDecimalOrNull() == null )
{
nbSaveError.Text = "Total amount must be allocated to accounts.";
nbSaveError.Visible = true;
return;
}
var personAlias = new PersonAliasService( rockContext ).GetPrimaryAlias( authorizedPersonId.Value );
int? personAliasId = personAlias != null ? personAlias.Id : (int?)null;
// if this transaction has an accountnumber associated with it (in other words, it's a valid scanned check), ensure there is a financialPersonBankAccount record
if ( financialTransaction.MICRStatus == MICRStatus.Success && !string.IsNullOrWhiteSpace( accountNumberSecured ) )
{
var financialPersonBankAccount = financialPersonBankAccountService.Queryable().Where( a => a.AccountNumberSecured == accountNumberSecured && a.PersonAlias.PersonId == authorizedPersonId.Value ).FirstOrDefault();
if ( financialPersonBankAccount == null )
{
if ( personAliasId.HasValue )
{
financialPersonBankAccount = new FinancialPersonBankAccount();
financialPersonBankAccount.PersonAliasId = personAliasId.Value;
financialPersonBankAccount.AccountNumberSecured = accountNumberSecured;
var checkMicrClearText = Encryption.DecryptString( financialTransaction.CheckMicrParts );
var parts = checkMicrClearText.Split( '_' );
if ( parts.Length >= 2 )
{
financialPersonBankAccount.AccountNumberMasked = parts[1].Masked();
}
financialPersonBankAccountService.Add( financialPersonBankAccount );
}
}
}
string prevPerson = ( financialTransaction.AuthorizedPersonAlias != null && financialTransaction.AuthorizedPersonAlias.Person != null ) ?
financialTransaction.AuthorizedPersonAlias.Person.FullName : string.Empty;
string newPerson = string.Empty;
if ( personAliasId.HasValue )
{
newPerson = personAlias.Person.FullName;
financialTransaction.AuthorizedPersonAliasId = personAliasId;
}
History.EvaluateChange( changes, "Person", prevPerson, newPerson );
// just in case this transaction is getting re-edited either by the same user, or somebody else, clean out any existing TransactionDetail records
foreach ( var detail in financialTransaction.TransactionDetails.ToList() )
{
financialTransactionDetailService.Delete( detail );
History.EvaluateChange( changes, detail.Account != null ? detail.Account.Name : "Unknown", detail.Amount.ToString( "C2" ), string.Empty );
}
foreach ( var accountBox in rptAccounts.ControlsOfTypeRecursive<CurrencyBox>() )
{
var amount = accountBox.Text.AsDecimalOrNull();
if ( amount.HasValue && amount.Value >= 0 )
{
var financialTransactionDetail = new FinancialTransactionDetail();
financialTransactionDetail.TransactionId = financialTransaction.Id;
financialTransactionDetail.AccountId = accountBox.Attributes["data-account-id"].AsInteger();
financialTransactionDetail.Amount = amount.Value;
financialTransactionDetailService.Add( financialTransactionDetail );
History.EvaluateChange( changes, accountBox.Label, 0.0M.ToString( "C2" ), amount.Value.ToString( "C2" ) );
}
}
financialTransaction.ProcessedByPersonAliasId = this.CurrentPersonAlias.Id;
financialTransaction.ProcessedDateTime = RockDateTime.Now;
changes.Add( "Matched transaction" );
HistoryService.SaveChanges(
rockContext,
typeof( FinancialBatch ),
Rock.SystemGuid.Category.HISTORY_FINANCIAL_TRANSACTION.AsGuid(),
financialTransaction.BatchId.Value,
changes,
personAlias != null && personAlias.Person != null ? personAlias.Person.FullName : string.Format( "Transaction Id: {0}", financialTransaction.Id ),
typeof( FinancialTransaction ),
financialTransaction.Id,
false
);
rockContext.SaveChanges();
}
else
{
// if the transaction was not matched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser( hfTransactionId.Value.AsInteger() );
}
NavigateToTransaction( Direction.Next );
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindGiversGrid()
{
// Get all the selected criteria values
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
var start = dateRange.Start;
var end = dateRange.End;
var minAmount = nreAmount.LowerValue;
var maxAmount = nreAmount.UpperValue;
var currencyTypeIds = new List<int>();
cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) );
var sourceTypeIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceTypeIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
var dataViewId = dvpDataView.SelectedValueAsInt();
GiversViewBy viewBy = GiversViewBy.Giver;
if ( !HideViewByOption )
{
viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver;
}
// Clear all the existing grid columns
var selectField = new SelectField();
var oldSelectField = gGiversGifts.ColumnsOfType<SelectField>().FirstOrDefault();
if (oldSelectField != null )
{
selectField.SelectedKeys.AddRange( oldSelectField.SelectedKeys );
}
gGiversGifts.Columns.Clear();
// Add a column for selecting rows
gGiversGifts.Columns.Add( selectField );
// Add a column for the person's name
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "PersonName",
HeaderText = "Person",
SortExpression = "LastName,NickName"
} );
// add a column for email (but is only included on excel export)
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Email",
HeaderText = "Email",
SortExpression = "Email",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
} );
// Add a column for total amount
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = "TotalAmount",
HeaderText = "Total",
SortExpression = "TotalAmount"
} );
// Add columns for the selected account totals
if ( accountIds.Any() )
{
var accounts = new FinancialAccountService( _rockContext )
.Queryable().AsNoTracking()
.Where( a => accountIds.Contains( a.Id ) )
.ToList();
foreach ( int accountId in accountIds )
{
var account = accounts.FirstOrDefault( a => a.Id == accountId );
if ( account != null )
{
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = account.Id.ToString(),
HeaderText = account.Name,
SortExpression = account.Id.ToString()
} );
}
}
}
// Add a column for the number of gifts
var numberGiftsField = new RockBoundField
{
DataField = "NumberGifts",
HeaderText = "Number of Gifts",
SortExpression = "NumberGifts",
DataFormatString = "{0:N0}",
};
numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
gGiversGifts.Columns.Add( numberGiftsField );
// Add a column to indicate if this is a first time giver
gGiversGifts.Columns.Add(
new BoolField
{
DataField = "IsFirstEverGift",
HeaderText = "Is First Gift",
SortExpression = "IsFirstEverGift"
} );
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstGift",
HeaderText = "First Gift",
SortExpression = "FirstGift"
} );
// Add a column for the first-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstEverGift",
HeaderText = "First Gift Ever",
SortExpression = "FirstEverGift"
} );
var transactionDetailService = new FinancialTransactionDetailService( _rockContext );
var personService = new PersonService( _rockContext );
// If dataview was selected get the person id's returned by the dataview
var dataViewPersonIds = new List<int>();
if ( dataViewId.HasValue )
{
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
SortProperty sortProperty = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sortProperty )
.Select( p => p.Id );
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
}
// Check to see if grid should display only people who gave a certain number of times and if so
// set the min value
int minCount = 0;
var previousPersonIds = new List<int>();
if ( radByPattern.Checked )
{
minCount = tbPatternXTimes.Text.AsInteger();
var missedStart = drpPatternDateRange.LowerValue;
var missedEnd = drpPatternDateRange.UpperValue;
if ( missedStart.HasValue && missedEnd.HasValue )
{
// Get the givingids that gave any amount during the pattern's date range. These
// are needed so that we know who to exclude from the result set
var previousGivingIds = transactionDetailService
.Queryable().AsNoTracking()
.Where( d =>
d.Transaction.TransactionDateTime.HasValue &&
d.Transaction.TransactionDateTime.Value >= missedStart.Value &&
d.Transaction.TransactionDateTime.Value < missedEnd.Value &&
accountIds.Contains( d.AccountId ) &&
d.Amount != 0.0M )
.Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId );
// Now get the person ids from the givingids
previousPersonIds = personService
.Queryable().AsNoTracking()
.Where( p => previousGivingIds.Contains( p.GivingId ) )
.Select( p => p.Id )
.ToList();
}
}
// Call the stored procedure to get all the giving data that matches the selected criteria.
// The stored procedure returns two tables. First is a list of all matching transaction summary
// information and the second table is each giving leader's first-ever gift date to a tax-deductible account
DataSet ds = FinancialTransactionDetailService.GetGivingAnalytics( start, end, minAmount, maxAmount,
accountIds, currencyTypeIds, sourceTypeIds, dataViewId, viewBy );
// Get the results table
DataTable dtResults = ds.Tables[0];
// Get the first-ever gift dates and load them into a dictionary for faster matching
DataTable dtFirstEver = ds.Tables[1];
var firstEverVals = new Dictionary<int, DateTime>();
foreach( DataRow row in ds.Tables[1].Rows )
{
if ( !DBNull.Value.Equals( row["FirstEverGift"] ) )
{
firstEverVals.Add( (int)row["PersonId"], (DateTime)row["FirstEverGift"] );
}
}
// Add columns to the result set for the first-ever data
dtResults.Columns.Add( new DataColumn( "IsFirstEverGift", typeof( bool ) ) );
dtResults.Columns.Add( new DataColumn( "FirstEverGift", typeof( DateTime ) ) );
foreach( DataRow row in dtResults.Rows )
{
bool rowValid = true;
// Get the person id
int personId = (int)row["Id"];
if ( radByPattern.Checked )
{
// If pattern was specified check minimum gifts and other date range
int numberGifts = (int)row["NumberGifts"];
if ( numberGifts < minCount )
{
rowValid = false;
}
else
{
// If this giving leader gave during the pattern date, remove the row since we
// only want those who did not
if ( previousPersonIds.Contains( personId ) )
{
rowValid = false;
}
}
}
if ( dataViewId.HasValue )
{
// If a dataview filter was specified, and this row is not part of dataview,
// remove it
if ( !dataViewPersonIds.Contains(personId))
{
rowValid = false;
// Remove person id from list so that list can be used later to optionally
// add rows for remaining people who were in the dataview, but not in the
// result set
dataViewPersonIds.Remove( personId );
}
}
if ( rowValid )
{
// Set the first ever information for each row
bool isFirstEverGift = false;
DateTime firstGift = (DateTime)row["FirstGift"];
if ( firstEverVals.ContainsKey( personId ) )
{
DateTime firstEverGift = firstEverVals[personId];
isFirstEverGift = firstEverGift.Equals( firstGift );
row["FirstEverGift"] = firstEverGift;
}
// If only first time givers should be included, remove any that are not
if ( radFirstTime.Checked && !isFirstEverGift )
{
rowValid = false;
}
else
{
row["IsFirstEverGift"] = isFirstEverGift;
}
}
if ( !rowValid )
{
row.Delete();
}
}
// if dataview was selected and it includes people not in the result set,
if ( dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any() )
{
// Query for the names of each of these people
foreach( var person in personService
.Queryable().AsNoTracking()
.Select( p => new {
p.Id,
p.Guid,
p.NickName,
p.LastName,
p.Email
}))
{
// Check for a first ever gift date
var firstEverGiftDate = firstEverVals
.Where( f => f.Key == person.Id )
.Select( f => f.Value )
.FirstOrDefault();
DataRow row = dtResults.NewRow();
row["Id"] = person.Id;
row["Guid"] = person.Guid;
row["NickName"] = person.NickName;
row["LastName"] = person.LastName;
row["PersonName"] = person.NickName + " " + person.LastName;
row["Email"] = person.Email;
row["IsFirstEverGift"] = false;
row["FirstEverGift"] = firstEverGiftDate;
dtResults.Rows.Add( row );
}
}
// Update the changes (deletes) in the datatable
dtResults.AcceptChanges();
// Calculate Total
if ( viewBy == GiversViewBy.Giver )
{
pnlTotal.Visible = true;
object amountTotalObj = dtResults.Compute( "Sum(TotalAmount)", null );
if ( amountTotalObj != null )
{
decimal amountTotal = amountTotalObj.ToString().AsDecimal();
lTotal.Text = amountTotal.FormatAsCurrency();
}
else
{
lTotal.Text = string.Empty;
}
}
else
{
pnlTotal.Visible = false;
}
// Sort the results
System.Data.DataView dv = dtResults.DefaultView;
if ( gGiversGifts.SortProperty != null )
{
try
{
var sortProperties = new List<string>();
foreach( string prop in gGiversGifts.SortProperty.Property.SplitDelimitedValues(false))
{
sortProperties.Add( string.Format( "[{0}] {1}", prop, gGiversGifts.SortProperty.DirectionString ) );
}
dv.Sort = sortProperties.AsDelimited( ", " );
}
catch
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
}
else
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
gGiversGifts.DataSource = dv;
gGiversGifts.DataBind();
}
/// <summary>
/// Shows the financial batch summary.
/// </summary>
/// <param name="batch">The financial batch.</param>
private void ShowReadonlyDetails( FinancialBatch batch )
{
SetEditMode( false );
if ( batch != null )
{
hfBatchId.SetValue( batch.Id );
SetHeadingInfo( batch, batch.Name );
string campusName = string.Empty;
if ( batch.CampusId.HasValue )
{
var campus = CampusCache.Read( batch.CampusId.Value );
if ( campus != null )
{
campusName = campus.ToString();
}
}
var rockContext = new RockContext();
var financialTransactionService = new FinancialTransactionService( rockContext );
var batchTransactions = financialTransactionService.Queryable().Where( a => a.BatchId.HasValue && a.BatchId.Value == batch.Id );
var financialTransactionDetailService = new FinancialTransactionDetailService( rockContext );
var qryTransactionDetails = financialTransactionDetailService.Queryable().Where( a => a.Transaction.BatchId == batch.Id );
decimal txnTotal = qryTransactionDetails.Select( a => (decimal?)a.Amount ).Sum() ?? 0;
decimal variance = txnTotal - batch.ControlAmount;
string amountFormat = string.Format(
"{0} / {1} / " + ( variance == 0.0M ? "{2}" : "<span class='label label-danger'>{2}</span>" ),
txnTotal.FormatAsCurrency(),
batch.ControlAmount.FormatAsCurrency(),
variance.FormatAsCurrency() );
lDetails.Text = new DescriptionList()
.Add( "Date Range", new DateRange( batch.BatchStartDateTime, batch.BatchEndDateTime ).ToString( "g" ) )
.Add( "Transaction / Control / Variance", amountFormat )
.Add( "Accounting Code", batch.AccountingSystemCode )
.Add( "Notes", batch.Note )
.Html;
// Account Summary
gAccounts.DataSource = qryTransactionDetails
.GroupBy( d => new
{
AccountId = d.AccountId,
AccountName = d.Account.Name
} )
.Select( s => new
{
Id = s.Key.AccountId,
Name = s.Key.AccountName,
Amount = s.Sum( a => (decimal?)a.Amount ) ?? 0.0M
} )
.OrderBy( s => s.Name )
.ToList();
gAccounts.DataBind();
// Currency Summary
gCurrencyTypes.DataSource = batchTransactions
.GroupBy( c => new
{
CurrencyTypeValueId = c.FinancialPaymentDetailId.HasValue ? c.FinancialPaymentDetail.CurrencyTypeValueId : 0,
} )
.Select( s => new
{
CurrencyTypeValueId = s.Key.CurrencyTypeValueId,
Amount = s.Sum( a => (decimal?)a.TransactionDetails.Sum( t => t.Amount ) ) ?? 0.0M
} )
.ToList()
.Select( s => new
{
Id = s.CurrencyTypeValueId,
Name = DefinedValueCache.GetName( s.CurrencyTypeValueId ),
Amount = s.Amount
} ).OrderBy( a => a.Name ).ToList();
gCurrencyTypes.DataBind();
}
}
/// <summary>
/// Handles the Delete event of the gTransactionDetails control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="Rock.Web.UI.Controls.RowEventArgs"/> instance containing the event data.</param>
protected void gTransactionDetails_Delete( object sender, Rock.Web.UI.Controls.RowEventArgs e )
{
var rockContext = new RockContext();
var ftdService = new FinancialTransactionDetailService( rockContext );
var ftd = ftdService.Get( e.RowKeyId );
if ( ftd != null )
{
string errorMessage;
if ( !ftdService.CanDelete( ftd, out errorMessage ) )
{
maGridWarning.Show( errorMessage, Rock.Web.UI.Controls.ModalAlertType.Information );
return;
}
ftdService.Delete( ftd );
rockContext.SaveChanges();
}
FinancialTransaction transaction = new FinancialTransaction();
transaction = new FinancialTransactionService( rockContext ).Get( hfIdTransValue.ValueAsInt() );
BindTransactionDetailGrid( transaction );
}
