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

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

新的数据结构如下图：

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

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'RoleDataPermit') AND OBJECTPROPERTY(id, N'ISVIEW') = 1)DROP VIEW RoleDataPermitGO/*****视图：查询所有角色的数据权限*****/CREATE VIEW RoleDataPermitASwith List as (select distinct       G.ID as DataId,       null as ParentId,       PM.RoleId,       0 as Action,       G.Name as 模块,       null as 读写权限from SYS_ModuleGroup Gjoin SYS_Module M on M.ModuleGroupId = G.IDjoin SYS_RolePerm_Module PM on PM.ModuleId = M.IDleft join SYS_RolePerm_Data PD on PD.PermId = PM.IDwhere PM.Permission >= 0  or PD.ID is not nullunion allselect 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 Mjoin SYS_RolePerm_Module PM on PM.ModuleId = M.IDwhere PM.Permission >= 0union allselect 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 PMwhere PM.Permission >= 0union allselect 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 PDjoin SYS_RolePerm_Module M on M.ID = PD.PermIdunion allselect 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 PMjoin SYS_RolePerm_Data PD on PD.PermId = PM.ID  and PD.Mode = 5join SYS_RolePerm_Custom PC on PC.PermDataId = PD.ID  and PC.OrgId is not nulljoin SYS_Organization O on O.ID = PC.OrgIdunion allselect 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 PMjoin SYS_RolePerm_Data PD on PD.PermId = PM.ID  and PD.Mode = 5join SYS_RolePerm_Custom PC on PC.PermDataId = PD.ID  and PC.UserId is not nulljoin SYS_User U on U.ID = PC.UserId)select newid() as ID, * from ListGO