fnGetCalendarEvents

CREATE function [dbo].[fnGetCalendarEvents]
(
    @EventCalendarID int,
    @BeginDate datetime,
    @EndDate datetime,
    @Category nvarchar(4000)
)
RETURNS @EventsTable TABLE (
        [ID] [int],
        [EventCalendarID] [int],
        [Category] [nvarchar](100),
        [CategoryName] [nvarchar](255),
        [BeginDate] [datetime],
        [BeginDateDisplay] [nvarchar](64),
        [BeginWeekDayDisplay] [nvarchar](64),
        [BeginTimeDisplay] [nvarchar](64),
        [EndDate] [datetime],
        [EndDateDisplay] [nvarchar](64),
        [EndWeekDayDisplay] [nvarchar](64),
        [EndTimeDisplay] [nvarchar](64),
        [Title] [nvarchar](255),
        [SubTitle] [nvarchar](255), 
        [Location] [nvarchar](255),
        [Details] [ntext],
        [Fees] [nvarchar](255),
        [MoreInfoURL] [nvarchar](255),
        [ContactInfo] [nvarchar](255),
        [CreatedUserID] [int],
        [CreateDate] [datetime],
        [ApprovedDate] [datetime],
        [ApprovedUserID] [int],
        [Deleted] [bit],
        [OccurenceGroupID] [int],
        [AllDayEvent] [bit],
        [UpdateDate] [datetime],
        [MapLocation] [int],
        [Cancelled] [bit],
        [TimeZoneID] [int],
        [RecurFrequency] [int],
        [RecurDay]    [int],
        [RecurMonth]    [int],
        [RecurDayOfWeek]    [nvarchar](200),--sterling changed length to 200
        [RecurStartDate]    [datetime],
        [RecurEndDate]    [datetime],
        [RecurPosition]    [int],
        [RecurMonthlyMethod]    [int],
        [RecurID]    [int],
        [RecurException]    [bit],
        [AthleticEventID] [int]
    )    
