The Goal

A one-stop place for everything you need to know about a registration.

The Code Behind

Page Parameter Block - 2 fields

- Active Registrations (Boolean)

- "Ministry" (code below)

SELECT
    [Id] AS [Value]
    , [Name] AS [Text]
FROM
    [Category]
WHERE
    [Id] IN (1106,1164,1215,1004,1212,1166,972,1224,1207,598,1007,999,1247,973,1246,982,1396,1433,1457,1325,772,967)
ORDER BY
    [Name]

(If I had to do this again, I'd probably use the category's ParentCategoryId instead of a list of Ids)

Dynamic Data Block

Query:

{% assign isActive = 'Global' | PageParameter:'ActiveRegistration' %}
{% if isActive == "False" %}{% assign activeParam = "" %}{% else %}{% assign activeParam = "AND ri.[IsActive] = 1" %}{% endif %}

{% assign catId = 'Global' | PageParameter:'CategoryId' %}{% if catId == empty %}{% assign catId = '771' %}{% endif %}
--DECLARE @cat INT = {{ catId }}


SELECT
    ri.[Id]
    , CONCAT('<a href="{{ 'Global' | Attribute:'InternalApplicationRoot' }}page/2432?RegistrationInstanceId=',ri.[Id],'">',ri.[Name],'</a>') AS [Registration]
    , CONCAT('<a href="{{ 'Global' | Attribute:'PublicApplicationRoot' }}Registration?RegistrationInstanceId=',ri.[Id],'">',ri.[Name],'</a>') AS [Form Link]
    , FORMAT(ri.StartDateTime,'d') AS [Start Date]
    , FORMAT(ri.EndDateTime,'d') AS [End Date]
    , rt.[WaitListEnabled] AS [Waitlist]
    , CASE WHEN rt.[SetCostOnInstance] = 1 THEN FORMAT(ri.[Cost],'C') ELSE FORMAT(rt.[Cost],'C') END AS [Cost]
    , CONCAT(COUNT(rr.[Id]),'/', CASE WHEN ri.[MaxAttendees] IS NULL THEN 0 ELSE ri.[MaxAttendees] END) AS [Registrants]  
    , CASE WHEN coalesce(ri.[Cost] + rt.[Cost],ri.[Cost],rt.[Cost],0) > 0 AND fa.[Id] = 4 THEN CONCAT('<span style="color: red;">',fa.[Name],'</span>') ELSE fa.[Name] END AS [PaymentAccount]
    , ri.[ContactEmail]
FROM
    [RegistrationInstance] ri
    INNER JOIN [RegistrationTemplate] rt ON rt.[Id] = ri.[RegistrationTemplateId]
    INNER JOIN [Category] cat ON cat.[Id] = rt.[CategoryId]
    LEFT JOIN [Category] pcat ON pcat.[Id] = cat.[ParentCategoryId]
    LEFT JOIN [Category] ppcat ON ppcat.[Id] = pcat.[ParentCategoryId]
    LEFT JOIN [Category] pppcat ON pppcat.[Id] = ppcat.[ParentCategoryId]
    LEFT JOIN [Registration] r ON r.[RegistrationInstanceId] = ri.[Id]
    LEFT JOIN [RegistrationRegistrant] rr ON rr.[RegistrationId] = r.[Id]
    LEFT JOIN [FinancialAccount] fa ON fa.[Id] = ri.[AccountId]
    LEFT JOIN [FinancialGateway] fg ON fg.[Id] = rt.[FinancialGatewayId]
WHERE
    (cat.[Id] IN ({{ catId }})
    OR pcat.[Id] IN ({{ catId }})
    OR ppcat.[Id] IN ({{ catId }})
    OR pppcat.[Id] IN ({{ catId }}))
    {{ activeParam }}
GROUP BY
    ri.[Id], ri.[Name], ri.[StartDateTime], ri.[EndDateTime], fa.[Name], ri.[MaxAttendees], ri.[Cost], rt.[Cost], rt.[SetCostOnInstance], rt.[WaitListEnabled], ri.[ContactEmail], fg.[Name], fa.[Id]
ORDER BY
    ri.[StartDateTime] DESC