Lava
<center><h2>Please select a team member below to donate directly to their trip fund or select the <i>Mission Team Icon</i> to make an all team donation.</h2></center><br><br>
<style>
.fundraiser-card-col {
margin-bottom: 30px;
}
.fundraiser-card {
border-radius: 8px;
overflow: hidden;
box-shadow: 0 4px 6px rgba(0,0,0,0.15);
max-width: 32rem;
margin: 0 auto;
background: #fff;
}
.fundraiser-card-body {
padding: 24px 16px;
}
.fundraiser-name {
font-weight: bold;
font-size: 18px;
margin: 0 0 4px 0;
}
.fundraiser-row {
display: flex;
flex-wrap: wrap;
}
</style>
{% assign groupId = 'Global' | PageParameter:'GroupId' | AsInteger | Default:0 %}
{% comment %}To hardcode this page eliminate the line above with the PageParameter and set the line below as {% group where:'Id == XXXXX' %}
with the XXXXX as YOUR Group Id number. Casting through AsInteger also protects the {% sql %} blocks below from SQL injection via the
querystring.{% endcomment %}
{% group where:'Id == {{ groupId }}' %}
{% assign groupTypeId = group.GroupTypeId %}
{% assign accountId = group | Attribute:'FinancialAccount','Id' | AsInteger | Default:0 %}
{% assign participationMode = group | Attribute:'ParticipationType' %}
{% endgroup %}
{% if groupId > 0 %}
<div class="row fundraiser-row">
{% groupmember where:'GroupId == {{ groupId }} && GroupRoleId == 65' %} <!--Change this to your Team Donation Group Role Id -->
{% assign teamProfiles = groupmemberItems %}
{% endgroupmember %}
{% groupmember where:'GroupId == {{ groupId }} && GroupRoleId != 65' %} <!--Change this to your Team Donation Group Role Id -->
{% assign regularMembers = groupmemberItems | OrderBy:'Person.LastName,Person.NickName' %}
{% endgroupmember %}
{% assign renderList = teamProfiles | Concat:regularMembers %}
{% for member in renderList %}
{% if member.GroupMemberStatus == "Inactive" %}{% continue %}{% endif %}
{% assign memberPerson = member.Person %}
{% assign photoUrl = member.Person.PhotoUrl %}
{% comment %}
Member goal: member-level override (qualified to this group type) first, then the group-level default.
OUTER APPLY + TOP 1 guarantees exactly one row, so no duplicate attributes can sneak in.
{% endcomment %}
{% sql %}
SELECT COALESCE(mg.Goal, gg.Goal, 0) AS MemberGoal
FROM [GroupMember] gm
OUTER APPLY (
SELECT TOP 1 TRY_CAST(avm.Value AS DECIMAL(18,2)) AS Goal
FROM [Attribute] am
INNER JOIN [AttributeValue] avm ON avm.AttributeId = am.Id AND avm.EntityId = gm.Id
WHERE am.[Key] = 'IndividualFundraisingGoal'
AND am.EntityTypeId = (SELECT Id FROM EntityType WHERE Name = 'Rock.Model.GroupMember')
AND am.EntityTypeQualifierColumn = 'GroupTypeId'
AND am.EntityTypeQualifierValue = '{{ groupTypeId }}'
AND TRY_CAST(avm.Value AS DECIMAL(18,2)) > 0
) mg
OUTER APPLY (
SELECT TOP 1 TRY_CAST(COALESCE(avg.Value, ag.DefaultValue) AS DECIMAL(18,2)) AS Goal
FROM [Attribute] ag
LEFT JOIN [AttributeValue] avg ON avg.AttributeId = ag.Id AND avg.EntityId = {{ groupId }}
WHERE ag.[Key] = 'IndividualFundraisingGoal'
AND ag.EntityTypeId = (SELECT Id FROM EntityType WHERE Name = 'Rock.Model.Group')
AND ag.EntityTypeQualifierColumn = 'GroupTypeId'
AND ag.EntityTypeQualifierValue = '{{ groupTypeId }}'
) gg
WHERE gm.Id = {{ member.Id }}
{% endsql %}
{% assign memberGoal = results[0].MemberGoal | AsDecimal | Default:0 %}
{% sql %}
SELECT ISNULL(SUM(ftd.Amount), 0) AS AmountRaised
FROM [FinancialTransactionDetail] ftd
INNER JOIN [FinancialTransaction] ft ON ft.Id = ftd.TransactionId
WHERE ftd.EntityTypeId = 90
AND ftd.EntityId = {{ member.Id }}
AND ftd.AccountId = {{ accountId }}
{% endsql %}
{% assign memberRaised = results[0].AmountRaised | AsDecimal | Default:0 %}
{% if memberGoal > 0 %}
{% assign memberPercent = memberRaised | DividedBy:memberGoal | Times:100 %}
{% else %}
{% assign memberPercent = 0 %}
{% endif %}
{% assign memberPercentWidth = memberPercent | AtMost:100 %}
{% assign memberPercentRounded = memberPercent | Floor %}
{% if memberPercent >= 100 %}
{% assign memberBarClass = 'progress-bar' %}
{% else %}
{% assign memberBarClass = 'progress-bar progress-bar-success' %}
{% endif %}
{% assign showFamilyBar = false %}
{% assign familyGoal = 0 %}
{% assign familyRaised = 0 %}
{% assign familyPercentWidth = 0 %}
{% assign familyPercentRounded = 0 %}
{% assign familyBarClass = 'progress-bar progress-bar-success' %}
{% if participationMode == 'Family' %}
{% comment %}
Family totals: each member's raised amount and goal are calculated separately (OUTER APPLY)
and then summed, so a member with multiple gifts no longer has their goal counted multiple times.
{% endcomment %}
{% sql %}
SELECT
COUNT(*) AS FamilyMemberCount,
ISNULL(SUM(r.Raised), 0) AS FamilyRaised,
ISNULL(SUM(COALESCE(mg.Goal, gg.Goal, 0)), 0) AS FamilyGoal
FROM [GroupMember] gm
INNER JOIN [Person] p ON p.Id = gm.PersonId
OUTER APPLY (
SELECT ISNULL(SUM(ftd.Amount), 0) AS Raised
FROM [FinancialTransactionDetail] ftd
WHERE ftd.EntityTypeId = 90
AND ftd.EntityId = gm.Id
AND ftd.AccountId = {{ accountId }}
) r
OUTER APPLY (
SELECT TOP 1 TRY_CAST(avm.Value AS DECIMAL(18,2)) AS Goal
FROM [Attribute] am
INNER JOIN [AttributeValue] avm ON avm.AttributeId = am.Id AND avm.EntityId = gm.Id
WHERE am.[Key] = 'IndividualFundraisingGoal'
AND am.EntityTypeId = (SELECT Id FROM EntityType WHERE Name = 'Rock.Model.GroupMember')
AND am.EntityTypeQualifierColumn = 'GroupTypeId'
AND am.EntityTypeQualifierValue = '{{ groupTypeId }}'
AND TRY_CAST(avm.Value AS DECIMAL(18,2)) > 0
) mg
OUTER APPLY (
SELECT TOP 1 TRY_CAST(COALESCE(avg.Value, ag.DefaultValue) AS DECIMAL(18,2)) AS Goal
FROM [Attribute] ag
LEFT JOIN [AttributeValue] avg ON avg.AttributeId = ag.Id AND avg.EntityId = {{ groupId }}
WHERE ag.[Key] = 'IndividualFundraisingGoal'
AND ag.EntityTypeId = (SELECT Id FROM EntityType WHERE Name = 'Rock.Model.Group')
AND ag.EntityTypeQualifierColumn = 'GroupTypeId'
AND ag.EntityTypeQualifierValue = '{{ groupTypeId }}'
) gg
WHERE gm.GroupId = {{ groupId }}
AND gm.GroupMemberStatus = 1
AND gm.IsArchived = 0
AND p.PrimaryFamilyId = (SELECT PrimaryFamilyId FROM Person WHERE Id = {{ memberPerson.Id }})
{% endsql %}
{% assign familyMemberCount = results[0].FamilyMemberCount | AsInteger | Default:1 %}
{% if familyMemberCount > 1 %}
{% assign showFamilyBar = true %}
{% assign familyRaised = results[0].FamilyRaised | AsDecimal | Default:0 %}
{% assign familyGoal = results[0].FamilyGoal | AsDecimal | Default:0 %}
{% if familyGoal > 0 %}
{% assign familyPercent = familyRaised | DividedBy:familyGoal | Times:100 %}
{% else %}
{% assign familyPercent = 0 %}
{% endif %}
{% assign familyPercentWidth = familyPercent | AtMost:100 %}
{% assign familyPercentRounded = familyPercent | Floor %}
{% if familyPercent >= 100 %}
{% assign familyBarClass = 'progress-bar' %}
{% endif %}
{% endif %}
{% endif %}
<div class="col-xs-12 col-sm-6 col-md-3 fundraiser-card-col">
<div class="fundraiser-card">
{% if photoUrl != "" %}
<div style="width: 100%; padding-bottom: 100%; position: relative; background-image: url('{{ photoUrl }}&h=500&w=500&mode=crop&anchor=middle&scale=both'); background-size: cover; background-position: center; background-repeat: no-repeat;"
role="img" aria-label="{{ memberPerson.NickName }} {{ memberPerson.LastName }} Picture"></div>
{% endif %}
<div class="fundraiser-card-body">
<h5 class="fundraiser-name">{{ memberPerson.NickName }} {{ memberPerson.LastName }}</h5>
{% if showFamilyBar and familyGoal > 0 %}
<div class="margin-b-sm">
<div class="clearfix" style="margin-bottom: 2px;">
<small class="pull-left text-muted"><strong>{{ memberPerson.LastName }} Family:</strong> {{ familyRaised | FormatAsCurrency }} raised</small>
<small class="pull-right text-muted">Goal: {{ familyGoal | FormatAsCurrency }}</small>
</div>
<div class="progress" style="height: 14px; border-radius: 4px; margin-bottom: 4px;">
{% if familyPercentWidth > 0 %}
<div class="{{ familyBarClass }}" role="progressbar" aria-valuenow="{{ familyPercentRounded }}"
aria-valuemin="0" aria-valuemax="100" style="width: {{ familyPercentWidth }}%; height: 14px; line-height: 14px; font-size: 11px; font-weight: bold;">{{ familyPercentRounded }}%</div>
{% else %}
<div class="progress-bar progress-bar-success" role="progressbar" aria-valuenow="0" aria-valuemin="0"
aria-valuemax="100" style="width: 2%; min-width: 2px; height: 14px;"><span class="sr-only">0%</span></div>
{% endif %}
</div>
</div>
{% elsif memberGoal > 0 %}
<div class="margin-b-sm">
<div class="clearfix" style="margin-bottom: 2px;">
<small class="pull-left text-muted">{{ memberRaised | FormatAsCurrency }} raised</small>
<small class="pull-right text-muted">Goal: {{ memberGoal | FormatAsCurrency }}</small>
</div>
<div class="progress" style="height: 14px; border-radius: 4px; margin-bottom: 4px;">
{% if memberPercentWidth > 0 %}
<div class="{{ memberBarClass }}" role="progressbar" aria-valuenow="{{ memberPercentRounded }}"
aria-valuemin="0" aria-valuemax="100" style="width: {{ memberPercentWidth }}%; height: 14px; line-height: 14px; font-size: 11px; font-weight: bold;">{{ memberPercentRounded }}%</div>
{% else %}
<div class="progress-bar progress-bar-success" role="progressbar" aria-valuenow="0" aria-valuemin="0"
aria-valuemax="100" style="width: 2%; min-width: 2px; height: 14px;"><span class="sr-only">0%</span></div>
{% endif %}
</div>
</div>
{% endif %}
{% capture link %}{{ 'Global' | Attribute:'PublicApplicationRoot' }}page/473?GroupId={{ groupId }}&GroupMemberId={{ member.Id }}{% endcapture %}
<p><a href="{{ link }}" class="btn btn-primary">View Participant Page</a></p>
</div>
</div>
</div>
{% endfor %}
</div>
{% else %}
{% if CurrentPersonCanEdit %}
<div class="alert alert-warning" role="alert">
<p><strong>Group id is not set.</strong><br />Make sure to add a Group ID.</p>
</div>
{% endif %}
{% endif %}