SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
*********************************
@szDBName:
'PTYPE' : 库存商品
'BTYPE' : 往来单位
'EMPLOYEE' : 职 员
'STOCK' : 仓 库
注意:参数@szDBName的值必须与对应基本信息表的名称相同
*********************************
*/
/*************** 错误代码ErrorCode *****************
-1 新的父节点不存在! (BaseInfoMoveNodeErrorMessage_1)
-2 新的父节点有帐务数据! (BaseInfoMoveNodeErrorMessage_2)
-3 系统错误!请与经销商联系。(BaseInfoMoveNodeErrorMessage_3)
-11 分类超过5级! (BaseInfoMoveNodeErrorMessage_11)
-4 抱歉!工具暂不支持该类基本信息的搬移。(BaseInfoMoveNodeErrorMessage_4)
BaseInfoMoveNodeErrorMessage_5=新的父节点有相关信息存在(往来帐务、库存),不能分类!
BaseInfoMoveNodeErrorMessage_6=数据不能从祖先节点搬移到子孙节点!
BaseInfoMoveNodeErrorMessage_10=系统设置错误!
**************** 错误代码ErrorCode ******************/
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='P' AND name='D_MoveOneBasicNew') DROP proc D_MoveOneBasicNew
go
create PROCEDURE D_MoveOneBasicNew
(
@szDBName VARCHAR(20),
@szOldID VARCHAR(25),
@szOldParID VARCHAR(25),
@szNewParID VARCHAR(25),
@NewLeveal INT,
@szNewID VARCHAR(25) OUTPUT
)
AS
DECLARE @CreateIDResult INT
DECLARE @NewTypeID VARCHAR(25)
DECLARE @nSoncount INT
DECLARE @nSonnum INT
DECLARE @ParRec INT
DECLARE @ErrorCode INT
SET @ErrorCode = 0
DECLARE @FieldName VARCHAR(15),
@OtherField VARCHAR(15),
@TableName VARCHAR(30),
@RightTable VARCHAR(10),
@UpdateGroup varchar(1)
BEGIN TRANSACTION
SET @FieldName = ''
SET @OtherField = ''
SET @RightTable = ''
IF @szDBName = 'PTYPE'
BEGIN
SET @FieldName = 'PTypeID'
SET @RightTable = 'T_PRIGHT'
END ELSE IF @szDBName = 'BTYPE'
BEGIN
SET @FieldName = 'BTypeID'
SET @RightTable = 'T_BRIGHT'
SET @UpdateGroup = 'B'
END ELSE IF @szDBName = 'EMPLOYEE'
BEGIN
SET @FieldName = 'ETypeID'
SET @UpdateGroup = 'E'
END ELSE IF @szDBName = 'STOCK'
BEGIN
SET @FieldName = 'KTypeID'
SET @OtherField = 'KTypeID2'
SET @RightTable = 'T_KRIGHT'
SET @UpdateGroup = 'G'
END
if left(@szNewParID,len(@szOldID)) = @szOldID
begin
SET @ErrorCode = -6
GOTO THE_END
end
IF @FieldName = ''
BEGIN
SET @ErrorCode = -4
GOTO THE_END
END
-- **************** 获得节点(@szOldID)的SonNum值 BEGIN ******************
DECLARE @SQL_SonNum NVARCHAR(200),
@SonNum INT
SET @SQL_SonNum = N'SELECT @SonNum = SonNum FROM ' + @szDBName + ' WHERE TypeID = ' + QUOTENAME(@szOldID, '''')
EXECUTE sp_executesql @SQL_SonNum, N'@SonNum INT OUTPUT', @SonNum OUTPUT
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
GOTO THE_END
-- **************** 获得节点(@szOldID)的SonNum值 END ******************
-- **************** 判断是否超过5级 BEGIN ******************
DECLARE @SQL_Level NVARCHAR(200),
@MaxLevel INT,
@MinLevel INT
SET @SQL_Level = N'SELECT @MaxLevel = MAX(leveal), @MinLevel = MIN(leveal) FROM ' + @szDBName + ' WHERE Deleted = 0 AND TypeID LIKE ' + QUOTENAME(@szOldID + '%', '''')
EXECUTE sp_executesql @SQL_Level, N'@MaxLevel INT OUTPUT, @MinLevel INT OUTPUT', @MaxLevel OUTPUT, @MinLevel OUTPUT
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
GOTO THE_END
IF @MaxLevel - @MinLevel + @NewLeveal > 5
BEGIN
SET @ErrorCode = -11
IF @ErrorCode <> 0
GOTO THE_END
END
DECLARE @LevelChanged INT
SET @LevelChanged = @NewLeveal - @MinLevel --搬移后基本信息变化的级数
-- **************** 判断是否超过5级 END ******************
--************************* 生成新的TypeID BEGIN **************************
EXEC @CreateIDResult = F_B_createId @szNewParid,@szDBName,@NewTypeID OUT,@nSonnum OUT,@nSoncount OUT, @ParRec OUT
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO THE_END
IF @CreateIDResult <> 0
BEGIN
IF @CreateIDResult = -101 SET @ErrorCode = -1
ELSE IF @CreateIDResult = -102 SET @ErrorCode = -2
ELSE IF @CreateIDResult = -103 SET @ErrorCode = -3
GOTO THE_END
END
IF LEN(@szNewParid) > 25
BEGIN
SET @ErrorCode = -11
GOTO THE_END
END
SET @szNewID = @NewTypeID
--************************* 生成新的TypeID END **************************
--************************* UPDATE基本信息表 BEGIN **************************
DECLARE @SQL_SetNewTypeID VARCHAR(800),
@SQL_SetSonTypeID VARCHAR(800),
@SQL_DecreaseOldSonNum VARCHAR(800),
@SQL_IncreaseNewSonNum VARCHAR(800),
@SQL_UpdateOtherTable VARCHAR(800),
@SQL_UpdateOtherTable2 VARCHAR(800)
SET @SQL_SetNewTypeID = 'UPDATE ' + @szDBName + ' SET TypeID = ' + QUOTENAME(@NewTypeID, '''') +
' , ParID = ' + QUOTENAME(@szNewParid, '''') +
' , ParRec = ' + QUOTENAME(@ParRec, '''') +
' , leveal =' + CAST(@NewLeveal AS VARCHAR(3)) +
' WHERE TypeID = ' + QUOTENAME(@szOldId, '''')
SET @SQL_DecreaseOldSonNum = 'UPDATE ' + @szDBName + ' SET SonNum = SonNum - 1' +
' WHERE TypeID = ' + QUOTENAME(@szOldParId, '''')
SET @SQL_IncreaseNewSonNum = 'UPDATE ' + @szDBName + ' SET SonNum = SonNum + 1, SonCount = SonCount + 1' +
' WHERE TypeID = ' + QUOTENAME(@szNewParid, '''')
EXEC(@SQL_SetNewTypeID)
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO THE_END
EXEC(@SQL_DecreaseOldSonNum)
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO THE_END
EXEC(@SQL_IncreaseNewSonNum)
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO THE_END
IF @SonNum > 0 -- ****** 非根节点 *******
BEGIN
-- *** Update节点(@szOldId)的所有子节点的信息 BEGIN ***
SET @SQL_SetSonTypeID = 'UPDATE ' + @szDBName + ' SET ' +
'TypeID = ' + QUOTENAME(@NewTypeID, '''') + ' + RIGHT(TypeID, LEN(TypeID) - LEN(' + QUOTENAME(@szOldId, '''') + ')), ' +
'ParID = ' + QUOTENAME(@NewTypeID, '''') + ' + RIGHT(ParID, LEN(ParID) - LEN(' + QUOTENAME(@szOldId, '''') + ')), ' +
'leveal = leveal + ' + CAST(@LevelChanged AS VARCHAR(3)) +
' WHERE TypeID LIKE ' + QUOTENAME(@szOldId + '%', '''') +
' AND TypeID <> ' + QUOTENAME(@szOldId, '''') +
' AND Deleted = 0'
EXEC(@SQL_SetSonTypeID)
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
GOTO THE_END
-- *** Update节点(@szOldId)的所有子节点的信息 END ***
END
--************************* UPDATE基本信息表 END **************************
--************************* UPDATE基本信息权限表 BEGIN **************************
DECLARE @UpdateBaseRightSQL VARCHAR(800)
IF @RightTable <> ''
BEGIN
DECLARE @SQL_Exists NVARCHAR(200),
@TableNum INT
SET @SQL_Exists = N'SELECT @TableNum = COUNT(*) FROM SYSOBJECTS WHERE NAME = ' + QUOTENAME(@RightTable, '''')
EXECUTE sp_executesql @SQL_Exists, N'@TableNum INT OUTPUT', @TableNum OUTPUT
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO THE_END
IF @TableNum > 0 --如果权限表(T_KRight/T_PRIGHT/T_BRight)存在(较早版本无)
BEGIN
IF @SonNum = 0 -- ******* 根节点 *******
BEGIN
SET @UpdateBaseRightSQL = 'UPDATE ' + @RightTable + ' SET RightID = ' + QUOTENAME(@NewTypeID, '''') +
' WHERE RightID = ' + QUOTENAME(@szOldId, '''')
END ELSE -- ****** 非根节点 *******
BEGIN
SET @UpdateBaseRightSQL = 'UPDATE ' + @RightTable + ' SET RightID = ' + QUOTENAME(@NewTypeID, '''') +
' + RIGHT(RightID, LEN(RightID) - LEN(' + QUOTENAME(@szOldId, '''') + '))' +
' WHERE RightID LIKE ' + QUOTENAME(@szOldId + '%', '''')
END
EXEC(@UpdateBaseRightSQL)
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO THE_END
execute