Another Hannah Update

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

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">

    <xsl:output method="xml" version="1.0" encoding="utf-8"/>

    <xsl:template match="/">
        <rss xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd" version="2.0">
            <xsl:for-each select="/*">
                <channel>
                    <title>
                        <xsl:value-of select="@author"/>: <xsl:value-of select="@title"/> (<xsl:value-of select="format/@title"/>)
                    </title>
                    <link>
                        <xsl:value-of select="@link"/>
                    </link>
                    <language>
                        <xsl:value-of select="@language"/>
                    </language>
                    <copyright>
                        <xsl:value-of select="@copyright"/>
                    </copyright>
                    <description>
                        <xsl:value-of select="description"/>
                    </description>
                    <itunes:subtitle>
                        <xsl:value-of select="@subtitle"/>
                    </itunes:subtitle>
                    <itunes:author>
                        <xsl:value-of select="@author"/>
                    </itunes:author>
                    <itunes:summary>
                        <xsl:value-of select="description"/>
                    </itunes:summary>
                    <itunes:owner>
                        <itunes:name>
                            <xsl:value-of select="@contactname"/>
                        </itunes:name>
                        <itunes:email>
                            <xsl:value-of select="@contactemail"/>
                        </itunes:email>
                    </itunes:owner>
                    <itunes:image>
                        <xsl:attribute name="href">
                            <xsl:value-of select="@imageurl"/>
                        </xsl:attribute>
                    </itunes:image>
                    <xsl:for-each select="category">
                        <xsl:call-template name="categorynode"></xsl:call-template>
                    </xsl:for-each>
                    <xsl:for-each select="items/item">
                        <xsl:call-template name="itemnode"></xsl:call-template>
                    </xsl:for-each>
                </channel>
            </xsl:for-each>
        </rss>
    </xsl:template>

    <xsl:template name="categorynode">
        <itunes:category>
            <xsl:attribute name="text">
                <xsl:value-of select="@text"/>
            </xsl:attribute>
            <xsl:for-each select="category">
                <xsl:call-template name="categorynode"></xsl:call-template>
            </xsl:for-each>
        </itunes:category>
    </xsl:template>

    <xsl:template name="itemnode">
        <item>
            <title>
                <xsl:choose>
                    <xsl:when test="topic">
                        <xsl:choose>
                            <xsl:when test="topic/@title = @title">
                                <xsl:value-of select="@title"/>
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="topic/@title"/>: <xsl:value-of select="@title"/>
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:value-of select="@title"/>
                    </xsl:otherwise>
                </xsl:choose>
            </title>
            <pubDate>
                <xsl:value-of select="@publish"/>
            </pubDate>
            <itunes:author>
                <xsl:value-of select="/channel/@author"/>
            </itunes:author>
            <xsl:if test="@imageurl">
                <itunes:image>
                    <xsl:attribute name="href">
                        <xsl:value-of select="@imageurl"/>
                    </xsl:attribute>
                </itunes:image>
            </xsl:if>
            <itunes:summary>
                <xsl:value-of select="description"/>
            </itunes:summary>
            <description>
                <xsl:value-of select="description"/>
            </description>

            <link>
                <xsl:value-of select="@link"/>
            </link>
            <enclosure>
                <xsl:attribute name="url">
                    <xsl:value-of select="enclosure/@url"/>
                </xsl:attribute>
                <xsl:attribute name="length">
                    <xsl:value-of select="enclosure/@duration"/>
                </xsl:attribute>
                <xsl:attribute name="type">
                    <xsl:value-of select="enclosure/@mimetype"/>
                </xsl:attribute>
            </enclosure>
            <itunes:keywords>
                <xsl:value-of select="keywords"/>
            </itunes:keywords>
            <xsl:if test="enclosure/@time">
                <itunes:duration>
                    <xsl:value-of select="enclosure/@time"/>
                </itunes:duration>
            </xsl:if>
        </item>
    </xsl:template>

</xsl:stylesheet>

<div class="heading2">Tools</div>
<div class="normalText" style="padding-left:15px">
    <a href='default.aspx?page=1437&Area=<area_id>'><area_name> Events</a><br/>
    <a href='default.aspx?page=4065&Area=<area_id>'>View <area_name> Families</a><br/>
</div>
<div class="heading2" style="padding-top:20px">Reports</div>
<div class="normalText" style="padding-left:20px">
    <a href='default.aspx?page=3987&title=<area_name_encoded> Members, Attenders and Guest Book Signers&AreaID=<area_id>' target='_blank'><area_name> Members, Attenders and Guest Book Signers</a><br/>
</div>

DECLARE @OrganizationID int
SET @OrganizationID = (SELECT TOP 1 organization_id FROM orgn_organization)

DECLARE @AllUsers int
SET @AllUsers = (SELECT TOP 1 role_id FROM secu_role WHERE role_name = 'All Users')

DECLARE @ParentMetricID int
DECLARE @MetricID int

-- Create Parent Metric
INSERT INTO [Arena].[dbo].[mtrc_metric]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[parent_metric_id]
       ,[metric_order]
       ,[graph_type]
       ,[title]
       ,[series_caption]
       ,[description]
       ,[source_summary]
       ,[collection_frequency]
       ,[collection_last_date]
       ,[collection_sql_statement]
       ,[organization_id]
       ,[aggregate_type]
       ,[numeric_x_values])
     VALUES
       (GETDATE()
       ,GETDATE()
       ,''
       ,''
       ,NULL
       ,1
       ,-1
       ,'Membership Status'
       ,''
       ,'Metric to show the number of people of each Membership Status over time'
       ,''
       ,0
       ,'1/1/1900'
       ,''
       ,@OrganizationID
       ,'MAX'
       ,0)    

