ALTER PROCEDURE [dbo].[AssignAreaRightToUsers]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @CustomerID varchar(50)
DECLARE CustCurosr CURSOR FOR
SELECT OID FROM tbl_customdata WHERE newflag=0
OPEN CustCurosr
FETCH NEXT FROM CustCurosr
INTO @CustomerID
WHILE @@FETCH_STATUS = 0
BEGIN
exec [AssignAreaRightToUsersByOID] @CustomerID
FETCH NEXT FROM CustCurosr
INTO @CustomerID
END
CLOSE CustCurosr
DEALLOCATE CustCurosr
END
ALTER PROCEDURE [dbo].[AssignAreaRightToUsersByOID]
@OID varchar(40)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @AreaID varchar(50)
DECLARE @RAOID varchar(50)
DECLARE @RIGHTEEOID varchar(50) --用户ID
--获取客户所属区域
select @AreaID=tbl_saleorg_oid from tbl_customdata where oid =@OID
PRINT '@AreaID=' + @AreaID
--根据区域找到有该区域权限的的用户
DECLARE MyCurosr CURSOR FOR
SELECT DISTINCT OID,RIGHTEEOID FROM TBL_RIGHTACTION
WHERE OID
IN(
SELECT a.RAOID FROM TBLAREARIGHTS A, TBL_SALEORG B WHERE A.TBL_SALEORG_OID = B.OID
AND b.oid =@AreaID
AND B.SOG_STATUS = 'USAGE'
)
OPEN MyCurosr
FETCH NEXT FROM MyCurosr
INTO @RAOID,@RIGHTEEOID
--给这些用户赋拥有这个客户的权限
BEGIN TRY
BEGIN TRANSACTION
--先删除该客户对应的区域权限
DELETE FROM TBLAREARIGHTS WHERE TBL_SALEORG_OID=lower(@OID)
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '@RAOID=' + @RAOID
PRINT '@RIGHTEEOID=' + @RIGHTEEOID
INSERT INTO TBLAREARIGHTS (TBL_SALEORG_OID,RAOID,ARS_RIGHTS) VALUES(lower(@OID),@RAOID,'Browse| | |')
FETCH NEXT FROM MyCurosr
INTO @RAOID,@RIGHTEEOID
END
UPDATE tbl_customdata SET newflag=1 WHERE oid = @OID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
CLOSE MyCurosr
DEALLOCATE MyCurosr
END