showclass.txt

Coffice协同办公管理系统标准版的功能模块覆盖了所有企业级用户的业务应用。能够协助企业员工进行日常办公管理

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


DECLARE	@UserName varchar(255)
DECLARE	@RIGHTCode int 
DECLARE	@IncludeFunctionNode int 
/*

============================================================
功能:	得到@UserName所有显示的组
参数:
	@UserName varchar(255)		:	用户名
	@RIGHTCode int =1		:	浏览权代号
	@IncludeFunctionNode		:	是否包含功能接点

备注:	可以优化
============================================================

*/
set @UserName='qiany'
set @RIGHTCode=1
set @IncludeFunctionNode =1
DECLARE @Staff_id int
DECLARE @Position_id int

SET NOCOUNT ON
SET @staff_id=0

--取得用户的ID和职位ID
SELECT 
	@staff_id = staff_id,
	@Position_id = Position_ID
	FROM 
		v_uds_staff_in_position
	WHERE staff_name = @username	


DECLARE @ids varchar(2000)
SET @ids =''

/*
DECLARE @sql nvarchar(2000)
DECLARE @tmpids varchar(2000)
DECLARE @oldids varchar(2000)


SET @tmpids=''
SET @oldids =''
*/

CREATE TABLE #Position(id int)

INSERT INTO #position EXEC sp_GetAllChildPositionID @Position_id

select @Position_id
select * from #position
--得到间接的浏览权，即职位对其下属的项目监督权
--得到该接点的所有子接点

/*
SELECT @ids = @ids + CONVERT(varchar,Position_id) + ',' 
	FROM 
		uds_Position 
	WHERE super_Position_id = @Position_id  
	  and super_Position_id <>Position_id 

--有子接点，则去掉最后的逗号
IF LEN(@ids)>0
	SET @ids = LEFT(@ids,LEN(@ids)-1)

CREATE TABLE #Position(id int)
SET @oldids = @ids

--得到该成员的所有下级职位接点
WHILE LEN(@oldids)>0
BEGIN
	--得到子接点的下级子接点表
	SET @sql = N'INSERT INTO #Position 
				SELECT Position_id 
					FROM 
						uds_Position 
					WHERE super_Position_id in (' + @oldids +')'
	EXEC (@sql)

	--合并接点表
	SET @tmpids=''
	SELECT @tmpids = @tmpids + CONVERT(varchar,id) + ',' 
		FROM #Position
	IF LEN(@tmpids)>0
		SET @tmpids = LEFT(@tmpids,LEN(@tmpids)-1)

	--把新的接点集合串作为下次循环的条件
	SET @oldids = @tmpids
	--删除接点表
	DELETE FROM #Position

	SET @ids = @ids + ',' + @tmpids
END 
*/


CREATE TABLE #staff (staff_id int)

--加入本人
INSERT INTO #staff values(@staff_id)

--加入所有下级成员

/*
IF LEN(@ids)>0
BEGIN
	IF RIGHT(@ids,1)=','
		SET @ids = LEFT(@ids,LEN(@ids)-1)		
	
	SET @sql =N'INSERT INTO #staff 
			SELECT a.staff_id 
				FROM 
					uds_staff a,
					uds_staff_in_position b 
				WHERE a.staff_id = b.staff_id 
				  and b.Position_id in (' + @ids + ')'
	PRINT @sql
	EXEC (@sql)
END
*/

INSERT INTO #staff 
			SELECT a.staff_id 
				FROM 
					uds_staff a,
					uds_staff_in_position b,
					#Position c
				WHERE 	a.staff_id = b.staff_id 
					and b.position_id = c.id
DROP TABLE #Position
--创建临时类表
CREATE TABLE #class (obj_id int)

--把所有可显示的类ID收集起来
--act_id=1为显示权
IF @staff_id>0
BEGIN

		SELECT *
			FROM 
				uds_assgn_rule a
			WHERE a.Position_id in (
							SELECT Position_id 
								FROM 
									uds_staff_in_position 
								WHERE staff_id in (
											SELECT staff_id FROM #staff
										   )
						) 
				  and based_on =1 
				  and a.act_id=@RIGHTCode

	--从职位中，能查看的接点
	INSERT INTO #class 
		SELECT team_id 
			FROM 
				uds_assgn_rule a
			WHERE a.Position_id in (
							SELECT Position_id 
								FROM 
									uds_staff_in_position 
								WHERE staff_id in (
											SELECT staff_id FROM #staff
										   )
						) 
				  and based_on =1 
				  and a.act_id=@RIGHTCode
	--从项目中，能查看的接点
