Recipe - Grade Capacities on Registration Instance

{% definedvalue where:'DefinedTypeId == 51' securityenabled:'false' sort:'Id desc' %}
    {% for definedvalue in definedvalueItems %}
        {% capture valuePair %}{{ definedvalue.Value }}^{{ definedvalue.Description }}{% endcapture %}
        {% if forloop.last %}{{ valuePair }}{% else %}{{ valuePair }},{% endif %}
    {% endfor %}
{% enddefinedvalue %}

{% comment %}
This file calculates and displays the available spots for different school grades in a registration process, based on set capacities on the instance and the number of registrants already registered for those grades. It can be adjusted to either include or exclude grades that are fully booked, based on a user-defined setting.
{% endcomment %}

{% assign host = 'Global' | Page:'Host' %}
{% assign publicAppRoot = 'Global' | Attribute:'PublicApplicationRoot' %}
{% comment %}We only want to hide the at/over capacity grades on the external site during registration. Otherwise we'll see blank grade values on the registrants page.{% endcomment %}
{% if publicAppRoot contains host %}
    {% comment %}Hide grades that are at capacity if we're on the external site.{% endcomment %}
    {% assign hideAtCapacity = 1 %}
{% else %}
    {% comment %}Don't hide if we're not on the external site.{% endcomment %}
    {% assign hideAtCapacity = 0 %}
{% endif %}

{% comment %}Assign variables used in the query.{% endcomment %}
{% comment %}They need to work for any page the attribtue is viewed on for the registrant, not just the external registration page.{% endcomment %}

{% assign registrationInstanceId = 'Global' | PageParameter:'RegistrationInstanceId' %}

{% comment %}If not provided, get instance ID from registrantId - likely on internal site registrant page.{% endcomment %}
{% assign registrantId = 'Global' | PageParameter:'RegistrantId' %}
{% if registrationInstanceId =='' and registrantId !='' %}
    {% registrationregistrant Id:'{{ registrantId }}' securityenabled:'false' %}
        {% assign registrationInstanceId = registrationregistrant.Registration.RegistrationInstanceId %}
    {% endregistrationregistrant %}
{% endif %}

{% comment %}If still not provided, get instance ID from slug - targetting external site where a url slug linkage has been set and page parameter for instance id is not available.{% endcomment %}
{% assign slug = 'Global' | PageParameter:'Slug' %}
{% if registrationInstanceId =='' and slug !='' %}
    {% sql return:'linkages' %}
        SELECT TOP 1
            [RegistrationInstanceId]
        FROM
            [EventItemOccurrenceGroupMap]
        WHERE
            [UrlSlug] = '{{ slug }}'
    {% endsql %}
    {% for linkage in linkages %}
        {% assign registrationInstanceId = linkage.RegistrationInstanceId %}
    {% endfor %}
{% endif %}

{% registrationinstance Id:'{{ registrationInstanceId }}' securityenabled:'false %}
    {% assign registrationTemplateId = registrationinstance.RegistrationTemplateId %}
{% endregistrationinstance %}

{% assign registrationInstanceAttributeId = 48882 %}

{% comment %}The registrationRegistrantAttributeKey is set on the template form field question. It's the actual drop down select field.{% endcomment %}
{% assign registrationRegistrantAttributeKey = 'GradeCapacity' %}

{% sql %}
-- CTE to parse and aggregate capacities by grade
WITH GradeCapacities AS (
    SELECT 
        -- Parsing the grade value from the key-value pair
        CASE 
            WHEN CHARINDEX('^', kv.value) > 0 THEN RIGHT(kv.value, LEN(kv.value) - CHARINDEX('^', kv.value))
            ELSE NULL 
        END AS GradeValue,
        -- Parsing and casting the capacity value from the key-value pair and summing up
        SUM(CAST(CASE 
                WHEN CHARINDEX('^', kv.value) > 0 THEN LEFT(kv.value, CHARINDEX('^', kv.value) - 1) 
                ELSE '0' 
             END AS INT)) AS TotalCapacity
    FROM 
        RegistrationInstance ri
    JOIN 
        AttributeValue av ON ri.Id = av.EntityId AND av.AttributeId = {{ registrationInstanceAttributeId }}
    CROSS APPLY 
        STRING_SPLIT(av.Value, '|') kv
    WHERE 
        ri.Id = {{ registrationInstanceId }}
    GROUP BY 
        CASE 
            WHEN CHARINDEX('^', kv.value) > 0 THEN RIGHT(kv.value, LEN(kv.value) - CHARINDEX('^', kv.value))
            ELSE NULL 
        END
),
-- CTE to count registrants for each grade
RegistrantCount AS (
    SELECT 
        av.Value AS GradeValue,
        COUNT(*) AS RegistrantCount
    FROM 
        RegistrationRegistrant rr
    JOIN 
       AttributeValue av ON rr.Id = av.EntityId
    JOIN 
       Attribute a ON av.AttributeId = a.Id AND a.[Key] = '{{ registrationRegistrantAttributeKey }}'
    JOIN 
        Registration r ON rr.RegistrationId = r.Id
    JOIN 
        RegistrationInstance ri ON r.RegistrationInstanceId = ri.Id
    WHERE 
        ri.RegistrationTemplateId = {{ registrationTemplateId }} AND ri.Id = {{ registrationInstanceId }}
    GROUP BY 
        av.Value
)
-- Main query to select grade capacities and calculate remaining capacity
SELECT 
    gc.GradeValue,
    dv.Value AS DefinedValue,
    dv.Description AS GradeDescription,
    gc.TotalCapacity,
    ISNULL(rc.RegistrantCount, 0) AS RegistrantCount,
    gc.TotalCapacity - ISNULL(rc.RegistrantCount, 0) AS RemainingCapacity
FROM 
    GradeCapacities gc
LEFT JOIN 
    RegistrantCount rc ON gc.GradeValue = rc.GradeValue
JOIN 
    DefinedValue dv ON gc.GradeValue = dv.Value
JOIN 
    DefinedType dt ON dv.DefinedTypeId = dt.Id AND dt.Id = 51
WHERE 
    ({{ hideAtCapacity }} = 0 OR (gc.TotalCapacity - ISNULL(rc.RegistrantCount, 0) > 0))
ORDER BY 
    dv.Id desc
{% endsql %}

{% comment %}Below is what gets rendered in the single select attribute.{% endcomment %}
{% for result in results %}
    {% assign remaining = result.RemainingCapacity %}
    {% capture resultPair %}{{ result.GradeValue }}^{{ result.GradeDescription }} ({{ remaining }} {{ 'Spots' | PluralizeForQuantity:remaining }} Remaining){% endcapture %}
    {% if forloop.last %}{{ resultPair }}{% else %}{{ resultPair }},{% endif %}
{% endfor %}

{% comment %}Including the file{% endcomment %}
{% include "/Content/Lava/eventGradeCapacities.lava" %}