		COMMIT TRAN
		RETURN
	END
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_setDice]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_setDice
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.5.16
' Description :
'   주사위 게임에서 한판이 끝났을때 결과를 저장함.
' Parameter :
'	input Parameter
'		@sDice    : 주사위 결과
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2413 : invaild @sDice
'				-2492 : db error(insert)
'	return Record
'		nGameNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_setDice] 
	@sDice    nvarchar(3)
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @nGameNo AS int

	SET @nRet    = 0
	SET @nGameNo = 0

	IF LEN(ISNULL(@sDice, '')) <= 0 BEGIN
		SET @nRet = -3314
		RETURN
	END

	INSERT INTO tGambleDice (sDice)
	VALUES (@sDice)
	IF @@ERROR <> 0 OR @@IDENTITY = 0 BEGIN
		SET @nRet = -3392
		RETURN
	END
	ELSE BEGIN
		SET @nGameNo = @@IDENTITY
		SELECT @nGameNo
	END

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_setDiceLog]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_setDiceLog
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.5.16
' Description :
'   주사위 게임에서 한판이 끝났을때 각 유저의 로그를 기록함
' Parameter :
'	input Parameter
'		@nGameNo       : 게임번호
'		@nCharNo       : 캐릭터번호
'		@nSlotNo       : 슬롯번호
'		@nBattingMoney : 배팅금액(코인)
'		@nBattingRate  : 배당율
'		@bStatus       : 승/패(0:패/1:승)
'		@nTaxRate      : 세금비율
'		@nDividend     : 배당금(코인)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2492 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_setDiceLog]
	@nGameNo       int 
,	@nCharNo       int
,	@nSlotNo       int
,	@nBattingMoney bigint
,	@nBattingRate  int
,	@bStatus       bit
,	@nTaxRate      int
,	@nDividend     bigint
,	@nRet          int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	INSERT INTO tGambleDiceLog (nGameNo, nCharNo, nSlotNo, nBattingMoney, nBattingRate, bStatus, nTaxRate, nDividend)
	VALUES (@nGameNo, @nCharNo, @nSlotNo, @nBattingMoney, @nBattingRate, @bStatus, @nTaxRate, @nDividend)
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -3392
		RETURN
	END

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_setDiceResult]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_setDiceResult
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.5.16
' Description :
'   주사위 게임에서 한판이 끝났을때 각 유저의 총 배당금을 지급함.
' Parameter :
'	input Parameter
'		@nGameNo        : 게임번호
'		@nCharNo        : 캐릭터번호
'		@nTotalDividend : 배당금 총 합계(코인)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2414 : invaild @nGameNo
'				-2415 : invaild @nCharNo
'				-2421 : not enough Money
'				-2492 : db error(insert)
'				-2493 : db error(update)
'	return Record
'		nCustomCharNo, nMoney
' Modify 2011.7.11
'	동시성 문제 해결을 위해 테이블 힌트 추가 : WITH(UPDLOCK, HOLDLOCK)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_setDiceResult] 
	@nGameNo        int
,	@nCharNo        int
,	@nTotalDividend bigint
,	@nRet           int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @coin bigint, @resultCoin bigint

	SET @nRet  = 0
	SET @coin = 0
	SET @resultCoin = 0

	IF ISNULL(@nGameNo, 0) <= 0 BEGIN
		SET @nRet = -3315
		RETURN
	END

	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -3311
		RETURN
	END

	BEGIN TRAN

	SELECT @coin = nCoin FROM tGambleCoin WITH(UPDLOCK, HOLDLOCK) WHERE nCharNo = @nCharNo
	IF ( @coin + @nTotalDividend ) > 0 BEGIN
		SET @resultCoin = @coin + @nTotalDividend
	END
	ELSE BEGIN
		SET @resultCoin = 0
	END
	
	UPDATE tGambleCoin 
	   SET nCoin = @resultCoin
	     , sys_update_date = GETDATE()
	 WHERE nCharNo = @nCharNo
	IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
		SET @nRet = -3323
		ROLLBACK TRAN
		RETURN
	END

	INSERT INTO tGambleDiceResult (nGameNo, nCharNo, nBeforeMoney, nTotalDividend, nAfterMoney)
	VALUES (@nGameNo, @nCharNo, @coin, @nTotalDividend, @resultCoin)
	IF @@ERROR <> 0 BEGIN
		SET @nRet = - 3330
		ROLLBACK TRAN
		RETURN
	END
	ELSE BEGIN
		SET @nRet = 0
		COMMIT TRAN
	END

	SELECT nCharNo, nCoin FROM tGambleCoin WHERE nCharNo = @nCharNo
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_setMoneyToCoin]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_setMoneyToCoin
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.5.16
' Description :
'   유료아이템을 이용한 코인만 증가되는 경우
' Parameter :
'	input Parameter
'		@nGameNo : 게임번호
'		@nCoin   : 코인
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2414 : invaild @nGameNo
'				-2415 : invaild @nCharNo
'				-2421 : not enough Money
'				-2492 : db error(insert)
'				-2493 : db error(update)
'	return Record
'		nCustomCharNo, nMoney
' Modify 2011.7.11
'	동시성 문제 해결을 위해 테이블 힌트 추가 : WITH(UPDLOCK, HOLDLOCK)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_setMoneyToCoin]
	@nCharNo int
,	@nMoney  bigint
,	@nCoin   bigint
,	@nLimit  bigint
,	@dDate   datetime
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @status tinyint
	DECLARE @money bigint, @coin bigint, @exchangedCoin bigint
	
	SET @status = 1 -- 게임머니 -> 코인
	SET @nRet = 0
	
	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -3311
		RETURN
	END

	IF ISNULL(@nMoney, 0) <= 0 BEGIN
		SET @nRet = -3312
		RETURN
	END	
	
	IF ISNULL(@nCoin, 0) <= 0 BEGIN
		SET @nRet = -3313
		RETURN
	END	
	
	-- 일정시간동안 교환한 코인이 넘을 경우 교환할 수 없음
	SELECT @exchangedCoin = ISNULL(SUM(nExchangedCoin), 0) 
	  FROM tGambleCoinExchangeLog 
	 WHERE nCharNo = @nCharNo 
	   AND dDate  >= @dDate
	   AND nStatus = @status
	IF @nLimit < @exchangedCoin + @nCoin BEGIN
		SET @nRet = -3327
		RETURN	
	END

	BEGIN TRAN
		
	SELECT @money = nMoney FROM tCharacter WITH(UPDLOCK, HOLDLOCK) WHERE nCharNo = @nCharNo AND bDeleted = 0
	IF @money IS NULL BEGIN
		SET @nRet = -3324
		ROLLBACK TRAN
		RETURN
	END
	
	IF @money < @nMoney BEGIN
		SET @nRet = -3328
		ROLLBACK TRAN
		RETURN
	END
		
	UPDATE tCharacter
	   SET nMoney   = nMoney - @nMoney
	 WHERE nCharNo  = @nCharNo
	   AND bDeleted = 0
	   AND nMoney  >= @nMoney
	IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
		ROLLBACK TRAN
		SET @nRet = -3325
		RETURN		
	END
	
	SELECT @coin = nCoin FROM tGambleCoin WITH(UPDLOCK, HOLDLOCK) WHERE nCharNo = @nCharNo
	IF @coin IS NULL BEGIN
		INSERT INTO tGambleCoin (nCharNo, nCoin)
		VALUES (@nCharNo, @nCoin)
		IF @@ERROR <> 0 BEGIN
			ROLLBACK TRAN
			SET @nRet = -3329
			RETURN
		END	
		
		SET @coin = 0
	END
	ELSE BEGIN
		UPDATE tGambleCoin 
		   SET sys_update_date = GETDATE()
			 , nCoin = nCoin + @nCoin 
		 WHERE nCharNo = @nCharNo 
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			SET @nRet = -3323
			RETURN
		END
	END

	INSERT INTO tGambleCoinExchangeLog (nCharNo, nExchangedCoin, nStatus, nBeforeCoin, nAfterCoin, nBeforeMoney, nAfterMoney)
	VALUES (@nCharNo, @nCoin, @status, @coin, @coin + @nCoin, @money, @money - @nMoney)
	IF @@ERROR <> 0 BEGIN
		ROLLBACK TRAN
		SET @nRet = -3326
		RETURN
	END
	ELSE BEGIN
		COMMIT TRAN
		RETURN
	END
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_setSlotMachineResult]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Gamble_setSlotMachineResult] 
	@nCharNo      int
,	@nBattingUnit bigint
,	@nBatting     tinyint
,	@sGameResult  nvarchar(10)
,	@bIsWin       bit
,	@nPrizeCoin   bigint
,	@bIsJackpot   bit
,	@nJackpotRate int
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @gameName nvarchar(20), @charID nvarchar(80)
	DECLARE @gameNo bigint, @battingCoin bigint, @coin bigint, @resultCoin bigint, @jackpotCoin bigint

	SET @nRet = 0
	SET @battingCoin = 0
	SET @resultCoin = 0
	SET @gameName = 'SlotMachine'
	SET @battingCoin = @nBattingUnit * @nBatting 
		
	IF ISNULL(@nCharNo, 0) = 0 BEGIN
		SET @nRet = -3311
		RETURN
	END
	IF ISNULL(@nBattingUnit, 0) = 0 BEGIN
		SET @nRet = -3314
		RETURN		
	END
	IF ISNULL(@nBatting, 0) = 0 BEGIN
		SET @nRet = -3315
		RETURN		
	END	
	IF ISNULL(@sGameResult, '') = '' BEGIN
		SET @nRet = -3316
		RETURN		
	END
	
	SELECT @charID = sID FROM tCharacter WHERE nCharNo = @nCharNo AND bDeleted = 0
	IF @charID IS NOT NULL BEGIN
		BEGIN TRAN
		
		-- 1. 게임결과 기록
		INSERT INTO tGambleSlotMachineLog (nCharNo, nBattingUnit, nBatting, sGameResult, bIsWin, nPrizeCoin)
		VALUES (@nCharNo, @nBattingUnit, @nBatting, @sGameResult, @bIsWin, @nPrizeCoin)
		IF @@ERROR <> 0 OR @@IDENTITY = 0 BEGIN
			SET @nRet = -3333
			ROLLBACK TRAN
			RETURN
		END
		SET @gameNo = @@IDENTITY
		
		-- 2. 정산( 배팅코인(-) / 당첨코인(+)
		SELECT @coin = ISNULL(nCoin, 0) FROM tGambleCoin WITH(UPDLOCK, HOLDLOCK) WHERE nCharNo = @nCharNo
		IF (@coin - @battingCoin + @nPrizeCoin) > 0 BEGIN
			SET @resultCoin = @coin - @battingCoin + @nPrizeCoin
		END
		ELSE BEGIN
			SET @resultCoin = 0
		END
		
		UPDATE tGambleCoin 
		   SET nCoin = @resultCoin
			 , sys_update_date = GETDATE()
		 WHERE nCharNo = @nCharNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -3334
			ROLLBACK TRAN
			RETURN
		END		 
		
		-- 3. 잭팟일 경우
		IF @bIsJackpot = 1 BEGIN
			-- 3-1. 잭팟 누적코인 지급
			SELECT @jackpotCoin = ISNULL(nJackpotCoin, 0) 
			  FROM tGambleJackpot WITH(UPDLOCK, HOLDLOCK)
			 WHERE sGameName = @gameName
			   AND nBattingUnit = @nBattingUnit
			IF @jackpotCoin <> 0 BEGIN
				SET @jackpotCoin = @jackpotCoin * @nJackpotRate / 1000
			END
			
			UPDATE tGambleCoin 
			   SET nCoin = nCoin + @jackpotCoin 
			     , sys_update_date = GETDATE()
			 WHERE nCharNo = @nCharNo 
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
				SET @nRet = -3335
				ROLLBACK TRAN
				RETURN
			END
			
			-- 3-2. 잭팟 누적코인 초기화
			UPDATE tGambleJackpot 
			   SET nJackpotCoin = 0
			     , sys_update_date = GETDATE()
			 WHERE sGameName = @gameName 
			   AND nBattingUnit = @nBattingUnit 
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
				SET @nRet = -3336
				ROLLBACK TRAN
				RETURN
			END
			
			-- 3-3. 잭팟랭킹 등록
			INSERT INTO tGambleJackpotLog (sGameName, nBattingUnit, sID, nJackpotCoin, nGameNo)
			VALUES (@gameName, @nBattingUnit, @charID, @jackpotCoin, @gameNo)
			IF @@ERROR <> 0 BEGIN
				SET @nRet = -3337
				ROLLBACK TRAN
				RETURN
			END						   				  
		END
		-- 4. 잭팟이 아닌경우
		ELSE BEGIN
			-- 4-1. 배팅금액 누적
			IF EXISTS(SELECT sGameName FROM tGambleJackpot WHERE sGameName = @gameName AND nBattingUnit = @nBattingUnit) BEGIN
				UPDATE tGambleJackpot 
				   SET nJackpotCoin = nJackpotCoin + @battingCoin 
					 , sys_update_date = GETDATE()
				 WHERE sGameName = @gameName
				   AND nBattingUnit = @nBattingUnit 
				IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
					SET @nRet = -3338
					ROLLBACK TRAN
					RETURN
				END	
			END
			ELSE BEGIN
				INSERT INTO tGambleJackpot (sGameName, nBattingUnit, nJackpotCoin)
				VALUES (@gameName, @nBattingUnit, @battingCoin)
				IF @@ERROR <> 0 BEGIN
					SET @nRet = -3338
					ROLLBACK TRAN
					RETURN
				END				
			END		   
		END
		
		COMMIT TRAN
	END
	ELSE BEGIN
		SET @nRet = -3339
	END
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_subCoin]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_subCoin
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.9.9
' Description :
'   코인으로 아이템을 구매하는 경우
' Parameter :
'	input Parameter
'		@nGameNo : 게임번호
'		@nCoin   : 코인
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3311 : 캐릭터번호가 입력되지 않았습니다.
'				-3313 : 코인이 입력되지 않았습니다.
'				-3322 : 코인이 부족합니다.
'				-3323 : 코인 증가/차감에 실패하였습니다.(update)
'				-3326 : 코인 교환 로그 기록에 실패하였습니다.(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_subCoin]
	@nCharNo  int
,	@nCoin    bigint
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @status tinyint, @coin bigint

	SET @status = 3 -- 코인	-> 아이템
	SET @nRet = 0

	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -3311
		RETURN
	END

	IF ISNULL(@nCoin, 0) < 0 BEGIN
		SET @nRet = -3313
		RETURN
	END
	
	-- 구매할 코인이 0 값이 들어올 경우 오류는 아닌것으로 판단.
	IF @nCoin = 0 BEGIN
		SET @nRet = 0
		RETURN
	END	
		
	SELECT @coin = ISNULL(nCoin, 0) FROM tGambleCoin WITH(UPDLOCK, HOLDLOCK) WHERE nCharNo = @nCharNo
	IF @coin < @nCoin BEGIN
		SET @nRet = -3322
		RETURN		
	END
	ELSE BEGIN
		BEGIN TRAN
		
		UPDATE tGambleCoin 
		   SET sys_update_date = GETDATE()
			 , nCoin = nCoin - @nCoin 
		 WHERE nCharNo = @nCharNo 
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			SET @nRet = -3323
			RETURN
		END

		INSERT INTO tGambleCoinExchangeLog (nCharNo, nExchangedCoin, nStatus, nBeforeCoin, nAfterCoin)
		VALUES (@nCharNo, @nCoin, @status, @coin, @coin - @nCoin)
		IF @@ERROR <> 0 BEGIN
			ROLLBACK TRAN
			SET @nRet = -3326
			RETURN
		END
		ELSE BEGIN
			COMMIT TRAN
			RETURN
		END
	END		
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Guestbook_delArticle]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Guestbook_delArticle
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2008.6.25
' Description :
'   방명록에 글 삭제
' Parameter :
'	input Parameter
'		@nArticleNo : 게시물번호
'		@nCharNo    : 삭제자 캐릭터번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1623 : invaild delete user
'				-1694 : db error(delete)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Guestbook_delArticle]
	@nArticleNo int
,	@nCharNo    int
,	@nRet       int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF EXISTS(SELECT nArticleNo FROM tMinihouseGuest 
			   WHERE nArticleNo = @nArticleNo AND (nCharNo = @nCharNo OR nWriterNo = @nCharNo)) BEGIN
		DELETE FROM tMinihouseGuest WHERE nArticleNo = @nArticleNo
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1694
		END
	END
	ELSE BEGIN
		SET @nRet = -1623
	END

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Guestbook_getArticleCount]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Guestbook_getArticleCount
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2008.6.25
' Description :
'   방명록에 총 글수
' Parameter :
'	input Parameter
'		@nCharNo    : 방명록 주인 캐릭터번호
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Guestbook_getArticleCount]
	@nCharNo int
AS
BEGIN
	SET NOCOUNT ON

	SELECT COUNT(nArticleNo) AS nCount FROM tMinihouseGuest WHERE nCharNo = @nCharNo

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Guestbook_getArticleList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Guestbook_getArticleList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2008.6.25
' Description :
'   방명록에 글 삭제
' Parameter :
'	input Parameter
'		@nCharNo    : 방명록 주인 캐릭터번호
'		@nPageNo    : 페이지번호
'		@nPageLimit : 페이지당 리스트 수
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Guestbook_getArticleList]
	@nCharNo int
,	@nPageNo int = 1
,	@nPageLimit int = 5
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @STMT	AS NVARCHAR(1000)
	DECLARE @PARAMS	AS NVARCHAR(1000)
	DECLARE @TOP	AS INT
	DECLARE @START	AS INT

	SET @TOP = ((@nPageNo - 1) * @nPageLimit) + 1
	SET @STMT = ''
	SET @STMT = @STMT + 'SELECT @MIN = ISNULL(MIN(nArticleNo), 0) '
	SET @STMT = @STMT + '  FROM (SELECT TOP ' + CAST(@TOP AS NVARCHAR) + ' nArticleNo '
	SET @STMT = @STMT + '		  FROM tMinihouseGuest WITH(NOLOCK) '
	SET @STMT = @STMT + ' 		 WHERE nCharNo = ' +  CAST(@nCharNo AS NVARCHAR)
	SET @STMT = @STMT + '		 ORDER BY nArticleNo DESC) AS A'
	SET @PARAMS = N'@TOP AS INT, @nCharNo AS INT, @MIN AS INT OUTPUT'
	EXEC sp_executesql @STMT, @PARAMS, @TOP, @nCharNo, @MIN = @START OUTPUT

	SET @STMT = ''
	SET @STMT = 'SELECT TOP ' + CAST(@nPageLimit AS NVARCHAR) + ' * FROM tMinihouseGuest WITH(NOLOCK) '
	SET @STMT = @STMT + ' WHERE nCharNo = ' +  CAST(@nCharNo AS NVARCHAR) + ' AND nArticleNo <= ' + CAST(@START AS NVARCHAR(10))
	SET @PARAMS = N'@nPageLimit AS INT, @nCharNo AS INT, @START AS INT'
	EXEC sp_executesql @STMT, @PARAMS, @nPageLimit, @nCharNo, @START

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Guestbook_setArticle]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Guestbook_setArticle
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2008.6.25
' Description :
'   방명록에 글 작성
' Parameter :
'	input Parameter
'		@nCharNo   : 방명록 주인 캐릭터번호
'		@nWriterNo : 작성자 캐릭터번호
'		@sContent  : 내용
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1692 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Guestbook_setArticle]
	@nCharNo   int
,	@nWriterNo int
,	@sContent  nvarchar(1000)
,	@nRet      int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @sWriterID AS nvarchar(40)

	SET @nRet = 0
	SET @sWriterID = ''

	IF EXISTS(SELECT nCharNo FROM tCharacter WHERE nCharNo = @nWriterNo) BEGIN
		SELECT @sWriterID = sID FROM tCharacter WHERE nCharNo = @nWriterNo
	END
	ELSE BEGIN
		SET @nRet = -1624
		RETURN
	END

	INSERT INTO tMinihouseGuest (nCharNo, nWriterNo, sWriterID, sContent)
	VALUES (@nCharNo, @nWriterNo, @sWriterID, @sContent)
	IF @@ERROR <> 0 OR @@IDENTITY = 0 BEGIN
		SET @nRet = -1692
	END

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Guild_GetToken]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE  PROCEDURE [dbo].[usp_Guild_GetToken]
/*
Get Guild Token
2008.12 By Lunar
Input:
	See SQL
	
Output:
	nRet = 0,  Error(Not found guild)
	nRet = 1,  OK
*/
@nNo int,
@nToken bigint output,
@nRet int output
AS
SET NOCOUNT ON
SET @nRet = 0
SELECT @nToken = ISNULL(MAX(nToken), 0) FROM tGuildToken WHERE nNo = @nNo
IF @@ERROR <> 0 --OR @@ROWCOUNT <> 1
BEGIN
	SET @nRet = -1
	RETURN
END
-- end




GO
/****** Object:  StoredProcedure [dbo].[usp_Guild_GetTokenAll]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO





/*--------------------------------------------------------------------------------------
' 이  름 : usp_Guild_GetTokenAll
' 작성자 : 
' 작성일 : 
' Description :
'  길드  토너먼트 보상 토큰 데이터
' Parameter :
'	input Parameter
'		
'		
'	output Parameter
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Guild_GetTokenAll]
AS
BEGIN
	SET NOCOUNT ON
	SELECT nNo, nToken FROM tGuildToken
	SET NOCOUNT OFF
END




GO
/****** Object:  StoredProcedure [dbo].[usp_Guild_SetToken]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE  PROCEDURE [dbo].[usp_Guild_SetToken]
/*
Set Guild Token
2008.12 By Lunar
Input:
	See SQL
	
Output:
	nRet = -1,  Error(Not found guild)
	nRet = -2,  Error(Not found guild)
	nRet = 1,  OK
*/
@nNo int,
@nToken bigint,
@nRet int output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF EXISTS(SELECT nNo FROM tGuildToken WHERE nNo = @nNo) BEGIN
		UPDATE tGuildToken SET nToken = @nToken WHERE nNo = @nNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1
			RETURN
		END
	END
	ELSE BEGIN
		INSERT INTO tGuildToken (nNo, nToken)
		VALUES (@nNo, @nToken)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -2
			RETURN
		END
	END			
	
	SET NOCOUNT OFF
END
-- end





GO
/****** Object:  StoredProcedure [dbo].[usp_Guild_TokenSub]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[usp_Guild_TokenSub]
/*
Sub money
2008.12 By Lunar
 input:
	see SQL
 output:
	@nRet	1 = OK
	        0 = Error, or Minus value
*/
@nGuildNo int,
@nSubToken bigint,
@nRet int OUTPUT
AS
SET NOCOUNT ON
DECLARE @nGuildToken bigint
EXEC usp_Guild_GetToken @nGuildNo, @nGuildToken OUTPUT, @nRet OUTPUT
IF @nRet <> 0
	RETURN
-- Sub money
SET @nGuildToken = @nGuildToken - @nSubToken
-- Check minus
IF @nGuildToken < 0
BEGIN
	SET @nRet = -1
	RETURN	
END
EXEC usp_Guild_SetToken @nGuildNo, @nGuildToken, @nRet OUTPUT
-- End




GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_delMember]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_delMember
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.4.28
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì Å»Åð
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invalid nNo
'				-1512 : invalid nCharNo
'				-1592 : db error(delete)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_delMember]
	@nNo     int
,	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nCharNo <= 0 BEGIN
		SET @nRet = -1512
		RETURN
	END

	DELETE FROM tGuildAcademyMember WHERE nNo = @nNo AND nCharNo = @nCharNo
	IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
		SET @nRet = -1592
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_delReward]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_delReward
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.8.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì º¸»ó ÃÊ±âÈ­(·¹ÄÚµå ´ÜÀ§)
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nLevel  : ·¹º§
'		@nTypeID : º¸»óÅ¸ÀÔ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1596 : db error(delete)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_delReward]
	@nNo     int
,	@nLevel  smallint
,	@nTypeID tinyint
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nTypeID = 1 BEGIN
		DELETE FROM tGuildAcademyReward WHERE nNo = @nNo AND nLevel = @nLevel AND nTypeID = @nTypeID
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1596
			RETURN
		END
	END
	ELSE BEGIN
		DELETE FROM tGuildAcademyReward WHERE nNo = @nNo AND nLevel = @nLevel AND nTypeID > 1
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1596
			RETURN
		END
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_delRewardAll]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_delRewardAll
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.8.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì º¸»ó ÃÊ±âÈ­(ÀüÃ¼)
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nTypeID : º¸»óÅ¸ÀÔ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1595 : db error(delete)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_delRewardAll]
	@nNo     int
