USP_DATALIST_EVENTDOCUMENTATION

This datalist returns all documentation for an event.
Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DOCUMENTATIONTYPECODE	tinyint	IN	Documentation type
@TITLE	nvarchar(50)	IN	Title
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@INCLUDESUBEVENTS	bit	IN	Include sub-events
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_EVENTDOCUMENTATION 
                (
                    @EVENTID uniqueidentifier = null,
                    @DOCUMENTATIONTYPECODE tinyint = null,
                    @TITLE nvarchar(50) = '',
                    @CURRENTAPPUSERID uniqueidentifier = null, 
                    @INCLUDESUBEVENTS bit = 0
                )
                as
                    set nocount on;

                    select    
                        EVENTNOTE.ID,
                        '70337D76-3541-455E-8631-6AC5F5841EBB' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        EVENTNOTE.DATEENTERED,
                        EVENTNOTETYPECODE.DESCRIPTION as [TYPE],
                        EVENTNOTE.TITLE,
                        NF.NAME,
                        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Event note',EVENTNOTE.ID) as ANNOTATIONEXISTS,
                        EVENTS.NAME as [EVENTNAME]
                    from
                        dbo.UFN_CHILDEVENTSWITHSITEACCESS(@EVENTID, @CURRENTAPPUSERID) EVENTS
                    inner join
                        dbo.EVENTNOTE on EVENTNOTE.EVENTID = EVENTS.ID
                    inner join
                        dbo.EVENTNOTETYPECODE
                    on
                        EVENTNOTETYPECODE.ID = EVENTNOTE.EVENTNOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTNOTE.AUTHORID) NF
                    where 
                        (EVENTS.ID = @EVENTID or @INCLUDESUBEVENTS = 1) and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or EVENTNOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        EVENTMEDIALINK.ID,
                        'D2046931-AE8D-43CB-887F-93D68DBFCA4D' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        EVENTMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        EVENTMEDIALINK.DATEENTERED,
                        EVENTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        EVENTMEDIALINK.TITLE,
                        NF.NAME,
                        cast(0 as bit) as ANNOTATIONEXISTS,
                        EVENTS.NAME as [EVENTNAME]
                    from
                        dbo.UFN_CHILDEVENTSWITHSITEACCESS(@EVENTID, @CURRENTAPPUSERID) EVENTS
                    inner join
                        dbo.EVENTMEDIALINK on EVENTMEDIALINK.EVENTID = EVENTS.ID
                    inner join
                        dbo.EVENTMEDIALINKTYPECODE
                    on
                        EVENTMEDIALINKTYPECODE.ID = EVENTMEDIALINK.EVENTMEDIALINKTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTMEDIALINK.AUTHORID) NF
                    where 
                        (EVENTS.ID = @EVENTID or @INCLUDESUBEVENTS = 1) and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or EVENTMEDIALINK.TITLE like @TITLE + '%')        

                    union all    

                    select    
                        EVENTATTACHMENT.ID,
                        '8AEFD48E-6244-4D03-83D6-E81CBBD55AD1' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        EVENTATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        EVENTATTACHMENT.DATEENTERED,
                        EVENTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        EVENTATTACHMENT.TITLE,
                        NF.NAME,
                        cast(0 as bit) as ANNOTATIONEXISTS,
                        EVENTS.NAME as [EVENTNAME]
                    from
                        dbo.UFN_CHILDEVENTSWITHSITEACCESS(@EVENTID, @CURRENTAPPUSERID) EVENTS
                    inner join
                        dbo.EVENTATTACHMENT on EVENTATTACHMENT.EVENTID = EVENTS.ID
                    inner join
                        dbo.EVENTATTACHMENTTYPECODE
                    on
                        EVENTATTACHMENTTYPECODE.ID = EVENTATTACHMENT.EVENTATTACHMENTTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTATTACHMENT.AUTHORID) NF
                    where 
                        (EVENTS.ID = @EVENTID or @INCLUDESUBEVENTS = 1) and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or EVENTATTACHMENT.TITLE like @TITLE + '%')                        
                    order by
                        DATEENTERED desc;