改进版本的精确数据权限定义和实现

由于工程实现上的某些小问题，为了达到方便实现如图效果，对数据结构做了一点点的调整。

新的数据结构如下图：

第一个图片的数据源视图：

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