由于工程实现上的某些小问题,为了达到方便实现如图效果,对数据结构做了一点点的调整。
新的数据结构如下图:
第一个图片的数据源视图:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'RoleDataPermit') AND OBJECTPROPERTY(id, N'ISVIEW') = 1)
DROP VIEW RoleDataPermit
GO
/*****视图:查询所有角色的数据权限*****/
CREATE VIEW RoleDataPermit
AS
with
List as (
select distinct
G.ID as DataId,
null as ParentId,
PM.RoleId,
0 as Action,
G.Name as 模块,
null as 读写权限
from SYS_ModuleGroup G
join SYS_Module M on M.ModuleGroupId = G.ID
join SYS_RolePerm_Module PM on PM.ModuleId = M.ID
left join SYS_RolePerm_Data PD on PD.PermId = PM.ID
where PM.Permission >= 0
or PD.ID is not null
union all
select M.ID as DataId,
case when M.ModuleGroupId is null then M.ParentId else M.ModuleGroupId end as ParentId,
PM.RoleId,
1 as Action,
M.ApplicationName as 模块,
null as 读写权限
from SYS_Module M
join SYS_RolePerm_Module PM on PM.ModuleId = M.ID
where PM.Permission >= 0
union all
select PM.ID as DataId,
PM.ModuleId as ParentId,
PM.RoleId,
2 as Action,
'无归属数据' as 模块,
case when PM.Permission = 0 then '只读' else '读写' end as 读写权限
from SYS_RolePerm_Module PM
where PM.Permission >= 0
union all
select PD.ID as DataId,
M.ModuleId as ParentId,
M.RoleId,
PD.Mode + 3 as Action,
case PD.Mode when 0 then '仅本人' when 1 then '仅本部门' when 2 then '本部门所有' when 3 then '本机构所有' when 4 then '根机构所有' else '自定义' end as 模块,
case when PD.Permission = 0 and PD.Mode < 5 then '只读' when PD.Permission = 1 and PD.Mode < 5 then '读写' else null end as 读写权限
from SYS_RolePerm_Data PD
join SYS_RolePerm_Module M on M.ID = PD.PermId
union all
select PC.ID as DataId,
PC.PermDataId as ParentId,
PM.RoleId,
9 as Action,
O.FullName as 模块,
case when PC.Permission = 0 then '只读' else '读写' end as 读写权限
from SYS_RolePerm_Module PM
join SYS_RolePerm_Data PD on PD.PermId = PM.ID
and PD.Mode = 5
join SYS_RolePerm_Custom PC on PC.PermDataId = PD.ID
and PC.OrgId is not null
join SYS_Organization O on O.ID = PC.OrgId
union all
select PC.ID as DataId,
PC.PermDataId as ParentId,
PM.RoleId,
10 as Action,
U.Name + '(' + U.LoginName + ')' as 模块,
case when PC.Permission = 0 then '只读' else '读写' end as 读写权限
from SYS_RolePerm_Module PM
join SYS_RolePerm_Data PD on PD.PermId = PM.ID
and PD.Mode = 5
join SYS_RolePerm_Custom PC on PC.PermDataId = PD.ID
and PC.UserId is not null
join SYS_User U on U.ID = PC.UserId
)
select newid() as ID, * from List
GO