USP_DATALIST_SYSTEMROLEFEATURES

CREATE procedure dbo.USP_DATALIST_SYSTEMROLEFEATURES(@SYSTEMROLEID uniqueidentifier)
as

  with XMLNAMESPACES
  (
    'bb_appfx_datalist' as dl,
    'bb_appfx_recordoperation' as ro,
    'bb_appfx_queryview' as qv,
    'bb_appfx_report' as rp
  )
    select 'Data List' as FEATURETYPE, DL.UINAME as DISPLAYNAME, DL.DESCRIPTION, DL.SECURITYUIFOLDER, 'res:datalistspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_DATALISTCATALOG as DL inner join dbo.SYSTEMROLEPERM_DATALIST as PERM on DL.ID = PERM.DATALISTCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and cast(isnull(DL.DATALISTSPEC.value('/dl:DataListSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit) = 1

    union all

    select 'Business Process' as FEATURETYPE, BP.NAME as DISPLAYNAME, BP.DESCRIPTION, BP.SECURITYUIFOLDER, 'res:businessprocessspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSIONN
    from dbo.V_INSTALLED_BUSINESSPROCESSCATALOG as BP inner join dbo.SYSTEMROLEPERM_BUSINESSPROCESS as PERM on BP.ID = PERM.BUSINESSPROCESSCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and len(BP.SECURITYUIFOLDER) > 0

    union all

    select 'Dashboard' as FEATURETYPE, DASH.NAME as DISPLAYNAME, DASH.DESCRIPTION, DASH.SECURITYUIFOLDER, 'res:flashdashspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_DASHBOARDCATALOG as DASH inner join dbo.SYSTEMROLEPERM_DASHBOARD as PERM on DASH.ID = PERM.DASHBOARDCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID

    union all

    select 'Record Operation' as FEATURETYPE, RO.UINAME, RO.DESCRIPTION, RO.SECURITYUIFOLDER, 'res:recordoperationspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_RECORDOPERATIONCATALOG as RO inner join dbo.SYSTEMROLEPERM_RECORDOPERATION as PERM on RO.ID = PERM.RECORDOPERATIONCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and cast(isnull(RO.RECORDOPERATIONSPECXML.value('/ro:RecordOperationSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit) = 1

    union all

    select case when DFT.MODE = 0 then 'View Form' when DFT.MODE = 1 then 'Edit Form' else 'Add Form' end as FEATURETYPE, DFI.UINAME as DISPLAYNAME, DFI.DESCRIPTION, DFT.SECURITYUIFOLDER, case when DFT.MODE = 0 then  'res:viewdataformtemplatespec' when DFT.MODE = 1 then 'res:editdataformtemplatespec' else 'res:adddataformtemplatespec' end as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.DATAFORMINSTANCECATALOG as DFI inner join dbo.V_INSTALLED_DATAFORMTEMPLATECATALOG as DFT on DFI.DATAFORMTEMPLATECATALOGID = DFT.ID
    inner join dbo.SYSTEMROLEPERM_DATAFORMINSTANCE as PERM on DFI.ID = PERM.DATAFORMINSTANCECATALOGID
    where (SYSTEMROLEID = @SYSTEMROLEID)
    and    DFT.SECURITYUIDISPLAYFEATURE = 1

    union all
    select 'KPI' as FEATURETYPE, KPI.NAME, KPI.DESCRIPTION, KPI.SECURITYUIFOLDER, 'res:kpispec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_KPICATALOG as KPI inner join dbo.SYSTEMROLEPERM_KPI as PERM on KPI.ID = PERM.KPICATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and len(KPI.SECURITYUIFOLDER) > 0

    union all

    select 'Query View' as FEATURETYPE, QUERYVIEW.DISPLAYNAME [NAME], QUERYVIEW.DESCRIPTION, QUERYVIEW.SECURITYUIFOLDER, 'res:queryviewspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_QUERYVIEWCATALOG as QUERYVIEW inner join dbo.SYSTEMROLEPERM_QUERYVIEW as PERM on QUERYVIEW.ID = PERM.QUERYVIEWCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and cast(isnull(QUERYVIEW.QUERYVIEWSPEC.value('/qv:QueryViewSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit) = 1

    union all

    select 'Smart Query' as FEATURETYPE, SMARTQUERY.UINAME as DISPLAYNAME, SMARTQUERY.DESCRIPTION, SMARTQUERY.SECURITYUIFOLDER, 'res:smartqueryspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_SMARTQUERYCATALOG as SMARTQUERY inner join dbo.SYSTEMROLEPERM_SMARTQUERY as PERM on SMARTQUERY.ID = PERM.SMARTQUERYCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and len(SMARTQUERY.SECURITYUIFOLDER) > 0

    union all

    select 'Report Parameter' as FEATURETYPE, REPORT.UINAME as DISPLAYNAME, REPORT.DESCRIPTION, 
        replace(cast(isnull(REPORT.REPORTSPECXML.value('/rp:ReportSpec[1]/rp:Folder[1]','nvarchar(100)'), 'TEST') as nvarchar(255)), '/','\') as SECURITYUIFOLDER, 
        'res:reportspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_REPORTCATALOG as REPORT inner join dbo.SYSTEMROLEPERM_REPORT as PERM on REPORT.ID = PERM.REPORTCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID

    union all

    select 'System Privilege' as FEATURETYPE, SYSTEMPRIVILEGE.NAME as DISPLAYNAME, SYSTEMPRIVILEGE.DESCRIPTION, SYSTEMPRIVILEGE.SECURITYUIFOLDER, 
        'res:systemprivilegespec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.SYSTEMPRIVILEGECATALOG as SYSTEMPRIVILEGE inner join dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE as PERM on SYSTEMPRIVILEGE.ID = PERM.SYSTEMPRIVILEGECATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and len(SYSTEMPRIVILEGE.SECURITYUIFOLDER) > 0

    union all

    select 'Search List' as FEATURETYPE, SEARCHLIST.UINAME as DISPLAYNAME, SEARCHLIST.DESCRIPTION, SEARCHLIST.SECURITYUIFOLDER, 
        'res:searchlistspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_SEARCHLISTCATALOG as SEARCHLIST inner join dbo.SYSTEMROLEPERM_SEARCHLIST as PERM on SEARCHLIST.ID = PERM.SEARCHLISTCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and len(SEARCHLIST.SECURITYUIFOLDER) > 0

  union all

    select 'Map Entity' as FEATURETYPE, MAPENTITY.UINAME as DISPLAYNAME, MAPENTITY.DESCRIPTION, MAPENTITY.SECURITYUIFOLDER, 
        'res:mapentityspec' as IMAGEKEY, (case PERM.GRANTORDENY when 0 then 'Denied' else 'Granted' end) as PERMISSION
    from dbo.V_INSTALLED_MAPENTITYCATALOG as MAPENTITY inner join dbo.SYSTEMROLEPERM_MAPENTITY as PERM on MAPENTITY.ID = PERM.MAPENTITYCATALOGID
    where SYSTEMROLEID = @SYSTEMROLEID
        and len(MAPENTITY.SECURITYUIFOLDER) > 0

    order by FEATURETYPE, DISPLAYNAME