p_department

USE [db_NLSOS]
GO
/****** 对象:  StoredProcedure [dbo].[p_department]    脚本日期: 05/04/2012 14:58:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[p_department]  
-- alter PROCEDURE [dbo].[p_department]  

-- ========================================  
-- 部门管理
-- ========================================  
-- 参数:   
-- 返回值：  
-- 注：5次失败尝试。（锁定失败、死锁）  
-- ========================================  

-- 测试数据

-- USE [db_NLSOS]
-- GO
-- 
-- DECLARE	@return_value int
-- 
-- EXEC	@return_value = [dbo].[p_department]
-- 		@action = N'update',
-- 		@id = N'8',
-- 		@category = N'事业局',
-- 		@parent_id = N'2',
-- 		@order_by = N'1',
-- 		@number = N'1',
-- 		@name = N'1',
-- 		@note = N'1'
-- 
-- SELECT	'Return Value' = @return_value
-- 
-- GO


-- ========================================  

(        

	-- 参数

	 @action           varChar(1000)  = 'insert' -- 取值: Insert 、Update 、Delete、Audit、setActive
	,@id               varChar(1000)  = '0'
	,@category         varChar(1000)  = ''
	,@parent_id        varChar(1000)  = '0'
	,@source_id        varChar(1000)  = '0' -- 源
	,@target_id        varChar(1000)  = '0' -- 目标
	,@order_by         varChar(1000)  = ''
	,@number           varChar(1000)  = ''
	,@name             varChar(1000)  = ''
	,@inserted_date    varChar(1000)  = getdate
	,@updated_date     varChar(1000)  = getdate
	,@note             varChar(1000)  = ''
	,@ids              varChar(1000)  = '' -- 批量删除
	
	
	
)    
AS    
-- 定义变量    
-------------------------------------------------    
DECLARE    

     @Err           INT            -- 错误编号    
    ,@ErrCounter    INT            -- 错误次数    
    ,@ErrCounterMax INT            -- 最大错误次数    
    ,@code          varChar(1000)  -- 返回代码      
    ,@message       varChar(1000)  -- 返回信息   

	set @code    = '0'       -- 默认值
	set @message = 'success' -- 
	
	
DECLARE @new_id_path varchar(500) -- 新的部门 ID_Path
	
	
-- 存储过程 Start
BEGIN    

-- 事务 Start
BEGIN TRANSACTION TRANS  

    SET @ErrCounter = 0     
    SET @ErrCounterMax = 5    
    
    LockTimeOutRetry: -- 锁定超时入口    
    BEGIN TRY    
	
        -- （1）Insert  
        -------------------------------------------------  
		-- 1、读取编号
		-- 2、插入数据
		-- 3、更新路径
        -------------------------------------------------  
        IF @action = 'insert'  
		
		begin
		
			-- 声明变量
			declare @new_number varchar(50)
			declare @number_category varchar(50)
			
			set @number_category = 'department_number'

			-- 更新数值
			update t_number 
			set [current_number] = [current_number] + 1 
			where category = @number_category


			-- 取得编号
			select 
				@new_number 
					= [prefix] 
						+ REPLICATE('0',len([format])- len([current_number])) 
						+ cast([current_number] as varchar)
						+ [suffix]
			from t_number
			where category = @number_category
		
			-- 添加记录
			INSERT INTO [db_NLSOS].[dbo].[t_department]
			   ([category]
			   ,[parent_id]
			   ,[order_by]
			   ,[number]
			   ,[name]
			   ,[inserted_date]
			   ,[updated_date]
			   ,[note]
			   )
			VALUES
			   ('事业局' -- <category, varchar(500),>
			   ,@parent_id -- <parent_id, int,>
			   ,@order_by
			   ,@new_number -- <number, varchar(500),>
			   ,@name -- <name, varchar(500),>
			   ,getdate() -- <inserted_date, datetime,>
			   ,null -- <updated_date, datetime,>
			   ,'' -- <note, varchar(500),>
			   )

			   
			-- #更新 id_path
			declare @SCOPE_IDENTITY int  
			
			-- 刚插入记录的 ID
			SELECT @SCOPE_IDENTITY = SCOPE_IDENTITY()
			
			-- 新 id_path
			select @new_id_path = id_path
			from t_department
			where id = @parent_id
			
			set @new_id_path
				= (case when @new_id_path is null then '/0/' else @new_id_path end) 
					+ convert(varchar(500), @SCOPE_IDENTITY )+ '/'
			
			-- 更新
			update [t_department] 			
			set [id_path] = @new_id_path
			where id = @SCOPE_IDENTITY
		
		end
	
        -- （2）update  
        -------------------------------------------------  
		-- 1、读取新路径
		-- 2、更新数据
        -------------------------------------------------  
        IF @action = 'update'
		
		begin
					
			
			-- 新 id_path
			select @new_id_path = id_path
			from t_department
			where id = @parent_id
			
			set @new_id_path
				= (case when @new_id_path is null then '/0/' else @new_id_path end) 
					+ convert(varchar(500), @id )+ '/'
			
			--（2）更新数据
			update [t_department] 			
			set  
				-- [category]      = @category    -- <category, varchar(500),>
				-- [parent_id]     = @parent_id   -- <parent_id, int,>
				--,[id_path]       = @new_id_path -- <id_path, varchar(500),>
				 [order_by]      = @order_by    -- <order_by, varchar(500),>
				,[number]        = @number      -- <number, varchar(500),>
				,[name]          = @name        -- <name, varchar(500),>
				,[updated_date]  = getDate()    -- <updated_date, datetime,>
				,[note]          = @note        -- <note, varchar(500),>				
			where
				id = @id  -- <搜索条件,,>
			
		end
		
		
	
        -- 删
        -------------------------------------------------  
		-- 1、id = 1 不能删
        -------------------------------------------------  
        IF @action = 'delete'  
		
		begin
		
			-- 变量
			declare @deletable varchar(50) -- 是否可以删除？
			declare @hasChildren varchar(50) -- 是否有子部门？
			declare @isExist varchar(50) -- 是否存在？
			
			-- 是否有子部门（是否是其它部门的上级部门。）
			;with a as(
				select parent_id from t_department where parent_id = @id
			)
			select @hasChildren = case when count(*) > 0 then 'Y' else 'N' end  from a
			
			-- 是否存在
			select @isExist = case when count(*) > 0 then 'Y' else 'N' end
			from t_department 
			where id = @id

		
			if @id = 0 -- 根
			begin
				set @code    = '5'
				set @message = '根目录，不能删除！'
			end
			else if @hasChildren = 'Y' -- 有子部门
			begin
				set @code    = '3'
				set @message = '将删除的记录，拥有下级子目录，不可一并删除。'
			end
			else if @isExist = 'N' -- 不存在
			begin
				set @code    = '5'
				set @message = '记录不存在。'
			end
			else
				-- 删除数据
				delete t_department where id = @id
		
		end
	
        -- 批量删除  
        -------------------------------------------------  
		-- 1、id = 1 不能删
        -------------------------------------------------  
        IF @action = 'multi_delete'  
		
		begin
		

			-- 表变量
			DECLARE @t_department_id Table
			(
				departmentId int NOT NULL
			)

			-- 插入测试值
			--insert into @t_department_id(departmentId) values(5)
			--select * from @t_department_id

			-- ids
			--declare @department_ids varchar(50)
			--set @department_ids = '1,133,23'

			-- 临时字符串
			declare @str varchar(50)
			declare @departmentId varchar(50)
			set @str = @ids

			-- 存入临时表
			while charIndex( ',', @str ) > 0
			begin	
				set @departmentId = left( @str, charIndex (',', @str)-1)
				set @str = right( @str, len(@str) - len(@departmentId)- 1 )
				--select @departmentId, len(@str) - len(@departmentId) -1, @str
				insert into @t_department_id(departmentId) values( @departmentId )
			end

			insert into @t_department_id(departmentId) values( @str )

			-- select * from @t_department_id

			-- select * from @t_department_id t1 cross join @t_department_id t2

			--declare @deletable varchar(50) -- 是否可以删除？
			;
			with a as(

				select departmentId from @t_department_id

			)
			,b as(
				select a.departmentId, b.id,b.id_path
				from a cross join t_department b
				where charIndex('/' + convert(varchar(50),a.departmentId) + '/', b.id_path) > 0
					and a.departmentId != b.id -- 本身不删除
			)
			select @deletable = case when count(*) > 0 then 'N' else 'Y' end  from b

			-- select @deletable as [deletable]
			
			if charIndex(',1,',','+ @ids+',') > 0
			begin
				set @code    = '5'
				set @message = '根目录，不能删除！'
			end
			else if @deletable = 'N'
			begin
				set @code    = '3'
				set @message = '将删除的记录，拥有下级子目录，不可一并删除。'
			end
			else
				-- 删除数据
				exec( 'delete t_department where id in ('+ @ids + ')')				
		end
		-- end of multi_delete
		
		
	
        -- 移植 Graft
        -------------------------------------------------  
		-- 嫁接、部门调整
        -------------------------------------------------  
        IF @action = 'graft'  
		
		begin
		
			-- 目标不能是源的直接父（相当于没变化）
			-- 源部门不能是目标部门的N级父部门。（0级也不行，0级意味着同一部门。）
			
			set @parent_id = -1

			declare @is_same     varchar(50) -- 源与目标相同吗？
			declare @is_parent   varchar(50) -- 源是目标的直接子节点吗？
			declare @is_n_parent varchar(50) -- 源是目标的N级父节点吗？

			set @is_same     = 'no'
			set @is_parent   = 'no'
			set @is_n_parent = 'no'

			-- 相同吗？
			if( @source_id = @target_id )
				begin
					set @is_same = 'yes'
					set @code    = '201204281048'
					set @message = '节点相同，操作取消。'
				end
				
				
			-- 直接子吗？
			if( @is_same = 'no' )
				begin
					select @parent_id = parent_id from t_department where id = @source_id
					if @parent_id = @target_id
					begin
						set @is_parent = 'yes'
						set @code    = '201204281106'
						set @message = '源节点是目标节点的直接子节点（移植无意义！），操作取消。'
					end
				end
				
				
			-- N 级父吗？
			if( @is_same = 'no' and @is_parent = 'no' )	
				begin	
					declare @target_id_2 varchar(50)
					set @target_id_2 = @target_id
					
					while( @parent_id != 0 )
						begin
							select @parent_id = parent_id from t_department where id = @target_id_2
							if( @parent_id = @source_id)
							begin
								set @is_n_parent = 'yes'
								set @code    = '201204281107'
								set @message = '源节点是目标的N级父，操作取消。'
								BREAK
							end
							set @target_id_2 = @parent_id
						end
				end
					
			-- 执行移植
			if( @is_same = 'no' and @is_parent = 'no' and @is_n_parent = 'no' )				
				begin
					update t_department set parent_id = @target_id where id = @source_id
				end
			
			
			
			
		end
		-- end of graft
		
		
			
    END TRY    
	
	
	
	
	
	
    -- 错误检测    
    -------------------------------------------------    
    BEGIN CATCH  
	
        set @Err = @@ERROR   
		
        IF @Err <> 0  
            BEGIN    
                ROLLBACK TRANSACTION TRANS   
                GOTO ErrorHandler      
            END  
			
    END CATCH    
	
    COMMIT TRANSACTION TRANS  
	
    -- Success    
    select 
		 @code    as [code]
		,@message as [message] 
		
    RETURN    
	
	
	
    
    -- 错误处理    
    -------------------------------------------------    
    ErrorHandler:    
        IF (@err = 1222 OR @err = 1205) AND @ErrCounter >= @ErrCounterMax    
            Begin    
                RAISERROR('Unable to Lock Data after a few attempts.',16,1)    
                set @code = @err    
                SELECT @message =ERROR_MESSAGE()    
            END    
        IF @err = 1222 OR @err = 1205 -- Lock Timeout / Deadlock    
            BEGIN    
                WAITFOR DELAY '00:00:00.25'    
                SET @ErrCounter = @ErrCounter + 1    
                GOTO LockTimeOutRetry    
            END    
			
    -- else unknown error    
	
    set @code = @err
	
    SELECT @message = ERROR_MESSAGE()     
	
    -- RAISERROR( @message ,16,1)    
    select 
		 @code       as [code]
		,@message    as [message]
	
END    

RETURN

-- end