Gngwc Room

USE [EPIC_001011012]
GO
/****** Object:  StoredProcedure [dbo].[_InsertRefTeleLink]    Script Date: 05/10/2012 23:17:46 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[_InsertRefTeleLink]
@Service				int,
@OwnerTeleport_CodeName		varchar(128),
@TargetTeleport_CodeName		varchar(128),
@Fee					int,
@RestrictBindMethod			tinyint,
@CheckResult				tinyint,
@Restrict1				int,
@Data1_1				int,
@Data1_2				int,
@Restrict2				int,
@Data2_1				int,
@Data2_2				int,
@Restrict3				int,
@Data3_1				int,
@Data3_2				int,
@Restrict4				int,
@Data4_1				int,
@Data4_2				int,
@Restrict5				int,
@Data5_1				int,
@Data5_2				int
as
	declare @OwnerTeleport		int
	declare @TargetTeleport		int
	declare @obj_common_id	int
	declare @link			int
begin transaction
begin
	set @link = 0
	select @OwnerTeleport = ID from _RefTeleport where CodeName128 = @OwnerTeleport_CodeName
	select @TargetTeleport = ID from _RefTeleport where CodeName128 = @TargetTeleport_CodeName
	if (@OwnerTeleport = 0 or @TargetTeleport = 0)
	begin
		rollback transaction
		raiserror('owner 혹은 target teleport가 존재하지 않는다. [owner: %s, target: %s]', @OwnerTeleport_CodeName, @TargetTeleport_CodeName)
		return
	end
	if (exists (select * from _RefTeleLink where OwnerTeleport = @OwnerTeleport and TargetTeleport = @TargetTeleport))
	begin
		update _RefTeleLink
		set Service = @Service,
			Fee = @Fee,
			RestrictBindMethod = @RestrictBindMethod, CheckResult = @CheckResult,
			Restrict1 = @Restrict1, Data1_1 = @Data1_1, Data1_2 = @Data1_2,
			Restrict2 = @Restrict2, Data2_1 = @Data2_1, Data2_2 = @Data2_2,
			Restrict3 = @Restrict3, Data3_1 = @Data3_1, Data3_2 = @Data3_2,
			Restrict4 = @Restrict4, Data4_1 = @Data4_1, Data4_2 = @Data4_2,
			Restrict5 = @Restrict5, Data5_1 = @Data5_1, Data5_2 = @Data5_2
		where OwnerTeleport = @OwnerTeleport and TargetTeleport = @TargetTeleport
	end
	else
	begin
		insert into _RefTeleLink
		values (@Service,
				@OwnerTeleport,
				@TargetTeleport,
				@Fee,
				@RestrictBindMethod,
				@CheckResult,
				@Restrict1, @Data1_1, @Data1_2,
				@Restrict2, @Data2_1, @Data2_2,
				@Restrict3, @Data3_1, @Data3_2,
				@Restrict4, @Data4_1, @Data4_2,
				@Restrict5, @Data5_1, @Data5_2)
	end
	if (@@error <> 0)
	begin
		rollback transaction
		raiserror('TeleportLink 추가(혹은 갱신) 실패! [%s, %s]', 1, 16, @OwnerTeleport_CodeName, @TargetTeleport_CodeName)
		return
	end
	
	commit transaction
	
end

USE [EPIC_001011012]
GO
/****** Object:  StoredProcedure [dbo].[_InsertRefTeleport]    Script Date: 05/10/2012 23:19:04 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[_InsertRefTeleport]
@Service		as int,
@CodeName128	as varchar(129),
@AssocObjCodeName	as varchar(129),
@ZoneName128	as varchar(129),
@GenRegionID		as int,
@GenPos_X		as smallint,
@GenPos_Y		as smallint,
@GenPos_Z		as smallint,
@GenAreaRadius	as smallint,
@CanBeResurrectPos	as tinyint,
@CanGotoResurrectPos as tinyint
as
	declare @assoc_ref_obj		int
	declare @count			int
begin transaction
begin
	select @count = count(*) from _RefTeleport where AssocRefObjID = @assoc_ref_obj or CodeName128 = @CodeName128	
	if (@count > 1)
	begin
		rollback transaction
		raiserror('동일한 텔레포트가 중복되어 지정된 듯 하다! 확인 요망! [%s]', 1, 16, @CodeName128)
		return
	end
	set @assoc_ref_obj = 0
	if (@AssocObjCodeName <> 'xxx')
	begin
		execute @assoc_ref_obj = _FindRefObjCommonID @AssocObjCodeName
		if (@assoc_ref_obj is null or @assoc_ref_obj = 0)
		begin
			rollback transaction
			raiserror('Attach할 오브젝트를 찾을 수 없다! : %s - %s', 1, 16, @CodeName128, @AssocObjCodeName)
			return
		end
	end
	-- sql 이 unsigned short를 지원하지 않는 관계로 WORD 형으로 입력되는 RegionID를 Short형으로 변환한거다... 음수로 바꾼거지 뭐...
	if (@GenRegionID > 32767)
	begin
		set @GenRegionID = (@GenRegionID - 32767 - 2) - 32767
	end
	if (exists (select * from _RefTeleport where CodeName128 = @CodeName128))
	begin
		update _RefTeleport
		set Service = @Service, AssocRefObjID = @assoc_ref_obj, 
		     ZoneName128 = @ZoneName128,
		     GenRegionID = @GenRegionID, 
		     GenPos_X = @GenPos_X, GenPos_Y = @GenPos_Y, GenPos_Z = @GenPos_Z,
		     GenAreaRadius = @GenAreaRadius,
		     CanBeResurrectPos = @CanBeResurrectPos, CanGotoResurrectPos = @CanGotoResurrectPos
		where CodeName128 = @CodeName128
	end
	else		
	begin
		insert into _RefTeleport 
		values  (@Service, @CodeName128, @assoc_ref_obj, @ZoneName128,
			@GenRegionID, @GenPos_X, @GenPos_Y, @GenPos_Z, @GenAreaRadius,
			@CanBeResurrectPos, @CanGotoResurrectPos)
	end
	if (@@error <> 0)
	begin
		rollback transaction
		
		if (@count <> 0)
			begin raiserror('Teleport 데이터 Update 실패! :%s', 1, 16, @CodeName128) end
		else
			begin raiserror('Teleport 데이터 Insert 실패! :%s', 1, 16, @CodeName128) end
		return
	end
	
	commit transaction
end

USE [EPIC_001011012]
GO
/****** Object:  StoredProcedure [dbo].[_LinkRefRegions]    Script Date: 05/10/2012 23:19:21 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE	[dbo].[_LinkRefRegions]
AS
begin TRANSACTION
begin	
	declare @LinkNum	int
	declare @this_region	int
	declare @region_x	int
	declare @region_z	int
	declare @wRegionID	smallint
	declare @wTempRID	smallint
	declare @col_link	varchar(256)
	declare @query		varchar(256)
	declare @db_id_to_link	smallint
	
	-- clear all existing region link
	set @LinkNum = 1
	while (@LinkNum <= 10)
	begin
		set @query = 'update _RefRegion set LinkedRegion_' + cast(@LinkNum as varchar(10)) + ' = 0'
		execute (@query)
		
		set @LinkNum = @LinkNum + 1
	end
	-- loop all region
	declare region_cursor CURSOR FOR
	select 	wRegionID
	from	_RefRegion
	OPEN region_cursor
	FETCH NEXT FROM region_cursor INTO @wRegionID
	WHILE @@FETCH_STATUS = 0
	begin		
		if (@@error = 0 and @wRegionID <> 0)
		begin
			-- ??? ??!
			if (@wRegionID > 0)
			begin
				set @LinkNum = 8
				while (@LinkNum >= 0)
				begin
					set @region_z = (@wRegionID & 0xff00) / 256
					set @region_x = (@wRegionID & 0x0ff)
					set @wTempRID = 0;
									
					if (@LinkNum = 0)
					begin
						set @region_x = @region_x - 1
						set @col_link = 'LinkedRegion_1'
					end
					else if (@LinkNum = 1)
					begin
						set @region_x = @region_x - 1
						set @region_z = @region_z + 1
						set @col_link = 'LinkedRegion_2'
					end
					else if (@LinkNum = 2)
					begin
						set @region_z = @region_z + 1
						set @col_link = 'LinkedRegion_3'
					end
					else if (@LinkNum = 3)
					begin
						set @region_x = @region_x + 1
						set @region_z = @region_z + 1
						set @col_link = 'LinkedRegion_4'
					end
					else if (@LinkNum = 4)
					begin
						set @region_x = @region_x + 1
						set @col_link = 'LinkedRegion_5'
					end
					else if (@LinkNum = 5)
					begin
						set @region_x = @region_x + 1
						set @region_z = @region_z - 1
						set @col_link = 'LinkedRegion_6'
					end
					else if (@LinkNum = 6)
					begin
						set @region_z = @region_z - 1
						set @col_link = 'LinkedRegion_7'
					end
					else if (@LinkNum = 7)
					begin
						set @region_x = @region_x - 1
						set @region_z = @region_z - 1
						set @col_link = 'LinkedRegion_8'
					end
	
					set @wTempRID = (@region_z & 0x00ff) * 256
					set @wTempRID = @wTempRID + (@region_x & 0x00ff)
	
					select @db_id_to_link = wRegionID
					from _RefRegion
					where wRegionID = @wTempRID
	
					if (@@ROWCOUNT <> 0)
					begin
						set @query = 'update _RefRegion set  ' + @col_link + ' = ' + cast(@db_id_to_link as varchar(10)) + ' where wRegionID = ' + cast(@wRegionID as varchar(10))
					end
					else	-- region link 0
					begin
						set @query = 'update _RefRegion set  ' + @col_link + ' = 0 where wRegionID = ' + cast(@wRegionID as varchar(10))
					end
	
					execute(@query)
				set @LinkNum = @LinkNum - 1
				end
			end
		end
	FETCH NEXT FROM region_cursor INTO @wRegionID
	end
	
	CLOSE region_cursor
	DEALLOCATE region_cursor
	COMMIT TRANSACTION
end