USP_DATALIST_APPLICATIONUSERFEATURES

CREATE procedure dbo.USP_DATALIST_APPLICATIONUSERFEATURES
(
    @APPUSERID uniqueidentifier,
    @FEATURETYPECODE tinyint = null
)
as
    select 'Data List' as FEATURETYPE, DL.UINAME as DISPLAYNAME, DL.DESCRIPTION, DL.SECURITYUIFOLDER, 'res:datalistspec' as IMAGEKEY
    from dbo.V_INSTALLED_DATALISTCATALOG as DL
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 3) and
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                DL.ID = SRA.DATALISTCATALOGID
            group by
                SRA.DATALISTCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'Business Process' as FEATURETYPE, BP.NAME as DISPLAYNAME, BP.DESCRIPTION, BP.SECURITYUIFOLDER, 'res:businessprocessspec' as IMAGEKEY
    from dbo.V_INSTALLED_BUSINESSPROCESSCATALOG as BP
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 1) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BUSINESSPROCESS_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                BP.ID = SRA.BUSINESSPROCESSCATALOGID
            group by
                SRA.BUSINESSPROCESSCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'Dashboard' as FEATURETYPE, DASH.NAME as DISPLAYNAME, DASH.DESCRIPTION, DASH.SECURITYUIFOLDER, 'res:flashdashspec' as IMAGEKEY
    from dbo.V_INSTALLED_DASHBOARDCATALOG as DASH
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 2) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_DASHBOARD_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                DASH.ID = SRA.DASHBOARDCATALOGID
            group by
                SRA.DASHBOARDCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'Record Operation' as FEATURETYPE, RO.UINAME as DISPLAYNAME, RO.DESCRIPTION, RO.SECURITYUIFOLDER, 'res:recordoperationspec' as IMAGEKEY
    from dbo.V_INSTALLED_RECORDOPERATIONCATALOG as RO
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 8) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_RECORDOPERATION_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                RO.ID = SRA.RECORDOPERATIONCATALOGID
            group by
                SRA.RECORDOPERATIONCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select
        case DFT.MODE
            when 0 then 'View Form'
            when 1 then 'Edit Form'
            when 2 then 'Add Form'
        end as FEATURETYPE,
        DFI.UINAME as DISPLAYNAME, DFI.DESCRIPTION, DFT.SECURITYUIFOLDER,
        case DFT.MODE
            when 0 then 'res:viewdataformtemplatespec'
            when 1 then 'res:editdataformtemplatespec'
            when 2 then 'res:adddataformtemplatespec'
        end as IMAGEKEY
    from dbo.DATAFORMINSTANCECATALOG as DFI
    inner join dbo.V_INSTALLED_DATAFORMTEMPLATECATALOG as DFT on DFI.DATAFORMTEMPLATECATALOGID = DFT.ID
    where (@FEATURETYPECODE is null or 
            (@FEATURETYPECODE = 0 and DFT.MODE = 2) or
            (@FEATURETYPECODE = 4 and DFT.MODE = 1) or
            (@FEATURETYPECODE = 13 and DFT.MODE = 0)) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                DFI.ID = SRA.DATAFORMINSTANCECATALOGID
            group by
                SRA.DATAFORMINSTANCECATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'System Privilege' as FEATURETYPE, SP.NAME, SP.DESCRIPTION, SP.SECURITYUIFOLDER, 'res:systemprivilegespec' as IMAGEKEY
    from dbo.SYSTEMPRIVILEGECATALOG as SP
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 12) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                SP.ID = SRA.SYSTEMPRIVILEGECATALOGID
            group by
                SRA.SYSTEMPRIVILEGECATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'Map Entity' as FEATURETYPE, ME.UINAME as DISPLAYNAME, ME.DESCRIPTION, ME.SECURITYUIFOLDER, 'res:mapentityspec' as IMAGEKEY
    from dbo.V_INSTALLED_MAPENTITYCATALOG as ME
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 6) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_MAPENTITY_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                ME.ID = SRA.MAPENTITYCATALOGID
            group by
                SRA.MAPENTITYCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'KPI' as FEATURETYPE, KPI.NAME, KPI.DESCRIPTION, KPI.SECURITYUIFOLDER, 'res:kpispec' as IMAGEKEY
    from dbo.V_INSTALLED_KPICATALOG as KPI
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 5) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_KPI_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                KPI.ID = SRA.KPICATALOGID
            group by
                SRA.KPICATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'Query View' as FEATURETYPE, QUERYVIEW.DISPLAYNAME [NAME], QUERYVIEW.DESCRIPTION, QUERYVIEW.SECURITYUIFOLDER, 'res:queryviewspec' as IMAGEKEY
    from dbo.V_INSTALLED_QUERYVIEWCATALOG as QUERYVIEW
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 7) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                QUERYVIEW.ID = SRA.QUERYVIEWCATALOGID
            group by
                SRA.QUERYVIEWCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )
    and cast(coalesce(QUERYVIEW.QUERYVIEWSPEC.value('declare namespace bbfa="bb_appfx_queryview";/bbfa:QueryViewSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit) = 1

    union all

    select 'Report Parameter' as FEATURETYPE, REPORT.UINAME as DISPLAYNAME, REPORT.DESCRIPTION,
    replace(cast(coalesce(REPORT.REPORTSPECXML.value('declare namespace bbfa="bb_appfx_report";/bbfa:ReportSpec[1]/bbfa:Folder[1]','nvarchar(100)'), 'TEST') as nvarchar(255)), '/','\') as SECURITYUIFOLDER, 'res:reportspec' as IMAGEKEY
    from dbo.V_INSTALLED_REPORTCATALOG as REPORT
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 9) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_REPORT_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                REPORT.ID = SRA.REPORTCATALOGID
            group by
                SRA.REPORTCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'Search List' as FEATURETYPE, SEARCHLIST.UINAME as DISPLAYNAME, SEARCHLIST.DESCRIPTION, SEARCHLIST.SECURITYUIFOLDER, 'res:searchlistspec' as IMAGEKEY
    from dbo.V_INSTALLED_SEARCHLISTCATALOG as SEARCHLIST
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 10) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SEARCHLIST_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                SEARCHLIST.ID = SRA.SEARCHLISTCATALOGID
            group by
                SRA.SEARCHLISTCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    union all

    select 'Smart Query' as FEATURETYPE, SMARTQUERY.UINAME as DISPLAYNAME, SMARTQUERY.DESCRIPTION, SMARTQUERY.SECURITYUIFOLDER, 'res:smartqueryspec' as IMAGEKEY
    from dbo.V_INSTALLED_SMARTQUERYCATALOG as SMARTQUERY
    where (@FEATURETYPECODE is null or @FEATURETYPECODE = 11) and 
        exists (
        select null
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY_BYROLE SRA
            where
                SRA.APPUSERID = @APPUSERID and
                SMARTQUERY.ID = SRA.SMARTQUERYCATALOGID
            group by
                SRA.SMARTQUERYCATALOGID 
            having
                min(convert(tinyint, SRA.GRANTORDENY)) = 1
        )

    order by FEATURETYPE, DISPLAYNAME