SET @ParentMetricID = SCOPE_IDENTITY()

INSERT INTO [Arena].[dbo].[secu_permission]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[object_type]
       ,[operation_type]
       ,[subject_type]
       ,[object_key]
       ,[subject_key])
    VALUES
        (GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,6
        ,0
        ,0
        ,@ParentMetricID
        ,@AllUsers )

INSERT INTO [Arena].[dbo].[secu_permission]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[object_type]
       ,[operation_type]
       ,[subject_type]
       ,[object_key]
       ,[subject_key])
    VALUES
        (GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,6
        ,1
        ,0
        ,@ParentMetricID
        ,@AllUsers )

DECLARE @LookupID int
DECLARE @LookupValue varchar(50)
DECLARE @LookupOrder int

DECLARE MemberStatusCursor CURSOR LOCAL FOR
    SELECT
        lookup_id,
        lookup_value,
        lookup_order
    FROM core_lookup L
    INNER JOIN core_lookup_type LT ON LT.lookup_type_id = L.lookup_type_id
    WHERE LT.guid = '0B4532DB-3188-40F5-B188-E7E6E4448C85'

OPEN MemberStatusCursor

FETCH NEXT FROM MemberStatusCursor
INTO @LookupID, @LookupValue, @LookupOrder

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO [Arena].[dbo].[mtrc_metric]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[parent_metric_id]
           ,[metric_order]
           ,[graph_type]
           ,[title]
           ,[series_caption]
           ,[description]
           ,[source_summary]
           ,[collection_frequency]
           ,[collection_last_date]
           ,[collection_sql_statement]
           ,[organization_id]
           ,[aggregate_type]
           ,[numeric_x_values])
        VALUES
           (GETDATE()
           ,GETDATE()
           ,''
           ,''
           ,@ParentMetricID
           ,@LookupOrder
           ,0
           ,'Number of ' + @LookupValue + 's'
           ,@LookupValue + 's'
           ,'Number of people classifed as "' + @LookupValue + '"'
           ,''
           ,2
           ,GETDATE()
           ,'SELECT COUNT(*) AS people_count FROM Arena.dbo.core_person WHERE record_status = 0 AND member_status = ' + CAST(@LookupID as varchar)
           ,@OrganizationID
           ,'MAX'
           ,0)

    SET @MetricID = SCOPE_IDENTITY()

    INSERT INTO [Arena].[dbo].[secu_permission]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[object_type]
           ,[operation_type]
           ,[subject_type]
           ,[object_key]
           ,[subject_key])
        VALUES
            (GETDATE()
            ,GETDATE()
            ,''
            ,''
            ,6
            ,0
            ,0
            ,@MetricID
            ,@AllUsers )

    INSERT INTO [Arena].[dbo].[secu_permission]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[object_type]
           ,[operation_type]
           ,[subject_type]
           ,[object_key]
           ,[subject_key])
        VALUES
            (GETDATE()
            ,GETDATE()
            ,''
            ,''
            ,6
            ,1
            ,0
            ,@MetricID
            ,@AllUsers )

    FETCH NEXT FROM MemberStatusCursor
    INTO @LookupID, @LookupValue, @LookupOrder

END

CLOSE MemberStatusCursor
DEALLOCATE MemberStatusCursor

SET NOCOUNT ON
GO

DECLARE @ParentMetricID int
DECLARE @MinHistoryDate datetime
DECLARE @WorkingDate datetime
DECLARE @Status varchar(15)

SET @ParentMetricID = (SELECT MAX(metric_id) FROM mtrc_metric WHERE title = 'Membership Status' AND parent_metric_id IS NULL)
SET @MinHistoryDate = (SELECT MIN(date_created) FROM core_member_status_history)
SET @WorkingDate = GETDATE()

DELETE mtrc_metric_item
FROM mtrc_metric_item MI
INNER JOIN mtrc_metric M ON M.metric_id = MI.metric_id
WHERE M.parent_metric_id = @ParentMetricID

WHILE @MinHistoryDate IS NOT NULL
AND @WorkingDate > @MinHistoryDate
BEGIN

    SET @Status = CONVERT(varchar(10), @WorkingDate, 101) + '...'
    RAISERROR (@Status, 0, 1) WITH NOWAIT;

    WITH memberStatusTable (person_id, member_status)
    AS
    (
        SELECT
            person_id,
            ISNULL((SELECT TOP 1 member_status
                FROM core_member_status_history
                WHERE person_id = P.person_id
                AND date_created < @WorkingDate
                ORDER BY date_created DESC), p.member_status) AS member_status
        FROM core_person P
        WHERE date_created <= @WorkingDate
    )

    INSERT INTO mtrc_metric_item
    SELECT
         GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,M.metric_id
        ,(SELECT COUNT(*)
            FROM memberStatusTable
            WHERE member_status = L.lookup_id)
        ,@WorkingDate
        ,''
        ,0
    FROM core_lookup L
    INNER JOIN core_lookup_type LT
        ON LT.lookup_type_id = L.lookup_type_id
    INNER JOIN mtrc_metric M
        ON M.title = 'Number of ' + L.lookup_value + 's'
        AND M.parent_metric_id = @ParentMetricID
    WHERE LT.guid = '0B4532DB-3188-40F5-B188-E7E6E4448C85'

    SET @WorkingDate = DATEADD(wk, -1, @WorkingDate)

END