,	@nTypeID tinyint
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nTypeID = 1 BEGIN
		DELETE FROM tGuildAcademyReward WHERE nNo = @nNo AND nTypeID = @nTypeID
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1596
			RETURN
		END
	END
	ELSE BEGIN
		DELETE FROM tGuildAcademyReward WHERE nNo = @nNo AND nTypeID > 1
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1596
			RETURN
		END
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_depositMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_depositMoney
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.14.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì¿¡ ÀÔ±ÝÇÏ±â
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'		@nMoney  : ±Ý¾×
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1512 : invaild nCharNo
'				-1514 : invaild nMoney
'				-1524 : not enough money
'				-1525 : invaild nNo
'				-1526 : invaild nCharNo
'				-1593 : db error
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_depositMoney]
	@nNo     int
,	@nCharNo int
,	@nMoney  bigint
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @money AS bigint
	SET @nRet = 0
	SET @money = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nCharNo <= 0 BEGIN
		SET @nRet = -1512
		RETURN
	END

	IF @nMoney <= 0 BEGIN
		SET @nRet = -1514
		RETURN
	END

	BEGIN TRAN
	
	IF NOT EXISTS(SELECT nCharNo FROM tCharacter WHERE nCharNo = @nCharNo AND bDeleted = 0) BEGIN
		SET @nRet = -1524
		ROLLBACK TRAN
		RETURN
	END

	IF NOT EXISTS(SELECT nNo FROM tGuildAcademy WHERE nNo = @nNo) BEGIN
		SET @nRet = -1525
		ROLLBACK TRAN
		RETURN
	END

	SELECT @money = nMoney FROM tCharacter WHERE nCharNo = @nCharNo
	IF @money < @nMoney BEGIN		
		SET @nRet = -1526
		ROLLBACK TRAN
		RETURN
	END
	ELSE BEGIN
		UPDATE tCharacter SET nMoney = nMoney - @nMoney WHERE nCharNo = @nCharNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1593
			ROLLBACK TRAN
			RETURN
		END

		UPDATE tGuildAcademy SET nMoney = nMoney + @nMoney WHERE nNo = @nNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1593
			ROLLBACK TRAN
			RETURN
		END
		ELSE BEGIN
			SET @nRet = 0
			COMMIT TRAN
		END
	END
			
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getGraduateCount]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getGraduateCount
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.16.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì Á¹¾÷»ý ÃÑ¼ö
' Parameter :
'	input Parameter
'		@nNo 	        : ±æµå¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1591 : db error(select)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getGraduateCount]
	@nNo int
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	SELECT ISNULL(COUNT(nCharNo), 0) AS cnt
	  FROM tGuildAcademyGraduate
	 WHERE nNo = @nNo
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -1591
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getGraduateList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getGraduateList
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.16.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì Á¹¾÷»ý ¸®½ºÆ®
' Parameter :
'	input Parameter
'		@nNo 	        : ±æµå¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1591 : db error(select)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getGraduateList]
	@nNo int
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	SELECT sID, nClass, nJoinLevel, dJoinDate, nGraduateLevel, dGraduateDate
	  FROM tGuildAcademyGraduate
	 WHERE nNo = @nNo
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -1591
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getGuildAcademyList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getGuildAcademyList
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.4.29
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì ¸®½ºÆ®
' Parameter :
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1594 : db error(select)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getGuildAcademyList]
	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	SELECT GA.nNo, nMasterCharNo, C1.sID, nMaxMember, GA.nMoney, nPoint
		 , T2.nRanking, ISNULL(dBuffEndTime, GETDATE()), ISNULL(dNotifyDate, GETDATE()), ISNULL(sNotify, ''), ISNULL(sIntro, '')
		 , T1.nCharNo, T1.sID, ISNULL(T3.nGraduateCount, 0) AS nGraduateCount
	  FROM tGuildAcademy AS GA INNER JOIN 
		   tCharacter AS C1 ON GA.nMasterCharNo = C1.nCharNo INNER JOIN (
				SELECT G.nNo, GM.nCharNo, C.sID
				  FROM tGuild AS G, tGuildMember AS GM, tCharacter AS C
				 WHERE G.nNo = GM.nNo AND GM.nCharNo = C.nCharNo
				   AND GM.nGrade = 0 AND C.bDeleted = 0
				) AS T1 ON GA.nNo = T1.nNo INNER JOIN (
				-- ranking check
				SELECT T1.nNo, COUNT(T2.nPoint) + 1 AS nRanking
				  FROM tGuildAcademy T1 LEFT OUTER JOIN tGuildAcademy T2 ON T1.nPoint < T2.nPoint
				 GROUP BY T1.nNo, T1.nPoint
				) AS T2 ON GA.nNo = T2.nNo LEFT OUTER JOIN (
				-- graduate counting
				SELECT nNo, ISNULL(COUNT(nCharNo), 0) AS nGraduateCount
				  FROM tGuildAcademyGraduate
				 GROUP BY nNo
				) AS T3 ON GA.nNo = T3.nNo
	 ORDER BY T2.nRanking
	IF @@ERROR <> 0
		BEGIN
			SET @nRet = -1594
		END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getGuildNo]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getGuildNo
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.4.28
' Description :
'   ÀÔ·Â¹ÞÀº charNo°¡ ¼Ò¼ÓµÈ ±æµå¹øÈ£
' Parameter :
'	input Parameter
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1512 : invalid nCharNo
'				-1521 : not found nNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getGuildNo]
	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nCharNo <= 0 BEGIN
		SET @nRet = -1512
		RETURN
	END

	IF EXISTS(SELECT nNo FROM tGuildAcademyMember WHERE nCharNo = @nCharNo) BEGIN
		SELECT nNo FROM tGuildAcademyMember WHERE nCharNo = @nCharNo
	END
	ELSE BEGIN
		-- 2008.8.28 ÀÏ ¾÷µ¥ÀÌÆ®½Ã ¼­¹öÇÁ·Î±×·¥ÀÇ ¼öÁ¤ ½Ç¼ö·Î ÀÎÇØ
		-- Á¤»óÀûÀÎ Ã³¸®°¡ µÇÁö ¾Ê´Â Çö»óÀÌ ¹ß»ýÇÔ.
		-- 2008.9.4 ÀÏ ¾÷µ¥ÀÌÆ®½Ã Á¤»óÀûÀ¸·Î ÁøÇàÇÒ ¿¹Á¤ÀÓ.
		-- SET @nRet = -1521
		SET @nRet = 0
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getMemberList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getMemberList
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.4.28
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì¿¡ ¼Ò¼ÓµÈ ¸É¹ö¸®½ºÆ®
' Parameter :
'	input Parameter
'		@nNo 	: ±æµå¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invalid nNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getMemberList]
	@nNo  int
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	SELECT G.nCharNo, G.nJoinLevel, G.dJoinDate, G.nGrade, G.sIntro
		 , C.sID, C.nLevel, C.sLoginZone, C.dLastLoginDate
		 , S.nClass
	  FROM tGuildAcademyMember AS G INNER JOIN 
		   tCharacter          AS C ON C.nCharNo =  G.nCharNo INNER JOIN 
		   tCharacterShape     AS S ON S.nCharNo =  G.nCharNo
	 WHERE G.nNo = @nNo AND C.bDeleted = 0

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getMoney
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.27
' Description :
'   ÀÔ·Â¹ÞÀº ¾ÆÄ«µ¥¹ÌÀÇ Money¸¦ °¡Á®¿È.
' Parameter :
'	input Parameter
'		@nNo     : ±æµå¾ÆÄ«µ¥¹Ì¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1521 : not found nNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getMoney]
	@nNo  int
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF EXISTS(SELECT nNo FROM tGuildAcademy WHERE nNo = @nNo) BEGIN
		SELECT nMoney FROM tGuildAcademy WHERE nNo = @nNo
	END
	ELSE BEGIN
		SET @nRet = -1521
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getRanking]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getRanking
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.26.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì ·©Å·À» °¡Á®¿À´Â ÀúÀåÇÁ·Î½ÃÀú
' Parameter :
'	input Parameter
'		@nCount   : »êÁ¤±âÁØÀÏ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1591 : db error(select)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getRanking]
	@nCount int = 1
,	@nRet   int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	DECLARE @MAX_COUNT AS INT

	SELECT @MAX_COUNT = ISNULL(MAX(nCount), 0)
	  FROM tGuildAcademyRanking
	
	SELECT dDate, nRanking, nGuildNo, sGuildName, nMasterNo, sMasterID, nPoint
	  FROM tGuildAcademyRanking
	 WHERE nCount = @MAX_COUNT - (@nCount - 1)

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getReward]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getReward
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.8.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì º¸»ó ¸®½ºÆ®(·¹º§º°)
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nLevel  : ·¹º§
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1513 : invaild nLevel
'				-1591 : db error
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getReward]
	@nNo    int
,	@nLevel tinyint
,	@nRet   int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nLevel <= 0 BEGIN
		SET @nRet = -1513
		RETURN
	END

	SELECT nTypeID, nValue, nItemID FROM tGuildAcademyReward WHERE nNo = @nNo AND nLevel = @nLevel
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -1591
	END	

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getRewardAll]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_getRewardAll
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.8.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì º¸»ó ¸®½ºÆ®(ÀüÃ¼)
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nTypeID : º¸»óÅ¸ÀÔ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1591 : db error
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_getRewardAll]
	@nNo     int
,	@nTypeID int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nTypeID = 1 BEGIN
		SELECT nLevel, nTypeID, nValue, nItemID FROM tGuildAcademyReward WHERE nNo = @nNo AND nTypeID = 1
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1591
		END	
	END 
	ELSE BEGIN
		SELECT nLevel, nTypeID, nValue, nItemID FROM tGuildAcademyReward WHERE nNo = @nNo AND nTypeID > 1
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1591
		END	
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_raiseMember]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_raiseMember
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.7.7
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì ÀÎ¿øÀÌ ±æµå ÀÎ¿øÀ¸·Î ½Â°ÝÇÒ¶§ »ç¿ë.
' Parameter :
'	input Parameter
'		@nNo     : ±æµå¹øÈ£
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'		@nGrade  : µî±Þ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1528 : ±æµå¾ÆÄ«µ¥¹Ì ¸â¹ö°¡ ¾Æ´Ô
'				-1530 : ±æµå °¡ÀÔÀÎ¿øÀ» ÃÊ°úÇÔ.
'				-1531 : db error(±æµå °¡ÀÔ)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_raiseMember]
	@nNo     int
,	@nCharNo int
,	@nGrade  tinyint
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @nMax AS INT
	DECLARE @nCnt AS INT

	SET @nRet = 0

	IF EXISTS(SELECT nNo FROM tGuildAcademyMember WHERE nNo = @nNo AND nCharNo = @nCharNo) BEGIN
		SELECT @nMax = nMaxMembers, @nCnt = nCnt
		  FROM tGuild AS T1 INNER JOIN (
				SELECT nNo, ISNULL(COUNT(*), 0) AS nCnt
				  FROM tCharacter AS C, tGuildMember AS G
				 WHERE C.nCharNo = G.nCharNo 
				   AND G.nNo = @nNo
				   AND C.bDeleted = 0
				 GROUP BY nNo) AS T2 ON (T1.nNo = T2.nNo)
		IF @nCnt < @nMax BEGIN
			BEGIN TRAN
	
			-- ±æµå¾ÆÄ«µ¥¹Ì Å»Åð(0:ok)
			EXEC usp_GuildAcademy_delMember @nNo, @nCharNo, @nRet output
			IF @nRet <> 0 BEGIN
				ROLLBACK TRAN
				RETURN
			END
	
			-- ±æµå °¡ÀÔ(1:ok)
			EXEC p_Guild_Member_Join @nNo, @nCharNo, @nGrade, @nRet output
			IF @nRet <> 1 BEGIN
				SET @nRet = -1531
				ROLLBACK TRAN
				RETURN
			END
			ELSE BEGIN
				SET @nRet = 0
				COMMIT TRAN
			END
		END
		ELSE BEGIN
			SET @nRet = -1530
		END
	END
	ELSE BEGIN
		SET @nRet = -1528
	END
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setAcademy]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setAcademy
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.30
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì »ý¼º
'	±æµå¸¦ »ý¼ºÇÏ´Â ÀúÀåÇÁ·Î½ÃÀú¿¡¼­ È£ÃâµÇ´Â ÀúÀåÇÁ·Î½ÃÀúÀÓ.
' Parameter :
'	input Parameter
'		@nNo           : ±æµå¹øÈ£
'		@nMasterCharNo : ±æµå¾ÆÄ«µ¥¹Ì ¸¶½ºÅÍ¹øÈ£
'		@nMaxMember    : °¡ÀÔÁ¦ÇÑÀÎ¿ø
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1521 : already join nNo
'				-1592 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setAcademy]
	@nNo           int
,	@nMasterCharNo int
,	@nMaxMember    smallint
,	@nRet          int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF EXISTS(SELECT nNo FROM tGuildAcademy WHERE nNo = @nNo) BEGIN
		SET @nRet = -1521
		RETURN
	END
	ELSE BEGIN
		INSERT INTO tGuildAcademy (nNo, nMasterCharNo, nMaxMember)
		VALUES (@nNo, @nMasterCharNo, @nMaxMember)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1592
			RETURN
		END
	END
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setGraduate]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setGraduate
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.16.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì Á¹¾÷»ý µî·Ï
' Parameter :
'	input Parameter
'		@nNo 	        : ±æµå¹øÈ£
'		@nCharNo        : Ä³¸¯ÅÍ¹øÈ£
'		@sID            : Ä³¸¯ÅÍ¸í
'		@nClass         : Å¬·¡½º¹øÈ£(DEFINE µÇ¾îÀÖÀ½)
'		@nGraduateLevel : Á¹¾÷·¹º§
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1512 : invaild nCharNo
'				-1515 : invaild sID
'				-1516 : invaild nClass
'				-1517 : invaild nGraduateLevel
'				-1528 : ±æµå¾ÆÄ«µ¥¹Ì °¡ÀÔÁ¤º¸°¡ ¾ø½À´Ï´Ù.
'				-1592 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setGraduate]
	@nNo            int
,	@nCharNo        int
,	@sID            nvarchar(40)
,	@nClass         tinyint
,	@nGraduateLevel smallint
,	@nRet           int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @nJoinLevel smallint
	DECLARE @dJoinDate  datetime

	SET @nRet = 0
	SET @nJoinLevel = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nCharNo <= 0 BEGIN
		SET @nRet = -1512
		RETURN
	END

	IF LEN(ISNULL(@sID, '')) = 0 BEGIN
		SET @nRet = -1515
		RETURN
	END

	IF @nClass <= 0 BEGIN
		SET @nRet = -1516
		RETURN
	END

	IF @nGraduateLevel <= 0 BEGIN
		SET @nRet = -1517
		RETURN
	END

	SELECT @nJoinLevel = nJoinLevel, @dJoinDate = dJoinDate
	  FROM tGuildAcademyMember
	 WHERE nNo = @nNo AND nCharNo = @nCharNo
	IF @nJoinLevel <= 0 BEGIN
		SET @nRet = -1528
		RETURN
	END
	ELSE BEGIN
		INSERT INTO tGuildAcademyGraduate (nNo, nCharNo, sID, nClass, nJoinLevel, dJoinDate, nGraduateLevel)
		VALUES (@nNo, @nCharNo, @sID, @nClass, @nJoinLevel, @dJoinDate, @nGraduateLevel)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1592
			RETURN
		END
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setMaster]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setMaster
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.6.5
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì °¡ÀÔ
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invalid nNo or invalid nCharNo
'				-1593 : db error(update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setMaster]
	@nNo       int
,	@nCharNo   int
,	@nRet      int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	
	IF EXISTS(SELECT nNo FROM tGuildMember WHERE nNo = @nNo AND nCharNo = @nCharNo) BEGIN
		UPDATE tGuildAcademy SET nMasterCharNo = @nCharNo
		 WHERE nNo = @nNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1593
			RETURN
		END
	END 
	ELSE BEGIN
		SET @nRet = -1511
		RETURN
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setMember]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setMember
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.4.28
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì °¡ÀÔ
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'		@nGrade	 : µî±Þ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invalid nNo
'				-1512 : invalid nCharNo
'				-1522 : ±æµå¿¡ ¼Ò¼ÓµÈ Ä³¸¯ÅÍ
'				-1523 : Ä³¸¯ÅÍ ·¹º§ ¿À·ù
'				-1524 : Ä³¸¯ÅÍ°¡ Á¸ÀçÇÏÁö ¾Ê°Å³ª »èÁ¦µÈ Ä³¸¯ÅÍ
'				-1591 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setMember]
	@nNo       int
,	@nCharNo   int
,	@dJoinDate datetime
,	@nGrade    tinyint = 0
,	@nRet      int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	DECLARE @nLevel AS int

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nCharNo <= 0 BEGIN
		SET @nRet = -1512
		RETURN
	END

	IF EXISTS(SELECT nNo FROM tGuildMember WHERE nNo = @nNo AND nCharNo = @nCharNo) BEGIN
		SET @nRet = -1522
		RETURN
	END

	IF EXISTS(SELECT nCharNo FROM tCharacter WHERE nCharNo = @nCharNo AND bDeleted = 0) BEGIN
		SELECT @nLevel = nLevel FROM tCharacter WHERE nCharNo = @nCharNo
		IF @nLevel <= 0 BEGIN
			SET @nRet = -1523
			RETURN
		END
	END
	ELSE BEGIN
		SET @nRet = -1524
		RETURN
	END
	
	INSERT tGuildAcademyMember (nNo, nCharNo, nJoinLevel, dJoinDate, nGrade)
	VALUES ( @nNo, @nCharNo, @nLevel, @dJoinDate, @nGrade )
	IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
		SET @nRet = -1591
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setMemberLevelup]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setMemberLevelup
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.27
' Description :
'   ÀÔ·Â¹ÞÀº charNo°¡ ¼Ò¼ÓµÈ ±æµå¹øÈ£
' Parameter :
'	input Parameter
'		@nNo       : ¾ÆÄ«µ¥¹Ì¹øÈ£
'		@nPoint    : Áõ°¡µÉ Æ÷ÀÎÆ®
'		@nBuffTime : Áõ°¡µÉ ¹öÇÁ½Ã°£(ºÐ´ÜÀ§)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1521 : not found nNo
'				-1593 : db_error (update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setMemberLevelup]
	@nNo       int
,	@nPoint    int
,	@nBuffTime int
,	@nRet      int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	DECLARE @BUFF_END_TIME AS DATETIME

	IF EXISTS(SELECT nNo FROM tGuildAcademy WHERE nNo = @nNo) BEGIN
		BEGIN TRAN

		SELECT @BUFF_END_TIME = ISNULL(dBuffEndTime, GETDATE()) FROM tGuildAcademy WHERE nNo = @nNo
		IF @BUFF_END_TIME > GETDATE() BEGIN
			SET @BUFF_END_TIME = DATEADD ( minute, @nBuffTime, @BUFF_END_TIME ) 
		END
		ELSE BEGIN
			SET @BUFF_END_TIME = DATEADD ( minute, @nBuffTime, GETDATE() ) 
		END

		UPDATE tGuildAcademy SET nPoint = nPoint + @nPoint, dBuffEndTime = @BUFF_END_TIME
		 WHERE nNo = @nNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1593
			ROLLBACK TRAN
			RETURN
		END
		ELSE BEGIN			
			COMMIT TRAN
		END
	END
	ELSE BEGIN
		SET @nRet = -1521
	END	

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setNotify]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setNotify
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.4.28
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì °øÁö»çÇ× ÀÛ¼º
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@sNotify : °øÁö»çÇ×
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invalid nNo
'				-1593 : db error(update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setNotify]
	@nNo     int
,	@sNotify nvarchar(1024)
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	UPDATE tGuildAcademy 
	   SET dNotifyDate = getdate(), sNotify = @sNotify 
	 WHERE nNo = @nNo
	IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
		SET @nRet = -1593
	END
	ELSE BEGIN
		SET @nRet = 0
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setRanking]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 2008.5.26 ÀÛ¾÷
/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setRanking
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.26.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì ·©Å·À» »êÁ¤ÇÏ´Â ÀúÀåÇÁ·Î½ÃÀú
'	¼­¹ö(World_ManagerÀÇ È£Ãâ¿¡ ÀÇÇØ µ¿ÀÛÇÑ´Ù.)
' Parameter :
'	input Parameter
'		@nDays   : »êÁ¤±âÁØÀÏ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1591 : db error(select)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setRanking]
	@nRet  int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	DECLARE @MAX_COUNT AS INT

	BEGIN TRAN

	SELECT @MAX_COUNT = ISNULL(MAX(nCount), 0) + 1
	  FROM tGuildAcademyRanking

	INSERT INTO tGuildAcademyRanking (dDate, nRanking, nGuildNo, sGuildName, nMasterNo, sMasterID, nPoint, nCount)
		SELECT GETDATE() AS dDate, R.nRanking, R.nNo, G.sName, GA.nMasterCharNo, C.sID, R.nPoint, @MAX_COUNT
		  FROM tGuild AS G, tGuildAcademy AS GA, tCharacter AS C, (
				SELECT COUNT(T2.nPoint) + 1 AS nRanking, T1.nNo, T1.nPoint
				  FROM tGuildAcademy T1 LEFT OUTER JOIN tGuildAcademy T2 ON T1.nPoint < T2.nPoint
				 GROUP BY T1.nNo, T1.nPoint) AS R
		 WHERE G.nNo = R.nNo AND C.nCharNo = GA.nMasterCharNo AND GA.nNo = R.nNo
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -1592
		ROLLBACK TRAN
		RETURN
	END

	UPDATE tGuildAcademy SET nPoint = 0
	IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
		SET @nRet = -1593
		ROLLBACK TRAN
		RETURN
	END
	ELSE BEGIN
		SET @nRet = 0
		COMMIT TRAN
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setReward]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_setReward
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.8.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì º¸»ó ¼³Á¤
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nLevel  : ·¹º§
'		@nTypeID : º¸»óÅ¸ÀÔ
'		@nValue  : º¸»ó°ª
'		@nItemID : ¾ÆÀÌÅÛ
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1513 : invaild nLevel
'				-1595 : db error(select)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_setReward]
	@nNo     int
,	@nLevel  smallint
,	@nTypeID tinyint
,	@nValue  bigint
,	@nItemID int = NULL
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nLevel <= 0 BEGIN
		SET @nRet = -1513
		RETURN
	END

	INSERT INTO tGuildAcademyReward (nNo, nLevel, nTypeID, nValue, nItemID)
	VALUES (@nNo, @nLevel, @nTypeID, @nValue, @nItemID)
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -1595
		RETURN
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_withdrawMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_GuildAcademy_withdrawMoney
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.5.14.
' Description :
'   ±æµå¾ÆÄ«µ¥¹Ì¿¡ ÀÔ±ÝÇÏ±â
' Parameter :
'	input Parameter
'		@nNo 	 : ±æµå¹øÈ£
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'		@nMoney  : ±Ý¾×
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1511 : invaild nNo
'				-1512 : invaild nCharNo
'				-1514 : invaild nMoney
'				-1524 : not enough money
'				-1525 : invaild nNo
'				-1526 : invaild nCharNo
'				-1593 : db error
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildAcademy_withdrawMoney]
	@nNo     int
,	@nCharNo int
,	@nMoney  bigint
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @money AS bigint
	SET @nRet = 0
	SET @money = 0

	IF @nNo <= 0 BEGIN
		SET @nRet = -1511
		RETURN
	END

	IF @nCharNo <= 0 BEGIN
		SET @nRet = -1512
		RETURN
	END

	IF @nMoney <= 0 BEGIN
		SET @nRet = -1514
		RETURN
	END

	BEGIN TRAN
	
	IF NOT EXISTS(SELECT nCharNo FROM tCharacter WHERE nCharNo = @nCharNo AND bDeleted = 0) BEGIN
		SET @nRet = -1524
		ROLLBACK TRAN
		RETURN
	END

	IF NOT EXISTS(SELECT nNo FROM tGuildAcademy WHERE nNo = @nNo) BEGIN
		SET @nRet = -1525
		ROLLBACK TRAN
		RETURN
	END

	SELECT @money = nMoney FROM tGuildAcademy WHERE nNo = @nNo
	IF @money < @nMoney BEGIN		
		SET @nRet = -1527
		ROLLBACK TRAN
		RETURN
	END
	ELSE BEGIN
		UPDATE tGuildAcademy SET nMoney = nMoney - @nMoney WHERE nNo = @nNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1593
			ROLLBACK TRAN
			RETURN
		END

		UPDATE tCharacter SET nMoney = nMoney + @nMoney WHERE nCharNo = @nCharNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1593
			ROLLBACK TRAN
			RETURN
		END
		ELSE BEGIN
			SET @nRet = 0
			COMMIT TRAN
		END
	END
			
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_GuildTournament_getChoiceList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildTournament_getChoiceList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.4.6
' Description :
'   길드토너먼트에 선정된 길드리스트
' Parameter :
'	input Parameter
'		@nGTNo : 길드토너먼트번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1411 : invaild nGTNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildTournament_getChoiceList]
	@nGTNo int
,	@nRet  int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	IF ISNULL(@nGTNo, 0) <= 0 BEGIN
		SET @nRet = -1411
		RETURN
	END

	SELECT nNo FROM tGuildTournamentRegist WITH(NOLOCK) WHERE nGTNo = @nGTNo AND bChoice = 1

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GuildTournament_getMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildTournament_getMoney
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.4.7
' Description :
'   길드토너먼트를 신청비용 총합계
' Parameter :
'	input Parameter
'		@nGTNo : 길드토너먼트번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1411 : invaild nGTNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildTournament_getMoney]
	@nGTNo int
,	@nRet  int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	IF ISNULL(@nGTNo, 0) <= 0 BEGIN
		SET @nRet = -1411
		RETURN
	END

	SELECT ISNULL(SUM(nMoney), 0) AS nTotalMoney 
	  FROM tGuildTournamentRegist WITH(NOLOCK) 
	 WHERE nGTNo = @nGTNo

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GuildTournament_getRegistList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildTournament_getRegistList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.4.6
' Description :
'   길드토너먼트를 신청한 길드리스트
' Parameter :
'	input Parameter
'		@nGTNo : 길드토너먼트번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1411 : invaild nGTNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildTournament_getRegistList]
	@nGTNo int
,	@nRet  int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	IF ISNULL(@nGTNo, 0) <= 0 BEGIN
		SET @nRet = -1411
		RETURN
	END

	SELECT nNo FROM tGuildTournamentRegist WITH(NOLOCK) WHERE nGTNo = @nGTNo

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GuildTournament_setChoice]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildTournament_setChoice
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.4.7
' Description :
'   길드토너먼트를 신청리스트중 조건에 맞는 길드를 선정하는 작업을 함.
' Parameter :
'	input Parameter
'		@nGTNo        : 길드토너먼트번호
'		@nLimitLevel  : 제한 레벨
'		@nLimitMember : 제한 인원
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1411 : invaild nGTNo
'				-1493 : db error(update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildTournament_setChoice]
	@nGTNo        int
,	@nLimitLevel  int = 1
,	@nLimitMember int = 1
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	IF ISNULL(@nGTNo, 0) <= 0 BEGIN
		SET @nRet = -1411
		RETURN
	END

	BEGIN TRAN

	UPDATE tGuildTournamentRegist
	   SET bChoice = 1
	 WHERE nGTNo = @nGTNo AND nNo IN (
			SELECT TOP 16 T4.nNo
			  FROM tGuildTournamentRegist AS T4 INNER JOIN (
					SELECT T1.nNo, ISNULL(COUNT(T2.nCharNo), 0) AS nMember
					  FROM tGuildMember AS T1, tCharacter AS T2 
					 WHERE T1.nCharNo = T2.nCharNo
					   AND T2.nLevel >= @nLimitLevel
					 GROUP BY T1.nNo
					HAVING COUNT(T2.nCharNo) >= @nLimitMember) AS T3 ON T4.nNo = T3.nNo
			 WHERE nGTNo = @nGTNo
			 ORDER BY nSeed DESC)
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -1493
		ROLLBACK TRAN
	END
	ELSE BEGIN
		SET @nRet = 0
		COMMIT TRAN
	END

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GuildTournament_setRegist]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*-------------------------------------------------------------------------------------
' 파일명 : KR_procedureGuildTournament.sql
' 내  용 : 길드토너먼트 관련 저장프로시저
' 작성자 : 고동균(kodong@gmail.com)
' 작성일 : 2009.4.6
' Description :
'	길드토너먼트 신청과 관련하여 추가 개발에 대한 프로시저임.
'	원래의 프로시저는 별도로 요청하여야 함.
'------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildTournament_setRegist
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.4.6
' Description :
'   길드토너먼트를 신청하는 저장프로시저
'	각종 제한사항이 체크되어야 함.
' Parameter :
'	input Parameter
'		@nGTNo        : 길드토너먼트번호
'		@nNo          : 길드번호
'		@nCharNo      : 캐릭터번호
'		@nMoney       : 신청비
'		@nLimitLevel  : 제한 레벨
'		@nLimitMember : 제한 인원
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1411 : invaild nGTNo
'				-1412 : invaild nNo
'				-1413 : invaild nCharNo
'				-1421 : already regist nNo
'				-1422 : check level, member
'				-1423 : not enough money
'				-1492 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildTournament_setRegist]
	@nGTNo        int
,	@nNo          int
,	@nCharNo      int
,	@nMoney       bigint = 0
,	@nLimitLevel  int = 1
,	@nLimitMember int = 1
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @cnt AS int

	SET @nRet = 0
	SET @cnt  = 0

	IF ISNULL(@nGTNo, 0) <= 0 BEGIN
		SET @nRet = -1411
		RETURN
	END

	IF ISNULL(@nNo, 0) <= 0 BEGIN
		SET @nRet = -1412
		RETURN
	END

	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -1413
		RETURN
	END

	IF EXISTS(SELECT nGTNo FROM tGuildTournamentRegist WHERE nGTNo = @nGTNo AND nNo = @nNo) BEGIN
		SET @nRet = -1421
		RETURN
	END

	-- 길드토너먼트 신청제한 체크(레벨, 인원)
	SELECT @cnt = ISNULL(COUNT(T1.nCharNo), 0)
	  FROM tGuildMember AS T1, tCharacter AS T2 
	 WHERE T1.nCharNo = T2.nCharNo
	   AND T1.nNo = @nNo
	   AND T2.nLevel >= @nLimitLevel
	IF @cnt < @nLimitMember BEGIN
		SET @nRet = -1422
		RETURN
	END

	BEGIN TRAN

	-- 길드토너먼트 신청비 차감
	EXEC p_Char_MoneySub @nCharNo, @nMoney, @nRet OUTPUT
	IF @nRet <> 1 BEGIN
		SET @nRet = -1423
		ROLLBACK TRAN
		RETURN
	END

	INSERT INTO tGuildTournamentRegist (nGTNo, nNo, nMoney, nSeed)
	VALUES (@nGTNo, @nNo, @nMoney, CAST(RAND() * 100000 AS INT))
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -1492
		ROLLBACK TRAN
	END
	ELSE BEGIN
		SET @nRet = 0
		COMMIT TRAN
	END

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_HolyPromise_getRemainMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE  PROCEDURE [dbo].[usp_HolyPromise_getRemainMoney]
/*
get cen reward
2009.4 By Luunar
 input:
	see SQL
 output:
	= 0 No Error
	< 0 Error
*/

@nCharNo		int,
@nRet			int OUTPUT

AS
BEGIN

	SET NOCOUNT ON

	DECLARE @nRemainMoney bigint
	SET @nRemainMoney = 0

	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -2211
		RETURN
	END

	-- ÀÜ¾×ÀÌ ¾ó¸¶³ª ÀÖ´ÂÁö °¡Á®¿Â´Ù.
	SELECT TOP 1 @nRemainMoney = nRemainMoney 
	  FROM tHolyPromiseReward 
	 WHERE nCharNo = @nCharNo 
	 ORDER BY nSeqNo DESC

	-- ¸¸¾à ÀÜ¾×ÀÌ 0¿ø º¸´Ù ¸¹´Ù¸é
	IF @nRemainMoney > 0
	BEGIN
		-- ÀÜ¾×À» ¹Ù·Î Ãâ·ÂÇØÁÖ°í ³¡
		SELECT @nRemainMoney
		SET @nRet = 0
		RETURN
	END
	ELSE
	-- ÀÜ¾×ÀÌ 0¿øÀÏ °æ¿ì¿£ »óÀ§·Î½á µî·ÏµÇ¾î ÀÖ´ÂÁö ¿©ºÎ¸¦ Ã¼Å©ÇØ¾ß ÇÔ
	BEGIN
		-- ÇöÀç »óÀ§·Î½á µî·ÏµÇ¾î ÀÖ´Ù¸é
		IF EXISTS(SELECT TOP 1 nUpCharNo FROM tHolyPromise WHERE nUpCharNo = @nCharNo)
		BEGIN
			-- ÀÜ¾×À» Ãâ·ÂÇØÁÖ°í ³¡(´Ù¸¸ ÀÜ¾×Àº ¾ðÁ¦³ª 0¿øÀÏ °ÍÀÌ´Ù.)
			SELECT 0
			SET @nRet = 0
			RETURN
		END
		ELSE
		-- »óÀ§·Î½á µî·ÏµÇ¾î ÀÖÁö ¾Ê´Â °æ¿ì
		BEGIN
			-- È¦¸® ÇÁ¶ó¹Ì½º ¼³¸íÃ¢ ¶ç¿ì¶ó°í ¸í·É
			SET @nRet = -2224
			RETURN
		END	
	END
END





GO
/****** Object:  StoredProcedure [dbo].[usp_HolyPromise_setMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO





CREATE   PROCEDURE [dbo].[usp_HolyPromise_setMoney]
/*
cen use reward
2009.4 By Luunar
 input:
	see SQL
 output:
	= 0 No Error
	< 0 Error
ÇÏÀ§¸â¹ö°¡ µ·À» »ç¿ëÇÏ¸é »óÀ§¸â¹ö¿¡°Ô µ·À» Áö±Þ
*/

@nCharNo		int,
@nRewardMoney	bigint,
@nLevelGap		int,
@nRet			int OUTPUT

AS
BEGIN
	SET NOCOUNT ON

	DECLARE @nUpMemberCharNo	int
	DECLARE @nUpMemberLevel	int
	DECLARE @nMyLevel		int
	DECLARE @nType          tinyint

	SET @nUpMemberCharNo	= 0
	SET @nUpMemberLevel		= 0
	SET @nMyLevel		= 0
	SET @nType = 1 -- (1:ÀÔ±Ý/2:Ãâ±Ý)

	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -2211
		RETURN
	END

	IF ISNULL(@nRewardMoney, 0) <= 0 BEGIN
		SET @nRet = -2212
		RETURN
	END


	IF EXISTS(SELECT nUpCharNo FROM tHolyPromise WHERE nCharNo = @nCharNo)
	BEGIN
		-- »óÀ§ ÄÉ¸¯ÅÍ ¹øÈ£ ¹× ·¹º§À» °¡Á®¿Í º»´Ù.
		SELECT @nUpMemberCharNo = P.nUpCharNo, @nUpMemberLevel = C.nLevel FROM
			tHolyPromise AS P INNER JOIN tCharacter AS C
			ON C.nCharNo =  P.nUpCharNo
		WHERE P.nCharNo = @nCharNo AND C.bDeleted = 0

		-- »óÀ§°¡ ½ÇÁ¸ÇÏ¸é
		IF @nUpMemberCharNo > 0 and @nUpMemberLevel > 0
		BEGIN
			-- ³ªÀÇ ÇÏÀ§ ¸É¹ö(³ª)ÀÇ Level À» °¡Á®¿Â´Ù.
			SELECT @nMyLevel = nLevel FROM tCharacter WHERE nCharNo = @nCharNo
			-- ÇÏÀ§¿Í »óÀ§ ¸É¹ö°£ Level Â÷ÀÌ°¡ ÀÏÁ¤·® ÀÌ»óÀ» ¹þ¾î³ª¸é º¸»óÇØ ÁÖÁö ¾Ê´Â´Ù.
			IF @nUpMemberLevel - @nMyLevel < @nLevelGap
			BEGIN
				SET @nRet = -2223
				RETURN
			END

			-- »óÀ§ ¸É¹ö¿¡°Ô µ· º¸»ó Áö±Þ
			DECLARE @nSeqNo		int
			DECLARE @nRemainMoney	bigint

			SET @nSeqNo       = 0
			SET @nRemainMoney = 0

			BEGIN TRAN

			SELECT TOP 1 @nSeqNo = nSeqNo, @nRemainMoney = nRemainMoney 
			  FROM tHolyPromiseReward 
			 WHERE nCharNo = @nUpMemberCharNo 
			 ORDER BY nSeqNo DESC

			SET @nSeqNo       = @nSeqNo + 1
			SET @nRemainMoney = @nRemainMoney + @nRewardMoney

			INSERT INTO tHolyPromiseReward( nCharNo, nSeqNo, nType, nMoney, nRemainMoney, nReceiptCharNo )
			VALUES (@nUpMemberCharNo, @nSeqNo, @nType, @nRewardMoney, @nRemainMoney, @nCharNo)
			IF @@ERROR <> 0 BEGIN
				-- ÆÄÀÏ ¿¡·¯Ã³¸®
				SET @nRet = -2292
				ROLLBACK TRAN
				RETURN
			END
			ELSE BEGIN
				SET @nRet = 0
				COMMIT TRAN
				RETURN			
			END
		END
		ELSE
		BEGIN
			-- »óÀ§ ¸É¹ö°¡ Á¸ÀçÇÏÁö ¾Ê´Â´Ù.
			SET @nRet = -2222
			RETURN
		END
	END
	ELSE
	BEGIN
		-- È¦¸®ÇÁ¶ó¹Ì½º¿¡ °¡ÀÔÇÑ ÀûÀÌ ¾ø´Ù. (Å« ÀÇ¹Ì´Â ¾øÀ½. ¾îÂ÷ÇÇ ´ÙÀ½ ´Ü°è¿¡¼­ °É·¯Áü)
		SET @nRet = -2221
		RETURN
	END
END




GO
/****** Object:  StoredProcedure [dbo].[usp_HolyPromise_withDrawRemainMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE   PROCEDURE [dbo].[usp_HolyPromise_withDrawRemainMoney]
/*
withdraw cen reward
2009.4 By Luunar
 input:
	see SQL
 output:
	= 0 No Error
	< 0 Error
*/

@nCharNo		int,
@nRet			int OUTPUT

AS
BEGIN
	SET NOCOUNT ON

	DECLARE @nType tinyint
	SET @nType = 2 -- (1:ÀÔ±Ý/2:Ãâ±Ý)

	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -2211
		RETURN
	END

	IF EXISTS(SELECT nMoney FROM tHolyPromiseReward WHERE nCharNo = @nCharNo)
	BEGIN
		-- Æ®·£Àè¼Ç ½ÃÀÛ
		BEGIN TRAN
			DECLARE @nRewardMoney	bigint
			DECLARE @nSeqNo		int

			-- º¸»ó ±Ý¾× °¡Á®¿À±â
			SELECT TOP 1 @nSeqNo = nSeqNo, @nRewardMoney = nRemainMoney FROM tHolyPromiseReward WHERE nCharNo = @nCharNo ORDER BY nSeqNo DESC

			-- Áö±Þ ±Ý¾×ÀÌ 0¿øº¸´Ù ÀÛ°Å³ª °°Àº °æ¿ì¿£ Áö±ÞÇØ ÁÖÁö ¾Ê´Â´Ù.
			IF @nRewardMoney <= 0
			BEGIN
				SET @nRet = -2226
				ROLLBACK TRAN
				RETURN
			END

			-- º¸»ó ±Ý¾× Áö±Þ
			EXEC p_Char_MoneyAdd @nCharNo, @nRewardMoney, @nRet OUTPUT
			IF @nRet <> 1
			BEGIN
				SET @nRet = -2227
				ROLLBACK TRAN
				RETURN
			END

			-- º¸»ó ±â·Ï ±âÀç
			SET @nSeqNo = @nSeqNo + 1

			INSERT INTO tHolyPromiseReward( nCharNo, nSeqNo, nType, nMoney, nRemainMoney, nReceiptCharNo )
			VALUES (@nCharNo, @nSeqNo, @nType, @nRewardMoney, 0, 0)
			IF @@ERROR <> 0
			BEGIN
				SET @nRet = -2292
				ROLLBACK TRAN
				RETURN
			END

			-- °á°ú
			SET @nRet = 0
			SELECT @nRewardMoney
		COMMIT TRAN
	END
	ELSE
	BEGIN
		-- ÅëÀåÀÌ °³¼³µÈ ÀûÀÌ ¾ø´Ù.
		SET @nRet = -2225
		RETURN
	END
END




GO
/****** Object:  StoredProcedure [dbo].[usp_InGameGuide_getViewList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_InGameGuide_getViewList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.04.04.
' Parameter :
'	input Parameter
'		@nSellerNo : 판매자
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'	return Record : 판매자의 판매아이템 리스트
'		nAuctionNo : 경매번호
'		nTerm      : 판매기간
'		nSellPrice : 판매가격
'		nItemKey   : 아이템키
'		nItemID    : 아이템식별자
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_InGameGuide_getViewList]
	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0

	IF EXISTS(SELECT nCharNo FROM tInGameGuide WITH(NOLOCK) WHERE nCharNo = @nCharNo) BEGIN
		SELECT nGuideNo
		  FROM tInGameGuide 
		 WHERE nCharNo = @nCharNo
	END
	ELSE BEGIN
		SET @nRet = -1111
	END
END



GO
/****** Object:  StoredProcedure [dbo].[usp_InGameGuide_setGuideNo]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_InGameGuide_setGuideNo
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.04.04.
' Parameter :
'	input Parameter
'		@nSellerNo : 판매자
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'	return Record : 판매자의 판매아이템 리스트
'		nAuctionNo : 경매번호
'		nTerm      : 판매기간
'		nSellPrice : 판매가격
'		nItemKey   : 아이템키
'		nItemID    : 아이템식별자
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_InGameGuide_setGuideNo]
	@nCharNo  int
,	@nGuideNo int
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0

	IF EXISTS(SELECT nCharNo FROM tInGameGuide WITH(NOLOCK) WHERE nCharNo = @nCharNo AND nGuideNo = @nGuideNo) BEGIN
		SET @nRet = -1111
	END
	ELSE BEGIN
		INSERT INTO tInGameGuide (nCharNo, nGuideNo)
		VALUES (@nCharNo, @nGuideNo)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -2222
		END
	END
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_Add_EnchantGem]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<KHRISMIZER>
-- Create date: <2010. 08. 13>
-- Description:	<nil>
-- =============================================
CREATE PROCEDURE [dbo].[usp_Item_Add_EnchantGem] 
	@nCharNo INT,
	@nEquipmentItemKey BIGINT,
	@nGemItemKey BIGINT,
	@nGemSocketIndex TINYINT,
	@nGemID INT,
	@nRestGemCount TINYINT,
	@nRet INT = 0 OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	
	SET @nRet = 0
	
    BEGIN TRAN
    
    INSERT tItemEnchant (nItemKey, nSlot, nGemItemID, nRestCount)
		VALUES (@nEquipmentItemKey, @nGemSocketIndex, @nGemID, @nRestGemCount)

	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -101
		RETURN
	END

	EXEC p_Item_Delete @nGemItemKey, @nRet OUTPUT
	
	IF @@ERROR <> 0 OR @nRet <> 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -202
		RETURN
	END

    COMMIT TRAN 
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_Add_EnchantSocket]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<KHRISMIZER>
-- Create date: <2010. 08. 13>
-- Description:	<nil>
-- =============================================
CREATE PROCEDURE [dbo].[usp_Item_Add_EnchantSocket]
	@nCharNo INT,
	@nEquipmentItemKey BIGINT,
	@nDrillItemKey BIGINT,
	@nAllSocketCount TINYINT OUTPUT,
	@nDrillLotType SMALLINT,
	@nDrillLotValue BIGINT OUTPUT,
	@nRet INT = 0 OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	
	SET @nRet = 0

    BEGIN TRAN

	EXEC p_Item_Delete @nDrillItemKey, @nRet OUTPUT

	IF @@ERROR <> 0 OR @nRet <> 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -101
		RETURN
	END
	
	SET @nDrillLotValue = 0
	
	DECLARE @nCreatedSocketCount TINYINT
	
	EXEC p_Item_GetOption @nEquipmentItemKey, 523, @nAllSocketCount OUTPUT, @nRet OUTPUT

	IF @@ERROR <> 0 OR @nRet = 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -201
		RETURN
	END

	EXEC p_Item_GetOption @nEquipmentItemKey, 524, @nCreatedSocketCount OUTPUT, @nRet OUTPUT

	IF @@ERROR <> 0 OR @nRet = 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -202
		RETURN
	END
	
	SET @nAllSocketCount = @nAllSocketCount + 1
	SET @nCreatedSocketCount = @nCreatedSocketCount + 1
	
	EXEC p_Item_SetOption @nEquipmentItemKey, 523, @nAllSocketCount, @nRet OUTPUT

	IF @@ERROR <> 0 OR @nRet <> 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -301
		RETURN
	END
	
	EXEC p_Item_SetOption @nEquipmentItemKey, 524, @nCreatedSocketCount, @nRet OUTPUT
	
	IF @@ERROR <> 0 OR @nRet <> 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -302
		RETURN
	END
	
    COMMIT TRAN  
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_Del_EnchantGem]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<KHRISMIZER>
-- Create date: <2010. 08. 13>
-- Description:	<nil>
-- =============================================
CREATE PROCEDURE [dbo].[usp_Item_Del_EnchantGem] 
	@nCharNo INT,
	@nEquipmentItemKey BIGINT,
	@nRemoverItemKey BIGINT,
	@nResetSocketIndex TINYINT,
	@bNeedItem TINYINT,
	@nRemoverLotType SMALLINT,
	@nRemoverLotValue BIGINT = 0 OUTPUT,
	@nRet INT = 0 OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	
	SET @nRet = 0
	SET @nRemoverLotValue = 0
	
    BEGIN TRAN

	IF @bNeedItem = 1
	BEGIN
		EXEC p_Item_Delete @nRemoverItemKey, @nRet OUTPUT

		IF @@ERROR <> 0 OR @nRet <> 0
		BEGIN
			ROLLBACK TRAN
			SET @nRet = -101
			RETURN
		END
	END
	
    DELETE FROM tItemEnchant
		WHERE nItemKey = @nEquipmentItemKey
			AND nSlot = @nResetSocketIndex

	IF @@ERROR <> 0 OR @@ROWCOUNT = 0
	BEGIN
		ROLLBACK TRAN
		SET @nRet = -201
		RETURN
	END

    COMMIT TRAN
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_Get_EnchantAll]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<KHRISMIZER>
-- Create date: <2010. 08. 13>
-- Description:	<nil>
-- =============================================
CREATE PROCEDURE [dbo].[usp_Item_Get_EnchantAll]
	@nEquipmentItemKey BIGINT,
	@nRet INT = 0 OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

	SET @nRet = 0
	
	SELECT nSlot, nGemItemID, nRestCount FROM tItemEnchant WHERE nItemKey = @nEquipmentItemKey

	IF @@ERROR <> 0
	BEGIN
		SET @nRet = -101
		RETURN
	END 
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getEmptyStorage]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Item_getEmptyStorage
' 작성자 : 고동균(kodong@gmail.com)
' 작성일 : 2008.4.1
' Description 
'   owner와 storageType을 받아 빈공간의 storage번호를 리턴하는 프로시저
' Parameter 
'	input Parameter
'		@owner       : 캐릭터번호
'		@storageType : 저장소
'	output Parameter
'		@ret         : result
'			<  0 : fail
'			>= 0 : empty storage number
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getEmptyStorage]
	@owner       int = 0
,	@storageType tinyint = 0
,	@ret         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @err AS bit
	DECLARE @emptyStorage AS smallint
	SET @err = 0

	IF @owner = 0
		SET @err = 1
	IF @storageType = 0
		SET @err = 1

	IF @err <> 1
		BEGIN	
			IF EXISTS(SELECT nItemKey FROM tItem WHERE nOwner = @owner AND nStorageType = @storageType AND nStorage = 0)
				BEGIN
					SELECT @emptyStorage = ISNULL(MIN(nStorage + 1), 0)
					  FROM tItem 
					 WHERE nOwner = @owner 
					   AND nStorageType = @storageType 
					   AND nStorage + 1 NOT IN (
							SELECT nStorage
							  FROM tItem 
							 WHERE nOwner = @owner AND nStorageType = @storageType)
				END
			ELSE
				BEGIN
					SET @emptyStorage = 0
				END

			SET @ret = @emptyStorage
			PRINT @ret
		END
	ELSE
		SET @ret = -1

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_GuildToken_BuyAll]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[usp_Item_GuildToken_BuyAll]
/*
Item buy all
2004.8 By CJC
 input:
	see SQL
 output:
	@nRet	0 = OK
	        1 = Not found item owner or Need more owner's money/fame
	        ? = Error code
	        
 operation:
	1. money/fame check and sub
	2. make buy item
	
*/
@nItemKey bigint,
@nOwner int,
@nStorageType tinyint,
@nStorage smallint,
@nItemID int,
@nFlags int,
@nPriceGuildToken bigint,
@nRet int OUTPUT
AS
SET NOCOUNT ON
DECLARE @CreateItemRet int
BEGIN TRAN

-- 아이템 소유자가 충분한 돈을 가지고 있는지 확인 후 돈 감소

EXEC usp_Guild_TokenSub @nOwner, @nPriceGuildToken, @nRet OUTPUT
IF @nRet <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 1
	RETURN
END


-- 아이템 생성 저장프로시져 호출
EXEC p_Item_Create @nItemKey, @nOwner, @nStorageType, @nStorage, @nItemID, @nFlags, @CreateItemRet OUTPUT
IF @CreateItemRet <> 0 OR @@ERROR <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = @CreateItemRet
	RETURN
END
-- 완료
COMMIT TRAN
SET @nRet = 0
-- end




GO
/****** Object:  StoredProcedure [dbo].[usp_Item_GuildToken_BuyLot]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[usp_Item_GuildToken_BuyLot]
/*
Item buy - change lot and money/fame
2008.12 By Lunar
 input:
	see SQL
 output:
	@nRet	0 = OK
	        1 = Not found item owner or Need more owner's money
	        ? = Error code
	        
 operation:
	1. money/fame check and sub
	2. make buy item
	
*/
@nItemKey bigint,
@nOwner int,
@nOptionType smallint,
@nOptionData bigint,
@nPriceGuildToken bigint,
@nRet int OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN


EXEC usp_Guild_TokenSub @nOwner, @nPriceGuildToken, @nRet OUTPUT
IF @nRet <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 1
	RETURN
END

-- 아이템 옵션 저장프로시져 호출(랏 수정)
EXEC p_Item_SetOption @nItemKey, @nOptionType, @nOptionData, @nRet OUTPUT
IF @nRet <> 0
BEGIN
	ROLLBACK TRAN
	RETURN
END
-- 완료
COMMIT TRAN
-- end




GO
/****** Object:  StoredProcedure [dbo].[usp_Item_Set_EnchantGemLot]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<KHRISMIZER>
-- Create date: <2010. 08. 13>
-- Description:	<nil>
-- =============================================
CREATE PROCEDURE [dbo].[usp_Item_Set_EnchantGemLot]
	@nCharNo INT,
	@nEquipmentItemKey BIGINT,
	@nSlot TINYINT,
	@nRestGemCount TINYINT,
	@nRet INT = 0 OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	UPDATE tItemEnchant
		SET nRestCount = @nRestGemCount
		FROM tItemEnchant
		WHERE nItemKey = @nEquipmentItemKey
			AND nSlot = @nSlot

	IF @@ROWCOUNT <> 1 OR @@ERROR <> 0
	BEGIN
		SET @nRet = -101
		RETURN
	END 
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setPutOnBelonged]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Get item storage
2009.9.23 By kodong
2010.12.27 By kodong
'	아이템테이블의 UNIQUE 제약조건 제거로 인해 해당 스토리지에 
'	아이템이 이미 존재하는지 체크하는 프로세스 추가
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
2010.12.27 By kodong : new error code 
'				-2521 : not empty storage
'
'				-2592 : db error(insert)
'				-2593 : db error(update)
*/
CREATE PROCEDURE [dbo].[usp_Item_setPutOnBelonged]
	@nItemKey     bigint
,	@nStorageType tinyint
,	@nStorage     smallint
,	@nOptionType  smallint
,	@nOptionData  bigint
,	@nRet         int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	
	DECLARE @owner AS int
	SET @nRet = 0
	
	SELECT @owner = nOwner FROM tItem WHERE nItemKey = @nItemKey
	IF EXISTS(SELECT nItemKey 
				FROM tItem 
			   WHERE nOwner = @owner 
				 AND nStorageType = @nStorageType 
				 AND nStorage = @nStorage
				 AND nFlags = 0) BEGIN
		SET @nRet = -2521
		RETURN
	END	
	ELSE BEGIN
		BEGIN TRAN
			
		UPDATE tItem 
		SET	nStorageType = @nStorageType, nStorage = @nStorage
		WHERE nItemKey = @nItemKey
		IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
			SET @nRet = -2593
			ROLLBACK TRAN
			RETURN
		END

		IF EXISTS(SELECT nItemKey FROM tItemOptions WHERE nItemKey = @nItemKey AND nOptionType = @nOptionType) BEGIN
			UPDATE tItemOptions
			SET nOptionType = @nOptionType,
				nOptionData = @nOptionData
			WHERE nItemKey = @nItemKey
			AND   nOptionType = @nOptionType
			IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
				SET @nRet = -2593
				ROLLBACK TRAN
				RETURN
			END
		END
		ELSE BEGIN
			INSERT INTO tItemOptions (nItemKey, nOptionType, nOptionData)
			VALUES (@nItemKey, @nOptionType, @nOptionData)
			IF @@ERROR <> 0 BEGIN
				SET @nRet = -2592
				ROLLBACK TRAN
				RETURN
			END
		END

		COMMIT TRAN		
	END
	
	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setPutOnBelongedCancel]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Item_setPutOnBelongedCancel
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.6.23
' Description :
'   착용시 귀속아이템의 귀속 상태를 해제함.
' Parameter :
'	input Parameter
'		@nCharNo               int      : 캐릭터번호(아이템 소유자 : 해제유료아이템, 귀속아이템 모두 해당)
'		@nCancelItemKey        bigint   : 착용시 귀속 해제 유료아이템키
'		@nCancelOptionType     smallint : 착용시 귀속 해제 유료아이템 개수 체크 옵션
'		@nCancelOptionData     bigint   : 착용시 귀속 해제 유료아이템 개수 체크값
'		@nTargetItemKey        bigint   : 대상 아이템키
'		@nTargetOptionType     smallint : 대상 아이템키 귀속 여부 체크 옵션
'		@nTargetOptionData     bigint   : 대상 아이템키 귀속 여부 체크값
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2592 : db error(insert)
'				-2593 : db error(update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_setPutOnBelongedCancel]
	@nCharNo               int
,	@nCancelItemKey        bigint
,	@nCancelOptionType     smallint
,	@nCancelOptionData     bigint
,	@nTargetItemKey        bigint
,	@nTargetOptionType     smallint
,	@nTargetOptionData     bigint
,	@nRet                  int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @cancelOptionData bigint
	DECLARE @targetOptionData bigint
	
	SET @nRet = 0
	SET @cancelOptionData = 0
	SET @targetOptionData = 0
	
	-- invalid nCharNo	
	IF ISNULL(@nCharNo, 0) <= 0 BEGIN
		SET @nRet = -2511
		RETURN
	END
		
	IF NOT EXISTS(SELECT nItemKey FROM tItem WHERE nItemKey = @nCancelItemKey AND nOwner = @nCharNo) BEGIN
		SET @nRet = -2512
		RETURN
	END

	IF NOT EXISTS(SELECT nItemKey FROM tItem WHERE nItemKey = @nTargetItemKey AND nOwner = @nCharNo) BEGIN
		SET @nRet = -2513
		RETURN
	END

	BEGIN TRAN

	-- 해제 유료아이템 차감 또는 삭제	
	SELECT @cancelOptionData = nOptionData
	  FROM tItemOptions WITH (UPDLOCK)
	 WHERE nItemKey    = @nCancelItemKey 
	   AND nOptionType = @nCancelOptionType 
	IF @nCancelOptionData <> @cancelOptionData BEGIN
		SET @nRet = -2521
		ROLLBACK TRAN
		RETURN
	END
	IF @cancelOptionData > 1 BEGIN
		SET @cancelOptionData = @cancelOptionData - 1
		EXEC p_Item_SetOption @nCancelItemKey, @nCancelOptionType, @cancelOptionData, @nRet output
		IF @nRet <> 0 BEGIN
			SET @nRet = -2522
			ROLLBACK TRAN
			RETURN						
		END					
	END
	ELSE BEGIN
		EXEC p_Item_Delete @nCancelItemKey, @nRet output
		IF @nRet <> 0 BEGIN
			SET @nRet = -2523
			ROLLBACK TRAN
			RETURN						
		END					
	END
	
	-- 착용시 귀속 옵션변경(1 -> 0)
	SELECT @targetOptionData = nOptionData
	  FROM tItemOptions WITH (UPDLOCK)
	 WHERE nItemKey    = @nTargetItemKey 
	   AND nOptionType = @nTargetOptionType 
	IF @targetOptionData <> 1 BEGIN
		SET @nRet = -2524
		ROLLBACK TRAN
		RETURN
	END
	ELSE BEGIN
		EXEC p_Item_SetOption @nTargetItemKey, @nTargetOptionType, @nTargetOptionData, @nRet output
		IF @nRet <> 0 BEGIN
			SET @nRet = -2525
			ROLLBACK TRAN
			RETURN						
		END			
	END
	
	COMMIT TRAN
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setPutOnBelongedExchange]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Item_setPutOnBelongedExchange]
/*
Exchange item storage
2009.9.23 By kodong
 input:
	nItemKey1,nStorageType1,nStoage1, nOptionType1, nOptionData1 = #1 Item
	nItemKey2,nStorageType2,nStoage2 = #2 Item
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2592 : db error(insert)
'				-2593 : db error(update)
 operation:
	Set nItemKey1 To nStorageType1,nStoage1
	Set nItemKey2 To nStorageType2,nStoage2
	
	1. #1 item move to temp storage   * #1,#2 is already input that exchanged data.
	2. set #2's storage to #2
	3. set #1's storage to #1
	4. set #1's option #1
*/
@nItemKey1     bigint,
@nStorageType1 tinyint,
@nStorage1     smallint,
@nItemKey2     bigint,
@nStorageType2 tinyint,
@nStorage2     smallint,
@nOptionType1  smallint,
@nOptionData1  bigint,
@nRet          int OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	BEGIN TRAN

	-- Step 1 (nStorageType = 11 은 swap 용도로 사용하는 슬롯)
	UPDATE tItem SET nStorageType = 11
	WHERE nItemKey = @nItemKey1
	IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
		SET @nRet = -2593
		ROLLBACK TRAN
		RETURN
	END
		
	-- Step 2
	UPDATE tItem SET nStorageType = @nStorageType2, nStorage = @nStorage2
	WHERE nItemKey = @nItemKey2
	IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
		SET @nRet = -2593
		ROLLBACK TRAN
		RETURN
	END
		
	-- Step 3
	UPDATE tItem SET nStorageType = @nStorageType1, nStorage = @nStorage1
	WHERE nItemKey = @nItemKey1
	IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
		SET @nRet = -2593
		ROLLBACK TRAN
		RETURN
	END

	-- Step 4 (착용시 귀속 옵션을 찾아 있으면 업데이트, 없으면 새로 생성)
	IF EXISTS(SELECT nItemKey FROM tItemOptions WHERE nItemKey = @nItemKey1 AND nOptionType = @nOptionType1) BEGIN
		UPDATE tItemOptions
		SET nOptionType = @nOptionType1,
			nOptionData = @nOptionData1
		WHERE nItemKey = @nItemKey1
		AND nOptionType = @nOptionType1
		IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
			SET @nRet = -2593
			ROLLBACK TRAN
			RETURN
		END
	END
	ELSE BEGIN
		INSERT INTO tItemOptions (nItemKey, nOptionType, nOptionData)
		VALUES (@nItemKey1, @nOptionType1, @nOptionData1)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -2592
			ROLLBACK TRAN
			RETURN
		END
	END
	
	COMMIT TRAN

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Joker_Class_Add]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Joker_Class_Add]
@nOldClass int,
@nNewClass int

AS

SET NOCOUNT ON

UPDATE tCharacterShape
    SET nClass = @nNewClass
    WHERE nClass = @nOldClass

UPDATE tGuildAcademyGraduate
    SET nClass = @nNewClass
    WHERE nClass = @nOldClass

UPDATE tRank
    SET nClass = @nNewClass
    WHERE nClass = @nOldClass



GO
/****** Object:  StoredProcedure [dbo].[usp_Minihouse_delGate]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_Minihouse_delGate
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.6.17
' Description :
'   ¹Ì´ÏÇÏ¿ì½ºÀÇ °ÔÀÌÆ®¸¦ »èÁ¦ÇÔ.
' Parameter :
'	input Parameter
'		@nCharNo  : Ä³¸¯ÅÍ¹øÈ£
'		@nItemID  : ¾ÆÀÌÅÛ¾ÆÀÌµð(°ÔÀÌÆ® °ü·Ã ½ºÅ©·Ñ ¾ÆÀÌÅÛ ¾ÆÀÌµð)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1622 : not found Gate
'				-1694 : db error(delete)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Minihouse_delGate]
	@nCharNo  int
,	@nItemID  int
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF EXISTS(SELECT nCharNo FROM tMinihouseGate WHERE nCharNo = @nCharNo AND nItemID = @nItemID) BEGIN
		DELETE FROM tMinihouseGate WHERE nCharNo = @nCharNo AND nItemID = @nItemID
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1694
		END
	END
	ELSE BEGIN
		SET @nRet = -1622
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Minihouse_getGateList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_Minihouse_getGateList
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.6.17
' Description :
'   ¹Ì´ÏÇÏ¿ì½ºÀÇ °ÔÀÌÆ® ¸®½ºÆ®¸¦ °¡Á®¿È.
' Parameter :
'	input Parameter
'		@nCharNo  : Ä³¸¯ÅÍ¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1611 : invaild nCharNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Minihouse_getGateList]
	@nCharNo  int
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF @nCharNo <= 0 BEGIN
		SET @nRet = -1611
		RETURN
	END

	IF NOT EXISTS(SELECT nCharNo FROM tMinihouse WHERE nCharNo = @nCharNo) BEGIN
		INSERT INTO tMinihouse (nCharNo) VALUES (@nCharNo)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1692
			RETURN
		END
	END

	SELECT nCharNo, nItemID, bType, ISNULL(dEndDate, 0) AS dEndDate
	  FROM tMinihouseGate
	 WHERE nCharNo = @nCharNo AND (dEndDate > GETDATE() OR dEndDate IS NULL)

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Minihouse_getVisitorCount]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_Minihouse_getVisitorCount
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.6.17
' Description :
'   ¹Ì´ÏÇÏ¿ì½ºÀÇ ¹æ¹®ÀÚ¼ö¸¦ °¡Á®¿È
'	¸¸¾à, ´ë»óÀÚ(nCharNo)ÀÇ ¹Ì´ÏÇÏ¿ì½º°¡ ¾øÀ» °æ¿ì ¹Ì´ÏÇÏ¿ì½º¸¦ »ý¼º½ÃÅ°°í ±âº»°ªÀ»
'	°¡Á®¿È.
' Parameter :
'	input Parameter
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1692 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Minihouse_getVisitorCount]
	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF EXISTS(SELECT nCharNo FROM tMinihouse WHERE nCharNo = @nCharNo) BEGIN
		SELECT nVisitor FROM tMinihouse WHERE nCharNo = @nCharNo
	END
	ELSE BEGIN
		INSERT INTO tMinihouse (nCharNo) VALUES (@nCharNo)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1692
			RETURN
		END

		SELECT nVisitor FROM tMinihouse WHERE nCharNo = @nCharNo
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Minihouse_setGate]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_Minihouse_setGate
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.6.17
' Description :
'   ¹Ì´ÏÇÏ¿ì½ºÀÇ °ÔÀÌÆ®¸¦ µî·ÏÇÔ.
'	Áßº¹µÇ´Â °ÔÀÌÆ®´Â °¡Áú ¼ö ¾øÀ¸¹Ç·Î ¿À·ù·Î Ã³¸®ÇÔ.
'	2008.6.18 Ãß°¡ - °íµ¿±Õ
'	°ÔÀÌÆ®¸¦ ¹è¿ì¸é ÇØ´ç ½ºÅ©·Ñ ¾ÆÀÌÅÛÀ» »èÁ¦Ã³¸® ÇØ¾ßÇØ¼­ °ÔÀÌÆ® Ãß°¡Àü¿¡
'	ÇØ´ç¾ÆÀÌÅÛÀ» Á¤»óÀûÀ¸·Î »èÁ¦ÇßÀ»¶§¸¸ °ÔÀÌÆ® Ãß°¡Ã³¸®¸¦ ¿Ï·áÇÔ.
' Parameter :
'	input Parameter
'		@nCharNo  : Ä³¸¯ÅÍ¹øÈ£
'		@nItemID  : ¾ÆÀÌÅÛ¾ÆÀÌµð(°ÔÀÌÆ® °ü·Ã ½ºÅ©·Ñ ¾ÆÀÌÅÛ ¾ÆÀÌµð)
'		@bType    : ±â°£Á¦ ¿©ºÎ(0:¿µ±¸/1:±â°£Á¦)
'		@dEndDate : Á¾·áÀÏ
'		@nItemKey : »èÁ¦ÇÏ·Á´Â ¾ÆÀÌÅÛÅ°
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1621 : already register Gate
'				-1692 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Minihouse_setGate]
	@nCharNo  int
,	@nItemID  int
,	@bType    tinyint
,	@dEndDate datetime = NULL
,	@nItemKey bigint
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	
	IF EXISTS(SELECT nCharNo FROM tMinihouseGate WHERE nCharNo = @nCharNo AND nItemID = @nItemID) BEGIN
		-- ±âÁ¸¿¡ µî·ÏµÈ °ÔÀÌÆ®°¡ ÀÖÀ» °æ¿ì »ç¿ë±â°£ÀÌ ÀÌ¹Ì Áö³µÀ¸¸é »èÁ¦Ã³¸®¸¦ ¸ÕÀúÇÑ´Ù.
		DELETE FROM tMinihouseGate 
		 WHERE nCharNo = @nCharNo 
		   AND nItemID = @nItemID 
		   AND dEndDate < GETDATE()
		   AND dEndDate IS NOT NULL
	END

	IF EXISTS(SELECT nCharNo FROM tMinihouseGate WHERE nCharNo = @nCharNo AND nItemID = @nItemID) BEGIN
		-- »óÀ§¿¡¼­ »èÁ¦Çß´Âµ¥µµ ÀÖ´Â °æ¿ì´Â ±â°£ÀÌ ³²¾Ò°Å³ª ¿µ±¸¾ÆÀÌÅÛÀÏ °æ¿ìÀÓ.
		SET @nRet = -1621
	END
	ELSE BEGIN
		IF @bType = 0 BEGIN
			SET @dEndDate = NULL
		END

		BEGIN TRAN
		-- °ÔÀÌÆ® ½ºÅ©·Ñ ¾ÆÀÌÅÛÀ» »èÁ¦ÇÔ.
		EXEC p_Item_Delete @nItemKey, @nRet output
		IF @nRet = 0 BEGIN
			INSERT INTO tMinihouseGate (nCharNo, nItemID, bType, dEndDate) 
			VALUES (@nCharNo, @nItemID, @bType, @dEndDate)
			IF @@ERROR <> 0 BEGIN
				SET @nRet = -1692
				ROLLBACK TRAN
			END
			ELSE BEGIN
				COMMIT TRAN
			END
		END
		ELSE BEGIN
			SET @nRet = -1622
			ROLLBACK TRAN
		END		
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Minihouse_setVisitorCount]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*-------------------------------------------------------------------------------------
' ÆÄÀÏ¸í : US_procedureMinihouse.sql
' ³»  ¿ë : Minihouse °ü·Ã ÀúÀåÇÁ·Î½ÃÀú
' ÀÛ¼ºÀÚ : °íµ¿±Õ(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.6.17
' Description :
'------------------------------------------------------------------------------------*/

/*-------------------------------------------------------------------------------------
' ÀúÀåÇÁ·Î½ÃÀú ¸®½ºÆ®
'	1. usp_Minihouse_setVisitorCount
'	2. usp_Minihouse_getVisitorCount
'------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------
' ÀÌ  ¸§ : usp_Minihouse_setVisitorCount
' ÀÛ¼ºÀÚ : Ko Dong Gyun(kodong@gmail.com)
' ÀÛ¼ºÀÏ : 2008.6.17
' Description :
'   ¹Ì´ÏÇÏ¿ì½ºÀÇ ¹æ¹®ÀÚ¼ö¸¦ Áõ°¡½ÃÅ´
'	¸¸¾à, ´ë»óÀÚ(nCharNo)ÀÇ ¹Ì´ÏÇÏ¿ì½º°¡ ¾øÀ» °æ¿ì ¹Ì´ÏÇÏ¿ì½º¸¦ »ý¼º½ÃÅ°°í Áõ°¡½ÃÅ´
' Parameter :
'	input Parameter
'		@nCharNo : Ä³¸¯ÅÍ¹øÈ£
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1692 : db error(insert)
'				-1693 : db error(update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Minihouse_setVisitorCount]
	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF EXISTS(SELECT nCharNo FROM tMinihouse WHERE nCharNo = @nCharNo) BEGIN
		UPDATE tMinihouse SET nVisitor = nVisitor + 1 WHERE nCharNo = @nCharNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1693
		END
	END
	ELSE BEGIN
		INSERT INTO tMinihouse (nCharNo) VALUES (@nCharNo)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1692
			RETURN
		END

		UPDATE tMinihouse SET nVisitor = nVisitor + 1 WHERE nCharNo = @nCharNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1693
		END
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_SafeTrade_setAll]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 파일명 : KR_procedureSafeTrade.sql
' 내  용 : 개인상점 거래에 따른 개선된 저장프로시저 
' 작성자 : 고동균(kodong@gmail.com)
' 작성일 : 2010.4.12
' Description :
'	
'------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------
' 이  름 : usp_SafeTrade_setAll
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2010.4.12
' Description :
'   하나의 셀에 있는 아이템이 통채로 소유권이 변경될때 사용
' Parameter :
'	input Parameter
'		@nBuyer       : 구매자
'		@nSeller      : 판매자
'		@nMoveMoney   : 금액
'		@nItemKey     : 아이템
'		@nStorageType : 인벤토리 종류
'		@nStorage     : 인벤토리 빈공간 번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2621 : mismatch nSeller, nOwner
'				-2622 : invaild StorageType
'				-2623 : error p_Char_MoneyMove
'				-2624 : error p_Item_SetStorageAndOwner
'	return Record
'		nGameNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_SafeTrade_setAll]
	@nBuyer       int,
	@nSeller      int,
	@nMoveMoney   bigint,
	@nItemKey     bigint,
	@nStorageType tinyint,
	@nStorage     smallint,	
	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ret AS int
	DECLARE @nOwner AS int
	DECLARE @nOwnerStorageType AS tinyint
	SET @ret = 0
	SET @nOwner = 0
	SET @nOwnerStorageType = 0

	-- 아이템 소유권 확인(판매자 소유의 아이템인지 확인)
	SELECT @nOwner = nOwner, @nOwnerStorageType = nStorageType FROM tItem WHERE nItemKey = @nItemKey
	IF @nOwner <> @nSeller BEGIN
		SET @nRet = -2621
		RETURN
	END
	IF @nOwnerStorageType <> 9 BEGIN
		SET @nRet = -2622
		RETURN
	END
	ELSE BEGIN
		BEGIN TRAN
		
		-- 구매자의 돈을 판매자에게 이동 시킴
		EXEC p_Char_MoneyMove @nBuyer, @nSeller, @nMoveMoney, @ret output
		IF @ret = 0 BEGIN
			ROLLBACK TRAN
			SET @nRet = -2623
			RETURN
		END	
		
		-- 아이템을 구매자의 인벤토리로 이동시킴
		EXEC p_Item_SetStorageAndOwner @nItemKey, @nBuyer, @nStorageType, @nStorage, @ret output
		IF @ret = 0 BEGIN
			ROLLBACK TRAN
			SET @nRet = -2624
			RETURN
		END
		ELSE BEGIN
			COMMIT TRAN
			SET @nRet = 0
		END
	END

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_SafeTrade_setLot]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_SafeTrade_setLot
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2010.4.19
' Description :
'   판매자의 아이템이 없어지는 경우는 없는데, 왜냐하면 구매자는 아이템이 생성되고 
'   판매자는 아이템이 없어지는 상황이란 결국 아이템 이동이 되어버리기 때문이다. 
'	그렇기 때문에 이 작업에서는 구매자만 아이템이 생성되고 판매자는 분할되는 경우
' Parameter :
'	input Parameter
'		@nBuyer				: 구매자
'		@nBuyerItemKey      : 구매자 아이템키
'		@nBuyerOptionType   : 구매자 아이템옵션
'		@nSeller			: 판매자
'		@nSellerItemKey		: 판매자 아이템키
'		@nSellerOptionType  : 판매자 아이템옵션
'		@nMoveMoney			: 금액
'		@nSplitLot			: 수량
'		@bIsDelete			: 판매자 아이템 삭제여부
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2621 : mismatch nSeller, nOwner
'				-2622 : invaild StorageType
'				-2623 : error p_Char_MoneyMove
'				-2624 : error p_Item_SetStorageAndOwner
'	return Record
'		nGameNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_SafeTrade_setLot]
	@nBuyer            int,
	@nBuyerItemKey     bigint,
	@nBuyerStorageType tinyint,
	@nBuyerStorage     smallint,
	@nBuyerItemID      int,
	@nBuyerFlags       int,
	@nBuyerOptionType  smallint,
	@nSeller           int,
	@nSellerItemKey    bigint,
	@nSellerOptionType smallint,
	@nMoveMoney        bigint,
	@nSplitLot         bigint,
	@nRet              int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ret AS int
	DECLARE @nBuyerOptionData AS bigint
	DECLARE @nSellerOptionData AS bigint
	DECLARE @bIsDelete AS bit
	SET @ret = 0
	SET @nBuyerOptionData = 0
	SET @nSellerOptionData = 0
	SET @bIsDelete = 0

	BEGIN TRAN
	
	-- 구매자에게 판매자의 아이템을 구입할 수 있도록 아이템을 생성시킨다. 단, Lot 은 0개로 초기화
	-- 2010.12.29 By kodong
	EXEC p_Item_Create @nBuyerItemKey, @nBuyer, @nBuyerStorageType, @nBuyerStorage, @nBuyerItemID, @nBuyerFlags, @nRet output
	/*
	INSERT INTO tItem (nItemKey,  nOwner,  nStorageType,  nStorage,  nItemID,  nFlags)
	VALUES (@nBuyerItemKey, @nBuyer, @nBuyerStorageType, @nBuyerStorage, @nBuyerItemID, @nBuyerFlags)
	*/
	IF @@ERROR <> 0 BEGIN
		ROLLBACK TRAN
		SET @nRet = -2628
		RETURN
	END
	ELSE BEGIN
		INSERT INTO tItemOptions (nItemKey, nOptionType, nOptionData)
		VALUES (@nBuyerItemKey, @nBuyerOptionType, @nBuyerOptionData)
		IF @@ERROR <> 0 BEGIN
			ROLLBACK TRAN
			SET @nRet = -2629
			RETURN
		END	
	END
	
	-- 아이템을 구매자의 인벤토리로 이동시킴
	EXEC usp_SafeTrade_setMerge @nBuyer, @nBuyerItemKey, @nBuyerOptionType, @nSeller, @nSellerItemKey,
								@nSellerOptionType, @nMoveMoney, @nSplitLot, @bIsDelete, @ret output
	IF @ret <> 0 BEGIN
		ROLLBACK TRAN
		SET @nRet = @ret
		RETURN
	END
	ELSE BEGIN
		COMMIT TRAN
		SET @nRet = 0
	END

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_SafeTrade_setMerge]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_SafeTrade_setMerge
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2010.4.12
' Description :
'   Lot 아이템중에서 판매자의 아이템중 일부 또는 전체가 이동되는 것이며,
'   구매자 입장에서는 반드시 같은 종류의 아이템을 가지고 있어서 합쳐지는 경우이다.
' Parameter :
'	input Parameter
'		@nBuyer				: 구매자
'		@nBuyerItemKey      : 구매자 아이템키
'		@nBuyerOptionType   : 구매자 아이템옵션
'		@nSeller			: 판매자
'		@nSellerItemKey		: 판매자 아이템키
'		@nSellerOptionType  : 판매자 아이템옵션
'		@nMoveMoney			: 금액
'		@nSplitLot			: 수량
'		@bIsDelete			: 판매자 아이템 삭제여부
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2621 : mismatch nSeller, nOwner
'				-2622 : invaild StorageType
'				-2623 : error p_Char_MoneyMove
'				-2624 : error p_Item_SetStorageAndOwner
'	return Record
'		nGameNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_SafeTrade_setMerge]
	@nBuyer            int,
	@nBuyerItemKey     bigint,
	@nBuyerOptionType  smallint,
	@nSeller           int,
	@nSellerItemKey    bigint,
	@nSellerOptionType smallint,
	@nMoveMoney        bigint,
	@nSplitLot         bigint,
	@bIsDelete         bit,
	@nRet              int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ret AS int
	DECLARE @nBuyerOptionData AS bigint
	DECLARE @nSellerOptionData AS bigint
	DECLARE @nSellerLot AS bit
	SET @ret = 0
	SET @nBuyerOptionData = 0
	SET @nSellerOptionData = 0
	SET @nSellerLot = 0

	BEGIN TRAN
	
	-- 구매자의 돈을 판매자에게 이동 시킴
	EXEC p_Char_MoneyMove @nBuyer, @nSeller, @nMoveMoney, @ret output
	IF @ret = 0 BEGIN
		ROLLBACK TRAN
		SET @nRet = -2623
		RETURN
	END	
	
	-- 아이템을 구매자의 인벤토리로 이동시킴
	EXEC p_Item_Split_N_Merge_New @nSellerItemKey, @nSeller, @nSellerOptionType, @nSellerOptionData output,
								  @nBuyerItemKey, @nBuyer, @nBuyerOptionType, @nBuyerOptionData output, @nSplitLot, @ret output
	IF @ret <> 0 BEGIN
		ROLLBACK TRAN
		SET @nRet = -2624
		RETURN
	END

	-- 판매자 아이템의 Lot 개수가 0 이 아닌경우 1 로 만들어 판매자의 아이템이 삭제됨을 방지한다.
	IF @nSellerOptionData > 0 BEGIN
		SET @nSellerLot = 1
	END

	IF @bIsDelete = @nSellerLot BEGIN
		IF @bIsDelete = 0 BEGIN
			-- Lot이 0인데 삭제 요청이 오지 않은 경우
			ROLLBACK TRAN
			SET @nRet = -2625
		END
		ELSE BEGIN
			-- Lot이 0이 아닌데 삭제 요청이 온 경우
			ROLLBACK TRAN
			SET @nRet = -2626
		END
	END
	ELSE BEGIN
		IF @bIsDelete = 1 BEGIN
			-- 판매자의 아이템삭제
			EXEC p_Item_Delete @nSellerItemKey, @ret output
			IF @ret <> 0 BEGIN
				ROLLBACK TRAN
				SET @nRet = -2627
			END
		END

		COMMIT TRAN
		SET @nRet = 0
	END

	SET NOCOUNT OFF
END



GO
/****** Object:  StoredProcedure [dbo].[usp_SpamBlock_getTime]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' 이  름 : usp_SpamBlock_getTime
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2010.7.8.
' Description :
'   캐릭터의 블럭시간과 대기시간을 가져옴
' Parameter :
'	input Parameter
'		@nCharNo : 캐릭터번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2822 : not found nCharNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_SpamBlock_getTime]
	@nCharNo int
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	IF EXISTS(SELECT nCharNo FROM tSpamBlockTime WHERE nCharNo = @nCharNo)
	BEGIN
		SELECT nCharNo, nBlockTime, nStandbyTime
		  FROM tSpamBlockTime
		 WHERE nCharNo = @nCharNo
	END
	ELSE BEGIN
		SET @nRet = -2822
	END

	SET NOCOUNT ON
END

GO
/****** Object:  StoredProcedure [dbo].[usp_SpamBlock_setTime]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*--------------------------------------------------------------------------------------
' 이  름 : usp_SpamBlock_setTime
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2010.7.8.
' Description :
'   캐릭터의 블럭시간과 대기시간을 설정
' Parameter :
'	input Parameter
'		@nCharNo      : 캐릭터번호
'		@nBlockTime   : 블럭시간(단위:초)
'		@nStandbyTime : 대기시간(단위:초)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2892 : db error(update)
'				-2393 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_SpamBlock_setTime]
	@nCharNo int
,	@nBlockTime int
,	@nStandbyTime int
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	IF EXISTS(SELECT nCharNo FROM tSpamBlockTime WHERE nCharNo = @nCharNo)
	BEGIN
		UPDATE tSpamBlockTime
		   SET sys_update_date = GETDATE()
		     , nBlockTime = @nBlockTime
		     , nStandbyTime = @nStandbyTime
		 WHERE nCharNo = @nCharNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
		BEGIN
			SET @nRet = -2893
		END
	END
	ELSE BEGIN
		INSERT INTO tSpamBlockTime (nCharNo, nBlockTime, nStandbyTime)
		VALUES (@nCharNo, @nBlockTime, @nStandbyTime)
		IF @@ERROR <> 0
		BEGIN
			SET @nRet = -2892
		END
	END

	SET NOCOUNT ON
END

GO
/****** Object:  StoredProcedure [dbo].[usp_SpamFilter_get]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' 이  름 : usp_SpamFilter_get
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2010.6.21.
' Description :
'   유저의 스패머 필터링 값을 가져옴
' Parameter :
'	input Parameter
'		@nUserNo         : 유저번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2821 : not found UserNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_SpamFilter_get]
	@nUserNo int
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	IF EXISTS(SELECT nUserNo FROM tSpamFilter WHERE nUserNo = @nUserNo)
	BEGIN
		SELECT nUserNo, bIsSpamSpread, bIsSpamBlock, nSpamFilterRate
		  FROM tSpamFilter
		 WHERE nUserNo = @nUserNo
	END
	ELSE BEGIN
		SET @nRet = -2821
	END

	SET NOCOUNT ON
END

GO
/****** Object:  StoredProcedure [dbo].[usp_SpamFilter_set]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' 이  름 : usp_SpamFilter_set
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2010.6.21.
' Description :
'   유저의 스패머 필터링 값을 설정
' Parameter :
'	input Parameter
'		@nUserNo         : 유저번호
'		@bIsSpamSpread   : 스팸 펼치기 여부(0:No/1:Yes)
'		@bIsSpamBlock    : 스팸 차단 여부(0:No/1:Yes)
'		@nSpamFilterRate : 스팸 차단 비율(%)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2892 : db error(update)
'				-2393 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_SpamFilter_set]
	@nUserNo int
,	@bIsSpamSpread bit
,	@bIsSpamBlock  bit
,	@nSpamFilterRate tinyint
,	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	IF EXISTS(SELECT nUserNo FROM tSpamFilter WHERE nUserNo = @nUserNo)
	BEGIN
		UPDATE tSpamFilter
		   SET sys_update_date = GETDATE()
		     , bIsSpamSpread = @bIsSpamSpread
		     , bIsSpamBlock = @bIsSpamBlock
		     , nSpamFilterRate = @nSpamFilterRate
		 WHERE nUserNo = @nUserNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
		BEGIN
			SET @nRet = -2893
		END
	END
	ELSE BEGIN
		INSERT INTO tSpamFilter (nUserNo, bIsSpamSpread, bIsSpamBlock, nSpamFilterRate)
		VALUES (@nUserNo, @bIsSpamSpread, @bIsSpamBlock, @nSpamFilterRate)
		IF @@ERROR <> 0
		BEGIN
			SET @nRet = -2892
		END
	END

	SET NOCOUNT ON
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Statistics_getGameMoneyList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Statistics_setTotalMoney
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.6.27
' Description :
'   게임내 게임머니 총 금액을 일자별 리스트로 출력.
' Parameter :
'	input Parameter
'		@dStartDate : 시작일
'		@dEndDate   : 종료일
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
' exec usp_Statistics_getGameMoneyList 'start_date', 'end_date', @nRet output
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Statistics_getGameMoneyList]
	@dStartDate datetime = NULL
,	@dEndDate   datetime = NULL
,	@nRet       int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0

	IF ISNULL(@dStartDate, '') = '' BEGIN
		SET @dStartDate = DATEADD(Month, -1, GETDATE())
	END

	IF ISNULL(@dEndDate, '') = '' BEGIN
		SET @dEndDate = GETDATE()
	END	
	
	-- create temp table	
	CREATE TABLE dbo.#tables (
		[dDate]            datetime
	,	[Character]        bigint
	,	[User]             bigint
	,	[GuildStorage]     bigint
	,	[GuildTournament]  bigint
	,	[HolyPromise]      bigint
	,	[AuctionInventory] bigint
	,	[Total]            bigint
	)

	INSERT INTO #tables 
	SELECT CAST(dDate AS nvarchar(10)) AS dDate
		 , SUM(CASE WHEN sCode = 'Character' THEN nMoney ELSE 0 END) AS 'Character'
		 , SUM(CASE WHEN sCode = 'User' THEN nMoney ELSE 0 END) AS 'User'
		 , SUM(CASE WHEN sCode = 'GuildStorage' THEN nMoney ELSE 0 END) AS 'GuildStorage'
		 , SUM(CASE WHEN sCode = 'GuildTournament' THEN nMoney ELSE 0 END) AS 'GuildTournament'
		 , SUM(CASE WHEN sCode = 'HolyPromise' THEN nMoney ELSE 0 END) AS 'HolyPromise'
		 , SUM(CASE WHEN sCode = 'AuctionInventory' THEN nMoney ELSE 0 END) AS 'AuctionInventory'
		 , SUM(nMoney) AS 'Total'
	  FROM tStatisticsGameMoney WITH(NOLOCK)
	 WHERE dDate >= @dStartDate AND dDate <= @dEndDate
	 GROUP BY CAST(dDate AS nvarchar(10))
	 ORDER BY dDate

	SELECT T1.*
		 , ISNULL((T1.Total - T2.Total), 0) AS 'Difference'
	  FROM (
			SELECT *
			  FROM #tables) T1 LEFT JOIN (
			SELECT dDate, Total
			  FROM #tables) T2 ON T1.dDate = DATEADD(dd, 1, T2.dDate)
END



GO
/****** Object:  StoredProcedure [dbo].[usp_Statistics_setTotalMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Statistics_setTotalMoney
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.6.27
' Description :
'   게임내 게임머니 총 금액을 일자별 누적.
' Parameter :
'	input Parameter : none
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'	return Record
'		nGameNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Statistics_setTotalMoney]
	@nRet int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	-- 캐릭터가 보유한 총 금액
	IF OBJECT_ID('tCharacter') IS NOT NULL
		AND OBJECTPROPERTY(OBJECT_ID('tCharacter'), 'IsTable') = 1 BEGIN
		INSERT INTO tStatisticsGameMoney (sCode, nMoney)
		SELECT 'Character' AS sCode, ISNULL(SUM(nMoney), 0) AS nMoney
		  FROM tCharacter WITH(NOLOCK)
		 WHERE bDeleted = 0
	END

	-- 계정에 보유한 총 금액
	IF OBJECT_ID('tUserMoney') IS NOT NULL
		AND OBJECTPROPERTY(OBJECT_ID('tUserMoney'), 'IsTable') = 1 BEGIN
		INSERT INTO tStatisticsGameMoney (sCode, nMoney)
		SELECT 'User' AS sCode, ISNULL(SUM(nUserMoney), 0) AS nMoney
		  FROM tUserMoney WITH(NOLOCK)
	END

	-- 길드창고에 보유한 총 금액
	IF OBJECT_ID('tGuildAcademy') IS NOT NULL
		AND OBJECTPROPERTY(OBJECT_ID('tGuildAcademy'), 'IsTable') = 1 BEGIN
		INSERT INTO tStatisticsGameMoney (sCode, nMoney)
		SELECT 'GuildStorage'AS sCode, ISNULL(SUM(nMoney), 0) AS nMoney
		  FROM tGuildAcademy WITH(NOLOCK)
	END

	-- 길드토너먼트 보상 금액중 찾지 않음 금액
	IF OBJECT_ID('tGuild') IS NOT NULL
		AND OBJECTPROPERTY(OBJECT_ID('tGuild'), 'IsTable') = 1 BEGIN
		INSERT INTO tStatisticsGameMoney (sCode, nMoney)
		SELECT 'GuildTournament' AS sCode, ISNULL(SUM(nMoney), 0) AS nMoney
		  FROM tGuild WITH(NOLOCK)
	END

	-- 홀리프라미스 보상 금액중 찾지 않은 금액
	IF OBJECT_ID('tHolyPromiseReward') IS NOT NULL
		AND OBJECTPROPERTY(OBJECT_ID('tHolyPromiseReward'), 'IsTable') = 1 BEGIN
		INSERT INTO tStatisticsGameMoney (sCode, nMoney)
		SELECT 'HolyPromise' AS sCode, ISNULL(SUM(T1.nRemainMoney), 0) AS nMoney
		  FROM tHolyPromiseReward T1 WITH(NOLOCK), (
				SELECT nCharNo, MAX(nSeqNo) AS nMaxSeqNo
				  FROM tHolyPromiseReward WITH(NOLOCK)
				 GROUP BY nCharNo) T2
		 WHERE T1.nCharNo = T2.nCharNo
		   AND T1.nSeqNo = T2.nMaxSeqNo 
		 ORDER BY 1
	END

	-- 경매장 판매 후 찾지 않은 금액
	IF OBJECT_ID('tAuctionInventory') IS NOT NULL
		AND OBJECTPROPERTY(OBJECT_ID('tAuctionInventory'), 'IsTable') = 1 BEGIN
		INSERT INTO tStatisticsGameMoney (sCode, nMoney)
		SELECT 'AuctionInventory' AS sCode, ISNULL(SUM(nMoney), 0) AS nMoney
		  FROM tAuctionInventory WITH(NOLOCK)
		 WHERE bIsDelete = 0
	END
END



GO
/****** Object:  UserDefinedFunction [dbo].[fn_Auction_getCategory]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 테이블 : fn_Auction_getCategory
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011년 01월 11일
' Description : 
'	카테고리 테이블을 검색하여 자신의 하위 카테고리 테이블을 모두 포함한 결과를
'	리턴하는 함수.

'	처음 부모 카테고리를 검색하여 존재한다면 하위 카테고리를 반복적으로 찾아
'	테이블 변수에 depth(깊이)만큼 계속 삽입하여 카테고리 리스트 테이블을 생성하는 원리.
'	depth가 존재해야 하는 이유는 무한 반복적으로 찾는 것을 방지하는 것이 목적임.
'-------------------------------------------------------------------------------------*/
CREATE FUNCTION [dbo].[fn_Auction_getCategory] (@categoryNo int) 
	RETURNS @_category TABLE (
		categoryNo   int
	,	parentsNo    int)
AS
BEGIN
	DECLARE @depth int
	SET @depth = 1

	DECLARE @retCategory TABLE (
		categoryNo   int
	,	parentsNo    int
	,	depth        tinyint)		

	INSERT INTO @retCategory
	SELECT nCategoryNo, nParentsNo, @depth
	  FROM tAuctionCategory
	 WHERE nCategoryNo = @categoryNo
	WHILE @@rowcount > 0 BEGIN
		SET @depth = @depth + 1

		INSERT @retCategory
		SELECT T1.nCategoryNo, T1.nParentsNo, @depth
		  FROM tAuctionCategory AS T1, @retCategory AS T2
		 WHERE T1.nParentsNo = T2.categoryNo AND T2.depth = @depth - 1
	END

	INSERT @_category
		SELECT categoryNo, parentsNo
		  FROM @retCategory
		 ORDER BY categoryNo

	RETURN
END



GO
/****** Object:  Table [dbo].[auditCharacterData]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[auditCharacterData](
	[audit_log_id] [uniqueidentifier] NULL,
	[audit_log_type] [char](3) NOT NULL,
	[audit_user] [sysname] NOT NULL,
	[audit_host] [char](50) NULL,
	[audit_application] [char](50) NULL,
	[audit_char_no] [int] NULL,
	[audit_char_money] [int] NULL,
	[audit_changed] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LoggedInChars]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LoggedInChars](
	[nCharNo] [int] NOT NULL,
	[nUserNo] [int] NULL,
	[nAdminLevel] [tinyint] NULL,
	[sID] [nvarchar](40) NULL,
	[nClass] [tinyint] NULL,
	[nLevel] [smallint] NULL,
	[nExp] [bigint] NULL,
	[nFame] [int] NULL,
	[nMoney] [bigint] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tArenaChallenge]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tArenaChallenge](
	[nCharNo] [int] NOT NULL,
	[nMonsterLevel] [smallint] NOT NULL,
	[nMonsterMKC] [smallint] NOT NULL,
	[nMonsterKPS] [smallint] NOT NULL,
	[nMonsterKills] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nMonsterLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tArenaFBZ]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tArenaFBZ](
	[nFBZWin] [int] NOT NULL,
	[nFBZLose] [int] NOT NULL,
	[nFBZLadder] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tArenaGuild]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tArenaGuild](
	[nGuildLose] [int] NOT NULL,
	[nGuildDraw] [int] NOT NULL,
	[nGuildNo] [int] NOT NULL,
	[nGuildWin] [int] NOT NULL,
	[nGuildLadder] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nGuildNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tArenaPvP]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tArenaPvP](
	[nPvPLadder] [int] NOT NULL,
	[nPvPWin] [int] NOT NULL,
	[nPvPLose] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nPvPDraw] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tAttendance]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tAttendance](
	[nCharNo] [int] NOT NULL,
	[dAttendanceDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[dAttendanceDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tAttendanceReward]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tAttendanceReward](
	[nCharNo] [int] NOT NULL,
	[dEventDate] [datetime] NOT NULL,
	[dRewardDate] [datetime] NOT NULL,
	[nRewardID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[dEventDate] ASC,
	[nRewardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tAuction]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tAuction](
	[nAuctionNo] [int] IDENTITY(1,1) NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nCategoryNo] [tinyint] NOT NULL,
	[nSellerNo] [int] NOT NULL,
	[nRegistration] [bigint] NOT NULL,
	[dSellStartDate] [datetime] NOT NULL,
	[dSellEndDate] [datetime] NOT NULL,
	[nSellPrice] [bigint] NOT NULL,
	[nItemKey] [bigint] NOT NULL,
	[nItemID] [int] NOT NULL,
	[nItemLevel] [smallint] NOT NULL,
	[nItemGrade] [tinyint] NOT NULL,
	[sItemName] [nvarchar](64) NOT NULL,
	[nBidderNo] [int] NULL,
	[dBidDate] [datetime] NULL,
	[nSellCharge] [bigint] NULL,
	[nStatus] [tinyint] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nAuctionNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tAuctionCategory]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tAuctionCategory](
	[nCategoryNo] [tinyint] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[sCategoryName] [nvarchar](30) NOT NULL,
	[bIsActive] [bit] NOT NULL,
	[nParentsNo] [tinyint] NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nCategoryNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tAuctionInventory]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tAuctionInventory](
	[nCharNo] [int] NOT NULL,
	[nAuctionNo] [int] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nInventoryType] [tinyint] NOT NULL,
	[dStartDate] [datetime] NOT NULL,
	[dEndDate] [datetime] NOT NULL,
	[nMoney] [bigint] NULL,
	[nItemKey] [bigint] NULL,
	[bIsDelete] [bit] NOT NULL,
	[dDeleteDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nAuctionNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tAutoPick]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tAutoPick](
	[nCharNo] [int] NOT NULL,
	[IsAutoPick] [tinyint] NOT NULL,
 CONSTRAINT [PK_tAutoPick] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacter]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tCharacter](
	[nCharNo] [int] IDENTITY(1,1) NOT NULL,
	[sID] [nvarchar](40) NOT NULL,
	[nUserNo] [int] NOT NULL,
	[nAdminLevel] [tinyint] NOT NULL,
	[nPS] [int] NOT NULL,
	[nGS] [int] NOT NULL,
	[dCreateDate] [datetime] NOT NULL,
	[bDeleted] [tinyint] NOT NULL,
	[nLoginZoneX] [int] NOT NULL,
	[nLoginZoneY] [int] NOT NULL,
	[nLoginZoneD] [tinyint] NOT NULL,
	[nPKYellowTime] [tinyint] NOT NULL,
	[dDeletedDate] [datetime] NOT NULL,
	[nAP] [smallint] NOT NULL,
	[sLoginZone] [varchar](16) NOT NULL,
	[nPKCount] [int] NOT NULL,
	[nLevel] [smallint] NOT NULL,
	[nExp] [bigint] NOT NULL,
	[nSlotNo] [tinyint] NOT NULL,
	[nHP] [smallint] NOT NULL,
	[nSP] [smallint] NOT NULL,
	[nHPS] [smallint] NOT NULL,
	[nSPS] [smallint] NOT NULL,
	[dLastLoginDate] [datetime] NOT NULL,
	[nLoginCount] [int] NOT NULL,
	[nCreateWorld] [tinyint] NOT NULL,
	[nFame] [int] NOT NULL,
	[nPrisonMin] [smallint] NOT NULL,
	[nPlayMin] [int] NOT NULL,
	[nFlags] [int] NOT NULL,
	[nMoney] [bigint] NOT NULL,
	[nStrength] [tinyint] NOT NULL,
	[nConstitute] [tinyint] NOT NULL,
	[nDexterity] [tinyint] NOT NULL,
	[nIntelligence] [tinyint] NOT NULL,
	[nMentalPower] [tinyint] NOT NULL,
	[nRedistributePoint] [tinyint] NOT NULL,
	[nKQHandle] [int] NOT NULL,
	[sKQMap] [varchar](16) NOT NULL,
	[nKQX] [int] NOT NULL,
	[nKQY] [int] NOT NULL,
	[nKQD] [tinyint] NOT NULL,
	[dKQDate] [datetime] NOT NULL,
 CONSTRAINT [PK__tCharacter__2610A626] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [DATA]
) ON [DATA]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tCharacterChangeID]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterChangeID](
	[nRowNo] [int] IDENTITY(1,1) NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nUserNo] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[bFlag] [bit] NOT NULL,
	[bInit] [bit] NOT NULL,
	[sOldID] [nvarchar](50) NULL,
	[sNewID] [nvarchar](50) NULL,
 CONSTRAINT [PK_tCharacterChangeID_NC] PRIMARY KEY NONCLUSTERED 
(
	[nRowNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterChargedBuffer]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterChargedBuffer](
	[nNo] [int] IDENTITY(1,1) NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nKey] [int] NOT NULL,
	[nID] [smallint] NOT NULL,
	[dUseDate] [datetime] NOT NULL,
	[dEndDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tCharacterChargedBuffer] PRIMARY KEY NONCLUSTERED 
(
	[nNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tCharacterDisposition]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterDisposition](
	[nCharNo] [int] NOT NULL,
	[nDisposition] [tinyint] NOT NULL,
	[nKillCount] [int] NOT NULL,
	[nKillPoint] [int] NOT NULL,
	[nChaosPoint] [int] NOT NULL,
	[nNeutralTime] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterGameOption]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterGameOption](
	[nCharNo] [int] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[nOptionNo] [smallint] NOT NULL,
	[nValue] [tinyint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nOptionNo] ASC,
	[nValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterKeyMap]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterKeyMap](
	[nCharNo] [int] NOT NULL,
	[nFunctionNo] [smallint] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nExtendedKey] [tinyint] NULL,
	[nASCIICode] [tinyint] NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nFunctionNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterLoading]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tCharacterLoading](
	[nCharNo] [int] NOT NULL,
	[nSeqNo] [int] NOT NULL,
	[dWorldTime] [datetime] NOT NULL,
	[dDBTime] [datetime] NOT NULL,
	[sLoadingMapName] [varchar](16) NOT NULL,
	[nFailType] [smallint] NOT NULL,
	[nLoadingFailCnt] [smallint] NOT NULL,
 CONSTRAINT [PK_tCharLoading] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nSeqNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tCharacterLoadingDetailInfo]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterLoadingDetailInfo](
	[nCharNo] [int] NOT NULL,
	[nSeqNo] [int] NOT NULL,
	[nErrorNo] [int] NOT NULL,
	[nErrorData] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nSeqNo] ASC,
	[nErrorNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterOptions]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tCharacterOptions](
	[nCharNo] [int] NOT NULL,
	[sShortCutData] [varbinary](1024) NULL,
	[sShortCutSize] [varbinary](24) NULL,
	[sVideo] [varbinary](60) NULL,
	[sSound] [varbinary](1) NULL,
	[sGame] [varbinary](64) NULL,
	[sWindowsPos] [varbinary](392) NULL,
	[sKeyMapping] [varbinary](308) NULL,
 CONSTRAINT [PK_tCharacterOptions] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
) ON [DATA]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tCharacterShape]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterShape](
	[nCharNo] [int] NOT NULL,
	[nClass] [tinyint] NOT NULL,
	[nRace] [tinyint] NOT NULL,
	[nGender] [tinyint] NOT NULL,
	[nHairType] [tinyint] NOT NULL,
	[nHairColor] [tinyint] NOT NULL,
	[nFaceShape] [tinyint] NOT NULL,
 CONSTRAINT [PK__tCharacterShape__336AA144] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tCharacterShortCut]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterShortCut](
	[nCharNo] [int] NOT NULL,
	[nSlotNo] [tinyint] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NOT NULL,
	[nCodeType] [smallint] NOT NULL,
	[nCodeNo] [smallint] NOT NULL,
	[nValue] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nSlotNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterSingleOption]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterSingleOption](
	[nCharNo] [int] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[bMysteryBolt] [bit] NOT NULL,
	[bAutoPick] [bit] NOT NULL,
	[dRestExpLastExecuteTime] [datetime] NOT NULL,
 CONSTRAINT [PK_tCharacterSingleOption_CL] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterState]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterState](
	[nCharNo] [int] NOT NULL,
	[nStateNo] [smallint] NOT NULL,
	[nStateLevel] [smallint] NOT NULL,
	[nStateTime] [int] NOT NULL,
	[nStateCount] [smallint] NOT NULL,
	[nStatePowerDemage] [tinyint] NOT NULL,
	[nStatePowerSP] [tinyint] NOT NULL,
	[nStatePowerKeepTime] [tinyint] NOT NULL,
	[nStatePowerCoolTime] [tinyint] NOT NULL,
	[nSetItemIndexA] [smallint] NOT NULL,
	[nSetItemValueA] [smallint] NOT NULL,
	[nSetItemIndexB] [smallint] NOT NULL,
	[nSetItemValueB] [smallint] NOT NULL,
	[nSetItemIndexC] [smallint] NOT NULL,
	[nSetItemValueC] [smallint] NOT NULL,
	[nSetItemIndexD] [smallint] NOT NULL,
	[nSetItemValueD] [smallint] NOT NULL,
	[nRestTime] [int] NOT NULL,
	[nCasterLevel] [smallint] NULL,
	[nCasterAttackPower] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nStateNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCharacterTitle]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCharacterTitle](
	[nNo] [bigint] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nType] [tinyint] NOT NULL,
	[nElementNo] [tinyint] NOT NULL,
	[nElementValue] [bigint] NOT NULL,
 CONSTRAINT [PK_tCharacterTitle] PRIMARY KEY NONCLUSTERED 
(
	[nNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA],
 CONSTRAINT [IX_tCharacterTitle] UNIQUE CLUSTERED 
(
	[nCharNo] ASC,
	[nType] ASC,
	[nElementNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tChatBlock]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tChatBlock](
	[nUserNo] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tChatBlock_CL] PRIMARY KEY CLUSTERED 
(
	[nUserNo] ASC,
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCollectionCard]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCollectionCard](
	[nCharNo] [int] NOT NULL,
	[nCardID] [int] NOT NULL,
	[nSerialNo] [bigint] NOT NULL,
	[nStarNumber] [tinyint] NOT NULL,
	[dDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nCardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCollectionFavorites]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCollectionFavorites](
	[nCharNo] [int] NOT NULL,
	[nSlotNo] [tinyint] NOT NULL,
	[nCardID] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nSlotNo] ASC,
	[nCardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCollectionReward]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCollectionReward](
	[nCharNo] [int] NOT NULL,
	[nReward] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nReward] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tCollectionSerialCount]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCollectionSerialCount](
	[nCardID] [int] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nCount] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tDice]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDice](
	[nGameNo] [int] IDENTITY(1,1) NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nItemKey] [bigint] NOT NULL,
	[sDice] [nvarchar](3) NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tDice_NC] PRIMARY KEY NONCLUSTERED 
(
	[nGameNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tDiceLog]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDiceLog](
	[nGameNo] [int] NOT NULL,
	[nCustomCharNo] [int] NOT NULL,
	[nSlotNo] [tinyint] NOT NULL,
	[nBattingMoney] [bigint] NOT NULL,
	[nBattingRate] [int] NOT NULL,
	[bStatus] [bit] NOT NULL,
	[nTaxRate] [int] NOT NULL,
	[nDividend] [bigint] NOT NULL,
 CONSTRAINT [PK_tDiceLog_CL] PRIMARY KEY CLUSTERED 
(
	[nGameNo] ASC,
	[nCustomCharNo] ASC,
	[nSlotNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tDicePayLog]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDicePayLog](
	[nCharNo] [int] NOT NULL,
	[nItemKey] [bigint] NOT NULL,
	[nTotalDividend] [bigint] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tDicePayLog_CL] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nItemKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tDiceResult]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDiceResult](
	[nGameNo] [int] NOT NULL,
	[nCustomCharNo] [int] NOT NULL,
	[nBeforeMoney] [bigint] NOT NULL,
	[nTotalDividend] [bigint] NOT NULL,
	[nAfterMoney] [bigint] NOT NULL,
 CONSTRAINT [PK_tDiceResult_CL] PRIMARY KEY CLUSTERED 
(
	[nGameNo] ASC,
	[nCustomCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tFriend]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tFriend](
	[nCharNo] [int] NOT NULL,
	[nFriendCharNo] [int] NOT NULL,
	[nFlags] [tinyint] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tFriend_CL] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nFriendCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tGambleCoin]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleCoin](
	[nCharNo] [int] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nCoin] [bigint] NOT NULL,
	[dDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGambleCoinExchangeLog]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleCoinExchangeLog](
	[nLogNo] [bigint] IDENTITY(1,1) NOT NULL,
	[nCharNo] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nExchangedCoin] [bigint] NOT NULL,
	[nStatus] [tinyint] NOT NULL,
	[nBeforeCoin] [bigint] NOT NULL,
	[nAfterCoin] [bigint] NOT NULL,
	[nBeforeMoney] [bigint] NOT NULL,
	[nAfterMoney] [bigint] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nLogNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGambleDice]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleDice](
	[nGameNo] [int] IDENTITY(1,1) NOT NULL,
	[sDice] [nvarchar](3) NOT NULL,
	[dDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nGameNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGambleDiceLog]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleDiceLog](
	[nGameNo] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nSlotNo] [tinyint] NOT NULL,
	[nBattingMoney] [bigint] NOT NULL,
	[nBattingRate] [int] NOT NULL,
	[bStatus] [bit] NOT NULL,
	[nTaxRate] [int] NOT NULL,
	[nDividend] [bigint] NOT NULL,
	[dDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nGameNo] ASC,
	[nCharNo] ASC,
	[nSlotNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGambleDiceResult]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleDiceResult](
	[nGameNo] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nBeforeMoney] [bigint] NOT NULL,
	[nTotalDividend] [bigint] NOT NULL,
	[nAfterMoney] [bigint] NOT NULL,
	[dDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nGameNo] ASC,
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGambleJackpot]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleJackpot](
	[sGameName] [nvarchar](20) NOT NULL,
	[nBattingUnit] [bigint] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NOT NULL,
	[nJackpotCoin] [bigint] NOT NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGambleJackpotLog]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleJackpotLog](
	[nLogNo] [bigint] IDENTITY(1,1) NOT NULL,
	[sGameName] [nvarchar](20) NOT NULL,
	[nBattingUnit] [bigint] NOT NULL,
	[sID] [nvarchar](80) NOT NULL,
	[nJackpotCoin] [bigint] NOT NULL,
	[nGameNo] [bigint] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nLogNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGambleSlotMachineLog]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGambleSlotMachineLog](
	[nGameNo] [bigint] IDENTITY(1,1) NOT NULL,
	[nCharNo] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nBattingUnit] [bigint] NOT NULL,
	[nBatting] [tinyint] NOT NULL,
	[sGameResult] [nvarchar](10) NOT NULL,
	[bIsWin] [bit] NOT NULL,
	[nPrizeCoin] [bigint] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nGameNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGameOptionInit]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGameOptionInit](
	[nOptionNo] [smallint] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nDefaultValue] [tinyint] NULL,
	[bIsActive] [bit] NOT NULL,
	[sDescription] [nvarchar](100) NULL,
	[sDescriptionLocal] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[nOptionNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuild]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuild](
	[nNo] [int] IDENTITY(1,1) NOT NULL,
	[sName] [nvarchar](18) NOT NULL,
	[sPassword] [nvarchar](10) NOT NULL,
	[nMaxMembers] [smallint] NOT NULL,
	[nMoney] [bigint] NOT NULL,
	[nType] [tinyint] NOT NULL,
	[nGrade] [tinyint] NOT NULL,
	[nFame] [int] NOT NULL,
	[nStoneLevel] [smallint] NOT NULL,
	[nExp] [bigint] NOT NULL,
	[dCreateDate] [datetime] NOT NULL,
	[dWarRequestDate] [datetime] NOT NULL,
	[dWarStartDate] [datetime] NOT NULL,
	[dWarEndDate] [datetime] NOT NULL,
	[nWarEnemy] [int] NOT NULL,
	[nWarWinCount] [int] NOT NULL,
	[nWarLoseCount] [int] NOT NULL,
	[nWarDrawCount] [int] NOT NULL,
	[nDismissStatus] [tinyint] NOT NULL,
	[dDismissDate] [datetime] NOT NULL,
	[sIntro] [nvarchar](128) NOT NULL,
	[dNotifyDate] [datetime] NOT NULL,
	[sNotifyCharID] [nvarchar](17) NOT NULL,
	[sNotify] [nvarchar](512) NOT NULL,
 CONSTRAINT [PK__tGuild__41B8C09B] PRIMARY KEY CLUSTERED 
(
	[nNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tGuildAcademy]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildAcademy](
	[nNo] [int] NOT NULL,
	[nMasterCharNo] [int] NOT NULL,
	[nMaxMember] [smallint] NOT NULL,
	[nMoney] [bigint] NOT NULL,
	[nPoint] [int] NOT NULL,
	[dBuffEndTime] [datetime] NULL,
	[dNotifyDate] [datetime] NULL,
	[sNotify] [nvarchar](512) NULL,
	[sIntro] [nvarchar](128) NULL,
 CONSTRAINT [PK_tGuildAcademy_CL] PRIMARY KEY CLUSTERED 
(
	[nNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildAcademyGraduate]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildAcademyGraduate](
	[nNo] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[sID] [nvarchar](40) NOT NULL,
	[nClass] [tinyint] NOT NULL,
	[nJoinLevel] [smallint] NOT NULL,
	[dJoinDate] [datetime] NOT NULL,
	[nGraduateLevel] [smallint] NOT NULL,
	[dGraduateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tGuildAcademyGraduate_CL] PRIMARY KEY CLUSTERED 
(
	[nNo] ASC,
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildAcademyMember]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildAcademyMember](
	[nNo] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nJoinLevel] [smallint] NOT NULL,
	[dJoinDate] [datetime] NOT NULL,
	[nGrade] [tinyint] NULL,
	[sIntro] [nvarchar](128) NULL,
 CONSTRAINT [PK_tGuildAcademyMember_CL] PRIMARY KEY CLUSTERED 
(
	[nNo] ASC,
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildAcademyRanking]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildAcademyRanking](
	[nRowNo] [int] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nRanking] [int] NOT NULL,
	[nGuildNo] [int] NOT NULL,
	[sGuildName] [nvarchar](18) NOT NULL,
	[nMasterNo] [int] NOT NULL,
	[sMasterID] [nvarchar](40) NOT NULL,
	[nPoint] [int] NOT NULL,
	[nCount] [int] NOT NULL,
 CONSTRAINT [PK_tGuildAcademyRanking_NC] PRIMARY KEY NONCLUSTERED 
(
	[nRowNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildAcademyReward]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildAcademyReward](
	[nNo] [int] NOT NULL,
	[nLevel] [smallint] NOT NULL,
	[nTypeID] [tinyint] NOT NULL,
	[nValue] [bigint] NULL,
	[nItemID] [int] NULL,
 CONSTRAINT [PK_tGuildAcademyReward_CL] PRIMARY KEY CLUSTERED 
(
	[nNo] ASC,
	[nLevel] ASC,
	[nTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildAcademyRewardType]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildAcademyRewardType](
	[nTypeID] [tinyint] NOT NULL,
	[sTypeName] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_tGuildAcademyRewardType_CL] PRIMARY KEY CLUSTERED 
(
	[nTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildMember]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildMember](
	[nNo] [int] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[dJoinDate] [datetime] NOT NULL,
	[nGrade] [tinyint] NOT NULL,
	[nFlags] [int] NOT NULL,
	[nExpRatio] [smallint] NOT NULL,
	[sIntro] [nvarchar](64) NOT NULL,
 CONSTRAINT [PK__tGuildMember__1FCDBCEB] PRIMARY KEY CLUSTERED 
(
	[nNo] ASC,
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA],
 CONSTRAINT [IX_tGuildMember] UNIQUE NONCLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tGuildToken]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildToken](
	[nNo] [int] NOT NULL,
	[nToken] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildTournament]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildTournament](
	[nGTNo] [int] NOT NULL,
	[nType] [smallint] NOT NULL,
	[nData] [int] NOT NULL,
 CONSTRAINT [PK_tGuildTournament] PRIMARY KEY CLUSTERED 
(
	[nGTNo] ASC,
	[nType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tGuildTournamentRegist]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGuildTournamentRegist](
	[nGTNo] [int] NOT NULL,
	[nNo] [int] NOT NULL,
	[nMoney] [bigint] NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nSeed] [int] NOT NULL,
	[bChoice] [bit] NOT NULL,
 CONSTRAINT [PK_tGuildTournamentRegist_CL] PRIMARY KEY CLUSTERED 
(
	[nGTNo] ASC,
	[nNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tHolyPromise]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tHolyPromise](
	[nCharNo] [int] NOT NULL,
	[nUpCharNo] [int] NOT NULL,
	[nFlags] [tinyint] NOT NULL,
	[dRegDate] [datetime] NOT NULL,
	[dRejoinableDate] [datetime] NOT NULL,
	[dMemberAcceptableDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tHolyPromise] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nUpCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tHolyPromiseReward]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tHolyPromiseReward](
	[nCharNo] [int] NOT NULL,
	[nSeqNo] [int] NOT NULL,
	[nType] [tinyint] NOT NULL,
	[nMoney] [bigint] NOT NULL,
	[nRemainMoney] [bigint] NOT NULL,
	[nReceiptCharNo] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tHolyPromiseReward] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nSeqNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tInGameGuide]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tInGameGuide](
	[nCharNo] [int] NOT NULL,
	[nGuideNo] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tInstanceDungeonCount]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tInstanceDungeonCount](
	[nCharNo] [int] NOT NULL,
	[sIDGroup] [nvarchar](50) NOT NULL,
	[nCount] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[sIDGroup] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tItem]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItem](
	[nItemKey] [bigint] NOT NULL,
	[nStorageType] [tinyint] NOT NULL,
	[nOwner] [bigint] NOT NULL,
	[nStorage] [smallint] NOT NULL,
	[nItemID] [int] NOT NULL,
	[nFlags] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK__tItem__1D121728] PRIMARY KEY CLUSTERED 
(
	[nItemKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tItemActionCoolTime]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItemActionCoolTime](
	[nCharNo] [int] NOT NULL,
	[nActionGroup] [smallint] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[dEndTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nActionGroup] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tItemEnchant]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItemEnchant](
	[nItemKey] [bigint] NOT NULL,
	[nSlot] [tinyint] NOT NULL,
	[nGemItemID] [int] NOT NULL,
	[nRestCount] [tinyint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nItemKey] ASC,
	[nSlot] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tItemMobList]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItemMobList](
	[nItemKey] [bigint] NOT NULL,
	[nMobID] [smallint] NOT NULL,
	[nGrade] [tinyint] NOT NULL,
	[nKillCount] [int] NOT NULL,
 CONSTRAINT [PK__tItemMobList__2724C5F0] PRIMARY KEY CLUSTERED 
(
	[nItemKey] ASC,
	[nMobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tItemOptions]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItemOptions](
	[nItemKey] [bigint] NOT NULL,
	[nOptionType] [smallint] NOT NULL,
	[nOptionData] [bigint] NOT NULL,
 CONSTRAINT [PK__tItemOptions__290D0E62] PRIMARY KEY CLUSTERED 
(
	[nItemKey] ASC,
	[nOptionType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tItemStorageFindEmpty]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItemStorageFindEmpty](
	[nStorage] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nStorage] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tItemTitle]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItemTitle](
	[nItemKey] [bigint] NOT NULL,
	[nTitleType] [smallint] NOT NULL,
	[sTitle] [nvarchar](24) NOT NULL,
	[nTitleCharNo] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nItemKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tKeyMapInit]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tKeyMapInit](
	[nFunctionNo] [smallint] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nExtendedKey] [tinyint] NULL,
	[nASCIICode] [tinyint] NOT NULL,
	[sDescription] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED 
(
	[nFunctionNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tKingdomQuest]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tKingdomQuest](
	[nQuestNo] [smallint] NOT NULL,
	[sData] [varchar](256) NOT NULL,
 CONSTRAINT [PK_tKingdomQuest] PRIMARY KEY CLUSTERED 
(
	[nQuestNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tLevelCatchUpRank]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tLevelCatchUpRank](
	[sID] [nvarchar](40) NOT NULL,
	[GuildName] [nvarchar](18) NULL,
	[nClass] [tinyint] NOT NULL,
	[nLevel] [smallint] NOT NULL,
	[dLastUpdated] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[sID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tLog_GuildMasterChange]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tLog_GuildMasterChange](
	[nRowNo] [bigint] IDENTITY(1,1) NOT NULL,
	[nGuildNo] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nOldGuildMaster] [int] NOT NULL,
	[nNewGuildMaster] [int] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nRowNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tMarriage]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tMarriage](
	[nCharNo] [int] NOT NULL,
	[nPartnerCharNo] [int] NOT NULL,
	[nStatus] [tinyint] NOT NULL,
	[nSubStatus] [tinyint] NOT NULL,
	[dWhen] [datetime] NOT NULL,
	[dSubWhen] [datetime] NOT NULL,
	[nMarriageCount] [tinyint] NOT NULL,
	[nDivorceCancel] [tinyint] NOT NULL,
 CONSTRAINT [PK_tMarriage] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nPartnerCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tMasterPupils]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tMasterPupils](
	[nPupils] [int] NOT NULL,
	[nMaster] [int] NOT NULL,
	[nFlags] [int] NOT NULL,
	[dRegDate] [datetime] NOT NULL,
 CONSTRAINT [PK__tMasterPupils__57A801BA] PRIMARY KEY CLUSTERED 
(
	[nMaster] ASC,
	[nPupils] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA],
 CONSTRAINT [IX_tMasterPupils] UNIQUE NONCLUSTERED 
(
	[nPupils] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tMinihouse]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tMinihouse](
	[nCharNo] [int] NOT NULL,
	[nVisitor] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tMinihouse_CL] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tMinihouseGate]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tMinihouseGate](
	[nCharNo] [int] NOT NULL,
	[nItemID] [int] NOT NULL,
	[bType] [tinyint] NOT NULL,
	[dDate] [datetime] NOT NULL,
	[dEndDate] [datetime] NULL,
 CONSTRAINT [PK_tMinihouseGate_CL] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tMinihouseGuest]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tMinihouseGuest](
	[nArticleNo] [int] IDENTITY(1,1) NOT NULL,
	[nCharNo] [int] NOT NULL,
	[nWriterNo] [int] NOT NULL,
	[sWriterID] [nvarchar](40) NOT NULL,
	[sContent] [nvarchar](1000) NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tMinihouseGuest_NC] PRIMARY KEY NONCLUSTERED 
(
	[nArticleNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tParty]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tParty](
	[nFlags] [int] NOT NULL,
	[nMasterCharNo] [int] NOT NULL,
	[nPartyNo] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nPartyNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tPartyMember]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tPartyMember](
	[nCharNo] [int] NOT NULL,
	[nFlag] [tinyint] NOT NULL,
	[nPartyNo] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nPartyNo] ASC,
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tPet]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tPet](
	[nStorage] [smallint] NOT NULL,
	[nPetNumber] [tinyint] NOT NULL,
	[nOwner] [bigint] NOT NULL,
	[nPetKey] [bigint] NOT NULL,
	[nPetID] [int] NOT NULL,
	[sInfo] [varchar](32) NOT NULL,
	[nFlags] [int] NOT NULL,
	[nAssignName] [nvarchar](24) NOT NULL,
	[nLevel] [smallint] NOT NULL,
	[nExp] [bigint] NOT NULL,
	[nSatiety] [tinyint] NOT NULL,
	[nSpeed] [tinyint] NOT NULL,
	[nAbilityType] [tinyint] NOT NULL,
	[nAbilityValue] [tinyint] NOT NULL,
	[nSkinColor] [tinyint] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK__tPet__6D9742D9] PRIMARY KEY CLUSTERED 
(
	[nPetKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tPetSkill]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tPetSkill](
	[nSkillLevel] [tinyint] NOT NULL,
	[nSkillNo] [int] NOT NULL,
	[nSkillExp] [int] NOT NULL,
	[nPetKey] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nPetKey] ASC,
	[nSkillNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tPrison]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tPrison](
	[nPrisonNum] [bigint] IDENTITY(1,1) NOT NULL,
	[nCharID] [int] NOT NULL,
	[dPrisonDate] [datetime] NOT NULL,
	[nMinute] [smallint] NOT NULL,
	[sReason] [nvarchar](16) NULL,
	[nRegNumber] [bigint] NULL,
	[sGmID] [nvarchar](16) NULL,
	[sRemark] [nvarchar](64) NULL,
 CONSTRAINT [PK_tPrison] PRIMARY KEY NONCLUSTERED 
(
	[nPrisonNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tQuest]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tQuest](
	[nCharNo] [int] NOT NULL,
	[nQuestNo] [int] NOT NULL,
	[nStatus] [tinyint] NOT NULL,
	[sData] [varbinary](100) NOT NULL,
 CONSTRAINT [PK__tQuest__3E52440B] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nQuestNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tQuest_63009]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tQuest_63009](
	[nCharNo] [int] NOT NULL,
	[nQuestNo] [int] NOT NULL,
	[nStatus] [tinyint] NOT NULL,
	[sData] [varbinary](100) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tQuestTimes]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tQuestTimes](
	[nCharNo] [int] NOT NULL,
	[nQuestNo] [int] NOT NULL,
	[nTimes] [int] NOT NULL,
	[dLastComplete] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nCharNo] ASC,
	[nQuestNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tRestExp]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tRestExp](
	[nCharNo] [int] NOT NULL,
	[dRestExpLastExcuteTime] [datetime] NOT NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tSkill]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tSkill](
	[nCharNo] [int] NOT NULL,
	[nSkillNo] [smallint] NOT NULL,
	[nSkillLevel] [tinyint] NOT NULL,
	[nSkillExp] [int] NOT NULL,
	[nSkillWriteTime] [bigint] NOT NULL,
	[nSkillCoolTime] [int] NOT NULL,
	[nSkillPowerDemage] [tinyint] NOT NULL,
	[nSkillPowerSP] [tinyint] NOT NULL,
	[nSkillPowerKeepTime] [tinyint] NOT NULL,
	[nSkillPowerCoolTime] [tinyint] NOT NULL,
 CONSTRAINT [PK__tSkillCharList__7814D14C] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC,
	[nSkillNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[tSpamBlockTime]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tSpamBlockTime](
	[nCharNo] [int] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[nBlockTime] [int] NOT NULL,
	[nStandbyTime] [int] NOT NULL,
 CONSTRAINT [PK_tSpamBlockTime_CL] PRIMARY KEY CLUSTERED 
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tSpamFilter]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tSpamFilter](
	[nUserNo] [int] NOT NULL,
	[sys_create_date] [datetime] NOT NULL,
	[sys_update_date] [datetime] NULL,
	[bIsSpamSpread] [bit] NOT NULL,
	[bIsSpamBlock] [bit] NOT NULL,
	[nSpamFilterRate] [tinyint] NOT NULL,
 CONSTRAINT [PK_tSpamFilter_CL] PRIMARY KEY CLUSTERED 
(
	[nUserNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tStatisticsGameMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tStatisticsGameMoney](
	[nRowNo] [bigint] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL,
	[sCode] [nvarchar](20) NOT NULL,
	[nMoney] [bigint] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[nRowNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tUserMoney]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tUserMoney](
	[nUserNo] [int] NOT NULL,
	[nUserMoney] [bigint] NOT NULL,
 CONSTRAINT [PK_tUserMoney] PRIMARY KEY CLUSTERED 
(
	[nUserNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO
/****** Object:  Table [dbo].[Waits_Sysprocesses]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Waits_Sysprocesses](
	[logdate] [datetime] NOT NULL,
	[spid] [smallint] NOT NULL,
	[Wait Time (s)] [numeric](17, 6) NULL,
	[lastwaittype] [nchar](32) NOT NULL,
	[waitresource] [nchar](256) NOT NULL,
	[dbid] [smallint] NOT NULL,
	[uid] [smallint] NOT NULL,
	[cpu] [int] NOT NULL,
	[physical_io] [bigint] NOT NULL,
	[memusage] [int] NOT NULL,
	[last_batch] [datetime] NOT NULL,
	[open_tran] [smallint] NOT NULL,
	[sid] [binary](86) NOT NULL,
	[hostname] [nchar](128) NOT NULL,
	[program_name] [nchar](128) NOT NULL,
	[nt_username] [nchar](128) NOT NULL,
	[loginame] [nchar](128) NOT NULL,
	[sql_text] [varchar](8000) NULL
) ON [DATA]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[VT_CHAR]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 뷰 dbo.VT_CHAR    스크립트 날짜: 2007-03-13 오후 6:25:09 ******/


CREATE VIEW [dbo].[VT_CHAR]
AS
SELECT   dbo.tCharacter.nCharNo AS CharNo, dbo.tCharacter.sID AS CharID, 
                dbo.tCharacter.nUserNo AS UserNo, dbo.tCharacter.nAdminLevel AS AdminLevel, 
                dbo.tCharacter.nPS AS PS, dbo.tCharacter.nGS AS GS, 
                dbo.tCharacter.dCreateDate AS CreateDate, dbo.tCharacter.bDeleted AS IsDel, 
                dbo.tCharacter.nLoginZoneX AS X, dbo.tCharacter.nLoginZoneY AS Y, 
                dbo.tCharacter.dDeletedDate AS DeletedDate, dbo.tCharacter.nAP AS AP, 
                dbo.tCharacter.sLoginZone AS Zone, dbo.tCharacter.nLevel AS Lvl, 
                dbo.tCharacter.nExp AS Experience, dbo.tCharacter.nSlotNo AS SlotNo, 
                dbo.tCharacter.nHP AS HP, dbo.tCharacter.nSP AS SP, 
                dbo.tCharacter.nHPS AS HPS, dbo.tCharacter.nSPS AS SPS, 
                dbo.tCharacter.dLastLoginDate AS LogDate, 
                dbo.tCharacter.nLoginCount AS LogCount, dbo.tCharacter.nCreateWorld AS World, 
                dbo.tCharacter.nPlayMin AS PlayTime, dbo.tCharacter.nFlags AS Flags, 
                dbo.tCharacter.nMoney AS Money, dbo.tCharacterShape.nClass AS Class, 
                dbo.tCharacterShape.nRace AS Race, dbo.tCharacterShape.nGender AS Sex, 
                dbo.tCharacterShape.nHairType AS HairType, 
                dbo.tCharacterShape.nHairColor AS HairColor, 
                dbo.tCharacterShape.nFaceShape AS Face
FROM      dbo.tCharacter INNER JOIN
                dbo.tCharacterShape ON dbo.tCharacter.nCharNo = dbo.tCharacterShape.nCharNo

GO
/****** Object:  View [dbo].[VT_ITEM]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 뷰 dbo.VT_ITEM    스크립트 날짜: 2007-03-13 오후 6:25:09 ******/



CREATE  VIEW [dbo].[VT_ITEM]
AS
SELECT   TOP 100 PERCENT dbo.tItem.nOwner AS Owner, 
                dbo.tItem.nStorageType AS StorageType, dbo.tItem.nStorage AS Storage, 
                dbo.tItem.nItemKey AS Itemkey, dbo.tItem.nItemID AS ItemID, 
                dbo.tItemOptions.nOptionType AS OptionType, 
                dbo.tItemOptions.nOptionData AS OptionData, dbo.tItemMobList.nMobID AS MobID, 
                dbo.tItemMobList.nGrade AS MobGrade, dbo.tItemMobList.nKillCount AS MobKillCount, 
                dbo.tItemTitle.nTitleType AS TitleType, dbo.tItemTitle.sTitle AS TitleName, 
                dbo.tItemTitle.nTitleCharNo AS TitleCreator, dbo.tItem.dDate AS CreateDate, 
                dbo.tItem.nFlags AS Flags
FROM      dbo.tItem LEFT OUTER JOIN
                dbo.tItemMobList ON 
                dbo.tItem.nItemKey = dbo.tItemMobList.nItemKey LEFT OUTER JOIN
                dbo.tItemTitle ON dbo.tItem.nItemKey = dbo.tItemTitle.nItemKey LEFT OUTER JOIN
                dbo.tItemOptions ON dbo.tItem.nItemKey = dbo.tItemOptions.nItemKey
GROUP BY dbo.tItem.nOwner, dbo.tItem.nStorageType, dbo.tItem.nStorage, 
                dbo.tItem.nItemKey, dbo.tItem.nItemID, dbo.tItemOptions.nOptionType, 
                dbo.tItemOptions.nOptionData, dbo.tItemMobList.nMobID, 
                dbo.tItemMobList.nGrade, dbo.tItemMobList.nKillCount, dbo.tItemTitle.nTitleType, 
                dbo.tItemTitle.sTitle, dbo.tItemTitle.nTitleCharNo, dbo.tItem.dDate, 
                dbo.tItem.nFlags
ORDER BY dbo.tItem.nOwner, dbo.tItem.nStorageType, dbo.tItem.nStorage, 
                dbo.tItem.nItemKey, dbo.tItem.nItemID, dbo.tItemOptions.nOptionType, 
                dbo.tItemOptions.nOptionData, dbo.tItemMobList.nMobID, 
                dbo.tItemMobList.nGrade, dbo.tItemMobList.nKillCount, dbo.tItemTitle.nTitleType, 
                dbo.tItemTitle.sTitle, dbo.tItemTitle.nTitleCharNo, dbo.tItem.dDate, 
                dbo.tItem.nFlags

GO
/****** Object:  View [dbo].[VT_ITEM_BRIF]    Script Date: 8/12/2015 5:06:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 뷰 dbo.VT_ITEM_BRIF    스크립트 날짜: 2007-03-13 오후 6:25:09 ******/


CREATE VIEW [dbo].[VT_ITEM_BRIF]
AS
SELECT   TOP 100 PERCENT dbo.tItem.nOwner AS 소유자, 
                dbo.tItem.nStorageType AS 인벤종류, dbo.tItem.nStorage AS 인벤위치, 
                dbo.tItem.nItemKey AS 아템키, dbo.tItem.nItemID AS 아템아뒤, 
                dbo.tItemTitle.nTitleType AS 타이틀타입, dbo.tItemTitle.sTitle AS 타이틀, 
                dbo.tItemTitle.nTitleCharNo AS 타이틀부여자, dbo.tItem.dDate AS 만든날, 
                dbo.tItem.nFlags AS 예비정보
FROM      dbo.tItem LEFT OUTER JOIN
                dbo.tItemTitle ON dbo.tItem.nItemKey = dbo.tItemTitle.nItemKey
ORDER BY dbo.tItem.nOwner, dbo.tItem.nStorageType, dbo.tItem.nStorage, 
                dbo.tItem.nItemKey, dbo.tItem.nItemID, dbo.tItemTitle.nTitleType, 
                dbo.tItemTitle.sTitle, dbo.tItemTitle.nTitleCharNo, dbo.tItem.dDate, 
                dbo.tItem.nFlags

GO
/****** Object:  Index [IX_tCharacterChangeID_CL]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE CLUSTERED INDEX [IX_tCharacterChangeID_CL] ON [dbo].[tCharacterChangeID]
(
	[nCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_tCharacterChargedBuffer]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE CLUSTERED INDEX [IX_tCharacterChargedBuffer] ON [dbo].[tCharacterChargedBuffer]
(
	[nCharNo] ASC,
	[nKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
GO
/****** Object:  Index [IX_tDice_CL]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE CLUSTERED INDEX [IX_tDice_CL] ON [dbo].[tDice]
(
	[nCharNo] ASC,
	[nItemKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_tGuildAcademyRanking_CL]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE CLUSTERED INDEX [IX_tGuildAcademyRanking_CL] ON [dbo].[tGuildAcademyRanking]
(
	[dDate] ASC,
	[nRanking] ASC,
	[nGuildNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IDX_nCharID_dPrsonDateDesc]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE CLUSTERED INDEX [IDX_nCharID_dPrsonDateDesc] ON [dbo].[tPrison]
(
	[nCharID] ASC,
	[dPrisonDate] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_tCharacter_sID_NC]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_tCharacter_sID_NC] ON [dbo].[tCharacter]
(
	[sID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [XAK1tCharacter_ID]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [XAK1tCharacter_ID] ON [dbo].[tCharacter]
(
	[sID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [DATA]
GO
/****** Object:  Index [XIE1tCharacter_UserNo]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE NONCLUSTERED INDEX [XIE1tCharacter_UserNo] ON [dbo].[tCharacter]
(
	[nUserNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [DATA]
GO
/****** Object:  Index [IX_nFriendCharNo]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE NONCLUSTERED INDEX [IX_nFriendCharNo] ON [dbo].[tFriend]
(
	[nFriendCharNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
GO
/****** Object:  Index [IX_tFriend]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE NONCLUSTERED INDEX [IX_tFriend] ON [dbo].[tFriend]
(
	[nFlags] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_tGuild]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_tGuild] ON [dbo].[tGuild]
(
	[sName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_tGuild_Name_NC]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_tGuild_Name_NC] ON [dbo].[tGuild]
(
	[sName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_tGuildTournament]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE NONCLUSTERED INDEX [IX_tGuildTournament] ON [dbo].[tGuildTournament]
(
	[nGTNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [XAK1tItem_Owner_StorageType_Pos]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [XAK1tItem_Owner_StorageType_Pos] ON [dbo].[tItem]
(
	[nOwner] ASC,
	[nStorageType] ASC,
	[nStorage] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [DATA]
GO
/****** Object:  Index [IX_tQuest]    Script Date: 8/12/2015 5:06:59 PM ******/
CREATE NONCLUSTERED INDEX [IX_tQuest] ON [dbo].[tQuest]
(
	[nCharNo] ASC,
	[nStatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
GO
ALTER TABLE [dbo].[auditCharacterData] ADD  DEFAULT (newid()) FOR [audit_log_id]
GO
ALTER TABLE [dbo].[auditCharacterData] ADD  DEFAULT (suser_sname()) FOR [audit_user]
GO
ALTER TABLE [dbo].[auditCharacterData] ADD  DEFAULT (host_name()) FOR [audit_host]
GO
ALTER TABLE [dbo].[auditCharacterData] ADD  DEFAULT (app_name()) FOR [audit_application]
GO
ALTER TABLE [dbo].[auditCharacterData] ADD  DEFAULT (getdate()) FOR [audit_changed]
GO
ALTER TABLE [dbo].[tArenaChallenge] ADD  CONSTRAINT [DF__tArenaCha__nMons__793DFFAF]  DEFAULT ((0)) FOR [nMonsterLevel]
GO
ALTER TABLE [dbo].[tArenaChallenge] ADD  CONSTRAINT [DF__tArenaCha__nMons__7A3223E8]  DEFAULT ((0)) FOR [nMonsterMKC]
GO
ALTER TABLE [dbo].[tArenaChallenge] ADD  CONSTRAINT [DF__tArenaCha__nMons__7B264821]  DEFAULT ((0)) FOR [nMonsterKPS]
GO
ALTER TABLE [dbo].[tArenaChallenge] ADD  CONSTRAINT [DF__tArenaCha__nMons__7C1A6C5A]  DEFAULT ((0)) FOR [nMonsterKills]
GO
ALTER TABLE [dbo].[tArenaFBZ] ADD  CONSTRAINT [DF__tArenaFre__nFBZW__7EF6D905]  DEFAULT ((0)) FOR [nFBZWin]
GO
ALTER TABLE [dbo].[tArenaFBZ] ADD  CONSTRAINT [DF__tArenaFre__nFBZL__7FEAFD3E]  DEFAULT ((0)) FOR [nFBZLose]
GO
ALTER TABLE [dbo].[tArenaFBZ] ADD  CONSTRAINT [DF__tArenaFre__nFBZL__00DF2177]  DEFAULT ((1000)) FOR [nFBZLadder]
GO
ALTER TABLE [dbo].[tArenaGuild] ADD  CONSTRAINT [DF__tArenaGui__nGuil__03BB8E22]  DEFAULT ((0)) FOR [nGuildLose]
GO
ALTER TABLE [dbo].[tArenaGuild] ADD  CONSTRAINT [DF__tArenaGui__nGuil__04AFB25B]  DEFAULT ((0)) FOR [nGuildDraw]
GO
ALTER TABLE [dbo].[tArenaGuild] ADD  CONSTRAINT [DF__tArenaGui__nGuil__05A3D694]  DEFAULT ((0)) FOR [nGuildWin]
GO
ALTER TABLE [dbo].[tArenaGuild] ADD  CONSTRAINT [DF__tArenaGui__nGuil__0697FACD]  DEFAULT ((1000)) FOR [nGuildLadder]
GO
ALTER TABLE [dbo].[tArenaPvP] ADD  CONSTRAINT [DF__tArenaPvP__nPvPL__09746778]  DEFAULT ((1000)) FOR [nPvPLadder]
GO
ALTER TABLE [dbo].[tArenaPvP] ADD  CONSTRAINT [DF__tArenaPvP__nPvPW__0A688BB1]  DEFAULT ((0)) FOR [nPvPWin]
GO
ALTER TABLE [dbo].[tArenaPvP] ADD  CONSTRAINT [DF__tArenaPvP__nPvPL__0B5CAFEA]  DEFAULT ((0)) FOR [nPvPLose]
GO
ALTER TABLE [dbo].[tArenaPvP] ADD  CONSTRAINT [DF__tArenaPvP__nPvPD__0C50D423]  DEFAULT ((0)) FOR [nPvPDraw]
GO
ALTER TABLE [dbo].[tAuction] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tAuction] ADD  DEFAULT (getdate()) FOR [dSellStartDate]
GO
ALTER TABLE [dbo].[tAuction] ADD  DEFAULT ((0)) FOR [nStatus]
GO
ALTER TABLE [dbo].[tAuctionCategory] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tAuctionCategory] ADD  DEFAULT ((0)) FOR [bIsActive]
GO
ALTER TABLE [dbo].[tAuctionInventory] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tAuctionInventory] ADD  DEFAULT (getdate()) FOR [dStartDate]
GO
ALTER TABLE [dbo].[tAuctionInventory] ADD  DEFAULT ((0)) FOR [bIsDelete]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nAdmi__0F2D40CE]  DEFAULT ((0)) FOR [nAdminLevel]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacter__nAEP__10216507]  DEFAULT ((0)) FOR [nPS]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacter__nDEP__11158940]  DEFAULT ((0)) FOR [nGS]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__dCrea__1209AD79]  DEFAULT (getdate()) FOR [dCreateDate]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__bDele__12FDD1B2]  DEFAULT ((0)) FOR [bDeleted]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nLogi__13F1F5EB]  DEFAULT ((5963)) FOR [nLoginZoneX]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nLogi__14E61A24]  DEFAULT ((9793)) FOR [nLoginZoneY]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nLogi__15DA3E5D]  DEFAULT ((90)) FOR [nLoginZoneD]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nPKYe__16CE6296]  DEFAULT ((0)) FOR [nPKYellowTime]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_dDeletedDate]  DEFAULT (getdate()) FOR [dDeletedDate]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacter__nAP__17C286CF]  DEFAULT ((0)) FOR [nAP]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__sLogi__18B6AB08]  DEFAULT ('Rou') FOR [sLoginZone]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nPKCo__19AACF41]  DEFAULT ((0)) FOR [nPKCount]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nLeve__1A9EF37A]  DEFAULT ((1)) FOR [nLevel]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacter__nExp__1B9317B3]  DEFAULT ((0)) FOR [nExp]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nSlot__1C873BEC]  DEFAULT ((0)) FOR [nSlotNo]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacter__nHP__1D7B6025]  DEFAULT ((100)) FOR [nHP]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacter__nSP__1E6F845E]  DEFAULT ((100)) FOR [nSP]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nHP2]  DEFAULT ((100)) FOR [nHPS]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nSP2]  DEFAULT ((100)) FOR [nSPS]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__dLast__1F63A897]  DEFAULT (getdate()) FOR [dLastLoginDate]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nLogi__2057CCD0]  DEFAULT ((0)) FOR [nLoginCount]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nCrea__214BF109]  DEFAULT ((0)) FOR [nCreateWorld]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nFame__22401542]  DEFAULT ((0)) FOR [nFame]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nPris__2334397B]  DEFAULT ((0)) FOR [nPrisonMin]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nPlay__24285DB4]  DEFAULT ((0)) FOR [nPlayMin]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF__tCharacte__nFlag__251C81ED]  DEFAULT ((0)) FOR [nFlags]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nMoney]  DEFAULT ((0)) FOR [nMoney]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nStrength]  DEFAULT ((0)) FOR [nStrength]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nConstitute]  DEFAULT ((0)) FOR [nConstitute]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nDexterity]  DEFAULT ((0)) FOR [nDexterity]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nIntelligence]  DEFAULT ((0)) FOR [nIntelligence]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nMentalPower]  DEFAULT ((0)) FOR [nMentalPower]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nRedistributePoint]  DEFAULT ((0)) FOR [nRedistributePoint]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nKQHandle]  DEFAULT ((-1)) FOR [nKQHandle]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_sLoginZone1]  DEFAULT ('') FOR [sKQMap]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nLoginZoneX1]  DEFAULT ((0)) FOR [nKQX]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nLoginZoneY1]  DEFAULT ((0)) FOR [nKQY]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_nLoginZoneD1]  DEFAULT ((90)) FOR [nKQD]
GO
ALTER TABLE [dbo].[tCharacter] ADD  CONSTRAINT [DF_tCharacter_dKQDate]  DEFAULT (getdate()) FOR [dKQDate]
GO
ALTER TABLE [dbo].[tCharacterChangeID] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tCharacterChangeID] ADD  DEFAULT ((0)) FOR [bFlag]
GO
ALTER TABLE [dbo].[tCharacterChangeID] ADD  DEFAULT ((0)) FOR [bInit]
GO
ALTER TABLE [dbo].[tCharacterChargedBuffer] ADD  CONSTRAINT [DF_tCharacterChargedBuffer_nID]  DEFAULT ((0)) FOR [nID]
GO
ALTER TABLE [dbo].[tCharacterGameOption] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tCharacterKeyMap] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tCharacterLoading] ADD  CONSTRAINT [DF_tCharLoading_dDBTime]  DEFAULT (getdate()) FOR [dDBTime]
GO
ALTER TABLE [dbo].[tCharacterShape] ADD  CONSTRAINT [DF__tCharacte__nClas__2DB1C7EE]  DEFAULT ((0)) FOR [nClass]
GO
ALTER TABLE [dbo].[tCharacterShape] ADD  CONSTRAINT [DF__tCharacte__nRace__2AD55B43]  DEFAULT ((0)) FOR [nRace]
GO
ALTER TABLE [dbo].[tCharacterShape] ADD  CONSTRAINT [DF__tCharacte__nGend__2CBDA3B5]  DEFAULT ((0)) FOR [nGender]
GO
ALTER TABLE [dbo].[tCharacterShape] ADD  CONSTRAINT [DF__tCharacte__nHair__2EA5EC27]  DEFAULT ((0)) FOR [nHairType]
GO
ALTER TABLE [dbo].[tCharacterShape] ADD  CONSTRAINT [DF__tCharacte__nHair__2F9A1060]  DEFAULT ((0)) FOR [nHairColor]
GO
ALTER TABLE [dbo].[tCharacterShape] ADD  CONSTRAINT [DF__tCharacte__nEyet__308E3499]  DEFAULT ((0)) FOR [nFaceShape]
GO
ALTER TABLE [dbo].[tCharacterShortCut] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tCharacterShortCut] ADD  DEFAULT (getdate()) FOR [sys_update_date]
GO
ALTER TABLE [dbo].[tCharacterSingleOption] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tCharacterSingleOption] ADD  DEFAULT ((1)) FOR [bMysteryBolt]
GO
ALTER TABLE [dbo].[tCharacterSingleOption] ADD  DEFAULT ((0)) FOR [bAutoPick]
GO
ALTER TABLE [dbo].[tCharacterSingleOption] ADD  DEFAULT (getdate()) FOR [dRestExpLastExecuteTime]
GO
ALTER TABLE [dbo].[tCharacterTitle] ADD  CONSTRAINT [DF_tCharacterTitle_dDate]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tCharacterTitle] ADD  CONSTRAINT [DF_tCharacterTitle_nElementValue]  DEFAULT ((0)) FOR [nElementValue]
GO
ALTER TABLE [dbo].[tChatBlock] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tCollectionCard] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tCollectionFavorites] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tCollectionReward] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tCollectionSerialCount] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tDice] ADD  CONSTRAINT [DF__tDice__dDate__5614BF03]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tDicePayLog] ADD  CONSTRAINT [DF__tDicePayL__dDate__5EAA0504]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tFriend] ADD  CONSTRAINT [DF__tFriend__nFlags__373B3228]  DEFAULT ((0)) FOR [nFlags]
GO
ALTER TABLE [dbo].[tFriend] ADD  CONSTRAINT [DF_tFriend_dDate]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGambleCoin] ADD  DEFAULT ((0)) FOR [nCoin]
GO
ALTER TABLE [dbo].[tGambleCoin] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGambleCoinExchangeLog] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGambleCoinExchangeLog] ADD  DEFAULT ((0)) FOR [nBeforeCoin]
GO
ALTER TABLE [dbo].[tGambleCoinExchangeLog] ADD  DEFAULT ((0)) FOR [nAfterCoin]
GO
ALTER TABLE [dbo].[tGambleCoinExchangeLog] ADD  DEFAULT ((0)) FOR [nBeforeMoney]
GO
ALTER TABLE [dbo].[tGambleCoinExchangeLog] ADD  DEFAULT ((0)) FOR [nAfterMoney]
GO
ALTER TABLE [dbo].[tGambleDice] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGambleDiceLog] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGambleDiceResult] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGambleJackpot] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tGambleJackpot] ADD  DEFAULT (getdate()) FOR [sys_update_date]
GO
ALTER TABLE [dbo].[tGambleJackpot] ADD  DEFAULT ((0)) FOR [nJackpotCoin]
GO
ALTER TABLE [dbo].[tGambleJackpotLog] ADD  DEFAULT ((0)) FOR [nJackpotCoin]
GO
ALTER TABLE [dbo].[tGambleSlotMachineLog] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGambleSlotMachineLog] ADD  DEFAULT ((0)) FOR [nPrizeCoin]
GO
ALTER TABLE [dbo].[tGameOptionInit] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF__tGuild__nMaxMemb__3BFFE745]  DEFAULT ((15)) FOR [nMaxMembers]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF__tGuild__nGuildMo__3DE82FB7]  DEFAULT ((0)) FOR [nMoney]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_nType]  DEFAULT ((0)) FOR [nType]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF__tGuild__nGuildLe__40C49C62]  DEFAULT ((1)) FOR [nGrade]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_nFame]  DEFAULT ((0)) FOR [nFame]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF__tGuild__nGuildSt__3FD07829]  DEFAULT ((1)) FOR [nStoneLevel]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF__tGuild__nGuildEx__3EDC53F0]  DEFAULT ((0)) FOR [nExp]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF__tGuild__dCreateD__3CF40B7E]  DEFAULT (getdate()) FOR [dCreateDate]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_dWarRequestDate]  DEFAULT (getdate()) FOR [dWarRequestDate]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_dWarStartDate]  DEFAULT (getdate()) FOR [dWarStartDate]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_dWarEndDate]  DEFAULT (getdate()) FOR [dWarEndDate]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_nWarEnemy]  DEFAULT ((0)) FOR [nWarEnemy]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_nWarWinCount]  DEFAULT ((0)) FOR [nWarWinCount]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_nWarLoseCount]  DEFAULT ((0)) FOR [nWarLoseCount]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_nWarDrawCount]  DEFAULT ((0)) FOR [nWarDrawCount]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_nDeleteStatus]  DEFAULT ((0)) FOR [nDismissStatus]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_dDeleteDate]  DEFAULT (getdate()) FOR [dDismissDate]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_sIntro]  DEFAULT ('') FOR [sIntro]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_dNotifyDate]  DEFAULT (getdate()) FOR [dNotifyDate]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_sNotifyCharID]  DEFAULT ('') FOR [sNotifyCharID]
GO
ALTER TABLE [dbo].[tGuild] ADD  CONSTRAINT [DF_tGuild_sNotify]  DEFAULT ('') FOR [sNotify]
GO
ALTER TABLE [dbo].[tGuildAcademy] ADD  CONSTRAINT [DF__tGuildAca__nMone__66B53B20]  DEFAULT ((0)) FOR [nMoney]
GO
ALTER TABLE [dbo].[tGuildAcademy] ADD  CONSTRAINT [DF__tGuildAca__nPoin__67A95F59]  DEFAULT ((0)) FOR [nPoint]
GO
ALTER TABLE [dbo].[tGuildAcademyGraduate] ADD  DEFAULT (getdate()) FOR [dJoinDate]
GO
ALTER TABLE [dbo].[tGuildAcademyGraduate] ADD  DEFAULT (getdate()) FOR [dGraduateDate]
GO
ALTER TABLE [dbo].[tGuildAcademyMember] ADD  DEFAULT (getdate()) FOR [dJoinDate]
GO
ALTER TABLE [dbo].[tGuildAcademyMember] ADD  DEFAULT ((255)) FOR [nGrade]
GO
ALTER TABLE [dbo].[tGuildAcademyRanking] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGuildMember] ADD  CONSTRAINT [DF__tGuildMem__dRegD__467D75B8]  DEFAULT (getdate()) FOR [dJoinDate]
GO
ALTER TABLE [dbo].[tGuildMember] ADD  CONSTRAINT [DF__tGuildMem__nMemb__4865BE2A]  DEFAULT ((0)) FOR [nGrade]
GO
ALTER TABLE [dbo].[tGuildMember] ADD  CONSTRAINT [DF__tGuildMem__nFlag__4959E263]  DEFAULT ((0)) FOR [nFlags]
GO
ALTER TABLE [dbo].[tGuildMember] ADD  CONSTRAINT [DF_tGuildMember_nExpRatio]  DEFAULT ((0)) FOR [nExpRatio]
GO
ALTER TABLE [dbo].[tGuildMember] ADD  CONSTRAINT [DF_tGuildMember_sIntro]  DEFAULT ('') FOR [sIntro]
GO
ALTER TABLE [dbo].[tGuildTournamentRegist] ADD  CONSTRAINT [DF__tGuildTou__dDate__1DD065E0]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tGuildTournamentRegist] ADD  CONSTRAINT [DF__tGuildTou__nSeed__1EC48A19]  DEFAULT ((0)) FOR [nSeed]
GO
ALTER TABLE [dbo].[tGuildTournamentRegist] ADD  CONSTRAINT [DF__tGuildTou__bChoi__1FB8AE52]  DEFAULT ((0)) FOR [bChoice]
GO
ALTER TABLE [dbo].[tHolyPromise] ADD  CONSTRAINT [DF_tHolyPromise_dRegDate]  DEFAULT (getdate()) FOR [dRegDate]
GO
ALTER TABLE [dbo].[tHolyPromise] ADD  CONSTRAINT [DF_tHolyPromise_dLeaveDate]  DEFAULT (getdate()) FOR [dRejoinableDate]
GO
ALTER TABLE [dbo].[tHolyPromise] ADD  CONSTRAINT [DF_tHolyPromise_dKickDate]  DEFAULT (getdate()) FOR [dMemberAcceptableDate]
GO
ALTER TABLE [dbo].[tHolyPromiseReward] ADD  CONSTRAINT [DF_tHolyPromiseReward_dDate]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tInstanceDungeonCount] ADD  DEFAULT ((0)) FOR [nCount]
GO
ALTER TABLE [dbo].[tItem] ADD  CONSTRAINT [DF__tItem__nFlags__226010D3]  DEFAULT ((0)) FOR [nFlags]
GO
ALTER TABLE [dbo].[tItem] ADD  CONSTRAINT [DF__tItem__dDate__2354350C]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tItemActionCoolTime] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tItemMobList] ADD  CONSTRAINT [DF_tItemMobList_nGrade]  DEFAULT ((1)) FOR [nGrade]
GO
ALTER TABLE [dbo].[tItemMobList] ADD  CONSTRAINT [DF__tItemMobL__nKill__2630A1B7]  DEFAULT ((0)) FOR [nKillCount]
GO
ALTER TABLE [dbo].[tItemTitle] ADD  CONSTRAINT [DF__tItemTitl__nTitl__2AF556D4]  DEFAULT ((0)) FOR [nTitleType]
GO
ALTER TABLE [dbo].[tItemTitle] ADD  CONSTRAINT [DF__tItemTitl__nTitl__2BE97B0D]  DEFAULT ((0)) FOR [nTitleCharNo]
GO
ALTER TABLE [dbo].[tKeyMapInit] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tLog_GuildMasterChange] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tMarriage] ADD  CONSTRAINT [DF_tMarriage_nStatus]  DEFAULT ((0)) FOR [nStatus]
GO
ALTER TABLE [dbo].[tMarriage] ADD  CONSTRAINT [DF_tMarriage_nSubStatus]  DEFAULT ((0)) FOR [nSubStatus]
GO
ALTER TABLE [dbo].[tMarriage] ADD  CONSTRAINT [DF_tMarriage_nMarriageCount]  DEFAULT ((0)) FOR [nMarriageCount]
GO
ALTER TABLE [dbo].[tMarriage] ADD  CONSTRAINT [DF_tMarriage_nDivorceCancel]  DEFAULT ((0)) FOR [nDivorceCancel]
GO
ALTER TABLE [dbo].[tMasterPupils] ADD  CONSTRAINT [DF__tMasterPu__nFlag__56B3DD81]  DEFAULT ((0)) FOR [nFlags]
GO
ALTER TABLE [dbo].[tMasterPupils] ADD  CONSTRAINT [DF_tMasterPupils_dRegDate]  DEFAULT (getdate()) FOR [dRegDate]
GO
ALTER TABLE [dbo].[tMinihouse] ADD  DEFAULT ((0)) FOR [nVisitor]
GO
ALTER TABLE [dbo].[tMinihouse] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tMinihouseGate] ADD  DEFAULT ((1)) FOR [bType]
GO
ALTER TABLE [dbo].[tMinihouseGate] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tMinihouseGuest] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tParty] ADD  CONSTRAINT [DF__tParty__nFlags__5C6CB6D7]  DEFAULT ((0)) FOR [nFlags]
GO
ALTER TABLE [dbo].[tPartyMember] ADD  CONSTRAINT [DF__tPartyMem__nFlag__5F492382]  DEFAULT ((0)) FOR [nFlag]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nPetNumber__6225902D]  DEFAULT ((0)) FOR [nPetNumber]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__sInfo__6319B466]  DEFAULT ('') FOR [sInfo]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nFlags__640DD89F]  DEFAULT ((0)) FOR [nFlags]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nAssignNam__6501FCD8]  DEFAULT ('') FOR [nAssignName]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nLevel__65F62111]  DEFAULT ((1)) FOR [nLevel]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nExp__66EA454A]  DEFAULT ((0)) FOR [nExp]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nSatiety__67DE6983]  DEFAULT ((0)) FOR [nSatiety]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nSpeed__68D28DBC]  DEFAULT ((1)) FOR [nSpeed]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nAbilityTy__69C6B1F5]  DEFAULT ((0)) FOR [nAbilityType]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nAbilityVa__6ABAD62E]  DEFAULT ((0)) FOR [nAbilityValue]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__nSkinColor__6BAEFA67]  DEFAULT ((0)) FOR [nSkinColor]
GO
ALTER TABLE [dbo].[tPet] ADD  CONSTRAINT [DF__tPet__dDate__6CA31EA0]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tPetSkill] ADD  CONSTRAINT [DF__tPetSkill__nSkil__6F7F8B4B]  DEFAULT ((0)) FOR [nSkillLevel]
GO
ALTER TABLE [dbo].[tPetSkill] ADD  CONSTRAINT [DF__tPetSkill__nSkil__7073AF84]  DEFAULT ((0)) FOR [nSkillExp]
GO
ALTER TABLE [dbo].[tPrison] ADD  CONSTRAINT [DF_tPrison_dPrisonDate]  DEFAULT (getdate()) FOR [dPrisonDate]
GO
ALTER TABLE [dbo].[tPrison] ADD  CONSTRAINT [DF_tPrison_nRegNumber]  DEFAULT ((0)) FOR [nRegNumber]
GO
ALTER TABLE [dbo].[tQuestTimes] ADD  DEFAULT ((0)) FOR [nTimes]
GO
ALTER TABLE [dbo].[tQuestTimes] ADD  DEFAULT (getdate()) FOR [dLastComplete]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF__tSkillCha__nSkil__762C88DA]  DEFAULT ((1)) FOR [nSkillLevel]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF__tSkillCha__nSkil__7720AD13]  DEFAULT ((0)) FOR [nSkillExp]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF_tSkill_dSkillWriteTime]  DEFAULT ((0)) FOR [nSkillWriteTime]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF_tSkill_nSkillCoolTime_1]  DEFAULT ((0)) FOR [nSkillCoolTime]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF_tSkill_nSkillPowerDemage]  DEFAULT ((0)) FOR [nSkillPowerDemage]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF_tSkill_nSkillPowerSP]  DEFAULT ((0)) FOR [nSkillPowerSP]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF_tSkill_nSkillPowerKeepTime]  DEFAULT ((0)) FOR [nSkillPowerKeepTime]
GO
ALTER TABLE [dbo].[tSkill] ADD  CONSTRAINT [DF_tSkill_nSkillPowerCoolTime]  DEFAULT ((0)) FOR [nSkillPowerCoolTime]
GO
ALTER TABLE [dbo].[tSpamBlockTime] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tSpamBlockTime] ADD  DEFAULT ((0)) FOR [nBlockTime]
GO
ALTER TABLE [dbo].[tSpamBlockTime] ADD  DEFAULT ((0)) FOR [nStandbyTime]
GO
ALTER TABLE [dbo].[tSpamFilter] ADD  DEFAULT (getdate()) FOR [sys_create_date]
GO
ALTER TABLE [dbo].[tSpamFilter] ADD  DEFAULT ((0)) FOR [bIsSpamSpread]
GO
ALTER TABLE [dbo].[tSpamFilter] ADD  DEFAULT ((0)) FOR [bIsSpamBlock]
GO
ALTER TABLE [dbo].[tSpamFilter] ADD  DEFAULT ((100)) FOR [nSpamFilterRate]
GO
ALTER TABLE [dbo].[tStatisticsGameMoney] ADD  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tStatisticsGameMoney] ADD  DEFAULT ((0)) FOR [nMoney]
GO
ALTER TABLE [dbo].[tUserMoney] ADD  CONSTRAINT [DF_tUserMoney_nStorageMoney]  DEFAULT ((0)) FOR [nUserMoney]
GO
ALTER TABLE [dbo].[tAuction]  WITH CHECK ADD FOREIGN KEY([nBidderNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tAuction]  WITH CHECK ADD FOREIGN KEY([nCategoryNo])
REFERENCES [dbo].[tAuctionCategory] ([nCategoryNo])
GO
ALTER TABLE [dbo].[tAuction]  WITH CHECK ADD FOREIGN KEY([nSellerNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tAuctionInventory]  WITH CHECK ADD FOREIGN KEY([nAuctionNo])
REFERENCES [dbo].[tAuction] ([nAuctionNo])
GO
ALTER TABLE [dbo].[tAuctionInventory]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterChangeID]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterChangeID]  WITH CHECK ADD  CONSTRAINT [FK_tCharacter_tCharacterChangeID_nCharNo] FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterChangeID] CHECK CONSTRAINT [FK_tCharacter_tCharacterChangeID_nCharNo]
GO
ALTER TABLE [dbo].[tCharacterChargedBuffer]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterChargedBuffer]  WITH CHECK ADD  CONSTRAINT [FK_tCharacterChargedBuffer_tCharacter] FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tCharacterChargedBuffer] CHECK CONSTRAINT [FK_tCharacterChargedBuffer_tCharacter]
GO
ALTER TABLE [dbo].[tCharacterDisposition]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterGameOption]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCharacterGameOption]  WITH CHECK ADD FOREIGN KEY([nOptionNo])
REFERENCES [dbo].[tGameOptionInit] ([nOptionNo])
GO
ALTER TABLE [dbo].[tCharacterKeyMap]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCharacterKeyMap]  WITH CHECK ADD FOREIGN KEY([nFunctionNo])
REFERENCES [dbo].[tKeyMapInit] ([nFunctionNo])
GO
ALTER TABLE [dbo].[tCharacterLoadingDetailInfo]  WITH CHECK ADD FOREIGN KEY([nCharNo], [nSeqNo])
REFERENCES [dbo].[tCharacterLoading] ([nCharNo], [nSeqNo])
GO
ALTER TABLE [dbo].[tCharacterOptions]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterShape]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterShortCut]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCharacterSingleOption]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterSingleOption]  WITH CHECK ADD  CONSTRAINT [FK_tCharacter_tCharacterSingleOption_nCharNo] FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterSingleOption] CHECK CONSTRAINT [FK_tCharacter_tCharacterSingleOption_nCharNo]
GO
ALTER TABLE [dbo].[tCharacterTitle]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCharacterTitle]  WITH CHECK ADD  CONSTRAINT [FK_tCharacterTitle_tCharacter] FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCharacterTitle] CHECK CONSTRAINT [FK_tCharacterTitle_tCharacter]
GO
ALTER TABLE [dbo].[tChatBlock]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCollectionCard]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tCollectionFavorites]  WITH CHECK ADD FOREIGN KEY([nCharNo], [nCardID])
REFERENCES [dbo].[tCollectionCard] ([nCharNo], [nCardID])
GO
ALTER TABLE [dbo].[tDice]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tDiceLog]  WITH CHECK ADD FOREIGN KEY([nGameNo], [nCustomCharNo])
REFERENCES [dbo].[tDiceResult] ([nGameNo], [nCustomCharNo])
GO
ALTER TABLE [dbo].[tDiceLog]  WITH CHECK ADD FOREIGN KEY([nGameNo], [nCustomCharNo])
REFERENCES [dbo].[tDiceResult] ([nGameNo], [nCustomCharNo])
GO
ALTER TABLE [dbo].[tDicePayLog]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tDiceResult]  WITH CHECK ADD FOREIGN KEY([nCustomCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tDiceResult]  WITH CHECK ADD FOREIGN KEY([nGameNo])
REFERENCES [dbo].[tDice] ([nGameNo])
GO
ALTER TABLE [dbo].[tDiceResult]  WITH CHECK ADD FOREIGN KEY([nGameNo])
REFERENCES [dbo].[tDice] ([nGameNo])
GO
ALTER TABLE [dbo].[tFriend]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGambleCoin]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGambleDiceLog]  WITH CHECK ADD FOREIGN KEY([nGameNo], [nCharNo])
REFERENCES [dbo].[tGambleDiceResult] ([nGameNo], [nCharNo])
GO
ALTER TABLE [dbo].[tGambleDiceResult]  WITH CHECK ADD FOREIGN KEY([nGameNo])
REFERENCES [dbo].[tGambleDice] ([nGameNo])
GO
ALTER TABLE [dbo].[tGambleJackpotLog]  WITH CHECK ADD FOREIGN KEY([nGameNo])
REFERENCES [dbo].[tGambleSlotMachineLog] ([nGameNo])
GO
ALTER TABLE [dbo].[tGambleSlotMachineLog]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildAcademy]  WITH CHECK ADD  CONSTRAINT [FK__tGuildAca__nMast__6A85CC04] FOREIGN KEY([nMasterCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildAcademy] CHECK CONSTRAINT [FK__tGuildAca__nMast__6A85CC04]
GO
ALTER TABLE [dbo].[tGuildAcademy]  WITH CHECK ADD FOREIGN KEY([nMasterCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildAcademy]  WITH CHECK ADD  CONSTRAINT [FK__tGuildAca__nMast__7F80E8EA] FOREIGN KEY([nMasterCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildAcademy] CHECK CONSTRAINT [FK__tGuildAca__nMast__7F80E8EA]
GO
ALTER TABLE [dbo].[tGuildAcademy]  WITH CHECK ADD  CONSTRAINT [FK__tGuildAcade__nNo__6991A7CB] FOREIGN KEY([nNo])
REFERENCES [dbo].[tGuild] ([nNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tGuildAcademy] CHECK CONSTRAINT [FK__tGuildAcade__nNo__6991A7CB]
GO
ALTER TABLE [dbo].[tGuildAcademyMember]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildAcademyMember]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildAcademyMember]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildAcademyMember]  WITH CHECK ADD  CONSTRAINT [FK__tGuildAcade__nNo__6F4A8121] FOREIGN KEY([nNo])
REFERENCES [dbo].[tGuildAcademy] ([nNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tGuildAcademyMember] CHECK CONSTRAINT [FK__tGuildAcade__nNo__6F4A8121]
GO
ALTER TABLE [dbo].[tGuildAcademyReward]  WITH CHECK ADD FOREIGN KEY([nTypeID])
REFERENCES [dbo].[tGuildAcademyRewardType] ([nTypeID])
GO
ALTER TABLE [dbo].[tGuildAcademyReward]  WITH CHECK ADD FOREIGN KEY([nTypeID])
REFERENCES [dbo].[tGuildAcademyRewardType] ([nTypeID])
GO
ALTER TABLE [dbo].[tGuildAcademyReward]  WITH CHECK ADD FOREIGN KEY([nTypeID])
REFERENCES [dbo].[tGuildAcademyRewardType] ([nTypeID])
GO
ALTER TABLE [dbo].[tGuildAcademyReward]  WITH CHECK ADD  CONSTRAINT [FK__tGuildAcade__nNo__75035A77] FOREIGN KEY([nNo])
REFERENCES [dbo].[tGuildAcademy] ([nNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tGuildAcademyReward] CHECK CONSTRAINT [FK__tGuildAcade__nNo__75035A77]
GO
ALTER TABLE [dbo].[tGuildMember]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tGuildMember]  WITH CHECK ADD  CONSTRAINT [FK_tGuildMember_tGuild] FOREIGN KEY([nNo])
REFERENCES [dbo].[tGuild] ([nNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tGuildMember] CHECK CONSTRAINT [FK_tGuildMember_tGuild]
GO
ALTER TABLE [dbo].[tGuildToken]  WITH CHECK ADD FOREIGN KEY([nNo])
REFERENCES [dbo].[tGuild] ([nNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tHolyPromise]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tHolyPromise]  WITH CHECK ADD  CONSTRAINT [FK_tHolyPromise_tCharacter] FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tHolyPromise] CHECK CONSTRAINT [FK_tHolyPromise_tCharacter]
GO
ALTER TABLE [dbo].[tHolyPromiseReward]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tItemActionCoolTime]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tItemEnchant]  WITH CHECK ADD FOREIGN KEY([nItemKey])
REFERENCES [dbo].[tItem] ([nItemKey])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tItemMobList]  WITH CHECK ADD  CONSTRAINT [FK__tItemMobL__nItem__2DD1C37F] FOREIGN KEY([nItemKey])
REFERENCES [dbo].[tItem] ([nItemKey])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tItemMobList] CHECK CONSTRAINT [FK__tItemMobL__nItem__2DD1C37F]
GO
ALTER TABLE [dbo].[tItemOptions]  WITH CHECK ADD  CONSTRAINT [FK__tItemOpti__nItem__2EC5E7B8] FOREIGN KEY([nItemKey])
REFERENCES [dbo].[tItem] ([nItemKey])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tItemOptions] CHECK CONSTRAINT [FK__tItemOpti__nItem__2EC5E7B8]
GO
ALTER TABLE [dbo].[tItemTitle]  WITH CHECK ADD  CONSTRAINT [FK__tItemTitl__nItem__35A8BAC8] FOREIGN KEY([nItemKey])
REFERENCES [dbo].[tItem] ([nItemKey])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tItemTitle] CHECK CONSTRAINT [FK__tItemTitl__nItem__35A8BAC8]
GO
ALTER TABLE [dbo].[tMarriage]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tMinihouse]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tMinihouse]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tMinihouseGate]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tMinihouse] ([nCharNo])
GO
ALTER TABLE [dbo].[tMinihouseGate]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tMinihouse] ([nCharNo])
GO
ALTER TABLE [dbo].[tMinihouseGuest]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tMinihouse] ([nCharNo])
GO
ALTER TABLE [dbo].[tMinihouseGuest]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tMinihouse] ([nCharNo])
GO
ALTER TABLE [dbo].[tPartyMember]  WITH CHECK ADD FOREIGN KEY([nPartyNo])
REFERENCES [dbo].[tParty] ([nPartyNo])
GO
ALTER TABLE [dbo].[tPartyMember]  WITH CHECK ADD FOREIGN KEY([nPartyNo])
REFERENCES [dbo].[tParty] ([nPartyNo])
GO
ALTER TABLE [dbo].[tPartyMember]  WITH CHECK ADD FOREIGN KEY([nPartyNo])
REFERENCES [dbo].[tParty] ([nPartyNo])
GO
ALTER TABLE [dbo].[tPetSkill]  WITH CHECK ADD  CONSTRAINT [FK__tPetSkill__nPetK__0EF836A4] FOREIGN KEY([nPetKey])
REFERENCES [dbo].[tPet] ([nPetKey])
GO
ALTER TABLE [dbo].[tPetSkill] CHECK CONSTRAINT [FK__tPetSkill__nPetK__0EF836A4]
GO
ALTER TABLE [dbo].[tPrison]  WITH CHECK ADD FOREIGN KEY([nCharID])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tQuest]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tSkill]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
ALTER TABLE [dbo].[tSpamBlockTime]  WITH CHECK ADD FOREIGN KEY([nCharNo])
REFERENCES [dbo].[tCharacter] ([nCharNo])
GO
USE [master]
GO
ALTER DATABASE [World00_Character] SET  READ_WRITE 
GO
