private IQueryable <VolunteerByCampus> GetCheckedInVols()
{
RockContext rockContext = new RockContext();
rockContext.SqlLogging(true);
rockContext.Database.CommandTimeout = GetAttributeValue("CommandTimeout").AsIntegerOrNull() ?? 300;
var volunteers = rockContext.Database.SqlQuery <VolunteerByCampus>(@"SELECT
PersonAliasId AS [VolunteerPerson ], COALESCE(a.CampusId, g.campusId) AS [CampusId]
FROM( Attendance a
join AttendanceOccurrence ao on a.OccurrenceId = ao.id
JOIN[Group] g on ao.GroupId = g.id
join AttributeValue av on g.Id = av.EntityId and av.AttributeId = 10946)
WHERE StartDateTime > '2019-11-10'
AND av.Value = 'True'" ).AsQueryable();
volunteers = volunteers.Where(v => v.CampusId.Equals(1));
return(volunteers);
// COALESCE(a.CampusId, g.campusId) AS [CampusId]
}
private IQueryable <GivingGroup> GetGivingGroups()
{
RockContext rockContext = new RockContext();
rockContext.SqlLogging(true);
int contributionTypeId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION).Id;
DateTime lower = SqlDateTime.MinValue.Value;
DateTime upper = SqlDateTime.MaxValue.Value;
// Filter by Last Gift DateRange
if (drpDates.LowerValue.HasValue || drpDates.UpperValue.HasValue)
{
lower = (drpDates.LowerValue.HasValue ? drpDates.LowerValue.Value : SqlDateTime.MinValue.Value);
upper = (drpDates.UpperValue.HasValue ? drpDates.UpperValue.Value.AddDays(1) : SqlDateTime.MaxValue.Value);
}
rockContext.Database.CommandTimeout = GetAttributeValue("CommandTimeout").AsIntegerOrNull() ?? 300;
var givingGroups = rockContext.Database.SqlQuery <GivingGroup>(@"
SELECT
max([GivingGroups].[TransactionDateTime] ) AS [LastGift],
[GivingGroups].[GivingId] AS [GivingId],
[GivingGroups].[GivingGroupName] AS [GivingGroupName]
FROM( SELECT
[FT].[TransactionDateTime] AS [TransactionDateTime],
[FT].[TransactionTypeValueId] AS [TransactionTypeValueId],
[P].[GivingId] AS [GivingId],
CASE WHEN ([P].[GivingId] LIKE N'G%') THEN [G].[Name]
ELSE CASE WHEN( [P].[NickName] IS NULL ) THEN N''
ELSE [P].[NickName] END + N' ' + CASE WHEN( [P].[LastName] IS NULL ) THEN N'' ELSE [P].[LastName] END END AS [GivingGroupName]
FROM [dbo].[FinancialTransaction] AS [FT]
LEFT OUTER JOIN[dbo].[PersonAlias] AS [PA] ON [FT].[AuthorizedPersonAliasId] = [PA].[Id]
LEFT OUTER JOIN[dbo].[Person] AS [P] ON [PA].[PersonId] = [P].[Id]
LEFT OUTER JOIN[dbo].[Group] AS [G] ON [P].[GivingGroupId] = [G].[Id]
WHERE [FT].[TransactionTypeValueId] = @p0
AND (@p1 is NULL OR [FT].[TransactionDateTime] >= @p1)
AND (@p2 is NULL OR [FT].[TransactionDateTime] < @p2)
) AS [GivingGroups]
LEFT JOIN (
SELECT av.Value FROM attributevalue av
INNER JOIN AttributeValue srav on av.EntityId = srav.EntityId and srav.AttributeId = 86060
WHERE av.attributeid = 86061 AND srav.Value = @p3
) Existing on Existing.Value = GivingId
WHERE (@p3 IS NULL OR Existing.Value is null)
GROUP by GivingId, GivingGroupName" ,
contributionTypeId, lower, upper, null /*"2019-01-01T00:00:00,2019-12-31T00:00:00"*/).AsQueryable();
// Filter by Giving ID
if (tbGivingId.Text.IsNotNullOrWhiteSpace())
{
givingGroups = givingGroups.Where(gg => gg.GivingId.Contains(tbGivingId.Text));
}
// Filter by Giving Group Name
if (tbGivingGroup.Text.IsNotNullOrWhiteSpace())
{
givingGroups = givingGroups.Where(gg => gg.GivingGroupName.Contains(tbGivingGroup.Text));
}
givingGroups = givingGroups.OrderByDescending(g => g.LastGift);
return(givingGroups);
}