TXT

showclass

By Monica Ferguson,2014-06-01 12:05
8 views 0
showclass

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

Report this document

For any questions or suggestions please email
cust-service@docsford.com