267 lines
9.2 KiB
Transact-SQL
267 lines
9.2 KiB
Transact-SQL
-- 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
|