sp_helptext proc_GetCanAddGroup
go
CREATE proc proc_GetCanAddGroup
(--返回可以添加的权限组，如当前目录下添加了权限组之后无法再添加
@CatalogId int,--目录id
@GroupId int--权限id
)
as
begin
with getall as
(
select c.*,g.Isinherit,g.e_state from ECM_CatalogTable c left join ECM_Grouping g on c.id=g.c_id where c.[State]=0
),
parent as
(
select * from getall g where g.id=@CatalogId
union all
select g.* from getall g inner join parent p on g.id=p.ParentId
)
,
getCatalogId as(
select id from parent where Isinherit=1 and e_state=0 group by id
union select id from parent where e_state=0 and id=@CatalogId --union：并集,合并
)
select * from userrolegroup
select groupname as lookname,id as lookvalue from userrolegroup where id not in(
select g_id from ECM_Bygroup where r_id in (
select id from ECM_Grouping where c_id in (select id from getCatalogId))
) and u_state=0
union
select groupname as lookname,id as lookvalue from userrolegroup where id in(
select g_id from ECM_Bygroup where r_id = @GroupId) and u_state=0
end
