spGetCalendarEventCategoriesCollection

      CREATE   PROCEDURE [dbo].[spGetCalendarEventCategoriesCollection]
            (
                @iEvtCalendarID                int,
                @iCodeTablesID                int,
                @Calendar_SiteContentID        int,
                @iFunctionMode                int,    -- display: 0; editor: 1
                @ClientUsersID                int,
                @ClientsID                    int
            )
            AS
            -- For Event Calendar Editor, this proc returns a table of all the calendar event categories in the database; and 
            -- indicates which categories have been picked for a particular calendar (identified by @iEvtCalendarID)
            -- For Event Calendar Display, the returned table will only include the event categories the user has rights to view

            -- The table has 9 columns:
            --  ID: the calendar event category IDs (it matches the ID column of the SiteCodeTableEntries with CodeTablesID: 1
            --  Description: the calendar event category name
            --  EntryGUID: the GUID of the category
            --  PickGUID: the GUID that identifies a category is picked in a calendar 
            --        (e.g. the category 'practice' picked in the calendar 'soccer' has a different PickGUID than 
            --             the category 'practice' picked in the calendar 'baseball')
            --  IsPicked: returns 1 if the event category is picked for this calendar
            --    OwnerID: the owner id of the category
            --  CanAdd: whether the user can add an event to this category (don't care in editor mode)
            --  CanEdit: whether the user can edit an event of this category (don't care in editor mode)
            --    CanDelete: whether the user can delete an event from this category (don't care in editor mode)
            --  CanDeleteThisCategory: returns 1 if no calendar event is categorized under this category (don't care in display mode),
            --                        user can help themselves deleting it from a calendar
            begin  
                DECLARE @MyTable TABLE (
                            ID int,
                            Description nvarchar(256),
                            EntryGUID uniqueidentifier,
                            PickGUID uniqueidentifier,
                            IsPicked int,
                            OwnerID int,
                            CanAdd int,
                            CanEdit int,
                            CanDelete int,
                            CanDeleteThisCategory bit)

                set nocount on

                IF @iFunctionMode = 1 -- don't check category security if coming from editor, or supervisor login
                    begin
                    INSERT INTO @MyTable
                        SELECT te.ID, te.Description, te.EntryGUID, p.CalendarCategoryPicksID,  
                            (SELECT dbo.fnCalEvtCategoryIsPicked(@iEvtCalendarID, te.EntryGUID)) AS 'IsPicked', 
                            0, 1, 1, 1, 'CanDeleteThisCategory' =
                            CASE WHEN EXISTS (SELECT * from dbo.CalendarEvent 
                                            where Category = cast(te.EntryGUID as varchar(36))
                                            and EventCalendarID = @iEvtCalendarID) THEN 0 
                                ELSE 1
                            END    
                        FROM dbo.SiteCodeTableEntries te 
                        LEFT JOIN dbo.CalendarEvtCategoryPicks p 
                            ON p.CategoryCodeTableEntryID = te.EntryGUID and p.EventCalendarID = @iEvtCalendarID
                        WHERE te.CodeTablesID = @iCodeTablesID 
                        ORDER BY te.Description 
                    end
                ELSE    
                    begin
                    DECLARE @ViewEnumID int
                    SELECT @ViewEnumID = ENUMID from dbo.CMSOBJECTTASK where TASKNAME = 'View'
                    DECLARE @EditEnumID int
                    SELECT @EditEnumID = ENUMID from dbo.CMSOBJECTTASK where TASKNAME = 'Add/Edit/Delete'
                    INSERT INTO @MyTable
                        SELECT te.ID, te.Description, te.EntryGUID, p.CalendarCategoryPicksID,  
                            (SELECT dbo.fnCalEvtCategoryIsPicked(@iEvtCalendarID, te.EntryGUID) AS 'IsPicked'), 
                            0, privAdd.VALUE, 0, 0, 0
                        FROM dbo.SiteCodeTableEntries te 
                        LEFT JOIN dbo.CalendarEvtCategoryPicks p 
                            ON p.CategoryCodeTableEntryID = te.EntryGUID and p.EventCalendarID = @iEvtCalendarID
                        INNER JOIN dbo.UFN_CLIENTUSERS_OBJECTTASKOBJECTS(@ClientUsersID, @ViewEnumID, 31) priv
                            ON p.CalendarCategoryPicksID = priv.OBJECTGUID
                        LEFT JOIN dbo.UFN_CLIENTUSERS_OBJECTTASKOBJECTS(@ClientUsersID, @EditEnumID, 31) privAdd
                            ON p.CalendarCategoryPicksID = privAdd.OBJECTGUID
                        WHERE (priv.VALUE <> 0) AND (te.CodeTablesID = @iCodeTablesID)
                        ORDER BY te.Description 
                    end

                UPDATE @MyTable SET OwnerID = (SELECT OwnerID from dbo.vwSiteContent where ID = @Calendar_SiteContentID)

                SELECT 
                    ID, Description, EntryGUID, PickGUID, IsPicked, OwnerID, CanAdd, CanEdit, CanDelete, CanDeleteThisCategory
                FROM @MyTable
            end