USP_SHELL_GETPAGES

CREATE procedure [dbo].[USP_SHELL_GETPAGES](@APPUSERID uniqueidentifier)
as

begin
    set nocount on;

    declare @IsSysAdmin bit;

    select @IsSysAdmin = ISSYSADMIN 
        from dbo.APPUSER 
        where ID = @APPUSERID;

    if @IsSysAdmin = 1
        select p.ID,
            p.[NAME],
            p.[DESCRIPTION],
            p.CONTEXTRECORDTYPEID,
            p.[PAGEDEFINITIONSPEC].value('declare namespace bbspec="bb_appfx_pagedefinition";/bbspec:PageDefinitionSpec[1]/bbspec:PageHeader[1]/@ImageKey','nvarchar(255)') AS [IMAGEKEY],
            r.[DEFAULTSEARCHLISTID],
            p.[UINAME]
            from dbo.V_INSTALLED_PAGEDEFINITIONCATALOG p left join 
                dbo.RECORDTYPE r on p.CONTEXTRECORDTYPEID = r.ID
        where
            ((p.CONTEXTRECORDTYPEID is null) or (not r.DEFAULTSEARCHLISTID is null))
    else
        begin
            with cte_contextpages (ID, [NAME], [DESCRIPTION], CONTEXTRECORDTYPEID, EXPRESSIONDATAFORMID, IMAGEKEY, PAGEDEFINITIONSPEC, DEFAULTSEARCHLISTID, UINAME) as
            ( select p.ID,
            p.[NAME],
            p.[DESCRIPTION],
            p.CONTEXTRECORDTYPEID,
            p.[PAGEDEFINITIONSPEC].value('declare namespace bbspec="bb_appfx_pagedefinition";/bbspec:PageDefinitionSpec[1]/@ExpressionDataFormID','nvarchar(36)') AS [EXPRESSIONDATAFORMID],
            p.[PAGEDEFINITIONSPEC].value('declare namespace bbspec="bb_appfx_pagedefinition";/bbspec:PageDefinitionSpec[1]/bbspec:PageHeader[1]/@ImageKey','nvarchar(255)') AS [IMAGEKEY],
            p.[PAGEDEFINITIONSPEC],
            r.[DEFAULTSEARCHLISTID],
            p.[UINAME]
            from dbo.V_INSTALLED_PAGEDEFINITIONCATALOG p left join 
                dbo.RECORDTYPE r on p.CONTEXTRECORDTYPEID = r.ID
            where
                ((p.CONTEXTRECORDTYPEID is null) or (not r.DEFAULTSEARCHLISTID is null))
            )

            select cte_contextpages.ID, cte_contextpages.NAME, cte_contextpages.DESCRIPTION, cte_contextpages.CONTEXTRECORDTYPEID, cte_contextpages.IMAGEKEY, cte_contextpages.DEFAULTSEARCHLISTID, cte_contextpages.UINAME
            from cte_contextpages
                left join dbo.DATAFORMINSTANCECATALOG d on d.ID = EXPRESSIONDATAFORMID
                left join dbo.DATAFORMTEMPLATECATALOG t on d.DATAFORMTEMPLATECATALOGID = t.ID
            where (EXPRESSIONDATAFORMID = '00000000-0000-0000-0000-000000000000' and dbo.UFN_SECURITY_APPUSER_GRANTED_PAGECONTENTS_IN_SYSTEMROLE(@APPUSERID, PAGEDEFINITIONSPEC) = 1) 
                or 
                ((coalesce(TEMPLATESPECXML.value(
                        'declare namespace bbspec="bb_appfx_viewdataformtemplate";
                         declare namespace c="bb_appfx_commontypes";
                         /bbspec:ViewDataFormTemplateSpec[1]/@NoSecurityRequired'
                        , 'bit')
                        , 0)
                ) = 1)
                or
                exists (select null
                            from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM v
                            where v.APPUSERID = @APPUSERID and
                                v.DATAFORMINSTANCECATALOGID=EXPRESSIONDATAFORMID
                            group by v.DATAFORMINSTANCECATALOGID
                            having min(convert(tinyint, v.GRANTORDENY)) = 1
                        )

        end

        return 0;
end