/**
*
*/
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[P_HL_RightList]')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE P_HL_RightList
END
GO
CREATE PROC P_HL_RightList
@username NVARCHAR(100) ,
@role_type INT --1 planning ---2buying--3finance
,
@data_Type NVARCHAR(50)
AS
BEGIN TRY
BEGIN TRAN
DECLARE @rolename NVARCHAR(80)
PRINT @username
--角色id
--1取得角色名称
SELECT @rolename = 'Heartland_' + USERNAME + '_' + cm.CNAME + '_'
+ dp.deEName
FROM dbo.[USER] ur
LEFT JOIN dbo.Department dp ON ur.usDeid = dp.deID
LEFT JOIN dbo.COMPANY cm ON dp.deCompany_id = cm.COMPANY_ID
WHERE ur.USERNAME = @username
PRINT @rolename
--2为用户创建新角色
DECLARE @role_di INT
INSERT INTO dbo.QX_WorkRole
( RoleName, Remark, RoleType_ID )
VALUES ( @rolename, -- RoleName - varchar(80)
'', -- Remark - varchar(1000)
1 -- RoleType_ID - tinyint
)
SET @role_di = SCOPE_IDENTITY()
--3为角色添加工作组
DECLARE @rolegroup_id INT
SELECT @rolegroup_id = rg.RoleGroup_ID
FROM dbo.QX_RoleGroup rg
INNER JOIN dbo.Department d ON d.deEName = rg.RoleGroupDesc
INNER JOIN dbo.[USER] u ON u.usDeid = d.deID
AND USERNAME = @username
INSERT INTO dbo.QX_WorkRole_RoleGroup
( RoleGroup_ID, Role_ID )
VALUES ( @rolegroup_id, -- RoleGroup_ID - int
@role_di -- Role_ID - int
)
----4 角色添加工作组
INSERT INTO dbo.QX_UserRole
( User_ID ,
Role_ID
)
SELECT User_ID ,
@role_di
FROM dbo.[USER] us
WHERE us.USERNAME = @username
----5 角色功能权限
IF @role_type = 1
BEGIN
INSERT INTO dbo.QX_RoleFunRight
( Fun_ID ,
Role_ID
)
SELECT Fun_ID ,
@role_di
FROM CAMSDB_DEV.dbo.QX_RoleFunRight rdr
INNER JOIN CAMSDB_DEV.dbo.QX_WorkRole wr ON wr.RoleName = 'Planning_OOH_模板'
AND rdr.Role_ID = wr.Role_ID
END
IF @role_type = 3 --Operation
BEGIN
INSERT INTO dbo.QX_RoleFunRight
( Fun_ID ,
Role_ID
)
SELECT DISTINCT
( Fun_ID ) ,
@role_di
FROM CAMSDB_DEV.dbo.QX_RoleFunRight rdr
INNER JOIN CAMSDB_DEV.dbo.QX_WorkRole wr ON ( wr.RoleName = 'Buying_Operation_模板'
OR wr.RoleName = 'Buying_OOH_模板'
)
AND rdr.Role_ID = wr.Role_ID
END
IF @role_type = 4
BEGIN
INSERT INTO dbo.QX_RoleFunRight
( Fun_ID ,
Role_ID
)
SELECT DISTINCT
( Fun_ID ) ,
@role_di
FROM CAMSDB_DEV.dbo.QX_RoleFunRight rdr
INNER JOIN CAMSDB_DEV.dbo.QX_WorkRole wr ON ( wr.RoleName = 'Finance_AR_模板'
OR wr.RoleName = 'Finance_AP_模板'
)
AND rdr.Role_ID = wr.Role_ID
END
IF @role_type = 123 --nego
BEGIN
INSERT INTO dbo.QX_RoleFunRight
( Fun_ID ,
Role_ID
)
SELECT DISTINCT
( Fun_ID ) ,
@role_di
FROM CAMSDB_DEV.dbo.QX_RoleFunRight rdr
INNER JOIN CAMSDB_DEV.dbo.QX_WorkRole wr ON ( wr.RoleName = 'Buying_nego_模板'
OR wr.RoleName = 'Buying_OOH_模板'
OR wr.RoleName = 'Planning_OOH_模板'
OR wr.RoleName = 'Buying_Operation_模板'
)
AND rdr.Role_ID = wr.Role_ID
END
IF @role_type = 23 --nego
BEGIN
INSERT INTO dbo.QX_RoleFunRight
( Fun_ID ,
Role_ID
)
SELECT DISTINCT
( Fun_ID ) ,
@role_di
FROM CAMSDB_DEV.dbo.QX_RoleFunRight rdr
INNER JOIN CAMSDB_DEV.dbo.QX_WorkRole wr ON ( wr.RoleName = 'Buying_nego_模板'
OR wr.RoleName = 'Buying_OOH_模板'
OR wr.RoleName = 'Buying_Operation_模板'
)
AND rdr.Role_ID = wr.Role_ID
END
--------添加数据权限--------------------
IF @data_Type = '1'
BEGIN
INSERT INTO dbo.QX_RoleDataRight
( ObjID ,
ObjType_ID ,
Role_ID
)
SELECT DISTINCT
ObjID ,
ObjType_ID ,
@role_di
FROM dbo.QX_RoleDataRight rdr
INNER JOIN dbo.QX_WorkRole wr ON wr.RoleName = 'HLAllClientAllBrand'
AND rdr.Role_ID = wr.Role_ID
END
ELSE
BEGIN
INSERT INTO dbo.QX_RoleDataRight
( ObjID ,
ObjType_ID ,
Role_ID
)
SELECT DISTINCT
ObjID ,
ObjType_ID ,
@role_di
FROM dbo.QX_RoleDataRight rdr
INNER JOIN dbo.QX_WorkRole wr ON wr.RoleName = 'HLAllClientAllBrand'
AND rdr.Role_ID = wr.Role_ID
AND ( rdr.ObjType_ID <> 3
OR rdr.ObjType_ID <> 5
)
END
IF @@ERROR <> 0
BEGIN
ROLLBACK
END
ELSE
PRINT '通过了'
COMMIT
END TRY
BEGIN CATCH
EXEC dbo.RaiseErrorInfo @Info = '系统不存在收款单位及存在收款单位公司权限'
END CATCH