Upcoming Staff Celebrations

Shared by Jeff Richmond, The Well Community Church
2 years ago

12.0 Operations Intermediate

Description

This recipe displays a list of upcoming staff birthdays, anniversaries and workiversaries (yes, it's a word) with the person's photo, name, and date of the milestone. For anniversaries and workiversaries, it displays how many years they've been married or on staff. On the day of, it also displays a special "happy birthday/anniversary/workiversary" message.

I know there are already a couple other recipes for displaying upcoming staff birthdays (etc.) on here, but since I've had three separate requests for my solution in the past few months, I figured I should just go ahead and finish up the last 10% to make an actual recipe out of it.

If my solution isn't quite what you were looking for or you just want to see other options, be sure to check out these awesome recipes:

(Please let me know if there are any other similar recipes I may have missed)

A Note on Widowed Staff Members

This recipe will still display upcoming anniversaries for staff members whose spouse has passed away unless they change their marital status to something other than "Married" and/or they remove the anniversary date from their profile.

Because some people prefer to keep their marital status and anniversary date even after losing a spouse, we have also added a "Widowed" tag to our own Rock instance, and then added an additional filter to the upcoming anniversaries to make sure they are excluded from the list. This functionality is not included in this recipe, but please don't hesitate to let me know if you're interested in more info.

Credit

I can't remember for sure, but I think my solution may have actually started out life as an implementation of Brent's recipe. There are at least some definite layout similarities between the two. So if you like my recipe, Brent may deserve a good portion of the credit. If you don't, then he had nothing to do with it!

Prerequisites

Staff Person Tag

We identify staff with an person tag:

Name: Staff
Scope: Organizational
Entity Type: Person

If you keep track of staff another way, you'll need to either create a Staff tag or modify the code in this recipe to work with your situation. You can also check out Brent's recipe for a solution that uses the RSR - Staff Workers security role (group), or Randy's recipe for a solution that uses a data view.

Staff Hire/Exit Date Attributes

You'll also need a way to keep track of the staff hire and exit dates. This recipe assumes those values are stored in person attributes.

Name: Staff Hire Date and Staff Exit Date
Key: StaffHireDate and StaffExitDate
Show on Bulk: Yes
Field Type: Date

How-To

Create a new Dynamic Data block on your internal Rock home page.

Block Properties:

Update Page: No
Enabled Lava Commands: Cache

Block Criteria:

Query:

There are some changes you'll need to make in the query.

On lines 4 and 5, change the hire and exit date attribute IDs to match your system.
On line 10, change the time zone to your own. Our database likes to return the current time using the UTC time zone, so we have to force dates to Pacific Time. If you're not Pacific, you'll need to change that.
Finally, on line 15, the number of days to look ahead is currently set to 21, so you may want to adjust that too.

DECLARE @ChildClassID AS int = 2
DECLARE @HireDateID AS int = 8215
DECLARE @ExitDateID AS int = 8216
DECLARE @SlackUserAttrID AS int = 18929
--------------------------------------------------------------------------

DECLARE @Gender AS table ( ID int, Gender varchar(6) )
INSERT INTO @Gender SELECT 1, 'male' UNION SELECT 2, 'female'

DECLARE @StaffTagID AS int
SELECT @StaffTagID = ID FROM Tag WHERE Name = 'Staff'

DECLARE @Today AS datetime2 = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
DECLARE @ThisYear AS int = YEAR(@Today)
DECLARE @ThisMonth AS int = MONTH(@Today)
DECLARE @TodayNum AS int = DAY(@Today)
DECLARE @TodayOfYear AS int = DATEPART(dayofyear, @Today)
DECLARE @DayRange AS int = 21

SELECT A.ID, A.NickName, A.NickName + ' ' + A.LastName AS FullName, A.PhotoID, A.Email, ISNULL(SV.Value, '') AS SlackUserID,
    A.EventMonth, A.EventDay, A.DaysUntilEvent, A.Years, A.Gender AS GenderID,
    IIF(A.AgeClassification = @ChildClassID, 'child-', '') + ISNULL(G.Gender, 'unknown') AS Gender,
    A.UpcomingBirthday, A.UpcomingAnniversary, A.UpcomingWorkiversary
FROM (
    SELECT P.ID, P.NickName, P.LastName, P.PhotoId, P.Email, P.AgeClassification, P.Gender,
        P.BirthYear AS EventYear, P.BirthMonth AS EventMonth, P.BirthDay AS EventDay, 
        CASE WHEN P.BirthMonth = @ThisMonth AND P.BirthDay = @TodayNum THEN 0
            ELSE DATEDIFF(day, @Today, CA.NextDate) --P.DaysUntilBirthday
        END AS DaysUntilEvent,
        CASE 
            WHEN MONTH(@Today) > P.BirthMonth 
                THEN DATEDIFF(year, DATEFROMPARTS(P.BirthYear, P.BirthMonth, P.BirthDay), @Today) + 1
            ELSE
                DATEDIFF(year, DATEFROMPARTS(P.BirthYear, P.BirthMonth, P.BirthDay), @Today) 
        END AS Years,
        CAST(1 AS bit) AS UpcomingBirthday, 
        CAST(0 AS bit) AS UpcomingAnniversary, 
        CAST(0 AS bit) AS UpcomingWorkiversary
    FROM Person AS P INNER JOIN 
        TaggedItem AS T on P.Guid = T.EntityGuid AND T.TagID = @StaffTagID CROSS APPLY 
        --(SELECT DATEFROMPARTS(CASE WHEN P.BirthMonth > @ThisMonth OR (P.BirthMonth = @ThisMonth AND P.BirthDay >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, P.BirthMonth, P.BirthDay) AS NextDate) AS CA
        (SELECT DATEFROMPARTS(
            CASE WHEN P.BirthMonth > @ThisMonth OR (P.BirthMonth = @ThisMonth AND P.BirthDay >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, 
            P.BirthMonth, 
            CASE WHEN P.BirthMonth = 2 AND P.BirthDay = 29 AND ISDATE(CAST(@ThisYear AS varchar) + '-02-29') = 0 THEN 28 ELSE P.BirthDay END) AS NextDate) AS CA
    WHERE P.BirthMonth <> '' AND P.BirthYear <> ''
        AND (P.DaysUntilBirthday <= @DayRange OR (P.BirthMonth = @ThisMonth AND P.BirthDay = @TodayNum))
    UNION ALL
    SELECT P.Id, P.NickName, P.LastName, P.PhotoId, P.Email, P.AgeClassification, P.Gender, 
        YEAR(P.AnniversaryDate) AS EventYear, MONTH(P.AnniversaryDate) AS EventMonth, DAY(P.AnniversaryDate) AS EventDay, 
        CASE WHEN MONTH(P.AnniversaryDate) = @ThisMonth AND DAY(P.AnniversaryDate) = @TodayNum THEN 0
            ELSE DATEDIFF(day, @Today, CA.NextDate) --P.DaysUntilAnniversary
        END AS DaysUntilEvent,
        CASE 
            WHEN MONTH(@Today) > MONTH(P.AnniversaryDate) 
                THEN DATEDIFF(year, P.AnniversaryDate, @Today) + 1
            ELSE
                DATEDIFF(year, P.AnniversaryDate, @Today) 
        END AS Years,
        CAST(0 AS bit) AS UpcomingBirthday, 
        CAST(1 AS bit) AS UpcomingAnniversary, 
        CAST(0 AS bit) AS UpcomingWorkiversary
    FROM Person AS P INNER JOIN 
        TaggedItem AS T on P.Guid = T.EntityGuid AND T.TagID = @StaffTagID CROSS APPLY 
        --(SELECT DATEFROMPARTS(CASE WHEN MONTH(P.AnniversaryDate) > @ThisMonth OR (MONTH(P.AnniversaryDate) = @ThisMonth AND DAY(P.AnniversaryDate) >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, MONTH(P.AnniversaryDate), DAY(P.AnniversaryDate)) AS NextDate) AS CA
        (SELECT DATEFROMPARTS(
            CASE WHEN MONTH(P.AnniversaryDate) > @ThisMonth OR (MONTH(P.AnniversaryDate) = @ThisMonth AND DAY(P.AnniversaryDate) >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, 
            MONTH(P.AnniversaryDate), 
            CASE WHEN MONTH(P.AnniversaryDate) = 2 AND DAY(P.AnniversaryDate) = 29 AND ISDATE(CAST(@ThisYear AS varchar) + '-02-29') = 0 THEN 28 ELSE DAY(P.AnniversaryDate) END) AS NextDate) AS CA 
    WHERE MONTH(P.AnniversaryDate) <> '' AND DAY(P.AnniversaryDate) <> ''
        AND (P.DaysUntilAnniversary <= @DayRange OR (MONTH(P.AnniversaryDate) = @ThisMonth AND DAY(P.AnniversaryDate) = @TodayNum))
    	--AND DATEDIFF(year, P.AnniversaryDate, @Today) > 0
    UNION ALL
    SELECT P.ID, P.NickName, P.LastName, P.PhotoId, P.Email, P.AgeClassification, P.Gender,
        YEAR(V.ValueAsDateTime) AS EventYear, MONTH(V.ValueAsDateTime) AS EventMonth, DAY(V.ValueAsDateTime) AS EventDay,
    	DATEDIFF(day, @Today, CA.NextDate) AS DaysUntilEvent,
        YEAR(CA.NextDate) - YEAR(V.ValueAsDateTime) AS Years,
        CAST(0 AS bit) AS UpcomingBirthday, 
        CAST(0 AS bit) AS UpcomingAnniversary, 
        CAST(1 AS bit) AS UpcomingWorkiversary
    FROM AttributeValue AS V INNER JOIN
        Person AS P ON P.ID = V.EntityID INNER JOIN 
        TaggedItem AS T on P.Guid = T.EntityGuid AND T.TagID = @StaffTagID CROSS APPLY 
        --(SELECT DATEFROMPARTS(CASE WHEN MONTH(V.ValueAsDateTime) > @ThisMonth OR (MONTH(V.ValueAsDateTime) = @ThisMonth AND DAY(V.ValueAsDateTime) >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, MONTH(V.ValueAsDateTime), DAY(V.ValueAsDateTime)) AS NextDate) AS CA
        (SELECT DATEFROMPARTS(
            CASE WHEN MONTH(V.ValueAsDateTime) > @ThisMonth OR (MONTH(V.ValueAsDateTime) = @ThisMonth AND DAY(V.ValueAsDateTime) >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, 
            MONTH(V.ValueAsDateTime), 
            CASE WHEN MONTH(V.ValueAsDateTime) = 2 AND DAY(V.ValueAsDateTime) = 29 AND ISDATE(CAST(@ThisYear AS varchar) + '-02-29') = 0 THEN 28 ELSE DAY(V.ValueAsDateTime) END) AS NextDate) AS CA 
    WHERE AttributeID = @HireDateID
    	--AND DATEDIFF(day, @Today, CA.NextDate) <= @DayRange
    	AND (DATEDIFF(day, @Today, CA.NextDate) <= @DayRange OR (MONTH(V.ValueAsDateTime) = @ThisMonth AND DAY(V.ValueAsDateTime) = @TodayNum))
    	AND P.ID NOT IN (SELECT EntityID FROM AttributeValue AS EV WHERE EV.AttributeID = @ExitDateID AND NOT EV.ValueAsDateTime IS NULL AND EV.ValueAsDateTime <= @Today)
    	--AND YEAR(CA.NextDate) - YEAR(V.ValueAsDateTime) >= 0
    ) AS A 
    LEFT JOIN AttributeValue SV ON SV.EntityID = A.ID AND SV.AttributeID = @SlackUserAttrID
    LEFT JOIN @Gender AS G ON G.ID = A.Gender
ORDER BY A.DaysUntilEvent, A.EventMonth, A.EventDay, A.LastName, A.NickName, A.UpcomingAnniversary DESC, A.UpcomingBirthday DESC, A.UpcomingWorkiversary DESC

Formatted Output:

Our version displays our logo next to the "Happy workiversary" messages. For the purposes of this recipe our logo icon has been replaced by the thumbs up icon ().

{%- cache key:'staff-milestones' duration:'3600' -%}
<div class="panel panel-block milestones"> 
    <div class="panel-heading"><h4 class="panel-title"><i class="fas fa-bullhorn"></i> Staff Celebrations</h4></div>
    <ul class="list-group">
        {%- assign date = 'Now' | Date:' M/d' -%}
        {%- for row in rows -%}
            {%- assign iconClass = '' -%}
            {%- assign titleAttr = '' -%}
            {%- assign celebrateText = '' -%}
            {%- assign emailType = '' -%}
            {%- assign number = '' -%}
            {%- capture eventdate %}{{ row.EventMonth }}/{{ row.EventDay }}{% endcapture -%}
            {%- if row.UpcomingBirthday == true -%}
                {%- assign iconClass = 'fa fa-fw fa-birthday-cake' -%}
                {%- assign titleAttr = 'Birthday' -%}
                {%- assign emailType = 'a birthday' -%}
                {%- if date == eventdate -%}
                    {%- capture celebrateText -%}<span class="celebrate"><i class="fa fa-gift"></i> <small>Happy Birthday!</small></span>{%- endcapture -%}
                {%- endif -%}
            {%- elseif row.UpcomingAnniversary == true -%}
                {%- assign iconClass = 'fa fa-fw fa-heart' -%}
                {%- assign number = row.Years | AsString | NumberToOrdinal -%}
                {%- if row.Years == 0 -%}
                    {%- capture titleAttr -%}Getting married!{%- endcapture -%}
                	{%- assign emailType = 'a congratulations' -%}
                	{%- assign number = 'Soon!' -%}
                {%- else -%}
                    {%- capture titleAttr -%}{{ number }} Anniversary{%- endcapture -%}
                	{%- assign emailType = 'an anniversary' -%}
                {%- endif -%}
                {%- if date == eventdate -%}
                    {%- if row.Years == 0 -%}
                    	{%- capture titleAttr -%}Just Married!{%- endcapture -%}
                        {%- capture celebrateText -%}<span class="celebrate"><i class="fa fa-glass-cheers"></i> <small>Congratulations!</small></span>{%- endcapture -%}
                    {%- else -%}
                    	{%- capture celebrateText -%}<span class="celebrate"><i class="fa fa-glass-cheers"></i> <small>Happy {{ number }} Anniversary!</small></span>{%- endcapture -%}
                    {%- endif -%}
                    {%- assign number = '' -%}
                {%- endif -%}
            {%- else -%}
                {%- assign iconClass = 'fa fa-fw fa-user-tie' -%}
                {%- assign number = row.Years | AsString | NumberToOrdinal -%}
                {%- if row.Years == 0 -%}
                    {%- capture titleAttr -%}New team member!{%- endcapture -%}
                	{%- assign number = 'New!' -%}
                {%- else -%}
                    {%- capture titleAttr -%}{{ number }} Workiversary{%- endcapture -%}
                {%- endif -%}
                {%- assign emailType = 'a congratulations' -%}
                {%- if date == eventdate -%}
                    {%- if row.Years == 0 -%}
                        {%- capture celebrateText -%}<span class="celebrate"><i class="far fa-thumbs-up"></i> <small>Welcome to the team!</small></span>{%- endcapture -%}
                    {%- else -%}
                        {%- capture celebrateText -%}<span class="celebrate"><i class="far fa-thumbs-up"></i> <small>Happy {{ number }} Workiversary!</small></span>{%- endcapture -%}
                    {%- endif -%}
                    {%- assign number = '' -%}
                {%- endif -%}
            {%- endif -%}
            <li class="list-group-item {{row.Gender}}" title="{{ titleAttr }}">
                <a href="/person/{{ row.Id }}" title="View {{ row.NickName }}'s profile" class="hidden-md">
                    {%- if row.PhotoId != null -%}
                        <img src="/GetImage.ashx?id={{row.PhotoId}}&maxwidth=24&maxheight=24&mode=pad&bgcolor=d4d2d0" alt="{{row.FullName}}" />
                    {%- else -%}
                        <img src="/Assets/Images/person-no-photo-{{row.Gender}}.svg" alt="No Photo" class="no-photo" />
                    {%- endif -%}
                </a>
                <a href="/person/{{ row.Id }}" title="View {{ row.NickName }}'s profile">{{ row.FullName }}</a>
                <a href="/Communication?person={{ row.Id }}" title="Send {{ row.NickName }} {{ emailType }} email">
                    <small><i class="far fa-envelope margin-l-sm visible-lg-inline-block" style="opacity:.6"> </i></small>
                </a>
                <span class="date pull-right">
                    {% if number != '' %}<small><small>{{ number }}</small></small>{% endif %}
                    <i class="{{ iconClass }}"></i>
                    <small>{{ row.EventMonth }}/{{ row.EventDay }}</small>
                </span>
                {{ celebrateText }}
            </li>
        {%- endfor -%}
    </ul>
</div>
{%- endcache -%}

CSS Styles

There's also a little bit of CSS that needs to be added to your Rock theme (Admin Tools > CMS Configuration > Themes > Rock > CSS Overrides):

.milestones
{
    li { line-height: 1em; }
    a { display: inline-block; }
    img
    {
        width: 30px; height: 30px;
        margin-right: 3px;
        padding: 1px;
        border: 2px solid @link-color;
        border-radius: 50%;
        &.no-photo { opacity: .5; }
    }
    .date 
    { 
        line-height: 1.5em; 
        i
        {
            width: 1em;
            text-align: center;
        }
        small
        {
            display: inline-block;
            width: 2.5em;
            text-align: center;
        }
    }
    .celebrate
    {
        display: block;
        margin-left: 38px;
    }
    .fa-heart { color: lighten(@brand-danger, 5%); }
    .fa-birthday-cake { color: #866eff; }
    .fa-thumbs-up { color: @brand-color; }
    .fa-glass-cheers { color: #eab043; }
    .fa-gift { color: #2b94fd; }
    .fa-user-tie { color: #888; }
    
    .male .fa-birthday-cake { color: #4797ff; }
    .female .fa-birthday-cake { color: #ba84ff; }
    .male .fa-user-tie { color: #3c5e9e; }
    .female .fa-user-tie { color: #8762a5; }
}

Bonus

Font Awesome doesn't currently include the "party popper" emoji, so I created one myself and added it to our own custom icon font. You can see an example of the icon in the screenshot below.

I attached the .svg file for my party popper icon to this recipe for anyone who's interested and knows how to add their own custom font to Rock. Use the Download File button to get it and feel free to use or modify the icon however you want.

By default, this recipe uses the bullhorn icon () in the panel heading instead of the party popper.

Follow Up

Please don't hesitate to leave a comment below or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe.

If you like this recipe, you might also want to check out my Staff Celebration Confetti recipe.

Change Log

2023-12-13 - Fixed incorrect year numbers for Jan anniversaries displayed in Dec. (Thanks to Tony Visconti for catching this one!)
2023-04-12 - Added marital status filter for anniversaries and added a note about widowed staff members.
2023-02-23 - Handle Feb 29th events. On non-leap years, the event will be treated as if it falls on Feb 28th instead.
2022-09-02 - Initial Version

Download File

Recipe

Upcoming Staff Celebrations

Upcoming Staff Celebrations

Description

A Note on Widowed Staff Members

Credit

Prerequisites

Staff Person Tag

Staff Hire/Exit Date Attributes

How-To

Block Properties:

Block Criteria:

CSS Styles

Bonus

Follow Up

Change Log

Login to add a comment...