May
28

Staff Anniversaries

For a long time, we’ve been putting our staff anniversaries on our Arena home page each month…

image

I’m ashamed to say that until now, our department admin has been doing this manually each month.  I also noticed that she recently started changing the highlighting each day to indicate which anniversaries in the month had already passed.  Obviously a lot of manual work for what can easily be automated.

Since we’re tracking all this information in Arena, this was a perfect scenario for the HTML from Stored Procedure module.

 

First a stored procedure is needed to generate the HTML…

CREATE PROC [dbo].[cust_ccv_sp_staff_anniversaries]
@HireDateAttrID int,
@PositionAttrID int
AS

DECLARE @MonthName varchar(12)
DECLARE @HtmlMsgPtr binary(16)
DECLARE @TempMessage varchar(8000)

DECLARE @Today datetime
SET @Today = GETDATE()

CREATE TABLE ##StaffHtmlTable ([html] text)

INSERT INTO ##StaffHtmlTable VALUES ('<hr/>')
SELECT @HtmlMsgPtr = TEXTPTR([html])
FROM ##StaffHtmlTable

SET @TempMessage = '<table style="width: 570px; height: 201px; " cellspacing="0" cellpadding="0"><tbody><tr>' +
    '<td class="highlightText" valign="top" align="left">' +
    '<img src="/Arena/Content/HtmlImages/Public/Images/General/icon_birthday.gif" />' +
    '<strong><font color="#808080" size="3">' + DATENAME(month, @Today) + ' Birthdays</font></strong></td>' +
    '<td class="highlightText" valign="top" align="left">' +
    '<img src="/Arena/Content/HtmlImages/Public/Images/General/award.jpg" />' +
    '<strong><font color="#808080" size="3">' + DATENAME(month, @Today) +
    ' Staff Years of Service</font></strong></td></tr><tr><td class="highlightText" valign="top">'
UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

DECLARE @StaffName varchar(100)
DECLARE @AnnivDate datetime
DECLARE @Position varchar(250)

DECLARE BirthDateCursor CURSOR READ_ONLY FOR
SELECT
    nick_name + ' ' + last_name,
    birth_date
FROM core_person
WHERE staff_member = 1
AND birth_date <> '1/1/1900'
AND MONTH(birth_date) = MONTH(@Today)
ORDER BY DAY(birth_Date), last_name, nick_name

OPEN BirthDateCursor

FETCH NEXT FROM BirthDateCursor
INTO    @StaffName,
        @AnnivDate

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF (@@FETCH_STATUS = 0)
    BEGIN

        -- Date
        IF DAY(@AnnivDate) < DAY(@Today)
            SET @TempMessage = '<font color="#a9a9a9">'
        ELSE
            SET @TempMessage = '<font color="#000000">'
        SET @TempMessage = @TempMessage + CAST(MONTH(@AnnivDate) as varchar) + '/' + CAST(DAY(@AnnivDate) as varchar)
        IF DAY(@AnnivDate) < 10
            SET @TempMessage = @TempMessage + '&nbsp;&nbsp;'
        ELSE
            SET @TempMessage = @TempMessage + '&nbsp;'

        SET @TempMessage = @TempMessage + '<strong>' + @StaffName + '</strong></font><br/>'
        UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

    END

    FETCH NEXT FROM BirthDateCursor
    INTO    @StaffName,
            @AnnivDate

END

CLOSE BirthDateCursor
DEALLOCATE BirthDateCursor

SET @TempMessage = '</td><td class="highlightText" valign="top">'
UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

DECLARE HireDateCursor CURSOR READ_ONLY FOR
SELECT
    nick_name + ' ' + last_name,
    HD.datetime_value,
    POS.varchar_value
FROM core_person P
INNER JOIN core_person_attribute HD
    ON HD.person_id = P.person_id
    AND HD.attribute_id = @HireDateAttrID
LEFT OUTER JOIN core_person_attribute POS
    ON POS.person_id = P.person_id
    AND POS.attribute_id = @PositionAttrID
WHERE P.staff_member = 1
AND ISNULL(MONTH(HD.datetime_value),0) = MONTH(@Today)
AND ISNULL(YEAR(HD.datetime_value),0) < YEAR(@Today)
ORDER BY ISNULL(DAY(HD.datetime_value),0), last_name, nick_name

OPEN HireDateCursor

