/// <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 ) ); }