-- Generated. Real tables and procedures from the 2021 release for features that were stubs in the 2014 testbed.
USE [LosaGame]
GO
IF OBJECT_ID('dbo.game_character_get_list') IS NOT NULL DROP PROCEDURE dbo.game_character_get_list
GO
CREATE PROCEDURE [dbo].[game_character_get_list]
(
	@accountIDX INT
)
AS

SET NOCOUNT ON

SELECT idx, ClassType,
       CI_type, CI_gender, CI_beard, CI_face, CI_hair, CI_skinColor, CI_hairColor, CI_trinket, CI_underwear,
       EI1_type, EI2_type, EI3_type, EI4_type, CI_position,
       limitTime, limitType, leaderType, rentalType, rentalTime, awakeType, awakeTime 
  FROM dbo.userCharacterDB WITH(NOLOCK)
 WHERE accountIDX = @accountIDX
 ORDER BY idx ASC
GO
IF OBJECT_ID('dbo.game_class_get_list') IS NOT NULL DROP PROCEDURE dbo.game_class_get_list
GO
CREATE PROCEDURE [dbo].[game_class_get_list]
(
	@accountIDX INT
)
AS

SET NOCOUNT ON

SELECT idx,
       class1_type, class1_level, class1_expert, class1_stat, class2_type, class2_level, class2_expert, class2_stat,
       class3_type, class3_level, class3_expert, class3_stat, class4_type, class4_level, class4_expert, class4_stat,
       class5_type, class5_level, class5_expert, class5_stat, class6_type, class6_level, class6_expert, class6_stat,
       class7_type, class7_level, class7_expert, class7_stat, class8_type, class8_level, class8_expert, class8_stat,
       class9_type, class9_level, class9_expert, class9_stat, class10_type, class10_level, class10_expert, class10_stat
  FROM dbo.userClassDB WITH(NOLOCK)
 WHERE accountIDX = @accountIDX
 ORDER BY idx ASC
GO
IF OBJECT_ID('dbo.game_game_get_data') IS NOT NULL DROP PROCEDURE dbo.game_game_get_data
GO
CREATE PROCEDURE [dbo].[game_game_get_data]  
(  
 @userID varchar(12)  
)  
As  
  
SET NOCOUNT ON  
  
  
DECLARE @CURRENTDATE DATETIME
DECLARE @CONNECTDATE DATETIME

SET @CURRENTDATE = GETDATE();
SET @CONNECTDATE = ( SELECT conndate FROM dbo.userGameDB WHERE accountIDX IN ( SELECT accountIDX FROM dbo.userMemberDB WHERE userID = @userID ))
  
BEGIN
	IF( @CURRENTDATE < @CONNECTDATE )
	SET @CONNECTDATE = @CURRENTDATE
END

SELECT a.accountIDX, b.userID, b.nickName, a.userState, a.gameMoney  
  , a.conn_count, @CONNECTDATE, a.userLevel, a.userEXP--, a.relateLevel  
  , b.eventType, isNull(c.class,0) as 'class', b.joinType, b.regDate, a.regionType  
  , a.rencpoint, a.renspoint, isNull(c.region,0) as 'region', b.cpType, ISNULL(d.cpID, '') AS 'cpID'  
  , ISNULL(d.note, '') AS 'cpUNIQ', b.limitType, b.limitDate, a.userFishingLevel, a.userFishingEXP  
  , a.refillData, a.userExcavationLevel, a.userExcavationEXP, a.userHerocEXP, a.userHerosEXP, a.practice   
  FROM userGameDB a WITH( NOLOCK )  
  JOIN userMemberDB b WITH( NOLOCK )  
 ON a.accountIDX = b.accountIDX  
  LEFT JOIN userRankingDB c WITH( NOLOCK )  
 ON a.accountIDX = c.accountIDX  
  LEFT JOIN userCooperationDB d WITH( NOLOCK )  
 ON a.accountIDX=d.accountIDX WHERE b.userID = @userID
