USP_DATALIST_STEWARDSHIPDOCUMENTATION

CREATE procedure dbo.USP_DATALIST_STEWARDSHIPDOCUMENTATION 
(
@STEWARDSHIPPLANID uniqueidentifier = null,
@DOCUMENTATIONTYPECODE tinyint = null,
@TITLE nvarchar(50) = ''
)
as begin
    set nocount on;

    select    
        STEWARDSHIPNOTE.ID,
        '1C0EF10D-2CE8-4F9C-A3E5-2D3B6BDB59AB' as [VIEWFORMID],
        0 as [DOCUMENTATIONTYPECODE],
        null as [MEDIAURL],
        null as [FILENAME],
        null as [FILEFIELDID],
        'Note' as [DOCUMENTATIONTYPE],
        STEWARDSHIPNOTE.DATEENTERED,
        STEWARDSHIPNOTETYPECODE.DESCRIPTION as [TYPE],
        STEWARDSHIPNOTE.TITLE,
        NF.NAME,
        coalesce(STEWARDSHIPPLANSTEP.OBJECTIVE, '') as STEP

    from dbo.STEWARDSHIPNOTE

    inner join dbo.STEWARDSHIPNOTETYPECODE
        on STEWARDSHIPNOTETYPECODE.ID = STEWARDSHIPNOTE.STEWARDSHIPNOTETYPECODEID

    left outer join dbo.STEWARDSHIPPLANSTEP
        on STEWARDSHIPNOTE.STEPID = STEWARDSHIPPLANSTEP.ID

    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPNOTE.AUTHORID) NF

    where 
        STEWARDSHIPNOTE.PLANID = @STEWARDSHIPPLANID and
        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
        (@TITLE = '' or STEWARDSHIPNOTE.TITLE like @TITLE + '%')

    union all

    select    
        STEWARDSHIPMEDIALINK.ID,
        'A8B5DA4E-B47D-4A93-A756-E4BDF47E738B' as [VIEWFORMID],
        1 as [DOCUMENTATIONTYPECODE],
        STEWARDSHIPMEDIALINK.MEDIAURL,
        null,
        null,
        'Media link' as [DOCUMENTATIONTYPE],
        STEWARDSHIPMEDIALINK.DATEENTERED,
        STEWARDSHIPMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
        STEWARDSHIPMEDIALINK.TITLE,
        NF.NAME,
        coalesce(STEWARDSHIPPLANSTEP.OBJECTIVE, '') as STEP

    from dbo.STEWARDSHIPMEDIALINK

    inner join dbo.STEWARDSHIPMEDIALINKTYPECODE
        on STEWARDSHIPMEDIALINKTYPECODE.ID = STEWARDSHIPMEDIALINK.STEWARDSHIPMEDIALINKTYPECODEID

    left outer join dbo.STEWARDSHIPPLANSTEP
        on STEWARDSHIPMEDIALINK.STEPID = STEWARDSHIPPLANSTEP.ID

    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPMEDIALINK.AUTHORID) NF

    where 
        STEWARDSHIPMEDIALINK.PLANID = @STEWARDSHIPPLANID and
        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
        (@TITLE = '' or STEWARDSHIPMEDIALINK.TITLE like @TITLE + '%')

    union all

    select    
        STEWARDSHIPATTACHMENT.ID,
        'D486D697-42B3-49BC-8F45-EB2DB2069DE3' as [VIEWFORMID],
        2 as [DOCUMENTATIONTYPECODE],
        null,
        STEWARDSHIPATTACHMENT.FILENAME,
        'FILE',
        'Attachment' as [DOCUMENTATIONTYPE],
        STEWARDSHIPATTACHMENT.DATEENTERED,
        STEWARDSHIPATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
        STEWARDSHIPATTACHMENT.TITLE,
        NF.NAME,
        coalesce(STEWARDSHIPPLANSTEP.OBJECTIVE, '') as STEP

    from dbo.STEWARDSHIPATTACHMENT

    inner join dbo.STEWARDSHIPATTACHMENTTYPECODE
        on STEWARDSHIPATTACHMENTTYPECODE.ID = STEWARDSHIPATTACHMENT.STEWARDSHIPATTACHMENTTYPECODEID

    left outer join dbo.STEWARDSHIPPLANSTEP
        on STEWARDSHIPATTACHMENT.STEPID = STEWARDSHIPPLANSTEP.ID

    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPATTACHMENT.AUTHORID) NF

    where 
        STEWARDSHIPATTACHMENT.PLANID = @STEWARDSHIPPLANID and
        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
        (@TITLE = '' or STEWARDSHIPATTACHMENT.TITLE like @TITLE + '%')

    union all

    select    
        CONSTITUENTNOTE.ID,    
        '55EBA39F-1513-46EC-85DC-7FEBAE3F0698' as [VIEWFORMID],
        3 as [DOCUMENTATIONTYPECODE],
        null as [MEDIAURL],
        null as [FILENAME],
        null as [FILEFIELDID],
        'Note - Constituent' as [DOCUMENTATIONTYPE],    
        CONSTITUENTNOTE.DATEENTERED,
        CONSTITUENTNOTETYPECODE.DESCRIPTION as TYPE,
        CONSTITUENTNOTE.TITLE,
        NF.NAME,
        null as STEP
    from
      dbo.STEWARDSHIPPLAN
    inner join
      dbo.CONSTITUENTNOTE on CONSTITUENTNOTE.CONSTITUENTID = STEWARDSHIPPLAN.CONSTITUENTID
    inner join
      dbo.CONSTITUENTNOTETYPECODE on CONSTITUENTNOTETYPECODE.ID = CONSTITUENTNOTE.CONSTITUENTNOTETYPECODEID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTNOTE.AUTHORID) NF
    where 
        STEWARDSHIPPLAN.ID = @STEWARDSHIPPLANID and
        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 3) and
        (@TITLE = '' or CONSTITUENTNOTE.TITLE like @TITLE + '%')                    

    order by
        DOCUMENTATIONTYPE asc, DATEENTERED desc;

end