AS
    begin

    declare @Events_ByCategories_TEMPTBL table(
        [ID] [int],
        [EventCalendarID] [int],
        [Category] [nvarchar](100),
        [CategoryName] [nvarchar](255),
        [BeginDate] [datetime],
        [BeginDateDisplay] [nvarchar](64),
        [BeginWeekDayDisplay] [nvarchar](64),
        [BeginTimeDisplay] [nvarchar](64),
        [EndDate] [datetime],
        [EndDateDisplay] [nvarchar](64),
        [EndWeekDayDisplay] [nvarchar](64),
        [EndTimeDisplay] [nvarchar](64),
        [Title] [nvarchar](255),
        [SubTitle] [nvarchar](255), 
        [Location] [nvarchar](255),
        [Details] [ntext],
        [Fees] [nvarchar](255),
        [MoreInfoURL] [nvarchar](255),
        [ContactInfo] [nvarchar](255),
        [CreatedUserID] [int],
        [CreateDate] [datetime],
        [ApprovedDate] [datetime],
        [ApprovedUserID] [int],
        [Deleted] [bit],
        [OccurenceGroupID] [int],
        [AllDayEvent] [bit],
        [UpdateDate] [datetime],
        [MapLocation] [int],
        [Cancelled] [bit],
        [TimeZoneID] [int],
        [RecurFrequency] [int],
        [RecurDay]    [int],
        [RecurMonth]    [int],
        [RecurDayOfWeek]    [nvarchar](200),--sterling changed length to 200
        [RecurStartDate]    [datetime],
        [RecurEndDate]    [datetime],
        [RecurPosition]    [int],
        [RecurMonthlyMethod]    [int],
        [RecurID]    [int],
        [RecurException]    [bit],
        [AthleticEventID] [int]
    )

    INSERT INTO @Events_ByCategories_TEMPTBL
        SELECT    [ID], [EventCalendarID], [Category], '' as 'CategoryName', 
            [BeginDate], dbo.fnFormatDateTime(Begindate, 'MM/DD') as 'BeginDateDisplay',
            SUBSTRING(dbo.fnFormatDateTime(Begindate, 'WEEKDAY'), 1, 3) as 'BeginWeekDayDisplay',
            dbo.fnFormatDateTime(BeginDate, 'HH:MM 12') as 'BeginTimeDisplay', 
            [EndDate], dbo.fnFormatDateTime(EndDate, 'MM/DD') as 'EndDateDisplay',
            dbo.fnFormatDateTime(EndDate, 'WEEKDAY') as 'EndWeekDayDisplay',
            dbo.fnFormatDateTime(EndDate, 'HH:MM 12') as 'EndTimeDisplay', 
            [Title], [SubTitle], [Location], [Details], [Fees], [MoreInfoURL], [ContactInfo], [CreatedUserID], 
            [CreateDate], [ApprovedDate], [ApprovedUserID], [Deleted], [OccurenceGroupID],
            [AllDayEvent], [UpdateDate], [MapLocation], [Cancelled], [TimeZoneID], [RecurFrequency], [RecurDay], [RecurMonth],
            [RecurDayOfWeek], [RecurStartDate], [RecurEndDate], [RecurPosition], [RecurMonthlyMethod], 
            '' as 'RecurID', [RecurException], [AthleticEventID]
        FROM dbo.CalendarEvent 
        WHERE EventCalendarID = @EventCalendarID
        AND OccurenceGroupID = 0
        AND
        (
            (@BeginDate >= BeginDate AND @EndDate <= EndDate)
            OR
            (BeginDate >= @BeginDate AND BeginDate <= @EndDate)
            OR
            (EndDate >= @BeginDate AND EndDate <= @EndDate) 
        )

        union all

        -- also insert the recurring events by finding those begin dates that qualify    
        SELECT    [ID], [EventCalendarID], [Category], '' as 'CategoryName', 
            r.BeginDate, dbo.fnFormatDateTime(r.Begindate, 'MM/DD') as 'BeginDateDisplay',
            SUBSTRING(dbo.fnFormatDateTime(r.Begindate, 'WEEKDAY'), 1, 3) as 'BeginWeekDayDisplay',
            dbo.fnFormatDateTime(r.BeginDate, 'HH:MM 12') as 'BeginTimeDisplay', 
            r.EndDate, dbo.fnFormatDateTime(r.EndDate, 'MM/DD') as 'EndDateDisplay',
            dbo.fnFormatDateTime(r.EndDate, 'WEEKDAY') as 'EndWeekDayDisplay',
            dbo.fnFormatDateTime(r.EndDate, 'HH:MM 12') as 'EndTimeDisplay', 
            [Title], [SubTitle], [Location], [Details], [Fees], [MoreInfoURL], [ContactInfo], [CreatedUserID], 
            [CreateDate], [ApprovedDate], [ApprovedUserID], [Deleted], [OccurenceGroupID],
            [AllDayEvent], [UpdateDate], [MapLocation], [Cancelled], [TimeZoneID], [RecurFrequency], [RecurDay], [RecurMonth],
            [RecurDayOfWeek], [RecurStartDate], [RecurEndDate], [RecurPosition], [RecurMonthlyMethod], 
            r.RecurID, [RecurException], [AthleticEventID]
        FROM dbo.CalendarEvent c
        INNER JOIN dbo.CalendarEvtRecur r ON r.EventID = c.ID
        WHERE c.EventCalendarID = @EventCalendarID
        AND c.OccurenceGroupID > 0 
        AND
        (
            (@BeginDate >= r.BeginDate AND @EndDate <= r.EndDate)
            OR
            (r.BeginDate >= @BeginDate AND r.BeginDate <= @EndDate)
            OR
            (r.EndDate >= @BeginDate AND r.EndDate <= @EndDate) 
        )


    DECLARE @iPos int
    DECLARE @iCategoryGUID varchar(36)

    SET @iPos = CHARINDEX(',',@Category)
    WHILE @iPos > 0 
        begin
            SET @iCategoryGUID = LEFT(@Category,@iPos-1)
            INSERT @EventsTable
                SELECT 
                    [ID],
                    [EventCalendarID],
                    [Category],
                    [CategoryName],
                    [BeginDate],
                    [BeginDateDisplay],
                    [BeginWeekDayDisplay],
                    [BeginTimeDisplay],
                    [EndDate],
                    [EndDateDisplay],
                    [EndWeekDayDisplay],
                    [EndTimeDisplay],
                    [Title],
                    [SubTitle], 
                    [Location],
                    [Details],
                    [Fees],
                    [MoreInfoURL],
                    [ContactInfo],
                    [CreatedUserID],
                    [CreateDate],
                    [ApprovedDate],
                    [ApprovedUserID],
                    [Deleted],
                    [OccurenceGroupID],
                    [AllDayEvent],
                    [UpdateDate],
                    [MapLocation],
                    [Cancelled],
                    [TimeZoneID],
                    [RecurFrequency],
                    [RecurDay],
                    [RecurMonth],
                    [RecurDayOfWeek],
                    [RecurStartDate],
                    [RecurEndDate],
                    [RecurPosition],
                    [RecurMonthlyMethod],
                    [RecurID],
                    [RecurException],
                    [AthleticEventID]
                FROM @Events_ByCategories_TEMPTBL
                WHERE Category = @iCategoryGUID

            SET @Category = SUBSTRING(@Category,@iPos+1,LEN(@Category)-@iPos)
            SET @iPos = CHARINDEX(',',@Category)
        end

    IF (LEN(@Category) > 0)
        INSERT @EventsTable
                SELECT 
                    [ID],
                    [EventCalendarID],
                    [Category],
                    [CategoryName],
                    [BeginDate],
                    [BeginDateDisplay],
                    [BeginWeekDayDisplay],
                    [BeginTimeDisplay],
                    [EndDate],
                    [EndDateDisplay],
                    [EndWeekDayDisplay],
                    [EndTimeDisplay],
                    [Title],
                    [SubTitle], 
                    [Location],
                    [Details],
                    [Fees],
                    [MoreInfoURL],
                    [ContactInfo],
                    [CreatedUserID],
                    [CreateDate],
                    [ApprovedDate],
                    [ApprovedUserID],
                    [Deleted],
                    [OccurenceGroupID],
                    [AllDayEvent],
                    [UpdateDate],
                    [MapLocation],
                    [Cancelled],
                    [TimeZoneID],
                    [RecurFrequency],
                    [RecurDay],
                    [RecurMonth],
                    [RecurDayOfWeek],
                    [RecurStartDate],
                    [RecurEndDate],
                    [RecurPosition],
                    [RecurMonthlyMethod],
                    [RecurID],
                    [RecurException],
                    [AthleticEventID]
                FROM @Events_ByCategories_TEMPTBL
                WHERE Category = @Category


    INSERT @EventsTable
            SELECT 
                [ID],
                [EventCalendarID],
                [Category],
                [CategoryName],
                [BeginDate],
                [BeginDateDisplay],
                [BeginWeekDayDisplay],
                [BeginTimeDisplay],
                [EndDate],
                [EndDateDisplay],
                [EndWeekDayDisplay],
                [EndTimeDisplay],
                [Title],
                [SubTitle], 
                [Location],
                [Details],
                [Fees],
                [MoreInfoURL],
                [ContactInfo],
                [CreatedUserID],
                [CreateDate],
                [ApprovedDate],
                [ApprovedUserID],
                [Deleted],
                [OccurenceGroupID],
                [AllDayEvent],
                [UpdateDate],
                [MapLocation],
                [Cancelled],
                [TimeZoneID],
                [RecurFrequency],
                [RecurDay],
                [RecurMonth],
                [RecurDayOfWeek],
                [RecurStartDate],
                [RecurEndDate],
                [RecurPosition],
                [RecurMonthlyMethod],
                [RecurID],
                [RecurException],
                [AthleticEventID] 
            FROM @Events_ByCategories_TEMPTBL
            WHERE Category = ''

    UPDATE @EventsTable SET CategoryName =    
                Case Category 
                    When '' Then ''
                    Else (SELECT dbo.fnGetSiteCodeTableEntryDescription(1, Category))
                End
    UPDATE @EventsTable SET BeginTimeDisplay = 
                Case AllDayEvent
                    When 1 Then 'All day'
                    Else BeginTimeDisplay
                End
    UPDATE @EventsTable SET EndTimeDisplay = 
                Case AllDayEvent
                    When 1 Then ''
                    Else EndTimeDisplay
                End

    update @EventsTable
        set Location = ATHLETICS_LOCATIONS.Name
    from @EventsTable ET
        inner join dbo.ATHLETICS_EVENTS on ET.AthleticEventID = ATHLETICS_EVENTS.ID
        inner join dbo.ATHLETICS_LOCATIONS on ATHLETICS_EVENTS.LocationID = ATHLETICS_LOCATIONS.ID
    where ET.Location is null or ET.Location = ''

    RETURN

end