GO
IF OBJECT_ID('dbo.game_game_save') IS NOT NULL DROP PROCEDURE dbo.game_game_save
GO
CREATE PROCEDURE [dbo].[game_game_save]
(
	@accountIDX int
,	@userState int
,	@gameMoney bigint
,	@conn_count int
,	@connDate datetime
,	@userLevel int
,	@userEXP int
,	@playTime int
,	@regionType int
,	@userFishingLevel int
,	@userFishingEXP int
,	@refillData int
,	@userExcavationLevel int
,	@userExcavationEXP int
,	@init_time int
)
As

SET NOCOUNT ON

IF @userLevel < 50
BEGIN
	-- 기존 루틴 그대로
	UPDATE dbo.userGameDB
	   SET gameMoney = @gameMoney
	,	userState = @userState
	,	playTime = playTime + Round((@playTime/1000)/60,0)
	,	conn_count = @conn_count
	,	userLevel = @userLevel
	,	userEXP = @userEXP
	,	userFishingLevel = @userFishingLevel
	,	userFishingEXP = @userFishingEXP
	,	regionType = @regionType
	,	connDate = @connDate
	,	refillData = @refillData
	,	userExcavationLevel = @userExcavationLevel
	,	userExcavationEXP = @userExcavationEXP 
	, Practice = 0
	 WHERE accountIDX = @accountIDX

END
ELSE
BEGIN
	--
	IF( EXISTS( SELECT * FROM dbo.userRelativeLevelDB WITH( NOLOCK )
				 WHERE accountIDX = @accountIDX ) )
	BEGIN
		
		UPDATE dbo.userGameDB
		   SET gameMoney = @gameMoney
		,	userState = @userState
		,	playTime = playTime + Round((@playTime/1000)/60,0)
		,	conn_count = @conn_count
		--,	userLevel = @userLevel
		,	userEXP = @userEXP
		,	userFishingLevel = @userFishingLevel
		,	userFishingEXP = @userFishingEXP
		,	regionType = @regionType
		,	connDate = @connDate
		,	refillData = @refillData
		,	userExcavationLevel = @userExcavationLevel
		,	userExcavationEXP = @userExcavationEXP 
		, Practice = 0
		 WHERE accountIDX = @accountIDX

		UPDATE dbo.userRelativeLevelDB
		   SET init_time = @init_time
		 WHERE accountIDX = @accountIDX

	END
	ELSE
	BEGIN
		-- 기존 루틴 그대로 : 혹시나 ?
		UPDATE dbo.userGameDB
		   SET gameMoney = @gameMoney
		,	userState = @userState
		,	playTime = playTime + Round((@playTime/1000)/60,0)
		,	conn_count = @conn_count
		,	userLevel = @userLevel
		,	userEXP = @userEXP
		,	userFishingLevel = @userFishingLevel
		,	userFishingEXP = @userFishingEXP
		,	regionType = @regionType
		,	connDate = @connDate
		,	refillData = @refillData
		,	userExcavationLevel = @userExcavationLevel
		,	userExcavationEXP = @userExcavationEXP 
		, Practice = 0
		 WHERE accountIDX = @accountIDX
	END
END
GO
IF OBJECT_ID('dbo.game_item_extra_get_list') IS NOT NULL DROP PROCEDURE dbo.game_item_extra_get_list
GO
CREATE PROCEDURE [dbo].[game_item_extra_get_list]
(
	@idx INT
,	@accountIDX INT
,	@getCount int
)
AS

SET NOCOUNT ON

