CREATE PROCEDURE [dbo].[up_char_sanction]
	@kind					tinyint, -- 1 : sanction, 2:release
	@sanction_uid			uniqueidentifier,
	@personnel				nvarchar(50),
	@releaser				nvarchar(50),
	@comment				nvarchar(128),
	@character_id			uniqueidentifier,	
	@sanction_type			tinyint,
	@sanction_count			tinyint,
	@sanction_period_type	tinyint
as 
set nocount on
		
begin tran

	declare	@get_uid	uniqueidentifier,
			@period		int,
			@cur_time	datetime,
			@end_time	datetime,
			@new_uid	uniqueidentifier,
			@is_insert	tinyint

	set @get_uid = '00000000-0000-0000-0000-000000000000'

	if @sanction_period_type = 1		set @period = 3
	if @sanction_period_type = 2		set @period = 7
	if @sanction_period_type = 3		set @period = 10
	if @sanction_period_type = 4		set @period = 15
	if @sanction_period_type = 5		set @period = -1

--	select @get_uid=uid from Character_Sanction where
--		character_id = @character_id and sanction_count=@sanction_count

	set @cur_time = getdate()
	if @sanction_type <> 5
	begin
		set @end_time = dateadd(day, @period, @cur_time)
	end
	else
	begin
		set @end_time = NULL
	end

	set @new_uid = '00000000-0000-0000-0000-000000000000'
	set @is_insert = 0

	if @kind = 1
	begin
		if @get_uid <> '00000000-0000-0000-0000-000000000000'
		begin
			set @new_uid = @get_uid
			set @is_insert = 1
			update Character_Sanction set
					sanction_type			= @sanction_type,
					sanction_count			= @sanction_count, 
					sanction_period_type	= @sanction_period_type,
					period					= @period,
					start_time				= @cur_time,
					end_time				= @end_time,
					personnel				= @personnel,
					releaser				= '',	-- Rlease init
					comment					= '',
					is_apply				= 1,
					reg_date				= @cur_time
			where uid = @get_uid
		end
		else
		begin
			set @new_uid = NEWID()
			set @is_insert = 1
			insert into Character_Sanction(
					uid,
					sanction_type,
					sanction_count, 
					sanction_period_type,
					period,
					character_id,
					start_time,
					end_time,
					personnel,
					releaser,
					comment,
					is_apply,
					reg_date)
			values(
					@new_uid,
					@sanction_type,
					@sanction_count, 
					@sanction_period_type,
					@period,
					@character_id,
					@cur_time,
					@end_time,
					@personnel,
					'',	-- Rlease init
					'',
					1,
					@cur_time)
		end
	end
	else
	begin
		set @new_uid = @sanction_uid
		set @is_insert = 0

		select @new_uid = uid from Character_Sanction with (READUNCOMMITTED) where 
				character_id = @character_id and is_apply = 1

		update Character_Sanction set
					releaser				= @releaser,
					comment					= @comment,
					is_apply				= 0
		where character_id = @character_id
		
	--	where uid = @sanction_uid
	end

	if EXISTS (select uid from Character_Sanction  with (READUNCOMMITTED) where 
			character_id = @character_id and is_apply = 1)
	begin
		update CharacterTable set Block_YN = 'Y' where character_id=@character_id
	end
	else
	begin
		update CharacterTable set Block_YN = 'N' where character_id=@character_id
	end

declare @ERR int

 SET @ERR = @@ERROR
        IF @ERR <> 0 BEGIN
	RAISERROR('처리오류', 16, 10)
  	IF @@TRANCOUNT <> 0  ROLLBACK
		RETURN (@ERR)
	END
       COMMIT TRAN

select NEW_ID = @new_uid, IS_INSERT = @is_insert