/// <summary>
/// Gets the giving automation monthly account giving history. This is used for the Giving Overview block's monthly
/// bar chart and also yearly summary.
/// </summary>
/// <param name="givingId">The giving identifier.</param>
/// <param name="startDateTime">The start date time.</param>
/// <returns>List<MonthlyAccountGivingHistory>.</returns>
public List <MonthlyAccountGivingHistory> GetGivingAutomationMonthlyAccountGivingHistory(string givingId, DateTime?startDateTime)
{
var givingIdPersonAliasIdQuery = new PersonAliasService(this.Context as RockContext)
.Queryable()
.Where(a => a.Person.GivingId == givingId)
.Select(a => a.Id);
var qry = GetGivingAutomationSourceTransactionQuery()
.AsNoTracking()
.Where(t =>
t.TransactionDateTime.HasValue &&
t.AuthorizedPersonAliasId.HasValue &&
givingIdPersonAliasIdQuery.Contains(t.AuthorizedPersonAliasId.Value));
if (startDateTime.HasValue)
{
qry = qry.Where(t => t.TransactionDateTime >= startDateTime);
}
var views = qry
.SelectMany(t => t.TransactionDetails.Select(td => new
{
TransactionDateTime = t.TransactionDateTime.Value,
td.AccountId,
td.Amount,
// For each Refund (there could be more than one) get the refund amount for each if the refunds's Detail records for the Account.
// Then sum that up for the total refund amount for the account
AccountRefundAmount = td.Transaction
.Refunds.Select(a => a.FinancialTransaction.TransactionDetails.Where(rrr => rrr.AccountId == td.AccountId)
.Sum(rrrr => ( decimal? )rrrr.Amount)).Sum() ?? 0.0M
}))
.ToList();
var monthlyAccountGivingHistoryList = views
.GroupBy(a => new { a.TransactionDateTime.Year, a.TransactionDateTime.Month, a.AccountId })
.Select(t => new MonthlyAccountGivingHistory
{
Year = t.Key.Year,
Month = t.Key.Month,
AccountId = t.Key.AccountId,
Amount = t.Sum(d => d.Amount + d.AccountRefundAmount)
})
.OrderByDescending(a => a.Year)
.ThenByDescending(a => a.Month)
.ToList();
return(monthlyAccountGivingHistoryList);
}
/// <summary>
/// Gets the giving automation monthly account giving history. This is used for the Giving Overview block's monthly
/// bar chart and also yearly summary.
/// </summary>
/// <param name="givingId">The giving identifier.</param>
/// <param name="startDateTime">The start date time.</param>
/// <returns>List<MonthlyAccountGivingHistory>.</returns>
public List <MonthlyAccountGivingHistory> GetGivingAutomationMonthlyAccountGivingHistory(string givingId, DateTime?startDateTime)
{
var givingIdPersonAliasIdQuery = new PersonAliasService(this.Context as RockContext)
.Queryable()
.Where(a => a.Person.GivingId == givingId)
.Select(a => a.Id);
var qry = GetGivingAutomationSourceTransactionQuery()
.AsNoTracking()
.Where(t =>
t.TransactionDateTime.HasValue &&
t.AuthorizedPersonAliasId.HasValue &&
givingIdPersonAliasIdQuery.Contains(t.AuthorizedPersonAliasId.Value));
if (startDateTime.HasValue)
{
qry = qry.Where(t => t.TransactionDateTime >= startDateTime);
}
var views = qry
.SelectMany(t => t.TransactionDetails.Select(td => new
{
TransactionDateTime = t.TransactionDateTime.Value,
td.AccountId,
td.Amount
}))
.ToList();
var monthlyAccountGivingHistoryList = views
.GroupBy(a => new { a.TransactionDateTime.Year, a.TransactionDateTime.Month, a.AccountId })
.Select(t => new MonthlyAccountGivingHistory
{
Year = t.Key.Year,
Month = t.Key.Month,
AccountId = t.Key.AccountId,
Amount = t.Sum(d => d.Amount)
})
.OrderByDescending(a => a.Year)
.ThenByDescending(a => a.Month)
.ToList();
return(monthlyAccountGivingHistoryList);
}
/// <summary>
/// Updates the IsRead property of SMS Responses sent from the provided person to the SMSPhone number stored in SmsFromDefinedValue.
/// </summary>
/// <param name="fromPersonId">From person identifier.</param>
/// <param name="relatedSmsFromDefinedValueId">The defined value ID of the from SMS phone number.</param>
public void UpdateReadPropertyByFromPersonId(int fromPersonId, int relatedSmsFromDefinedValueId)
{
var personAliasIdQuery = new PersonAliasService(this.Context as RockContext).Queryable().Where(a => a.PersonId == fromPersonId).Select(a => a.Id);
var communicationResponsesToUpdate = Queryable().Where(a => a.FromPersonAliasId.HasValue && personAliasIdQuery.Contains(a.FromPersonAliasId.Value) && a.RelatedSmsFromDefinedValueId == relatedSmsFromDefinedValueId && a.IsRead == false);
this.Context.BulkUpdate(communicationResponsesToUpdate, a => new CommunicationResponse {
IsRead = true
});
}
/// <summary>
/// Gets the SMS conversation history for a person alias ID. Includes the communication sent by Rock that the person may be responding to.
/// </summary>
/// <param name="personId">The person identifier.</param>
/// <param name="relatedSmsFromDefinedValueId">The related SMS from defined value identifier.</param>
/// <returns>List<CommunicationRecipientResponse>.</returns>
public List <CommunicationRecipientResponse> GetCommunicationConversationForPerson(int personId, int relatedSmsFromDefinedValueId)
{
List <CommunicationRecipientResponse> communicationRecipientResponseList = new List <CommunicationRecipientResponse>();
var smsMediumEntityTypeId = EntityTypeCache.GetId(SystemGuid.EntityType.COMMUNICATION_MEDIUM_SMS).Value;
var personAliasIdQuery = new PersonAliasService(this.Context as RockContext).Queryable().Where(a => a.PersonId == personId).Select(a => a.Id);
var communicationResponseQuery = this.Queryable()
.Where(r => r.RelatedMediumEntityTypeId == smsMediumEntityTypeId &&
r.RelatedSmsFromDefinedValueId == relatedSmsFromDefinedValueId &&
r.FromPersonAliasId.HasValue &&
personAliasIdQuery.Contains(r.FromPersonAliasId.Value)
);
var communicationResponseList = communicationResponseQuery.ToList();
foreach (var communicationResponse in communicationResponseList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = communicationResponse.CreatedDateTime,
PersonId = communicationResponse?.FromPersonAlias?.PersonId,
FullName = communicationResponse?.FromPersonAlias?.Person.FullName,
IsRead = communicationResponse.IsRead,
MessageKey = communicationResponse.MessageKey,
IsOutbound = false,
RecipientPersonAliasId = communicationResponse.FromPersonAliasId,
SMSMessage = communicationResponse.Response,
MessageStatus = CommunicationRecipientStatus.Delivered, // We are just going to call these delivered because we have them. Setting this will tell the UI to not display the status.
CommunicationResponseId = communicationResponse.Id,
};
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
var communicationRecipientQuery = new CommunicationRecipientService(this.Context as RockContext)
.Queryable()
.Where(r => r.MediumEntityTypeId == smsMediumEntityTypeId)
.Where(r => r.Communication.SMSFromDefinedValueId == relatedSmsFromDefinedValueId)
.Where(r => r.PersonAliasId.HasValue)
.Where(r => personAliasIdQuery.Contains(r.PersonAliasId.Value))
.Where(r => r.Status == CommunicationRecipientStatus.Delivered || r.Status == CommunicationRecipientStatus.Pending);
var communicationRecipientList = communicationRecipientQuery.Include(a => a.PersonAlias.Person.PhoneNumbers).Select(a => new
{
a.CreatedDateTime,
a.Communication.SenderPersonAlias.Person,
a.Communication,
PersonAliasId = a.Communication.SenderPersonAliasId,
a.SentMessage,
a.Status
}).ToList();
foreach (var communicationRecipient in communicationRecipientList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = communicationRecipient.CreatedDateTime,
PersonId = communicationRecipient.Person?.Id,
FullName = communicationRecipient.Person?.FullName,
IsRead = true,
IsOutbound = true,
RecipientPersonAliasId = communicationRecipient.PersonAliasId,
SMSMessage = communicationRecipient.SentMessage,
MessageStatus = communicationRecipient.Status,
CommunicationId = communicationRecipient.Communication?.Id,
};
if (communicationRecipient.Person?.IsNameless() == true)
{
// if the person is nameless, we'll need to know their number since we don't know their name
communicationRecipientResponse.MessageKey = communicationRecipient.Person?.PhoneNumbers.FirstOrDefault()?.Number;
}
else
{
// If the Person is not nameless, we just need to show their name, not their number
communicationRecipientResponse.MessageKey = null;
}
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
return(communicationRecipientResponseList.OrderBy(a => a.CreatedDateTime).ToList());
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid( bool isExporting = false )
{
_currencyTypes = new Dictionary<int, string>();
_creditCardTypes = new Dictionary<int, string>();
// If configured for a registration and registration is null, return
int registrationEntityTypeId = EntityTypeCache.Read( typeof( Rock.Model.Registration ) ).Id;
if ( ContextTypesRequired.Any( e => e.Id == registrationEntityTypeId ) && _registration == null )
{
return;
}
// If configured for a person and person is null, return
int personEntityTypeId = EntityTypeCache.Read( "Rock.Model.Person" ).Id;
if ( ContextTypesRequired.Any( e => e.Id == personEntityTypeId ) && _person == null )
{
return;
}
// If configured for a batch and batch is null, return
int batchEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialBatch" ).Id;
if ( ContextTypesRequired.Any( e => e.Id == batchEntityTypeId ) && _batch == null )
{
return;
}
// If configured for a batch and batch is null, return
int scheduledTxnEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialScheduledTransaction" ).Id;
if ( ContextTypesRequired.Any( e => e.Id == scheduledTxnEntityTypeId ) && _scheduledTxn == null )
{
return;
}
// Qry
var rockContext = new RockContext();
var qry = new FinancialTransactionService( rockContext ).Queryable();
// Transaction Types
var transactionTypeValueIdList = GetAttributeValue( "TransactionTypes" ).SplitDelimitedValues().AsGuidList().Select( a => DefinedValueCache.Read( a ) ).Where( a => a != null ).Select( a => a.Id ).ToList();
if ( transactionTypeValueIdList.Any() )
{
qry = qry.Where( t => transactionTypeValueIdList.Contains( t.TransactionTypeValueId ) );
}
// Set up the selection filter
if ( _batch != null )
{
// If transactions are for a batch, the filter is hidden so only check the batch id
qry = qry.Where( t => t.BatchId.HasValue && t.BatchId.Value == _batch.Id );
// If the batch is closed, do not allow any editing of the transactions
if ( _batch.Status != BatchStatus.Closed && _canEdit )
{
gTransactions.IsDeleteEnabled = _canEdit;
}
else
{
gTransactions.IsDeleteEnabled = false;
}
}
else if ( _scheduledTxn != null )
{
// If transactions are for a batch, the filter is hidden so only check the batch id
qry = qry.Where( t => t.ScheduledTransactionId.HasValue && t.ScheduledTransactionId.Value == _scheduledTxn.Id );
gTransactions.IsDeleteEnabled = false;
}
else if ( _registration != null )
{
qry = qry
.Where( t => t.TransactionDetails
.Any( d =>
d.EntityTypeId.HasValue &&
d.EntityTypeId.Value == registrationEntityTypeId &&
d.EntityId.HasValue &&
d.EntityId.Value == _registration.Id ) );
gTransactions.IsDeleteEnabled = false;
}
else // Person
{
// otherwise set the selection based on filter settings
if ( _person != null )
{
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService( rockContext ).Queryable().Where( a => a.Person.GivingId == _person.GivingId ).Select( a => a.Id ).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
qry = qry.Where( t => t.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.AuthorizedPersonAliasId.Value) );
}
// Date Range
var drp = new DateRangePicker();
drp.DelimitedValues = gfTransactions.GetUserPreference( "Date Range" );
if ( drp.LowerValue.HasValue )
{
qry = qry.Where( t => t.TransactionDateTime >= drp.LowerValue.Value );
}
if ( drp.UpperValue.HasValue )
{
DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 );
qry = qry.Where( t => t.TransactionDateTime < upperDate );
}
// Amount Range
var nre = new NumberRangeEditor();
nre.DelimitedValues = gfTransactions.GetUserPreference( "Amount Range" );
if ( nre.LowerValue.HasValue )
{
qry = qry.Where( t => t.TransactionDetails.Sum( d => d.Amount ) >= nre.LowerValue.Value );
}
if ( nre.UpperValue.HasValue )
{
qry = qry.Where( t => t.TransactionDetails.Sum( d => d.Amount ) <= nre.UpperValue.Value );
}
// Transaction Code
string transactionCode = gfTransactions.GetUserPreference( "Transaction Code" );
if ( !string.IsNullOrWhiteSpace( transactionCode ) )
{
qry = qry.Where( t => t.TransactionCode == transactionCode.Trim() );
}
// Account Id
var accountIds = ( gfTransactions.GetUserPreference( "Account" ) ?? "" ).SplitDelimitedValues().AsIntegerList().Where( a => a > 0 ).ToList();
{
if ( accountIds.Any() )
{
qry = qry.Where( t => t.TransactionDetails.Any( d => accountIds.Contains( d.AccountId ) || ( d.Account.ParentAccountId.HasValue && accountIds.Contains( d.Account.ParentAccountId.Value ) ) ) );
}
}
// Transaction Type
int transactionTypeId = int.MinValue;
if ( int.TryParse( gfTransactions.GetUserPreference( "Transaction Type" ), out transactionTypeId ) )
{
qry = qry.Where( t => t.TransactionTypeValueId == transactionTypeId );
}
// Currency Type
int currencyTypeId = int.MinValue;
if ( int.TryParse( gfTransactions.GetUserPreference( "Currency Type" ), out currencyTypeId ) )
{
qry = qry.Where( t => t.FinancialPaymentDetail != null && t.FinancialPaymentDetail.CurrencyTypeValueId == currencyTypeId );
}
// Credit Card Type
int creditCardTypeId = int.MinValue;
if ( int.TryParse( gfTransactions.GetUserPreference( "Credit Card Type" ), out creditCardTypeId ) )
{
qry = qry.Where( t => t.FinancialPaymentDetail != null && t.FinancialPaymentDetail.CreditCardTypeValueId == creditCardTypeId );
}
// Source Type
int sourceTypeId = int.MinValue;
if ( int.TryParse( gfTransactions.GetUserPreference( "Source Type" ), out sourceTypeId ) )
{
qry = qry.Where( t => t.SourceTypeValueId == sourceTypeId );
}
// Campus
if ( this.ContextEntity() == null )
{
var campus = CampusCache.Read( gfTransactions.GetUserPreference( "Campus" ).AsInteger() );
if ( campus != null )
{
qry = qry.Where( b => b.Batch != null && b.Batch.CampusId == campus.Id );
}
}
if ( !( this.ContextEntity() is Person ) )
{
var filterPersonId = gfTransactions.GetUserPreference( "Person" ).AsIntegerOrNull();
if ( filterPersonId.HasValue )
{
// get the transactions for the person or all the members in the person's giving group (Family)
var filterPerson = new PersonService( rockContext ).Get( filterPersonId.Value );
if ( filterPerson != null )
{
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService( rockContext ).Queryable().Where( a => a.Person.GivingId == filterPerson.GivingId ).Select( a => a.Id ).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
qry = qry.Where( t => t.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains( t.AuthorizedPersonAliasId.Value ) );
}
}
}
}
SortProperty sortProperty = gTransactions.SortProperty;
if ( sortProperty != null )
{
if ( sortProperty.Property == "TotalAmount" )
{
if ( sortProperty.Direction == SortDirection.Ascending )
{
qry = qry.OrderBy( t => t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.00M );
}
else
{
qry = qry.OrderByDescending( t => t.TransactionDetails.Sum( d => (decimal?)d.Amount ) ?? 0.0M );
}
}
else
{
qry = qry.Sort( sortProperty );
}
}
else
{
// Default sort by Id if the transations are seen via the batch,
// otherwise sort by descending date time.
if ( ContextTypesRequired.Any( e => e.Id == batchEntityTypeId ) )
{
qry = qry.OrderBy( t => t.Id );
}
else
{
qry = qry.OrderByDescending( t => t.TransactionDateTime ).ThenByDescending( t => t.Id );
}
}
var lTransactionImageField = gTransactions.ColumnsOfType<RockLiteralField>().FirstOrDefault( a => a.ID == "lTransactionImage" );
var summaryField = gTransactions.ColumnsOfType<RockBoundField>().FirstOrDefault( a => a.DataField == "Summary" );
var showImages = bddlOptions.SelectedValue.AsIntegerOrNull() == 1;
if ( lTransactionImageField != null )
{
lTransactionImageField.Visible = showImages;
}
if ( summaryField != null )
{
summaryField.Visible = !showImages;
}
if ( showImages )
{
qry = qry.Include( a => a.Images );
}
_isExporting = isExporting;
gTransactions.SetLinqDataSource( qry.AsNoTracking() );
gTransactions.DataBind();
_isExporting = false;
if ( _batch == null &&
_scheduledTxn == null &&
_registration == null &&
_person == null )
{
pnlSummary.Visible = true;
// No context - show account summary
var qryTransactionDetails = qry.SelectMany( a => a.TransactionDetails );
var qryFinancialAccount = new FinancialAccountService( rockContext ).Queryable();
var accountSummaryQry = qryTransactionDetails.GroupBy( a => a.AccountId ).Select( a => new
{
AccountId = a.Key,
TotalAmount = (decimal?)a.Sum( d => d.Amount )
} ).Join( qryFinancialAccount, k1 => k1.AccountId, k2 => k2.Id, ( td, fa ) => new { td.TotalAmount, fa.Name, fa.Order, fa.Id } );
// check for filtered accounts
var accountIds = ( gfTransactions.GetUserPreference( "Account" ) ?? "" ).SplitDelimitedValues().AsIntegerList().Where( a => a > 0 ).ToList();
if ( accountIds.Any() )
{
accountSummaryQry = accountSummaryQry.Where( a => accountIds.Contains( a.Id ) ).OrderBy( a => a.Order );
lbFiltered.Text = "Filtered Account List";
lbFiltered.Visible = true;
}
else
{
lbFiltered.Visible = false;
}
var summaryList = accountSummaryQry.ToList();
var grandTotalAmount = ( summaryList.Count > 0 ) ? summaryList.Sum( a => a.TotalAmount ?? 0 ) : 0;
lGrandTotal.Text = grandTotalAmount.FormatAsCurrency();
rptAccountSummary.DataSource = summaryList.Select( a => new { a.Name, TotalAmount = a.TotalAmount.FormatAsCurrency() } ).ToList();
rptAccountSummary.DataBind();
}
else
{
pnlSummary.Visible = false;
}
}
/// <summary>
/// Gets the giving automation source transaction query by giving identifier.
/// </summary>
/// <param name="givingId">The giving identifier.</param>
/// <returns>IQueryable<FinancialTransaction>.</returns>
public IQueryable <FinancialTransaction> GetGivingAutomationSourceTransactionQueryByGivingId(string givingId)
{
var givingIdPersonAliasIdQuery = new PersonAliasService(this.Context as RockContext).Queryable().Where(a => a.Person.GivingId == givingId).Select(a => a.Id);
return(GetGivingAutomationSourceTransactionQuery().Where(a => a.AuthorizedPersonAliasId.HasValue && givingIdPersonAliasIdQuery.Contains(a.AuthorizedPersonAliasId.Value)));
}
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 );
Person targetPerson = CurrentPerson;
// get excluded currency types setting
List<Guid> excludedCurrencyTypes = new List<Guid>();
if ( GetAttributeValue( "ExcludedCurrencyTypes" ).IsNotNullOrWhitespace() )
{
excludedCurrencyTypes = GetAttributeValue( "ExcludedCurrencyTypes" ).Split( ',' ).Select( Guid.Parse ).ToList();
}
if ( GetAttributeValue( "AllowPersonQuerystring" ).AsBoolean() )
{
if ( !string.IsNullOrWhiteSpace( Request["PersonGuid"] ) )
{
Guid? personGuid = Request["PersonGuid"].AsGuidOrNull();
if ( personGuid.HasValue )
{
var person = new PersonService( rockContext ).Get( personGuid.Value );
if ( person != null )
{
targetPerson = person;
}
}
}
}
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService( rockContext ).Queryable().Where( a => a.Person.GivingId == targetPerson.GivingId ).Select( a => a.Id ).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where( t => t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains( t.Transaction.AuthorizedPersonAliasId.Value ) );
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 ) );
}
if ( excludedCurrencyTypes.Count > 0 )
{
qry = qry.Where( t => !excludedCurrencyTypes.Contains( t.Transaction.FinancialPaymentDetail.CurrencyTypeValue.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 == targetPerson.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 mailingAddress = targetPerson.GetMailingLocation();
if ( mailingAddress != null )
{
mergeFields.Add( "StreetAddress1", mailingAddress.Street1 );
mergeFields.Add( "StreetAddress2", mailingAddress.Street2 );
mergeFields.Add( "City", mailingAddress.City );
mergeFields.Add( "State", mailingAddress.State );
mergeFields.Add( "PostalCode", mailingAddress.PostalCode );
mergeFields.Add( "Country", mailingAddress.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 => new { t.Account.Name, t.Account.PublicName, t.Account.Description })
.Select(s => new AccountSummary
{
AccountName = s.Key.Name,
PublicName = s.Key.PublicName,
Description = s.Key.Description,
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 == targetPerson.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();
}
}
private void DisplayResults()
{
var numberOfYears = GetAttributeValue( "MaxYearsToDisplay" ).AsInteger();
RockContext rockContext = new RockContext();
FinancialTransactionDetailService financialTransactionDetailService = new FinancialTransactionDetailService( rockContext );
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService( rockContext ).Queryable().Where( a => a.Person.GivingId == TargetPerson.GivingId ).Select( a => a.Id ).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where( t=> t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains( t.Transaction.AuthorizedPersonAliasId.Value ) );
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();
}
}
public IQueryable<FinancialTransaction> GetByGivingId( string givingId )
{
if ( string.IsNullOrWhiteSpace( givingId ) || !( givingId.StartsWith( "P" ) || givingId.StartsWith( "G" ) ) )
{
var response = new HttpResponseMessage( HttpStatusCode.BadRequest );
response.Content = new StringContent( "The supplied givingId is not valid" );
throw new HttpResponseException( response );
}
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService( (RockContext)this.Service.Context ).Queryable().Where( a => a.Person.GivingId == givingId ).Select( a => a.Id ).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
return Get().Where( t => t.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains( t.AuthorizedPersonAliasId.Value ) );
}