SELECT TOP (@getCount) idx,
	   item1_type, item1_reinforce, item1_slotindex, item1_tradetype, item1_limitType, item1_mcustom, item1_fcustom, item1_failexp, item1_limitDate,
	   item2_type, item2_reinforce, item2_slotindex, item2_tradetype, item2_limitType, item2_mcustom, item2_fcustom, item2_failexp, item2_limitDate,
	   item3_type, item3_reinforce, item3_slotindex, item3_tradetype, item3_limitType, item3_mcustom, item3_fcustom, item3_failexp, item3_limitDate,
	   item4_type, item4_reinforce, item4_slotindex, item4_tradetype, item4_limitType, item4_mcustom, item4_fcustom, item4_failexp, item4_limitDate,
	   item5_type, item5_reinforce, item5_slotindex, item5_tradetype, item5_limitType, item5_mcustom, item5_fcustom, item5_failexp, item5_limitDate,
	   item6_type, item6_reinforce, item6_slotindex, item6_tradetype, item6_limitType, item6_mcustom, item6_fcustom, item6_failexp, item6_limitDate,
	   item7_type, item7_reinforce, item7_slotindex, item7_tradetype, item7_limitType, item7_mcustom, item7_fcustom, item7_failexp, item7_limitDate,
	   item8_type, item8_reinforce, item8_slotindex, item8_tradetype, item8_limitType, item8_mcustom, item8_fcustom, item8_failexp, item8_limitDate,
	   item9_type, item9_reinforce, item9_slotindex, item9_tradetype, item9_limitType, item9_mcustom, item9_fcustom, item9_failexp, item9_limitDate,
	   item10_type, item10_reinforce, item10_slotindex, item10_tradetype, item10_limitType, item10_mcustom, item10_fcustom, item10_failexp, item10_limitDate
  FROM dbo.userItemExtraDB WITH (NOLOCK)
 WHERE accountIDX = @accountIDX
   AND idx > @idx
 ORDER BY idx ASC
GO
IF OBJECT_ID('dbo.game_guild_login_get_data') IS NOT NULL DROP PROCEDURE dbo.game_guild_login_get_data
GO
-- =============================================
-- Subject		: dbo.game_guild_login_get_data
-- DBA			: KeuN
-- DATE			: 2015-08-24 09:43
-- Description	: 길드원 데이터 SELECT (길드본부 추가)
-- =============================================


CREATE PROCEDURE [dbo].[game_guild_login_get_data]
(
	@ACCOUNT_IDX int
)
AS

	SET NOCOUNT ON

	DECLARE @GUILD_IDX	INT = 0;
	DECLARE @GUILD_HQ	TINYINT = 0;

	-- 길드 본부 활성화 여부 확인
	SET @GUILD_IDX = (SELECT guildIDX FROM dbo.userGuildInfoDB WHERE AccountIDX=@ACCOUNT_IDX)
	IF (@GUILD_IDX<>0) or (@GUILD_IDX IS NOT NULL)
	BEGIN
		IF EXISTS (SELECT * FROM dbo.userGuildHQDB WHERE GuildIDX=@GUILD_IDX)
		BEGIN
			SET @GUILD_HQ=1
		END
	END

	-- 사용자 길드 관련 데이터 세팅
	SELECT B.idx, B.guildName, B.guildmark, A.guildPosition, A.msg, A.AttendDate, A.GuildRankDate, A.regDate, @GUILD_HQ
	FROM dbo.userGuildInfoDB as A WITH(READUNCOMMITTED) INNER JOIN dbo.userGuildDB AS B WITH(READUNCOMMITTED)
	ON A.guildIDX = B.idx 
	WHERE A.accountIDX = @ACCOUNT_IDX
	AND A.guildJoinType in (-1, 1)
GO
IF OBJECT_ID('dbo.game_guild_get_list') IS NOT NULL DROP PROCEDURE dbo.game_guild_get_list
GO
CREATE PROCEDURE [dbo].[game_guild_get_list]
(
	@topn int
,	@idx int
)
AS

SET NOCOUNT ON

SELECT TOP (@topn) a.idx, a.guildName, a.aboutguild, a.guildmark, a.point, a.maxcount, a.regDate,
       a.membercount, a.todaypoint, a.guildLevel,
	   b.type1_win, b.type1_lose, b.type1_kill, b.type1_death
  FROM dbo.userGuildDB a WITH(NOLOCK)
 INNER JOIN dbo.userRecordGuildDB b WITH(NOLOCK)
    ON a.idx = b.guildIDX
 WHERE a.idx < @idx
 ORDER BY a.idx DESC
GO

USE [LosaGame]
GO
-- game_friend_list_asc names this index in a table hint; the 2012 table does not have it.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='idx_userAccIDX' AND object_id=OBJECT_ID('dbo.userFriendDB'))
	CREATE CLUSTERED INDEX idx_userAccIDX ON dbo.userFriendDB (userAccIDX)
GO