/*	INSERT INTO #class 
		SELECT team_id 
			FROM 
				uds_assgn_rule a
			WHERE a.team_id in (
						SELECT team_id 
							FROM 
								uds_staff_in_team 
							WHERE staff_id in (
										SELECT staff_id FROM #staff
									   )
--是否只有成员拥有项目的权利
--							   and member_type = 1
					    ) 
			  and based_on =2 
			  and a.act_id =@RIGHTCode


	--项目角色，能查看的接点
	INSERT INTO #class
		SELECT A.team_id
			FROM
				uds_staff_in_team a,
				uds_activity b
			WHERE a.member_type = b.act_id
			   and b.proc_id = @RIGHTCode
			   and a.staff_id in (
						SELECT staff_id 
							FROM #staff
					     )

	--从角色中，能查看的接点
	INSERT INTO #class 
		SELECT team_id 
			FROM 
				uds_assgn_rule a
			WHERE a.role_id in (
						SELECT role_id 
							FROM 
								uds_staff_in_role 
							WHERE staff_id = @staff_id
					    )
			and based_on =3 
			and a.act_id =@RIGHTCode
	--从个人中，能查看的接点
	INSERT INTO #class 
		SELECT team_id 
			FROM 
				uds_assgn_rule a
			WHERE a.staff_id in (
						SELECT staff_id 
							FROM #staff
					     )
			  and based_on =4 
			  and a.act_id =@RIGHTCode
*/
END 

DROP TABLE #staff
IF EXISTS(SELECT 1 FROM #class WHERE obj_id =0) 
BEGIN
	IF @IncludeFunctionNode = 1 
	SELECT  * 
		FROM uds_class 
		WHERE classid = classparentid
		ORDER BY classtype 
	ELSE
		SELECT  * 
			FROM uds_class 
			WHERE classid = classparentid
			  AND classtype <=1
			ORDER BY classtype 
	PRINT '全局'
	RETURN 
END
	
DECLARE @id INT
DECLARE @pid INT
DECLARE @curid INT

SET @ids=''

--得到可显示类ID的字符串
SELECT @ids = @ids +  CONVERT(varchar,obj_id) +',' FROM #class

--删除临时表#CLASS中所有非根节点的节点
WHILE LEN(@ids)>0
BEGIN
	CREATE TABLE #path (pid int)	
	SET @id = CONVERT(int,substring(@ids,1,charindex(',',@ids)-1))

	--备份原ID
	SET @curid = @id

	--得到此ID的所有父ID到临时Path表
	SELECT @pid=classparentid FROM uds_class WHERE classid=@id	
	WHILE @pid<>@id
	BEGIN
--		PRINT 'pid=' + CONVERT(varchar, @pid ) + ':id=' + CONVERT(varchar,@id)
		INSERT INTO #path (pid) values(@pid)
		SET @id = @pid
		SELECT @pid=classparentid FROM uds_class WHERE classid=@id
	END
	
	--如果有记录在其所有父ID中,则其为某一节点的子ID,删除之
	IF EXISTS(SELECT 1 FROM #class WHERE obj_id in (SELECT pid FROM #path))				
		DELETE FROM #class WHERE obj_id = @curid
		
	DROP TABLE #path
	--剪切字符串
	SET @ids = substring(@ids,charindex(',',@ids)+1,LEN(@ids))	

END

--是否包含功能接点
IF @IncludeFunctionNode = 1 
	--得到拥有的类信息
	SELECT * FROM uds_class 
		WHERE classid IN (SELECT obj_id FROM  #class)  
		ORDER BY classtype
ELSE
	--得到拥有的类信息,只包含项目和文档
	SELECT * FROM uds_class 
		WHERE classid in (SELECT obj_id FROM  #class)  
		  AND classtype <=1
		ORDER BY classtype
	
DROP TABLE #class

SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO