BEGIN
	SELECT @nStatPoint = nDexterity FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nDexterity = nDexterity - 1					WHERE nCharNo = @nCharNo AND nDexterity > 0
END
IF @nStatPointType = 3
BEGIN
	SELECT @nStatPoint = nIntelligence FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nIntelligence = nIntelligence - 1			WHERE nCharNo = @nCharNo AND nIntelligence > 0
END
IF @nStatPointType = 4 
BEGIN
	SELECT @nStatPoint = nMentalPower FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nMentalPower = nMentalPower - 1				WHERE nCharNo = @nCharNo AND nMentalPower > 0
END
IF @@ERROR <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 1
	RETURN
END
-- Delete used charge item
DECLARE @TempRet int
EXEC p_Item_Delete @nDeleteItemKey, @TempRet OUTPUT
IF @TempRet <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 2
	RETURN
END
-- Done
COMMIT TRAN
SET @nRet = 0
RETURN


GO
/****** Object:  StoredProcedure [dbo].[usp_ChargeItem_UseStatPointDecLotChange]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_ChargeItem_UseStatPointDecLotChange]
/*
Charge item use - Stat point decrement and use item lot change
2006.6 By CJC
 input:
	see SQL
 output:
	@nRet	0 = OK
	        1 = Error decrement character stat point.
	        2 = Error used charge item change lot
*/
@nCharNo int,
@nStatPointType tinyint,
@nChangeItemKey bigint,
@nChangeItemOptionType smallint,
@nChangeItemOptionData bigint,		
@nRet tinyint OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
-- CHECK Type and Decrement stat-point
DECLARE @nStatPoint tinyint
IF @nStatPointType = 0
BEGIN
	SELECT @nStatPoint = nStrength FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nStrength	= nStrength - 1					WHERE nCharNo = @nCharNo AND nStrength > 0
END
IF @nStatPointType = 1
BEGIN
	SELECT @nStatPoint = nConstitute FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nConstitute = nConstitute - 1				WHERE nCharNo = @nCharNo AND nConstitute > 0
END
	
IF @nStatPointType = 2
BEGIN
	SELECT @nStatPoint = nDexterity FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nDexterity = nDexterity - 1					WHERE nCharNo = @nCharNo AND nDexterity > 0
END
IF @nStatPointType = 3
BEGIN
	SELECT @nStatPoint = nIntelligence FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nIntelligence = nIntelligence - 1			WHERE nCharNo = @nCharNo AND nIntelligence > 0
END
IF @nStatPointType = 4 
BEGIN
	SELECT @nStatPoint = nMentalPower FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nStatPoint < 1
	BEGIN
		SET @nRet = 1
		RETURN
	END
	
	UPDATE tCharacter	SET nMentalPower = nMentalPower - 1				WHERE nCharNo = @nCharNo AND nMentalPower > 0
END
IF @@ERROR <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 1
	RETURN
END
-- Used charge item change lot
DECLARE @TempRet int
EXEC p_Item_SetOption @nChangeItemKey, @nChangeItemOptionType, @nChangeItemOptionData, @TempRet OUTPUT
IF @TempRet <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 2
	RETURN
END
-- Done
COMMIT TRAN
SET @nRet = 0
RETURN


GO
/****** Object:  StoredProcedure [dbo].[usp_ChargeItem_UseStatPointInitDel]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_ChargeItem_UseStatPointInitDel]
/*
Charge item use - Stat point init and use item delete
2006.6 By CJC
 input:
	see SQL
 output:
	@nRet	0 = OK
	        1 = Error clear character stat point.
	        2 = Error delete used charge item
*/
@nCharNo int,
@nDeleteItemKey bigint,
@nRet tinyint OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
-- Clear character stat point.
UPDATE tCharacter SET 
	nStrength = 0, 
	nConstitute = 0, 
	nDexterity = 0, 
	nIntelligence = 0, 
	nMentalPower = 0
WHERE nCharNo = @nCharNo
IF @@ERROR <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 1
	RETURN
END
-- Delete used charge item
DECLARE @TempRet int
EXEC p_Item_Delete @nDeleteItemKey, @TempRet OUTPUT
IF @TempRet <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 2
	RETURN
END
-- Done
COMMIT TRAN
SET @nRet = 0
RETURN


GO
/****** Object:  StoredProcedure [dbo].[usp_ChargeItem_UseStatPointInitLotChange]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_ChargeItem_UseStatPointInitLotChange]
/*
Charge item use - Stat point init and use item lot change
2006.6 By CJC
 input:
	see SQL
 output:
	@nRet	0 = OK
	        1 = Error clear character stat point.
	        2 = Error used charge item change lot
*/
@nCharNo int,
@nChangeItemKey bigint,
@nChangeItemOptionType smallint,
@nChangeItemOptionData bigint,		
@nRet tinyint OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
-- Clear character stat point.
UPDATE tCharacter SET 
	nStrength = 0, 
	nConstitute = 0, 
	nDexterity = 0, 
	nIntelligence = 0, 
	nMentalPower = 0
WHERE nCharNo = @nCharNo
IF @@ERROR <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 1
	RETURN
END
-- Used charge item change lot
DECLARE @TempRet int
EXEC p_Item_SetOption @nChangeItemKey, @nChangeItemOptionType, @nChangeItemOptionData, @TempRet OUTPUT
IF @TempRet <> 0
BEGIN
	ROLLBACK TRAN
	SET @nRet = 2
	RETURN
END
-- Done
COMMIT TRAN
SET @nRet = 0
RETURN


GO
/****** Object:  StoredProcedure [dbo].[usp_ChatBlock_delBlock]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_ChatBlock_delBlock
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.2.12
' Description :
'   채팅 블럭 삭제
' Parameter :
'	input Parameter
'		@nUserNo : 유저번호(차단하는 유저)
'		@nCharNo : 캐릭터번호(차단당하는 캐릭터)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2311 : invaild nUserNo
'				-2312 : invaild nCharNo
'				-2321 : not found data
'				-2392 : db error(delete)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_ChatBlock_delBlock]
	@nUserNo int
,	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF ISNULL(@nUserNo, 0) <= 0 BEGIN
		SET @nRet = -2311
		RETURN
	END

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

	IF NOT EXISTS(SELECT nUserNo FROM tChatBlock WHERE nUserNo = @nUserNo AND nCharNo = @nCharNo) BEGIN
		SET @nRet = -2323
		RETURN
	END
	ELSE BEGIN
		DELETE FROM tChatBlock WHERE nUserNo = @nUserNo AND nCharNo = @nCharNo
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -2394
		END
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_ChatBlock_delBlockALL]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_ChatBlock_delBlockALL
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.2.12
' Description :
'   채팅 블럭 삭제(해당 유저가 가지고 있는것 모두 삭제)
' Parameter :
'	input Parameter
'		@nUserNo : 유저번호(차단하는 유저)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2311 : invaild nUserNo
'				-2321 : not found data
'				-2392 : db error(delete)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_ChatBlock_delBlockALL]
	@nUserNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF ISNULL(@nUserNo, 0) <= 0 BEGIN
		SET @nRet = -2311
		RETURN
	END

	IF NOT EXISTS(SELECT nUserNo FROM tChatBlock WHERE nUserNo = @nUserNo) BEGIN
		SET @nRet = -2321
		RETURN
	END
	ELSE BEGIN
		DELETE FROM tChatBlock WHERE nUserNo = @nUserNo
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -2394
		END
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_ChatBlock_getBlockList]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_ChatBlock_getBlockList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.2.12
' Description :
'   채팅 블럭 리스트
' Parameter :
'	input Parameter
'		@nUserNo : 유저번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2311 : invaild nUserNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_ChatBlock_getBlockList]
	@nUserNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	IF ISNULL(@nUserNo, 0) <= 0 BEGIN
		SET @nRet = -2311
		RETURN
	END

	SELECT T1.nCharNo, T2.sID, T2.nAdminLevel, T2.bDeleted
	  FROM tChatBlock T1, tCharacter T2
	 WHERE T1.nUserNo = @nUserNo AND T1.nCharNo = T2.nCharNo
END


GO
/****** Object:  StoredProcedure [dbo].[usp_ChatBlock_setBlock]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 파일명 : KR_procedureChatBlock.sql
' 내  용 : 채팅 블럭 관련 저장프로시저
' 작성자 : 고동균(kodong@gmail.com)
' 작성일 : 2009.2.12
' Description :
'------------------------------------------------------------------------------------*/

-- usp_ChatBlock_setBlock
-- usp_ChatBlock_delBlock
-- usp_ChatBlock_getBlockList

/*--------------------------------------------------------------------------------------
' 이  름 : usp_ChatBlock_setBlock
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.2.12
' Description :
'   채팅 블럭 등록
' Parameter :
'	input Parameter
'		@nUserNo : 유저번호(차단하는 유저)
'		@sID     : 캐릭터명(차단당하는 캐릭터)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2311 : invaild nUserNo
'				-2312 : invaild sID
'				-2321 : already join nNo
'				-2322 : limit max count
'				-2323 : not found sID
'				-2392 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_ChatBlock_setBlock]
	@nUserNo int
,	@sID     nvarchar(40) = ''
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @cnt AS int
	DECLARE @maxCnt AS int
	DECLARE @nCharNo AS int

	SET @nRet = 0
	SET @nCharNo = 0
	SET @maxCnt = 100 -- 최대등록 차단수(차후 변경시 DB 저장프로시저만 수정하면 반영됨)

	IF ISNULL(@nUserNo, 0) <= 0 BEGIN
		SET @nRet = -2311
		RETURN
	END

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

	SELECT @nCharNo = nCharNo FROM tCharacter WHERE sID = @sID AND bDeleted = 0
	IF @nCharNo <= 0 BEGIN
		SET @nRet = -2323
		RETURN
	END

	IF EXISTS(SELECT nUserNo FROM tChatBlock WHERE nUserNo = @nUserNo AND nCharNo = @nCharNo) BEGIN
		SET @nRet = -2321
		RETURN
	END

	SELECT @cnt = ISNULL(COUNT(*), 0) FROM tChatBlock WHERE nUserNo = @nUserNo
	IF @cnt > @maxCnt BEGIN
		SET @nRet = -2322
		RETURN
	END		
	ELSE BEGIN
		INSERT INTO tChatBlock (nUserNo, nCharNo)
		VALUES (@nUserNo, @nCharNo)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -2392
			RETURN
		END

		SELECT T1.nCharNo, T2.sID, T2.nAdminLevel, T2.bDeleted
		  FROM tChatBlock T1, tCharacter T2
		 WHERE T1.nUserNo = @nUserNo 
		   AND T1.nCharNo = @nCharNo
		   AND T1.nCharNo = T2.nCharNo
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_getCardList]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_getCardList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.15
'   등록된 컬렉션 카드 리스트 
' Parameter :
'	input Parameter
'		@nCharNo      : 캐릭터번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_getCardList]
	@nCharNo      int
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

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

	SELECT nCardID, nSerialNo, nStarNumber, dDate 
	  FROM tCollectionCard 
	 WHERE nCharNo = @nCharNo 	
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_getFavoritesList]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_getFavoritesList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.15
'   마이 컬렉션 카드 리스트 
' Parameter :
'	input Parameter
'		@nCharNo      : 캐릭터번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_getFavoritesList]
	@nCharNo      int
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

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

	SELECT T1.nSlotNo, T1.nCardID, T2.nSerialNo, T2.nStarNumber 
	  FROM tCollectionFavorites T1, tCollectionCard T2 
	 WHERE T1.nCharNo = T2.nCharNo
	   AND T1.nCardID = T2.nCardID 
	   AND T1.nCharNo = @nCharNo 
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_getRewardList]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_getRewardList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.22
'   캐릭터별 보상을 받은 리스트 
' Parameter :
'	input Parameter
'		@nCharNo      : 캐릭터번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_getRewardList]
	@nCharNo      int
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

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

	SELECT nReward 
	  FROM tCollectionReward 
	 WHERE nCharNo = @nCharNo 
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_getSerialList]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_getSerialList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.22
'   카드아이디별 최종 발급 번호를 가져옴. 서버 시작시 모든 리스트를 서버에서 조회
' Parameter :
'	input Parameter : 해당사항없음
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_getSerialList]
	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	
	SELECT nCardID, nCount
	  FROM tCollectionSerialCount 
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_setCard]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_setCard
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.15
'   컬렉션 카드 등록
' 주  의 :
'	2011.11.15 by kodong
'	-- tItem 테이블에서 해당 아이템이 존재하는지 확인 후 컬렉션 테이블에 등록하고 그 후에
'	   tItem 테이블에서 해당 아이템을 삭제하는 프로세스라 tItem 테이블을 행 잠금을 
'      고려하였으나 유저의 이득이 없는 관계로 서버측과 협의하여 테이블 행 잠금을 하지 않았음.
' Parameter :
'	input Parameter
'		@nCharNo      : 캐릭터번호
'		@nCardID      : 카드번호
'		@nSerialNo    : 카드 고유번호
'		@nStarNumber  : 별의개수
'		@nItemKey     : 삭제될 아이템키
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_setCard]
	@nCharNo      int
,	@nCardID      int
,	@nSerialNo    bigint
,	@nStarNumber  tinyint
,	@nItemKey     bigint
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	SET @nRet = 0
	
	IF ISNULL(@nCharNo, 0) = 0 BEGIN
		SET @nRet = -1111
		RETURN
	END
	IF ISNULL(@nCardID, 0) = 0 BEGIN
		SET @nRet = -1111
		RETURN	
	END
	IF ISNULL(@nSerialNo, 0) = 0 BEGIN
		SET @nRet = -1111
		RETURN	
	END
	IF ISNULL(@nStarNumber, 0) = 0 BEGIN
		SET @nRet = -1111
		RETURN	
	END

	IF EXISTS(SELECT nItemKey FROM tItem WHERE nItemKey = @nItemKey) BEGIN
		BEGIN TRAN
		
		-- 1. 컬렉션 카드 등록
		IF EXISTS(SELECT nCharNo FROM tCollectionCard WHERE nCharNo = @nCharNo AND nCardID = @nCardID) BEGIN
			-- 1-1. 이미 존재하는 경우
			UPDATE tCollectionCard 
			   SET nSerialNo   = @nSerialNo 
				 , nStarNumber = @nStarNumber 
			 WHERE nCharNo = @nCharNo 
			   AND nCardID = @nCardID 
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
				SET @nRet = -9999
				ROLLBACK TRAN
				RETURN
			END
		END
		ELSE BEGIN
			-- 1-2. 새로 등록하는 경우
			INSERT INTO tCollectionCard (nCharNo, nCardID, nSerialNo, nStarNumber)
			VALUES (@nCharNo, @nCardID, @nSerialNo, @nStarNumber)
			IF @@ERROR <> 0 BEGIN
				SET @nRet = -9999
				ROLLBACK TRAN
				RETURN
			END
		END
		
		-- 2. 컬렉션에 등록된 카드의 경우 tItem 테이블에서 해당 아이템을 삭제
		DELETE FROM tItem WHERE nItemKey = @nItemKey
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -9999
			ROLLBACK TRAN
			RETURN
		END	
		
		COMMIT TRAN		
	END
	ELSE BEGIN
		SET @nRet = -9999
		RETURN
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_setFavorites]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_setFavorites
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.15
'   마이 컬렉션 카드 등록
' 주  의 :
' Parameter :
'	input Parameter
'		@nCharNo      : 캐릭터번호
'		@nSlotNo      : 슬롯번호
'		@nCardID      : 카드번호
'		@bRegist      : 등록여부(0:삭제/1:등록)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_setFavorites]
	@nCharNo      int
,	@nSlotNo      tinyint	
,	@nCardID      int
,	@bRegist      bit
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

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

	IF ISNULL(@nSlotNo, -1) < 0 BEGIN
		SET @nRet = -1111
		RETURN	
	END

	BEGIN TRAN

	IF @bRegist = 1 BEGIN
		IF ISNULL(@nCardID, 0) = 0 BEGIN
			SET @nRet = -1111
			ROLLBACK TRAN
			RETURN	
		END		
		
		-- 1. 마이컬렉션 카드 등록
		IF EXISTS(SELECT nCharNo FROM tCollectionFavorites WHERE nCharNo = @nCharNo AND nSlotNo = @nSlotNo) BEGIN
			-- 1-1. 이미 존재하는 경우
			UPDATE tCollectionFavorites 
			   SET nCardID = @nCardID 
			 WHERE nCharNo = @nCharNo
			   AND nSlotNo = @nSlotNo 
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
				SET @nRet = -9999
				ROLLBACK TRAN
				RETURN
			END
		END
		ELSE BEGIN
			-- 1-2. 새로 등록하는 경우
			INSERT INTO tCollectionFavorites (nCharNo, nSlotNo, nCardID)
			VALUES (@nCharNo, @nSlotNo, @nCardID)
			IF @@ERROR <> 0 BEGIN
				SET @nRet = -9999
				ROLLBACK TRAN
				RETURN
			END
		END
	END
	ELSE BEGIN
		IF EXISTS(SELECT nCharNo FROM tCollectionFavorites WHERE nCharNo = @nCharNo AND nSlotNo = @nSlotNo) BEGIN
			DELETE FROM tCollectionFavorites WHERE nCharNo = @nCharNo AND nSlotNo = @nSlotNo
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
				SET @nRet = -9999
				ROLLBACK TRAN
				RETURN
			END
		END
		ELSE BEGIN
			SET @nRet = -5555
			ROLLBACK TRAN
			RETURN
		END
	END
	
	COMMIT TRAN
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_setReward]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_setReward
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.22
'   캐릭터별 보상 받은 기록를 함. 
' Parameter :
'	input Parameter
'		@nCharNo      : 캐릭터번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_setReward]
	@nCharNo      int
,	@nReward      int
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

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

	IF EXISTS(SELECT nCharNo FROM tCollectionReward WHERE nCharNo = @nCharNo AND nReward = @nReward) BEGIN
		SET @nRet = -2222
		RETURN
	END
	ELSE BEGIN
		INSERT INTO tCollectionReward (nCharNo, nReward)
		VALUES (@nCharNo, @nReward)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -3333
			RETURN
		END
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Collection_setSerial]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Collection_setSerial
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.22
'   카드아이디별 최종 발급 번호를 저장함. 
' Parameter :
'	input Parameter
'		@nCardID      : 캐릭터번호
'		@nCount       : 최종발급번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-3191 : db error (insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Collection_setSerial]
	@nCardID      int
,	@nCount       bigint
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	
	IF ISNULL(@nCardID, 0) = 0 BEGIN
		SET @nRet = -1111
		RETURN
	END
	
	IF EXISTS(SELECT nCardID FROM tCollectionSerialCount WHERE nCardID = @nCardID) BEGIN
		BEGIN TRAN
		
		UPDATE tCollectionSerialCount 
		   SET sys_update_date = GETDATE()
		     , nCount          = @nCount
		 WHERE nCardID = @nCardID 
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -4444
			ROLLBACK TRAN
			RETURN
		END
		
		COMMIT TRAN
	END
	ELSE BEGIN
		IF @nCount <> 1 BEGIN
			SET @nRet = -5555
			RETURN
		END
		
		INSERT INTO tCollectionSerialCount (nCardID, nCount)
		VALUES (@nCardID, @nCount)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -6666
			RETURN
		END
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Dice_chgMachineToMoneycard]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Dice_chgMachineToMoneycard
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.7.23
' Description :
'   주사위 게임기를 머니카드로 교체함.
'	중요.!!! 교체시 머니카드의 금액을 확정함.
'	중요.!!! 거치된 게임기의 경우 인벤토리로 회수하는 작업을 병행함.
' Parameter :
'	input Parameter
'		@nItemKey       : 게임기 아이템키
'		@nItemIDOld     : 게임기 아이템아이디
'		@nItemIDNew     : 머니카드 아이템아이디
'		@nOptionType    : 배당금 옵션타입
'		@nTotalDividend : 배당금(수익 금액)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2412 : invaild @nItemKey
'				-2416 : invaild @nTotalDividend
'				-2417 : invaild @nItemIDOld
'				-2418 : invaild @nItemIDNew
'				-2419 : invaild @nOptionType
'				-2493 : db error(update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Dice_chgMachineToMoneycard]
	@nItemKey       bigint
,	@nItemIDOld     int
,	@nItemIDNew     int
,	@nOptionType    smallint
,	@nTotalDividend bigint
,	@nRet              int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @nIsPlace AS smallint 
	DECLARE @nIsPlaceValue AS bigint

	SET @nRet          = 0
	SET @nIsPlace      = 904 -- 가구아이템이 거치된 아이템인지 판단하는 OptionType
	SET @nIsPlaceValue = 0   -- 거치된 아이템을 회수하기 위함 Value

	IF ISNULL(@nItemKey, 0) <= 0 BEGIN
		SET @nRet = -2412
		RETURN
	END

	IF ISNULL(@nItemIDOLD, 0) <= 0 BEGIN
		SET @nRet = -2417
		RETURN
	END

	IF ISNULL(@nItemIDNEW, 0) <= 0 BEGIN
		SET @nRet = -2418
		RETURN
	END

	IF ISNULL(@nOptionType, 0) <= 0 BEGIN
		SET @nRet = -2419
		RETURN
	END

	IF ISNULL(@nTotalDividend, 0) <= 0 BEGIN
		SET @nRet = -2416
		RETURN
	END

	IF EXISTS(SELECT nItemKey FROM tItem WHERE nItemKey = @nItemKey AND nItemID = @nItemIDOLD) BEGIN
		BEGIN TRAN

		-- 게임기의 아이템아이디를 머니카드의 아이템아이디로 교체
		UPDATE tItem SET nItemID = @nItemIDNEW
		 WHERE nItemKey = @nItemKey
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -2493
			ROLLBACK TRAN
			RETURN
		END

		-- 거치된 게임기의 경우 인벤토리로 회수하는 작업을 함.
		UPDATE tItemOptions SET nOptionData = @nIsPlaceValue
		 WHERE nItemKey = @nItemKey AND nOptionType = @nIsPlace
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -2493
			ROLLBACK TRAN
		END

		-- 머니카드에 배당금을 확정함
		UPDATE tItemOptions SET nOptionData = @nTotalDividend
		 WHERE nItemKey = @nItemKey AND nOptionType = @nOptionType
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -2493
			ROLLBACK TRAN
		END
		ELSE BEGIN
			SET @nRet = 0
			COMMIT TRAN
		END
	END
	ELSE BEGIN
		SET @nRet = -2422
	END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Dice_getDiceExpect]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Dice_getDiceExpect
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.6.30
' Description :
'   주사위 게임에서 방장의 수익 금액을 계산함
' Parameter :
'	input Parameter
'		@nCharNo  : 방장 캐릭터번호
'		@nItemKey : 아이템키
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2411 : invaild @nCharNo
'				-2412 : invaild @nItemKey
'	return Record
'		nCount
'		nTotalDividend
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Dice_getDiceExpect]
	@nCharNo  int
,	@nItemKey bigint
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

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

	IF ISNULL(@nItemKey, 0) <= 0 BEGIN
		SET @nRet = -2412
		RETURN
	END

	SELECT ISNULL(COUNT(DISTINCT(A.nGameNo)), 0) AS nCount, ISNULL(SUM(nTotalDividend), 0) AS nTotalDividend
	  FROM tDice AS A, tDiceResult AS B
	 WHERE A.nGameNo = B.nGameNo
	   AND A.nCharNo = @nCharNo
	   AND A.nItemKey = @nItemKey

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Dice_getDiceRank]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Dice_getDiceRank
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.6.30
' Description :
'   주사위 게임에서 아이템당 순위를 가져옴.
'	상위 20 캐릭터
' Parameter :
'	input Parameter
'		@nCharNo  : 방장 캐릭터번호
'		@nItemKey : 아이템키
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2411 : invaild @nCharNo
'				-2412 : invaild @nItemKey
'	return Record
'		nCustomCharNo, nWinCount
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Dice_getDiceRank]
	@nCharNo  int
,	@nItemKey bigint
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

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

	IF ISNULL(@nItemKey, 0) <= 0 BEGIN
		SET @nRet = -2412
		RETURN
	END

	SELECT TOP 20 C.sID, COUNT(B.nGameNo) AS nWinCount
	  FROM tDice AS A, tDiceLog AS B, tCharacter AS C
	 WHERE A.nGameNo = B.nGameNo
	   AND B.nCustomCharNo = C.nCharNo
	   AND A.nCharNo = @nCharNo
	   AND A.nItemKey = @nItemKey
	   AND B.bStatus = 1
	   AND C.bDeleted = 0
	 GROUP BY C.sID
	 ORDER BY nWinCount DESC

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Dice_setDice]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 파일명 : KR_procedureDice.sql
' 내  용 : 주사위 게임 관련 저장프로시저
' 작성자 : 고동균(kodong@gmail.com)
' 작성일 : 2009.6.30
' Description :
'	주사위 게임 관련 저장프로시저
'------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------
' 이  름 : usp_Dice_setDice
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.6.30
' Description :
'   주사위 게임에서 한판이 끝났을때 결과를 저장함.
' Parameter :
'	input Parameter
'		@nCharNo  : 방장 캐릭터번호
'		@nItemKey : 아이템키
'		@sDice    : 주사위 결과
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2411 : invaild @nCharNo
'				-2412 : invaild @nItemKey
'				-2413 : invaild @sDice
'				-2492 : db error(insert)
'	return Record
'		nGameNo
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Dice_setDice] 
	@nCharNo  int
,	@nItemKey bigint
,	@sDice    nvarchar(3)
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @nGameNo AS int

	SET @nRet    = 0
	SET @nGameNo = 0

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

	IF ISNULL(@nItemKey, 0) <= 0 BEGIN
		SET @nRet = -2412
		RETURN
	END

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

	INSERT INTO tDice (nCharNo, nItemKey, sDice)
	VALUES (@nCharNo, @nItemKey, @sDice)
	IF @@ERROR <> 0 OR @@IDENTITY = 0 BEGIN
		SET @nRet = -2492
		RETURN
	END
	ELSE BEGIN
		SET @nGameNo = @@IDENTITY
		SELECT @nGameNo
	END

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Dice_setDiceLog]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Dice_setDiceLog
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.6.30
' Description :
'   주사위 게임에서 한판이 끝났을때 각 유저의 로그를 기록함
' Parameter :
'	input Parameter
'		@nGameNo       : 게임번호
'		@nCustomCharNo : 고객 캐릭터번호
'		@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_Dice_setDiceLog]
	@nGameNo       int 
,	@nCustomCharNo 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 tDiceLog (nGameNo, nCustomCharNo, nSlotNo, nBattingMoney, nBattingRate, bStatus, nTaxRate, nDividend)
	VALUES (@nGameNo, @nCustomCharNo, @nSlotNo, @nBattingMoney, @nBattingRate, @bStatus, @nTaxRate, @nDividend)
	IF @@ERROR <> 0 BEGIN
		SET @nRet = -2492
		RETURN
	END

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Dice_setDicePay]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Dice_setDicePay
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.7.22
' Description :
'   주사위 게임에서 방장에게 수익 금액을 지급함.
' Parameter :
'	input Parameter
'		@nCharNo        : 방장 캐릭터번호
'		@nItemKey       : 머니카드(게임기) 아이템키
'		@nTotalDividend : 배당금(수익 금액)
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2411 : invaild @nCharNo
'				-2412 : invaild @nItemKey
'				-2416 : invaild @nTotalDividend
'				-2492 : db error(insert) : 로그기록 실패
'				-2493 : db error(update) : 배당금 지급 실패
'				-2494 : db error(delete) : 머니카드 아이템키 삭제 실패
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Dice_setDicePay]
	@nCharNo        int
,	@nItemKey       bigint
,	@nTotalDividend bigint
,	@nRet           int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	SET @nRet = 0

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

	IF ISNULL(@nItemKey, 0) <= 0 BEGIN
		SET @nRet = -2412
		RETURN
	END

	IF ISNULL(@nTotalDividend, 0) <= 0 BEGIN
		SET @nRet = -2416
		RETURN
	END

	IF EXISTS(SELECT nItemKey FROM tItem WHERE nItemKey = @nItemKey AND nOwner = @nCharNo) BEGIN
		BEGIN TRAN

		-- 머니카드 아이템 삭제
		DELETE FROM tItem WHERE nItemKey = @nItemKey AND nOwner = @nCharNo 
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -2494
			ROLLBACK TRAN
			RETURN
		END

		-- 돈 지급
		UPDATE tCharacter SET nMoney = nMoney + @nTotalDividend WHERE nCharNo = @nCharNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -2493
			ROLLBACK TRAN
			RETURN
		END

		-- 돈 지급 로그
		INSERT INTO tDicePayLog (nCharNo, nItemKey, nTotalDividend)
		VALUES (@nCharNo, @nItemKey, @nTotalDividend)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -2492
			ROLLBACK TRAN
			RETURN
		END
		ELSE BEGIN
			SET @nRet = 0
			COMMIT TRAN
			RETURN
		END
	END
	ELSE BEGIN
		SET @nRet = -2423
		RETURN	
	END
	
	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Dice_setDiceResult]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Dice_setDiceResult
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.6.30
' Description :
'   주사위 게임에서 한판이 끝났을때 각 유저의 총 배당금을 지급함.
' Parameter :
'	input Parameter
'		@nGameNo        : 게임번호
'		@nCustomCharNo  : 고객 캐릭터번호
'		@nTotalDividend : 배당금 총 합계
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2414 : invaild @nGameNo
'				-2415 : invaild @nCustomCharNo
'				-2421 : not enough Money
'				-2492 : db error(insert)
'				-2493 : db error(update)
'	return Record
'		nCustomCharNo, nMoney
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Dice_setDiceResult] 
	@nGameNo        int
,	@nCustomCharNo  int
,	@nTotalDividend bigint
,	@nRet           int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @money bigint, @resultMoney bigint

	SET @nRet  = 0
	SET @money = 0

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

	IF ISNULL(@nCustomCharNo, 0) <= 0 BEGIN
		SET @nRet = -2415
		RETURN
	END

	BEGIN TRAN

	SELECT @money = nMoney FROM tCharacter WHERE nCharNo = @nCustomCharNo
	IF ( @money + @nTotalDividend ) > 0 BEGIN
		SET @resultMoney = @money + @nTotalDividend
	END
	ELSE BEGIN
		SET @resultMoney = 0
	END
	
	UPDATE tCharacter SET nMoney = @resultMoney
	WHERE nCharNo = @nCustomCharNo
	IF @@ERROR <> 0 OR @@ROWCOUNT > 1 BEGIN
		SET @nRet = - 2493
		ROLLBACK TRAN
		RETURN
	END

	INSERT INTO tDiceResult (nGameNo, nCustomCharNo, nBeforeMoney, nTotalDividend, nAfterMoney)
	VALUES (@nGameNo, @nCustomCharNo, @money, @nTotalDividend, @resultMoney)
	IF @@ERROR <> 0 BEGIN
		SET @nRet = - 2492
		ROLLBACK TRAN
		RETURN
	END
	ELSE BEGIN
		SET @nRet = 0
		COMMIT TRAN
	END

	SELECT nCharNo AS nCustomCharNo, nMoney FROM tCharacter WHERE nCharNo = @nCustomCharNo
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Event_chkReturn_20081113]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Event_chkReturn_20081113
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2008.11.12
' Description :
'   휴먼계정이 재접속시 아이템을 지급하는 저장프로시저
'	캐릭터번호를 받는 이유는 기존 프로토콜(서버측)에서 캐릭터번호만을 넘기기 때문에
'	기존 프로토콜 수정없이 처리하기 위함이다.
'	따라서, 저장프로시저에서 해당 캐릭터번호의 유저번호를 검색하여 처리한다.
' Parameter :
'	input Parameter
'		@nCharNo : 캐릭터번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1911 : 캐릭터번호가 잘 못 입력되었습니다.
'				-1921 : 10월 14일 ~ 11월 12일 이내에 비접속자가 아님
'				-1996 : db error(remote execute)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Event_chkReturn_20081113]
	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @sDate    AS datetime
	DECLARE @lastDate AS datetime
	DECLARE @nUserNo  AS int

	SET @nRet     = 0
	SET @sDate    = '2008-10-14 00:00:00'
	SET @nUserNo  = 0

	SELECT @nUserNo = nUserNo FROM tCharacter WHERE nCharNo = @nCharNo
	IF @nUserNo = 0 BEGIN
		SET @nRet = -1911
		RETURN
	END

	SELECT @lastDate = ISNULL(MAX(dLastLoginDate), GETDATE()) 
	  FROM tCharacter
	 WHERE nUserNo = @nUserNo AND dCreateDate < @sDate
	-- 마지막 로그인 날짜가 @sDate 보다 작아야 지급대상임.
	IF @lastDate < @sDate BEGIN
		EXEC Account.dbo.usp_Event_0811_payPresent @nUserNo, 2, '', @nRet output
		IF @nRet <> 0 BEGIN
			INSERT INTO tEventLog_20081113 (userNo, eventNo, errorNo)
			VALUES (@nUserNo, 2, @nRet)

			SET @nRet = -1996
		END
		ELSE BEGIN
			SET @nRet = 0
		END
	END
	ELSE BEGIN
		SET @nRet = -1921
	END

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_addCoin]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_addCoin
' 작성자 : 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_addCoin]
	@nCharNo  int
,	@nItemKey bigint
,	@nCoin    bigint
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @status tinyint, @coin bigint
	
	SET @status = 2 -- 유료아이템 -> 코인
	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
		
	IF EXISTS(SELECT nItemKey FROM tItem WHERE nItemKey = @nItemKey AND nOwner = @nCharNo) BEGIN
		BEGIN TRAN

		DELETE FROM tItem WHERE nItemKey = @nItemKey AND nOwner = @nCharNo 
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			SET @nRet = -3332
			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)
		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
	ELSE BEGIN
		SET @nRet = -3331
		RETURN	
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_getCoin]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_getCoin
' 작성자 : 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
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_getCoin]
	@nCharNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	IF EXISTS(SELECT nCharNo FROM tGambleCoin WHERE nCharNo = @nCharNo) BEGIN
		SELECT nCoin FROM tGambleCoin WHERE nCharNo = @nCharNo
	END
	ELSE BEGIN
		SELECT 0 AS nCoin
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_getDiceRank]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_getDiceRank
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.6.30
' Description :
'   주사위 게임에서 아이템당 순위를 가져옴.
'	상위 20 캐릭터
' Parameter :
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-2411 : invaild @nCharNo
'				-2412 : invaild @nItemKey
'	return Record
'		sID, nWinCount
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_getDiceRank]
	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0

	SELECT TOP 20 C.sID, COUNT(B.nGameNo) AS nWinCount
	  FROM tGambleDice AS A, tGambleDiceLog AS B, tCharacter AS C
	 WHERE A.nGameNo = B.nGameNo
	   AND B.nCharNo = C.nCharNo
	   AND B.bStatus = 1
	   AND C.bDeleted = 0
	 GROUP BY C.sID
	 ORDER BY nWinCount DESC

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_getExchangedCoin]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_getExchangedCoin
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.5.16
' Description :
'   유료아이템을 이용한 코인만 증가되는 경우
' Parameter :
'	input Parameter
'		@nGameNo : 게임번호
'		@nDate   : 코인
'	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
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_getExchangedCoin]
	@nCharNo int
,	@dDate   datetime
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	SELECT ISNULL(SUM(nExchangedCoin), 0) AS nExchangedCoin
	  FROM tGambleCoinExchangeLog 
	 WHERE nCharNo = @nCharNo 
	   AND dDate   >= @dDate 
	   AND nStatus = 1
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_getJackpotCoin]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_getJackpotCoin
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.7.13
' Description :
'   슬롯머신 잭팟 누적금액 리스트
' Parameter :
'	input Parameter
'		@sGameName : 게임이름
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'	return Record
'		nBattingUnit, nJackpotCoin
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_getJackpotCoin] 
	@sGameName    nvarchar(20)
--,	@nBattingUnit bigint
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	
	SELECT nBattingUnit, nJackpotCoin 
	  FROM tGambleJackpot 
	 WHERE sGameName = @sGameName 
--	   AND nBattingUnit = @nBattingUnit 	 
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_getJackpotRanking]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_getJackpotRanking
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.7.13
' Description :
'   슬롯머신 잭팟 랭킹
' Parameter :
'	input Parameter
'		@sGameName : 게임이름
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'	return Record
'		nBattingUnit, nJackpotCoin
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Gamble_getJackpotRanking] 
	@sGameName    nvarchar(20)
,	@nBattingUnit bigint
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	
	SELECT TOP 50 sID, nJackpotCoin  
	  FROM tGambleJackpotLog 
	 WHERE sGameName = @sGameName 
	   AND nBattingUnit = @nBattingUnit 
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Gamble_setCoinToMoney]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Gamble_setCoinToMoney
' 작성자 : 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_setCoinToMoney]
	@nCharNo int
,	@nMoney  bigint
,	@nCoin   bigint
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @status tinyint
	DECLARE @money bigint, @coin bigint
	
	SET @status = 0 -- 코인 -> 게임머니
	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	

	BEGIN TRAN
		
	SELECT @coin = nCoin FROM tGambleCoin WITH(UPDLOCK, HOLDLOCK) WHERE nCharNo = @nCharNo
	IF @coin IS NULL BEGIN
		SET @nRet = -3321
		ROLLBACK TRAN
		RETURN
	END
	
	IF @coin < @nCoin BEGIN
		SET @nRet = -3322
		ROLLBACK TRAN
		RETURN
	END
	
	UPDATE tGambleCoin 
	   SET sys_update_date = GETDATE()
		 , nCoin = nCoin - @nCoin 
	 WHERE nCharNo = @nCharNo 
	   AND nCoin  >= @coin
	IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
		ROLLBACK TRAN
		SET @nRet = -3323
		RETURN
	END
	
	SELECT @money = nMoney FROM tCharacter WITH(UPDLOCK, HOLDLOCK) WHERE nCharNo = @nCharNo AND bDeleted = 0
	IF @money IS NULL BEGIN		
		ROLLBACK TRAN
		SET @nRet = -3324
		RETURN
	END
	ELSE BEGIN
		UPDATE tCharacter
		   SET nMoney   = nMoney + @nMoney
		 WHERE nCharNo  = @nCharNo
		   AND bDeleted = 0
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			SET @nRet = -3325
			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_setDice]    Script Date: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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
			SET @nRet = 0
		END

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_getMemberList]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildAcademy_getMemberList
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2008.4.28
' Description :
'   길드아카데미에 소속된 맴버리스트
'	2011.3.22 comment by kodong
'	길드아카데미 멤버의 채팅금지시간을 가져오는 부분 추가됨
' 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

	-- add G.nChatBanTime
	SELECT G.nCharNo, G.nJoinLevel, G.dJoinDate, G.nGrade, G.sIntro, G.nChatBanTime
		 , 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildAcademy_getRanking
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2008.5.26.
' Description :
'   길드아카데미 랭킹을 산정하는 저장프로시저
'	서버(World_Manager의 호출에 의해 동작한다.)
' 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
,	@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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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_setChatBanTime]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildAcademy_setChatBanTime
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.3.22
' 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_setChatBanTime]
	@nNo          int
,	@nCharNo      int
,	@nChatBanTime int
,	@nRet         int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @nRet = 0
	
	IF EXISTS(SELECT nNo FROM tGuildAcademyMember WHERE nNo = @nNo AND nCharNo = @nCharNo) BEGIN
		UPDATE tGuildAcademyMember 
		   SET nChatBanTime = @nChatBanTime
		 WHERE nNo = @nNo AND nCharNo = @nCharNo
		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_setGraduate]    Script Date: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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
			
			COMMIT TRAN
		END
	ELSE
		BEGIN
			SET @nRet = -1521
		END	

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setNotify]    Script Date: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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

	SET @nRet = 0
	COMMIT TRAN

	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GuildAcademy_setReward]    Script Date: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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 AND nMoney >= @nMoney
			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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildTournament_setRegist
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2009.4.6
' Description :
'   길드토너먼트를 신청하는 저장프로시저
'	각종 제한사항이 체크되어야 함.
'	2011.8.22 by kodong
'	길드토너먼트 신청시 이전 길드토너먼트 우승 길드는 참여할 수 없도록 지정
' 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 @prevGTNo int, @prevWinnerNo int
	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

	-- 2011.8.22 by kodong
	-- 길드토너먼트 신청시 이전 길드토너먼트 우승길드는 신청할 수 없음.	
	SELECT TOP 1 @prevGTNo = nGTNo 
	  FROM tGuildTournament 
	 WHERE nGTNo < @nGTNo 
	 ORDER BY nGTNo DESC
	IF @prevGTNo IS NOT NULL BEGIN
		SELECT @prevWinnerNo = nGuildNo
		  FROM tGuildTournament 
		 WHERE nGTNo = @prevGTNo
		   AND nType >= 3028 AND nType <= 3029
		   AND nData = 1
		IF @prevWinnerNo = @nNo BEGIN
			SET @nRet = -1424
			RETURN
		END
	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_GuildTournament_setResult]    Script Date: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_GuildTournament_setResult
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.8.24
' Description :
'	2011.8.22 by kodong
'	이전 우승길드 참여제한이 생기면서 결과를 저장하는 프로시저를 분리함.
'	길드번호를 저장하는 프로토콜을 추가함.
'	OPTool 수정에 대한 이슈 있음.(OPTool은 임의의 결과 설정시 p_GuildTournament_set을 호출함.
' Parameter :
'	input Parameter
'		@nGTNo        : 길드토너먼트번호
'		@nType        : 트리의 위치를 설정(대전표)
'		@nData        : 승패(1:승리/2:패배)
'		@nGuildNo     : 길드번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1492 : db error(insert)
'				-1493 : db error(update)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_GuildTournament_setResult]
	@nGTNo    int
,	@nType    smallint
,	@nData    int
,	@nGuildNo int
,	@nRet     int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	IF EXISTS(SELECT nGTNo FROM tGuildTournament WHERE  nGTNo = @nGTNo AND nType = @nType) BEGIN
		UPDATE tGuildTournament 
		   SET nData = @nData 
		     , nGuildNo = @nGuildNo 
		 WHERE nGTNo = @nGTNo 
		   AND nType = @nType
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			SET @nRet = -1493
		END		   
	END
	ELSE BEGIN
		INSERT tGuildTournament (nGTNo, nType, nData, nGuildNo)
		VALUES (@nGTNo, @nType, @nData, @nGuildNo)
		IF @@ERROR <> 0 BEGIN
			SET @nRet = -1492
		END			
	END
END


GO
/****** Object:  StoredProcedure [dbo].[usp_HolyPromise_getRemainMoney]    Script Date: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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: 12/20/2015 6:11:00 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
USE [master]
GO
ALTER DATABASE [World00_Character] SET  READ_WRITE 
GO
