管家婆辉煌版基础信息搬移工具

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 ****************** -- **************** 判断是否超过５级 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 --搬移后基本信息变化的级数 -- **************** 判断是否超过５级 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