{% sql return:'pending' %}
    SELECT es.Name, COUNT(esi.EntitySetId) AS CallsLeft
      FROM EntitySetItem esi
    	JOIN EntitySet es ON (esi.EntitySetId = es.Id)
     WHERE esi.EntitySetId IN (6353, 6354, 6355) -- CHANGE THESE IDS
     GROUP BY es.Name;
{% endsql %}

{% sql return:'created' %}
    WITH requests AS (
SELECT cr.Id, cr.ConnectionOpportunityId
  FROM ConnectionRequest cr 
 WHERE cr.ConnectionOpportunityId IN (49,50,51) -- CHANGE THESE IDS
)
SELECT COUNT(requests.Id) AS CreatedRequests, requests.ConnectionOpportunityId
  FROM requests
 GROUP BY requests.ConnectionOpportunityId;
{% endsql %}

{% assign adultcallsleft = 0 %}
{% assign kidsidecallsleft = 0 %}
{% assign smcallsleft = 0 %}
{% assign adultcallscreated = created[0].CreatedRequests %}
{% assign kidsidecallscreated = created[1].CreatedRequests %}
{% assign smcallscreated = created[2].CreatedRequests %}

{% for row in pending %}
    {% case pending[forloop.index0].Name %}
        {% when 'Adults' %}
            {% assign adultcallsleft = pending[forloop.index0].CallsLeft %}
        {% when 'Kidside' %}
            {% assign kidsidecallsleft = pending[forloop.index0].CallsLeft %}
        {% when 'Student Ministry' %}
            {% assign smcallsleft = pending[forloop.index0].CallsLeft %}
        {% else %}
    {% endcase %}
{% endfor %}

{% assign totaladultcalls = adultcallsleft | Plus:adultcallscreated %}
{% assign totalkidsidecalls = kidsidecallsleft | Plus:kidsidecallscreated %}
{% assign totalsmcalls = smcallsleft | Plus:smcallscreated %}

{% assign adultpercentage = adultcallscreated | DividedBy:totaladultcalls | Times:100 | AsInteger %}
{% assign kidsidepercentage = kidsidecallscreated | DividedBy:totalkidsidecalls | Times:100 | AsInteger %}
{% assign studentpercentage = smcallscreated | DividedBy:totalsmcalls | Times:100 | AsInteger %}

<div class="row">
    <div class="col-lg-4">
        <div class="metric" style="border: 1px solid #ccc; padding: 12px; margin-bottom: 12px; background: -webkit-linear-gradient(left, #d2f8d2 {{ adultpercentage }}%, white {{ adultpercentage }}%); background: -moz-linear-gradient(left, #d2f8d2 {{ adultpercentage }}%, white {{ adultpercentage }}%); background: -ms-linear-gradient(left, #d2f8d2 {{ adultpercentage }}%, white {{ adultpercentage }}%); background: linear-gradient(left, #d2f8d2 {{ adultpercentage }}%, white {{ adultpercentage }}%);">
            <h5 style="font-size: 24px; margin-top: 0; margin-bottom: 0; width: 100%; white-space: nowrap; overflow: hidden; text-overflow: ellipsis;">Adult Campaign</h5>
            <span class="date">{{ 'Now' | Date:'sd' }}</span>
            <i class="icon fas fa-user" style="float: right; opacity: .3; font-size: 65px; border-radius: 0; width: 85px; height: 65px;"></i>
            <div class="value" style="font-size: 48px; font-weight: 800; line-height: 1em;">
                {{ adultcallsleft }} Calls to Create
                <small style="display: block; font-weight: 300; font-size: 14px; line-height: 1em;">{{ adultpercentage }}% created</small>
            </div>
        </div>
    </div>
    <div class="col-lg-4">
        <div class="metric" style="border: 1px solid #ccc; padding: 12px; margin-bottom: 12px; background: -webkit-linear-gradient(left, #d2f8d2 {{ kidsidepercentage }}%, white {{ kidsidepercentage }}%); background: -moz-linear-gradient(left, #d2f8d2 {{ kidsidepercentage }}%, white {{ kidsidepercentage }}%); background: -ms-linear-gradient(left, #d2f8d2 {{ kidsidepercentage }}%, white {{ kidsidepercentage }}%); background: linear-gradient(left, #d2f8d2 {{ kidsidepercentage }}%, white {{ kidsidepercentage }}%);">
            <h5 style="font-size: 24px; margin-top: 0; margin-bottom: 0; width: 100%; white-space: nowrap; overflow: hidden; text-overflow: ellipsis;">Kidside Campaign</h5>
            <span class="date">{{ 'Now' | Date:'sd' }}</span>
            <i class="icon fas fa-child" style="float: right; opacity: .3; font-size: 65px; border-radius: 0; width: 85px; height: 65px;"></i>
            <div class="value" style="font-size: 48px; font-weight: 800; line-height: 1em;">
                {{ kidsidecallsleft }} Calls to Create
                <small style="display: block; font-weight: 300; font-size: 14px; line-height: 1em;">{{ kidsidepercentage }}% created</small>
            </div>
        </div>
    </div>
    <div class="col-lg-4">
        <div class="metric" style="border: 1px solid #ccc; padding: 12px; margin-bottom: 12px; background: -webkit-linear-gradient(left, #d2f8d2 {{ studentpercentage }}%, white {{ studentpercentage }}%); background: -moz-linear-gradient(left, #d2f8d2 {{ studentpercentage }}%, white {{ studentpercentage }}%); background: -ms-linear-gradient(left, #d2f8d2 {{ studentpercentage }}%, white {{ studentpercentage }}%); background: linear-gradient(left, #d2f8d2 {{ studentpercentage }}%, white {{ studentpercentage }}%);">
            <h5 style="font-size: 24px; margin-top: 0; margin-bottom: 0; width: 100%; white-space: nowrap; overflow: hidden; text-overflow: ellipsis;">Student Ministry Campaign</h5>
            <span class="date">{{ 'Now' | Date:'sd' }}</span>
            <i class="icon fas fa-graduation-cap" style="float: right; opacity: .3; font-size: 65px; border-radius: 0; width: 85px; height: 65px;"></i>
            <div class="value" style="font-size: 48px; font-weight: 800; line-height: 1em;">
                {{ smcallsleft }} Calls to Create
                <small style="display: block; font-weight: 300; font-size: 14px; line-height: 1em;">{{ studentpercentage }}% created</small>
            </div>
        </div>
    </div>
</div>