Description

This recipe creates a directory of staff members that displays the person's name, title/position, staff anniverary, and birthday. Next to the staff anniversay date, it displays the number of years the person has currently been on staff.

Credit

Our internal homepage has "Upcoming Staff Birthdays" and "Upcoming Staff Work Anniversaries" blocks that were created using the Staff Birthday/Anniversaries recipe by Randy Aufrecht, so part of the code that was used to create the directory page came from that recipe.

Prerequisites

Staff Hire Date & Title

Each individual will need to have an attribute for their staff hire date and their title. We have a Person Attribute Category of "Brown HR" and are able to edit this information on the person's profile page (all of the information is deleted when someone leaves staff and we go to remove their internal access):

You will need to pull the attribute IDs for both the staff hire date and title. And add an image to your system's assets to use if the staff member doesn't have a profile picture.

How-To

Create a new page under the Intranet section to house the staff directory and add a Dynamic Data Block to the page.

SQL Query

Things to change in the query:

• On lines 1 and 2, change the hire date and title attribute IDs to match your system.
• On line 18, the year used to avoid pulling a former staff member is the year our church was founded, so you may want to change the year

DECLARE @StaffHireDateAttributeId AS INT = 6570
DECLARE @TitleAttributeId AS INT = 7000
-----------------------------------------------------------------------
SELECT
  p. [Id]
  , [NickName]
  , [LastName]
  , [BirthDate]
  , av.[Value] as StaffHireDate
  , (FLOOR(DATEDIFF(dd,av.[Value],GETDATE()+0) / 365.25)) as 'NumYears'
  , av3.[Value] as Title
  , p.[PhotoId]
FROM
   [Person] p
   INNER JOIN [AttributeValue] av ON av.[EntityId] = p.[Id] AND av.AttributeId = @StaffHireDateAttributeId
   INNER JOIN [AttributeValue] av3 ON av3.[EntityId] = p.[Id] AND av3.AttributeId = @TitleAttributeId
WHERE
   av.[Value] >'1882-01-01'
ORDER BY
   [LastName]

Formatted Output:

Customize the results of the query using lava to get a card for each staff member. Optionally, you can opt to "Wrap in Panel" and add a title.

<div class="row">
    {% for row in rows %}
        <div class="col-md-3">
            <div class="card md-3">
                        
                    {% if row.PhotoId != null %}
                        <img class="card-img-top" src="/GetImage.ashx?Id={{ row.PhotoId }}&h=200&w=200">
                    {% else %}
                        <img src="Content/InternalSite/Images/no-image.jpeg">
                    {% endif %}
                
                <div class="card-body">
                    <h5 class="card-title">{{ row.NickName }} {{ row.LastName }}</h5>
                    <p class="card-text">{{ row.Title }}</br>
                    Staff Anniversary: {{ row.StaffHireDate | Date:'MMMM'}} {{ row.StaffHireDate | Date:'d' | NumberToOrdinal }} ({{ row.NumYears }} years)</br>
                    Birthday: {{ row.BirthDate | Date:'MMMM'}} {{ row.BirthDate | Date:'d' | NumberToOrdinal }}</p>
                </div>
            </div>
        </div>
    {% endfor %}
</div>