FETCH NEXT FROM HireDateCursor
INTO    @StaffName,
        @AnnivDate,
        @Position

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF (@@FETCH_STATUS = 0)
    BEGIN

        DECLARE @Years int
        SET @Years = YEAR(@Today) - YEAR(@AnnivDate)

        -- Date
        IF DAY(@AnnivDate) < DAY(@Today)
            SET @TempMessage = '<font color="#a9a9a9">'
        ELSE
            SET @TempMessage = '<font color="#000000">'
        SET @TempMessage = @TempMessage + CAST(MONTH(@AnnivDate) as varchar) + '/' + CAST(DAY(@AnnivDate) as varchar)
        IF DAY(@AnnivDate) < 10
            SET @TempMessage = @TempMessage + '&nbsp;&nbsp;'
        ELSE
            SET @TempMessage = @TempMessage + '&nbsp;'

        SET @TempMessage = @TempMessage + '<strong>' + @StaffName + '</strong>, ' + @Position + ' - <strong>' + CAST(@Years as varchar)
        IF @Years > 1
        BEGIN
            IF @Years >= 5
                SET @TempMessage = @TempMessage + ' Years!'
            ELSE
                SET @TempMessage = @TempMessage + ' Years'
        END
        ELSE
            SET @TempMessage = @TempMessage + ' Year'

        SET @TempMessage = @TempMessage + '</strong></font><br/>'
        UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

    END

    FETCH NEXT FROM HireDateCursor
    INTO    @StaffName,
            @AnnivDate,
            @Position

END

CLOSE HireDateCursor
DEALLOCATE HireDateCursor

SET @TempMessage = '</td></tr></table>'
UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

SELECT [html] FROM ##StaffHtmlTable

DROP TABLE ##StaffHtmlTable

 

This stored procedure expects a couple person attribute IDs for the Hire Date and Position…

image

 

And then a new HTML From Stored Procedure module is added to our Arena homepage. Notice the two person attribute ID values that are passed to the stored procedure in the Parameters setting.

image

 

I love doing these quick little things that help someone save a lot of time. 

(if you want to use the same stored procedure, here’s the two images we use…

 icon_birthday award )

Posted by David Turner Arena Subscribe to RSS feed

8 Comments on Staff Anniversaries

  1. Austin Spooner says:
    June 6, 2009 at 1:48 pm

    David, did you guys have to change any settings in your style sheet to get it to format correctly? When I set this up it has an extra couple lines between the title and the information. The text is also pretty large. Thanks!

  2. David Turner says:
    June 7, 2009 at 4:47 am

    It doesn’t look like we had to change any style information. The only class it uses is “highlightText” which is a font size of 12px. I did update the stored procedure since I posted this. The first block of HTML now looks like this…

    SET @TempMessage = '<table cellspacing="10" cellpadding="0"><tbody><tr>' +
    '<td class="highlightText" valign="top" align="left" nowrap>' +
    '<img src="/Arena/Content/HtmlImages/Public/Images/General/icon_birthday.gif" />' +
    '<strong><font color="#808080" size="3">' + DATENAME(month, @Today) + ' Birthdays</font></strong></td>' +
    '<td class="highlightText" valign="top" align="left" nowrap>' +
    '<img src="/Arena/Content/HtmlImages/Public/Images/General/award.jpg" />' +
    '<strong><font color="#808080" size="3">' + DATENAME(month, @Today) +
    ' Staff Years of Service</font></strong></td></tr><tr><td class="highlightText" valign="top">'

  3. Austin Spooner says:
    June 7, 2009 at 11:50 am

    that edit made all the difference :) – thanks!

  4. Nick says:
    June 16, 2009 at 3:27 pm

    David, have you guys ever thought about implementing some Output Caching control in the base PortalModule class so all module output could be easily cached via some module setting?

  5. David Turner says:
    July 7, 2009 at 8:06 am

    Nick, No really haven’t thought about it much. Probably should though. I see that Jason is implementing some lower-level caching in the framework he’s using for the HR modules. I’m interested in seeing more of what he’s doing.

  6. David Turner says:
    July 7, 2009 at 8:09 am

    Updated stored procedure to use a connection specific temporary table instead of a global temporary table (renamed all occurrences of ##StaffHtmlTable to #StaffHtmlTable). Hopefully that fixes the ‘Table already exists’ errors we’re getting sometimes. The HtmlFroStoredProcedure module really could benefit from some output caching.

  7. Nick says:
    July 16, 2009 at 12:15 pm

    BTW, we’re using your “Staff Anniversaries” technique now and everyone loves it. We only tweaked the style a bit to make it a tad smaller. Thanks again – great job!

  8. Nick says:
    August 3, 2009 at 10:14 am

    We also discovered if a person did not have a position value set, they would not show up in the anniversary report. To fix that, I changed the POS.varchar_value SELECT in the second half of the stored procedure to this: ISNULL( POS.varchar_value, ” )

Leave a Reply

*